When the test expression in a simple (as opposed to searched) CASE expression has value NULL, the NULL branch is not used:
create table ttt (cNull varchar(255));
insert into ttt values(NULL);
select CASE cNull WHEN NULL THEN 'b' ELSE 'a' END FROM ttt;
·----·
| C1 |
·----·
| a | INCORRECT!!!
·----·
select CASE WHEN cNull IS NULL THEN 'b' ELSE cNull END FROM ttt;
·----·
| C1 |
·----·
| b | CORRECT
·----·
Is the first form being considered as "CASE WHEN cNull=NULL THEN..."? This would obviously fail.
Kind regards,
Mick.
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
I am able to reproduce your issue directly in HSQLDB 2.3.1 and HSQLDB 2.4.1.
try(Statementst=conn.createStatement()){st.execute("CREATE TABLE ttt (id int PRIMARY KEY, cNull varchar(255))");st.execute("INSERT INTO ttt (id) VALUES (1)");Stringsql=null;ResultSetrs=null;sql="select CASE cNull WHEN NULL THEN 2 ELSE 1 END FROM ttt";rs=st.executeQuery(sql);rs.next();System.out.println(rs.getInt(1));// 1sql="select CASE WHEN cNull IS NULL THEN 2 ELSE 1 END FROM ttt";rs=st.executeQuery(sql);rs.next();System.out.println(rs.getInt(1));// 2}
However, I am also able to reproduce the issue using Microsoft SQL Server 2014:
C:\Users\gord>sqlcmd -S .\SQLEXPRESS -E -t 10
1> use myDb
2> go
Changed database context to 'myDb'.
1> CREATE TABLE ttt (id int PRIMARY KEY, cNull varchar(255));
2> go
1> INSERT INTO ttt (id) VALUES (1)
2> go
(1 rows affected)
1> select CASE cNull WHEN NULL THEN 2 ELSE 1 END FROM ttt
2> go
-----------
1
(1 rows affected)
1> select CASE WHEN cNull IS NULL THEN 2 ELSE 1 END FROM ttt
2> go
-----------
2
(1 rows affected)
1>
Thus, this issue is not specific to HSQLDB or UCanAccess.
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
Hi,
When the test expression in a simple (as opposed to searched) CASE expression has value NULL, the NULL branch is not used:
Is the first form being considered as "CASE WHEN cNull=NULL THEN..."? This would obviously fail.
Kind regards,
Mick.
I am able to reproduce your issue directly in HSQLDB 2.3.1 and HSQLDB 2.4.1.
However, I am also able to reproduce the issue using Microsoft SQL Server 2014:
Thus, this issue is not specific to HSQLDB or UCanAccess.
It is not an issue at all for me: null = null is always false in sql
Thanks for investigating, Gord - my bad!