[CALCITE-2302] Implicit type cast support

• Type: Improvement
• Status: Closed
• Priority: Critical
• Resolution: Fixed
• Affects Version/s: 1.17.0
• Fix Version/s: 1.21.0
• Component/s: core
• Labels: pull-request-available
• Assignee: Danny Chen
• Reporter: Danny Chen
• Created: 07/May/18 08:38
• Updated: 25/May/22 08:07
• Resolved: 29/Aug/19 09:10

Description

Now many DBs have support implicit type cast, eg: SqlServer, Oracle, Hive.

Implicit type cast is an useful function for many cases, So we should support this.

I checkout Calcite code and found that:

1. Now we use a validator to validate our operands types[ through kinds of namespaces and scopes ]
2. Most of the validations will finally goes to

   SqlOperator.validateOperands
   

1. which will use validation logic defined in corresponding SqlOperandTypeChecker

What i'm confused about is where should i put the implicit type cast logic in? I figured out 2 ways:

1. Supply a tool class/rules to add casts into a parsed SqlNode tree which will then go through the validation logic later on.
2. Unleash the validation logic in kinds of SqlOperandTypeChecker, then modify the RelNode/RexNodes tree converted from a validated SqlNode tree to add in casts through custom RelOptRules.

So guys, which of the 2 ways should i go, or if there are better way to do this?

I need your help.

 

Updated 18-05-30:

Hi guys, i have made a PR in CALCITE-2302

This is design doc: Calcite Implicit Type Cast Design.

This is the type conversion matrix: Type Conversion Matrix.

I really appreciate your suggestions, thx.

Issue Links

• causes: CALCITE-3315 Multiple failures in Druid IT tests due to implicit casts
• causes: CALCITE-3360 SqlValidator throws NPE for unregistered function without implicit type coercion
• contains: CALCITE-2829 Use consistent types when processing ranges
• contains: CALCITE-3217 Support "SELECT NULL"
• is related to: CALCITE-2921 Allow NULL as argument to function calls
• is related to: CALCITE-3326 SQL with invalid function throws NullPointerException if typeCoercion is disabled
• relates to: CALCITE-2745 RexSimplify fails with ClassCastException when the expression contains comparisons of operands with different types
• relates to: CALCITE-5156 Support implicit integer type cast for IN Sub-query
• relates to: CALCITE-2325 Calcite can not verify a explicit cast column in some scenario
• relates to: CALCITE-3327 Simplify SqlValidatorTest by replacing 'boolean typeCoercion' parameter with fluent method 'Sql.withTypeCoercion(boolean)'
• relates to: CALCITE-3423 Support using CAST operation and BOOLEAN type value in table macro
• relates to: CALCITE-3206 Better error message when param type incompatibility
• relates to: CALCITE-3331 Support implicit type cast for operators that use single operand family checker
• links to: Design of Calcite Implicit Type Cast
• links to: GitHub Pull Request #706
• links to: PR706

Sub-Tasks

• Returns integer for VARCHAR and INT division of PostgreSql dialect: Closed
• Add doc to site for implicit type coercion: Closed