[IGNITE-21134] Sql. UPPER, LOWER and SUBSTRING functions must support NULL values

• Type: Bug
• Status: Resolved
• Priority: Major
• Resolution: Fixed
• Affects Version/s: 3.0.0-beta2
• Fix Version/s: 3.0.0-beta2
• Component/s: clients, sql
• Labels: ignite-3
• Assignee: Evgeny Stanilovsky
• Reporter: Andrey Khitrin
• Created: 21/Dec/23 14:24
• Updated: 18/Jan/24 06:14
• Resolved: 18/Jan/24 06:14

Description

As described in ANSI99 specification, functions UPPER and LOWER must return NULL values for NULL argument:

 

5) If <fold> is specified, then:

 .. b) if S is the null value, then the result of the <fold> is the null value.

In the recent AI3 (commit c2ac5850973ae3bfd44b06fc6e3b5880f9f292f1) an error is shown instead:

sql-cli> SELECT UPPER(NULL);
Unknown error
Unsupported Column type NULL

sql-cli> SELECT LOWER(NULL);
Unknown error
Unsupported Column type NULL

sql-cli> SELECT SUBSTRING(NULL FROM 1 FOR 2);
Unknown error
Unsupported Column type NULL

NOTE: Most probably, it may be caused by client module, not sql module because a text of error is located in client-common module:

$ grep -r 'Unsupported Column type' modules/*/src/main

modules/client-common/src/main/java/org/apache/ignite/internal/jdbc/JdbcConverterUtils.java:                throw new IllegalArgumentException("Unsupported Column type " + columnType);

And when I add the following assertion to some test in ItFunctionsTest, it passes:

        assertQuery("SELECT LOWER(NULL)").returns(null).check();

Previous AI3 versions allowed using NULL values in UPPER and LOWER functions, hence it's a degradation.

Issue Links

• links to: GitHub Pull Request #3034
• links to: GitHub Pull Request #3034