Hacker News Reader

Comment by semiquaver | Hacker News Reader

  > WHERE column = NULL and then query WHERE column <> NULL

With respect, your statement seems to indicate that you simply haven’t heard of or don’t understand the three-valued-logic semantics behind SQL.

NULL means what it means and is conceptually foundational to SQL. Comparing it to anything returns UNKNOWN, the “third value” besides true and false. And WHERE needs a TRUE predicate. So your implied fault that they “have no problem“ really is just a matter of you not understanding the tool you are complaining about.

To match against the NULLness of a value you must use special purpose operators. In this case

   WHERE column IS NULL OR column IS NOT NULL

View on HN

Are we talking Oracle null or ansi null? I'm just being sarcastic and I don't actually remember all the fed up s** Oracle did

Oracle's weird null behaviour is that there is no empty string, so ('' IS NULL) is a true statement in Oracle.

That is weird, good to know!

Hi apologist. How was the woodwork? Your exact comment was why I wrote mine.

  You could write predicates over non-existent columns:

  root=> SELECT * FROM t
         WHERE definitely_not_a_column = 4;

Cool, definitely_not_a_column doesn't exist, so therefore that whole WHERE clause doesn't exist either, so the real query is SELECT FROM t.
SELECT * FROM t WHERE 4 = 5; a | b ---+---- 5 | 6 (1 row)
We're comparing 4 and 5. Precedent?
System.out.println(((Integer)4).compareTo((Integer)5)); > -1
What happens when you treat -1 as a bool? The following languages agree that -1 is true: C, C++, JS, Python, PHP, Ruby, Perl, Lua, R.

So it's SELECT FROM t WHERE true;

Now, let's get busy blaming Jepsen for using the tool wrong.

> therefore that whole WHERE clause doesn't exist either

No, therefore the condition is unsatisfiable and equivalent to WHERE FALSE. And that's letting you ignore the SQL spec, which says the query is invalid and should return "Column not found" because the column name cannot be resolved.

> We're comparing 4 and 5. Precedent?

I really hope you're trolling, for your own sake.

The explanation is more damning than the devex.

The condition is unsatisfiable, and the engine went ahead and gave a 0-warning result?

Are you claiming that 4 = 5?

I'm claiming that

1) It would be no more fucked up than anything else that has been grandfathered into our tools.

2) If it already had mass adoption in the market, people would jump onto this thread to explain to me that I just don't understand the brilliant semantics behind language of why 4 = 5.

I spent more time than I’d like to admit trying to understand your comments here. I’m convinced you are a very odd person with a confused mind.

Brilliant semantics? No one said anything normative. You’re arguing with yourself on your own non-sequitur. SQL is SQL. It’s extremely flawed but who freaking cares, that’s why ParticleDB shouldn’t have tried to implement SQL let alone full postgres compatibility when they clearly are not equipped to do so! Your comments about how strange and non-intuitive SQL are true but irrelevant.

Apologist for what?

Reply on news.ycombinator.com