Uploaded image for project: 'Calcite'
  1. Calcite
  2. CALCITE-1863

Timestamp "<>" operator compares box references instead of values

    XMLWordPrintableJSON

    Details

    • Type: Bug
    • Status: Open
    • Priority: Major
    • Resolution: Unresolved
    • Affects Version/s: 1.14.0, 1.13.0, 1.12.0
    • Fix Version/s: None
    • Component/s: None
    • Labels:
      None

      Description

      In MySQL:
      USE db;
      CREATE TABLE test(id INTEGER NOT NULL PRIMARY KEY, dt datetime NOT NULL);
      INSERT INTO test values (1, '2017-06-29 09:00:00');

      Then connect to it twice, and full outer join on the primary key where the timestamp is different:

      Connection connection = DriverManager.getConnection("jdbc:calcite:", info);
      CalciteConnection calciteConnection = connection.unwrap(CalciteConnection.class);
      SchemaPlus rootSchema = calciteConnection.getRootSchema();
      JdbcSchema a = JdbcSchema.create(calciteConnection.getRootSchema(),
                          "A", aDataSource, null, "db");
      rootSchema.add("A", a);
      JdbcSchema b = JdbcSchema.create(calciteConnection.getRootSchema(),
                          "B", bDataSource, null, "db");
      rootSchema.add("B", b);
      PreparedStatement st = connection.prepareStatement(
                          "SELECT * FROM A.test AS a FULL OUTER JOIN B.test AS b " +
                          "ON a.id=b.id " +
                          "WHERE a.dt <> b.dt");
      ResultSet results = st.executeQuery();
      

      results now contains that row joined to itself, since the "dt" in WHERE tested as not equal to itself.

        Attachments

        1. timestamp-test.patch
          2 kB
          Damjan Jovanovic

          Issue Links

            Activity

              People

              • Assignee:
                Unassigned
                Reporter:
                damjan Damjan Jovanovic
              • Votes:
                0 Vote for this issue
                Watchers:
                3 Start watching this issue

                Dates

                • Created:
                  Updated: