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

["Damjan Jovanovic (JIRA)" <ji...@apache.org>]

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

Damjan Jovanovic commented on CALCITE-1863:
-------------------------------------------

The 64-bit MySQL type BIGINT is also represented as a java.lang.Long, yet it is unboxed before comparison and gives the correct query results:
{code}
    final Long inp1_ = (Long) current[1];
    final Long inp3_ = (Long) current[3];
    if (inp1_ != null && inp3_ != null && inp1_.longValue() != inp3_.longValue()) {
{code}

So why isn't TIMESTAMP unboxed, which Calcite also represents as a java.lang.Long? The answer lies in this section of the stack:

{noformat}
		RexToLixTranslator.translate0(RexNode, RexImpTable$NullAs, Type) line: 471	
		RexToLixTranslator.translate(RexNode, RexImpTable$NullAs, Type) line: 221	
		RexToLixTranslator.translate0(RexNode, RexImpTable$NullAs, Type) line: 500	
		RexToLixTranslator.translate(RexNode, RexImpTable$NullAs, Type) line: 221	
		RexToLixTranslator.translate(RexNode, Type) line: 216	
		RexToLixTranslator.translateList(List<RexNode>, List<Type>) line: 729	
		RexToLixTranslator.translateList(List<RexNode>) line: 702	
		RexImpTable.implementCall(RexToLixTranslator, RexCall, NotNullImplementor, RexImpTable$NullAs) line: 930	
{noformat}

EnumUtils.internalTypes() converts RexNode types from TIMESTAMP to Long (but why not "long", when the column is "NOT NULL"? Is this the real problem?).
Then RexToLixTranslator.translateList() extracts "desiredType" from its "storageTypes" list, getting java.lang.Long.
This is passed to RexToLixTranslator.translate() as its "storageType" parameter.
Ultimately RexToLixTranslator.translate0() enters the INPUT_REF case in its switch statement,
and this shortcut ends up being taken, skipping the unboxing that would happen later:
{code}
      if (nullAs == RexImpTable.NullAs.NOT_POSSIBLE
          && input.type.equals(storageType)) {
        // When we asked for not null input that would be stored as box, avoid
        // unboxing via nullAs.handle below.
        return input;
      }
{code}

When BIGINT is used instead, 
EnumUtils.internalTypes() does nothing,
"null" is passed to RexToLixTranslator.translate() as its "storageType" parameter,
that "if" statement is skipped,
and the value is correctly unboxed by later statements in RexToLixTranslator.translate0().

I tried to add
{code}
          && !Primitive.isBox(input.type)
{code}
to the "if" statement, but while that fixed my query, it also resulted in many unit test failures of the form:
{noformat}
Caused by: org.codehaus.commons.compiler.CompileException: Line 82, Column 36: Incompatible expression types "int" and "java.lang.Integer"
{noformat}

If instead I always pass "null" as the "storageType" from RexToLixTranslator.translateList(), my query also works, but I get other unit test failures, such as:
{noformat}
Caused by: java.lang.ClassCastException: java.sql.Date cannot be cast to java.lang.Number
{noformat}

I am not sure where to go from here. Please help?

> 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)