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 <danny0...@apache.org> 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
