[teiid-issues] [JBoss JIRA] (TEIID-3573) Infinispan-dsl-cache translator: Operator <> incorrectly handles NULL values

[Steven Hawkins (JIRA)]

    [ https://issues.jboss.org/browse/TEIID-3573?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=13092836#comment-13092836 ] 

Steven Hawkins commented on TEIID-3573:
---------------------------------------

IS NULL/IS NOT NULL is appropriate from the user query when you are explicitly testing for null.  In this case we are not testing null, rather intNum <> 1.  It's returning true for a null intNum - that is not expected with 3 valued SQL (but could be the result in 2 valued systems).  

> Unless you're thinking of adding an additional filtering in the translator such that when a column that's used in the WHERE clause is returned that has a null value, and NULL or ISNULL checks are not used, then filter out the row?

Yes, if they are a 2 valued system, then we should change the translator logic to compensate.  So if it sees where intNum<>1 it would need to produce "intNum<>1 and intNum IS NOT NULL".  If that's not generally something that the translator can handle we can look at letting the engine compensate, but that could come with a lot of restrictions.


> Infinispan-dsl-cache translator: Operator <> incorrectly handles NULL values
> ----------------------------------------------------------------------------
>
>                 Key: TEIID-3573
>                 URL: https://issues.jboss.org/browse/TEIID-3573
>             Project: Teiid
>          Issue Type: Bug
>          Components: Misc. Connectors
>    Affects Versions: 8.7.1
>            Reporter: Filip Elias
>            Assignee: Van Halbert
>
> Operator '<>' returns true for NULL <> 1
> Example:
> {code}
> select intKey,intNum from smallA where intNum<>1
> {code} 
> It returns also rows which have NULL in column intNum
> I believe that NULL <> 1 is not true in SQL.



--
This message was sent by Atlassian JIRA
(v6.3.15#6346)