Details
-
Bug
-
Status: Closed (View Workflow)
-
Critical
-
Resolution: Fixed
-
1.2.5
-
None
-
debian 9
-
2020-3, 2020-4, 2020-5
Description
bug not present in 1.1.7 and definitely present in 1.2.5
order by is not correctly processed for trivial queries
let's say we want to compute sales for region 'x' and 'y', for each of 3 business units 1,2 and 3 like this:
| bu | x | y |
|---|---|---|
| 1 | 10 | 12 |
| 2 | 11 | 11 |
| 3 | 12 | 10 |
when applying an order by clause, 'order by bu' or 'order by x' work but 'order by y' is ignored
drop table if exists t; |
create table t(a int, b varchar(10), c decimal(7,2)) engine=columnstore; |
insert into t(a,b,c) values |
(1,'x',10),(2,'x',11), (3, 'x', 12), |
(1,'y',12),(2,'y',11), (3, 'y', 10); |
|
|
-- order by column bu or x work fine
|
select
|
a as bu, |
sum(case when b='x' then c else null end) as 'x', |
sum(case when b='y' then c else null end) as 'y' |
from t |
group by bu |
order by bu /* or order by x */ |
;
|
|
|
-- order by column y produces a random ordering of rows
|
select
|
a as bu, |
sum(case when b='x' then c else null end) as 'x', |
sum(case when b='y' then c else null end) as 'y' |
from t |
group by bu |
order by y asc |
;
|
Attachments
Activity
I have submitted a patch for 1.4.4.
The patch for 1.4 does not fix 1.2, probably related to what Roman said previously.
Build verified: 1.4.4-1 source
/root/ColumnStore/buildColumnstoreFromGithubSource/server
commit 86a634a0feaf7788c9bcf7cc763e500d2be97d75
Author: Sergei Golubchik <serg@mariadb.org>
Date: Fri Feb 28 21:55:32 2020 +0100
Revert "make columnstore maturity gamma"
This reverts commit e4a0372cd08a53f97a62d6b6ef32114b553cacb7.
/root/ColumnStore/buildColumnstoreFromGithubSource/server/engine
commit ca3e2d78d6e1d06fb6711befe7bb2d618e801929
Merge: ec3630d f437152
Author: Patrick LeBlanc <43503225+pleblanc1976@users.noreply.github.com>
Date: Thu Mar 19 11:43:55 2020 -0500
Merge pull request #1113 from pleblanc1976/develop-1.4
Bumped version num to 1.4.4-1
Reproduced the issue in 1.2.5-1 and verified fix in 1.4.4-1
MariaDB [mytest]> select
-> a as bu,
-> sum(case when b='x' then c else null end) as 'x',
-> sum(case when b='y' then c else null end) as 'y'
-> from t
-> group by bu
-> order by bu /* or order by x */
-> ;
----------------
| bu | x | y |
----------------
| 1 | 10.00 | 12.00 |
| 2 | 11.00 | 11.00 |
| 3 | 12.00 | 10.00 |
----------------
3 rows in set (0.069 sec)
MariaDB [mytest]> select
-> a as bu,
-> sum(case when b='x' then c else null end) as 'x',
-> sum(case when b='y' then c else null end) as 'y'
-> from t
-> group by bu
-> order by y asc
-> ;
----------------
| bu | x | y |
----------------
| 3 | 12.00 | 10.00 |
| 2 | 11.00 | 11.00 |
| 1 | 10.00 | 12.00 |
----------------
3 rows in set (0.056 sec)
MariaDB [mytest]> select a as bu, sum(case when b='x' then c else null end) as 'x', sum(case when b='y' then c else null end) as 'y' from t group by bu order by y desc;
----------------
| bu | x | y |
----------------
| 1 | 10.00 | 12.00 |
| 2 | 11.00 | 11.00 |
| 3 | 12.00 | 10.00 |
----------------
3 rows in set (0.093 sec)
develop-1.4 should be tested having column x in mind, whilst develop-1.2 had issues with sorting over column y.
There was no patch for develop-1.4 untill this moment so 1.4 returned records in the wrong order. jrojas has just pushed it.
Build verified: 1.4.4-1 source
/root/ColumnStore/buildColumnstoreFromGithubSource/server
commit ec0071afa50b78930860a14802b8cfc9791f7d13
Author: Sergei Petrunia <psergey@askmonty.org>
Date: Thu Mar 26 01:26:39 2020 +0300
MDEV-21887: federatedx crashes on SELECT ... INTO query in select_handler code
/root/ColumnStore/buildColumnstoreFromGithubSource/server/engine
commit 6e953d01d268161ac50dabb6aa9a122ed1d43e0f
Merge: 4468c93 ba06727
Author: Gagan Goel <gagan.nith@gmail.com>
Date: Thu Mar 26 11:41:35 2020 -0400
Merge pull request #1117 from drrtuy/MCOL-3828_1_4
MCOL-3828 This commit replaces the method that calls JOIN::optimise()
Verified both x and y columns, asc and desc.
MariaDB [mytest]> create table t(a int, b varchar(10), c decimal(7,2)) engine=columnstore;
Query OK, 0 rows affected (0.263 sec)
MariaDB [mytest]> insert into t(a,b,c) values
-> (1,'x',10),(2,'x',11), (3, 'x', 12),
-> (1,'y',12),(2,'y',11), (3, 'y', 10);
Query OK, 6 rows affected (0.632 sec)
Records: 6 Duplicates: 0 Warnings: 0
MariaDB [mytest]> select
-> a as bu,
-> sum(case when b='x' then c else null end) as 'x',
-> sum(case when b='y' then c else null end) as 'y'
-> from t
-> group by bu
-> order by bu /* or order by x */
-> ;
----------------
| bu | x | y |
----------------
| 1 | 10.00 | 12.00 |
| 2 | 11.00 | 11.00 |
| 3 | 12.00 | 10.00 |
----------------
3 rows in set (0.232 sec)
MariaDB [mytest]> select
-> a as bu,
-> sum(case when b='x' then c else null end) as 'x',
-> sum(case when b='y' then c else null end) as 'y'
-> from t
-> group by bu
-> order by y asc
-> ;
----------------
| bu | x | y |
----------------
| 3 | 12.00 | 10.00 |
| 2 | 11.00 | 11.00 |
| 1 | 10.00 | 12.00 |
----------------
3 rows in set (0.105 sec)
MariaDB [mytest]> select a as bu, sum(case when b='x' then c else null end) as 'x', sum(case when b='y' then c else null end) as 'y' from t group by bu order by y desc;
----------------
| bu | x | y |
----------------
| 1 | 10.00 | 12.00 |
| 2 | 11.00 | 11.00 |
| 3 | 12.00 | 10.00 |
----------------
3 rows in set (0.100 sec)
MariaDB [mytest]> select a as bu, sum(case when b='x' then c else null end) as 'x', sum(case when b='y' then c else null end) as 'y' from t group by bu order by x asc;
----------------
| bu | x | y |
----------------
| 1 | 10.00 | 12.00 |
| 2 | 11.00 | 11.00 |
| 3 | 12.00 | 10.00 |
----------------
3 rows in set (0.061 sec)
MariaDB [mytest]> select a as bu, sum(case when b='x' then c else null end) as 'x', sum(case when b='y' then c else null end) as 'y' from t group by bu order by x desc;
----------------
| bu | x | y |
----------------
| 3 | 12.00 | 10.00 |
| 2 | 11.00 | 11.00 |
| 1 | 10.00 | 12.00 |
----------------
3 rows in set (0.050 sec)
It looks like a bug in our fork code.
There are at least two workarounds though. First is to put the ORDER BY key column 'y' in the second place after the projected bu.
MariaDB [test]> select a as bu, sum(case when b='y' then c else null end) as 'y',sum(case when b='x' then c else null end) as 'x' from t group by bu order by y desc;+------+-------+-------+| bu | y | x |+------+-------+-------+| 1 | 12.00 | 10.00 || 2 | 11.00 | 11.00 || 3 | 10.00 | 12.00 |+------+-------+-------+3 rows in set (0.038 sec)Another is to use CS internal sorting wrapping the original query into a subquery if it is possible:
MariaDB [test]> select * from (select a as bu, sum(case when b='x' then c else null end) as 'x', sum(case when b='y' then c else null end) as 'y' from t group by bu order by y asc limit 10) t;+------+-------+-------+| bu | x | y |+------+-------+-------+| 3 | 12.00 | 10.00 || 2 | 11.00 | 11.00 || 1 | 10.00 | 12.00 |+------+-------+-------+3 rows in set (0.028 sec)