Syntax error parsing WHERE id IN (SELECT 1 FROM dual)

[jcberleur]

Running the following query (generated by an orm)

SELECT * FROM tbl WHERE id IN (SELECT 1 FROM dual WHERE 1 = 0);

I get this error

ERROR 1105 (HY000): target: legacy.-.master: vttablet: rpc error: code = InvalidArgument desc = syntax error at position 36 near ':vtg1' (CallerID: user)

using the EXPLAIN or passing the query directly to mysql/mariadb works correctly

MySQL [legacy]> EXPLAIN FORMAT=VITESS SELECT * FROM tbl WHERE id IN (SELECT 1 FROM dual WHERE 1 = 0);
+----------+-----------------+----------+-------------+------------+----------------------------------------------------------------+
| operator | variant         | keyspace | destination | tabletType | query                                                          |
+----------+-----------------+----------+-------------+------------+----------------------------------------------------------------+
| Route    | SelectUnsharded | legacy   |             | UNKNOWN    | select * from tbl where id in (select 1 from dual where 1 = 0) |
+----------+-----------------+----------+-------------+------------+----------------------------------------------------------------+

version

Running vitess/lite:v10.0.2 with the kubernetes operator

Version: 10.0.2 (Git revision fc7847093 branch 'heads/v10.0.2') built on Thu May 27 09:00:55 UTC 2021 by vitess@c8edcabbb04a using go1.15.6 linux/amd64

[jcberleur]

SELECT IN seems to have been added in #8155, but subquery support is still missing

ERROR 1235 (42000): gen4 does not yet support: subquery

[GuptaManan100]

Which planner version are you using, V3 or Gen4 or Gen4Fallback? Also, have you turned on query normalization on the vtgate?

[jcberleur]

I tried the default and Gen4

[GuptaManan100]

Yes that makes sense, the problem is in the rewriter, which should be fixed by #8589. Work on supporting subqueries in Gen4 is still underway.

[GuptaManan100]

Please reopen if the issue persists.