Simple CASE expression incorrect when used with NULL

[Mick Francis]

Hi,

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.

[Gord Thompson]

I am able to reproduce your issue directly in HSQLDB 2.3.1 and HSQLDB 2.4.1.

try (Statement st = conn.createStatement()) {
    st.execute("CREATE TABLE ttt (id int PRIMARY KEY, cNull varchar(255))");
    st.execute("INSERT INTO ttt (id) VALUES (1)");
    String sql = null;
    ResultSet rs = 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));  // 1
    sql = "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.

[Marco Amadei]

It is not an issue    at all for me: null = null is   always false in sql

[Mick Francis]

Thanks for investigating, Gord - my bad!