Re: [External] The Joy of NULLs (not)

Hi,
let me comment on the difference between 'NULL' and NULL.
I was merely saying that in fact nobody usually ever writes 'NULL'; indeed, even in your example you write a and you do not write'a'.
The same difference would apply between 'a' and a.
Anyway, this does not change my general argument on the semantics of SQL NULL values.
If you are interested in the full formal picture, you could read:

Enrico Franconi and Sergio Tessaris (2012). The algebra and the logic for SQL nulls. In Juliana Freire and Dan Suciu, editors, Proceedings of the 6th Alberto Mendelzon International Workshop on Foundations of Data Management, Ouro Preto, Brazil, June 27-30, 2012, volume 866 of CEUR Workshop Proceedings, pages 114–128, 2012.
<http://www.inf.unibz.it/~franconi/papers/Franconi%20and%20Tessaris%20-%202012%20-%20The%20Algebra%20and%20the%20Logic%20for%20SQL%20Nulls.pdf>

cheers
--e.

Il giorno 14 ago 2019, alle ore 21:00, Diogo FC Patrao <djogopatrao@gmail.com<mailto:djogopatrao@gmail.com>> ha scritto:

Hi Enrico

> The situation is slightly more complex than that.
> NULL values in standard SQL are exactly defined as letting any equality involving a NULL value fail.
> Note that the string 'NULL' represents a NULL value, namely if you type the string NULL into a cell of type STRING then it is understood to be a NULL value.

I implemented your example in mysql (using console to minimize interference from user interfaces):

TABLE: col1 | col2
       -----+-----
         a  | 'NULL'
         b  | NULL


mysql> create temporary table test
    -> (
    -> col1 varchar(10),
    -> col2 varchar(20)
    -> );
Query OK, 0 rows affected (0,08 sec)

mysql> insert into test values ( 'a', 'NULL' );
Query OK, 1 row affected (0,05 sec)

mysql> insert into test values ( 'b', NULL );
Query OK, 1 row affected (0,06 sec)

The query you provide will yield only the first line (as in your example):

mysql> SELECT * FROM bancos_clinicos.test WHERE test.c1 = test.c1 AND test.c2 = test.c2;
+------+------+
| c1   | c2   |
+------+------+
| a    | NULL |
+------+------+
1 row in set (0,00 sec)

So 'NULL' doesn't share this equality failure property with NULL.

mysql> select * from test where c2 = 'NULL';
+------+------+
| c1   | c2   |
+------+------+
| a    | NULL |
+------+------+
1 row in set (0,00 sec)

mysql> select * from bancos_clinicos.test where c2 is NULL;
+------+------+
| c1   | c2   |
+------+------+
| b    | NULL |
+------+------+
1 row in set (0,00 sec)


> This is where the implementors failed: a NULL value is never equal to itself.

If that was the case, then the guy would never get any ticket, as  "TICKETS.PLATE=NULL" would always yield false.

However, that went backwards and he got a lot of tickets, so I guess the query to get the tickets was correctly making the condition "TICKETS.PLATE='NULL'", but the code that did the insertion of those tickets somehow casted NULL to 'NULL' (which is correct when translating from database to screen but not the other way round).



Cheers,

dfcp


--
diogo patrão




On Tue, Aug 13, 2019 at 4:53 AM Franconi Enrico <franconi@inf.unibz.it<mailto:franconi@inf.unibz.it>> wrote:
The situation is slightly more complex than that.
NULL values in standard SQL are exactly defined as letting any equality involving a NULL value fail.
Note that the string 'NULL' represents a NULL value, namely if you type the string NULL into a cell of type STRING then it is understood to be a NULL value.
This is where the implementors failed: a NULL value is never equal to itself.
This can be understood with the following standard SQL example (try it!).

With the database:

TABLE: col1 | col2
       -----+-----
         a  |  b
         b  | NULL

the query (meant to be the identity query, namely returning the input table itself):

SELECT * FROM TABLE
WHERE TABLE.col1 = TABLE.col1 AND TABLE.col2 = TABLE.col2 ;

gives the result:

col1 | col2
-----+-----
  a  |  b

In SQL, the query above returns the table TABLE if and only if the table TABLE does not have any NULL value, otherwise it returns just the tuples not containing a NULL value, i.e., in this case only the first tuple <a,b>. Informally this is due to the fact that a SQL NULL value is never equal (or not equal) to anything, including itself. This is because a SQL NULL value represents the absence of a value.

Note that this is where SQL NULL values are radically different from RDF bnodes. Indeed a bnode is EQUAL to itself but different from any other bnode. This is because a RDF bnode represents the existence of an unknown value.

--e.

Il giorno 12 ago 2019, alle ore 16:41, Diogo FC Patrao <djogopatrao@gmail.com<mailto:djogopatrao@gmail.com>> ha scritto:


Vanity license plates in USA are strings, right? Then this problem would only happen if NULL='NULL', which is not.

It could be that the private company stored 'NULL' instead of NULL to the unassigned tickets, but that's really bad coding/design (and easy to fix, I guess).

Or maybe the DAO wrongly translate NULL to 'NULL' at some point.

Cheers

dfcp










--
diogo patrão




On Mon, Aug 12, 2019 at 11:11 AM Young,Jeff (OR) <jyoung@oclc.org<mailto:jyoung@oclc.org>> wrote:
Here’s an example showing blank nodes being used to declare the place of birth is unknown in Wikidata:

https://w.wiki/6$y


In the UI, it is rendered like this:

<image001.png>

Jeff

From: Daniel Hernandez <daniel@degu.cl<mailto:daniel@degu.cl>>
Date: Monday, August 12, 2019 at 9:42 AM
To: "semantic-web@w3.org<mailto:semantic-web@w3.org>" <semantic-web@w3.org<mailto:semantic-web@w3.org>>
Subject: [External] Re: The Joy of NULLs (not)
Resent-From: <semantic-web@w3.org<mailto:semantic-web@w3.org>>
Resent-Date: Monday, August 12, 2019 at 9:37 AM

As Enrico pointed, blank nodes can be used to represent unknown values.
An example of this use is Wikidata. I don't know another example.

--
Daniel

On Mon, 12 Aug 2019 07:36:41 +0000
Franconi Enrico <franconi@inf.unibz.it<mailto:franconi@inf.unibz.it>> wrote:

> Mike, this could easily happen in an RDF world if you register a
> vanity licence plate with anything starting with "_". Indeed, bnodes
> would be the right way to represent unknown but existing plates. --e.
>
> Il giorno 11 ago 2019, alle ore 23:10, Michael F Uschold
> <uschold@gmail.com<mailto:uschold@gmail.com><mailto:uschold@gmail.com<mailto:uschold@gmail.com>>> ha scritto:
>
>> This is hilarious. It could never happen in an RDF world! No value,
>> no triple.
>>
>> He tried to prank the DMV. Then his vanity license plate backfired
>> big time.
>> https://mashable.com/article/dmv-vanity-license-plate-def-con-backfire/<http://flip.it/NIk7FD>