NULL values for collation sequence
(1) By Mark Benningfield (mbenningfield1) on 2020-11-27 23:17:49 [link] [source]
I am doing some testing on a custom collation sequence, and I find that no NULL values are passed to the collation sequence as arguments. After a little looking I found this post on the old Nabble archive.
My testing confirms that no NULL values are passed to my custom collation sequence. Can I positively assert that no NULL values will ever be passed to the collation sequence?
(2) By Warren Young (wyoung) on 2020-11-27 23:27:57 in reply to 1 [link] [source]
Given that SQL NULL is the value that is not equal to anything else — not even to NULL! — then how could NULL values possibly collate? If NULL ≠ NULL, then how can you make logical sense of any other relation, such as NULL ≥ NULL?
(3) By Mark Benningfield (mbenningfield1) on 2020-11-27 23:42:23 in reply to 2 [source]
In the old Nabble archive post, DRH states that NULLS always sort first, which is not specified in the SQL92 standard, but is left up to implementations.
I'm not asking how to collate NULL values, I'm asking if there is an explicit guarantee in the API that SQLite will always take care of sorting the NULLS first, and that a collation sequence will not have make sure to do so. That certainly appears to be the case.
(4) By Richard Hipp (drh) on 2020-11-27 23:43:00 in reply to 1 [link] [source]
Can I positively assert that no NULL values will ever be passed to the collation sequence?
Yes. See line 1003 of main.c
(5) By Mark Benningfield (mbenningfield1) on 2020-11-27 23:43:54 in reply to 4 [link] [source]
Outstanding. Thank you.
(6) By Keith Medcalf (kmedcalf) on 2020-11-28 00:58:41 in reply to 5 [link] [source]
Collation sequences only apply to text. NULL is not valid text. A "null string" (that is, a string that does not contain anything) is actually a string and not NULL.
So it really matters what you mean by NULL.
A "null string" is a string (text) that does not contain anything. It is a string/text value, and that value is "empty" of content. Conversely NULL is a specific value that is of the NULL type. Collating sequences only apply to TEXT values.