subselect4.result

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	1	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	1	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	t2	ALL	NULL	NULL	NULL	NULL	2	Using where; FirstMatch(t1); Using join buffer (flat, BNL join)
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	t2	ALL	NULL	NULL	NULL	NULL	2	Using where; FirstMatch(t1); Using join buffer (flat, BNL join)
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	1	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'),('x');
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	3	Using where; FirstMatch(t1)
1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	3	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	3	Using where; FirstMatch(t1)
1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	3	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	t2	ALL	NULL	NULL	NULL	NULL	3	Using where; Start temporary; End temporary
1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	3	Using join buffer (flat, BNL join)
1	PRIMARY	t3	ALL	NULL	NULL	NULL	NULL	3	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	t4	index	NULL	PRIMARY	3	NULL	2	Using index
1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	4	func	1	
1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	3	Using where
2	MATERIALIZED	t2	ALL	NULL	NULL	NULL	NULL	3	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_t1	index	NULL	f4	5	NULL	2	Using index; Using temporary
3	SUBQUERY	SQ1_t3	range	f4	f4	5	NULL	2	Using where; 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>	const	NULL	distinct_key	4	const	1	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>	const	NULL	distinct_key	4	const	1	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>	const	NULL	distinct_key	5	const	1	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>	index_subquery	NULL	distinct_key	5	const	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 ( 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>	const	NULL	distinct_key	4	const	1	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>	const	NULL	distinct_key	4	const	1	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>	const	NULL	distinct_key	5	const	1	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>	const	NULL	distinct_key	5	const	1	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>	index_subquery	NULL	distinct_key	5	const	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 ( 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	
Warnings:
Note	1105	Cannot use key `col_varchar_key` part[0] for lookup: `test`.`t1`.`col_varchar_key` of type `varchar` < "0" of type `bigint`
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
Warnings:
Note	1105	Cannot use key `col_varchar_key` part[0] for lookup: `test`.`t1`.`col_varchar_key` of type `varchar` < "0" of type `bigint`
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;
explain 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 10;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	2	
1	PRIMARY	t1	index	NULL	v1	9	NULL	5	Using index
2	MATERIALIZED	t3	ALL	NULL	NULL	NULL	NULL	4	Using where
2	MATERIALIZED	t1	ref	v1	v1	4	test.t3.v2	1	Using index
2	MATERIALIZED	t4	ALL	NULL	NULL	NULL	NULL	50	
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 10;
1
Warnings:
Warning	1931	Query execution was interrupted. The query exceeded LIMIT ROWS EXAMINED 10. 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	Using where
1	PRIMARY	<derived3>	ref	key1,distinct_key	key1	4	test.t2.b	1	Using where; FirstMatch(t2)
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	tn	range	PRIMARY	PRIMARY	32	NULL	10	Using index condition; Using where
2	DEPENDENT SUBQUERY	tms	eq_ref	PRIMARY	PRIMARY	32	test.tn.key1	1	Using index
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,
    "cost": "REPLACED",
    "r_loops": 1,
    "r_total_time_ms": "REPLACED",
    "duplicate_removal": {
      "temporary_table": {
        "nested_loop": [
          {
            "table": {
              "table_name": "t1",
              "access_type": "ALL",
              "loops": 1,
              "r_loops": 1,
              "rows": 2,
              "r_rows": 2,
              "cost": "REPLACED",
              "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,
                "cost": "REPLACED",
                "r_loops": 1,
                "r_total_time_ms": "REPLACED",
                "nested_loop": [
                  {
                    "table": {
                      "table_name": "t1",
                      "access_type": "ALL",
                      "loops": 1,
                      "r_loops": 1,
                      "rows": 2,
                      "r_rows": 2,
                      "cost": "REPLACED",
                      "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,
                      "cost": "REPLACED",
                      "r_loops": 1,
                      "r_total_time_ms": "REPLACED",
                      "nested_loop": [
                        {
                          "table": {
                            "table_name": "t1",
                            "access_type": "ALL",
                            "loops": 1,
                            "r_loops": 1,
                            "rows": 2,
                            "r_rows": 2,
                            "cost": "REPLACED",
                            "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	t3	ALL	NULL	NULL	NULL	NULL	2	50.00	Using where; FirstMatch(t2); Using join buffer (flat, BNL join)
Warnings:
Note	1003	select `test`.`t2`.`b` AS `b` from `test`.`t2` semi join (`test`.`t3`) where `test`.`t3`.`c` = `test`.`t2`.`b`
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)
#
# MDEV-29362: Constant subquery used as left part of IN subquery
#
CREATE TABLE t1 (a int) ENGINE=MyISAM;
INSERT INTO t1 VALUES (15), (1), (2);
CREATE TABLE t2 (b int) ENGINE=MyISAM;
INSERT INTO t2 VALUES (15), (1);
CREATE TABLE t3 (c int) ENGINE=MyISAM;
INSERT INTO t3 VALUES (15), (1);
SET optimizer_switch='condition_pushdown_from_having=off';
SELECT a FROM t1 GROUP BY a
HAVING a = ( (SELECT b FROM t2 where b=1) IN (SELECT c FROM t3)  ) + 1;
a
2
SELECT a FROM t1 GROUP BY a
HAVING a IN ( (SELECT b FROM t2 where b=1) IN (SELECT c FROM t3)  );
a
1
SET optimizer_switch='condition_pushdown_from_having=on';
SELECT a FROM t1 GROUP BY a
HAVING a = ( (SELECT b FROM t2 where b=1) IN (SELECT c FROM t3)  ) + 1;
a
2
SELECT a FROM t1 GROUP BY a
HAVING a IN ( (SELECT b FROM t2 where b=1) IN (SELECT c FROM t3)  );
a
1
EXPLAIN FORMAT=JSON SELECT a FROM t1 GROUP BY a
HAVING a = ( (SELECT b FROM t2 where b=1) IN (SELECT c FROM t3)  ) + 1;
EXPLAIN
{
  "query_block": {
    "select_id": 1,
    "cost": 0.012403489,
    "nested_loop": [
      {
        "table": {
          "table_name": "t1",
          "access_type": "ALL",
          "loops": 1,
          "rows": 3,
          "cost": 0.010504815,
          "filtered": 100,
          "attached_condition": "t1.a = <cache>((<in_optimizer>((subquery#2),<exists>(subquery#3))) + 1)"
        }
      }
    ],
    "subqueries": [
      {
        "query_block": {
          "select_id": 3,
          "cost": 0.01034841,
          "having_condition": "trigcond(t3.c is null)",
          "nested_loop": [
            {
              "table": {
                "table_name": "t3",
                "access_type": "ALL",
                "loops": 1,
                "rows": 2,
                "cost": 0.01034841,
                "filtered": 100,
                "attached_condition": "trigcond(1 = t3.c or t3.c is null)"
              }
            }
          ]
        }
      },
      {
        "query_block": {
          "select_id": 2,
          "cost": 0.01034841,
          "nested_loop": [
            {
              "table": {
                "table_name": "t2",
                "access_type": "ALL",
                "loops": 1,
                "rows": 2,
                "cost": 0.01034841,
                "filtered": 100,
                "attached_condition": "t2.b = 1"
              }
            }
          ]
        }
      }
    ]
  }
}
PREPARE stmt FROM "SELECT a FROM t1 GROUP BY a
HAVING a = ( (SELECT b FROM t2 where b=1) IN (SELECT c FROM t3)  ) + 1";
EXECUTE stmt;
a
2
EXECUTE stmt;
a
2
DEALLOCATE PREPARE stmt;
DROP TABLE t1,t2,t3;
# End of 10.4 tests