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?