[teiid-issues] [JBoss JIRA] (TEIID-4802) Not in with subquery does not work correctly
[
https://issues.jboss.org/browse/TEIID-4802?page=com.atlassian.jira.plugin...
]
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)