2783982 - An incorrect result may be returned from an outer join query including a CASE statement - SAP ASE

• A join query with OUTER JOIN clauses on a condition including a CASE statement may return an incorrect result set.
• The CASE statement on the OUTER JOIN condition possibly returns a NULL value.
• This problem may occur if merge joins or hash joins are chosen, and it does not occur if only nested loop joins are used.
• Below is a sample query which possibly causes this problem :

SELECT test_a.a_col1, test_a.a_col2, test_b.b_col4, test_c.c_col4
  FROM test_a
  LEFT OUTER JOIN test_b ON test_a.a_col1 = test_b.b_col1 and test_a.a_col2 = test_b.b_col2 and test_b.b_col3 = 'T001'
  LEFT OUTER JOIN test_c ON test_c.c_col1 =  CASE
                                                                       WHEN test_a.a_col2 = 'TEST01' THEN '1'
                                                                       ELSE null
                                                                  END

• Even if the ELSE clause is not specified, the default is ELSE NULL. So, the same problem can occur.

• SAP Adaptive Server Enterprise 15.7
• SAP Adaptive Server Enterprise 16.0

CR819088, CR#819088, CR 819088, incorrect result, wrong result, case, NULL, merge join, hash join , KBA , BC-SYB-ASE , Sybase ASE Database Platform (non Business Suite) , Bug Filed