Unexpected result in a distributed query #7666

SaltTan · 2019-11-06T21:55:50Z

I installed 19.16.2 on one node of the cluster that runs 19.13.7, and I observe unexpected behaviour when I try to run a distributed query across the cluster:

Connected to ClickHouse server version 19.16.2 revision 54427.

select version(), timezone(), uptime() from cluster(segmented,system,one)

┌─version()─┬─timezone()─┬─uptime()─┐
│ 19.16.2.2 │ Etc/UTC    │    24322 │
│ 19.16.2.2 │ Etc/UTC    │    24322 │
│ 19.16.2.2 │ Etc/UTC    │    24322 │
│ 19.16.2.2 │ Etc/UTC    │    24322 │
│ 19.16.2.2 │ Etc/UTC    │    24322 │
│ 19.16.2.2 │ Etc/UTC    │    24322 │
└───────────┴────────────┴──────────┘

Connected to ClickHouse server version 19.13.7 revision 54425.

select version(), timezone(), uptime(), dummy from cluster(segmented,system,one)

┌─version()──┬─timezone()─┬─uptime()─┬─dummy─┐
│ 19.13.7.57 │ Etc/UTC    │    28025 │     0 │
│ 19.13.7.57 │ Etc/UTC    │    28231 │     0 │
│ 19.13.7.57 │ Etc/UTC    │    28204 │     0 │
│ 19.13.7.57 │ Etc/UTC    │    28215 │     0 │
│ 19.13.7.57 │ Etc/UTC    │    28195 │     0 │
│ 19.16.2.2  │ Etc/UTC    │    24321 │     0 │
└────────────┴────────────┴──────────┴───────┘

Sometimes when I run this query on 19.16.2 I get an exception:

2019.11.06 16:50:44.050025 [ 92 ] {b622279b-5799-4313-bfb5-ed5f3af698de} <Error> executeQuery: Code: 171, e.displayText() = DB::Exception: Cannot convert column `uptime()` because it is constant but values of constants are different in source and result (version 19.16.2.2 (official build)) (from [::1]:35134) (in query: select version(), timezone(), uptime() from cluster(segmented,system,one)), Stack trace:

0. 0x55588af4c7b0 StackTrace::StackTrace() /usr/bin/clickhouse
1. 0x55588af4c585 DB::Exception::Exception(std::__cxx11::basic_string<char, std::char_traits<char>, std::allocator<char> > const&, int) /usr/bin/clickhouse
2. 0x55588ad49828 ? /usr/bin/clickhouse
3. 0x55588e2b9d44 DB::InterpreterSelectQuery::unifyStreams(DB::InterpreterSelectQuery::Pipeline&, DB::Block) /usr/bin/clickhouse
4. 0x55588e2c68d6 DB::InterpreterSelectQuery::executeWithMultipleStreams() /usr/bin/clickhouse
5. 0x55588e2e3b73 DB::InterpreterSelectWithUnionQuery::executeWithMultipleStreams() /usr/bin/clickhouse
6. 0x55588e2e5a58 DB::InterpreterSelectWithUnionQuery::execute() /usr/bin/clickhouse
7. 0x55588e3f1586 ? /usr/bin/clickhouse
8. 0x55588e3f274e DB::executeQuery(std::__cxx11::basic_string<char, std::char_traits<char>, std::allocator<char> > const&, DB::Context&, bool, DB::QueryProcessingStage::Enum, bool, bool) /usr/bin/clickhouse
9. 0x55588afd4964 DB::TCPHandler::runImpl() /usr/bin/clickhouse
10. 0x55588afd539b DB::TCPHandler::run() /usr/bin/clickhouse
11. 0x55588ee6dfe0 Poco::Net::TCPServerConnection::start() /usr/bin/clickhouse
12. 0x55588ee6e6fd Poco::Net::TCPServerDispatcher::run() /usr/bin/clickhouse
13. 0x5558905448d1 Poco::PooledThread::run() /usr/bin/clickhouse
14. 0x55589054267c Poco::ThreadImpl::runnableEntry(void*) /usr/bin/clickhouse
15. 0x555890cb41e0 ? /usr/bin/clickhouse
16. 0x7f10171cc4a4 start_thread /lib/x86_64-linux-gnu/libpthread-2.24.so
17. 0x7f1016b02d0f clone /lib/x86_64-linux-gnu/libc-2.24.so

@amosbird @KochetovNicolai could you please help with investigation?

The text was updated successfully, but these errors were encountered:

amosbird · 2019-11-07T00:33:25Z

It seems some scalar function is not suitable for scalar optimization. Try setting enable_scalar_subquery_optimization to false for the moment.

SaltTan · 2019-11-07T00:50:06Z

It seems some scalar function is not suitable for scalar optimization. Try setting enable_scalar_subquery_optimization to false for the moment.

There is no such setting in 19.16.2

SaltTan · 2019-11-14T17:53:02Z

I tried 19.17.2.4 and got an exception for both version() and uptime():

DB::Exception: Block structure mismatch in MergingSorted stream: different values of constants, actual: '19.13.7.57', expected: '19.17.2.4'

DB::Exception: Block structure mismatch in MergingSorted stream: different values of constants, actual: 705665, expected: 424

2019.11.14 17:47:27.664726 [ 84 ] {19d373a7-2d4c-4c51-9783-58870d6068d4} <Error> HTTPHandler: Code: 171, e.displayText() = DB::Exception: Block structure mismatch in MergingSorted stream: different values of constants, actual: '19.13.7.57', expected: '19.17.2.4', Stack trace:

0. 0x5633aae35c40 StackTrace::StackTrace() /usr/bin/clickhouse
1. 0x5633aae35a15 DB::Exception::Exception(std::__cxx11::basic_string<char, std::char_traits<char>, std::allocator<char> > const&, int) /usr/bin/clickhouse
2. 0x5633aab2486a ? /usr/bin/clickhouse
3. 0x5633aab2548c ? /usr/bin/clickhouse
4. 0x5633ae7fbeff DB::MergingSortedBlockInputStream::init(std::vector<COW<DB::IColumn>::mutable_ptr<DB::IColumn>, std::allocator<COW<DB::IColumn>::mutable_ptr<DB::IColumn> > >&) /usr/bin/clickhouse
5. 0x5633ae7fd78f DB::MergingSortedBlockInputStream::readImpl() /usr/bin/clickhouse
6. 0x5633ae08bbda DB::IBlockInputStream::read() /usr/bin/clickhouse
7. 0x5633ae7d1a6b DB::ExpressionBlockInputStream::readImpl() /usr/bin/clickhouse
8. 0x5633ae08bbda DB::IBlockInputStream::read() /usr/bin/clickhouse
9. 0x5633ae0aa0cb DB::copyData(DB::IBlockInputStream&, DB::IBlockOutputStream&, std::atomic<bool>*) /usr/bin/clickhouse
10. 0x5633ae31df97 DB::executeQuery(DB::ReadBuffer&, DB::WriteBuffer&, bool, DB::Context&, std::function<void (std::__cxx11::basic_string<char, std::char_traits<char>, std::allocator<char> > const&)>, std::function<void (std::__cxx11::basic_string<char, std::char_traits<char>, std::allocator<char> > const&)>) /usr/bin/clickhouse
11. 0x5633aaeca12e DB::HTTPHandler::processQuery(Poco::Net::HTTPServerRequest&, HTMLForm&, Poco::Net::HTTPServerResponse&, DB::HTTPHandler::Output&) /usr/bin/clickhouse
12. 0x5633aaecc4a9 DB::HTTPHandler::handleRequest(Poco::Net::HTTPServerRequest&, Poco::Net::HTTPServerResponse&) /usr/bin/clickhouse
13. 0x5633aedbb219 Poco::Net::HTTPServerConnection::run() /usr/bin/clickhouse
14. 0x5633aedb7790 Poco::Net::TCPServerConnection::start() /usr/bin/clickhouse
15. 0x5633aedb7ead Poco::Net::TCPServerDispatcher::run() /usr/bin/clickhouse
16. 0x5633b048e021 Poco::PooledThread::run() /usr/bin/clickhouse
17. 0x5633b048bdcc Poco::ThreadImpl::runnableEntry(void*) /usr/bin/clickhouse
18. 0x5633b0c01f40 ? /usr/bin/clickhouse
19. 0x7ff6d6f1a4a4 start_thread /lib/x86_64-linux-gnu/libpthread-2.24.so
20. 0x7ff6d6850d0f clone /lib/x86_64-linux-gnu/libc-2.24.so
 (version 19.17.2.4 (official build))

KochetovNicolai · 2019-11-15T09:08:51Z

Suppose I need to continue fixing it.
@alexey-milovidov maybe the correct way to solve it is to expect that version() and uptime() are not constant even in scope of query?

SaltTan · 2019-12-20T17:12:41Z

I have just realized that the exception only happens when I sort the data:

SELECT
    version(),
    uptime()
FROM cluster(segmented, system, one)

┌─version()──┬─uptime()─┐
│ 19.16.6.17 │  1383195 │
│ 19.13.7.57 │  3813312 │
│ 19.13.7.57 │  1744510 │
│ 19.13.7.57 │  3813483 │
│ 19.13.7.57 │  1742709 │
│ 19.16.9.37 │     5344 │
└────────────┴──────────┘

6 rows in set. Elapsed: 0.403 sec.

:) select version(), uptime() from cluster(segmented,system,one) order by rand()

SELECT
    version(),
    uptime()
FROM cluster(segmented, system, one)
ORDER BY rand() ASC

↗ Progress: 5.00 rows, 5.00 B (46.34 rows/s., 46.34 B/s.) Received exception from server (version 19.16.6):
Code: 171. DB::Exception: Received from localhost:9000. DB::Exception: Block structure mismatch in MergingSorted stream: different values of constants, actual: '19.13.7.57', expected: '19.16.6.17'.

The same happens with dictGet called with a constant argument:

:) select dictGetString('node', 'name', toUInt64(1)) from cluster(segmented,system,one) order by rand()

SELECT dictGetString('node', 'name', toUInt64(1))
FROM cluster(segmented, system, one)
ORDER BY rand() ASC

↘ Progress: 5.00 rows, 5.00 B (46.10 rows/s., 46.10 B/s.) Received exception from server (version 19.16.6):
Code: 171. DB::Exception: Received from localhost:9000. DB::Exception: Block structure mismatch in MergingSorted stream: different values of constants, actual: 's794', expected: 's797'.

vapongit · 2020-01-03T13:21:50Z

some more examples (19.17.6.36).

Correct

SELECT 
    version() AS v, 
    hostName() AS h, 
    uptime() AS u
FROM cluster(cluster, system, one)

┌─v──────────┬─h────────────┬──────u─┐
│ 19.17.6.36 │ clickhouse05 │ 609636 │
└────────────┴──────────────┴────────┘
┌─v──────────┬─h────────────┬──────u─┐
│ 19.17.6.36 │ clickhouse04 │ 610319 │
└────────────┴──────────────┴────────┘
┌─v──────────┬─h────────────┬──────u─┐
│ 19.17.6.36 │ clickhouse06 │ 610290 │
└────────────┴──────────────┴────────┘

Add sorting. Incorrect

SELECT 
    version() AS v, 
    hostName() AS h, 
    uptime() AS u
FROM cluster(cluster, system, one)
ORDER BY h ASC

Code: 171. DB::Exception: Received from localhost:9000. DB::Exception: Block structure mismatch in MergingSorted stream: different values of constants, actual: 610324, expected: 609641.

Add GROUP BY. Incorrect

SELECT 
    version() AS v, 
    hostName() AS h, 
    uptime() AS u
FROM cluster(cluster, system, one)
GROUP BY h

┌─v──────────┬─h────────────┬──────u─┐
│ 19.17.6.36 │ clickhouse04 │ 609981 │
│ 19.17.6.36 │ clickhouse06 │ 609981 │
│ 19.17.6.36 │ clickhouse05 │ 609981 │
└────────────┴──────────────┴────────┘

NOTE: incorrect result and there are no errors 'Column X is not under aggregate function and not in GROUP BY' for columns v and u

Using COUNT() and system.one. Correct

SELECT 
   hostName() AS h, 
   count()
FROM cluster(cluster, system, one)
GROUP BY h

┌─h────────────┬─count()─┐
│ clickhouse04 │       1 │
│ clickhouse06 │       1 │
│ clickhouse05 │       1 │
└──────────────┴─────────┘

Using COUNT() and non-sytem table. Incorrect

SELECT 
    hostName() AS h, 
    count()
FROM cluster(cluster, default, raw_events_log_shard)
GROUP BY h

Code: 8. DB::Exception: Received from localhost:9000. DB::Exception: Cannot find column `hostName()` in source stream.

Replace COUNT() with SUM(1). Correct

SELECT 
    hostName() AS h, 
    sum(1)
FROM cluster(cluster, default, raw_events_log_shard)
GROUP BY h

┌─h────────────┬──────sum(1)─┐
│ clickhouse04 │ 20740513053 │
│ clickhouse06 │ 20732537512 │
│ clickhouse05 │ 20764740906 │
└──────────────┴─────────────┘

uptime() + COUNT()+ GROUP BY. Incorrect for system and non-sytem tables
a) system table

SELECT 
    uptime() AS u, 
    count()
FROM cluster(cluster, system, one)
GROUP BY u

┌──────u─┬─count()─┐
│ 611037 │       3 │
└────────┴─────────┘

b) non-system table

SELECT 
    uptime() AS u, 
    count()
FROM cluster(cluster, default, raw_events_log_shard)
GROUP BY u

┌──────u─┬─────count()─┐
│ 611288 │ 62243852157 │
└────────┴─────────────┘

NOTE: correct result for count() but incorrect for grouping column

Add materialize to uptime() + COUNT()+ GROUP BY. Correct for system table, incorrect for non-system table
a) system table

SELECT 
    materialize(uptime()) AS u, 
    count()
FROM cluster(cluster, system, one)
GROUP BY u

┌──────u─┬─count()─┐
│ 611706 │       1 │
│ 611052 │       1 │
│ 611735 │       1 │
└────────┴─────────┘

b) non-system table

SELECT 
    materialize(uptime()) AS u, 
    count()
FROM cluster(cluster, default, raw_events_log_shard)
GROUP BY u

Code: 8. DB::Exception: Received from localhost:9000. DB::Exception: Cannot find column `materialize(uptime())` in source stream.

SaltTan · 2020-08-06T19:03:52Z

I cannot reproduce the sorting issue anymore (DB::Exception: Block structure mismatch in MergingSorted stream: different values of constants).
I tried 20.3.11, 20.3.13, 20.4.7, 20.5.2, 20.5.4

SaltTan · 2020-08-06T19:15:10Z

The grouping issue:

SELECT
    version() AS h,
    count()
FROM remote('localhost,remotehost', system, one)
GROUP BY h

┌─h─────────┬─count()─┐
│ 20.5.4.40 │       2 │
└───────────┴─────────┘


SELECT
    materialize(version()) AS h,
    count()
FROM remote('localhost,remotehost', system, one)
GROUP BY h

┌─h─────────┬─count()─┐
│ 20.5.2.7  │       1 │
│ 20.5.4.40 │       1 │
└───────────┴─────────┘

KochetovNicolai · 2020-08-06T19:19:21Z

@alexey-milovidov the problem is the same as I showed for randConstant and now64.
Maybe just remove isDeterministicInScopeOfQuery for it?

alexey-milovidov · 2020-08-06T22:17:49Z

@KochetovNicolai Ok, let's do it.

I see that the isDeterministicInScopeOfQuery property is only used for JIT and RedundantFunctionsInOrderByVisitor.

SaltTan · 2020-10-15T21:18:35Z

The grouping issue is still relevant in 20.9.3.45

azat · 2020-11-19T18:27:10Z

Looks like has been fixed in 983aaa3/93e4802fa4919894 (#16618, 20.12+)

@alexey-milovidov the problem is the same as I showed for randConstant and now64.
Maybe just remove isDeterministicInScopeOfQuery for it?

@KochetovNicolai but isn't it isSuitableForConstantFolding?

KochetovNicolai · 2020-11-20T09:26:26Z

but isn't it isSuitableForConstantFolding?

I think it is not suitable. Because otherwise we could replace this function calculation by constant (it has not arguments and return constant result). It is hard to detect if we have some other node which returns different version result.

azat · 2020-11-20T09:33:02Z

I think it is not suitable

I'm talking about that, that query, with GROUP BY version(), does not work because version returns true for isSuitableForConstantFolding before 93e4802.

In other words isSuitableForConstantFolding returning true looks like a fix for the problem.

SaltTan · 2021-05-10T13:57:34Z

This is broken again in 21.3

select version(), uptime() from cluster(segmented,system,one)

version() | uptime()
----------+---------
21.4.3.21 |   344985
20.9.7.11 |   344985
21.4.3.21 |   344985
20.9.7.11 |   344985
21.4.3.21 |   344985

alexey-milovidov · 2021-06-14T18:35:25Z

Workaround:
SELECT any(version()), any(uptime()) FROM cluster(segmented, system, one) GROUP BY hostName()

alexey-milovidov · 2021-06-14T18:37:05Z

The place where the expression in SQL query is executed is not specified for distributed queries.
That's why I remove the "bug" label.

SaltTan added the bug Confirmed user-visible misbehaviour in official release label Nov 6, 2019

KochetovNicolai mentioned this issue Nov 8, 2019

Fix remote constants. #7689

Merged

filimonov added the v19.16 label Jan 15, 2020

vapongit mentioned this issue Feb 3, 2020

WITH FILL against const columns #8754

Closed

filimonov added v19.17 labels Feb 13, 2020

filimonov added v20.3-affected labels Jul 6, 2020

filimonov mentioned this issue Jul 6, 2020

Distributed with group by hostName - count() issue #9899

Closed

SaltTan changed the title ~~19.16 Unexpected result or exception in a distributed query~~ Unexpected result or exception in a distributed query Oct 28, 2020

den-crane mentioned this issue Dec 9, 2020

filesystem* functions produces wrong result in case of distributed queries. #17937

Closed

alexey-milovidov changed the title ~~Unexpected result or exception in a distributed query~~ Unexpected result in a distributed query May 10, 2021

den-crane added v21.3-affected labels May 10, 2021

den-crane added the v21.5-affected label May 10, 2021

alexey-milovidov closed this as completed Jun 14, 2021

alexey-milovidov added st-wontfix Known issue, no plans to fix it currenlty and removed bug Confirmed user-visible misbehaviour in official release labels Jun 14, 2021

den-crane mentioned this issue Sep 9, 2023

Wrong results are generated when using cluster, view, and with #54447

Open

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

Unexpected result in a distributed query #7666

Unexpected result in a distributed query #7666

SaltTan commented Nov 6, 2019

amosbird commented Nov 7, 2019

SaltTan commented Nov 7, 2019

SaltTan commented Nov 14, 2019

KochetovNicolai commented Nov 15, 2019

SaltTan commented Dec 20, 2019

vapongit commented Jan 3, 2020

SaltTan commented Aug 6, 2020

SaltTan commented Aug 6, 2020

KochetovNicolai commented Aug 6, 2020

alexey-milovidov commented Aug 6, 2020

SaltTan commented Oct 15, 2020

azat commented Nov 19, 2020

KochetovNicolai commented Nov 20, 2020

azat commented Nov 20, 2020

SaltTan commented May 10, 2021

alexey-milovidov commented Jun 14, 2021

alexey-milovidov commented Jun 14, 2021

Unexpected result in a distributed query #7666

Unexpected result in a distributed query #7666

Comments

SaltTan commented Nov 6, 2019

amosbird commented Nov 7, 2019

SaltTan commented Nov 7, 2019

SaltTan commented Nov 14, 2019

KochetovNicolai commented Nov 15, 2019

SaltTan commented Dec 20, 2019

vapongit commented Jan 3, 2020

SaltTan commented Aug 6, 2020

SaltTan commented Aug 6, 2020

KochetovNicolai commented Aug 6, 2020

alexey-milovidov commented Aug 6, 2020

SaltTan commented Oct 15, 2020

azat commented Nov 19, 2020

KochetovNicolai commented Nov 20, 2020

azat commented Nov 20, 2020

SaltTan commented May 10, 2021

alexey-milovidov commented Jun 14, 2021

alexey-milovidov commented Jun 14, 2021