[MCOL-3485] ORDER BY no longer working in some queries Created: 2019-09-06  Updated: 2020-03-26  Resolved: 2020-03-26

Status: Closed
Project: MariaDB ColumnStore
Component/s: ?
Affects Version/s: 1.2.5
Fix Version/s: 1.4.4

Type: Bug Priority: Critical
Reporter: antoine Assignee: Daniel Lee (Inactive)
Resolution: Fixed Votes: 0
Labels: None
Environment:

debian 9

Sprint: 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
 
;



 Comments   
Comment by Roman [ 2019-12-04 ]

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)

Comment by Jose Rojas (Inactive) [ 2020-02-27 ]

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.

Comment by Daniel Lee (Inactive) [ 2020-03-25 ]

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)

Comment by Roman [ 2020-03-26 ]

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.

Comment by Daniel Lee (Inactive) [ 2020-03-26 ]

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)

Generated at Thu Feb 08 02:43:04 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.