feature: `IN` subquery in select

[cyliu0]

Describe the bug

dev=> select 3 in (2, 3);
 ?column?
----------
 t
(1 row)
dev=>CREATE TABLE orders (PRIMARY KEY (order_id)) WITH (connector = 'kafka', kafka.topic = 'mysql.mydb.orders', kafka.brokers = 'message_queue:29092', kafka.scan.startup.mode = 'earliest') FORMAT DEBEZIUM ENCODE AVRO (schema.registry = 'http://message_queue:8081';
dev=> select 3 in (select order_id from orders);
ERROR:  QueryError: Feature is not yet implemented: In(3:Int32)
Tracking issue: https://github.com/risingwavelabs/risingwave/issues/1343

Error message/log

No response

To Reproduce

No response

Expected behavior

No response

How did you deploy RisingWave?

No response

The version of RisingWave

No response

Additional context

No response

[xiangjinwu]

3 in (2, 3) and 3 in (select order_id from orders) are 2 different types of expressions: https://www.postgresql.org/docs/15/functions-comparisons.html#FUNCTIONS-COMPARISONS-IN-SCALAR https://www.postgresql.org/docs/15/functions-subquery.html#FUNCTIONS-SUBQUERY-IN

For the subquery form, we only implemented its support in where: #2187 That being said, we could have a clearer message, or add the support if it is needed.

https://github.com/risingwavelabs/risingwave/blob/d0a498432e0fca8e073883381430bb0dbe4ddb48/src/frontend/src/planner/select.rs#L366-L375

[github-actions[bot]]

This issue has been open for 60 days with no activity.

If you think it is still relevant today, and needs to be done in the near future, you can comment to update the status, or just manually remove the no-issue-activity label.

You can also confidently close this issue as not planned to keep our backlog clean. Don't worry if you think the issue is still valuable to continue in the future. It's searchable and can be reopened when it's time. 😄