summary: SUBSTRING startposition < 1 should be allowed and handled as 1. => SUBSTRING startposition smaller than 1 should be allowed and handled as 1.

description: The following use of SUBSTRING is currently not allowed

select substring('abcdef' from 0) from rdb$database

It results in error "Invalid offset parameter -1 to SUBSTRING. Only positive integers are allowed."

However SQL:2011 says (6.30 <string value function>):

"""
3) If <character substring function> is specified, then:
[..]
b) Let C be the value of the <character value expression>, let LC be the length in characters of C, and let S be the value of the <start position>.
[..]
f) Case:
[..]
ii) Otherwise,
1) Let S1 be the larger of S and 1 (one). Let E1 be the smaller of E and LC+1. Let L1 be E1-S1.
2) The result of the <character substring function> is a character string containing the L1 characters of C starting at character number S1 in the same order that the characters appear in C.
"""

In other words, if <start position> < 1, then it should be handled as if it is 1.

Test case:

select substring('abcdef' from 0) from rdb$database

Expected result: 'abcdef'

=>

The following use of SUBSTRING is currently not allowed

select substring('abcdef' from 0) from rdb$database

It results in error "Invalid offset parameter -1 to SUBSTRING. Only positive integers are allowed."

However SQL:2011 says (6.30 <string value function>):

"""
3) If <character substring function> is specified, then:
[..]
b) Let C be the value of the <character value expression>, let LC be the length in characters of C, and let S be the value of the <start position>.
c) If <string length> is specified, then let L be the value of <string length> and let E be S+L. Otherwise, let E be the larger of LC + 1 and S.
[..]
e) If E is less than S, then an exception condition is raised: data exception — substring error.
f) Case:
i) If S is greater than LC or if E is less than 1 (one), then the result of the <character substring function> is a zero-length string.
ii) Otherwise,
1) Let S1 be the larger of S and 1 (one). Let E1 be the smaller of E and LC+1. Let L1 be E1-S1.
2) The result of the <character substring function> is a character string containing the L1 characters of C starting at character number S1 in the same order that the characters appear in C.
"""

In other words, if a <start position> < 1 it should still be allowed, and should be taken into account for determining the end of string value.

Test cases:

select substring('abcdef' from 0) from rdb$database
Expected result: 'abcdef'

select substring('abcdef' from 0 for 2) from rdb$database
Expected result: 'a' (and NOT 'ab')

select substring('abcdef' from -5 for 2) from rdb$database
Expected result: ''

summary: SUBSTRING startposition smaller than 1 should be allowed and handled as 1. => SUBSTRING startposition smaller than 1 should be allowed

assignee: Adriano dos Santos Fernandes [ asfernandes ]

status: Open [ 1 ] => Resolved [ 5 ]

resolution: Fixed [ 1 ]

Fix Version: 4.0 Beta 1 [ 10750 ]

status: Resolved [ 5 ] => Resolved [ 5 ]

QA Status: No test => Done successfully

status: Resolved [ 5 ] => Closed [ 6 ]