SELECT wat FROM sql

Re:SELECT wat FROM sql by frankmcsherry on Monday April 27, 2020 @04:33PM (#22999732)

Let's check out SQL's "acknowledged lack of data" in a case statement:

    create table test(a bool);
    insert into test(a) values (NULL);
    select case a when a then true else false end from test;

The answer should be NULL, right?

[ Parent | on HN ]

Re:SELECT wat FROM sql by sjwright on Monday April 27, 2020 @06:59PM (#23001174)

No, the answer must be false.

Since null = null is false, your case statement will return the else value. You hard coded it to be false, therefore it will return false.

[ Parent | on HN ]

Re:SELECT wat FROM sql by frankmcsherry on Tuesday April 28, 2020 @08:50AM (#23006445)

Yes, I know what it will be, but it isn't really acknowledging the lack of data, is it? It turns NULL input data into a 100% false result.

[ Parent | on HN ]

Re:SELECT wat FROM sql by sjwright on Tuesday April 28, 2020 @11:51AM (#23008197)

You’re the one who wrote the logic; YOUR code is what has forcibly collapsed the null to false. It’s absurd for you to write code and then complain when it works exactly as written.

If you want the lack of data to be acknowledged in a case statement, it’s up to you to handle it. SQL isn’t a magical fairy that does what you want without asking.

[ Parent | on HN ]