Below is the list of changes that have just been committed into a local
5.1 repository of evgen. When evgen does a push these changes will
be propagated to the main repository and, within 24 hours after the
push, to the public repository.
For information on how to access the public repository
see http://dev.mysql.com/doc/mysql/en/installing-source-tree.html
ChangeSet@stripped, 2006-08-01 09:24:19+04:00, evgen@stripped +32 -0
Merge epotemkin@stripped:/home/bk/mysql-5.1-opt
into sunlight.local:/local_work/tmp_merge-5.1-opt-mysql
MERGE: 1.2181.114.6
mysql-test/r/auto_increment.result@stripped, 2006-08-01 09:24:14+04:00, evgen@stripped +0 -0
Auto merged
MERGE: 1.42.1.1
mysql-test/r/binlog_row_blackhole.result@stripped, 2006-08-01 09:24:14+04:00, evgen@stripped +0 -0
Auto merged
MERGE: 1.6.1.1
mysql-test/r/binlog_stm_blackhole.result@stripped, 2006-08-01 09:24:14+04:00, evgen@stripped +0 -0
Auto merged
MERGE: 1.13.1.1
mysql-test/r/date_formats.result@stripped, 2006-08-01 09:24:14+04:00, evgen@stripped +0 -0
Auto merged
MERGE: 1.24.1.1
mysql-test/r/func_compress.result@stripped, 2006-08-01 09:24:14+04:00, evgen@stripped +0 -0
Auto merged
MERGE: 1.16.1.1
mysql-test/r/func_gconcat.result@stripped, 2006-08-01 09:24:14+04:00, evgen@stripped +0 -0
Auto merged
MERGE: 1.57.1.1
mysql-test/r/func_group.result@stripped, 2006-08-01 09:24:14+04:00, evgen@stripped +0 -0
Auto merged
MERGE: 1.48.1.3
mysql-test/r/func_math.result@stripped, 2006-08-01 09:24:14+04:00, evgen@stripped +0 -0
Auto merged
MERGE: 1.36.1.1
mysql-test/r/func_str.result@stripped, 2006-08-01 09:24:14+04:00, evgen@stripped +0 -0
Auto merged
MERGE: 1.119.1.2
mysql-test/r/func_system.result@stripped, 2006-08-01 09:24:14+04:00, evgen@stripped +0 -0
Auto merged
MERGE: 1.24.1.1
mysql-test/r/func_time.result@stripped, 2006-08-01 09:24:15+04:00, evgen@stripped +0 -0
Auto merged
MERGE: 1.59.1.1
mysql-test/r/gis.result@stripped, 2006-08-01 09:24:15+04:00, evgen@stripped +0 -0
Auto merged
MERGE: 1.30.2.1
mysql-test/r/group_by.result@stripped, 2006-08-01 09:24:15+04:00, evgen@stripped +0 -0
Auto merged
MERGE: 1.68.1.1
mysql-test/r/group_min_max.result@stripped, 2006-08-01 09:24:15+04:00, evgen@stripped +0 -0
Auto merged
MERGE: 1.23.2.1
mysql-test/r/myisam.result@stripped, 2006-08-01 09:24:15+04:00, evgen@stripped +0 -0
Auto merged
MERGE: 1.83.1.1
mysql-test/r/olap.result@stripped, 2006-08-01 09:24:15+04:00, evgen@stripped +0 -0
Auto merged
MERGE: 1.32.1.1
mysql-test/r/partition_pruning.result@stripped, 2006-08-01 09:24:15+04:00, evgen@stripped +0 -0
Auto merged
MERGE: 1.19.1.1
mysql-test/r/query_cache.result@stripped, 2006-08-01 09:24:15+04:00, evgen@stripped +0 -0
Auto merged
MERGE: 1.75.1.1
mysql-test/r/rpl_get_lock.result@stripped, 2006-08-01 09:24:15+04:00, evgen@stripped +0 -0
Auto merged
MERGE: 1.13.1.1
mysql-test/r/rpl_master_pos_wait.result@stripped, 2006-08-01 09:24:15+04:00, evgen@stripped +0 -0
Auto merged
MERGE: 1.6.1.1
mysql-test/r/select.result@stripped, 2006-08-01 09:24:15+04:00, evgen@stripped +0 -0
Auto merged
MERGE: 1.132.1.3
mysql-test/r/subselect.result@stripped, 2006-08-01 09:24:15+04:00, evgen@stripped +0 -0
Auto merged
MERGE: 1.147.1.1
mysql-test/r/type_blob.result@stripped, 2006-08-01 09:24:15+04:00, evgen@stripped +0 -0
Auto merged
MERGE: 1.52.1.1
mysql-test/r/union.result@stripped, 2006-08-01 09:24:15+04:00, evgen@stripped +0 -0
Auto merged
MERGE: 1.85.1.2
mysql-test/r/variables.result@stripped, 2006-08-01 09:24:15+04:00, evgen@stripped +0 -0
Auto merged
MERGE: 1.85.1.1
mysql-test/r/view.result@stripped, 2006-08-01 09:24:15+04:00, evgen@stripped +0 -0
Auto merged
MERGE: 1.166.1.1
mysql-test/t/group_min_max.test@stripped, 2006-08-01 09:24:15+04:00, evgen@stripped +0 -0
Auto merged
MERGE: 1.21.2.1
mysql-test/t/partition_pruning.test@stripped, 2006-08-01 09:24:15+04:00, evgen@stripped +0 -0
Auto merged
MERGE: 1.16.1.1
sql/opt_range.cc@stripped, 2006-08-01 09:24:15+04:00, evgen@stripped +0 -0
Auto merged
MERGE: 1.223.1.2
sql/sql_class.cc@stripped, 2006-08-01 09:24:15+04:00, evgen@stripped +0 -0
Auto merged
MERGE: 1.271.3.1
sql/sql_select.cc@stripped, 2006-08-01 09:24:16+04:00, evgen@stripped +0 -0
Auto merged
MERGE: 1.417.2.2
sql/table.h@stripped, 2006-08-01 09:24:16+04:00, evgen@stripped +0 -0
Auto merged
MERGE: 1.142.1.1
# This is a BitKeeper patch. What follows are the unified diffs for the
# set of deltas contained in the patch. The rest of the patch, the part
# that BitKeeper cares about, is below these diffs.
# User: evgen
# Host: sunlight.local
# Root: /local_work/tmp_merge-5.1-opt-mysql/RESYNC
--- 1.227/sql/opt_range.cc 2006-08-01 09:24:25 +04:00
+++ 1.228/sql/opt_range.cc 2006-08-01 09:24:25 +04:00
@@ -579,7 +579,8 @@
static SEL_TREE *get_mm_tree(RANGE_OPT_PARAM *param,COND *cond);
static bool is_key_scan_ror(PARAM *param, uint keynr, uint8 nparts);
-static ha_rows check_quick_select(PARAM *param,uint index,SEL_ARG *key_tree);
+static ha_rows check_quick_select(PARAM *param,uint index,SEL_ARG *key_tree,
+ bool update_tbl_stats);
static ha_rows check_quick_keys(PARAM *param,uint index,SEL_ARG *key_tree,
char *min_key,uint min_key_flag,
char *max_key, uint max_key_flag);
@@ -589,6 +590,7 @@
MEM_ROOT *alloc = NULL);
static TRP_RANGE *get_key_scans_params(PARAM *param, SEL_TREE *tree,
bool index_read_must_be_used,
+ bool update_tbl_stats,
double read_time);
static
TRP_ROR_INTERSECT *get_best_ror_intersect(const PARAM *param, SEL_TREE *tree,
@@ -1956,16 +1958,46 @@
quick - Parameter to use when reading records.
In the table struct the following information is updated:
- quick_keys - Which keys can be used
- quick_rows - How many rows the key matches
+ quick_keys - Which keys can be used
+ quick_rows - How many rows the key matches
+ quick_condition_rows - E(# rows that will satisfy the table condition)
+
+ IMPLEMENTATION
+ quick_condition_rows value is obtained as follows:
+
+ It is a minimum of E(#output rows) for all considered table access
+ methods (range and index_merge accesses over various indexes).
+
+ The obtained value is not a true E(#rows that satisfy table condition)
+ but rather a pessimistic estimate. To obtain a true E(#...) one would
+ need to combine estimates of various access methods, taking into account
+ correlations between sets of rows they will return.
+
+ For example, if values of tbl.key1 and tbl.key2 are independent (a right
+ assumption if we have no information about their correlation) then the
+ correct estimate will be:
+
+ E(#rows("tbl.key1 < c1 AND tbl.key2 < c2")) =
+ = E(#rows(tbl.key1 < c1)) / total_rows(tbl) * E(#rows(tbl.key2 < c2)
+
+ which is smaller than
+
+ MIN(E(#rows(tbl.key1 < c1), E(#rows(tbl.key2 < c2)))
+
+ which is currently produced.
TODO
- Check if this function really needs to modify keys_to_use, and change the
- code to pass it by reference if it doesn't.
+ * Change the value returned in quick_condition_rows from a pessimistic
+ estimate to true E(#rows that satisfy table condition).
+ (we can re-use some of E(#rows) calcuation code from index_merge/intersection
+ for this)
+
+ * Check if this function really needs to modify keys_to_use, and change the
+ code to pass it by reference if it doesn't.
- In addition to force_quick_range other means can be (an usually are) used
- to make this function prefer range over full table scan. Figure out if
- force_quick_range is really needed.
+ * In addition to force_quick_range other means can be (an usually are) used
+ to make this function prefer range over full table scan. Figure out if
+ force_quick_range is really needed.
RETURN
-1 if impossible select (i.e. certainly no rows will be selected)
@@ -2117,10 +2149,15 @@
Notice that it can be constructed no matter if there is a range tree.
*/
group_trp= get_best_group_min_max(¶m, tree);
- if (group_trp && group_trp->read_cost < best_read_time)
+ if (group_trp)
{
- best_trp= group_trp;
- best_read_time= best_trp->read_cost;
+ param.table->quick_condition_rows= min(group_trp->records,
+ head->file->stats.records);
+ if (group_trp->read_cost < best_read_time)
+ {
+ best_trp= group_trp;
+ best_read_time= best_trp->read_cost;
+ }
}
if (tree)
@@ -2136,7 +2173,7 @@
bool can_build_covering= FALSE;
/* Get best 'range' plan and prepare data for making other plans */
- if ((range_trp= get_key_scans_params(¶m, tree, FALSE,
+ if ((range_trp= get_key_scans_params(¶m, tree, FALSE, TRUE,
best_read_time)))
{
best_trp= range_trp;
@@ -2179,13 +2216,15 @@
SEL_IMERGE *imerge;
TABLE_READ_PLAN *best_conj_trp= NULL, *new_conj_trp;
LINT_INIT(new_conj_trp); /* no empty index_merge lists possible */
-
DBUG_PRINT("info",("No range reads possible,"
" trying to construct index_merge"));
List_iterator_fast<SEL_IMERGE> it(tree->merges);
while ((imerge= it++))
{
new_conj_trp= get_best_disjunct_quick(¶m, imerge, best_read_time);
+ if (new_conj_trp)
+ set_if_smaller(param.table->quick_condition_rows,
+ new_conj_trp->records);
if (!best_conj_trp || (new_conj_trp && new_conj_trp->read_cost <
best_conj_trp->read_cost))
best_conj_trp= new_conj_trp;
@@ -3488,7 +3527,7 @@
{
DBUG_EXECUTE("info", print_sel_tree(param, *ptree, &(*ptree)->keys_map,
"tree in SEL_IMERGE"););
- if (!(*cur_child= get_key_scans_params(param, *ptree, TRUE, read_time)))
+ if (!(*cur_child= get_key_scans_params(param, *ptree, TRUE, FALSE, read_time)))
{
/*
One of index scans in this index_merge is more expensive than entire
@@ -4389,6 +4428,7 @@
ha_rows best_rows = double2rows(intersect_best->out_rows);
if (!best_rows)
best_rows= 1;
+ set_if_smaller(param->table->quick_condition_rows, best_rows);
trp->records= best_rows;
trp->index_scan_costs= intersect_best->index_scan_costs;
trp->cpk_scan= cpk_scan_used? cpk_scan: NULL;
@@ -4539,6 +4579,7 @@
trp->read_cost= total_cost;
trp->records= records;
trp->cpk_scan= NULL;
+ set_if_smaller(param->table->quick_condition_rows, records);
DBUG_PRINT("info",
("Returning covering ROR-intersect plan: cost %g, records %lu",
@@ -4563,7 +4604,8 @@
*/
static TRP_RANGE *get_key_scans_params(PARAM *param, SEL_TREE *tree,
- bool index_read_must_be_used,
+ bool index_read_must_be_used,
+ bool update_tbl_stats,
double read_time)
{
int idx;
@@ -4598,7 +4640,7 @@
bool read_index_only= index_read_must_be_used ? TRUE :
(bool) param->table->used_keys.is_set(keynr);
- found_records= check_quick_select(param, idx, *key);
+ found_records= check_quick_select(param, idx, *key, update_tbl_stats);
if (param->is_ror_scan)
{
tree->n_ror_scans++;
@@ -6817,9 +6859,12 @@
SYNOPSIS
check_quick_select
param Parameter from test_quick_select
- idx Number of index to use in PARAM::key SEL_TREE::key
- tree Transformed selection condition, tree->key[idx] holds intervals
- tree to be used for scanning.
+ idx Number of index to use in tree->keys
+ tree Transformed selection condition, tree->keys[idx]
+ holds the range tree to be used for scanning.
+ update_tbl_stats If true, update table->quick_keys with information
+ about range scan we've evaluated.
+
NOTES
param->is_ror_scan is set to reflect if the key scan is a ROR (see
is_key_scan_ror function for more info)
@@ -6833,7 +6878,7 @@
*/
static ha_rows
-check_quick_select(PARAM *param,uint idx,SEL_ARG *tree)
+check_quick_select(PARAM *param,uint idx,SEL_ARG *tree, bool update_tbl_stats)
{
ha_rows records;
bool cpk_scan;
@@ -6874,10 +6919,19 @@
records=check_quick_keys(param,idx,tree,param->min_key,0,param->max_key,0);
if (records != HA_POS_ERROR)
{
- param->table->quick_keys.set_bit(key);
+ if (update_tbl_stats)
+ {
+ param->table->quick_keys.set_bit(key);
+ param->table->quick_key_parts[key]=param->max_key_part+1;
+ param->table->quick_n_ranges[key]= param->n_ranges;
+ param->table->quick_condition_rows=
+ min(param->table->quick_condition_rows, records);
+ }
+ /*
+ Need to save quick_rows in any case as it is used when calculating
+ cost of ROR intersection:
+ */
param->table->quick_rows[key]=records;
- param->table->quick_key_parts[key]=param->max_key_part+1;
- param->table->quick_n_ranges[key]= param->n_ranges;
if (cpk_scan)
param->is_ror_scan= TRUE;
}
@@ -9027,7 +9081,7 @@
&cur_param_idx);
/* Check if this range tree can be used for prefix retrieval. */
cur_quick_prefix_records= check_quick_select(param, cur_param_idx,
- cur_index_tree);
+ cur_index_tree, TRUE);
}
cost_group_min_max(table, cur_index_info, used_key_parts,
cur_group_key_parts, tree, cur_index_tree,
--- 1.280/sql/sql_class.cc 2006-08-01 09:24:25 +04:00
+++ 1.281/sql/sql_class.cc 2006-08-01 09:24:25 +04:00
@@ -861,6 +861,11 @@
item->maybe_null=1;
field_list.push_back(item= new Item_return_int("rows", 10,
MYSQL_TYPE_LONGLONG));
+ if (lex->describe & DESCRIBE_EXTENDED)
+ {
+ field_list.push_back(item= new Item_float("filtered", 0.1234, 2, 4));
+ item->maybe_null=1;
+ }
item->maybe_null= 1;
field_list.push_back(new Item_empty_string("Extra", 255, cs));
return (result->send_fields(field_list,
--- 1.427/sql/sql_select.cc 2006-08-01 09:24:25 +04:00
+++ 1.428/sql/sql_select.cc 2006-08-01 09:24:25 +04:00
@@ -2115,6 +2115,7 @@
s->key_dependent= 0;
if (tables->schema_table)
table->file->stats.records= 2;
+ table->quick_condition_rows= table->file->records();
s->on_expr_ref= &tables->on_expr;
if (*s->on_expr_ref)
@@ -3798,15 +3799,25 @@
{ // Check full join
ha_rows rnd_records= s->found_records;
/*
- If there is a restriction on the table, assume that 25% of the
- rows can be skipped on next part.
- This is to force tables that this table depends on before this
- table
+ If there is a filtering condition on the table (i.e. ref analyzer found
+ at least one "table.keyXpartY= exprZ", where exprZ refers only to tables
+ preceding this table in the join order we're now considering), then
+ assume that 25% of the rows will be filtered out by this condition.
+
+ This heuristic is supposed to force tables used in exprZ to be before
+ this table in join order.
*/
if (found_constraint)
rnd_records-= rnd_records/4;
/*
+ If applicable, get a more accurate estimate. Don't use the two
+ heuristics at once.
+ */
+ if (s->table->quick_condition_rows != s->found_records)
+ rnd_records= s->table->quick_condition_rows;
+
+ /*
Range optimizer never proposes a RANGE if it isn't better
than FULL: so if RANGE is present, it's always preferred to FULL.
Here we estimate its cost.
@@ -3817,6 +3828,10 @@
For each record we:
- read record range through 'quick'
- skip rows which does not satisfy WHERE constraints
+ TODO:
+ We take into account possible use of join cache for ALL/index
+ access (see first else-branch below), but we don't take it into
+ account here for range/index_merge access. Find out why this is so.
*/
tmp= record_count *
(s->quick->read_time +
@@ -4386,6 +4401,8 @@
return;
DBUG_ENTER("best_extension_by_limited_search");
+ DBUG_EXECUTE("opt", print_plan(join, idx, read_time, record_count, idx,
+ "SOFAR:"););
/*
'join' is a partial plan with lower cost than the best plan so far,
@@ -14160,6 +14177,8 @@
item_list.push_back(item_null);
if (join->thd->lex->describe & DESCRIBE_PARTITIONS)
item_list.push_back(item_null);
+ if (join->thd->lex->describe & DESCRIBE_EXTENDED)
+ item_list.push_back(item_null);
item_list.push_back(new Item_string(message,strlen(message),cs));
if (result->send_data(item_list))
@@ -14220,6 +14239,9 @@
item_list.push_back(item_null);
/* ref */
item_list.push_back(item_null);
+ /* in_rows */
+ if (join->thd->lex->describe & DESCRIBE_EXTENDED)
+ item_list.push_back(item_null);
/* rows */
item_list.push_back(item_null);
/* extra */
@@ -14375,10 +14397,33 @@
item_list.push_back(item_null);
item_list.push_back(item_null);
}
+
/* Add "rows" field to item_list. */
- item_list.push_back(new Item_int((longlong) (ulonglong)
- join->best_positions[i]. records_read,
- 21));
+ ha_rows examined_rows;
+ if (tab->select && tab->select->quick)
+ examined_rows= tab->select->quick->records;
+ else if (tab->type == JT_NEXT || tab->type == JT_ALL)
+ examined_rows= tab->table->file->records();
+ else
+ examined_rows=(ha_rows)join->best_positions[i].records_read;
+
+ item_list.push_back(new Item_int((longlong) (ulonglong) examined_rows,
+ 21));
+
+ /* Add "filtered" field to item_list. */
+ if (join->thd->lex->describe & DESCRIBE_EXTENDED)
+ {
+ Item_float *filtered;
+ float f;
+ if (examined_rows)
+ f= 100.0 * join->best_positions[i].records_read / examined_rows;
+ else
+ f= 0.0;
+ item_list.push_back((filtered= new Item_float(f)));
+ filtered->decimals= 2;
+ }
+
+
/* Build "Extra" field and add it to item_list. */
my_bool key_read=table->key_read;
if ((tab->type == JT_NEXT || tab->type == JT_CONST) &&
--- 1.145/sql/table.h 2006-08-01 09:24:25 +04:00
+++ 1.146/sql/table.h 2006-08-01 09:24:25 +04:00
@@ -264,10 +264,27 @@
MY_BITMAP *read_set, *write_set; /* Active column sets */
query_id_t query_id;
+ /*
+ For each key that has quick_keys.is_set(key) == TRUE: estimate of #records
+ and max #key parts that range access would use.
+ */
ha_rows quick_rows[MAX_KEY];
+
+ /* Bitmaps of key parts that =const for the entire join. */
key_part_map const_key_parts[MAX_KEY];
+
uint quick_key_parts[MAX_KEY];
uint quick_n_ranges[MAX_KEY];
+
+ /*
+ Estimate of number of records that satisfy SARGable part of the table
+ condition, or table->file->records if no SARGable condition could be
+ constructed.
+ This value is used by join optimizer as an estimate of number of records
+ that will pass the table condition (condition that depends on fields of
+ this table and constants)
+ */
+ ha_rows quick_condition_rows;
/*
If this table has TIMESTAMP field with auto-set property (pointed by
--- 1.33/mysql-test/r/olap.result 2006-08-01 09:24:25 +04:00
+++ 1.34/mysql-test/r/olap.result 2006-08-01 09:24:25 +04:00
@@ -87,8 +87,8 @@
TV NULL NULL 600
NULL NULL NULL 7785
explain extended select product, country_id , year, sum(profit) from t1 group by product, country_id, year with rollup;
-id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 ALL NULL NULL NULL NULL 15 Using filesort
+id select_type table type possible_keys key key_len ref rows filtered Extra
+1 SIMPLE t1 ALL NULL NULL NULL NULL 15 100.00 Using filesort
Warnings:
Note 1003 select `test`.`t1`.`product` AS `product`,`test`.`t1`.`country_id` AS `country_id`,`test`.`t1`.`year` AS `year`,sum(`test`.`t1`.`profit`) AS `sum(profit)` from `test`.`t1` group by `test`.`t1`.`product`,`test`.`t1`.`country_id`,`test`.`t1`.`year` with rollup
select product, country_id , sum(profit) from t1 group by product desc, country_id with rollup;
--- 1.86/mysql-test/r/union.result 2006-08-01 09:24:25 +04:00
+++ 1.87/mysql-test/r/union.result 2006-08-01 09:24:25 +04:00
@@ -691,9 +691,9 @@
`da` datetime DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
drop table t1;
-create table t1 SELECT dt from t2 UNION select sc from t2;
-select * from t1;
-dt
+create table t1 SELECT dt from t2 UNION select trim(sc) from t2;
+select trim(dt) from t1;
+trim(dt)
1972-10-22 11:50:00
testc
show create table t1;
@@ -732,7 +732,7 @@
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
- `dt` longblob
+ `dt` blob
) ENGINE=MyISAM DEFAULT CHARSET=latin1
drop table t1;
create table t1 SELECT sv from t2 UNION select b from t2;
@@ -743,7 +743,7 @@
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
- `sv` longblob
+ `sv` blob
) ENGINE=MyISAM DEFAULT CHARSET=latin1
drop table t1;
create table t1 SELECT i from t2 UNION select d from t2 UNION select b from t2;
@@ -755,7 +755,7 @@
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
- `i` longblob
+ `i` blob
) ENGINE=MyISAM DEFAULT CHARSET=latin1
drop table t1;
create table t1 SELECT sv from t2 UNION select tx from t2;
@@ -766,7 +766,7 @@
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
- `sv` longtext
+ `sv` text
) ENGINE=MyISAM DEFAULT CHARSET=latin1
drop table t1;
create table t1 SELECT b from t2 UNION select tx from t2;
@@ -777,7 +777,7 @@
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
- `b` longblob
+ `b` blob
) ENGINE=MyISAM DEFAULT CHARSET=latin1
drop table t1,t2;
create table t1 select 1 union select -1;
@@ -1306,6 +1306,21 @@
5
99
drop table t1;
+create table t1(f1 char(1), f2 char(5), f3 binary(1), f4 binary(5), f5 timestamp, f6 varchar(1) character set utf8 collate utf8_general_ci, f7 text);
+create table t2 as select *, f6 as f8 from t1 union select *, f7 from t1;
+show create table t2;
+Table Create Table
+t2 CREATE TABLE `t2` (
+ `f1` char(1) DEFAULT NULL,
+ `f2` char(5) DEFAULT NULL,
+ `f3` binary(1) DEFAULT NULL,
+ `f4` binary(5) DEFAULT NULL,
+ `f5` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
+ `f6` varchar(1) CHARACTER SET utf8 DEFAULT NULL,
+ `f7` text,
+ `f8` text CHARACTER SET utf8
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+drop table t1, t2;
(select avg(1)) union (select avg(1)) union (select avg(1)) union
(select avg(1)) union (select avg(1)) union (select avg(1)) union
(select avg(1)) union (select avg(1)) union (select avg(1)) union
--- 1.149/mysql-test/r/subselect.result 2006-08-01 09:24:25 +04:00
+++ 1.150/mysql-test/r/subselect.result 2006-08-01 09:24:25 +04:00
@@ -3,8 +3,8 @@
(select 2)
2
explain extended select (select 2);
-id select_type table type possible_keys key key_len ref rows Extra
-1 PRIMARY NULL NULL NULL NULL NULL NULL NULL No tables used
+id select_type table type possible_keys key key_len ref rows filtered Extra
+1 PRIMARY NULL NULL NULL NULL NULL NULL NULL NULL No tables used
Warnings:
Note 1249 Select 2 was reduced during optimization
Note 1003 select 2 AS `(select 2)`
@@ -13,10 +13,10 @@
1
2
explain extended SELECT (SELECT 1) UNION SELECT (SELECT 2);
-id select_type table type possible_keys key key_len ref rows Extra
-1 PRIMARY NULL NULL NULL NULL NULL NULL NULL No tables used
-3 UNION NULL NULL NULL NULL NULL NULL NULL No tables used
-NULL UNION RESULT <union1,3> ALL NULL NULL NULL NULL NULL
+id select_type table type possible_keys key key_len ref rows filtered Extra
+1 PRIMARY NULL NULL NULL NULL NULL NULL NULL NULL No tables used
+3 UNION NULL NULL NULL NULL NULL NULL NULL NULL No tables used
+NULL UNION RESULT <union1,3> ALL NULL NULL NULL NULL NULL NULL
Warnings:
Note 1249 Select 2 was reduced during optimization
Note 1249 Select 4 was reduced during optimization
@@ -25,11 +25,11 @@
(SELECT (SELECT 0 UNION SELECT 0))
0
explain extended SELECT (SELECT (SELECT 0 UNION SELECT 0));
-id select_type table type possible_keys key key_len ref rows Extra
-1 PRIMARY NULL NULL NULL NULL NULL NULL NULL No tables used
-3 SUBQUERY NULL NULL NULL NULL NULL NULL NULL No tables used
-4 UNION NULL NULL NULL NULL NULL NULL NULL No tables used
-NULL UNION RESULT <union3,4> ALL NULL NULL NULL NULL NULL
+id select_type table type possible_keys key key_len ref rows filtered Extra
+1 PRIMARY NULL NULL NULL NULL NULL NULL NULL NULL No tables used
+3 SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL No tables used
+4 UNION NULL NULL NULL NULL NULL NULL NULL NULL No tables used
+NULL UNION RESULT <union3,4> ALL NULL NULL NULL NULL NULL NULL
Warnings:
Note 1249 Select 2 was reduced during optimization
Note 1003 select (select 0 AS `0` union select 0 AS `0`) AS `(SELECT (SELECT 0 UNION SELECT 0))`
@@ -43,10 +43,10 @@
SELECT (SELECT a) as a;
ERROR 42S22: Reference 'a' not supported (forward reference in item list)
EXPLAIN EXTENDED SELECT 1 FROM (SELECT 1 as a) as b HAVING (SELECT a)=1;
-id select_type table type possible_keys key key_len ref rows Extra
-1 PRIMARY <derived2> system NULL NULL NULL NULL 1
-3 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL No tables used
-2 DERIVED NULL NULL NULL NULL NULL NULL NULL No tables used
+id select_type table type possible_keys key key_len ref rows filtered Extra
+1 PRIMARY <derived2> system NULL NULL NULL NULL 1 100.00
+3 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL No tables used
+2 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used
Warnings:
Note 1276 Field or reference 'a' of SELECT #3 was resolved in SELECT #1
Note 1276 Field or reference 'b.a' of SELECT #3 was resolved in SELECT #1
@@ -181,12 +181,12 @@
4 8
3 8
explain extended (select * from t2 where t2.b=(select a from t3 order by 1 desc limit 1)) union (select * from t4 where t4.b=(select max(t2.a)*4 from t2) order by a);
-id select_type table type possible_keys key key_len ref rows Extra
-1 PRIMARY t2 ALL NULL NULL NULL NULL 2 Using where
-2 SUBQUERY t3 ALL NULL NULL NULL NULL 3 Using filesort
-3 UNION t4 ALL NULL NULL NULL NULL 3 Using where
-4 SUBQUERY t2 ALL NULL NULL NULL NULL 2
-NULL UNION RESULT <union1,3> ALL NULL NULL NULL NULL NULL
+id select_type table type possible_keys key key_len ref rows filtered Extra
+1 PRIMARY t2 ALL NULL NULL NULL NULL 2 100.00 Using where
+2 SUBQUERY t3 ALL NULL NULL NULL NULL 3 100.00 Using filesort
+3 UNION t4 ALL NULL NULL NULL NULL 3 100.00 Using where
+4 SUBQUERY t2 ALL NULL NULL NULL NULL 2 100.00
+NULL UNION RESULT <union1,3> ALL NULL NULL NULL NULL NULL NULL
Warnings:
Note 1003 (select `test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b` from `test`.`t2` where (`test`.`t2`.`b` = (select `test`.`t3`.`a` AS `a` from `test`.`t3` order by 1 desc limit 1))) union (select `test`.`t4`.`a` AS `a`,`test`.`t4`.`b` AS `b` from `test`.`t4` where (`test`.`t4`.`b` = (select (max(`test`.`t2`.`a`) * 4) AS `max(t2.a)*4` from `test`.`t2`)) order by `a`)
select (select a from t3 where a<t2.a*4 order by 1 desc limit 1), a from t2;
@@ -199,10 +199,10 @@
7 2
explain extended select (select t3.a from t3 where a<8 order by 1 desc limit 1), a from
(select * from t2 where a>1) as tt;
-id select_type table type possible_keys key key_len ref rows Extra
-1 PRIMARY <derived3> system NULL NULL NULL NULL 1
-3 DERIVED t2 ALL NULL NULL NULL NULL 2 Using where
-2 SUBQUERY t3 ALL NULL NULL NULL NULL 3 Using where; Using filesort
+id select_type table type possible_keys key key_len ref rows filtered Extra
+1 PRIMARY <derived3> system NULL NULL NULL NULL 1 100.00
+3 DERIVED t2 ALL NULL NULL NULL NULL 2 100.00 Using where
+2 SUBQUERY t3 ALL NULL NULL NULL NULL 3 100.00 Using where; Using filesort
Warnings:
Note 1003 select (select `test`.`t3`.`a` AS `a` from `test`.`t3` where (`test`.`t3`.`a` < 8) order by 1 desc limit 1) AS `(select t3.a from t3 where a<8 order by 1 desc limit 1)`,`tt`.`a` AS `a` from (select `test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b` from `test`.`t2` where (`test`.`t2`.`a` > 1)) `tt`
select * from t1 where t1.a=(select t2.a from t2 where t2.b=(select max(a) from t3) order by 1 desc limit 1);
@@ -219,10 +219,10 @@
8 4.5000
9 7.5000
explain extended select b,(select avg(t2.a+(select min(t3.a) from t3 where t3.a >= t4.a)) from t2) from t4;
-id select_type table type possible_keys key key_len ref rows Extra
-1 PRIMARY t4 ALL NULL NULL NULL NULL 3
-2 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 2
-3 DEPENDENT SUBQUERY t3 ALL NULL NULL NULL NULL 3 Using where
+id select_type table type possible_keys key key_len ref rows filtered Extra
+1 PRIMARY t4 ALL NULL NULL NULL NULL 3 100.00
+2 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 2 100.00
+3 DEPENDENT SUBQUERY t3 ALL NULL NULL NULL NULL 3 100.00 Using where
Warnings:
Note 1276 Field or reference 't4.a' of SELECT #3 was resolved in SELECT #1
Note 1003 select `test`.`t4`.`b` AS `b`,(select avg((`test`.`t2`.`a` + (select min(`test`.`t3`.`a`) AS `min(t3.a)` from `test`.`t3` where (`test`.`t3`.`a` >= `test`.`t4`.`a`)))) AS `avg(t2.a+(select min(t3.a) from t3 where t3.a >= t4.a))` from `test`.`t2`) AS `(select avg(t2.a+(select min(t3.a) from t3 where t3.a >= t4.a)) from t2)` from `test`.`t4`
@@ -267,9 +267,9 @@
6
7
explain extended select * from t3 where a >= any (select b from t2);
-id select_type table type possible_keys key key_len ref rows Extra
-1 PRIMARY t3 ALL NULL NULL NULL NULL 3 Using where
-2 SUBQUERY t2 ALL NULL NULL NULL NULL 3
+id select_type table type possible_keys key key_len ref rows filtered Extra
+1 PRIMARY t3 ALL NULL NULL NULL NULL 3 100.00 Using where
+2 SUBQUERY t2 ALL NULL NULL NULL NULL 3 100.00
Warnings:
Note 1003 select `test`.`t3`.`a` AS `a` from `test`.`t3` where <nop>((`test`.`t3`.`a` >= (select min(`test`.`t2`.`b`) from `test`.`t2`)))
select * from t3 where a >= all (select b from t2);
@@ -307,11 +307,11 @@
NULL 1
2 2
explain extended select (select a from t1 where t1.a=t2.a union select a from t5 where t5.a=t2.a), a from t2;
-id select_type table type possible_keys key key_len ref rows Extra
-1 PRIMARY t2 ALL NULL NULL NULL NULL 2
-2 DEPENDENT SUBQUERY t1 system NULL NULL NULL NULL 1
-3 DEPENDENT UNION t5 ALL NULL NULL NULL NULL 2 Using where
-NULL UNION RESULT <union2,3> ALL NULL NULL NULL NULL NULL
+id select_type table type possible_keys key key_len ref rows filtered Extra
+1 PRIMARY t2 ALL NULL NULL NULL NULL 2 100.00
+2 DEPENDENT SUBQUERY t1 system NULL NULL NULL NULL 1 100.00
+3 DEPENDENT UNION t5 ALL NULL NULL NULL NULL 2 100.00 Using where
+NULL UNION RESULT <union2,3> ALL NULL NULL NULL NULL NULL NULL
Warnings:
Note 1276 Field or reference 't2.a' of SELECT #2 was resolved in SELECT #1
Note 1276 Field or reference 't2.a' of SELECT #3 was resolved in SELECT #1
@@ -328,9 +328,9 @@
1 2
2 2
explain extended select * from t6 where exists (select * from t7 where uq = clinic_uq);
-id select_type table type possible_keys key key_len ref rows Extra
-1 PRIMARY t6 ALL NULL NULL NULL NULL 4 Using where
-2 DEPENDENT SUBQUERY t7 eq_ref PRIMARY PRIMARY 4 test.t6.clinic_uq 1 Using index
+id select_type table type possible_keys key key_len ref rows filtered Extra
+1 PRIMARY t6 ALL NULL NULL NULL NULL 4 100.00 Using where
+2 DEPENDENT SUBQUERY t7 eq_ref PRIMARY PRIMARY 4 test.t6.clinic_uq 1 100.00 Using index
Warnings:
Note 1276 Field or reference 'clinic_uq' of SELECT #2 was resolved in SELECT #1
Note 1003 select `test`.`t6`.`patient_uq` AS `patient_uq`,`test`.`t6`.`clinic_uq` AS `clinic_uq` from `test`.`t6` where exists(select 1 AS `Not_used` from `test`.`t7` where (`test`.`t7`.`uq` = `test`.`t6`.`clinic_uq`))
@@ -362,11 +362,11 @@
INSERT INTO t8 (pseudo,email) VALUES ('joce1','test1');
INSERT INTO t8 (pseudo,email) VALUES ('2joce1','2test1');
EXPLAIN EXTENDED SELECT pseudo,(SELECT email FROM t8 WHERE pseudo=(SELECT pseudo FROM t8 WHERE pseudo='joce')) FROM t8 WHERE pseudo=(SELECT pseudo FROM t8 WHERE pseudo='joce');
-id select_type table type possible_keys key key_len ref rows Extra
-1 PRIMARY t8 ref PRIMARY PRIMARY 37 const 1 Using where; Using index
-4 SUBQUERY t8 ref PRIMARY PRIMARY 37 1 Using where; Using index
-2 SUBQUERY t8 ref PRIMARY PRIMARY 37 const 1 Using where
-3 SUBQUERY t8 ref PRIMARY PRIMARY 37 1 Using where; Using index
+id select_type table type possible_keys key key_len ref rows filtered Extra
+1 PRIMARY t8 ref PRIMARY PRIMARY 37 const 1 100.00 Using where; Using index
+4 SUBQUERY t8 ref PRIMARY PRIMARY 37 1 100.00 Using where; Using index
+2 SUBQUERY t8 ref PRIMARY PRIMARY 37 const 1 100.00 Using where
+3 SUBQUERY t8 ref PRIMARY PRIMARY 37 1 100.00 Using where; Using index
Warnings:
Note 1003 select `test`.`t8`.`pseudo` AS `pseudo`,(select `test`.`t8`.`email` AS `email` from `test`.`t8` where (`test`.`t8`.`pseudo` = (select `test`.`t8`.`pseudo` AS `pseudo` from `test`.`t8` where (`test`.`t8`.`pseudo` = _latin1'joce')))) AS `(SELECT email FROM t8 WHERE pseudo=(SELECT pseudo FROM t8 WHERE pseudo='joce'))` from `test`.`t8` where (`test`.`t8`.`pseudo` = (select `test`.`t8`.`pseudo` AS `pseudo` from `test`.`t8` where (`test`.`t8`.`pseudo` = _latin1'joce')))
SELECT pseudo FROM t8 WHERE pseudo=(SELECT pseudo,email FROM
@@ -391,14 +391,14 @@
INSERT INTO t1 (topic,date,pseudo) VALUES
('43506','2002-10-02','joce'),('40143','2002-08-03','joce');
EXPLAIN EXTENDED SELECT DISTINCT date FROM t1 WHERE date='2002-08-03';
-id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 index NULL PRIMARY 43 NULL 2 Using where; Using index
+id select_type table type possible_keys key key_len ref rows filtered Extra
+1 SIMPLE t1 index NULL PRIMARY 43 NULL 2 100.00 Using where; Using index
Warnings:
Note 1003 select distinct `test`.`t1`.`date` AS `date` from `test`.`t1` where (`test`.`t1`.`date` = 20020803)
EXPLAIN EXTENDED SELECT (SELECT DISTINCT date FROM t1 WHERE date='2002-08-03');
-id select_type table type possible_keys key key_len ref rows Extra
-1 PRIMARY NULL NULL NULL NULL NULL NULL NULL No tables used
-2 SUBQUERY t1 index NULL PRIMARY 43 NULL 2 Using where; Using index
+id select_type table type possible_keys key key_len ref rows filtered Extra
+1 PRIMARY NULL NULL NULL NULL NULL NULL NULL NULL No tables used
+2 SUBQUERY t1 index NULL PRIMARY 43 NULL 2 100.00 Using where; Using index
Warnings:
Note 1003 select (select distinct `test`.`t1`.`date` AS `date` from `test`.`t1` where (`test`.`t1`.`date` = 20020803)) AS `(SELECT DISTINCT date FROM t1 WHERE date='2002-08-03')`
SELECT DISTINCT date FROM t1 WHERE date='2002-08-03';
@@ -415,11 +415,11 @@
SELECT 1 FROM t1 WHERE 1=(SELECT 1 UNION ALL SELECT 1) UNION SELECT 1;
ERROR 21000: Subquery returns more than 1 row
EXPLAIN EXTENDED SELECT 1 FROM t1 WHERE 1=(SELECT 1 UNION SELECT 1);
-id select_type table type possible_keys key key_len ref rows Extra
-1 PRIMARY t1 index NULL topic 3 NULL 2 Using index
-2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL No tables used
-3 UNION NULL NULL NULL NULL NULL NULL NULL No tables used
-NULL UNION RESULT <union2,3> ALL NULL NULL NULL NULL NULL
+id select_type table type possible_keys key key_len ref rows filtered Extra
+1 PRIMARY t1 index NULL topic 3 NULL 2 100.00 Using index
+2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL No tables used
+3 UNION NULL NULL NULL NULL NULL NULL NULL NULL No tables used
+NULL UNION RESULT <union2,3> ALL NULL NULL NULL NULL NULL NULL
Warnings:
Note 1003 select 1 AS `1` from `test`.`t1`
drop table t1;
@@ -538,14 +538,14 @@
EXPLAIN EXTENDED SELECT numreponse FROM t1 WHERE numeropost='1' AND numreponse=(SELECT 1 FROM t1 WHERE numeropost='1');
ERROR 21000: Subquery returns more than 1 row
EXPLAIN EXTENDED SELECT MAX(numreponse) FROM t1 WHERE numeropost='1';
-id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Select tables optimized away
+id select_type table type possible_keys key key_len ref rows filtered Extra
+1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL Select tables optimized away
Warnings:
Note 1003 select max(`test`.`t1`.`numreponse`) AS `MAX(numreponse)` from `test`.`t1` where (`test`.`t1`.`numeropost` = _latin1'1')
EXPLAIN EXTENDED SELECT numreponse FROM t1 WHERE numeropost='1' AND numreponse=(SELECT MAX(numreponse) FROM t1 WHERE numeropost='1');
-id select_type table type possible_keys key key_len ref rows Extra
-1 PRIMARY t1 const PRIMARY,numreponse PRIMARY 7 const,const 1 Using index
-2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL Select tables optimized away
+id select_type table type possible_keys key key_len ref rows filtered Extra
+1 PRIMARY t1 const PRIMARY,numreponse PRIMARY 7 const,const 1 100.00 Using index
+2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL Select tables optimized away
Warnings:
Note 1003 select `test`.`t1`.`numreponse` AS `numreponse` from `test`.`t1` where ((`test`.`t1`.`numeropost` = _latin1'1'))
drop table t1;
@@ -719,8 +719,8 @@
id
1
EXPLAIN EXTENDED SELECT * FROM t2 WHERE id IN (SELECT 1);
-id select_type table type possible_keys key key_len ref rows Extra
-1 PRIMARY t2 ref id id 5 const 1 Using where; Using index
+id select_type table type possible_keys key key_len ref rows filtered Extra
+1 PRIMARY t2 ref id id 5 const 1 100.00 Using where; Using index
Warnings:
Note 1249 Select 2 was reduced during optimization
Note 1003 select `test`.`t2`.`id` AS `id` from `test`.`t2` where (`test`.`t2`.`id` = 1)
@@ -731,18 +731,18 @@
id
2
EXPLAIN EXTENDED SELECT * FROM t2 WHERE id IN (SELECT 1+(select 1));
-id select_type table type possible_keys key key_len ref rows Extra
-1 PRIMARY t2 ref id id 5 const 1 Using where; Using index
+id select_type table type possible_keys key key_len ref rows filtered Extra
+1 PRIMARY t2 ref id id 5 const 1 100.00 Using where; Using index
Warnings:
Note 1249 Select 3 was reduced during optimization
Note 1249 Select 2 was reduced during optimization
Note 1003 select `test`.`t2`.`id` AS `id` from `test`.`t2` where (`test`.`t2`.`id` = (1 + 1))
EXPLAIN EXTENDED SELECT * FROM t2 WHERE id IN (SELECT 1 UNION SELECT 3);
-id select_type table type possible_keys key key_len ref rows Extra
-1 PRIMARY t2 index NULL id 5 NULL 2 Using where; Using index
-2 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL No tables used
-3 DEPENDENT UNION NULL NULL NULL NULL NULL NULL NULL No tables used
-NULL UNION RESULT <union2,3> ALL NULL NULL NULL NULL NULL
+id select_type table type possible_keys key key_len ref rows filtered Extra
+1 PRIMARY t2 index NULL id 5 NULL 2 100.00 Using where; Using index
+2 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL No tables used
+3 DEPENDENT UNION NULL NULL NULL NULL NULL NULL NULL NULL No tables used
+NULL UNION RESULT <union2,3> ALL NULL NULL NULL NULL NULL NULL
Warnings:
Note 1003 select `test`.`t2`.`id` AS `id` from `test`.`t2` where <in_optimizer>(`test`.`t2`.`id`,<exists>(select 1 AS `1` having (<cache>(`test`.`t2`.`id`) = <ref_null_helper>(1)) union select 3 AS `3` having (<cache>(`test`.`t2`.`id`) = <ref_null_helper>(3))))
SELECT * FROM t2 WHERE id IN (SELECT 5 UNION SELECT 3);
@@ -865,8 +865,8 @@
10.5 > ANY (SELECT * from t1)
1
explain extended select (select a+1) from t1;
-id select_type table type possible_keys key key_len ref rows Extra
-1 PRIMARY t1 ALL NULL NULL NULL NULL 3
+id select_type table type possible_keys key key_len ref rows filtered Extra
+1 PRIMARY t1 ALL NULL NULL NULL NULL 3 100.00
Warnings:
Note 1276 Field or reference 'a' of SELECT #2 was resolved in SELECT #1
Note 1249 Select 2 was reduced during optimization
@@ -888,9 +888,9 @@
3 1
4 0
explain extended SELECT t1.a, t1.a in (select t2.a from t2) FROM t1;
-id select_type table type possible_keys key key_len ref rows Extra
-1 PRIMARY t1 index NULL PRIMARY 4 NULL 4 Using index
-2 DEPENDENT SUBQUERY t2 index_subquery a a 5 func 2 Using index
+id select_type table type possible_keys key key_len ref rows filtered Extra
+1 PRIMARY t1 index NULL PRIMARY 4 NULL 4 100.00 Using index
+2 DEPENDENT SUBQUERY t2 index_subquery a a 5 func 2 100.00 Using index
Warnings:
Note 1003 select `test`.`t1`.`a` AS `a`,<in_optimizer>(`test`.`t1`.`a`,<exists>(<index_lookup>(<cache>(`test`.`t1`.`a`) in t2 on a checking NULL))) AS `t1.a in (select t2.a from t2)` from `test`.`t1`
CREATE TABLE t3 (a int(11) default '0');
@@ -902,10 +902,10 @@
3 1
4 0
explain extended SELECT t1.a, t1.a in (select t2.a from t2,t3 where t3.a=t2.a) FROM t1;
-id select_type table type possible_keys key key_len ref rows Extra
-1 PRIMARY t1 index NULL PRIMARY 4 NULL 4 Using index
-2 DEPENDENT SUBQUERY t2 ref_or_null a a 5 func 2 Using where; Using index
-2 DEPENDENT SUBQUERY t3 ALL NULL NULL NULL NULL 3 Using where
+id select_type table type possible_keys key key_len ref rows filtered Extra
+1 PRIMARY t1 index NULL PRIMARY 4 NULL 4 100.00 Using index
+2 DEPENDENT SUBQUERY t2 ref_or_null a a 5 func 2 100.00 Using where; Using index
+2 DEPENDENT SUBQUERY t3 ALL NULL NULL NULL NULL 3 100.00 Using where
Warnings:
Note 1003 select `test`.`t1`.`a` AS `a`,<in_optimizer>(`test`.`t1`.`a`,<exists>(select 1 AS `Not_used` from `test`.`t2` join `test`.`t3` where ((`test`.`t3`.`a` = `test`.`t2`.`a`) and ((<cache>(`test`.`t1`.`a`) = `test`.`t2`.`a`) or isnull(`test`.`t2`.`a`))) having <is_not_null_test>(`test`.`t2`.`a`))) AS `t1.a in (select t2.a from t2,t3 where t3.a=t2.a)` from `test`.`t1`
drop table t1,t2,t3;
@@ -1015,21 +1015,21 @@
drop table t1;
CREATE TABLE t1 (a int(1));
EXPLAIN EXTENDED SELECT (SELECT RAND() FROM t1) FROM t1;
-id select_type table type possible_keys key key_len ref rows Extra
-1 PRIMARY t1 system NULL NULL NULL NULL 0 const row not found
-2 UNCACHEABLE SUBQUERY t1 system NULL NULL NULL NULL 0 const row not found
+id select_type table type possible_keys key key_len ref rows filtered Extra
+1 PRIMARY t1 system NULL NULL NULL NULL 0 0.00 const row not found
+2 UNCACHEABLE SUBQUERY t1 system NULL NULL NULL NULL 0 0.00 const row not found
Warnings:
Note 1003 select (select rand() AS `RAND()` from `test`.`t1`) AS `(SELECT RAND() FROM t1)` from `test`.`t1`
EXPLAIN EXTENDED SELECT (SELECT ENCRYPT('test') FROM t1) FROM t1;
-id select_type table type possible_keys key key_len ref rows Extra
-1 PRIMARY t1 system NULL NULL NULL NULL 0 const row not found
-2 UNCACHEABLE SUBQUERY t1 system NULL NULL NULL NULL 0 const row not found
+id select_type table type possible_keys key key_len ref rows filtered Extra
+1 PRIMARY t1 system NULL NULL NULL NULL 0 0.00 const row not found
+2 UNCACHEABLE SUBQUERY t1 system NULL NULL NULL NULL 0 0.00 const row not found
Warnings:
Note 1003 select (select encrypt(_latin1'test') AS `ENCRYPT('test')` from `test`.`t1`) AS `(SELECT ENCRYPT('test') FROM t1)` from `test`.`t1`
EXPLAIN EXTENDED SELECT (SELECT BENCHMARK(1,1) FROM t1) FROM t1;
-id select_type table type possible_keys key key_len ref rows Extra
-1 PRIMARY t1 system NULL NULL NULL NULL 0 const row not found
-2 UNCACHEABLE SUBQUERY t1 system NULL NULL NULL NULL 0 const row not found
+id select_type table type possible_keys key key_len ref rows filtered Extra
+1 PRIMARY t1 system NULL NULL NULL NULL 0 0.00 const row not found
+2 UNCACHEABLE SUBQUERY t1 system NULL NULL NULL NULL 0 0.00 const row not found
Warnings:
Note 1003 select (select benchmark(1,1) AS `BENCHMARK(1,1)` from `test`.`t1`) AS `(SELECT BENCHMARK(1,1) FROM t1)` from `test`.`t1`
drop table t1;
@@ -1121,10 +1121,10 @@
insert into t1 values (1), (2), (3);
explain extended select a,(select (select rand() from t1 limit 1) from t1 limit 1)
from t1;
-id select_type table type possible_keys key key_len ref rows Extra
-1 PRIMARY t1 ALL NULL NULL NULL NULL 3
-2 UNCACHEABLE SUBQUERY t1 ALL NULL NULL NULL NULL 3
-3 UNCACHEABLE SUBQUERY t1 ALL NULL NULL NULL NULL 3
+id select_type table type possible_keys key key_len ref rows filtered Extra
+1 PRIMARY t1 ALL NULL NULL NULL NULL 3 100.00
+2 UNCACHEABLE SUBQUERY t1 ALL NULL NULL NULL NULL 3 100.00
+3 UNCACHEABLE SUBQUERY t1 ALL NULL NULL NULL NULL 3 100.00
Warnings:
Note 1003 select `test`.`t1`.`a` AS `a`,(select (select rand() AS `rand()` from `test`.`t1` limit 1) AS `(select rand() from t1 limit 1)` from `test`.`t1` limit 1) AS `(select (select rand() from t1 limit 1) from t1 limit 1)` from `test`.`t1`
drop table t1;
@@ -1176,9 +1176,9 @@
0 IN (SELECT 1 FROM t1 a)
0
EXPLAIN EXTENDED SELECT 0 IN (SELECT 1 FROM t1 a);
-id select_type table type possible_keys key key_len ref rows Extra
-1 PRIMARY NULL NULL NULL NULL NULL NULL NULL No tables used
-2 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE
+id select_type table type possible_keys key key_len ref rows filtered Extra
+1 PRIMARY NULL NULL NULL NULL NULL NULL NULL NULL No tables used
+2 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE
Warnings:
Note 1003 select <in_optimizer>(0,<exists>(select 1 AS `Not_used` from `test`.`t1` `a`)) AS `0 IN (SELECT 1 FROM t1 a)`
INSERT INTO t1 (pseudo) VALUES ('test1');
@@ -1186,9 +1186,9 @@
0 IN (SELECT 1 FROM t1 a)
0
EXPLAIN EXTENDED SELECT 0 IN (SELECT 1 FROM t1 a);
-id select_type table type possible_keys key key_len ref rows Extra
-1 PRIMARY NULL NULL NULL NULL NULL NULL NULL No tables used
-2 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE
+id select_type table type possible_keys key key_len ref rows filtered Extra
+1 PRIMARY NULL NULL NULL NULL NULL NULL NULL NULL No tables used
+2 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE
Warnings:
Note 1003 select <in_optimizer>(0,<exists>(select 1 AS `Not_used` from `test`.`t1` `a`)) AS `0 IN (SELECT 1 FROM t1 a)`
drop table t1;
@@ -1231,9 +1231,9 @@
create table t1 (id int not null auto_increment primary key, salary int, key(salary));
insert into t1 (salary) values (100),(1000),(10000),(10),(500),(5000),(50000);
explain extended SELECT id FROM t1 where salary = (SELECT MAX(salary) FROM t1);
-id select_type table type possible_keys key key_len ref rows Extra
-1 PRIMARY t1 ref salary salary 5 const 1 Using where
-2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL Select tables optimized away
+id select_type table type possible_keys key key_len ref rows filtered Extra
+1 PRIMARY t1 ref salary salary 5 const 1 100.00 Using where
+2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL Select tables optimized away
Warnings:
Note 1003 select `test`.`t1`.`id` AS `id` from `test`.`t1` where (`test`.`t1`.`salary` = (select max(`test`.`t1`.`salary`) AS `MAX(salary)` from `test`.`t1`))
drop table t1;
@@ -1293,9 +1293,9 @@
3
4
explain extended select * from t2 where t2.a in (select a from t1);
-id select_type table type possible_keys key key_len ref rows Extra
-1 PRIMARY t2 index NULL PRIMARY 4 NULL 4 Using where; Using index
-2 DEPENDENT SUBQUERY t1 unique_subquery PRIMARY PRIMARY 4 func 1 Using index
+id select_type table type possible_keys key key_len ref rows filtered Extra
+1 PRIMARY t2 index NULL PRIMARY 4 NULL 4 100.00 Using where; Using index
+2 DEPENDENT SUBQUERY t1 unique_subquery PRIMARY PRIMARY 4 func 1 100.00 Using index
Warnings:
Note 1003 select `test`.`t2`.`a` AS `a` from `test`.`t2` where <in_optimizer>(`test`.`t2`.`a`,<exists>(<primary_index_lookup>(<cache>(`test`.`t2`.`a`) in t1 on PRIMARY)))
select * from t2 where t2.a in (select a from t1 where t1.b <> 30);
@@ -1303,9 +1303,9 @@
2
4
explain extended select * from t2 where t2.a in (select a from t1 where t1.b <> 30);
-id select_type table type possible_keys key key_len ref rows Extra
-1 PRIMARY t2 index NULL PRIMARY 4 NULL 4 Using where; Using index
-2 DEPENDENT SUBQUERY t1 unique_subquery PRIMARY PRIMARY 4 func 1 Using index; Using where
+id select_type table type possible_keys key key_len ref rows filtered Extra
+1 PRIMARY t2 index NULL PRIMARY 4 NULL 4 100.00 Using where; Using index
+2 DEPENDENT SUBQUERY t1 unique_subquery PRIMARY PRIMARY 4 func 1 100.00 Using index; Using where
Warnings:
Note 1003 select `test`.`t2`.`a` AS `a` from `test`.`t2` where <in_optimizer>(`test`.`t2`.`a`,<exists>(<primary_index_lookup>(<cache>(`test`.`t2`.`a`) in t1 on PRIMARY where (`test`.`t1`.`b` <> 30))))
select * from t2 where t2.a in (select t1.a from t1,t3 where t1.b=t3.a);
@@ -1313,10 +1313,10 @@
2
3
explain extended select * from t2 where t2.a in (select t1.a from t1,t3 where t1.b=t3.a);
-id select_type table type possible_keys key key_len ref rows Extra
-1 PRIMARY t2 index NULL PRIMARY 4 NULL 4 Using where; Using index
-2 DEPENDENT SUBQUERY t1 eq_ref PRIMARY PRIMARY 4 func 1 Using where
-2 DEPENDENT SUBQUERY t3 eq_ref PRIMARY PRIMARY 4 test.t1.b 1 Using where; Using index
+id select_type table type possible_keys key key_len ref rows filtered Extra
+1 PRIMARY t2 index NULL PRIMARY 4 NULL 4 100.00 Using where; Using index
+2 DEPENDENT SUBQUERY t1 eq_ref PRIMARY PRIMARY 4 func 1 100.00 Using where
+2 DEPENDENT SUBQUERY t3 eq_ref PRIMARY PRIMARY 4 test.t1.b 1 100.00 Using where; Using index
Warnings:
Note 1003 select `test`.`t2`.`a` AS `a` from `test`.`t2` where <in_optimizer>(`test`.`t2`.`a`,<exists>(select 1 AS `Not_used` from `test`.`t1` join `test`.`t3` where ((`test`.`t3`.`a` = `test`.`t1`.`b`) and (<cache>(`test`.`t2`.`a`) = `test`.`t1`.`a`))))
drop table t1, t2, t3;
@@ -1332,9 +1332,9 @@
3
4
explain extended select * from t2 where t2.a in (select a from t1);
-id select_type table type possible_keys key key_len ref rows Extra
-1 PRIMARY t2 index NULL a 5 NULL 4 Using where; Using index
-2 DEPENDENT SUBQUERY t1 index_subquery a a 5 func 1001 Using index
+id select_type table type possible_keys key key_len ref rows filtered Extra
+1 PRIMARY t2 index NULL a 5 NULL 4 100.00 Using where; Using index
+2 DEPENDENT SUBQUERY t1 index_subquery a a 5 func 1001 100.00 Using index
Warnings:
Note 1003 select `test`.`t2`.`a` AS `a` from `test`.`t2` where <in_optimizer>(`test`.`t2`.`a`,<exists>(<index_lookup>(<cache>(`test`.`t2`.`a`) in t1 on a)))
select * from t2 where t2.a in (select a from t1 where t1.b <> 30);
@@ -1342,9 +1342,9 @@
2
4
explain extended select * from t2 where t2.a in (select a from t1 where t1.b <> 30);
-id select_type table type possible_keys key key_len ref rows Extra
-1 PRIMARY t2 index NULL a 5 NULL 4 Using where; Using index
-2 DEPENDENT SUBQUERY t1 index_subquery a a 5 func 1001 Using index; Using where
+id select_type table type possible_keys key key_len ref rows filtered Extra
+1 PRIMARY t2 index NULL a 5 NULL 4 100.00 Using where; Using index
+2 DEPENDENT SUBQUERY t1 index_subquery a a 5 func 1001 100.00 Using index; Using where
Warnings:
Note 1003 select `test`.`t2`.`a` AS `a` from `test`.`t2` where <in_optimizer>(`test`.`t2`.`a`,<exists>(<index_lookup>(<cache>(`test`.`t2`.`a`) in t1 on a where (`test`.`t1`.`b` <> 30))))
select * from t2 where t2.a in (select t1.a from t1,t3 where t1.b=t3.a);
@@ -1352,10 +1352,10 @@
2
3
explain extended select * from t2 where t2.a in (select t1.a from t1,t3 where t1.b=t3.a);
-id select_type table type possible_keys key key_len ref rows Extra
-1 PRIMARY t2 index NULL a 5 NULL 4 Using where; Using index
-2 DEPENDENT SUBQUERY t1 ref a a 5 func 1001 Using where; Using index
-2 DEPENDENT SUBQUERY t3 index a a 5 NULL 3 Using where; Using index
+id select_type table type possible_keys key key_len ref rows filtered Extra
+1 PRIMARY t2 index NULL a 5 NULL 4 100.00 Using where; Using index
+2 DEPENDENT SUBQUERY t1 ref a a 5 func 1001 100.00 Using where; Using index
+2 DEPENDENT SUBQUERY t3 index a a 5 NULL 3 100.00 Using where; Using index
Warnings:
Note 1003 select `test`.`t2`.`a` AS `a` from `test`.`t2` where <in_optimizer>(`test`.`t2`.`a`,<exists>(select 1 AS `Not_used` from `test`.`t1` join `test`.`t3` where ((`test`.`t3`.`a` = `test`.`t1`.`b`) and (<cache>(`test`.`t2`.`a`) = `test`.`t1`.`a`))))
insert into t1 values (3,31);
@@ -1369,9 +1369,9 @@
2
4
explain extended select * from t2 where t2.a in (select a from t1 where t1.b <> 30);
-id select_type table type possible_keys key key_len ref rows Extra
-1 PRIMARY t2 index NULL a 5 NULL 4 Using where; Using index
-2 DEPENDENT SUBQUERY t1 index_subquery a a 5 func 1001 Using index; Using where
+id select_type table type possible_keys key key_len ref rows filtered Extra
+1 PRIMARY t2 index NULL a 5 NULL 4 100.00 Using where; Using index
+2 DEPENDENT SUBQUERY t1 index_subquery a a 5 func 1001 100.00 Using index; Using where
Warnings:
Note 1003 select `test`.`t2`.`a` AS `a` from `test`.`t2` where <in_optimizer>(`test`.`t2`.`a`,<exists>(<index_lookup>(<cache>(`test`.`t2`.`a`) in t1 on a where (`test`.`t1`.`b` <> 30))))
drop table t1, t2, t3;
@@ -1427,8 +1427,8 @@
s1
tttt
explain extended (select * from t1);
-id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 system NULL NULL NULL NULL 1
+id select_type table type possible_keys key key_len ref rows filtered Extra
+1 SIMPLE t1 system NULL NULL NULL NULL 1 100.00
Warnings:
Note 1003 (select `test`.`t1`.`s1` AS `s1` from `test`.`t1`)
(select * from t1);
@@ -1460,27 +1460,27 @@
a2 1
a3 1
explain extended select s1, s1 NOT IN (SELECT s1 FROM t2) from t1;
-id select_type table type possible_keys key key_len ref rows Extra
-1 PRIMARY t1 index NULL s1 6 NULL 3 Using index
-2 DEPENDENT SUBQUERY t2 index_subquery s1 s1 6 func 2 Using index
+id select_type table type possible_keys key key_len ref rows filtered Extra
+1 PRIMARY t1 index NULL s1 6 NULL 3 100.00 Using index
+2 DEPENDENT SUBQUERY t2 index_subquery s1 s1 6 func 2 100.00 Using index
Warnings:
Note 1003 select `test`.`t1`.`s1` AS `s1`,not(<in_optimizer>(`test`.`t1`.`s1`,<exists>(<index_lookup>(<cache>(`test`.`t1`.`s1`) in t2 on s1 checking NULL)))) AS `s1 NOT IN (SELECT s1 FROM t2)` from `test`.`t1`
explain extended select s1, s1 = ANY (SELECT s1 FROM t2) from t1;
-id select_type table type possible_keys key key_len ref rows Extra
-1 PRIMARY t1 index NULL s1 6 NULL 3 Using index
-2 DEPENDENT SUBQUERY t2 index_subquery s1 s1 6 func 2 Using index
+id select_type table type possible_keys key key_len ref rows filtered Extra
+1 PRIMARY t1 index NULL s1 6 NULL 3 100.00 Using index
+2 DEPENDENT SUBQUERY t2 index_subquery s1 s1 6 func 2 100.00 Using index
Warnings:
Note 1003 select `test`.`t1`.`s1` AS `s1`,<in_optimizer>(`test`.`t1`.`s1`,<exists>(<index_lookup>(<cache>(`test`.`t1`.`s1`) in t2 on s1 checking NULL))) AS `s1 = ANY (SELECT s1 FROM t2)` from `test`.`t1`
explain extended select s1, s1 <> ALL (SELECT s1 FROM t2) from t1;
-id select_type table type possible_keys key key_len ref rows Extra
-1 PRIMARY t1 index NULL s1 6 NULL 3 Using index
-2 DEPENDENT SUBQUERY t2 index_subquery s1 s1 6 func 2 Using index
+id select_type table type possible_keys key key_len ref rows filtered Extra
+1 PRIMARY t1 index NULL s1 6 NULL 3 100.00 Using index
+2 DEPENDENT SUBQUERY t2 index_subquery s1 s1 6 func 2 100.00 Using index
Warnings:
Note 1003 select `test`.`t1`.`s1` AS `s1`,not(<in_optimizer>(`test`.`t1`.`s1`,<exists>(<index_lookup>(<cache>(`test`.`t1`.`s1`) in t2 on s1 checking NULL)))) AS `s1 <> ALL (SELECT s1 FROM t2)` from `test`.`t1`
explain extended select s1, s1 NOT IN (SELECT s1 FROM t2 WHERE s1 < 'a2') from t1;
-id select_type table type possible_keys key key_len ref rows Extra
-1 PRIMARY t1 index NULL s1 6 NULL 3 Using index
-2 DEPENDENT SUBQUERY t2 index_subquery s1 s1 6 func 2 Using index; Using where
+id select_type table type possible_keys key key_len ref rows filtered Extra
+1 PRIMARY t1 index NULL s1 6 NULL 3 100.00 Using index
+2 DEPENDENT SUBQUERY t2 index_subquery s1 s1 6 func 2 100.00 Using index; Using where
Warnings:
Note 1003 select `test`.`t1`.`s1` AS `s1`,not(<in_optimizer>(`test`.`t1`.`s1`,<exists>(<index_lookup>(<cache>(`test`.`t1`.`s1`) in t2 on s1 checking NULL where (`test`.`t2`.`s1` < _latin1'a2'))))) AS `s1 NOT IN (SELECT s1 FROM t2 WHERE s1 < 'a2')` from `test`.`t1`
drop table t1,t2;
@@ -1493,17 +1493,17 @@
7
3
explain extended select * from t3 where a >= all (select b from t2);
-id select_type table type possible_keys key key_len ref rows Extra
-1 PRIMARY t3 ALL NULL NULL NULL NULL 3 Using where
-2 SUBQUERY t2 system NULL NULL NULL NULL 0 const row not found
+id select_type table type possible_keys key key_len ref rows filtered Extra
+1 PRIMARY t3 ALL NULL NULL NULL NULL 3 100.00 Using where
+2 SUBQUERY t2 system NULL NULL NULL NULL 0 0.00 const row not found
Warnings:
Note 1003 select `test`.`t3`.`a` AS `a` from `test`.`t3` where <not>((`test`.`t3`.`a` < (select max(`test`.`t2`.`b`) from `test`.`t2`)))
select * from t3 where a >= some (select b from t2);
a
explain extended select * from t3 where a >= some (select b from t2);
-id select_type table type possible_keys key key_len ref rows Extra
-1 PRIMARY t3 ALL NULL NULL NULL NULL 3 Using where
-2 SUBQUERY t2 system NULL NULL NULL NULL 0 const row not found
+id select_type table type possible_keys key key_len ref rows filtered Extra
+1 PRIMARY t3 ALL NULL NULL NULL NULL 3 100.00 Using where
+2 SUBQUERY t2 system NULL NULL NULL NULL 0 0.00 const row not found
Warnings:
Note 1003 select `test`.`t3`.`a` AS `a` from `test`.`t3` where <nop>((`test`.`t3`.`a` >= (select min(`test`.`t2`.`b`) from `test`.`t2`)))
select * from t3 where a >= all (select b from t2 group by 1);
@@ -1512,49 +1512,49 @@
7
3
explain extended select * from t3 where a >= all (select b from t2 group by 1);
-id select_type table type possible_keys key key_len ref rows Extra
-1 PRIMARY t3 ALL NULL NULL NULL NULL 3 Using where
-2 SUBQUERY t2 system NULL NULL NULL NULL 0 const row not found
+id select_type table type possible_keys key key_len ref rows filtered Extra
+1 PRIMARY t3 ALL NULL NULL NULL NULL 3 100.00 Using where
+2 SUBQUERY t2 system NULL NULL NULL NULL 0 0.00 const row not found
Warnings:
Note 1003 select `test`.`t3`.`a` AS `a` from `test`.`t3` where <not>((`test`.`t3`.`a` < <max>(select `test`.`t2`.`b` AS `b` from `test`.`t2` group by 1)))
select * from t3 where a >= some (select b from t2 group by 1);
a
explain extended select * from t3 where a >= some (select b from t2 group by 1);
-id select_type table type possible_keys key key_len ref rows Extra
-1 PRIMARY t3 ALL NULL NULL NULL NULL 3 Using where
-2 SUBQUERY t2 system NULL NULL NULL NULL 0 const row not found
+id select_type table type possible_keys key key_len ref rows filtered Extra
+1 PRIMARY t3 ALL NULL NULL NULL NULL 3 100.00 Using where
+2 SUBQUERY t2 system NULL NULL NULL NULL 0 0.00 const row not found
Warnings:
Note 1003 select `test`.`t3`.`a` AS `a` from `test`.`t3` where <nop>((`test`.`t3`.`a` >= <min>(select `test`.`t2`.`b` AS `b` from `test`.`t2` group by 1)))
select * from t3 where NULL >= any (select b from t2);
a
explain extended select * from t3 where NULL >= any (select b from t2);
-id select_type table type possible_keys key key_len ref rows Extra
-1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE
-2 SUBQUERY t2 system NULL NULL NULL NULL 0 const row not found
+id select_type table type possible_keys key key_len ref rows filtered Extra
+1 PRIMARY NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE
+2 SUBQUERY t2 system NULL NULL NULL NULL 0 0.00 const row not found
Warnings:
Note 1003 select `test`.`t3`.`a` AS `a` from `test`.`t3`
select * from t3 where NULL >= any (select b from t2 group by 1);
a
explain extended select * from t3 where NULL >= any (select b from t2 group by 1);
-id select_type table type possible_keys key key_len ref rows Extra
-1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE
-2 SUBQUERY t2 system NULL NULL NULL NULL 0 const row not found
+id select_type table type possible_keys key key_len ref rows filtered Extra
+1 PRIMARY NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE
+2 SUBQUERY t2 system NULL NULL NULL NULL 0 0.00 const row not found
Warnings:
Note 1003 select `test`.`t3`.`a` AS `a` from `test`.`t3`
select * from t3 where NULL >= some (select b from t2);
a
explain extended select * from t3 where NULL >= some (select b from t2);
-id select_type table type possible_keys key key_len ref rows Extra
-1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE
-2 SUBQUERY t2 system NULL NULL NULL NULL 0 const row not found
+id select_type table type possible_keys key key_len ref rows filtered Extra
+1 PRIMARY NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE
+2 SUBQUERY t2 system NULL NULL NULL NULL 0 0.00 const row not found
Warnings:
Note 1003 select `test`.`t3`.`a` AS `a` from `test`.`t3`
select * from t3 where NULL >= some (select b from t2 group by 1);
a
explain extended select * from t3 where NULL >= some (select b from t2 group by 1);
-id select_type table type possible_keys key key_len ref rows Extra
-1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE
-2 SUBQUERY t2 system NULL NULL NULL NULL 0 const row not found
+id select_type table type possible_keys key key_len ref rows filtered Extra
+1 PRIMARY NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE
+2 SUBQUERY t2 system NULL NULL NULL NULL 0 0.00 const row not found
Warnings:
Note 1003 select `test`.`t3`.`a` AS `a` from `test`.`t3`
insert into t2 values (2,2), (2,1), (3,3), (3,1);
@@ -1563,9 +1563,9 @@
6
7
explain extended select * from t3 where a > all (select max(b) from t2 group by a);
-id select_type table type possible_keys key key_len ref rows Extra
-1 PRIMARY t3 ALL NULL NULL NULL NULL 3 Using where
-2 SUBQUERY t2 ALL NULL NULL NULL NULL 4 Using temporary; Using filesort
+id select_type table type possible_keys key key_len ref rows filtered Extra
+1 PRIMARY t3 ALL NULL NULL NULL NULL 3 100.00 Using where
+2 SUBQUERY t2 ALL NULL NULL NULL NULL 4 100.00 Using temporary; Using filesort
Warnings:
Note 1003 select `test`.`t3`.`a` AS `a` from `test`.`t3` where <not>((`test`.`t3`.`a` <= <max>(select max(`test`.`t2`.`b`) AS `max(b)` from `test`.`t2` group by `test`.`t2`.`a`)))
drop table t2, t3;
@@ -1612,11 +1612,11 @@
s1
e
explain extended select * from t1 where 'f' > any (select s1 from t1 union select s1 from t1);
-id select_type table type possible_keys key key_len ref rows Extra
-1 PRIMARY t1 system NULL NULL NULL NULL 1
-2 SUBQUERY t1 system NULL NULL NULL NULL 1
-3 UNION t1 system NULL NULL NULL NULL 1
-NULL UNION RESULT <union2,3> ALL NULL NULL NULL NULL NULL
+id select_type table type possible_keys key key_len ref rows filtered Extra
+1 PRIMARY t1 system NULL NULL NULL NULL 1 100.00
+2 SUBQUERY t1 system NULL NULL NULL NULL 1 100.00
+3 UNION t1 system NULL NULL NULL NULL 1 100.00
+NULL UNION RESULT <union2,3> ALL NULL NULL NULL NULL NULL NULL
Warnings:
Note 1003 select `test`.`t1`.`s1` AS `s1` from `test`.`t1`
drop table t1;
@@ -1733,15 +1733,15 @@
11 text11
12 text12
explain extended select * from t1 where id not in (select id from t1 where id < 8);
-id select_type table type possible_keys key key_len ref rows Extra
-1 PRIMARY t1 ALL NULL NULL NULL NULL 12 Using where
-2 DEPENDENT SUBQUERY t1 unique_subquery PRIMARY PRIMARY 4 func 1 Using index; Using where
+id select_type table type possible_keys key key_len ref rows filtered Extra
+1 PRIMARY t1 ALL NULL NULL NULL NULL 12 100.00 Using where
+2 DEPENDENT SUBQUERY t1 unique_subquery PRIMARY PRIMARY 4 func 1 100.00 Using index; Using where
Warnings:
Note 1003 select `test`.`t1`.`id` AS `id`,`test`.`t1`.`text` AS `text` from `test`.`t1` where not(<in_optimizer>(`test`.`t1`.`id`,<exists>(<primary_index_lookup>(<cache>(`test`.`t1`.`id`) in t1 on PRIMARY where (`test`.`t1`.`id` < 8)))))
explain extended select * from t1 as tt where not exists (select id from t1 where id < 8 and (id = tt.id or id is null) having id is not null);
-id select_type table type possible_keys key key_len ref rows Extra
-1 PRIMARY tt ALL NULL NULL NULL NULL 12 Using where
-2 DEPENDENT SUBQUERY t1 eq_ref PRIMARY PRIMARY 4 test.tt.id 1 Using where; Using index
+id select_type table type possible_keys key key_len ref rows filtered Extra
+1 PRIMARY tt ALL NULL NULL NULL NULL 12 100.00 Using where
+2 DEPENDENT SUBQUERY t1 eq_ref PRIMARY PRIMARY 4 test.tt.id 1 100.00 Using where; Using index
Warnings:
Note 1276 Field or reference 'tt.id' of SELECT #2 was resolved in SELECT #1
Note 1003 select `test`.`tt`.`id` AS `id`,`test`.`tt`.`text` AS `text` from `test`.`t1` `tt` where not(exists(select `test`.`t1`.`id` AS `id` from `test`.`t1` where ((`test`.`t1`.`id` < 8) and (`test`.`t1`.`id` = `test`.`tt`.`id`)) having (`test`.`t1`.`id` is not null)))
@@ -1765,10 +1765,10 @@
1000 text1000 NULL NULL 1000 text1000
1001 text1001 NULL NULL 1000 text1000
explain extended select * from t1 a left join t2 b on (a.id=b.id or b.id is null) join t1 c on (if(isnull(b.id), 1000, b.id)=c.id);
-id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE a ALL NULL NULL NULL NULL 14
-1 SIMPLE b eq_ref PRIMARY PRIMARY 4 test.a.id 2
-1 SIMPLE c eq_ref PRIMARY PRIMARY 4 func 1 Using where
+id select_type table type possible_keys key key_len ref rows filtered Extra
+1 SIMPLE a ALL NULL NULL NULL NULL 14 100.00
+1 SIMPLE b eq_ref PRIMARY PRIMARY 4 test.a.id 2 100.00
+1 SIMPLE c eq_ref PRIMARY PRIMARY 4 func 1 100.00 Using where
Warnings:
Note 1003 select `test`.`a`.`id` AS `id`,`test`.`a`.`text` AS `text`,`test`.`b`.`id` AS `id`,`test`.`b`.`text` AS `text`,`test`.`c`.`id` AS `id`,`test`.`c`.`text` AS `text` from `test`.`t1` `a` left join `test`.`t2` `b` on(((`test`.`b`.`id` = `test`.`a`.`id`) or isnull(`test`.`b`.`id`))) join `test`.`t1` `c` where (if(isnull(`test`.`b`.`id`),1000,`test`.`b`.`id`) = `test`.`c`.`id`)
drop table t1,t2;
@@ -2275,9 +2275,9 @@
1 2
3 4
explain extended select * from t1 up where exists (select * from t1 where t1.a=up.a);
-id select_type table type possible_keys key key_len ref rows Extra
-1 PRIMARY up ALL NULL NULL NULL NULL 2 Using where
-2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 2 Using where
+id select_type table type possible_keys key key_len ref rows filtered Extra
+1 PRIMARY up ALL NULL NULL NULL NULL 2 100.00 Using where
+2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 2 100.00 Using where
Warnings:
Note 1276 Field or reference 'up.a' of SELECT #2 was resolved in SELECT #1
Note 1003 select `test`.`up`.`a` AS `a`,`test`.`up`.`b` AS `b` from `test`.`t1` `up` where exists(select 1 AS `Not_used` from `test`.`t1` where (`test`.`t1`.`a` = `test`.`up`.`a`))
@@ -2813,21 +2813,21 @@
10 5 NULL
10 10 NULL
explain extended SELECT one,two,ROW(one,two) IN (SELECT one,two FROM t2 WHERE flag = '0') as 'test' from t1;
-id select_type table type possible_keys key key_len ref rows Extra
-1 PRIMARY t1 ALL NULL NULL NULL NULL 8
-2 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 9 Using where
+id select_type table type possible_keys key key_len ref rows filtered Extra
+1 PRIMARY t1 ALL NULL NULL NULL NULL 8 100.00
+2 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 9 100.00 Using where
Warnings:
Note 1003 select `test`.`t1`.`one` AS `one`,`test`.`t1`.`two` AS `two`,<in_optimizer>((`test`.`t1`.`one`,`test`.`t1`.`two`),<exists>(select `test`.`t2`.`one` AS `one`,`test`.`t2`.`two` AS `two` from `test`.`t2` where ((`test`.`t2`.`flag` = _latin1'0') and ((<cache>(`test`.`t1`.`one`) = `test`.`t2`.`one`) or isnull(`test`.`t2`.`one`)) and ((<cache>(`test`.`t1`.`two`) = `test`.`t2`.`two`) or isnull(`test`.`t2`.`two`))) having (<is_not_null_test>(`test`.`t2`.`one`) and <is_not_null_test>(`test`.`t2`.`two`)))) AS `test` from `test`.`t1`
explain extended SELECT one,two from t1 where ROW(one,two) IN (SELECT one,two FROM t2 WHERE flag = 'N');
-id select_type table type possible_keys key key_len ref rows Extra
-1 PRIMARY t1 ALL NULL NULL NULL NULL 8 Using where
-2 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 9 Using where
+id select_type table type possible_keys key key_len ref rows filtered Extra
+1 PRIMARY t1 ALL NULL NULL NULL NULL 8 100.00 Using where
+2 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 9 100.00 Using where
Warnings:
Note 1003 select `test`.`t1`.`one` AS `one`,`test`.`t1`.`two` AS `two` from `test`.`t1` where <in_optimizer>((`test`.`t1`.`one`,`test`.`t1`.`two`),<exists>(select `test`.`t2`.`one` AS `one`,`test`.`t2`.`two` AS `two` from `test`.`t2` where ((`test`.`t2`.`flag` = _latin1'N') and (<cache>(`test`.`t1`.`one`) = `test`.`t2`.`one`) and (<cache>(`test`.`t1`.`two`) = `test`.`t2`.`two`))))
explain extended SELECT one,two,ROW(one,two) IN (SELECT one,two FROM t2 WHERE flag = '0' group by one,two) as 'test' from t1;
-id select_type table type possible_keys key key_len ref rows Extra
-1 PRIMARY t1 ALL NULL NULL NULL NULL 8
-2 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 9 Using where; Using temporary; Using filesort
+id select_type table type possible_keys key key_len ref rows filtered Extra
+1 PRIMARY t1 ALL NULL NULL NULL NULL 8 100.00
+2 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 9 100.00 Using where; Using temporary; Using filesort
Warnings:
Note 1003 select `test`.`t1`.`one` AS `one`,`test`.`t1`.`two` AS `two`,<in_optimizer>((`test`.`t1`.`one`,`test`.`t1`.`two`),<exists>(select `test`.`t2`.`one` AS `one`,`test`.`t2`.`two` AS `two` from `test`.`t2` where (`test`.`t2`.`flag` = _latin1'0') group by `test`.`t2`.`one`,`test`.`t2`.`two` having (((<cache>(`test`.`t1`.`one`) = `test`.`t2`.`one`) or isnull(`test`.`t2`.`one`)) and ((<cache>(`test`.`t1`.`two`) = `test`.`t2`.`two`) or isnull(`test`.`t2`.`two`)) and <is_not_null_test>(`test`.`t2`.`one`) and <is_not_null_test>(`test`.`t2`.`two`)))) AS `test` from `test`.`t1`
DROP TABLE t1,t2;
--- 1.168/mysql-test/r/view.result 2006-08-01 09:24:25 +04:00
+++ 1.169/mysql-test/r/view.result 2006-08-01 09:24:25 +04:00
@@ -48,8 +48,8 @@
select v1.b from v1;
ERROR 42S22: Unknown column 'v1.b' in 'field list'
explain extended select c from v1;
-id select_type table type possible_keys key key_len ref rows Extra
-1 PRIMARY t1 ALL NULL NULL NULL NULL 5
+id select_type table type possible_keys key key_len ref rows filtered Extra
+1 PRIMARY t1 ALL NULL NULL NULL NULL 5 100.00
Warnings:
Note 1003 select (`test`.`t1`.`b` + 1) AS `c` from `test`.`t1`
create algorithm=temptable view v2 (c) as select b+1 from t1;
@@ -64,9 +64,9 @@
6
11
explain extended select c from v2;
-id select_type table type possible_keys key key_len ref rows Extra
-1 PRIMARY <derived2> ALL NULL NULL NULL NULL 5
-2 DERIVED t1 ALL NULL NULL NULL NULL 5
+id select_type table type possible_keys key key_len ref rows filtered Extra
+1 PRIMARY <derived2> ALL NULL NULL NULL NULL 5 100.00
+2 DERIVED t1 ALL NULL NULL NULL NULL 5 100.00
Warnings:
Note 1003 select `v2`.`c` AS `c` from `test`.`v2`
create view v3 (c) as select a+1 from v1;
@@ -82,8 +82,8 @@
7
12
explain extended select c from v3;
-id select_type table type possible_keys key key_len ref rows Extra
-1 PRIMARY t1 ALL NULL NULL NULL NULL 5
+id select_type table type possible_keys key key_len ref rows filtered Extra
+1 PRIMARY t1 ALL NULL NULL NULL NULL 5 100.00
Warnings:
Note 1003 select ((`test`.`t1`.`b` + 1) + 1) AS `c` from `test`.`t1`
create algorithm=temptable view v4 (c) as select c+1 from v2;
@@ -95,10 +95,10 @@
7
12
explain extended select c from v4;
-id select_type table type possible_keys key key_len ref rows Extra
-1 PRIMARY <derived2> ALL NULL NULL NULL NULL 5
-2 DERIVED <derived3> ALL NULL NULL NULL NULL 5
-3 DERIVED t1 ALL NULL NULL NULL NULL 5
+id select_type table type possible_keys key key_len ref rows filtered Extra
+1 PRIMARY <derived2> ALL NULL NULL NULL NULL 5 100.00
+2 DERIVED <derived3> ALL NULL NULL NULL NULL 5 100.00
+3 DERIVED t1 ALL NULL NULL NULL NULL 5 100.00
Warnings:
Note 1003 select `v4`.`c` AS `c` from `test`.`v4`
create view v5 (c) as select c+1 from v2;
@@ -110,9 +110,9 @@
7
12
explain extended select c from v5;
-id select_type table type possible_keys key key_len ref rows Extra
-1 PRIMARY <derived3> ALL NULL NULL NULL NULL 5
-3 DERIVED t1 ALL NULL NULL NULL NULL 5
+id select_type table type possible_keys key key_len ref rows filtered Extra
+1 PRIMARY <derived3> ALL NULL NULL NULL NULL 5 100.00
+3 DERIVED t1 ALL NULL NULL NULL NULL 5 100.00
Warnings:
Note 1003 select (`v2`.`c` + 1) AS `c` from `test`.`v2`
create algorithm=temptable view v6 (c) as select c+1 from v1;
@@ -124,9 +124,9 @@
7
12
explain extended select c from v6;
-id select_type table type possible_keys key key_len ref rows Extra
-1 PRIMARY <derived2> ALL NULL NULL NULL NULL 5
-2 DERIVED t1 ALL NULL NULL NULL NULL 5
+id select_type table type possible_keys key key_len ref rows filtered Extra
+1 PRIMARY <derived2> ALL NULL NULL NULL NULL 5 100.00
+2 DERIVED t1 ALL NULL NULL NULL NULL 5 100.00
Warnings:
Note 1003 select `v6`.`c` AS `c` from `test`.`v6`
show tables;
@@ -375,8 +375,8 @@
20
30
explain extended select * from v1;
-id select_type table type possible_keys key key_len ref rows Extra
-1 PRIMARY t1 ALL NULL NULL NULL NULL 5 Using where
+id select_type table type possible_keys key key_len ref rows filtered Extra
+1 PRIMARY t1 ALL NULL NULL NULL NULL 5 100.00 Using where
Warnings:
Note 1003 select `test`.`t1`.`b` AS `c` from `test`.`t1` where (`test`.`t1`.`a` < 3)
update v1 set c=c+1;
@@ -1390,10 +1390,10 @@
2 2 NULL
4 NULL NULL
explain extended select * from t3 left join v3 on (t3.a = v3.a);
-id select_type table type possible_keys key key_len ref rows Extra
-1 PRIMARY t3 ALL NULL NULL NULL NULL 3
-1 PRIMARY t1 ALL NULL NULL NULL NULL 3
-1 PRIMARY t2 ALL NULL NULL NULL NULL 2
+id select_type table type possible_keys key key_len ref rows filtered Extra
+1 PRIMARY t3 ALL NULL NULL NULL NULL 3 100.00
+1 PRIMARY t1 ALL NULL NULL NULL NULL 3 100.00
+1 PRIMARY t2 ALL NULL NULL NULL NULL 2 100.00
Warnings:
Note 1003 select `test`.`t3`.`a` AS `a`,`test`.`t1`.`a` AS `a`,`test`.`t2`.`a` AS `b` from `test`.`t3` left join (`test`.`t1` left join `test`.`t2` on((`test`.`t1`.`a` = `test`.`t2`.`a`))) on((`test`.`t3`.`a` = `test`.`t1`.`a`)) where 1
create view v1 (a) as select a from t1;
@@ -1405,10 +1405,10 @@
2 2 NULL
4 NULL NULL
explain extended select * from t3 left join v4 on (t3.a = v4.a);
-id select_type table type possible_keys key key_len ref rows Extra
-1 PRIMARY t3 ALL NULL NULL NULL NULL 3
-1 PRIMARY t1 ALL NULL NULL NULL NULL 3
-1 PRIMARY t2 ALL NULL NULL NULL NULL 2
+id select_type table type possible_keys key key_len ref rows filtered Extra
+1 PRIMARY t3 ALL NULL NULL NULL NULL 3 100.00
+1 PRIMARY t1 ALL NULL NULL NULL NULL 3 100.00
+1 PRIMARY t2 ALL NULL NULL NULL NULL 2 100.00
Warnings:
Note 1003 select `test`.`t3`.`a` AS `a`,`test`.`t1`.`a` AS `a`,`test`.`t2`.`a` AS `b` from `test`.`t3` left join (`test`.`t1` left join (`test`.`t2`) on((`test`.`t1`.`a` = `test`.`t2`.`a`))) on((`test`.`t3`.`a` = `test`.`t1`.`a`)) where 1
prepare stmt1 from "select * from t3 left join v4 on (t3.a = v4.a);";
--- 1.16/mysql-test/r/binlog_stm_blackhole.result 2006-08-01 09:24:25 +04:00
+++ 1.17/mysql-test/r/binlog_stm_blackhole.result 2006-08-01 09:24:25 +04:00
@@ -70,8 +70,8 @@
Only MyISAM tables support collections
Full-text indexes are called collections
explain extended select * from t1 where MATCH(a,b) AGAINST ("collections");
-id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 fulltext a a 0 1 Using where
+id select_type table type possible_keys key key_len ref rows filtered Extra
+1 SIMPLE t1 fulltext a a 0 1 100.00 Using where
Warnings:
Note 1003 select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` where (match `test`.`t1`.`a`,`test`.`t1`.`b` against (_latin1'collections'))
select * from t1 where MATCH(a,b) AGAINST ("indexes");
--- 1.28/mysql-test/r/date_formats.result 2006-08-01 09:24:25 +04:00
+++ 1.29/mysql-test/r/date_formats.result 2006-08-01 09:24:25 +04:00
@@ -434,8 +434,8 @@
Warnings:
Error 1411 Incorrect datetime value: '15-01-2001 12:59:59' for function str_to_time
explain extended select makedate(1997,1), addtime("31.12.97 11.59.59.999999 PM", "1 1.1.1.000002"),subtime("31.12.97 11.59.59.999999 PM", "1 1.1.1.000002"),timediff("01.01.97 11:59:59.000001 PM","31.12.95 11:59:59.000002 PM"),cast(str_to_date("15-01-2001 12:59:59", "%d-%m-%Y %H:%i:%S") as TIME), maketime(23,11,12),microsecond("1997-12-31 23:59:59.000001");
-id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE NULL NULL NULL NULL NULL NULL NULL No tables used
+id select_type table type possible_keys key key_len ref rows filtered Extra
+1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL No tables used
Warnings:
Note 1003 select makedate(1997,1) AS `makedate(1997,1)`,addtime(_latin1'31.12.97 11.59.59.999999 PM',_latin1'1 1.1.1.000002') AS `addtime("31.12.97 11.59.59.999999 PM", "1 1.1.1.000002")`,subtime(_latin1'31.12.97 11.59.59.999999 PM',_latin1'1 1.1.1.000002') AS `subtime("31.12.97 11.59.59.999999 PM", "1 1.1.1.000002")`,timediff(_latin1'01.01.97 11:59:59.000001 PM',_latin1'31.12.95 11:59:59.000002 PM') AS `timediff("01.01.97 11:59:59.000001 PM","31.12.95 11:59:59.000002 PM")`,cast(str_to_date(_latin1'15-01-2001 12:59:59',_latin1'%d-%m-%Y %H:%i:%S') as time) AS `cast(str_to_date("15-01-2001 12:59:59", "%d-%m-%Y %H:%i:%S") as TIME)`,maketime(23,11,12) AS `maketime(23,11,12)`,microsecond(_latin1'1997-12-31 23:59:59.000001') AS `microsecond("1997-12-31 23:59:59.000001")`
create table t1 (d date);
--- 1.8/mysql-test/r/binlog_row_blackhole.result 2006-08-01 09:24:25 +04:00
+++ 1.9/mysql-test/r/binlog_row_blackhole.result 2006-08-01 09:24:25 +04:00
@@ -70,8 +70,8 @@
Only MyISAM tables support collections
Full-text indexes are called collections
explain extended select * from t1 where MATCH(a,b) AGAINST ("collections");
-id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 fulltext a a 0 1 Using where
+id select_type table type possible_keys key key_len ref rows filtered Extra
+1 SIMPLE t1 fulltext a a 0 1 100.00 Using where
Warnings:
Note 1003 select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` where (match `test`.`t1`.`a`,`test`.`t1`.`b` against (_latin1'collections'))
select * from t1 where MATCH(a,b) AGAINST ("indexes");
--- 1.44/mysql-test/r/auto_increment.result 2006-08-01 09:24:25 +04:00
+++ 1.45/mysql-test/r/auto_increment.result 2006-08-01 09:24:25 +04:00
@@ -140,8 +140,8 @@
last_insert_id()
255
explain extended select last_insert_id();
-id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE NULL NULL NULL NULL NULL NULL NULL No tables used
+id select_type table type possible_keys key key_len ref rows filtered Extra
+1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL No tables used
Warnings:
Note 1003 select last_insert_id() AS `last_insert_id()`
insert into t1 set i = 254;
--- 1.53/mysql-test/r/func_group.result 2006-08-01 09:24:25 +04:00
+++ 1.54/mysql-test/r/func_group.result 2006-08-01 09:24:25 +04:00
@@ -287,8 +287,8 @@
2 1 1 1.00000 0.00000 1 1 1 1 1
3 1 1 1.00000 0.00000 1 1 1 1 1
explain extended select SQL_BIG_RESULT a,count(b), sum(b), avg(b), std(b), min(b), max(b), bit_and(b), bit_or(b), bit_xor(b) from t1 group by a;
-id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 ALL NULL NULL NULL NULL 5 Using filesort
+id select_type table type possible_keys key key_len ref rows filtered Extra
+1 SIMPLE t1 ALL NULL NULL NULL NULL 5 100.00 Using filesort
Warnings:
Note 1003 select sql_big_result `test`.`t1`.`a` AS `a`,count(`test`.`t1`.`b`) AS `count(b)`,sum(`test`.`t1`.`b`) AS `sum(b)`,avg(`test`.`t1`.`b`) AS `avg(b)`,std(`test`.`t1`.`b`) AS `std(b)`,min(`test`.`t1`.`b`) AS `min(b)`,max(`test`.`t1`.`b`) AS `max(b)`,bit_and(`test`.`t1`.`b`) AS `bit_and(b)`,bit_or(`test`.`t1`.`b`) AS `bit_or(b)`,bit_xor(`test`.`t1`.`b`) AS `bit_xor(b)` from `test`.`t1` group by `test`.`t1`.`a`
drop table t1;
--- 1.37/mysql-test/r/func_math.result 2006-08-01 09:24:25 +04:00
+++ 1.38/mysql-test/r/func_math.result 2006-08-01 09:24:25 +04:00
@@ -3,32 +3,32 @@
floor(5.5) floor(-5.5)
5 -6
explain extended select floor(5.5),floor(-5.5);
-id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE NULL NULL NULL NULL NULL NULL NULL No tables used
+id select_type table type possible_keys key key_len ref rows filtered Extra
+1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL No tables used
Warnings:
Note 1003 select floor(5.5) AS `floor(5.5)`,floor(-(5.5)) AS `floor(-5.5)`
select ceiling(5.5),ceiling(-5.5);
ceiling(5.5) ceiling(-5.5)
6 -5
explain extended select ceiling(5.5),ceiling(-5.5);
-id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE NULL NULL NULL NULL NULL NULL NULL No tables used
+id select_type table type possible_keys key key_len ref rows filtered Extra
+1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL No tables used
Warnings:
Note 1003 select ceiling(5.5) AS `ceiling(5.5)`,ceiling(-(5.5)) AS `ceiling(-5.5)`
select truncate(52.64,1),truncate(52.64,2),truncate(52.64,-1),truncate(52.64,-2), truncate(-52.64,1),truncate(-52.64,-1);
truncate(52.64,1) truncate(52.64,2) truncate(52.64,-1) truncate(52.64,-2) truncate(-52.64,1) truncate(-52.64,-1)
52.6 52.64 50 0 -52.6 -50
explain extended select truncate(52.64,1),truncate(52.64,2),truncate(52.64,-1),truncate(52.64,-2), truncate(-52.64,1),truncate(-52.64,-1);
-id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE NULL NULL NULL NULL NULL NULL NULL No tables used
+id select_type table type possible_keys key key_len ref rows filtered Extra
+1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL No tables used
Warnings:
Note 1003 select truncate(52.64,1) AS `truncate(52.64,1)`,truncate(52.64,2) AS `truncate(52.64,2)`,truncate(52.64,-(1)) AS `truncate(52.64,-1)`,truncate(52.64,-(2)) AS `truncate(52.64,-2)`,truncate(-(52.64),1) AS `truncate(-52.64,1)`,truncate(-(52.64),-(1)) AS `truncate(-52.64,-1)`
select round(5.5),round(-5.5);
round(5.5) round(-5.5)
6 -6
explain extended select round(5.5),round(-5.5);
-id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE NULL NULL NULL NULL NULL NULL NULL No tables used
+id select_type table type possible_keys key key_len ref rows filtered Extra
+1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL No tables used
Warnings:
Note 1003 select round(5.5,0) AS `round(5.5)`,round(-(5.5),0) AS `round(-5.5)`
select round(5.64,1),round(5.64,2),round(5.64,-1),round(5.64,-2);
@@ -38,48 +38,48 @@
abs(-10) sign(-5) sign(5) sign(0)
10 -1 1 0
explain extended select abs(-10), sign(-5), sign(5), sign(0);
-id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE NULL NULL NULL NULL NULL NULL NULL No tables used
+id select_type table type possible_keys key key_len ref rows filtered Extra
+1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL No tables used
Warnings:
Note 1003 select abs(-(10)) AS `abs(-10)`,sign(-(5)) AS `sign(-5)`,sign(5) AS `sign(5)`,sign(0) AS `sign(0)`
select log(exp(10)),exp(log(sqrt(10))*2),log(-1),log(NULL),log(1,1),log(3,9),log(-1,2),log(NULL,2);
log(exp(10)) exp(log(sqrt(10))*2) log(-1) log(NULL) log(1,1) log(3,9) log(-1,2) log(NULL,2)
10 10 NULL NULL NULL 2 NULL NULL
explain extended select log(exp(10)),exp(log(sqrt(10))*2),log(-1),log(NULL),log(1,1),log(3,9),log(-1,2),log(NULL,2);
-id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE NULL NULL NULL NULL NULL NULL NULL No tables used
+id select_type table type possible_keys key key_len ref rows filtered Extra
+1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL No tables used
Warnings:
Note 1003 select log(exp(10)) AS `log(exp(10))`,exp((log(sqrt(10)) * 2)) AS `exp(log(sqrt(10))*2)`,log(-(1)) AS `log(-1)`,log(NULL) AS `log(NULL)`,log(1,1) AS `log(1,1)`,log(3,9) AS `log(3,9)`,log(-(1),2) AS `log(-1,2)`,log(NULL,2) AS `log(NULL,2)`
select ln(exp(10)),exp(ln(sqrt(10))*2),ln(-1),ln(0),ln(NULL);
ln(exp(10)) exp(ln(sqrt(10))*2) ln(-1) ln(0) ln(NULL)
10 10 NULL NULL NULL
explain extended select ln(exp(10)),exp(ln(sqrt(10))*2),ln(-1),ln(0),ln(NULL);
-id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE NULL NULL NULL NULL NULL NULL NULL No tables used
+id select_type table type possible_keys key key_len ref rows filtered Extra
+1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL No tables used
Warnings:
Note 1003 select ln(exp(10)) AS `ln(exp(10))`,exp((ln(sqrt(10)) * 2)) AS `exp(ln(sqrt(10))*2)`,ln(-(1)) AS `ln(-1)`,ln(0) AS `ln(0)`,ln(NULL) AS `ln(NULL)`
select log2(8),log2(15),log2(-2),log2(0),log2(NULL);
log2(8) log2(15) log2(-2) log2(0) log2(NULL)
3 3.9068905956085 NULL NULL NULL
explain extended select log2(8),log2(15),log2(-2),log2(0),log2(NULL);
-id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE NULL NULL NULL NULL NULL NULL NULL No tables used
+id select_type table type possible_keys key key_len ref rows filtered Extra
+1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL No tables used
Warnings:
Note 1003 select log2(8) AS `log2(8)`,log2(15) AS `log2(15)`,log2(-(2)) AS `log2(-2)`,log2(0) AS `log2(0)`,log2(NULL) AS `log2(NULL)`
select log10(100),log10(18),log10(-4),log10(0),log10(NULL);
log10(100) log10(18) log10(-4) log10(0) log10(NULL)
2 1.2552725051033 NULL NULL NULL
explain extended select log10(100),log10(18),log10(-4),log10(0),log10(NULL);
-id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE NULL NULL NULL NULL NULL NULL NULL No tables used
+id select_type table type possible_keys key key_len ref rows filtered Extra
+1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL No tables used
Warnings:
Note 1003 select log10(100) AS `log10(100)`,log10(18) AS `log10(18)`,log10(-(4)) AS `log10(-4)`,log10(0) AS `log10(0)`,log10(NULL) AS `log10(NULL)`
select pow(10,log10(10)),power(2,4);
pow(10,log10(10)) power(2,4)
10 16
explain extended select pow(10,log10(10)),power(2,4);
-id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE NULL NULL NULL NULL NULL NULL NULL No tables used
+id select_type table type possible_keys key key_len ref rows filtered Extra
+1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL No tables used
Warnings:
Note 1003 select pow(10,log10(10)) AS `pow(10,log10(10))`,pow(2,4) AS `power(2,4)`
set @@rand_seed1=10000000,@@rand_seed2=1000000;
@@ -87,16 +87,16 @@
rand(999999) rand()
0.014231365187309 0.028870999839968
explain extended select rand(999999),rand();
-id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE NULL NULL NULL NULL NULL NULL NULL No tables used
+id select_type table type possible_keys key key_len ref rows filtered Extra
+1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL No tables used
Warnings:
Note 1003 select rand(999999) AS `rand(999999)`,rand() AS `rand()`
select pi(),format(sin(pi()/2),6),format(cos(pi()/2),6),format(abs(tan(pi())),6),format(cot(1),6),format(asin(1),6),format(acos(0),6),format(atan(1),6);
pi() format(sin(pi()/2),6) format(cos(pi()/2),6) format(abs(tan(pi())),6) format(cot(1),6) format(asin(1),6) format(acos(0),6) format(atan(1),6)
3.141593 1.000000 0.000000 0.000000 0.642093 1.570796 1.570796 0.785398
explain extended select pi(),format(sin(pi()/2),6),format(cos(pi()/2),6),format(abs(tan(pi())),6),format(cot(1),6),format(asin(1),6),format(acos(0),6),format(atan(1),6);
-id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE NULL NULL NULL NULL NULL NULL NULL No tables used
+id select_type table type possible_keys key key_len ref rows filtered Extra
+1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL No tables used
Warnings:
Note 1003 select pi() AS `pi()`,format(sin((pi() / 2)),6) AS `format(sin(pi()/2),6)`,format(cos((pi() / 2)),6) AS `format(cos(pi()/2),6)`,format(abs(tan(pi())),6) AS `format(abs(tan(pi())),6)`,format((1 / tan(1)),6) AS `format(cot(1),6)`,format(asin(1),6) AS `format(asin(1),6)`,format(acos(0),6) AS `format(acos(0),6)`,format(atan(1),6) AS `format(atan(1),6)`
select degrees(pi()),radians(360);
@@ -124,8 +124,8 @@
format(4.55, 1) format(4.551, 1)
4.6 4.6
explain extended select degrees(pi()),radians(360);
-id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE NULL NULL NULL NULL NULL NULL NULL No tables used
+id select_type table type possible_keys key key_len ref rows filtered Extra
+1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL No tables used
Warnings:
Note 1003 select degrees(pi()) AS `degrees(pi())`,radians(360) AS `radians(360)`
select rand(rand);
--- 1.25/mysql-test/r/func_system.result 2006-08-01 09:24:25 +04:00
+++ 1.26/mysql-test/r/func_system.result 2006-08-01 09:24:25 +04:00
@@ -38,8 +38,8 @@
charset(version())
utf8
explain extended select database(), user();
-id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE NULL NULL NULL NULL NULL NULL NULL No tables used
+id select_type table type possible_keys key key_len ref rows filtered Extra
+1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL No tables used
Warnings:
Note 1003 select database() AS `database()`,user() AS `user()`
create table t1 (version char(60)) select database(), user(), version() as 'version';
--- 1.65/mysql-test/r/func_time.result 2006-08-01 09:24:25 +04:00
+++ 1.66/mysql-test/r/func_time.result 2006-08-01 09:24:25 +04:00
@@ -726,8 +726,8 @@
strcmp(concat(utc_date(),' ',utc_time()),utc_timestamp())=0
1
explain extended select period_add("9602",-12),period_diff(199505,"9404"),from_days(to_days("960101")),dayofmonth("1997-01-02"), month("1997-01-02"), monthname("1972-03-04"),dayofyear("0000-00-00"),HOUR("1997-03-03 23:03:22"),MINUTE("23:03:22"),SECOND(230322),QUARTER(980303),WEEK("1998-03-03"),yearweek("2000-01-01",1),week(19950101,1),year("98-02-03"),weekday(curdate())-weekday(now()),dayname("1962-03-03"),unix_timestamp(),sec_to_time(time_to_sec("0:30:47")/6.21),curtime(),utc_time(),curdate(),utc_date(),utc_timestamp(),date_format("1997-01-02 03:04:05", "%M %W %D %Y %y %m %d %h %i %s %w"),from_unixtime(unix_timestamp("1994-03-02 10:11:12")),"1997-12-31 23:59:59" + INTERVAL 1 SECOND,"1998-01-01 00:00:00" - INTERVAL 1 SECOND,INTERVAL 1 DAY + "1997-12-31", extract(YEAR FROM "1999-01-02 10:11:12"),date_add("1997-12-31 23:59:59",INTERVAL 1 SECOND);
-id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE NULL NULL NULL NULL NULL NULL NULL No tables used
+id select_type table type possible_keys key key_len ref rows filtered Extra
+1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL No tables used
Warnings:
Note 1003 select period_add(_latin1'9602',-(12)) AS `period_add("9602",-12)`,period_diff(199505,_latin1'9404') AS `period_diff(199505,"9404")`,from_days(to_days(_latin1'960101')) AS `from_days(to_days("960101"))`,dayofmonth(_latin1'1997-01-02') AS `dayofmonth("1997-01-02")`,month(_latin1'1997-01-02') AS `month("1997-01-02")`,monthname(_latin1'1972-03-04') AS `monthname("1972-03-04")`,dayofyear(_latin1'0000-00-00') AS `dayofyear("0000-00-00")`,hour(_latin1'1997-03-03 23:03:22') AS `HOUR("1997-03-03 23:03:22")`,minute(_latin1'23:03:22') AS `MINUTE("23:03:22")`,second(230322) AS `SECOND(230322)`,quarter(980303) AS `QUARTER(980303)`,week(_latin1'1998-03-03',0) AS `WEEK("1998-03-03")`,yearweek(_latin1'2000-01-01',1) AS `yearweek("2000-01-01",1)`,week(19950101,1) AS `week(19950101,1)`,year(_latin1'98-02-03') AS `year("98-02-03")`,(weekday(curdate()) - weekday(now())) AS `weekday(curdate())-weekday(now())`,dayname(_latin1'1962-03-03') AS `dayname("1962-03-03")`,unix_timestamp() AS
`unix_timestamp()`,sec_to_time((time_to_sec(_latin1'0:30:47') / 6.21)) AS `sec_to_time(time_to_sec("0:30:47")/6.21)`,curtime() AS `curtime()`,utc_time() AS `utc_time()`,curdate() AS `curdate()`,utc_date() AS `utc_date()`,utc_timestamp() AS `utc_timestamp()`,date_format(_latin1'1997-01-02 03:04:05',_latin1'%M %W %D %Y %y %m %d %h %i %s %w') AS `date_format("1997-01-02 03:04:05", "%M %W %D %Y %y %m %d %h %i %s %w")`,from_unixtime(unix_timestamp(_latin1'1994-03-02 10:11:12')) AS `from_unixtime(unix_timestamp("1994-03-02 10:11:12"))`,(_latin1'1997-12-31 23:59:59' + interval 1 second) AS `"1997-12-31 23:59:59" + INTERVAL 1 SECOND`,(_latin1'1998-01-01 00:00:00' - interval 1 second) AS `"1998-01-01 00:00:00" - INTERVAL 1 SECOND`,(_latin1'1997-12-31' + interval 1 day) AS `INTERVAL 1 DAY + "1997-12-31"`,extract(year from _latin1'1999-01-02 10:11:12') AS `extract(YEAR FROM "1999-01-02 10:11:12")`,(_latin1'1997-12-31 23:59:59' + interval 1 second) AS `date_add("1997-12-31 23:59:59",INT
ERVAL 1 SECOND)`
SET @TMP=NOW();
@@ -815,8 +815,8 @@
End of 4.1 tests
explain extended select timestampdiff(SQL_TSI_WEEK, '2001-02-01', '2001-05-01') as a1,
timestampdiff(SQL_TSI_FRAC_SECOND, '2001-02-01 12:59:59.120000', '2001-05-01 12:58:58.119999') as a2;
-id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE NULL NULL NULL NULL NULL NULL NULL No tables used
+id select_type table type possible_keys key key_len ref rows filtered Extra
+1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL No tables used
Warnings:
Note 1003 select timestampdiff(WEEK,_latin1'2001-02-01',_latin1'2001-05-01') AS `a1`,timestampdiff(SECOND_FRAC,_latin1'2001-02-01 12:59:59.120000',_latin1'2001-05-01 12:58:58.119999') AS `a2`
select time_format('100:00:00', '%H %k %h %I %l');
--- 1.69/mysql-test/r/group_by.result 2006-08-01 09:24:25 +04:00
+++ 1.70/mysql-test/r/group_by.result 2006-08-01 09:24:25 +04:00
@@ -285,8 +285,8 @@
2 2
1 1
explain extended select sql_big_result spid,sum(userid) from t1 group by spid desc;
-id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 ALL NULL NULL NULL NULL 8 Using filesort
+id select_type table type possible_keys key key_len ref rows filtered Extra
+1 SIMPLE t1 ALL NULL NULL NULL NULL 8 100.00 Using filesort
Warnings:
Note 1003 select sql_big_result `test`.`t1`.`spID` AS `spid`,sum(`test`.`t1`.`userID`) AS `sum(userid)` from `test`.`t1` group by `test`.`t1`.`spID` desc
explain select sql_big_result spid,sum(userid) from t1 group by spid desc order by null;
@@ -537,11 +537,11 @@
explain select t1.a,t2.b from t1,t2 where t1.a=t2.a group by t1.a,t2.b;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 ALL NULL NULL NULL NULL 6 Using temporary; Using filesort
-1 SIMPLE t2 ALL a NULL NULL NULL 3 Using where
+1 SIMPLE t2 ALL a NULL NULL NULL 4 Using where
explain select t1.a,t2.b from t1,t2 where t1.a=t2.a group by t1.a,t2.b ORDER BY NULL;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 ALL NULL NULL NULL NULL 6 Using temporary
-1 SIMPLE t2 ALL a NULL NULL NULL 3 Using where
+1 SIMPLE t2 ALL a NULL NULL NULL 4 Using where
drop table t1,t2;
create table t1 (a int, b int);
insert into t1 values (1, 4),(10, 40),(1, 4),(10, 43),(1, 4),(10, 41),(1, 4),(10, 43),(1, 4);
--- 1.133/mysql-test/r/select.result 2006-08-01 09:24:25 +04:00
+++ 1.134/mysql-test/r/select.result 2006-08-01 09:24:25 +04:00
@@ -2730,6 +2730,81 @@
EXPLAIN SELECT * FROM t1 FORCE INDEX (a);
ERROR 42000: Key 'a' doesn't exist in table 't1'
DROP TABLE t1;
+CREATE TABLE t1 (a int, b int);
+INSERT INTO t1 VALUES (1,1), (2,1), (4,10);
+CREATE TABLE t2 (a int PRIMARY KEY, b int, KEY b (b));
+INSERT INTO t2 VALUES (1,NULL), (2,10);
+ALTER TABLE t1 ENABLE KEYS;
+EXPLAIN SELECT STRAIGHT_JOIN SQL_NO_CACHE COUNT(*) FROM t2, t1 WHERE t1.b = t2.b OR t2.b IS NULL;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t2 index b b 5 NULL 2 Using index
+1 SIMPLE t1 ALL NULL NULL NULL NULL 3 Using where
+SELECT STRAIGHT_JOIN SQL_NO_CACHE * FROM t2, t1 WHERE t1.b = t2.b OR t2.b IS NULL;
+a b a b
+1 NULL 1 1
+1 NULL 2 1
+1 NULL 4 10
+2 10 4 10
+EXPLAIN SELECT STRAIGHT_JOIN SQL_NO_CACHE COUNT(*) FROM t2, t1 WHERE t1.b = t2.b OR t2.b IS NULL;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t2 index b b 5 NULL 2 Using index
+1 SIMPLE t1 ALL NULL NULL NULL NULL 3 Using where
+SELECT STRAIGHT_JOIN SQL_NO_CACHE * FROM t2, t1 WHERE t1.b = t2.b OR t2.b IS NULL;
+a b a b
+1 NULL 1 1
+1 NULL 2 1
+1 NULL 4 10
+2 10 4 10
+DROP TABLE IF EXISTS t1,t2;
+CREATE TABLE t1 (key1 float default NULL, UNIQUE KEY key1 (key1));
+CREATE TABLE t2 (key2 float default NULL, UNIQUE KEY key2 (key2));
+INSERT INTO t1 VALUES (0.3762),(0.3845),(0.6158),(0.7941);
+INSERT INTO t2 VALUES (1.3762),(1.3845),(1.6158),(1.7941);
+explain select max(key1) from t1 where key1 <= 0.6158;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Select tables optimized away
+explain select max(key2) from t2 where key2 <= 1.6158;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Select tables optimized away
+explain select min(key1) from t1 where key1 >= 0.3762;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Select tables optimized away
+explain select min(key2) from t2 where key2 >= 1.3762;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Select tables optimized away
+explain select max(key1), min(key2) from t1, t2
+where key1 <= 0.6158 and key2 >= 1.3762;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Select tables optimized away
+explain select max(key1) from t1 where key1 <= 0.6158 and rand() + 0.5 >= 0.5;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Select tables optimized away
+explain select min(key1) from t1 where key1 >= 0.3762 and rand() + 0.5 >= 0.5;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Select tables optimized away
+select max(key1) from t1 where key1 <= 0.6158;
+max(key1)
+0.61580002307892
+select max(key2) from t2 where key2 <= 1.6158;
+max(key2)
+1.6158000230789
+select min(key1) from t1 where key1 >= 0.3762;
+min(key1)
+0.37619999051094
+select min(key2) from t2 where key2 >= 1.3762;
+min(key2)
+1.3761999607086
+select max(key1), min(key2) from t1, t2
+where key1 <= 0.6158 and key2 >= 1.3762;
+max(key1) min(key2)
+0.61580002307892 1.3761999607086
+select max(key1) from t1 where key1 <= 0.6158 and rand() + 0.5 >= 0.5;
+max(key1)
+0.61580002307892
+select min(key1) from t1 where key1 >= 0.3762 and rand() + 0.5 >= 0.5;
+min(key1)
+0.37619999051094
+DROP TABLE t1,t2;
CREATE TABLE t1 (i BIGINT UNSIGNED NOT NULL);
INSERT INTO t1 VALUES (10);
SELECT i='1e+01',i=1e+01, i in (1e+01,1e+01), i in ('1e+01','1e+01') FROM t1;
@@ -3395,3 +3470,12 @@
1 0 1 1 0 1 NULL
2 0 1 NULL NULL NULL NULL
drop table t1,t2;
+SELECT 0.9888889889 * 1.011111411911;
+0.9888889889 * 1.011111411911
+0.9998769417899202067879
+prepare stmt from 'select 1 as " a "';
+Warnings:
+Warning 1546 Leading spaces are removed from name ' a '
+execute stmt;
+a
+1
--- 1.53/mysql-test/r/type_blob.result 2006-08-01 09:24:25 +04:00
+++ 1.54/mysql-test/r/type_blob.result 2006-08-01 09:24:25 +04:00
@@ -514,8 +514,8 @@
charset(load_file('../../std_data/words.dat')),
collation(load_file('../../std_data/words.dat')),
coercibility(load_file('../../std_data/words.dat'));
-id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE NULL NULL NULL NULL NULL NULL NULL No tables used
+id select_type table type possible_keys key key_len ref rows filtered Extra
+1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL No tables used
Warnings:
Note 1003 select charset(load_file(_latin1'../../std_data/words.dat')) AS `charset(load_file('../../std_data/words.dat'))`,collation(load_file(_latin1'../../std_data/words.dat')) AS `collation(load_file('../../std_data/words.dat'))`,coercibility(load_file(_latin1'../../std_data/words.dat')) AS `coercibility(load_file('../../std_data/words.dat'))`
update t1 set imagem=load_file('../../std_data/words.dat') where id=1;
--- 1.87/mysql-test/r/variables.result 2006-08-01 09:24:25 +04:00
+++ 1.88/mysql-test/r/variables.result 2006-08-01 09:24:25 +04:00
@@ -72,8 +72,8 @@
@t1:=(@t2:=1)+@t3:=4 @t1 @t2 @t3
5 5 1 4
explain extended select @t1:=(@t2:=1)+@t3:=4,@t1,@t2,@t3;
-id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE NULL NULL NULL NULL NULL NULL NULL No tables used
+id select_type table type possible_keys key key_len ref rows filtered Extra
+1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL No tables used
Warnings:
Note 1003 select (@t1:=((@t2:=1) + (@t3:=4))) AS `@t1:=(@t2:=1)+@t3:=4`,(@t1) AS `@t1`,(@t2) AS `@t2`,(@t3) AS `@t3`
select @t5;
@@ -132,16 +132,16 @@
last_insert_id(345)
345
explain extended select last_insert_id(345);
-id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE NULL NULL NULL NULL NULL NULL NULL No tables used
+id select_type table type possible_keys key key_len ref rows filtered Extra
+1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL No tables used
Warnings:
Note 1003 select last_insert_id(345) AS `last_insert_id(345)`
select @@IDENTITY,last_insert_id(), @@identity;
@@IDENTITY last_insert_id() @@identity
345 345 345
explain extended select @@IDENTITY,last_insert_id(), @@identity;
-id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE NULL NULL NULL NULL NULL NULL NULL No tables used
+id select_type table type possible_keys key key_len ref rows filtered Extra
+1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL No tables used
Warnings:
Note 1003 select 345 AS `@@IDENTITY`,last_insert_id() AS `last_insert_id()`,345 AS `@@identity`
set big_tables=OFF, big_tables=ON, big_tables=0, big_tables=1, big_tables="OFF", big_tables="ON";
--- 1.87/mysql-test/r/myisam.result 2006-08-01 09:24:25 +04:00
+++ 1.88/mysql-test/r/myisam.result 2006-08-01 09:24:25 +04:00
@@ -346,11 +346,11 @@
explain select * from t1,t2 where t1.a=t2.a;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t2 ALL a NULL NULL NULL 2
-1 SIMPLE t1 ALL a NULL NULL NULL 4 Using where
+1 SIMPLE t1 ALL a NULL NULL NULL 5 Using where
explain select * from t1,t2 force index(a) where t1.a=t2.a;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t2 ALL a NULL NULL NULL 2
-1 SIMPLE t1 ALL a NULL NULL NULL 4 Using where
+1 SIMPLE t1 ALL a NULL NULL NULL 5 Using where
explain select * from t1 force index(a),t2 force index(a) where t1.a=t2.a;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t2 ALL a NULL NULL NULL 2
@@ -362,7 +362,7 @@
explain select * from t1,t2 force index(c) where t1.a=t2.a;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t2 ALL NULL NULL NULL NULL 2
-1 SIMPLE t1 ALL a NULL NULL NULL 4 Using where
+1 SIMPLE t1 ALL a NULL NULL NULL 5 Using where
explain select * from t1 where a=0 or a=2;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 ALL a NULL NULL NULL 5 Using where
--- 1.76/mysql-test/r/query_cache.result 2006-08-01 09:24:25 +04:00
+++ 1.77/mysql-test/r/query_cache.result 2006-08-01 09:24:25 +04:00
@@ -230,8 +230,8 @@
select benchmark(1,1) from t1;
benchmark(1,1)
explain extended select benchmark(1,1) from t1;
-id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 system NULL NULL NULL NULL 0 const row not found
+id select_type table type possible_keys key key_len ref rows filtered Extra
+1 SIMPLE t1 system NULL NULL NULL NULL 0 0.00 const row not found
Warnings:
Note 1003 select benchmark(1,1) AS `benchmark(1,1)` from `test`.`t1`
show status like "Qcache_queries_in_cache";
--- 1.7/mysql-test/r/rpl_master_pos_wait.result 2006-08-01 09:24:25 +04:00
+++ 1.8/mysql-test/r/rpl_master_pos_wait.result 2006-08-01 09:24:25 +04:00
@@ -8,8 +8,8 @@
master_pos_wait('master-bin.999999',0,2)
-1
explain extended select master_pos_wait('master-bin.999999',0,2);
-id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE NULL NULL NULL NULL NULL NULL NULL No tables used
+id select_type table type possible_keys key key_len ref rows filtered Extra
+1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL No tables used
Warnings:
Note 1003 select master_pos_wait(_latin1'master-bin.999999',0,2) AS `master_pos_wait('master-bin.999999',0,2)`
select master_pos_wait('master-bin.999999',0);
--- 1.120/mysql-test/r/func_str.result 2006-08-01 09:24:25 +04:00
+++ 1.121/mysql-test/r/func_str.result 2006-08-01 09:24:25 +04:00
@@ -33,6 +33,9 @@
select position(binary 'll' in 'hello'),position('a' in binary 'hello');
position(binary 'll' in 'hello') position('a' in binary 'hello')
3 0
+select left('hello',null), right('hello',null);
+left('hello',null) right('hello',null)
+NULL NULL
select left('hello',2),right('hello',2),substring('hello',2,2),mid('hello',1,5) ;
left('hello',2) right('hello',2) substring('hello',2,2) mid('hello',1,5)
he lo el hello
@@ -1038,6 +1041,49 @@
f1 f2
test a94a8fe5ccb19ba61c4c0873d391e987982fbbd3
drop table t1;
+CREATE TABLE t1 (a varchar(10));
+INSERT INTO t1 VALUES ('abc'), ('xyz');
+SELECT a, CONCAT(a,' ',a) AS c FROM t1
+HAVING LEFT(c,LENGTH(c)-INSTR(REVERSE(c)," ")) = a;
+a c
+abc abc abc
+xyz xyz xyz
+SELECT a, CONCAT(a,' ',a) AS c FROM t1
+HAVING LEFT(CONCAT(a,' ',a),
+LENGTH(CONCAT(a,' ',a))-
+INSTR(REVERSE(CONCAT(a,' ',a))," ")) = a;
+a c
+abc abc abc
+xyz xyz xyz
+DROP TABLE t1;
+CREATE TABLE t1 (s varchar(10));
+INSERT INTO t1 VALUES ('yadda'), ('yaddy');
+EXPLAIN EXTENDED SELECT s FROM t1 WHERE TRIM(s) > 'ab';
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 ALL NULL NULL NULL NULL 2 Using where
+Warnings:
+Note 1003 select `test`.`t1`.`s` AS `s` from `test`.`t1` where (trim(`test`.`t1`.`s`) > _latin1'ab')
+EXPLAIN EXTENDED SELECT s FROM t1 WHERE TRIM('y' FROM s) > 'ab';
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 ALL NULL NULL NULL NULL 2 Using where
+Warnings:
+Note 1003 select `test`.`t1`.`s` AS `s` from `test`.`t1` where (trim(both _latin1'y' from `test`.`t1`.`s`) > _latin1'ab')
+EXPLAIN EXTENDED SELECT s FROM t1 WHERE TRIM(LEADING 'y' FROM s) > 'ab';
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 ALL NULL NULL NULL NULL 2 Using where
+Warnings:
+Note 1003 select `test`.`t1`.`s` AS `s` from `test`.`t1` where (trim(leading _latin1'y' from `test`.`t1`.`s`) > _latin1'ab')
+EXPLAIN EXTENDED SELECT s FROM t1 WHERE TRIM(TRAILING 'y' FROM s) > 'ab';
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 ALL NULL NULL NULL NULL 2 Using where
+Warnings:
+Note 1003 select `test`.`t1`.`s` AS `s` from `test`.`t1` where (trim(trailing _latin1'y' from `test`.`t1`.`s`) > _latin1'ab')
+EXPLAIN EXTENDED SELECT s FROM t1 WHERE TRIM(BOTH 'y' FROM s) > 'ab';
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 ALL NULL NULL NULL NULL 2 Using where
+Warnings:
+Note 1003 select `test`.`t1`.`s` AS `s` from `test`.`t1` where (trim(both _latin1'y' from `test`.`t1`.`s`) > _latin1'ab')
+DROP TABLE t1;
End of 4.1 tests
create table t1 (d decimal default null);
insert into t1 values (null);
--- 1.34/mysql-test/r/gis.result 2006-08-01 09:24:25 +04:00
+++ 1.35/mysql-test/r/gis.result 2006-08-01 09:24:25 +04:00
@@ -225,8 +225,8 @@
120 POLYGON((0 0,10 0,10 10,0 10,0 0))
121 POLYGON((3 6,44 6,44 9,3 9,3 6))
explain extended select Dimension(g), GeometryType(g), IsEmpty(g), AsText(Envelope(g)) from gis_geometry;
-id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE gis_geometry ALL NULL NULL NULL NULL 21
+id select_type table type possible_keys key key_len ref rows filtered Extra
+1 SIMPLE gis_geometry ALL NULL NULL NULL NULL 21 100.00
Warnings:
Note 1003 select dimension(`test`.`gis_geometry`.`g`) AS `Dimension(g)`,geometrytype(`test`.`gis_geometry`.`g`) AS `GeometryType(g)`,isempty(`test`.`gis_geometry`.`g`) AS `IsEmpty(g)`,astext(envelope(`test`.`gis_geometry`.`g`)) AS `AsText(Envelope(g))` from `test`.`gis_geometry`
SELECT fid, X(g) FROM gis_point;
@@ -242,8 +242,8 @@
103 20
104 20
explain extended select X(g),Y(g) FROM gis_point;
-id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE gis_point ALL NULL NULL NULL NULL 4
+id select_type table type possible_keys key key_len ref rows filtered Extra
+1 SIMPLE gis_point ALL NULL NULL NULL NULL 4 100.00
Warnings:
Note 1003 select x(`test`.`gis_point`.`g`) AS `X(g)`,y(`test`.`gis_point`.`g`) AS `Y(g)` from `test`.`gis_point`
SELECT fid, AsText(StartPoint(g)) FROM gis_line;
@@ -277,8 +277,8 @@
106 1
107 0
explain extended select AsText(StartPoint(g)),AsText(EndPoint(g)),GLength(g),NumPoints(g),AsText(PointN(g, 2)),IsClosed(g) FROM gis_line;
-id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE gis_line ALL NULL NULL NULL NULL 3
+id select_type table type possible_keys key key_len ref rows filtered Extra
+1 SIMPLE gis_line ALL NULL NULL NULL NULL 3 100.00
Warnings:
Note 1003 select astext(startpoint(`test`.`gis_line`.`g`)) AS `AsText(StartPoint(g))`,astext(endpoint(`test`.`gis_line`.`g`)) AS `AsText(EndPoint(g))`,glength(`test`.`gis_line`.`g`) AS `GLength(g)`,numpoints(`test`.`gis_line`.`g`) AS `NumPoints(g)`,astext(pointn(`test`.`gis_line`.`g`,2)) AS `AsText(PointN(g, 2))`,isclosed(`test`.`gis_line`.`g`) AS `IsClosed(g)` from `test`.`gis_line`
SELECT fid, AsText(Centroid(g)) FROM gis_polygon;
@@ -307,8 +307,8 @@
109 LINESTRING(10 10,20 10,20 20,10 20,10 10)
110 NULL
explain extended select AsText(Centroid(g)),Area(g),AsText(ExteriorRing(g)),NumInteriorRings(g),AsText(InteriorRingN(g, 1)) FROM gis_polygon;
-id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE gis_polygon ALL NULL NULL NULL NULL 3
+id select_type table type possible_keys key key_len ref rows filtered Extra
+1 SIMPLE gis_polygon ALL NULL NULL NULL NULL 3 100.00
Warnings:
Note 1003 select astext(centroid(`test`.`gis_polygon`.`g`)) AS `AsText(Centroid(g))`,area(`test`.`gis_polygon`.`g`) AS `Area(g)`,astext(exteriorring(`test`.`gis_polygon`.`g`)) AS `AsText(ExteriorRing(g))`,numinteriorrings(`test`.`gis_polygon`.`g`) AS `NumInteriorRings(g)`,astext(interiorringn(`test`.`gis_polygon`.`g`,1)) AS `AsText(InteriorRingN(g, 1))` from `test`.`gis_polygon`
SELECT fid, IsClosed(g) FROM gis_multi_line;
@@ -346,8 +346,8 @@
120 2
121 2
explain extended SELECT fid, NumGeometries(g) from gis_multi_point;
-id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE gis_multi_point ALL NULL NULL NULL NULL 3
+id select_type table type possible_keys key key_len ref rows filtered Extra
+1 SIMPLE gis_multi_point ALL NULL NULL NULL NULL 3 100.00
Warnings:
Note 1003 select `test`.`gis_multi_point`.`fid` AS `fid`,numgeometries(`test`.`gis_multi_point`.`g`) AS `NumGeometries(g)` from `test`.`gis_multi_point`
SELECT fid, AsText(GeometryN(g, 2)) from gis_multi_point;
@@ -374,8 +374,8 @@
120 POINT(0 0)
121 POINT(44 6)
explain extended SELECT fid, AsText(GeometryN(g, 2)) from gis_multi_point;
-id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE gis_multi_point ALL NULL NULL NULL NULL 3
+id select_type table type possible_keys key key_len ref rows filtered Extra
+1 SIMPLE gis_multi_point ALL NULL NULL NULL NULL 3 100.00
Warnings:
Note 1003 select `test`.`gis_multi_point`.`fid` AS `fid`,astext(geometryn(`test`.`gis_multi_point`.`g`,2)) AS `AsText(GeometryN(g, 2))` from `test`.`gis_multi_point`
SELECT g1.fid as first, g2.fid as second,
@@ -393,9 +393,9 @@
Equals(g1.g, g2.g) as e, Disjoint(g1.g, g2.g) as d, Touches(g1.g, g2.g) as t,
Intersects(g1.g, g2.g) as i, Crosses(g1.g, g2.g) as r
FROM gis_geometrycollection g1, gis_geometrycollection g2 ORDER BY first, second;
-id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE g1 ALL NULL NULL NULL NULL 2 Using temporary; Using filesort
-1 SIMPLE g2 ALL NULL NULL NULL NULL 2
+id select_type table type possible_keys key key_len ref rows filtered Extra
+1 SIMPLE g1 ALL NULL NULL NULL NULL 2 100.00 Using temporary; Using filesort
+1 SIMPLE g2 ALL NULL NULL NULL NULL 2 100.00
Warnings:
Note 1003 select `test`.`g1`.`fid` AS `first`,`test`.`g2`.`fid` AS `second`,within(`test`.`g1`.`g`,`test`.`g2`.`g`) AS `w`,contains(`test`.`g1`.`g`,`test`.`g2`.`g`) AS `c`,overlaps(`test`.`g1`.`g`,`test`.`g2`.`g`) AS `o`,equals(`test`.`g1`.`g`,`test`.`g2`.`g`) AS `e`,disjoint(`test`.`g1`.`g`,`test`.`g2`.`g`) AS `d`,touches(`test`.`g1`.`g`,`test`.`g2`.`g`) AS `t`,intersects(`test`.`g1`.`g`,`test`.`g2`.`g`) AS `i`,crosses(`test`.`g1`.`g`,`test`.`g2`.`g`) AS `r` from `test`.`gis_geometrycollection` `g1` join `test`.`gis_geometrycollection` `g2` order by `test`.`g1`.`fid`,`test`.`g2`.`fid`
DROP TABLE gis_point, gis_line, gis_polygon, gis_multi_point, gis_multi_line, gis_multi_polygon, gis_geometrycollection, gis_geometry;
@@ -436,26 +436,26 @@
AsText(GeometryFromWKB(AsWKB(GeometryFromText('POINT(1 4)'))))
POINT(1 4)
explain extended SELECT AsText(GeometryFromWKB(AsWKB(GeometryFromText('POINT(1 4)'))));
-id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE NULL NULL NULL NULL NULL NULL NULL No tables used
+id select_type table type possible_keys key key_len ref rows filtered Extra
+1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL No tables used
Warnings:
Note 1003 select astext(geometryfromwkb(aswkb(geometryfromtext(_latin1'POINT(1 4)')))) AS `AsText(GeometryFromWKB(AsWKB(GeometryFromText('POINT(1 4)'))))`
explain extended SELECT AsText(GeometryFromWKB(AsWKB(PointFromText('POINT(1 4)'))));
-id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE NULL NULL NULL NULL NULL NULL NULL No tables used
+id select_type table type possible_keys key key_len ref rows filtered Extra
+1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL No tables used
Warnings:
Note 1003 select astext(geometryfromwkb(aswkb(geometryfromtext(_latin1'POINT(1 4)')))) AS `AsText(GeometryFromWKB(AsWKB(PointFromText('POINT(1 4)'))))`
SELECT SRID(GeomFromText('LineString(1 1,2 2)',101));
SRID(GeomFromText('LineString(1 1,2 2)',101))
101
explain extended SELECT SRID(GeomFromText('LineString(1 1,2 2)',101));
-id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE NULL NULL NULL NULL NULL NULL NULL No tables used
+id select_type table type possible_keys key key_len ref rows filtered Extra
+1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL No tables used
Warnings:
Note 1003 select srid(geometryfromtext(_latin1'LineString(1 1,2 2)',101)) AS `SRID(GeomFromText('LineString(1 1,2 2)',101))`
explain extended select issimple(MultiPoint(Point(3, 6), Point(4, 10))), issimple(Point(3, 6));
-id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE NULL NULL NULL NULL NULL NULL NULL No tables used
+id select_type table type possible_keys key key_len ref rows filtered Extra
+1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL No tables used
Warnings:
Note 1003 select issimple(multipoint(point(3,6),point(4,10))) AS `issimple(MultiPoint(Point(3, 6), Point(4, 10)))`,issimple(point(3,6)) AS `issimple(Point(3, 6))`
create table t1 (a geometry not null);
--- 1.14/mysql-test/r/rpl_get_lock.result 2006-08-01 09:24:25 +04:00
+++ 1.15/mysql-test/r/rpl_get_lock.result 2006-08-01 09:24:25 +04:00
@@ -22,8 +22,8 @@
is_free_lock("lock") is_used_lock("lock") = connection_id()
0 1
explain extended select is_free_lock("lock"), is_used_lock("lock");
-id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE NULL NULL NULL NULL NULL NULL NULL No tables used
+id select_type table type possible_keys key key_len ref rows filtered Extra
+1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL No tables used
Warnings:
Note 1003 select is_free_lock(_latin1'lock') AS `is_free_lock("lock")`,is_used_lock(_latin1'lock') AS `is_used_lock("lock")`
select is_free_lock("lock2");
--- 1.20/mysql-test/r/partition_pruning.result 2006-08-01 09:24:25 +04:00
+++ 1.21/mysql-test/r/partition_pruning.result 2006-08-01 09:24:25 +04:00
@@ -538,27 +538,67 @@
explain partitions select * from t2 where b = 4;
id select_type table partitions type possible_keys key key_len ref rows Extra
1 SIMPLE t2 p0,p1,p2,p3,p4 ref b b 5 const 76 Using where
+explain extended select * from t2 where b = 6;
+id select_type table type possible_keys key key_len ref rows filtered Extra
+1 SIMPLE t2 ref b b 5 const 76 100.00 Using where
+Warnings:
+Note 1003 select `test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b` from `test`.`t2` where (`test`.`t2`.`b` = 6)
explain partitions select * from t2 where b = 6;
id select_type table partitions type possible_keys key key_len ref rows Extra
1 SIMPLE t2 p0,p1,p2,p3,p4 ref b b 5 const 76 Using where
+explain extended select * from t2 where b in (1,3,5);
+id select_type table type possible_keys key key_len ref rows filtered Extra
+1 SIMPLE t2 ALL b NULL NULL NULL 910 40.66 Using where
+Warnings:
+Note 1003 select `test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b` from `test`.`t2` where (`test`.`t2`.`b` in (1,3,5))
explain partitions select * from t2 where b in (1,3,5);
id select_type table partitions type possible_keys key key_len ref rows Extra
1 SIMPLE t2 p0,p1,p2,p3,p4 ALL b NULL NULL NULL 910 Using where
+explain extended select * from t2 where b in (2,4,6);
+id select_type table type possible_keys key key_len ref rows filtered Extra
+1 SIMPLE t2 ALL b NULL NULL NULL 910 25.05 Using where
+Warnings:
+Note 1003 select `test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b` from `test`.`t2` where (`test`.`t2`.`b` in (2,4,6))
explain partitions select * from t2 where b in (2,4,6);
id select_type table partitions type possible_keys key key_len ref rows Extra
1 SIMPLE t2 p0,p1,p2,p3,p4 ALL b NULL NULL NULL 910 Using where
+explain extended select * from t2 where b in (7,8,9);
+id select_type table type possible_keys key key_len ref rows filtered Extra
+1 SIMPLE t2 ALL b NULL NULL NULL 910 36.70 Using where
+Warnings:
+Note 1003 select `test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b` from `test`.`t2` where (`test`.`t2`.`b` in (7,8,9))
explain partitions select * from t2 where b in (7,8,9);
id select_type table partitions type possible_keys key key_len ref rows Extra
1 SIMPLE t2 p0,p1,p2,p3,p4 ALL b NULL NULL NULL 910 Using where
+explain extended select * from t2 where b > 5;
+id select_type table type possible_keys key key_len ref rows filtered Extra
+1 SIMPLE t2 ALL b NULL NULL NULL 910 44.84 Using where
+Warnings:
+Note 1003 select `test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b` from `test`.`t2` where (`test`.`t2`.`b` > 5)
explain partitions select * from t2 where b > 5;
id select_type table partitions type possible_keys key key_len ref rows Extra
1 SIMPLE t2 p0,p1,p2,p3,p4 ALL b NULL NULL NULL 910 Using where
+explain extended select * from t2 where b > 5 and b < 8;
+id select_type table type possible_keys key key_len ref rows filtered Extra
+1 SIMPLE t2 ALL b NULL NULL NULL 910 22.09 Using where
+Warnings:
+Note 1003 select `test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b` from `test`.`t2` where ((`test`.`t2`.`b` > 5) and (`test`.`t2`.`b` < 8))
explain partitions select * from t2 where b > 5 and b < 8;
id select_type table partitions type possible_keys key key_len ref rows Extra
1 SIMPLE t2 p0,p1,p2,p3,p4 ALL b NULL NULL NULL 910 Using where
+explain extended select * from t2 where b > 5 and b < 7;
+id select_type table type possible_keys key key_len ref rows filtered Extra
+1 SIMPLE t2 range b b 5 NULL 76 100.00 Using where
+Warnings:
+Note 1003 select `test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b` from `test`.`t2` where ((`test`.`t2`.`b` > 5) and (`test`.`t2`.`b` < 7))
explain partitions select * from t2 where b > 5 and b < 7;
id select_type table partitions type possible_keys key key_len ref rows Extra
1 SIMPLE t2 p0,p1,p2,p3,p4 range b b 5 NULL 76 Using where
+explain extended select * from t2 where b > 0 and b < 5;
+id select_type table type possible_keys key key_len ref rows filtered Extra
+1 SIMPLE t2 ALL b NULL NULL NULL 910 41.65 Using where
+Warnings:
+Note 1003 select `test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b` from `test`.`t2` where ((`test`.`t2`.`b` > 0) and (`test`.`t2`.`b` < 5))
explain partitions select * from t2 where b > 0 and b < 5;
id select_type table partitions type possible_keys key key_len ref rows Extra
1 SIMPLE t2 p0,p1,p2,p3,p4 ALL b NULL NULL NULL 910 Using where
--- 1.17/mysql-test/t/partition_pruning.test 2006-08-01 09:24:25 +04:00
+++ 1.18/mysql-test/t/partition_pruning.test 2006-08-01 09:24:25 +04:00
@@ -459,13 +459,29 @@
explain partitions select * from t2 where (a = 100 OR a = 900);
explain partitions select * from t2 where (a > 100 AND a < 600);
explain partitions select * from t2 where b = 4;
+
+explain extended select * from t2 where b = 6;
explain partitions select * from t2 where b = 6;
+
+explain extended select * from t2 where b in (1,3,5);
explain partitions select * from t2 where b in (1,3,5);
+
+explain extended select * from t2 where b in (2,4,6);
explain partitions select * from t2 where b in (2,4,6);
+
+explain extended select * from t2 where b in (7,8,9);
explain partitions select * from t2 where b in (7,8,9);
+
+explain extended select * from t2 where b > 5;
explain partitions select * from t2 where b > 5;
+
+explain extended select * from t2 where b > 5 and b < 8;
explain partitions select * from t2 where b > 5 and b < 8;
+
+explain extended select * from t2 where b > 5 and b < 7;
explain partitions select * from t2 where b > 5 and b < 7;
+
+explain extended select * from t2 where b > 0 and b < 5;
explain partitions select * from t2 where b > 0 and b < 5;
flush status;
--- 1.26/mysql-test/r/group_min_max.result 2006-08-01 09:24:25 +04:00
+++ 1.27/mysql-test/r/group_min_max.result 2006-08-01 09:24:25 +04:00
@@ -1558,9 +1558,11 @@
explain select distinct a1,a2,b from t1 where (a2 >= 'b') and (b = 'a');
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 range NULL idx_t1_1 147 NULL 17 Using where; Using index for group-by
-explain select distinct a1,a2,b,c from t1 where (a2 >= 'b') and (b = 'a') and (c = 'i121');
-id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 index NULL idx_t1_1 163 NULL 128 Using where; Using index
+explain extended select distinct a1,a2,b,c from t1 where (a2 >= 'b') and (b = 'a') and (c = 'i121');
+id select_type table type possible_keys key key_len ref rows filtered Extra
+1 SIMPLE t1 index NULL idx_t1_1 163 NULL 128 50.78 Using where; Using index
+Warnings:
+Note 1003 select distinct `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2`,`test`.`t1`.`b` AS `b`,`test`.`t1`.`c` AS `c` from `test`.`t1` where ((`test`.`t1`.`c` = _latin1'i121') and (`test`.`t1`.`b` = _latin1'a') and (`test`.`t1`.`a2` >= _latin1'b'))
explain select distinct a1,a2,b from t1 where (a1 > 'a') and (a2 > 'a') and (b = 'c');
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 range idx_t1_0,idx_t1_1,idx_t1_2 idx_t1_1 147 NULL 14 Using where; Using index for group-by
@@ -1573,9 +1575,11 @@
explain select distinct a1,a2,b from t2 where (a2 >= 'b') and (b = 'a');
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t2 range NULL idx_t2_1 146 NULL # Using where; Using index for group-by
-explain select distinct a1,a2,b,c from t2 where (a2 >= 'b') and (b = 'a') and (c = 'i121');
-id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t2 index NULL idx_t2_1 163 NULL 164 Using where; Using index
+explain extended select distinct a1,a2,b,c from t2 where (a2 >= 'b') and (b = 'a') and (c = 'i121');
+id select_type table type possible_keys key key_len ref rows filtered Extra
+1 SIMPLE t2 index NULL idx_t2_1 163 NULL 164 50.61 Using where; Using index
+Warnings:
+Note 1003 select distinct `test`.`t2`.`a1` AS `a1`,`test`.`t2`.`a2` AS `a2`,`test`.`t2`.`b` AS `b`,`test`.`t2`.`c` AS `c` from `test`.`t2` where ((`test`.`t2`.`c` = _latin1'i121') and (`test`.`t2`.`b` = _latin1'a') and (`test`.`t2`.`a2` >= _latin1'b'))
explain select distinct a1,a2,b from t2 where (a1 > 'a') and (a2 > 'a') and (b = 'c');
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t2 range idx_t2_0,idx_t2_1,idx_t2_2 idx_t2_1 146 NULL # Using where; Using index for group-by
@@ -1800,15 +1804,19 @@
explain select count(distinct a1,a2,b,c) from t1 where (a2 >= 'b') and (b = 'a') and (c = 'i121');
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 index NULL idx_t1_1 163 NULL 128 Using where; Using index
-explain select count(distinct a1,a2,b) from t1 where (a1 > 'a') and (a2 > 'a') and (b = 'c');
-id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 index idx_t1_0,idx_t1_1,idx_t1_2 idx_t1_2 147 NULL 128 Using where; Using index
+explain extended select count(distinct a1,a2,b) from t1 where (a1 > 'a') and (a2 > 'a') and (b = 'c');
+id select_type table type possible_keys key key_len ref rows filtered Extra
+1 SIMPLE t1 index idx_t1_0,idx_t1_1,idx_t1_2 idx_t1_2 147 NULL 128 75.00 Using where; Using index
+Warnings:
+Note 1003 select count(distinct `test`.`t1`.`a1`,`test`.`t1`.`a2`,`test`.`t1`.`b`) AS `count(distinct a1,a2,b)` from `test`.`t1` where ((`test`.`t1`.`b` = _latin1'c') and (`test`.`t1`.`a1` > _latin1'a') and (`test`.`t1`.`a2` > _latin1'a'))
explain select count(distinct b) from t1 where (a2 >= 'b') and (b = 'a');
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 index NULL idx_t1_2 147 NULL 128 Using where; Using index
-explain select ord(a1) + count(distinct a1,a2,b) from t1 where (a1 > 'a') and (a2 > 'a');
-id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 index idx_t1_0,idx_t1_1,idx_t1_2 idx_t1_2 147 NULL 128 Using where; Using index
+explain extended select ord(a1) + count(distinct a1,a2,b) from t1 where (a1 > 'a') and (a2 > 'a');
+id select_type table type possible_keys key key_len ref rows filtered Extra
+1 SIMPLE t1 index idx_t1_0,idx_t1_1,idx_t1_2 idx_t1_2 147 NULL 128 75.00 Using where; Using index
+Warnings:
+Note 1003 select (ord(`test`.`t1`.`a1`) + count(distinct `test`.`t1`.`a1`,`test`.`t1`.`a2`,`test`.`t1`.`b`)) AS `ord(a1) + count(distinct a1,a2,b)` from `test`.`t1` where ((`test`.`t1`.`a1` > _latin1'a') and (`test`.`t1`.`a2` > _latin1'a'))
select count(distinct a1,a2,b) from t1 where (a2 >= 'b') and (b = 'a');
count(distinct a1,a2,b)
4
@@ -1911,24 +1919,32 @@
explain select a1,a2,b,d from t1 group by a1,a2,b;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 ALL NULL NULL NULL NULL 128 Using temporary; Using filesort
-explain select a1,a2,min(b),max(b) from t1
+explain extended select a1,a2,min(b),max(b) from t1
where (a1 = 'b' or a1 = 'd' or a1 = 'a' or a1 = 'c') and (a2 > 'a') and (c > 'a111') group by a1,a2;
-id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 range idx_t1_0,idx_t1_1,idx_t1_2 idx_t1_1 130 NULL 76 Using where; Using index
-explain select a1,a2,b,min(c),max(c) from t1
+id select_type table type possible_keys key key_len ref rows filtered Extra
+1 SIMPLE t1 range idx_t1_0,idx_t1_1,idx_t1_2 idx_t1_1 130 NULL 76 85.53 Using where; Using index
+Warnings:
+Note 1003 select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2`,min(`test`.`t1`.`b`) AS `min(b)`,max(`test`.`t1`.`b`) AS `max(b)` from `test`.`t1` where (((`test`.`t1`.`a1` = _latin1'b') or (`test`.`t1`.`a1` = _latin1'd') or (`test`.`t1`.`a1` = _latin1'a') or (`test`.`t1`.`a1` = _latin1'c')) and (`test`.`t1`.`a2` > _latin1'a') and (`test`.`t1`.`c` > _latin1'a111')) group by `test`.`t1`.`a1`,`test`.`t1`.`a2`
+explain extended select a1,a2,b,min(c),max(c) from t1
where (a1 = 'b' or a1 = 'd' or a1 = 'a' or a1 = 'c') and (a2 > 'a') and (d > 'xy2') group by a1,a2,b;
-id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 ALL idx_t1_0,idx_t1_1,idx_t1_2 NULL NULL NULL 128 Using where; Using temporary; Using filesort
-explain select a1,a2,b,c from t1
+id select_type table type possible_keys key key_len ref rows filtered Extra
+1 SIMPLE t1 ALL idx_t1_0,idx_t1_1,idx_t1_2 NULL NULL NULL 128 50.78 Using where; Using temporary; Using filesort
+Warnings:
+Note 1003 select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2`,`test`.`t1`.`b` AS `b`,min(`test`.`t1`.`c`) AS `min(c)`,max(`test`.`t1`.`c`) AS `max(c)` from `test`.`t1` where (((`test`.`t1`.`a1` = _latin1'b') or (`test`.`t1`.`a1` = _latin1'd') or (`test`.`t1`.`a1` = _latin1'a') or (`test`.`t1`.`a1` = _latin1'c')) and (`test`.`t1`.`a2` > _latin1'a') and (`test`.`t1`.`d` > _latin1'xy2')) group by `test`.`t1`.`a1`,`test`.`t1`.`a2`,`test`.`t1`.`b`
+explain extended select a1,a2,b,c from t1
where (a1 = 'b' or a1 = 'd' or a1 = 'a' or a1 = 'c') and (a2 > 'a') and (d > 'xy2') group by a1,a2,b,c;
-id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 ALL idx_t1_0,idx_t1_1,idx_t1_2 NULL NULL NULL 128 Using where; Using temporary; Using filesort
+id select_type table type possible_keys key key_len ref rows filtered Extra
+1 SIMPLE t1 ALL idx_t1_0,idx_t1_1,idx_t1_2 NULL NULL NULL 128 50.78 Using where; Using temporary; Using filesort
+Warnings:
+Note 1003 select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2`,`test`.`t1`.`b` AS `b`,`test`.`t1`.`c` AS `c` from `test`.`t1` where (((`test`.`t1`.`a1` = _latin1'b') or (`test`.`t1`.`a1` = _latin1'd') or (`test`.`t1`.`a1` = _latin1'a') or (`test`.`t1`.`a1` = _latin1'c')) and (`test`.`t1`.`a2` > _latin1'a') and (`test`.`t1`.`d` > _latin1'xy2')) group by `test`.`t1`.`a1`,`test`.`t1`.`a2`,`test`.`t1`.`b`,`test`.`t1`.`c`
explain select a1,a2,b,max(c),min(c) from t2 where (a2 = 'a') and (b = 'b') or (b < 'b') group by a1;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t2 index NULL idx_t2_1 163 NULL 164 Using where; Using index
-explain select a1,a2,b from t1 where (a1 = 'b' or a1 = 'd' or a1 = 'a' or a1 = 'c') and (a2 > 'a') and (c > 'a111') group by a1,a2,b;
-id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 range idx_t1_0,idx_t1_1,idx_t1_2 idx_t1_1 130 NULL 76 Using where; Using index
+explain extended select a1,a2,b from t1 where (a1 = 'b' or a1 = 'd' or a1 = 'a' or a1 = 'c') and (a2 > 'a') and (c > 'a111') group by a1,a2,b;
+id select_type table type possible_keys key key_len ref rows filtered Extra
+1 SIMPLE t1 range idx_t1_0,idx_t1_1,idx_t1_2 idx_t1_1 130 NULL 76 85.53 Using where; Using index
+Warnings:
+Note 1003 select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2`,`test`.`t1`.`b` AS `b` from `test`.`t1` where (((`test`.`t1`.`a1` = _latin1'b') or (`test`.`t1`.`a1` = _latin1'd') or (`test`.`t1`.`a1` = _latin1'a') or (`test`.`t1`.`a1` = _latin1'c')) and (`test`.`t1`.`a2` > _latin1'a') and (`test`.`t1`.`c` > _latin1'a111')) group by `test`.`t1`.`a1`,`test`.`t1`.`a2`,`test`.`t1`.`b`
explain select a1,a2,min(b),c from t2 where (a2 = 'a') and (c = 'a111') group by a1;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t2 index NULL idx_t2_1 163 NULL 164 Using where; Using index
@@ -1948,12 +1964,16 @@
explain select a1,a2,count(a2) from t1 group by a1,a2,b;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 index NULL idx_t1_1 163 NULL 128 Using index
-explain select a1,a2,count(a2) from t1 where (a1 > 'a') group by a1,a2,b;
-id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 index idx_t1_0,idx_t1_1,idx_t1_2 idx_t1_1 163 NULL 128 Using where; Using index
-explain select sum(ord(a1)) from t1 where (a1 > 'a') group by a1,a2,b;
-id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 index idx_t1_0,idx_t1_1,idx_t1_2 idx_t1_1 163 NULL 128 Using where; Using index
+explain extended select a1,a2,count(a2) from t1 where (a1 > 'a') group by a1,a2,b;
+id select_type table type possible_keys key key_len ref rows filtered Extra
+1 SIMPLE t1 index idx_t1_0,idx_t1_1,idx_t1_2 idx_t1_1 163 NULL 128 75.00 Using where; Using index
+Warnings:
+Note 1003 select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2`,count(`test`.`t1`.`a2`) AS `count(a2)` from `test`.`t1` where (`test`.`t1`.`a1` > _latin1'a') group by `test`.`t1`.`a1`,`test`.`t1`.`a2`,`test`.`t1`.`b`
+explain extended select sum(ord(a1)) from t1 where (a1 > 'a') group by a1,a2,b;
+id select_type table type possible_keys key key_len ref rows filtered Extra
+1 SIMPLE t1 index idx_t1_0,idx_t1_1,idx_t1_2 idx_t1_1 163 NULL 128 75.00 Using where; Using index
+Warnings:
+Note 1003 select sum(ord(`test`.`t1`.`a1`)) AS `sum(ord(a1))` from `test`.`t1` where (`test`.`t1`.`a1` > _latin1'a') group by `test`.`t1`.`a1`,`test`.`t1`.`a2`,`test`.`t1`.`b`
explain select distinct(a1) from t1 where ord(a2) = 98;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 index NULL idx_t1_1 163 NULL 128 Using where; Using index
--- 1.26/mysql-test/t/group_min_max.test 2006-08-01 09:24:25 +04:00
+++ 1.27/mysql-test/t/group_min_max.test 2006-08-01 09:24:25 +04:00
@@ -489,7 +489,7 @@
-- plans
explain select distinct a1,a2,b from t1;
explain select distinct a1,a2,b from t1 where (a2 >= 'b') and (b = 'a');
-explain select distinct a1,a2,b,c from t1 where (a2 >= 'b') and (b = 'a') and (c = 'i121');
+explain extended select distinct a1,a2,b,c from t1 where (a2 >= 'b') and (b = 'a') and (c = 'i121');
explain select distinct a1,a2,b from t1 where (a1 > 'a') and (a2 > 'a') and (b = 'c');
explain select distinct b from t1 where (a2 >= 'b') and (b = 'a');
@@ -497,7 +497,7 @@
explain select distinct a1,a2,b from t2;
--replace_column 9 #
explain select distinct a1,a2,b from t2 where (a2 >= 'b') and (b = 'a');
-explain select distinct a1,a2,b,c from t2 where (a2 >= 'b') and (b = 'a') and (c = 'i121');
+explain extended select distinct a1,a2,b,c from t2 where (a2 >= 'b') and (b = 'a') and (c = 'i121');
--replace_column 9 #
explain select distinct a1,a2,b from t2 where (a1 > 'a') and (a2 > 'a') and (b = 'c');
explain select distinct b from t2 where (a2 >= 'b') and (b = 'a');
@@ -568,9 +568,9 @@
explain select count(distinct a1,a2,b) from t1 where (a2 >= 'b') and (b = 'a');
explain select count(distinct a1,a2,b,c) from t1 where (a2 >= 'b') and (b = 'a') and (c = 'i121');
-explain select count(distinct a1,a2,b) from t1 where (a1 > 'a') and (a2 > 'a') and (b = 'c');
+explain extended select count(distinct a1,a2,b) from t1 where (a1 > 'a') and (a2 > 'a') and (b = 'c');
explain select count(distinct b) from t1 where (a2 >= 'b') and (b = 'a');
-explain select ord(a1) + count(distinct a1,a2,b) from t1 where (a1 > 'a') and (a2 > 'a');
+explain extended select ord(a1) + count(distinct a1,a2,b) from t1 where (a1 > 'a') and (a2 > 'a');
select count(distinct a1,a2,b) from t1 where (a2 >= 'b') and (b = 'a');
select count(distinct a1,a2,b,c) from t1 where (a2 >= 'b') and (b = 'a') and (c = 'i121');
@@ -607,19 +607,19 @@
-- predicate that references an attribute that is after the MIN/MAX argument
-- in the index
-explain select a1,a2,min(b),max(b) from t1
+explain extended select a1,a2,min(b),max(b) from t1
where (a1 = 'b' or a1 = 'd' or a1 = 'a' or a1 = 'c') and (a2 > 'a') and (c > 'a111') group by a1,a2;
-- predicate that references a non-indexed attribute
-explain select a1,a2,b,min(c),max(c) from t1
+explain extended select a1,a2,b,min(c),max(c) from t1
where (a1 = 'b' or a1 = 'd' or a1 = 'a' or a1 = 'c') and (a2 > 'a') and (d > 'xy2') group by a1,a2,b;
-explain select a1,a2,b,c from t1
+explain extended select a1,a2,b,c from t1
where (a1 = 'b' or a1 = 'd' or a1 = 'a' or a1 = 'c') and (a2 > 'a') and (d > 'xy2') group by a1,a2,b,c;
-- non-equality predicate for a non-group select attribute
explain select a1,a2,b,max(c),min(c) from t2 where (a2 = 'a') and (b = 'b') or (b < 'b') group by a1;
-explain select a1,a2,b from t1 where (a1 = 'b' or a1 = 'd' or a1 = 'a' or a1 = 'c') and (a2 > 'a') and (c > 'a111') group by a1,a2,b;
+explain extended select a1,a2,b from t1 where (a1 = 'b' or a1 = 'd' or a1 = 'a' or a1 = 'c') and (a2 > 'a') and (c > 'a111') group by a1,a2,b;
-- non-group field with an equality predicate that references a keypart after the
-- MIN/MAX argument
@@ -638,8 +638,8 @@
-- other aggregate functions than MIN/MAX
explain select a1,a2,count(a2) from t1 group by a1,a2,b;
-explain select a1,a2,count(a2) from t1 where (a1 > 'a') group by a1,a2,b;
-explain select sum(ord(a1)) from t1 where (a1 > 'a') group by a1,a2,b;
+explain extended select a1,a2,count(a2) from t1 where (a1 > 'a') group by a1,a2,b;
+explain extended select sum(ord(a1)) from t1 where (a1 > 'a') group by a1,a2,b;
#
--- 1.17/mysql-test/r/func_compress.result 2006-08-01 09:24:25 +04:00
+++ 1.18/mysql-test/r/func_compress.result 2006-08-01 09:24:25 +04:00
@@ -8,16 +8,16 @@
uncompress(compress(@test_compress_string))
string for test compress function aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
explain extended select uncompress(compress(@test_compress_string));
-id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE NULL NULL NULL NULL NULL NULL NULL No tables used
+id select_type table type possible_keys key key_len ref rows filtered Extra
+1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL No tables used
Warnings:
Note 1003 select uncompress(compress((@test_compress_string))) AS `uncompress(compress(@test_compress_string))`
select uncompressed_length(compress(@test_compress_string))=length(@test_compress_string);
uncompressed_length(compress(@test_compress_string))=length(@test_compress_string)
1
explain extended select uncompressed_length(compress(@test_compress_string))=length(@test_compress_string);
-id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE NULL NULL NULL NULL NULL NULL NULL No tables used
+id select_type table type possible_keys key key_len ref rows filtered Extra
+1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL No tables used
Warnings:
Note 1003 select (uncompressed_length(compress((@test_compress_string))) = length((@test_compress_string))) AS `uncompressed_length(compress(@test_compress_string))=length(@test_compress_string)`
select uncompressed_length(compress(@test_compress_string));
--- 1.58/mysql-test/r/func_gconcat.result 2006-08-01 09:24:25 +04:00
+++ 1.59/mysql-test/r/func_gconcat.result 2006-08-01 09:24:25 +04:00
@@ -647,3 +647,16 @@
charset(group_concat(c1 order by c2))
latin1
drop table t1;
+CREATE TABLE t1 (a INT(10), b LONGTEXT, PRIMARY KEY (a));
+SET GROUP_CONCAT_MAX_LEN = 20000000;
+INSERT INTO t1 VALUES (1,REPEAT(CONCAT('A',CAST(CHAR(0) AS BINARY),'B'), 40000));
+INSERT INTO t1 SELECT a + 1, b FROM t1;
+SELECT a, CHAR_LENGTH(b) FROM t1;
+a CHAR_LENGTH(b)
+1 120000
+2 120000
+SELECT CHAR_LENGTH( GROUP_CONCAT(b) ) FROM t1;
+CHAR_LENGTH( GROUP_CONCAT(b) )
+240001
+SET GROUP_CONCAT_MAX_LEN = 1024;
+DROP TABLE t1;
| Thread |
|---|
| • bk commit into 5.1 tree (evgen:1.2250) | eugene | 1 Aug |
