[jira] [Commented] (DERBY-7089) Discrepancy between documented and observed behavior of SUBSTR

["Richard N. Hillegas (Jira)" <ji...@apache.org>]

    [ 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)