Derby
  1. Derby
  2. DERBY-6017

IN lists with mixed types may return wrong results

    Details

    • Type: Bug Bug
    • Status: Closed
    • Priority: Major Major
    • Resolution: Fixed
    • Affects Version/s: 10.9.1.0
    • Fix Version/s: 10.8.3.3, 10.9.2.2, 10.10.1.1
    • Component/s: SQL
    • Labels:
      None
    • Issue & fix info:
      Repro attached
    • Bug behavior facts:
      Wrong query result

      Description

      Given this table:

      ij> connect 'jdbc:derby:memory:db;create=true';
      ij> create table t(x bigint);
      0 rows inserted/updated/deleted
      ij> insert into t values 9223372036854775805, 9223372036854775806, 9223372036854775807;
      3 rows inserted/updated/deleted

      A query that uses an IN list that contains all the three values actually stored in the table, returns all three rows as expected:

      ij> select * from t where x in (9223372036854775805, 9223372036854775806, 9223372036854775807);
      X
      --------------------
      9223372036854775805
      9223372036854775806
      9223372036854775807

      3 rows selected

      However, if we add a value whose type precedence is higher, like a DOUBLE value, and that value happens to be equal to the approximation of the other values in the IN list when they are cast from BIGINT to DOUBLE, only one row is returned:

      ij> select * from t where x in (9223372036854775805, 9223372036854775806, 9223372036854775807, 9.223372036854776E18);
      X
      --------------------
      9223372036854775805

      1 row selected

      I believe this query should return all three rows too.

      1. d6017-3a-weme-test-failure.diff
        2 kB
        Knut Anders Hatlen
      2. d6017-2a-cast-if-needed.diff
        12 kB
        Knut Anders Hatlen
      3. d6017-1a-duplicates.diff
        8 kB
        Knut Anders Hatlen

        Issue Links

          Activity

            People

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

              Dates

              • Created:
                Updated:
                Resolved:

                Development