[
https://issues.apache.org/jira/browse/DERBY-7089?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17221017#comment-17221017
]
Richard N. Hillegas commented on DERBY-7089:
--------------------------------------------
Thanks for bringing this issue to our attention. SUBSTR is not defined by the
SQL Standard. The Standard equivalent is a more verbose SUBSTRING function.
Derby's behavior looks correct to me even if it doesn't agree with the behavior
of other databases. I am classifying this issue as a documentation bug. Thanks.
> 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
> Components: Documentation
> Affects Versions: 10.15.2.0
> Reporter: Piotr Zygielo
> Priority: Minor
>
> 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)
