Piotr Zygielo created DERBY-7089:
------------------------------------
Summary: Discrepancy between documented and observed behavior of
SUBSTR
Key: DERBY-7089
URL: https://issues.apache.org/jira/browse/DERBY-7089
Project: Derby
Issue Type: Bug
Affects Versions: 10.15.2.0
Reporter: Piotr Zygielo
Per [https://db.apache.org/derby/docs/10.15/ref/rrefsqlj93082.html:]
{quote}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.
{quote}
{quote}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.
{quote}
So from the above {{startPosition = 0}} is indeed allowed, right?
Yet, following happens:
{noformat}
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.
{noformat}
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|https://docs.oracle.com/database/121/SQLRF/functions196.htm#SQLRF06114])
--
This message was sent by Atlassian Jira
(v8.3.4#803005)
