[IGNITE-23673] Calcite engine. Functions NVL and DECODE fail when used with parameters of different data types

• Type: Bug
• Status: Resolved
• Priority: Major
• Resolution: Fixed
• Fix Version/s: 2.17
• Labels: calcite ise
• Release Note: SQL Calcite: Fixed NVL/DECODE failure when used with parameters of different data types
• Ignite Flags: Release Notes Required
• Assignee: Aleksey Plekhanov
• Reporter: Aleksey Plekhanov
• Created: 14/Nov/24 09:19
• Updated: 5 days ago 06:42
• Resolved: 5 days ago 06:42

Description

For example, query:

select nvl('1', 2)

Fails with:

Cannot infer return type for NVL; operand types: [VARCHAR, INTEGER]

Query:

select decode('1', 1, '1', '2')

Fails with:

org.apache.ignite.IgniteCheckedException: while resolving method 'eq[class java.lang.String, int]' in class class org.apache.calcite.runtime.SqlFunctions

These functions are rewritten to the CASE-WHEN operator after the validation, but fail on validation. But if we use CASE-WHEN in the original query, it works well, types are casted correctly and validation passed:

select case when '1' is not null then '1' else 2 end
select case when '1' = 1 then '1' else '2' end

Issue Links

• is cloned by: IGNITE-23726 Calcite engine. Functions NVL and DECODE fail when used with parameters of different data types
• links to: GitHub Pull Request #11659