[CALCITE-1863] Timestamp "<>" operator compares box references instead of values

• Type: Bug
• Status: Open
• Priority: Major
• Resolution: Unresolved
• Affects Version/s: 1.14.0, 1.13.0, 1.12.0
• Assignee: Unassigned
• Reporter: Damjan Jovanovic
• Created: 29/Jun/17 11:35
• Updated: 22/Feb/19 01:22

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.

Issue Links

• is related to: CALCITE-1754 In Csv adapter, convert DATE and TIME values to int, and TIMESTAMP values to long