Skip to content

DB::Exception: Cannot get JOIN keys from JOIN ON section #22399

Closed
#22753
Closed
DB::Exception: Cannot get JOIN keys from JOIN ON section#22399
#22753
Assignees
vdimir
Labels
bugConfirmed user-visible misbehaviour in official releasecomp-joinsJOINs end-to-end (planning hooks + runtime join operators/algorithms). Single bucket to avoid pla...
@Zhile

Description

@Zhile
Zhile
opened on Mar 31, 2021

(you don't have to strictly follow this form)

Describe the bug
When join key is array join alias, there will be exception thrown.

Does it reproduce on recent release?
Reproduced in 21.3.4.25 easily

How to reproduce

CREATE TABLE d
(
    `id` Int64,
    `name` String
)
ENGINE = MergeTree
ORDER BY id

CREATE TABLE f
(
    `id` Int64,
    `d_ids` Array(Int64),
    `m1` Int64,
    `m2` Int64
)
ENGINE = MergeTree
ORDER BY id

Select SQL and error:

SELECT
    id,
    name,
    sum(m1)
FROM f
LEFT ARRAY JOIN d_ids AS d_array_id
LEFT JOIN d ON d.id = d_array_id
GROUP BY
    id,
    name

Received exception from server (version 21.3.4):
Code: 403. DB::Exception: Received from localhost:9000. DB::Exception: Cannot get JOIN keys from JOIN ON section: d.id = d_array_id.

Expected behavior
This query can be executed successfully in 21.2.7.11

Error message and/or stacktrace

2021.03.31 04:35:47.463228 [ 53 ] {82709d0e-a3d6-4d03-afab-4320fea13c3b} <Error> executeQuery: Code: 403, e.displayText() = DB::Exception: Cannot get JOIN keys from JOIN ON section: d.id = d_array_id (version 21.3.4.25 (official build)) (from 127.0.0.1:36904) (in query: select id, name, sum(m1) from f left array join d_ids as d_array_id left join d on d.id = d_array_id group by id, name;), Stack trace (when copying this message, always include the lines below):

0. DB::TreeRewriter::analyzeSelect(std::__1::shared_ptr<DB::IAST>&, DB::TreeRewriterResult&&, DB::SelectQueryOptions const&, std::__1::vector<DB::TableWithColumnNamesAndTypes, std::__1::allocator<DB::TableWithColumnNamesAndTypes> > const&, 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&, std::__1::shared_ptr<DB::TableJoin>) const @ 0xf0d6ba8 in /usr/bin/clickhouse
1. ? @ 0xec78d3e in /usr/bin/clickhouse
2. DB::InterpreterSelectQuery::InterpreterSelectQuery(std::__1::shared_ptr<DB::IAST> const&, DB::Context const&, std::__1::shared_ptr<DB::IBlockInputStream> const&, std::__1::optional<DB::Pipe>, std::__1::shared_ptr<DB::IStorage> const&, DB::SelectQueryOptions const&, 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&, std::__1::shared_ptr<DB::StorageInMemoryMetadata const> const&) @ 0xec755aa in /usr/bin/clickhouse
3. DB::InterpreterSelectQuery::InterpreterSelectQuery(std::__1::shared_ptr<DB::IAST> const&, DB::Context const&, DB::SelectQueryOptions const&, 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&) @ 0xec73d8d in /usr/bin/clickhouse
4. DB::InterpreterSelectWithUnionQuery::buildCurrentChildInterpreter(std::__1::shared_ptr<DB::IAST> const&, 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&) @ 0xef96755 in /usr/bin/clickhouse
5. DB::InterpreterSelectWithUnionQuery::InterpreterSelectWithUnionQuery(std::__1::shared_ptr<DB::IAST> const&, DB::Context const&, DB::SelectQueryOptions const&, 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&) @ 0xef95050 in /usr/bin/clickhouse
6. DB::InterpreterFactory::get(std::__1::shared_ptr<DB::IAST>&, DB::Context&, DB::SelectQueryOptions const&) @ 0xec2abb0 in /usr/bin/clickhouse
7. ? @ 0xf132e89 in /usr/bin/clickhouse
8. DB::executeQuery(std::__1::basic_string<char, std::__1::char_traits<char>, std::__1::allocator<char> > const&, DB::Context&, bool, DB::QueryProcessingStage::Enum, bool) @ 0xf131a63 in /usr/bin/clickhouse
9. DB::TCPHandler::runImpl() @ 0xf8befbd in /usr/bin/clickhouse
10. DB::TCPHandler::run() @ 0xf8d1529 in /usr/bin/clickhouse
11. Poco::Net::TCPServerConnection::start() @ 0x11f84caf in /usr/bin/clickhouse
12. Poco::Net::TCPServerDispatcher::run() @ 0x11f866c1 in /usr/bin/clickhouse
13. Poco::PooledThread::run() @ 0x120bcde9 in /usr/bin/clickhouse
14. Poco::ThreadImpl::runnableEntry(void*) @ 0x120b8c4a in /usr/bin/clickhouse
15. start_thread @ 0x9609 in /usr/lib/x86_64-linux-gnu/libpthread-2.31.so
16. __clone @ 0x122293 in /usr/lib/x86_64-linux-gnu/libc-2.31.so

2021.03.31 04:35:47.463825 [ 53 ] {82709d0e-a3d6-4d03-afab-4320fea13c3b} <Error> TCPHandler: Code: 403, e.displayText() = DB::Exception: Cannot get JOIN keys from JOIN ON section: d.id = d_array_id, Stack trace:

0. DB::TreeRewriter::analyzeSelect(std::__1::shared_ptr<DB::IAST>&, DB::TreeRewriterResult&&, DB::SelectQueryOptions const&, std::__1::vector<DB::TableWithColumnNamesAndTypes, std::__1::allocator<DB::TableWithColumnNamesAndTypes> > const&, 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&, std::__1::shared_ptr<DB::TableJoin>) const @ 0xf0d6ba8 in /usr/bin/clickhouse
1. ? @ 0xec78d3e in /usr/bin/clickhouse
2. DB::InterpreterSelectQuery::InterpreterSelectQuery(std::__1::shared_ptr<DB::IAST> const&, DB::Context const&, std::__1::shared_ptr<DB::IBlockInputStream> const&, std::__1::optional<DB::Pipe>, std::__1::shared_ptr<DB::IStorage> const&, DB::SelectQueryOptions const&, 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&, std::__1::shared_ptr<DB::StorageInMemoryMetadata const> const&) @ 0xec755aa in /usr/bin/clickhouse
3. DB::InterpreterSelectQuery::InterpreterSelectQuery(std::__1::shared_ptr<DB::IAST> const&, DB::Context const&, DB::SelectQueryOptions const&, 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&) @ 0xec73d8d in /usr/bin/clickhouse
4. DB::InterpreterSelectWithUnionQuery::buildCurrentChildInterpreter(std::__1::shared_ptr<DB::IAST> const&, 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&) @ 0xef96755 in /usr/bin/clickhouse
5. DB::InterpreterSelectWithUnionQuery::InterpreterSelectWithUnionQuery(std::__1::shared_ptr<DB::IAST> const&, DB::Context const&, DB::SelectQueryOptions const&, 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&) @ 0xef95050 in /usr/bin/clickhouse
6. DB::InterpreterFactory::get(std::__1::shared_ptr<DB::IAST>&, DB::Context&, DB::SelectQueryOptions const&) @ 0xec2abb0 in /usr/bin/clickhouse
7. ? @ 0xf132e89 in /usr/bin/clickhouse
8. DB::executeQuery(std::__1::basic_string<char, std::__1::char_traits<char>, std::__1::allocator<char> > const&, DB::Context&, bool, DB::QueryProcessingStage::Enum, bool) @ 0xf131a63 in /usr/bin/clickhouse
9. DB::TCPHandler::runImpl() @ 0xf8befbd in /usr/bin/clickhouse
10. DB::TCPHandler::run() @ 0xf8d1529 in /usr/bin/clickhouse
11. Poco::Net::TCPServerConnection::start() @ 0x11f84caf in /usr/bin/clickhouse
12. Poco::Net::TCPServerDispatcher::run() @ 0x11f866c1 in /usr/bin/clickhouse
13. Poco::PooledThread::run() @ 0x120bcde9 in /usr/bin/clickhouse
14. Poco::ThreadImpl::runnableEntry(void*) @ 0x120b8c4a in /usr/bin/clickhouse
15. start_thread @ 0x9609 in /usr/lib/x86_64-linux-gnu/libpthread-2.31.so
16. __clone @ 0x122293 in /usr/lib/x86_64-linux-gnu/libc-2.31.so

Metadata

Metadata

Assignees

Labels

bugConfirmed user-visible misbehaviour in official releasecomp-joinsJOINs end-to-end (planning hooks + runtime join operators/algorithms). Single bucket to avoid pla...

Type

No type

Projects

No projects

Milestone

No milestone

Relationships

None yet

Development

No branches or pull requests

Issue actions