After MDEV-25080 and MCOL-4901, UNIONs in outer select statements are pushed down to ColumnStore.
However, adding an ORDER BY clause to a UNION in outer select is not sorting the final result set as expected. Here is the expected output from InnoDB:
MariaDB [test]> show create table i1;
|
+-------+-------------------------------------------------------------------------------------------+
|
| Table | Create Table |
|
+-------+-------------------------------------------------------------------------------------------+
|
| i1 | CREATE TABLE `i1` (
|
`a` varchar(30) DEFAULT NULL
|
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
|
+-------+-------------------------------------------------------------------------------------------+
|
1 row in set (0.001 sec)
|
|
MariaDB [test]> show create table i2;
|
+-------+-------------------------------------------------------------------------------------------+
|
| Table | Create Table |
|
+-------+-------------------------------------------------------------------------------------------+
|
| i2 | CREATE TABLE `i2` (
|
`a` varchar(30) DEFAULT NULL
|
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
|
+-------+-------------------------------------------------------------------------------------------+
|
1 row in set (0.001 sec)
|
|
MariaDB [test]> select * from i1;
|
+------+
|
| a |
|
+------+
|
| abc |
|
| bcd |
|
| cde |
|
+------+
|
3 rows in set (0.001 sec)
|
|
MariaDB [test]> select * from i2;
|
+------+
|
| a |
|
+------+
|
| bcd |
|
| cde |
|
| def |
|
| efg |
|
+------+
|
4 rows in set (0.001 sec)
|
|
MariaDB [test]> select a from i1 union all select a from i2 order by a;
|
+------+
|
| a |
|
+------+
|
| abc |
|
| bcd |
|
| bcd |
|
| cde |
|
| cde |
|
| def |
|
| efg |
|
+------+
|
7 rows in set (0.002 sec)
|
Below is what ColumnStore is returning:
MariaDB [test]> show create table t1;
|
+-------+------------------------------------------------------------------------------------------------+
|
| Table | Create Table |
|
+-------+------------------------------------------------------------------------------------------------+
|
| t1 | CREATE TABLE `t1` (
|
`a` varchar(30) DEFAULT NULL
|
) ENGINE=Columnstore DEFAULT CHARSET=latin1 |
|
+-------+------------------------------------------------------------------------------------------------+
|
1 row in set (0.001 sec)
|
|
MariaDB [test]> show create table t2;
|
+-------+------------------------------------------------------------------------------------------------+
|
| Table | Create Table |
|
+-------+------------------------------------------------------------------------------------------------+
|
| t2 | CREATE TABLE `t2` (
|
`a` varchar(30) DEFAULT NULL
|
) ENGINE=Columnstore DEFAULT CHARSET=latin1 |
|
+-------+------------------------------------------------------------------------------------------------+
|
1 row in set (0.001 sec)
|
|
MariaDB [test]> select * from t1;
|
+------+
|
| a |
|
+------+
|
| abc |
|
| bcd |
|
| cde |
|
+------+
|
3 rows in set (0.019 sec)
|
|
MariaDB [test]> select * from t2;
|
+------+
|
| a |
|
+------+
|
| bcd |
|
| cde |
|
| def |
|
| efg |
|
+------+
|
4 rows in set (0.011 sec)
|
|
MariaDB [test]> select a from t1 union all select a from t2 order by a;
|
+------+
|
| a |
|
+------+
|
| abc |
|
| bcd |
|
| cde |
|
| bcd |
|
| cde |
|
| def |
|
| efg |
|
+------+
|
7 rows in set (0.028 sec)
|
Bug
Major
MCOL-4901 Allow pushdown of queries involving UNIONs in outer select to ColumnStore
Also update the following MTR tests once this MCOL is fixed:
mysql-test/columnstore/basic/t/mdev-25080.test
mysql-test/columnstore/basic/t/union.test
mysql-test/columnstore/basic/t/mcol641-union.test