# Bug#26436185 Assertion 'buf_is_inside_another(data_in_mysql_buf, ...)
CREATE TABLE t1 (
pk int NOT NULL,
col_int_key int DEFAULT NULL,
col_int int DEFAULT NULL,
col_varchar varchar(1) DEFAULT NULL,
PRIMARY KEY (pk),
KEY col_int_key (col_int_key)
);
INSERT INTO t1 VALUES (10,7,5,'l'), (12,7,4,'o');
CREATE TABLE t2 (
col_date_key date DEFAULT NULL,
col_datetime_key datetime DEFAULT NULL,
col_int_key int(11) DEFAULT NULL,
col_varchar_key varchar(1) DEFAULT NULL,
col_varchar varchar(1) DEFAULT NULL,
col_time time DEFAULT NULL,
pk int NOT NULL,
col_date date DEFAULT NULL,
col_time_key time DEFAULT NULL,
col_datetime datetime DEFAULT NULL,
col_int int DEFAULT NULL,
PRIMARY KEY (pk),
KEY col_date_key (col_date_key),
KEY col_datetime_key (col_datetime_key),
KEY col_int_key (col_int_key),
KEY col_varchar_key (col_varchar_key),
KEY col_time_key (col_time_key)
);
Warnings:
Warning	1681	Integer display width is deprecated and will be removed in a future release.
INSERT INTO t2(col_int_key,col_varchar_key,col_varchar,pk,col_int)  VALUES
(8,'a','w',1,5),
(9,'y','f',7,0),
(9,'z','i',11,9),
(9,'r','s',12,3),
(7,'n','i',13,6),
(9,'j','v',17,9),
(240,'u','k',20,6);
CREATE TABLE t3 (
col_int int DEFAULT NULL,
col_int_key int(11) DEFAULT NULL,
pk int NOT NULL,
PRIMARY KEY (pk),
KEY col_int_key (col_int_key)
);
Warnings:
Warning	1681	Integer display width is deprecated and will be removed in a future release.
INSERT INTO t3 VALUES (8,4,1);
ANALYZE TABLE t1, t2, t3;
Table	Op	Msg_type	Msg_text
test.t1	analyze	status	OK
test.t2	analyze	status	OK
test.t3	analyze	status	OK
EXPLAIN SELECT table2.col_int_key AS field1
FROM (SELECT sq1_t1.*
FROM t1 AS sq1_t1 RIGHT OUTER JOIN t2 AS sq1_t2
ON sq1_t2.col_varchar_key = sq1_t1.col_varchar
) AS table1
LEFT JOIN t1 AS table2
RIGHT JOIN t2 AS table3
ON table3.pk = table2.col_int_key
ON table3.col_int_key = table2.col_int
WHERE table3.col_int_key >= ALL
(SELECT sq2_t1.col_int AS sq2_field1
FROM t2 AS sq2_t1 STRAIGHT_JOIN t3 AS sq2_t2
ON sq2_t2.col_int = sq2_t1.pk AND
sq2_t1.col_varchar IN
(SELECT sq21_t1.col_varchar AS sq21_field1
FROM t2 AS sq21_t1 STRAIGHT_JOIN t1 AS sq21_t2
ON sq21_t2.col_int_key = sq21_t1.pk
WHERE sq21_t1.pk = 7
)
WHERE sq2_t2.col_int_key >= table2.col_int AND
sq2_t1.col_int_key <= table2.col_int_key
);
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	PRIMARY	sq1_t2	NULL	index	NULL	col_varchar_key	7	NULL	7	100.00	Using index
1	PRIMARY	sq1_t1	NULL	ALL	NULL	NULL	NULL	NULL	2	100.00	Using where; Using join buffer (Block Nested Loop)
1	PRIMARY	table2	NULL	ALL	col_int_key	NULL	NULL	NULL	2	100.00	NULL
1	PRIMARY	table3	NULL	eq_ref	PRIMARY,col_int_key	PRIMARY	4	test.table2.col_int_key	1	100.00	Using where
3	DEPENDENT SUBQUERY	<subquery4>	NULL	ALL	NULL	NULL	NULL	NULL	NULL	100.00	NULL
3	DEPENDENT SUBQUERY	sq2_t1	NULL	ALL	PRIMARY,col_int_key	NULL	NULL	NULL	7	14.29	Range checked for each record (index map: 0x9)
3	DEPENDENT SUBQUERY	sq2_t2	NULL	ALL	col_int_key	NULL	NULL	NULL	1	100.00	Range checked for each record (index map: 0x2)
4	MATERIALIZED	sq21_t1	NULL	const	PRIMARY	PRIMARY	4	const	1	100.00	NULL
4	MATERIALIZED	sq21_t2	NULL	ref	col_int_key	col_int_key	5	const	1	100.00	Using index
Warnings:
Note	1276	Field or reference 'test.table2.col_int' of SELECT #3 was resolved in SELECT #1
Note	1276	Field or reference 'test.table2.col_int_key' of SELECT #3 was resolved in SELECT #1
Note	1003	/* select#1 */ select `test`.`table2`.`col_int_key` AS `field1` from `test`.`t2` `sq1_t2` left join `test`.`t1` `sq1_t1` on((`test`.`sq1_t1`.`col_varchar` = `test`.`sq1_t2`.`col_varchar_key`)) left join (`test`.`t2` `table3` join `test`.`t1` `table2`) on(((`test`.`table3`.`pk` = `test`.`table2`.`col_int_key`) and (`test`.`table3`.`col_int_key` = `test`.`table2`.`col_int`))) where <not>(<in_optimizer>(`test`.`table3`.`col_int_key`,<exists>(/* select#3 */ select 1 from `test`.`t2` `sq2_t1` straight_join `test`.`t3` `sq2_t2` semi join (`test`.`t2` `sq21_t1` straight_join `test`.`t1` `sq21_t2`) where ((`test`.`sq2_t2`.`col_int` = `test`.`sq2_t1`.`pk`) and (`test`.`sq2_t1`.`col_varchar` = `<subquery4>`.`sq21_field1`) and (`test`.`sq21_t1`.`pk` = 7) and (`test`.`sq21_t2`.`col_int_key` = 7) and (`test`.`sq2_t2`.`col_int_key` >= `test`.`table2`.`col_int`) and (`test`.`sq2_t1`.`col_int_key` <= `test`.`table2`.`col_int_key`) and <if>(outer_field_is_not_null, ((<cache>(`test`.`table3`.`col_int_key`) < `test`.`sq2_t1`.`col_int`) or (`test`.`sq2_t1`.`col_int` is null)), true)) having <if>(outer_field_is_not_null, <is_not_null_test>(`test`.`sq2_t1`.`col_int`), true))))
SELECT table2.col_int_key AS field1
FROM (SELECT sq1_t1.*
FROM t1 AS sq1_t1 RIGHT OUTER JOIN t2 AS sq1_t2
ON sq1_t2.col_varchar_key = sq1_t1.col_varchar
) AS table1
LEFT JOIN t1 AS table2
RIGHT JOIN t2 AS table3
ON table3.pk = table2.col_int_key
ON table3.col_int_key = table2.col_int
WHERE table3.col_int_key >= ALL
(SELECT sq2_t1.col_int AS sq2_field1
FROM t2 AS sq2_t1 STRAIGHT_JOIN t3 AS sq2_t2
ON sq2_t2.col_int = sq2_t1.pk AND
sq2_t1.col_varchar IN
(SELECT sq21_t1.col_varchar AS sq21_field1
FROM t2 AS sq21_t1 STRAIGHT_JOIN t1 AS sq21_t2
ON sq21_t2.col_int_key = sq21_t1.pk
WHERE sq21_t1.pk = 7
)
WHERE sq2_t2.col_int_key >= table2.col_int AND
sq2_t1.col_int_key <= table2.col_int_key
);
field1
NULL
NULL
NULL
NULL
NULL
NULL
NULL
DROP TABLE t1, t2, t3;
#
# Bug#24713879 ASSERTION `MAYBE_NULL' FAILED. HANDLE_FATAL_SIGNAL IN TEM_FUNC_CONCAT::VAL_STR
#
CREATE TABLE t1(k VARCHAR(10) PRIMARY KEY);
CREATE TABLE t2(k VARCHAR(10) PRIMARY KEY);
SET SQL_MODE='';
EXPLAIN SELECT (SELECT 'X' FROM t2
WHERE t2.k = CONCAT(t1.k, 'X')) = 'XXX'
FROM t1
WHERE k ='X';
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	no matching row in const table
2	SUBQUERY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Not optimized, outer query is empty
Warnings:
Note	1276	Field or reference 'test.t1.k' of SELECT #2 was resolved in SELECT #1
Note	1003	/* select#1 */ select ((/* select#2 */ select 'X' from `test`.`t2` where (`test`.`t2`.`k` = concat(NULL,'X'))) = 'XXX') AS `(SELECT 'X' FROM t2
WHERE t2.k = CONCAT(t1.k, 'X')) = 'XXX'` from `test`.`t1` where (NULL = 'X')
SELECT (SELECT 'X' FROM t2
WHERE t2.k = CONCAT(t1.k, 'X')) = 'XXX'
FROM t1
WHERE k ='X';
(SELECT 'X' FROM t2
WHERE t2.k = CONCAT(t1.k, 'X')) = 'XXX'
EXPLAIN SELECT (SELECT 'X' FROM t2
WHERE t2.k = CONCAT(t1.k, 'X')) = 'XXX',
SUM(k)
FROM t1;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	PRIMARY	t1	NULL	index	NULL	PRIMARY	42	NULL	1	100.00	Using index
2	DEPENDENT SUBQUERY	t2	NULL	eq_ref	PRIMARY	PRIMARY	42	func	1	100.00	Using where; Using index
Warnings:
Note	1276	Field or reference 'test.t1.k' of SELECT #2 was resolved in SELECT #1
Note	1003	/* select#1 */ select ((/* select#2 */ select 'X' from `test`.`t2` where (`test`.`t2`.`k` = concat(`test`.`t1`.`k`,'X'))) = 'XXX') AS `(SELECT 'X' FROM t2
WHERE t2.k = CONCAT(t1.k, 'X')) = 'XXX'`,sum(`test`.`t1`.`k`) AS `SUM(k)` from `test`.`t1`
SELECT (SELECT 'X' FROM t2
WHERE t2.k = CONCAT(t1.k, 'X')) = 'XXX',
SUM(k)
FROM t1;
(SELECT 'X' FROM t2
WHERE t2.k = CONCAT(t1.k, 'X')) = 'XXX'	SUM(k)
NULL	NULL
EXPLAIN SELECT SUM(k), k
FROM t1
HAVING (SELECT 'X' FROM t2
WHERE t2.k = CONCAT(t1.k, 'X')) = 'XXX';
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	PRIMARY	t1	NULL	index	NULL	PRIMARY	42	NULL	1	100.00	Using index
2	DEPENDENT SUBQUERY	t2	NULL	eq_ref	PRIMARY	PRIMARY	42	func	1	100.00	Using where; Using index
Warnings:
Note	1276	Field or reference 'test.t1.k' of SELECT #2 was resolved in SELECT #1
Note	1276	Field or reference 'test.t1.k' of SELECT #2 was resolved in SELECT #1
Note	1003	/* select#1 */ select sum(`test`.`t1`.`k`) AS `SUM(k)`,`test`.`t1`.`k` AS `k` from `test`.`t1` having ((/* select#2 */ select 'X' from `test`.`t2` where (`test`.`t2`.`k` = concat(`test`.`t1`.`k`,'X'))) = 'XXX')
SELECT SUM(k), k
FROM t1
HAVING (SELECT 'X' FROM t2
WHERE t2.k = CONCAT(t1.k, 'X')) = 'XXX';
SUM(k)	k
EXPLAIN SELECT (SELECT 'X' FROM t2
WHERE t2.k = CONCAT(t1.k, 'X')
AND SUM(t1.k)) = 'XXX'
FROM t1;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	PRIMARY	t1	NULL	index	NULL	PRIMARY	42	NULL	1	100.00	Using index
2	DEPENDENT SUBQUERY	t2	NULL	eq_ref	PRIMARY	PRIMARY	42	func	1	100.00	Using where; Using index
Warnings:
Note	1276	Field or reference 'test.t1.k' of SELECT #2 was resolved in SELECT #1
Note	1276	Field or reference 'test.t1.k' of SELECT #2 was resolved in SELECT #1
Note	1003	/* select#1 */ select ((/* select#2 */ select 'X' from `test`.`t2` where ((`test`.`t2`.`k` = concat(`test`.`t1`.`k`,'X')) and (0 <> sum(`test`.`t1`.`k`)))) = 'XXX') AS `(SELECT 'X' FROM t2
WHERE t2.k = CONCAT(t1.k, 'X')
AND SUM(t1.k)) = 'XXX'` from `test`.`t1`
SELECT (SELECT 'X' FROM t2
WHERE t2.k = CONCAT(t1.k, 'X')
AND SUM(t1.k)) = 'XXX'
FROM t1;
(SELECT 'X' FROM t2
WHERE t2.k = CONCAT(t1.k, 'X')
AND SUM(t1.k)) = 'XXX'
NULL
SET SQL_MODE=ONLY_FULL_GROUP_BY;
EXPLAIN SELECT (SELECT 'X' FROM t2
WHERE t2.k = CONCAT(t1.k, 'X')) = 'XXX'
FROM t1
WHERE k ='X';
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	no matching row in const table
2	SUBQUERY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Not optimized, outer query is empty
Warnings:
Note	1276	Field or reference 'test.t1.k' of SELECT #2 was resolved in SELECT #1
Note	1003	/* select#1 */ select ((/* select#2 */ select 'X' from `test`.`t2` where (`test`.`t2`.`k` = concat(NULL,'X'))) = 'XXX') AS `(SELECT 'X' FROM t2
WHERE t2.k = CONCAT(t1.k, 'X')) = 'XXX'` from `test`.`t1` where (NULL = 'X')
SELECT (SELECT 'X' FROM t2
WHERE t2.k = CONCAT(t1.k, 'X')) = 'XXX'
FROM t1
WHERE k ='X';
(SELECT 'X' FROM t2
WHERE t2.k = CONCAT(t1.k, 'X')) = 'XXX'
EXPLAIN SELECT (SELECT 'X' FROM t2
WHERE t2.k = CONCAT(t1.k, 'X')) = 'XXX',
SUM(k)
FROM t1;
ERROR 42000: In aggregated query without GROUP BY, expression #1 of SELECT list contains nonaggregated column 'test.t1.k'; this is incompatible with sql_mode=only_full_group_by
EXPLAIN SELECT SUM(k), k
FROM t1
HAVING (SELECT 'X' FROM t2
WHERE t2.k = CONCAT(t1.k, 'X')) = 'XXX';
ERROR 42000: In aggregated query without GROUP BY, expression #2 of SELECT list contains nonaggregated column 'test.t1.k'; this is incompatible with sql_mode=only_full_group_by
EXPLAIN SELECT (SELECT 'X' FROM t2
WHERE t2.k = CONCAT(t1.k, 'X')
AND SUM(t1.k)) = 'XXX'
FROM t1;
ERROR 42000: In aggregated query without GROUP BY, expression #1 of SELECT list contains nonaggregated column 'test.t1.k'; this is incompatible with sql_mode=only_full_group_by
SET SQL_MODE=STRICT_TRANS_TABLES;
Warnings:
Warning	3135	'NO_ZERO_DATE', 'NO_ZERO_IN_DATE' and 'ERROR_FOR_DIVISION_BY_ZERO' sql modes should be used with strict mode. They will be merged with strict mode in a future release.
EXPLAIN SELECT (SELECT 'X' FROM t2
WHERE t2.k = CONCAT(t1.k, 'X')) = 'XXX'
FROM t1
WHERE k ='X';
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	no matching row in const table
2	SUBQUERY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Not optimized, outer query is empty
Warnings:
Note	1276	Field or reference 'test.t1.k' of SELECT #2 was resolved in SELECT #1
Note	1003	/* select#1 */ select ((/* select#2 */ select 'X' from `test`.`t2` where (`test`.`t2`.`k` = concat(NULL,'X'))) = 'XXX') AS `(SELECT 'X' FROM t2
WHERE t2.k = CONCAT(t1.k, 'X')) = 'XXX'` from `test`.`t1` where (NULL = 'X')
SELECT (SELECT 'X' FROM t2
WHERE t2.k = CONCAT(t1.k, 'X')) = 'XXX'
FROM t1
WHERE k ='X';
(SELECT 'X' FROM t2
WHERE t2.k = CONCAT(t1.k, 'X')) = 'XXX'
EXPLAIN SELECT (SELECT 'X' FROM t2
WHERE t2.k = CONCAT(t1.k, 'X')) = 'XXX',
SUM(k)
FROM t1;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	PRIMARY	t1	NULL	index	NULL	PRIMARY	42	NULL	1	100.00	Using index
2	DEPENDENT SUBQUERY	t2	NULL	eq_ref	PRIMARY	PRIMARY	42	func	1	100.00	Using where; Using index
Warnings:
Note	1276	Field or reference 'test.t1.k' of SELECT #2 was resolved in SELECT #1
Note	1003	/* select#1 */ select ((/* select#2 */ select 'X' from `test`.`t2` where (`test`.`t2`.`k` = concat(`test`.`t1`.`k`,'X'))) = 'XXX') AS `(SELECT 'X' FROM t2
WHERE t2.k = CONCAT(t1.k, 'X')) = 'XXX'`,sum(`test`.`t1`.`k`) AS `SUM(k)` from `test`.`t1`
SELECT (SELECT 'X' FROM t2
WHERE t2.k = CONCAT(t1.k, 'X')) = 'XXX',
SUM(k)
FROM t1;
(SELECT 'X' FROM t2
WHERE t2.k = CONCAT(t1.k, 'X')) = 'XXX'	SUM(k)
NULL	NULL
EXPLAIN SELECT SUM(k), k
FROM t1
HAVING (SELECT 'X' FROM t2
WHERE t2.k = CONCAT(t1.k, 'X')) = 'XXX';
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	PRIMARY	t1	NULL	index	NULL	PRIMARY	42	NULL	1	100.00	Using index
2	DEPENDENT SUBQUERY	t2	NULL	eq_ref	PRIMARY	PRIMARY	42	func	1	100.00	Using where; Using index
Warnings:
Note	1276	Field or reference 'test.t1.k' of SELECT #2 was resolved in SELECT #1
Note	1276	Field or reference 'test.t1.k' of SELECT #2 was resolved in SELECT #1
Note	1003	/* select#1 */ select sum(`test`.`t1`.`k`) AS `SUM(k)`,`test`.`t1`.`k` AS `k` from `test`.`t1` having ((/* select#2 */ select 'X' from `test`.`t2` where (`test`.`t2`.`k` = concat(`test`.`t1`.`k`,'X'))) = 'XXX')
SELECT SUM(k), k
FROM t1
HAVING (SELECT 'X' FROM t2
WHERE t2.k = CONCAT(t1.k, 'X')) = 'XXX';
SUM(k)	k
EXPLAIN SELECT (SELECT 'X' FROM t2
WHERE t2.k = CONCAT(t1.k, 'X')
AND SUM(t1.k)) = 'XXX'
FROM t1;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	PRIMARY	t1	NULL	index	NULL	PRIMARY	42	NULL	1	100.00	Using index
2	DEPENDENT SUBQUERY	t2	NULL	eq_ref	PRIMARY	PRIMARY	42	func	1	100.00	Using where; Using index
Warnings:
Note	1276	Field or reference 'test.t1.k' of SELECT #2 was resolved in SELECT #1
Note	1276	Field or reference 'test.t1.k' of SELECT #2 was resolved in SELECT #1
Note	1003	/* select#1 */ select ((/* select#2 */ select 'X' from `test`.`t2` where ((`test`.`t2`.`k` = concat(`test`.`t1`.`k`,'X')) and (0 <> sum(`test`.`t1`.`k`)))) = 'XXX') AS `(SELECT 'X' FROM t2
WHERE t2.k = CONCAT(t1.k, 'X')
AND SUM(t1.k)) = 'XXX'` from `test`.`t1`
SELECT (SELECT 'X' FROM t2
WHERE t2.k = CONCAT(t1.k, 'X')
AND SUM(t1.k)) = 'XXX'
FROM t1;
(SELECT 'X' FROM t2
WHERE t2.k = CONCAT(t1.k, 'X')
AND SUM(t1.k)) = 'XXX'
NULL
SET SQL_MODE=DEFAULT;
EXPLAIN SELECT (SELECT 'X' FROM t2
WHERE t2.k = CONCAT(t1.k, 'X')) = 'XXX'
FROM t1
WHERE k ='X';
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	no matching row in const table
2	SUBQUERY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Not optimized, outer query is empty
Warnings:
Note	1276	Field or reference 'test.t1.k' of SELECT #2 was resolved in SELECT #1
Note	1003	/* select#1 */ select ((/* select#2 */ select 'X' from `test`.`t2` where (`test`.`t2`.`k` = concat(NULL,'X'))) = 'XXX') AS `(SELECT 'X' FROM t2
WHERE t2.k = CONCAT(t1.k, 'X')) = 'XXX'` from `test`.`t1` where (NULL = 'X')
SELECT (SELECT 'X' FROM t2
WHERE t2.k = CONCAT(t1.k, 'X')) = 'XXX'
FROM t1
WHERE k ='X';
(SELECT 'X' FROM t2
WHERE t2.k = CONCAT(t1.k, 'X')) = 'XXX'
EXPLAIN SELECT (SELECT 'X' FROM t2
WHERE t2.k = CONCAT(t1.k, 'X')) = 'XXX',
SUM(k)
FROM t1;
ERROR 42000: In aggregated query without GROUP BY, expression #1 of SELECT list contains nonaggregated column 'test.t1.k'; this is incompatible with sql_mode=only_full_group_by
EXPLAIN SELECT SUM(k), k
FROM t1
HAVING (SELECT 'X' FROM t2
WHERE t2.k = CONCAT(t1.k, 'X')) = 'XXX';
ERROR 42000: In aggregated query without GROUP BY, expression #2 of SELECT list contains nonaggregated column 'test.t1.k'; this is incompatible with sql_mode=only_full_group_by
EXPLAIN SELECT (SELECT 'X' FROM t2
WHERE t2.k = CONCAT(t1.k, 'X')
AND SUM(t1.k)) = 'XXX'
FROM t1;
ERROR 42000: In aggregated query without GROUP BY, expression #1 of SELECT list contains nonaggregated column 'test.t1.k'; this is incompatible with sql_mode=only_full_group_by
DROP TABLE t1,t2;
#
# Bug#27665085 ASSERTION FAILED: JOIN == 0.
#
SET sql_mode='';
CREATE TABLE a(d INT,e BIGINT, KEY(e));
INSERT a VALUES (0,0);
CREATE TABLE b(f TIME);
INSERT b VALUES (null),(null),(null);
CREATE TABLE c(g DATETIME(6) NOT NULL);
INSERT c(g) VALUES (now()+interval 1 day);
INSERT c(g) VALUES (now()-interval 1 day);
SELECT 1 FROM a WHERE (SELECT f FROM b WHERE (SELECT 1 FROM c)) <=> e GROUP BY d;
ERROR 21000: Subquery returns more than 1 row
SET sql_mode=default;
DROP TABLES a, b, c;
#
# Bug#27182010 SUBQUERY INCORRECTLY SHOWS DUPLICATE VALUES ON SUBQUERIES
#
CREATE TABLE p (Id INT,PRIMARY KEY (Id));
INSERT INTO p VALUES (1);
# Test UNIQUE KEY with NULL values
CREATE TABLE s (Id INT, u INT, UNIQUE KEY o(Id, u) );
INSERT INTO s VALUES (1, NULL),(1, NULL);
ANALYZE TABLE p, s;
Table	Op	Msg_type	Msg_text
test.p	analyze	status	OK
test.s	analyze	status	OK
EXPLAIN SELECT p.Id FROM (p) WHERE p.Id IN (
SELECT s.Id FROM s WHERE Id=1 AND u IS NULL)ORDER BY Id DESC;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	p	NULL	const	PRIMARY	PRIMARY	4	const	1	100.00	Using index
1	SIMPLE	s	NULL	ref	o	o	10	const,const	1	100.00	Using where; Using index; FirstMatch(p)
Warnings:
Note	1003	/* select#1 */ select '1' AS `Id` from `test`.`p` semi join (`test`.`s`) where ((`test`.`s`.`Id` = 1) and (`test`.`s`.`u` is null)) order by '1' desc
EXPLAIN SELECT p.Id FROM (p) WHERE p.Id IN (
SELECT s.Id FROM s WHERE Id=1 AND u IS NOT NULL) ORDER BY Id DESC;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	p	NULL	const	PRIMARY	PRIMARY	4	const	1	100.00	Using index
1	SIMPLE	s	NULL	range	o	o	10	NULL	1	100.00	Using where; Using index; FirstMatch(p)
Warnings:
Note	1003	/* select#1 */ select '1' AS `Id` from `test`.`p` semi join (`test`.`s`) where ((`test`.`s`.`Id` = 1) and (`test`.`s`.`u` is not null)) order by '1' desc
SELECT p.Id FROM (p) WHERE p.Id IN (
SELECT s.Id FROM s WHERE Id=1 AND u IS NULL)ORDER BY Id DESC;
Id
1
SELECT p.Id FROM (p) WHERE p.Id IN (
SELECT s.Id FROM s WHERE Id=1 AND u IS NOT NULL) ORDER BY Id DESC;
Id
# UNIQUE KEY without NULL values
CREATE TABLE s1 (Id INT, u INT, UNIQUE KEY o(Id, u) );
INSERT INTO s1 VALUES (1, 2),(1, 3);
ANALYZE TABLE s1;
Table	Op	Msg_type	Msg_text
test.s1	analyze	status	OK
EXPLAIN SELECT p.Id FROM (p) WHERE p.Id IN (
SELECT s1.Id FROM s1 WHERE Id=1 AND u IS NOT NULL) ORDER BY Id DESC;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	p	NULL	const	PRIMARY	PRIMARY	4	const	1	100.00	Using index
1	SIMPLE	s1	NULL	range	o	o	10	NULL	1	100.00	Using where; Using index; FirstMatch(p)
Warnings:
Note	1003	/* select#1 */ select '1' AS `Id` from `test`.`p` semi join (`test`.`s1`) where ((`test`.`s1`.`Id` = 1) and (`test`.`s1`.`u` is not null)) order by '1' desc
EXPLAIN SELECT p.Id FROM (p) WHERE p.Id IN (
SELECT s1.Id FROM s1 WHERE Id=1 AND u != 1) ORDER BY Id DESC;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	p	NULL	const	PRIMARY	PRIMARY	4	const	1	100.00	Using index
1	SIMPLE	s1	NULL	index	o	o	10	NULL	2	100.00	Using where; Using index; FirstMatch(p)
Warnings:
Note	1003	/* select#1 */ select '1' AS `Id` from `test`.`p` semi join (`test`.`s1`) where ((`test`.`s1`.`Id` = 1) and (`test`.`s1`.`u` <> 1)) order by '1' desc
SELECT p.Id FROM (p) WHERE p.Id IN (
SELECT s1.Id FROM s1 WHERE Id=1 AND u IS NOT NULL) ORDER BY Id DESC;
Id
1
SELECT p.Id FROM (p) WHERE p.Id IN (
SELECT s1.Id FROM s1 WHERE Id=1 AND u != 1) ORDER BY Id DESC;
Id
1
# NON UNIQUE KEY Scenario
CREATE TABLE s2 (Id INT, u INT, KEY o(Id, u) );
INSERT INTO s2 VALUES (1, NULL),(1, NULL);
#UNIQUE KEY with NON NULL FIELDS
CREATE TABLE s3 (Id INT NOT NULL, u INT NOT NULL, UNIQUE KEY o(Id, u));
INSERT INTO s3 VALUES (1, 2),(1, 3);
EXPLAIN SELECT p.Id FROM (p) WHERE p.Id IN (
SELECT s.Id FROM s2 s WHERE Id=1 AND u IS NULL) ORDER BY Id DESC;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	p	NULL	const	PRIMARY	PRIMARY	4	const	1	100.00	Using index
1	SIMPLE	s	NULL	ref	o	o	10	const,const	1	100.00	Using where; Using index; FirstMatch(p)
Warnings:
Note	1003	/* select#1 */ select '1' AS `Id` from `test`.`p` semi join (`test`.`s2` `s`) where ((`test`.`s`.`Id` = 1) and (`test`.`s`.`u` is null)) order by '1' desc
EXPLAIN SELECT p.Id FROM (p) WHERE p.Id IN (
SELECT s.Id FROM s3 s WHERE Id=1 AND u IS NOT NULL)
ORDER BY Id DESC;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	p	NULL	const	PRIMARY	PRIMARY	4	const	1	100.00	Using index
1	SIMPLE	s	NULL	ref	o	o	4	const	1	100.00	Using index; FirstMatch(p)
Warnings:
Note	1003	/* select#1 */ select '1' AS `Id` from `test`.`p` semi join (`test`.`s3` `s`) where (`test`.`s`.`Id` = 1) order by '1' desc
SELECT p.Id FROM (p) WHERE p.Id IN (
SELECT s.Id FROM s2 s WHERE Id=1 AND u IS NULL) ORDER BY Id DESC;
Id
1
SELECT p.Id FROM (p) WHERE p.Id IN (
SELECT s.Id FROM s3 s WHERE Id=1 AND u IS NOT NULL)
ORDER BY Id DESC;
Id
1
DROP TABLE p, s, s1, s2, s3;
#
# Bug#28239008: WL#9571: SIG11 IN ITEM_FIELD::RESULT_TYPE() AT SQL/ITEM.H
#
CREATE TABLE t1 (f1 varchar(1) DEFAULT NULL);
INSERT INTO t1 VALUES ('5');
CREATE TABLE t2 (f1 varchar(1) DEFAULT NULL);
INSERT INTO t2 VALUES ('Y');
PREPARE prep_stmt FROM "SELECT t2.f1 FROM (t2 LEFT JOIN t1
 ON (1 = ANY (SELECT f1 FROM t1 WHERE 1 IS NULL)))" ;
EXECUTE prep_stmt ;
f1
Y
DROP TABLE t1,t2;
CREATE TABLE t1 (f1 varchar(1) DEFAULT NULL);
INSERT INTO t1 VALUES ('Z') ;
CREATE TABLE t2 (f1 varchar(1) DEFAULT NULL);
INSERT INTO t2 VALUES ('Z') ;
PREPARE prep_stmt FROM "
SELECT t2.f1 FROM t2 LEFT OUTER JOIN
(SELECT  * FROM t2 WHERE ('y',1)
 IN (SELECT alias1.f1 , 0 FROM t1 AS alias1 LEFT JOIN t2 ON 0)) AS alias ON 0";
EXECUTE prep_stmt ;
f1
Z
PREPARE prep_stmt FROM "
SELECT t2.f1 FROM (t2 LEFT OUTER JOIN (SELECT  * FROM t2 WHERE ('y',1)
 IN (SELECT alias1.f1 , 0 FROM
     (t1 INNER JOIN  (t1 AS alias1 LEFT JOIN t2 ON 0) ON 0))) AS alias ON 0)";
EXECUTE prep_stmt ;
f1
Z
DROP TABLE t1,t2;
#
# Bug#28805105: Sig11 in calc_length_and_keyparts
#
CREATE TABLE t1 (cv VARCHAR(1) DEFAULT NULL);
INSERT INTO t1 VALUES ('h'), ('Q'), ('I'), ('q'), ('W');
ANALYZE TABLE t1;
Table	Op	Msg_type	Msg_text
test.t1	analyze	status	OK
EXPLAIN SELECT cv
FROM t1
WHERE EXISTS (SELECT alias1.cv AS field1
FROM t1 AS alias1 RIGHT JOIN t1 AS alias2
ON alias1.cv = alias2.cv
);
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	NULL	ALL	NULL	NULL	NULL	NULL	5	100.00	NULL
1	SIMPLE	<subquery2>	NULL	const	<auto_distinct_key>	<auto_distinct_key>	8	const	1	100.00	NULL
2	MATERIALIZED	alias2	NULL	ALL	NULL	NULL	NULL	NULL	5	100.00	NULL
2	MATERIALIZED	alias1	NULL	ALL	NULL	NULL	NULL	NULL	5	100.00	Using where; Using join buffer (Block Nested Loop)
Warnings:
Note	1003	/* select#1 */ select `test`.`t1`.`cv` AS `cv` from `test`.`t1` semi join (`test`.`t1` `alias2` left join `test`.`t1` `alias1` on((`test`.`alias1`.`cv` = `test`.`alias2`.`cv`))) where true
SELECT cv
FROM t1
WHERE EXISTS (SELECT alias1.cv AS field1
FROM t1 AS alias1 RIGHT JOIN t1 AS alias2
ON alias1.cv = alias2.cv
);
cv
h
Q
I
q
W
DROP TABLE t1;
# Bug#28970261: Sig6 in decorrelate_equality()
CREATE TABLE t1 (col_varchar_key varchar(1) DEFAULT NULL);
EXPLAIN SELECT *
FROM t1
WHERE col_varchar_key IN
(SELECT col_varchar_key
FROM t1
WHERE col_varchar_key =
(SELECT col_varchar_key
FROM t1
WHERE col_varchar_key > @var1
)
);
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	PRIMARY	t1	NULL	ALL	NULL	NULL	NULL	NULL	1	100.00	Using where
1	PRIMARY	t1	NULL	ALL	NULL	NULL	NULL	NULL	1	100.00	Using where; FirstMatch(t1); Using join buffer (Block Nested Loop)
3	UNCACHEABLE SUBQUERY	t1	NULL	ALL	NULL	NULL	NULL	NULL	1	100.00	Using where
Warnings:
Note	1003	/* select#1 */ select `test`.`t1`.`col_varchar_key` AS `col_varchar_key` from `test`.`t1` semi join (`test`.`t1`) where ((`test`.`t1`.`col_varchar_key` = `test`.`t1`.`col_varchar_key`) and (`test`.`t1`.`col_varchar_key` = (/* select#3 */ select `test`.`t1`.`col_varchar_key` from `test`.`t1` where (`test`.`t1`.`col_varchar_key` > <cache>((@`var1`))))))
SELECT *
FROM t1
WHERE col_varchar_key IN
(SELECT col_varchar_key
FROM t1
WHERE col_varchar_key =
(SELECT col_varchar_key
FROM t1
WHERE col_varchar_key > @var1
)
);
col_varchar_key
EXPLAIN SELECT *
FROM t1
WHERE col_varchar_key IN
(SELECT col_varchar_key
FROM t1
WHERE col_varchar_key =
(SELECT col_varchar_key
FROM t1
WHERE col_varchar_key = RAND()
)
);
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	PRIMARY	t1	NULL	ALL	NULL	NULL	NULL	NULL	1	100.00	Using where
1	PRIMARY	t1	NULL	ALL	NULL	NULL	NULL	NULL	1	100.00	Using where; FirstMatch(t1); Using join buffer (Block Nested Loop)
3	UNCACHEABLE SUBQUERY	t1	NULL	ALL	NULL	NULL	NULL	NULL	1	100.00	Using where
Warnings:
Note	1003	/* select#1 */ select `test`.`t1`.`col_varchar_key` AS `col_varchar_key` from `test`.`t1` semi join (`test`.`t1`) where ((`test`.`t1`.`col_varchar_key` = `test`.`t1`.`col_varchar_key`) and (`test`.`t1`.`col_varchar_key` = (/* select#3 */ select `test`.`t1`.`col_varchar_key` from `test`.`t1` where (`test`.`t1`.`col_varchar_key` = rand()))))
SELECT *
FROM t1
WHERE col_varchar_key IN
(SELECT col_varchar_key
FROM t1
WHERE col_varchar_key =
(SELECT col_varchar_key
FROM t1
WHERE col_varchar_key = RAND()
)
);
col_varchar_key
DROP TABLE t1;
#
# Bug#29356132:OPTIMIZED-AWAY SUBQUERY IN PREPARED STATEMENT CAUSES ASSERT FAILURE IN EXECUTION
#
CREATE TABLE t1 (f1 varchar(1));
INSERT INTO t1 VALUES ('5');
CREATE TABLE t2 (f1 varchar(1));
INSERT INTO t2 VALUES ('Y');
PREPARE prep_stmt FROM "SELECT t2.f1 FROM (t2 LEFT JOIN t1
 ON 1 IN (SELECT f1 FROM t1 WHERE FALSE))" ;
EXECUTE prep_stmt ;
f1
Y
DROP TABLE t1,t2;