diff options
Diffstat (limited to 'mysql-test/t/subselect.test')
| -rw-r--r-- | mysql-test/t/subselect.test | 229 |
1 files changed, 216 insertions, 13 deletions
diff --git a/mysql-test/t/subselect.test b/mysql-test/t/subselect.test index 0b368f5be67..6369e4edbeb 100644 --- a/mysql-test/t/subselect.test +++ b/mysql-test/t/subselect.test @@ -14,7 +14,7 @@ drop view if exists v2; set @subselect_tmp=@@optimizer_switch; set @@optimizer_switch=ifnull(@optimizer_switch_for_subselect_test, - "semijoin=on,firstmatch=on,loosescan=on,partial_match_rowid_merge=off,partial_match_table_scan=off"); + "semijoin=on,firstmatch=on,loosescan=on,semijoin_with_cache=on,partial_match_rowid_merge=off,partial_match_table_scan=off"); set optimizer_switch='mrr=on,mrr_sort_keys=on,index_condition_pushdown=on'; select (select 2); @@ -292,8 +292,9 @@ SELECT (SELECT numeropost FROM t1 HAVING numreponse=a),numreponse FROM (SELECT * SELECT numreponse, (SELECT numeropost FROM t1 HAVING numreponse=a) FROM (SELECT * FROM t1) as a; SELECT numreponse, (SELECT numeropost FROM t1 HAVING numreponse=1) FROM (SELECT * FROM t1) as a; INSERT INTO t1 (numeropost,numreponse,pseudo) VALUES (1,1,'joce'),(1,2,'joce'),(1,3,'test'); --- error ER_SUBQUERY_NO_1_ROW EXPLAIN EXTENDED SELECT numreponse FROM t1 WHERE numeropost='1' AND numreponse=(SELECT 1 FROM t1 WHERE numeropost='1'); +-- error ER_SUBQUERY_NO_1_ROW +SELECT numreponse FROM t1 WHERE numeropost='1' AND numreponse=(SELECT 1 FROM t1 WHERE numeropost='1'); EXPLAIN EXTENDED SELECT MAX(numreponse) FROM t1 WHERE numeropost='1'; EXPLAIN EXTENDED SELECT numreponse FROM t1 WHERE numeropost='1' AND numreponse=(SELECT MAX(numreponse) FROM t1 WHERE numeropost='1'); drop table t1; @@ -503,6 +504,54 @@ SELECT t1.a, t1.a in (select t2.a from t2,t3 where t3.a=t2.a) FROM t1; explain extended SELECT t1.a, t1.a in (select t2.a from t2,t3 where t3.a=t2.a) FROM t1; drop table t1,t2,t3; +--echo # check correct NULL Processing for normal IN/ALL/ANY +--echo # and 2 ways of max/min optimization +create table t1 (a int); +insert into t1 values (1), (100), (NULL), (1000); +create table t2 (a int not null); + +--echo # subselect returns empty set (for NULL and non-NULL left part) +select a, a in (select * from t2) from t1; +select a, a > any (select * from t2) from t1; +select a, a > all (select * from t2) from t1; +select a from t1 where a in (select * from t2); +select a from t1 where a > any (select * from t2); +select a from t1 where a > all (select * from t2); +select a from t1 where a in (select * from t2 group by a); +select a from t1 where a > any (select * from t2 group by a); +select a from t1 where a > all (select * from t2 group by a); + +insert into t2 values (1),(200); + +--echo # sebselect returns non-empty set without NULLs +select a, a in (select * from t2) from t1; +select a, a > any (select * from t2) from t1; +select a, a > all (select * from t2) from t1; +select a from t1 where a in (select * from t2); +select a from t1 where a > any (select * from t2); +select a from t1 where a > all (select * from t2); +select a from t1 where a in (select * from t2 group by a); +select a from t1 where a > any (select * from t2 group by a); +select a from t1 where a > all (select * from t2 group by a); + +drop table t2; +create table t2 (a int); +insert into t2 values (1),(NULL),(200); + +--echo # sebselect returns non-empty set with NULLs +select a, a in (select * from t2) from t1; +select a, a > any (select * from t2) from t1; +select a, a > all (select * from t2) from t1; +select a from t1 where a in (select * from t2); +select a from t1 where a > any (select * from t2); +select a from t1 where a > all (select * from t2); +select a from t1 where a in (select * from t2 group by a); +select a from t1 where a > any (select * from t2 group by a); +select a from t1 where a > all (select * from t2 group by a); + + +drop table t1, t2; + #LIMIT is not supported now create table t1 (a float); -- error ER_NOT_SUPPORTED_YET @@ -934,7 +983,7 @@ drop table t1,t2; # # correct ALL optimisation # -create table t2 (a int, b int); +create table t2 (a int, b int not null); create table t3 (a int); insert into t3 values (6),(7),(3); select * from t3 where a >= all (select b from t2); @@ -1817,6 +1866,9 @@ drop table t1; # Bug#11867 queries with ROW(,elems>) IN (SELECT DISTINCT <cols> FROM ...) # +set @tmp11867_optimizer_switch=@@optimizer_switch; +set optimizer_switch='semijoin_with_cache=off'; + CREATE TABLE t1 (one int, two int, flag char(1)); CREATE TABLE t2 (one int, two int, flag char(1)); INSERT INTO t1 VALUES(1,2,'Y'),(2,3,'Y'),(3,4,'Y'),(5,6,'N'),(7,8,'N'); @@ -1844,6 +1896,7 @@ explain extended SELECT one,two from t1 where ROW(one,two) IN (SELECT one,two FR explain extended SELECT one,two,ROW(one,two) IN (SELECT one,two FROM t2 WHERE flag = '0' group by one,two) as 'test' from t1; DROP TABLE t1,t2; +set optimizer_switch=@tmp11867_optimizer_switch; # # Bug#12392 where cond with IN predicate for rows and NULL values in table @@ -4722,6 +4775,87 @@ SELECT 1 as foo FROM t1 WHERE a < SOME DROP TABLE t1; +# +# LP BUG#823169 NULLs with ALL/ANY and maxmin optimization +# +CREATE TABLE t1 (a int(11), b varchar(1)); +INSERT INTO t1 VALUES (2,NULL),(5,'d'),(7,'g'); + +SELECT a FROM t1 WHERE b < ANY ( SELECT b FROM t1 GROUP BY b ); +SELECT a FROM t1 WHERE b < ANY ( SELECT b FROM t1 ); +SELECT a FROM t1 WHERE b > ANY ( SELECT b FROM t1 GROUP BY b ); +SELECT a FROM t1 WHERE b > ANY ( SELECT b FROM t1 ); +SELECT a FROM t1 WHERE b <= ANY ( SELECT b FROM t1 GROUP BY b ); +SELECT a FROM t1 WHERE b <= ANY ( SELECT b FROM t1 ); +SELECT a FROM t1 WHERE b >= ANY ( SELECT b FROM t1 GROUP BY b ); +SELECT a FROM t1 WHERE b >= ANY ( SELECT b FROM t1 ); +SELECT a FROM t1 WHERE b = ANY ( SELECT b FROM t1 ); +SELECT a FROM t1 WHERE b = ANY ( SELECT b FROM t1 GROUP BY b ); +SELECT a FROM t1 WHERE b <> ANY ( SELECT b FROM t1 ); +SELECT a FROM t1 WHERE b <> ANY ( SELECT b FROM t1 GROUP BY b ); + +SELECT a FROM t1 WHERE b < ALL ( SELECT b FROM t1 GROUP BY b ); +SELECT a FROM t1 WHERE b < ALL ( SELECT b FROM t1 ); +SELECT a FROM t1 WHERE b > ALL ( SELECT b FROM t1 GROUP BY b ); +SELECT a FROM t1 WHERE b > ALL ( SELECT b FROM t1 ); +SELECT a FROM t1 WHERE b <= ALL ( SELECT b FROM t1 GROUP BY b ); +SELECT a FROM t1 WHERE b <= ALL ( SELECT b FROM t1 ); +SELECT a FROM t1 WHERE b >= ALL ( SELECT b FROM t1 GROUP BY b ); +SELECT a FROM t1 WHERE b >= ALL ( SELECT b FROM t1 ); +SELECT a FROM t1 WHERE b = ALL ( SELECT b FROM t1 ); +SELECT a FROM t1 WHERE b = ALL ( SELECT b FROM t1 GROUP BY b ); +SELECT a FROM t1 WHERE b <> ALL ( SELECT b FROM t1 ); +SELECT a FROM t1 WHERE b <> ALL ( SELECT b FROM t1 GROUP BY b ); + +delete from t1; +INSERT INTO t1 VALUES (2,NULL),(5,'d'),(7,'g'); + +SELECT a FROM t1 WHERE b < ANY ( SELECT b FROM t1 GROUP BY b ); +SELECT a FROM t1 WHERE b < ANY ( SELECT b FROM t1 ); +SELECT a FROM t1 WHERE b > ANY ( SELECT b FROM t1 GROUP BY b ); +SELECT a FROM t1 WHERE b > ANY ( SELECT b FROM t1 ); +SELECT a FROM t1 WHERE b <= ANY ( SELECT b FROM t1 GROUP BY b ); +SELECT a FROM t1 WHERE b <= ANY ( SELECT b FROM t1 ); +SELECT a FROM t1 WHERE b >= ANY ( SELECT b FROM t1 GROUP BY b ); +SELECT a FROM t1 WHERE b >= ANY ( SELECT b FROM t1 ); +SELECT a FROM t1 WHERE b = ANY ( SELECT b FROM t1 ); +SELECT a FROM t1 WHERE b = ANY ( SELECT b FROM t1 GROUP BY b ); +SELECT a FROM t1 WHERE b <> ANY ( SELECT b FROM t1 ); +SELECT a FROM t1 WHERE b <> ANY ( SELECT b FROM t1 GROUP BY b ); + +SELECT a FROM t1 WHERE b < ALL ( SELECT b FROM t1 GROUP BY b ); +SELECT a FROM t1 WHERE b < ALL ( SELECT b FROM t1 ); +SELECT a FROM t1 WHERE b > ALL ( SELECT b FROM t1 GROUP BY b ); +SELECT a FROM t1 WHERE b > ALL ( SELECT b FROM t1 ); +SELECT a FROM t1 WHERE b <= ALL ( SELECT b FROM t1 GROUP BY b ); +SELECT a FROM t1 WHERE b <= ALL ( SELECT b FROM t1 ); +SELECT a FROM t1 WHERE b >= ALL ( SELECT b FROM t1 GROUP BY b ); +SELECT a FROM t1 WHERE b >= ALL ( SELECT b FROM t1 ); +SELECT a FROM t1 WHERE b = ALL ( SELECT b FROM t1 ); +SELECT a FROM t1 WHERE b = ALL ( SELECT b FROM t1 GROUP BY b ); +SELECT a FROM t1 WHERE b <> ALL ( SELECT b FROM t1 ); +SELECT a FROM t1 WHERE b <> ALL ( SELECT b FROM t1 GROUP BY b ); + +drop table t1; + +--echo # +--echo # Fix of lp:780386 (NULL left part with empty ALL subquery). +--echo # +CREATE TABLE t1 ( f11 int) ; +INSERT IGNORE INTO t1 VALUES (0),(0); + +CREATE TABLE t2 ( f3 int, f10 int, KEY (f10,f3)) ; +INSERT IGNORE INTO t2 VALUES (NULL,NULL),(5,0); + +DROP TABLE IF EXISTS t3; +CREATE TABLE t3 ( f3 int) ; +INSERT INTO t3 VALUES (0),(0); + +SELECT a1.f3 AS r FROM t2 AS a1 , t1 WHERE a1.f3 < ALL ( SELECT f3 FROM t3 WHERE f3 = 1 ) ; +DROP TABLE t1, t2, t3; + +--echo End of 5.2 tests + --echo # --echo # BUG#779885: Crash in eliminate_item_equal with materialization=on in --echo # maria-5.3 @@ -4748,20 +4882,29 @@ WHERE DROP TABLE t1,t2,t3; --echo # ---echo # Fix of LP BUG#780386 (NULL left part with empty ALL subquery). +--echo # BUG lp:813473: Wrong result with outer join + NOT IN subquery +--echo # This bug is a duplicate of Bug#11764086 whose test case is added below --echo # -CREATE TABLE t1 ( f11 int) ; -INSERT IGNORE INTO t1 VALUES (0),(0); -CREATE TABLE t2 ( f3 int, f10 int, KEY (f10,f3)) ; -INSERT IGNORE INTO t2 VALUES (NULL,NULL),(5,0); +CREATE TABLE t1 (c int) ; +INSERT INTO t1 VALUES (5),(6); -DROP TABLE IF EXISTS t3; -CREATE TABLE t3 ( f3 int) ; -INSERT INTO t3 VALUES (0),(0); +CREATE TABLE t2 (a int, b int) ; +INSERT INTO t2 VALUES (20,9),(20,9); -SELECT a1.f3 AS r FROM t2 AS a1 , t1 WHERE a1.f3 < ALL ( SELECT f3 FROM t3 WHERE f3 = 1 ) ; -DROP TABLE t1, t2, t3; +create table t3 (d int, e int); +insert into t3 values (2, 9), (3,10); + +EXPLAIN +SELECT t2.b , t1.c +FROM t2 LEFT JOIN t1 ON t1.c < 3 +WHERE (t2.b , t1.c) NOT IN (SELECT * from t3); + +SELECT t2.b , t1.c +FROM t2 LEFT JOIN t1 ON t1.c < 3 +WHERE (t2.b, t1.c) NOT IN (SELECT * from t3); + +drop table t1, t2, t3; --echo End of 5.3 tests @@ -4860,4 +5003,64 @@ eval explain $query; DROP TABLE t1,t2; +--echo # +--echo # lp:826279: assertion failure with GROUP BY a result of subquery +--echo # + +CREATE TABLE t1 (a int); +INSERT INTO t1 VALUES (0), (0); + +CREATE TABLE t2 (a int, b int, c int); +INSERT INTO t2 VALUES (10,7,0), (0,7,0); + +CREATE TABLE t3 (a int, b int); +INSERT INTO t3 VALUES (10,7), (0,7); + +SELECT SUM(DISTINCT b), + (SELECT t2.a FROM t1 JOIN t2 ON t2.c != 0 + WHERE t.a != 0 AND t2.a != 0) + FROM (SELECT * FROM t3) AS t +GROUP BY 2; + +SELECT SUM(DISTINCT b), + (SELECT t2.a FROM t1,t2 WHERE t.a != 0 or 1=2 LIMIT 1) + FROM (SELECT * FROM t3) AS t +GROUP BY 2; + +DROP TABLE t1,t2,t3; + +--echo # +--echo # Bug#12329653 +--echo # EXPLAIN, UNION, PREPARED STATEMENT, CRASH, SQL_FULL_GROUP_BY +--echo # + +CREATE TABLE t1(a1 int); +INSERT INTO t1 VALUES (1),(2); + +SELECT @@session.sql_mode INTO @old_sql_mode; +SET SESSION sql_mode='ONLY_FULL_GROUP_BY'; + +## First a simpler query, illustrating the transformation +## '1 < some (...)' => '1 < max(...)' +SELECT 1 FROM t1 WHERE 1 < SOME (SELECT a1 FROM t1); + +## The query which made the server crash. +PREPARE stmt FROM +'SELECT 1 UNION ALL +SELECT 1 FROM t1 +ORDER BY +(SELECT 1 FROM t1 AS t1_0 + WHERE 1 < SOME (SELECT a1 FROM t1) +)' ; + +--error ER_SUBQUERY_NO_1_ROW +EXECUTE stmt ; +--error ER_SUBQUERY_NO_1_ROW +EXECUTE stmt ; + +SET SESSION sql_mode=@old_sql_mode; + +DEALLOCATE PREPARE stmt; +DROP TABLE t1; + set optimizer_switch=@subselect_tmp; |