There are two kinds of intervals (in Calcite and in standard SQL). Days-hours-minutes-seconds-millseconds intervals, and year-month intervals. The former are represented internally in milliseconds. The latter are represented in months.
This is one area where Postgres does things differently from the SQL standard. > On Feb 10, 2022, at 3:48 AM, Chathura Widanage <[email protected]> > wrote: > > Thanks, Stamatis. Below is the original SQL query. > > select l_returnflag, l_linestatus, sum(l_quantity) as sum_qty, > sum(l_extendedprice) as sum_base_price, sum(l_extendedprice*(1-l_discount)) > as sum_disc_price, sum(l_extendedprice*(1-l_discount)*(1+l_tax)) as > sum_charge, avg(l_quantity) as avg_qty, avg(l_extendedprice) as avg_price, > avg(l_discount) as avg_disc, count(*) as count_order from lineitem where > l_shipdate <= date '1998-12-01' - interval '90' day group by l_returnflag, > l_linestatus order by l_returnflag, l_linestatus > > Based on the shared line of code, even a month should be represented in > milis right? But when the below query is transformed it shows months in > months. > > with revenue (suplier_no, total_revenue) as ( select l_suppkey, > sum(l_extendedprice * (1-l_discount)) from lineitem where l_shipdate >= > date '1996-01-01' and l_shipdate < date '1996-01-01' + interval '3' month > group by l_suppkey ) select s_suppkey, s_name, s_address, s_phone, > total_revenue from supplier, revenue where s_suppkey = suplier_no and > total_revenue = ( select max(total_revenue) from revenue ) order by > s_suppkey > > AND(>=($1, 1993-07-01 00:00:00), <($1, CAST(+(1993-07-01, 3:INTERVAL > MONTH)):TIMESTAMP(0) NOT NULL)) > > Thanks for the pointer Stamatis, I'll see whether there is something to do > with the RexSimplify/RexExecutor. > > Regards, > Chathura > > > On Thu, Feb 10, 2022 at 5:07 PM Stamatis Zampetakis <[email protected]> > wrote: > >> Hi Chathura, >> >> It is difficult to reason about correctness without having the actual SQL >> query at hand. >> >> The fact that you have milliseconds is not by itself a problem and has to >> do with the way Calcite internally represents intervals (see comment in >> [1]). >> >> Also from the examples you provided the behavior in 1.29.0 does not seem to >> be an additional transformation rather than a missing simplification >> (constant reduction). I am not sure if this is intentional or not but I >> guess you can have a look at the changes landed around >> RexSimplify/RexExecutor. >> >> Best, >> Stamatis >> >> [1] >> >> https://github.com/apache/calcite/blob/812e3e98eae518cf85cd1b6b7f055fb96784a423/core/src/main/java/org/apache/calcite/rex/RexLiteral.java#L357 >> >> >> On Thu, Feb 10, 2022 at 8:02 AM Chathura Widanage < >> [email protected]> wrote: >> >>> Hi community, >>> >>> I'm comparing two rel expressions generated by calcite 1.25.0 and 1.29.0 >>> and noticed there is an invalid IntervalSQLType plugged into the query. >>> >>> <=($6, 1998-09-02 00:00:00) : Calcite 1.25.0 >>> vs >>> <=($6, CAST(-(1998-12-01, 7776000000:INTERVAL DAY)):TIMESTAMP(0) NOT >> NULL) >>> : >>> Calcite 1.29.0 >>> >>> 7776000000 is 90 days in milliseconds, but the IntervalSQLType/value >>> combination is invalid. >>> >>> Could you please let me know whether this could be a bug and whether >> there >>> an option to prevent such transformations at all? >>> >>> Regards, >>> Chathura >>> >>> PS: This comes on queries from tpch benchmark and invalid conversion is >>> from tpch-01. >>> >>> I'm seeing similar conversions in other queries, but they seem to be >>> correct, but feels this transformation is redundant. >>> >>> AND(>=($1, 1993-07-01 00:00:00), <($1, 1993-10-01 00:00:00)) >>> vs >>> AND(>=($1, 1993-07-01 00:00:00), <($1, CAST(+(1993-07-01, 3:INTERVAL >>> MONTH)):TIMESTAMP(0) NOT NULL)) >>> >>
