At file:///home/psergey/dev/maria-5.3-subqueries-r7/
------------------------------------------------------------
revno: 2760
revision-id: psergey@askmonty.org-20100217104755-3psvc5fmo3pqsnpy
parent: psergey@askmonty.org-20100217100527-k81b7torhmj99moy
committer: Sergey Petrunya
branch nick: maria-5.3-subqueries-r7
timestamp: Wed 2010-02-17 13:47:55 +0300
message:
Subquery backport:
- More test results updates (checked)
=== modified file 'mysql-test/r/subselect3_jcl6.result'
--- a/mysql-test/r/subselect3_jcl6.result 2010-02-11 21:56:02 +0000
+++ b/mysql-test/r/subselect3_jcl6.result 2010-02-17 10:47:55 +0000
@@ -877,7 +877,7 @@
Note 1276 Field or reference 'test.t1.a' of SELECT #3 was resolved in SELECT #2
Note 1276 Field or reference 'test.t1.c' of SELECT #3 was resolved in SELECT #2
Error 1054 Unknown column 'c' in 'field list'
-Note 1003 select `c` AS `c` from (select (select count(`test`.`t1`.`a`) AS `COUNT(a)` from dual group by `c`) AS `(SELECT COUNT(a) FROM
+Note 1003 select `c` AS `c` from (select (select count(`test`.`t1`.`a`) AS `COUNT(a)` from (select count(`test`.`t1`.`b`) AS `COUNT(b)` from `test`.`t1`) `x` group by `t1`.`c`) AS `(SELECT COUNT(a) FROM
(SELECT COUNT(b) FROM t1) AS x GROUP BY c
)` from `test`.`t1` group by `test`.`t1`.`b`) `y`
DROP TABLE t1;
@@ -1122,7 +1122,7 @@
explain select * from t3 where a in (select t1.kp1 from t1,t4 where kp1<20
and t4.pk=t1.c);
id select_type table type possible_keys key key_len ref rows Extra
-1 PRIMARY t1 range kp1 kp1 5 NULL 48 Using index condition; Using where; Using MRR; LooseScan
+1 PRIMARY t1 range kp1 kp1 5 NULL 48 Using index condition; Using MRR; LooseScan
1 PRIMARY t4 eq_ref PRIMARY PRIMARY 4 test.t1.c 1 Using index; FirstMatch(t1)
1 PRIMARY t3 ALL NULL NULL NULL NULL 100 Using where; Using join buffer
drop table t1, t3, t4;
=== modified file 'mysql-test/r/subselect_no_semijoin.result'
--- a/mysql-test/r/subselect_no_semijoin.result 2010-01-17 20:52:20 +0000
+++ b/mysql-test/r/subselect_no_semijoin.result 2010-02-17 10:47:55 +0000
@@ -54,7 +54,7 @@
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' AS `a`) = 1)
+Note 1003 select 1 AS `1` from (select 1 AS `a`) `b` having ((select '1' AS `a`) = 1)
SELECT 1 FROM (SELECT 1 as a) as b HAVING (SELECT a)=1;
1
1
@@ -207,7 +207,7 @@
3 DERIVED t2 ALL NULL NULL NULL NULL 2 100.00 Using where
2 SUBQUERY t3 ALL NULL NULL NULL NULL 3 100.00 Using where; Using filesort
Warnings:
-Note 1003 select (select `test`.`t3`.`a` AS `a` from `test`.`t3` where (`test`.`t3`.`a` < 8) order by 1 desc limit 1) AS `(select t3.a from t3 where a<8 order by 1 desc limit 1)`,'2' AS `a` from dual
+Note 1003 select (select `test`.`t3`.`a` AS `a` from `test`.`t3` where (`test`.`t3`.`a` < 8) order by 1 desc limit 1) AS `(select t3.a from t3 where a<8 order by 1 desc limit 1)`,'2' AS `a` from (select `test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b` from `test`.`t2` where (`test`.`t2`.`a` > 1)) `tt`
select * from t1 where t1.a=(select t2.a from t2 where t2.b=(select max(a) from t3) order by 1 desc limit 1);
a
2
@@ -318,7 +318,7 @@
Warnings:
Note 1276 Field or reference 'test.t2.a' of SELECT #2 was resolved in SELECT #1
Note 1276 Field or reference 'test.t2.a' of SELECT #3 was resolved in SELECT #1
-Note 1003 select (select '2' AS `a` from dual where ('2' = `test`.`t2`.`a`) union select `test`.`t5`.`a` AS `a` from `test`.`t5` where (`test`.`t5`.`a` = `test`.`t2`.`a`)) AS `(select a from t1 where t1.a=t2.a union select a from t5 where t5.a=t2.a)`,`test`.`t2`.`a` AS `a` from `test`.`t2`
+Note 1003 select (select '2' AS `a` from `test`.`t1` where ('2' = `test`.`t2`.`a`) union select `test`.`t5`.`a` AS `a` from `test`.`t5` where (`test`.`t5`.`a` = `test`.`t2`.`a`)) AS `(select a from t1 where t1.a=t2.a union select a from t5 where t5.a=t2.a)`,`test`.`t2`.`a` AS `a` from `test`.`t2`
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));
@@ -739,7 +739,7 @@
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` = (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
@@ -1437,7 +1437,7 @@
id select_type table type possible_keys key key_len ref rows filtered Extra
1 SIMPLE t1 system NULL NULL NULL NULL 1 100.00
Warnings:
-Note 1003 (select 'tttt' AS `s1` from dual)
+Note 1003 (select 'tttt' AS `s1` from `test`.`t1`)
(select * from t1);
s1
tttt
@@ -1625,7 +1625,7 @@
3 UNION t1 system NULL NULL NULL NULL 1 100.00
NULL UNION RESULT 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 `test`.`t1` where 1
drop table t1;
CREATE TABLE t1 (number char(11) NOT NULL default '') ENGINE=MyISAM CHARSET=latin1;
INSERT INTO t1 VALUES ('69294728265'),('18621828126'),('89356874041'),('95895001874');
@@ -4686,7 +4686,7 @@
explain
SELECT t1.a, (SELECT 1 FROM t2 WHERE t2.b=t3.c AND t2.c=t1.a ORDER BY t2.d LIMIT 1) AS incorrect FROM t1, t3 WHERE t3.b=t1.a;
id select_type table type possible_keys key key_len ref rows Extra
-1 PRIMARY t3 index b,b_2 b 10 NULL 2 Using where; Using index
+1 PRIMARY t3 index b,b_2 b 10 NULL 2 Using index
1 PRIMARY t1 eq_ref PRIMARY PRIMARY 4 test.t3.b 1 Using index
2 DEPENDENT SUBQUERY t2 index b,b_2,c d 5 NULL 1 Using where
SELECT t1.a, (SELECT 1 FROM t2 WHERE t2.b=t3.c AND t2.c=t1.a ORDER BY t2.d LIMIT 1) AS incorrect FROM t1, t3 WHERE t3.b=t1.a;
=== modified file 'mysql-test/r/subselect_sj.result'
--- a/mysql-test/r/subselect_sj.result 2010-02-11 23:59:58 +0000
+++ b/mysql-test/r/subselect_sj.result 2010-02-17 10:47:55 +0000
@@ -12,7 +12,7 @@
Flattened because of dependency, t10=func(t1)
explain select * from t1 where a in (select pk from t10);
id select_type table type possible_keys key key_len ref rows Extra
-1 PRIMARY t1 ALL NULL NULL NULL NULL 3 Using where
+1 PRIMARY t1 ALL NULL NULL NULL NULL 3
1 PRIMARY t10 eq_ref PRIMARY PRIMARY 4 test.t1.a 1 Using index
select * from t1 where a in (select pk from t10);
a b
@@ -39,7 +39,7 @@
a b
explain select * from t1 where a in (select pk from t10) and b in (select pk from t10);
id select_type table type possible_keys key key_len ref rows Extra
-1 PRIMARY t1 ALL NULL NULL NULL NULL 3 Using where
+1 PRIMARY t1 ALL NULL NULL NULL NULL 3
1 PRIMARY t10 eq_ref PRIMARY PRIMARY 4 test.t1.a 1 Using index
1 PRIMARY t10 eq_ref PRIMARY PRIMARY 4 test.t1.b 1 Using index
select * from t1 where a in (select pk from t10) and b in (select pk from t10);
@@ -50,8 +50,8 @@
flattening a nested subquery
explain select * from t1 where a in (select pk from t10 where t10.a in (select pk from t12));
id select_type table type possible_keys key key_len ref rows Extra
-1 PRIMARY t1 ALL NULL NULL NULL NULL 3 Using where
-1 PRIMARY t10 eq_ref PRIMARY PRIMARY 4 test.t1.a 1 Using where
+1 PRIMARY t1 ALL NULL NULL NULL NULL 3
+1 PRIMARY t10 eq_ref PRIMARY PRIMARY 4 test.t1.a 1
1 PRIMARY t12 eq_ref PRIMARY PRIMARY 4 test.t10.a 1 Using index
select * from t1 where a in (select pk from t10 where t10.a in (select pk from t12));
a b
@@ -61,8 +61,8 @@
flattening subquery w/ several tables
explain extended select * from t1 where a in (select t10.pk from t10, t12 where t12.pk=t10.a);
id select_type table type possible_keys key key_len ref rows filtered Extra
-1 PRIMARY t1 ALL NULL NULL NULL NULL 3 100.00 Using where
-1 PRIMARY t10 eq_ref PRIMARY PRIMARY 4 test.t1.a 1 100.00 Using where
+1 PRIMARY t1 ALL NULL NULL NULL NULL 3 100.00
+1 PRIMARY t10 eq_ref PRIMARY PRIMARY 4 test.t1.a 1 100.00
1 PRIMARY t12 eq_ref PRIMARY PRIMARY 4 test.t10.a 1 100.00 Using index
Warnings:
Note 1003 select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t10` join `test`.`t12` join `test`.`t1` where ((`test`.`t10`.`pk` = `test`.`t1`.`a`) and (`test`.`t12`.`pk` = `test`.`t10`.`a`))
@@ -545,7 +545,7 @@
(SELECT t1.pk FROM t0 t1 JOIN t0 t2 ON t2.vkey = t1.vnokey);
id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY t0 ALL PRIMARY NULL NULL NULL 5 100.00
-1 PRIMARY t1 eq_ref PRIMARY PRIMARY 4 test.t0.pk 1 100.00 Using where
+1 PRIMARY t1 eq_ref PRIMARY PRIMARY 4 test.t0.pk 1 100.00
1 PRIMARY t2 ref vkey vkey 4 test.t1.vnokey 2 100.00 Using index; FirstMatch(t1)
Warnings:
Note 1003 select `test`.`t0`.`vkey` AS `vkey` from `test`.`t0` `t1` semi join (`test`.`t0` `t2`) join `test`.`t0` where ((`test`.`t2`.`vkey` = `test`.`t1`.`vnokey`) and (`test`.`t1`.`pk` = `test`.`t0`.`pk`))
=== modified file 'mysql-test/r/subselect_sj2.result'
--- a/mysql-test/r/subselect_sj2.result 2010-01-17 14:51:10 +0000
+++ b/mysql-test/r/subselect_sj2.result 2010-02-17 10:47:55 +0000
@@ -32,7 +32,7 @@
9 5
explain select * from t2 where b in (select a from t1);
id select_type table type possible_keys key key_len ref rows Extra
-1 PRIMARY t1 ALL NULL NULL NULL NULL 3 Using where; Materialize; Scan
+1 PRIMARY t1 ALL NULL NULL NULL NULL 3 Materialize; Scan
1 PRIMARY t2 ref b b 5 test.t1.a 2
select * from t2 where b in (select a from t1);
a b
@@ -73,7 +73,7 @@
from t0 A, t0 B where B.a <5;
explain select * from t3 where b in (select a from t0);
id select_type table type possible_keys key key_len ref rows Extra
-1 PRIMARY t0 ALL NULL NULL NULL NULL 10 Using where; Materialize; Scan
+1 PRIMARY t0 ALL NULL NULL NULL NULL 10 Materialize; Scan
1 PRIMARY t3 ref b b 5 test.t0.a 1
set @save_ecp= @@engine_condition_pushdown;
set engine_condition_pushdown=0;
@@ -417,7 +417,7 @@
where t0.a in ( select t1.a from t1,t2 where t2.a=t0.a and
t1.b=t2.b);
id select_type table type possible_keys key key_len ref rows filtered Extra
-1 PRIMARY t0 ALL NULL NULL NULL NULL 5 100.00 Using where
+1 PRIMARY t0 ALL NULL NULL NULL NULL 5 100.00
1 PRIMARY t1 ref a a 5 test.t0.a 1 100.00 Start temporary
1 PRIMARY t2 eq_ref PRIMARY PRIMARY 4 test.t0.a 1 100.00 Using where; End temporary
Warnings:
=== modified file 'mysql-test/r/subselect_sj2_jcl6.result'
--- a/mysql-test/r/subselect_sj2_jcl6.result 2010-01-17 14:51:10 +0000
+++ b/mysql-test/r/subselect_sj2_jcl6.result 2010-02-17 10:47:55 +0000
@@ -36,7 +36,7 @@
9 5
explain select * from t2 where b in (select a from t1);
id select_type table type possible_keys key key_len ref rows Extra
-1 PRIMARY t1 ALL NULL NULL NULL NULL 3 Using where; Materialize; Scan
+1 PRIMARY t1 ALL NULL NULL NULL NULL 3 Materialize; Scan
1 PRIMARY t2 ref b b 5 test.t1.a 2 Using join buffer
select * from t2 where b in (select a from t1);
a b
@@ -77,8 +77,8 @@
from t0 A, t0 B where B.a <5;
explain select * from t3 where b in (select a from t0);
id select_type table type possible_keys key key_len ref rows Extra
-1 PRIMARY t0 ALL NULL NULL NULL NULL 10 Using where; Materialize; Scan
-1 PRIMARY t3 ref b b 5 test.t0.a 1
+1 PRIMARY t0 ALL NULL NULL NULL NULL 10 Materialize; Scan
+1 PRIMARY t3 ref b b 5 test.t0.a 1 Using join buffer
set @save_ecp= @@engine_condition_pushdown;
set engine_condition_pushdown=0;
select * from t3 where b in (select A.a+B.a from t0 A, t0 B where B.a<5);
@@ -421,7 +421,7 @@
where t0.a in ( select t1.a from t1,t2 where t2.a=t0.a and
t1.b=t2.b);
id select_type table type possible_keys key key_len ref rows filtered Extra
-1 PRIMARY t0 ALL NULL NULL NULL NULL 5 100.00 Using where
+1 PRIMARY t0 ALL NULL NULL NULL NULL 5 100.00
1 PRIMARY t1 ref a a 5 test.t0.a 1 100.00 Start temporary; Using join buffer
1 PRIMARY t2 eq_ref PRIMARY PRIMARY 4 test.t0.a 1 100.00 Using where; End temporary; Using join buffer
Warnings:
=== modified file 'mysql-test/r/subselect_sj_jcl6.result'
--- a/mysql-test/r/subselect_sj_jcl6.result 2010-02-11 23:59:58 +0000
+++ b/mysql-test/r/subselect_sj_jcl6.result 2010-02-17 10:47:55 +0000
@@ -16,7 +16,7 @@
Flattened because of dependency, t10=func(t1)
explain select * from t1 where a in (select pk from t10);
id select_type table type possible_keys key key_len ref rows Extra
-1 PRIMARY t1 ALL NULL NULL NULL NULL 3 Using where
+1 PRIMARY t1 ALL NULL NULL NULL NULL 3
1 PRIMARY t10 eq_ref PRIMARY PRIMARY 4 test.t1.a 1 Using index
select * from t1 where a in (select pk from t10);
a b
@@ -43,7 +43,7 @@
a b
explain select * from t1 where a in (select pk from t10) and b in (select pk from t10);
id select_type table type possible_keys key key_len ref rows Extra
-1 PRIMARY t1 ALL NULL NULL NULL NULL 3 Using where
+1 PRIMARY t1 ALL NULL NULL NULL NULL 3
1 PRIMARY t10 eq_ref PRIMARY PRIMARY 4 test.t1.a 1 Using index
1 PRIMARY t10 eq_ref PRIMARY PRIMARY 4 test.t1.b 1 Using index
select * from t1 where a in (select pk from t10) and b in (select pk from t10);
@@ -54,8 +54,8 @@
flattening a nested subquery
explain select * from t1 where a in (select pk from t10 where t10.a in (select pk from t12));
id select_type table type possible_keys key key_len ref rows Extra
-1 PRIMARY t1 ALL NULL NULL NULL NULL 3 Using where
-1 PRIMARY t10 eq_ref PRIMARY PRIMARY 4 test.t1.a 1 Using where; Using join buffer
+1 PRIMARY t1 ALL NULL NULL NULL NULL 3
+1 PRIMARY t10 eq_ref PRIMARY PRIMARY 4 test.t1.a 1 Using join buffer
1 PRIMARY t12 eq_ref PRIMARY PRIMARY 4 test.t10.a 1 Using index
select * from t1 where a in (select pk from t10 where t10.a in (select pk from t12));
a b
@@ -65,8 +65,8 @@
flattening subquery w/ several tables
explain extended select * from t1 where a in (select t10.pk from t10, t12 where t12.pk=t10.a);
id select_type table type possible_keys key key_len ref rows filtered Extra
-1 PRIMARY t1 ALL NULL NULL NULL NULL 3 100.00 Using where
-1 PRIMARY t10 eq_ref PRIMARY PRIMARY 4 test.t1.a 1 100.00 Using where; Using join buffer
+1 PRIMARY t1 ALL NULL NULL NULL NULL 3 100.00
+1 PRIMARY t10 eq_ref PRIMARY PRIMARY 4 test.t1.a 1 100.00 Using join buffer
1 PRIMARY t12 eq_ref PRIMARY PRIMARY 4 test.t10.a 1 100.00 Using index
Warnings:
Note 1003 select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t10` join `test`.`t12` join `test`.`t1` where ((`test`.`t10`.`pk` = `test`.`t1`.`a`) and (`test`.`t12`.`pk` = `test`.`t10`.`a`))
@@ -549,7 +549,7 @@
(SELECT t1.pk FROM t0 t1 JOIN t0 t2 ON t2.vkey = t1.vnokey);
id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY t0 ALL PRIMARY NULL NULL NULL 5 100.00
-1 PRIMARY t1 eq_ref PRIMARY PRIMARY 4 test.t0.pk 1 100.00 Using where; Using join buffer
+1 PRIMARY t1 eq_ref PRIMARY PRIMARY 4 test.t0.pk 1 100.00 Using join buffer
1 PRIMARY t2 ref vkey vkey 4 test.t1.vnokey 2 100.00 Using index; FirstMatch(t1)
Warnings:
Note 1003 select `test`.`t0`.`vkey` AS `vkey` from `test`.`t0` `t1` semi join (`test`.`t0` `t2`) join `test`.`t0` where ((`test`.`t2`.`vkey` = `test`.`t1`.`vnokey`) and (`test`.`t1`.`pk` = `test`.`t0`.`pk`))
=== modified file 'mysql-test/r/view.result'
--- a/mysql-test/r/view.result 2009-12-15 07:16:46 +0000
+++ b/mysql-test/r/view.result 2010-02-17 10:47:55 +0000
@@ -2342,11 +2342,11 @@
EXPLAIN SELECT t1.* FROM t1 JOIN t2 WHERE t1.a=t2.a AND t1.b=t2.b AND t1.a=1;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 ref a a 5 const 1 Using index
-1 SIMPLE t2 ref a a 10 const,test.t1.b 2 Using index
+1 SIMPLE t2 ref a a 10 const,test.t1.b 1 Using index
EXPLAIN SELECT * FROM v1 WHERE a=1;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 ref a a 5 const 1 Using index
-1 SIMPLE t2 ref a a 10 const,test.t1.b 2 Using index
+1 SIMPLE t2 ref a a 10 const,test.t1.b 1 Using index
EXPLAIN SELECT * FROM v2 WHERE a=1;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 ref a a 5 const 1 Using index
