db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Dag H. Wanvik (JIRA)" <j...@apache.org>
Subject [jira] [Created] (DERBY-5957) 0 returned instead of NULL with correlated scalar subquery in select list
Date Fri, 19 Oct 2012 14:22:11 GMT
Dag H. Wanvik created DERBY-5957:
------------------------------------

             Summary: 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

Mime
View raw message