I basically agree with that principle. However, there are other principles: (a) when comparing dates to timestamps, say TIMESTAMP ‘2017-1-1 12:34:54’ <= DATE ‘2017-1-1’, does it give the answer one would expect?, (b) what does the standard say (if anything)?, (c) what do other databases do? I would weigh them all before making a decision.
Please make comments on https://issues.apache.org/jira/browse/CALCITE-1178 <https://issues.apache.org/jira/browse/CALCITE-1178>. Julian > On Dec 14, 2017, at 9:15 AM, Vova Vysotskyi <[email protected]> wrote: > > IMHO to allow reflexivity, symmetry and transitivity relations for > equivalence '='; > transitivity and anti-reflexivity relations for '>' operator, we should > cast both operands to the type that has greater precision. > > 2017-09-01 19:45 GMT+03:00 Julian Hyde <[email protected] > <mailto:[email protected]>>: > >> I think you’re running into https://issues.apache.org/ >> <https://issues.apache.org/> >> jira/browse/CALCITE-1178 <https://issues.apache.org/ >> <https://issues.apache.org/> >> jira/browse/CALCITE-1178> which was logged by a Drill committer a while >> ago but has not been fixed. Calcite is strongly typed internally. So if we >> are to allow ’aTimestamp > aDate’ then we either have to add a new overload >> of ‘>’ that allows (timestamp, date) arguments, or we need to insert >> implicit casts, so the expression becomes either ‘cast(aTimestamp AS DATE) >>> aDate’ or ‘aTimestamp > CAST(aDate AS TIMESTAMP)’. >> >> I don’t know which of these 3 alternatives matches the desired semantics >> of ‘>’. >> >> What should the following expressions return? >> >> TIMESTAMP ‘1970-01-01 12:00:00’ > DATE ‘1970-01-01’ >> TIMESTAMP ‘1970-01-01 12:00:00’ = DATE ‘1970-01-01’ >> TIMESTAMP ‘1970-01-01 12:00:00’ >= DATE ‘1970-01-01’ >> >> >> Julian >> >> >>> On Sep 1, 2017, at 9:31 AM, Roman Kulyk <[email protected]> wrote: >>> >>> Hi, team! >>> >>> Working on upgrade of Calcite version for Drill I got class cast >> exceptions >>> like "ClassCastException: org.apache.calcite.util.DateString cannot be >> cast >>> to org.apache.calcite.util.TimestampString" in Drill. >>> >>> This issue is reproducible we have "date + interval" situation, for >> example: >>> >>> >>> >>> *where o.o_orderdate >= date '1996-10-01' and o.o_orderdate < date >>> '1996-10-01' + interval '3' month* >>> >>> In this case Drill will cast DATE to TIMESTAMP and we will get >>> ClassCastException when Calcite will try to compare DateString >>> ('1996-10-01') with TimestampString ('1996-10-01' + interval '3'). To >> avoid >>> this situation added a general class for Date/Time/Timestamp literals to >>> make them comparable to each other. You can see my commit in my local >>> branch: https://github.com/KulykRoman/incubator-calcite/commit/0656d8fec >>> >>> So do I need to create Calcite Jira and open pull request on Calcite >> master >>> or this changes should be only in Drill-Calcite branch? >> >> > > > -- > Kind regards, > Volodymyr Vysotskyi
