diff options
Diffstat (limited to 'mysql-test/main/subselect.test')
| -rw-r--r-- | mysql-test/main/subselect.test | 94 |
1 files changed, 44 insertions, 50 deletions
diff --git a/mysql-test/main/subselect.test b/mysql-test/main/subselect.test index c5cec99cebf..9af3727df21 100644 --- a/mysql-test/main/subselect.test +++ b/mysql-test/main/subselect.test @@ -53,7 +53,7 @@ SELECT * FROM (SELECT 1 as id) b WHERE id IN (SELECT * FROM (SELECT 1 as id) c O SELECT * FROM (SELECT 1) a WHERE 1 IN (SELECT 1,1); SELECT 1 IN (SELECT 1); SELECT 1 FROM (SELECT 1 as a) b WHERE 1 IN (SELECT (SELECT a)); --- error ER_PARSE_ERROR +-- error ER_CANT_USE_OPTION_HERE select (SELECT 1 FROM (SELECT 1) a PROCEDURE ANALYSE(1)); -- error ER_PARSE_ERROR SELECT 1 FROM (SELECT 1) a PROCEDURE ANALYSE((SELECT 1)); @@ -99,7 +99,8 @@ select (select a from t3), a from t2; select * from t2 where t2.a=(select a from t1); insert into t3 values (6),(7),(3); select * from t2 where t2.b=(select a from t3 order by 1 desc limit 1); -(select * from t2 where t2.b=(select a from t3 order by 1 desc limit 1)) union (select * from t4 order by a limit 2) limit 3; +(select * from t2 where t2.b=(select a from t3 order by 1 desc limit 1)) +union (select * from t4 order by a limit 2) order by a limit 3; (select * from t2 where t2.b=(select a from t3 order by 1 desc limit 1)) union (select * from t4 where t4.b=(select max(t2.a)*4 from t2) order by a); explain extended (select * from t2 where t2.b=(select a from t3 order by 1 desc limit 1)) union (select * from t4 where t4.b=(select max(t2.a)*4 from t2) order by a); select (select a from t3 where a<t2.a*4 order by 1 desc limit 1), a from t2; @@ -2604,8 +2605,6 @@ SELECT sql_no_cache * FROM t1 WHERE NOT EXISTS (SELECT i FROM t1) ); -#TODO:not supported ---error ER_PARSE_ERROR SELECT * FROM t1 WHERE NOT EXISTS (((SELECT i FROM t1) UNION (SELECT i FROM t1))); @@ -4229,31 +4228,31 @@ INSERT INTO t1 VALUES (1),(2); CREATE TABLE t2( a INT, b INT ); ---error ER_PARSE_ERROR +--error ER_CANT_USE_OPTION_HERE SELECT * FROM (SELECT a INTO @var FROM t1 WHERE a = 2) t1a; ---error ER_PARSE_ERROR +--error ER_CANT_USE_OPTION_HERE SELECT * FROM (SELECT a INTO OUTFILE 'file' FROM t1 WHERE a = 2) t1a; ---error ER_PARSE_ERROR +--error ER_CANT_USE_OPTION_HERE SELECT * FROM (SELECT a INTO DUMPFILE 'file' FROM t1 WHERE a = 2) t1a; ---error ER_PARSE_ERROR +--error ER_CANT_USE_OPTION_HERE SELECT * FROM ( SELECT 1 a UNION SELECT a INTO @var FROM t1 WHERE a = 2 ) t1a; ---error ER_PARSE_ERROR +--error ER_CANT_USE_OPTION_HERE SELECT * FROM ( SELECT 1 a UNION SELECT a INTO OUTFILE 'file' FROM t1 WHERE a = 2 ) t1a; ---error ER_PARSE_ERROR +--error ER_CANT_USE_OPTION_HERE SELECT * FROM ( SELECT 1 a UNION @@ -4281,25 +4280,25 @@ SELECT * FROM ( SELECT * FROM ((SELECT 1 a) UNION SELECT 1 a) q; SELECT * FROM (SELECT 1 a UNION (SELECT 1 a)) alias; SELECT * FROM (SELECT 1 UNION SELECT 1) t1a; ---error ER_PARSE_ERROR +--error ER_CANT_USE_OPTION_HERE SELECT * FROM ((SELECT 1 a INTO @a)) t1a; ---error ER_PARSE_ERROR +--error ER_CANT_USE_OPTION_HERE SELECT * FROM ((SELECT 1 a INTO OUTFILE 'file' )) t1a; ---error ER_PARSE_ERROR +--error ER_CANT_USE_OPTION_HERE SELECT * FROM ((SELECT 1 a INTO DUMPFILE 'file' )) t1a; ---error ER_PARSE_ERROR +--error ER_CANT_USE_OPTION_HERE SELECT * FROM (SELECT 1 a UNION (SELECT 1 a INTO @a)) t1a; ---error ER_PARSE_ERROR +--error ER_CANT_USE_OPTION_HERE SELECT * FROM (SELECT 1 a UNION (SELECT 1 a INTO DUMPFILE 'file' )) t1a; ---error ER_PARSE_ERROR +--error ER_CANT_USE_OPTION_HERE SELECT * FROM (SELECT 1 a UNION (SELECT 1 a INTO OUTFILE 'file' )) t1a; ---error ER_PARSE_ERROR +--error ER_CANT_USE_OPTION_HERE SELECT * FROM (SELECT 1 a UNION ((SELECT 1 a INTO @a))) t1a; ---error ER_PARSE_ERROR +--error ER_CANT_USE_OPTION_HERE SELECT * FROM (SELECT 1 a UNION ((SELECT 1 a INTO DUMPFILE 'file' ))) t1a; ---error ER_PARSE_ERROR +--error ER_CANT_USE_OPTION_HERE SELECT * FROM (SELECT 1 a UNION ((SELECT 1 a INTO OUTFILE 'file' ))) t1a; SELECT * FROM (SELECT 1 a ORDER BY a) t1a; @@ -4313,7 +4312,7 @@ SELECT * FROM (SELECT 1 a UNION SELECT 1 a ORDER BY a LIMIT 1) t1a; # aliases after. # SELECT * FROM t1 JOIN (SELECT 1 UNION SELECT 1) alias ON 1; ---error ER_DERIVED_MUST_HAVE_ALIAS +--error ER_PARSE_ERROR SELECT * FROM t1 JOIN ((SELECT 1 UNION SELECT 1)) ON 1; --error ER_PARSE_ERROR SELECT * FROM t1 JOIN (t1 t1a UNION SELECT 1) ON 1; @@ -4324,10 +4323,14 @@ SELECT * FROM t1 JOIN (t1 t1a) t1a ON 1; --error ER_PARSE_ERROR SELECT * FROM t1 JOIN ((t1 t1a)) t1a ON 1; +--error ER_PARSE_ERROR SELECT * FROM t1 JOIN (t1 t1a) ON 1; +--error ER_PARSE_ERROR SELECT * FROM t1 JOIN ((t1 t1a)) ON 1; +--error ER_PARSE_ERROR SELECT * FROM (t1 t1a); +--error ER_PARSE_ERROR SELECT * FROM ((t1 t1a)); SELECT * FROM t1 JOIN (SELECT 1 t1a) alias ON 1; @@ -4345,41 +4348,41 @@ SELECT * FROM t1 WHERE a = ALL ( SELECT 1 ); SELECT * FROM t1 WHERE a = ALL ( SELECT 1 UNION SELECT 1 ); SELECT * FROM t1 WHERE a = ANY ( SELECT 3 UNION SELECT 1 ); ---error ER_PARSE_ERROR +--error ER_CANT_USE_OPTION_HERE SELECT * FROM t1 WHERE a = ANY ( SELECT 1 UNION SELECT 1 INTO @a); ---error ER_PARSE_ERROR +--error ER_CANT_USE_OPTION_HERE SELECT * FROM t1 WHERE a = ANY ( SELECT 1 UNION SELECT 1 INTO OUTFILE 'file' ); ---error ER_PARSE_ERROR +--error ER_CANT_USE_OPTION_HERE SELECT * FROM t1 WHERE a = ANY ( SELECT 1 UNION SELECT 1 INTO DUMPFILE 'file' ); SELECT * FROM t1 WHERE a = ( SELECT 1 ); SELECT * FROM t1 WHERE a = ( SELECT 1 UNION SELECT 1 ); ---error ER_PARSE_ERROR +--error ER_CANT_USE_OPTION_HERE SELECT * FROM t1 WHERE a = ( SELECT 1 INTO @a); ---error ER_PARSE_ERROR +--error ER_CANT_USE_OPTION_HERE SELECT * FROM t1 WHERE a = ( SELECT 1 INTO OUTFILE 'file' ); ---error ER_PARSE_ERROR +--error ER_CANT_USE_OPTION_HERE SELECT * FROM t1 WHERE a = ( SELECT 1 INTO DUMPFILE 'file' ); ---error ER_PARSE_ERROR +--error ER_CANT_USE_OPTION_HERE SELECT * FROM t1 WHERE a = ( SELECT 1 UNION SELECT 1 INTO @a); ---error ER_PARSE_ERROR +--error ER_CANT_USE_OPTION_HERE SELECT * FROM t1 WHERE a = ( SELECT 1 UNION SELECT 1 INTO OUTFILE 'file' ); ---error ER_PARSE_ERROR +--error ER_CANT_USE_OPTION_HERE SELECT * FROM t1 WHERE a = ( SELECT 1 UNION SELECT 1 INTO DUMPFILE 'file' ); ---error ER_PARSE_ERROR +--error ER_CANT_USE_OPTION_HERE SELECT ( SELECT 1 INTO @v ); ---error ER_PARSE_ERROR +--error ER_CANT_USE_OPTION_HERE SELECT ( SELECT 1 INTO OUTFILE 'file' ); ---error ER_PARSE_ERROR +--error ER_CANT_USE_OPTION_HERE SELECT ( SELECT 1 INTO DUMPFILE 'file' ); ---error ER_PARSE_ERROR +--error ER_CANT_USE_OPTION_HERE SELECT ( SELECT 1 UNION SELECT 1 INTO @v ); ---error ER_PARSE_ERROR +--error ER_CANT_USE_OPTION_HERE SELECT ( SELECT 1 UNION SELECT 1 INTO OUTFILE 'file' ); ---error ER_PARSE_ERROR +--error ER_CANT_USE_OPTION_HERE SELECT ( SELECT 1 UNION SELECT 1 INTO DUMPFILE 'file' ); # Make sure context is popped when we leave the nested select @@ -4391,12 +4394,9 @@ SELECT * FROM t2 WHERE (a, b) IN (SELECT a, b FROM t2); # Make sure the parser does not allow nested UNIONs anywhere ---error ER_PARSE_ERROR SELECT 1 UNION ( SELECT 1 UNION SELECT 1 ); ---error ER_PARSE_ERROR ( SELECT 1 UNION SELECT 1 ) UNION SELECT 1; ---error ER_PARSE_ERROR SELECT ( SELECT 1 UNION ( SELECT 1 UNION SELECT 1 ) ); --error ER_PARSE_ERROR SELECT ( ( SELECT 1 UNION SELECT 1 ) UNION SELECT 1; @@ -4405,25 +4405,19 @@ SELECT ((SELECT 1 UNION SELECT 1 UNION SELECT 1)); --error ER_PARSE_ERROR SELECT * FROM ( SELECT 1 UNION ( SELECT 1 UNION SELECT 1 ) ); ---error ER_DERIVED_MUST_HAVE_ALIAS +--error ER_PARSE_ERROR SELECT * FROM ( ( SELECT 1 UNION SELECT 1 ) UNION SELECT 1 ); SELECT * FROM ( ( SELECT 1 UNION SELECT 1 ) UNION SELECT 1 ) a; SELECT * FROM ( SELECT 1 UNION SELECT 1 UNION SELECT 1 ) a; ---error ER_PARSE_ERROR SELECT * FROM t1 WHERE a = ( SELECT 1 UNION ( SELECT 1 UNION SELECT 1 ) ); ---error ER_PARSE_ERROR SELECT * FROM t1 WHERE a = ALL ( SELECT 1 UNION ( SELECT 1 UNION SELECT 1 ) ); ---error ER_PARSE_ERROR SELECT * FROM t1 WHERE a = ANY ( SELECT 1 UNION ( SELECT 1 UNION SELECT 1 ) ); ---error ER_PARSE_ERROR SELECT * FROM t1 WHERE a IN ( SELECT 1 UNION ( SELECT 1 UNION SELECT 1 ) ); --error ER_PARSE_ERROR SELECT * FROM t1 WHERE a = ( ( SELECT 1 UNION SELECT 1 ) UNION SELECT 1 ); ---error ER_PARSE_ERROR SELECT * FROM t1 WHERE a = ALL ( ( SELECT 1 UNION SELECT 1 ) UNION SELECT 1 ); ---error ER_PARSE_ERROR SELECT * FROM t1 WHERE a = ANY ( ( SELECT 1 UNION SELECT 1 ) UNION SELECT 1 ); --error ER_PARSE_ERROR SELECT * FROM t1 WHERE a IN ( ( SELECT 1 UNION SELECT 1 ) UNION SELECT 1 ); @@ -4433,17 +4427,17 @@ SELECT * FROM t1 WHERE a = ALL ( SELECT 1 UNION SELECT 1 UNION SELECT 1 ); SELECT * FROM t1 WHERE a = ANY ( SELECT 1 UNION SELECT 1 UNION SELECT 1 ); SELECT * FROM t1 WHERE a IN ( SELECT 1 UNION SELECT 1 UNION SELECT 1 ); ---error ER_PARSE_ERROR +--error ER_CANT_USE_OPTION_HERE SELECT * FROM t1 WHERE EXISTS ( SELECT 1 UNION SELECT 1 INTO @v ); SELECT EXISTS(SELECT 1+1); ---error ER_PARSE_ERROR +--error ER_CANT_USE_OPTION_HERE SELECT EXISTS(SELECT 1+1 INTO @test); ---error ER_PARSE_ERROR +--error ER_CANT_USE_OPTION_HERE SELECT * FROM t1 WHERE a IN ( SELECT 1 UNION SELECT 1 INTO @v ); ---error ER_PARSE_ERROR +--error ER_CANT_USE_OPTION_HERE SELECT * FROM t1 WHERE EXISTS ( SELECT 1 INTO @v ); ---error ER_PARSE_ERROR +--error ER_CANT_USE_OPTION_HERE SELECT * FROM t1 WHERE a IN ( SELECT 1 INTO @v ); DROP TABLE t1, t2; |