#
# MDEV-25080: Allow pushdown of queries involving UNIONs
# in outer select to foreign engines
#
# Remove the sorted_result MTR qualifier and add ORDER BY
# clause after MCOL-5222 is fixed
#
CREATE USER IF NOT EXISTS'cejuser'@'localhost' IDENTIFIED BY 'Vagrant1|0000001';
GRANT ALL PRIVILEGES ON *.* TO 'cejuser'@'localhost';
FLUSH PRIVILEGES;
DROP DATABASE IF EXISTS mdev25080;
CREATE DATABASE mdev25080;
USE mdev25080;
CREATE TABLE t1 (a varchar(30)) ENGINE=ColumnStore;
CREATE TABLE t2 (a varchar(30)) ENGINE=ColumnStore;
CREATE TABLE t3 (a varchar(30)) ENGINE=MyISAM;
CREATE TABLE t4 (a varchar(30)) ENGINE=MyISAM;
INSERT INTO t1 VALUES ('abc'), ('bcd'), ('cde');
INSERT INTO t2 VALUES ('bcd'), ('cde'), ('def'), ('efg');
INSERT INTO t3 VALUES ('t3_myisam1'), ('t3_myisam2'), ('t3_myisam3');
INSERT INTO t4 VALUES ('t4_myisam1'), ('t4_myisam2'), ('t4_myisam3');
# Pushdown of the whole UNION
SELECT * FROM t1 UNION SELECT * FROM t2;
a
abc
bcd
cde
def
efg
EXPLAIN SELECT * FROM t1 UNION SELECT * FROM t2;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
NULL	PUSHED UNION	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
SELECT * FROM t1 UNION ALL SELECT * FROM t2;
a
abc
bcd
bcd
cde
cde
def
efg
EXPLAIN SELECT * FROM t1 UNION ALL SELECT * FROM t2;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
NULL	PUSHED UNION	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
# UNION with a foreign engine
SELECT * FROM t1 UNION SELECT * FROM t3;
a
abc
bcd
cde
t3_myisam1
t3_myisam2
t3_myisam3
EXPLAIN SELECT * FROM t1 UNION SELECT * FROM t3;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
NULL	PUSHED UNION	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
# More than two SELECTs in a UNIT:
SELECT * FROM t1 UNION
SELECT * FROM t2 UNION ALL
SELECT * FROM t1;
a
abc
abc
bcd
bcd
cde
cde
def
efg
EXPLAIN SELECT * FROM t1 UNION
SELECT * FROM t2 UNION ALL
SELECT * FROM t1;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
NULL	PUSHED UNION	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
(SELECT * FROM t1 UNION
SELECT * FROM t2) UNION ALL
SELECT * FROM t1;
a
abc
abc
bcd
bcd
cde
cde
def
efg
EXPLAIN (SELECT * FROM t1 UNION
SELECT * FROM t2) UNION ALL
SELECT * FROM t1;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
NULL	PUSHED UNION	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
SELECT * FROM t1 UNION
SELECT * FROM t2 UNION ALL
SELECT * FROM t3 UNION
SELECT * FROM t4;
a
abc
bcd
cde
def
efg
t3_myisam1
t3_myisam2
t3_myisam3
t4_myisam1
t4_myisam2
t4_myisam3
EXPLAIN SELECT * FROM t1 UNION
SELECT * FROM t2 UNION ALL
SELECT * FROM t3 UNION
SELECT * FROM t4;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
NULL	PUSHED UNION	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
(SELECT * FROM t1 UNION
SELECT * FROM t2) UNION ALL
(SELECT * FROM t3 UNION
SELECT * FROM t4);
a
abc
bcd
cde
def
efg
t3_myisam1
t3_myisam2
t3_myisam3
t4_myisam1
t4_myisam2
t4_myisam3
EXPLAIN (SELECT * FROM t1 UNION
SELECT * FROM t2) UNION ALL
(SELECT * FROM t3 UNION
SELECT * FROM t4);
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
NULL	PUSHED UNION	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
SELECT count(*) FROM t1 UNION
SELECT count(*) FROM t2 UNION ALL
SELECT count(*)+20 FROM t2 UNION
SELECT count(*)+5 FROM t1;
count(*)
24
3
4
8
EXPLAIN
SELECT count(*) FROM t1 UNION
SELECT count(*) FROM t2 UNION ALL
SELECT count(*)+20 FROM t2 UNION
SELECT count(*)+5 FROM t1;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
NULL	PUSHED UNION	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
# UNION inside a derived table: the whole derived table must be pushed
SELECT a FROM
(SELECT a FROM t1 UNION ALL SELECT a FROM t2) q ORDER BY a;
a
abc
bcd
bcd
cde
cde
def
efg
EXPLAIN
SELECT a FROM
(SELECT a FROM t1 UNION ALL SELECT a FROM t2) q ORDER BY a;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	PUSHED SELECT	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
SELECT a FROM
(SELECT a FROM t1 UNION ALL SELECT a FROM t3) q ORDER BY a;
a
abc
bcd
cde
t3_myisam1
t3_myisam2
t3_myisam3
EXPLAIN
SELECT a FROM
(SELECT a FROM t1 UNION ALL SELECT a FROM t3) q ORDER BY a;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	PUSHED SELECT	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
# Prepared statements
PREPARE stmt FROM "SELECT * FROM t1 UNION
  SELECT * FROM t2";
EXECUTE stmt;
a
abc
bcd
cde
def
efg
EXECUTE stmt;
a
abc
bcd
cde
def
efg
EXECUTE stmt;
a
abc
bcd
cde
def
efg
PREPARE stmt FROM "EXPLAIN SELECT * FROM t1 UNION
  SELECT * FROM t2";
EXECUTE stmt;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
NULL	PUSHED UNION	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
EXECUTE stmt;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
NULL	PUSHED UNION	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
PREPARE stmt FROM "(SELECT * FROM t1 UNION
  SELECT * FROM t2) UNION ALL
  (SELECT * FROM t1 UNION
  SELECT * FROM t2)";
EXECUTE stmt;
a
abc
abc
bcd
bcd
cde
cde
def
def
efg
efg
EXECUTE stmt;
a
abc
abc
bcd
bcd
cde
cde
def
def
efg
efg
EXECUTE stmt;
a
abc
abc
bcd
bcd
cde
cde
def
def
efg
efg
PREPARE stmt FROM "EXPLAIN (SELECT * FROM t1 UNION
  SELECT * FROM t2) UNION ALL
  (SELECT * FROM t1 UNION
  SELECT * FROM t2)";
EXECUTE stmt;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
NULL	PUSHED UNION	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
EXECUTE stmt;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
NULL	PUSHED UNION	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
# MCOL-5432 Disable UNION pushdown if an ORDER BY or a LIMIT
# clause is involved, until MCOL-5222 is fixed.
SELECT * FROM t1 UNION ALL SELECT * FROM t2 ORDER BY a;
a
abc
bcd
bcd
cde
cde
def
efg
EXPLAIN SELECT * FROM t1 UNION ALL SELECT * FROM t2 ORDER BY a;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	2000	
2	UNION	t2	ALL	NULL	NULL	NULL	NULL	2000	
NULL	UNION RESULT	<union1,2>	ALL	NULL	NULL	NULL	NULL	NULL	Using filesort
SELECT * FROM t1 UNION ALL SELECT * FROM t2 LIMIT 3;
a
abc
bcd
cde
EXPLAIN SELECT * FROM t1 UNION ALL SELECT * FROM t2 LIMIT 3;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	2000	
2	UNION	t2	ALL	NULL	NULL	NULL	NULL	2000	
SELECT * FROM t1 UNION ALL SELECT * FROM t2 ORDER BY a DESC LIMIT 5;
a
efg
def
cde
cde
bcd
EXPLAIN SELECT * FROM t1 UNION ALL SELECT * FROM t2 ORDER BY a DESC LIMIT 5;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	2000	
2	UNION	t2	ALL	NULL	NULL	NULL	NULL	2000	
NULL	UNION RESULT	<union1,2>	ALL	NULL	NULL	NULL	NULL	NULL	Using filesort
SELECT * FROM t1 UNION ALL SELECT * FROM t2 ORDER BY a LIMIT 3 OFFSET 2;
a
bcd
cde
cde
EXPLAIN SELECT * FROM t1 UNION ALL SELECT * FROM t2 ORDER BY a LIMIT 3 OFFSET 2;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	2000	
2	UNION	t2	ALL	NULL	NULL	NULL	NULL	2000	
NULL	UNION RESULT	<union1,2>	ALL	NULL	NULL	NULL	NULL	NULL	Using filesort
DROP USER 'cejuser'@'localhost';
DROP TABLE t1, t2, t3, t4;
DROP DATABASE mdev25080;