Drizzle

NULL values

Asked by Federico on 2009-04-27

In MySQL, UNKNOWN is a synonym for NULL. In my understanding, NULL could be an unknown value, but even a value which doesn't exists; while the word UNKOWN makes me think that the value exists.

But if the value exists, some expressions should give a result which is not NULL (or UNKNOWN):

a = UNKNOWN;

-> a<=100 OR a>100 should return TRUE
-> a * 0 should return 0
-> a - a should return 0

Will Drizzle ever consider solving this problem implementing a real UNKNOWN value?
I hope this is the right place for questions like this.

Question information

Language:: English Edit question

Status:: Solved

For:: Drizzle Edit question

Assignee:: No assignee Edit question

Solved by:: Roland Bouman

Solved:: 2009-04-29

Last query:: 2009-04-29

Last reply:: 2009-04-29

Link existing bug

Revision history for this message

Jay Pipes (jaypipes) said on 2009-04-29:

Hi!

I'm really unsure what you mean. There is no UNKNOWN in MySQL:

mysql> SELECT NULL;
+------+
| NULL |
+------+
| NULL |
+------+
1 row in set (0.00 sec)

mysql> SELECT UNKNOWN;
ERROR 1054 (42S22): Unknown column 'UNKNOWN' in 'field list'

Revision history for this message

Federico (federico-raz) said on 2009-04-29:

I mean this:

http://dev.mysql.com/doc/refman/5.1/en/comparison-operators.html#operator_is

"IS boolean_value

Tests a value against a boolean value, where boolean_value can be TRUE, FALSE, or UNKNOWN. "

UNKNOWN should be a synonym for NULL

Revision history for this message

Roland Bouman (roland-bouman) said on 2009-04-29:

Hi!

On Wed, Apr 29, 2009 at 9:49 PM, Jay Pipes
<email address hidden> wrote:
> I'm really unsure what you mean. There is no UNKNOWN in MySQL:

Actually - there is...

http://dev.mysql.com/doc/refman/5.1/en/comparison-operators.html#operator_is

However, it is not a true synonum of NULL. Rather, UNKNOWN is
something that only works in these cases:

x IS UNKNOWN and x IS NOT UNKNOWN

Personally, I think this is some rudimentary code that's just placed
there in case they want to impllement true 3-valued logic for boolean
expressions.

UNKNOWN is defined in the standard:

...
<boolean literal> ::=
TRUE
| FALSE
| UNKNOWN
...
10) The truth value of a <boolean literal> is true if TRUE is
speciﬁed, is false if FALSE is speciﬁed,
and is unknown if UNKNOWN is speciﬁed.
NOTE 53 – The null value of the boolean data type is equivalent to the
unknown truth value (see
Subclause 4.6, ‘‘Boolean types’’)
...

and its function is to provide an equivalent to NULL for boolean types.

Either way, I don't think MySQL's current behaviour with respect to
booleans corresponds to the standard, and I don't think the current
implementation of UNKNOWN matches the standard either.

So if drizzle was to implement UNKNOWN, it would be very different
from what you now see in MySQL.

HTH, Roland.

>
> mysql> SELECT NULL;
> +------+
> | NULL |
> +------+
> | NULL |
> +------+
> 1 row in set (0.00 sec)
>
> mysql> SELECT UNKNOWN;
> ERROR 1054 (42S22): Unknown column 'UNKNOWN' in 'field list'
>
> --
> You received this question notification because you are a member of
> Drizzle-developers, which is an answer contact for Drizzle.
>

--
Roland Bouman
http://rpbouman.blogspot.com/