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

["Julian Hyde (JIRA)" <ji...@apache.org>]

    [ https://issues.apache.org/jira/browse/CALCITE-1863?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16070389#comment-16070389 ] 

Julian Hyde commented on CALCITE-1863:
--------------------------------------

Ah, good find. I had assumed that because it worked for one comparison operator it would work for all. But apparently not.

Can you please add tests for dateTime literals to SqlOperatorBaseTest.testLessThanOperator (and verify that they fail without your fix, succeed with it).

The code is spread across several classes: RexToLixTranslator, JavaRowFormat, PhysTypeImpl, Expressions, EnumUtils. Put a break point in EnumerableJoin.implement and see where it goes.

> Timestamp "<>" operator compares box references instead of values
> -----------------------------------------------------------------
>
>                 Key: CALCITE-1863
>                 URL: https://issues.apache.org/jira/browse/CALCITE-1863
>             Project: Calcite
>          Issue Type: Bug
>    Affects Versions: 1.12.0, 1.13.0
>            Reporter: Damjan Jovanovic
>            Assignee: Julian Hyde
>
> 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:
> {code}
> 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();
> {code}
> results now contains that row joined to itself, since the "dt" in WHERE tested as not equal to itself.



--
This message was sent by Atlassian JIRA
(v6.4.14#64029)