Something from a SQL standard that didn't exist at the time NULL-safe operators where implement, is IS [NOT] DISTINCT FROM that has the same function.

Just doing some research, I found this:
"Comparisons Involving null
Comparisons (<, >, =, …) to null are neither true nor false but instead return the third logical value of SQL: unknown. The following example removes all rows—even those where col is actually null—because the where clause treats unknown like false."

The article had links to SQL-2016 standard documentation. If you compare "a"= NULL the result is unknown or False. Not True. Why does MariaDB default this to True?

Looks like unknown in the form of NULL to me:

ref: fiddle.

That doesn't mean it meets SQL ISO specs.

"a" = Null => False
"a" != Null => True
"a" > Null => Error not a valid comparison
"a" < Null => Error not a valid comparison

1222tmiller, I don't understand what you're saying here.

If a <=> b is a test for a and b being equal, then NOT a <=> b is a test for them being not equal. Is that what you mean?

Why does MariaDB default this to True?

MariaDB defaults what to True?

I was trying to do just that inside a column IF statement. IF(true, UseValueIfTrue, UseValueIfFalse);

It read better as IF(a != b, x, y); It started failing with NULL as I tested this early on and it worked.

So I did exactly as you suggested and IF(NOT(a <=> a), x, y) failed. So I just flipped it to get around the bug:

IF(a <=> b, y, x);

The ANSI standard is NULL = NULL => True Anything else is false. An unassigned value is not equal to "Tom".

NULL = 'Tom' => False Why I get a true back all of a sudden is a mystery. I am using the new 3.0 connector, so it could be that.

Could you clarify that, please?

• what do you mean by "failed"?
• Where can I read that "The ANSI standard is NULL = NULL => True Anything else is false" ?

IF(a != b, x, y);

If a = "MyTestString" b = NULL

a is not equal to b so the result should be true. But it returns false and uses x as the result.

This is a bug.

No, this is not a bug.

See the MariaDB Knowledge Base: https://mariadb.com/kb/en/null-values/

Or, literally, the very first results in internet search for "comparison with null": https://learnsql.com/blog/null-comparison-operators/