diff options
Diffstat (limited to 'mysql-test/r/subselect.result')
| -rw-r--r-- | mysql-test/r/subselect.result | 829 |
1 files changed, 773 insertions, 56 deletions
diff --git a/mysql-test/r/subselect.result b/mysql-test/r/subselect.result index 8503164dde2..3b5b838b56a 100644 --- a/mysql-test/r/subselect.result +++ b/mysql-test/r/subselect.result @@ -51,12 +51,12 @@ ERROR 42S22: Reference 'a' not supported (forward reference in item list) EXPLAIN EXTENDED SELECT 1 FROM (SELECT 1 as a) as b HAVING (SELECT a)=1; id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY <derived2> system NULL NULL NULL NULL 1 100.00 -3 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL No tables used +3 SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL No tables used 2 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used Warnings: Note 1276 Field or reference '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 (<expr_cache><1>((select 1)) = 1) +Note 1003 select 1 AS `1` from dual having ((select 1) = 1) SELECT 1 FROM (SELECT 1 as a) as b HAVING (SELECT a)=1; 1 1 @@ -323,7 +323,7 @@ NULL UNION RESULT <union2,3> ALL NULL NULL NULL NULL NULL NULL 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 <expr_cache><`test`.`t2`.`a`>((select 2 from `test`.`t1` where (2 = `test`.`t2`.`a`) union select `test`.`t5`.`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 <expr_cache><`test`.`t2`.`a`>((select 2 from dual where (2 = `test`.`t2`.`a`) union select `test`.`t5`.`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)); @@ -371,12 +371,12 @@ INSERT INTO t8 (pseudo,email) VALUES ('joce1','test1'); INSERT INTO t8 (pseudo,email) VALUES ('2joce1','2test1'); EXPLAIN EXTENDED SELECT pseudo,(SELECT email FROM t8 WHERE pseudo=(SELECT pseudo FROM t8 WHERE pseudo='joce')) FROM t8 WHERE pseudo=(SELECT pseudo FROM t8 WHERE pseudo='joce'); id select_type table type possible_keys key key_len ref rows filtered Extra -1 PRIMARY t8 const PRIMARY PRIMARY 37 const 1 100.00 Using where; Using index +1 PRIMARY t8 const PRIMARY PRIMARY 37 const 1 100.00 Using index 4 SUBQUERY t8 const PRIMARY PRIMARY 37 const 1 100.00 Using index -2 SUBQUERY t8 const PRIMARY PRIMARY 37 const 1 100.00 Using where +2 SUBQUERY t8 const PRIMARY PRIMARY 37 const 1 100.00 3 SUBQUERY t8 const PRIMARY PRIMARY 37 const 1 100.00 Using index Warnings: -Note 1003 select `test`.`t8`.`pseudo` AS `pseudo`,(select `test`.`t8`.`email` from `test`.`t8` where (`test`.`t8`.`pseudo` = (select 'joce' from `test`.`t8` where 1))) AS `(SELECT email FROM t8 WHERE pseudo=(SELECT pseudo FROM t8 WHERE pseudo='joce'))` from `test`.`t8` where (`test`.`t8`.`pseudo` = (select 'joce' from `test`.`t8` where 1)) +Note 1003 select 'joce' AS `pseudo`,(select 'test' from `test`.`t8` where 1) AS `(SELECT email FROM t8 WHERE pseudo=(SELECT pseudo FROM t8 WHERE pseudo='joce'))` from `test`.`t8` where 1 SELECT pseudo FROM t8 WHERE pseudo=(SELECT pseudo,email FROM t8 WHERE pseudo='joce'); ERROR 21000: Operand should contain 1 column(s) @@ -429,7 +429,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra 3 UNION NULL NULL NULL NULL NULL NULL NULL NULL No tables used NULL UNION RESULT <union2,3> ALL NULL NULL NULL NULL NULL NULL Warnings: -Note 1003 select 1 AS `1` from `test`.`t1` where (1 = (select 1 union select 1)) +Note 1003 select 1 AS `1` from `test`.`t1` where 1 drop table t1; CREATE TABLE `t1` ( `numeropost` mediumint(8) unsigned NOT NULL auto_increment, @@ -517,6 +517,7 @@ ERROR 21000: Subquery returns more than 1 row show warnings; Level Code Message Error 1242 Subquery returns more than 1 row +Error 1028 Sort aborted: Subquery returns more than 1 row drop table t1; create table t1 (a int); insert into t1 values (1),(2),(3); @@ -547,11 +548,7 @@ SELECT numreponse, (SELECT numeropost FROM t1 HAVING numreponse=1) FROM (SELECT numreponse (SELECT numeropost FROM t1 HAVING numreponse=1) INSERT INTO t1 (numeropost,numreponse,pseudo) VALUES (1,1,'joce'),(1,2,'joce'),(1,3,'test'); EXPLAIN EXTENDED SELECT numreponse FROM t1 WHERE numeropost='1' AND numreponse=(SELECT 1 FROM t1 WHERE numeropost='1'); -id select_type table type possible_keys key key_len ref rows filtered Extra -1 PRIMARY t1 const PRIMARY,numreponse PRIMARY 7 const,const 1 100.00 Using where; Using index -2 SUBQUERY t1 ref PRIMARY PRIMARY 3 const 2 100.00 Using index -Warnings: -Note 1003 select `test`.`t1`.`numreponse` AS `numreponse` from `test`.`t1` where ((`test`.`t1`.`numeropost` = '1') and (`test`.`t1`.`numreponse` = (select 1 from `test`.`t1` where (`test`.`t1`.`numeropost` = '1')))) +ERROR 21000: Subquery returns more than 1 row SELECT numreponse FROM t1 WHERE numeropost='1' AND numreponse=(SELECT 1 FROM t1 WHERE numeropost='1'); ERROR 21000: Subquery returns more than 1 row EXPLAIN EXTENDED SELECT MAX(numreponse) FROM t1 WHERE numeropost='1'; @@ -561,10 +558,10 @@ Warnings: Note 1003 select max(`test`.`t1`.`numreponse`) AS `MAX(numreponse)` from `test`.`t1` where (`test`.`t1`.`numeropost` = '1') EXPLAIN EXTENDED SELECT numreponse FROM t1 WHERE numeropost='1' AND numreponse=(SELECT MAX(numreponse) FROM t1 WHERE numeropost='1'); id select_type table type possible_keys key key_len ref rows filtered Extra -1 PRIMARY t1 const PRIMARY,numreponse PRIMARY 7 const,const 1 100.00 Using where; Using index +1 PRIMARY t1 const PRIMARY,numreponse PRIMARY 7 const,const 1 100.00 Using index 2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL Select tables optimized away Warnings: -Note 1003 select `test`.`t1`.`numreponse` AS `numreponse` from `test`.`t1` where ((`test`.`t1`.`numeropost` = '1') and (`test`.`t1`.`numreponse` = (select max(`test`.`t1`.`numreponse`) from `test`.`t1` where (`test`.`t1`.`numeropost` = '1')))) +Note 1003 select 3 AS `numreponse` from `test`.`t1` where ((1 = '1')) drop table t1; CREATE TABLE t1 (a int(1)); INSERT INTO t1 VALUES (1); @@ -749,11 +746,11 @@ id 2 EXPLAIN EXTENDED SELECT * FROM t2 WHERE id IN (SELECT 1+(select 1)); id select_type table type possible_keys key key_len ref rows filtered Extra -1 PRIMARY t2 ref id id 5 const 1 100.00 Using index +1 PRIMARY t2 ref id id 5 const 1 100.00 Using where; Using index Warnings: Note 1249 Select 3 was reduced during optimization Note 1249 Select 2 was reduced during optimization -Note 1003 select `test`.`t2`.`id` AS `id` from `test`.`t2` where (`test`.`t2`.`id` = (1 + 1)) +Note 1003 select `test`.`t2`.`id` AS `id` from `test`.`t2` where (`test`.`t2`.`id` = <cache>((1 + 1))) EXPLAIN EXTENDED SELECT * FROM t2 WHERE id IN (SELECT 1 UNION SELECT 3); id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t2 index NULL id 5 NULL 2 100.00 Using where; Using index @@ -907,7 +904,7 @@ a t1.a in (select t2.a from t2) explain extended SELECT t1.a, t1.a in (select t2.a from t2) FROM t1; id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t1 index NULL PRIMARY 4 NULL 4 100.00 Using index -2 DEPENDENT SUBQUERY t2 index_subquery a a 5 func 2 100.00 Using index +2 SUBQUERY t2 index_subquery a a 5 func 2 100.00 Using index Warnings: Note 1003 select `test`.`t1`.`a` AS `a`,<expr_cache><`test`.`t1`.`a`>(<in_optimizer>(`test`.`t1`.`a`,<exists>(<index_lookup>(<cache>(`test`.`t1`.`a`) in t2 on a checking NULL having <is_not_null_test>(`test`.`t2`.`a`))))) AS `t1.a in (select t2.a from t2)` from `test`.`t1` CREATE TABLE t3 (a int(11) default '0'); @@ -1053,9 +1050,9 @@ a drop table t1, t2; create table t1 (a float); select 10.5 IN (SELECT * from t1 LIMIT 1); -ERROR 42000: This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery' +ERROR 42000: This version of MariaDB doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery' select 10.5 IN (SELECT * from t1 LIMIT 1 UNION SELECT 1.5); -ERROR 42000: This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery' +ERROR 42000: This version of MariaDB doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery' drop table t1; create table t1 (a int, b int, c varchar(10)); create table t2 (a int); @@ -1107,7 +1104,7 @@ select ROW(1, 1, 'a') IN (select b,a,c from t1 where c='b' or c='a'); ROW(1, 1, 'a') IN (select b,a,c from t1 where c='b' or c='a') 1 select ROW(1, 1, 'a') IN (select b,a,c from t1 limit 2); -ERROR 42000: This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery' +ERROR 42000: This version of MariaDB doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery' drop table t1; create table t1 (a int); insert into t1 values (1); @@ -1128,7 +1125,7 @@ ERROR 42S02: Table 'test.t1' doesn't exist CREATE TABLE t1 (a int, KEY(a)); HANDLER t1 OPEN; HANDLER t1 READ a=((SELECT 1)); -ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'SELECT 1))' at line 1 +ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'SELECT 1))' at line 1 HANDLER t1 CLOSE; drop table t1; create table t1 (a int); @@ -1322,7 +1319,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY NULL NULL NULL NULL NULL NULL NULL NULL No tables used 2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables Warnings: -Note 1003 select <in_optimizer>(0,<exists>(select 1 from `test`.`t1` `a` where (0 = 1))) AS `0 IN (SELECT 1 FROM t1 a)` +Note 1003 select <in_optimizer>(0,<exists>(select 1 from dual where (0 = 1))) AS `0 IN (SELECT 1 FROM t1 a)` INSERT INTO t1 (pseudo) VALUES ('test1'); SELECT 0 IN (SELECT 1 FROM t1 a); 0 IN (SELECT 1 FROM t1 a) @@ -1374,7 +1371,7 @@ create table t1 (id int not null auto_increment primary key, salary int, key(sal insert into t1 (salary) values (100),(1000),(10000),(10),(500),(5000),(50000); explain extended SELECT id FROM t1 where salary = (SELECT MAX(salary) FROM t1); id select_type table type possible_keys key key_len ref rows filtered Extra -1 PRIMARY t1 ref salary salary 5 const 0 0.00 Using where +1 PRIMARY t1 ref salary salary 5 const 1 100.00 Using where 2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL Select tables optimized away Warnings: Note 1003 select `test`.`t1`.`id` AS `id` from `test`.`t1` where (`test`.`t1`.`salary` = (select max(`test`.`t1`.`salary`) from `test`.`t1`)) @@ -1576,7 +1573,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 @@ -1608,25 +1605,25 @@ a3 1 explain extended select s1, s1 NOT IN (SELECT s1 FROM t2) from t1; id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t1 index NULL s1 6 NULL 3 100.00 Using index -2 DEPENDENT SUBQUERY t2 index_subquery s1 s1 6 func 2 100.00 Using index; Full scan on NULL key +2 SUBQUERY t2 index_subquery s1 s1 6 func 2 100.00 Using index; Full scan on NULL key Warnings: Note 1003 select `test`.`t1`.`s1` AS `s1`,(not(<expr_cache><`test`.`t1`.`s1`>(<in_optimizer>(`test`.`t1`.`s1`,<exists>(<index_lookup>(<cache>(`test`.`t1`.`s1`) in t2 on s1 checking NULL having trigcond(<is_not_null_test>(`test`.`t2`.`s1`)))))))) AS `s1 NOT IN (SELECT s1 FROM t2)` from `test`.`t1` explain extended select s1, s1 = ANY (SELECT s1 FROM t2) from t1; id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t1 index NULL s1 6 NULL 3 100.00 Using index -2 DEPENDENT SUBQUERY t2 index_subquery s1 s1 6 func 2 100.00 Using index; Full scan on NULL key +2 SUBQUERY t2 index_subquery s1 s1 6 func 2 100.00 Using index; Full scan on NULL key Warnings: Note 1003 select `test`.`t1`.`s1` AS `s1`,<expr_cache><`test`.`t1`.`s1`>(<in_optimizer>(`test`.`t1`.`s1`,<exists>(<index_lookup>(<cache>(`test`.`t1`.`s1`) in t2 on s1 checking NULL having trigcond(<is_not_null_test>(`test`.`t2`.`s1`)))))) AS `s1 = ANY (SELECT s1 FROM t2)` from `test`.`t1` explain extended select s1, s1 <> ALL (SELECT s1 FROM t2) from t1; id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t1 index NULL s1 6 NULL 3 100.00 Using index -2 DEPENDENT SUBQUERY t2 index_subquery s1 s1 6 func 2 100.00 Using index; Full scan on NULL key +2 SUBQUERY t2 index_subquery s1 s1 6 func 2 100.00 Using index; Full scan on NULL key Warnings: Note 1003 select `test`.`t1`.`s1` AS `s1`,(not(<expr_cache><`test`.`t1`.`s1`>(<in_optimizer>(`test`.`t1`.`s1`,<exists>(<index_lookup>(<cache>(`test`.`t1`.`s1`) in t2 on s1 checking NULL having trigcond(<is_not_null_test>(`test`.`t2`.`s1`)))))))) AS `s1 <> ALL (SELECT s1 FROM t2)` from `test`.`t1` explain extended select s1, s1 NOT IN (SELECT s1 FROM t2 WHERE s1 < 'a2') from t1; id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t1 index NULL s1 6 NULL 3 100.00 Using index -2 DEPENDENT SUBQUERY t2 index_subquery s1 s1 6 func 2 100.00 Using index; Using where; Full scan on NULL key +2 SUBQUERY t2 index_subquery s1 s1 6 func 2 100.00 Using index; Using where; Full scan on NULL key Warnings: Note 1003 select `test`.`t1`.`s1` AS `s1`,(not(<expr_cache><`test`.`t1`.`s1`>(<in_optimizer>(`test`.`t1`.`s1`,<exists>(<index_lookup>(<cache>(`test`.`t1`.`s1`) in t2 on s1 checking NULL where (`test`.`t2`.`s1` < 'a2') having trigcond(<is_not_null_test>(`test`.`t2`.`s1`)))))))) AS `s1 NOT IN (SELECT s1 FROM t2 WHERE s1 < 'a2')` from `test`.`t1` drop table t1,t2; @@ -1675,34 +1672,34 @@ select * from t3 where NULL >= any (select b from t2); a explain extended select * from t3 where NULL >= any (select b from t2); id select_type table type possible_keys key key_len ref rows filtered Extra -1 PRIMARY t3 ALL NULL NULL NULL NULL 3 100.00 -2 SUBQUERY t2 system NULL NULL NULL NULL 0 0.00 const row not found +1 PRIMARY NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE +2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL no matching row in const table Warnings: -Note 1003 select `test`.`t3`.`a` AS `a` from `test`.`t3` where <nop>(<in_optimizer>(NULL,((select min(NULL) from `test`.`t2`) <= NULL))) +Note 1003 select `test`.`t3`.`a` AS `a` from `test`.`t3` where 0 select * from t3 where NULL >= any (select b from t2 group by 1); a explain extended select * from t3 where NULL >= any (select b from t2 group by 1); id select_type table type possible_keys key key_len ref rows filtered Extra -1 PRIMARY t3 ALL NULL NULL NULL NULL 3 100.00 -2 SUBQUERY t2 system NULL NULL NULL NULL 0 0.00 const row not found +1 PRIMARY NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE +2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL no matching row in const table Warnings: -Note 1003 select `test`.`t3`.`a` AS `a` from `test`.`t3` where <nop>(<in_optimizer>(NULL,((select min(NULL) from `test`.`t2`) <= NULL))) +Note 1003 select `test`.`t3`.`a` AS `a` from `test`.`t3` where 0 select * from t3 where NULL >= some (select b from t2); a explain extended select * from t3 where NULL >= some (select b from t2); id select_type table type possible_keys key key_len ref rows filtered Extra -1 PRIMARY t3 ALL NULL NULL NULL NULL 3 100.00 -2 SUBQUERY t2 system NULL NULL NULL NULL 0 0.00 const row not found +1 PRIMARY NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE +2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL no matching row in const table Warnings: -Note 1003 select `test`.`t3`.`a` AS `a` from `test`.`t3` where <nop>(<in_optimizer>(NULL,((select min(NULL) from `test`.`t2`) <= NULL))) +Note 1003 select `test`.`t3`.`a` AS `a` from `test`.`t3` where 0 select * from t3 where NULL >= some (select b from t2 group by 1); a explain extended select * from t3 where NULL >= some (select b from t2 group by 1); id select_type table type possible_keys key key_len ref rows filtered Extra -1 PRIMARY t3 ALL NULL NULL NULL NULL 3 100.00 -2 SUBQUERY t2 system NULL NULL NULL NULL 0 0.00 const row not found +1 PRIMARY NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE +2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL no matching row in const table Warnings: -Note 1003 select `test`.`t3`.`a` AS `a` from `test`.`t3` where <nop>(<in_optimizer>(NULL,((select min(NULL) from `test`.`t2`) <= NULL))) +Note 1003 select `test`.`t3`.`a` AS `a` from `test`.`t3` where 0 insert into t2 values (2,2), (2,1), (3,3), (3,1); select * from t3 where a > all (select max(b) from t2 group by a); a @@ -1764,7 +1761,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra 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 <nop>(<in_optimizer>('f',(<min>(select 'e' from `test`.`t1` union select 'e' from `test`.`t1`) < 'f'))) +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'); @@ -1929,7 +1926,7 @@ create table t1(id int); create table t2(id int); create table t3(flag int); select (select * from t3 where id not null) from t1, t2; -ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'null) from t1, t2' at line 1 +ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'null) from t1, t2' at line 1 drop table t1,t2,t3; CREATE TABLE t1 (id INT); CREATE TABLE t2 (id INT); @@ -3099,8 +3096,8 @@ ON r.a = (SELECT t2.a FROM t2 WHERE t2.c = t1.a AND t2.b <= '359899' ORDER BY t2.c DESC, t2.b DESC LIMIT 1) WHERE t1.a = 10; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 system PRIMARY NULL NULL NULL 1 -1 PRIMARY r eq_ref PRIMARY PRIMARY 4 const 1 Using where -2 DEPENDENT SUBQUERY t2 range b b 40 NULL 2 Using where +1 PRIMARY r const PRIMARY PRIMARY 4 const 1 +2 SUBQUERY t2 range b b 40 NULL 2 Using where SELECT sql_no_cache t1.a, r.a, r.b FROM t1 LEFT JOIN t2 r ON r.a = (SELECT t2.a FROM t2 WHERE t2.c = t1.a AND t2.b <= '359899' ORDER BY t2.c DESC, t2.b DESC LIMIT 1) WHERE t1.a = 10; @@ -3111,8 +3108,8 @@ ON r.a = (SELECT t2.a FROM t2 WHERE t2.c = t1.a AND t2.b <= '359899' ORDER BY t2.c, t2.b LIMIT 1) WHERE t1.a = 10; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 system PRIMARY NULL NULL NULL 1 -1 PRIMARY r eq_ref PRIMARY PRIMARY 4 const 1 Using where -2 DEPENDENT SUBQUERY t2 range b b 40 NULL 2 Using index condition +1 PRIMARY r const PRIMARY PRIMARY 4 const 1 +2 SUBQUERY t2 range b b 40 NULL 2 Using index condition SELECT sql_no_cache t1.a, r.a, r.b FROM t1 LEFT JOIN t2 r ON r.a = (SELECT t2.a FROM t2 WHERE t2.c = t1.a AND t2.b <= '359899' ORDER BY t2.c, t2.b LIMIT 1) WHERE t1.a = 10; @@ -3163,7 +3160,7 @@ INSERT INTO t2 VALUES (1),(2),(3); EXPLAIN SELECT a, a IN (SELECT a FROM t1) FROM t2; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t2 ALL NULL NULL NULL NULL 3 -2 DEPENDENT SUBQUERY t1 index_subquery a a 5 func 2 Using index; Full scan on NULL key +2 SUBQUERY t1 index_subquery a a 5 func 2 Using index; Full scan on NULL key SELECT a, a IN (SELECT a FROM t1) FROM t2; a a IN (SELECT a FROM t1) 1 1 @@ -3699,7 +3696,7 @@ ORDER BY t1.t DESC LIMIT 1); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t2 system NULL NULL NULL NULL 1 1 PRIMARY t1 index NULL PRIMARY 16 NULL 11 Using where; Using index -2 DEPENDENT SUBQUERY t1 range PRIMARY PRIMARY 16 NULL 5 Using where; Using index +2 SUBQUERY t1 range PRIMARY PRIMARY 16 NULL 5 Using where; Using index SELECT * FROM t1,t2 WHERE t1.t = (SELECT t1.t FROM t1 WHERE t1.t < t2.t AND t1.i2=1 AND t2.i1=t1.i1 @@ -3707,6 +3704,30 @@ ORDER BY t1.t DESC LIMIT 1); i1 i2 t i1 i2 t 24 1 2005-05-27 12:40:30 24 1 2006-06-20 12:29:40 DROP TABLE t1, t2; +CREATE TABLE t1 (i INT); +(SELECT i FROM t1) UNION (SELECT i FROM t1); +i +SELECT sql_no_cache * FROM t1 WHERE NOT EXISTS +( +(SELECT i FROM t1) UNION +(SELECT i FROM t1) +); +i +SELECT * FROM t1 +WHERE NOT EXISTS (((SELECT i FROM t1) UNION (SELECT i FROM t1))); +ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'UNION (SELECT i FROM t1)))' at line 2 +explain select ((select t11.i from t1 t11) union (select t12.i from t1 t12)) +from t1; +ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'union (select t12.i from t1 t12)) +from t1' at line 1 +explain select * from t1 where not exists +((select t11.i from t1 t11) union (select t12.i from t1 t12)); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 system NULL NULL NULL NULL 0 const row not found +2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL no matching row in const table +3 UNION NULL NULL NULL NULL NULL NULL NULL no matching row in const table +NULL UNION RESULT <union2,3> ALL NULL NULL NULL NULL NULL +DROP TABLE t1; CREATE TABLE t1 (a VARCHAR(250), b INT auto_increment, PRIMARY KEY (b)); insert into t1 (a) values (FLOOR(rand() * 100)); insert into t1 (a) select FLOOR(rand() * 100) from t1; @@ -4174,7 +4195,7 @@ CREATE TABLE t1 (a int, b int, KEY (a)); INSERT INTO t1 VALUES (1,1),(2,1); EXPLAIN SELECT 1 FROM t1 WHERE a = (SELECT COUNT(*) FROM t1 GROUP BY b); id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY t1 ref a a 5 const 0 Using where; Using index +1 PRIMARY t1 ref a a 5 const 1 Using where; Using index 2 SUBQUERY t1 ALL NULL NULL NULL NULL 2 Using temporary; Using filesort DROP TABLE t1; CREATE TABLE t1 (id int NOT NULL, st CHAR(2), INDEX idx(id)); @@ -4396,6 +4417,35 @@ out_a MIN(b) 1 2 2 4 DROP TABLE t1; +CREATE TABLE t1 (a INT); +CREATE TABLE t2 (a INT); +INSERT INTO t1 VALUES (1),(2); +INSERT INTO t2 VALUES (1),(2); +SELECT 2 FROM t1 WHERE EXISTS ((SELECT 1 FROM t2 WHERE t1.a=t2.a)); +2 +2 +2 +EXPLAIN EXTENDED +SELECT 2 FROM t1 WHERE EXISTS ((SELECT 1 FROM t2 WHERE t1.a=t2.a)); +id select_type table type possible_keys key key_len ref rows filtered Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 2 100.00 Using where +2 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 2 100.00 Using where +Warnings: +Note 1276 Field or reference 'test.t1.a' of SELECT #2 was resolved in SELECT #1 +Note 1003 select 2 AS `2` from `test`.`t1` where <expr_cache><`test`.`t1`.`a`>(exists(select 1 from `test`.`t2` where (`test`.`t1`.`a` = `test`.`t2`.`a`))) +EXPLAIN EXTENDED +SELECT 2 FROM t1 WHERE EXISTS ((SELECT 1 FROM t2 WHERE t1.a=t2.a) UNION +(SELECT 1 FROM t2 WHERE t1.a = t2.a)); +id select_type table type possible_keys key key_len ref rows filtered Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 2 100.00 Using where +2 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 2 100.00 Using where +3 DEPENDENT UNION t2 ALL NULL NULL NULL NULL 2 100.00 Using where +NULL UNION RESULT <union2,3> ALL NULL NULL NULL NULL NULL NULL +Warnings: +Note 1276 Field or reference 'test.t1.a' of SELECT #2 was resolved in SELECT #1 +Note 1276 Field or reference 'test.t1.a' of SELECT #3 was resolved in SELECT #1 +Note 1003 select 2 AS `2` from `test`.`t1` where <expr_cache><`test`.`t1`.`a`>(exists((select 1 from `test`.`t2` where (`test`.`t1`.`a` = `test`.`t2`.`a`)) union (select 1 from `test`.`t2` where (`test`.`t1`.`a` = `test`.`t2`.`a`)))) +DROP TABLE t1,t2; create table t0(a int); insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); create table t1(f11 int, f12 int); @@ -4527,7 +4577,7 @@ FROM t1 WHERE a = 230; 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 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables +2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables SELECT MAX(b), (SELECT COUNT(*) FROM st1,st2 WHERE st2.b <= t1.b) FROM t1 WHERE a = 230; @@ -4573,7 +4623,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 2 100.00 2 SUBQUERY t1 ALL NULL NULL NULL NULL 2 100.00 Warnings: -Note 1003 select 1 AS `1` from `test`.`t1` where <nop>(<in_optimizer>(1,((select max(`test`.`t1`.`a1`) from `test`.`t1`) > 1))) +Note 1003 select 1 AS `1` from `test`.`t1` where 1 SELECT 1 FROM t1 WHERE 1 < SOME (SELECT a1 FROM t1); 1 1 @@ -5022,6 +5072,270 @@ id g v s 51 50 NULL l 61 60 NULL l drop table t1, t2; +# +# Bug#33204: INTO is allowed in subselect, causing inconsistent results +# +CREATE TABLE t1( a INT ); +INSERT INTO t1 VALUES (1),(2); +CREATE TABLE t2( a INT, b INT ); +SELECT * +FROM (SELECT a INTO @var FROM t1 WHERE a = 2) t1a; +ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'INTO @var FROM t1 WHERE a = 2) t1a' at line 2 +SELECT * +FROM (SELECT a INTO OUTFILE 'file' FROM t1 WHERE a = 2) t1a; +ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'INTO OUTFILE 'file' FROM t1 WHERE a = 2) t1a' at line 2 +SELECT * +FROM (SELECT a INTO DUMPFILE 'file' FROM t1 WHERE a = 2) t1a; +ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'INTO DUMPFILE 'file' FROM t1 WHERE a = 2) t1a' at line 2 +SELECT * FROM ( +SELECT 1 a +UNION +SELECT a INTO @var FROM t1 WHERE a = 2 +) t1a; +ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'INTO @var FROM t1 WHERE a = 2 +) t1a' at line 4 +SELECT * FROM ( +SELECT 1 a +UNION +SELECT a INTO OUTFILE 'file' FROM t1 WHERE a = 2 +) t1a; +ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'INTO OUTFILE 'file' FROM t1 WHERE a = 2 +) t1a' at line 4 +SELECT * FROM ( +SELECT 1 a +UNION +SELECT a INTO DUMPFILE 'file' FROM t1 WHERE a = 2 +) t1a; +ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'INTO DUMPFILE 'file' FROM t1 WHERE a = 2 +) t1a' at line 4 +SELECT * FROM (SELECT a FROM t1 WHERE a = 2) t1a; +a +2 +SELECT * FROM ( +SELECT a FROM t1 WHERE a = 2 +UNION +SELECT a FROM t1 WHERE a = 2 +) t1a; +a +2 +SELECT * FROM ( +SELECT 1 a +UNION +SELECT a FROM t1 WHERE a = 2 +UNION +SELECT a FROM t1 WHERE a = 2 +) t1a; +a +1 +2 +SELECT * FROM ((SELECT 1 a) UNION SELECT 1 a); +ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ')' at line 1 +SELECT * FROM (SELECT 1 a UNION (SELECT 1 a)) alias; +a +1 +SELECT * FROM (SELECT 1 UNION SELECT 1) t1a; +1 +1 +SELECT * FROM ((SELECT 1 a INTO @a)) t1a; +ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'INTO @a)) t1a' at line 1 +SELECT * FROM ((SELECT 1 a INTO OUTFILE 'file' )) t1a; +ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'INTO OUTFILE 'file' )) t1a' at line 1 +SELECT * FROM ((SELECT 1 a INTO DUMPFILE 'file' )) t1a; +ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'INTO DUMPFILE 'file' )) t1a' at line 1 +SELECT * FROM (SELECT 1 a UNION (SELECT 1 a INTO @a)) t1a; +ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'INTO @a)) t1a' at line 1 +SELECT * FROM (SELECT 1 a UNION (SELECT 1 a INTO DUMPFILE 'file' )) t1a; +ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'INTO DUMPFILE 'file' )) t1a' at line 1 +SELECT * FROM (SELECT 1 a UNION (SELECT 1 a INTO OUTFILE 'file' )) t1a; +ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'INTO OUTFILE 'file' )) t1a' at line 1 +SELECT * FROM (SELECT 1 a UNION ((SELECT 1 a INTO @a))) t1a; +ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'INTO @a))) t1a' at line 1 +SELECT * FROM (SELECT 1 a UNION ((SELECT 1 a INTO DUMPFILE 'file' ))) t1a; +ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'INTO DUMPFILE 'file' ))) t1a' at line 1 +SELECT * FROM (SELECT 1 a UNION ((SELECT 1 a INTO OUTFILE 'file' ))) t1a; +ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'INTO OUTFILE 'file' ))) t1a' at line 1 +SELECT * FROM (SELECT 1 a ORDER BY a) t1a; +a +1 +SELECT * FROM (SELECT 1 a UNION SELECT 1 a ORDER BY a) t1a; +a +1 +SELECT * FROM (SELECT 1 a UNION SELECT 1 a LIMIT 1) t1a; +a +1 +SELECT * FROM (SELECT 1 a UNION SELECT 1 a ORDER BY a LIMIT 1) t1a; +a +1 +SELECT * FROM t1 JOIN (SELECT 1 UNION SELECT 1) alias ON 1; +a 1 +1 1 +2 1 +SELECT * FROM t1 JOIN ((SELECT 1 UNION SELECT 1)) ON 1; +ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ')) ON 1' at line 1 +SELECT * FROM t1 JOIN (t1 t1a UNION SELECT 1) ON 1; +ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ') ON 1' at line 1 +SELECT * FROM t1 JOIN ((t1 t1a UNION SELECT 1)) ON 1; +ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ')) ON 1' at line 1 +SELECT * FROM t1 JOIN (t1 t1a) t1a ON 1; +ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 't1a ON 1' at line 1 +SELECT * FROM t1 JOIN ((t1 t1a)) t1a ON 1; +ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 't1a ON 1' at line 1 +SELECT * FROM t1 JOIN (t1 t1a) ON 1; +a a +1 1 +2 1 +1 2 +2 2 +SELECT * FROM t1 JOIN ((t1 t1a)) ON 1; +a a +1 1 +2 1 +1 2 +2 2 +SELECT * FROM (t1 t1a); +a +1 +2 +SELECT * FROM ((t1 t1a)); +a +1 +2 +SELECT * FROM t1 JOIN (SELECT 1 t1a) alias ON 1; +a t1a +1 1 +2 1 +SELECT * FROM t1 JOIN ((SELECT 1 t1a)) alias ON 1; +a t1a +1 1 +2 1 +SELECT * FROM t1 JOIN (SELECT 1 a) a ON 1; +a a +1 1 +2 1 +SELECT * FROM t1 JOIN ((SELECT 1 a)) a ON 1; +a a +1 1 +2 1 +SELECT * FROM (t1 JOIN (SELECT 1) t1a1 ON 1) t1a2; +ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 't1a2' at line 1 +SELECT * FROM t1 WHERE a = ALL ( SELECT 1 ); +a +1 +SELECT * FROM t1 WHERE a = ALL ( SELECT 1 UNION SELECT 1 ); +a +1 +SELECT * FROM t1 WHERE a = ANY ( SELECT 3 UNION SELECT 1 ); +a +1 +SELECT * FROM t1 WHERE a = ANY ( SELECT 1 UNION SELECT 1 INTO @a); +ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'INTO @a)' at line 1 +SELECT * FROM t1 WHERE a = ANY ( SELECT 1 UNION SELECT 1 INTO OUTFILE 'file' ); +ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'INTO OUTFILE 'file' )' at line 1 +SELECT * FROM t1 WHERE a = ANY ( SELECT 1 UNION SELECT 1 INTO DUMPFILE 'file' ); +ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'INTO DUMPFILE 'file' )' at line 1 +SELECT * FROM t1 WHERE a = ( SELECT 1 ); +a +1 +SELECT * FROM t1 WHERE a = ( SELECT 1 UNION SELECT 1 ); +a +1 +SELECT * FROM t1 WHERE a = ( SELECT 1 INTO @a); +ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'INTO @a)' at line 1 +SELECT * FROM t1 WHERE a = ( SELECT 1 INTO OUTFILE 'file' ); +ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'INTO OUTFILE 'file' )' at line 1 +SELECT * FROM t1 WHERE a = ( SELECT 1 INTO DUMPFILE 'file' ); +ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'INTO DUMPFILE 'file' )' at line 1 +SELECT * FROM t1 WHERE a = ( SELECT 1 UNION SELECT 1 INTO @a); +ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'INTO @a)' at line 1 +SELECT * FROM t1 WHERE a = ( SELECT 1 UNION SELECT 1 INTO OUTFILE 'file' ); +ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'INTO OUTFILE 'file' )' at line 1 +SELECT * FROM t1 WHERE a = ( SELECT 1 UNION SELECT 1 INTO DUMPFILE 'file' ); +ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'INTO DUMPFILE 'file' )' at line 1 +SELECT ( SELECT 1 INTO @v ); +ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'INTO @v )' at line 1 +SELECT ( SELECT 1 INTO OUTFILE 'file' ); +ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'INTO OUTFILE 'file' )' at line 1 +SELECT ( SELECT 1 INTO DUMPFILE 'file' ); +ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'INTO DUMPFILE 'file' )' at line 1 +SELECT ( SELECT 1 UNION SELECT 1 INTO @v ); +ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'INTO @v )' at line 1 +SELECT ( SELECT 1 UNION SELECT 1 INTO OUTFILE 'file' ); +ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'INTO OUTFILE 'file' )' at line 1 +SELECT ( SELECT 1 UNION SELECT 1 INTO DUMPFILE 'file' ); +ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'INTO DUMPFILE 'file' )' at line 1 +SELECT ( SELECT a FROM t1 WHERE a = 1 ), a FROM t1; +( SELECT a FROM t1 WHERE a = 1 ) a +1 1 +1 2 +SELECT ( SELECT a FROM t1 WHERE a = 1 UNION SELECT 1 ), a FROM t1; +( SELECT a FROM t1 WHERE a = 1 UNION SELECT 1 ) a +1 1 +1 2 +SELECT * FROM t2 WHERE (a, b) IN (SELECT a, b FROM t2); +a b +SELECT 1 UNION ( SELECT 1 UNION SELECT 1 ); +ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'UNION SELECT 1 )' at line 1 +( SELECT 1 UNION SELECT 1 ) UNION SELECT 1; +ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'UNION SELECT 1 ) UNION SELECT 1' at line 1 +SELECT ( SELECT 1 UNION ( SELECT 1 UNION SELECT 1 ) ); +ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'UNION SELECT 1 ) )' at line 1 +SELECT ( ( SELECT 1 UNION SELECT 1 ) UNION SELECT 1; +ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'UNION SELECT 1' at line 1 +SELECT ( SELECT 1 UNION SELECT 1 UNION SELECT 1 ); +( SELECT 1 UNION SELECT 1 UNION SELECT 1 ) +1 +SELECT ((SELECT 1 UNION SELECT 1 UNION SELECT 1)); +((SELECT 1 UNION SELECT 1 UNION SELECT 1)) +1 +SELECT * FROM ( SELECT 1 UNION ( SELECT 1 UNION SELECT 1 ) ); +ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'UNION SELECT 1 ) )' at line 1 +SELECT * FROM ( ( SELECT 1 UNION SELECT 1 ) UNION SELECT 1 ); +ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ') UNION SELECT 1 )' at line 1 +SELECT * FROM ( SELECT 1 UNION SELECT 1 UNION SELECT 1 ) a; +1 +1 +SELECT * FROM t1 WHERE a = ( SELECT 1 UNION ( SELECT 1 UNION SELECT 1 ) ); +ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'UNION SELECT 1 ) )' at line 1 +SELECT * FROM t1 WHERE a = ALL ( SELECT 1 UNION ( SELECT 1 UNION SELECT 1 ) ); +ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'UNION SELECT 1 ) )' at line 1 +SELECT * FROM t1 WHERE a = ANY ( SELECT 1 UNION ( SELECT 1 UNION SELECT 1 ) ); +ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'UNION SELECT 1 ) )' at line 1 +SELECT * FROM t1 WHERE a IN ( SELECT 1 UNION ( SELECT 1 UNION SELECT 1 ) ); +ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'UNION SELECT 1 ) )' at line 1 +SELECT * FROM t1 WHERE a = ( ( SELECT 1 UNION SELECT 1 ) UNION SELECT 1 ); +ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'UNION SELECT 1 )' at line 1 +SELECT * FROM t1 WHERE a = ALL ( ( SELECT 1 UNION SELECT 1 ) UNION SELECT 1 ); +ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'UNION SELECT 1 ) UNION SELECT 1 )' at line 1 +SELECT * FROM t1 WHERE a = ANY ( ( SELECT 1 UNION SELECT 1 ) UNION SELECT 1 ); +ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'UNION SELECT 1 ) UNION SELECT 1 )' at line 1 +SELECT * FROM t1 WHERE a IN ( ( SELECT 1 UNION SELECT 1 ) UNION SELECT 1 ); +ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'UNION SELECT 1 )' at line 1 +SELECT * FROM t1 WHERE a = ( SELECT 1 UNION SELECT 1 UNION SELECT 1 ); +a +1 +SELECT * FROM t1 WHERE a = ALL ( SELECT 1 UNION SELECT 1 UNION SELECT 1 ); +a +1 +SELECT * FROM t1 WHERE a = ANY ( SELECT 1 UNION SELECT 1 UNION SELECT 1 ); +a +1 +SELECT * FROM t1 WHERE a IN ( SELECT 1 UNION SELECT 1 UNION SELECT 1 ); +a +1 +SELECT * FROM t1 WHERE EXISTS ( SELECT 1 UNION SELECT 1 INTO @v ); +ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'INTO @v )' at line 1 +SELECT EXISTS(SELECT 1+1); +EXISTS(SELECT 1+1) +1 +SELECT EXISTS(SELECT 1+1 INTO @test); +ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'INTO @test)' at line 1 +SELECT * FROM t1 WHERE a IN ( SELECT 1 UNION SELECT 1 INTO @v ); +ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'INTO @v )' at line 1 +SELECT * FROM t1 WHERE EXISTS ( SELECT 1 INTO @v ); +ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'INTO @v )' at line 1 +SELECT * FROM t1 WHERE a IN ( SELECT 1 INTO @v ); +ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'INTO @v )' at line 1 +DROP TABLE t1, t2; CREATE TABLE t1 (a ENUM('rainbow')); INSERT INTO t1 VALUES (),(),(),(),(); SELECT 1 FROM t1 GROUP BY (SELECT 1 FROM t1 ORDER BY AVG(LAST_INSERT_ID())); @@ -5166,6 +5480,58 @@ ORDER BY (SELECT * FROM t1 WHERE MATCH(a) AGAINST ('+abc' IN BOOLEAN MODE)); SELECT * FROM t2 UNION SELECT * FROM t2 ORDER BY (SELECT * FROM t1 WHERE MATCH(a) AGAINST ('+abc' IN BOOLEAN MODE)); DROP TABLE t1,t2; +# +# Bug #58818: Incorrect result for IN/ANY subquery +# with HAVING condition +# +CREATE TABLE t1(i INT); +INSERT INTO t1 VALUES (1), (2), (3); +CREATE TABLE t1s(i INT); +INSERT INTO t1s VALUES (10), (20), (30); +CREATE TABLE t2s(i INT); +INSERT INTO t2s VALUES (100), (200), (300); +SELECT * FROM t1 +WHERE t1.i NOT IN +( +SELECT STRAIGHT_JOIN t2s.i +FROM +t1s LEFT OUTER JOIN t2s ON t2s.i = t1s.i +HAVING t2s.i = 999 +); +i +1 +2 +3 +SELECT * FROM t1 +WHERE t1.I IN +( +SELECT STRAIGHT_JOIN t2s.i +FROM +t1s LEFT OUTER JOIN t2s ON t2s.i = t1s.i +HAVING t2s.i = 999 +) IS UNKNOWN; +i +SELECT * FROM t1 +WHERE NOT t1.I = ANY +( +SELECT STRAIGHT_JOIN t2s.i +FROM +t1s LEFT OUTER JOIN t2s ON t2s.i = t1s.i +HAVING t2s.i = 999 +); +i +1 +2 +3 +SELECT * FROM t1 +WHERE t1.i = ANY ( +SELECT STRAIGHT_JOIN t2s.i +FROM +t1s LEFT OUTER JOIN t2s ON t2s.i = t1s.i +HAVING t2s.i = 999 +) IS UNKNOWN; +i +DROP TABLE t1,t1s,t2s; # LP BUG#675248 - select->prep_where references on freed memory CREATE TABLE t1 (a int, b int); insert into t1 values (1,1),(0,0); @@ -5198,6 +5564,45 @@ ERROR 21000: Subquery returns more than 1 row drop table t1,t2; End of 5.1 tests # +# Bug #11765713 58705: +# OPTIMIZER LET ENGINE DEPEND ON UNINITIALIZED VALUES +# CREATED BY OPT_SUM_QUERY +# +CREATE TABLE t1(a INT NOT NULL, KEY (a)); +INSERT INTO t1 VALUES (0), (1); +SELECT 1 as foo FROM t1 WHERE a < SOME +(SELECT a FROM t1 WHERE a <=> +(SELECT a FROM t1) +); +ERROR 21000: Subquery returns more than 1 row +SELECT 1 as foo FROM t1 WHERE a < SOME +(SELECT a FROM t1 WHERE a <=> +(SELECT a FROM t1 where a is null) +); +foo +DROP TABLE t1; +# +# Bug #57704: Cleanup code dies with void TABLE::set_keyread(bool): +# Assertion `file' failed. +# +CREATE TABLE t1 (a INT); +SELECT 1 FROM +(SELECT ROW( +(SELECT 1 FROM t1 RIGHT JOIN +(SELECT 1 FROM t1, t1 t2) AS d ON 1), +1) FROM t1) AS e; +ERROR 21000: Operand should contain 1 column(s) +DROP TABLE t1; +# +# Bug#13721076 CRASH WITH TIME TYPE/TIMESTAMP() AND WARNINGS IN SUBQUERY +# +CREATE TABLE t1(a TIME NOT NULL); +INSERT INTO t1 VALUES ('00:00:32'); +SELECT 1 FROM t1 WHERE a > +(SELECT timestamp(a) AS a FROM t1); +1 +DROP TABLE t1; +# # No BUG#, a case brought from 5.2's innodb_mysql_lock.test # create table t1 (i int not null primary key); @@ -5485,7 +5890,7 @@ SELECT a FROM t1 WHERE b <> ALL ( SELECT b FROM t1 GROUP BY b ); a drop table t1; # -# Fix of LP BUG#780386 (NULL left part with empty ALL subquery). +# Fix of lp:780386 (NULL left part with empty ALL subquery). # CREATE TABLE t1 ( f11 int) ; INSERT IGNORE INTO t1 VALUES (0),(0); @@ -5787,7 +6192,7 @@ ON t2.f10 = t3.f10 f1 DROP TABLE t1,t2,t3; # -# BUG LP:813473: Wrong result with outer join + NOT IN subquery +# BUG lp:813473: Wrong result with outer join + NOT IN subquery # This bug is a duplicate of Bug#11764086 whose test case is added below # CREATE TABLE t1 (c int) ; @@ -5815,6 +6220,27 @@ b c SET optimizer_switch=@save_optimizer_switch; drop table t1, t2, t3; # +# BUG#50257: Missing info in REF column of the EXPLAIN +# lines for subselects +# +CREATE TABLE t1 (a INT, b INT, INDEX (a)); +INSERT INTO t1 VALUES (3, 10), (2, 20), (7, 10), (5, 20); + +set @tmp_optimizer_switch=@@optimizer_switch; +set optimizer_switch='derived_merge=off,derived_with_keys=off'; +EXPLAIN SELECT * FROM (SELECT * FROM t1 WHERE a=7) t; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY <derived2> ALL NULL NULL NULL NULL 2 +2 DERIVED t1 ref a a 5 const 1 +set optimizer_switch=@tmp_optimizer_switch; + +EXPLAIN SELECT * FROM t1 WHERE EXISTS (SELECT * FROM t1 WHERE a=7); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 4 +2 SUBQUERY t1 ref a a 5 const 1 Using index + +DROP TABLE t1; +# # Bug#11764086: Null left operand to NOT IN in WHERE clause # behaves differently than real NULL # @@ -5888,7 +6314,55 @@ GROUP BY b 1 DROP TABLE t1, t2; # -# LP bug #826279: assertion failure with GROUP BY a result of subquery +# BUG#12616253 - WRONG RESULT WITH EXISTS(SUBQUERY) (MISSING ROWS) +# +CREATE TABLE t1 (f1 varchar(1)); +INSERT INTO t1 VALUES ('v'),('s'); +CREATE TABLE t2 (f1_key varchar(1), KEY (f1_key)); +INSERT INTO t2 VALUES ('j'),('v'),('c'),('m'),('d'), +('d'),('y'),('t'),('d'),('s'); +SELECT table1.f1, table2.f1_key +FROM t1 AS table1, t2 AS table2 +WHERE EXISTS +( +SELECT DISTINCT f1_key +FROM t2 +WHERE f1_key != table2.f1_key AND f1_key >= table1.f1 ); +f1 f1_key +v j +s j +v v +s v +v c +s c +v m +s m +v d +s d +v d +s d +v y +s y +v t +s t +v d +s d +v s +s s +explain SELECT table1.f1, table2.f1_key +FROM t1 AS table1, t2 AS table2 +WHERE EXISTS +( +SELECT DISTINCT f1_key +FROM t2 +WHERE f1_key != table2.f1_key AND f1_key >= table1.f1 ); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY table1 ALL NULL NULL NULL NULL 2 +1 PRIMARY table2 index NULL f1_key 4 NULL 10 Using where; Using index; Using join buffer (flat, BNL join) +2 DEPENDENT SUBQUERY t2 index f1_key f1_key 4 NULL 10 Using where; Using index +DROP TABLE t1,t2; +# +# lp:826279: assertion failure with GROUP BY a result of subquery # CREATE TABLE t1 (a int); INSERT INTO t1 VALUES (0), (0); @@ -6175,7 +6649,7 @@ EXPLAIN SELECT COUNT(f1), (SELECT f1 FROM t1 WHERE f2 > 0 limit 1) AS f4 FROM t2, t1 WHERE 'v'= f3; 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 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 2 +2 SUBQUERY t1 ALL NULL NULL NULL NULL 2 SELECT COUNT(f1), (SELECT f1 FROM t1 WHERE f2 > 0 limit 1) AS f4 FROM t2, t1 WHERE 'v'= f3; COUNT(f1) f4 0 NULL @@ -6183,7 +6657,7 @@ EXPLAIN SELECT COUNT(f1), exists(SELECT f1 FROM t1 WHERE f2 > 0 limit 1) AS f4 FROM t2, t1 WHERE 'v'= f3; 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 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 2 +2 SUBQUERY t1 ALL NULL NULL NULL NULL 2 SELECT COUNT(f1), exists(SELECT f1 FROM t1 WHERE f2 > 0 limit 1) AS f4 FROM t2, t1 WHERE 'v'= f3; COUNT(f1) f4 0 0 @@ -6290,3 +6764,246 @@ u2 DROP TABLE t1; # return optimizer switch changed in the beginning of this test set optimizer_switch=@subselect_tmp; +# +# lp:944706 Query with impossible or constant subquery in WHERE or HAVING is not +# precomputed and thus not part of optimization +# +CREATE TABLE t1 ( a VARCHAR(16), KEY (a) ); +INSERT INTO t1 VALUES ('Abilene'),('Akron'),('Albany'),('Albuquerque'),('Alexandria'),('Allentown'), +('Amarillo'),('Anaheim'),('Anchorage'),('Ann Arbor'),('Arden-Arcade'); +EXPLAIN +SELECT MAX( alias2.a ) AS field +FROM t1 AS alias1, t1 AS alias2, t1 AS alias3 +WHERE alias1.a = alias2.a OR alias1.a = 'y' +HAVING field>'B' AND ( 'Moscow' ) IN ( SELECT a FROM t1 ); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible HAVING +2 SUBQUERY t1 index_subquery a a 19 const 1 Using index; Using where +SELECT MAX( alias2.a ) AS field +FROM t1 AS alias1, t1 AS alias2, t1 AS alias3 +WHERE alias1.a = alias2.a OR alias1.a = 'y' +HAVING field>'B' AND ( 'Moscow' ) IN ( SELECT a FROM t1 ); +field +EXPLAIN +SELECT MAX( alias2.a ) +FROM t1 AS alias1, t1 AS alias2, t1 AS alias3 +WHERE alias1.a = alias2.a OR ('Moscow') IN ( SELECT a FROM t1 ); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY alias1 index a a 19 NULL 11 Using where; Using index +1 PRIMARY alias2 ref a a 19 test.alias1.a 2 Using index +1 PRIMARY alias3 index NULL a 19 NULL 11 Using index; Using join buffer (flat, BNL join) +2 SUBQUERY t1 index_subquery a a 19 const 1 Using index; Using where +SELECT MAX( alias2.a ) +FROM t1 AS alias1, t1 AS alias2, t1 AS alias3 +WHERE alias1.a = alias2.a OR ('Moscow') IN ( SELECT a FROM t1 ); +MAX( alias2.a ) +Arden-Arcade +drop table t1; +# +# MDEV-277 CHEAP SQ: Server crashes in st_join_table::get_examined_rows +# with semijoin+materialization, IN and = subqueries +# +CREATE TABLE t1 (a1 INT); +INSERT INTO t1 VALUES (4),(6); +CREATE TABLE t2 (b1 INT); +INSERT INTO t2 VALUES (1),(7); +EXPLAIN +SELECT * FROM t1 +WHERE a1 = (SELECT COUNT(*) FROM t1 WHERE a1 IN (SELECT a1 FROM t1, t2)); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 2 Using where +2 SUBQUERY t1 ALL NULL NULL NULL NULL 2 +2 SUBQUERY <subquery3> eq_ref distinct_key distinct_key 4 func 1 +3 MATERIALIZED t1 ALL NULL NULL NULL NULL 2 +3 MATERIALIZED t2 ALL NULL NULL NULL NULL 2 Using join buffer (flat, BNL join) +SELECT * FROM t1 +WHERE a1 = (SELECT COUNT(*) FROM t1 WHERE a1 IN (SELECT a1 FROM t1, t2)); +a1 +drop table t1, t2; +# +# MDEV-287 CHEAP SQ: A query with subquery in SELECT list, EXISTS, +# inner joins takes hundreds times longer +# +CREATE TABLE t1 (a INT); +INSERT INTO t1 VALUES (1),(7); +CREATE TABLE t2 (b INT); +INSERT INTO t2 VALUES (4),(5); +CREATE TABLE t3 (c INT); +INSERT INTO t3 VALUES (8),(3); +set @@expensive_subquery_limit= 0; +EXPLAIN +SELECT (SELECT MIN(b) FROM t1, t2 WHERE b = a AND (b = alias1.b OR EXISTS (SELECT * FROM t3))) +FROM t2 alias1, t1 alias2, t1 alias3; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY alias1 ALL NULL NULL NULL NULL 2 +1 PRIMARY alias2 ALL NULL NULL NULL NULL 2 Using join buffer (flat, BNL join) +1 PRIMARY alias3 ALL NULL NULL NULL NULL 2 Using join buffer (flat, BNL join) +2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 2 Using where +2 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 2 Using where; Using join buffer (flat, BNL join) +3 SUBQUERY t3 ALL NULL NULL NULL NULL 2 +flush status; +SELECT (SELECT MIN(b) FROM t1, t2 WHERE b = a AND (b = alias1.b OR EXISTS (SELECT * FROM t3))) +FROM t2 alias1, t1 alias2, t1 alias3; +(SELECT MIN(b) FROM t1, t2 WHERE b = a AND (b = alias1.b OR EXISTS (SELECT * FROM t3))) +NULL +NULL +NULL +NULL +NULL +NULL +NULL +NULL +show status like "subquery_cache%"; +Variable_name Value +Subquery_cache_hit 6 +Subquery_cache_miss 2 +show status like '%Handler_read%'; +Variable_name Value +Handler_read_first 0 +Handler_read_key 8 +Handler_read_last 0 +Handler_read_next 0 +Handler_read_prev 0 +Handler_read_rnd 0 +Handler_read_rnd_deleted 0 +Handler_read_rnd_next 22 +set @@expensive_subquery_limit= default; +EXPLAIN +SELECT (SELECT MIN(b) FROM t1, t2 WHERE b = a AND (b = alias1.b OR EXISTS (SELECT * FROM t3))) +FROM t2 alias1, t1 alias2, t1 alias3; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY alias1 ALL NULL NULL NULL NULL 2 +1 PRIMARY alias2 ALL NULL NULL NULL NULL 2 Using join buffer (flat, BNL join) +1 PRIMARY alias3 ALL NULL NULL NULL NULL 2 Using join buffer (flat, BNL join) +2 SUBQUERY t1 ALL NULL NULL NULL NULL 2 +2 SUBQUERY t2 ALL NULL NULL NULL NULL 2 Using where; Using join buffer (flat, BNL join) +3 SUBQUERY t3 ALL NULL NULL NULL NULL 2 +flush status; +SELECT (SELECT MIN(b) FROM t1, t2 WHERE b = a AND (b = alias1.b OR EXISTS (SELECT * FROM t3))) +FROM t2 alias1, t1 alias2, t1 alias3; +(SELECT MIN(b) FROM t1, t2 WHERE b = a AND (b = alias1.b OR EXISTS (SELECT * FROM t3))) +NULL +NULL +NULL +NULL +NULL +NULL +NULL +NULL +show status like "subquery_cache%"; +Variable_name Value +Subquery_cache_hit 0 +Subquery_cache_miss 0 +show status like '%Handler_read%'; +Variable_name Value +Handler_read_first 0 +Handler_read_key 0 +Handler_read_last 0 +Handler_read_next 0 +Handler_read_prev 0 +Handler_read_rnd 0 +Handler_read_rnd_deleted 0 +Handler_read_rnd_next 16 +drop table t1, t2, t3; +# +# MDEV-288 CHEAP SQ: Valgrind warnings "Memory lost" with IN and EXISTS nested subquery, materialization+semijoin +# +CREATE TABLE t1 (a INT); +INSERT INTO t1 VALUES (0),(8); +CREATE TABLE t2 (b INT PRIMARY KEY); +INSERT INTO t2 VALUES (1),(2); +EXPLAIN +SELECT * FROM t1 WHERE 4 IN (SELECT MAX(b) FROM t2 WHERE EXISTS (SELECT * FROM t1)); +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 MATERIALIZED NULL NULL NULL NULL NULL NULL NULL Select tables optimized away +3 SUBQUERY t1 ALL NULL NULL NULL NULL 2 +SELECT * FROM t1 WHERE 4 IN (SELECT MAX(b) FROM t2 WHERE EXISTS (SELECT * FROM t1)); +a +drop table t1,t2; +# +# MDEV-410: EXPLAIN shows type=range, while SHOW EXPLAIN and userstat show full table scan is used +# +CREATE TABLE t1 (a VARCHAR(3) PRIMARY KEY) ENGINE=MyISAM; +INSERT INTO t1 VALUES ('USA'); +CREATE TABLE t2 (b INT, c VARCHAR(52), KEY(b)) ENGINE=MyISAM; +INSERT INTO t2 VALUES (3813,'United States'),(3940,'Russia'); +CREATE TABLE t3 (d INT, KEY(d)) ENGINE=MyISAM; +INSERT INTO t3 VALUES (12),(22),(9),(45); +create table t4 like t3; +insert into t4 select * from t3; +# This should not show range access for table t2 +explain +SELECT MIN(b) FROM ( SELECT * FROM t1, t2, t3 WHERE d = b ) AS alias1 +WHERE SLEEP(0.1) OR c < 'p' OR b = ( SELECT MIN(b) FROM t2 ); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 system NULL NULL NULL NULL 1 +1 PRIMARY t2 ALL b NULL NULL NULL 2 Using where +1 PRIMARY t3 ref d d 5 test.t2.b 2 Using where; Using index +3 SUBQUERY NULL NULL NULL NULL NULL NULL NULL Select tables optimized away +set @tmp_mdev410=@@global.userstat; +set global userstat=on; +flush table_statistics; +flush index_statistics; +SELECT MIN(b) FROM ( SELECT * FROM t1, t2, t3 WHERE d = b ) AS alias1 +WHERE SLEEP(0.1) OR c < 'p' OR b = ( SELECT MIN(b) FROM t2 ); +MIN(b) +NULL +# The following shows that t2 was indeed scanned with a full scan. +show table_statistics; +Table_schema Table_name Rows_read Rows_changed Rows_changed_x_#indexes +test t1 2 0 0 +test t2 3 0 0 +show index_statistics; +Table_schema Table_name Index_name Rows_read +test t2 b 1 +set global userstat=@tmp_mdev410; +DROP TABLE t1,t2,t3,t4; +# +# MDEV-430: Server crashes in select_describe on EXPLAIN with +# materialization+semijoin, 2 nested subqueries, aggregate functions +# +CREATE TABLE t1 (a INT, KEY(a)); +INSERT INTO t1 VALUES (1),(8); +CREATE TABLE t2 (b INT, KEY(b)); +INSERT INTO t2 VALUES (45),(17),(20); +EXPLAIN SELECT * FROM t1 WHERE EXISTS ( SELECT a FROM t1, t2 WHERE b = a GROUP BY a HAVING a <> 1 ) ; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE +2 SUBQUERY t1 index a a 5 NULL 2 Using where; Using index +2 SUBQUERY t2 ref b b 5 test.t1.a 2 Using index +DROP TABLE t1,t2; +# +# MDEV-435: Expensive subqueries may be evaluated during optimization in merge_key_fields +# +CREATE TABLE t1 (a INT, KEY(a)) ENGINE=MyISAM; +INSERT INTO t1 VALUES (8),(0); +CREATE TABLE t2 (b INT, c VARCHAR(1)) ENGINE=MyISAM; +INSERT INTO t2 VALUES (4,'j'),(6,'v'); +CREATE TABLE t3 (d VARCHAR(1)) ENGINE=MyISAM; +INSERT INTO t3 VALUES ('b'),('c'); +EXPLAIN +SELECT * FROM t1 +WHERE a = (SELECT MAX(b) FROM t2 WHERE c IN (SELECT MAX(d) FROM t3)) OR a = 10; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 index a a 5 NULL 2 Using where; Using index +2 SUBQUERY <subquery3> ALL distinct_key NULL NULL NULL 1 +2 SUBQUERY t2 ALL NULL NULL NULL NULL 2 Using where; Using join buffer (flat, BNL join) +3 MATERIALIZED t3 ALL NULL NULL NULL NULL 2 +SELECT * FROM t1 +WHERE a = (SELECT MAX(b) FROM t2 WHERE c IN (SELECT MAX(d) FROM t3)) OR a = 10; +a +drop table t1, t2, t3; +# +# MDEV-405: Server crashes in test_if_skip_sort_order on EXPLAIN with GROUP BY and HAVING in EXISTS subquery +# +CREATE TABLE t1 (a INT, KEY(a)); +INSERT INTO t1 VALUES (1),(8); +CREATE TABLE t2 (b INT, KEY(b)); +INSERT INTO t2 VALUES (45),(17),(20); +EXPLAIN SELECT * FROM t1 WHERE EXISTS ( SELECT a FROM t1, t2 WHERE b = a GROUP BY a HAVING a <> 1 ) ; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE +2 SUBQUERY t1 index a a 5 NULL 2 Using where; Using index +2 SUBQUERY t2 ref b b 5 test.t1.a 2 Using index +DROP TABLE t1,t2; |