On 2 Aug 2011, at 9:06am, Igor Sereda wrote:
> Thanks, Simon - it's a bug then. Hope it will get pulled into the bug
> tracker.
> Simon Slavin-3 wrote:
>
>> The spec for '<=' should say that comparing any number with NULL always
>> gives a NULL result. If SQLite is doing anything apart from that, it's a
>> bug.
>>
>> Okay, here it is: SQL92 8.2 (1) (a):
>>
>> "If XV or YV is the null value, then "X <comp op> Y" is unknown."
>>
>> In this context, returning 'unknown' means returning NULL.
You know, I might have to withdraw the last part of that. The SQL92 spec talks
about truth functions having three possible answers: FALSE, TRUE, and UNKNOWN.
It treats UNKNOWN as a reserved word in the same way as it treats TRUE,
contains statements like
"If "R <comp op> <quantifier> T" is neither true nor false,
then it is unknown."
and the truth tables in section 8.12 give results for TRUE, FALSE, and UNKNOWN.
And nowhere can I find any statement that says UNKNOWN means NULL. And I can
a distinction between the two: NULL means "I don't have that datum" whereas
UNKNOWN means "I can't work that out".
But I don't think SQLite implements UNKNOWN. So we need someone intimately
familiar with the guts of SQLite, which I'm not.
Either way, I don't think it should be possible to come up with a well-ordering
system that includes NULL, numbers and strings and stays within the SQL
standard. The big question is whether any of the big users of SQLite is
depending on this behaviour.
Simon.
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
