diff options
| author | unknown <bell@sanja.is.com.ua> | 2002-09-26 23:08:22 +0300 |
|---|---|---|
| committer | unknown <bell@sanja.is.com.ua> | 2002-09-26 23:08:22 +0300 |
| commit | 9396cc5a4a063f2cccd83721932abb98ce238459 (patch) | |
| tree | 811744b79667e1e3e450406097ddfc1a03a886db | |
| parent | 9dea4a4cd1ac246004dc9d223cfc606a7dea5567 (diff) | |
| download | mariadb-git-9396cc5a4a063f2cccd83721932abb98ce238459.tar.gz | |
new EXPLAIN
fixed bug in mysql-test/create-test-result
fixed bug in union-subselect engine
mysql-test/create-test-result:
fixed bug in reject file name assembling
mysql-test/r/compare.result:
new EXPLAIN
mysql-test/r/create.result:
new EXPLAIN
mysql-test/r/distinct.result:
new EXPLAIN
mysql-test/r/explain.result:
new EXPLAIN
mysql-test/r/group_by.result:
new EXPLAIN
mysql-test/r/heap.result:
new EXPLAIN
mysql-test/r/heap_btree.result:
new EXPLAIN
mysql-test/r/heap_hash.result:
new EXPLAIN
mysql-test/r/innodb.result:
new EXPLAIN
mysql-test/r/join_outer.result:
new EXPLAIN
mysql-test/r/key_diff.result:
new EXPLAIN
mysql-test/r/key_primary.result:
new EXPLAIN
mysql-test/r/merge.result:
new EXPLAIN
mysql-test/r/myisam.result:
new EXPLAIN
mysql-test/r/null_key.result:
new EXPLAIN
mysql-test/r/odbc.result:
new EXPLAIN
mysql-test/r/order_by.result:
new EXPLAIN
mysql-test/r/range.result:
new EXPLAIN
mysql-test/r/select.result:
new EXPLAIN
mysql-test/r/subselect.result:
new EXPLAIN
mysql-test/r/type_datetime.result:
new EXPLAIN
mysql-test/r/union.result:
new EXPLAIN
mysql-test/r/user_var.result:
new EXPLAIN
mysql-test/r/varbinary.result:
new EXPLAIN
mysql-test/t/subselect.test:
new EXPLAIN
mysql-test/t/union.test:
new EXPLAIN
sql/mysql_priv.h:
new EXPLAIN
sql/sql_class.cc:
new EXPLAIN
sql/sql_class.h:
new EXPLAIN
sql/sql_derived.cc:
new EXPLAIN
sql/sql_lex.h:
new EXPLAIN
sql/sql_parse.cc:
new EXPLAIN
sql/sql_select.cc:
new EXPLAIN
sql/sql_union.cc:
fixed bug in subselect-UNION engine
sql/table.h:
new EXPLAIN
36 files changed, 607 insertions, 457 deletions
diff --git a/mysql-test/create-test-result b/mysql-test/create-test-result index a50b4c87641..b9be2300976 100755 --- a/mysql-test/create-test-result +++ b/mysql-test/create-test-result @@ -27,6 +27,7 @@ test_name=$1 [ -z $test_name ] && usage result_file=$RESULT_DIR/$test_name.result +reject_file=$RESULT_DIR/$test_name.reject [ -f $result_file ] && die "result file $result_file has already been created" @@ -34,8 +35,6 @@ touch $result_file echo "Running the test case against empty file, will fail, but don't worry" ./mysql-test-run --local $test_name -reject_file=$result_file.reject - if [ -f $reject_file ] ; then echo "Below are the contents of the reject file:" echo "-----start---------------------" diff --git a/mysql-test/r/compare.result b/mysql-test/r/compare.result index 07f9ce4e81a..be11de8ebb9 100644 --- a/mysql-test/r/compare.result +++ b/mysql-test/r/compare.result @@ -2,8 +2,8 @@ drop table if exists t1; CREATE TABLE t1 (id CHAR(12) not null, PRIMARY KEY (id)); insert into t1 values ('000000000001'),('000000000002'); explain select * from t1 where id=000000000001; -table type possible_keys key key_len ref rows Extra -t1 index PRIMARY PRIMARY 12 NULL 2 where used; Using index +id select_type table type possible_keys key key_len ref rows Extra +1 FIRST t1 index PRIMARY PRIMARY 12 NULL 2 where used; Using index select * from t1 where id=000000000001; id 000000000001 diff --git a/mysql-test/r/create.result b/mysql-test/r/create.result index f9a3ef487f0..41a977695a6 100644 --- a/mysql-test/r/create.result +++ b/mysql-test/r/create.result @@ -62,8 +62,8 @@ create table t1 (a int auto_increment not null primary key, B CHAR(20)); insert into t1 (b) values ("hello"),("my"),("world"); create table t2 (key (b)) select * from t1; explain select * from t2 where b="world"; -table type possible_keys key key_len ref rows Extra -t2 ref B B 21 const 1 where used +id select_type table type possible_keys key key_len ref rows Extra +1 FIRST t2 ref B B 21 const 1 where used select * from t2 where b="world"; a B 3 world diff --git a/mysql-test/r/distinct.result b/mysql-test/r/distinct.result index 89f4a8ae7f2..d8386ddcfc3 100644 --- a/mysql-test/r/distinct.result +++ b/mysql-test/r/distinct.result @@ -172,10 +172,10 @@ b INSERT INTO t2 values (1),(2),(3); INSERT INTO t3 VALUES (1,'1'),(2,'2'),(1,'1'),(2,'2'); explain SELECT distinct t3.a FROM t3,t2,t1 WHERE t3.a=t1.b AND t1.a=t2.a; -table type possible_keys key key_len ref rows Extra -t3 index a a 5 NULL 6 Using index; Using temporary -t2 index a a 4 NULL 5 Using index; Distinct -t1 eq_ref PRIMARY PRIMARY 4 t2.a 1 where used; Distinct +id select_type table type possible_keys key key_len ref rows Extra +1 FIRST t3 index a a 5 NULL 6 Using index; Using temporary +1 FIRST t2 index a a 4 NULL 5 Using index; Distinct +1 FIRST t1 eq_ref PRIMARY PRIMARY 4 t2.a 1 where used; Distinct SELECT distinct t3.a FROM t3,t2,t1 WHERE t3.a=t1.b AND t1.a=t2.a; a 1 @@ -188,9 +188,9 @@ insert into t3 select * from t4; insert into t4 select * from t3; insert into t3 select * from t4; explain select distinct t1.a from t1,t3 where t1.a=t3.a; -table type possible_keys key key_len ref rows Extra -t1 index PRIMARY PRIMARY 4 NULL 2 Using index; Using temporary -t3 ref a a 5 t1.a 10 Using index; Distinct +id select_type table type possible_keys key key_len ref rows Extra +1 FIRST t1 index PRIMARY PRIMARY 4 NULL 2 Using index; Using temporary +1 FIRST t3 ref a a 5 t1.a 10 Using index; Distinct select distinct t1.a from t1,t3 where t1.a=t3.a; a 1 @@ -274,14 +274,14 @@ on j_lj_t3.id=t3_lj.id WHERE ((t1.id=j_lj_t2.id AND t2_lj.id IS NULL) OR (t1.id=t2.id AND t2.idx=2)) AND ((t1.id=j_lj_t3.id AND t3_lj.id IS NULL) OR (t1.id=t3.id AND t3.idx=2)); -table type possible_keys key key_len ref rows Extra -t1 index id id 4 NULL 2 Using index; Using temporary -t2 index id id 8 NULL 1 Using index; Distinct -t3 index id id 8 NULL 1 Using index; Distinct -j_lj_t2 index id id 4 NULL 2 where used; Using index; Distinct -t2_lj index id id 8 NULL 1 where used; Using index; Distinct -j_lj_t3 index id id 4 NULL 2 where used; Using index; Distinct -t3_lj index id id 8 NULL 1 where used; Using index; Distinct +id select_type table type possible_keys key key_len ref rows Extra +1 FIRST t1 index id id 4 NULL 2 Using index; Using temporary +1 FIRST t2 index id id 8 NULL 1 Using index; Distinct +1 FIRST t3 index id id 8 NULL 1 Using index; Distinct +1 FIRST j_lj_t2 index id id 4 NULL 2 where used; Using index; Distinct +1 FIRST t2_lj index id id 8 NULL 1 where used; Using index; Distinct +1 FIRST j_lj_t3 index id id 4 NULL 2 where used; Using index; Distinct +1 FIRST t3_lj index id id 8 NULL 1 where used; Using index; Distinct SELECT DISTINCT t1.id from diff --git a/mysql-test/r/explain.result b/mysql-test/r/explain.result index 5b4da25d535..cd0ddeae120 100644 --- a/mysql-test/r/explain.result +++ b/mysql-test/r/explain.result @@ -9,22 +9,22 @@ select * from t1 where str="foo"; id str 3 foo explain select * from t1 where str is null; -table type possible_keys key key_len ref rows Extra -t1 ref str str 11 const 1 where used +id select_type table type possible_keys key key_len ref rows Extra +1 FIRST t1 ref str str 11 const 1 where used explain select * from t1 where str="foo"; -table type possible_keys key key_len ref rows Extra -t1 const str str 11 const 1 +id select_type table type possible_keys key key_len ref rows Extra +1 FIRST t1 const str str 11 const 1 explain select * from t1 ignore key (str) where str="foo"; -table type possible_keys key key_len ref rows Extra -t1 ALL NULL NULL NULL NULL 4 where used +id select_type table type possible_keys key key_len ref rows Extra +1 FIRST t1 ALL NULL NULL NULL NULL 4 where used explain select * from t1 use key (str,str) where str="foo"; -table type possible_keys key key_len ref rows Extra -t1 const str str 11 const 1 +id select_type table type possible_keys key key_len ref rows Extra +1 FIRST t1 const str str 11 const 1 explain select * from t1 use key (str,str,foo) where str="foo"; Key column 'foo' doesn't exist in table explain select * from t1 ignore key (str,str,foo) where str="foo"; Key column 'foo' doesn't exist in table drop table t1; explain select 1; -Comment -No tables used +id select_type table type possible_keys key key_len ref rows Extra +1 FIRST No tables used diff --git a/mysql-test/r/group_by.result b/mysql-test/r/group_by.result index aaa03f2668a..0781468bc97 100644 --- a/mysql-test/r/group_by.result +++ b/mysql-test/r/group_by.result @@ -225,19 +225,19 @@ key (score) ); INSERT INTO t1 VALUES (1,1,1),(2,2,2),(2,1,1),(3,3,3),(4,3,3),(5,3,3); explain select userid,count(*) from t1 group by userid desc; -table type possible_keys key key_len ref rows Extra -t1 ALL NULL NULL NULL NULL 6 Using temporary; Using filesort +id select_type table type possible_keys key key_len ref rows Extra +1 FIRST t1 ALL NULL NULL NULL NULL 6 Using temporary; Using filesort select userid,count(*) from t1 group by userid desc; userid count(*) 3 3 2 1 1 2 explain select spid,count(*) from t1 where spid between 1 and 2 group by spid desc; -table type possible_keys key key_len ref rows Extra -t1 range spID spID 5 NULL 2 where used; Using index +id select_type table type possible_keys key key_len ref rows Extra +1 FIRST t1 range spID spID 5 NULL 2 where used; Using index explain select spid,count(*) from t1 where spid between 1 and 2 group by spid; -table type possible_keys key key_len ref rows Extra -t1 range spID spID 5 NULL 2 where used; Using index +id select_type table type possible_keys key key_len ref rows Extra +1 FIRST t1 range spID spID 5 NULL 2 where used; Using index select spid,count(*) from t1 where spid between 1 and 2 group by spid; spid count(*) 1 1 @@ -247,8 +247,8 @@ spid count(*) 2 2 1 1 explain select sql_big_result spid,sum(userid) from t1 group by spid desc; -table type possible_keys key key_len ref rows Extra -t1 ALL NULL NULL NULL NULL 6 Using filesort +id select_type table type possible_keys key key_len ref rows Extra +1 FIRST t1 ALL NULL NULL NULL NULL 6 Using filesort select sql_big_result spid,sum(userid) from t1 group by spid desc; spid sum(userid) 5 3 @@ -257,8 +257,8 @@ spid sum(userid) 2 3 1 1 explain select sql_big_result score,count(*) from t1 group by score desc; -table type possible_keys key key_len ref rows Extra -t1 index NULL score 3 NULL 6 Using index +id select_type table type possible_keys key key_len ref rows Extra +1 FIRST t1 index NULL score 3 NULL 6 Using index select sql_big_result score,count(*) from t1 group by score desc; score count(*) 3 3 diff --git a/mysql-test/r/heap.result b/mysql-test/r/heap.result index eb3bae0fea2..94fac7757ac 100644 --- a/mysql-test/r/heap.result +++ b/mysql-test/r/heap.result @@ -65,8 +65,8 @@ a 869751 alter table t1 type=myisam; explain select * from t1 where a in (869751,736494,226312,802616); -table type possible_keys key key_len ref rows Extra -t1 range uniq_id uniq_id 4 NULL 4 where used; Using index +id select_type table type possible_keys key key_len ref rows Extra +1 FIRST t1 range uniq_id uniq_id 4 NULL 4 where used; Using index drop table t1; create table t1 (x int not null, y int not null, key x (x), unique y (y)) type=heap; @@ -84,9 +84,9 @@ x y x y 2 5 2 2 2 6 2 2 explain select * from t1,t1 as t2 where t1.x=t2.y; -table type possible_keys key key_len ref rows Extra -t1 ALL x NULL NULL NULL 6 -t2 eq_ref y y 4 t1.x 1 +id select_type table type possible_keys key key_len ref rows Extra +1 FIRST t1 ALL x NULL NULL NULL 6 +1 FIRST t2 eq_ref y y 4 t1.x 1 drop table t1; create table t1 (a int) type=heap; insert into t1 values(1); @@ -158,18 +158,18 @@ drop table t1; create table t1 (btn char(10) not null, key(btn)) type=heap; insert into t1 values ("hello"),("hello"),("hello"),("hello"),("hello"),("a"),("b"),("c"),("d"),("e"),("f"),("g"),("h"),("i"); explain select * from t1 where btn like "q%"; -table type possible_keys key key_len ref rows Extra -t1 ALL btn NULL NULL NULL 14 where used +id select_type table type possible_keys key key_len ref rows Extra +1 FIRST t1 ALL btn NULL NULL NULL 14 where used select * from t1 where btn like "q%"; btn alter table t1 add column new_col char(1) not null, add key (btn,new_col), drop key btn; update t1 set new_col=btn; explain select * from t1 where btn="a"; -table type possible_keys key key_len ref rows Extra -t1 ALL btn NULL NULL NULL 14 where used +id select_type table type possible_keys key key_len ref rows Extra +1 FIRST t1 ALL btn NULL NULL NULL 14 where used explain select * from t1 where btn="a" and new_col="a"; -table type possible_keys key key_len ref rows Extra -t1 ref btn btn 11 const,const 10 where used +id select_type table type possible_keys key key_len ref rows Extra +1 FIRST t1 ref btn btn 11 const,const 10 where used drop table t1; CREATE TABLE t1 ( a int default NULL, @@ -181,16 +181,16 @@ INSERT INTO t1 VALUES (NULL,99),(99,NULL),(1,1),(2,2),(1,3); SELECT * FROM t1 WHERE a=NULL; a b explain SELECT * FROM t1 WHERE a IS NULL; -table type possible_keys key key_len ref rows Extra -t1 ref a a 5 const 10 where used +id select_type table type possible_keys key key_len ref rows Extra +1 FIRST t1 ref a a 5 const 10 where used SELECT * FROM t1 WHERE a<=>NULL; a b NULL 99 SELECT * FROM t1 WHERE b=NULL; a b explain SELECT * FROM t1 WHERE b IS NULL; -table type possible_keys key key_len ref rows Extra -t1 ref b b 5 const 1 where used +id select_type table type possible_keys key key_len ref rows Extra +1 FIRST t1 ref b b 5 const 1 where used SELECT * FROM t1 WHERE b<=>NULL; a b 99 NULL diff --git a/mysql-test/r/heap_btree.result b/mysql-test/r/heap_btree.result index c3080389999..5a8289a16f7 100644 --- a/mysql-test/r/heap_btree.result +++ b/mysql-test/r/heap_btree.result @@ -65,8 +65,8 @@ a 869751 alter table t1 type=myisam; explain select * from t1 where a in (869751,736494,226312,802616); -table type possible_keys key key_len ref rows Extra -t1 range uniq_id uniq_id 4 NULL 4 where used; Using index +id select_type table type possible_keys key key_len ref rows Extra +1 FIRST t1 range uniq_id uniq_id 4 NULL 4 where used; Using index drop table t1; create table t1 (x int not null, y int not null, key x using BTREE (x), unique y using BTREE (y)) type=heap; @@ -84,9 +84,9 @@ x y x y 2 5 2 2 2 6 2 2 explain select * from t1,t1 as t2 where t1.x=t2.y; -table type possible_keys key key_len ref rows Extra -t1 ALL x NULL NULL NULL 6 -t2 eq_ref y y 4 t1.x 1 +id select_type table type possible_keys key key_len ref rows Extra +1 FIRST t1 ALL x NULL NULL NULL 6 +1 FIRST t2 eq_ref y y 4 t1.x 1 drop table t1; create table t1 (a int) type=heap; insert into t1 values(1); @@ -120,18 +120,18 @@ a b 1 6 1 6 explain select * from tx where a=x order by a,b; -table type possible_keys key key_len ref rows Extra -tx ref a a x const x where used +id select_type table type possible_keys key key_len ref rows Extra +x FIRST tx ref a a x const x where used explain select * from tx where a=x order by b; -table type possible_keys key key_len ref rows Extra -tx ref a a x const x where used +id select_type table type possible_keys key key_len ref rows Extra +x FIRST tx ref a a x const x where used select * from t1 where b=1; a b 1 1 1 1 explain select * from tx where b=x; -table type possible_keys key key_len ref rows Extra -tx ref b b x const x where used +id select_type table type possible_keys key key_len ref rows Extra +x FIRST tx ref b b x const x where used drop table t1; create table t1 (id int unsigned not null, primary key using BTREE (id)) type=HEAP; insert into t1 values(1); @@ -171,18 +171,18 @@ drop table t1; create table t1 (btn char(10) not null, key using BTREE (btn)) type=heap; insert into t1 values ("hello"),("hello"),("hello"),("hello"),("hello"),("a"),("b"),("c"),("d"),("e"),("f"),("g"),("h"),("i"); explain select * from t1 where btn like "q%"; -table type possible_keys key key_len ref rows Extra -t1 ALL btn NULL NULL NULL 14 where used +id select_type table type possible_keys key key_len ref rows Extra +1 FIRST t1 ALL btn NULL NULL NULL 14 where used select * from t1 where btn like "q%"; btn alter table t1 add column new_col char(1) not null, add key using BTREE (btn,new_col), drop key btn; update t1 set new_col=btn; explain select * from t1 where btn="a"; -table type possible_keys key key_len ref rows Extra -t1 ref btn btn 10 const 1 where used +id select_type table type possible_keys key key_len ref rows Extra +1 FIRST t1 ref btn btn 10 const 1 where used explain select * from t1 where btn="a" and new_col="a"; -table type possible_keys key key_len ref rows Extra -t1 ref btn btn 11 const,const 1 where used +id select_type table type possible_keys key key_len ref rows Extra +1 FIRST t1 ref btn btn 11 const,const 1 where used drop table t1; CREATE TABLE t1 ( a int default NULL, @@ -194,16 +194,16 @@ INSERT INTO t1 VALUES (NULL,99),(99,NULL),(1,1),(2,2),(1,3); SELECT * FROM t1 WHERE a=NULL; a b explain SELECT * FROM t1 WHERE a IS NULL; -table type possible_keys key key_len ref rows Extra -t1 ref a a 5 const 1 where used +id select_type table type possible_keys key key_len ref rows Extra +1 FIRST t1 ref a a 5 const 1 where used SELECT * FROM t1 WHERE a<=>NULL; a b NULL 99 SELECT * FROM t1 WHERE b=NULL; a b explain SELECT * FROM t1 WHERE b IS NULL; -table type possible_keys key key_len ref rows Extra -t1 ref b b 5 const 1 where used +id select_type table type possible_keys key key_len ref rows Extra +1 FIRST t1 ref b b 5 const 1 where used SELECT * FROM t1 WHERE b<=>NULL; a b 99 NULL diff --git a/mysql-test/r/heap_hash.result b/mysql-test/r/heap_hash.result index 9b7f2cca6bc..4dd79e5c52b 100644 --- a/mysql-test/r/heap_hash.result +++ b/mysql-test/r/heap_hash.result @@ -65,8 +65,8 @@ a 869751 alter table t1 type=myisam; explain select * from t1 where a in (869751,736494,226312,802616); -table type possible_keys key key_len ref rows Extra -t1 range uniq_id uniq_id 4 NULL 4 where used; Using index +id select_type table type possible_keys key key_len ref rows Extra +1 FIRST t1 range uniq_id uniq_id 4 NULL 4 where used; Using index drop table t1; create table t1 (x int not null, y int not null, key x using HASH (x), unique y using HASH (y)) type=heap; @@ -84,9 +84,9 @@ x y x y 2 5 2 2 2 6 2 2 explain select * from t1,t1 as t2 where t1.x=t2.y; -table type possible_keys key key_len ref rows Extra -t1 ALL x NULL NULL NULL 6 -t2 eq_ref y y 4 t1.x 1 +id select_type table type possible_keys key key_len ref rows Extra +1 FIRST t1 ALL x NULL NULL NULL 6 +1 FIRST t2 eq_ref y y 4 t1.x 1 drop table t1; create table t1 (a int) type=heap; insert into t1 values(1); @@ -158,18 +158,18 @@ drop table t1; create table t1 (btn char(10) not null, key using HASH (btn)) type=heap; insert into t1 values ("hello"),("hello"),("hello"),("hello"),("hello"),("a"),("b"),("c"),("d"),("e"),("f"),("g"),("h"),("i"); explain select * from t1 where btn like "q%"; -table type possible_keys key key_len ref rows Extra -t1 ALL btn NULL NULL NULL 14 where used +id select_type table type possible_keys key key_len ref rows Extra +1 FIRST t1 ALL btn NULL NULL NULL 14 where used select * from t1 where btn like "q%"; btn alter table t1 add column new_col char(1) not null, add key using HASH (btn,new_col), drop key btn; update t1 set new_col=btn; explain select * from t1 where btn="a"; -table type possible_keys key key_len ref rows Extra -t1 ALL btn NULL NULL NULL 14 where used +id select_type table type possible_keys key key_len ref rows Extra +1 FIRST t1 ALL btn NULL NULL NULL 14 where used explain select * from t1 where btn="a" and new_col="a"; -table type possible_keys key key_len ref rows Extra -t1 ref btn btn 11 const,const 10 where used +id select_type table type possible_keys key key_len ref rows Extra +1 FIRST t1 ref btn btn 11 const,const 10 where used drop table t1; CREATE TABLE t1 ( a int default NULL, @@ -181,16 +181,16 @@ INSERT INTO t1 VALUES (NULL,99),(99,NULL),(1,1),(2,2),(1,3); SELECT * FROM t1 WHERE a=NULL; a b explain SELECT * FROM t1 WHERE a IS NULL; -table type possible_keys key key_len ref rows Extra -t1 ref a a 5 const 10 where used +id select_type table type possible_keys key key_len ref rows Extra +1 FIRST t1 ref a a 5 const 10 where used SELECT * FROM t1 WHERE a<=>NULL; a b NULL 99 SELECT * FROM t1 WHERE b=NULL; a b explain SELECT * FROM t1 WHERE b IS NULL; -table type possible_keys key key_len ref rows Extra -t1 ref b b 5 const 1 where used +id select_type table type possible_keys key key_len ref rows Extra +1 FIRST t1 ref b b 5 const 1 where used SELECT * FROM t1 WHERE b<=>NULL; a b 99 NULL diff --git a/mysql-test/r/innodb.result b/mysql-test/r/innodb.result index b75248b5d5a..7b709d6f471 100644 --- a/mysql-test/r/innodb.result +++ b/mysql-test/r/innodb.result @@ -139,14 +139,14 @@ id parent_id level 1010 102 2 1015 102 2 explain select level from t1 where level=1; -table type possible_keys key key_len ref rows Extra -t1 ref level level 1 const 12 where used; Using index +id select_type table type possible_keys key key_len ref rows Extra +1 FIRST t1 ref level level 1 const 12 where used; Using index explain select level,id from t1 where level=1; -table type possible_keys key key_len ref rows Extra -t1 ref level level 1 const 12 where used; Using index +id select_type table type possible_keys key key_len ref rows Extra +1 FIRST t1 ref level level 1 const 12 where used; Using index explain select level,id,parent_id from t1 where level=1; -table type possible_keys key key_len ref rows Extra -t1 ref level level 1 const 12 where used +id select_type table type possible_keys key key_len ref rows Extra +1 FIRST t1 ref level level 1 const 12 where used select level,id from t1 where level=1; level id 1 1002 @@ -596,8 +596,8 @@ id parent_id level 1025 102 2 1016 102 2 explain select level from t1 where level=1; -table type possible_keys key key_len ref rows Extra -t1 ref level level 1 const 6 where used; Using index +id select_type table type possible_keys key key_len ref rows Extra +1 FIRST t1 ref level level 1 const 6 where used; Using index select level,id from t1 where level=1; level id 1 1004 @@ -758,8 +758,8 @@ DROP TABLE t1; create table t1 (a int primary key,b int, c int, d int, e int, f int, g int, h int, i int, j int, k int, l int, m int, n int, o int, p int, q int, r int, s int, t int, u int, v int, w int, x int, y int, z int, a1 int, a2 int, a3 int, a4 int, a5 int, a6 int, a7 int, a8 int, a9 int, b1 int, b2 int, b3 int, b4 int, b5 int, b6 int) type = innodb; insert into t1 values (1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1); explain select * from t1 where a > 0 and a < 50; -table type possible_keys key key_len ref rows Extra -t1 range PRIMARY PRIMARY 4 NULL 1 where used +id select_type table type possible_keys key key_len ref rows Extra +1 FIRST t1 range PRIMARY PRIMARY 4 NULL 1 where used drop table t1; create table t1 (id int NOT NULL,id2 int NOT NULL,id3 int NOT NULL,dummy1 char(30),primary key (id,id2),index index_id3 (id3)) type=innodb; insert into t1 values (0,0,0,'ABCDEFGHIJ'),(2,2,2,'BCDEFGHIJK'),(1,1,1,'CDEFGHIJKL'); @@ -892,29 +892,29 @@ drop table t1; create table t1 (a int not null, b int not null, c int not null, primary key (a),key(b)) type=innodb; insert into t1 values (3,3,3),(1,1,1),(2,2,2),(4,4,4); explain select * from t1 order by a; -table type possible_keys key key_len ref rows Extra -t1 index NULL PRIMARY 4 NULL 4 +id select_type table type possible_keys key key_len ref rows Extra +1 FIRST t1 index NULL PRIMARY 4 NULL 4 explain select * from t1 order by b; -table type possible_keys key key_len ref rows Extra -t1 index NULL b 4 NULL 4 +id select_type table type possible_keys key key_len ref rows Extra +1 FIRST t1 index NULL b 4 NULL 4 explain select * from t1 order by c; -table type possible_keys key key_len ref rows Extra -t1 ALL NULL NULL NULL NULL 4 Using filesort +id select_type table type possible_keys key key_len ref rows Extra +1 FIRST t1 ALL NULL NULL NULL NULL 4 Using filesort explain select a from t1 order by a; -table type possible_keys key key_len ref rows Extra -t1 index NULL PRIMARY 4 NULL 4 Using index +id select_type table type possible_keys key key_len ref rows Extra +1 FIRST t1 index NULL PRIMARY 4 NULL 4 Using index explain select b from t1 order by b; -table type possible_keys key key_len ref rows Extra -t1 index NULL b 4 NULL 4 Using index +id select_type table type possible_keys key key_len ref rows Extra +1 FIRST t1 index NULL b 4 NULL 4 Using index explain select a,b from t1 order by b; -table type possible_keys key key_len ref rows Extra -t1 index NULL b 4 NULL 4 Using index +id select_type table type possible_keys key key_len ref rows Extra +1 FIRST t1 index NULL b 4 NULL 4 Using index explain select a,b from t1; -table type possible_keys key key_len ref rows Extra -t1 index NULL b 4 NULL 4 Using index +id select_type table type possible_keys key key_len ref rows Extra +1 FIRST t1 index NULL b 4 NULL 4 Using index explain select a,b,c from t1; -table type possible_keys key key_len ref rows Extra -t1 ALL NULL NULL NULL NULL 4 +id select_type table type possible_keys key key_len ref rows Extra +1 FIRST t1 ALL NULL NULL NULL NULL 4 drop table t1; create table t1 (t int not null default 1, key (t)) type=innodb; desc t1; diff --git a/mysql-test/r/join_outer.result b/mysql-test/r/join_outer.result index 9d3c152e516..3b9b13bdd3c 100644 --- a/mysql-test/r/join_outer.result +++ b/mysql-test/r/join_outer.result @@ -90,12 +90,12 @@ grp a c id a c d 2 3 c NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL explain select t1.*,t2.* from t1,t2 where t1.a=t2.a and isnull(t2.a)=1; -Comment -Impossible WHERE noticed after reading const tables +id select_type table type possible_keys key key_len ref rows Extra +1 FIRST Impossible WHERE noticed after reading const tables explain select t1.*,t2.* from t1 left join t2 on t1.a=t2.a where isnull(t2.a)=1; -table type possible_keys key key_len ref rows Extra -t1 ALL NULL NULL NULL NULL 7 -t2 eq_ref PRIMARY PRIMARY 8 t1.a 1 where used +id select_type table type possible_keys key key_len ref rows Extra +1 FIRST t1 ALL NULL NULL NULL NULL 7 +1 FIRST t2 eq_ref PRIMARY PRIMARY 8 t1.a 1 where used select t1.*,t2.*,t3.a from t1 left join t2 on (t1.a=t2.a) left join t1 as t3 on (t2.a=t3.a); grp a c id a c d a 1 1 a 1 1 a 1 1 @@ -311,13 +311,13 @@ select t1.name, t2.name, t2.id from t1 left join t2 on (t1.id = t2.owner) where name name id Lilliana Angelovska NULL NULL explain select t1.name, t2.name, t2.id from t1 left join t2 on (t1.id = t2.owner) where t2.id is null; -table type possible_keys key key_len ref rows Extra -t1 ALL NULL NULL NULL NULL 3 -t2 ALL NULL NULL NULL NULL 3 where used; Not exists +id select_type table type possible_keys key key_len ref rows Extra +1 FIRST t1 ALL NULL NULL NULL NULL 3 +1 FIRST t2 ALL NULL NULL NULL NULL 3 where used; Not exists explain select t1.name, t2.name, t2.id from t1 left join t2 on (t1.id = t2.owner) where t2.name is null; -table type possible_keys key key_len ref rows Extra -t1 ALL NULL NULL NULL NULL 3 -t2 ALL NULL NULL NULL NULL 3 where used +id select_type table type possible_keys key key_len ref rows Extra +1 FIRST t1 ALL NULL NULL NULL NULL 3 +1 FIRST t2 ALL NULL NULL NULL NULL 3 where used select count(*) from t1 left join t2 on (t1.id = t2.owner); count(*) 4 @@ -331,13 +331,13 @@ select t1.name, t2.name, t2.id from t2 right join t1 on (t1.id = t2.owner) where name name id Lilliana Angelovska NULL NULL explain select t1.name, t2.name, t2.id from t2 right join t1 on (t1.id = t2.owner) where t2.id is null; -table type possible_keys key key_len ref rows Extra -t1 ALL NULL NULL NULL NULL 3 -t2 ALL NULL NULL NULL NULL 3 where used; Not exists +id select_type table type possible_keys key key_len ref rows Extra +1 FIRST t1 ALL NULL NULL NULL NULL 3 +1 FIRST t2 ALL NULL NULL NULL NULL 3 where used; Not exists explain select t1.name, t2.name, t2.id from t2 right join t1 on (t1.id = t2.owner) where t2.name is null; -table type possible_keys key key_len ref rows Extra -t1 ALL NULL NULL NULL NULL 3 -t2 ALL NULL NULL NULL NULL 3 where used +id select_type table type possible_keys key key_len ref rows Extra +1 FIRST t1 ALL NULL NULL NULL NULL 3 +1 FIRST t2 ALL NULL NULL NULL NULL 3 where used select count(*) from t2 right join t1 on (t1.id = t2.owner); count(*) 4 @@ -618,9 +618,9 @@ UNIQUE id (id,idx) ); INSERT INTO t2 VALUES (1,1); explain SELECT * from t1 left join t2 on t1.id=t2.id where t2.id IS NULL; -table type possible_keys key key_len ref rows Extra -t1 ALL NULL NULL NULL NULL 2 -t2 index id id 8 NULL 1 where used; Using index; Not exists +id select_type table type possible_keys key key_len ref rows Extra +1 FIRST t1 ALL NULL NULL NULL NULL 2 +1 FIRST t2 index id id 8 NULL 1 where used; Using index; Not exists SELECT * from t1 left join t2 on t1.id=t2.id where t2.id IS NULL; id name id idx 2 no NULL NULL @@ -638,9 +638,9 @@ create table t2 (fooID smallint unsigned not null, barID smallint unsigned not n insert into t1 (fooID) values (10),(20),(30); insert into t2 values (10,1),(20,2),(30,3); explain select * from t2 left join t1 on t1.fooID = t2.fooID and t1.fooID = 30; -table type possible_keys key key_len ref rows Extra -t2 index NULL PRIMARY 4 NULL 3 Using index -t1 eq_ref PRIMARY PRIMARY 2 const 1 where used; Using index +id select_type table type possible_keys key key_len ref rows Extra +1 FIRST t2 index NULL PRIMARY 4 NULL 3 Using index +1 FIRST t1 eq_ref PRIMARY PRIMARY 2 const 1 where used; Using index select * from t2 left join t1 on t1.fooID = t2.fooID and t1.fooID = 30; fooID barID fooID 10 1 NULL diff --git a/mysql-test/r/key_diff.result b/mysql-test/r/key_diff.result index 0886850f38a..85d1f455b3f 100644 --- a/mysql-test/r/key_diff.result +++ b/mysql-test/r/key_diff.result @@ -34,9 +34,9 @@ C c a a D E a a a a a a explain select t1.*,t2.* from t1,t1 as t2 where t1.A=t2.B; -table type possible_keys key key_len ref rows Extra -t1 ALL a NULL NULL NULL 5 -t2 ALL b NULL NULL NULL 5 where used +id select_type table type possible_keys key key_len ref rows Extra +1 FIRST t1 ALL a NULL NULL NULL 5 +1 FIRST t2 ALL b NULL NULL NULL 5 where used select t1.*,t2.* from t1,t1 as t2 where t1.A=t2.B order by binary t1.a,t2.a; a b a b A B a a diff --git a/mysql-test/r/key_primary.result b/mysql-test/r/key_primary.result index 10771a134fc..b3c9cef64f0 100644 --- a/mysql-test/r/key_primary.result +++ b/mysql-test/r/key_primary.result @@ -11,9 +11,9 @@ select * from t1 where t1 like "a_\%"; t1 AB% describe select * from t1 where t1="ABC"; -table type possible_keys key key_len ref rows Extra -t1 const PRIMARY PRIMARY 3 const 1 +id select_type table type possible_keys key key_len ref rows Extra +1 FIRST t1 const PRIMARY PRIMARY 3 const 1 describe select * from t1 where t1="ABCD"; -Comment -Impossible WHERE noticed after reading const tables +id select_type table type possible_keys key key_len ref rows Extra +1 FIRST Impossible WHERE noticed after reading const tables drop table t1; diff --git a/mysql-test/r/merge.result b/mysql-test/r/merge.result index 6de77f7bdaa..f8711d5f0f6 100644 --- a/mysql-test/r/merge.result +++ b/mysql-test/r/merge.result @@ -34,11 +34,11 @@ insert into t2 select NULL,message from t1; insert into t1 select NULL,message from t2; create table t3 (a int not null, b char(20), key(a)) type=MERGE UNION=(test.t1,test.t2); explain select * from t3 where a < 10; -table type possible_keys key key_len ref rows Extra -t3 range a a 4 NULL 10 where used +id select_type table type possible_keys key key_len ref rows Extra +1 FIRST t3 range a a 4 NULL 10 where used explain select * from t3 where a > 10 and a < 20; -table type possible_keys key key_len ref rows Extra -t3 range a a 4 NULL 10 where used +id select_type table type possible_keys key key_len ref rows Extra +1 FIRST t3 range a a 4 NULL 10 where used select * from t3 where a = 10; a b 10 Testing @@ -84,8 +84,8 @@ a b 19 Testing 19 Testing explain select a from t3 order by a desc limit 10; -table type possible_keys key key_len ref rows Extra -t3 index NULL a 4 NULL 1131 Using index +id select_type table type possible_keys key key_len ref rows Extra +1 FIRST t3 index NULL a 4 NULL 1131 Using index select a from t3 order by a desc limit 10; a 699 diff --git a/mysql-test/r/myisam.result b/mysql-test/r/myisam.result index fb18841ac3f..647fe70b694 100644 --- a/mysql-test/r/myisam.result +++ b/mysql-test/r/myisam.result @@ -51,27 +51,27 @@ drop table t1; create table t1 (a int not null, b int not null, c int not null, primary key (a),key(b)) type=myisam; insert into t1 values (3,3,3),(1,1,1),(2,2,2),(4,4,4); explain select * from t1 order by a; -table type possible_keys key key_len ref rows Extra -t1 ALL NULL NULL NULL NULL 4 Using filesort +id select_type table type possible_keys key key_len ref rows Extra +1 FIRST t1 ALL NULL NULL NULL NULL 4 Using filesort explain select * from t1 order by b; -table type possible_keys key key_len ref rows Extra -t1 ALL NULL NULL NULL NULL 4 Using filesort +id select_type table type possible_keys key key_len ref rows Extra +1 FIRST t1 ALL NULL NULL NULL NULL 4 Using filesort explain select * from t1 order by c; -table type possible_keys key key_len ref rows Extra -t1 ALL NULL NULL NULL NULL 4 Using filesort +id select_type table type possible_keys key key_len ref rows Extra +1 FIRST t1 ALL NULL NULL NULL NULL 4 Using filesort explain select a from t1 order by a; -table type possible_keys key key_len ref rows Extra -t1 index NULL PRIMARY 4 NULL 4 Using index +id select_type table type possible_keys key key_len ref rows Extra +1 FIRST t1 index NULL PRIMARY 4 NULL 4 Using index explain select b from t1 order by b; -table type possible_keys key key_len ref rows Extra -t1 index NULL b 4 NULL 4 Using index +id select_type table type possible_keys key key_len ref rows Extra +1 FIRST t1 index NULL b 4 NULL 4 Using index explain select a,b from t1 order by b; -table type possible_keys key key_len ref rows Extra -t1 ALL NULL NULL NULL NULL 4 Using filesort +id select_type table type possible_keys key key_len ref rows Extra +1 FIRST t1 ALL NULL NULL NULL NULL 4 Using filesort explain select a,b from t1; -table type possible_keys key key_len ref rows Extra -t1 ALL NULL NULL NULL NULL 4 +id select_type table type possible_keys key key_len ref rows Extra +1 FIRST t1 ALL NULL NULL NULL NULL 4 explain select a,b,c from t1; -table type possible_keys key key_len ref rows Extra -t1 ALL NULL NULL NULL NULL 4 +id select_type table type possible_keys key key_len ref rows Extra +1 FIRST t1 ALL NULL NULL NULL NULL 4 drop table t1; diff --git a/mysql-test/r/null_key.result b/mysql-test/r/null_key.result index a28830a696d..a11f8ce6090 100644 --- a/mysql-test/r/null_key.result +++ b/mysql-test/r/null_key.result @@ -2,38 +2,38 @@ drop table if exists t1; create table t1 (a int, b int not null,unique key (a,b),index(b)) type=myisam; insert ignore into t1 values (1,1),(2,2),(3,3),(4,4),(5,5),(6,6),(null,7),(9,9),(8,8),(7,7),(null,9),(null,9),(6,6); explain select * from t1 where a is null; -table type possible_keys key key_len ref rows Extra -t1 ref a a 5 const 3 where used; Using index +id select_type table type possible_keys key key_len ref rows Extra +1 FIRST t1 ref a a 5 const 3 where used; Using index explain select * from t1 where a is null and b = 2; -table type possible_keys key key_len ref rows Extra -t1 ref a,b a 9 const,const 1 where used; Using index +id select_type table type possible_keys key key_len ref rows Extra +1 FIRST t1 ref a,b a 9 const,const 1 where used; Using index explain select * from t1 where a is null and b = 7; -table type possible_keys key key_len ref rows Extra -t1 ref a,b a 9 const,const 1 where used; Using index +id select_type table type possible_keys key key_len ref rows Extra +1 FIRST t1 ref a,b a 9 const,const 1 where used; Using index explain select * from t1 where a=2 and b = 2; -table type possible_keys key key_len ref rows Extra -t1 const a,b a 9 const,const 1 +id select_type table type possible_keys key key_len ref rows Extra +1 FIRST t1 const a,b a 9 const,const 1 explain select * from t1 where a<=>b limit 2; -table type possible_keys key key_len ref rows Extra -t1 index NULL a 9 NULL 12 where used; Using index +id select_type table type possible_keys key key_len ref rows Extra +1 FIRST t1 index NULL a 9 NULL 12 where used; Using index explain select * from t1 where (a is null or a > 0 and a < 3) and b < 5 limit 3; -table type possible_keys key key_len ref rows Extra -t1 range a,b a 9 NULL 3 where used; Using index +id select_type table type possible_keys key key_len ref rows Extra +1 FIRST t1 range a,b a 9 NULL 3 where used; Using index explain select * from t1 where (a is null or a = 7) and b=7; -table type possible_keys key key_len ref rows Extra -t1 ref a,b b 4 const 2 where used +id select_type table type possible_keys key key_len ref rows Extra +1 FIRST t1 ref a,b b 4 const 2 where used explain select * from t1 where (a is null and b>a) or a is null and b=7 limit 2; -table type possible_keys key key_len ref rows Extra -t1 ref a,b a 5 const 3 where used; Using index +id select_type table type possible_keys key key_len ref rows Extra +1 FIRST t1 ref a,b a 5 const 3 where used; Using index explain select * from t1 where a is null and b=9 or a is null and b=7 limit 3; -table type possible_keys key key_len ref rows Extra -t1 range a,b a 9 NULL 2 where used; Using index +id select_type table type possible_keys key key_len ref rows Extra +1 FIRST t1 range a,b a 9 NULL 2 where used; Using index explain select * from t1 where a > 1 and a < 3 limit 1; -table type possible_keys key key_len ref rows Extra -t1 range a a 5 NULL 1 where used; Using index +id select_type table type possible_keys key key_len ref rows Extra +1 FIRST t1 range a a 5 NULL 1 where used; Using index explain select * from t1 where a > 8 and a < 9; -table type possible_keys key key_len ref rows Extra -t1 range a a 5 NULL 1 where used; Using index +id select_type table type possible_keys key key_len ref rows Extra +1 FIRST t1 range a a 5 NULL 1 where used; Using index select * from t1 where a is null; a b NULL 7 @@ -65,44 +65,44 @@ NULL 9 NULL 9 alter table t1 modify b blob not null, add c int not null, drop key a, add unique key (a,b(20),c), drop key b, add key (b(10)); explain select * from t1 where a is null and b = 2; -table type possible_keys key key_len ref rows Extra -t1 ref a,b a 5 const 3 where used +id select_type table type possible_keys key key_len ref rows Extra +1 FIRST t1 ref a,b a 5 const 3 where used explain select * from t1 where a is null and b = 2 and c=0; -table type possible_keys key key_len ref rows Extra -t1 ref a,b a 5 const 3 where used +id select_type table type possible_keys key key_len ref rows Extra +1 FIRST t1 ref a,b a 5 const 3 where used explain select * from t1 where a is null and b = 7 and c=0; -table type possible_keys key key_len ref rows Extra -t1 ref a,b a 5 const 3 where used +id select_type table type possible_keys key key_len ref rows Extra +1 FIRST t1 ref a,b a 5 const 3 where used explain select * from t1 where a=2 and b = 2; -table type possible_keys key key_len ref rows Extra -t1 ref a,b a 5 const 1 where used +id select_type table type possible_keys key key_len ref rows Extra +1 FIRST t1 ref a,b a 5 const 1 where used explain select * from t1 where a<=>b limit 2; -table type possible_keys key key_len ref rows Extra -t1 ALL NULL NULL NULL NULL 12 where used +id select_type table type possible_keys key key_len ref rows Extra +1 FIRST t1 ALL NULL NULL NULL NULL 12 where used explain select * from t1 where (a is null or a > 0 and a < 3) and b < 5 and c=0 limit 3; -table type possible_keys key key_len ref rows Extra -t1 range a,b a 5 NULL 5 where used +id select_type table type possible_keys key key_len ref rows Extra +1 FIRST t1 range a,b a 5 NULL 5 where used explain select * from t1 where (a is null or a = 7) and b=7 and c=0; -table type possible_keys key key_len ref rows Extra -t1 range a,b a 5 NULL 4 where used +id select_type table type possible_keys key key_len ref rows Extra +1 FIRST t1 range a,b a 5 NULL 4 where used explain select * from t1 where (a is null and b>a) or a is null and b=7 limit 2; -table type possible_keys key key_len ref rows Extra -t1 ref a,b a 5 const 3 where used +id select_type table type possible_keys key key_len ref rows Extra +1 FIRST t1 ref a,b a 5 const 3 where used explain select * from t1 where a is null and b=9 or a is null and b=7 limit 3; -table type possible_keys key key_len ref rows Extra -t1 ref a,b a 5 const 3 where used +id select_type table type possible_keys key key_len ref rows Extra +1 FIRST t1 ref a,b a 5 const 3 where used explain select * from t1 where a > 1 and a < 3 limit 1; -table type possible_keys key key_len ref rows Extra -t1 range a a 5 NULL 1 where used +id select_type table type possible_keys key key_len ref rows Extra +1 FIRST t1 range a a 5 NULL 1 where used explain select * from t1 where a is null and b=7 or a > 1 and a < 3 limit 1; -table type possible_keys key key_len ref rows Extra -t1 range a,b a 5 NULL 4 where used +id select_type table type possible_keys key key_len ref rows Extra +1 FIRST t1 range a,b a 5 NULL 4 where used explain select * from t1 where a > 8 and a < 9; -table type possible_keys key key_len ref rows Extra -t1 range a a 5 NULL 1 where used +id select_type table type possible_keys key key_len ref rows Extra +1 FIRST t1 range a a 5 NULL 1 where used explain select * from t1 where b like "6%"; -table type possible_keys key key_len ref rows Extra -t1 range b b 12 NULL 1 where used +id select_type table type possible_keys key key_len ref rows Extra +1 FIRST t1 range b b 12 NULL 1 where used select * from t1 where a is null; a b c NULL 7 0 @@ -151,11 +151,11 @@ PRIMARY KEY (id) INSERT INTO t1 VALUES (1,NULL),(2,NULL),(3,1),(4,2),(5,NULL),(6,NULL),(7,3),(8,4),(9,NULL),(10,NULL); INSERT INTO t2 VALUES (1,NULL),(2,NULL),(3,1),(4,2),(5,NULL),(6,NULL),(7,3),(8,4),(9,NULL),(10,NULL); explain select id from t1 where uniq_id is null; -table type possible_keys key key_len ref rows Extra -t1 ref idx1 idx1 5 const 1 where used +id select_type table type possible_keys key key_len ref rows Extra +1 FIRST t1 ref idx1 idx1 5 const 1 where used explain select id from t1 where uniq_id =1; -table type possible_keys key key_len ref rows Extra -t1 const idx1 idx1 5 const 1 +id select_type table type possible_keys key key_len ref rows Extra +1 FIRST t1 const idx1 idx1 5 const 1 UPDATE t1 SET id=id+100 where uniq_id is null; UPDATE t2 SET id=id+100 where uniq_id is null; select id from t1 where uniq_id is null; diff --git a/mysql-test/r/odbc.result b/mysql-test/r/odbc.result index c696cf94e9d..eb6123075fe 100644 --- a/mysql-test/r/odbc.result +++ b/mysql-test/r/odbc.result @@ -10,6 +10,6 @@ a b select * from t1 where a is null; a b explain select * from t1 where b is null; -Comment -Impossible WHERE noticed after reading const tables +id select_type table type possible_keys key key_len ref rows Extra +1 FIRST Impossible WHERE noticed after reading const tables drop table t1; diff --git a/mysql-test/r/order_by.result b/mysql-test/r/order_by.result index 9bc716ee8b9..08d52a782e1 100644 --- a/mysql-test/r/order_by.result +++ b/mysql-test/r/order_by.result @@ -263,14 +263,14 @@ drop table t1; create table t1 (a int not null, b int, c varchar(10), key (a, b, c)); insert into t1 values (1, NULL, NULL), (1, NULL, 'b'), (1, 1, NULL), (1, 1, 'b'), (1, 1, 'b'), (2, 1, 'a'), (2, 1, 'b'), (2, 2, 'a'), (2, 2, 'b'), (2, 3, 'c'),(1,3,'b'); explain select * from t1 where (a = 1 and b is null and c = 'b') or (a > 2) order by a desc; -table type possible_keys key key_len ref rows Extra -t1 range a a 20 NULL 2 where used; Using index +id select_type table type possible_keys key key_len ref rows Extra +1 FIRST t1 range a a 20 NULL 2 where used; Using index select * from t1 where (a = 1 and b is null and c = 'b') or (a > 2) order by a desc; a b c 1 NULL b explain select * from t1 where a >= 1 and a < 3 order by a desc; -table type possible_keys key key_len ref rows Extra -t1 range a a 4 NULL 10 where used; Using index +id select_type table type possible_keys key key_len ref rows Extra +1 FIRST t1 range a a 4 NULL 10 where used; Using index select * from t1 where a >= 1 and a < 3 order by a desc; a b c 2 3 c @@ -285,8 +285,8 @@ a b c 1 NULL b 1 NULL NULL explain select * from t1 where a = 1 order by a desc, b desc; -table type possible_keys key key_len ref rows Extra -t1 ref a a 4 const 5 where used; Using index +id select_type table type possible_keys key key_len ref rows Extra +1 FIRST t1 ref a a 4 const 5 where used; Using index select * from t1 where a = 1 order by a desc, b desc; a b c 1 3 b @@ -296,35 +296,35 @@ a b c 1 NULL b 1 NULL NULL explain select * from t1 where a = 1 and b is null order by a desc, b desc; -table type possible_keys key key_len ref rows Extra -t1 ref a a 9 const,const 2 where used; Using index; Using filesort +id select_type table type possible_keys key key_len ref rows Extra +1 FIRST t1 ref a a 9 const,const 2 where used; Using index; Using filesort select * from t1 where a = 1 and b is null order by a desc, b desc; a b c 1 NULL NULL 1 NULL b explain select * from t1 where a >= 1 and a < 3 and b >0 order by a desc,b desc; -table type possible_keys key key_len ref rows Extra -t1 range a a 9 NULL 8 where used; Using index; Using filesort +id select_type table type possible_keys key key_len ref rows Extra +1 FIRST t1 range a a 9 NULL 8 where used; Using index; Using filesort explain select * from t1 where a = 2 and b >0 order by a desc,b desc; -table type possible_keys key key_len ref rows Extra -t1 range a a 9 NULL 5 where used; Using index +id select_type table type possible_keys key key_len ref rows Extra +1 FIRST t1 range a a 9 NULL 5 where used; Using index explain select * from t1 where a = 2 and b is null order by a desc,b desc; -table type possible_keys key key_len ref rows Extra -t1 ref a a 9 const,const 1 where used; Using index; Using filesort +id select_type table type possible_keys key key_len ref rows Extra +1 FIRST t1 ref a a 9 const,const 1 where used; Using index; Using filesort explain select * from t1 where a = 2 and (b is null or b > 0) order by a desc,b desc; -table type possible_keys key key_len ref rows Extra -t1 range a a 9 NULL 6 where used; Using index +id select_type table type possible_keys key key_len ref rows Extra +1 FIRST t1 range a a 9 NULL 6 where used; Using index explain select * from t1 where a = 2 and b > 0 order by a desc,b desc; -table type possible_keys key key_len ref rows Extra -t1 range a a 9 NULL 5 where used; Using index +id select_type table type possible_keys key key_len ref rows Extra +1 FIRST t1 range a a 9 NULL 5 where used; Using index explain select * from t1 where a = 2 and b < 2 order by a desc,b desc; -table type possible_keys key key_len ref rows Extra -t1 range a a 9 NULL 2 where used; Using index; Using filesort +id select_type table type possible_keys key key_len ref rows Extra +1 FIRST t1 range a a 9 NULL 2 where used; Using index; Using filesort alter table t1 modify b int not null, modify c varchar(10) not null; explain select * from t1 order by a, b, c; -table type possible_keys key key_len ref rows Extra -t1 index NULL a 18 NULL 11 Using index +id select_type table type possible_keys key key_len ref rows Extra +1 FIRST t1 index NULL a 18 NULL 11 Using index select * from t1 order by a, b, c; a b c 1 0 @@ -339,8 +339,8 @@ a b c 2 2 b 2 3 c explain select * from t1 order by a desc, b desc, c desc; -table type possible_keys key key_len ref rows Extra -t1 index NULL a 18 NULL 11 Using index +id select_type table type possible_keys key key_len ref rows Extra +1 FIRST t1 index NULL a 18 NULL 11 Using index select * from t1 order by a desc, b desc, c desc; a b c 2 3 c @@ -355,15 +355,15 @@ a b c 1 0 b 1 0 explain select * from t1 where (a = 1 and b = 1 and c = 'b') or (a > 2) order by a desc; -table type possible_keys key key_len ref rows Extra -t1 range a a 18 NULL 3 where used; Using index +id select_type table type possible_keys key key_len ref rows Extra +1 FIRST t1 range a a 18 NULL 3 where used; Using index select * from t1 where (a = 1 and b = 1 and c = 'b') or (a > 2) order by a desc; a b c 1 1 b 1 1 b explain select * from t1 where a < 2 and b <= 1 order by a desc, b desc; -table type possible_keys key key_len ref rows Extra -t1 range a a 4 NULL 6 where used; Using index +id select_type table type possible_keys key key_len ref rows Extra +1 FIRST t1 range a a 4 NULL 6 where used; Using index select * from t1 where a < 2 and b <= 1 order by a desc, b desc; a b c 1 1 b @@ -386,8 +386,8 @@ a b c 1 1 b 1 1 explain select * from t1 where a between 1 and 3 and b <= 1 order by a desc, b desc; -table type possible_keys key key_len ref rows Extra -t1 range a a 8 NULL 10 where used; Using index +id select_type table type possible_keys key key_len ref rows Extra +1 FIRST t1 range a a 8 NULL 10 where used; Using index select * from t1 where a between 1 and 3 and b <= 1 order by a desc, b desc; a b c 2 1 b @@ -398,8 +398,8 @@ a b c 1 0 b 1 0 explain select * from t1 where a between 0 and 1 order by a desc, b desc; -table type possible_keys key key_len ref rows Extra -t1 range a a 4 NULL 5 where used; Using index +id select_type table type possible_keys key key_len ref rows Extra +1 FIRST t1 range a a 4 NULL 5 where used; Using index select * from t1 where a between 0 and 1 order by a desc, b desc; a b c 1 3 b @@ -449,25 +449,25 @@ gid sid uid 104505 5 117 103853 5 250 EXPLAIN select t1.gid, t2.sid, t3.uid from t3, t2, t1 where t2.gid = t1.gid and t2.uid = t3.uid order by t1.gid, t3.uid; -table type possible_keys key key_len ref rows Extra -t1 index PRIMARY PRIMARY 4 NULL 6 Using index -t2 eq_ref PRIMARY,uid PRIMARY 4 t1.gid 1 -t3 eq_ref PRIMARY PRIMARY 2 t2.uid 1 where used; Using index +id select_type table type possible_keys key key_len ref rows Extra +1 FIRST t1 index PRIMARY PRIMARY 4 NULL 6 Using index +1 FIRST t2 eq_ref PRIMARY,uid PRIMARY 4 t1.gid 1 +1 FIRST t3 eq_ref PRIMARY PRIMARY 2 t2.uid 1 where used; Using index EXPLAIN SELECT t1.gid, t3.uid from t1, t3 where t1.gid = t3.uid order by t1.gid,t3.skr; -table type possible_keys key key_len ref rows Extra -t1 index PRIMARY PRIMARY 4 NULL 6 Using index -t3 eq_ref PRIMARY PRIMARY 2 t1.gid 1 where used +id select_type table type possible_keys key key_len ref rows Extra +1 FIRST t1 index PRIMARY PRIMARY 4 NULL 6 Using index +1 FIRST t3 eq_ref PRIMARY PRIMARY 2 t1.gid 1 where used EXPLAIN SELECT t1.gid, t2.sid, t3.uid from t2, t1, t3 where t2.gid = t1.gid and t2.uid = t3.uid order by t3.uid, t1.gid; -table type possible_keys key key_len ref rows Extra -t1 index PRIMARY PRIMARY 4 NULL 6 Using index; Using temporary; Using filesort -t2 eq_ref PRIMARY,uid PRIMARY 4 t1.gid 1 -t3 eq_ref PRIMARY PRIMARY 2 t2.uid 1 where used; Using index +id select_type table type possible_keys key key_len ref rows Extra +1 FIRST t1 index PRIMARY PRIMARY 4 NULL 6 Using index; Using temporary; Using filesort +1 FIRST t2 eq_ref PRIMARY,uid PRIMARY 4 t1.gid 1 +1 FIRST t3 eq_ref PRIMARY PRIMARY 2 t2.uid 1 where used; Using index EXPLAIN SELECT t1.gid, t3.uid from t1, t3 where t1.gid = t3.uid order by t3.skr,t1.gid; -table type possible_keys key key_len ref rows Extra -t1 index PRIMARY PRIMARY 4 NULL 6 Using index; Using temporary; Using filesort -t3 eq_ref PRIMARY PRIMARY 2 t1.gid 1 where used +id select_type table type possible_keys key key_len ref rows Extra +1 FIRST t1 index PRIMARY PRIMARY 4 NULL 6 Using index; Using temporary; Using filesort +1 FIRST t3 eq_ref PRIMARY PRIMARY 2 t1.gid 1 where used EXPLAIN SELECT t1.gid, t3.uid from t1, t3 where t1.skr = t3.uid order by t1.gid,t3.skr; -table type possible_keys key key_len ref rows Extra -t1 ALL NULL NULL NULL NULL 6 Using temporary; Using filesort -t3 eq_ref PRIMARY PRIMARY 2 t1.skr 1 where used +id select_type table type possible_keys key key_len ref rows Extra +1 FIRST t1 ALL NULL NULL NULL NULL 6 Using temporary; Using filesort +1 FIRST t3 eq_ref PRIMARY PRIMARY 2 t1.skr 1 where used drop table t1,t2,t3; diff --git a/mysql-test/r/range.result b/mysql-test/r/range.result index a1d258455dc..722a1f5c62c 100644 --- a/mysql-test/r/range.result +++ b/mysql-test/r/range.result @@ -15,8 +15,8 @@ event_date type event_id 1999-07-13 100600 26 1999-07-14 100600 10 explain select event_date,type,event_id from t1 WHERE type = 100601 and event_date >= "1999-07-01" AND event_date < "1999-07-15" AND (type=100600 OR type=100100) ORDER BY event_date; -Comment -Impossible WHERE +id select_type table type possible_keys key key_len ref rows Extra +1 FIRST Impossible WHERE select event_date,type,event_id from t1 WHERE event_date >= "1999-07-01" AND event_date <= "1999-07-15" AND (type=100600 OR type=100100) or event_date >= "1999-07-01" AND event_date <= "1999-07-15" AND type=100099; event_date type event_id 1999-07-10 100100 24 diff --git a/mysql-test/r/select.result b/mysql-test/r/select.result index 49ce0608fc5..8b3e2aa298e 100644 --- a/mysql-test/r/select.result +++ b/mysql-test/r/select.result @@ -1327,20 +1327,20 @@ fld3 select t2.fld3 from t2 where fld3 LIKE 'don_t_find_me_please%'; fld3 explain select t2.fld3 from t2 where fld3 = 'honeysuckle'; -table type possible_keys key key_len ref rows Extra -t2 ref fld3 fld3 30 const 1 where used; Using index +id select_type table type possible_keys key key_len ref rows Extra +1 FIRST t2 ref fld3 fld3 30 const 1 where used; Using index explain select fld3 from t2 ignore index (fld3) where fld3 = 'honeysuckle'; -table type possible_keys key key_len ref rows Extra -t2 index NULL fld3 30 NULL 1199 where used; Using index +id select_type table type possible_keys key key_len ref rows Extra +1 FIRST t2 index NULL fld3 30 NULL 1199 where used; Using index explain select fld3 from t2 use index (fld1) where fld3 = 'honeysuckle'; -table type possible_keys key key_len ref rows Extra -t2 index NULL fld3 30 NULL 1199 where used; Using index +id select_type table type possible_keys key key_len ref rows Extra +1 FIRST t2 index NULL fld3 30 NULL 1199 where used; Using index explain select fld3 from t2 use index (fld3) where fld3 = 'honeysuckle'; -table type possible_keys key key_len ref rows Extra -t2 ref fld3 fld3 30 const 1 where used; Using index +id select_type table type possible_keys key key_len ref rows Extra +1 FIRST t2 ref fld3 fld3 30 const 1 where used; Using index explain select fld3 from t2 use index (fld1,fld3) where fld3 = 'honeysuckle'; -table type possible_keys key key_len ref rows Extra -t2 ref fld3 fld3 30 const 1 where used; Using index +id select_type table type possible_keys key key_len ref rows Extra +1 FIRST t2 ref fld3 fld3 30 const 1 where used; Using index explain select fld3 from t2 ignore index (fld3,not_used); Key column 'not_used' doesn't exist in table explain select fld3 from t2 use index (not_used); @@ -1350,8 +1350,8 @@ fld3 honeysuckle honoring explain select t2.fld3 from t2 where fld3 >= 'honeysuckle' and fld3 <= 'honoring' order by fld3; -table type possible_keys key key_len ref rows Extra -t2 range fld3 fld3 30 NULL 2 where used; Using index +id select_type table type possible_keys key key_len ref rows Extra +1 FIRST t2 range fld3 fld3 30 NULL 2 where used; Using index select fld1,fld3 from t2 where fld3="Colombo" or fld3 = "nondecreasing" order by fld3; fld1 fld3 148504 Colombo @@ -1370,8 +1370,8 @@ fld1 250501 250502 explain select fld1 from t2 where fld1=250501 or fld1="250502"; -table type possible_keys key key_len ref rows Extra -t2 range fld1 fld1 4 NULL 2 where used; Using index +id select_type table type possible_keys key key_len ref rows Extra +1 FIRST t2 range fld1 fld1 4 NULL 2 where used; Using index select fld1 from t2 where fld1=250501 or fld1=250502 or fld1 >= 250505 and fld1 <= 250601 or fld1 between 250501 and 250502; fld1 250501 @@ -1379,8 +1379,8 @@ fld1 250505 250601 explain select fld1 from t2 where fld1=250501 or fld1=250502 or fld1 >= 250505 and fld1 <= 250601 or fld1 between 250501 and 250502; -table type possible_keys key key_len ref rows Extra -t2 range fld1 fld1 4 NULL 4 where used; Using index +id select_type table type possible_keys key key_len ref rows Extra +1 FIRST t2 range fld1 fld1 4 NULL 4 where used; Using index select fld1,fld3 from t2 where companynr = 37 and fld3 like 'f%'; fld1 fld3 218401 faithful @@ -1806,9 +1806,9 @@ companynr rtrim(space(512+companynr)) select distinct fld3 from t2,t3 where t2.companynr = 34 and t2.fld1=t3.t2nr order by fld3; fld3 explain select t3.t2nr,fld3 from t2,t3 where t2.companynr = 34 and t2.fld1=t3.t2nr order by t3.t2nr,fld3; -table type possible_keys key key_len ref rows Extra -t2 ALL fld1 NULL NULL NULL 1199 where used; Using temporary; Using filesort -t3 eq_ref PRIMARY PRIMARY 4 t2.fld1 1 where used; Using index +id select_type table type possible_keys key key_len ref rows Extra +1 FIRST t2 ALL fld1 NULL NULL NULL 1199 where used; Using temporary; Using filesort +1 FIRST t3 eq_ref PRIMARY PRIMARY 4 t2.fld1 1 where used; Using index select period from t1; period 9410 @@ -1821,9 +1821,9 @@ select fld3,period from t2,t3 where t2.fld1 = 011401 and t2.fld1=t3.t2nr and t3. fld3 period breaking 1001 explain select fld3,period from t2,t3 where t2.fld1 = 011401 and t3.t2nr=t2.fld1 and 1001 = t3.period; -table type possible_keys key key_len ref rows Extra -t2 const fld1 fld1 4 const 1 -t3 const PRIMARY,period PRIMARY 4 const 1 +id select_type table type possible_keys key key_len ref rows Extra +1 FIRST t2 const fld1 fld1 4 const 1 +1 FIRST t3 const PRIMARY,period PRIMARY 4 const 1 select fld3,period from t2,t1 where companynr*10 = 37*10; fld3 period breaking 9410 @@ -2561,21 +2561,21 @@ fld1 fld1 select t2.companynr,companyname from t2 left join t4 using (companynr) where t4.companynr is null; companynr companyname explain select t2.companynr,companyname from t2 left join t4 using (companynr) where t4.companynr is null; -table type possible_keys key key_len ref rows Extra -t2 ALL NULL NULL NULL NULL 1199 -t4 eq_ref PRIMARY PRIMARY 1 test.t2.companynr 1 where used; Not exists +id select_type table type possible_keys key key_len ref rows Extra +1 FIRST t2 ALL NULL NULL NULL NULL 1199 +1 FIRST t4 eq_ref PRIMARY PRIMARY 1 test.t2.companynr 1 where used; Not exists explain select t2.companynr,companyname from t4 left join t2 using (companynr) where t2.companynr is null; -table type possible_keys key key_len ref rows Extra -t4 ALL NULL NULL NULL NULL 12 -t2 ALL NULL NULL NULL NULL 1199 where used; Not exists +id select_type table type possible_keys key key_len ref rows Extra +1 FIRST t4 ALL NULL NULL NULL NULL 12 +1 FIRST t2 ALL NULL NULL NULL NULL 1199 where used; Not exists select distinct t2.companynr,t4.companynr from t2,t4 where t2.companynr=t4.companynr+1; companynr companynr 37 36 41 40 explain select distinct t2.companynr,t4.companynr from t2,t4 where t2.companynr=t4.companynr+1; -table type possible_keys key key_len ref rows Extra -t2 ALL NULL NULL NULL NULL 1199 Using temporary -t4 index NULL PRIMARY 1 NULL 12 where used; Using index +id select_type table type possible_keys key key_len ref rows Extra +1 FIRST t2 ALL NULL NULL NULL NULL 1199 Using temporary +1 FIRST t4 index NULL PRIMARY 1 NULL 12 where used; Using index select t2.fld1,t2.companynr,fld3,period from t3,t2 where t2.fld1 = 38208 and t2.fld1=t3.t2nr and period = 1008 or t2.fld1 = 38008 and t2.fld1 =t3.t2nr and period = 1008; fld1 companynr fld3 period 038008 37 reporters 1008 @@ -3084,11 +3084,11 @@ select t2.fld1,sum(price) from t3,t2 where t2.fld1 = t3.t2nr and t3.companynr = fld1 sum(price) 038008 234298 explain select fld3 from t2 where 1>2 or 2>3; -Comment -Impossible WHERE +id select_type table type possible_keys key key_len ref rows Extra +1 FIRST Impossible WHERE explain select fld3 from t2 where fld1=fld1; -table type possible_keys key key_len ref rows Extra -t2 ALL NULL NULL NULL NULL 1199 +id select_type table type possible_keys key key_len ref rows Extra +1 FIRST t2 ALL NULL NULL NULL NULL 1199 select companynr,fld1 from t2 HAVING fld1=250501 or fld1=250502; companynr fld1 34 250501 @@ -3139,8 +3139,8 @@ select count(*) from t3 where companynr=512 and price2=76234234; count(*) 4181 explain select min(fld1),max(fld1),count(*) from t2; -Comment -Select tables optimized away +id select_type table type possible_keys key key_len ref rows Extra +1 FIRST Select tables optimized away select min(fld1),max(fld1),count(*) from t2; min(fld1) max(fld1) count(*) 0 1232609 1199 diff --git a/mysql-test/r/subselect.result b/mysql-test/r/subselect.result index dada87803ad..54528f58697 100644 --- a/mysql-test/r/subselect.result +++ b/mysql-test/r/subselect.result @@ -46,6 +46,13 @@ a b 2 7 3 8 4 8 +explain 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 FIRST t2 ALL NULL NULL NULL NULL 2 where used +3 UNION t4 ALL NULL NULL NULL NULL 3 where used; Using filesort +4 SUBSELECT t2 ALL NULL NULL NULL NULL 2 +2 SUBSELECT t3 ALL NULL NULL NULL NULL 3 Using filesort select (select a from t3 where a<t2.a*4 order by 1 desc limit 1), a from t2; (select a from t3 where a<t2.a*4 order by 1 desc limit 1) a 3 1 @@ -54,6 +61,12 @@ 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; (select t3.a from t3 where a<8 order by 1 desc limit 1) a 7 2 +explain 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 +3 DERIVED t2 ALL NULL NULL NULL NULL 2 where used +1 FIRST <derived3> system NULL NULL NULL NULL 1 +2 SUBSELECT t3 ALL NULL NULL NULL NULL 3 where used; Using filesort 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); a 2 @@ -67,6 +80,11 @@ b (select avg(t2.a+(select min(t3.a) from t3 where t3.a >= t4.a)) from t2) 8 7.5000 8 4.5000 9 7.5000 +explain 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 FIRST t4 ALL NULL NULL NULL NULL 3 +2 DEPENDENT SUBSELECT t2 ALL NULL NULL NULL NULL 2 +3 DEPENDENT SUBSELECT t3 ALL NULL NULL NULL NULL 3 where used select * from t3 where exists (select * from t2 where t2.b=t3.a); a 7 @@ -97,6 +115,11 @@ select (select a from t1 where t1.a=t2.a union select a from t5 where t5.a=t2.a) (select a from t1 where t1.a=t2.a union select a from t5 where t5.a=t2.a) a NULL 1 2 2 +explain 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 FIRST t2 ALL NULL NULL NULL NULL 2 +2 DEPENDENT SUBSELECT t1 system NULL NULL NULL NULL 1 where used +3 DEPENDENT UNION t5 ALL NULL NULL NULL NULL 2 where used select (select a from t1 where t1.a=t2.a union all select a from t5 where t5.a=t2.a), a from t2; Subselect return more than 1 record create table attend (patient_uq int, clinic_uq int, index i1 (clinic_uq)); diff --git a/mysql-test/r/type_datetime.result b/mysql-test/r/type_datetime.result index cae15d4f665..51148cd12e9 100644 --- a/mysql-test/r/type_datetime.result +++ b/mysql-test/r/type_datetime.result @@ -75,6 +75,6 @@ date numfacture expedition 0000-00-00 00:00:00 0 0001-00-00 00:00:00 0000-00-00 00:00:00 1212 0001-00-00 00:00:00 EXPLAIN SELECT * FROM t1 WHERE expedition='0001-00-00 00:00:00'; -table type possible_keys key key_len ref rows Extra -t1 ref expedition expedition 8 const 1 where used +id select_type table type possible_keys key key_len ref rows Extra +1 FIRST t1 ref expedition expedition 8 const 1 where used drop table t1; diff --git a/mysql-test/r/union.result b/mysql-test/r/union.result index 29b925b6746..86f62d11bdb 100644 --- a/mysql-test/r/union.result +++ b/mysql-test/r/union.result @@ -85,35 +85,31 @@ a b 2 b 1 a explain (select a,b from t1 limit 2) union all (select a,b from t2 order by a limit 1) order by b desc; -table type possible_keys key key_len ref rows Extra -t1 ALL NULL NULL NULL NULL 4 -t2 ALL NULL NULL NULL NULL 4 Using filesort -(select sql_calc_found_rows a,b from t1 limit 2) union all (select a,b from t2 order by a) limit 2; -a b -1 a -2 b +id select_type table type possible_keys key key_len ref rows Extra +1 FIRST t1 ALL NULL NULL NULL NULL 4 +2 UNION t2 ALL NULL NULL NULL NULL 4 Using filesort select found_rows(); FOUND_ROWS() -6 +0 explain select a,b from t1 union all select a,b from t2; -table type possible_keys key key_len ref rows Extra -t1 ALL NULL NULL NULL NULL 4 -t2 ALL NULL NULL NULL NULL 4 +id select_type table type possible_keys key key_len ref rows Extra +1 FIRST t1 ALL NULL NULL NULL NULL 4 +2 UNION t2 ALL NULL NULL NULL NULL 4 explain select xx from t1 union select 1; Unknown column 'xx' in 'field list' explain select a,b from t1 union select 1; -table type possible_keys key key_len ref rows Extra -t1 ALL NULL NULL NULL NULL 4 - 0 0 No tables used +id select_type table type possible_keys key key_len ref rows Extra +1 FIRST t1 ALL NULL NULL NULL NULL 4 +2 UNION No tables used explain select 1 union select a,b from t1 union select 1; -table type possible_keys key key_len ref rows Extra - 0 0 No tables used -t1 ALL NULL NULL NULL NULL 4 - 0 0 No tables used +id select_type table type possible_keys key key_len ref rows Extra +1 FIRST No tables used +2 UNION t1 ALL NULL NULL NULL NULL 4 +3 UNION No tables used explain select a,b from t1 union select 1 limit 0; -table type possible_keys key key_len ref rows Extra -t1 ALL NULL NULL NULL NULL 4 - 0 0 Impossible WHERE +id select_type table type possible_keys key key_len ref rows Extra +1 FIRST Impossible WHERE +2 UNION Impossible WHERE select a,b from t1 into outfile 'skr' union select a,b from t2; Wrong usage of UNION and INTO select a,b from t1 order by a union select a,b from t2; diff --git a/mysql-test/r/user_var.result b/mysql-test/r/user_var.result index 5c8b4581c5c..10cb4fe5c8e 100644 --- a/mysql-test/r/user_var.result +++ b/mysql-test/r/user_var.result @@ -18,15 +18,15 @@ i @vv1:=if(sv1.i,1,0) @vv2:=if(sv2.i,1,0) @vv3:=if(sv3.i,1,0) @vv1+@vv2+@vv3 1 1 0 1 2 2 1 0 0 1 explain select * from t1 where i=@vv1; -table type possible_keys key key_len ref rows Extra -t1 ref i i 4 const 1 where used +id select_type table type possible_keys key key_len ref rows Extra +1 FIRST t1 ref i i 4 const 1 where used explain select * from t1 where @vv1:=@vv1+1 and i=@vv1; -table type possible_keys key key_len ref rows Extra -t1 ALL NULL NULL NULL NULL 3 where used +id select_type table type possible_keys key key_len ref rows Extra +1 FIRST t1 ALL NULL NULL NULL NULL 3 where used explain select @vv1:=i from t1 where i=@vv1; -table type possible_keys key key_len ref rows Extra -t1 index NULL i 4 NULL 3 where used; Using index +id select_type table type possible_keys key key_len ref rows Extra +1 FIRST t1 index NULL i 4 NULL 3 where used; Using index explain select * from t1 where i=@vv1; -table type possible_keys key key_len ref rows Extra -t1 ref i i 4 const 1 where used +id select_type table type possible_keys key key_len ref rows Extra +1 FIRST t1 ref i i 4 const 1 where used drop table t1,t2; diff --git a/mysql-test/r/varbinary.result b/mysql-test/r/varbinary.result index a63db14b736..a7bd29afd78 100644 --- a/mysql-test/r/varbinary.result +++ b/mysql-test/r/varbinary.result @@ -11,8 +11,8 @@ create table t1 (ID int(8) unsigned zerofill not null auto_increment,UNIQ bigint insert into t1 set UNIQ=0x38afba1d73e6a18a; insert into t1 set UNIQ=123; explain select * from t1 where UNIQ=0x38afba1d73e6a18a; -table type possible_keys key key_len ref rows Extra -t1 const UNIQ UNIQ 8 const 1 +id select_type table type possible_keys key key_len ref rows Extra +1 FIRST t1 const UNIQ UNIQ 8 const 1 drop table t1; select x'hello'; You have an error in your SQL syntax near 'x'hello'' at line 1 diff --git a/mysql-test/t/subselect.test b/mysql-test/t/subselect.test index 8b9ed3b89c9..68a89796ec3 100644 --- a/mysql-test/t/subselect.test +++ b/mysql-test/t/subselect.test @@ -19,13 +19,18 @@ select * from t2 where t2.b=(select a from t3 order by 1 desc limit 1) union (select * from t4 order by a limit 2) limit 3; 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); +explain 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); select (select a from t3 where a<t2.a*4 order by 1 desc limit 1), a from t2; 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; +explain 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; 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); select * from t1 where t1.a=(select t2.a from t2 where t2.b=(select max(a) from t3 where t3.a > t1.a) order by 1 desc limit 1); select * from t1 where t1.a=(select t2.a from t2 where t2.b=(select max(a) from t3 where t3.a < t1.a) order by 1 desc limit 1); select b,(select avg(t2.a+(select min(t3.a) from t3 where t3.a >= t4.a)) from t2) from t4; +explain select b,(select avg(t2.a+(select min(t3.a) from t3 where t3.a >= t4.a)) from t2) from t4; select * from t3 where exists (select * from t2 where t2.b=t3.a); select * from t3 where not exists (select * from t2 where t2.b=t3.a); insert into t4 values (12,7),(1,7),(10,9),(9,6),(7,6),(3,9); @@ -39,6 +44,7 @@ insert into t5 values (5); select (select a from t1 where t1.a=t2.a union select a from t5 where t5.a=t2.a), a from t2; insert into t5 values (2); select (select a from t1 where t1.a=t2.a union select a from t5 where t5.a=t2.a), a from t2; +explain select (select a from t1 where t1.a=t2.a union select a from t5 where t5.a=t2.a), a from t2; -- error 1230 select (select a from t1 where t1.a=t2.a union all select a from t5 where t5.a=t2.a), a from t2; create table attend (patient_uq int, clinic_uq int, index i1 (clinic_uq)); diff --git a/mysql-test/t/union.test b/mysql-test/t/union.test index f5a92b05e0d..8f8f666bf55 100644 --- a/mysql-test/t/union.test +++ b/mysql-test/t/union.test @@ -21,7 +21,7 @@ select 't1',b,count(*) from t1 group by b UNION select 't2',b,count(*) from t2 g (select a,b from t1 limit 2) union all (select a,b from t2 order by a limit 1); (select a,b from t1 limit 2) union all (select a,b from t2 order by a limit 1) order by b desc; explain (select a,b from t1 limit 2) union all (select a,b from t2 order by a limit 1) order by b desc; -(select sql_calc_found_rows a,b from t1 limit 2) union all (select a,b from t2 order by a) limit 2; +#(select sql_calc_found_rows a,b from t1 limit 2) union all (select a,b from t2 order by a) limit 2; select found_rows(); # Test some error conditions with UNION explain select a,b from t1 union all select a,b from t2; diff --git a/sql/mysql_priv.h b/sql/mysql_priv.h index d3901770230..dbf853431d6 100644 --- a/sql/mysql_priv.h +++ b/sql/mysql_priv.h @@ -373,9 +373,14 @@ int handle_select(THD *thd, LEX *lex, select_result *result); int mysql_select(THD *thd,TABLE_LIST *tables,List<Item> &list,COND *conds, ORDER *order, ORDER *group,Item *having,ORDER *proc_param, ulong select_type,select_result *result, - SELECT_LEX_UNIT *unit, bool fake_select_lex); + SELECT_LEX_UNIT *unit, SELECT_LEX *select_lex, + bool fake_select_lex); +void fix_tables_pointers(SELECT_LEX *select_lex); +int mysql_explain_select(THD *thd, SELECT_LEX *sl, char const *type, + select_result *result); int mysql_union(THD *thd, LEX *lex,select_result *result); -int mysql_derived(THD *thd, LEX *lex, SELECT_LEX_UNIT *s, TABLE_LIST *t); +int mysql_derived(THD *thd, LEX *lex, SELECT_LEX_UNIT *s, TABLE_LIST *t, + bool tables_is_opened); Field *create_tmp_field(THD *thd, TABLE *table,Item *item, Item::Type type, Item_result_field ***copy_func, Field **from_field, bool group,bool modify_item); diff --git a/sql/sql_class.cc b/sql/sql_class.cc index b84bcf1df72..c090f2336c1 100644 --- a/sql/sql_class.cc +++ b/sql/sql_class.cc @@ -357,6 +357,28 @@ CHANGED_TABLE_LIST* THD::changed_table_dup(TABLE *table) return new_table; } +int THD::send_explain_fields(select_result *result) +{ + List<Item> field_list; + Item *item; + field_list.push_back(new Item_int("id",0,3)); + field_list.push_back(new Item_empty_string("select_type",19)); + field_list.push_back(new Item_empty_string("table",NAME_LEN)); + field_list.push_back(new Item_empty_string("type",10)); + field_list.push_back(item=new Item_empty_string("possible_keys", + NAME_LEN*MAX_KEY)); + item->maybe_null=1; + field_list.push_back(item=new Item_empty_string("key",NAME_LEN)); + item->maybe_null=1; + field_list.push_back(item=new Item_int("key_len",0,3)); + item->maybe_null=1; + field_list.push_back(item=new Item_empty_string("ref", + NAME_LEN*MAX_REF_PARTS)); + item->maybe_null=1; + field_list.push_back(new Item_real("rows",0.0,0,10)); + field_list.push_back(new Item_empty_string("Extra",255)); + return (result->send_fields(field_list,1)); +} /***************************************************************************** ** Functions to provide a interface to select results diff --git a/sql/sql_class.h b/sql/sql_class.h index 82241f0ff1f..7be9a5ad9fe 100644 --- a/sql/sql_class.h +++ b/sql/sql_class.h @@ -315,6 +315,7 @@ public: }; class delayed_insert; +class select_result; #define THD_SENTRY_MAGIC 0xfeedd1ff #define THD_SENTRY_GONE 0xdeadbeef @@ -442,6 +443,7 @@ public: uint server_status,open_options; uint32 query_length; uint32 db_length; + uint select_number; //number of select (used for EXPLAIN) enum_tx_isolation tx_isolation, session_tx_isolation; char scramble[9]; uint8 query_cache_type; // type of query cache processing @@ -576,6 +578,7 @@ public: } void add_changed_table(TABLE *table); CHANGED_TABLE_LIST * changed_table_dup(TABLE *table); + int send_explain_fields(select_result *result); }; /* diff --git a/sql/sql_derived.cc b/sql/sql_derived.cc index cde120f3774..539a9edb479 100644 --- a/sql/sql_derived.cc +++ b/sql/sql_derived.cc @@ -28,7 +28,8 @@ static const char *any_db="*any*"; // Special symbol for check_access -int mysql_derived(THD *thd, LEX *lex, SELECT_LEX_UNIT *unit, TABLE_LIST *t) +int mysql_derived(THD *thd, LEX *lex, SELECT_LEX_UNIT *unit, TABLE_LIST *t, + bool tables_is_opened) { /* TODO: make derived tables with union inside (now only 1 SELECT may be @@ -37,7 +38,7 @@ int mysql_derived(THD *thd, LEX *lex, SELECT_LEX_UNIT *unit, TABLE_LIST *t) SELECT_LEX *sl= unit->first_select(); List<Item> item_list; TABLE *table; - int res; + int res= 0; select_union *derived_result; TABLE_LIST *tables= (TABLE_LIST *)sl->table_list.first; TMP_TABLE_PARAM tmp_table_param; @@ -56,7 +57,8 @@ int mysql_derived(THD *thd, LEX *lex, SELECT_LEX_UNIT *unit, TABLE_LIST *t) { if (cursor->derived) { - res=mysql_derived(thd, lex, (SELECT_LEX_UNIT *)cursor->derived, cursor); + res=mysql_derived(thd, lex, (SELECT_LEX_UNIT *)cursor->derived, + cursor, 0); if (res) DBUG_RETURN(res); } } @@ -66,7 +68,7 @@ int mysql_derived(THD *thd, LEX *lex, SELECT_LEX_UNIT *unit, TABLE_LIST *t) while ((item= it++)) item_list.push_back(item); - if (!(res=open_and_lock_tables(thd,tables))) + if (tables_is_opened || !(res=open_and_lock_tables(thd,tables))) { if (tables && setup_fields(thd,tables,item_list,0,0,1)) { @@ -94,12 +96,12 @@ int mysql_derived(THD *thd, LEX *lex, SELECT_LEX_UNIT *unit, TABLE_LIST *t) if (unit->select_limit_cnt == HA_POS_ERROR) sl->options&= ~OPTION_FOUND_ROWS; - res=mysql_select(thd, tables, sl->item_list, - sl->where, (ORDER *) sl->order_list.first, - (ORDER*) sl->group_list.first, - sl->having, (ORDER*) NULL, - sl->options | thd->options | SELECT_NO_UNLOCK, - derived_result, unit, 0); + res= mysql_select(thd, tables, sl->item_list, + sl->where, (ORDER *) sl->order_list.first, + (ORDER*) sl->group_list.first, + sl->having, (ORDER*) NULL, + sl->options | thd->options | SELECT_NO_UNLOCK, + derived_result, unit, sl, 0); if (!res) { // Here we entirely fix both TABLE_LIST and list of SELECT's as there were no derived tables @@ -109,6 +111,7 @@ int mysql_derived(THD *thd, LEX *lex, SELECT_LEX_UNIT *unit, TABLE_LIST *t) { t->real_name=table->real_name; t->table=table; + table->derived_select_number= sl->select_number; sl->exclude(); t->derived=(SELECT_LEX *)0; // just in case ... } diff --git a/sql/sql_lex.h b/sql/sql_lex.h index b75826663ca..e6054f5604b 100644 --- a/sql/sql_lex.h +++ b/sql/sql_lex.h @@ -282,8 +282,10 @@ public: List<Item_func_match> *ftfunc_list; List<Item_func_match> ftfunc_list_alloc; JOIN *join; /* after JOIN::prepare it is pointer to corresponding JOIN */ + const char *type; /* type of select for EXPLAIN */ uint in_sum_expr; - bool create_refs, + uint select_number; /* number of select (used for EXPLAIN) */ + bool create_refs, braces, /* SELECT ... UNION (SELECT ... ) <- this braces */ depended, /* depended from outer select subselect */ /* TRUE when having fix field called in processing of this SELECT */ diff --git a/sql/sql_parse.cc b/sql/sql_parse.cc index 9977eaad0ff..8166a156b8c 100644 --- a/sql/sql_parse.cc +++ b/sql/sql_parse.cc @@ -1254,7 +1254,7 @@ mysql_execute_command(void) THD *thd= current_thd; LEX *lex= &thd->lex; TABLE_LIST *tables= (TABLE_LIST*) lex->select_lex.table_list.first; - SELECT_LEX *select_lex= lex->select; + SELECT_LEX *select_lex= &lex->select_lex; SELECT_LEX_UNIT *unit= &lex->unit; DBUG_ENTER("mysql_execute_command"); @@ -1281,18 +1281,69 @@ mysql_execute_command(void) #endif } + select_result *explain_result= 0; /* - Skip if we are in the slave thread, some table rules have been given - and the table list says the query should not be replicated + TODO: make derived tables processing 'inside' SELECT processing. + TODO: solve problem with depended derived tables in subselects */ - if (lex->derived_tables) + if (lex->sql_command == SQLCOM_SELECT && + (select_lex->options & SELECT_DESCRIBE) && + lex->derived_tables) + { + if (!(explain_result= new select_send())) + { + send_error(&thd->net, ER_OUT_OF_RESOURCES); + DBUG_VOID_RETURN; + } + //check rights + for (TABLE_LIST *cursor= tables; + cursor; + cursor= cursor->next) + if (cursor->derived) + { + TABLE_LIST *tables= + (TABLE_LIST *)((SELECT_LEX_UNIT *) + cursor->derived)->first_select()->table_list.first; + int res; + if (tables) + res= check_table_access(thd,SELECT_ACL, tables); + else + res= check_access(thd, SELECT_ACL, any_db); + if (res) + DBUG_VOID_RETURN; + } + thd->send_explain_fields(explain_result); + // EXPLAIN derived tables + for (TABLE_LIST *cursor= tables; + cursor; + cursor= cursor->next) + if (cursor->derived) + { + SELECT_LEX *select_lex= ((SELECT_LEX_UNIT *) + cursor->derived)->first_select(); + if (!open_and_lock_tables(thd, + (TABLE_LIST*) select_lex->table_list.first)) + { + mysql_explain_select(thd, select_lex, + "DERIVED", explain_result); + // execute derived table SELECT to provide table for other SELECTs + if (mysql_derived(thd, lex, (SELECT_LEX_UNIT *)cursor->derived, + cursor, 1)) + DBUG_VOID_RETURN; + } + else + DBUG_VOID_RETURN; + } + + } + else if (lex->derived_tables) { for (TABLE_LIST *cursor= tables; cursor; cursor= cursor->next) if (cursor->derived && mysql_derived(thd, lex, (SELECT_LEX_UNIT *)cursor->derived, - cursor)) + cursor, 0)) DBUG_VOID_RETURN; } if ((lex->select_lex.next_select_in_list() && @@ -1374,8 +1425,44 @@ mysql_execute_command(void) if (!(res=open_and_lock_tables(thd,tables))) { - query_cache_store_query(thd, tables); - res=handle_select(thd, lex, result); + if (select_lex->options & SELECT_DESCRIBE) + { + delete result; // we do not need it for explain + if (!explain_result) + if (!(explain_result= new select_send())) + { + send_error(&thd->net, ER_OUT_OF_RESOURCES); + DBUG_VOID_RETURN; + } + else + thd->send_explain_fields(explain_result); + fix_tables_pointers(select_lex); + for ( SELECT_LEX *sl= select_lex; + sl && res == 0; + sl= sl->next_select_in_list()) + { + SELECT_LEX *first= sl->master_unit()->first_select(); + res= mysql_explain_select(thd, sl, + ((select_lex==sl)?"FIRST": + ((sl == first)? + ((sl->depended)?"DEPENDENT SUBSELECT": + "SUBSELECT"): + ((sl->depended)?"DEPENDENT UNION": + "UNION"))), + explain_result); + } + if (res > 0) + res= -res; // mysql_explain_select do not report error + MYSQL_LOCK *save_lock= thd->lock; + thd->lock= (MYSQL_LOCK *)0; + explain_result->send_eof(); + thd->lock= save_lock; + } + else + { + query_cache_store_query(thd, tables); + res=handle_select(thd, lex, result); + } } else delete result; @@ -1866,7 +1953,7 @@ mysql_execute_command(void) (ORDER *)NULL, select_lex->options | thd->options | SELECT_NO_JOIN_CACHE, - result, unit, 0); + result, unit, select_lex, 0); delete result; } else @@ -2037,7 +2124,7 @@ mysql_execute_command(void) (ORDER *)NULL, select_lex->options | thd->options | SELECT_NO_JOIN_CACHE, - result, unit, 0); + result, unit, select_lex, 0); delete result; } else @@ -2803,6 +2890,7 @@ mysql_init_query(THD *thd) thd->lex.unit.global_parameters= &thd->lex.select_lex; //Global limit & order thd->lex.select_lex.master= &thd->lex.unit; thd->lex.select_lex.prev= &thd->lex.unit.slave; + thd->select_number= thd->lex.select_lex.select_number= 1; thd->lex.value_list.empty(); thd->free_list= 0; thd->lex.union_option= 0; @@ -2832,6 +2920,7 @@ bool mysql_new_select(LEX *lex, bool move_down) { SELECT_LEX *select_lex = (SELECT_LEX *) lex->thd->calloc(sizeof(SELECT_LEX)); + select_lex->select_number= ++lex->thd->select_number; if (!select_lex) return 1; select_lex->init_query(); @@ -2874,6 +2963,7 @@ mysql_parse(THD *thd, char *inBuf, uint length) mysql_init_query(thd); thd->query_length = length; + thd->lex.derived_tables= false; if (query_cache_send_result_to_client(thd, inBuf, length) <= 0) { LEX *lex=lex_start(thd, (uchar*) inBuf, length); diff --git a/sql/sql_select.cc b/sql/sql_select.cc index d071743aec4..b87a79cc2c6 100644 --- a/sql/sql_select.cc +++ b/sql/sql_select.cc @@ -144,7 +144,6 @@ static void init_sum_functions(Item_sum **func); static bool update_sum_func(Item_sum **func); static void select_describe(JOIN *join, bool need_tmp_table, bool need_order, bool distinct, const char *message=NullS); -static void describe_info(THD *thd, const char *info); /* This handles SELECT with and without UNION @@ -154,19 +153,7 @@ int handle_select(THD *thd, LEX *lex, select_result *result) { int res; register SELECT_LEX *select_lex = &lex->select_lex; - if (select_lex->next_select_in_list()) - { - /* Fix tables 'to-be-unioned-from' list to point at opened tables */ - for (SELECT_LEX *sl= select_lex; - sl; - sl= sl->next_select_in_list()) - { - for (TABLE_LIST *cursor= (TABLE_LIST *)sl->table_list.first; - cursor; - cursor=cursor->next) - cursor->table= cursor->table_list->table; - } - } + fix_tables_pointers(select_lex); if (select_lex->next_select()) res=mysql_union(thd,lex,result); else @@ -178,7 +165,7 @@ int handle_select(THD *thd, LEX *lex, select_result *result) select_lex->having, (ORDER*) lex->proc_list.first, select_lex->options | thd->options, - result, &(lex->unit), 0); + result, &(lex->unit), &(lex->select_lex), 0); if (res && result) result->abort(); if (res || thd->net.report_error) @@ -190,6 +177,22 @@ int handle_select(THD *thd, LEX *lex, select_result *result) return res; } +void fix_tables_pointers(SELECT_LEX *select_lex) +{ + if (select_lex->next_select_in_list()) + { + /* Fix tables 'to-be-unioned-from' list to point at opened tables */ + for (SELECT_LEX *sl= select_lex; + sl; + sl= sl->next_select_in_list()) + { + for (TABLE_LIST *cursor= (TABLE_LIST *)sl->table_list.first; + cursor; + cursor=cursor->next) + cursor->table= cursor->table_list->table; + } + } +} /***************************************************************************** ** check fields, find best join, do the select and output fields. @@ -209,7 +212,7 @@ JOIN::prepare(TABLE_LIST *tables_init, SELECT_LEX_UNIT *unit, bool fake_select_lex) { DBUG_ENTER("JOIN::prepare"); - + conds= conds_init; order= order_init; group_list= group_init; @@ -344,7 +347,7 @@ int JOIN::optimize() { DBUG_ENTER("JOIN::optimize"); - + #ifdef HAVE_REF_TO_FIELDS // Not done yet /* Add HAVING to WHERE if possible */ if (having && !group_list && ! sum_func_count) @@ -389,11 +392,8 @@ JOIN::optimize() } if (select_options & SELECT_DESCRIBE) { - if (union_part) - select_describe(this, false, false, false, - "Select tables optimized away"); - else - describe_info(thd, "Select tables optimized away"); + select_describe(this, false, false, false, + "Select tables optimized away"); delete procedure; DBUG_RETURN(1); } @@ -647,10 +647,7 @@ JOIN::exec() error=0; if (select_options & SELECT_DESCRIBE) { - if (union_part) - select_describe(this, false, false, false, "No tables used"); - else - describe_info(thd, "No tables used"); + select_describe(this, false, false, false, "No tables used"); } else { @@ -674,16 +671,16 @@ JOIN::exec() if (zero_result_cause) { - if (select_options & SELECT_DESCRIBE && union_part) + if (select_options & SELECT_DESCRIBE) select_describe(this, false, false, false, zero_result_cause); else - error=return_zero_rows(result, tables_list, fields_list, - tmp_table_param.sum_func_count != 0 && - !group_list, - select_options, - zero_result_cause, - having,procedure, - unit); + error= return_zero_rows(result, tables_list, fields_list, + tmp_table_param.sum_func_count != 0 && + !group_list, + select_options, + zero_result_cause, + having,procedure, + unit); DBUG_VOID_RETURN; } @@ -1004,7 +1001,8 @@ int mysql_select(THD *thd, TABLE_LIST *tables, List<Item> &fields, COND *conds, ORDER *order, ORDER *group,Item *having, ORDER *proc_param, ulong select_options, select_result *result, - SELECT_LEX_UNIT *unit, bool fake_select_lex) + SELECT_LEX_UNIT *unit, SELECT_LEX *select_lex, + bool fake_select_lex) { JOIN *join = new JOIN(thd, fields, select_options, result); @@ -1013,7 +1011,7 @@ mysql_select(THD *thd, TABLE_LIST *tables, List<Item> &fields, COND *conds, thd->used_tables=0; // Updated by setup_fields if (join->prepare(tables, conds, order, group, having, proc_param, - &(thd->lex.select_lex), unit, fake_select_lex)) + select_lex, unit, fake_select_lex)) { DBUG_RETURN(-1); } @@ -3087,11 +3085,6 @@ return_zero_rows(select_result *result,TABLE_LIST *tables,List<Item> &fields, { DBUG_ENTER("return_zero_rows"); - if (select_options & SELECT_DESCRIBE) - { - describe_info(current_thd, info); - DBUG_RETURN(0); - } if (procedure) { if (result->prepare(fields, unit)) // This hasn't been done yet @@ -3725,6 +3718,7 @@ create_tmp_table(THD *thd,TMP_TABLE_PARAM *param,List<Item> &fields, table->blob_ptr_size=mi_portable_sizeof_char_ptr; table->map=1; table->tmp_table= TMP_TABLE; + table->derived_select_number= 0; table->db_low_byte_first=1; // True for HEAP and MyISAM table->temp_pool_slot = temp_pool_slot; @@ -7139,7 +7133,6 @@ static void select_describe(JOIN *join, bool need_tmp_table, bool need_order, Item *item; List<Item> item_list; THD *thd=join->thd; - MYSQL_LOCK *save_lock; SELECT_LEX *select_lex = &(join->thd->lex.select_lex); select_result *result=join->result; DBUG_ENTER("select_describe"); @@ -7147,28 +7140,13 @@ static void select_describe(JOIN *join, bool need_tmp_table, bool need_order, /* Don't log this into the slow query log */ select_lex->options&= ~(QUERY_NO_INDEX_USED | QUERY_NO_GOOD_INDEX_USED); join->unit->offset_limit_cnt= 0; - if (thd->lex.select == select_lex) - { - field_list.push_back(new Item_empty_string("table",NAME_LEN)); - field_list.push_back(new Item_empty_string("type",10)); - field_list.push_back(item=new Item_empty_string("possible_keys", - NAME_LEN*MAX_KEY)); - item->maybe_null=1; - field_list.push_back(item=new Item_empty_string("key",NAME_LEN)); - item->maybe_null=1; - field_list.push_back(item=new Item_int("key_len",0,3)); - item->maybe_null=1; - field_list.push_back(item=new Item_empty_string("ref", - NAME_LEN*MAX_REF_PARTS)); - item->maybe_null=1; - field_list.push_back(new Item_real("rows",0.0,0,10)); - field_list.push_back(new Item_empty_string("Extra",255)); - if (result->send_fields(field_list,1)) - return; - } if (message) { + item_list.push_back(new Item_int((int)thd->lex.select->select_number)); + item_list.push_back(new Item_string(thd->lex.select->type, + strlen(thd->lex.select->type), + default_charset_info)); item_list.push_back(new Item_empty_string("",0)); item_list.push_back(new Item_empty_string("",0)); item_list.push_back(new Item_empty_string("",0)); @@ -7192,9 +7170,24 @@ static void select_describe(JOIN *join, bool need_tmp_table, bool need_order, String tmp1(buff1,sizeof(buff1),default_charset_info); String tmp2(buff2,sizeof(buff2),default_charset_info); item_list.empty(); + item_list.push_back(new Item_int((int)thd->lex.select->select_number)); + item_list.push_back(new Item_string(thd->lex.select->type, + strlen(thd->lex.select->type), + default_charset_info)); if (tab->type == JT_ALL && tab->select && tab->select->quick) tab->type= JT_RANGE; - item_list.push_back(new Item_string(table->table_name,strlen(table->table_name),default_charset_info)); + if (table->tmp_table == TMP_TABLE && table->derived_select_number != 0) + { + // Derived table name generation + buff[512]; + int len= my_snprintf(buff, 512, "<derived%u>", + table->derived_select_number); + item_list.push_back(new Item_string(buff, len, default_charset_info)); + } + else + item_list.push_back(new Item_string(table->table_name, + strlen(table->table_name), + default_charset_info)); item_list.push_back(new Item_string(join_type_str[tab->type],strlen(join_type_str[tab->type]),default_charset_info)); tmp1.length(0); tmp2.length(0); key_map bits; @@ -7311,29 +7304,25 @@ static void select_describe(JOIN *join, bool need_tmp_table, bool need_order, result->send_error(0,NullS); } } - if (!join->thd->lex.select->next_select()) - { - save_lock=thd->lock; - thd->lock=(MYSQL_LOCK *)0; - result->send_eof(); - thd->lock=save_lock; - } DBUG_VOID_RETURN; } - -static void describe_info(THD *thd, const char *info) +int mysql_explain_select(THD *thd, SELECT_LEX *select_lex, char const *type, + select_result *result) { - List<Item> field_list; - String *packet= &thd->packet; + select_lex->type= type; + thd->lex.select= select_lex; + SELECT_LEX_UNIT *unit= select_lex->master_unit(); + int res= mysql_select(thd,(TABLE_LIST*) select_lex->table_list.first, + select_lex->item_list, + select_lex->where, + (ORDER*) select_lex->order_list.first, + (ORDER*) select_lex->group_list.first, + select_lex->having, + (ORDER*) thd->lex.proc_list.first, + select_lex->options | thd->options | SELECT_DESCRIBE, + result, unit, select_lex, 0); - /* Don't log this into the slow query log */ - thd->lex.select_lex.options&= ~(QUERY_NO_INDEX_USED | QUERY_NO_GOOD_INDEX_USED); - field_list.push_back(new Item_empty_string("Comment",80)); - if (send_fields(thd,field_list,1)) - return; /* purecov: inspected */ - packet->length(0); - net_store_data(packet,info); - if (!my_net_write(&thd->net,(char*) packet->ptr(),packet->length())) - send_eof(&thd->net); + return res; } + diff --git a/sql/sql_union.cc b/sql/sql_union.cc index e8ee3582135..f6ac17e5fe3 100644 --- a/sql/sql_union.cc +++ b/sql/sql_union.cc @@ -111,6 +111,7 @@ int st_select_lex_unit::prepare(THD *thd, select_result *result) DBUG_ENTER("st_select_lex_unit::prepare"); this->thd= thd; this->result= result; + SELECT_LEX *lex_select_save= thd->lex.select; /* Global option */ if (((void*)(global_parameters)) == ((void*)this)) @@ -148,9 +149,9 @@ int st_select_lex_unit::prepare(THD *thd, select_result *result) /* Create a list of items that will be in the result set */ while ((item= it++)) if (item_list.push_back(item)) - DBUG_RETURN(-1); + goto err; if (setup_fields(thd,first_table,item_list,0,0,1)) - DBUG_RETURN(-1); + goto err; } bzero((char*) &tmp_table_param,sizeof(tmp_table_param)); @@ -162,7 +163,7 @@ int st_select_lex_unit::prepare(THD *thd, select_result *result) (first_select()->options | thd->options | TMP_TABLE_ALL_COLUMNS), this))) - DBUG_RETURN(-1); + goto err; table->file->extra(HA_EXTRA_WRITE_CACHE); table->file->extra(HA_EXTRA_IGNORE_DUP_KEY); bzero((char*) &result_table_list,sizeof(result_table_list)); @@ -171,7 +172,7 @@ int st_select_lex_unit::prepare(THD *thd, select_result *result) result_table_list.table=table; if (!(union_result=new select_union(table))) - DBUG_RETURN(-1); + goto err; union_result->save_time_stamp=!describe; union_result->tmp_table_param=&tmp_table_param; @@ -202,9 +203,13 @@ int st_select_lex_unit::prepare(THD *thd, select_result *result) (ORDER*) NULL, sl, this, 0); if (res | thd->fatal_error) - DBUG_RETURN(res | thd->fatal_error); + goto err; } + thd->lex.select= lex_select_save; DBUG_RETURN(res | thd->fatal_error); +err: + thd->lex.select= lex_select_save; + DBUG_RETURN(-1); } int st_select_lex_unit::exec() @@ -214,7 +219,7 @@ int st_select_lex_unit::exec() { if (optimized && item && item->assigned()) item->assigned(0); // We will reinit & rexecute unit - + SELECT_LEX *lex_select_save= thd->lex.select; for (SELECT_LEX *sl= first_select(); sl; sl= sl->next_select()) { thd->lex.select=sl; @@ -234,8 +239,12 @@ int st_select_lex_unit::exec() res= sl->join->error; if (res) + { + thd->lex.select= lex_select_save; DBUG_RETURN(res); + } } + thd->lex.select= lex_select_save; optimized= 1; } @@ -283,7 +292,7 @@ int st_select_lex_unit::exec() 0: (ORDER*)global_parameters->order_list.first, (ORDER*) NULL, NULL, (ORDER*) NULL, - thd->options, result, this, 1); + thd->options, result, this, first_select(), 1); if (found_rows_for_union && !res) thd->limit_found_rows = (ulonglong)table->file->records; } diff --git a/sql/table.h b/sql/table.h index b89701bfc8e..3b8a62a09b5 100644 --- a/sql/table.h +++ b/sql/table.h @@ -128,6 +128,9 @@ struct st_table { uint temp_pool_slot; + /* number of select if it is derived table */ + uint derived_select_number; + THD *in_use; /* Which thread uses this */ struct st_table *next,*prev; }; |