calcite-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Julian Hyde <jh...@apache.org>
Subject Re: Support complete implicit type coercion (DISCUSSION)
Date Mon, 27 May 2019 07:15:59 GMT
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.cyz@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 <wellfengzhu@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 <ykt836@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.yuan@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.cyz@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
>>>> 
>>>> 
>>> 


Mime
View raw message