Re: Re: TIMESTAMPDIFF and datetime subtraction can not work well with JDBC adapter
As I known, MySQL supports timestampdiff directly, PG and Oracle allow
datetime subtraction that the format of the result is "XX days HH:mm:ss", Spark
SQL and Hive SQL support datediff function. And maybe we can use unix_timestamp
instead to get datetime subtraction result in seconds for Spark SQL and Hive
SQL. Similarly, PG can use EXTRACT(EPOCH FROM... to get result in seconds.
I am free these days to participate in this issue.
Best wishes
Wenhui Tang
winifred.wenhui.t...@gmail.com
From: Julian Hyde
Date: 2020-04-03 02:40
To: dev
Subject: Re: TIMESTAMPDIFF and datetime subtraction can not work well with JDBC
adapter
I think you have identified the issues correctly.
I prefer SqlDatetimeSubtractionOperator to SqlTimestampDiffFunction because it
is standard and more powerful. But some work is needed to make SQL generation
run smoothly. Especially with data types, as you point out.
I would approach this by writing tests in RelToSqlConverterTest with the
desired SQL for each database and change RelToSqlConverter to make them pass.
Pragmatic, not very elegant.
Julian
> On Apr 2, 2020, at 6:45 AM, TANG Wen-hui
> wrote:
>
> Dear All,
>
> As for issue https://issues.apache.org/jira/browse/CALCITE-3312(I'm working
> on this issue), I have noticed several problems. So I am writing this mail
> for some advices.
>
> 1.Calcite support the syntax like that "(datetime - datetime)
> IntervalQualifier";
> A simple test in JdbcTest:
> @Test public void testTimestampMinus() {
>CalciteAssert.that()
>.with(CalciteAssert.Config.JDBC_SCOTT)
>.query("select (date'2003-12-30' - date'2001-11-30') year from
> JDBC_SCOTT.emp")
>.returns("");
> }
> However, the result returns "+63" which is wrong. This is because that
> RelToSqlConverter simply convert it to "SELECT (DATE '2003-12-30' - DATE
> '2001-11-30') FROM "SCOTT"."EMP"" (see
> SqlDatetimeSubtractionOperator#unparse).
> As I konw, most database systems cannot deal with this sitution properly, and
> the standard format of datetime value expression is like that
> "
> ::=
>
> |
> |
> |
> ::=
>
> ::=
> [ ]
> ::=
>
> |
> ::=
> AT
> ::=
> LOCAL
> | TIME ZONE
> "[1]
>
> 2.Calcite will translate SqlTimestampDiffFunction to
> SqlDatetimeSubtractionOperator during the conversion from SqlNode to RexNode
> for SqlTimestampDiffFunction(see TimestampDiffConvertlet#convertCall). But
> the different return types between SqlTimestampDiffFunction whose return type
> is SqlTypeName.BIGINT or SqlTypeName.INTEGER and
> SqlDatetimeSubtractionOperator whose return type is IntervalSqlType introduce
> the internal operator REINTERPRET and /INT(see makeCastIntervalToExact). The
> internal operator should not be converted to sql. I did a test that I
> overrided the "unparse" method for REINTERPRET and / and still got the wrong
> result like above.
>
> I have a few thoughts about this kind of situation, but I am not sure it goes
> right way.
> 1.We can forbidden the conversion from Project to JdbcProject for the Project
> which contains RexCall whose operator is SqlDatetimeSubtractionOperator and
> operands both are datetime.
> 2.Or we can translate SqlDatetimeSubtractionOperator to
> SqlTimestampDiffFunction or other similar functions during unparsing(for
> example, MySQL supports TIMESTAMPDIFF rather than datetime subtraction), but
> the internal operator and the situation that databases which support this
> feature take different approaches to achieve this feature make thing
> confusing.
>
> Best wishes
> Wenhui Tang
>
> [1] ISO/IEC CD 9075-2
>
>
>
>
> winifred.wenhui.t...@gmail.com
Re: TIMESTAMPDIFF and datetime subtraction can not work well with JDBC adapter
I think you have identified the issues correctly.
I prefer SqlDatetimeSubtractionOperator to SqlTimestampDiffFunction because it
is standard and more powerful. But some work is needed to make SQL generation
run smoothly. Especially with data types, as you point out.
I would approach this by writing tests in RelToSqlConverterTest with the
desired SQL for each database and change RelToSqlConverter to make them pass.
Pragmatic, not very elegant.
Julian
> On Apr 2, 2020, at 6:45 AM, TANG Wen-hui
> wrote:
>
> Dear All,
>
> As for issue https://issues.apache.org/jira/browse/CALCITE-3312(I'm working
> on this issue), I have noticed several problems. So I am writing this mail
> for some advices.
>
> 1.Calcite support the syntax like that "(datetime - datetime)
> IntervalQualifier";
> A simple test in JdbcTest:
> @Test public void testTimestampMinus() {
>CalciteAssert.that()
>.with(CalciteAssert.Config.JDBC_SCOTT)
>.query("select (date'2003-12-30' - date'2001-11-30') year from
> JDBC_SCOTT.emp")
>.returns("");
> }
> However, the result returns "+63" which is wrong. This is because that
> RelToSqlConverter simply convert it to "SELECT (DATE '2003-12-30' - DATE
> '2001-11-30') FROM "SCOTT"."EMP"" (see
> SqlDatetimeSubtractionOperator#unparse).
> As I konw, most database systems cannot deal with this sitution properly, and
> the standard format of datetime value expression is like that
> "
> ::=
>
> |
> |
> |
> ::=
>
> ::=
> [ ]
> ::=
>
> |
> ::=
> AT
> ::=
> LOCAL
> | TIME ZONE
> "[1]
>
> 2.Calcite will translate SqlTimestampDiffFunction to
> SqlDatetimeSubtractionOperator during the conversion from SqlNode to RexNode
> for SqlTimestampDiffFunction(see TimestampDiffConvertlet#convertCall). But
> the different return types between SqlTimestampDiffFunction whose return type
> is SqlTypeName.BIGINT or SqlTypeName.INTEGER and
> SqlDatetimeSubtractionOperator whose return type is IntervalSqlType introduce
> the internal operator REINTERPRET and /INT(see makeCastIntervalToExact). The
> internal operator should not be converted to sql. I did a test that I
> overrided the "unparse" method for REINTERPRET and / and still got the wrong
> result like above.
>
> I have a few thoughts about this kind of situation, but I am not sure it goes
> right way.
> 1.We can forbidden the conversion from Project to JdbcProject for the Project
> which contains RexCall whose operator is SqlDatetimeSubtractionOperator and
> operands both are datetime.
> 2.Or we can translate SqlDatetimeSubtractionOperator to
> SqlTimestampDiffFunction or other similar functions during unparsing(for
> example, MySQL supports TIMESTAMPDIFF rather than datetime subtraction), but
> the internal operator and the situation that databases which support this
> feature take different approaches to achieve this feature make thing
> confusing.
>
> Best wishes
> Wenhui Tang
>
> [1] ISO/IEC CD 9075-2
>
>
>
>
> winifred.wenhui.t...@gmail.com
TIMESTAMPDIFF and datetime subtraction can not work well with JDBC adapter
Dear All,
As for issue https://issues.apache.org/jira/browse/CALCITE-3312(I'm working on
this issue), I have noticed several problems. So I am writing this mail for
some advices.
1.Calcite support the syntax like that "(datetime - datetime)
IntervalQualifier";
A simple test in JdbcTest:
@Test public void testTimestampMinus() {
CalciteAssert.that()
.with(CalciteAssert.Config.JDBC_SCOTT)
.query("select (date'2003-12-30' - date'2001-11-30') year from
JDBC_SCOTT.emp")
.returns("");
}
However, the result returns "+63" which is wrong. This is because that
RelToSqlConverter simply convert it to "SELECT (DATE '2003-12-30' - DATE
'2001-11-30') FROM "SCOTT"."EMP"" (see SqlDatetimeSubtractionOperator#unparse).
As I konw, most database systems cannot deal with this sitution properly, and
the standard format of datetime value expression is like that
"
::=
|
|
|
::=
::=
[ ]
::=
|
::=
AT
::=
LOCAL
| TIME ZONE
"[1]
2.Calcite will translate SqlTimestampDiffFunction to
SqlDatetimeSubtractionOperator during the conversion from SqlNode to RexNode
for SqlTimestampDiffFunction(see TimestampDiffConvertlet#convertCall). But the
different return types between SqlTimestampDiffFunction whose return type is
SqlTypeName.BIGINT or SqlTypeName.INTEGER and SqlDatetimeSubtractionOperator
whose return type is IntervalSqlType introduce the internal operator
REINTERPRET and /INT(see makeCastIntervalToExact). The internal operator should
not be converted to sql. I did a test that I overrided the "unparse" method for
REINTERPRET and / and still got the wrong result like above.
I have a few thoughts about this kind of situation, but I am not sure it goes
right way.
1.We can forbidden the conversion from Project to JdbcProject for the Project
which contains RexCall whose operator is SqlDatetimeSubtractionOperator and
operands both are datetime.
2.Or we can translate SqlDatetimeSubtractionOperator to
SqlTimestampDiffFunction or other similar functions during unparsing(for
example, MySQL supports TIMESTAMPDIFF rather than datetime subtraction), but
the internal operator and the situation that databases which support this
feature take different approaches to achieve this feature make thing confusing.
Best wishes
Wenhui Tang
[1] ISO/IEC CD 9075-2
winifred.wenhui.t...@gmail.com
