Multiple ORDER BY expressions not returning correct order #13958
Description
Describe the bug
When using ORDER BY with multiple expressions on a MergeTree table, the ordering is not correct.
How to reproduce
yandex/clickhouse-server:20.5.2.7
Create the table:
create table order_test
(
timestamp DateTime64(3),
color LowCardinality(Nullable(String))
) engine = MergeTree()
PARTITION BY toYYYYMM(timestamp)
ORDER BY timestamp
SETTINGS index_granularity = 8192;Fill the table (run a few time times to get a range of timestamps):
insert into order_test
select now64(),
arrayElement(['red', 'green', 'blue', null], modulo(number, 4) + 1) as color
from (select number from system.numbers limit 1000000);Query:
SELECT count(),
color,
toStartOfSecond(timestamp) AS `second`
FROM order_test AS i
GROUP BY color, `second`
ORDER BY color, `second` desc
LIMIT 500;See out of order results:
500000,blue,2020-08-21 18:30:06.000
500000,blue,2020-08-21 18:30:09.000
250000,blue,2020-08-21 18:30:03.000
250000,blue,2020-08-21 18:30:08.000
250000,blue,2020-08-21 18:30:07.000
Expected behavior
See results in order by descending second:
500000,blue,2020-08-21 18:30:09.000
250000,blue,2020-08-21 18:30:08.000
250000,blue,2020-08-21 18:30:07.000
500000,blue,2020-08-21 18:30:06.000
250000,blue,2020-08-21 18:30:03.000
Notes:
It appears when I take out LowCardinality from the color column, the order works as expected.