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

[Van Halbert (JIRA)]

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

Van Halbert edited comment on TEIID-3573 at 7/28/15 9:13 AM:
-------------------------------------------------------------

>> The combination of intNum <> 23 and intNum is Null criteria is an issue in both cases.
>I'm not sure what you mean.

My thinking was wrong on this, the result is correct, no rows should have been returned.  Because,  intNum <> 23 removed the rows where intNum = <null>, therefore adding  "and intNum is Null" had no intersecting rows to choose from.

Based on these tests and to get the right results, both SupportNot and SupportNull need to be set to false.

As for performing a full scan for a negated predicate, it appears JDG is not doing a full scan on an indexed attribute.  Based on the timing using squirrel,   full scan:   Total: 3.722, SQL query: 3.719,    scan on index:  Total: 0.133, SQL query: 0.129
Test query:  select intKey,intNum, stringNum, floatnum from smallA where intNum <> 23

And this is only with 49 rows being returned.


was (Author: van.halbert):
>> The combination of intNum <> 23 and intNum is Null criteria is an issue in both cases.
>I'm not sure what you mean.

My thinking was wrong on this, the result is correct, no rows should have been returned.  Because,  intNum <> 23 removed the rows where intNum = <null>, therefore adding  "and intNum is Null" had no intersecting rows to choose from.

Based on these tests and to get the right results, both SupportNot and SupportNull need to be set to false.

As for performing a full scan for a negated predicate, it appears JDG is not doing a full scan on an indexed attribute.  Based on the timing using squirrel,   full scan:   Total: 3.722, SQL query: 3.719,    scan on index:  Total: 0.133, SQL query: 0.129

And this is only with 49 rows being returned.

> 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)