calcite-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Yuzhao Chen <yuzhao....@gmail.com>
Subject Re: Support complete implicit type coercion (DISCUSSION)
Date Wed, 29 May 2019 06:19:15 GMT
Thanks Julian,

I’m glad that we can push this forward.

Some thoughts about making this feature pluggable:


• For function disable: For the current design draft, I have added a flag in SqlValidator
so use can enable/disable all the implicit coercions.
• For adopting to different sql dialects: It should have different semantics for user to
adopt to their semantics they want, for the first PR, i only make a default one, but I believe
we can add more for major SqlConformance
• For making the behavior modular: For current design I split different coercion rules into
kinds of method  implementations of interface TypeCoercion, for following the pattern of
(SqlReturnTypeInference, SqlOperandTypeInference, SqlOperandTypeChecker), do you mean I need
to package these rules into different components ? (So we can move them around for reusing)

I firstly designed the TypeCoercion component like pattern RelDataTypeFactory, so user can
extend and tweak the logic though inheritance or extending the default impl( say different
SQL dialect may have different TypeCoercion instances).

Best,
Danny Chan
在 2019年5月27日 +0800 PM3:24,Julian Hyde <jhyde@apache.org>,写道:
> 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