Thanks Danny for starting this discussion. This is a useful feature and let us push it forward. I would like to spend some time on the design doc and PR#706.
Best, Chunwei On Mon, May 27, 2019 at 3:24 PM Julian Hyde <[email protected]> wrote: > 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 <[email protected]> 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 <[email protected]>，写道： > >> 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 > >>>> > >>>> > >>> > >
