diff options
Diffstat (limited to 'mysql-test/main/subselect4.result')
| -rw-r--r-- | mysql-test/main/subselect4.result | 3238 |
1 files changed, 3238 insertions, 0 deletions
diff --git a/mysql-test/main/subselect4.result b/mysql-test/main/subselect4.result new file mode 100644 index 00000000..a041d76e --- /dev/null +++ b/mysql-test/main/subselect4.result @@ -0,0 +1,3238 @@ +drop table if exists t0,t1,t2,t3,t4,t5,t6; +drop view if exists v1, v2; +set @subselect4_tmp= @@optimizer_switch; +set optimizer_switch='semijoin=on,firstmatch=on,loosescan=on'; +set optimizer_switch='semijoin_with_cache=on'; +set optimizer_switch='mrr=on,mrr_sort_keys=on,index_condition_pushdown=on'; +# +# Bug #46791: Assertion failed:(table->key_read==0),function unknown +# function,file sql_base.cc +# +CREATE TABLE t1 (a INT, b INT, KEY(a)); +INSERT INTO t1 VALUES (1,1),(2,2); +CREATE TABLE t2 LIKE t1; +INSERT INTO t2 VALUES (1,1),(2,2); +CREATE TABLE t3 LIKE t1; +# should have 1 impossible where and 2 dependent subqueries +EXPLAIN +SELECT 1 FROM t1 +WHERE NOT EXISTS (SELECT 1 FROM t2 WHERE 1 = (SELECT MIN(t2.b) FROM t3)) +ORDER BY count(*); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 index NULL a 5 NULL 2 Using index +2 SUBQUERY t2 ALL NULL NULL NULL NULL 2 Using where +3 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL no matching row in const table +# should not crash the next statement +SELECT 1 FROM t1 +WHERE NOT EXISTS (SELECT 1 FROM t2 WHERE 1 = (SELECT MIN(t2.b) FROM t3)) +ORDER BY count(*); +1 +1 +# should not crash: the crash is caused by the previous statement +SELECT 1; +1 +1 +DROP TABLE t1,t2,t3; +# +# Bug #47106: Crash / segfault on adding EXPLAIN to a non-crashing +# query +# +CREATE TABLE t1 ( +a INT, +b INT, +PRIMARY KEY (a), +KEY b (b) +); +INSERT INTO t1 VALUES (1, 1), (2, 1); +CREATE TABLE t2 LIKE t1; +INSERT INTO t2 SELECT * FROM t1; +CREATE TABLE t3 LIKE t1; +INSERT INTO t3 SELECT * FROM t1; +# Should not crash. +# Should have 1 impossible where and 2 dependent subqs. +EXPLAIN +SELECT +(SELECT 1 FROM t1,t2 WHERE t2.b > t3.b) +FROM t3 WHERE 1 = 0 GROUP BY 1; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE +2 DEPENDENT SUBQUERY t1 index NULL PRIMARY 4 NULL 2 Using index +2 DEPENDENT SUBQUERY t2 ALL b NULL NULL NULL 2 Range checked for each record (index map: 0x2) +# should return 0 rows +SELECT +(SELECT 1 FROM t1,t2 WHERE t2.b > t3.b) +FROM t3 WHERE 1 = 0 GROUP BY 1; +(SELECT 1 FROM t1,t2 WHERE t2.b > t3.b) +DROP TABLE t1,t2,t3; +End of 5.0 tests. +CREATE TABLE t1 (col_int_nokey int(11) NOT NULL, col_varchar_nokey varchar(1) NOT NULL) engine=myisam; +INSERT INTO t1 VALUES (2,'s'),(0,'v'),(2,'s'); +CREATE TABLE t2 ( +pk int(11) NOT NULL AUTO_INCREMENT, +`col_int_key` int(11) NOT NULL, +col_varchar_key varchar(1) NOT NULL, +PRIMARY KEY (pk), +KEY `col_int_key` (`col_int_key`), +KEY `col_varchar_key` (`col_varchar_key`) +) ENGINE=MyISAM; +INSERT INTO t2 VALUES (4,10,'g'), (5,20,'v'); +SELECT t1.col_int_nokey,(SELECT MIN( t2_a.col_int_key ) FROM t2 t2_a, t2 t2_b, t1 t1_a WHERE t1_a.col_varchar_nokey = t2_b.col_varchar_key and t1.col_int_nokey ) as sub FROM t1; +col_int_nokey sub +2 10 +0 NULL +2 10 +SELECT t1.col_int_nokey,(SELECT MIN( t2_a.col_int_key ) +1 FROM t2 t2_a, t2 t2_b, t1 t1_a WHERE t1_a.col_varchar_nokey = t2_b.col_varchar_key and t1.col_int_nokey ) as sub FROM t1; +col_int_nokey sub +2 11 +0 NULL +2 11 +DROP TABLE t1,t2; +# +# Bug#54568: create view cause Assertion failed: 0, +# file .\item_subselect.cc, line 836 +# +EXPLAIN SELECT 1 LIKE ( 1 IN ( SELECT 1 ) ); +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE NULL NULL NULL NULL NULL NULL NULL No tables used +Warnings: +Note 1249 Select 2 was reduced during optimization +DESCRIBE SELECT 1 LIKE ( 1 IN ( SELECT 1 ) ); +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE NULL NULL NULL NULL NULL NULL NULL No tables used +Warnings: +Note 1249 Select 2 was reduced during optimization +# None of the below should crash +CREATE VIEW v1 AS SELECT 1 LIKE ( 1 IN ( SELECT 1 ) ); +CREATE VIEW v2 AS SELECT 1 LIKE '%' ESCAPE ( 1 IN ( SELECT 1 ) ); +DROP VIEW v1, v2; +# +# Bug#51070: Query with a NOT IN subquery predicate returns a wrong +# result set +# +CREATE TABLE t1 ( a INT, b INT ); +INSERT INTO t1 VALUES ( 1, NULL ), ( 2, NULL ); +CREATE TABLE t2 ( c INT, d INT ); +INSERT INTO t2 VALUES ( NULL, 3 ), ( NULL, 4 ); +CREATE TABLE t3 ( e INT, f INT ); +INSERT INTO t3 VALUES ( NULL, NULL ), ( NULL, NULL ); +CREATE TABLE t4 ( a INT ); +INSERT INTO t4 VALUES (1), (2), (3); +CREATE TABLE t5 ( a INT ); +INSERT INTO t5 VALUES (NULL), (2); +SET @old_optimizer_switch = @@session.optimizer_switch; +SET SESSION optimizer_switch = 'materialization=off,in_to_exists=on,semijoin=off'; +EXPLAIN +SELECT * FROM t1 WHERE ( a, b ) NOT IN ( SELECT c, d FROM t2 ); +id select_type table type possible_keys key key_len ref rows Extra +x PRIMARY x x x x x x x x +x DEPENDENT SUBQUERY x x x x x x x x +SELECT * FROM t1 WHERE ( a, b ) NOT IN ( SELECT c, d FROM t2 ); +a b +EXPLAIN +SELECT * FROM t1 WHERE ( a, b ) NOT IN ( SELECT c, d FROM t2 ) IS NULL; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 2 Using where +2 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 2 Using where +SELECT * FROM t1 WHERE ( a, b ) NOT IN ( SELECT c, d FROM t2 ) IS NULL; +a b +1 NULL +2 NULL +SELECT * FROM t1 WHERE ( a, b ) IN ( SELECT c, d FROM t2 ) IS NULL; +a b +1 NULL +2 NULL +SELECT * FROM t1 WHERE ( a, b ) NOT IN ( SELECT c, d FROM t2 ) IS UNKNOWN; +a b +1 NULL +2 NULL +SELECT * FROM t1 WHERE (( a, b ) NOT IN ( SELECT c, d FROM t2 )) IS UNKNOWN; +a b +1 NULL +2 NULL +SELECT * FROM t1 WHERE 1 = 1 AND ( a, b ) NOT IN ( SELECT c, d FROM t2 ); +a b +EXPLAIN +SELECT * FROM t1 WHERE ( a, b ) NOT IN ( SELECT e, f FROM t3 ); +id select_type table type possible_keys key key_len ref rows Extra +x PRIMARY x x x x x x x x +x DEPENDENT SUBQUERY x x x x x x x x +SELECT * FROM t1 WHERE ( a, b ) NOT IN ( SELECT e, f FROM t3 ); +a b +EXPLAIN +SELECT * FROM t2 WHERE ( c, d ) NOT IN ( SELECT a, b FROM t1 ); +id select_type table type possible_keys key key_len ref rows Extra +x PRIMARY x x x x x x x x +x DEPENDENT SUBQUERY x x x x x x x x +SELECT * FROM t2 WHERE ( c, d ) NOT IN ( SELECT a, b FROM t1 ); +c d +EXPLAIN +SELECT * FROM t3 WHERE ( e, f ) NOT IN ( SELECT c, d FROM t2 ); +id select_type table type possible_keys key key_len ref rows Extra +x PRIMARY x x x x x x x x +x DEPENDENT SUBQUERY x x x x x x x x +SELECT * FROM t3 WHERE ( e, f ) NOT IN ( SELECT c, d FROM t2 ); +e f +EXPLAIN +SELECT * FROM t2 WHERE ( c, d ) NOT IN ( SELECT e, f FROM t3 ); +id select_type table type possible_keys key key_len ref rows Extra +x PRIMARY x x x x x x x x +x DEPENDENT SUBQUERY x x x x x x x x +SELECT * FROM t2 WHERE ( c, d ) NOT IN ( SELECT e, f FROM t3 ); +c d +SELECT * FROM t1 WHERE ( a, b ) NOT IN +( SELECT c, d FROM t2 WHERE c = 1 AND c <> 1 ); +a b +1 NULL +2 NULL +SELECT * FROM t1 WHERE b NOT IN ( SELECT c FROM t2 WHERE c = 1 ); +a b +1 NULL +2 NULL +SELECT * FROM t1 WHERE NULL NOT IN ( SELECT c FROM t2 WHERE c = 1 AND c <> 1 ); +a b +1 NULL +2 NULL +SET SESSION optimizer_switch = @old_optimizer_switch; +DROP TABLE t1, t2, t3, t4, t5; +# +# Bug#58207: invalid memory reads when using default column value and +# tmptable needed +# +CREATE TABLE t(a VARCHAR(245) DEFAULT +'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa'); +INSERT INTO t VALUES (''),(''),(''),(''),(''),(''),(''),(''),(''),(''),(''); +SELECT * FROM (SELECT default(a) FROM t GROUP BY a) d; +default(a) +aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa +DROP TABLE t; +# +# LP BUG#1009187, MDEV-373, MYSQL bug#58628 +# Wrong result for a query with [NOT] IN subquery predicate if +# the left part of the predicate is explicit NULL +# +CREATE TABLE t1 (pk INT NOT NULL, i INT NOT NULL); +INSERT INTO t1 VALUES (0,10), (1,20), (2,30), (3,40); +CREATE TABLE t2a (pk INT NOT NULL, i INT NOT NULL, PRIMARY KEY(i,pk)); +INSERT INTO t2a VALUES (0,0), (1,1), (2,2), (3,3); +CREATE TABLE t2b (pk INT, i INT); +INSERT INTO t2b VALUES (0,0), (1,1), (2,2), (3,3); +CREATE TABLE t2c (pk INT NOT NULL, i INT NOT NULL); +INSERT INTO t2c VALUES (0,0), (1,1), (2,2), (3,3); +create index it2c on t2c (i,pk); +CREATE TABLE t2d (pk INT NOT NULL, i INT NOT NULL, PRIMARY KEY(i)); +INSERT INTO t2d VALUES (0,0), (1,1), (2,2), (3,3); +EXPLAIN +SELECT * FROM t1 WHERE NULL NOT IN (SELECT t2a.i FROM t2a WHERE t2a.pk = t1.pk); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 4 Using where +2 DEPENDENT SUBQUERY t2a unique_subquery PRIMARY PRIMARY 8 const,test.t1.pk 1 Using index; Using where; Full scan on NULL key +SELECT * FROM t1 WHERE NULL NOT IN (SELECT t2a.i FROM t2a WHERE t2a.pk = t1.pk); +pk i +SELECT * FROM t1 WHERE 1+NULL NOT IN (SELECT t2a.i FROM t2a WHERE t2a.pk = t1.pk); +pk i +SELECT * FROM t1 WHERE NULL IN (SELECT t2a.i FROM t2a WHERE t2a.pk = t1.pk) IS UNKNOWN; +pk i +0 10 +1 20 +2 30 +3 40 +SELECT t1.pk, NULL NOT IN (SELECT t2a.i FROM t2a WHERE t2a.pk = t1.pk) FROM t1; +pk NULL NOT IN (SELECT t2a.i FROM t2a WHERE t2a.pk = t1.pk) +0 NULL +1 NULL +2 NULL +3 NULL +EXPLAIN +SELECT * FROM t1 WHERE NULL NOT IN (SELECT t2b.i FROM t2b WHERE t2b.pk = t1.pk); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 4 Using where +2 DEPENDENT SUBQUERY t2b ALL NULL NULL NULL NULL 4 Using where +SELECT * FROM t1 WHERE NULL NOT IN (SELECT t2b.i FROM t2b WHERE t2b.pk = t1.pk); +pk i +SELECT * FROM t1 WHERE NULL IN (SELECT t2b.i FROM t2b WHERE t2b.pk = t1.pk) IS UNKNOWN; +pk i +0 10 +1 20 +2 30 +3 40 +SELECT t1.pk, NULL NOT IN (SELECT t2b.i FROM t2b WHERE t2b.pk = t1.pk) FROM t1; +pk NULL NOT IN (SELECT t2b.i FROM t2b WHERE t2b.pk = t1.pk) +0 NULL +1 NULL +2 NULL +3 NULL +EXPLAIN +SELECT * FROM t1 WHERE NULL NOT IN (SELECT t2c.i FROM t2c WHERE t2c.pk = t1.pk); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 4 Using where +2 DEPENDENT SUBQUERY t2c index_subquery it2c it2c 8 const,test.t1.pk 2 Using index; Using where; Full scan on NULL key +SELECT * FROM t1 WHERE NULL NOT IN (SELECT t2c.i FROM t2c WHERE t2c.pk = t1.pk); +pk i +SELECT * FROM t1 WHERE NULL IN (SELECT t2c.i FROM t2c WHERE t2c.pk = t1.pk) IS UNKNOWN; +pk i +0 10 +1 20 +2 30 +3 40 +SELECT t1.pk, NULL NOT IN (SELECT t2c.i FROM t2c WHERE t2c.pk = t1.pk) FROM t1; +pk NULL NOT IN (SELECT t2c.i FROM t2c WHERE t2c.pk = t1.pk) +0 NULL +1 NULL +2 NULL +3 NULL +EXPLAIN +SELECT * FROM t1 WHERE NULL NOT IN (SELECT t2d.i FROM t2d WHERE t2d.pk = t1.pk); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 4 Using where +2 DEPENDENT SUBQUERY t2d const PRIMARY PRIMARY 4 const 1 Using where; Full scan on NULL key +SELECT * FROM t1 WHERE NULL NOT IN (SELECT t2d.i FROM t2d WHERE t2d.pk = t1.pk); +pk i +SELECT * FROM t1 WHERE NULL IN (SELECT t2d.i FROM t2d WHERE t2d.pk = t1.pk) IS UNKNOWN; +pk i +0 10 +1 20 +2 30 +3 40 +SELECT t1.pk, NULL NOT IN (SELECT t2d.i FROM t2d WHERE t2d.pk = t1.pk) FROM t1; +pk NULL NOT IN (SELECT t2d.i FROM t2d WHERE t2d.pk = t1.pk) +0 NULL +1 NULL +2 NULL +3 NULL +EXPLAIN +SELECT * FROM t1 WHERE (NULL, 1) NOT IN (SELECT t2a.i, t2a.pk FROM t2a WHERE t2a.pk = t1.pk); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 4 Using where +2 DEPENDENT SUBQUERY t2a unique_subquery PRIMARY PRIMARY 8 const,test.t1.pk 1 Using index; Using where; Full scan on NULL key +SELECT * FROM t1 WHERE (NULL, 1) NOT IN (SELECT t2a.i, t2a.pk FROM t2a WHERE t2a.pk = t1.pk); +pk i +0 10 +2 30 +3 40 +SELECT (NULL, 1) NOT IN (SELECT t2a.i, t2a.pk FROM t2a WHERE t2a.pk = t1.pk) as exp from t1; +exp +1 +NULL +1 +1 +EXPLAIN +SELECT * FROM t1 WHERE (NULL, 1) NOT IN (SELECT t2b.i, t2b.pk FROM t2b WHERE t2b.pk = t1.pk); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 4 Using where +2 DEPENDENT SUBQUERY t2b ALL NULL NULL NULL NULL 4 Using where +SELECT * FROM t1 WHERE (NULL, 1) NOT IN (SELECT t2b.i, t2b.pk FROM t2b WHERE t2b.pk = t1.pk); +pk i +0 10 +2 30 +3 40 +SELECT (NULL, 1) NOT IN (SELECT t2b.i, t2b.pk FROM t2b WHERE t2b.pk = t1.pk) as exp from t1; +exp +1 +NULL +1 +1 +EXPLAIN +SELECT * FROM t1 WHERE (NULL, 1) NOT IN (SELECT t2c.i, t2c.pk FROM t2c WHERE t2c.pk = t1.pk); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 4 Using where +2 DEPENDENT SUBQUERY t2c index_subquery it2c it2c 8 const,test.t1.pk 2 Using index; Using where; Full scan on NULL key +SELECT * FROM t1 WHERE (NULL, 1) NOT IN (SELECT t2c.i, t2c.pk FROM t2c WHERE t2c.pk = t1.pk); +pk i +0 10 +2 30 +3 40 +SELECT (NULL, 1) NOT IN (SELECT t2c.i, t2c.pk FROM t2c WHERE t2c.pk = t1.pk) as exp from t1; +exp +1 +NULL +1 +1 +EXPLAIN +SELECT * FROM t1 WHERE (NULL, 1) NOT IN (SELECT t2d.i, t2d.pk FROM t2d WHERE t2d.pk = t1.pk); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 4 Using where +2 DEPENDENT SUBQUERY t2d const PRIMARY PRIMARY 4 const 1 Using where; Full scan on NULL key +SELECT * FROM t1 WHERE (NULL, 1) NOT IN (SELECT t2d.i, t2d.pk FROM t2d WHERE t2d.pk = t1.pk); +pk i +0 10 +2 30 +3 40 +SELECT (NULL, 1) NOT IN (SELECT t2d.i, t2d.pk FROM t2d WHERE t2d.pk = t1.pk) as exp from t1; +exp +1 +NULL +1 +1 +drop table t1, t2a, t2b, t2c, t2d; +# +# End of 5.1 tests. +# +# +# BUG#46743 "Azalea processing correlated, aggregate SELECT +# subqueries incorrectly" +# +CREATE TABLE t1 (c int); +INSERT INTO t1 VALUES (NULL); +CREATE TABLE t2 (d int , KEY (d)); +INSERT INTO t2 VALUES (NULL),(NULL); +0 rows in subquery +SELECT 1 AS RESULT FROM t2,t1 WHERE d = c; +RESULT +base query +SELECT (SELECT 1 FROM t2 WHERE d = c) AS RESULT FROM t1 ; +RESULT +NULL +EXPLAIN EXTENDED SELECT (SELECT 1 FROM t2 WHERE d = c) AS RESULT FROM t1 ; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 PRIMARY t1 system NULL NULL NULL NULL 1 100.00 +2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables +Warnings: +Note 1276 Field or reference 'test.t1.c' of SELECT #2 was resolved in SELECT #1 +Note 1003 /* select#1 */ select (/* select#2 */ select 1 from `test`.`t2` where 0) AS `RESULT` from dual +first equivalent variant +SELECT (SELECT 1 FROM t2 WHERE d = IFNULL(c,NULL)) AS RESULT FROM t1 GROUP BY c ; +RESULT +NULL +EXPLAIN EXTENDED SELECT (SELECT 1 FROM t2 WHERE d = IFNULL(c,NULL)) AS RESULT FROM t1 GROUP BY c; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 PRIMARY t1 system NULL NULL NULL NULL 1 100.00 +2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables +Warnings: +Note 1276 Field or reference 'test.t1.c' of SELECT #2 was resolved in SELECT #1 +Note 1003 /* select#1 */ select (/* select#2 */ select 1 from `test`.`t2` where 0) AS `RESULT` from dual group by NULL +second equivalent variant +SELECT (SELECT 1 FROM t2 WHERE d = c) AS RESULT FROM t1 GROUP BY c ; +RESULT +NULL +EXPLAIN EXTENDED SELECT (SELECT 1 FROM t2 WHERE d = c) AS RESULT FROM t1 GROUP BY c ; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 PRIMARY t1 system NULL NULL NULL NULL 1 100.00 +2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables +Warnings: +Note 1276 Field or reference 'test.t1.c' of SELECT #2 was resolved in SELECT #1 +Note 1003 /* select#1 */ select (/* select#2 */ select 1 from `test`.`t2` where 0) AS `RESULT` from dual group by NULL +DROP TABLE t1,t2; +# +# BUG#45928 "Differing query results depending on MRR and +# engine_condition_pushdown settings" +# +CREATE TABLE `t1` ( +`pk` int(11) NOT NULL AUTO_INCREMENT, +`time_nokey` time NOT NULL, +`varchar_key` varchar(1) NOT NULL, +`varchar_nokey` varchar(1) NOT NULL, +PRIMARY KEY (`pk`), +KEY `varchar_key` (`varchar_key`) +) AUTO_INCREMENT=12 DEFAULT CHARSET=latin1; +INSERT INTO `t1` VALUES (10,'00:00:00','i','i'),(11,'00:00:00','',''); +set @old_optimizer_switch = @@session.optimizer_switch; +SET SESSION OPTIMIZER_SWITCH = 'materialization=off,semijoin=off,loosescan=off,firstmatch=off,mrr=on,engine_condition_pushdown=on'; +Warnings: +Warning 1681 'engine_condition_pushdown=on' is deprecated and will be removed in a future release +SELECT `time_nokey` G1 FROM t1 WHERE ( `varchar_nokey` , `varchar_key` ) IN ( +SELECT `varchar_nokey` , `varchar_nokey` ) AND `varchar_key` >= 'c' HAVING G1 ORDER +BY `pk` ; +G1 +set @@session.optimizer_switch = @old_optimizer_switch; +DROP TABLE t1; +# +# During work with BUG#45863 I had problems with a query that was +# optimized differently in regular and prepared mode. +# Because there was a bug in one of the selected strategies, I became +# aware of the problem. Adding an EXPLAIN query to catch this. +DROP TABLE IF EXISTS t1, t2, t3; +CREATE TABLE t1 +(EMPNUM CHAR(3) NOT NULL, +EMPNAME CHAR(20), +GRADE DECIMAL(4), +CITY CHAR(15)); +CREATE TABLE t2 +(PNUM CHAR(3) NOT NULL, +PNAME CHAR(20), +PTYPE CHAR(6), +BUDGET DECIMAL(9), +CITY CHAR(15)); +CREATE TABLE t3 +(EMPNUM CHAR(3) NOT NULL, +PNUM CHAR(3) NOT NULL, +HOURS DECIMAL(5)); +INSERT INTO t1 VALUES ('E1','Alice',12,'Deale'); +INSERT INTO t1 VALUES ('E2','Betty',10,'Vienna'); +INSERT INTO t1 VALUES ('E3','Carmen',13,'Vienna'); +INSERT INTO t1 VALUES ('E4','Don',12,'Deale'); +INSERT INTO t1 VALUES ('E5','Ed',13,'Akron'); +INSERT INTO t2 VALUES ('P1','MXSS','Design',10000,'Deale'); +INSERT INTO t2 VALUES ('P2','CALM','Code',30000,'Vienna'); +INSERT INTO t2 VALUES ('P3','SDP','Test',30000,'Tampa'); +INSERT INTO t2 VALUES ('P4','SDP','Design',20000,'Deale'); +INSERT INTO t2 VALUES ('P5','IRM','Test',10000,'Vienna'); +INSERT INTO t2 VALUES ('P6','PAYR','Design',50000,'Deale'); +INSERT INTO t3 VALUES ('E1','P1',40); +INSERT INTO t3 VALUES ('E1','P2',20); +INSERT INTO t3 VALUES ('E1','P3',80); +INSERT INTO t3 VALUES ('E1','P4',20); +INSERT INTO t3 VALUES ('E1','P5',12); +INSERT INTO t3 VALUES ('E1','P6',12); +INSERT INTO t3 VALUES ('E2','P1',40); +INSERT INTO t3 VALUES ('E2','P2',80); +INSERT INTO t3 VALUES ('E3','P2',20); +INSERT INTO t3 VALUES ('E4','P2',20); +INSERT INTO t3 VALUES ('E4','P4',40); +INSERT INTO t3 VALUES ('E4','P5',80); +SET @old_optimizer_switch = @@session.optimizer_switch; +SET @old_join_cache_level = @@session.join_cache_level; +SET SESSION optimizer_switch = 'firstmatch=on,loosescan=on,materialization=on,in_to_exists=off,semijoin=on'; +SET SESSION join_cache_level = 1; +CREATE UNIQUE INDEX t1_IDX ON t1(EMPNUM); +EXPLAIN SELECT EMPNAME +FROM t1 +WHERE EMPNUM IN +(SELECT EMPNUM +FROM t3 +WHERE PNUM IN +(SELECT PNUM +FROM t2 +WHERE PTYPE = 'Design')); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ALL t1_IDX NULL NULL NULL 5 +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 3 func 1 +2 MATERIALIZED t2 ALL NULL NULL NULL NULL 6 Using where +2 MATERIALIZED t3 ALL NULL NULL NULL NULL 12 Using where; Using join buffer (flat, BNL join) +PREPARE stmt FROM "EXPLAIN SELECT EMPNAME +FROM t1 +WHERE EMPNUM IN + (SELECT EMPNUM + FROM t3 + WHERE PNUM IN + (SELECT PNUM + FROM t2 + WHERE PTYPE = 'Design'))"; +EXECUTE stmt; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ALL t1_IDX NULL NULL NULL 5 +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 3 func 1 +2 MATERIALIZED t2 ALL NULL NULL NULL NULL 6 Using where +2 MATERIALIZED t3 ALL NULL NULL NULL NULL 12 Using where; Using join buffer (flat, BNL join) +EXECUTE stmt; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ALL t1_IDX NULL NULL NULL 5 +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 3 func 1 +2 MATERIALIZED t2 ALL NULL NULL NULL NULL 6 Using where +2 MATERIALIZED t3 ALL NULL NULL NULL NULL 12 Using where; Using join buffer (flat, BNL join) +DEALLOCATE PREPARE stmt; +DROP INDEX t1_IDX ON t1; +CREATE INDEX t1_IDX ON t1(EMPNUM); +EXPLAIN SELECT EMPNAME +FROM t1 +WHERE EMPNUM IN +(SELECT EMPNUM +FROM t3 +WHERE PNUM IN +(SELECT PNUM +FROM t2 +WHERE PTYPE = 'Design')); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ALL t1_IDX NULL NULL NULL 5 +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 3 func 1 +2 MATERIALIZED t2 ALL NULL NULL NULL NULL 6 Using where +2 MATERIALIZED t3 ALL NULL NULL NULL NULL 12 Using where; Using join buffer (flat, BNL join) +PREPARE stmt FROM "EXPLAIN SELECT EMPNAME +FROM t1 +WHERE EMPNUM IN + (SELECT EMPNUM + FROM t3 + WHERE PNUM IN + (SELECT PNUM + FROM t2 + WHERE PTYPE = 'Design'))"; +EXECUTE stmt; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ALL t1_IDX NULL NULL NULL 5 +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 3 func 1 +2 MATERIALIZED t2 ALL NULL NULL NULL NULL 6 Using where +2 MATERIALIZED t3 ALL NULL NULL NULL NULL 12 Using where; Using join buffer (flat, BNL join) +EXECUTE stmt; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ALL t1_IDX NULL NULL NULL 5 +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 3 func 1 +2 MATERIALIZED t2 ALL NULL NULL NULL NULL 6 Using where +2 MATERIALIZED t3 ALL NULL NULL NULL NULL 12 Using where; Using join buffer (flat, BNL join) +DEALLOCATE PREPARE stmt; +DROP INDEX t1_IDX ON t1; +EXPLAIN SELECT EMPNAME +FROM t1 +WHERE EMPNUM IN +(SELECT EMPNUM +FROM t3 +WHERE PNUM IN +(SELECT PNUM +FROM t2 +WHERE PTYPE = 'Design')); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 5 +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 3 func 1 +2 MATERIALIZED t2 ALL NULL NULL NULL NULL 6 Using where +2 MATERIALIZED t3 ALL NULL NULL NULL NULL 12 Using where; Using join buffer (flat, BNL join) +PREPARE stmt FROM "EXPLAIN SELECT EMPNAME +FROM t1 +WHERE EMPNUM IN + (SELECT EMPNUM + FROM t3 + WHERE PNUM IN + (SELECT PNUM + FROM t2 + WHERE PTYPE = 'Design'))"; +EXECUTE stmt; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 5 +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 3 func 1 +2 MATERIALIZED t2 ALL NULL NULL NULL NULL 6 Using where +2 MATERIALIZED t3 ALL NULL NULL NULL NULL 12 Using where; Using join buffer (flat, BNL join) +EXECUTE stmt; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 5 +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 3 func 1 +2 MATERIALIZED t2 ALL NULL NULL NULL NULL 6 Using where +2 MATERIALIZED t3 ALL NULL NULL NULL NULL 12 Using where; Using join buffer (flat, BNL join) +DEALLOCATE PREPARE stmt; +SET SESSION optimizer_switch = @old_optimizer_switch; +SET SESSION join_cache_level = @old_join_cache_level; +DROP TABLE t1, t2, t3; +# +# BUG#45221 Query SELECT pk FROM C WHERE pk IN (SELECT int_key) failing +# +CREATE TABLE t1 ( +i1_key INT, +i2 INT, +i3 INT, +KEY i1_index (i1_key) +); +INSERT INTO t1 VALUES (9,1,2), (9,2,1); +CREATE TABLE t2 ( +pk INT NOT NULL, +i1 INT, +PRIMARY KEY (pk) +); +INSERT INTO t2 VALUES (9,1); +# Enable Index condition pushdown +set @old_icp=@@optimizer_switch; +SET SESSION optimizer_switch="engine_condition_pushdown=on"; +Warnings: +Warning 1681 'engine_condition_pushdown=on' is deprecated and will be removed in a future release + +SELECT pk +FROM t2 +WHERE +pk IN ( +SELECT i1_key +FROM t1 +WHERE t1.i2 < t1.i3 XOR t2.i1 > 1 +ORDER BY t1.i2 desc); +pk +9 +# Restore old value for Index condition pushdown +SET SESSION optimizer_switch=@old_icp; +DROP TABLE t1,t2; +# +# End of 5.3 tests. +# +# +# Bug#53236 Segfault in DTCollation::set(DTCollation&) +# +CREATE TABLE t1 ( +pk INTEGER AUTO_INCREMENT, +col_varchar VARCHAR(1), +PRIMARY KEY (pk) +) +; +INSERT INTO t1 (col_varchar) +VALUES +('w'), +('m') +; +SELECT table1.pk +FROM ( t1 AS table1 JOIN t1 AS table2 ON (table1.col_varchar = +table2.col_varchar) ) +WHERE ( 1, 2 ) IN ( SELECT SUBQUERY1_t1.pk AS SUBQUERY1_field1, +SUBQUERY1_t1.pk AS SUBQUERY1_field2 +FROM ( t1 AS SUBQUERY1_t1 JOIN t1 AS SUBQUERY1_t2 +ON (SUBQUERY1_t2.col_varchar = +SUBQUERY1_t1.col_varchar) ) ) +; +pk +drop table t1; +# +# BUG#716293: "Range checked for each record" is not used if condition refers to outside of subquery +# +create table t1 (a int); +insert into t1 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); +create table t2 (a int, b int, `filler` char(200), key(a), key (b)); +insert into t2 +select A.a + 10*B.a + 100 * C.a, A.a + 10*B.a + 100 * C.a, 'filler' from t1 A, t1 B, t1 C; +# The following must use "Range checked for each record" for table B +explain +select a, +(select sum(X.a+B.b) from t1 X, t2 B where B.a=A.a or B.b=A.a) +from t1 A; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY A ALL NULL NULL NULL NULL 10 +2 DEPENDENT SUBQUERY X ALL NULL NULL NULL NULL 10 +2 DEPENDENT SUBQUERY B ALL a,b NULL NULL NULL 1000 Range checked for each record (index map: 0x3) +drop table t1, t2; +# +# BUG#723822: Crash in get_constant_key_infix with EXISTS ( SELECT .. DISTINCT ) +# +CREATE TABLE t1 ( f1 int(11), f3 varchar(1)) ; +INSERT INTO t1 VALUES ('8','c'),('5','f'); +ALTER TABLE t1 ADD KEY (f3,f1); +CREATE TABLE t2 ( f4 varchar(1)) ; +INSERT INTO t2 VALUES ('f'),('d'); +SELECT * FROM t2 +WHERE EXISTS ( +SELECT DISTINCT f3 +FROM t1 +WHERE f3 <= t2.f4 +); +f4 +f +d +drop table t1,t2; +# +# LP BUG#718763 Second crash in select_describe() and materialization +# +CREATE TABLE t1 ( f1 int(11), f3 int(11), f10 varchar(1), KEY (f3)) ; +INSERT INTO t1 VALUES ('28','6','m'),('29','4','c'); +CREATE TABLE t2 (f11 varchar(1)) ; +INSERT INTO t2 VALUES ('f'),('d'); +SET @old_optimizer_switch = @@session.optimizer_switch; +SET SESSION optimizer_switch = 'materialization=on,in_to_exists=off'; +EXPLAIN +SELECT * FROM t1 +WHERE f3 = ( +SELECT t1.f3 FROM t1 +WHERE ( t1.f10 ) IN ( SELECT f11 FROM t2 GROUP BY f11 )); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables +2 SUBQUERY t1 ALL NULL NULL NULL NULL 2 +2 SUBQUERY <subquery3> eq_ref distinct_key distinct_key 4 func 1 +3 MATERIALIZED t2 ALL NULL NULL NULL NULL 2 +SELECT * FROM t1 +WHERE f3 = ( +SELECT t1.f3 FROM t1 +WHERE ( t1.f10 ) IN ( SELECT f11 FROM t2 GROUP BY f11 )); +f1 f3 f10 +EXPLAIN +SELECT * FROM t1 +WHERE f3 = ( +SELECT f3 FROM t1 +WHERE ( f10, f10 ) IN ( SELECT f11, f11 FROM t2 GROUP BY f11 )); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables +2 SUBQUERY t1 ALL NULL NULL NULL NULL 2 +2 SUBQUERY <subquery3> eq_ref distinct_key distinct_key 8 func,func 1 +3 MATERIALIZED t2 ALL NULL NULL NULL NULL 2 +SELECT * FROM t1 +WHERE f3 = ( +SELECT f3 FROM t1 +WHERE ( f10, f10 ) IN ( SELECT f11, f11 FROM t2 GROUP BY f11 )); +f1 f3 f10 +SET @@optimizer_switch = 'materialization=on,in_to_exists=off,semijoin=off'; +EXPLAIN +SELECT * FROM t1 +WHERE f3 = ( +SELECT t1.f3 FROM t1 +WHERE ( t1.f10 ) IN ( SELECT max(f11) FROM t2 GROUP BY f11 )); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ref f3 f3 5 const 0 Using where +2 SUBQUERY t1 ALL NULL NULL NULL NULL 2 Using where +3 MATERIALIZED t2 ALL NULL NULL NULL NULL 2 Using temporary +SELECT * FROM t1 +WHERE f3 = ( +SELECT t1.f3 FROM t1 +WHERE ( t1.f10 ) IN ( SELECT max(f11) FROM t2 GROUP BY f11 )); +f1 f3 f10 +EXPLAIN +SELECT * FROM t1 +WHERE f3 = ( +SELECT f3 FROM t1 +WHERE ( f10, f10 ) IN ( SELECT max(f11), f11 FROM t2 GROUP BY f11 )); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ref f3 f3 5 const 0 Using where +2 SUBQUERY t1 ALL NULL NULL NULL NULL 2 Using where +3 MATERIALIZED t2 ALL NULL NULL NULL NULL 2 Using temporary +SELECT * FROM t1 +WHERE f3 = ( +SELECT f3 FROM t1 +WHERE ( f10, f10 ) IN ( SELECT max(f11), f11 FROM t2 GROUP BY f11 )); +f1 f3 f10 +SET SESSION optimizer_switch = @old_optimizer_switch; +drop table t1,t2; +# +# LP BUG#715738: Wrong result with implicit grouping and empty result set +# +CREATE TABLE t1 (f1 int, f2 int); +CREATE TABLE t2 (f3 int, f4 int not null, PRIMARY KEY (f3)); +set @save_optimizer_switch=@@optimizer_switch; +SET @@optimizer_switch = 'materialization=on,in_to_exists=off,semijoin=off'; +EXPLAIN +SELECT * FROM t1 WHERE (2, 0) NOT IN (SELECT f3, min(f4) FROM t2); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE +2 MATERIALIZED NULL NULL NULL NULL NULL NULL NULL no matching row in const table +SELECT * FROM t1 WHERE (2, 0) NOT IN (SELECT f3, min(f4) FROM t2); +f1 f2 +EXPLAIN +SELECT * FROM t1 WHERE (2, 0) NOT IN (SELECT f3+f4, min(f4) FROM t2); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE +2 MATERIALIZED NULL NULL NULL NULL NULL NULL NULL no matching row in const table +SELECT * FROM t1 WHERE (2, 0) NOT IN (SELECT f3+f4, min(f4) FROM t2); +f1 f2 +EXPLAIN +SELECT * FROM t1 WHERE (2, 0) NOT IN (SELECT f3, min(f4)+max(f4) FROM t2); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE +2 MATERIALIZED NULL NULL NULL NULL NULL NULL NULL no matching row in const table +SELECT * FROM t1 WHERE (2, 0) NOT IN (SELECT f3, min(f4)+max(f4) FROM t2); +f1 f2 +EXPLAIN +SELECT (2, 0) NOT IN (SELECT f3, min(f4) FROM t2) as not_in; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY NULL NULL NULL NULL NULL NULL NULL No tables used +2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL no matching row in const table +SELECT (2, 0) NOT IN (SELECT f3, min(f4) FROM t2) as not_in; +not_in +NULL +EXPLAIN +SELECT * FROM t1 WHERE (2, 0) NOT IN (SELECT f3, count(f4) FROM t2); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE +2 MATERIALIZED NULL NULL NULL NULL NULL NULL NULL no matching row in const table +SELECT * FROM t1 WHERE (2, 0) NOT IN (SELECT f3, count(f4) FROM t2); +f1 f2 +EXPLAIN +SELECT * FROM t1 WHERE (2, 0) NOT IN (SELECT f3, f3 + count(f4) FROM t2); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE +2 MATERIALIZED NULL NULL NULL NULL NULL NULL NULL no matching row in const table +SELECT * FROM t1 WHERE (2, 0) NOT IN (SELECT f3, f3 + count(f4) FROM t2); +f1 f2 +EXPLAIN +SELECT (2, 0) NOT IN (SELECT f3, count(f4) FROM t2) as not_in; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY NULL NULL NULL NULL NULL NULL NULL No tables used +2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL no matching row in const table +SELECT (2, 0) NOT IN (SELECT f3, count(f4) FROM t2) as not_in; +not_in +NULL +EXPLAIN +SELECT (2, 0) NOT IN (SELECT f3, count(f4) FROM t2 HAVING max(f4) > 7) as not_in; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY NULL NULL NULL NULL NULL NULL NULL No tables used +2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL no matching row in const table +SELECT (2, 0) NOT IN (SELECT f3, count(f4) FROM t2 HAVING max(f4) > 7) as not_in; +not_in +1 +EXPLAIN +SELECT (2, 0) NOT IN (SELECT f3, count(f4) FROM t2 HAVING max(f4) is null) as not_in; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY NULL NULL NULL NULL NULL NULL NULL No tables used +2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL no matching row in const table +SELECT (2, 0) NOT IN (SELECT f3, count(f4) FROM t2 HAVING max(f4) is null) as not_in; +not_in +NULL +EXPLAIN +SELECT (2, 0) NOT IN (SELECT max(f3+f3), count(f4) FROM t2) as not_in; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY NULL NULL NULL NULL NULL NULL NULL No tables used +2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL no matching row in const table +SELECT (2, 0) NOT IN (SELECT max(f3+f3), count(f4) FROM t2) as not_in; +not_in +NULL +EXPLAIN +SELECT (2, 0) NOT IN (SELECT max(f3+f3), count(f4)+f3 FROM t2) as not_in; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY NULL NULL NULL NULL NULL NULL NULL No tables used +2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL no matching row in const table +SELECT (2, 0) NOT IN (SELECT max(f3+f3), count(f4)+f3 FROM t2) as not_in; +not_in +NULL +EXPLAIN +SELECT * FROM t1 WHERE (2, 0) NOT IN (SELECT min(f3)+f3, min(f4)+f3+max(f4) FROM t2); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE +2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL No matching min/max row +SELECT * FROM t1 WHERE (2, 0) NOT IN (SELECT min(f3)+f3, min(f4)+f3+max(f4) FROM t2); +f1 f2 +SET @@optimizer_switch = 'materialization=off,in_to_exists=on,semijoin=off'; +EXPLAIN +SELECT * FROM t1 WHERE (2, 0) NOT IN (SELECT f3, min(f4) FROM t2); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE +2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL no matching row in const table +SELECT * FROM t1 WHERE (2, 0) NOT IN (SELECT f3, min(f4) FROM t2); +f1 f2 +EXPLAIN +SELECT * FROM t1 WHERE (2, 0) NOT IN (SELECT f3+f4, min(f4) FROM t2); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE +2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL no matching row in const table +SELECT * FROM t1 WHERE (2, 0) NOT IN (SELECT f3+f4, min(f4) FROM t2); +f1 f2 +EXPLAIN +SELECT * FROM t1 WHERE (2, 0) NOT IN (SELECT f3, min(f4)+max(f4) FROM t2); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE +2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL no matching row in const table +SELECT * FROM t1 WHERE (2, 0) NOT IN (SELECT f3, min(f4)+max(f4) FROM t2); +f1 f2 +EXPLAIN +SELECT (2, 0) NOT IN (SELECT f3, min(f4) FROM t2) as not_in; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY NULL NULL NULL NULL NULL NULL NULL No tables used +2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL no matching row in const table +SELECT (2, 0) NOT IN (SELECT f3, min(f4) FROM t2) as not_in; +not_in +NULL +EXPLAIN +SELECT * FROM t1 WHERE (2, 0) NOT IN (SELECT f3, count(f4) FROM t2); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE +2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL no matching row in const table +SELECT * FROM t1 WHERE (2, 0) NOT IN (SELECT f3, count(f4) FROM t2); +f1 f2 +EXPLAIN +SELECT * FROM t1 WHERE (2, 0) NOT IN (SELECT f3, f3 + count(f4) FROM t2); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE +2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL no matching row in const table +SELECT * FROM t1 WHERE (2, 0) NOT IN (SELECT f3, f3 + count(f4) FROM t2); +f1 f2 +EXPLAIN +SELECT (2, 0) NOT IN (SELECT f3, count(f4) FROM t2) as not_in; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY NULL NULL NULL NULL NULL NULL NULL No tables used +2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL no matching row in const table +SELECT (2, 0) NOT IN (SELECT f3, count(f4) FROM t2) as not_in; +not_in +NULL +EXPLAIN +SELECT (2, 0) NOT IN (SELECT f3, count(f4) FROM t2 HAVING max(f4) > 7) as not_in; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY NULL NULL NULL NULL NULL NULL NULL No tables used +2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL no matching row in const table +SELECT (2, 0) NOT IN (SELECT f3, count(f4) FROM t2 HAVING max(f4) > 7) as not_in; +not_in +1 +EXPLAIN +SELECT (2, 0) NOT IN (SELECT f3, count(f4) FROM t2 HAVING max(f4) is null) as not_in; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY NULL NULL NULL NULL NULL NULL NULL No tables used +2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL no matching row in const table +SELECT (2, 0) NOT IN (SELECT f3, count(f4) FROM t2 HAVING max(f4) is null) as not_in; +not_in +NULL +EXPLAIN +SELECT (2, 0) NOT IN (SELECT max(f3+f3), count(f4) FROM t2) as not_in; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY NULL NULL NULL NULL NULL NULL NULL No tables used +2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL no matching row in const table +SELECT (2, 0) NOT IN (SELECT max(f3+f3), count(f4) FROM t2) as not_in; +not_in +NULL +EXPLAIN +SELECT (2, 0) NOT IN (SELECT max(f3+f3), count(f4)+f3 FROM t2) as not_in; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY NULL NULL NULL NULL NULL NULL NULL No tables used +2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL no matching row in const table +SELECT (2, 0) NOT IN (SELECT max(f3+f3), count(f4)+f3 FROM t2) as not_in; +not_in +NULL +EXPLAIN +SELECT * FROM t1 WHERE (2, 0) NOT IN (SELECT min(f3)+f3, min(f4)+f3+max(f4) FROM t2); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE +2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL No matching min/max row +SELECT * FROM t1 WHERE (2, 0) NOT IN (SELECT min(f3)+f3, min(f4)+f3+max(f4) FROM t2); +f1 f2 +INSERT INTO t1 VALUES (1, 2); +INSERT INTO t1 VALUES (3, 4); +INSERT INTO t2 VALUES (5, 6); +INSERT INTO t2 VALUES (7, 8); +SET @@optimizer_switch = 'materialization=on,in_to_exists=off,semijoin=off'; +EXPLAIN +SELECT * FROM t1 WHERE (2, 0) NOT IN (SELECT f3, min(f4) FROM t2 WHERE f3 > 10); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE +2 MATERIALIZED t2 range PRIMARY PRIMARY 4 NULL 1 Using index condition; Rowid-ordered scan +SELECT * FROM t1 WHERE (2, 0) NOT IN (SELECT f3, min(f4) FROM t2 WHERE f3 > 10); +f1 f2 +EXPLAIN +SELECT * FROM t1 WHERE (2, 0) NOT IN (SELECT f3+f4, min(f4) FROM t2 WHERE f3 > 10); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE +2 MATERIALIZED t2 range PRIMARY PRIMARY 4 NULL 1 Using index condition; Rowid-ordered scan +SELECT * FROM t1 WHERE (2, 0) NOT IN (SELECT f3+f4, min(f4) FROM t2 WHERE f3 > 10); +f1 f2 +EXPLAIN +SELECT * FROM t1 WHERE (2, 0) NOT IN (SELECT f3, min(f4)+max(f4) FROM t2 WHERE f3 > 10); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE +2 MATERIALIZED t2 range PRIMARY PRIMARY 4 NULL 1 Using index condition; Rowid-ordered scan +SELECT * FROM t1 WHERE (2, 0) NOT IN (SELECT f3, min(f4)+max(f4) FROM t2 WHERE f3 > 10); +f1 f2 +EXPLAIN +SELECT (2, 0) NOT IN (SELECT f3, min(f4) FROM t2 WHERE f3 > 10) as not_in; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY NULL NULL NULL NULL NULL NULL NULL No tables used +2 SUBQUERY t2 range PRIMARY PRIMARY 4 NULL 1 Using index condition; Rowid-ordered scan +SELECT (2, 0) NOT IN (SELECT f3, min(f4) FROM t2 WHERE f3 > 10) as not_in; +not_in +NULL +EXPLAIN +SELECT * FROM t1 WHERE (2, 0) NOT IN (SELECT f3, count(f4) FROM t2 WHERE f3 > 10); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE +2 MATERIALIZED t2 range PRIMARY PRIMARY 4 NULL 1 Using index condition; Rowid-ordered scan +SELECT * FROM t1 WHERE (2, 0) NOT IN (SELECT f3, count(f4) FROM t2 WHERE f3 > 10); +f1 f2 +EXPLAIN +SELECT * FROM t1 WHERE (2, 0) NOT IN (SELECT f3, f3 + count(f4) FROM t2 WHERE f3 > 10); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE +2 MATERIALIZED t2 range PRIMARY PRIMARY 4 NULL 1 Using index condition; Rowid-ordered scan +SELECT * FROM t1 WHERE (2, 0) NOT IN (SELECT f3, f3 + count(f4) FROM t2 WHERE f3 > 10); +f1 f2 +EXPLAIN +SELECT (2, 0) NOT IN (SELECT f3, count(f4) FROM t2 WHERE f3 > 10) as not_in; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY NULL NULL NULL NULL NULL NULL NULL No tables used +2 SUBQUERY t2 range PRIMARY PRIMARY 4 NULL 1 Using index condition; Rowid-ordered scan +SELECT (2, 0) NOT IN (SELECT f3, count(f4) FROM t2 WHERE f3 > 10) as not_in; +not_in +NULL +EXPLAIN +SELECT (2, 0) NOT IN (SELECT f3, count(f4) FROM t2 WHERE f3 > 10 HAVING max(f4) > 7) as not_in; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY NULL NULL NULL NULL NULL NULL NULL No tables used +2 SUBQUERY t2 range PRIMARY PRIMARY 4 NULL 1 Using index condition; Rowid-ordered scan +SELECT (2, 0) NOT IN (SELECT f3, count(f4) FROM t2 WHERE f3 > 10 HAVING max(f4) > 7) as not_in; +not_in +1 +EXPLAIN +SELECT (2, 0) NOT IN (SELECT f3, count(f4) FROM t2 WHERE f3 > 10 HAVING max(f4) is null) as not_in; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY NULL NULL NULL NULL NULL NULL NULL No tables used +2 SUBQUERY t2 range PRIMARY PRIMARY 4 NULL 1 Using index condition; Rowid-ordered scan +SELECT (2, 0) NOT IN (SELECT f3, count(f4) FROM t2 WHERE f3 > 10 HAVING max(f4) is null) as not_in; +not_in +NULL +EXPLAIN +SELECT (2, 0) NOT IN (SELECT max(f3+f3), count(f4) FROM t2 WHERE f3 > 10) as not_in; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY NULL NULL NULL NULL NULL NULL NULL No tables used +2 SUBQUERY t2 range PRIMARY PRIMARY 4 NULL 1 Using index condition; Rowid-ordered scan +SELECT (2, 0) NOT IN (SELECT max(f3+f3), count(f4) FROM t2 WHERE f3 > 10) as not_in; +not_in +NULL +EXPLAIN +SELECT (2, 0) NOT IN (SELECT max(f3+f3), count(f4)+f3 FROM t2 WHERE f3 > 10) as not_in; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY NULL NULL NULL NULL NULL NULL NULL No tables used +2 SUBQUERY t2 range PRIMARY PRIMARY 4 NULL 1 Using index condition; Rowid-ordered scan +SELECT (2, 0) NOT IN (SELECT max(f3+f3), count(f4)+f3 FROM t2 WHERE f3 > 10) as not_in; +not_in +NULL +EXPLAIN +SELECT * FROM t1 WHERE (2, 0) NOT IN (SELECT min(f3)+f3, min(f4)+f3+max(f4) FROM t2 WHERE f3 > 10); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE +2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL No matching min/max row +SELECT * FROM t1 WHERE (2, 0) NOT IN (SELECT min(f3)+f3, min(f4)+f3+max(f4) FROM t2 WHERE f3 > 10); +f1 f2 +SET @@optimizer_switch = 'materialization=off,in_to_exists=on,semijoin=off'; +EXPLAIN +SELECT * FROM t1 WHERE (2, 0) NOT IN (SELECT f3, min(f4) FROM t2 WHERE f3 > 10); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE +2 SUBQUERY t2 range PRIMARY PRIMARY 4 NULL 1 Using index condition; Rowid-ordered scan +SELECT * FROM t1 WHERE (2, 0) NOT IN (SELECT f3, min(f4) FROM t2 WHERE f3 > 10); +f1 f2 +EXPLAIN +SELECT * FROM t1 WHERE (2, 0) NOT IN (SELECT f3+f4, min(f4) FROM t2 WHERE f3 > 10); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE +2 SUBQUERY t2 range PRIMARY PRIMARY 4 NULL 1 Using index condition; Rowid-ordered scan +SELECT * FROM t1 WHERE (2, 0) NOT IN (SELECT f3+f4, min(f4) FROM t2 WHERE f3 > 10); +f1 f2 +EXPLAIN +SELECT * FROM t1 WHERE (2, 0) NOT IN (SELECT f3, min(f4)+max(f4) FROM t2 WHERE f3 > 10); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE +2 SUBQUERY t2 range PRIMARY PRIMARY 4 NULL 1 Using index condition; Rowid-ordered scan +SELECT * FROM t1 WHERE (2, 0) NOT IN (SELECT f3, min(f4)+max(f4) FROM t2 WHERE f3 > 10); +f1 f2 +EXPLAIN +SELECT (2, 0) NOT IN (SELECT f3, min(f4) FROM t2 WHERE f3 > 10) as not_in; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY NULL NULL NULL NULL NULL NULL NULL No tables used +2 SUBQUERY t2 range PRIMARY PRIMARY 4 NULL 1 Using index condition; Rowid-ordered scan +SELECT (2, 0) NOT IN (SELECT f3, min(f4) FROM t2 WHERE f3 > 10) as not_in; +not_in +NULL +EXPLAIN +SELECT * FROM t1 WHERE (2, 0) NOT IN (SELECT f3, count(f4) FROM t2 WHERE f3 > 10); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE +2 SUBQUERY t2 range PRIMARY PRIMARY 4 NULL 1 Using index condition; Rowid-ordered scan +SELECT * FROM t1 WHERE (2, 0) NOT IN (SELECT f3, count(f4) FROM t2 WHERE f3 > 10); +f1 f2 +EXPLAIN +SELECT * FROM t1 WHERE (2, 0) NOT IN (SELECT f3, f3 + count(f4) FROM t2 WHERE f3 > 10); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE +2 SUBQUERY t2 range PRIMARY PRIMARY 4 NULL 1 Using index condition; Rowid-ordered scan +SELECT * FROM t1 WHERE (2, 0) NOT IN (SELECT f3, f3 + count(f4) FROM t2 WHERE f3 > 10); +f1 f2 +EXPLAIN +SELECT (2, 0) NOT IN (SELECT f3, count(f4) FROM t2 WHERE f3 > 10) as not_in; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY NULL NULL NULL NULL NULL NULL NULL No tables used +2 SUBQUERY t2 range PRIMARY PRIMARY 4 NULL 1 Using index condition; Rowid-ordered scan +SELECT (2, 0) NOT IN (SELECT f3, count(f4) FROM t2 WHERE f3 > 10) as not_in; +not_in +NULL +EXPLAIN +SELECT (2, 0) NOT IN (SELECT f3, count(f4) FROM t2 WHERE f3 > 10 HAVING max(f4) > 7) as not_in; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY NULL NULL NULL NULL NULL NULL NULL No tables used +2 SUBQUERY t2 range PRIMARY PRIMARY 4 NULL 1 Using index condition; Rowid-ordered scan +SELECT (2, 0) NOT IN (SELECT f3, count(f4) FROM t2 WHERE f3 > 10 HAVING max(f4) > 7) as not_in; +not_in +1 +EXPLAIN +SELECT (2, 0) NOT IN (SELECT f3, count(f4) FROM t2 WHERE f3 > 10 HAVING max(f4) is null) as not_in; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY NULL NULL NULL NULL NULL NULL NULL No tables used +2 SUBQUERY t2 range PRIMARY PRIMARY 4 NULL 1 Using index condition; Rowid-ordered scan +SELECT (2, 0) NOT IN (SELECT f3, count(f4) FROM t2 WHERE f3 > 10 HAVING max(f4) is null) as not_in; +not_in +NULL +EXPLAIN +SELECT (2, 0) NOT IN (SELECT max(f3+f3), count(f4) FROM t2 WHERE f3 > 10) as not_in; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY NULL NULL NULL NULL NULL NULL NULL No tables used +2 SUBQUERY t2 range PRIMARY PRIMARY 4 NULL 1 Using index condition; Rowid-ordered scan +SELECT (2, 0) NOT IN (SELECT max(f3+f3), count(f4) FROM t2 WHERE f3 > 10) as not_in; +not_in +NULL +EXPLAIN +SELECT (2, 0) NOT IN (SELECT max(f3+f3), count(f4)+f3 FROM t2 WHERE f3 > 10) as not_in; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY NULL NULL NULL NULL NULL NULL NULL No tables used +2 SUBQUERY t2 range PRIMARY PRIMARY 4 NULL 1 Using index condition; Rowid-ordered scan +SELECT (2, 0) NOT IN (SELECT max(f3+f3), count(f4)+f3 FROM t2 WHERE f3 > 10) as not_in; +not_in +NULL +EXPLAIN +SELECT * FROM t1 WHERE (2, 0) NOT IN (SELECT min(f3)+f3, min(f4)+f3+max(f4) FROM t2 WHERE f3 > 10); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE +2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL No matching min/max row +SELECT * FROM t1 WHERE (2, 0) NOT IN (SELECT min(f3)+f3, min(f4)+f3+max(f4) FROM t2 WHERE f3 > 10); +f1 f2 +set @@optimizer_switch=@save_optimizer_switch; +drop table t1,t2; +# +# LP BUG#613029 Wrong result with materialization and semijoin, and +# valgrind warnings in Protocol::net_store_data with materialization +# for implicit grouping +# +CREATE TABLE t1 ( +pk int(11) NOT NULL AUTO_INCREMENT, +f2 int(11) NOT NULL, +f3 varchar(1) NOT NULL, +PRIMARY KEY (pk), +KEY f2 (f2)); +INSERT INTO t1 VALUES (1,9,'x'); +INSERT INTO t1 VALUES (2,5,'g'); +CREATE TABLE t2 ( +pk int(11) NOT NULL AUTO_INCREMENT, +f2 int(11) NOT NULL, +f3 varchar(1) NOT NULL, +PRIMARY KEY (pk), +KEY f2 (f2)); +INSERT INTO t2 VALUES (1,7,'p'); +set @save_optimizer_switch=@@optimizer_switch; +set @@optimizer_switch='materialization=off,in_to_exists=on,semijoin=off'; +EXPLAIN +SELECT t1.f3, MAX(t1.f2) +FROM t1, t2 +WHERE (t2.pk = t1.pk) AND t2.pk IN (SELECT f2 FROM t1); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t2 system PRIMARY NULL NULL NULL 1 +1 PRIMARY t1 const PRIMARY PRIMARY 4 const 1 +2 DEPENDENT SUBQUERY t1 index_subquery f2 f2 4 func 2 Using index +SELECT t1.f3, MAX(t1.f2) +FROM t1, t2 +WHERE (t2.pk = t1.pk) AND t2.pk IN (SELECT f2 FROM t1); +f3 MAX(t1.f2) +NULL NULL +set @@optimizer_switch='materialization=on,in_to_exists=off,semijoin=off'; +EXPLAIN +SELECT t1.f3, MAX(t1.f2) +FROM t1, t2 +WHERE (t2.pk = t1.pk) AND t2.pk IN (SELECT f2 FROM t1); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t2 system PRIMARY NULL NULL NULL 1 +1 PRIMARY t1 const PRIMARY PRIMARY 4 const 1 +2 MATERIALIZED t1 index NULL f2 4 NULL 2 Using index +SELECT t1.f3, MAX(t1.f2) +FROM t1, t2 +WHERE (t2.pk = t1.pk) AND t2.pk IN (SELECT f2 FROM t1); +f3 MAX(t1.f2) +NULL NULL +TODO: add a test case for semijoin when the wrong result is fixed +set @@optimizer_switch='materialization=off,semijoin=on'; +set @@optimizer_switch=@save_optimizer_switch; +drop table t1, t2; +# +# LP BUG#777691 Wrong result with subqery in select list and subquery cache=off in maria-5.3 +# +CREATE TABLE t1 ( f1 varchar(32)) ; +INSERT INTO t1 VALUES ('b'),('x'),('c'),('x'); +CREATE TABLE t2 ( f2 int, f3 varchar(32)) ; +INSERT INTO t2 VALUES (1,'x'); +set @save_optimizer_switch=@@optimizer_switch; +set @@optimizer_switch='materialization=off,in_to_exists=on,subquery_cache=off'; +EXPLAIN +SELECT t1.f1, ( SELECT MAX( f2 ) FROM t2 WHERE t2.f3 = t1.f1 ) as max_f2 FROM t1; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 4 +2 DEPENDENT SUBQUERY t2 system NULL NULL NULL NULL 1 +SELECT t1.f1, ( SELECT MAX( f2 ) FROM t2 WHERE t2.f3 = t1.f1 ) as max_f2 FROM t1; +f1 max_f2 +b NULL +x 1 +c NULL +x 1 +set @@optimizer_switch='materialization=on,in_to_exists=off,subquery_cache=off'; +EXPLAIN +SELECT t1.f1, ( SELECT MAX( f2 ) FROM t2 WHERE t2.f3 = t1.f1 ) as max_f2 FROM t1; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 4 +2 DEPENDENT SUBQUERY t2 system NULL NULL NULL NULL 1 +SELECT t1.f1, ( SELECT MAX( f2 ) FROM t2 WHERE t2.f3 = t1.f1 ) as max_f2 FROM t1; +f1 max_f2 +b NULL +x 1 +c NULL +x 1 +set @@optimizer_switch='materialization=off,in_to_exists=on,subquery_cache=off'; +Even when t2 is not constant table, the result must be the same. +INSERT INTO t2 VALUES (2,'y'); +EXPLAIN +SELECT t1.f1, ( SELECT MAX( f2 ) FROM t2 WHERE t2.f3 = t1.f1 ) as max_f2 FROM t1; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 4 +2 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 2 Using where +SELECT t1.f1, ( SELECT MAX( f2 ) FROM t2 WHERE t2.f3 = t1.f1 ) as max_f2 FROM t1; +f1 max_f2 +b NULL +x 1 +c NULL +x 1 +set @@optimizer_switch=@save_optimizer_switch; +drop table t1, t2; +# +# LP BUG#641203 Query returns rows where no result is expected (impossible WHERE) +# +CREATE TABLE t1 (c1 varchar(1) DEFAULT NULL); +CREATE TABLE t2 (c1 varchar(1) DEFAULT NULL); +INSERT INTO t2 VALUES ('k'), ('d'); +CREATE TABLE t3 (c1 varchar(1) DEFAULT NULL); +INSERT INTO t3 VALUES ('a'), ('b'), ('c'); +CREATE TABLE t4 (c1 varchar(1) primary key); +INSERT INTO t4 VALUES ('k'), ('d'); +SET @save_optimizer_switch=@@optimizer_switch; +SET optimizer_switch='outer_join_with_cache=off'; +SET optimizer_switch='semijoin_with_cache=off'; +SET optimizer_switch='materialization=off'; +EXPLAIN +SELECT * FROM t1 RIGHT JOIN t2 ON t1.c1 WHERE 's' IN (SELECT c1 FROM t2); +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 +1 PRIMARY t2 ALL NULL NULL NULL NULL 2 Using where; FirstMatch(t1) +1 PRIMARY t2 ALL NULL NULL NULL NULL 2 Using join buffer (flat, BNL join) +SELECT * FROM t1 RIGHT JOIN t2 ON t1.c1 WHERE 's' IN (SELECT c1 FROM t2); +c1 c1 +EXPLAIN +SELECT * FROM t2 LEFT JOIN t1 ON t1.c1 WHERE 's' IN (SELECT c1 FROM t2); +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 +1 PRIMARY t2 ALL NULL NULL NULL NULL 2 Using where; FirstMatch(t1) +1 PRIMARY t2 ALL NULL NULL NULL NULL 2 Using join buffer (flat, BNL join) +SELECT * FROM t2 LEFT JOIN t1 ON t1.c1 WHERE 's' IN (SELECT c1 FROM t2); +c1 c1 +SET optimizer_switch='materialization=on'; +EXPLAIN +SELECT * FROM (t2 LEFT JOIN t1 ON t1.c1) LEFT JOIN t3 on t3.c1 WHERE 's' IN (SELECT c1 FROM t2); +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 +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 +1 PRIMARY t2 ALL NULL NULL NULL NULL 2 Using join buffer (flat, BNL join) +1 PRIMARY t3 ALL NULL NULL NULL NULL 3 Using where +2 MATERIALIZED t2 ALL NULL NULL NULL NULL 2 Using where +SELECT * FROM (t2 LEFT JOIN t1 ON t1.c1) LEFT JOIN t3 on t3.c1 WHERE 's' IN (SELECT c1 FROM t2); +c1 c1 c1 +EXPLAIN +SELECT * FROM t4 LEFT JOIN t2 ON t4.c1 WHERE 's' IN (SELECT c1 FROM t2); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 +1 PRIMARY t4 index NULL PRIMARY 3 NULL 2 Using index; Using join buffer (flat, BNL join) +1 PRIMARY t2 ALL NULL NULL NULL NULL 2 Using where +2 MATERIALIZED t2 ALL NULL NULL NULL NULL 2 Using where +SELECT * FROM t4 LEFT JOIN t2 ON t4.c1 WHERE 's' IN (SELECT c1 FROM t2); +c1 c1 +SET optimizer_switch=@save_optimizer_switch; +drop table t1, t2, t3, t4; +# +# LP BUG#675981 Assertion `cache != __null' failed in sub_select_cache() +# on EXPLAIN +# +CREATE TABLE t1 (f1 int,f2 int) ; +INSERT IGNORE INTO t1 VALUES ('2','5'),('2',NULL); +CREATE TABLE t2 (f1 int, f5 int) ; +INSERT IGNORE INTO t2 VALUES (1,0); +CREATE TABLE t3 (f4 int) ; +INSERT IGNORE INTO t3 VALUES (0),(0); +set @@optimizer_switch='in_to_exists=on,materialization=off,semijoin=off'; +EXPLAIN +SELECT * FROM t2 +WHERE f1 IN (SELECT t1.f2 FROM t1 JOIN t3 ON t3.f4); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t2 system NULL NULL NULL NULL 1 +2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 2 Using where +2 DEPENDENT SUBQUERY t3 ALL NULL NULL NULL NULL 2 Using where; Using join buffer (flat, BNL join) +drop table t1, t2, t3; +# +# LP BUG#680005 Second assertion `cache != __null' failed in +# sub_select_cache() on EXPLAIN +# +CREATE TABLE t1 (f1 int,f2 int,f4 int,f6 int,KEY (f4)) ; +INSERT IGNORE INTO t1 VALUES +('1','5','1','0'),('2','1','1','0'),('2','2','2','0'),('0',NULL,'0','0'), +('2','1','2','0'),('2','0','0','0'),('2','2','2','0'),('2','8','2','0'), +('2','7','2','0'),('2','5','2','0'),('2',NULL,'1','0'); +CREATE TABLE t2 (f3 int) ; +INSERT IGNORE INTO t2 VALUES ('7'); +CREATE TABLE t3 (f3 int) ; +INSERT IGNORE INTO t3 VALUES ('2'); +EXPLAIN +SELECT t1.f4 +FROM t2 JOIN t1 ON t1.f6 +WHERE +( t1.f2 ) IN (SELECT SUBQUERY2_t1.f3 +FROM t3 AS SUBQUERY2_t1 +JOIN +(t1 AS SUBQUERY2_t2 +JOIN +t1 AS SUBQUERY2_t3 ON SUBQUERY2_t3.f1) +ON SUBQUERY2_t3.f2) +GROUP BY t1.f4 ORDER BY t1.f1 LIMIT 10; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t2 system NULL NULL NULL NULL 1 Using temporary; Using filesort +1 PRIMARY t1 ALL NULL NULL NULL NULL 11 Using where +2 DEPENDENT SUBQUERY SUBQUERY2_t1 system NULL NULL NULL NULL 1 +2 DEPENDENT SUBQUERY SUBQUERY2_t2 index NULL f4 5 NULL 11 Using index +2 DEPENDENT SUBQUERY SUBQUERY2_t3 ALL NULL NULL NULL NULL 11 Using where; Using join buffer (flat, BNL join) +drop table t1, t2, t3; +# +# LP BUG#680038 bool close_thread_table(THD*, TABLE**): +# Assertion `table->key_read == 0' failed in EXPLAIN +# +CREATE TABLE t1 (f1 int,f3 int,f4 int) ; +INSERT IGNORE INTO t1 VALUES (NULL,1,0); +CREATE TABLE t2 (f2 int,f4 int,f5 int) ; +INSERT IGNORE INTO t2 VALUES (8,0,0),(5,0,0); +CREATE TABLE t3 (f4 int,KEY (f4)) ; +INSERT IGNORE INTO t3 VALUES (0),(0); +set @@optimizer_switch='semijoin=off'; +# NOTE: the following should have 'SUBQUERY', not 'DEPENDENT SUBQUERY' +# for line with id=2, see MDEV-27794. +EXPLAIN +SELECT * FROM t1 WHERE +(SELECT f2 FROM t2 +WHERE f4 <= ALL +(SELECT max(SQ1_t1.f4) +FROM t3 AS SQ1_t1 JOIN t3 AS SQ1_t3 ON SQ1_t3.f4 +GROUP BY SQ1_t1.f4)); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 system NULL NULL NULL NULL 1 +2 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 2 Using where +3 SUBQUERY SQ1_t3 range f4 f4 5 NULL 2 Using where; Using index; Using temporary +3 SUBQUERY SQ1_t1 index NULL f4 5 NULL 2 Using index; Using join buffer (flat, BNL join) +SELECT * FROM t1 WHERE +(SELECT f2 FROM t2 +WHERE f4 <= ALL +(SELECT max(SQ1_t1.f4) +FROM t3 AS SQ1_t1 JOIN t3 AS SQ1_t3 ON SQ1_t3.f4 +GROUP BY SQ1_t1.f4)); +ERROR 21000: Subquery returns more than 1 row +drop table t1, t2, t3; +# +# BUG#52317: Assertion failing in Field_varstring::store() +# at field.cc:6833 +# +CREATE TABLE t1 (i INTEGER); +INSERT INTO t1 VALUES (1); +CREATE TABLE t2 (i INTEGER, KEY k(i)); +INSERT INTO t2 VALUES (1), (2); +EXPLAIN +SELECT i FROM t1 WHERE (1) NOT IN (SELECT i FROM t2); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 system NULL NULL NULL NULL 1 +2 SUBQUERY t2 index_subquery k k 5 const 2 Using index +DROP TABLE t2; +DROP TABLE t1; +# +# LP BUG#680846: Crash in clear_tables() with subqueries +# +CREATE TABLE t1 (f3 int) ; +INSERT IGNORE INTO t1 VALUES (0),(0); +CREATE TABLE t2 (f1 int,f3 int,f4 varchar(32)) ; +INSERT IGNORE INTO t2 VALUES (1,0,'f'); +EXPLAIN +SELECT COUNT(t2.f3), +(SELECT COUNT(f3) FROM t1 WHERE t2.f1) AS f9 +FROM t2 JOIN t1 ON t1.f3 +WHERE ('v') IN (SELECT f4 FROM t2) +GROUP BY f9; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE +3 SUBQUERY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables +2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 2 +SELECT COUNT(t2.f3), +(SELECT COUNT(f3) FROM t1 WHERE t2.f1) AS f9 +FROM t2 JOIN t1 ON t1.f3 +WHERE ('v') IN (SELECT f4 FROM t2) +GROUP BY f9; +COUNT(t2.f3) f9 +EXPLAIN +SELECT COUNT(t2.f3), +(SELECT COUNT(f3) FROM t1 WHERE t2.f1) AS f9 +FROM t2 JOIN t1 ON t1.f3 +WHERE ('v') IN (SELECT f4 FROM t2) +ORDER BY f9; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE +3 SUBQUERY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables +2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 2 +SELECT COUNT(t2.f3), +(SELECT COUNT(f3) FROM t1 WHERE t2.f1) AS f9 +FROM t2 JOIN t1 ON t1.f3 +WHERE ('v') IN (SELECT f4 FROM t2) +ORDER BY f9; +COUNT(t2.f3) f9 +0 NULL +EXPLAIN +SELECT COUNT(t2.f3), +(SELECT t2.f1 FROM t1 limit 1) AS f9 +FROM t2 JOIN t1 +WHERE ('v') IN (SELECT f4 FROM t2) +GROUP BY f9; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE +3 SUBQUERY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables +2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 2 +SELECT COUNT(t2.f3), +(SELECT t2.f1 FROM t1 limit 1) AS f9 +FROM t2 JOIN t1 +WHERE ('v') IN (SELECT f4 FROM t2) +GROUP BY f9; +COUNT(t2.f3) f9 +EXPLAIN +SELECT COUNT(t2.f3), +(SELECT t2.f1 FROM t1 limit 1) AS f9 +FROM t2 JOIN t1 +WHERE ('v') IN (SELECT f4 FROM t2) +ORDER BY f9; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE +3 SUBQUERY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables +2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 2 +SELECT COUNT(t2.f3), +(SELECT t2.f1 FROM t1 limit 1) AS f9 +FROM t2 JOIN t1 +WHERE ('v') IN (SELECT f4 FROM t2) +ORDER BY f9; +COUNT(t2.f3) f9 +0 NULL +drop table t1,t2; +# +# LP BUG#682683 Crash in create_tmp_table called from +# JOIN::init_execution +# +CREATE TABLE t2 (f1 int) ; +INSERT INTO t2 VALUES (1),(2); +CREATE TABLE t1 (f1 int) ; +EXPLAIN +SELECT (SELECT f1 FROM t1) AS field1 FROM t2 GROUP BY field1; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t2 ALL NULL NULL NULL NULL 2 +2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL no matching row in const table +SELECT (SELECT f1 FROM t1) AS field1 FROM t2 GROUP BY field1; +field1 +NULL +EXPLAIN +SELECT (SELECT f1 FROM t1) AS field1 FROM t2 ORDER BY field1; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t2 ALL NULL NULL NULL NULL 2 +2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL no matching row in const table +SELECT (SELECT f1 FROM t1) AS field1 FROM t2 ORDER BY field1; +field1 +NULL +NULL +INSERT INTO t1 VALUES (1),(2); +EXPLAIN +SELECT (SELECT f1 FROM t1) AS field1 FROM t2 GROUP BY field1; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t2 ALL NULL NULL NULL NULL 2 +2 SUBQUERY t1 ALL NULL NULL NULL NULL 2 +SELECT (SELECT f1 FROM t1) AS field1 FROM t2 GROUP BY field1; +ERROR 21000: Subquery returns more than 1 row +EXPLAIN +SELECT (SELECT f1 FROM t1) AS field1 FROM t2 ORDER BY field1; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t2 ALL NULL NULL NULL NULL 2 +2 SUBQUERY t1 ALL NULL NULL NULL NULL 2 +SELECT (SELECT f1 FROM t1) AS field1 FROM t2 ORDER BY field1; +ERROR 21000: Subquery returns more than 1 row +drop table t1,t2; +# +# LP BUG#680943 Assertion `!table || (!table->read_set || +# bitmap_is_set(table->read_set, field_index))' failed with subquery +# +CREATE TABLE t1 (f1 int,f3 int) ; +INSERT IGNORE INTO t1 VALUES ('6','0'),('4','0'); +CREATE TABLE t2 (f1 int,f2 int,f3 int) ; +INSERT IGNORE INTO t2 VALUES ('6','0','0'),('2','0','0'); +SELECT f2 +FROM (SELECT * FROM t2) AS alias1 +WHERE (SELECT SQ2_t2.f1 +FROM t1 JOIN t1 AS SQ2_t2 ON SQ2_t2.f3 +WHERE SQ2_t2.f3 AND alias1.f1) +ORDER BY f3 ; +f2 +drop table t1,t2; +# +# LP BUG#715062: Wrong result with VIEW + UNION + subquery in maria-5.3-mwl89 +# +create table t1 (f1 int); +create table t2 (f2 int); +create table t3 (f3 int); +insert into t1 values (2); +insert into t2 values (2); +insert into t3 values (7); +CREATE VIEW v1 AS SELECT 2 UNION SELECT 2 ; +CREATE VIEW v2 AS SELECT * from t1 UNION SELECT * from t2 ; +set @save_optimizer_switch=@@optimizer_switch; +SET @@optimizer_switch = 'in_to_exists=off,semijoin=off,materialization=on'; +EXPLAIN +SELECT 'bug' FROM DUAL WHERE ( 5 ) IN ( SELECT * FROM v1 ); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY NULL NULL NULL NULL NULL NULL NULL No tables used +2 SUBQUERY <derived3> ALL NULL NULL NULL NULL 2 Using where +3 DERIVED NULL NULL NULL NULL NULL NULL NULL No tables used +4 UNION NULL NULL NULL NULL NULL NULL NULL No tables used +NULL UNION RESULT <union3,4> ALL NULL NULL NULL NULL NULL +SELECT 'bug' FROM DUAL WHERE ( 5 ) IN ( SELECT * FROM v1 ); +bug +EXPLAIN +SELECT ( 5 ) IN ( SELECT * FROM v1 ); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY NULL NULL NULL NULL NULL NULL NULL No tables used +2 SUBQUERY <derived3> ALL NULL NULL NULL NULL 2 Using where +3 DERIVED NULL NULL NULL NULL NULL NULL NULL No tables used +4 UNION NULL NULL NULL NULL NULL NULL NULL No tables used +NULL UNION RESULT <union3,4> ALL NULL NULL NULL NULL NULL +SELECT ( 5 ) IN ( SELECT * FROM v1 ); +( 5 ) IN ( SELECT * FROM v1 ) +0 +EXPLAIN +SELECT 'bug' FROM DUAL WHERE ( 5 ) IN (SELECT * FROM v2); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY NULL NULL NULL NULL NULL NULL NULL No tables used +2 SUBQUERY <derived3> ALL NULL NULL NULL NULL 2 Using where +3 DERIVED t1 system NULL NULL NULL NULL 1 +4 UNION t2 system NULL NULL NULL NULL 1 +NULL UNION RESULT <union3,4> ALL NULL NULL NULL NULL NULL +SELECT 'bug' FROM DUAL WHERE ( 5 ) IN (SELECT * FROM v2); +bug +EXPLAIN +SELECT 'bug' FROM t3 WHERE ( 5 ) IN (SELECT * FROM v2); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t3 system NULL NULL NULL NULL 1 +2 MATERIALIZED <derived3> ALL NULL NULL NULL NULL 2 +3 DERIVED t1 system NULL NULL NULL NULL 1 +4 UNION t2 system NULL NULL NULL NULL 1 +NULL UNION RESULT <union3,4> ALL NULL NULL NULL NULL NULL +SELECT 'bug' FROM t3 WHERE ( 5 ) IN (SELECT * FROM v2); +bug +EXPLAIN +SELECT ( 5 ) IN ( SELECT * FROM v2 ); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY NULL NULL NULL NULL NULL NULL NULL No tables used +2 SUBQUERY <derived3> ALL NULL NULL NULL NULL 2 Using where +3 DERIVED t1 system NULL NULL NULL NULL 1 +4 UNION t2 system NULL NULL NULL NULL 1 +NULL UNION RESULT <union3,4> ALL NULL NULL NULL NULL NULL +SELECT ( 5 ) IN ( SELECT * FROM v2 ); +( 5 ) IN ( SELECT * FROM v2 ) +0 +SET @@optimizer_switch = 'in_to_exists=on,semijoin=off,materialization=off'; +EXPLAIN +SELECT 'bug' FROM DUAL WHERE ( 5 ) IN ( SELECT * FROM v1 ); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY NULL NULL NULL NULL NULL NULL NULL No tables used +2 SUBQUERY <derived3> ALL NULL NULL NULL NULL 2 Using where +3 DERIVED NULL NULL NULL NULL NULL NULL NULL No tables used +4 UNION NULL NULL NULL NULL NULL NULL NULL No tables used +NULL UNION RESULT <union3,4> ALL NULL NULL NULL NULL NULL +SELECT 'bug' FROM DUAL WHERE ( 5 ) IN ( SELECT * FROM v1 ); +bug +EXPLAIN +SELECT ( 5 ) IN ( SELECT * FROM v1 ); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY NULL NULL NULL NULL NULL NULL NULL No tables used +2 SUBQUERY <derived3> ALL NULL NULL NULL NULL 2 Using where +3 DERIVED NULL NULL NULL NULL NULL NULL NULL No tables used +4 UNION NULL NULL NULL NULL NULL NULL NULL No tables used +NULL UNION RESULT <union3,4> ALL NULL NULL NULL NULL NULL +SELECT ( 5 ) IN ( SELECT * FROM v1 ); +( 5 ) IN ( SELECT * FROM v1 ) +0 +EXPLAIN +SELECT 'bug' FROM DUAL WHERE ( 5 ) IN (SELECT * FROM v2); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY NULL NULL NULL NULL NULL NULL NULL No tables used +2 SUBQUERY <derived3> ALL NULL NULL NULL NULL 2 Using where +3 DERIVED t1 system NULL NULL NULL NULL 1 +4 UNION t2 system NULL NULL NULL NULL 1 +NULL UNION RESULT <union3,4> ALL NULL NULL NULL NULL NULL +SELECT 'bug' FROM DUAL WHERE ( 5 ) IN (SELECT * FROM v2); +bug +EXPLAIN +SELECT 'bug' FROM t3 WHERE ( 5 ) IN (SELECT * FROM v2); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t3 system NULL NULL NULL NULL 1 +2 SUBQUERY <derived3> ALL NULL NULL NULL NULL 2 Using where +3 DERIVED t1 system NULL NULL NULL NULL 1 +4 UNION t2 system NULL NULL NULL NULL 1 +NULL UNION RESULT <union3,4> ALL NULL NULL NULL NULL NULL +SELECT 'bug' FROM t3 WHERE ( 5 ) IN (SELECT * FROM v2); +bug +EXPLAIN +SELECT ( 5 ) IN ( SELECT * FROM v2 ); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY NULL NULL NULL NULL NULL NULL NULL No tables used +2 SUBQUERY <derived3> ALL NULL NULL NULL NULL 2 Using where +3 DERIVED t1 system NULL NULL NULL NULL 1 +4 UNION t2 system NULL NULL NULL NULL 1 +NULL UNION RESULT <union3,4> ALL NULL NULL NULL NULL NULL +SELECT ( 5 ) IN ( SELECT * FROM v2 ); +( 5 ) IN ( SELECT * FROM v2 ) +0 +set @@optimizer_switch=@save_optimizer_switch; +drop table t1,t2,t3; +drop view v1,v2; +# +# LP BUG#715069 Wrong result with GROUP BY inside subquery and materialization=off +# +CREATE TABLE t0 ( f1 int(11), f2 int(11), f10 varchar(1), PRIMARY KEY (f1)) ; +INSERT INTO t0 VALUES (8,8,'u'),(10,5,'o'); +CREATE TABLE t1 (f1a int, f2a int not null, f3a varchar(3) not null, PRIMARY KEY (f1a)) ; +INSERT INTO t1 VALUES +(8,8,'a1a'), +(10,5,'b1b'); +CREATE TABLE t2 (f1b int, f2b int not null, f3b varchar(3) not null, PRIMARY KEY (f1b)) ; +INSERT INTO t2 VALUES +(10,5,'d1d'); +set @save_optimizer_switch=@@optimizer_switch; +SET optimizer_switch='outer_join_with_cache=off'; +set @@optimizer_switch = 'in_to_exists=on,materialization=off,semijoin=off'; +EXPLAIN +SELECT alias2.f1 , alias2.f2 +FROM t0 AS alias1 +RIGHT JOIN t0 AS alias2 ON alias2.f10 +WHERE ( alias2.f1 , alias2.f2 ) IN ( SELECT max(f2) , f1 FROM t0 GROUP BY f2 , f1 ); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY alias2 ALL NULL NULL NULL NULL 2 Using where +1 PRIMARY alias1 index NULL PRIMARY 4 NULL 2 Using where; Using index +2 DEPENDENT SUBQUERY t0 ALL NULL NULL NULL NULL 2 Using temporary +SELECT alias2.f1 , alias2.f2 +FROM t0 AS alias1 +RIGHT JOIN t0 AS alias2 ON alias2.f10 +WHERE ( alias2.f1 , alias2.f2 ) IN ( SELECT max(f2) , f1 FROM t0 GROUP BY f2 , f1 ); +f1 f2 +8 8 +Warnings: +Warning 1292 Truncated incorrect DECIMAL value: 'u' +EXPLAIN +SELECT * FROM t2 WHERE (f1b, f2b) IN (SELECT max(f1a), f2a FROM t1 GROUP BY f1a, f2a); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t2 system NULL NULL NULL NULL 1 +2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 2 Using temporary +SELECT * FROM t2 WHERE (f1b, f2b) IN (SELECT max(f1a), f2a FROM t1 GROUP BY f1a, f2a); +f1b f2b f3b +10 5 d1d +EXPLAIN +SELECT * FROM t2 WHERE (f1b) IN (SELECT max(f1a) FROM t1 GROUP BY f1a, f2a); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t2 system NULL NULL NULL NULL 1 +2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 2 Using temporary +SELECT * FROM t2 WHERE (f1b) IN (SELECT max(f1a) FROM t1 GROUP BY f1a, f2a); +f1b f2b f3b +10 5 d1d +set @@optimizer_switch = 'in_to_exists=off,materialization=on,semijoin=off'; +EXPLAIN +SELECT alias2.f1 , alias2.f2 +FROM t0 AS alias1 +RIGHT JOIN t0 AS alias2 ON alias2.f10 +WHERE ( alias2.f1 , alias2.f2 ) IN ( SELECT max(f2) , f1 FROM t0 GROUP BY f2 , f1 ); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY alias2 ALL NULL NULL NULL NULL 2 Using where +1 PRIMARY alias1 index NULL PRIMARY 4 NULL 2 Using where; Using index +2 MATERIALIZED t0 ALL NULL NULL NULL NULL 2 Using temporary +SELECT alias2.f1 , alias2.f2 +FROM t0 AS alias1 +RIGHT JOIN t0 AS alias2 ON alias2.f10 +WHERE ( alias2.f1 , alias2.f2 ) IN ( SELECT max(f2) , f1 FROM t0 GROUP BY f2 , f1 ); +f1 f2 +8 8 +Warnings: +Warning 1292 Truncated incorrect DECIMAL value: 'u' +EXPLAIN +SELECT * FROM t2 WHERE (f1b, f2b) IN (SELECT max(f1a), f2a FROM t1 GROUP BY f1a, f2a); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t2 system NULL NULL NULL NULL 1 +2 MATERIALIZED t1 ALL NULL NULL NULL NULL 2 Using temporary +SELECT * FROM t2 WHERE (f1b, f2b) IN (SELECT max(f1a), f2a FROM t1 GROUP BY f1a, f2a); +f1b f2b f3b +10 5 d1d +EXPLAIN +SELECT * FROM t2 WHERE (f1b) IN (SELECT max(f1a) FROM t1 GROUP BY f1a, f2a); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t2 system NULL NULL NULL NULL 1 +2 MATERIALIZED t1 ALL NULL NULL NULL NULL 2 Using temporary +SELECT * FROM t2 WHERE (f1b) IN (SELECT max(f1a) FROM t1 GROUP BY f1a, f2a); +f1b f2b f3b +10 5 d1d +set @@optimizer_switch=@save_optimizer_switch; +drop table t0,t1,t2; +# +# LP BUG#715759 Wrong result with in_to_exists=on in maria-5.3-mwl89 +# +set @save_optimizer_switch=@@optimizer_switch; +CREATE TABLE t1 (a1 int, a2 int) ; +INSERT INTO t1 VALUES (1, 2); +INSERT INTO t1 VALUES (3, 4); +CREATE TABLE t2 (b1 int, b2 int) ; +INSERT INTO t2 VALUES (1, 2); +SET @@optimizer_switch = 'in_to_exists=on,materialization=off,semijoin=off'; +EXPLAIN SELECT * FROM t1 WHERE a1 IN (SELECT b1 FROM t2 WHERE b1 = b2); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 2 Using where +2 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables +SELECT * FROM t1 WHERE a1 IN (SELECT b1 FROM t2 WHERE b1 = b2); +a1 a2 +set @@optimizer_switch=@save_optimizer_switch; +drop table t1, t2; +# +# LP BUG#772309 join_tab_cmp_straight(): Assertion `!jt2->emb_sj_nest' failed in maria-5.3-mwl89 with semijoin +# +CREATE TABLE t1 ( f2 int) ; +INSERT INTO t1 VALUES (0),(0); +CREATE TABLE t2 ( f1 int NOT NULL ) ; +INSERT INTO t2 VALUES (0),(0); +CREATE TABLE t3 ( f1 int NOT NULL , f2 int) ; +INSERT INTO t3 VALUES (0,0), (0,0); +EXPLAIN SELECT STRAIGHT_JOIN ( +SELECT f2 FROM t1 WHERE ( f2 ) IN ( SELECT t3.f2 FROM t3 JOIN t2 ON t2.f1 = 1 ) +) as exp; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY NULL NULL NULL NULL NULL NULL NULL No tables used +2 SUBQUERY t1 ALL NULL NULL NULL NULL 2 Using where +3 DEPENDENT SUBQUERY t3 ALL NULL NULL NULL NULL 2 Using where +3 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 2 Using where; Using join buffer (flat, BNL join) +SELECT STRAIGHT_JOIN ( +SELECT f2 FROM t1 WHERE ( f2 ) IN ( SELECT t3.f2 FROM t3 JOIN t2 ON t2.f1 = 1 ) +) as exp; +exp +NULL +drop table t1, t2, t3; +# +# LP BUG#777597 Wrong result with multipart keys, in_to_exists=on, NOT IN in MWL#89 +# +CREATE TABLE t1 ( f4 int); +INSERT IGNORE INTO t1 VALUES (2),(2); +CREATE TABLE t2 ( f3 int, f10 int, KEY (f10,f3) ); +INSERT IGNORE INTO t2 VALUES (6, 1), (6, 1); +CREATE TABLE t3 ( f10 int ); +INSERT IGNORE INTO t3 VALUES (1); +SET SESSION optimizer_switch='in_to_exists=on,materialization=off'; +EXPLAIN +SELECT * FROM t1 WHERE ( 6 ) NOT IN ( SELECT t2.f3 FROM t2 JOIN t3 ON t3.f10 = t2.f10); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE +2 SUBQUERY t3 system NULL NULL NULL NULL 1 +2 SUBQUERY t2 ref_or_null f10 f10 10 const,const 2 Using where; Using index +SELECT * FROM t1 WHERE ( 6 ) NOT IN ( SELECT t2.f3 FROM t2 JOIN t3 ON t3.f10 = t2.f10); +f4 +drop table t1,t2,t3; +# +# LP BUG#778413 Third crash in select_describe() in maria-5.3-mwl89 +# +CREATE TABLE t1 ( f11 int) ; +INSERT INTO t1 VALUES (1),(1); +CREATE TABLE t2 ( f1 int NOT NULL) ; +INSERT INTO t2 VALUES (20); +CREATE TABLE t3 (f3 int) ; +INSERT INTO t3 VALUES (2),(2); +EXPLAIN SELECT * FROM t2 +WHERE t2.f1 = ( +SELECT MAX( f3 ) FROM t3 +WHERE EXISTS ( +SELECT DISTINCT f11 +FROM t1)); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t2 system NULL NULL NULL NULL 1 +2 SUBQUERY t3 ALL NULL NULL NULL NULL 2 +3 SUBQUERY t1 ALL NULL NULL NULL NULL 2 +drop table t1, t2, t3; +# +# LP BUG#802979 Assertion `table->key_read == 0' in close_thread_table +# +CREATE TABLE t1 ( f1 int, f2 int , KEY (f1)) ; +INSERT IGNORE INTO t1 VALUES (1,0),(5,0); +CREATE TABLE t2 ( f1 int, f2 int , KEY (f1)) ; +INSERT IGNORE INTO t2 VALUES (1,0),(5,0); +CREATE TABLE t3 ( f1 int, f2 int , KEY (f1)) ; +INSERT IGNORE INTO t3 VALUES (1,0),(5,0); +CREATE TABLE t4 ( f1 int, f2 int , KEY (f1)) ; +INSERT IGNORE INTO t4 VALUES (1,0),(5,0); +EXPLAIN +SELECT * +FROM t1, t2 +WHERE t2.f2 = (SELECT f2 FROM t3 +WHERE EXISTS (SELECT DISTINCT f1 FROM t4)) +AND t2.f2 = t1.f1; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ref f1 f1 5 const 0 Using where +1 PRIMARY t2 ALL NULL NULL NULL NULL 2 Using where; Using join buffer (flat, BNL join) +2 SUBQUERY t3 ALL NULL NULL NULL NULL 2 +3 SUBQUERY t4 index NULL f1 5 NULL 2 Using index +SELECT * +FROM t1, t2 +WHERE t2.f2 = (SELECT f2 FROM t3 +WHERE EXISTS (SELECT DISTINCT f1 FROM t4)) +AND t2.f2 = t1.f1; +ERROR 21000: Subquery returns more than 1 row +EXPLAIN +SELECT * +FROM t1, t2 +WHERE t2.f2 = (SELECT f2 FROM t3 +WHERE EXISTS (SELECT DISTINCT f1 FROM t4) LIMIT 1) +AND t2.f2 = t1.f1; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ref f1 f1 5 const 0 Using where +1 PRIMARY t2 ALL NULL NULL NULL NULL 2 Using where; Using join buffer (flat, BNL join) +2 SUBQUERY t3 ALL NULL NULL NULL NULL 2 +3 SUBQUERY t4 index NULL f1 5 NULL 2 Using index +SELECT * +FROM t1, t2 +WHERE t2.f2 = (SELECT f2 FROM t3 +WHERE EXISTS (SELECT DISTINCT f1 FROM t4) LIMIT 1) +AND t2.f2 = t1.f1; +f1 f2 f1 f2 +drop table t1,t2,t3,t4; +# +# LP BUG#611690 Crash in select_describe() with nested subqueries +# +CREATE TABLE t1 ( +col_int_key int(11) DEFAULT NULL, +col_varchar_key varchar(1) DEFAULT NULL, +col_varchar_nokey varchar(1) DEFAULT NULL, +KEY col_int_key (col_int_key), +KEY col_varchar_key (col_varchar_key,col_int_key) +) ENGINE=MyISAM DEFAULT CHARSET=latin1; +INSERT INTO t1 VALUES (8,'v','v'); +INSERT INTO t1 VALUES (9,'r','r'); +CREATE TABLE t2 ( +col_int_key int(11) DEFAULT NULL, +col_varchar_key varchar(1) DEFAULT NULL, +col_varchar_nokey varchar(1) DEFAULT NULL, +KEY col_int_key (col_int_key), +KEY col_varchar_key (col_varchar_key,col_int_key) +) ENGINE=MyISAM DEFAULT CHARSET=latin1; +INSERT INTO t2 VALUES (2,'w','w'); +INSERT INTO t2 VALUES (9,'m','m'); +set @old_optimizer_switch = @@optimizer_switch; +set @@optimizer_switch='subquery_cache=off,materialization=on,in_to_exists=off,semijoin=off'; +EXPLAIN +SELECT col_int_key +FROM t2 +WHERE (SELECT SUBQUERY2_t1.col_int_key +FROM t1 SUBQUERY2_t1 STRAIGHT_JOIN t1 SUBQUERY2_t2 +ON SUBQUERY2_t2.col_varchar_key +WHERE SUBQUERY2_t2.col_varchar_nokey IN +(SELECT col_varchar_nokey FROM t1 GROUP BY col_varchar_nokey)); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t2 index NULL col_int_key 5 NULL 2 Using index +2 SUBQUERY SUBQUERY2_t1 index NULL col_int_key 5 NULL 2 Using index +2 SUBQUERY SUBQUERY2_t2 ALL col_varchar_key NULL NULL NULL 2 Using where; Using join buffer (flat, BNL join) +3 MATERIALIZED t1 ALL NULL NULL NULL NULL 2 +SELECT col_int_key +FROM t2 +WHERE (SELECT SUBQUERY2_t1.col_int_key +FROM t1 SUBQUERY2_t1 STRAIGHT_JOIN t1 SUBQUERY2_t2 +ON SUBQUERY2_t2.col_varchar_key +WHERE SUBQUERY2_t2.col_varchar_nokey IN +(SELECT col_varchar_nokey FROM t1 GROUP BY col_varchar_nokey)); +col_int_key +Warnings: +Warning 1292 Truncated incorrect DECIMAL value: 'v' +Warning 1292 Truncated incorrect DECIMAL value: 'r' +set @@optimizer_switch='subquery_cache=off,materialization=off,in_to_exists=on,semijoin=off'; +EXPLAIN +SELECT col_int_key +FROM t2 +WHERE (SELECT SUBQUERY2_t1.col_int_key +FROM t1 SUBQUERY2_t1 STRAIGHT_JOIN t1 SUBQUERY2_t2 +ON SUBQUERY2_t2.col_varchar_key +WHERE SUBQUERY2_t2.col_varchar_nokey IN +(SELECT col_varchar_nokey FROM t1 GROUP BY col_varchar_nokey)); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t2 index NULL col_int_key 5 NULL 2 Using index +2 SUBQUERY SUBQUERY2_t1 index NULL col_int_key 5 NULL 2 Using index +2 SUBQUERY SUBQUERY2_t2 ALL col_varchar_key NULL NULL NULL 2 Using where; Using join buffer (flat, BNL join) +3 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 2 Using where +SELECT col_int_key +FROM t2 +WHERE (SELECT SUBQUERY2_t1.col_int_key +FROM t1 SUBQUERY2_t1 STRAIGHT_JOIN t1 SUBQUERY2_t2 +ON SUBQUERY2_t2.col_varchar_key +WHERE SUBQUERY2_t2.col_varchar_nokey IN +(SELECT col_varchar_nokey FROM t1 GROUP BY col_varchar_nokey)); +col_int_key +Warnings: +Warning 1292 Truncated incorrect DECIMAL value: 'v' +Warning 1292 Truncated incorrect DECIMAL value: 'r' +drop table t1, t2; +set @@optimizer_switch = @old_optimizer_switch; +# +# LP BUG#612543 Crash in Item_field::used_tables() with view + subquery + prepared statements +# +CREATE TABLE t1 ( f1 int(11), f2 varchar(1)); +CREATE TABLE t2 ( f3 varchar(1)); +insert into t1 values (2,'x'), (5,'y'); +insert into t2 values ('x'), ('z'); +CREATE VIEW v2 AS SELECT * FROM t2; +set @old_optimizer_switch = @@optimizer_switch; +set @@optimizer_switch='materialization=on,in_to_exists=off,semijoin=off,subquery_cache=off'; +EXPLAIN SELECT * FROM t1 JOIN v2 ON t1.f2 > 'a' WHERE v2.f3 IN ( SELECT f2 FROM t1 ); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 2 Using where +1 PRIMARY t2 ALL NULL NULL NULL NULL 2 Using where; Using join buffer (flat, BNL join) +2 MATERIALIZED t1 ALL NULL NULL NULL NULL 2 +PREPARE st1 FROM "SELECT * FROM t1 JOIN v2 ON t1.f2 > 'a' WHERE v2.f3 IN ( SELECT f2 FROM t1 )"; +EXECUTE st1; +f1 f2 f3 +2 x x +5 y x +EXECUTE st1; +f1 f2 f3 +2 x x +5 y x +set @@optimizer_switch='materialization=off,in_to_exists=on,semijoin=off,subquery_cache=off'; +EXPLAIN SELECT * FROM t1 JOIN v2 ON t1.f2 > 'a' WHERE v2.f3 IN ( SELECT f2 FROM t1 ); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 2 Using where +1 PRIMARY t2 ALL NULL NULL NULL NULL 2 Using where; Using join buffer (flat, BNL join) +2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 2 Using where +PREPARE st2 FROM "SELECT * FROM t1 JOIN v2 ON t1.f2 > 'a' WHERE v2.f3 IN ( SELECT f2 FROM t1 )"; +EXECUTE st2; +f1 f2 f3 +2 x x +5 y x +EXECUTE st2; +f1 f2 f3 +2 x x +5 y x +set @@optimizer_switch='materialization=on,in_to_exists=on,semijoin=off,subquery_cache=off'; +EXPLAIN SELECT * FROM t1 JOIN v2 ON t1.f2 > 'a' WHERE v2.f3 IN ( SELECT f2 FROM t1 ); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 2 Using where +1 PRIMARY t2 ALL NULL NULL NULL NULL 2 Using where; Using join buffer (flat, BNL join) +2 MATERIALIZED t1 ALL NULL NULL NULL NULL 2 +PREPARE st3 FROM "SELECT * FROM t1 JOIN v2 ON t1.f2 > 'a' WHERE v2.f3 IN ( SELECT f2 FROM t1 )"; +EXECUTE st3; +f1 f2 f3 +2 x x +5 y x +EXECUTE st3; +f1 f2 f3 +2 x x +5 y x +set @@optimizer_switch = @old_optimizer_switch; +drop table t1, t2; +drop view v2; +# +# LP BUG#611396 RQG: crash in Item_field::register_field_in_read_map with semijoin=off +# and prepared statements and materialization +CREATE TABLE t1 ( f1 int(11), f2 int(11)) ; +CREATE TABLE t2 ( f1 int(11), f4 varchar(1), PRIMARY KEY (f1)) ; +INSERT INTO t2 VALUES ('23','j'),('24','e'); +CREATE TABLE t3 ( f1 int(11), f4 varchar(1)) ; +INSERT INTO t3 VALUES ('8','j'); +set @old_optimizer_switch = @@optimizer_switch; +set @@optimizer_switch='materialization=on,in_to_exists=off,semijoin=off'; +EXPLAIN +SELECT t2.f1, (SELECT f2 FROM t1 WHERE (7) IN (SELECT f1 FROM t1)) +FROM t2 JOIN t3 ON t3.f4 = t2.f4 +WHERE t3.f1 = 8 +GROUP BY 1, 2; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t3 system NULL NULL NULL NULL 1 +1 PRIMARY t2 ALL NULL NULL NULL NULL 2 Using where; Using filesort +2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE +3 MATERIALIZED NULL NULL NULL NULL NULL NULL NULL no matching row in const table +PREPARE st1 FROM " +SELECT t2.f1, (SELECT f2 FROM t1 WHERE (7) IN (SELECT f1 FROM t1)) +FROM t2 JOIN t3 ON t3.f4 = t2.f4 +WHERE t3.f1 = 8 +GROUP BY 1, 2"; +EXECUTE st1; +f1 (SELECT f2 FROM t1 WHERE (7) IN (SELECT f1 FROM t1)) +23 NULL +EXECUTE st1; +f1 (SELECT f2 FROM t1 WHERE (7) IN (SELECT f1 FROM t1)) +23 NULL +set @@optimizer_switch = @old_optimizer_switch; +drop table t1, t2, t3; +# +# LP BUG#611382 RQG: Query returns extra rows when executed with materialization=on +# +CREATE TABLE t1 ( f4 varchar(1)) ENGINE=MyISAM; +INSERT INTO t1 VALUES (NULL); +CREATE TABLE t2 ( f2 date, f3 varchar(1), f4 varchar(1)) ; +INSERT INTO t2 VALUES ('2005-05-03','c','c'),('1900-01-01','d','d'); +CREATE TABLE t3 ( f3 varchar(1)) ; +INSERT INTO t3 VALUES ('c'); +set @old_optimizer_switch = @@optimizer_switch; +set @@optimizer_switch = 'materialization=on,in_to_exists=off,semijoin=off'; +EXPLAIN SELECT t1.f4 +FROM t1 JOIN ( t2 JOIN t3 ON t3.f3 = t2.f4 ) ON t3.f3 = t2.f3 +WHERE t1.f4 IN ( SELECT f4 FROM t2 ) ; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 system NULL NULL NULL NULL 1 +1 PRIMARY t3 system NULL NULL NULL NULL 1 +1 PRIMARY t2 ALL NULL NULL NULL NULL 2 Using where +2 MATERIALIZED t2 ALL NULL NULL NULL NULL 2 +SELECT t1.f4 +FROM t1 JOIN ( t2 JOIN t3 ON t3.f3 = t2.f4 ) ON t3.f3 = t2.f3 +WHERE t1.f4 IN ( SELECT f4 FROM t2 ) ; +f4 +set @@optimizer_switch = 'materialization=off,in_to_exists=on,semijoin=off'; +EXPLAIN SELECT t1.f4 +FROM t1 JOIN ( t2 JOIN t3 ON t3.f3 = t2.f4 ) ON t3.f3 = t2.f3 +WHERE t1.f4 IN ( SELECT f4 FROM t2 ) ; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 system NULL NULL NULL NULL 1 +1 PRIMARY t3 system NULL NULL NULL NULL 1 +1 PRIMARY t2 ALL NULL NULL NULL NULL 2 Using where +2 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 2 Using where +SELECT t1.f4 +FROM t1 JOIN ( t2 JOIN t3 ON t3.f3 = t2.f4 ) ON t3.f3 = t2.f3 +WHERE t1.f4 IN ( SELECT f4 FROM t2 ) ; +f4 +set @@optimizer_switch = @old_optimizer_switch; +drop table t1, t2, t3; +# +# LP BUG#782305: Wrong result/valgrind warning in Item_sum_hybrid::any_value() +# +CREATE TABLE t1 ( f1 int) ; +INSERT INTO t1 VALUES (2),(3); +CREATE TABLE t2 (f2 int) ; +INSERT INTO t2 VALUES (2),(3); +PREPARE st1 FROM ' +SELECT * FROM t2 +WHERE f2 <= SOME ( SELECT f1 FROM t1 ); +'; +EXECUTE st1; +f2 +2 +3 +EXECUTE st1; +f2 +2 +3 +PREPARE st2 FROM ' +SELECT * FROM t2 +WHERE f2 <= SOME (SELECT f1-2 FROM t1 UNION SELECT f1-1 FROM t1); +'; +EXECUTE st2; +f2 +2 +EXECUTE st2; +f2 +2 +drop table t1, t2; +# +# LP BUG#825018: Crash in check_and_do_in_subquery_rewrites() with corrlated subquery in select list +# +CREATE TABLE t1 (a int, b int); +INSERT INTO t1 VALUES (10,1),(11,7); +CREATE TABLE t2 (a int); +INSERT INTO t2 VALUES (2),(3); +CREATE TABLE t3 (a int, b int); +INSERT INTO t3 VALUES (1,1); +CREATE PROCEDURE sp1 () LANGUAGE SQL +SELECT (SELECT t1.a +FROM t1 +WHERE t1.b = t3.b +AND t1.b IN ( SELECT a FROM t2 )) sq +FROM t3 +GROUP BY 1; +CALL sp1(); +sq +NULL +CALL sp1(); +sq +NULL +drop procedure sp1; +prepare st1 from " +SELECT (SELECT t1.a + FROM t1 + WHERE t1.b = t3.b + AND t1.b IN ( SELECT a FROM t2 )) sq +FROM t3 +GROUP BY 1"; +execute st1; +sq +NULL +execute st1; +sq +NULL +deallocate prepare st1; +drop table t1, t2, t3; +set optimizer_switch=@subselect4_tmp; +# +# LP BUG#833702 Wrong result with nested IN and singlerow subqueries and equality propagation +# +CREATE TABLE t2 (c int , a int, b int); +INSERT INTO t2 VALUES (10,7,0); +CREATE TABLE t3 (a int, b int) ; +INSERT INTO t3 VALUES (5,0),(7,0); +CREATE TABLE t4 (a int); +INSERT INTO t4 VALUES (2),(8); +set @@optimizer_switch='semijoin=off,in_to_exists=on,materialization=off,subquery_cache=off'; +SELECT * FROM t2 +WHERE t2.b IN (SELECT b FROM t3 WHERE t3.a = t2.a AND a < SOME (SELECT * FROM t4)) +OR ( t2.c > 242 ); +c a b +10 7 0 +EXPLAIN SELECT * FROM t2 +WHERE t2.b IN (SELECT t3.b FROM t3 WHERE t3.a < ANY (SELECT t4.a FROM t4) and t3.a = 7); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t2 system NULL NULL NULL NULL 1 +2 DEPENDENT SUBQUERY t3 ALL NULL NULL NULL NULL 2 Using where +3 SUBQUERY t4 ALL NULL NULL NULL NULL 2 +SELECT * FROM t2 +WHERE t2.b IN (SELECT t3.b FROM t3 WHERE t3.a < ANY (SELECT t4.a FROM t4) and t3.a = 7); +c a b +10 7 0 +drop table t2, t3, t4; +# +# BUG#934597: Assertion `! is_set()' failed in Diagnostics_area::set_ok_status(THD... +# +CREATE TABLE t1 ( a VARCHAR(1) ); +INSERT INTO t1 VALUES ('u'),('k'); +CREATE TABLE t2 AS +SELECT a AS field1 FROM t1 +WHERE ( SELECT alias1.a +FROM t1 AS alias1 +) IS NOT NULL; +ERROR 21000: Subquery returns more than 1 row +DROP TABLE t2; +ERROR 42S02: Unknown table 'test.t2' +DROP TABLE t1; +# +# LP BUG#1000649 EXPLAIN shows incorrectly a non-correlated constant IN subquery is correlated +# +create table ten (a int); +insert into ten values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); +create table t1 (a int, b int, c int); +insert into t1 select a,a,a from ten; +create table five (a int, b int, c int); +insert into five select a,a,a from ten limit 5; +set @@optimizer_switch='semijoin=on,in_to_exists=on,materialization=off'; +explain select * from t1 where 33 in (select b from five) or c > 11; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 10 Using where +2 SUBQUERY five ALL NULL NULL NULL NULL 5 Using where +drop table ten, t1, five; +# +# LP BUG#1008773 Wrong result (NULL instead of a value) with no matching rows, subquery in FROM and HAVING +# +CREATE TABLE t1 (a INT) ENGINE=MyISAM; +CREATE TABLE t2 (b INT) ENGINE=MyISAM; +INSERT INTO t2 VALUES (1); +EXPLAIN +SELECT MAX(a), ( SELECT 1 FROM t2 ) AS bb FROM t1; +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 t2 system NULL NULL NULL NULL 1 +SELECT MAX(a), ( SELECT 1 FROM t2 ) AS bb FROM t1; +MAX(a) bb +NULL 1 +EXPLAIN +SELECT MAX(a), 1 in ( SELECT b FROM t2 ) AS bb FROM t1; +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 t2 system NULL NULL NULL NULL 1 +SELECT MAX(a), 1 in ( SELECT b FROM t2 ) AS bb FROM t1; +MAX(a) bb +NULL 1 +EXPLAIN +SELECT MAX(a), 1 >= ALL ( SELECT b FROM t2 ) AS bb FROM t1; +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 Impossible WHERE noticed after reading const tables +SELECT MAX(a), 1 >= ALL ( SELECT b FROM t2 ) AS bb FROM t1; +MAX(a) bb +NULL 1 +EXPLAIN +SELECT MAX(a), ( SELECT 1 FROM t2 where b = a) AS bb FROM t1; +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 Impossible WHERE noticed after reading const tables +SELECT MAX(a), ( SELECT 1 FROM t2 where b = a) AS bb FROM t1; +MAX(a) bb +NULL NULL +EXPLAIN +SELECT MAX(a), a in ( SELECT b FROM t2 ) AS bb FROM t1; +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 DEPENDENT SUBQUERY t2 system NULL NULL NULL NULL 1 +SELECT MAX(a), a in ( SELECT b FROM t2 ) AS bb FROM t1; +MAX(a) bb +NULL NULL +EXPLAIN +SELECT MAX(a), a >= ALL ( SELECT b FROM t2 ) AS bb FROM t1; +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 DEPENDENT SUBQUERY t2 system NULL NULL NULL NULL 1 +SELECT MAX(a), a >= ALL ( SELECT b FROM t2 ) AS bb FROM t1; +MAX(a) bb +NULL NULL +drop table t1, t2; +set optimizer_switch=@subselect4_tmp; +# +# MDEV-3928 Assertion `example' failed in Item_cache::is_expensive_processor with a 2-level IN subquery +# +CREATE TABLE t1 (a1 INT, b1 TIME) ENGINE=MyISAM; +INSERT INTO t1 VALUES (4,'21:22:34'),(6,'10:50:38'); +CREATE TABLE t2 (a2 INT, b2 TIME) ENGINE=MyISAM; +INSERT INTO t2 VALUES (8, '06:17:39'); +CREATE TABLE t3 (a3 INT, b3 TIME) ENGINE=MyISAM; +INSERT INTO t3 VALUES (1,'00:00:01'),(7,'00:00:02'); +EXPLAIN +SELECT * FROM t1 WHERE a1 IN ( +SELECT a2 FROM t2 WHERE a2 IN ( +SELECT a3 FROM t3 WHERE b2 = b1 AND b2 <= b1 ORDER BY b3 +) +); +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 ALL NULL NULL NULL NULL 2 Using where +1 PRIMARY t3 ALL NULL NULL NULL NULL 2 Using where; FirstMatch(t1); Using join buffer (flat, BNL join) +SELECT * FROM t1 WHERE a1 IN ( +SELECT a2 FROM t2 WHERE a2 IN ( +SELECT a3 FROM t3 WHERE b2 = b1 AND b2 <= b1 ORDER BY b3 +) +); +a1 b1 +drop table t1, t2, t3; +# +# MDEV-4056:Server crashes in Item_func_trig_cond::val_int +# with FROM and NOT IN subqueries, LEFT JOIN, derived_merge+in_to_exists +# +set @optimizer_switch_MDEV4056 = @@optimizer_switch; +SET optimizer_switch = 'derived_merge=on,in_to_exists=on'; +CREATE TABLE t1 (a VARCHAR(1)) ENGINE=MyISAM; +INSERT INTO t1 VALUES ('x'),('d'); +CREATE TABLE t2 (pk INT PRIMARY KEY, b INT, c VARCHAR(1)) ENGINE=MyISAM; +INSERT INTO t2 VALUES (1,2,'v'),(2,150,'v'); +SELECT * FROM t1 LEFT JOIN ( +SELECT * FROM t2 WHERE ( pk, pk ) NOT IN ( +SELECT MIN(b), SUM(pk) FROM t1 +) +) AS alias1 ON (a = c) +WHERE b IS NULL OR a < 'u'; +a pk b c +x NULL NULL NULL +d NULL NULL NULL +drop table t1,t2; +set @@optimizer_switch = @optimizer_switch_MDEV4056; +# +# MDEV-5103: server crashed on singular Item_equal +# +CREATE TABLE t1 ( +a enum('p','r') NOT NULL DEFAULT 'r', +b int NOT NULL DEFAULT '0', +c char(32) NOT NULL, +d varchar(255) NOT NULL, +PRIMARY KEY (a, b), UNIQUE KEY idx(a, c) +); +INSERT INTO t1 VALUES ('r', 1, 'ad18832202b199728921807033a8a515', '001_cbr643'); +CREATE TABLE t2 ( +a enum('p','r') NOT NULL DEFAULT 'r', +b int NOT NULL DEFAULT '0', +e datetime NOT NULL DEFAULT '0000-00-00 00:00:00', +PRIMARY KEY (a, b, e) +); +INSERT INTO t2 VALUES ('r', 1, '2013-10-05 14:25:30'); +SELECT * FROM t1 AS t +WHERE a='r' AND (c,b) NOT IN (SELECT c,b FROM t2 WHERE (c,b)=(t.c,t.b)); +a b c d +DROP TABLE t1, t2; +# +# MDEV-5468: assertion failure with a simplified condition in subselect +# +CREATE TABLE t1 (a int, b int) ENGINE=MyISAM; +INSERT INTO t1 VALUES (1,1); +CREATE TABLE t2 ( pk int PRIMARY KEY, c INT) ENGINE=MyISAM; +INSERT INTO t2 VALUES (1,4), (2,6); +SELECT ( SELECT MAX(b) FROM t1, t2 WHERE pk = a AND b < from_sq.c ) AS select_sq, +COUNT( DISTINCT from_sq.c ) +FROM ( SELECT DISTINCT t2_1.* FROM t2 AS t2_1, t2 AS t2_2 ) AS from_sq +GROUP BY select_sq ; +select_sq COUNT( DISTINCT from_sq.c ) +1 2 +DROP TABLE t1,t2; +CREATE TABLE t1 (id int, a2 char(2), a3 char(3)) ENGINE=MyISAM; +INSERT INTO t1 VALUES (1,'BE','BEL'); +CREATE TABLE t2 (id int, a2 char(2), a3 char(3)) ENGINE=MyISAM; +INSERT INTO t2 VALUES (1,'BE','BEL'), (2,'MX','MEX'); +CREATE VIEW v2 AS SELECT DISTINCT * FROM t2; +SELECT * FROM t1 AS outer_t1, v2 +WHERE v2.a3 = outer_t1.a3 +AND EXISTS ( SELECT * FROM t1 WHERE a2 < v2.a2 AND id = outer_t1.id ) +AND outer_t1.a3 < 'J' +ORDER BY v2.id; +id a2 a3 id a2 a3 +DROP VIEW v2; +DROP TABLE t1,t2; +# +# MDEV-5686: degenerate disjunct in NOT IN subquery +# +CREATE TABLE t1 (a int, b int, c varchar(3)) ENGINE=MyISAM; +INSERT INTO t1 VALUES (1,1,'CAN'),(2,2,'AUS'); +CREATE TABLE t2 (f int) ENGINE=MyISAM; +INSERT INTO t2 VALUES (3); +EXPLAIN EXTENDED +SELECT * FROM t2 +WHERE f NOT IN (SELECT b FROM t1 +WHERE 0 OR (c IN ('USA') OR c NOT IN ('USA')) AND a = b); +id select_type table type possible_keys key key_len ref rows filtered Extra +1 PRIMARY t2 system NULL NULL NULL NULL 1 100.00 +2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 2 100.00 Using where +Warnings: +Note 1003 /* select#1 */ select 3 AS `f` from dual where !<expr_cache><3>(<in_optimizer>(3,<exists>(/* select#2 */ select `test`.`t1`.`b` from `test`.`t1` where `test`.`t1`.`b` = `test`.`t1`.`a` and (`test`.`t1`.`c` = 'USA' or `test`.`t1`.`c` <> 'USA') and trigcond(<cache>(3) = `test`.`t1`.`b` or `test`.`t1`.`b` is null) having trigcond(`test`.`t1`.`b` is null)))) +SELECT * FROM t2 +WHERE f NOT IN (SELECT b FROM t1 +WHERE 0 OR (c IN ('USA') OR c NOT IN ('USA')) AND a = b); +f +3 +DROP TABLE t1,t2; +# +# MDEV-3899 Valgrind warnings (blocks are definitely lost) in filesort on IN subquery with SUM and DISTINCT +# +CREATE TABLE t1 (a INT) ENGINE=MyISAM; +INSERT INTO t1 VALUES (1),(9); +CREATE TABLE t2 (b INT) ENGINE=MyISAM; +INSERT INTO t2 VALUES (8); +SELECT * FROM t1 +WHERE (1, 1) IN (SELECT a, SUM(DISTINCT a) FROM t1, t2 GROUP BY a); +a +1 +9 +drop table t1, t2; +# +# MDEV-3902 Assertion `record_length == m_record_length' failed at Filesort_buffer::alloc_sort_buffer +# +CREATE TABLE t1 (a INT) ENGINE=MyISAM; +INSERT INTO t1 VALUES (1),(2); +CREATE TABLE t2 (pk INT PRIMARY KEY, b INT) ENGINE=MyISAM; +INSERT INTO t2 VALUES (1,1),(2,7); +CREATE TABLE t3 (c INT) ENGINE=MyISAM; +INSERT INTO t3 VALUES (8); +SELECT * FROM t1 +WHERE (1, 5) IN (SELECT b, SUM(DISTINCT b) FROM t2, t3 GROUP BY b); +a +SELECT * FROM t2 AS alias1, t2 AS alias2 +WHERE EXISTS ( SELECT 1 ) AND (alias2.pk = alias1.b ) +ORDER BY alias1.b; +pk b pk b +1 1 1 1 +drop table t1, t2, t3; +# +# MDEV-4144 simple subquery causes full scan instead of range scan +# +CREATE TABLE t1 (id int not null auto_increment, x int not null, primary key(id)); +INSERT INTO t1 (x) VALUES (0),(0),(0); +EXPLAIN +SELECT x FROM t1 WHERE id > (SELECT MAX(id) - 1000 FROM t1) ORDER BY x LIMIT 1; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 range PRIMARY PRIMARY 4 NULL 3 Using where; Using filesort +2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL Select tables optimized away +SELECT x FROM t1 WHERE id > (SELECT MAX(id) - 1000 FROM t1) ORDER BY x LIMIT 1; +x +0 +drop table t1; +# +# MDEV-7691: Assertion `outer_context || !*from_field || *from_field == not_found_field' ... +# +set optimizer_switch=default; +CREATE TABLE t1 (a INT) ENGINE=MyISAM; +INSERT INTO t1 VALUES (4),(6); +CREATE TABLE t2 (b INT) ENGINE=MyISAM; +INSERT INTO t2 VALUES (1),(8); +PREPARE stmt FROM " +SELECT * FROM t2 +HAVING 0 IN ( + SELECT a FROM t1 + WHERE a IN ( + SELECT a FROM t1 + WHERE b = a + ) +) +"; +EXECUTE stmt; +b +EXECUTE stmt; +b +# Alternative test case, without HAVING +CREATE TABLE t3 (i INT) ENGINE=MyISAM; +INSERT INTO t3 VALUES (4),(6); +PREPARE stmt FROM " +SELECT * FROM t3 AS t10 +WHERE EXISTS ( + SELECT * FROM t3 AS t20 WHERE t10.i IN ( + SELECT i FROM t3 + ) +)"; +EXECUTE stmt; +i +4 +6 +EXECUTE stmt; +i +4 +6 +drop table t1, t2, t3; +# +# MDEV-11078: NULL NOT IN (non-empty subquery) should never return results +# +create table t1(a int,b int); +create table t2(a int,b int); +insert into t1 value (1,2); +select (NULL) in (select 1 from t1); +(NULL) in (select 1 from t1) +NULL +select (null) in (select 1 from t2); +(null) in (select 1 from t2) +0 +select 1 in (select 1 from t1); +1 in (select 1 from t1) +1 +select 1 in (select 1 from t2); +1 in (select 1 from t2) +0 +select 1 from dual where null in (select 1 from t1); +1 +select 1 from dual where null in (select 1 from t2); +1 +select (null,null) in (select * from t1); +(null,null) in (select * from t1) +NULL +select (null,null) in (select * from t2); +(null,null) in (select * from t2) +0 +select 1 from dual where null not in (select 1 from t1); +1 +select 1 from dual where null not in (select 1 from t2); +1 +1 +drop table t1,t2; +# +# MDEV-6486: Assertion `!table || (!table->read_set || bitmap_is_set(table->read_set, field_index))' +# failed with SELECT SQ, TEXT field +# +CREATE TABLE t1 (a VARCHAR(8), KEY(a)) ENGINE=MyISAM; +INSERT INTO t1 VALUES ('foo'),( 'bar'); +CREATE TABLE t2 (b VARCHAR(8), c TINYTEXT, KEY(b)) ENGINE=MyISAM; +INSERT INTO t2 VALUES ('baz','baz'),('qux', 'qux'); +SELECT ( SELECT COUNT(*) FROM t1 WHERE a = c ) AS field, COUNT(DISTINCT c) +FROM t2 WHERE b <= 'quux' GROUP BY field; +field COUNT(DISTINCT c) +0 1 +drop table t1,t2; +# +# MDEV-15555: select from DUAL where false yielding wrong result when in a IN +# +explain +SELECT 2 IN (SELECT 2 from DUAL WHERE 1 != 1); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY NULL NULL NULL NULL NULL NULL NULL No tables used +2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE +SELECT 2 IN (SELECT 2 from DUAL WHERE 1 != 1); +2 IN (SELECT 2 from DUAL WHERE 1 != 1) +0 +SET optimizer_switch= @@global.optimizer_switch; +set @@tmp_table_size= @@global.tmp_table_size; +# +# MDEV-14515: Wrong results for tableless query with subquery in WHERE +# and implicit aggregation +# +create table t1 (i1 int, i2 int); +insert into t1 values (1314, 1084),(1330, 1084),(1401, 1084),(580, 1084); +create table t2 (cd int); +insert into t2 values +(1330), (1330), (1330), (1330), (1330), (1330), (1330), (1330), +(1330), (1330), (1330), (1330), (1330), (1330), (1330), (1330); +select max(10) from dual +where exists (select 1 from t2 join t1 on t1.i1 = t2.cd and t1.i2 = 345); +max(10) +NULL +insert into t2 select * from t2; +select max(10) from dual +where exists (select 1 from t2 join t1 on t1.i1 = t2.cd and t1.i2 = 345); +max(10) +NULL +DROP TABLE t1,t2; +# +# MDEV-10232 Scalar result of subquery changes after adding an outer select stmt +# +create table t1(c1 int, c2 int, primary key(c2)); +insert into t1 values(2,1),(1,2); +select (select c1 from t1 group by c1,c2 order by c1 limit 1) as x; +x +1 +(select c1 from t1 group by c1,c2 order by c1 limit 1); +c1 +1 +drop table t1; +# +# MDEV-22498: SIGSEGV in Bitmap<64u>::merge on SELECT +# +set @save_sql_select_limit= @@sql_select_limit; +SET sql_select_limit=0; +CREATE TABLE t1(b INT, c INT); +CREATE TABLE t2(a INT, b INT); +INSERT INTO t1 VALUES (1,1),(2,2),(3,3); +INSERT INTO t2 VALUES (1,1),(2,2),(3,3); +EXPLAIN EXTENDED SELECT sum(a), t2.a, t2.b FROM t2 HAVING t2.a IN (SELECT t2.b FROM t1); +id select_type table type possible_keys key key_len ref rows filtered Extra +1 PRIMARY NULL NULL NULL NULL NULL NULL NULL NULL Zero limit +2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 3 100.00 +Warnings: +Note 1276 Field or reference 'test.t2.b' of SELECT #2 was resolved in SELECT #1 +Note 1276 Field or reference 'test.t2.b' of SELECT #2 was resolved in SELECT #1 +Note 1003 /* select#1 */ select sum(`test`.`t2`.`a`) AS `sum(a)`,`test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b` from `test`.`t2` having <in_optimizer>(`test`.`t2`.`a`,<exists>(/* select#2 */ select `test`.`t2`.`b` from `test`.`t1` where <cache>(`test`.`t2`.`a`) = `test`.`t2`.`b`)) +SELECT sum(a), t2.a, t2.b FROM t2 HAVING t2.a IN (SELECT t2.b FROM t1); +sum(a) a b +SET @@sql_select_limit= @save_sql_select_limit; +EXPLAIN EXTENDED SELECT sum(a), t2.a, t2.b FROM t2 HAVING t2.a IN (SELECT t2.b FROM t1); +id select_type table type possible_keys key key_len ref rows filtered Extra +1 PRIMARY t2 ALL NULL NULL NULL NULL 3 100.00 +2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 3 100.00 +Warnings: +Note 1276 Field or reference 'test.t2.b' of SELECT #2 was resolved in SELECT #1 +Note 1276 Field or reference 'test.t2.b' of SELECT #2 was resolved in SELECT #1 +Note 1003 /* select#1 */ select sum(`test`.`t2`.`a`) AS `sum(a)`,`test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b` from `test`.`t2` having <expr_cache><`test`.`t2`.`a`,`test`.`t2`.`b`>(<in_optimizer>(`test`.`t2`.`a`,<exists>(/* select#2 */ select `test`.`t2`.`b` from `test`.`t1` where <cache>(`test`.`t2`.`a`) = `test`.`t2`.`b`))) +SELECT sum(a), t2.a, t2.b FROM t2 HAVING t2.a IN (SELECT t2.b FROM t1); +sum(a) a b +6 1 1 +DROP TABLE t1,t2; +# +# MDEV-17606: Query returns wrong results (while using CHARACTER SET utf8) +# +CREATE TABLE t1(l1 varchar(10), i2 int); +INSERT INTO t1 VALUES ('e',2),('o',6),('x',4); +CREATE TABLE t2 (v1 varchar(10) CHARACTER SET utf8, KEY v1 (v1(3))); +INSERT INTO t2 VALUES ('k'),('rid'),('f'),('x'); +EXPLAIN EXTENDED SELECT * FROM t1 where ( t1.l1 < ANY (SELECT MAX(t2.v1) FROM t2)); +id select_type table type possible_keys key key_len ref rows filtered Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 3 100.00 Using where +2 SUBQUERY t2 ALL NULL NULL NULL NULL 4 100.00 +Warnings: +Note 1003 /* select#1 */ select `test`.`t1`.`l1` AS `l1`,`test`.`t1`.`i2` AS `i2` from `test`.`t1` where <nop>(<in_optimizer>(`test`.`t1`.`l1`,<max>(/* select#2 */ select max(`test`.`t2`.`v1`) from `test`.`t2`) > convert(<cache>(`test`.`t1`.`l1`) using utf8mb3))) +SELECT * FROM t1 where ( t1.l1 < ANY (SELECT MAX(t2.v1) FROM t2)); +l1 i2 +e 2 +o 6 +DROP TABLE t1, t2; +# +# MDEV-19232: Floating point precision / value comparison problem +# +CREATE TABLE t1 (region varchar(60), area decimal(10,0), population decimal(11,0)); +INSERT INTO t1 VALUES ('Central America and the Caribbean',91,11797); +INSERT INTO t1 VALUES ('Central America and the Caribbean',442,66422); +SET @save_optimizer_switch=@@optimizer_switch; +SET optimizer_switch='subquery_cache=on'; +SELECT +population, area, population/area, +cast(population/area as DECIMAL(20,9)) FROM t1 LIMIT 1; +population area population/area cast(population/area as DECIMAL(20,9)) +11797 91 129.6374 129.637362637 +SELECT * FROM t1 A +WHERE population/area = (SELECT MAX(population/area) from t1 B where A.region = B.region); +region area population +Central America and the Caribbean 442 66422 +SET optimizer_switch='subquery_cache=off'; +SELECT * FROM t1 A +WHERE population/area = (SELECT MAX(population/area) from t1 B where A.region = B.region); +region area population +Central America and the Caribbean 442 66422 +SET @@optimizer_switch= @save_optimizer_switch; +DROP TABLE t1; +# +# MDEV-9513: Assertion `join->group_list || !join->is_in_subquery()' failed in create_sort_index +# +CREATE TABLE t1 (a INT); +INSERT INTO t1 VALUES (1),(2); +CREATE TABLE t2 (a INT); +INSERT INTO t2 VALUES (2),(3); +EXPLAIN +SELECT t1.a FROM t1 WHERE t1.a IN ( SELECT A.a FROM t1 A UNION SELECT B.a FROM t2 B ORDER BY 1); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 2 Using where +2 DEPENDENT SUBQUERY A ALL NULL NULL NULL NULL 2 Using where +3 DEPENDENT UNION B ALL NULL NULL NULL NULL 2 Using where +NULL UNION RESULT <union2,3> ALL NULL NULL NULL NULL NULL +SELECT t1.a FROM t1 WHERE t1.a IN ( SELECT A.a FROM t1 A UNION SELECT B.a FROM t2 B ORDER BY 1); +a +1 +2 +EXPLAIN +SELECT t1.a FROM t1 WHERE EXISTS (SELECT A.a FROM t1 A UNION SELECT B.a FROM t2 B ORDER BY 1); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 2 +2 SUBQUERY A ALL NULL NULL NULL NULL 2 +3 UNION B ALL NULL NULL NULL NULL 2 +NULL UNION RESULT <union2,3> ALL NULL NULL NULL NULL NULL +SELECT t1.a FROM t1 WHERE EXISTS (SELECT A.a FROM t1 A UNION SELECT B.a FROM t2 B ORDER BY 1); +a +1 +2 +EXPLAIN +SELECT t1.a FROM t1 WHERE t1.a IN ( SELECT A.a FROM t1 A UNION ALL SELECT B.a FROM t2 B ORDER BY 1); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 2 Using where +2 DEPENDENT SUBQUERY A ALL NULL NULL NULL NULL 2 Using where +3 DEPENDENT UNION B ALL NULL NULL NULL NULL 2 Using where +SELECT t1.a FROM t1 WHERE t1.a IN ( SELECT A.a FROM t1 A UNION ALL SELECT B.a FROM t2 B ORDER BY 1); +a +1 +2 +DROP TABLE t1,t2; +# end of 10.1 tests +# +# MDEV-22852: SIGSEGV in sortlength (optimized builds) +# +SET @save_optimizer_switch=@@optimizer_switch; +SET optimizer_switch='subquery_cache=off'; +CREATE TABLE t1 (a INT,b INT); +INSERT INTO t1 VALUES (0,0),(0,0); +SELECT (SELECT DISTINCT t1i.b FROM t1 t1i GROUP BY t1i.a ORDER BY MAX(t1o.b)) as exp FROM t1 AS t1o; +exp +0 +SET @@optimizer_switch= @save_optimizer_switch; +DROP TABLE t1; +# +# MDEV-17066: Bytes lost or Assertion `status_var.local_memory_used == 0 after DELETE +# with subquery with ROLLUP +# +CREATE TABLE t1 (i INT DEFAULT 0, c VARCHAR(2048)); +INSERT INTO t1 SELECT 0, seq FROM seq_1_to_6000; +CREATE TABLE t2 (f VARCHAR(2048) DEFAULT ''); +INSERT INTO t2 VALUES ('1'),('bar'); +EXPLAIN +SELECT * FROM t2 WHERE f IN ( SELECT MAX(c) FROM t1 GROUP BY c WITH ROLLUP); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t2 ALL NULL NULL NULL NULL 2 Using where +2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 6000 Using filesort +SELECT * FROM t2 WHERE f IN ( SELECT MAX(c) FROM t1 GROUP BY c WITH ROLLUP); +f +1 +SELECT * FROM t2; +f +1 +bar +DELETE FROM t2 WHERE f IN ( SELECT MAX(c) FROM t1 GROUP BY c WITH ROLLUP ); +SELECT * FROM t2; +f +bar +DROP TABLE t1, t2; +# +# MDEV-23449: alias do not exist and a query do not report an error +# +CREATE TABLE t1(a INT, b INT); +INSERT INTO t1 VALUES (1,1), (2,2), (3,3), (4,4), (5,5); +SELECT a, b FROM t1 WHERE a IN (SELECT A.a FROM t1 A GROUP BY s.id); +ERROR 42S22: Unknown column 's.id' in 'group statement' +DROP TABLE t1; +# +# MDEV-24519: Server crashes in Charset::set_charset upon SELECT +# +CREATE TABLE t1 (a VARBINARY(8)); +INSERT INTO t1 VALUES ('foo'),('bar'); +CREATE TABLE t2 (b VARBINARY(8)); +EXPLAIN +SELECT a FROM t1 WHERE (a, a) IN (SELECT 'qux', 'qux') AND a = (SELECT MIN(b) FROM t2); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE +3 SUBQUERY NULL NULL NULL NULL NULL NULL NULL no matching row in const table +2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL No tables used +SELECT a FROM t1 WHERE (a, a) IN (SELECT 'qux', 'qux') AND a = (SELECT MIN(b) FROM t2); +a +DROP TABLE t1,t2; +CREATE TABLE t1 (a INT); +INSERT INTO t1 VALUES (1),(2); +CREATE TABLE t2 (b VARBINARY(8)); +EXPLAIN +SELECT a FROM t1 WHERE (a, a) IN (SELECT 1, 2) AND a = (SELECT MIN(b) FROM t2); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 2 Using where +3 SUBQUERY NULL NULL NULL NULL NULL NULL NULL no matching row in const table +2 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL No tables used +SELECT a FROM t1 WHERE (a, a) IN (SELECT 1, 2) AND a = (SELECT MIN(b) FROM t2); +a +DROP TABLE t1,t2; +# +# MDEV-22462: Item_in_subselect::create_single_in_to_exists_cond(JOIN *, Item **, Item **): Assertion `false' failed. +# +select 1 from dual where 1 in (select 5 from dual where 1); +1 +create table t1 (a int); +insert into t1 values (1),(2),(3); +update t1 set a = 2 where a in (select a from dual where a = a); +drop table t1; +# +# MDEV-18335: Assertion `!error || error == 137' failed in subselect_rowid_merge_engine::init +# +CREATE TABLE t1 (i1 int,v1 varchar(1),KEY (v1,i1)); +INSERT INTO t1 VALUES +(9,'y'),(4,'q'),(0,null),(0,'p'),(null,'j'); +CREATE TABLE t2 (pk int); +INSERT INTO t2 VALUES (1),(2); +CREATE TABLE t3 (v2 varchar(1)); +INSERT INTO t3 VALUES +('p'),('j'),('y'),('q'); +CREATE TABLE t4 (v2 varchar(1)); +INSERT INTO t4 VALUES +('a'),('a'),('b'),('b'),('c'),('c'), +('d'),('d'),('e'),('e'),('f'),('f'), +('g'),('g'),('h'),('h'),('i'),('i'), +('j'),('j'),('k'),('k'),('l'),('l'), +('m'),('m'),('n'),('n'),('o'),('o'), +('p'),('p'),('q'),('q'),('r'),('r'), +('s'),('s'),('t'),('t'),('u'),('u'),('v'),('v'), +('w'),('w'),('x'),('x'), (NULL),(NULL); +SET @save_join_cache_level=@@join_cache_level; +SET join_cache_level=0; +select 1 +from t2 join t1 on +('i','w') not in (select t1.v1,t4.v2 from t4,t1,t3 where t3.v2 = t1.v1) LIMIT ROWS EXAMINED 500; +1 +Warnings: +Warning 1931 Query execution was interrupted. The query exceeded LIMIT ROWS EXAMINED 500. The query result may be incomplete +SET join_cache_level= @save_join_cache_level; +DROP TABLE t1,t2,t3,t4; +# +# MDEV-21265: IN predicate conversion to IN subquery should be allowed for a broader set of datatype comparison +# +CREATE TABLE t1(a VARCHAR(50) collate utf8_general_ci, b INT); +INSERT INTO t1 VALUES ('abc',1), ('def', 2), ('ghi', 3), ('jkl', 4), ('mno', 5); +CREATE TABLE t2(a VARCHAR(50) collate utf8mb4_general_ci, b INT); +INSERT INTO t2 VALUES ('abc',1), ('def', 2), ('ghi', 3), ('jkl', 4), ('mno', 5); +set @save_in_predicate_conversion_threshold= @@in_predicate_conversion_threshold; +set in_predicate_conversion_threshold=2; +set names 'utf8mb4'; +# +# IN predicate to IN subquery is not allowed as materialization is not allowed +# The character set on the inner side is not equal to or a proper subset of the outer side +# +EXPLAIN +SELECT * FROM t1 WHERE (t1.a,t1.b) IN (('abx',1),('def',2), ('abc', 3)); +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 5 Using where +set names 'utf8'; +# +# IN predicate to IN subquery is performed as materialization is llowed +# The character set on the inner side is a proper subset of the outer side +# +# this test in 10.5 has only 2 rows in the IN predicate +EXPLAIN +SELECT * FROM t2 WHERE (t2.a,t2.b) IN (('abc',1), ('def', 2)); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t2 ALL NULL NULL NULL NULL 5 +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 16 func,func 1 Using where +2 MATERIALIZED <derived3> ALL NULL NULL NULL NULL 2 +3 DERIVED NULL NULL NULL NULL NULL NULL NULL No tables used +set names default; +set @@in_predicate_conversion_threshold= @save_in_predicate_conversion_threshold; +DROP TABLE t1,t2; +# +# MDEV-24925: Server crashes in Item_subselect::init_expr_cache_tracker +# +CREATE TABLE t1 (id INT PRIMARY KEY); +INSERT INTO t1 VALUES (1),(2); +SELECT +1 IN ( +SELECT +(SELECT COUNT(id) +FROM t1 +WHERE t1_outer.id <> id +) AS f +FROM +t1 AS t1_outer +GROUP BY f +) as exp; +exp +1 +SELECT +1 IN ( +SELECT +(SELECT COUNT(id) +FROM t1 +WHERE t1_outer.id <> id +) AS f +FROM +t1 AS t1_outer +GROUP BY 1 +) as exp; +exp +1 +DROP TABLE t1; +# +# MDEV-24898: Server crashes in st_select_lex::next_select / Item_subselect::is_expensive +# (Testcase) +# +CREATE TABLE t1 (a INT); +INSERT INTO t1 VALUES (1),(2); +CREATE TABLE t2 (b INT); +INSERT INTO t2 VALUES (3),(4); +SELECT 1 IN (SELECT (SELECT a FROM t1) AS x FROM t2 GROUP BY x); +ERROR 21000: Subquery returns more than 1 row +drop table t1,t2; +# +# MDEV-25629: Crash in get_sort_by_table() in subquery with order by having outer ref +# +CREATE TABLE t1 (i1 int); +insert into t1 values (1),(2); +SELECT 1 +FROM (t1 JOIN t1 AS ref_t1 ON +(t1.i1 > (SELECT ref_t1.i1 AS c0 FROM t1 b ORDER BY -c0))); +ERROR 21000: Subquery returns more than 1 row +DROP TABLE t1; +# +# MDEV-22377: Subquery in an UPDATE query uses full scan instead of range +# +CREATE TABLE t1 ( +key1 varchar(30) NOT NULL, +col1 int(11) NOT NULL, +filler char(100) +); +insert into t1 select seq, seq, seq from seq_1_to_100; +CREATE TABLE t10 ( +key1 varchar(30) NOT NULL, +col1 int, +filler char(100), +PRIMARY KEY (key1) +); +insert into t10 select seq, seq, seq from seq_1_to_1000; +CREATE TABLE t11 ( +key1 varchar(30) NOT NULL, +filler char(100), +PRIMARY KEY (key1) +); +insert into t11 select seq, seq from seq_1_to_1000; +set @tmp_os=@@optimizer_switch; +set optimizer_switch='semijoin=off,materialization=off'; +# Must use range access (not full scan) for table tms: +explain select * from t1 hist +WHERE +key1 IN ('1','2','3','4','5','6','7','8','9','10') AND +hist.col1 NOT IN (SELECT tn.col1 +FROM t10 tn JOIN t11 tms ON tms.key1 = tn.key1 +WHERE tn.key1 IN ('1','2','3','4','5','6','7','8','9','10') +); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY hist ALL NULL NULL NULL NULL 100 Using where +2 DEPENDENT SUBQUERY tms range PRIMARY PRIMARY 32 NULL 10 Using where; Using index +2 DEPENDENT SUBQUERY tn eq_ref PRIMARY PRIMARY 32 test.tms.key1 1 Using where +set optimizer_switch=@tmp_os; +drop table t1, t10, t11; +# +# MDEV-28268: Server crashes in Expression_cache_tracker::fetch_current_stats +# +CREATE TABLE t1 (a INT, b INT); +INSERT INTO t1 VALUES (1,2),(3,4); +ANALYZE FORMAT=JSON +SELECT DISTINCT +(SELECT MIN(a) FROM t1 WHERE b <= ANY (SELECT a FROM t1)) AS f +FROM t1; +ANALYZE +{ + "query_optimization": { + "r_total_time_ms": "REPLACED" + }, + "query_block": { + "select_id": 1, + "r_loops": 1, + "r_total_time_ms": "REPLACED", + "duplicate_removal": { + "temporary_table": { + "nested_loop": [ + { + "table": { + "table_name": "t1", + "access_type": "ALL", + "r_loops": 1, + "rows": 2, + "r_rows": 2, + "r_table_time_ms": "REPLACED", + "r_other_time_ms": "REPLACED", + "r_engine_stats": REPLACED, + "filtered": 100, + "r_filtered": 100 + } + } + ], + "subqueries": [ + { + "expression_cache": { + "state": "disabled", + "r_loops": 0, + "query_block": { + "select_id": 2, + "r_loops": 1, + "r_total_time_ms": "REPLACED", + "nested_loop": [ + { + "table": { + "table_name": "t1", + "access_type": "ALL", + "r_loops": 1, + "rows": 2, + "r_rows": 2, + "r_table_time_ms": "REPLACED", + "r_other_time_ms": "REPLACED", + "r_engine_stats": REPLACED, + "filtered": 100, + "r_filtered": 50, + "attached_condition": "<nop>(<in_optimizer>(t1.b,(subquery#3) >= 4))" + } + } + ], + "subqueries": [ + { + "query_block": { + "select_id": 3, + "r_loops": 1, + "r_total_time_ms": "REPLACED", + "nested_loop": [ + { + "table": { + "table_name": "t1", + "access_type": "ALL", + "r_loops": 1, + "rows": 2, + "r_rows": 2, + "r_table_time_ms": "REPLACED", + "r_other_time_ms": "REPLACED", + "r_engine_stats": REPLACED, + "filtered": 100, + "r_filtered": 100 + } + } + ] + } + } + ] + } + } + } + ] + } + } + } +} +DROP TABLE t1; +# End of 10.2 tests +# +# MDEV-29139: Redundannt subquery in GROUP BY clause of ANY/ALL subquery +# +create table t1 (a int); +insert into t1 values (3), (1), (2); +create table t2 (b int not null); +insert into t2 values (4), (2); +create table t3 (c int); +insert into t3 values (7), (1); +explain extended select a from t1 +where a >= any (select b from t2 group by (select c from t3 where c = 1)); +id select_type table type possible_keys key key_len ref rows filtered Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 3 100.00 Using where +2 SUBQUERY t2 ALL NULL NULL NULL NULL 2 100.00 +Warnings: +Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a` from `test`.`t1` where <nop>(<in_optimizer>(`test`.`t1`.`a`,(/* select#2 */ select min(`test`.`t2`.`b`) from `test`.`t2`) <= <cache>(`test`.`t1`.`a`))) +select a from t1 +where a >= any (select b from t2 group by (select c from t3 where c = 1)); +a +3 +2 +prepare stmt from "select a from t1 +where a >= any (select b from t2 group by (select c from t3 where c = 1))"; +execute stmt; +a +3 +2 +execute stmt; +a +3 +2 +deallocate prepare stmt; +explain extended select a from t1 +where a <= all (select b from t2 group by (select c from t3 where c = 1)); +id select_type table type possible_keys key key_len ref rows filtered Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 3 100.00 Using where +2 SUBQUERY t2 ALL NULL NULL NULL NULL 2 100.00 +Warnings: +Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a` from `test`.`t1` where <not>(<in_optimizer>(`test`.`t1`.`a`,(/* select#2 */ select min(`test`.`t2`.`b`) from `test`.`t2`) < <cache>(`test`.`t1`.`a`))) +select a from t1 +where a <= all (select b from t2 group by (select c from t3 where c = 1)); +a +1 +2 +explain extended select a from t1 +where a >= any (select b from t2 group by 1 + (select c from t3 where c = 1)); +id select_type table type possible_keys key key_len ref rows filtered Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 3 100.00 Using where +2 SUBQUERY t2 ALL NULL NULL NULL NULL 2 100.00 +Warnings: +Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a` from `test`.`t1` where <nop>(<in_optimizer>(`test`.`t1`.`a`,(/* select#2 */ select min(`test`.`t2`.`b`) from `test`.`t2`) <= <cache>(`test`.`t1`.`a`))) +select a from t1 +where a >= any (select b from t2 group by 1 + (select c from t3 where c = 1)); +a +3 +2 +drop table t1,t2,t3; +# +# MDEV-29139: Redundant IN/ALL/ANY predicand in GROUP BY clause of +# IN/ALL/ANY/EXISTS subquery +# +create table t1 (a int); +create table t2 (b int); +create table t3 (c int); +create table t4 (d int); +insert into t1 values (3), (1); +insert into t2 values (3), (2); +insert into t3 values (4), (2); +insert into t4 values (1), (7); +explain extended select b from t2 +where exists (select c from t3 +group by (select a from t1 where a = 1) in (select d from t4)); +id select_type table type possible_keys key key_len ref rows filtered Extra +1 PRIMARY t2 ALL NULL NULL NULL NULL 2 100.00 +2 SUBQUERY t3 ALL NULL NULL NULL NULL 2 100.00 +Warnings: +Note 1003 /* select#1 */ select `test`.`t2`.`b` AS `b` from `test`.`t2` where 1 +select b from t2 +where exists (select c from t3 +group by (select a from t1 where a = 1) in (select d from t4)); +b +3 +2 +prepare stmt from "select b from t2 +where exists (select c from t3 +group by (select a from t1 where a = 1) in (select d from t4))"; +execute stmt; +b +3 +2 +execute stmt; +b +3 +2 +deallocate prepare stmt; +explain extended select b from t2 +where exists (select c from t3 +group by (select a from t1 where a = 1) >= +any (select d from t4)); +id select_type table type possible_keys key key_len ref rows filtered Extra +1 PRIMARY t2 ALL NULL NULL NULL NULL 2 100.00 +2 SUBQUERY t3 ALL NULL NULL NULL NULL 2 100.00 +Warnings: +Note 1003 /* select#1 */ select `test`.`t2`.`b` AS `b` from `test`.`t2` where 1 +select b from t2 +where exists (select c from t3 +group by (select a from t1 where a = 1) >= +any (select d from t4)); +b +3 +2 +explain extended select b from t2 +where exists (select c from t3 +group by (select a from t1 where a = 1) < +all (select d from t4)); +id select_type table type possible_keys key key_len ref rows filtered Extra +1 PRIMARY t2 ALL NULL NULL NULL NULL 2 100.00 +2 SUBQUERY t3 ALL NULL NULL NULL NULL 2 100.00 +Warnings: +Note 1003 /* select#1 */ select `test`.`t2`.`b` AS `b` from `test`.`t2` where 1 +select b from t2 +where exists (select c from t3 +group by (select a from t1 where a = 1) < +all (select d from t4)); +b +3 +2 +explain extended select b from t2 +where b in (select c from t3 +group by (select a from t1 where a = 1) in (select d from t4)); +id select_type table type possible_keys key key_len ref rows filtered Extra +1 PRIMARY t2 ALL NULL NULL NULL NULL 2 100.00 +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 100.00 +2 MATERIALIZED t3 ALL NULL NULL NULL NULL 2 100.00 +Warnings: +Note 1003 select `test`.`t2`.`b` AS `b` from `test`.`t2` semi join (`test`.`t3`) where 1 +select b from t2 +where b in (select c from t3 +group by (select a from t1 where a = 1) in (select d from t4)); +b +2 +explain extended select b from t2 +where b >= any (select c from t3 +group by (select a from t1 where a = 1) in +(select d from t4)); +id select_type table type possible_keys key key_len ref rows filtered Extra +1 PRIMARY t2 ALL NULL NULL NULL NULL 2 100.00 Using where +2 SUBQUERY t3 ALL NULL NULL NULL NULL 2 100.00 +Warnings: +Note 1003 /* select#1 */ select `test`.`t2`.`b` AS `b` from `test`.`t2` where <nop>(<in_optimizer>(`test`.`t2`.`b`,(/* select#2 */ select min(`test`.`t3`.`c`) from `test`.`t3`) <= <cache>(`test`.`t2`.`b`))) +select b from t2 +where b >= any (select c from t3 +group by (select a from t1 where a = 1) in +(select d from t4)); +b +3 +2 +explain extended select b from t2 +where b <= all (select c from t3 +group by (select a from t1 where a = 1) in +(select d from t4)); +id select_type table type possible_keys key key_len ref rows filtered Extra +1 PRIMARY t2 ALL NULL NULL NULL NULL 2 100.00 Using where +2 SUBQUERY t3 ALL NULL NULL NULL NULL 2 100.00 +Warnings: +Note 1003 /* select#1 */ select `test`.`t2`.`b` AS `b` from `test`.`t2` where <not>(<in_optimizer>(`test`.`t2`.`b`,<min>(/* select#2 */ select `test`.`t3`.`c` from `test`.`t3`) < <cache>(`test`.`t2`.`b`))) +select b from t2 +where b <= all (select c from t3 +group by (select a from t1 where a = 1) in +(select d from t4)); +b +2 +drop table t1,t2,t3,t4; +# End of 10.3 tests +# +# MDEV-19134: EXISTS() slower if ORDER BY is defined +# +create table t0 (a int); +insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); +create table t1(a int, b int); +insert into t1 select +A.a + B.a*10, A.a + B.a*10 from t0 A, t0 B; +create table t2 as select * from t1; +# This will be converted to semi-join: +explain +select * from t1 +where exists (select * from t2 where t2.a=t1.a order by t2.b); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 100 +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 +2 MATERIALIZED t2 ALL NULL NULL NULL NULL 100 +# query with a non-zero constant LIMIT is converted to semi-join, too: +explain +select * from t1 +where exists (select * from t2 where t2.a=t1.a order by t2.b limit 2); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 100 +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 +2 MATERIALIZED t2 ALL NULL NULL NULL NULL 100 +# Zero LIMIT should prevent the conversion (but it is not visible atm +# due to MDEV-19429) +explain +select * from t1 +where exists (select * from t2 where t2.a=t1.a order by t2.b limit 0); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 100 Using where +2 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL Zero limit +# LIMIT+OFFSET prevents the conversion: +explain +select * from t1 +where exists (select * from t2 where t2.a=t1.a order by t2.b limit 2,3); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 100 Using where +2 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 100 Using where; Using filesort +# This will be merged and converted into a semi-join: +explain +select * from t1 where t1.a in (select t2.a from t2 order by t2.b); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 100 +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 +2 MATERIALIZED t2 ALL NULL NULL NULL NULL 100 +drop table t0, t1, t2; +# +# MDEV-32320: Server crashes at TABLE::add_tmp_key +# +select +( ( 'x' , 1.000000 ) , 1 ) +IN +(SELECT +'x' , 'x' + WHERE ( 'x' ) +UNION +SELECT 1 , 'x' + HAVING 1 != 1 +) as T; +ERROR 21000: Operand should contain 2 column(s) +SELECT +EXISTS ( +WITH x ( x ) AS ( SELECT 1 ) +SELECT NULL +WHERE ( 1 , 1 ) = +(SELECT +1 , ( ( x , 1.000000 ) , 1 ) +IN +(SELECT 'x' , 'x' WHERE ( ( 'x' ) ) +UNION +SELECT 1 , x HAVING 1 != 1 +) +FROM x +) +); +ERROR 21000: Operand should contain 2 column(s) +# End of 10.4 tests |