Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

SUBSTRING startposition smaller than 1 should be allowed [CORE5480] #5750

Closed
firebird-issue-importer opened this issue Feb 11, 2017 · 9 comments
Closed

Comments

@firebird-issue-importer

Submitted by: @mrotteveel

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: ''

Commits: cec00e7 b993ed2

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Feb 11, 2017

Commented by: @mrotteveel

As a datapoint: PostgreSQL does this (start position < 1 is handled as 1)

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Feb 11, 2017

Modified by: @mrotteveel

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

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Feb 11, 2017

Commented by: @mrotteveel

I have updated the ticket with a corrected version of the requirements when using SUBSTRING with a start position smaller than 1; I initially incorrectly stated that it should be handled exactly as if start position = 1.

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Feb 11, 2017

Modified by: @mrotteveel

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

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Feb 28, 2017

Modified by: @asfernandes

assignee: Adriano dos Santos Fernandes [ asfernandes ]

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Mar 1, 2017

Modified by: @asfernandes

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

resolution: Fixed [ 1 ]

Fix Version: 4.0 Beta 1 [ 10750 ]

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Mar 4, 2017

Commented by: @mrotteveel

I did some tests on Firebird-4.0.0.546 and it works as I requested and expected. Thanks.

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Mar 4, 2017

Modified by: @pavel-zotov

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

QA Status: No test => Done successfully

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Mar 4, 2017

Modified by: @pavel-zotov

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

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Projects
None yet
Linked pull requests

Successfully merging a pull request may close this issue.

None yet
2 participants