[teiid-issues] [JBoss JIRA] (TEIID-4802) Not in with subquery does not work correctly

[Bram Gadeyne (JIRA)]

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

Bram Gadeyne edited comment on TEIID-4802 at 3/9/17 9:32 AM:
-------------------------------------------------------------

Hi Steven,

Yes,

The query "select WSidentifier from prod_U_PV_PClist" returns 96 results where wsidentifier is unique (for non null values). and 2 where wsidentifier is null.

The query "select cl.hostname from izalert_clientstatus cl" returns 123 results where hostname is unique.

If I change the subquery to "select WSidentifier from prod_U_PV_PClist where WSidentifier is not null" then I to get the desired results.

Could the null value be causing this?


was (Author: gadeyne.bram):
Hi Steven,

Yes,

The query "select WSidentifier from prod_U_PV_PClist" returns 96 results where wsidentifier is unique. and 2 where wsidentifier is null.

The query "select cl.hostname from izalert_clientstatus cl" returns 123 results where hostname is unique.

If I change the subquery to "select WSidentifier from prod_U_PV_PClist where WSidentifier is not null" then I to get the desired results.

Could the null value be causing this?

> Not in with subquery does not work correctly
> --------------------------------------------
>
>                 Key: TEIID-4802
>                 URL: https://issues.jboss.org/browse/TEIID-4802
>             Project: Teiid
>          Issue Type: Bug
>    Affects Versions: 9.0.5, 9.1.4
>            Reporter: Bram Gadeyne
>            Assignee: Steven Hawkins
>            Priority: Critical
>         Attachments: query_plan_not_in.txt
>
>
> Hi,
> This query does not return any results where it should.
> {code:sql}
> select cl.*
> from izalert_clientstatus cl 
> where cl.hostname not in (
> 	select WSidentifier from prod_U_PV_PClist
> ) 
> {code}
> This query is more or less equivalent to the one above but does return 28 results.
> {code:sql}
> select cl.hostname
> from izalert_clientstatus cl
> left join prod_U_PV_PClist pl on cl.hostname = pl.WSidentifier
> where pl.WSidentifier is null
> {code}
> hostname and wsidentifier are both String fields. In the clientstatus table it has a length of 255, in the other table 63. The values for hostname and wsidentifier are e.g. 'COSARA4' and 'PC-10360'



--
This message was sent by Atlassian JIRA
(v7.2.3#72005)