Mailing-List: contact derby-dev-help@db.apache.org; run by ezmlm
Precedence: bulk
Reply-To: <derby-dev@db.apache.org>
Date: Fri, 19 Oct 2012 15:38:11 +0000 (UTC)
From: "Dag H. Wanvik (JIRA)" <jira@apache.org>
To: derby-dev@db.apache.org
Message-ID: <327865155.1194.1350661091997.JavaMail.jiratomcat@arcas>
In-Reply-To: <465327760.946.1350656532325.JavaMail.jiratomcat@arcas>
Subject: [jira] [Comment Edited] (DERBY-5957) 0 returned instead of NULL
 with correlated scalar subquery in select list
MIME-Version: 1.0
Content-Type: text/plain; charset=utf-8
Content-Transfer-Encoding: 7bit


    [ https://issues.apache.org/jira/browse/DERBY-5957?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=13480077#comment-13480077 ] 

Dag H. Wanvik edited comment on DERBY-5957 at 10/19/12 3:37 PM:
----------------------------------------------------------------

Closing as invalid: pilot error; the "0" came from ResultSet#getInt; checking with ResultSet#wasNull shows its really NULL. Safer to use getString :-)
                
      was (Author: dagw):
    Closing as invalid: pilot error; the "0" came from ResultSet#getInt; checking with ResultSet#wasNull shows its really NULL. Safer to user getString :-)
                  
> 0 returned instead of NULL with correlated scalar subquery in select list
> -------------------------------------------------------------------------
>
>                 Key: DERBY-5957
>                 URL: https://issues.apache.org/jira/browse/DERBY-5957
>             Project: Derby
>          Issue Type: Bug
>          Components: SQL
>    Affects Versions: 10.9.1.0
>            Reporter: Dag H. Wanvik
>
> In DERBY-5954 we saw an example query that returned 0 instead of NULL if a correlated scalar subquery in a select list didn't find a row, and another column in the select list has a values, i.e.:
> s.executeUpdate("create table derby5954( a int )");
> s.executeUpdate("insert into derby5954 values 1,2,3,4,5,6,7");
> ResultSet rs = s.executeQuery(
>         "SELECT a_t1, (" + 
>         "     SELECT a_t2 FROM (" + 
>         "         SELECT a as a_t2 FROM derby5954) " + 
>         "         as T2" +
>         "         where T2.a_t2 = T1.a_t1 + 1  ) " + 
>         "     as a_outer" + 
>         "     FROM (SELECT a as a_t1 from derby5954) as T1");
> gives
> 1 2
> 2 3
> 3 4
> 4 5
> 5 6
> 6 7
> 7 0
> where the last row's second column should be NULL, not "0"., since in the predicate T2.a_t2 = T1.a_t1 + 1
> there will be no T2.a_t2 which matches 7 (T1.a_t1's last row) +  1.

--
This message is automatically generated by JIRA.
If you think it was sent incorrectly, please contact your JIRA administrators
For more information on JIRA, see: http://www.atlassian.com/software/jira