diff options
Diffstat (limited to 'mysql-test/main/subselect.test')
| -rw-r--r-- | mysql-test/main/subselect.test | 53 |
1 files changed, 31 insertions, 22 deletions
diff --git a/mysql-test/main/subselect.test b/mysql-test/main/subselect.test index 189dde382dd..dd80b789516 100644 --- a/mysql-test/main/subselect.test +++ b/mysql-test/main/subselect.test @@ -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; @@ -2025,6 +2026,8 @@ insert into t1 values (10); create table t2 (a int primary key, b varchar(32), c int, unique key b(c, b)); insert into t2(a, c, b) values (1,10,'359'), (2,10,'35988'), (3,10,'35989'); +insert into t2(a, c, b) values (4,10,'360'), (5,10,'35998'), (6,10,'35999'); +analyze table t1; explain 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' @@ -2604,8 +2607,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))); @@ -3879,7 +3880,7 @@ CREATE TABLE t3 ( KEY b_2 (b) ); -INSERT INTO t3 VALUES (1,1,1), (2,32,1); +INSERT INTO t3 VALUES (1,1,1), (2,32,1), (3,33,1), (4,34,2); 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; @@ -4313,7 +4314,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 +4325,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; @@ -4391,12 +4396,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 +4407,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 ); @@ -5933,13 +5929,6 @@ call procedure2(); drop procedure procedure2; -SELECT - (SELECT user FROM mysql.user - WHERE h.host in (SELECT host FROM mysql.user) - ) AS sq -FROM mysql.host h GROUP BY h.host; - - --echo # --echo # MDEV-7846:Server crashes in Item_subselect::fix --echo #_fields or fails with Thread stack overrun @@ -6166,3 +6155,23 @@ SELECT * FROM t t1 RIGHT JOIN t t2 ON (t2.pk = t1.pk) DROP TABLE t; --echo # End of 10.2 tests + + +--echo # +--echo # Start of 10.4 tests +--echo # + +--echo # +--echo # MDEV-16861 Split Item::update_null_value() into a new virtual method in Type_handler +--echo # + +--error ER_ILLEGAL_PARAMETER_DATA_TYPES2_FOR_OPERATION +SELECT ROW(1,2) = EXISTS (SELECT 1); +--error ER_ILLEGAL_PARAMETER_DATA_TYPES2_FOR_OPERATION +SELECT ROW(1,2) = 1 IN (SELECT 1 UNION SELECT 2); +--error ER_ILLEGAL_PARAMETER_DATA_TYPES2_FOR_OPERATION +SELECT ROW(1,2) = (1 = ANY (SELECT 1 UNION SELECT 2)); + +--echo # +--echo # End of 10.4 tests +--echo # |