Question about timestampdiff
Hi, I'm investigating the behavior of the functions timestampdiff with
different time-units.
I found that in the case of the time-units: second, minute, hour, and day, the
logical plan returned by Calcite is consistent. Since the input is a timestamp
in milliseconds, in the same way, the result is in milliseconds.
For example, for the following query:
"select l_shipdate, l_commitdate, timestampdiff(DAY, l_commitdate, l_shipdate)
as diff from lineitem limit 5"
its logical plan produced is:
LogicalSort(fetch=[5])
LogicalProject(l_shipdate=[$10], l_commitdate=[$11],
diff=[CAST(/INT(Reinterpret(-($10, $11)), 8640)):INTEGER])
LogicalTableScan(table=[[main, lineitem]])
So far, so good. However, for the month and year case, the output is not what I
would expect. For the query:
"select l_shipdate, l_commitdate, timestampdiff(MONTH, l_commitdate,
l_shipdate) as diff from lineitem limit 5"
its logical plan produced is:
LogicalSort(fetch=[5])
LogicalProject(l_shipdate=[$10], l_commitdate=[$11],
diff=[CAST(Reinterpret(-($10, $11))):INTEGER])
LogicalTableScan(table=[[main, lineitem]])
What I expected is that the subtraction is also divided by a month in
milliseconds, something like:
LogicalProject(l_shipdate=[$10], l_commitdate=[$11],
[CAST(/INT(Reinterpret(-($10, $11)), 259200)):INTEGER])
Doesn't seem to be a bug in Calcite, because the processing of constants in the
unit tests passes OK:
SqlOperatorBaseTest.java:
..
tester.checkScalar("{fn TIMESTAMPDIFF(HOUR,"
+ " TIMESTAMP '2014-03-29 12:34:56',"
+ " TIMESTAMP '2014-03-29 12:34:56')}", "0", "INTEGER NOT NULL");
tester.checkScalar("{fn TIMESTAMPDIFF(MONTH,"
+ " TIMESTAMP '2019-09-01 00:00:00',"
+ " TIMESTAMP '2020-03-01 00:00:00')}", "6", "INTEGER NOT NULL");
..
So I wonder if there is something I'm not considering that is making me think
wrong. Please, any ideas?
Re: [QUESTION] Pushing up evaluations from LogicalProjects
Hi Stamatis, thank you for your attention and actually many thanks to everyone who is seeing/commenting about this topic. To be honest I didn't know about the existence of RelFieldTrimmer. I would like to test this, how could I use RelFieldTrimmer? can you give me a suggestion, please? On 2019/10/12 07:00:00, Stamatis Zampetakis wrote: > Hi Rommel, > > I was hoping that this could be done at least by RelFieldTrimmer [1]. Are > you using it already? > > Best, > Stamatis > > [1] > https://github.com/apache/calcite/blob/master/core/src/main/java/org/apache/calcite/sql2rel/RelFieldTrimmer.java > > On Sat, Oct 12, 2019 at 6:06 AM XING JIN wrote: > > > Filed a JIRA: > > https://issues.apache.org/jira/browse/CALCITE-3405 > > > > Haisheng Yuan 于2019年10月12日周六 上午4:34写道： > > > > > Yes, definitely. > > > > > > You can go through the project expression with InputFinder to find all > > the > > > used columns, create a logical project with those columns, and remap the > > > top project with new column indexes. > > > > > > On the other hand, instead of creating a new intermidiate pogical > > project, > > > we can also update ProjectTableScanRule to accept LogicalProject that is > > > not a simple mapping, and do the same task I mentioned above. > > > > > > - Haisheng > > > > > > -- > > > 发件人：Rommel Quintanilla > > > 日 期：2019年10月12日 03:15:31 > > > 收件人： > > > 主 题：[QUESTION] Pushing up evaluations from LogicalProjects > > > > > > Hi, maybe you can help me. > > > I have this portion from a larger logical plan: > > > .. > > > LogicalProject(l_orderkey=[$0], l_suppkey=[$2], *=[*($5, -(1, > > > $6))]) > > > LogicalTableScan(table=[[main, lineitem]]) > > > .. > > > > > > Because the LogicalProject above contains an evaluation, the > > > ProjectTableScanRule can't convert it to a BindableTableScan. > > > > > > I wonder if somehow the evaluation could be pushed up more or less like > > > this: > > > .. > > > LogicalProject(l_orderkey=[$0], l_suppkey=[$1], *=[*($2, -(1, $3))]) > > > LogicalProject(l_orderkey=[$0], l_suppkey=[$2], l_extendedprice=[$5], > > > l_discount=[$6]]) > > > LogicalTableScan(table=[[main, lineitem]]) > > > .. > > > > > > Regards. > > > > > >
Re: [QUESTION] Pushing up evaluations from LogicalProjects
new column indexes. > > > > > > > > On the other hand, instead of creating a new intermidiate pogical > > > project, > > > > we can also update ProjectTableScanRule to accept LogicalProject that > > is > > > > not a simple mapping, and do the same task I mentioned above. > > > > > > > > - Haisheng > > > > > > > > -- > > > > 发件人：Rommel Quintanilla > > > > 日 期：2019年10月12日 03:15:31 > > > > 收件人： > > > > 主 题：[QUESTION] Pushing up evaluations from LogicalProjects > > > > > > > > Hi, maybe you can help me. > > > > I have this portion from a larger logical plan: > > > > .. > > > > LogicalProject(l_orderkey=[$0], l_suppkey=[$2], *=[*($5, -(1, > > > > $6))]) > > > > LogicalTableScan(table=[[main, lineitem]]) > > > > .. > > > > > > > > Because the LogicalProject above contains an evaluation, the > > > > ProjectTableScanRule can't convert it to a BindableTableScan. > > > > > > > > I wonder if somehow the evaluation could be pushed up more or less like > > > > this: > > > > .. > > > > LogicalProject(l_orderkey=[$0], l_suppkey=[$1], *=[*($2, -(1, $3))]) > > > > LogicalProject(l_orderkey=[$0], l_suppkey=[$2], l_extendedprice=[$5], > > > > l_discount=[$6]]) > > > > LogicalTableScan(table=[[main, lineitem]]) > > > > .. > > > > > > > > Regards. > > > > > > > > > >
[QUESTION] Pushing up evaluations from LogicalProjects
Hi, maybe you can help me. I have this portion from a larger logical plan: .. LogicalProject(l_orderkey=[$0], l_suppkey=[$2], *=[*($5, -(1, $6))]) LogicalTableScan(table=[[main, lineitem]]) .. Because the LogicalProject above contains an evaluation, the ProjectTableScanRule can't convert it to a BindableTableScan. I wonder if somehow the evaluation could be pushed up more or less like this: .. LogicalProject(l_orderkey=[$0], l_suppkey=[$1], *=[*($2, -(1, $3))]) LogicalProject(l_orderkey=[$0], l_suppkey=[$2], l_extendedprice=[$5], l_discount=[$6]]) LogicalTableScan(table=[[main, lineitem]]) .. Regards.
