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
Jay Pipes (jaypipes) said : #1

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'

Federico (federico-raz) said : #2

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

Best Roland Bouman (roland-bouman) said : #3

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
specified, is false if FALSE is specified,
and is unknown if UNKNOWN is specified.
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/

Federico (federico-raz) said : #4

Thanks Roland Bouman, that solved my question.

Vahaber (alphagoblin4) said : #5

Roland Bouman, how i can decide this problem on Unix ??