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)), 86400000)):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)), 2592000000)):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?
