[GitHub] [arrow-datafusion] alamb commented on issue #2254: [Optimizer] Infer is not null predicate from `where clause`

[GitBox <gi...@apache.org>]

alamb commented on issue #2254:
URL: https://github.com/apache/arrow-datafusion/issues/2254#issuecomment-1101759932

   I think this is a *very* subtle area of SQL and it depends on how the expression is being used
   
   For example, 
   
   ```sql
   select * from t1 where c1 > 0;  
   ```
   and 
   ```sql
   select * from t1 where c1 > 0 and c1 is not null;
   ```
   
   Are semantically equivalent (produce the same answers), but these are not:
   
   ```sql
   select c1 > 0  from t1;
   ```
   and
   ```sql
   select c1 > 0 and c1 is not null from t1;
   ```
   
   The reason is that the semantics of the `WHERE` clause are that only rows that evaluate to `true` are passed (if the row evaluates to `NULL` or `false` then they don't pass).
   
   However, in general, you still have to handle the three way logic with `NULL` -- I am not at all convinced we do this entirely correctly in the expression simplification pass. We should probably have two modes: `used in where` and `used elsewhere` or something 🤔 


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: github-unsubscribe@arrow.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org