Cross join failed with the error 'Cannot refer column "x" to table' #9855
Description
Cross join failed with the error 'Cannot refer column "x" to table' when used more than two tables.
This query works:
SELECT count()
FROM numbers(4) AS n1, numbers(3) AS n2
WHERE n1.number > (select avg(n.number) from numbers(3) n);This one failed:
SELECT count()
FROM numbers(4) AS n1, numbers(3) AS n2, numbers(6) AS n3
WHERE n1.number > (select avg(n.number) from numbers(3) n);with error:
Received exception from server (version 20.3.3):
Code: 352. DB::Exception: Received from localhost:9000. DB::Exception: Cannot refer column 'n.number' to table. Stack trace:
0. Poco::Exception::Exception(std::__1::basic_string<char, std::__1::char_traits<char>, std::__1::allocator<char> > const&, int) @ 0x102d352c in /usr/bin/clickhouse
1. DB::Exception::Exception(std::__1::basic_string<char, std::__1::char_traits<char>, std::__1::allocator<char> > const&, int) @ 0x8f2d989 in /usr/bin/clickhouse
2. ? @ 0xd2c3819 in /usr/bin/clickhouse
3. ? @ 0xd2be444 in /usr/bin/clickhouse
4. ? @ 0xd2be444 in /usr/bin/clickhouse
5. ? @ 0xd2be444 in /usr/bin/clickhouse
6. ? @ 0xd2be444 in /usr/bin/clickhouse
7. ? @ 0xd2be444 in /usr/bin/clickhouse
8. ? @ 0xd2be444 in /usr/bin/clickhouse
9. ? @ 0xd2be444 in /usr/bin/clickhouse
10. ? @ 0xd2be444 in /usr/bin/clickhouse
11. ? @ 0xd2be444 in /usr/bin/clickhouse
12. DB::JoinToSubqueryTransformMatcher::visit(DB::ASTSelectQuery&, std::__1::shared_ptr<DB::IAST>&, DB::JoinToSubqueryTransformMatcher::Data&) @ 0xd2bee24 in /usr/bin/clickhouse
13. DB::JoinToSubqueryTransformMatcher::visit(std::__1::shared_ptr<DB::IAST>&, DB::JoinToSubqueryTransformMatcher::Data&) @ 0xd2c15df in /usr/bin/clickhouse
14. DB::InDepthNodeVisitor<DB::JoinToSubqueryTransformMatcher, true, std::__1::shared_ptr<DB::IAST> >::visit(std::__1::shared_ptr<DB::IAST>&) @ 0xcfc8145 in /usr/bin/clickhouse
15. 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&) @ 0xcfba877 in /usr/bin/clickhouse
16. 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&) @ 0xcfbb395 in /usr/bin/clickhouse
17. 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&) @ 0xd1b6d26 in /usr/bin/clickhouse
18. DB::InterpreterFactory::get(std::__1::shared_ptr<DB::IAST>&, DB::Context&, DB::QueryProcessingStage::Enum) @ 0xcf0f404 in /usr/bin/clickhouse
19. ? @ 0xd3b3a05 in /usr/bin/clickhouse
20. 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) @ 0xd3b66fd in /usr/bin/clickhouse
21. DB::TCPHandler::runImpl() @ 0x9009ab9 in /usr/bin/clickhouse
22. DB::TCPHandler::run() @ 0x900a9cc in /usr/bin/clickhouse
23. Poco::Net::TCPServerConnection::start() @ 0xe088257 in /usr/bin/clickhouse
24. Poco::Net::TCPServerDispatcher::run() @ 0xe0886bd in /usr/bin/clickhouse
25. Poco::PooledThread::run() @ 0x1035d71f in /usr/bin/clickhouse
26. Poco::ThreadImpl::runnableEntry(void*) @ 0x10359768 in /usr/bin/clickhouse
27. ? @ 0x1035b009 in /usr/bin/clickhouse
28. start_thread @ 0x76db in /lib/x86_64-linux-gnu/libpthread-2.27.so
29. __clone @ 0x12188f in /lib/x86_64-linux-gnu/libc-2.27.so[CH 20.3.3]