NOT IN working incorectly.
--------------------------
Key: CORE-4523
URL: http://tracker.firebirdsql.org/browse/CORE-4523
Project: Firebird Core
Issue Type: Bug
Affects Versions: 3.0 Alpha 2
Reporter: Tim Kelly
Hi. I ran a query with NOT IN that erroneously isn't returning any results when
I thought that is should. I constructed the same query using a RIGHT JOIN
instead and I can see that indeed the first query should have returned the
results where the left side of the Join was NULL.
I have tried creating a simple test case but could not reproduce the issue. I
have done a backup and restore on my database and also tested with latest
snapshot and the NOT IN query still does not work as expected.
I can't restore the database onto version 2.5 as it contains features not
supported in that version.
I can provide the database for testing purposes but it's confidential so I
can't post on here.
select OID TS_OID from hwk_bpl_travel_staff where OID NOT IN (SELECT
AUTOUPDATE_BO_OID from DOV_BO_DYNAMIC_DATA);
(RETURNS NO DATA)
I did not expect this so I tried:
select dd.oid DD_OID, ts.oid TS_OID from dov_bo_dynamic_data dd right join
hwk_bpl_travel_staff ts on dd.AUTOUPDATE_BO_OID = ts.oid where dd.oid is null;
DD_OID TS_OID
=================== ===================
<null> C6BF-55D5-72B3-2C54
<null> FC08-BB37-54E1-9D1E
<null> 7987-082D-DF03-F1F1
<null> E0BC-1CDB-EDAF-B68B
<null> 0A19-5C34-0BB8-FFAB
<null> 002A-343A-93B1-B5BC
<null> 2DC3-E1EE-4995-5530
<null> 0B42-BFB8-C0A1-7836
<null> 355B-43E8-56C4-2E05
<null> 9B7F-7313-1832-6687
<null> 9D17-06D1-380B-DAE8
<null> 182A-5559-87FB-4055
<null> 3F52-9D74-87CF-BE0A
<null> ADF7-FB58-841C-BE5C
<null> 8790-2C6F-054D-268B
<null> 211D-413C-1F88-8643
<null> 30BA-B980-5BD3-DC57
<null> D3F5-8464-FEA3-D962
<null> 69D6-98A0-770C-DC3F
<null> C3BF-8DFB-B1F6-6FD4
DD_OID TS_OID
=================== ===================
<null> 4A58-E2DA-8551-0E89
<null> E6AC-46D0-0905-C306
<null> F1F7-5EF8-3AA2-50AD
<null> 8D32-CC1B-EEDE-B2E4
<null> BA29-F132-33FF-6429
<null> 160E-2F41-3070-6ED0
<null> 505B-A5B5-230C-8727
<null> 7DD6-0C88-8798-8464
<null> B078-F8CF-ABAA-04FF
<null> 2B9A-8520-FB9B-792D
<null> 9A15-B16E-764A-DE01
<null> 36F6-0C6E-46FB-1E11
<null> 356D-6A9F-1BC1-C55A
<null> 254D-5F61-616C-77C0
<null> 7926-1B66-F197-4B7A
<null> 6C7B-FD75-3C66-E833
<null> 36B0-1E35-DFE8-87A4
Thanks,
Tim
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
http://tracker.firebirdsql.org/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira
------------------------------------------------------------------------------
Firebird-Devel mailing list, web interface at
https://lists.sourceforge.net/lists/listinfo/firebird-devel
