You are viewing a plain text version of this content. The canonical link for it is here.
Posted to derby-dev@db.apache.org by "Dag H. Wanvik (JIRA)" <ji...@apache.org> on 2012/10/19 16:22:11 UTC
[jira] [Created] (DERBY-5957) 0 returned instead of NULL with
correlated scalar subquery in select list
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
[jira] [Resolved] (DERBY-5957) 0 returned instead of NULL with
correlated scalar subquery in select list
Posted by "Dag H. Wanvik (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-5957?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Dag H. Wanvik resolved DERBY-5957.
----------------------------------
Resolution: Invalid
Bug behavior facts: (was: Deviation from standard,Wrong query result)
Issue & fix info: (was: Repro attached)
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
[jira] [Updated] (DERBY-5957) 0 returned instead of NULL with
correlated scalar subquery in select list
Posted by "Dag H. Wanvik (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-5957?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Dag H. Wanvik updated DERBY-5957:
---------------------------------
Issue & fix info: Repro attached
> 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
[jira] [Comment Edited] (DERBY-5957) 0 returned instead of NULL
with correlated scalar subquery in select list
Posted by "Dag H. Wanvik (JIRA)" <ji...@apache.org>.
[ 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