Skip to content

COMMA to CROSS JOIN rewriter is not enabled or cannot rewrite query #9782

Closed
#9830
Closed
COMMA to CROSS JOIN rewriter is not enabled or cannot rewrite query#9782
#9830
Assignees
4ertus2
Labels
bugConfirmed user-visible misbehaviour in official releasecomp-joinsJOINs end-to-end (planning hooks + runtime join operators/algorithms). Single bucket to avoid pla...
@qoega

Description

@qoega
qoega
opened on Mar 20, 2020

Describe the bug
Request worked on 20.1, but fails on master(20.3+)

select
	s_acctbal,
	s_name,
	n_name,
	p_partkey,
	p_mfgr,
	s_address,
	s_phone,
	s_comment,
	s_suppkey
from
(
	select
		ps1.ps_suppkey as ps_suppkey,
		n1.n_nationkey as n_nationkey,
		n1.n_regionkey as n_regionkey,
		s1.s_acctbal as s_acctbal,
		s1.s_name as s_name,
		n1.n_name as n_name,
		s1.s_address as s_address,
		s1.s_phone as s_phone,
		s1.s_comment as s_comment,
		s1.s_suppkey as s_suppkey,
		p1.p_mfgr as p_mfgr,
		p1.p_partkey as p_partkey
	from
		partsupp as ps1,
		supplier as s1,
		nation as n1,
		part as p1,
		region as r1
	where
		s1.s_suppkey = ps1.ps_suppkey
		and s1.s_nationkey = n1.n_nationkey
		and n1.n_regionkey = r1.r_regionkey
		and p1.p_partkey = ps1.ps_partkey
		and p1.p_size = 15
) as t1
ANY LEFT JOIN
(
	select
		min(ps_supplycost) as min_cost,
		ps2.ps_suppkey as ps_suppkey
	from
		partsupp as ps2,
		supplier as s2,
		nation as n2,
		region as r2
	where
		s2.s_suppkey = ps2.ps_suppkey
		and s2.s_nationkey = n2.n_nationkey
		and n2.n_regionkey = r2.r_regionkey
		and r2.r_name = toFixedString('EUROPE', 25)
	group by
		ps_suppkey
) as t2
on t1.ps_suppkey = t2.ps_suppkey
where p_type like '%BRASS'
order by
	s_acctbal desc,
	n_name,
	s_name,
	p_partkey
limit 100;
2020.03.20 17:18:21.242893 [ 1017330 ] {69d35959-d10b-469c-9e0a-54f3f6301d90} <Error> executeQuery: Code: 48, e.displayText() = DB::Exception: COMMA to CROSS JOIN rewriter is not enabled or cannot rewrite query (version 20.4.1.1) (from [::1]:43832) (in query: SELECT s_acctbal, s_name, n_name, p_partkey, p_mfgr, s_address, s_phone, s_comment, s_suppkey FROM (SELECT ps1.ps_suppkey AS ps_suppkey, n1.n_nationkey AS n_nationkey, n1.n_regionkey AS n_regionkey, s1.s_acctbal AS s_acctbal, s1.s_name AS s_name, n1.n_name AS n_name, s1.s_address AS s_address, s1.s_phone AS s_phone, s1.s_comment AS s_comment, s1.s_suppkey AS s_suppkey, p1.p_mfgr AS p_mfgr, p1.p_partkey AS p_partkey FROM partsupp AS ps1 , supplier AS s1 , nation AS n1 , part AS p1 , region AS r1 WHERE (s1.s_suppkey = ps1.ps_suppkey) AND (s1.s_nationkey = n1.n_nationkey) AND (n1.n_regionkey = r1.r_regionkey) AND (p1.p_partkey = ps1.ps_partkey) AND (p1.p_size = 15)) AS t1 ANY LEFT JOIN (SELECT min(ps_supplycost) AS min_cost, ps2.ps_suppkey AS ps_suppkey FROM partsupp AS ps2 , supplier AS s2 , nation AS n2 , region AS r2 WHERE (s2.s_suppkey = ps2.ps_suppkey) AND (s2.s_nationkey = n2.n_nationkey) AND (n2.n_regionkey = r2.r_regionkey) AND (r2.r_name = toFixedString('EUROPE', 25)) GROUP BY ps_suppkey) AS t2 ON t1.ps_suppkey = t2.ps_suppkey WHERE p_type LIKE '%BRASS' ORDER BY s_acctbal DESC, n_name ASC, s_name ASC, p_partkey ASC LIMIT 100), Stack trace (when copying this message, always include the lines below):

0. /home/qoega/clickhouse/build/../contrib/libcxx/include/exception:129: Poco::Exception::Exception(std::__1::basic_string<char, std::__1::char_traits<char>, std::__1::allocator<char> > const&, int) @ 0xaf213c2 in /home/qoega/clickhouse/build/dbms/programs/clickhouse
1. /home/qoega/clickhouse/build/../dbms/src/Common/Exception.cpp:30: DB::Exception::Exception(std::__1::basic_string<char, std::__1::char_traits<char>, std::__1::allocator<char> > const&, int) @ 0x4c42eee in /home/qoega/clickhouse/build/dbms/programs/clickhouse
2. /home/qoega/clickhouse/build/../dbms/src/Interpreters/JoinToSubqueryTransformVisitor.cpp:0: DB::JoinToSubqueryTransformMatcher::visit(DB::ASTSelectQuery&, std::__1::shared_ptr<DB::IAST>&, DB::JoinToSubqueryTransformMatcher::Data&) @ 0x88fe0c2 in /home/qoega/clickhouse/build/dbms/programs/clickhouse
3. /home/qoega/clickhouse/build/../contrib/libcxx/include/memory:3826: DB::InDepthNodeVisitor<DB::JoinToSubqueryTransformMatcher, true, std::__1::shared_ptr<DB::IAST> >::visit(std::__1::shared_ptr<DB::IAST>&) @ 0x8744978 in /home/qoega/clickhouse/build/dbms/programs/clickhouse
4. /home/qoega/clickhouse/build/../contrib/libcxx/include/iterator:1480: DB::InDepthNodeVisitor<DB::JoinToSubqueryTransformMatcher, true, std::__1::shared_ptr<DB::IAST> >::visit(std::__1::shared_ptr<DB::IAST>&) @ 0x874499b in /home/qoega/clickhouse/build/dbms/programs/clickhouse
5. /home/qoega/clickhouse/build/../contrib/libcxx/include/iterator:1480: DB::InDepthNodeVisitor<DB::JoinToSubqueryTransformMatcher, true, std::__1::shared_ptr<DB::IAST> >::visit(std::__1::shared_ptr<DB::IAST>&) @ 0x874499b in /home/qoega/clickhouse/build/dbms/programs/clickhouse
6. /home/qoega/clickhouse/build/../contrib/libcxx/include/iterator:1480: DB::InDepthNodeVisitor<DB::JoinToSubqueryTransformMatcher, true, std::__1::shared_ptr<DB::IAST> >::visit(std::__1::shared_ptr<DB::IAST>&) @ 0x874499b in /home/qoega/clickhouse/build/dbms/programs/clickhouse
7. /home/qoega/clickhouse/build/../contrib/libcxx/include/iterator:1480: DB::InDepthNodeVisitor<DB::JoinToSubqueryTransformMatcher, true, std::__1::shared_ptr<DB::IAST> >::visit(std::__1::shared_ptr<DB::IAST>&) @ 0x874499b in /home/qoega/clickhouse/build/dbms/programs/clickhouse
8. /home/qoega/clickhouse/build/../contrib/libcxx/include/iterator:1480: DB::InDepthNodeVisitor<DB::JoinToSubqueryTransformMatcher, true, std::__1::shared_ptr<DB::IAST> >::visit(std::__1::shared_ptr<DB::IAST>&) @ 0x874499b in /home/qoega/clickhouse/build/dbms/programs/clickhouse
9. /home/qoega/clickhouse/build/../contrib/libcxx/include/iterator:1480: DB::InDepthNodeVisitor<DB::JoinToSubqueryTransformMatcher, true, std::__1::shared_ptr<DB::IAST> >::visit(std::__1::shared_ptr<DB::IAST>&) @ 0x874499b in /home/qoega/clickhouse/build/dbms/programs/clickhouse
10. /home/qoega/clickhouse/build/../contrib/libcxx/include/iterator:1480: DB::InDepthNodeVisitor<DB::JoinToSubqueryTransformMatcher, true, std::__1::shared_ptr<DB::IAST> >::visit(std::__1::shared_ptr<DB::IAST>&) @ 0x874499b in /home/qoega/clickhouse/build/dbms/programs/clickhouse
11. /home/qoega/clickhouse/build/../contrib/libcxx/include/memory:3826: 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&) @ 0x872fd6a in /home/qoega/clickhouse/build/dbms/programs/clickhouse
12. /home/qoega/clickhouse/build/../dbms/src/Interpreters/InterpreterSelectQuery.cpp:166: 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&) @ 0x872f3ce in /home/qoega/clickhouse/build/dbms/programs/clickhouse
13. /home/qoega/clickhouse/build/../contrib/libcxx/include/memory:2204: 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&) @ 0x87806bf in /home/qoega/clickhouse/build/dbms/programs/clickhouse
14. /home/qoega/clickhouse/build/../contrib/libcxx/include/memory:2204: std::__1::__unique_if<DB::InterpreterSelectWithUnionQuery>::__unique_single std::__1::make_unique<DB::InterpreterSelectWithUnionQuery, std::__1::shared_ptr<DB::IAST>&, DB::Context&, DB::SelectQueryOptions>(std::__1::shared_ptr<DB::IAST>&, DB::Context&, DB::SelectQueryOptions&&) @ 0x871e96e in /home/qoega/clickhouse/build/dbms/programs/clickhouse
15. /home/qoega/clickhouse/build/../dbms/src/Interpreters/InterpreterFactory.cpp:0: DB::InterpreterFactory::get(std::__1::shared_ptr<DB::IAST>&, DB::Context&, DB::QueryProcessingStage::Enum) @ 0x871e198 in /home/qoega/clickhouse/build/dbms/programs/clickhouse
16. /home/qoega/clickhouse/build/../dbms/src/Interpreters/executeQuery.cpp:0: DB::executeQueryImpl(char const*, char const*, DB::Context&, bool, DB::QueryProcessingStage::Enum, bool, DB::ReadBuffer*, bool) @ 0x8973523 in /home/qoega/clickhouse/build/dbms/programs/clickhouse
17. /home/qoega/clickhouse/build/../dbms/src/Interpreters/executeQuery.cpp:572: DB::executeQuery(std::__1::basic_string<char, std::__1::char_traits<char>, std::__1::allocator<char> > const&, DB::Context&, bool, DB::QueryProcessingStage::Enum, bool, bool) @ 0x8972be6 in /home/qoega/clickhouse/build/dbms/programs/clickhouse
18. /home/qoega/clickhouse/build/../dbms/programs/server/TCPHandler.cpp:0: DB::TCPHandler::runImpl() @ 0x4c9f75d in /home/qoega/clickhouse/build/dbms/programs/clickhouse
19. /home/qoega/clickhouse/build/../dbms/programs/server/TCPHandler.cpp:0: DB::TCPHandler::run() @ 0x4ca892c in /home/qoega/clickhouse/build/dbms/programs/clickhouse
20. /home/qoega/clickhouse/build/../contrib/poco/Net/src/TCPServerConnection.cpp:57: Poco::Net::TCPServerConnection::start() @ 0xae7208c in /home/qoega/clickhouse/build/dbms/programs/clickhouse
21. /home/qoega/clickhouse/build/../contrib/libcxx/include/atomic:1036: Poco::Net::TCPServerDispatcher::run() @ 0xae7254c in /home/qoega/clickhouse/build/dbms/programs/clickhouse
22. /home/qoega/clickhouse/build/../contrib/poco/Foundation/src/ThreadPool.cpp:213: Poco::PooledThread::run() @ 0xaf77a27 in /home/qoega/clickhouse/build/dbms/programs/clickhouse
23. /home/qoega/clickhouse/build/../contrib/poco/Foundation/include/Poco/SharedPtr.h:156: Poco::ThreadImpl::runnableEntry(void*) @ 0xaf758ac in /home/qoega/clickhouse/build/dbms/programs/clickhouse
24. start_thread @ 0x76db in /lib/x86_64-linux-gnu/libpthread-2.27.so
25. /build/glibc-OTsEL5/glibc-2.27/misc/../sysdeps/unix/sysv/linux/x86_64/clone.S:97: clone @ 0x12188f in /usr/lib/debug/lib/x86_64-linux-gnu/libc-2.27.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