Physical planner does not support subquery expression

[killme2008]

What type of bug is this?

Unexpected error

What subsystems are affected?

Datanode

What happened?

Looks like a datafusion feature missing: https://github.com/apache/arrow-datafusion/blob/master/datafusion/physical-expr/src/planner.rs#L446

Create a table and insert some data:

CREATE TABLE integers(i INTEGER, j BIGINT TIME INDEX);
INSERT INTO integers VALUES (1, 1), (2, 2), (3, 3), (NULL, 4);

Run queries:

SELECT * FROM integers WHERE i IN ((SELECT i FROM integers)) ORDER BY I;

SELECT * FROM integers WHERE i NOT IN ((SELECT i FROM integers WHERE i=1)) ORDER BY I;

SELECT * FROM integers WHERE i IN ((SELECT i FROM integers)) AND i<3 ORDER BY I;

SELECT i1.i,i2.i FROM integers i1, integers i2 WHERE i IN ((SELECT i FROM integers)) AND i1.i=i2.i ORDER BY 1;

All these queries fail with the error:

Failed to execute, error: Datanode { code: 3001, msg: "Failed to execute SQL, 
source: Fail to create physical plan: 
This feature is not implemented: Physical plan does not support logical expression (<subquery>)" }

What operating system did you use?

Mac Osx

Relevant log output and stack trace

No response

How can we reproduce the bug?

Described above.

[waynexia]

It looks like you have duplicated brackets around subqueries. It should be (<subquery>) rather than ((<subquery>)), isn't it?

I can get results with the following queries after removing a pair of brackets for each:

SELECT * FROM integers WHERE column1 IN (SELECT column1 FROM integers);

SELECT * FROM integers WHERE column1 NOT IN (SELECT column1 FROM integers WHERE column1=1);

SELECT * FROM integers WHERE column1 IN (SELECT column1 FROM integers) AND column1<3 ;

SELECT i1.column1,i2.column1 FROM integers i1, integers i2 WHERE column1 IN (SELECT column1 FROM integers) AND i1.column1=i2.column1 ;

[MichaelScofield]

wait for apache/datafusion#5529

[evenyag]

Does PostgreSQL support this?

[alamb]

When I tried this, postgres errors:

postgres=# CREATE TABLE integers(i INTEGER, j BIGINT);
CREATE TABLE
postgres=# INSERT INTO integers VALUES (1, 1), (2, 2), (3, 3), (NULL, 4);
INSERT 0 4
postgres=# SELECT i1.i,i2.i FROM integers i1, integers i2 WHERE i IN ((SELECT i FROM integers)) AND i1.i=i2.i ORDER BY 1;
ERROR:  column reference "i" is ambiguous
LINE 1: ...ECT i1.i,i2.i FROM integers i1, integers i2 WHERE i IN ((SEL...

[MichaelScofield]

I've tested all 4 sqls in mysql, pg and duckdb. The last sql all failed with some error like "...ambiguous column i...". However, the other 3 are good.