Thanks for your response, Zhu Feng, I totally agree with you. The first thing we should consider with implicit type coercion is to make it pluggable. In the original PR[1], I make implementation of different SqlNodes into separate methods, and we can inherent the TypeCoercion interface to make some extension for different Sql Dialect.
But I agree with you, we should make the Sql Dialect somehow bindable with different TypeCoercion implementations. So user can customize their transformation behaviors based on the Sql Dialect they use. [1] https://github.com/apache/calcite/pull/706 Best, Danny Chan 在 2019年5月27日 +0800 PM12:51，Zhu Feng <wellfeng...@gmail.com>，写道： > 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 <ykt...@gmail.com> 于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 <h.y...@alibaba-inc.com> > > 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<yuzhao....@gmail.com> > > > 日 期：2019年05月27日 10:20:47 > > > 收件人：<dev@calcite.apache.org> > > > 主 题：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 > > > > > > > >
