“Row_Number” function is not working with “In” Clause , we have a requirement in which we want to filter the result and generate the Uniquer id.
we have used below query and its giving the error.
Query
SELECT UniqueId FROM (SELECT CAST ((ROW_NUMBER() OVER()) AS VARCHAR) AS UniqueId , from (SELECT “Reporting” FROM ((SELECT * FROM “1M.parquet”) ) WHERE “Reporting” IN (‘1100’,‘4’)) )*
Error
While invoking method ‘public org.apache.calcite.sql2rel.RelFieldTrimmer$TrimResult com.dremio.exec.planner.sql.handlers.DremioFieldTrimmer.trimFields(org.apache.calcite.rel.core.Project,org.apache.calcite.util.ImmutableBitSet,java.util.Set)’
Can you please guide what we can do to resolve the issue.
I was able to reproduce this issue using the following query against our sample source.
It looks like the issue is that you have to include at least one actual column from your table.
This will fail with the error your see
select cast(ROW_NUMBER()OVER() as VARCHAR) as uniqueid from Samples.“samples.dremio.com”.“NYC-taxi-trips” where year(“NYC-taxi-trips”.pickup_datetime) IN (2014) limit 100
This will pass because I’m grabbing an actual column from the dataset
select “NYC-taxi-trips”.pickup_datetime, cast(ROW_NUMBER()OVER() as VARCHAR) as uniqueid from Samples.“samples.dremio.com”.“NYC-taxi-trips” where year(“NYC-taxi-trips”.pickup_datetime) IN (2014) limit 100
This also holds if you run a similar query where you select one of the fiels from your dataset and use a different one in the IN clause.
This will also work
select “NYC-taxi-trips”.pickup_datetime, cast(ROW_NUMBER()OVER() as VARCHAR) as uniqueid from Samples.“samples.dremio.com”.“NYC-taxi-trips” where “NYC-taxi-trips”.passenger_count IN (6, 8) limit 5;
Im going to do some research to see if this is expected or not.
A colleague confirmed that this behavior is unexpected. The workaround I listed above may help you in the mean time. I have filed a bug internally but I cannot share a timeline as to when it may be addressed. Thanks again for sharing this error.