From 1a4f499ca87634d4256d2531bab68b32f3e5fa49 Mon Sep 17 00:00:00 2001 From: unknown Date: Thu, 6 May 2004 20:40:21 +0300 Subject: EXPLAIN UNION using same routing which used for execution which allow return correct bug messages (Bug #3639) EXPLAIN of hidden SELECT of UNION mysql-test/r/derived.result: explain of hidden select mysql-test/r/subselect.result: explain of hidden select mysql-test/r/union.result: explain of hidden select correct error messages on explain mysql-test/t/subselect.test: show eliminated costants in WHERE clause mysql-test/t/union.test: correct error messages on EXPLAIN with union sql/item.cc: fixed name constructing for global ORDER BY items sql/sql_class.h: select ID can be negative (for hidden SELECTs) removed unused field sql/sql_lex.cc: new flag of UNION EXPLAIN sql/sql_lex.h: new flag of UNION EXPLAIN select ID can be negative (for hidden SELECTs) sql/sql_select.cc: EXPLAIN UNION using same routing which used for execution explain for hidden SELECT of UNION sql/sql_union.cc: EXPLAIN UNION using same routing which used for execution --- mysql-test/r/union.result | 23 +++++++++++++---------- 1 file changed, 13 insertions(+), 10 deletions(-) (limited to 'mysql-test/r/union.result') diff --git a/mysql-test/r/union.result b/mysql-test/r/union.result index bf8b8df7c65..d14c4408946 100644 --- a/mysql-test/r/union.result +++ b/mysql-test/r/union.result @@ -86,6 +86,7 @@ explain extended (select a,b from t1 limit 2) union all (select a,b from t2 ord id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 4 2 UNION t2 ALL NULL NULL NULL NULL 4 Using filesort +-1 UNION RESULT ALL NULL NULL NULL NULL NULL Using filesort Warnings: Note 1003 (select high_priority test.t1.a AS `a`,test.t1.b AS `b` from test.t1 limit 2) union all (select test.t2.a AS `a`,test.t2.b AS `b` from test.t2 order by test.t2.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; @@ -106,21 +107,15 @@ explain select a,b from t1 union all select a,b from t2; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 4 2 UNION t2 ALL NULL NULL NULL NULL 4 +-1 UNION RESULT ALL NULL NULL NULL NULL NULL explain select xx from t1 union select 1; ERROR 42S22: Unknown column 'xx' in 'field list' explain select a,b from t1 union select 1; -id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY t1 ALL NULL NULL NULL NULL 4 -2 UNION NULL NULL NULL NULL NULL NULL NULL No tables used +ERROR 21000: The used SELECT statements have a different number of columns explain select 1 union select a,b from t1 union select 1; -id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY NULL NULL NULL NULL NULL NULL NULL No tables used -2 UNION t1 ALL NULL NULL NULL NULL 4 -3 UNION NULL NULL NULL NULL NULL NULL NULL No tables used +ERROR 21000: The used SELECT statements have a different number of columns explain select a,b from t1 union select 1 limit 0; -id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE -2 UNION NULL NULL NULL NULL NULL NULL NULL Impossible WHERE +ERROR 21000: The used SELECT statements have a different number of columns select a,b from t1 into outfile 'skr' union select a,b from t2; ERROR HY000: Wrong usage of UNION and INTO select a,b from t1 order by a union select a,b from t2; @@ -475,6 +470,7 @@ explain extended (select * from t1 where a=1) union (select * from t2 where a=1) id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 const PRIMARY PRIMARY 4 const 1 2 UNION t2 const PRIMARY PRIMARY 4 const 1 +-1 UNION RESULT ALL NULL NULL NULL NULL NULL Warnings: Note 1003 (select high_priority test.t1.a AS `a`,test.t1.b AS `b` from test.t1 where (test.t1.a = 1)) union (select test.t2.a AS `a`,test.t2.b AS `b` from test.t2 where (test.t2.a = 1)) (select * from t1 where a=5) union (select * from t2 where a=1); @@ -497,10 +493,12 @@ id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables 2 UNION t1 index PRIMARY PRIMARY 4 NULL 4 Using index 2 UNION t2 index PRIMARY PRIMARY 4 NULL 3 Using where; Using index +-1 UNION RESULT ALL NULL NULL NULL NULL NULL explain (select * from t1 where a=1) union (select * from t1 where b=1); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 const PRIMARY PRIMARY 4 const 1 2 UNION t1 ref b b 5 const 1 Using where +-1 UNION RESULT ALL NULL NULL NULL NULL NULL drop table t1,t2; create table t1 ( id int not null auto_increment, primary key (id) ,user_name text ); create table t2 ( id int not null auto_increment, primary key (id) ,group_name text ); @@ -950,3 +948,8 @@ a 1 drop table t1, t2; set sql_select_limit=default; +CREATE TABLE t1 (i int(11) default NULL,c char(1) default NULL,KEY i (i)); +CREATE TABLE t2 (i int(11) default NULL,c char(1) default NULL,KEY i (i)); +explain (select * from t1) union (select * from t2) order by not_existing_column; +ERROR 42S22: Unknown column 'not_existing_column' in 'order clause' +drop table t1, t2; -- cgit v1.2.1