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