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 <jingsongl...@gmail.com> 于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 <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 >
