Uploaded image for project: 'Derby'
  1. Derby
  2. DERBY-6080

Cast to same type changes result with IN subquery

    XMLWordPrintableJSON

    Details

    • 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
    • Fix Version/s: None
    • Component/s: SQL
    • Urgency:
      Normal
    • Issue & fix info:
      High Value Fix, Repro attached
    • Bug behavior facts:
      Wrong query result

      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.

        Attachments

          Issue Links

            Activity

              People

              • Assignee:
                Unassigned
                Reporter:
                knutanders Knut Anders Hatlen
              • Votes:
                0 Vote for this issue
                Watchers:
                3 Start watching this issue

                Dates

                • Created:
                  Updated: