MDEV-334: Backport of UNION ALL optimization from mysql-5.7.

Although the original code of mysql-5.7 was adjusted
to the current MariaDB code the main ideas of the optimization
were preserved.

1 files changed, 116 insertions, 46 deletions

diff --git a/mysql-test/r/union.result b/mysql-test/r/union.result
index 6d99cad30f0..5cfb7e003d2 100644
--- a/mysql-test/r/union.result
+++ b/mysql-test/r/union.result
@@ -107,7 +107,6 @@ explain select a,b from t1 union all select a,b from t2;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	4	
 2	UNION	t2	ALL	NULL	NULL	NULL	NULL	4	
-NULL	UNION RESULT	<union1,2>	ALL	NULL	NULL	NULL	NULL	NULL	
 explain select xx from t1 union select 1;
 ERROR 42S22: Unknown column 'xx' in 'field list'
 explain select a,b from t1 union select 1;
@@ -341,14 +340,13 @@ select found_rows();
 found_rows()
 4
 (SELECT SQL_CALC_FOUND_ROWS * FROM t1 LIMIT 1) UNION SELECT * FROM t2 LIMIT 1;
-ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '' at line 1
-SELECT SQL_CALC_FOUND_ROWS * FROM t1 LIMIT 1 UNION all SELECT * FROM t2 LIMIT 2;
 a
 1
-3
 select found_rows();
 found_rows()
-6
+4
+SELECT SQL_CALC_FOUND_ROWS * FROM t1 LIMIT 1 UNION all SELECT * FROM t2 LIMIT 2;
+ERROR HY000: Incorrect usage of UNION and LIMIT
 SELECT SQL_CALC_FOUND_ROWS * FROM t1 UNION all SELECT * FROM t2 LIMIT 2;
 a
 1
@@ -362,7 +360,7 @@ a
 2
 select found_rows();
 found_rows()
-6
+5
 SELECT SQL_CALC_FOUND_ROWS * FROM t1 UNION SELECT * FROM t2 LIMIT 100;
 a
 1
@@ -374,31 +372,11 @@ select found_rows();
 found_rows()
 5
 SELECT SQL_CALC_FOUND_ROWS * FROM t1 LIMIT 100 UNION SELECT * FROM t2;
-a
-1
-2
-3
-4
-5
-select found_rows();
-found_rows()
-5
+ERROR HY000: Incorrect usage of UNION and LIMIT
 SELECT SQL_CALC_FOUND_ROWS * FROM t1 LIMIT 1 UNION SELECT * FROM t2;
-a
-1
-3
-4
-5
-select found_rows();
-found_rows()
-6
+ERROR HY000: Incorrect usage of UNION and LIMIT
 SELECT SQL_CALC_FOUND_ROWS * FROM t1 LIMIT 1 UNION SELECT * FROM t2 LIMIT 2;
-a
-1
-3
-select found_rows();
-found_rows()
-6
+ERROR HY000: Incorrect usage of UNION and LIMIT
 SELECT SQL_CALC_FOUND_ROWS * FROM t1 UNION SELECT * FROM t2 LIMIT 2,2;
 a
 3
@@ -407,13 +385,7 @@ select found_rows();
 found_rows()
 5
 SELECT SQL_CALC_FOUND_ROWS * FROM t1 limit 2,2 UNION SELECT * FROM t2;
-a
-3
-4
-5
-select found_rows();
-found_rows()
-5
+ERROR HY000: Incorrect usage of UNION and LIMIT
 SELECT * FROM t1 UNION SELECT * FROM t2 ORDER BY a desc LIMIT 1;
 a
 5
@@ -430,7 +402,7 @@ drop temporary table t1;
 create table t1 select a from t1 union select a from t2;
 ERROR 42S01: Table 't1' already exists
 select a from t1 union select a from t2 order by t2.a;
-ERROR 42S22: Unknown column 't2.a' in 'order clause'
+ERROR 42000: Table 't2' from one of the SELECTs cannot be used in field list
 drop table t1,t2;
 select length(version()) > 1 as `*` UNION select 2;
 *
@@ -1202,32 +1174,32 @@ foo
 bar
 drop table t1;
 CREATE TABLE t1 (
-a ENUM('ä','ö','ü') character set utf8 not null default 'ü',
+a ENUM('ä','ö','ü') character set utf8 not null default 'ü',
 b ENUM("one", "two") character set utf8,
 c ENUM("one", "two")
 );
 show create table t1;
 Table	Create Table
 t1	CREATE TABLE `t1` (
-  `a` enum('ä','ö','ü') CHARACTER SET utf8 NOT NULL DEFAULT 'ü',
+  `a` enum('ä','ö','ü') CHARACTER SET utf8 NOT NULL DEFAULT 'ü',
   `b` enum('one','two') CHARACTER SET utf8 DEFAULT NULL,
   `c` enum('one','two') DEFAULT NULL
 ) ENGINE=MyISAM DEFAULT CHARSET=latin1
-insert into t1 values ('ä', 'one', 'one'), ('ö', 'two', 'one'), ('ü', NULL, NULL);
+insert into t1 values ('ä', 'one', 'one'), ('ö', 'two', 'one'), ('ü', NULL, NULL);
 create table t2 select NULL union select a from t1;
 show columns from t2;
 Field	Type	Null	Key	Default	Extra
-NULL	enum('ä','ö','ü')	YES		NULL	
+NULL	enum('ä','ö','ü')	YES		NULL	
 drop table t2;
 create table t2 select a from t1 union select NULL;
 show columns from t2;
 Field	Type	Null	Key	Default	Extra
-a	enum('ä','ö','ü')	YES		NULL	
+a	enum('ä','ö','ü')	YES		NULL	
 drop table t2;
 create table t2 select a from t1 union select a from t1;
 show columns from t2;
 Field	Type	Null	Key	Default	Extra
-a	varchar(1)	NO			
+a	varchar(2)	NO			
 drop table t2;
 create table t2 select a from t1 union select c from t1;
 drop table t2;
@@ -1616,11 +1588,18 @@ a
 EXPLAIN EXTENDED
 SELECT * FROM t1 UNION SELECT * FROM t1
 ORDER BY MATCH(a) AGAINST ('+abc' IN BOOLEAN MODE);
-ERROR 42000: Incorrect usage/placement of 'MATCH()'
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	2	100.00	
+2	UNION	t1	ALL	NULL	NULL	NULL	NULL	2	100.00	
+NULL	UNION RESULT	<union1,2>	ALL	NULL	NULL	NULL	NULL	NULL	NULL	Using filesort
+Warnings:
+Note	1003	select `test`.`t1`.`a` AS `a` from `test`.`t1` union select `test`.`t1`.`a` AS `a` from `test`.`t1` order by (match `a` against ('+abc' in boolean mode))
 # Should not crash
 SELECT * FROM t1 UNION SELECT * FROM t1
 ORDER BY MATCH(a) AGAINST ('+abc' IN BOOLEAN MODE);
-ERROR 42000: Incorrect usage/placement of 'MATCH()'
+a
+1
+2
 # Should not crash
 (SELECT * FROM t1) UNION (SELECT * FROM t1)
 ORDER BY MATCH(a) AGAINST ('+abc' IN BOOLEAN MODE);
@@ -1638,7 +1617,7 @@ NULL	UNION RESULT	<union1,2>	ALL	NULL	NULL	NULL	NULL	NULL	NULL	Using filesort
 3	DEPENDENT SUBQUERY	t2	ALL	NULL	NULL	NULL	NULL	2	100.00	Using where
 Warnings:
 Note	1276	Field or reference 'a' of SELECT #3 was resolved in SELECT #-1
-Note	1003	select `test`.`t1`.`a` AS `a` from `test`.`t1` union select `test`.`t1`.`a` AS `a` from `test`.`t1` order by (select `a` from `test`.`t2` where (`test`.`t2`.`b` = 12))
+Note	1003	select `test`.`t1`.`a` AS `a` from `test`.`t1` union select `test`.`t1`.`a` AS `a` from `test`.`t1` order by <expr_cache><>((select `a` from `test`.`t2` where (`test`.`t2`.`b` = 12)))
 # Should not crash
 SELECT * FROM t1 UNION SELECT * FROM t1
 ORDER BY (SELECT a FROM t2 WHERE b = 12);
@@ -1901,3 +1880,94 @@ id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 3	UNION	t1	ALL	NULL	NULL	NULL	NULL	4	
 NULL	UNION RESULT	<union2,3>	ALL	NULL	NULL	NULL	NULL	NULL	
 drop table t1;
+#
+# WL#1763 Avoid creating temporary table in UNION ALL
+#
+EXPLAIN SELECT 1 UNION ALL SELECT 1 LIMIT 1 OFFSET 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	UNION	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
+# Bug #17579498 CHANGES IN DATATYPE OF THE RESULT QUERY IN UNION.
+CREATE TABLE t1 (a TIME);
+CREATE TABLE t2 (b DATETIME);
+CREATE TABLE t3
+SELECT a FROM t1 UNION ALL SELECT b FROM t2;
+SELECT column_name, column_type
+FROM information_schema.columns
+WHERE TABLE_NAME='t3';
+column_name	column_type
+a	datetime
+DROP TABLE t1, t2, t3;
+# Bug #17602922 RESULT DIFFERENCES IN UNION QUERIES WITH IN
+# (SUBQUERY-UNION ALL)
+CREATE TABLE t1 (a VARCHAR(1));
+INSERT INTO t1 VALUES (NULL);
+INSERT INTO t1 VALUES (NULL);
+INSERT INTO t1 VALUES ('j');
+INSERT INTO t1 VALUES ('k');
+INSERT INTO t1 VALUES ('r');
+INSERT INTO t1 VALUES ('r');
+INSERT INTO t1 VALUES ('h');
+SELECT a FROM t1 WHERE a IN (SELECT 'r' FROM t1 UNION ALL SELECT 'j');
+a
+j
+r
+r
+CREATE TABLE t2
+SELECT a FROM t1 WHERE a IN (SELECT 'r' FROM t1 UNION ALL SELECT 'j');
+SELECT * FROM t2;
+a
+j
+r
+r
+DROP TABLE t1, t2;
+CREATE TABLE t1 (a INT PRIMARY KEY);
+CREATE TABLE t2 (a INT PRIMARY KEY);
+INSERT INTO t2 VALUES (1);
+SELECT a, SUM(a) FROM t2 UNION ALL SELECT a, MIN(a) FROM t1 ;
+a	SUM(a)
+1	1
+NULL	NULL
+SELECT FOUND_ROWS();
+FOUND_ROWS()
+2
+DROP TABLE t1, t2;
+# Bug #17669551 CRASH/ASSERT AT SELECT_CREATE::PREPARE2 AT
+# SQL_INSERT.CC
+CREATE TABLE t1 (a INT);
+CREATE TABLE t2 SELECT a, a FROM t1 UNION ALL SELECT a, a FROM t1;
+ERROR 42S21: Duplicate column name 'a'
+DROP TABLE t1;
+# Bug #17694956 RESULT DIFFERENCES IN UNION ALL QUERIES WITH LIMIT
+CREATE TABLE t1 (a INT);
+INSERT INTO t1 VALUES (1);
+(SELECT a FROM t1 ORDER BY a LIMIT 0) UNION ALL SELECT a FROM t1;
+a
+1
+DROP TABLE t1;
+# Bug #17708480 FOUND_ROWS() VALUE DO NOT MATCH WITH UNION ALL QUERIES
+CREATE TABLE t1 (a INT) ENGINE=MEMORY;
+CREATE TABLE t2 (a INT) ENGINE=MEMORY;
+INSERT INTO t2 VALUES (1);
+SELECT COUNT(*) FROM (
+SELECT                     * FROM t2 UNION ALL SELECT * FROM t1) q;
+COUNT(*)
+1
+SELECT SQL_CALC_FOUND_ROWS * FROM t2 UNION ALL SELECT * FROM t1;
+a
+1
+SELECT FOUND_ROWS();
+FOUND_ROWS()
+1
+SELECT COUNT(*) FROM (
+SELECT                     * FROM t1 UNION ALL SELECT * FROM t2) q;
+COUNT(*)
+1
+SELECT SQL_CALC_FOUND_ROWS * FROM t1 UNION ALL SELECT * FROM t2;
+a
+1
+SELECT FOUND_ROWS();
+FOUND_ROWS()
+1
+DROP TABLE t1, t2;
+# End of WL1763 tests