Details
-
Bug
-
Status: Stalled (View Workflow)
-
Major
-
Resolution: Unresolved
-
23.10.1
-
None
-
None
Description
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) |
Attachments
Issue Links
- is blocked by
-
-
- Closed
-
- relates to
-
MCOL-4901 Allow pushdown of queries involving UNIONs in outer select to ColumnStore
-
- Closed
-