[IGNITE-19341] SQL: SUBSTRING function does not support NULL values (try 2)

• Type: Bug
• Status: Resolved
• Priority: Major
• Resolution: Fixed
• Affects Version/s: 3.0.0-beta1
• Fix Version/s: 3.0
• Component/s: sql
• Labels: calcite2-required ignite-3 sql
• Assignee: Evgeny Stanilovsky
• Reporter: Andrey Khitrin
• Created: 21/Apr/23 12:42
• Updated: 04/Sep/24 16:10
• Resolved: 09/Jun/23 08:31

Description

ANSI99 SQL specification ("6.18 <string value function>") says the following:

3) If <character substring function> is specified, then:
  a) Let C be the value of the <character value expression>, ..., and let S be the value of the <start position>.
  b) If <string length> is specified, then let L be the value of <string length> ...
  c) If either C, S, or L is the null value, then the result of the <character substring function> is the null value.

So, we should expect the following behavior:

SUBSTRING('text' FROM 1 FOR NULL) -> NULL
SUBSTRING('text' FROM NULL FOR 2) -> NULL
SUBSTRING(NULL FROM 1 FOR 2) -> NULL

Instead, we got errors for these queries:

sql-cli> SELECT SUBSTRING('text' FROM 1 FOR NULL);
SQL query execution error
Exception while executing query [query=SELECT SUBSTRING('text' FROM 1 FOR NULL);]. Error message:From line 1, column 8
to line 1, column 40: Cannot apply 'SUBSTRING' to arguments of type 'SUBSTRING(<CHAR(4)> FROM <INTEGER> FOR <NULL>)'. Supported form(s): 'SUBSTRING(<CHAR> FROM <INTEGER>)'
'SUBSTRING(<CHAR> FROM <INTEGER> FOR <INTEGER>)'
'SUBSTRING(<VARCHAR> FROM <INTEGER>)'
'SUBSTRING(<VARCHAR> FROM <INTEGER> FOR <INTEGER>)'
'SUBSTRING(<BINARY> FROM <INTEGER>)'
'SUBSTRING(<BINARY> FROM <INTEGER> FOR <INTEGER>)'
'SUBSTRING(<VARBINARY> FROM <INTEGER>)'
'SUBSTRING(<VARBINARY> FROM <INTEGER> FOR <INTEGER>)'

sql-cli> SELECT SUBSTRING('text' FROM NULL FOR 2);
SQL query execution error
Exception while executing query [query=SELECT SUBSTRING('text' FROM NULL FOR 2);]. Error message:From line 1, column 8
to line 1, column 40: Cannot apply 'SUBSTRING' to arguments of type 'SUBSTRING(<CHAR(4)> FROM <NULL> FOR <INTEGER>)'. Supported form(s): 'SUBSTRING(<CHAR> FROM <INTEGER>)'
'SUBSTRING(<CHAR> FROM <INTEGER> FOR <INTEGER>)'
'SUBSTRING(<VARCHAR> FROM <INTEGER>)'
'SUBSTRING(<VARCHAR> FROM <INTEGER> FOR <INTEGER>)'
'SUBSTRING(<BINARY> FROM <INTEGER>)'
'SUBSTRING(<BINARY> FROM <INTEGER> FOR <INTEGER>)'
'SUBSTRING(<VARBINARY> FROM <INTEGER>)'
'SUBSTRING(<VARBINARY> FROM <INTEGER> FOR <INTEGER>)'

Only such request works fine:

sql-cli> SELECT SUBSTRING(NULL FROM 1 FOR 2);
╔═════════╗
║ EXPR$0  ║
╠═════════╣
║ null    ║
╚═════════╝

Issue Links

• is a clone of: IGNITE-18488 SQL: SUBSTRING function does not support NULL values
• is fixed by: CALCITE-5708 SUBSTRING validation error if any parameter is a NULL literal
• links to: GitHub Pull Request #2159
• links to: GitHub Pull Request #2159