summaryrefslogtreecommitdiff
path: root/mysql-test/r/subselect_no_mat.result
diff options
context:
space:
mode:
Diffstat (limited to 'mysql-test/r/subselect_no_mat.result')
-rw-r--r--mysql-test/r/subselect_no_mat.result492
1 files changed, 403 insertions, 89 deletions
diff --git a/mysql-test/r/subselect_no_mat.result b/mysql-test/r/subselect_no_mat.result
index 9365223b1a6..cd805fb8e86 100644
--- a/mysql-test/r/subselect_no_mat.result
+++ b/mysql-test/r/subselect_no_mat.result
@@ -1,11 +1,14 @@
select @@optimizer_switch like '%materialization=on%';
@@optimizer_switch like '%materialization=on%'
-1
+0
set optimizer_switch='materialization=off';
+set optimizer_switch='mrr=on,mrr_sort_keys=on,index_condition_pushdown=on';
drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t11,t12;
drop view if exists v2;
-set @save_optimizer_switch=@@optimizer_switch;
-set @@optimizer_switch="partial_match_rowid_merge=off,partial_match_table_scan=off";
+set @subselect_tmp=@@optimizer_switch;
+set @@optimizer_switch=ifnull(@optimizer_switch_for_subselect_test,
+"semijoin=on,firstmatch=on,loosescan=on,partial_match_rowid_merge=off,partial_match_table_scan=off");
+set optimizer_switch='mrr=on,mrr_sort_keys=on,index_condition_pushdown=on';
select (select 2);
(select 2)
2
@@ -57,7 +60,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra
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 dual 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
@@ -206,11 +209,11 @@ select (select t3.a from t3 where a<8 order by 1 desc limit 1), a from
explain extended select (select t3.a from t3 where a<8 order by 1 desc limit 1), a from
(select * from t2 where a>1) as tt;
id select_type table type possible_keys key key_len ref rows filtered Extra
-1 PRIMARY <derived3> system NULL NULL NULL NULL 1 100.00
+1 PRIMARY <derived3> ALL NULL NULL NULL NULL 2 100.00
3 DERIVED t2 ALL NULL NULL NULL NULL 2 100.00 Using where
2 SUBQUERY t3 ALL NULL NULL NULL NULL 3 100.00 Using where; Using filesort
Warnings:
-Note 1003 select (select `test`.`t3`.`a` 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
+Note 1003 select (select `test`.`t3`.`a` from `test`.`t3` where (`test`.`t3`.`a` < 8) order by 1 desc limit 1) AS `(select t3.a from t3 where a<8 order by 1 desc limit 1)`,`tt`.`a` AS `a` from (select `test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b` from `test`.`t2` where (`test`.`t2`.`a` > 1)) `tt`
select * from t1 where t1.a=(select t2.a from t2 where t2.b=(select max(a) from t3) order by 1 desc limit 1);
a
2
@@ -277,7 +280,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY t3 ALL NULL NULL NULL NULL 3 100.00 Using where
2 SUBQUERY t2 ALL NULL NULL NULL NULL 3 100.00
Warnings:
-Note 1003 select `test`.`t3`.`a` AS `a` from `test`.`t3` where <nop>((`test`.`t3`.`a` >= (select min(`test`.`t2`.`b`) from `test`.`t2`)))
+Note 1003 select `test`.`t3`.`a` AS `a` from `test`.`t3` where <nop>(<in_optimizer>(`test`.`t3`.`a`,((select min(`test`.`t2`.`b`) from `test`.`t2`) <= `test`.`t3`.`a`)))
select * from t3 where a >= all (select b from t2);
a
7
@@ -321,7 +324,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 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`
+Note 1003 select (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));
@@ -339,7 +342,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra
2 DEPENDENT SUBQUERY t7 eq_ref PRIMARY PRIMARY 4 test.t6.clinic_uq 1 100.00 Using index
Warnings:
Note 1276 Field or reference 'test.t6.clinic_uq' of SELECT #2 was resolved in SELECT #1
-Note 1003 select `test`.`t6`.`patient_uq` AS `patient_uq`,`test`.`t6`.`clinic_uq` AS `clinic_uq` from `test`.`t6` where <expr_cache><`test`.`t6`.`clinic_uq`>(exists(select 1 from `test`.`t7` where (`test`.`t7`.`uq` = `test`.`t6`.`clinic_uq`)))
+Note 1003 select `test`.`t6`.`patient_uq` AS `patient_uq`,`test`.`t6`.`clinic_uq` AS `clinic_uq` from `test`.`t6` where exists(select 1 from `test`.`t7` where (`test`.`t7`.`uq` = `test`.`t6`.`clinic_uq`))
select * from t1 where a= (select a from t2,t4 where t2.b=t4.b);
ERROR 23000: Column 'a' in field list is ambiguous
drop table t1,t2,t3;
@@ -370,11 +373,11 @@ 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 index
-4 SUBQUERY t8 const PRIMARY PRIMARY 37 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
-3 SUBQUERY t8 const PRIMARY PRIMARY 37 1 100.00 Using index
+3 SUBQUERY t8 const PRIMARY PRIMARY 37 const 1 100.00 Using index
Warnings:
-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
+Note 1003 select 'joce' AS `pseudo`,(select 'test' from `test`.`t8` where ('joce' = (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 ('joce' = (select '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)
@@ -427,7 +430,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
+Note 1003 select 1 AS `1` from `test`.`t1` where (1 = (select 1 union select 1))
drop table t1;
CREATE TABLE `t1` (
`numeropost` mediumint(8) unsigned NOT NULL auto_increment,
@@ -512,6 +515,9 @@ select numeropost as a FROM t1 GROUP BY (SELECT 1 FROM t1 HAVING a=1);
ERROR 21000: Subquery returns more than 1 row
select numeropost as a FROM t1 ORDER BY (SELECT 1 FROM t1 HAVING a=1);
ERROR 21000: Subquery returns more than 1 row
+show warnings;
+Level Code Message
+Error 1242 Subquery returns more than 1 row
drop table t1;
create table t1 (a int);
insert into t1 values (1),(2),(3);
@@ -547,13 +553,13 @@ EXPLAIN EXTENDED SELECT MAX(numreponse) FROM t1 WHERE numeropost='1';
id select_type table type possible_keys key key_len ref rows filtered Extra
1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL Select tables optimized away
Warnings:
-Note 1003 select max(`test`.`t1`.`numreponse`) AS `MAX(numreponse)` from `test`.`t1` where (`test`.`t1`.`numeropost` = '1')
+Note 1003 select max(`test`.`t1`.`numreponse`) AS `MAX(numreponse)` from `test`.`t1` where multiple equal(1, `test`.`t1`.`numeropost`)
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 index
2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL Select tables optimized away
Warnings:
-Note 1003 select '3' AS `numreponse` from `test`.`t1` where (('1' = '1'))
+Note 1003 select 3 AS `numreponse` from `test`.`t1` where ((3 = (select max(`test`.`t1`.`numreponse`) from `test`.`t1` where multiple equal(1, `test`.`t1`.`numeropost`))))
drop table t1;
CREATE TABLE t1 (a int(1));
INSERT INTO t1 VALUES (1);
@@ -593,7 +599,7 @@ a b
select * from t1 where b = (select b from t2 where t1.a = t2.a);
a b
2 12
-delete from t1 where b = (select b from t1);
+delete from t1 where b in (select b from t1);
ERROR HY000: You can't specify target table 't1' for update in FROM clause
delete from t1 where b = (select b from t2);
ERROR 21000: Subquery returns more than 1 row
@@ -750,7 +756,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra
3 DEPENDENT UNION NULL NULL NULL NULL NULL NULL NULL NULL No tables used
NULL UNION RESULT <union2,3> ALL NULL NULL NULL NULL NULL NULL
Warnings:
-Note 1003 select `test`.`t2`.`id` AS `id` from `test`.`t2` where <expr_cache><`test`.`t2`.`id`>(<in_optimizer>(`test`.`t2`.`id`,<exists>(select 1 having (<cache>(`test`.`t2`.`id`) = <ref_null_helper>(1)) union select 3 having (<cache>(`test`.`t2`.`id`) = <ref_null_helper>(3)))))
+Note 1003 select `test`.`t2`.`id` AS `id` from `test`.`t2` where <in_optimizer>(`test`.`t2`.`id`,<exists>(select 1 having (<cache>(`test`.`t2`.`id`) = <ref_null_helper>(1)) union select 3 having (<cache>(`test`.`t2`.`id`) = <ref_null_helper>(3))))
SELECT * FROM t2 WHERE id IN (SELECT 5 UNION SELECT 3);
id
SELECT * FROM t2 WHERE id IN (SELECT 5 UNION SELECT 2);
@@ -898,7 +904,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY t1 index NULL PRIMARY 4 NULL 4 100.00 Using index
2 DEPENDENT SUBQUERY t2 index_subquery a a 5 func 2 100.00 Using index
Warnings:
-Note 1003 select `test`.`t1`.`a` AS `a`,<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`
+Note 1003 select `test`.`t1`.`a` AS `a`,<in_optimizer>(`test`.`t1`.`a`,<exists>(<index_lookup>(<cache>(`test`.`t1`.`a`) in t2 on a checking NULL 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');
INSERT INTO t3 VALUES (1),(2),(3);
SELECT t1.a, t1.a in (select t2.a from t2,t3 where t3.a=t2.a) FROM t1;
@@ -910,10 +916,10 @@ a t1.a in (select t2.a from t2,t3 where t3.a=t2.a)
explain extended SELECT t1.a, t1.a in (select t2.a from t2,t3 where t3.a=t2.a) FROM t1;
id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY t1 index NULL PRIMARY 4 NULL 4 100.00 Using index
-2 DEPENDENT SUBQUERY t2 ref_or_null a a 5 func 2 100.00 Using index
-2 DEPENDENT SUBQUERY t3 ALL NULL NULL NULL NULL 3 100.00 Using where; Using join buffer
+2 DEPENDENT SUBQUERY t2 ref_or_null a a 5 func 2 100.00 Using where; Using index
+2 DEPENDENT SUBQUERY t3 ALL NULL NULL NULL NULL 3 100.00 Using where; Using join buffer (flat, BNL join)
Warnings:
-Note 1003 select `test`.`t1`.`a` AS `a`,<expr_cache><`test`.`t1`.`a`>(<in_optimizer>(`test`.`t1`.`a`,<exists>(select 1 from `test`.`t2` join `test`.`t3` where ((`test`.`t3`.`a` = `test`.`t2`.`a`) and ((<cache>(`test`.`t1`.`a`) = `test`.`t2`.`a`) or isnull(`test`.`t2`.`a`))) having <is_not_null_test>(`test`.`t2`.`a`)))) AS `t1.a in (select t2.a from t2,t3 where t3.a=t2.a)` from `test`.`t1`
+Note 1003 select `test`.`t1`.`a` AS `a`,<in_optimizer>(`test`.`t1`.`a`,<exists>(select `test`.`t2`.`a` from `test`.`t2` join `test`.`t3` where ((`test`.`t3`.`a` = `test`.`t2`.`a`) and ((<cache>(`test`.`t1`.`a`) = `test`.`t2`.`a`) or isnull(`test`.`t2`.`a`))) having <is_not_null_test>(`test`.`t2`.`a`))) AS `t1.a in (select t2.a from t2,t3 where t3.a=t2.a)` from `test`.`t1`
drop table t1,t2,t3;
create table t1 (a float);
select 10.5 IN (SELECT * from t1 LIMIT 1);
@@ -1184,9 +1190,9 @@ SELECT 0 IN (SELECT 1 FROM t1 a);
EXPLAIN EXTENDED SELECT 0 IN (SELECT 1 FROM t1 a);
id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY NULL NULL NULL NULL NULL NULL NULL NULL No tables used
-2 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE
+2 DEPENDENT 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)) 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)
@@ -1194,9 +1200,9 @@ SELECT 0 IN (SELECT 1 FROM t1 a);
EXPLAIN EXTENDED SELECT 0 IN (SELECT 1 FROM t1 a);
id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY NULL NULL NULL NULL NULL NULL NULL NULL No tables used
-2 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE
+2 DEPENDENT 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)) AS `0 IN (SELECT 1 FROM t1 a)`
+Note 1003 select <in_optimizer>(0,<exists>(select 1 from `test`.`t1` `a` where (0 = 1))) AS `0 IN (SELECT 1 FROM t1 a)`
drop table t1;
CREATE TABLE `t1` (
`i` int(11) NOT NULL default '0',
@@ -1238,7 +1244,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 1 100.00 Using index condition
+1 PRIMARY t1 ref salary salary 5 const 0 0.00 Using index condition
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`))
@@ -1301,7 +1307,7 @@ a
explain extended select * from t2 where t2.a in (select a from t1);
id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY t2 index PRIMARY PRIMARY 4 NULL 4 100.00 Using index
-1 PRIMARY t1 index PRIMARY PRIMARY 4 NULL 4 75.00 Using where; Using index; Using join buffer
+1 PRIMARY t1 index PRIMARY PRIMARY 4 NULL 4 75.00 Using where; Using index; Using join buffer (flat, BNL join)
Warnings:
Note 1003 select `test`.`t2`.`a` AS `a` from `test`.`t1` join `test`.`t2` where (`test`.`t1`.`a` = `test`.`t2`.`a`)
select * from t2 where t2.a in (select a from t1 where t1.b <> 30);
@@ -1311,7 +1317,7 @@ a
explain extended select * from t2 where t2.a in (select a from t1 where t1.b <> 30);
id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY t2 index PRIMARY PRIMARY 4 NULL 4 100.00 Using index
-1 PRIMARY t1 ALL PRIMARY NULL NULL NULL 4 75.00 Using where; Using join buffer
+1 PRIMARY t1 ALL PRIMARY NULL NULL NULL 4 75.00 Using where; Using join buffer (flat, BNL join)
Warnings:
Note 1003 select `test`.`t2`.`a` AS `a` from `test`.`t1` join `test`.`t2` where ((`test`.`t1`.`a` = `test`.`t2`.`a`) and (`test`.`t1`.`b` <> 30))
select * from t2 where t2.a in (select t1.a from t1,t3 where t1.b=t3.a);
@@ -1321,7 +1327,7 @@ a
explain extended select * from t2 where t2.a in (select t1.a from t1,t3 where t1.b=t3.a);
id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY t2 index PRIMARY PRIMARY 4 NULL 4 100.00 Using index
-1 PRIMARY t1 ALL PRIMARY NULL NULL NULL 4 75.00 Using where; Using join buffer
+1 PRIMARY t1 ALL PRIMARY NULL NULL NULL 4 75.00 Using where; Using join buffer (flat, BNL join)
1 PRIMARY t3 eq_ref PRIMARY PRIMARY 4 test.t1.b 1 100.00 Using index
Warnings:
Note 1003 select `test`.`t2`.`a` AS `a` from `test`.`t1` join `test`.`t3` join `test`.`t2` where ((`test`.`t1`.`a` = `test`.`t2`.`a`) and (`test`.`t3`.`a` = `test`.`t1`.`b`))
@@ -1343,7 +1349,7 @@ a
4
explain extended select * from t2 where t2.a in (select a from t1);
id select_type table type possible_keys key key_len ref rows filtered Extra
-1 PRIMARY t2 index a a 5 NULL 4 100.00 Using index
+1 PRIMARY t2 index a a 5 NULL 4 100.00 Using where; Using index
1 PRIMARY t1 ref a a 5 test.t2.a 101 100.00 Using index; FirstMatch(t2)
Warnings:
Note 1003 select `test`.`t2`.`a` AS `a` from `test`.`t2` semi join (`test`.`t1`) where (`test`.`t1`.`a` = `test`.`t2`.`a`)
@@ -1353,7 +1359,7 @@ a
4
explain extended select * from t2 where t2.a in (select a from t1 where t1.b <> 30);
id select_type table type possible_keys key key_len ref rows filtered Extra
-1 PRIMARY t2 index a a 5 NULL 4 100.00 Using index
+1 PRIMARY t2 index a a 5 NULL 4 100.00 Using where; Using index
1 PRIMARY t1 ref a a 5 test.t2.a 101 100.00 Using where; Using index; FirstMatch(t2)
Warnings:
Note 1003 select `test`.`t2`.`a` AS `a` from `test`.`t2` semi join (`test`.`t1`) where ((`test`.`t1`.`a` = `test`.`t2`.`a`) and (`test`.`t1`.`b` <> 30))
@@ -1363,9 +1369,9 @@ a
3
explain extended select * from t2 where t2.a in (select t1.a from t1,t3 where t1.b=t3.a);
id select_type table type possible_keys key key_len ref rows filtered Extra
-1 PRIMARY t2 index a a 5 NULL 4 100.00 Using index
-1 PRIMARY t3 index a a 5 NULL 3 100.00 Using index
-1 PRIMARY t1 ref a a 10 test.t2.a,test.t3.a 116 100.61 Using index; FirstMatch(t2)
+1 PRIMARY t2 index a a 5 NULL 4 100.00 Using where; Using index
+1 PRIMARY t3 index a a 5 NULL 3 100.00 Using where; Using index
+1 PRIMARY t1 ref a a 10 test.t2.a,test.t3.a 116 100.00 Using index; FirstMatch(t2)
Warnings:
Note 1003 select `test`.`t2`.`a` AS `a` from `test`.`t2` semi join (`test`.`t1` join `test`.`t3`) where ((`test`.`t1`.`a` = `test`.`t2`.`a`) and (`test`.`t1`.`b` = `test`.`t3`.`a`))
insert into t1 values (3,31);
@@ -1380,7 +1386,7 @@ a
4
explain extended select * from t2 where t2.a in (select a from t1 where t1.b <> 30);
id select_type table type possible_keys key key_len ref rows filtered Extra
-1 PRIMARY t2 index a a 5 NULL 4 100.00 Using index
+1 PRIMARY t2 index a a 5 NULL 4 100.00 Using where; Using index
1 PRIMARY t1 ref a a 5 test.t2.a 101 100.00 Using where; Using index; FirstMatch(t2)
Warnings:
Note 1003 select `test`.`t2`.`a` AS `a` from `test`.`t2` semi join (`test`.`t1`) where ((`test`.`t1`.`a` = `test`.`t2`.`a`) and (`test`.`t1`.`b` <> 30))
@@ -1418,7 +1424,7 @@ INSERT INTO t1 VALUES ('z','?');
select * from t1 where s1 > (select max(s2) from t1);
ERROR HY000: Illegal mix of collations (latin1_german1_ci,IMPLICIT) and (latin1_swedish_ci,IMPLICIT) for operation '>'
select * from t1 where s1 > any (select max(s2) from t1);
-ERROR HY000: Illegal mix of collations (latin1_german1_ci,IMPLICIT) and (latin1_swedish_ci,IMPLICIT) for operation '>'
+ERROR HY000: Illegal mix of collations (latin1_swedish_ci,IMPLICIT) and (latin1_german1_ci,IMPLICIT) for operation '<'
drop table t1;
create table t1(toid int,rd int);
create table t2(userid int,pmnew int,pmtotal int);
@@ -1474,25 +1480,25 @@ 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
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`
+Note 1003 select `test`.`t1`.`s1` AS `s1`,(not(<in_optimizer>(`test`.`t1`.`s1`,<exists>(<index_lookup>(<cache>(`test`.`t1`.`s1`) in t2 on s1 checking NULL 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
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`
+Note 1003 select `test`.`t1`.`s1` AS `s1`,<in_optimizer>(`test`.`t1`.`s1`,<exists>(<index_lookup>(<cache>(`test`.`t1`.`s1`) in t2 on s1 checking NULL 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
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`
+Note 1003 select `test`.`t1`.`s1` AS `s1`,(not(<in_optimizer>(`test`.`t1`.`s1`,<exists>(<index_lookup>(<cache>(`test`.`t1`.`s1`) in t2 on s1 checking NULL 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
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`
+Note 1003 select `test`.`t1`.`s1` AS `s1`,(not(<in_optimizer>(`test`.`t1`.`s1`,<exists>(<index_lookup>(<cache>(`test`.`t1`.`s1`) in t2 on s1 checking NULL where (`test`.`t2`.`s1` < '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;
create table t2 (a int, b int);
create table t3 (a int);
@@ -1507,7 +1513,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY t3 ALL NULL NULL NULL NULL 3 100.00 Using where
2 SUBQUERY t2 system NULL NULL NULL NULL 0 0.00 const row not found
Warnings:
-Note 1003 select `test`.`t3`.`a` AS `a` from `test`.`t3` where <not>((`test`.`t3`.`a` < (select max(NULL) from `test`.`t2`)))
+Note 1003 select `test`.`t3`.`a` AS `a` from `test`.`t3` where <not>(<in_optimizer>(`test`.`t3`.`a`,((select max(NULL) from `test`.`t2`) > `test`.`t3`.`a`)))
select * from t3 where a >= some (select b from t2);
a
explain extended select * from t3 where a >= some (select b from t2);
@@ -1515,7 +1521,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY t3 ALL NULL NULL NULL NULL 3 100.00 Using where
2 SUBQUERY t2 system NULL NULL NULL NULL 0 0.00 const row not found
Warnings:
-Note 1003 select `test`.`t3`.`a` AS `a` from `test`.`t3` where <nop>((`test`.`t3`.`a` >= (select min(NULL) from `test`.`t2`)))
+Note 1003 select `test`.`t3`.`a` AS `a` from `test`.`t3` where <nop>(<in_optimizer>(`test`.`t3`.`a`,((select min(NULL) from `test`.`t2`) <= `test`.`t3`.`a`)))
select * from t3 where a >= all (select b from t2 group by 1);
a
6
@@ -1526,7 +1532,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY t3 ALL NULL NULL NULL NULL 3 100.00 Using where
2 SUBQUERY t2 system NULL NULL NULL NULL 0 0.00 const row not found
Warnings:
-Note 1003 select `test`.`t3`.`a` AS `a` from `test`.`t3` where <not>((`test`.`t3`.`a` < <max>(select NULL from `test`.`t2` group by 1)))
+Note 1003 select `test`.`t3`.`a` AS `a` from `test`.`t3` where <not>(<in_optimizer>(`test`.`t3`.`a`,(<max>(select NULL from `test`.`t2` group by 1) > `test`.`t3`.`a`)))
select * from t3 where a >= some (select b from t2 group by 1);
a
explain extended select * from t3 where a >= some (select b from t2 group by 1);
@@ -1534,39 +1540,39 @@ id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY t3 ALL NULL NULL NULL NULL 3 100.00 Using where
2 SUBQUERY t2 system NULL NULL NULL NULL 0 0.00 const row not found
Warnings:
-Note 1003 select `test`.`t3`.`a` AS `a` from `test`.`t3` where <nop>((`test`.`t3`.`a` >= <min>(select NULL from `test`.`t2` group by 1)))
+Note 1003 select `test`.`t3`.`a` AS `a` from `test`.`t3` where <nop>(<in_optimizer>(`test`.`t3`.`a`,(<min>(select NULL from `test`.`t2` group by 1) <= `test`.`t3`.`a`)))
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 NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE
+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
Warnings:
-Note 1003 select `test`.`t3`.`a` AS `a` from `test`.`t3` where 0
+Note 1003 select `test`.`t3`.`a` AS `a` from `test`.`t3` where <nop>(<in_optimizer>(NULL,((select min(NULL) from `test`.`t2`) <= NULL)))
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 NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE
+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
Warnings:
-Note 1003 select `test`.`t3`.`a` AS `a` from `test`.`t3` where 0
+Note 1003 select `test`.`t3`.`a` AS `a` from `test`.`t3` where <nop>(<in_optimizer>(NULL,(<min>(select NULL from `test`.`t2` group by 1) <= NULL)))
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 NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE
+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
Warnings:
-Note 1003 select `test`.`t3`.`a` AS `a` from `test`.`t3` where 0
+Note 1003 select `test`.`t3`.`a` AS `a` from `test`.`t3` where <nop>(<in_optimizer>(NULL,((select min(NULL) from `test`.`t2`) <= NULL)))
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 NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE
+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
Warnings:
-Note 1003 select `test`.`t3`.`a` AS `a` from `test`.`t3` where 0
+Note 1003 select `test`.`t3`.`a` AS `a` from `test`.`t3` where <nop>(<in_optimizer>(NULL,(<min>(select NULL from `test`.`t2` group by 1) <= NULL)))
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
@@ -1575,9 +1581,9 @@ a
explain extended select * from t3 where a > all (select max(b) from t2 group by a);
id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY t3 ALL NULL NULL NULL NULL 3 100.00 Using where
-2 SUBQUERY t2 ALL NULL NULL NULL NULL 4 100.00 Using temporary; Using filesort
+2 SUBQUERY t2 ALL NULL NULL NULL NULL 4 100.00 Using temporary
Warnings:
-Note 1003 select `test`.`t3`.`a` AS `a` from `test`.`t3` where <not>((`test`.`t3`.`a` <= <max>(select max(`test`.`t2`.`b`) from `test`.`t2` group by `test`.`t2`.`a`)))
+Note 1003 select `test`.`t3`.`a` AS `a` from `test`.`t3` where <not>(<in_optimizer>(`test`.`t3`.`a`,(<max>(select max(`test`.`t2`.`b`) from `test`.`t2` group by `test`.`t2`.`a`) >= `test`.`t3`.`a`)))
drop table t2, t3;
CREATE TABLE `t1` ( `id` mediumint(9) NOT NULL auto_increment, `taskid` bigint(20) NOT NULL default '0', `dbid` int(11) NOT NULL default '0', `create_date` datetime NOT NULL default '0000-00-00 00:00:00', `last_update` datetime NOT NULL default '0000-00-00 00:00:00', PRIMARY KEY (`id`)) ENGINE=MyISAM CHARSET=latin1 AUTO_INCREMENT=3 ;
INSERT INTO `t1` (`id`, `taskid`, `dbid`, `create_date`,`last_update`) VALUES (1, 1, 15, '2003-09-29 10:31:36', '2003-09-29 10:31:36'), (2, 1, 21, now(), now());
@@ -1628,7 +1634,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 dual where 1
+Note 1003 select 'e' AS `s1` from dual where <nop>(<in_optimizer>('f',(<min>(select 'e' from dual union select 'e' from dual) < 'f')))
drop table t1;
CREATE TABLE t1 (number char(11) NOT NULL default '') ENGINE=MyISAM CHARSET=latin1;
INSERT INTO t1 VALUES ('69294728265'),('18621828126'),('89356874041'),('95895001874');
@@ -1747,14 +1753,14 @@ id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY t1 ALL NULL NULL NULL NULL 12 100.00 Using where
2 DEPENDENT SUBQUERY t1 unique_subquery PRIMARY PRIMARY 4 func 1 100.00 Using index; Using where
Warnings:
-Note 1003 select `test`.`t1`.`id` AS `id`,`test`.`t1`.`text` AS `text` from `test`.`t1` where (not(<expr_cache><`test`.`t1`.`id`>(<in_optimizer>(`test`.`t1`.`id`,<exists>(<primary_index_lookup>(<cache>(`test`.`t1`.`id`) in t1 on PRIMARY where ((`test`.`t1`.`id` < 8) and (<cache>(`test`.`t1`.`id`) = `test`.`t1`.`id`))))))))
+Note 1003 select `test`.`t1`.`id` AS `id`,`test`.`t1`.`text` AS `text` from `test`.`t1` where (not(<in_optimizer>(`test`.`t1`.`id`,<exists>(<primary_index_lookup>(<cache>(`test`.`t1`.`id`) in t1 on PRIMARY where ((`test`.`t1`.`id` < 8) and (<cache>(`test`.`t1`.`id`) = `test`.`t1`.`id`)))))))
explain extended select * from t1 as tt where not exists (select id from t1 where id < 8 and (id = tt.id or id is null) having id is not null);
id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY tt ALL NULL NULL NULL NULL 12 100.00 Using where
2 DEPENDENT SUBQUERY t1 eq_ref PRIMARY PRIMARY 4 test.tt.id 1 100.00 Using where; Using index
Warnings:
Note 1276 Field or reference 'test.tt.id' of SELECT #2 was resolved in SELECT #1
-Note 1003 select `test`.`tt`.`id` AS `id`,`test`.`tt`.`text` AS `text` from `test`.`t1` `tt` where (not(<expr_cache><`test`.`tt`.`id`>(exists(select `test`.`t1`.`id` from `test`.`t1` where ((`test`.`t1`.`id` < 8) and (`test`.`t1`.`id` = `test`.`tt`.`id`)) having (`test`.`t1`.`id` is not null)))))
+Note 1003 select `test`.`tt`.`id` AS `id`,`test`.`tt`.`text` AS `text` from `test`.`t1` `tt` where (not(exists(select `test`.`t1`.`id` from `test`.`t1` where ((`test`.`t1`.`id` < 8) and (`test`.`t1`.`id` = `test`.`tt`.`id`)) having (`test`.`t1`.`id` is not null))))
insert into t1 (id, text) values (1000, 'text1000'), (1001, 'text1001');
create table t2 (id int not null, text varchar(20) not null default '', primary key (id));
insert into t2 (id, text) values (1, 'text1'), (2, 'text2'), (3, 'text3'), (4, 'text4'), (5, 'text5'), (6, 'text6'), (7, 'text7'), (8, 'text8'), (9, 'text9'), (10, 'text10'), (11, 'text1'), (12, 'text2'), (13, 'text3'), (14, 'text4'), (15, 'text5'), (16, 'text6'), (17, 'text7'), (18, 'text8'), (19, 'text9'), (20, 'text10'),(21, 'text1'), (22, 'text2'), (23, 'text3'), (24, 'text4'), (25, 'text5'), (26, 'text6'), (27, 'text7'), (28, 'text8'), (29, 'text9'), (30, 'text10'), (31, 'text1'), (32, 'text2'), (33, 'text3'), (34, 'text4'), (35, 'text5'), (36, 'text6'), (37, 'text7'), (38, 'text8'), (39, 'text9'), (40, 'text10'), (41, 'text1'), (42, 'text2'), (43, 'text3'), (44, 'text4'), (45, 'text5'), (46, 'text6'), (47, 'text7'), (48, 'text8'), (49, 'text9'), (50, 'text10');
@@ -2290,7 +2296,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra
2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 2 100.00 Using where
Warnings:
Note 1276 Field or reference 'test.up.a' of SELECT #2 was resolved in SELECT #1
-Note 1003 select `test`.`up`.`a` AS `a`,`test`.`up`.`b` AS `b` from `test`.`t1` `up` where <expr_cache><`test`.`up`.`a`>(exists(select 1 from `test`.`t1` where (`test`.`t1`.`a` = `test`.`up`.`a`)))
+Note 1003 select `test`.`up`.`a` AS `a`,`test`.`up`.`b` AS `b` from `test`.`t1` `up` where exists(select 1 from `test`.`t1` where (`test`.`t1`.`a` = `test`.`up`.`a`))
drop table t1;
CREATE TABLE t1 (t1_a int);
INSERT INTO t1 VALUES (1);
@@ -2831,7 +2837,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY t1 ALL NULL NULL NULL NULL 8 100.00
2 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 9 100.00 Using where
Warnings:
-Note 1003 select `test`.`t1`.`one` AS `one`,`test`.`t1`.`two` AS `two`,<expr_cache><`test`.`t1`.`two`,`test`.`t1`.`one`>(<in_optimizer>((`test`.`t1`.`one`,`test`.`t1`.`two`),<exists>(select `test`.`t2`.`one`,`test`.`t2`.`two` from `test`.`t2` where ((`test`.`t2`.`flag` = '0') and trigcond(((<cache>(`test`.`t1`.`one`) = `test`.`t2`.`one`) or isnull(`test`.`t2`.`one`))) and trigcond(((<cache>(`test`.`t1`.`two`) = `test`.`t2`.`two`) or isnull(`test`.`t2`.`two`)))) having (trigcond(<is_not_null_test>(`test`.`t2`.`one`)) and trigcond(<is_not_null_test>(`test`.`t2`.`two`)))))) AS `test` from `test`.`t1`
+Note 1003 select `test`.`t1`.`one` AS `one`,`test`.`t1`.`two` AS `two`,<in_optimizer>((`test`.`t1`.`one`,`test`.`t1`.`two`),<exists>(select `test`.`t2`.`one`,`test`.`t2`.`two` from `test`.`t2` where ((`test`.`t2`.`flag` = '0') and trigcond(((<cache>(`test`.`t1`.`one`) = `test`.`t2`.`one`) or isnull(`test`.`t2`.`one`))) and trigcond(((<cache>(`test`.`t1`.`two`) = `test`.`t2`.`two`) or isnull(`test`.`t2`.`two`)))) having (trigcond(<is_not_null_test>(`test`.`t2`.`one`)) and trigcond(<is_not_null_test>(`test`.`t2`.`two`))))) AS `test` from `test`.`t1`
explain extended SELECT one,two from t1 where ROW(one,two) IN (SELECT one,two FROM t2 WHERE flag = 'N');
id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY t1 ALL NULL NULL NULL NULL 8 100.00
@@ -2841,9 +2847,9 @@ Note 1003 select `test`.`t1`.`one` AS `one`,`test`.`t1`.`two` AS `two` from `tes
explain extended SELECT one,two,ROW(one,two) IN (SELECT one,two FROM t2 WHERE flag = '0' group by one,two) as 'test' from t1;
id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY t1 ALL NULL NULL NULL NULL 8 100.00
-2 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 9 100.00 Using where; Using temporary; Using filesort
+2 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 9 100.00 Using where; Using temporary
Warnings:
-Note 1003 select `test`.`t1`.`one` AS `one`,`test`.`t1`.`two` AS `two`,<expr_cache><`test`.`t1`.`two`,`test`.`t1`.`one`>(<in_optimizer>((`test`.`t1`.`one`,`test`.`t1`.`two`),<exists>(select `test`.`t2`.`one`,`test`.`t2`.`two` from `test`.`t2` where (`test`.`t2`.`flag` = '0') group by `test`.`t2`.`one`,`test`.`t2`.`two` having (trigcond(((<cache>(`test`.`t1`.`one`) = `test`.`t2`.`one`) or isnull(`test`.`t2`.`one`))) and trigcond(((<cache>(`test`.`t1`.`two`) = `test`.`t2`.`two`) or isnull(`test`.`t2`.`two`))) and trigcond(<is_not_null_test>(`test`.`t2`.`one`)) and trigcond(<is_not_null_test>(`test`.`t2`.`two`)))))) AS `test` from `test`.`t1`
+Note 1003 select `test`.`t1`.`one` AS `one`,`test`.`t1`.`two` AS `two`,<in_optimizer>((`test`.`t1`.`one`,`test`.`t1`.`two`),<exists>(select `test`.`t2`.`one`,`test`.`t2`.`two` from `test`.`t2` where (`test`.`t2`.`flag` = '0') group by `test`.`t2`.`one`,`test`.`t2`.`two` having (trigcond(((<cache>(`test`.`t1`.`one`) = `test`.`t2`.`one`) or isnull(`test`.`t2`.`one`))) and trigcond(((<cache>(`test`.`t1`.`two`) = `test`.`t2`.`two`) or isnull(`test`.`t2`.`two`))) and trigcond(<is_not_null_test>(`test`.`t2`.`one`)) and trigcond(<is_not_null_test>(`test`.`t2`.`two`))))) AS `test` from `test`.`t1`
DROP TABLE t1,t2;
CREATE TABLE t1 (a char(5), b char(5));
INSERT INTO t1 VALUES (NULL,'aaa'), ('aaa','aaa');
@@ -2959,7 +2965,7 @@ 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 const PRIMARY PRIMARY 4 const 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
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'
@@ -2971,7 +2977,7 @@ 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 const PRIMARY PRIMARY 4 const 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
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'
@@ -3113,10 +3119,10 @@ SELECT a FROM t1
ORDER BY IFNULL((SELECT b FROM t2 WHERE b > 2),
(SELECT c FROM t2 WHERE c=a AND b > 2 ORDER BY b));
a
-2
-4
1
+2
3
+4
SELECT a FROM t1
ORDER BY IFNULL((SELECT b FROM t2 WHERE b > 1),
(SELECT c FROM t2 WHERE c=a AND b > 1 ORDER BY b));
@@ -3125,8 +3131,8 @@ SELECT a FROM t1
ORDER BY IFNULL((SELECT b FROM t2 WHERE b > 4),
(SELECT c FROM t2 WHERE c=a AND b > 2 ORDER BY b));
a
-2
1
+2
3
4
SELECT a FROM t1
@@ -3423,7 +3429,7 @@ EXPLAIN
SELECT * FROM t1 WHERE (a,b) = ANY (SELECT a, max(b) FROM t1 GROUP BY a);
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t1 ALL NULL NULL NULL NULL 9 Using where
-2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 9 Using temporary; Using filesort
+2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 9 Using temporary
ALTER TABLE t1 ADD INDEX(a);
SELECT * FROM t1 WHERE (a,b) = ANY (SELECT a, max(b) FROM t1 GROUP BY a);
a b
@@ -3434,7 +3440,7 @@ EXPLAIN
SELECT * FROM t1 WHERE (a,b) = ANY (SELECT a, max(b) FROM t1 GROUP BY a);
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t1 ALL NULL NULL NULL NULL 9 Using where
-2 DEPENDENT SUBQUERY t1 index NULL a 8 NULL 1 Using filesort
+2 DEPENDENT SUBQUERY t1 index NULL a 8 NULL 1
DROP TABLE t1;
create table t1( f1 int,f2 int);
insert into t1 values (1,1),(2,2);
@@ -3584,9 +3590,9 @@ 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
+1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables
+2 SUBQUERY t11 system NULL NULL NULL NULL 0 const row not found
+3 UNION t12 system NULL NULL NULL NULL 0 const row not found
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));
@@ -4056,7 +4062,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 1 Using where; Using index
+1 PRIMARY t1 ref a a 5 const 0 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));
@@ -4120,8 +4126,6 @@ INSERT INTO `t1` VALUES ('asdf','2007-02-08 01:11:26');
INSERT INTO `t2` VALUES ('abcdefghijk');
INSERT INTO `t2` VALUES ('asdf');
SET session sort_buffer_size=8192;
-Warnings:
-Warning 1292 Truncated incorrect sort_buffer_size value: '8192'
SELECT (SELECT 1 FROM t1 WHERE t1.a=t2.a ORDER BY t1.b LIMIT 1) AS d1 FROM t2;
d1
1
@@ -4229,8 +4233,8 @@ CREATE INDEX I1 ON t1 (a);
CREATE INDEX I2 ON t1 (b);
EXPLAIN SELECT a,b FROM t1 WHERE b IN (SELECT a FROM t1);
id select_type table type possible_keys key key_len ref rows Extra
-1 PRIMARY t1 index I1 I1 2 NULL 2 Using index; LooseScan
-1 PRIMARY t1 ref I2 I2 13 test.t1.a 2 Using index condition
+1 PRIMARY t1 ALL I2 NULL NULL NULL 2 Using where
+1 PRIMARY t1 ref I1 I1 2 test.t1.b 2 Using where; Using index; FirstMatch(t1)
SELECT a,b FROM t1 WHERE b IN (SELECT a FROM t1);
a b
CREATE TABLE t2 (a VARCHAR(1), b VARCHAR(10));
@@ -4239,15 +4243,15 @@ CREATE INDEX I1 ON t2 (a);
CREATE INDEX I2 ON t2 (b);
EXPLAIN SELECT a,b FROM t2 WHERE b IN (SELECT a FROM t2);
id select_type table type possible_keys key key_len ref rows Extra
-1 PRIMARY t2 index I1 I1 4 NULL 2 Using index; LooseScan
-1 PRIMARY t2 ref I2 I2 13 test.t2.a 2 Using index condition
+1 PRIMARY t2 ALL I2 NULL NULL NULL 2 Using where
+1 PRIMARY t2 ref I1 I1 4 test.t2.b 2 Using where; Using index; FirstMatch(t2)
SELECT a,b FROM t2 WHERE b IN (SELECT a FROM t2);
a b
EXPLAIN
SELECT a,b FROM t1 WHERE b IN (SELECT a FROM t1 WHERE LENGTH(a)<500);
id select_type table type possible_keys key key_len ref rows Extra
-1 PRIMARY t1 index I1 I1 2 NULL 2 Using where; Using index; LooseScan
-1 PRIMARY t1 ref I2 I2 13 test.t1.a 2 Using index condition
+1 PRIMARY t1 ALL I2 NULL NULL NULL 2 Using where
+1 PRIMARY t1 ref I1 I1 2 test.t1.b 2 Using where; Using index; FirstMatch(t1)
SELECT a,b FROM t1 WHERE b IN (SELECT a FROM t1 WHERE LENGTH(a)<500);
a b
DROP TABLE t1,t2;
@@ -4291,7 +4295,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra
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`)))
+Note 1003 select 2 AS `2` from `test`.`t1` where 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));
@@ -4303,7 +4307,7 @@ 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`))))
+Note 1003 select 2 AS `2` from `test`.`t1` where 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);
@@ -4379,15 +4383,15 @@ INSERT INTO t1 VALUES (1),(2);
EXPLAIN EXTENDED SELECT 1 FROM t1 WHERE 1 IN (SELECT 1 FROM t1 GROUP BY 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
-2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 2 100.00 Using temporary; Using filesort
+2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 2 100.00 Using temporary
Warnings:
-Note 1003 select 1 AS `1` from `test`.`t1` where <expr_cache><1>(<in_optimizer>(1,<exists>(select 1 from `test`.`t1` group by `test`.`t1`.`a` having 1)))
+Note 1003 select 1 AS `1` from `test`.`t1` where <in_optimizer>(1,<exists>(select 1 from `test`.`t1` group by `test`.`t1`.`a` having (1 = <ref_null_helper>(1))))
EXPLAIN EXTENDED SELECT 1 FROM t1 WHERE 1 IN (SELECT 1 FROM t1 WHERE a > 3 GROUP BY a);
id select_type table type possible_keys key key_len ref rows filtered Extra
-1 PRIMARY NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables
-2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 2 100.00 Using where; Using temporary; Using filesort
+1 PRIMARY t1 ALL NULL NULL NULL NULL 2 100.00
+2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 2 100.00 Using where; Using temporary
Warnings:
-Note 1003 select 1 AS `1` from `test`.`t1` where <in_optimizer>(1,<exists>(select 1 from `test`.`t1` where (`test`.`t1`.`a` > 3) group by `test`.`t1`.`a` having 1))
+Note 1003 select 1 AS `1` from `test`.`t1` where <in_optimizer>(1,<exists>(select 1 from `test`.`t1` where (`test`.`t1`.`a` > 3) group by `test`.`t1`.`a` having (1 = <ref_null_helper>(1))))
DROP TABLE t1;
#
# Bug#45061: Incorrectly market field caused wrong result.
@@ -5124,7 +5128,6 @@ SELECT 1 FROM t1 GROUP BY
1
1
DROP TABLE t1;
-set @@optimizer_switch=@save_optimizer_switch;
#
# Bug #49512 : subquery with aggregate function crash
# subselect_single_select_engine::exec()
@@ -5308,6 +5311,36 @@ 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);
+CREATE TABLE t2 (c int);
+insert into t2 values (1),(2);
+prepare stmt1 from "select sum(a),(select sum(c) from t2 where table1.b) as sub
+from t1 as table1 group by sub";
+execute stmt1;
+sum(a) sub
+0 NULL
+1 3
+deallocate prepare stmt1;
+prepare stmt1 from "select sum(a),(select sum(c) from t2 having table1.b) as sub
+from t1 as table1";
+execute stmt1;
+sum(a) sub
+1 3
+deallocate prepare stmt1;
+drop table t1,t2;
+#
+# Bug LP#693935/#58727: Assertion failure with
+# a single row subquery returning more than one row
+#
+create table t1 (a char(1) charset utf8);
+insert into t1 values ('a'), ('b');
+create table t2 (a binary(1));
+insert into t2 values ('x'), ('y');
+select * from t2 where a=(select a from t1) and a='x';
+ERROR 21000: Subquery returns more than 1 row
+drop table t1,t2;
End of 5.1 tests
#
# Bug #11765713 58705:
@@ -5356,7 +5389,288 @@ k
3
drop table t1,t2,t3;
drop view v2;
+#
+# Bug#52068: Optimizer generates invalid semijoin materialization plan
+#
+drop table if exists ot1, ot2, it1, it2;
+CREATE TABLE ot1(a INTEGER);
+INSERT INTO ot1 VALUES(5), (8);
+CREATE TABLE it2(a INTEGER);
+INSERT INTO it2 VALUES(9), (5), (1), (8);
+CREATE TABLE it3(a INTEGER);
+INSERT INTO it3 VALUES(7), (1), (0), (5), (1), (4);
+CREATE TABLE ot4(a INTEGER);
+INSERT INTO ot4 VALUES(1), (3), (5), (7), (9), (7), (3), (1);
+SELECT * FROM ot1,ot4
+WHERE (ot1.a,ot4.a) IN (SELECT it2.a,it3.a
+FROM it2,it3);
+a a
+5 1
+8 1
+5 5
+8 5
+5 7
+8 7
+5 7
+8 7
+5 1
+8 1
+explain SELECT * FROM ot1,ot4
+WHERE (ot1.a,ot4.a) IN (SELECT it2.a,it3.a
+FROM it2,it3);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY ot1 ALL NULL NULL NULL NULL 2 Start temporary
+1 PRIMARY it2 ALL NULL NULL NULL NULL 4 Using where; Using join buffer (flat, BNL join)
+1 PRIMARY it3 ALL NULL NULL NULL NULL 6 Using join buffer (flat, BNL join)
+1 PRIMARY ot4 ALL NULL NULL NULL NULL 8 Using where; End temporary; Using join buffer (flat, BNL join)
+DROP TABLE IF EXISTS ot1, ot4, it2, it3;
+#
+# Bug#729039: NULL keys used to evaluate subquery
+#
+CREATE TABLE t1 (a int) ;
+INSERT INTO t1 VALUES (NULL), (1), (NULL), (2);
+CREATE TABLE t2 (a int, INDEX idx(a)) ;
+INSERT INTO t2 VALUES (NULL), (1), (NULL);
+SELECT * FROM t1
+WHERE EXISTS (SELECT a FROM t2 USE INDEX () WHERE t2.a = t1.a);
+a
+1
+EXPLAIN
+SELECT * FROM t1
+WHERE EXISTS (SELECT a FROM t2 USE INDEX() WHERE t2.a = t1.a);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY t1 ALL NULL NULL NULL NULL 4 Using where
+2 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 3 Using where
+SELECT * FROM t1
+WHERE EXISTS (SELECT a FROM t2 WHERE t2.a = t1.a);
+a
+1
+EXPLAIN
+SELECT * FROM t1
+WHERE EXISTS (SELECT a FROM t2 WHERE t2.a = t1.a);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY t1 ALL NULL NULL NULL NULL 4 Using where
+2 DEPENDENT SUBQUERY t2 ref idx idx 5 test.t1.a 2 Using index
+DROP TABLE t1,t2;
+#
+# BUG#752992: Wrong results for a subquery with 'semijoin=on'
+#
+CREATE TABLE t1 (pk INTEGER PRIMARY KEY, i INTEGER NOT NULL);
+INSERT INTO t1 VALUES (11,0);
+INSERT INTO t1 VALUES (12,5);
+INSERT INTO t1 VALUES (15,0);
+CREATE TABLE t2 (pk INTEGER PRIMARY KEY, i INTEGER NOT NULL);
+INSERT INTO t2 VALUES (11,1);
+INSERT INTO t2 VALUES (12,2);
+INSERT INTO t2 VALUES (15,4);
+EXPLAIN SELECT * FROM t1 WHERE pk IN (SELECT it.pk FROM t2 JOIN t2 AS it ON 1);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY t1 ALL PRIMARY NULL NULL NULL 3 Start temporary
+1 PRIMARY t2 index NULL PRIMARY 4 NULL 3 Using index; Using join buffer (flat, BNL join)
+1 PRIMARY it eq_ref PRIMARY PRIMARY 4 test.t1.pk 1 Using index; End temporary
+SELECT * FROM t1 WHERE pk IN (SELECT it.pk FROM t2 JOIN t2 AS it ON 1);
+pk i
+11 0
+12 5
+15 0
+DROP table t1,t2;
+#
+# Bug#751350: crash with pushed condition for outer references when
+# there should be none of such conditions
+#
+CREATE TABLE t1 (a int, b int) ;
+INSERT INTO t1 VALUES (0,0),(0,0);
+EXPLAIN
+SELECT b FROM t1
+WHERE ('0') IN ( SELECT a FROM t1 GROUP BY a )
+GROUP BY b;
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY t1 ALL NULL NULL NULL NULL 2 Using temporary; Using filesort
+2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 2 Using temporary
+SELECT b FROM t1
+WHERE ('0') IN ( SELECT a FROM t1 GROUP BY a )
+GROUP BY b;
+b
+0
+DROP TABLE t1;
+#
+# 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#779885: Crash in eliminate_item_equal with materialization=on in
+# maria-5.3
+#
+CREATE TABLE t1 ( f1 int );
+INSERT INTO t1 VALUES (19), (20);
+CREATE TABLE t2 ( f10 varchar(32) );
+INSERT INTO t2 VALUES ('c'),('d');
+CREATE TABLE t3 ( f10 varchar(32) );
+INSERT INTO t3 VALUES ('a'),('b');
+SELECT *
+FROM t1
+WHERE
+( 't' ) IN (
+SELECT t3.f10
+FROM t3
+JOIN t2
+ON t2.f10 = t3.f10
+);
+f1
+DROP TABLE t1,t2,t3;
+#
+# Fix of LP BUG#780386 (NULL left part with empty ALL subquery).
+#
+CREATE TABLE t1 ( f11 int) ;
+INSERT IGNORE INTO t1 VALUES (0),(0);
+CREATE TABLE t2 ( f3 int, f10 int, KEY (f10,f3)) ;
+INSERT IGNORE INTO t2 VALUES (NULL,NULL),(5,0);
+DROP TABLE IF EXISTS t3;
+Warnings:
+Note 1051 Unknown table 't3'
+CREATE TABLE t3 ( f3 int) ;
+INSERT INTO t3 VALUES (0),(0);
+SELECT a1.f3 AS r FROM t2 AS a1 , t1 WHERE a1.f3 < ALL ( SELECT f3 FROM t3 WHERE f3 = 1 ) ;
+r
+NULL
+5
+NULL
+5
+DROP TABLE t1, t2, t3;
+End of 5.3 tests
+End of 5.5 tests.
+#
+# Bug#12763207 - ASSERT IN SUBSELECT::SINGLE_VALUE_TRANSFORMER
+#
+CREATE TABLE t1(a1 int);
+INSERT INTO t1 VALUES (1),(2);
+CREATE TABLE t2(a1 int);
+INSERT INTO t2 VALUES (3);
+SELECT @@session.sql_mode INTO @old_sql_mode;
+SET SESSION sql_mode='ONLY_FULL_GROUP_BY';
+SELECT 1 FROM t1 WHERE 1 < SOME (SELECT 2 FROM t2);
+1
+1
+1
+SELECT 1 FROM t1 WHERE 1 < SOME (SELECT 2.0 FROM t2);
+1
+1
+1
+SELECT 1 FROM t1 WHERE 1 < SOME (SELECT 'a' FROM t2);
+1
+SELECT 1 FROM t1 WHERE 1 < SOME (SELECT a1 FROM t2);
+1
+1
+1
+SET SESSION sql_mode=@old_sql_mode;
+DROP TABLE t1, t2;
+#
+# 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);
+
+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
+
+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 11765699 - 58690: !TABLE || (!TABLE->READ_SET ||
+# BITMAP_IS_SET(TABLE->READ_SET, FIELD_INDEX
+#
+CREATE TABLE t1(a INT);
+INSERT INTO t1 VALUES (0), (1);
+CREATE TABLE t2(
+b TEXT,
+c INT,
+PRIMARY KEY (b(1))
+);
+INSERT INTO t2 VALUES ('a', 2), ('b', 3);
+SELECT 1 FROM t1 WHERE a =
+(SELECT 1 FROM t2 WHERE b =
+(SELECT 1 FROM t1 t11 WHERE c = 1 OR t1.a = 1 AND 1 = 2)
+ORDER BY b
+);
+1
+SELECT 1 FROM t1 WHERE a =
+(SELECT 1 FROM t2 WHERE b =
+(SELECT 1 FROM t1 t11 WHERE c = 1 OR t1.a = 1 AND 1 = 2)
+GROUP BY b
+);
+1
+DROP TABLE t1, t2;
+#
+# 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 range f1_key f1_key 4 NULL 6 Range checked for each record (index map: 0x1); Using temporary
+DROP TABLE t1,t2;
+set optimizer_switch=@subselect_tmp;
set optimizer_switch=default;
select @@optimizer_switch like '%materialization=on%';
@@optimizer_switch like '%materialization=on%'
-1
+0
View Lorry Controller Status