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 "hleem (Created) (JIRA)" <ji...@apache.org> on 2011/12/23 21:28:30 UTC
[jira] [Created] (DERBY-5556) NULLIF and CASE return different
result
NULLIF and CASE return different result
---------------------------------------
Key: DERBY-5556
URL: https://issues.apache.org/jira/browse/DERBY-5556
Project: Derby
Issue Type: Bug
Components: SQL
Affects Versions: 10.8.1.2
Environment: $ uname -a
Linux hleemus 3.1.4-1-ARCH #1 SMP PREEMPT Tue Nov 29 08:55:45 CET 2011 x86_64 AMD Athlon(tm) 64 X2 Dual Core Processor 5000+ AuthenticAMD GNU/Linux
$ java -version
java version "1.6.0_22"
Java(TM) SE Runtime Environment (build 1.6.0_22-b04)
Java HotSpot(TM) 64-Bit Server VM (build 17.1-b03, mixed mode)
$ java -Dij.database=jdbc:derby:dbase -classpath /home/hleem/.m2/repository/org/apache/derby/derbytools/10.8.1.2/derbytools-10.8.1.2.jar:/home/hleem/.m2/repository/org/apache/derby/derby/10.8.1.2/derby-10.8.1.2.jar org.apache.derby.tools.ij
ij version 10.8
CONNECTION0* - jdbc:derby:dbase
* = current connection
ij> select pr_parent FROM SDB.PARAMS;
PR_PARENT
-----------
10
10
10
NULL
4 rows selected
ij> select case when pr_parent is null then 0 else pr_parent end FROM SDB.PARAMS;
1
-----------
10
10
10
0
4 rows selected
ij> select nullif(pr_parent,0) FROM SDB.PARAMS;
1
-----------
10
10
10
NULL
4 rows selected
ij> exit;
$
Reporter: hleem
NULLIF and CASE return different result with null INTEGER value.
'pr_parent' in query is INTEGER type.
--
This message is automatically generated by JIRA.
If you think it was sent incorrectly, please contact your JIRA administrators: https://issues.apache.org/jira/secure/ContactAdministrators!default.jspa
For more information on JIRA, see: http://www.atlassian.com/software/jira
[jira] [Commented] (DERBY-5556) NULLIF and CASE return different
result
Posted by "hleem (Commented) (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-5556?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=13175776#comment-13175776 ]
hleem commented on DERBY-5556:
------------------------------
I have a difficult transition from ms sql. I confused isnull() from mssql with nullif. :(
Sorry for the noise in bugtracker.
> NULLIF and CASE return different result
> ---------------------------------------
>
> Key: DERBY-5556
> URL: https://issues.apache.org/jira/browse/DERBY-5556
> Project: Derby
> Issue Type: Bug
> Components: SQL
> Affects Versions: 10.8.1.2
> Environment: $ uname -a
> Linux hleemus 3.1.4-1-ARCH #1 SMP PREEMPT Tue Nov 29 08:55:45 CET 2011 x86_64 AMD Athlon(tm) 64 X2 Dual Core Processor 5000+ AuthenticAMD GNU/Linux
> $ java -version
> java version "1.6.0_22"
> Java(TM) SE Runtime Environment (build 1.6.0_22-b04)
> Java HotSpot(TM) 64-Bit Server VM (build 17.1-b03, mixed mode)
> $ java -Dij.database=jdbc:derby:dbase -classpath /home/hleem/.m2/repository/org/apache/derby/derbytools/10.8.1.2/derbytools-10.8.1.2.jar:/home/hleem/.m2/repository/org/apache/derby/derby/10.8.1.2/derby-10.8.1.2.jar org.apache.derby.tools.ij
> ij version 10.8
> CONNECTION0* - jdbc:derby:dbase
> * = current connection
> ij> select pr_parent FROM SDB.PARAMS;
> PR_PARENT
> -----------
> 10
> 10
> 10
> NULL
> 4 rows selected
> ij> select case when pr_parent is null then 0 else pr_parent end FROM SDB.PARAMS;
> 1
> -----------
> 10
> 10
> 10
> 0
> 4 rows selected
> ij> select nullif(pr_parent,0) FROM SDB.PARAMS;
> 1
> -----------
> 10
> 10
> 10
> NULL
> 4 rows selected
> ij> exit;
> $
> Reporter: hleem
> Labels: case, nullif
>
> NULLIF and CASE return different result with null INTEGER value.
> 'pr_parent' in query is INTEGER type.
--
This message is automatically generated by JIRA.
If you think it was sent incorrectly, please contact your JIRA administrators: https://issues.apache.org/jira/secure/ContactAdministrators!default.jspa
For more information on JIRA, see: http://www.atlassian.com/software/jira
[jira] [Commented] (DERBY-5556) NULLIF and CASE return different
result
Posted by "Knut Anders Hatlen (Commented) (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-5556?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=13175771#comment-13175771 ]
Knut Anders Hatlen commented on DERBY-5556:
-------------------------------------------
The query results look correct two me. The two queries are not equivalent, and I believe they should return different results in this case. The reference manual describes the relationship between NULLIF and CASE here: http://db.apache.org/derby/docs/10.8/ref/rrefcasenullif.html. You'll see that the equivalent CASE expression mentioned there is a little different from the one you used in your query.
Perhaps you meant to use COALESCE instead of NULLIF?
> NULLIF and CASE return different result
> ---------------------------------------
>
> Key: DERBY-5556
> URL: https://issues.apache.org/jira/browse/DERBY-5556
> Project: Derby
> Issue Type: Bug
> Components: SQL
> Affects Versions: 10.8.1.2
> Environment: $ uname -a
> Linux hleemus 3.1.4-1-ARCH #1 SMP PREEMPT Tue Nov 29 08:55:45 CET 2011 x86_64 AMD Athlon(tm) 64 X2 Dual Core Processor 5000+ AuthenticAMD GNU/Linux
> $ java -version
> java version "1.6.0_22"
> Java(TM) SE Runtime Environment (build 1.6.0_22-b04)
> Java HotSpot(TM) 64-Bit Server VM (build 17.1-b03, mixed mode)
> $ java -Dij.database=jdbc:derby:dbase -classpath /home/hleem/.m2/repository/org/apache/derby/derbytools/10.8.1.2/derbytools-10.8.1.2.jar:/home/hleem/.m2/repository/org/apache/derby/derby/10.8.1.2/derby-10.8.1.2.jar org.apache.derby.tools.ij
> ij version 10.8
> CONNECTION0* - jdbc:derby:dbase
> * = current connection
> ij> select pr_parent FROM SDB.PARAMS;
> PR_PARENT
> -----------
> 10
> 10
> 10
> NULL
> 4 rows selected
> ij> select case when pr_parent is null then 0 else pr_parent end FROM SDB.PARAMS;
> 1
> -----------
> 10
> 10
> 10
> 0
> 4 rows selected
> ij> select nullif(pr_parent,0) FROM SDB.PARAMS;
> 1
> -----------
> 10
> 10
> 10
> NULL
> 4 rows selected
> ij> exit;
> $
> Reporter: hleem
> Labels: case, nullif
>
> NULLIF and CASE return different result with null INTEGER value.
> 'pr_parent' in query is INTEGER type.
--
This message is automatically generated by JIRA.
If you think it was sent incorrectly, please contact your JIRA administrators: https://issues.apache.org/jira/secure/ContactAdministrators!default.jspa
For more information on JIRA, see: http://www.atlassian.com/software/jira
[jira] [Closed] (DERBY-5556) NULLIF and CASE return different
result
Posted by "Knut Anders Hatlen (Closed) (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-5556?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Knut Anders Hatlen closed DERBY-5556.
-------------------------------------
Resolution: Invalid
> NULLIF and CASE return different result
> ---------------------------------------
>
> Key: DERBY-5556
> URL: https://issues.apache.org/jira/browse/DERBY-5556
> Project: Derby
> Issue Type: Bug
> Components: SQL
> Affects Versions: 10.8.1.2
> Environment: $ uname -a
> Linux hleemus 3.1.4-1-ARCH #1 SMP PREEMPT Tue Nov 29 08:55:45 CET 2011 x86_64 AMD Athlon(tm) 64 X2 Dual Core Processor 5000+ AuthenticAMD GNU/Linux
> $ java -version
> java version "1.6.0_22"
> Java(TM) SE Runtime Environment (build 1.6.0_22-b04)
> Java HotSpot(TM) 64-Bit Server VM (build 17.1-b03, mixed mode)
> $ java -Dij.database=jdbc:derby:dbase -classpath /home/hleem/.m2/repository/org/apache/derby/derbytools/10.8.1.2/derbytools-10.8.1.2.jar:/home/hleem/.m2/repository/org/apache/derby/derby/10.8.1.2/derby-10.8.1.2.jar org.apache.derby.tools.ij
> ij version 10.8
> CONNECTION0* - jdbc:derby:dbase
> * = current connection
> ij> select pr_parent FROM SDB.PARAMS;
> PR_PARENT
> -----------
> 10
> 10
> 10
> NULL
> 4 rows selected
> ij> select case when pr_parent is null then 0 else pr_parent end FROM SDB.PARAMS;
> 1
> -----------
> 10
> 10
> 10
> 0
> 4 rows selected
> ij> select nullif(pr_parent,0) FROM SDB.PARAMS;
> 1
> -----------
> 10
> 10
> 10
> NULL
> 4 rows selected
> ij> exit;
> $
> Reporter: hleem
> Labels: case, nullif
>
> NULLIF and CASE return different result with null INTEGER value.
> 'pr_parent' in query is INTEGER type.
--
This message is automatically generated by JIRA.
If you think it was sent incorrectly, please contact your JIRA administrators: https://issues.apache.org/jira/secure/ContactAdministrators!default.jspa
For more information on JIRA, see: http://www.atlassian.com/software/jira