#At file:///work/bzrroot/mysql-6.0-codebase-bugfixing/ based on revid:jorgen.loland@stripped
3625 Evgeny Potemkin 2009-09-28 [merge]
Auto-merged fix for the bug#30302.
modified:
mysql-test/r/explain.result
mysql-test/r/func_default.result
mysql-test/r/func_regexp.result
mysql-test/r/func_test.result
mysql-test/r/having.result
mysql-test/r/select.result
mysql-test/r/select_jcl6.result
mysql-test/r/subselect.result
mysql-test/r/subselect_no_mat.result
mysql-test/r/subselect_no_opts.result
mysql-test/r/subselect_no_semijoin.result
mysql-test/r/type_datetime.result
mysql-test/t/explain.test
sql/sql_select.cc
sql/table.h
=== modified file 'mysql-test/r/explain.result'
--- a/mysql-test/r/explain.result 2009-09-21 12:56:38 +0000
+++ b/mysql-test/r/explain.result 2009-09-28 15:58:30 +0000
@@ -64,7 +64,7 @@ explain extended select * from v1 where
id select_type table type possible_keys key key_len ref rows filtered Extra
1 SIMPLE t1 system NULL NULL NULL NULL 1 100.00
Warnings:
-Note 1003 select '1' AS `f1`,'1' AS `f2` from `test`.`t1` where 1
+Note 1003 select '1' AS `f1`,'1' AS `f2` from dual where 1
explain extended select * from t1 where 0;
id select_type table type possible_keys key key_len ref rows filtered Extra
1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE
@@ -74,7 +74,7 @@ explain extended select * from t1 where
id select_type table type possible_keys key key_len ref rows filtered Extra
1 SIMPLE t1 system NULL NULL NULL NULL 1 100.00
Warnings:
-Note 1003 select '1' AS `f1`,'1' AS `f2` from `test`.`t1` where 1
+Note 1003 select '1' AS `f1`,'1' AS `f2` from dual where 1
explain extended select * from t1 having 0;
id select_type table type possible_keys key key_len ref rows filtered Extra
1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL Impossible HAVING
@@ -84,7 +84,7 @@ explain extended select * from t1 having
id select_type table type possible_keys key key_len ref rows filtered Extra
1 SIMPLE t1 system NULL NULL NULL NULL 1 100.00
Warnings:
-Note 1003 select '1' AS `f1`,'1' AS `f2` from `test`.`t1` having 1
+Note 1003 select '1' AS `f1`,'1' AS `f2` from dual having 1
drop view v1;
drop table t1;
CREATE TABLE t1(c INT);
@@ -223,4 +223,24 @@ explain extended select 1 from `t1`, `t1
where `t1`.`a` > all ( (select `a` from `t1` ) union (select `a`) );
ERROR 23000: Column 'a' in field list is ambiguous
drop table t1;
+#
+# Bug#30302: Tables that were optimized away are printed in the
+# EXPLAIN EXTENDED warning.
+#
+create table t1(f1 int);
+create table t2(f2 int);
+insert into t1 values(1);
+insert into t2 values(1),(2);
+explain extended select * from t1 where f1=1;
+id select_type table type possible_keys key key_len ref rows filtered Extra
+1 SIMPLE t1 system NULL NULL NULL NULL 1 100.00
+Warnings:
+Note 1003 select '1' AS `f1` from dual where 1
+explain extended select * from t1 join t2 on f1=f2 where f1=1;
+id select_type table type possible_keys key key_len ref rows filtered Extra
+1 SIMPLE t1 system NULL NULL NULL NULL 1 100.00
+1 SIMPLE t2 ALL NULL NULL NULL NULL 2 100.00 Using where
+Warnings:
+Note 1003 select '1' AS `f1`,`test`.`t2`.`f2` AS `f2` from `test`.`t2` where (`test`.`t2`.`f2` = 1)
+drop table t1,t2;
End of 6.0 tests.
=== modified file 'mysql-test/r/func_default.result'
--- a/mysql-test/r/func_default.result 2007-03-10 21:26:45 +0000
+++ b/mysql-test/r/func_default.result 2009-09-28 15:58:30 +0000
@@ -8,7 +8,7 @@ explain extended select default(str), de
id select_type table type possible_keys key key_len ref rows filtered Extra
1 SIMPLE t1 system NULL NULL NULL NULL 1 100.00
Warnings:
-Note 1003 select default('') AS `default(str)`,default('') AS `default(strnull)`,default('0') AS `default(intg)`,default('0') AS `default(rel)` from `test`.`t1`
+Note 1003 select default('') AS `default(str)`,default('') AS `default(strnull)`,default('0') AS `default(intg)`,default('0') AS `default(rel)` from dual
select * from t1 where str <> default(str);
str strnull intg rel
0 0
=== modified file 'mysql-test/r/func_regexp.result'
--- a/mysql-test/r/func_regexp.result 2008-09-05 08:36:02 +0000
+++ b/mysql-test/r/func_regexp.result 2009-09-28 15:58:30 +0000
@@ -52,7 +52,7 @@ explain extended select * from t1 where
id select_type table type possible_keys key key_len ref rows filtered Extra
1 SIMPLE t1 system NULL NULL NULL NULL 1 100.00
Warnings:
-Note 1003 select 'this is a test of some long text to see what happens' AS `xxx` from `test`.`t1` where ('this is a test of some long text to see what happens' regexp 'is a test of some long text to')
+Note 1003 select 'this is a test of some long text to see what happens' AS `xxx` from dual where ('this is a test of some long text to see what happens' regexp 'is a test of some long text to')
select * from t1 where xxx regexp('is a test of some long text to ');
xxx
this is a test of some long text to see what happens
=== modified file 'mysql-test/r/func_test.result'
--- a/mysql-test/r/func_test.result 2007-03-10 21:26:45 +0000
+++ b/mysql-test/r/func_test.result 2009-09-28 15:58:30 +0000
@@ -87,7 +87,7 @@ explain extended select - a from t1;
id select_type table type possible_keys key key_len ref rows filtered Extra
1 SIMPLE t1 system NULL NULL NULL NULL 1 100.00
Warnings:
-Note 1003 select -('1') AS `- a` from `test`.`t1`
+Note 1003 select -('1') AS `- a` from dual
drop table t1;
select 5 between 0 and 10 between 0 and 1,(5 between 0 and 10) between 0 and 1;
5 between 0 and 10 between 0 and 1 (5 between 0 and 10) between 0 and 1
=== modified file 'mysql-test/r/having.result'
--- a/mysql-test/r/having.result 2009-01-16 15:38:38 +0000
+++ b/mysql-test/r/having.result 2009-09-28 15:58:30 +0000
@@ -12,7 +12,7 @@ explain extended select count(a) as b fr
id select_type table type possible_keys key key_len ref rows filtered Extra
1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables
Warnings:
-Note 1003 select count('0') AS `b` from `test`.`t1` where 0 having (`b` >= 0)
+Note 1003 select count('0') AS `b` from dual where 0 having (`b` >= 0)
drop table t1;
CREATE TABLE t1 (
raw_id int(10) NOT NULL default '0',
=== modified file 'mysql-test/r/select.result'
--- a/mysql-test/r/select.result 2009-09-22 20:18:14 +0000
+++ b/mysql-test/r/select.result 2009-09-28 15:58:30 +0000
@@ -4422,7 +4422,7 @@ EXPLAIN EXTENDED SELECT * FROM t1 WHERE
id select_type table type possible_keys key key_len ref rows filtered Extra
1 SIMPLE t1 system NULL NULL NULL NULL 1 100.00
Warnings:
-Note 1003 select '1' AS `a`,'1' AS `b` from `test`.`t1` where 1
+Note 1003 select '1' AS `a`,'1' AS `b` from dual where 1
SELECT * FROM t1 WHERE (a=a AND a=a) OR b > 2;
a b
1 1
=== modified file 'mysql-test/r/select_jcl6.result'
--- a/mysql-test/r/select_jcl6.result 2009-09-22 20:18:14 +0000
+++ b/mysql-test/r/select_jcl6.result 2009-09-28 15:58:30 +0000
@@ -4426,7 +4426,7 @@ EXPLAIN EXTENDED SELECT * FROM t1 WHERE
id select_type table type possible_keys key key_len ref rows filtered Extra
1 SIMPLE t1 system NULL NULL NULL NULL 1 100.00
Warnings:
-Note 1003 select '1' AS `a`,'1' AS `b` from `test`.`t1` where 1
+Note 1003 select '1' AS `a`,'1' AS `b` from dual where 1
SELECT * FROM t1 WHERE (a=a AND a=a) OR b > 2;
a b
1 1
=== modified file 'mysql-test/r/subselect.result'
--- a/mysql-test/r/subselect.result 2009-08-31 13:50:36 +0000
+++ b/mysql-test/r/subselect.result 2009-09-28 15:58:30 +0000
@@ -50,7 +50,7 @@ id select_type table type possible_keys
Warnings:
Note 1276 Field or reference 'b.a' of SELECT #3 was resolved in SELECT #1
Note 1276 Field or reference 'b.a' of SELECT #3 was resolved in SELECT #1
-Note 1003 select 1 AS `1` from (select 1 AS `a`) `b` having ((select '1' AS `a`) = 1)
+Note 1003 select 1 AS `1` from dual having ((select '1' AS `a`) = 1)
SELECT 1 FROM (SELECT 1 as a) as b HAVING (SELECT a)=1;
1
1
@@ -203,7 +203,7 @@ id select_type table type possible_keys
3 DERIVED t2 ALL NULL NULL NULL NULL 2 100.00 Using where
2 SUBQUERY t3 ALL NULL NULL NULL NULL 3 100.00 Using where; Using filesort
Warnings:
-Note 1003 select (select `test`.`t3`.`a` AS `a` from `test`.`t3` where (`test`.`t3`.`a` < 8) order by 1 desc limit 1) AS `(select t3.a from t3 where a<8 order by 1 desc limit 1)`,'2' AS `a` from (select `test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b` from `test`.`t2` where (`test`.`t2`.`a` > 1)) `tt`
+Note 1003 select (select `test`.`t3`.`a` AS `a` from `test`.`t3` where (`test`.`t3`.`a` < 8) order by 1 desc limit 1) AS `(select t3.a from t3 where a<8 order by 1 desc limit 1)`,'2' AS `a` from dual
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
@@ -314,7 +314,7 @@ NULL UNION RESULT <union2,3> ALL NULL NU
Warnings:
Note 1276 Field or reference 'test.t2.a' of SELECT #2 was resolved in SELECT #1
Note 1276 Field or reference 'test.t2.a' of SELECT #3 was resolved in SELECT #1
-Note 1003 select (select '2' AS `a` from `test`.`t1` where ('2' = `test`.`t2`.`a`) union select `test`.`t5`.`a` AS `a` from `test`.`t5` where (`test`.`t5`.`a` = `test`.`t2`.`a`)) AS `(select a from t1 where t1.a=t2.a union select a from t5 where t5.a=t2.a)`,`test`.`t2`.`a` AS `a` from `test`.`t2`
+Note 1003 select (select '2' AS `a` from dual where ('2' = `test`.`t2`.`a`) union select `test`.`t5`.`a` AS `a` from `test`.`t5` where (`test`.`t5`.`a` = `test`.`t2`.`a`)) AS `(select a from t1 where t1.a=t2.a union select a from t5 where t5.a=t2.a)`,`test`.`t2`.`a` AS `a` from `test`.`t2`
select (select a from t1 where t1.a=t2.a union all select a from t5 where t5.a=t2.a), a from t2;
ERROR 21000: Subquery returns more than 1 row
create table t6 (patient_uq int, clinic_uq int, index i1 (clinic_uq));
@@ -1433,7 +1433,7 @@ explain extended (select * from t1);
id select_type table type possible_keys key key_len ref rows filtered Extra
1 SIMPLE t1 system NULL NULL NULL NULL 1 100.00
Warnings:
-Note 1003 (select 'tttt' AS `s1` from `test`.`t1`)
+Note 1003 (select 'tttt' AS `s1` from dual)
(select * from t1);
s1
tttt
@@ -1621,7 +1621,7 @@ id select_type table type possible_keys
3 UNION t1 system NULL NULL NULL NULL 1 100.00
NULL UNION RESULT <union2,3> ALL NULL NULL NULL NULL NULL NULL
Warnings:
-Note 1003 select 'e' AS `s1` from `test`.`t1` where 1
+Note 1003 select 'e' AS `s1` from dual where 1
drop table t1;
CREATE TABLE t1 (number char(11) NOT NULL default '') ENGINE=MyISAM CHARSET=latin1;
INSERT INTO t1 VALUES ('69294728265'),('18621828126'),('89356874041'),('95895001874');
=== modified file 'mysql-test/r/subselect_no_mat.result'
--- a/mysql-test/r/subselect_no_mat.result 2009-08-31 20:03:03 +0000
+++ b/mysql-test/r/subselect_no_mat.result 2009-09-28 15:58:30 +0000
@@ -54,7 +54,7 @@ id select_type table type possible_keys
Warnings:
Note 1276 Field or reference 'b.a' of SELECT #3 was resolved in SELECT #1
Note 1276 Field or reference 'b.a' of SELECT #3 was resolved in SELECT #1
-Note 1003 select 1 AS `1` from (select 1 AS `a`) `b` having ((select '1' AS `a`) = 1)
+Note 1003 select 1 AS `1` from dual having ((select '1' AS `a`) = 1)
SELECT 1 FROM (SELECT 1 as a) as b HAVING (SELECT a)=1;
1
1
@@ -207,7 +207,7 @@ id select_type table type possible_keys
3 DERIVED t2 ALL NULL NULL NULL NULL 2 100.00 Using where
2 SUBQUERY t3 ALL NULL NULL NULL NULL 3 100.00 Using where; Using filesort
Warnings:
-Note 1003 select (select `test`.`t3`.`a` AS `a` from `test`.`t3` where (`test`.`t3`.`a` < 8) order by 1 desc limit 1) AS `(select t3.a from t3 where a<8 order by 1 desc limit 1)`,'2' AS `a` from (select `test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b` from `test`.`t2` where (`test`.`t2`.`a` > 1)) `tt`
+Note 1003 select (select `test`.`t3`.`a` AS `a` from `test`.`t3` where (`test`.`t3`.`a` < 8) order by 1 desc limit 1) AS `(select t3.a from t3 where a<8 order by 1 desc limit 1)`,'2' AS `a` from dual
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
@@ -318,7 +318,7 @@ NULL UNION RESULT <union2,3> ALL NULL NU
Warnings:
Note 1276 Field or reference 'test.t2.a' of SELECT #2 was resolved in SELECT #1
Note 1276 Field or reference 'test.t2.a' of SELECT #3 was resolved in SELECT #1
-Note 1003 select (select '2' AS `a` from `test`.`t1` where ('2' = `test`.`t2`.`a`) union select `test`.`t5`.`a` AS `a` from `test`.`t5` where (`test`.`t5`.`a` = `test`.`t2`.`a`)) AS `(select a from t1 where t1.a=t2.a union select a from t5 where t5.a=t2.a)`,`test`.`t2`.`a` AS `a` from `test`.`t2`
+Note 1003 select (select '2' AS `a` from dual where ('2' = `test`.`t2`.`a`) union select `test`.`t5`.`a` AS `a` from `test`.`t5` where (`test`.`t5`.`a` = `test`.`t2`.`a`)) AS `(select a from t1 where t1.a=t2.a union select a from t5 where t5.a=t2.a)`,`test`.`t2`.`a` AS `a` from `test`.`t2`
select (select a from t1 where t1.a=t2.a union all select a from t5 where t5.a=t2.a), a from t2;
ERROR 21000: Subquery returns more than 1 row
create table t6 (patient_uq int, clinic_uq int, index i1 (clinic_uq));
@@ -1437,7 +1437,7 @@ explain extended (select * from t1);
id select_type table type possible_keys key key_len ref rows filtered Extra
1 SIMPLE t1 system NULL NULL NULL NULL 1 100.00
Warnings:
-Note 1003 (select 'tttt' AS `s1` from `test`.`t1`)
+Note 1003 (select 'tttt' AS `s1` from dual)
(select * from t1);
s1
tttt
@@ -1625,7 +1625,7 @@ id select_type table type possible_keys
3 UNION t1 system NULL NULL NULL NULL 1 100.00
NULL UNION RESULT <union2,3> ALL NULL NULL NULL NULL NULL NULL
Warnings:
-Note 1003 select 'e' AS `s1` from `test`.`t1` where 1
+Note 1003 select 'e' AS `s1` from dual where 1
drop table t1;
CREATE TABLE t1 (number char(11) NOT NULL default '') ENGINE=MyISAM CHARSET=latin1;
INSERT INTO t1 VALUES ('69294728265'),('18621828126'),('89356874041'),('95895001874');
=== modified file 'mysql-test/r/subselect_no_opts.result'
--- a/mysql-test/r/subselect_no_opts.result 2009-08-31 20:03:03 +0000
+++ b/mysql-test/r/subselect_no_opts.result 2009-09-28 15:58:30 +0000
@@ -54,7 +54,7 @@ id select_type table type possible_keys
Warnings:
Note 1276 Field or reference 'b.a' of SELECT #3 was resolved in SELECT #1
Note 1276 Field or reference 'b.a' of SELECT #3 was resolved in SELECT #1
-Note 1003 select 1 AS `1` from (select 1 AS `a`) `b` having ((select '1' AS `a`) = 1)
+Note 1003 select 1 AS `1` from dual having ((select '1' AS `a`) = 1)
SELECT 1 FROM (SELECT 1 as a) as b HAVING (SELECT a)=1;
1
1
@@ -207,7 +207,7 @@ id select_type table type possible_keys
3 DERIVED t2 ALL NULL NULL NULL NULL 2 100.00 Using where
2 SUBQUERY t3 ALL NULL NULL NULL NULL 3 100.00 Using where; Using filesort
Warnings:
-Note 1003 select (select `test`.`t3`.`a` AS `a` from `test`.`t3` where (`test`.`t3`.`a` < 8) order by 1 desc limit 1) AS `(select t3.a from t3 where a<8 order by 1 desc limit 1)`,'2' AS `a` from (select `test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b` from `test`.`t2` where (`test`.`t2`.`a` > 1)) `tt`
+Note 1003 select (select `test`.`t3`.`a` AS `a` from `test`.`t3` where (`test`.`t3`.`a` < 8) order by 1 desc limit 1) AS `(select t3.a from t3 where a<8 order by 1 desc limit 1)`,'2' AS `a` from dual
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
@@ -318,7 +318,7 @@ NULL UNION RESULT <union2,3> ALL NULL NU
Warnings:
Note 1276 Field or reference 'test.t2.a' of SELECT #2 was resolved in SELECT #1
Note 1276 Field or reference 'test.t2.a' of SELECT #3 was resolved in SELECT #1
-Note 1003 select (select '2' AS `a` from `test`.`t1` where ('2' = `test`.`t2`.`a`) union select `test`.`t5`.`a` AS `a` from `test`.`t5` where (`test`.`t5`.`a` = `test`.`t2`.`a`)) AS `(select a from t1 where t1.a=t2.a union select a from t5 where t5.a=t2.a)`,`test`.`t2`.`a` AS `a` from `test`.`t2`
+Note 1003 select (select '2' AS `a` from dual where ('2' = `test`.`t2`.`a`) union select `test`.`t5`.`a` AS `a` from `test`.`t5` where (`test`.`t5`.`a` = `test`.`t2`.`a`)) AS `(select a from t1 where t1.a=t2.a union select a from t5 where t5.a=t2.a)`,`test`.`t2`.`a` AS `a` from `test`.`t2`
select (select a from t1 where t1.a=t2.a union all select a from t5 where t5.a=t2.a), a from t2;
ERROR 21000: Subquery returns more than 1 row
create table t6 (patient_uq int, clinic_uq int, index i1 (clinic_uq));
@@ -1437,7 +1437,7 @@ explain extended (select * from t1);
id select_type table type possible_keys key key_len ref rows filtered Extra
1 SIMPLE t1 system NULL NULL NULL NULL 1 100.00
Warnings:
-Note 1003 (select 'tttt' AS `s1` from `test`.`t1`)
+Note 1003 (select 'tttt' AS `s1` from dual)
(select * from t1);
s1
tttt
@@ -1625,7 +1625,7 @@ id select_type table type possible_keys
3 UNION t1 system NULL NULL NULL NULL 1 100.00
NULL UNION RESULT <union2,3> ALL NULL NULL NULL NULL NULL NULL
Warnings:
-Note 1003 select 'e' AS `s1` from `test`.`t1` where 1
+Note 1003 select 'e' AS `s1` from dual where 1
drop table t1;
CREATE TABLE t1 (number char(11) NOT NULL default '') ENGINE=MyISAM CHARSET=latin1;
INSERT INTO t1 VALUES ('69294728265'),('18621828126'),('89356874041'),('95895001874');
=== modified file 'mysql-test/r/subselect_no_semijoin.result'
--- a/mysql-test/r/subselect_no_semijoin.result 2009-08-31 20:03:03 +0000
+++ b/mysql-test/r/subselect_no_semijoin.result 2009-09-28 15:58:30 +0000
@@ -54,7 +54,7 @@ id select_type table type possible_keys
Warnings:
Note 1276 Field or reference 'b.a' of SELECT #3 was resolved in SELECT #1
Note 1276 Field or reference 'b.a' of SELECT #3 was resolved in SELECT #1
-Note 1003 select 1 AS `1` from (select 1 AS `a`) `b` having ((select '1' AS `a`) = 1)
+Note 1003 select 1 AS `1` from dual having ((select '1' AS `a`) = 1)
SELECT 1 FROM (SELECT 1 as a) as b HAVING (SELECT a)=1;
1
1
@@ -207,7 +207,7 @@ id select_type table type possible_keys
3 DERIVED t2 ALL NULL NULL NULL NULL 2 100.00 Using where
2 SUBQUERY t3 ALL NULL NULL NULL NULL 3 100.00 Using where; Using filesort
Warnings:
-Note 1003 select (select `test`.`t3`.`a` AS `a` from `test`.`t3` where (`test`.`t3`.`a` < 8) order by 1 desc limit 1) AS `(select t3.a from t3 where a<8 order by 1 desc limit 1)`,'2' AS `a` from (select `test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b` from `test`.`t2` where (`test`.`t2`.`a` > 1)) `tt`
+Note 1003 select (select `test`.`t3`.`a` AS `a` from `test`.`t3` where (`test`.`t3`.`a` < 8) order by 1 desc limit 1) AS `(select t3.a from t3 where a<8 order by 1 desc limit 1)`,'2' AS `a` from dual
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
@@ -318,7 +318,7 @@ NULL UNION RESULT <union2,3> ALL NULL NU
Warnings:
Note 1276 Field or reference 'test.t2.a' of SELECT #2 was resolved in SELECT #1
Note 1276 Field or reference 'test.t2.a' of SELECT #3 was resolved in SELECT #1
-Note 1003 select (select '2' AS `a` from `test`.`t1` where ('2' = `test`.`t2`.`a`) union select `test`.`t5`.`a` AS `a` from `test`.`t5` where (`test`.`t5`.`a` = `test`.`t2`.`a`)) AS `(select a from t1 where t1.a=t2.a union select a from t5 where t5.a=t2.a)`,`test`.`t2`.`a` AS `a` from `test`.`t2`
+Note 1003 select (select '2' AS `a` from dual where ('2' = `test`.`t2`.`a`) union select `test`.`t5`.`a` AS `a` from `test`.`t5` where (`test`.`t5`.`a` = `test`.`t2`.`a`)) AS `(select a from t1 where t1.a=t2.a union select a from t5 where t5.a=t2.a)`,`test`.`t2`.`a` AS `a` from `test`.`t2`
select (select a from t1 where t1.a=t2.a union all select a from t5 where t5.a=t2.a), a from t2;
ERROR 21000: Subquery returns more than 1 row
create table t6 (patient_uq int, clinic_uq int, index i1 (clinic_uq));
@@ -1437,7 +1437,7 @@ explain extended (select * from t1);
id select_type table type possible_keys key key_len ref rows filtered Extra
1 SIMPLE t1 system NULL NULL NULL NULL 1 100.00
Warnings:
-Note 1003 (select 'tttt' AS `s1` from `test`.`t1`)
+Note 1003 (select 'tttt' AS `s1` from dual)
(select * from t1);
s1
tttt
@@ -1625,7 +1625,7 @@ id select_type table type possible_keys
3 UNION t1 system NULL NULL NULL NULL 1 100.00
NULL UNION RESULT <union2,3> ALL NULL NULL NULL NULL NULL NULL
Warnings:
-Note 1003 select 'e' AS `s1` from `test`.`t1` where 1
+Note 1003 select 'e' AS `s1` from dual where 1
drop table t1;
CREATE TABLE t1 (number char(11) NOT NULL default '') ENGINE=MyISAM CHARSET=latin1;
INSERT INTO t1 VALUES ('69294728265'),('18621828126'),('89356874041'),('95895001874');
=== modified file 'mysql-test/r/type_datetime.result'
--- a/mysql-test/r/type_datetime.result 2009-03-19 17:03:58 +0000
+++ b/mysql-test/r/type_datetime.result 2009-09-28 15:58:30 +0000
@@ -518,7 +518,7 @@ id select_type table type possible_keys
2 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE
Warnings:
Note 1276 Field or reference 'test.t1.cur_date' of SELECT #2 was resolved in SELECT #1
-Note 1003 select '1' AS `id`,'2007-04-25 18:30:22' AS `cur_date` from `test`.`t1` where <in_optimizer>('1',<exists>(select 1 AS `Not_used` from `test`.`t1` `x1` where 0))
+Note 1003 select '1' AS `id`,'2007-04-25 18:30:22' AS `cur_date` from dual where <in_optimizer>('1',<exists>(select 1 AS `Not_used` from `test`.`t1` `x1` where 0))
select * from t1
where id in (select id from t1 as x1 where (t1.cur_date is null));
id cur_date
@@ -530,7 +530,7 @@ id select_type table type possible_keys
2 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE
Warnings:
Note 1276 Field or reference 'test.t2.cur_date' of SELECT #2 was resolved in SELECT #1
-Note 1003 select '1' AS `id`,'2007-04-25' AS `cur_date` from `test`.`t2` where <in_optimizer>('1',<exists>(select 1 AS `Not_used` from `test`.`t2` `x1` where 0))
+Note 1003 select '1' AS `id`,'2007-04-25' AS `cur_date` from dual where <in_optimizer>('1',<exists>(select 1 AS `Not_used` from `test`.`t2` `x1` where 0))
select * from t2
where id in (select id from t2 as x1 where (t2.cur_date is null));
id cur_date
=== modified file 'mysql-test/t/explain.test'
--- a/mysql-test/t/explain.test 2009-09-21 12:56:38 +0000
+++ b/mysql-test/t/explain.test 2009-09-28 15:58:30 +0000
@@ -189,4 +189,17 @@ explain extended select 1 from `t1`, `t1
where `t1`.`a` > all ( (select `a` from `t1` ) union (select `a`) );
drop table t1;
+
+--echo #
+--echo # Bug#30302: Tables that were optimized away are printed in the
+--echo # EXPLAIN EXTENDED warning.
+--echo #
+create table t1(f1 int);
+create table t2(f2 int);
+insert into t1 values(1);
+insert into t2 values(1),(2);
+explain extended select * from t1 where f1=1;
+explain extended select * from t1 join t2 on f1=f2 where f1=1;
+drop table t1,t2;
+
--echo End of 6.0 tests.
=== modified file 'sql/sql_select.cc'
--- a/sql/sql_select.cc 2009-09-25 14:49:52 +0000
+++ b/sql/sql_select.cc 2009-09-28 16:23:25 +0000
@@ -4191,7 +4191,10 @@ make_join_statistics(JOIN *join, TABLE_L
goto error; // Fatal error
}
else
+ {
found_const_table_map|= s->table->map;
+ s->table->pos_in_table_list->optimized_away= TRUE;
+ }
}
/* loop until no more const tables are found */
@@ -21926,21 +21929,38 @@ static void print_join(THD *thd,
{
/* List is reversed => we should reverse it before using */
List_iterator_fast<TABLE_LIST> ti(*tables);
- TABLE_LIST **table= (TABLE_LIST **)thd->alloc(sizeof(TABLE_LIST*) *
- tables->elements);
- if (table == 0)
+ TABLE_LIST **table;
+ uint non_const_tables= 0;
+
+ for (TABLE_LIST *t= ti++; t ; t= ti++)
+ if (!t->optimized_away)
+ non_const_tables++;
+ if (!non_const_tables)
+ {
+ str->append(STRING_WITH_LEN("dual"));
+ return; // all tables were optimized away
+ }
+ ti.rewind();
+
+ if (!(table= (TABLE_LIST **)thd->alloc(sizeof(TABLE_LIST*) *
+ non_const_tables)))
return; // out of memory
- for (TABLE_LIST **t= table + (tables->elements - 1); t >= table; t--)
- *t= ti++;
-
+ TABLE_LIST *tmp, **t= table + (non_const_tables - 1);
+ while ((tmp= ti++))
+ {
+ if (tmp->optimized_away)
+ continue;
+ *t--= tmp;
+ }
+
/*
If the first table is a semi-join nest, swap it with something that is
not a semi-join nest.
*/
if ((*table)->sj_inner_tables)
{
- TABLE_LIST **end= table + tables->elements;
+ TABLE_LIST **end= table + non_const_tables;
for (TABLE_LIST **t2= table; t2!=end; t2++)
{
if (!(*t2)->sj_inner_tables)
@@ -21952,8 +21972,7 @@ static void print_join(THD *thd,
}
}
}
- DBUG_ASSERT(tables->elements >= 1);
- print_table_array(thd, str, table, table + tables->elements);
+ print_table_array(thd, str, table, table + non_const_tables);
}
=== modified file 'sql/table.h'
--- a/sql/table.h 2009-09-22 11:44:58 +0000
+++ b/sql/table.h 2009-09-28 15:58:30 +0000
@@ -1405,6 +1405,8 @@ struct TABLE_LIST
the parsed tree is created.
*/
uint8 trg_event_map;
+ /* TRUE <=> this table is a const one and was optimized away. */
+ bool optimized_away;
uint i_s_requested_object;
bool has_db_lookup_value;
Attachment: [text/bzr-bundle] bzr/epotemkin@mysql.com-20090928162325-f09rphdros0v39b3.bundle
| Thread |
|---|
| • bzr commit into mysql-6.0-codebase-bugfixing branch (epotemkin:3625)Bug#30302 | Evgeny Potemkin | 28 Sep |
