[DISCUSS] FLIP-154: SQL Implicit Type Coercion

[Danny Chan-2]

Hello, fellows, long time no see ~

Here i want to fire a discussion about the SQL implicit type coercion, a
required feature for SQL production but missed for Flink SQL for a long
time ~

SQL implicit type coercion is very useful for these cases:

- the inter-operation within builtin SQL operators, like binary arithmetic
operations (PLUS, MINOUS and so on), binary comparison, IN, CASE WHEN, SET
operator (UNION, INTERSECT ..)
- the built in functions type coercion for input parameters
- the SQL INSERT source that target connector row type when are are row
elements type mismatch
- the compatibility with other popular DB engines, like
MySQL/Hive/PostgreSQL and so on

And so much voices for a long time from the user mailing list.

I have created a FLIP WIKI here [1] and wait for your
appreciate suggestions ~

Thanks again for all you awesome guys and hope that we can have a
conclusion for the type coercion rules (the matrix) for Flink SQL soon.

[1]
https://cwiki.apache.org/confluence/display/FLINK/FLIP-154%3A+SQL+Implicit+Type+Coercion

Best,
Danny Chan

[Jingsong Li]

Thanks Danny for starting this discussion.

Big +1 for Implicit Type Coercion, in my opinion, it is very useful for
writing SQL conveniently.

I think there are two orthogonal things to discuss here:
1.[Matrix] Which types and which types can be implicitly converted.
2.[Strategies] In different cases, the implicit conversion is which type
converts to which type.

For #1, We may be able to choose a matrix table that allows more
conversions.

For #2, We need to be careful, we should be closer to standard SQL, which
means we should probably be closer to postgreSQL and MS-SQL.
About Strategies, what do you think of calcite's behavior now? Similar to
MS-SQL?
For example, there seems to be 2 style type coercions for binary arithmetic
with strings:
- For MySQL and Oracle style: coerce all the STRING operand to DOUBLE type
- PostgreSQL and SQL-SERVER style: coerce the STRING operand to the type of
the other operand(if it is a NUMERIC)

I think you can list the core differences between calcite and other
databases in terms of the above two aspects, which is not only conducive to
our discussion, but also conducive to the perception of users in the future.

BTW, it seems that the Type Conversion Matrix chapter in the FLIP does not
list the behavior of calcite, can you finish that?

Best,
Jingsong

On Tue, Mar 9, 2021 at 6:26 PM Danny Chan <[hidden email]> wrote:

> Hello, fellows, long time no see ~
>
> Here i want to fire a discussion about the SQL implicit type coercion, a
> required feature for SQL production but missed for Flink SQL for a long
> time ~
>
> SQL implicit type coercion is very useful for these cases:
>
> - the inter-operation within builtin SQL operators, like binary arithmetic
> operations (PLUS, MINOUS and so on), binary comparison, IN, CASE WHEN, SET
> operator (UNION, INTERSECT ..)
> - the built in functions type coercion for input parameters
> - the SQL INSERT source that target connector row type when are are row
> elements type mismatch
> - the compatibility with other popular DB engines, like
> MySQL/Hive/PostgreSQL and so on
>
> And so much voices for a long time from the user mailing list.
>
> I have created a FLIP WIKI here [1] and wait for your
> appreciate suggestions ~
>
> Thanks again for all you awesome guys and hope that we can have a
> conclusion for the type coercion rules (the matrix) for Flink SQL soon.
>
> [1]
>
> https://cwiki.apache.org/confluence/display/FLINK/FLIP-154%3A+SQL+Implicit+Type+Coercion
>
> Best,
> Danny Chan
>

--
Best, Jingsong Lee

[Danny Chan-2]

Thanks for the feedback, Jingsong ~

This design mainly follows the behaviors of PostgreSQL and SQL-SERVER,
because their rules are more in line with the SQL standard.

I have fixed the WIKI and add more details about the diff in it.

Best,
Danny Chan

Jingsong Li <[hidden email]> 于2021年3月11日周四 下午1:34写道：

> Thanks Danny for starting this discussion.
>
> Big +1 for Implicit Type Coercion, in my opinion, it is very useful for
> writing SQL conveniently.
>
> I think there are two orthogonal things to discuss here:
> 1.[Matrix] Which types and which types can be implicitly converted.
> 2.[Strategies] In different cases, the implicit conversion is which type
> converts to which type.
>
> For #1, We may be able to choose a matrix table that allows more
> conversions.
>
> For #2, We need to be careful, we should be closer to standard SQL, which
> means we should probably be closer to postgreSQL and MS-SQL.
> About Strategies, what do you think of calcite's behavior now? Similar to
> MS-SQL?
> For example, there seems to be 2 style type coercions for binary arithmetic
> with strings:
> - For MySQL and Oracle style: coerce all the STRING operand to DOUBLE type
> - PostgreSQL and SQL-SERVER style: coerce the STRING operand to the type of
> the other operand(if it is a NUMERIC)
>
> I think you can list the core differences between calcite and other
> databases in terms of the above two aspects, which is not only conducive to
> our discussion, but also conducive to the perception of users in the
> future.
>
> BTW, it seems that the Type Conversion Matrix chapter in the FLIP does not
> list the behavior of calcite, can you finish that?
>
> Best,
> Jingsong
>
> On Tue, Mar 9, 2021 at 6:26 PM Danny Chan <[hidden email]> wrote:
>
> > Hello, fellows, long time no see ~
> >
> > Here i want to fire a discussion about the SQL implicit type coercion, a
> > required feature for SQL production but missed for Flink SQL for a long
> > time ~
> >
> > SQL implicit type coercion is very useful for these cases:
> >
> > - the inter-operation within builtin SQL operators, like binary
> arithmetic
> > operations (PLUS, MINOUS and so on), binary comparison, IN, CASE WHEN,
> SET
> > operator (UNION, INTERSECT ..)
> > - the built in functions type coercion for input parameters
> > - the SQL INSERT source that target connector row type when are are row
> > elements type mismatch
> > - the compatibility with other popular DB engines, like
> > MySQL/Hive/PostgreSQL and so on
> >
> > And so much voices for a long time from the user mailing list.
> >
> > I have created a FLIP WIKI here [1] and wait for your
> > appreciate suggestions ~
> >
> > Thanks again for all you awesome guys and hope that we can have a
> > conclusion for the type coercion rules (the matrix) for Flink SQL soon.
> >
> > [1]
> >
> >
> https://cwiki.apache.org/confluence/display/FLINK/FLIP-154%3A+SQL+Implicit+Type+Coercion
> >
> > Best,
> > Danny Chan
> >
>
>
> --
> Best, Jingsong Lee
>

[Jingsong Li]

Thanks Danny for your update, the FLIP page looks very good now.

Flink already has some implicit casting rules in LogicalTypeCasts[1]. The
biggest difference is that the previous implicit casting is strictly
limited to the safety, "Implicit casts are used for safe type widening and
type generalization (finding a common supertype for a set of types) without
loss of information".

Now in this FLIP, we will bring various castings of char/varchar, which
means that the casting may fail, like '9xsa' / 2 (INT), maybe output a
NULL. Considering that it has been well supported by other popular
databases. I think it is OK.

What do you think? @Timo Walther <[hidden email]>

[1]
https://github.com/apache/flink/blob/97bfd049951f8d52a2e0aed14265074c4255ead0/flink-table/flink-table-common/src/main/java/org/apache/flink/table/types/logical/utils/LogicalTypeCasts.java

Best,
Jingsong

On Thu, Mar 11, 2021 at 3:41 PM Danny Chan <[hidden email]> wrote:

> Thanks for the feedback, Jingsong ~
>
> This design mainly follows the behaviors of PostgreSQL and SQL-SERVER,
> because their rules are more in line with the SQL standard.
>
> I have fixed the WIKI and add more details about the diff in it.
>
> Best,
> Danny Chan
>
> Jingsong Li <[hidden email]> 于2021年3月11日周四 下午1:34写道：
>
> > Thanks Danny for starting this discussion.
> >
> > Big +1 for Implicit Type Coercion, in my opinion, it is very useful for
> > writing SQL conveniently.
> >
> > I think there are two orthogonal things to discuss here:
> > 1.[Matrix] Which types and which types can be implicitly converted.
> > 2.[Strategies] In different cases, the implicit conversion is which type
> > converts to which type.
> >
> > For #1, We may be able to choose a matrix table that allows more
> > conversions.
> >
> > For #2, We need to be careful, we should be closer to standard SQL, which
> > means we should probably be closer to postgreSQL and MS-SQL.
> > About Strategies, what do you think of calcite's behavior now? Similar to
> > MS-SQL?
> > For example, there seems to be 2 style type coercions for binary
> arithmetic
> > with strings:
> > - For MySQL and Oracle style: coerce all the STRING operand to DOUBLE
> type
> > - PostgreSQL and SQL-SERVER style: coerce the STRING operand to the type
> of
> > the other operand(if it is a NUMERIC)
> >
> > I think you can list the core differences between calcite and other
> > databases in terms of the above two aspects, which is not only conducive
> to
> > our discussion, but also conducive to the perception of users in the
> > future.
> >
> > BTW, it seems that the Type Conversion Matrix chapter in the FLIP does
> not
> > list the behavior of calcite, can you finish that?
> >
> > Best,
> > Jingsong
> >
> > On Tue, Mar 9, 2021 at 6:26 PM Danny Chan <[hidden email]> wrote:
> >
> > > Hello, fellows, long time no see ~
> > >
> > > Here i want to fire a discussion about the SQL implicit type coercion,
> a
> > > required feature for SQL production but missed for Flink SQL for a long
> > > time ~
> > >
> > > SQL implicit type coercion is very useful for these cases:
> > >
> > > - the inter-operation within builtin SQL operators, like binary
> > arithmetic
> > > operations (PLUS, MINOUS and so on), binary comparison, IN, CASE WHEN,
> > SET
> > > operator (UNION, INTERSECT ..)
> > > - the built in functions type coercion for input parameters
> > > - the SQL INSERT source that target connector row type when are are row
> > > elements type mismatch
> > > - the compatibility with other popular DB engines, like
> > > MySQL/Hive/PostgreSQL and so on
> > >
> > > And so much voices for a long time from the user mailing list.
> > >
> > > I have created a FLIP WIKI here [1] and wait for your
> > > appreciate suggestions ~
> > >
> > > Thanks again for all you awesome guys and hope that we can have a
> > > conclusion for the type coercion rules (the matrix) for Flink SQL soon.
> > >
> > > [1]
> > >
> > >
> >
> https://cwiki.apache.org/confluence/display/FLINK/FLIP-154%3A+SQL+Implicit+Type+Coercion
> > >
> > > Best,
> > > Danny Chan
> > >
> >
> >
> > --
> > Best, Jingsong Lee
> >
>

--
Best, Jingsong Lee