I found that when the IN clause is used in where, and the IN list is too long, dremio will optimize it to INNER JOIN (VALUES…), but MySQL does not support this, resulting in execution errors.
sql:
sql.zip (7.0 KB)
profile:
66071a99-7e55-4a4f-bb75-bc1f407ac06c.zip (123.5 KB)
dremio info:
@bigfacewo This is an optimization rule that was developed for IN clauses having > 1500, can you try to disable exec.operator.orfast and retry the query. Do you a similar use case with PARQUET too?
This is the data I use:
orders2.zip (403.7 KB)
However, this problem does not exist when querying from the parquet file.
I think this is a good optimization, but the database support is not considered.
