diff options
Diffstat (limited to 'mysql-test/main/subselect_no_semijoin.result')
| -rw-r--r-- | mysql-test/main/subselect_no_semijoin.result | 80 |
1 files changed, 52 insertions, 28 deletions
diff --git a/mysql-test/main/subselect_no_semijoin.result b/mysql-test/main/subselect_no_semijoin.result index 674e8a30f75..52c81e58a49 100644 --- a/mysql-test/main/subselect_no_semijoin.result +++ b/mysql-test/main/subselect_no_semijoin.result @@ -182,7 +182,8 @@ select * from t2 where t2.b=(select a from t3 order by 1 desc limit 1); a b 1 7 2 7 -(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; a b 1 7 2 7 @@ -3103,13 +3104,18 @@ create table t1(a int, primary key (a)); 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; +Table Op Msg_type Msg_text +test.t1 analyze status Engine-independent statistics collected +test.t1 analyze status OK 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' ORDER BY t2.c DESC, t2.b DESC LIMIT 1) WHERE t1.a = 10; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 system PRIMARY NULL NULL NULL 1 1 PRIMARY r const PRIMARY PRIMARY 4 const 1 -2 SUBQUERY t2 range b b 40 NULL 2 Using where +2 SUBQUERY t2 range b b 40 NULL 3 Using where 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' ORDER BY t2.c DESC, t2.b DESC LIMIT 1) WHERE t1.a = 10; @@ -3121,7 +3127,7 @@ ON r.a = (SELECT t2.a FROM t2 WHERE t2.c = t1.a AND t2.b <= '359899' id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 system PRIMARY NULL NULL NULL 1 1 PRIMARY r const PRIMARY PRIMARY 4 const 1 -2 SUBQUERY t2 range b b 40 NULL 2 Using index condition +2 SUBQUERY t2 range b b 40 NULL 3 Using index condition 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' ORDER BY t2.c, t2.b LIMIT 1) WHERE t1.a = 10; @@ -3706,7 +3712,7 @@ ORDER BY t1.t DESC LIMIT 1); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t2 system NULL NULL NULL NULL 1 1 PRIMARY t1 index NULL PRIMARY 13 NULL 11 Using where; Using index -2 SUBQUERY t1 range PRIMARY PRIMARY 13 NULL 5 Using where; Using index +2 SUBQUERY t1 range PRIMARY PRIMARY 13 NULL 6 Using where; Using index SELECT * FROM t1,t2 WHERE t1.t = (SELECT t1.t FROM t1 WHERE t1.t < t2.t AND t1.i2=1 AND t2.i1=t1.i1 @@ -3725,7 +3731,7 @@ SELECT sql_no_cache * FROM t1 WHERE NOT EXISTS i SELECT * FROM t1 WHERE NOT EXISTS (((SELECT i FROM t1) UNION (SELECT i FROM t1))); -ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'UNION (SELECT i FROM t1)))' at line 2 +i 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 MariaDB server version for the right syntax to use near 'union (select t12.i from t1 t12)) @@ -4972,7 +4978,7 @@ UNIQUE KEY b (b,c), KEY c (c), 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; id select_type table type possible_keys key key_len ref rows Extra @@ -5182,11 +5188,11 @@ a 1 1 1 2 1 SELECT * FROM t1 JOIN ((SELECT 1 UNION SELECT 1)) ON 1; -ERROR 42000: Every derived table must have its own alias +ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'ON 1' at line 1 SELECT * FROM t1 JOIN (t1 t1a UNION SELECT 1) ON 1; -ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'SELECT 1) ON 1' at line 1 +ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'UNION SELECT 1) ON 1' at line 1 SELECT * FROM t1 JOIN ((t1 t1a UNION SELECT 1)) ON 1; -ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'SELECT 1)) ON 1' at line 1 +ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'UNION SELECT 1)) ON 1' at line 1 SELECT * FROM t1 JOIN (t1 t1a) t1a ON 1; ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 't1a ON 1' at line 1 SELECT * FROM t1 JOIN ((t1 t1a)) t1a ON 1; @@ -5285,11 +5291,14 @@ SELECT ( SELECT a FROM t1 WHERE a = 1 UNION SELECT 1 ), a FROM t1; SELECT * FROM t2 WHERE (a, b) IN (SELECT a, b FROM t2); a b SELECT 1 UNION ( SELECT 1 UNION SELECT 1 ); -ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'UNION SELECT 1 )' at line 1 +1 +1 ( SELECT 1 UNION SELECT 1 ) UNION SELECT 1; -ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'UNION SELECT 1 ) UNION SELECT 1' at line 1 +1 +1 SELECT ( SELECT 1 UNION ( SELECT 1 UNION SELECT 1 ) ); -ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'UNION SELECT 1 ) )' at line 1 +( SELECT 1 UNION ( SELECT 1 UNION SELECT 1 ) ) +1 SELECT ( ( SELECT 1 UNION SELECT 1 ) UNION SELECT 1; ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'UNION SELECT 1' at line 1 SELECT ( SELECT 1 UNION SELECT 1 UNION SELECT 1 ); @@ -5299,9 +5308,9 @@ SELECT ((SELECT 1 UNION SELECT 1 UNION SELECT 1)); ((SELECT 1 UNION SELECT 1 UNION SELECT 1)) 1 SELECT * FROM ( SELECT 1 UNION ( SELECT 1 UNION SELECT 1 ) ); -ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'UNION SELECT 1 ) )' at line 1 +ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '' at line 1 SELECT * FROM ( ( SELECT 1 UNION SELECT 1 ) UNION SELECT 1 ); -ERROR 42000: Every derived table must have its own alias +ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '' at line 1 SELECT * FROM ( ( SELECT 1 UNION SELECT 1 ) UNION SELECT 1 ) a; 1 1 @@ -5309,19 +5318,25 @@ SELECT * FROM ( SELECT 1 UNION SELECT 1 UNION SELECT 1 ) a; 1 1 SELECT * FROM t1 WHERE a = ( SELECT 1 UNION ( SELECT 1 UNION SELECT 1 ) ); -ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'UNION SELECT 1 ) )' at line 1 +a +1 SELECT * FROM t1 WHERE a = ALL ( SELECT 1 UNION ( SELECT 1 UNION SELECT 1 ) ); -ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'UNION SELECT 1 ) )' at line 1 +a +1 SELECT * FROM t1 WHERE a = ANY ( SELECT 1 UNION ( SELECT 1 UNION SELECT 1 ) ); -ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'UNION SELECT 1 ) )' at line 1 +a +1 SELECT * FROM t1 WHERE a IN ( SELECT 1 UNION ( SELECT 1 UNION SELECT 1 ) ); -ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'UNION SELECT 1 ) )' at line 1 +a +1 SELECT * FROM t1 WHERE a = ( ( SELECT 1 UNION SELECT 1 ) UNION SELECT 1 ); ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'UNION SELECT 1 )' at line 1 SELECT * FROM t1 WHERE a = ALL ( ( SELECT 1 UNION SELECT 1 ) UNION SELECT 1 ); -ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'UNION SELECT 1 ) UNION SELECT 1 )' at line 1 +a +1 SELECT * FROM t1 WHERE a = ANY ( ( SELECT 1 UNION SELECT 1 ) UNION SELECT 1 ); -ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'UNION SELECT 1 ) UNION SELECT 1 )' at line 1 +a +1 SELECT * FROM t1 WHERE a IN ( ( SELECT 1 UNION SELECT 1 ) UNION SELECT 1 ); ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'UNION SELECT 1 )' at line 1 SELECT * FROM t1 WHERE a = ( SELECT 1 UNION SELECT 1 UNION SELECT 1 ); @@ -7012,8 +7027,8 @@ SELECT * FROM t1 WHERE a = (SELECT MAX(b) FROM t2 WHERE c IN (SELECT MAX(d) FROM t3)) OR a = 10; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 index a a 5 NULL 2 Using where; Using index -2 SUBQUERY <subquery3> ALL distinct_key NULL NULL NULL 1 -2 SUBQUERY t2 ALL NULL NULL NULL NULL 2 Using where; Using join buffer (flat, BNL join) +2 SUBQUERY t2 ALL NULL NULL NULL NULL 2 Using where +2 SUBQUERY <subquery3> eq_ref distinct_key distinct_key 4 test.t2.c 1 3 MATERIALIZED t3 ALL NULL NULL NULL NULL 2 SELECT * FROM t1 WHERE a = (SELECT MAX(b) FROM t2 WHERE c IN (SELECT MAX(d) FROM t3)) OR a = 10; @@ -7072,12 +7087,6 @@ call procedure2(); ControlRev NULL 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; -sq # # MDEV-7846:Server crashes in Item_subselect::fix #_fields or fails with Thread stack overrun @@ -7291,6 +7300,21 @@ pk i c pk i c DROP TABLE t; # End of 10.2 tests # +# Start of 10.4 tests +# +# +# MDEV-16861 Split Item::update_null_value() into a new virtual method in Type_handler +# +SELECT ROW(1,2) = EXISTS (SELECT 1); +ERROR HY000: Illegal parameter data types row and boolean for operation '=' +SELECT ROW(1,2) = 1 IN (SELECT 1 UNION SELECT 2); +ERROR HY000: Illegal parameter data types row and boolean for operation '=' +SELECT ROW(1,2) = (1 = ANY (SELECT 1 UNION SELECT 2)); +ERROR HY000: Illegal parameter data types row and boolean for operation '=' +# +# End of 10.4 tests +# +# # MDEV-19714: JOIN::pseudo_bits_cond is not visible in EXPLAIN FORMAT=JSON # CREATE TABLE t1 ( a INT ); |