Support where <column> in <query>

[BohuTANG]

Summary

mysql> select number from numbers(10) where number in (select number from numbers(1));
ERROR 1105 (HY000): Code: 1005, displayText = Unsupported expression: number IN (SELECT number FROM numbers(1)), type: InSubquery { expr: Identifier(Ident { value: "number", quote_style: None }), subquery: Query { with: None, body: Select(Select { distinct: false, top: None, projection: [UnnamedExpr(Identifier(Ident { value: "number", quote_style: None }))], from: [TableWithJoins { relation: Table { name: ObjectName([Ident { value: "numbers", quote_style: None }]), alias: None, args: [Unnamed(Expr(Value(Number("1", false))))]

Server log:

2022-03-29T12:34:47.888782Z ERROR databend_query::servers::mysql::writers::query_result_writer: OnQuery Error: Code: 1005, displayText = Unsupported expression: number IN (SELECT number FROM numbers(1)), type: InSubquery { expr: Identifier(Ident { value: "number", quote_style: None }), subquery: Query { with: None, body: Select(Select { distinct: false, top: None, projection: [UnnamedExpr(Identifier(Ident { value: "number", quote_style: None }))], from: [TableWithJoins { relation: Table { name: ObjectName([Ident { value: "numbers", quote_style: None }]), alias: None, args: [Unnamed(Expr(Value(Number("1", false))))], with_hints: [] }, joins: [] }], lateral_views: [], selection: None, group_by: [], cluster_by: [], distribute_by: [], sort_by: [], having: None }), order_by: [], limit: None, offset: None, fetch: None }, negated: false } (while in analyze select filter).

   0: common_exception::exception_code::<impl common_exception::exception::ErrorCode>::SyntaxException
             at common/exception/src/exception_code.rs:36:66
   1: common_ast::parser::expr::expr_visitor::ExprVisitor::visit::{{closure}}
             at common/ast/src/parser/expr/expr_visitor.rs:82:34
   2: <core::future::from_generator::GenFuture<T> as core::future::future::Future>::poll
             at /rustc/8769f4ef2fe1efddd1f072485f97f568e7328f79/library/core/src/future/mod.rs:91:19
   3: <core::pin::Pin<P> as core::future::future::Future>::poll
             at /rustc/8769f4ef2fe1efddd1f072485f97f568e7328f79/library/core/src/future/future.rs:124:9
   4: common_ast::parser::expr::expr_visitor::ExprTraverser::accept::{{closure}}
             at common/ast/src/parser/expr/expr_visitor.rs:35:29
   5: <core::future::from_generator::GenFuture<T> as core::future::future::Future>::poll
             at /rustc/8769f4ef2fe1efddd1f072485f97f568e7328f79/library/core/src/future/mod.rs:91:19
   6: databend_query::sql::statements::analyzer_expr::ExprRPNBuilder::build::{{closure}}
             at query/src/sql/statements/analyzer_expr.rs:442:50
   7: <core::future::from_generator::GenFuture<T> as core::future::future::Future>::poll
             at /rustc/8769f4ef2fe1efddd1f072485f97f568e7328f79/library/core/src/future/mod.rs:91:19
   8: databend_query::sql::statements::analyzer_expr::ExpressionAnalyzer::analyze::{{closure}}
             at query/src/sql/statements/analyzer_expr.rs:61:75
   9: <core::future::from_generator::GenFuture<T> as core::future::future::Future>::poll
             at /rustc/8769f4ef2fe1efddd1f072485f97f568e7328f79/library/core/src/future/mod.rs:91:19
  10: databend_query::sql::statements::query::query_normalizer::QueryNormalizer::visit_filter::{{closure}}
             at query/src/sql/statements/query/query_normalizer.rs:94:82
  11: <core::future::from_generator::GenFuture<T> as core::future::future::Future>::poll
             at /rustc/8769f4ef2fe1efddd1f072485f97f568e7328f79/library/core/src/future/mod.rs:91:19
  12: databend_query::sql::statements::query::query_normalizer::QueryNormalizer::transform::{{closure}}
             at query/src/sql/statements/query/query_normalizer.rs:64:53
  13: <core::future::from_generator::GenFuture<T> as core::future::future::Future>::poll
             at /rustc/8769f4ef2fe1efddd1f072485f97f568e7328f79/library/core/src/future/mod.rs:91:19
  14: databend_query::sql::statements::query::query_normalizer::QueryNormalizer::normalize::{{closure}}
             at query/src/sql/statements/query/query_normalizer.rs:60:38
  15: <core::future::from_generator::GenFuture<T> as core::future::future::Future>::poll
             at /rustc/8769f4ef2fe1efddd1f072485f97f568e7328f79/library/core/src/future/mod.rs:91:19
  16: <databend_query::sql::statements::statement_select::DfQueryStatement as databend_query::sql::statements::analyzer_statement::AnalyzableStatement>::analyze::{{closure}}::{{closure}}
             at query/src/sql/statements/statement_select.rs:66:67

[likg227]

I implemented InSubquery for RisingWave yesterday(pr), and I want to implement it for Databend so that I can get familiar with the architecture of Databend. Let me have a try!

[leiysky]

@likg227 Hi, we are building our new planner framework recently, you can track the progress with the issue #3747.

An enhanced expression IR will be introduced later(related issues #1538 ), which is helpful to compile subqueries.

[xudong963]

@likg227 Hi, do you still want to give it a try?

[likg227]

@xudong963 Yes, I'm planning to solve it recently. But I still need some time to be familiar with the new planner framework and I also have my own work, so the progress may be slow. Anyway, if you want to implement it yourself, please go head and I'm ok with that.

[xudong963]

@likg227 Ok, I'll start it :)