[DERBY-6080] Cast to same type changes result with IN subquery

• Type: Bug
• Status: Open
• Priority: Minor
• Resolution: Unresolved
• Affects Version/s: 10.0.2.1, 10.1.1.0, 10.1.2.1, 10.1.3.1, 10.2.1.6, 10.2.2.0, 10.3.1.4, 10.3.2.1, 10.3.3.0, 10.4.1.3, 10.4.2.0, 10.5.1.1, 10.5.2.0, 10.5.3.0, 10.6.1.0, 10.6.2.1, 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
• Issue & fix info: High Value Fix, Repro attached
• Bug behavior facts: Wrong query result
• Assignee: Unassigned
• Reporter: Knut Anders Hatlen
• Created: 20/Feb/13 08:15
• Updated: 13/Jun/14 12:34

Description

Given these tables

ij> create table t4 (b bigint);
0 rows inserted/updated/deleted
ij> insert into t4 values 9223372036854775806, 9223372036854775807;
2 rows inserted/updated/deleted
ij> create table t5 (d double);
0 rows inserted/updated/deleted
ij> insert into t5 values 9.223372036854776E18;
1 row inserted/updated/deleted

the following two SELECT queries return different results:

ij> select * from t4 where b in (select d from t5);
B
--------------------
9223372036854775807

1 row selected
ij> select * from t4 where b in (select cast(d as double) from t5);
B
--------------------
9223372036854775806
9223372036854775807

2 rows selected

Since the only difference is D vs CAST(D AS DOUBLE), and the type of D is already DOUBLE, I'd expect the two queries to be equivalent.

Issue Links

• relates to: DERBY-6017 IN lists with mixed types may return wrong results