I did the debugging and then I was able to figure out that during validation casting is not added for Char types if the target type is of SqlTypeFamily.DATETIME family but only works if target type is one of SqlTypeFamily.DATE, SqlTypeFamily.TIME, SqlTypeFamily.TIMESTAMP. Will have a look at the test cases. Thanks for the reply.
On Thu, Sep 15, 2022 at 4:48 AM Julian Hyde <jhyde.apa...@gmail.com> wrote: > DATETIME is a type family, not a type. (Yeah, I know that some databases > have a DATETIME type, but the SQL standard doesn’t.) > > I don’t know all the details, but I recall that a type family is used as a > shorthand for a family of closely related types that can be implicitly > converted from one to the other (e.g. SMALLINT to INTEGER). And it may be > used as a shorthand for the type of a function parameter where the function > has several overloads. > > The conversion of a character literal (a constant string such as > ‘1970-01-01’) to a DATE, TIME or TIMESTAMP literal such as DATE > ‘1970-01-01’ might (again, I’m not sure) use its own particular mechanism, > a different mechanism from the implicit conversion of say a SMALLINT to an > INTEGER. If I were you I would seek out some applicable tests - there > should be quite a few in SqlValidatorTest - and step through them in a > debugger. > > Julian > > > > On Sep 13, 2022, at 8:49 AM, Parag Jain <paragjai...@gmail.com> wrote: > > > > In the AbstractTypeCoercion class, I see here > > < > https://github.com/apache/calcite/blob/main/core/src/main/java/org/apache/calcite/sql/validate/implicit/AbstractTypeCoercion.java#L675 > >, > > that DateTime family contains types SqlTypeFamily.DATE, > SqlTypeFamily.TIME, > > SqlTypeFamily.TIMESTAMP. Here > > < > https://github.com/apache/calcite/blob/main/core/src/main/java/org/apache/calcite/sql/validate/implicit/AbstractTypeCoercion.java#L709 > > > > I > > can see that the Char types can be cast to DateTime type. > > > > However, if I define a SqlFunction with an operand of > > SqlTypeFamily.DATETIME type then this logic fails and implicit cast does > > not work. > > > > As far as I understand SqlTypeFamily.DATETIME contains SqlTypeName of > DATE, > > TIME, TIME_WITH_LOCAL_TIME_ZONE, TIMESTAMP, > TIMESTAMP_WITH_LOCAL_TIME_ZONE. > > So shouldn't implicit cast work from Char to DATETIME work as well. > > > > Couple questions - > > 1. Is the DATETIME here used to represent values in 'YYYY-MM-DD hh:mm:ss' > > format or is it just a family type ? > > 2. What operand type should be used to define a function which can accept > > DATE and TIMESTAMP as well as values in 'YYYY-MM-DD hh:mm:ss' format ? > > > > Thanks > >
