If the query contains both of “IS NOT NULL” & " <>‘ABC’", the final SQL plan only have " <>‘ABC’".
I checked the souce code, seems the “is not null” is removed in PrelTransformer.toConvertibleRelRoot. Please check the below.
In convertible.rel, the condition still contains “IS NOT NULL”. But in reduced, it is removed. Is it a bug?
Hello @popejune
Can you provide the sample query/syntax that you have tried to test?
Also, let us know the Dremio version.
Thanks,
@Rakesh_Malugu
Actually, we are querying our customized plugin. We need get the SQL condition and send query to data source. But for the SQL plan, which is common logic for all plugins. I test the JSON data, it still miss the condition.
Version: 4.1.1-201912191016540894-05db06db
Below is a query sameple.
select id from table_1 where field_1 <> 'ABC' and field_1 is not null
Here is the call backtrace
Hi @Rakesh_Malugu,
I tested the JSON query, for the SQL plan, the “IS NOT NULL” will be removed, but the final result will check “IS NOT NULL”. Could you advise where to apply the query filters for the FIleSystem Data source?
Hi @Rakesh_Malugu,
I did more testing, seems if there is not equal, it will remove the NULL cause, which is unexpected.
Could you let me know the code where remove the NULL result?
Case 1: Correct
SELECT * FROM "Null"
Case 2: Correct
SELECT * FROM "Null" where SnapshotType<>'ABC' and SnapshotType is not null
Case 3: Incorrect, it is missing the null case.
SELECT * FROM "Null" where SnapshotType<>'ABC'
It seems calcite is resolving it correctly. Any evaluation of SQL expression is against not-null values. So to put:
something <> ‘ABC’
“something” above has to be not-null before above can be evaluated. So, indicating that “something” to be ‘is not null’ over again is logically not required when a literal evaluation is already a condition in the where-clause.
Thanks @desi. Given this is the standard, we will follow this.
