You are viewing a plain text version of this content. The canonical link for it is here.
Posted to issues@calcite.apache.org by "Damjan Jovanovic (JIRA)" <ji...@apache.org> on 2017/06/30 01:43:00 UTC
[jira] [Comment Edited] (CALCITE-1863) Timestamp is different from
itself in WHERE
[ https://issues.apache.org/jira/browse/CALCITE-1863?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16069325#comment-16069325 ]
Damjan Jovanovic edited comment on CALCITE-1863 at 6/30/17 1:42 AM:
--------------------------------------------------------------------
How do you get a plan? Is this it:
{quote}
LogicalProject(id=[$0], dt=[$1], id0=[$2], dt0=[$3])
LogicalFilter(condition=[<>($1, $3)])
LogicalJoin(condition=[=($0, $2)], joinType=[full])
JdbcTableScan(table=[[A, test]])
JdbcTableScan(table=[[B, test]])
{quote}
The timestamp comparison must happen in Calcite, as a.dt and b.dt are from different database connections.
was (Author: damjan):
How do you get a plan? Is this it:
LogicalProject(id=[$0], dt=[$1], id0=[$2], dt0=[$3])
LogicalFilter(condition=[<>($1, $3)])
LogicalJoin(condition=[=($0, $2)], joinType=[full])
JdbcTableScan(table=[[A, test]])
JdbcTableScan(table=[[B, test]])
The timestamp comparison must happen in Calcite, as a.dt and b.dt are from different database connections.
> Timestamp is different from itself in WHERE
> -------------------------------------------
>
> 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)