Clickhouse: Output of TreeExecutor is not sorted after OPTIMIZE FINAL DEDUPLICATE

Created on 10 Jun 2020  ·  7Comments  ·  Source: ClickHouse/ClickHouse

Describe the bug
I got the exception after trying to DEDUPLICATE mv. It worked on 20.3

OPTIMIZE TABLE targeting_service_mv.ts_last_active_wallet FINAL DEDUPLICATE;

ru.yandex.clickhouse.except.ClickHouseException: ClickHouse exception, code: 365, host: 168.63.64.61, port: 8123; Code: 365, e.displayText() = DB::Exception: Output of TreeExecutor is not sorted (version 20.4.4.18 (official build))

It works w/o DEDUPLICATE. And does what it intended to do, delete old records with earlier last_active

How to reproduce
Clickhouse-server 20.4.4.18

CREATE TABLE queues.client_event (
    `event` LowCardinality(String),
    `created_at` DateTime, 
    `wallet_uid` String,
    `device_id` String,    
    `message_id` String,
)
ENGINE = ReplacingMergeTree() PARTITION BY toYYYYMM(created_at)
ORDER BY (event, created_at, cityHash64(device_id, message_id))
SETTINGS index_granularity = 8192

CREATE MATERIALIZED VIEW targeting_service_mv.ts_last_active_wallet
ENGINE = ReplacingMergeTree(last_active)
ORDER BY (wallet_uid)
POPULATE
AS 
SELECT
    wallet_uid, toDate(created_at) as last_active
FROM queues.client_event
WHERE 
event IN ('MW: Home', 'LoyaltyCard: Home', 'MW: Launch', 'MW: First-Launch', 'MW: Loading: Shown')
and wallet_uid != '';

After creation I have inserted 12mln records in butches of 50k. Like that

insert into targeting_service_mv.ts_last_active_wallet (wallet_uid, last_active) values (....)

Expected behavior
OPTIMIZE executes w/o exceptions, table re merges (records with same wallet_uid but earlier last_active was deleted)

Error message and/or stacktrace

ru.yandex.clickhouse.except.ClickHouseException: ClickHouse exception, code: 365, host: 168.63.64.61, port: 8123; Code: 365, e.displayText() = DB::Exception: Output of TreeExecutor is not sorted (version 20.4.4.18 (official build))

    at ru.yandex.clickhouse.except.ClickHouseExceptionSpecifier.specify(ClickHouseExceptionSpecifier.java:58)
    at ru.yandex.clickhouse.except.ClickHouseExceptionSpecifier.specify(ClickHouseExceptionSpecifier.java:28)
    at ru.yandex.clickhouse.ClickHouseStatementImpl.checkForErrorAndThrow(ClickHouseStatementImpl.java:875)
    at ru.yandex.clickhouse.ClickHouseStatementImpl.getInputStream(ClickHouseStatementImpl.java:616)
    at ru.yandex.clickhouse.ClickHouseStatementImpl.executeQuery(ClickHouseStatementImpl.java:117)
    at ru.yandex.clickhouse.ClickHouseStatementImpl.executeQuery(ClickHouseStatementImpl.java:100)
    at ru.yandex.clickhouse.ClickHouseStatementImpl.executeQuery(ClickHouseStatementImpl.java:95)
    at ru.yandex.clickhouse.ClickHouseStatementImpl.executeQuery(ClickHouseStatementImpl.java:90)
    at ru.yandex.clickhouse.ClickHouseStatementImpl.execute(ClickHouseStatementImpl.java:226)
    at org.apache.commons.dbcp2.DelegatingStatement.execute(DelegatingStatement.java:291)
    at org.apache.commons.dbcp2.DelegatingStatement.execute(DelegatingStatement.java:291)
    at org.apache.zeppelin.jdbc.JDBCInterpreter.executeSql(JDBCInterpreter.java:736)
    at org.apache.zeppelin.jdbc.JDBCInterpreter.interpret(JDBCInterpreter.java:819)
    at org.apache.zeppelin.interpreter.LazyOpenInterpreter.interpret(LazyOpenInterpreter.java:103)
    at org.apache.zeppelin.interpreter.remote.RemoteInterpreterServer$InterpretJob.jobRun(RemoteInterpreterServer.java:632)
    at org.apache.zeppelin.scheduler.Job.run(Job.java:188)
    at org.apache.zeppelin.scheduler.ParallelScheduler$JobRunner.run(ParallelScheduler.java:162)
    at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:511)
    at java.util.concurrent.FutureTask.run(FutureTask.java:266)
    at java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.access$201(ScheduledThreadPoolExecutor.java:180)
    at java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.run(ScheduledThreadPoolExecutor.java:293)
    at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149)
    at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624)
    at java.lang.Thread.run(Thread.java:748)
Caused by: java.lang.Throwable: Code: 365, e.displayText() = DB::Exception: Output of TreeExecutor is not sorted (version 20.4.4.18 (official build))

    at ru.yandex.clickhouse.except.ClickHouseExceptionSpecifier.specify(ClickHouseExceptionSpecifier.java:53)
    ... 23 more
bug v20.4-affected
Source
picture alarin
👍3

Most helpful comment

I'm having the same issue

picture johnpyp on 20 Jun 2020
👍4

All 7 comments

Looks like to have the same reason as #10389

alexey-milovidov picture alexey-milovidov on 10 Jun 2020

I'm having the same issue

johnpyp picture johnpyp on 20 Jun 2020
👍4

Looks like I get this on 20.5.2 as well:

:) CREATE TABLE foo
 (
    timestamp DateTime CODEC(DoubleDelta, LZ4),
    val UInt64
 )
 ENGINE = SummingMergeTree()
 PARTITION BY toYYYYMM(timestamp)
 ORDER BY (timestamp)

:) INSERT INTO foo VALUES ('2020-01-01 00:00:00', 1)
:) INSERT INTO foo VALUES ('2020-01-01 01:00:00', 2) 
:) INSERT INTO foo VALUES ('2020-01-01 00:00:00', 3)
:) SELECT * FROM foo

SELECT *
FROM default.foo

┌───────────timestamp─┬─val─┐
│ 2020-01-01 00:00:00 │   3 │
└─────────────────────┴─────┘
┌───────────timestamp─┬─val─┐
│ 2020-01-01 00:00:00 │   1 │
└─────────────────────┴─────┘
┌───────────timestamp─┬─val─┐
│ 2020-01-01 01:00:00 │   2 │
└─────────────────────┴─────┘

3 rows in set. Elapsed: 0.003 sec. 

:) OPTIMIZE TABLE default.foo DEDUPLICATE

OPTIMIZE TABLE default.foo DEDUPLICATE


Received exception from server (version 20.5.2):
Code: 365. DB::Exception: Received from localhost:9000. DB::Exception: Output of TreeExecutor is not sorted. 

0 rows in set. Elapsed: 0.001 sec.

The trace of which is (looks like the original issue's trace is for JDBC):

hostname :) optimize table default.foo deduplicate

OPTIMIZE TABLE default.foo DEDUPLICATE

[hostname :)] 2020.07.07 10:18:35.665415 [ 6748 ] {712f0df5-d575-453c-beb5-efb4a543d40f} <Debug> executeQuery: (from 127.0.0.1:33642) optimize table default.foo deduplicate
[hostname :)] 2020.07.07 10:18:35.665503 [ 6748 ] {712f0df5-d575-453c-beb5-efb4a543d40f} <Trace> ContextAccess (default): Access granted: OPTIMIZE ON default.foo
[hostname :)] 2020.07.07 10:18:35.665534 [ 6748 ] {712f0df5-d575-453c-beb5-efb4a543d40f} <Debug> default.foo (MergerMutator): Selected 3 parts from 202001_1_1_0 to 202001_3_3_0
[hostname :)] 2020.07.07 10:18:35.665578 [ 6748 ] {712f0df5-d575-453c-beb5-efb4a543d40f} <Debug> DiskLocal: Reserving 1.00 MiB on disk `default`, having unreserved 405.47 GiB.
[hostname :)] 2020.07.07 10:18:35.665591 [ 6748 ] {712f0df5-d575-453c-beb5-efb4a543d40f} <Debug> default.foo (MergerMutator): Merging 3 parts: from 202001_1_1_0 to 202001_3_3_0 into Wide
[hostname :)] 2020.07.07 10:18:35.665607 [ 6748 ] {712f0df5-d575-453c-beb5-efb4a543d40f} <Debug> default.foo (MergerMutator): Selected MergeAlgorithm: Horizontal
[hostname :)] 2020.07.07 10:18:35.665621 [ 6748 ] {712f0df5-d575-453c-beb5-efb4a543d40f} <Trace> MergeTreeSequentialSource: Reading 2 marks from part 202001_1_1_0, total 1 rows starting from the beginning of the part
[hostname :)] 2020.07.07 10:18:35.665658 [ 6748 ] {712f0df5-d575-453c-beb5-efb4a543d40f} <Trace> MergeTreeSequentialSource: Reading 2 marks from part 202001_2_2_0, total 1 rows starting from the beginning of the part
[hostname :)] 2020.07.07 10:18:35.665682 [ 6748 ] {712f0df5-d575-453c-beb5-efb4a543d40f} <Trace> MergeTreeSequentialSource: Reading 2 marks from part 202001_3_3_0, total 1 rows starting from the beginning of the part
[hostname :)] 2020.07.07 10:18:35.666004 [ 6748 ] {712f0df5-d575-453c-beb5-efb4a543d40f} <Error> executeQuery: Code: 365, e.displayText() = DB::Exception: Output of TreeExecutor is not sorted (version 20.5.2.7 (official build)) (from 127.0.0.1:33642) (in query: optimize table default.foo deduplicate), Stack trace (when copying this message, always include the lines below):

0. Poco::Exception::Exception(std::__1::basic_string<char, std::__1::char_traits<char>, std::__1::allocator<char> > const&, int) @ 0x10ed0da0 in /usr/bin/clickhouse
1. DB::Exception::Exception(std::__1::basic_string<char, std::__1::char_traits<char>, std::__1::allocator<char> > const&, int) @ 0x95c923d in /usr/bin/clickhouse
2. DB::IBlockInputStream::getSortDescription() const @ 0xd99c5a9 in /usr/bin/clickhouse
3. DB::DistinctSortedBlockInputStream::DistinctSortedBlockInputStream(std::__1::shared_ptr<DB::IBlockInputStream> const&, DB::SizeLimits const&, unsigned long, std::__1::vector<std::__1::basic_string<char, std::__1::char_traits<char>, std::__1::allocator<char> >, std::__1::allocator<std::__1::basic_string<char, std::__1::char_traits<char>, std::__1::allocator<char> > > > const&) @ 0xe447d0b in /usr/bin/clickhouse
4. DB::MergeTreeDataMergerMutator::mergePartsToTemporaryPart(DB::FutureMergedMutatedPart const&, DB::MergeListEntry&, DB::TableStructureReadLockHolder&, long, std::__1::unique_ptr<DB::IReservation, std::__1::default_delete<DB::IReservation> > const&, bool, bool) @ 0xe42d70c in /usr/bin/clickhouse
5. DB::StorageMergeTree::merge(bool, std::__1::basic_string<char, std::__1::char_traits<char>, std::__1::allocator<char> > const&, bool, bool, std::__1::basic_string<char, std::__1::char_traits<char>, std::__1::allocator<char> >*) @ 0xe25356b in /usr/bin/clickhouse
6. DB::StorageMergeTree::optimize(std::__1::shared_ptr<DB::IAST> const&, std::__1::shared_ptr<DB::IAST> const&, bool, bool, DB::Context const&) @ 0xe2538e9 in /usr/bin/clickhouse
7. DB::InterpreterOptimizeQuery::execute() @ 0xdd8be5a in /usr/bin/clickhouse
8. ? @ 0xe074a59 in /usr/bin/clickhouse
9. DB::executeQuery(std::__1::basic_string<char, std::__1::char_traits<char>, std::__1::allocator<char> > const&, DB::Context&, bool, DB::QueryProcessingStage::Enum, bool) @ 0xe07811a in /usr/bin/clickhouse
10. DB::TCPHandler::runImpl() @ 0xe698946 in /usr/bin/clickhouse
11. DB::TCPHandler::run() @ 0xe699660 in /usr/bin/clickhouse
12. Poco::Net::TCPServerConnection::start() @ 0x10deebcb in /usr/bin/clickhouse
13. Poco::Net::TCPServerDispatcher::run() @ 0x10def05b in /usr/bin/clickhouse
14. Poco::PooledThread::run() @ 0x10f6db86 in /usr/bin/clickhouse
15. Poco::ThreadImpl::runnableEntry(void*) @ 0x10f68f80 in /usr/bin/clickhouse
16. start_thread @ 0x76db in /lib/x86_64-linux-gnu/libpthread-2.27.so
17. /build/glibc-2ORdQG/glibc-2.27/misc/../sysdeps/unix/sysv/linux/x86_64/clone.S:97: clone @ 0x121a3f in /usr/lib/debug/lib/x86_64-linux-gnu/libc-2.27.so


Received exception from server (version 20.5.2):
Code: 365. DB::Exception: Received from localhost:9000. DB::Exception: Output of TreeExecutor is not sorted. 

0 rows in set. Elapsed: 0.001 sec.
laingawbl picture laingawbl on 7 Jul 2020

@laingawbl OPTIMIZE DEDUPLICATE has sense only for simple MergeTree engine.
You should never use DEDUPLICATE against SummingMergeTree. It's the nonsense.

den-crane picture den-crane on 7 Jul 2020

@den-crane whoops, you're right. I misunderstood totally. Sorry!

laingawbl picture laingawbl on 7 Jul 2020

It should not give "Output of TreeExecutor is not sorted." exception nevertheless.

alexey-milovidov picture alexey-milovidov on 7 Jul 2020

Same exception on OPTIMIZE TABLE xxx DEDUPLICATE

2020.07.13 15:31:14.363796 [ 133 ] {76b755df-95c1-4771-820a-79bc0641c21d} <Error> executeQuery: Code: 365, e.displayText() = DB::Exception: Output of TreeExecutor is not sorted (version 20.5.2.7 (official build)) (from 127.0.0.1:41894) (in query: OPTIMIZE TABLE nessus.reportitems DEDUPLICATE;), Stack trace (when copying this message, always include the lines below):

0. Poco::Exception::Exception(std::__1::basic_string<char, std::__1::char_traits<char>, std::__1::allocator<char> > const&, int) @ 0x10ed0da0 in /usr/bin/clickhouse
1. DB::Exception::Exception(std::__1::basic_string<char, std::__1::char_traits<char>, std::__1::allocator<char> > const&, int) @ 0x95c923d in /usr/bin/clickhouse
2. DB::IBlockInputStream::getSortDescription() const @ 0xd99c5a9 in /usr/bin/clickhouse
3. DB::DistinctSortedBlockInputStream::DistinctSortedBlockInputStream(std::__1::shared_ptr<DB::IBlockInputStream> const&, DB::SizeLimits const&, unsigned long, std::__1::vector<std::__1::basic_string<char, std::__1::char_traits<char>, std::__1::allocator<char> >, std::__1::allocator<std::__1::basic_string<char, std::__1::char_traits<char>, std::__1::allocator<char> > > > const&) @ 0xe447d0b in /usr/bin/clickhouse
4. DB::MergeTreeDataMergerMutator::mergePartsToTemporaryPart(DB::FutureMergedMutatedPart const&, DB::MergeListEntry&, DB::TableStructureReadLockHolder&, long, std::__1::unique_ptr<DB::IReservation, std::__1::default_delete<DB::IReservation> > const&, bool, bool) @ 0xe42d70c in /usr/bin/clickhouse
5. DB::StorageMergeTree::merge(bool, std::__1::basic_string<char, std::__1::char_traits<char>, std::__1::allocator<char> > const&, bool, bool, std::__1::basic_string<char, std::__1::char_traits<char>, std::__1::allocator<char> >*) @ 0xe25356b in /usr/bin/clickhouse
6. DB::StorageMergeTree::optimize(std::__1::shared_ptr<DB::IAST> const&, std::__1::shared_ptr<DB::IAST> const&, bool, bool, DB::Context const&) @ 0xe2538e9 in /usr/bin/clickhouse
7. DB::InterpreterOptimizeQuery::execute() @ 0xdd8be5a in /usr/bin/clickhouse
8. ? @ 0xe074a59 in /usr/bin/clickhouse
9. DB::executeQuery(std::__1::basic_string<char, std::__1::char_traits<char>, std::__1::allocator<char> > const&, DB::Context&, bool, DB::QueryProcessingStage::Enum, bool) @ 0xe07811a in /usr/bin/clickhouse
10. DB::TCPHandler::runImpl() @ 0xe698946 in /usr/bin/clickhouse
11. DB::TCPHandler::run() @ 0xe699660 in /usr/bin/clickhouse
12. Poco::Net::TCPServerConnection::start() @ 0x10deebcb in /usr/bin/clickhouse
13. Poco::Net::TCPServerDispatcher::run() @ 0x10def05b in /usr/bin/clickhouse
14. Poco::PooledThread::run() @ 0x10f6db86 in /usr/bin/clickhouse
15. Poco::ThreadImpl::runnableEntry(void*) @ 0x10f68f80 in /usr/bin/clickhouse
16. start_thread @ 0x76db in /lib/x86_64-linux-gnu/libpthread-2.27.so
17. __clone @ 0x12188f in /lib/x86_64-linux-gnu/libc-2.27.so
ksverdlov picture ksverdlov on 13 Jul 2020
Was this page helpful?
0 / 5 - 0 ratings

Related issues

zhicwu picture zhicwu  ·  3Comments
atk91 picture atk91  ·  3Comments
healiseu picture healiseu  ·  3Comments
goranc picture goranc  ·  3Comments
innerr picture innerr  ·  3Comments