calcite-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Chunwei Lei <chunwei.l...@gmail.com>
Subject Re: Support complete implicit type coercion (DISCUSSION)
Date Mon, 27 May 2019 07:45:21 GMT
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 <jhyde@apache.org> 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 <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
  • Unnamed multipart/alternative (inline, None, 0 bytes)
View raw message