Details
-
New Feature
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Fixed
-
None
-
None
-
None
-
2019-04
Description
We do not support ORDER BY within subqueries that are unioned, for example:
SELECT * FROM |
((SELECT a, b FROM t1 ORDER BY b LIMIT 2) sq1 |
UNION ALL |
(SELECT a, b FROM t2 ORDER BY b LIMIT 2) sq2) |
ORDER BY 1,2; |
Attachments
Activity
| Field | Original Value | New Value |
|---|---|---|
| Assignee | Roman [ drrtuy ] | Andrew Hutchings [ linuxjedi ] |
| Status | Open [ 1 ] | In Review [ 10002 ] |
For QA: Here are the steps to reproduce the issue.
create table t1 (a bigint, b bigint)engine=columnstore;
create table t2 (a bigint, b bigint)engine=columnstore;
insert into t1 values (5,5),(4,4),(3,3);
insert into t1 values (5,5),(4,4),(3,3);
SELECT * FROM
((SELECT a, b FROM t1 ORDER BY b LIMIT 2) sq1
UNION ALL
(SELECT a, b FROM t2 ORDER BY b LIMIT 2) sq2)
ORDER BY 1,2;
| Fix Version/s | 1.2.4 [ 23254 ] |
| Sprint | 2019-04 [ 323 ] |
| Comment | [ A comment with security level 'Developers' was removed. ] |
| Status | In Review [ 10002 ] | In Testing [ 10301 ] |
| Assignee | Andrew Hutchings [ linuxjedi ] | Daniel Lee [ dleeyh ] |
| Support case ID | 27250 |
Reproduced the issue on 1.2.3-1
It is not yet fixed in last nights 1.2.4-1 nightly. It looks like it was merged after the build was made.
The SELECT statement in the last comment has an extra comma in the 2nd subquery.
| Support case ID | 27250 | 27250 27936 |
Build verified: 1.2.4-1 nightly
server commit:
137b9a8
engine commit:
b8de456
Reproduced incorrect result issue in 1.2.3-1:
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 13
Server version: 10.3.13-MariaDB-log Columnstore 1.2.3-1
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MariaDB [mytest]> SELECT * FROM ((SELECT a, b FROM t1 ORDER BY b LIMIT 2) UNION ALL (SELECT a, b FROM t2 ORDER BY b LIMIT 2)) as sq3 ORDER BY 1,2;
----------+
| a | b |
----------+
| 4 | 4 |
| 5 | 5 |
----------+
2 rows in set (0.110 sec)
1.2.4-1
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 14
Server version: 10.3.13-MariaDB-log Columnstore 1.2.4-1
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MariaDB [mytest]> SELECT * FROM ((SELECT a, b FROM t1 ORDER BY b LIMIT 2) UNION ALL (SELECT a, b FROM t2 ORDER BY b LIMIT 2)) as sq3 ORDER BY 1,2;
----------+
| a | b |
----------+
| 3 | 3 |
| 3 | 3 |
----------+
2 rows in set (0.033 sec)
| issue.field.resolutiondate | 2019-04-26 15:52:11.0 | 2019-04-26 15:52:11.062 |
| Resolution | Fixed [ 1 ] | |
| Status | In Testing [ 10301 ] | Closed [ 6 ] |
| Description |
We do not support ORDER BY within subqueries that are unioned, for example:
{code:sql} SELECT * FROM ((SELECT a, b FROM t1 ORDER BY b LIMIT 100) sq1 UNION ALL (SELECT a, b, FROM t2 ORDER BY b LIMIT 100) sq2) ORDER BY 1,2; {code} |
We do not support ORDER BY within subqueries that are unioned, for example:
{code:sql} SELECT * FROM ((SELECT a, b FROM t1 ORDER BY b LIMIT 2) sq1 UNION ALL (SELECT a, b FROM t2 ORDER BY b LIMIT 2) sq2) ORDER BY 1,2; {code} |
| Zendesk Related Tickets | 112487 147319 |
create table t1 (a bigint, b bigint)engine=columnstore;
create table t2 (a bigint, b bigint)engine=columnstore;
insert into t1 values (5,5),(4,4),(3,3);
insert into t1 values (5,5),(4,4),(3,3);
SELECT * FROM(
SELECT * FROM (
select * from (SELECT a, b FROM t1 ORDER BY b LIMIT 100) sq1
UNION ALL
select * from (SELECT a, b FROM t2 ORDER BY b LIMIT 100) sq2) sq3
order by sq3.a limit 999) sq4;
This query produces expected results. LimitedOrderBy got called three times as expected. And if I add ORDER BY at the top level then filesort comes into play.
I will get back to the original data set and problem description.