[IGNITE-19997] Sql. Enhancing test coverage of type coercion

• Type: Improvement
• Status: Open
• Priority: Major
• Resolution: Unresolved
• Component/s: sql
• Labels: ignite-3
• Epic Link: Sql. Extend test coverage for SQL-standard features. Phase 2
• Assignee: Unassigned
• Reporter: Konstantin Orlov
• Created: 18/Jul/23 11:49
• Updated: 11/Jun/24 12:07

Description

Currently, we have neither an understanding of how type coercion impacts the system, nor decent test coverage. As a result, trying to fix one part causes the other to break (see IGNITE-19128 --> IGNITE-19615 --> IGNITE-19976).

To improve the situation, let us perform the following steps:

• first, it is necessary to cover the current behavior of planner with an exhaustive set of tests:. Here, we need to cover following cases
  • coercion for set operations like UNION, INTERSECT and EXCEPT
  • coercion for binary comparison and arithmetic
  • coercion for IN operator
  • coercion for CASE operator
  • coercion for function arguments
  • coercion of source for INSERT, UPDATE and MERGE operators
  • etc (did I miss something?)
• then, we need to cover execution by the similar tests to make sure coercion and execution are aligned
• finally, we should revise coercion rules to remove unnecessary casts

Issue Links

• is related to: IGNITE-22462 Sql. Add planner test to verify type coercion for set operations like UNION, INTERSECT and EXCEPT
• relates to: IGNITE-18644 Sql. Type system. Types in predicate expressions may have unnecessary casts.
• relates to: IGNITE-19882 Sql. Queries that access columns of type DECIMAL do not pick index, when compared with INT/BIGINT.
• relates to: IGNITE-19931 Sql. Varbinary. Index is not selected for key = '1'::VARBINARY
• relates to: IGNITE-19128 Sql. Custom data types. IgniteIndexScan has incorrect types in searchBounds.
• relates to: IGNITE-19615 Index is not used while performing SELECT over an indexed column
• relates to: IGNITE-19976 Sql: ClassCastException when trying to select from indexed smallint column
• relates to: IGNITE-19999 Sql. Add planner test to verify numeric type coercion in binary comparison
• relates to: IGNITE-20000 Sql. Add planner test to verify numeric type coercion in binary arithmetic