refactor(sqlparser/binder): extra parentheses around subquery are handled inconsistently

[xiangjinwu]

BoundQuery could be created for each parenthesis, even though they are not considered as a Subquery

I see.

In the non-subquery or scalar-subquery case, extra levels of parentheses are Expr::Nested, including 1 + (((select 2))). There is a single Expr::Subquery and a single Query. This covers 1 + (((select 2) + 3) + 4).

But for subqueries with its own syntax, extra levels of parentheses are SetExpr::Query, including exists(((select 2))). There is a single Expr::Subquery with multiple Query levels, to accommodate exists(((select 2) union (select 3)) union (select 4))

Let me open an issue to track whether these two should be unified, especially the unsupported 1 < any(((select 2))) is parsed as case A today be may belong to case B.

Originally posted by @xiangjinwu in https://github.com/risingwavelabs/risingwave/pull/14430#pullrequestreview-1814733434

To view the parsed ast: cargo run --bin sqlparser <<< '(((select 2)));'

[xiangjinwu]

The Expr::Subquery case is expected to allow SetExpr::Query as well, as in PostgreSQL:

test=# select 10 + (((select 2) union (select 3)) except (select 2));
 ?column? 
----------
       13
(1 row)

However, our sqlparser is reporting an error:

sql parser error: Expected ), found: union at line:1, column:31

To summarize:

• exists(((select 2) union (select 3)) union (select 4)) uses SetExpr::Query
• exists(((select 2))) uses SetExpr::Query
• 10 + (((select 2) union (select 3)) except (select 2)) should use SetExpr::Query but is buggy today.
• 1 + (((select 2) + 3) + 4) uses Expr::Nested
• 1 + (((select 2))) can use both. It is Expr::Nested today but may or may not switch to SetExpr::Query after bug fix.