Bug 6539 - Query with not joins and condition returning the wrong result
Summary: Query with not joins and condition returning the wrong result
Status: RESOLVED NEEDINFO
Alias: None
Product: SQL
Classification: Unclassified
Component: all (show other bugs)
Version: 11.27.11 (Jul2017-SP3)
Hardware: Other Windows
: Normal major
Assignee: SQL devs
URL:
Keywords:
Depends on:
Blocks:
 
Reported: 2018-02-09 17:10 CET by Manuel
Modified: 2018-03-02 12:52 CET (History)
1 user (show)



Attachments
bp.sql: the table used in the query (239.08 KB, text/plain)
2018-02-09 17:11 CET, Manuel
Details
full_query.sql: the full query exposing the issue. (12.36 KB, text/plain)
2018-02-09 17:12 CET, Manuel
Details
create_view_t9c.sql: one of the view used in the query described in the details section (8.69 KB, text/plain)
2018-02-09 17:13 CET, Manuel
Details
create_view_t9d.sql: one of the view used in the query described in the details section (2.86 KB, text/plain)
2018-02-09 17:13 CET, Manuel
Details

Note You need to log in before you can comment on or make changes to this bug.
Description Manuel 2018-02-09 17:10:26 CET
User-Agent:       Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/64.0.3282.140 Safari/537.36
Build Identifier: 

I have a query involving the "NOT EXISTS" clause, as well as various joins and unions.

The complete query can be found in the file "full_query.sql" attached.

For sake of simplicity I created two views extracted from the query called "t9c" and "t9d".
The table used in the query can be imported via the file "bp.sql" also attached.

These are the content of the two views:

select * from "t9d" order by "c8v__y_g_0", "c8w__y_g_1"    ;

c8v__y_g_0        c8w__y_g_1     
---------------------------------
Collateralised    Liq Cat (sum)  
Collateralised    Record count   
Senior Unsecured  Liq Cat (sum)  
Senior Unsecured  Record count   
Subordinated      Liq Cat (sum)  
Subordinated      Record count   
Supranational     Liq Cat (sum)  
Supranational     Record count   



select * from "t9c" order by "c8l__y_g_0"  , "c8m__y_g_1" ;

c8l__y_g_0        c8m__y_g_1     c8n_axis_1_sort_0_1  
------------------------------------------------------
Collateralised    Liq Cat (sum)  3                    
Collateralised    Record count   3                    
Senior Unsecured  Liq Cat (sum)  7                    
Senior Unsecured  Record count   7                    
Subordinated      Liq Cat (sum)  3                    
Subordinated      Record count   3                    
Supranational     Liq Cat (sum)  2                    
Supranational     Record count   2       

Once the views has been created, I expect the query

  SELECT "t9d"."c8v__y_g_0"  AS "c99__y_g_0",
         "t9d"."c8w__y_g_1"  AS "c9a__y_g_1",
         CAST(NULL AS bigint) AS "c9b_axis_1_sort_0_1"
  FROM  "t9d"
  WHERE
   NOT EXISTS
   (
    SELECT
     1 AS "one"
    FROM
     "t9c"
    WHERE (
			( "t9d"."c8v__y_g_0" = "t9c"."c8l__y_g_0"
			  OR 
		      ("t9d"."c8v__y_g_0" IS NULL AND  "t9c"."c8l__y_g_0" IS NULL)
			)
     AND
			( "t9d"."c8w__y_g_1" = "t9c"."c8m__y_g_1"
			  OR
			  ("t9d"."c8w__y_g_1" IS NULL AND  "t9c"."c8m__y_g_1" IS NULL)
			)
		)
	)

to return no result (each pair (*g_0, *g_1) is contained in both tables).

However I get the records: 

c99__y_g_0        c9a__y_g_1     c9b_axis_1_sort_0_1  
------------------------------------------------------
Subordinated      Liq Cat (sum)  <null>               
Supranational     Liq Cat (sum)  <null>               
Senior Unsecured  Liq Cat (sum)  <null>               
Collateralised    Liq Cat (sum)  <null>             


Removing the condition on null values solves the issue and the result is empty as expected.

While investigating on this issue I found other queries returning the wrong result:

select * from "t9d", "t9c"
  WHERE
  (("t9d"."c8v__y_g_0" = "t9c"."c8l__y_g_0" OR  ("t9d"."c8v__y_g_0" IS NULL AND    "t9c"."c8l__y_g_0" IS NULL) )  AND   ("t9d"."c8w__y_g_1" = "t9c"."c8m__y_g_1" OR ("t9c"."c8m__y_g_1" IS NULL AND  "t9d"."c8w__y_g_1" IS NULL)))


select * from "t9d", "t9c"
  	WHERE
  	(("t9d"."c8v__y_g_0" = "t9c"."c8l__y_g_0" OR  (1 = 2) )  AND   ("t9d"."c8w__y_g_1" = "t9c"."c8m__y_g_1" OR ("t9c"."c8m__y_g_1" IS NULL AND  "t9d"."c8w__y_g_1" IS NULL)))


select * from "t9d", "t9c"
  	WHERE
(not("t9d"."c8v__y_g_0" <> "t9c"."c8l__y_g_0" AND (1 = 1))  AND   not("t9d"."c8w__y_g_1" <> "t9c"."c8m__y_g_1" and (1 = 1)))


Reproducible: Always

Steps to Reproduce:
1. Import the table contained in "bp.sql" attached.
2. run the query contained in "full_query.sql", or create the views in create_view_t9c.sql create_view_t9d.sql and use the query described in the Details section
3.
Actual Results:  
c99__y_g_0        c9a__y_g_1     c9b_axis_1_sort_0_1  
------------------------------------------------------
Subordinated      Liq Cat (sum)  <null>               
Supranational     Liq Cat (sum)  <null>               
Senior Unsecured  Liq Cat (sum)  <null>               
Collateralised    Liq Cat (sum)  <null>             


Expected Results:  
No records.
Comment 1 Manuel 2018-02-09 17:11:05 CET
Created attachment 593 [details]
bp.sql: the table used in the query
Comment 2 Manuel 2018-02-09 17:12:32 CET
Created attachment 594 [details]
full_query.sql: the full query exposing the issue.
Comment 3 Manuel 2018-02-09 17:13:33 CET
Created attachment 595 [details]
create_view_t9c.sql: one of the view used in the query described in the details section
Comment 4 Manuel 2018-02-09 17:13:50 CET
Created attachment 596 [details]
create_view_t9d.sql: one of the view used in the query described in the details section
Comment 5 Niels Nes cwiconfidential 2018-03-02 12:52:52 CET
seems solved