[ https://issues.apache.org/jira/browse/IGNITE-19341?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Evgeny Stanilovsky reassigned IGNITE-19341: ------------------------------------------- Assignee: Evgeny Stanilovsky > SQL: SUBSTRING function does not support NULL values (try 2) > ------------------------------------------------------------ > > Key: IGNITE-19341 > URL: https://issues.apache.org/jira/browse/IGNITE-19341 > Project: Ignite > Issue Type: Bug > Components: sql > Affects Versions: 3.0.0-beta1 > Reporter: Andrey Khitrin > Assignee: Evgeny Stanilovsky > Priority: Major > Labels: calcite2-required, ignite-3, sql > > ANSI99 SQL specification ("6.18 <string value function>") says the following: > {code:java} > 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. > {code} > So, we should expect the following behavior: > {code:sql} > SUBSTRING('text' FROM 1 FOR NULL) -> NULL > SUBSTRING('text' FROM NULL FOR 2) -> NULL > SUBSTRING(NULL FROM 1 FOR 2) -> NULL > {code} > Instead, we got errors for these queries: > {code:sql} > 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>)' > {code} > Only such request works fine: > {code:sql} > sql-cli> SELECT SUBSTRING(NULL FROM 1 FOR 2); > ╔═════════╗ > ║ EXPR$0 ║ > ╠═════════╣ > ║ null ║ > ╚═════════╝ > {code} -- This message was sent by Atlassian Jira (v8.20.10#820010)