[DERBY-6024] Incorrect handling of NULL in IN lists

• Type: Bug
• Status: Open
• Priority: Major
• Resolution: Unresolved
• Affects Version/s: 10.7.1.1, 10.8.1.2, 10.8.2.2, 10.8.3.0, 10.9.1.0, 10.10.1.1
• Component/s: SQL
• Labels: derby_triage10_11
• Urgency: Normal
• Bug behavior facts: Deviation from standard, Wrong query result
• Assignee: Unassigned
• Reporter: Knut Anders Hatlen
• Created: 21/Dec/12 09:21
• Updated: 19/Jun/13 09:43

Description

I believe the following query should return null instead of false, as the IN list contains an unknown value (null), so one cannot say definitely that it doesn't contain 1:

ij> values 1 in (cast(null as int), 2);
1

---

false

1 row selected

Per SQL:2003, part 2, 8.4 <in predicate>, syntax rules 2 and 5, the query is equivalent to these two queries:

ij> values 1 in (values cast(null as int), 2);
1

---

NULL

1 row selected
ij> values 1 = any (values cast(null as int), 2);
1

---

NULL

1 row selected

They both return NULL, which I believe is the correct result for the first query too.