[DERBY-6577] Quantified comparison returns wrong result in CASE, COALESCE, IN and BETWEEN

• Type: Bug
• Status: Closed
• Priority: Major
• Resolution: Fixed
• Affects Version/s: 10.10.2.0, 10.11.1.1
• Fix Version/s: 10.10.2.1, 10.11.1.1
• Component/s: SQL
• Issue & fix info: Repro attached
• Bug behavior facts: Regression, Wrong query result
• Assignee: Knut Anders Hatlen
• Reporter: Knut Anders Hatlen
• Created: 14/May/14 13:40
• Updated: 09/Sep/16 09:53
• Resolved: 13/Nov/14 17:48

Description

I'm seeing this on head of trunk:

ij> select c, c = all (values 'Y'), case when c = all (values 'Y') then true else false end from (values 'Y', 'N') v(c);
C|2    |3    
-------------
Y|true |false
N|false|true 

2 rows selected

Column 2 and column 3 should have the same value, but something seems to go wrong when the quantified comparison is used in a CASE expression.

I'm seeing the expected result on 10.10.2.0, though:

ij> select c, c = all (values 'Y'), case when c = all (values 'Y') then true else false end from (values 'Y', 'N') v(c);
C|2    |3    
-------------
Y|true |true 
N|false|false

2 rows selected

Issue Links

• is broken by: DERBY-6566 Simplify handling of untyped nulls in CASE and NULLIF expressions
• relates to: DERBY-6408 EXISTS returns NULL instead of FALSE
• relates to: DERBY-6409 Wrong result from quantified comparison
• relates to: DERBY-6759 Derby 10.10 backport issue (fall 2014)