Hi Yuzhao:
Thanks for raising this discussion. I think this feature is significant to
Calcite.
AFAIK, there is no standard on implicit type coercion. Even for those
widely-adopted RDBMSs (ORACLE, SqlSever, and etc.), we can find some
"unreasonable" corner cases that are not as user expected.
There are too many factors. Take (1 > '1') as an example, is "casting one
type to another type directly" (1 > cast('1' as int)) or "casting to common
types" (cast('1' as double) > cast('1' as double)) more suitable?
How about (1>'111111111111111111111111111111111')?
>From my point of view, we can make implicit type coercion as dialect
interfaces, and provide SqlDialect-specific implementations. In recent
years, our in-house platform has evolved many times from Oracle to Hive and
Spark SQL.
When migrating from one system to another, problems caused by implicit type
coercion brought us much pain. Different runtime conversion beheviors lead
to different results even for the same query.
I think a pluggable or dialect-configurable design benefits not only
Calcite itself but also the engines (Flink) that use Calcite.
Best,
DonnyZone
Kurt Young <[email protected]> 于2019年5月27日周一 上午11:34写道：
> Thanks Danny for pushing this.
>
> Just like you said, different engines may use different strategies for
> implicit type cast, so i
> think making the whole mechanism pluggable would be a good idea.
>
> Best,
> Kurt
>
>
> On Mon, May 27, 2019 at 11:08 AM Haisheng Yuan <[email protected]>
> wrote:
>
> > Thanks Danny for bringing it up.
> > This is a useful feature, we should push it forward.
> >
> > I went through the design doc, looks good in general.
> > I will also spend some time on the pull request 706.
> >
> > Thanks ~
> > Haisheng Yuan
> > ------------------------------------------------------------------
> > 发件人：Yuzhao Chen<[email protected]>
> > 日 期：2019年05月27日 10:20:47
> > 收件人：<[email protected]>
> > 主 题：Support complete implicit type coercion (DISCUSSION)
> >
> > Hi, guys.
> >
> > The implicit type coercion is almost supported by every production
> > RDBMS(MYSQL[1], ORACLE[2], SQLSERVER[3]), also some Hadoop data warehouse
> > facilitates like HIVE.
> >
> > As a query optimization engine of many comutation engines(like Apache
> > Flink) and some OLAP engines(like Apache Drill), Calcite would supply
> > better compatibility for sql query for the underlying engines it adapter
> > with with implicit type coercion. There are already some jira issues that
> > are relative with this topic more or less:
> >
> > 1. CALCITE-2992: Enhance implicit conversions when generating hash join
> > keys for an equiCondition
> > 2. CALCITE-3002: Case statement fails with: SqlValidatorException: Cannot
> > apply '=' to arguments of type '<INTEGER> = <BOOLEAN>'
> > 3. CALCITE-1531: SqlValidatorException when boolean operators are used
> > with NULL
> > 4. CALCITE-3081: https://issues.apache.org/jira/browse/CALCITE-3081
> > 5. CALCITE-2829: Use consistent types when processing ranges
> >
> > I have fired a issue CALCITE-2302 [4] about 1 year ago, with a design
> > doc(sowehow rough).
> >
> > Maybe we should fire a new discussion here, and hope for your suggesions
> :)
> >
> > [1] https://dev.mysql.com/doc/refman/5.5/en/type-conversion.html
> > [2]
> >
> https://docs.oracle.com/cd/B12037_01/server.101/b10759/sql_elements002.htm
> > [3]
> >
> https://docs.microsoft.com/en-us/sql/t-sql/data-types/data-type-conversion-database-engine?view=sql-server-2017
> > [4] https://issues.apache.org/jira/browse/CALCITE-2302
> > [5]
> >
> https://docs.google.com/document/d/1g2RUnLXyp_LjUlO-wbblKuP5hqEu3a_2Mt2k4dh6RwU/edit#heading=h.77f83nidn37j
> >
> > Best,
> > Danny Chan
> >
> >
>
