[jira] [Created] (DERBY-6080) Cast to same type changes result with IN subquery

["Knut Anders Hatlen (JIRA)" <ji...@apache.org>]

Knut Anders Hatlen created DERBY-6080:
-----------------------------------------

             Summary: Cast to same type changes result with IN subquery
                 Key: DERBY-6080
                 URL: https://issues.apache.org/jira/browse/DERBY-6080
             Project: Derby
          Issue Type: Bug
          Components: SQL
    Affects Versions: 10.9.1.0
            Reporter: Knut Anders Hatlen
            Priority: Minor


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.

--
This message is automatically generated by JIRA.
If you think it was sent incorrectly, please contact your JIRA administrators
For more information on JIRA, see: http://www.atlassian.com/software/jira