Below is the list of changes that have just been committed into a local
6.0 repository of kgeorge. When kgeorge does a push these changes will
be propagated to the main repository and, within 24 hours after the
push, to the public repository.
For information on how to access the public repository
see http://dev.mysql.com/doc/mysql/en/installing-source-tree.html
ChangeSet@stripped, 2007-11-26 18:10:09+02:00, gkodinov@stripped +3 -0
merge of 5.1-opt -> 6.0-opt
mysql-test/r/subselect_no_mat.result@stripped, 2007-11-26 18:10:07+02:00, gkodinov@stripped +65 -17
merge of 5.1-opt -> 6.0-opt
mysql-test/r/subselect_no_opts.result@stripped, 2007-11-26 18:10:08+02:00, gkodinov@stripped +65 -17
merge of 5.1-opt -> 6.0-opt
mysql-test/r/subselect_no_semijoin.result@stripped, 2007-11-26 18:10:08+02:00, gkodinov@stripped +65 -17
merge of 5.1-opt -> 6.0-opt
diff -Nrup a/mysql-test/r/subselect_no_mat.result b/mysql-test/r/subselect_no_mat.result
--- a/mysql-test/r/subselect_no_mat.result 2007-11-23 15:58:33 +02:00
+++ b/mysql-test/r/subselect_no_mat.result 2007-11-26 18:10:07 +02:00
@@ -81,10 +81,9 @@ ERROR HY000: Incorrect usage of PROCEDUR
SELECT 1 FROM (SELECT 1) a PROCEDURE ANALYSE((SELECT 1));
ERROR HY000: Incorrect parameters to procedure 'ANALYSE'
SELECT (SELECT 1) as a FROM (SELECT 1) b WHERE (SELECT a) IS NULL;
-a
+ERROR 42S22: Unknown column 'a' in 'field list'
SELECT (SELECT 1) as a FROM (SELECT 1) b WHERE (SELECT a) IS NOT NULL;
-a
-1
+ERROR 42S22: Unknown column 'a' in 'field list'
SELECT (SELECT 1,2,3) = ROW(1,2,3);
(SELECT 1,2,3) = ROW(1,2,3)
1
@@ -2299,24 +2298,27 @@ drop table t1,t2;
CREATE TABLE t1 ( a int, b int );
CREATE TABLE t2 ( c int, d int );
INSERT INTO t1 VALUES (1,2), (2,3), (3,4);
-SELECT a AS abc, b FROM t1 WHERE b = (SELECT MIN(b) FROM t1 WHERE a=abc);
+SELECT a AS abc, b FROM t1 outr WHERE b =
+(SELECT MIN(b) FROM t1 WHERE a=outr.a);
abc b
1 2
2 3
3 4
-INSERT INTO t2 SELECT a AS abc, b FROM t1 WHERE b = (SELECT MIN(b) FROM t1 WHERE a=abc);
+INSERT INTO t2 SELECT a AS abc, b FROM t1 outr WHERE b =
+(SELECT MIN(b) FROM t1 WHERE a=outr.a);
select * from t2;
c d
1 2
2 3
3 4
-CREATE TABLE t3 SELECT a AS abc, b FROM t1 WHERE b = (SELECT MIN(b) FROM t1 WHERE a=abc);
+CREATE TABLE t3 SELECT a AS abc, b FROM t1 outr WHERE b =
+(SELECT MIN(b) FROM t1 WHERE a=outr.a);
select * from t3;
abc b
1 2
2 3
3 4
-prepare stmt1 from "INSERT INTO t2 SELECT a AS abc, b FROM t1 WHERE b = (SELECT MIN(b) FROM t1 WHERE a=abc);";
+prepare stmt1 from "INSERT INTO t2 SELECT a AS abc, b FROM t1 outr WHERE b = (SELECT MIN(b) FROM t1 WHERE a=outr.a);";
execute stmt1;
deallocate prepare stmt1;
select * from t2;
@@ -2328,7 +2330,7 @@ c d
2 3
3 4
drop table t3;
-prepare stmt1 from "CREATE TABLE t3 SELECT a AS abc, b FROM t1 WHERE b = (SELECT MIN(b) FROM t1 WHERE a=abc);";
+prepare stmt1 from "CREATE TABLE t3 SELECT a AS abc, b FROM t1 outr WHERE b = (SELECT MIN(b) FROM t1 WHERE a=outr.a);";
execute stmt1;
select * from t3;
abc b
@@ -2510,7 +2512,9 @@ INSERT INTO t1 VALUES ('ASM','American S
INSERT INTO t1 VALUES ('ATF','French Southern territories','Antarctica','Antarctica',7780.00,0,0,NULL,0.00,NULL,'Terres australes françaises','Nonmetropolitan Territory of France','Jacques Chirac',NULL,'TF');
INSERT INTO t1 VALUES ('UMI','United States Minor Outlying Islands','Oceania','Micronesia/Caribbean',16.00,0,0,NULL,0.00,NULL,'United States Minor Outlying Islands','Dependent Territory of the US','George W. Bush',NULL,'UM');
/*!40000 ALTER TABLE t1 ENABLE KEYS */;
-SELECT DISTINCT Continent AS c FROM t1 WHERE Code <> SOME ( SELECT Code FROM t1 WHERE Continent = c AND Population < 200);
+SELECT DISTINCT Continent AS c FROM t1 outr WHERE
+Code <> SOME ( SELECT Code FROM t1 WHERE Continent = outr.Continent AND
+Population < 200);
c
Oceania
drop table t1;
@@ -3553,22 +3557,19 @@ SELECT sql_no_cache * FROM t1 WHERE NOT
(SELECT i FROM t1) UNION
(SELECT i FROM t1)
);
-i
+ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'UNION
+(SELECT i FROM t1)
+)' at line 3
SELECT * FROM t1
WHERE NOT EXISTS (((SELECT i FROM t1) UNION (SELECT i FROM t1)));
-i
+ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'UNION (SELECT i FROM t1)))' at line 2
explain select ((select t11.i from t1 t11) union (select t12.i from t1 t12))
from t1;
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'union (select t12.i from t1 t12))
from t1' at line 1
explain select * from t1 where not exists
((select t11.i from t1 t11) union (select t12.i from t1 t12));
-id select_type table type possible_keys key key_len ref rows Extra
-1 PRIMARY t1 system NULL NULL NULL NULL 0 const row not found
-2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL No tables used
-3 SUBQUERY NULL NULL NULL NULL NULL NULL NULL no matching row in const table
-4 UNION t12 system NULL NULL NULL NULL 0 const row not found
-NULL UNION RESULT <union2,4> ALL NULL NULL NULL NULL NULL
+ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'union (select t12.i from t1 t12))' at line 2
DROP TABLE t1;
CREATE TABLE t1 (a VARCHAR(250), b INT auto_increment, PRIMARY KEY (b));
insert into t1 (a) values (FLOOR(rand() * 100));
@@ -4312,6 +4313,53 @@ id select_type table type possible_keys
1 PRIMARY t1 ref I2 I2 13 test.t1.a 2 Using index condition
SELECT a,b FROM t1 WHERE b IN (SELECT a FROM t1 WHERE LENGTH(a)<500);
a b
+DROP TABLE t1,t2;
+CREATE TABLE t1(a INT, b INT);
+INSERT INTO t1 VALUES (1,1), (1,2), (2,3), (2,4);
+EXPLAIN
+SELECT a AS out_a, MIN(b) FROM t1
+WHERE b > (SELECT MIN(b) FROM t1 WHERE a = out_a)
+GROUP BY a;
+ERROR 42S22: Unknown column 'out_a' in 'where clause'
+SELECT a AS out_a, MIN(b) FROM t1
+WHERE b > (SELECT MIN(b) FROM t1 WHERE a = out_a)
+GROUP BY a;
+ERROR 42S22: Unknown column 'out_a' in 'where clause'
+EXPLAIN
+SELECT a AS out_a, MIN(b) FROM t1 t1_outer
+WHERE b > (SELECT MIN(b) FROM t1 WHERE a = t1_outer.a)
+GROUP BY a;
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY t1_outer ALL NULL NULL NULL NULL 4 Using where; Using temporary; Using filesort
+2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 4 Using where
+SELECT a AS out_a, MIN(b) FROM t1 t1_outer
+WHERE b > (SELECT MIN(b) FROM t1 WHERE a = t1_outer.a)
+GROUP BY a;
+out_a MIN(b)
+1 2
+2 4
+DROP TABLE t1;
+CREATE TABLE t1 (a INT);
+CREATE TABLE t2 (a INT);
+INSERT INTO t1 VALUES (1),(2);
+INSERT INTO t2 VALUES (1),(2);
+SELECT 2 FROM t1 WHERE EXISTS ((SELECT 1 FROM t2 WHERE t1.a=t2.a));
+2
+2
+2
+EXPLAIN EXTENDED
+SELECT 2 FROM t1 WHERE EXISTS ((SELECT 1 FROM t2 WHERE t1.a=t2.a));
+id select_type table type possible_keys key key_len ref rows filtered Extra
+1 PRIMARY t1 ALL NULL NULL NULL NULL 2 100.00 Using where
+2 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 2 100.00 Using where
+Warnings:
+Note 1276 Field or reference 'test.t1.a' of SELECT #2 was resolved in SELECT #1
+Note 1003 select 2 AS `2` from `test`.`t1` where exists(select 1 AS `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));
+ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'UNION
+(SELECT 1 FROM t2 WHERE t1.a = t2.a))' at line 2
DROP TABLE t1,t2;
End of 5.0 tests.
create table t_out (subcase char(3),
diff -Nrup a/mysql-test/r/subselect_no_opts.result b/mysql-test/r/subselect_no_opts.result
--- a/mysql-test/r/subselect_no_opts.result 2007-11-23 15:58:33 +02:00
+++ b/mysql-test/r/subselect_no_opts.result 2007-11-26 18:10:08 +02:00
@@ -81,10 +81,9 @@ ERROR HY000: Incorrect usage of PROCEDUR
SELECT 1 FROM (SELECT 1) a PROCEDURE ANALYSE((SELECT 1));
ERROR HY000: Incorrect parameters to procedure 'ANALYSE'
SELECT (SELECT 1) as a FROM (SELECT 1) b WHERE (SELECT a) IS NULL;
-a
+ERROR 42S22: Unknown column 'a' in 'field list'
SELECT (SELECT 1) as a FROM (SELECT 1) b WHERE (SELECT a) IS NOT NULL;
-a
-1
+ERROR 42S22: Unknown column 'a' in 'field list'
SELECT (SELECT 1,2,3) = ROW(1,2,3);
(SELECT 1,2,3) = ROW(1,2,3)
1
@@ -2299,24 +2298,27 @@ drop table t1,t2;
CREATE TABLE t1 ( a int, b int );
CREATE TABLE t2 ( c int, d int );
INSERT INTO t1 VALUES (1,2), (2,3), (3,4);
-SELECT a AS abc, b FROM t1 WHERE b = (SELECT MIN(b) FROM t1 WHERE a=abc);
+SELECT a AS abc, b FROM t1 outr WHERE b =
+(SELECT MIN(b) FROM t1 WHERE a=outr.a);
abc b
1 2
2 3
3 4
-INSERT INTO t2 SELECT a AS abc, b FROM t1 WHERE b = (SELECT MIN(b) FROM t1 WHERE a=abc);
+INSERT INTO t2 SELECT a AS abc, b FROM t1 outr WHERE b =
+(SELECT MIN(b) FROM t1 WHERE a=outr.a);
select * from t2;
c d
1 2
2 3
3 4
-CREATE TABLE t3 SELECT a AS abc, b FROM t1 WHERE b = (SELECT MIN(b) FROM t1 WHERE a=abc);
+CREATE TABLE t3 SELECT a AS abc, b FROM t1 outr WHERE b =
+(SELECT MIN(b) FROM t1 WHERE a=outr.a);
select * from t3;
abc b
1 2
2 3
3 4
-prepare stmt1 from "INSERT INTO t2 SELECT a AS abc, b FROM t1 WHERE b = (SELECT MIN(b) FROM t1 WHERE a=abc);";
+prepare stmt1 from "INSERT INTO t2 SELECT a AS abc, b FROM t1 outr WHERE b = (SELECT MIN(b) FROM t1 WHERE a=outr.a);";
execute stmt1;
deallocate prepare stmt1;
select * from t2;
@@ -2328,7 +2330,7 @@ c d
2 3
3 4
drop table t3;
-prepare stmt1 from "CREATE TABLE t3 SELECT a AS abc, b FROM t1 WHERE b = (SELECT MIN(b) FROM t1 WHERE a=abc);";
+prepare stmt1 from "CREATE TABLE t3 SELECT a AS abc, b FROM t1 outr WHERE b = (SELECT MIN(b) FROM t1 WHERE a=outr.a);";
execute stmt1;
select * from t3;
abc b
@@ -2510,7 +2512,9 @@ INSERT INTO t1 VALUES ('ASM','American S
INSERT INTO t1 VALUES ('ATF','French Southern territories','Antarctica','Antarctica',7780.00,0,0,NULL,0.00,NULL,'Terres australes françaises','Nonmetropolitan Territory of France','Jacques Chirac',NULL,'TF');
INSERT INTO t1 VALUES ('UMI','United States Minor Outlying Islands','Oceania','Micronesia/Caribbean',16.00,0,0,NULL,0.00,NULL,'United States Minor Outlying Islands','Dependent Territory of the US','George W. Bush',NULL,'UM');
/*!40000 ALTER TABLE t1 ENABLE KEYS */;
-SELECT DISTINCT Continent AS c FROM t1 WHERE Code <> SOME ( SELECT Code FROM t1 WHERE Continent = c AND Population < 200);
+SELECT DISTINCT Continent AS c FROM t1 outr WHERE
+Code <> SOME ( SELECT Code FROM t1 WHERE Continent = outr.Continent AND
+Population < 200);
c
Oceania
drop table t1;
@@ -3553,22 +3557,19 @@ SELECT sql_no_cache * FROM t1 WHERE NOT
(SELECT i FROM t1) UNION
(SELECT i FROM t1)
);
-i
+ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'UNION
+(SELECT i FROM t1)
+)' at line 3
SELECT * FROM t1
WHERE NOT EXISTS (((SELECT i FROM t1) UNION (SELECT i FROM t1)));
-i
+ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'UNION (SELECT i FROM t1)))' at line 2
explain select ((select t11.i from t1 t11) union (select t12.i from t1 t12))
from t1;
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'union (select t12.i from t1 t12))
from t1' at line 1
explain select * from t1 where not exists
((select t11.i from t1 t11) union (select t12.i from t1 t12));
-id select_type table type possible_keys key key_len ref rows Extra
-1 PRIMARY t1 system NULL NULL NULL NULL 0 const row not found
-2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL No tables used
-3 SUBQUERY NULL NULL NULL NULL NULL NULL NULL no matching row in const table
-4 UNION t12 system NULL NULL NULL NULL 0 const row not found
-NULL UNION RESULT <union2,4> ALL NULL NULL NULL NULL NULL
+ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'union (select t12.i from t1 t12))' at line 2
DROP TABLE t1;
CREATE TABLE t1 (a VARCHAR(250), b INT auto_increment, PRIMARY KEY (b));
insert into t1 (a) values (FLOOR(rand() * 100));
@@ -4312,6 +4313,53 @@ id select_type table type possible_keys
1 PRIMARY t1 ref I2 I2 13 test.t1.a 2 Using index condition
SELECT a,b FROM t1 WHERE b IN (SELECT a FROM t1 WHERE LENGTH(a)<500);
a b
+DROP TABLE t1,t2;
+CREATE TABLE t1(a INT, b INT);
+INSERT INTO t1 VALUES (1,1), (1,2), (2,3), (2,4);
+EXPLAIN
+SELECT a AS out_a, MIN(b) FROM t1
+WHERE b > (SELECT MIN(b) FROM t1 WHERE a = out_a)
+GROUP BY a;
+ERROR 42S22: Unknown column 'out_a' in 'where clause'
+SELECT a AS out_a, MIN(b) FROM t1
+WHERE b > (SELECT MIN(b) FROM t1 WHERE a = out_a)
+GROUP BY a;
+ERROR 42S22: Unknown column 'out_a' in 'where clause'
+EXPLAIN
+SELECT a AS out_a, MIN(b) FROM t1 t1_outer
+WHERE b > (SELECT MIN(b) FROM t1 WHERE a = t1_outer.a)
+GROUP BY a;
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY t1_outer ALL NULL NULL NULL NULL 4 Using where; Using temporary; Using filesort
+2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 4 Using where
+SELECT a AS out_a, MIN(b) FROM t1 t1_outer
+WHERE b > (SELECT MIN(b) FROM t1 WHERE a = t1_outer.a)
+GROUP BY a;
+out_a MIN(b)
+1 2
+2 4
+DROP TABLE t1;
+CREATE TABLE t1 (a INT);
+CREATE TABLE t2 (a INT);
+INSERT INTO t1 VALUES (1),(2);
+INSERT INTO t2 VALUES (1),(2);
+SELECT 2 FROM t1 WHERE EXISTS ((SELECT 1 FROM t2 WHERE t1.a=t2.a));
+2
+2
+2
+EXPLAIN EXTENDED
+SELECT 2 FROM t1 WHERE EXISTS ((SELECT 1 FROM t2 WHERE t1.a=t2.a));
+id select_type table type possible_keys key key_len ref rows filtered Extra
+1 PRIMARY t1 ALL NULL NULL NULL NULL 2 100.00 Using where
+2 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 2 100.00 Using where
+Warnings:
+Note 1276 Field or reference 'test.t1.a' of SELECT #2 was resolved in SELECT #1
+Note 1003 select 2 AS `2` from `test`.`t1` where exists(select 1 AS `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));
+ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'UNION
+(SELECT 1 FROM t2 WHERE t1.a = t2.a))' at line 2
DROP TABLE t1,t2;
End of 5.0 tests.
create table t_out (subcase char(3),
diff -Nrup a/mysql-test/r/subselect_no_semijoin.result b/mysql-test/r/subselect_no_semijoin.result
--- a/mysql-test/r/subselect_no_semijoin.result 2007-11-23 15:58:33 +02:00
+++ b/mysql-test/r/subselect_no_semijoin.result 2007-11-26 18:10:08 +02:00
@@ -81,10 +81,9 @@ ERROR HY000: Incorrect usage of PROCEDUR
SELECT 1 FROM (SELECT 1) a PROCEDURE ANALYSE((SELECT 1));
ERROR HY000: Incorrect parameters to procedure 'ANALYSE'
SELECT (SELECT 1) as a FROM (SELECT 1) b WHERE (SELECT a) IS NULL;
-a
+ERROR 42S22: Unknown column 'a' in 'field list'
SELECT (SELECT 1) as a FROM (SELECT 1) b WHERE (SELECT a) IS NOT NULL;
-a
-1
+ERROR 42S22: Unknown column 'a' in 'field list'
SELECT (SELECT 1,2,3) = ROW(1,2,3);
(SELECT 1,2,3) = ROW(1,2,3)
1
@@ -2299,24 +2298,27 @@ drop table t1,t2;
CREATE TABLE t1 ( a int, b int );
CREATE TABLE t2 ( c int, d int );
INSERT INTO t1 VALUES (1,2), (2,3), (3,4);
-SELECT a AS abc, b FROM t1 WHERE b = (SELECT MIN(b) FROM t1 WHERE a=abc);
+SELECT a AS abc, b FROM t1 outr WHERE b =
+(SELECT MIN(b) FROM t1 WHERE a=outr.a);
abc b
1 2
2 3
3 4
-INSERT INTO t2 SELECT a AS abc, b FROM t1 WHERE b = (SELECT MIN(b) FROM t1 WHERE a=abc);
+INSERT INTO t2 SELECT a AS abc, b FROM t1 outr WHERE b =
+(SELECT MIN(b) FROM t1 WHERE a=outr.a);
select * from t2;
c d
1 2
2 3
3 4
-CREATE TABLE t3 SELECT a AS abc, b FROM t1 WHERE b = (SELECT MIN(b) FROM t1 WHERE a=abc);
+CREATE TABLE t3 SELECT a AS abc, b FROM t1 outr WHERE b =
+(SELECT MIN(b) FROM t1 WHERE a=outr.a);
select * from t3;
abc b
1 2
2 3
3 4
-prepare stmt1 from "INSERT INTO t2 SELECT a AS abc, b FROM t1 WHERE b = (SELECT MIN(b) FROM t1 WHERE a=abc);";
+prepare stmt1 from "INSERT INTO t2 SELECT a AS abc, b FROM t1 outr WHERE b = (SELECT MIN(b) FROM t1 WHERE a=outr.a);";
execute stmt1;
deallocate prepare stmt1;
select * from t2;
@@ -2328,7 +2330,7 @@ c d
2 3
3 4
drop table t3;
-prepare stmt1 from "CREATE TABLE t3 SELECT a AS abc, b FROM t1 WHERE b = (SELECT MIN(b) FROM t1 WHERE a=abc);";
+prepare stmt1 from "CREATE TABLE t3 SELECT a AS abc, b FROM t1 outr WHERE b = (SELECT MIN(b) FROM t1 WHERE a=outr.a);";
execute stmt1;
select * from t3;
abc b
@@ -2510,7 +2512,9 @@ INSERT INTO t1 VALUES ('ASM','American S
INSERT INTO t1 VALUES ('ATF','French Southern territories','Antarctica','Antarctica',7780.00,0,0,NULL,0.00,NULL,'Terres australes françaises','Nonmetropolitan Territory of France','Jacques Chirac',NULL,'TF');
INSERT INTO t1 VALUES ('UMI','United States Minor Outlying Islands','Oceania','Micronesia/Caribbean',16.00,0,0,NULL,0.00,NULL,'United States Minor Outlying Islands','Dependent Territory of the US','George W. Bush',NULL,'UM');
/*!40000 ALTER TABLE t1 ENABLE KEYS */;
-SELECT DISTINCT Continent AS c FROM t1 WHERE Code <> SOME ( SELECT Code FROM t1 WHERE Continent = c AND Population < 200);
+SELECT DISTINCT Continent AS c FROM t1 outr WHERE
+Code <> SOME ( SELECT Code FROM t1 WHERE Continent = outr.Continent AND
+Population < 200);
c
Oceania
drop table t1;
@@ -3553,22 +3557,19 @@ SELECT sql_no_cache * FROM t1 WHERE NOT
(SELECT i FROM t1) UNION
(SELECT i FROM t1)
);
-i
+ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'UNION
+(SELECT i FROM t1)
+)' at line 3
SELECT * FROM t1
WHERE NOT EXISTS (((SELECT i FROM t1) UNION (SELECT i FROM t1)));
-i
+ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'UNION (SELECT i FROM t1)))' at line 2
explain select ((select t11.i from t1 t11) union (select t12.i from t1 t12))
from t1;
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'union (select t12.i from t1 t12))
from t1' at line 1
explain select * from t1 where not exists
((select t11.i from t1 t11) union (select t12.i from t1 t12));
-id select_type table type possible_keys key key_len ref rows Extra
-1 PRIMARY t1 system NULL NULL NULL NULL 0 const row not found
-2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL No tables used
-3 SUBQUERY NULL NULL NULL NULL NULL NULL NULL no matching row in const table
-4 UNION t12 system NULL NULL NULL NULL 0 const row not found
-NULL UNION RESULT <union2,4> ALL NULL NULL NULL NULL NULL
+ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'union (select t12.i from t1 t12))' at line 2
DROP TABLE t1;
CREATE TABLE t1 (a VARCHAR(250), b INT auto_increment, PRIMARY KEY (b));
insert into t1 (a) values (FLOOR(rand() * 100));
@@ -4312,6 +4313,53 @@ id select_type table type possible_keys
1 PRIMARY t1 ref I2 I2 13 test.t1.a 2 Using index condition
SELECT a,b FROM t1 WHERE b IN (SELECT a FROM t1 WHERE LENGTH(a)<500);
a b
+DROP TABLE t1,t2;
+CREATE TABLE t1(a INT, b INT);
+INSERT INTO t1 VALUES (1,1), (1,2), (2,3), (2,4);
+EXPLAIN
+SELECT a AS out_a, MIN(b) FROM t1
+WHERE b > (SELECT MIN(b) FROM t1 WHERE a = out_a)
+GROUP BY a;
+ERROR 42S22: Unknown column 'out_a' in 'where clause'
+SELECT a AS out_a, MIN(b) FROM t1
+WHERE b > (SELECT MIN(b) FROM t1 WHERE a = out_a)
+GROUP BY a;
+ERROR 42S22: Unknown column 'out_a' in 'where clause'
+EXPLAIN
+SELECT a AS out_a, MIN(b) FROM t1 t1_outer
+WHERE b > (SELECT MIN(b) FROM t1 WHERE a = t1_outer.a)
+GROUP BY a;
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY t1_outer ALL NULL NULL NULL NULL 4 Using where; Using temporary; Using filesort
+2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 4 Using where
+SELECT a AS out_a, MIN(b) FROM t1 t1_outer
+WHERE b > (SELECT MIN(b) FROM t1 WHERE a = t1_outer.a)
+GROUP BY a;
+out_a MIN(b)
+1 2
+2 4
+DROP TABLE t1;
+CREATE TABLE t1 (a INT);
+CREATE TABLE t2 (a INT);
+INSERT INTO t1 VALUES (1),(2);
+INSERT INTO t2 VALUES (1),(2);
+SELECT 2 FROM t1 WHERE EXISTS ((SELECT 1 FROM t2 WHERE t1.a=t2.a));
+2
+2
+2
+EXPLAIN EXTENDED
+SELECT 2 FROM t1 WHERE EXISTS ((SELECT 1 FROM t2 WHERE t1.a=t2.a));
+id select_type table type possible_keys key key_len ref rows filtered Extra
+1 PRIMARY t1 ALL NULL NULL NULL NULL 2 100.00 Using where
+2 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 2 100.00 Using where
+Warnings:
+Note 1276 Field or reference 'test.t1.a' of SELECT #2 was resolved in SELECT #1
+Note 1003 select 2 AS `2` from `test`.`t1` where exists(select 1 AS `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));
+ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'UNION
+(SELECT 1 FROM t2 WHERE t1.a = t2.a))' at line 2
DROP TABLE t1,t2;
End of 5.0 tests.
create table t_out (subcase char(3),
| Thread |
|---|
| • bk commit into 6.0 tree (gkodinov:1.2693) | kgeorge | 26 Nov |
