[DERBY-7089] Discrepancy between documented and observed behavior of SUBSTR

• Type: Bug
• Status: Open
• Priority: Minor
• Resolution: Unresolved
• Affects Version/s: 10.15.2.0
• Component/s: Documentation
• Bug behavior facts: Seen in production
• Assignee: Unassigned
• Reporter: Piotr Zygielo
• Created: 24/Oct/20 16:33
• Updated: 27/Oct/20 12:06

Description

Per https://db.apache.org/derby/docs/10.15/ref/rrefsqlj93082.html:

The parameter startPosition and the optional parameter lengthOfString are both integer expressions. The first character or bit has a startPosition of 1. If you specify 0, Derby assumes that you mean 1.

If the startPosition is positive, it refers to position from the start of the source expression (counting the first character as 1). The startPosition cannot be a negative number.

So from the above startPosition = 0 is indeed allowed, right?

Yet, following happens:

ij> connect 'jdbc:derby:memory:apache;create=true';
...
Booting Derby version The Apache Software Foundation - Apache Derby - 10.15.2.0 - (1873585): instance a816c00e-0175-5b4b-9a39-0000070285d8
...
ij> values (substr('abcdef', 1));
1 
------
abcdef

1 row selected

ij> values (substr('abcdef', 0));
1 
------
ERROR 22011: The second or third argument of the SUBSTR function is out of range.

 

So 0 causes error 22011.

I am not sure if it's mistake in docs or error in code, as other DB vendors also allow 0 to be treated as 1 for startPosition, eg. Oracle 12c)