[MCOL-1829] Output of 'select * (with order by limit) queries' returns unexpected result Created: 2018-10-23  Updated: 2020-08-25  Resolved: 2019-01-21

Status: Closed
Project: MariaDB ColumnStore
Component/s: None
Affects Version/s: 1.1.5, 1.2.1
Fix Version/s: 1.1.7, 1.2.3

Type: Bug Priority: Critical
Reporter: Muhammad Irfan Assignee: Daniel Lee (Inactive)
Resolution: Fixed Votes: 1
Labels: None

Issue Links:
Relates
relates to MCOL-2146 group_concat with distinct concatenat... Closed
relates to MCOL-3804 CLONE - group_concat with distinct co... Closed
Sprint: 2018-21, 2019-01

 Description   

1. simple table created. 4 rows of data is entered.

CREATE TABLE `football_teams` (
`num` int(11) DEFAULT NULL,
`name` varchar(32) DEFAULT NULL,
`roster_size` int(11) DEFAULT NULL
) ENGINE=Columnstore;

insert into football_teams VALUES (1,'Green Bay',53);
insert into football_teams VALUES (2,'Chicago',53);
insert into football_teams VALUES (3,'Detroit',53);
insert into football_teams VALUES (4,'Minnesota',53);

MariaDB [test]> select * from football_teams;
--------------------------

num name roster_size

--------------------------

1 Green Bay 53
2 Chicago 53
3 Detroit 53
4 Minnesota 53

--------------------------

2. A query that orders the entries by a column and limits the return. This output is as expected.

MariaDB [test]> select distinct num from football_teams order by num limit 3;
------

num

------

1
2
3

------

3. subquery result set is not equivalent with result in step-2

MariaDB [test]> select * from (select distinct num from football_teams order by num limit 3) a;
------

num

------

3

------

 Comments   
Comment by patrice [ 2018-11-30 ]

this bug is still present in 1.2.1

Comment by David Thompson (Inactive) [ 2018-12-20 ]

and confirmed still in 1.2.2, the presence of distinct is a necessary condition, removing this query works as expected.

Comment by Roman [ 2018-12-26 ]

Please review the change.

Comment by Andrew Hutchings (Inactive) [ 2019-01-07 ]

Approved and merged into 1.2, can you please backport to 1.1?

Comment by Roman [ 2019-01-15 ]

Please review the change.

Comment by Daniel Lee (Inactive) [ 2019-01-21 ]

Build verified:

1.1.7-1

server commit:
b5a7a22
engine commit:
d87b9a6

1.2.3-1

server commit:
61f32f2
engine commit:
83b2d4c

Please note that although these is an order-by clause in the sub-query, the final result may or may not be sorted. This is a known behavior. If sorted final result is desired, an order-by clause in the outer most query is required.

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