Thanks for starting this discussion. I agree that implicit type coercion will make Calcite (and systems derived from it) easier to use, and we should do it.
I also agree that it should be “plugggable”, in several senses: * First, it should be possible to disable all implicit coercions, and adopt the current behavior based on the SQL standard. * Second, where there are differences in semantics in major existing systems, users should be able to choose which semantics they want. * Third, we should make the behavior modular. The existing interfaces (SqlReturnTypeInference, SqlOperandTypeInference, SqlOperandTypeChecker) have been very successful, and we should follow that general pattern as we implement implicit type coercion. Julian > On May 26, 2019, at 11:39 PM, Yuzhao Chen <yuzhao....@gmail.com> wrote: > > 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 >>>> >>>> >>>
