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, 100 insertions, 13 deletions

diff --git a/mysql-test/t/union.test b/mysql-test/t/union.test
index 877509a9fc0..057c90b7c41 100644
--- a/mysql-test/t/union.test
+++ b/mysql-test/t/union.test
@@ -216,14 +216,12 @@ select found_rows();
 select found_rows();
 (SELECT SQL_CALC_FOUND_ROWS * FROM t1) UNION all (SELECT * FROM t2 LIMIT 1);
 select found_rows();
-# This used to work in 4.0 but not anymore in 4.1
---error 1064
 (SELECT SQL_CALC_FOUND_ROWS * FROM t1 LIMIT 1) UNION SELECT * FROM t2 LIMIT 1;
-#select found_rows();
+select found_rows();
 
 # In these case found_rows() should work
+--error ER_WRONG_USAGE
 SELECT SQL_CALC_FOUND_ROWS * FROM t1 LIMIT 1 UNION all SELECT * FROM t2 LIMIT 2;
-select found_rows();
 SELECT SQL_CALC_FOUND_ROWS * FROM t1 UNION all SELECT * FROM t2 LIMIT 2;
 select found_rows();
 
@@ -232,16 +230,16 @@ SELECT SQL_CALC_FOUND_ROWS * FROM t1 UNION SELECT * FROM t2 LIMIT 2;
 select found_rows();
 SELECT SQL_CALC_FOUND_ROWS * FROM t1 UNION SELECT * FROM t2 LIMIT 100;
 select found_rows();
+--error ER_WRONG_USAGE
 SELECT SQL_CALC_FOUND_ROWS * FROM t1 LIMIT 100 UNION SELECT * FROM t2;
-select found_rows();
+--error ER_WRONG_USAGE
 SELECT SQL_CALC_FOUND_ROWS * FROM t1 LIMIT 1 UNION SELECT * FROM t2;
-select found_rows();
+--error ER_WRONG_USAGE
 SELECT SQL_CALC_FOUND_ROWS * FROM t1 LIMIT 1 UNION SELECT * FROM t2 LIMIT 2;
-select found_rows();
 SELECT SQL_CALC_FOUND_ROWS * FROM t1 UNION SELECT * FROM t2 LIMIT 2,2;
 select found_rows();
+--error ER_WRONG_USAGE
 SELECT SQL_CALC_FOUND_ROWS * FROM t1 limit 2,2 UNION SELECT * FROM t2;
-select found_rows();
 
 # Test some limits with ORDER BY
 SELECT * FROM t1 UNION SELECT * FROM t2 ORDER BY a desc LIMIT 1;
@@ -255,7 +253,7 @@ create temporary table t1 select a from t1 union select a from t2;
 drop temporary table t1;
 --error ER_TABLE_EXISTS_ERROR
 create table t1 select a from t1 union select a from t2;
---error 1054
+--error ER_TABLENAME_NOT_ALLOWED_HERE
 select a from t1 union select a from t2 order by t2.a;
 drop table t1,t2;
 
@@ -723,12 +721,12 @@ drop table t1;
 # Enum merging test
 #
 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;
-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;
 drop table t2;
@@ -1086,13 +1084,11 @@ SELECT * FROM t1 UNION SELECT * FROM t1 ORDER BY a + 12;
 
 
 --echo # Should not crash
---error ER_CANT_USE_OPTION_HERE
 EXPLAIN EXTENDED
 SELECT * FROM t1 UNION SELECT * FROM t1
   ORDER BY MATCH(a) AGAINST ('+abc' IN BOOLEAN MODE);
 
 --echo # Should not crash
---error ER_CANT_USE_OPTION_HERE
 SELECT * FROM t1 UNION SELECT * FROM t1
   ORDER BY MATCH(a) AGAINST ('+abc' IN BOOLEAN MODE);
 
@@ -1300,3 +1296,94 @@ create table t1 (a int);
 insert t1 values (1),(2),(3),(1);
 explain select 1 from dual where exists (select max(a) from t1 group by a union select a+2 from t1);
 drop table t1;
+
+--echo #
+--echo # WL#1763 Avoid creating temporary table in UNION ALL
+--echo #
+
+EXPLAIN SELECT 1 UNION ALL SELECT 1 LIMIT 1 OFFSET 1;
+
+--echo # 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';
+
+DROP TABLE t1, t2, t3;
+
+--echo # Bug #17602922 RESULT DIFFERENCES IN UNION QUERIES WITH IN
+--echo # (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');
+
+CREATE TABLE t2
+SELECT a FROM t1 WHERE a IN (SELECT 'r' FROM t1 UNION ALL SELECT 'j');
+
+SELECT * FROM t2;
+
+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 ;
+
+SELECT FOUND_ROWS();
+
+DROP TABLE t1, t2;
+
+--echo # Bug #17669551 CRASH/ASSERT AT SELECT_CREATE::PREPARE2 AT
+--echo # SQL_INSERT.CC
+
+CREATE TABLE t1 (a INT);
+
+--error ER_DUP_FIELDNAME
+CREATE TABLE t2 SELECT a, a FROM t1 UNION ALL SELECT a, a FROM t1;
+
+DROP TABLE t1;
+
+--echo # 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;
+
+DROP TABLE t1;
+
+--echo # 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;
+SELECT SQL_CALC_FOUND_ROWS * FROM t2 UNION ALL SELECT * FROM t1;
+SELECT FOUND_ROWS();
+
+SELECT COUNT(*) FROM (
+SELECT                     * FROM t1 UNION ALL SELECT * FROM t2) q;
+SELECT SQL_CALC_FOUND_ROWS * FROM t1 UNION ALL SELECT * FROM t2;
+SELECT FOUND_ROWS();
+
+DROP TABLE t1, t2;
+
+--echo # End of WL1763 tests
+