summaryrefslogtreecommitdiff
path: root/mysql-test/r/subselect_no_exists_to_in.result
diff options
context:
space:
mode:
Diffstat (limited to 'mysql-test/r/subselect_no_exists_to_in.result')
-rw-r--r--mysql-test/r/subselect_no_exists_to_in.result92
1 files changed, 46 insertions, 46 deletions
diff --git a/mysql-test/r/subselect_no_exists_to_in.result b/mysql-test/r/subselect_no_exists_to_in.result
index 303303f4619..df9f1872a8c 100644
--- a/mysql-test/r/subselect_no_exists_to_in.result
+++ b/mysql-test/r/subselect_no_exists_to_in.result
@@ -60,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 ((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
@@ -201,7 +201,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra
4 SUBQUERY t2 ALL NULL NULL NULL NULL 2 100.00
NULL UNION RESULT <union1,3> ALL NULL NULL NULL NULL NULL NULL
Warnings:
-Note 1003 (select `test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b` from `test`.`t2` where (`test`.`t2`.`b` = (select `test`.`t3`.`a` from `test`.`t3` order by 1 desc limit 1))) union (select `test`.`t4`.`a` AS `a`,`test`.`t4`.`b` AS `b` from `test`.`t4` where (`test`.`t4`.`b` = (select (max(`test`.`t2`.`a`) * 4) from `test`.`t2`)))
+Note 1003 (select `test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b` from `test`.`t2` where `test`.`t2`.`b` = (select `test`.`t3`.`a` from `test`.`t3` order by 1 desc limit 1)) union (select `test`.`t4`.`a` AS `a`,`test`.`t4`.`b` AS `b` from `test`.`t4` where `test`.`t4`.`b` = (select max(`test`.`t2`.`a`) * 4 from `test`.`t2`))
select (select a from t3 where a<t2.a*4 order by 1 desc limit 1), a from t2;
(select a from t3 where a<t2.a*4 order by 1 desc limit 1) a
3 1
@@ -219,7 +219,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra
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)`,`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`
+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`
set optimizer_switch=@tmp_optimizer_switch;
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
@@ -241,7 +241,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra
3 DEPENDENT SUBQUERY t3 ALL NULL NULL NULL NULL 3 100.00 Using where
Warnings:
Note 1276 Field or reference 'test.t4.a' of SELECT #3 was resolved in SELECT #1
-Note 1003 select `test`.`t4`.`b` AS `b`,<expr_cache><`test`.`t4`.`a`>((select avg((`test`.`t2`.`a` + (select min(`test`.`t3`.`a`) from `test`.`t3` where (`test`.`t3`.`a` >= `test`.`t4`.`a`)))) from `test`.`t2`)) AS `(select avg(t2.a+(select min(t3.a) from t3 where t3.a >= t4.a)) from t2)` from `test`.`t4`
+Note 1003 select `test`.`t4`.`b` AS `b`,<expr_cache><`test`.`t4`.`a`>((select avg(`test`.`t2`.`a` + (select min(`test`.`t3`.`a`) from `test`.`t3` where `test`.`t3`.`a` >= `test`.`t4`.`a`)) from `test`.`t2`)) AS `(select avg(t2.a+(select min(t3.a) from t3 where t3.a >= t4.a)) from t2)` from `test`.`t4`
select * from t3 where exists (select * from t2 where t2.b=t3.a);
a
7
@@ -287,7 +287,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>(<in_optimizer>(`test`.`t3`.`a`,((select min(`test`.`t2`.`b`) from `test`.`t2`) <= <cache>(`test`.`t3`.`a`))))
+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`) <= <cache>(`test`.`t3`.`a`)))
select * from t3 where a >= all (select b from t2);
a
7
@@ -331,7 +331,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 <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));
@@ -349,7 +349,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 <expr_cache><`test`.`t6`.`clinic_uq`>(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;
@@ -410,13 +410,13 @@ EXPLAIN EXTENDED SELECT DISTINCT date FROM t1 WHERE date='2002-08-03';
id select_type table type possible_keys key key_len ref rows filtered Extra
1 SIMPLE t1 index NULL PRIMARY 43 NULL 2 100.00 Using where; Using index
Warnings:
-Note 1003 select distinct `test`.`t1`.`date` AS `date` from `test`.`t1` where (`test`.`t1`.`date` = DATE'2002-08-03')
+Note 1003 select distinct `test`.`t1`.`date` AS `date` from `test`.`t1` where `test`.`t1`.`date` = DATE'2002-08-03'
EXPLAIN EXTENDED SELECT (SELECT DISTINCT date FROM t1 WHERE date='2002-08-03');
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 t1 index NULL PRIMARY 43 NULL 2 100.00 Using where; Using index
Warnings:
-Note 1003 select (select distinct `test`.`t1`.`date` from `test`.`t1` where (`test`.`t1`.`date` = DATE'2002-08-03')) AS `(SELECT DISTINCT date FROM t1 WHERE date='2002-08-03')`
+Note 1003 select (select distinct `test`.`t1`.`date` from `test`.`t1` where `test`.`t1`.`date` = DATE'2002-08-03') AS `(SELECT DISTINCT date FROM t1 WHERE date='2002-08-03')`
SELECT DISTINCT date FROM t1 WHERE date='2002-08-03';
date
2002-08-03
@@ -563,7 +563,7 @@ 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 `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 index
@@ -745,7 +745,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra
1 SIMPLE t2 ref id id 5 const 1 100.00 Using index
Warnings:
Note 1249 Select 2 was reduced during optimization
-Note 1003 select `test`.`t2`.`id` AS `id` from `test`.`t2` where (`test`.`t2`.`id` = 1)
+Note 1003 select `test`.`t2`.`id` AS `id` from `test`.`t2` where `test`.`t2`.`id` = 1
SELECT * FROM t2 WHERE id IN (SELECT 1 UNION SELECT 3);
id
1
@@ -758,7 +758,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra
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` = <cache>((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
@@ -766,7 +766,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 <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))))
SELECT * FROM t2 WHERE id IN (SELECT 5 UNION SELECT 3);
id
SELECT * FROM t2 WHERE id IN (SELECT 5 UNION SELECT 2);
@@ -892,7 +892,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra
Warnings:
Note 1276 Field or reference 'test.t1.a' of SELECT #2 was resolved in SELECT #1
Note 1249 Select 2 was reduced during optimization
-Note 1003 select (`test`.`t1`.`a` + 1) AS `(select a+1)` from `test`.`t1`
+Note 1003 select `test`.`t1`.`a` + 1 AS `(select a+1)` from `test`.`t1`
select (select a+1) from t1;
(select a+1)
2.5
@@ -914,7 +914,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 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`,<expr_cache><`test`.`t1`.`a`>(<in_optimizer>(`test`.`t1`.`a`,<exists>(<index_lookup>(<cache>(`test`.`t1`.`a`) in t2 on a checking NULL having `test`.`t2`.`a` is null)))) 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;
@@ -929,7 +929,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra
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 `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`
+Note 1003 select `test`.`t1`.`a` AS `a`,<expr_cache><`test`.`t1`.`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 `test`.`t2`.`a` is null) having `test`.`t2`.`a` is null))) AS `t1.a in (select t2.a from t2,t3 where t3.a=t2.a)` from `test`.`t1`
drop table t1,t2,t3;
# check correct NULL Processing for normal IN/ALL/ANY
# and 2 ways of max/min optimization
@@ -1329,7 +1329,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 dual 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)
@@ -1339,7 +1339,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 `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',
@@ -1384,7 +1384,7 @@ 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 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`))
+Note 1003 select `test`.`t1`.`id` AS `id` from `test`.`t1` where `test`.`t1`.`salary` = (select max(`test`.`t1`.`salary`) from `test`.`t1`)
drop table t1;
CREATE TABLE t1 (
ID int(10) unsigned NOT NULL auto_increment,
@@ -1446,7 +1446,7 @@ 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 (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`)
+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);
a
2
@@ -1456,7 +1456,7 @@ 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 (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))
+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);
a
2
@@ -1467,7 +1467,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra
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`.`t3`.`a` = `test`.`t1`.`b`) and (`test`.`t1`.`a` = `test`.`t2`.`a`))
+Note 1003 select `test`.`t2`.`a` AS `a` from `test`.`t1` join `test`.`t3` join `test`.`t2` where `test`.`t3`.`a` = `test`.`t1`.`b` and `test`.`t1`.`a` = `test`.`t2`.`a`
drop table t1, t2, t3;
create table t1 (a int, b int, index a (a,b));
create table t2 (a int, index a (a));
@@ -1489,7 +1489,7 @@ 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 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`)
+Note 1003 select `test`.`t2`.`a` AS `a` from `test`.`t2` semi join (`test`.`t1`) where `test`.`t1`.`a` = `test`.`t2`.`a`
select * from t2 where t2.a in (select a from t1 where t1.b <> 30);
a
2
@@ -1499,7 +1499,7 @@ 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 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))
+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
select * from t2 where t2.a in (select t1.a from t1,t3 where t1.b=t3.a);
a
2
@@ -1510,7 +1510,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra
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`.`b` = `test`.`t3`.`a`) and (`test`.`t1`.`a` = `test`.`t2`.`a`))
+Note 1003 select `test`.`t2`.`a` AS `a` from `test`.`t2` semi join (`test`.`t1` join `test`.`t3`) where `test`.`t1`.`b` = `test`.`t3`.`a` and `test`.`t1`.`a` = `test`.`t2`.`a`
insert into t1 values (3,31);
select * from t2 where t2.a in (select a from t1 where t1.b <> 30);
a
@@ -1526,7 +1526,7 @@ 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 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))
+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
drop table t0, t1, t2, t3;
create table t1 (a int, b int);
create table t2 (a int, b int);
@@ -1617,25 +1617,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 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`,!<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(`test`.`t2`.`s1` is null))))) 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 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`,<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(`test`.`t2`.`s1` is null))))) 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 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`,!<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(`test`.`t2`.`s1` is null))))) 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 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`,!<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(`test`.`t2`.`s1` is null))))) 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 not null);
create table t3 (a int);
@@ -1650,7 +1650,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>(<in_optimizer>(`test`.`t3`.`a`,((select max(NULL) from `test`.`t2`) > <cache>(`test`.`t3`.`a`))))
+Note 1003 select `test`.`t3`.`a` AS `a` from `test`.`t3` where <not>(<in_optimizer>(`test`.`t3`.`a`,(select max(NULL) from `test`.`t2`) > <cache>(`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);
@@ -1658,7 +1658,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>(<in_optimizer>(`test`.`t3`.`a`,((select min(NULL) from `test`.`t2`) <= <cache>(`test`.`t3`.`a`))))
+Note 1003 select `test`.`t3`.`a` AS `a` from `test`.`t3` where <nop>(<in_optimizer>(`test`.`t3`.`a`,(select min(NULL) from `test`.`t2`) <= <cache>(`test`.`t3`.`a`)))
select * from t3 where a >= all (select b from t2 group by 1);
a
6
@@ -1669,7 +1669,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>(<in_optimizer>(`test`.`t3`.`a`,((select max(NULL) from `test`.`t2`) > <cache>(`test`.`t3`.`a`))))
+Note 1003 select `test`.`t3`.`a` AS `a` from `test`.`t3` where <not>(<in_optimizer>(`test`.`t3`.`a`,(select max(NULL) from `test`.`t2`) > <cache>(`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);
@@ -1677,7 +1677,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>(<in_optimizer>(`test`.`t3`.`a`,((select min(NULL) from `test`.`t2`) <= <cache>(`test`.`t3`.`a`))))
+Note 1003 select `test`.`t3`.`a` AS `a` from `test`.`t3` where <nop>(<in_optimizer>(`test`.`t3`.`a`,(select min(NULL) from `test`.`t2`) <= <cache>(`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);
@@ -1720,7 +1720,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 4 100.00 Using temporary
Warnings:
-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`) >= <cache>(`test`.`t3`.`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`) >= <cache>(`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());
@@ -1890,14 +1890,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 !<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`))))
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 !<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))
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');
@@ -1923,7 +1923,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra
1 SIMPLE b eq_ref PRIMARY PRIMARY 4 test.a.id 2 100.00
1 SIMPLE c eq_ref PRIMARY PRIMARY 4 func 1 100.00 Using index condition
Warnings:
-Note 1003 select `test`.`a`.`id` AS `id`,`test`.`a`.`text` AS `text`,`test`.`b`.`id` AS `id`,`test`.`b`.`text` AS `text`,`test`.`c`.`id` AS `id`,`test`.`c`.`text` AS `text` from `test`.`t1` `a` left join `test`.`t2` `b` on(((`test`.`b`.`id` = `test`.`a`.`id`) or isnull(`test`.`b`.`id`))) join `test`.`t1` `c` where (if(isnull(`test`.`b`.`id`),1000,`test`.`b`.`id`) = `test`.`c`.`id`)
+Note 1003 select `test`.`a`.`id` AS `id`,`test`.`a`.`text` AS `text`,`test`.`b`.`id` AS `id`,`test`.`b`.`text` AS `text`,`test`.`c`.`id` AS `id`,`test`.`c`.`text` AS `text` from `test`.`t1` `a` left join `test`.`t2` `b` on(`test`.`b`.`id` = `test`.`a`.`id` or `test`.`b`.`id` is null) join `test`.`t1` `c` where if(`test`.`b`.`id` is null,1000,`test`.`b`.`id`) = `test`.`c`.`id`
drop table t1,t2;
create table t1 (a int);
insert into t1 values (1);
@@ -2433,7 +2433,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 <expr_cache><`test`.`up`.`a`>(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);
@@ -2980,20 +2980,20 @@ 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`.`one`,`test`.`t1`.`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(trigcond(((<cache>(`test`.`t1`.`one`) = `test`.`t2`.`one`) or isnull(`test`.`t2`.`one`)))) and trigcond(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`,<expr_cache><`test`.`t1`.`one`,`test`.`t1`.`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(trigcond(<cache>(`test`.`t1`.`one`) = `test`.`t2`.`one` or `test`.`t2`.`one` is null)) and trigcond(trigcond(<cache>(`test`.`t1`.`two`) = `test`.`t2`.`two` or `test`.`t2`.`two` is null)) having trigcond(`test`.`t2`.`one` is null) and trigcond(`test`.`t2`.`two` is null)))) 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
1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 8 func,func 1 100.00
2 MATERIALIZED 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` from `test`.`t1` semi join (`test`.`t2`) where (`test`.`t2`.`flag` = 'N')
+Note 1003 select `test`.`t1`.`one` AS `one`,`test`.`t1`.`two` AS `two` from `test`.`t1` semi join (`test`.`t2`) where `test`.`t2`.`flag` = 'N'
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
Warnings:
-Note 1003 select `test`.`t1`.`one` AS `one`,`test`.`t1`.`two` AS `two`,<expr_cache><`test`.`t1`.`one`,`test`.`t1`.`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(trigcond(((<cache>(`test`.`t1`.`one`) = `test`.`t2`.`one`) or isnull(`test`.`t2`.`one`)))) and trigcond(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`,<expr_cache><`test`.`t1`.`one`,`test`.`t1`.`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(trigcond(<cache>(`test`.`t1`.`one`) = `test`.`t2`.`one` or `test`.`t2`.`one` is null)) and trigcond(trigcond(<cache>(`test`.`t1`.`two`) = `test`.`t2`.`two` or `test`.`t2`.`two` is null)) having trigcond(`test`.`t2`.`one` is null) and trigcond(`test`.`t2`.`two` is null)))) AS `test` from `test`.`t1`
DROP TABLE t1,t2;
set optimizer_switch=@tmp11867_optimizer_switch;
CREATE TABLE t1 (a char(5), b char(5));
@@ -4446,7 +4446,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 <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));
@@ -4458,7 +4458,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 <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);
@@ -4539,14 +4539,14 @@ 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 MATERIALIZED t1 ALL NULL NULL NULL NULL 2 100.00 Using temporary
Warnings:
-Note 1003 select 1 AS `1` from <materialize> (select min(`test`.`t1`.`a`) from `test`.`t1` group by `test`.`t1`.`a`) join `test`.`t1` where (`<subquery2>`.`min(a)` = 1)
+Note 1003 select 1 AS `1` from <materialize> (select min(`test`.`t1`.`a`) from `test`.`t1` group by `test`.`t1`.`a`) join `test`.`t1` where `<subquery2>`.`min(a)` = 1
EXPLAIN EXTENDED SELECT 1 FROM t1 WHERE 1 IN (SELECT min(a) FROM t1 WHERE a > 3 GROUP BY a);
id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY <subquery2> const distinct_key distinct_key 4 const 1 100.00
1 PRIMARY t1 ALL NULL NULL NULL NULL 2 100.00
2 MATERIALIZED t1 ALL NULL NULL NULL NULL 2 100.00 Using where; Using temporary
Warnings:
-Note 1003 select 1 AS `1` from <materialize> (select min(`test`.`t1`.`a`) from `test`.`t1` where (`test`.`t1`.`a` > 3) group by `test`.`t1`.`a`) join `test`.`t1` where (`<subquery2>`.`min(a)` = 1)
+Note 1003 select 1 AS `1` from <materialize> (select min(`test`.`t1`.`a`) from `test`.`t1` where `test`.`t1`.`a` > 3 group by `test`.`t1`.`a`) join `test`.`t1` where `<subquery2>`.`min(a)` = 1
SET join_cache_level=@save_join_cache_level;
DROP TABLE t1;
#
View Lorry Controller Status