On 5 Apr 2019, at 00:18, Simon Slavin <slav...@bigfraud.org> wrote:
>
> On 4 Apr 2019, at 10:12pm, Lifepillar <lifepil...@icloud.com> wrote:
>
>> This is essentially a pragmatic choice, as the semantics of NULLs is
>> unspecified and ambiguous.
>
> The way SQL handles NULLs may sometimes appear inconsistent, but is the
> result of SQL handling rows as sets. Some of the behaviour is, carefully
> designed, with the knowledge that the NULL that results from one step of an
> expression is going to be fed into the next step of the expression.
I beg to disagree. NULL's handling in SQL *is* inconsistent. The SQL committee
has taken some actions to make things... less worse, in particular by adding
F571 ("is true", "is false", "is unknown"), but the castle still remains (and
will likely forever remain) a castle of sand.
> For those purposes, NULL means one of two things: "no value" or "value
> unknown”.
There is a third option: “no information” (value may exist and be unknown, or
it may not exist). And before someone asks: no, there is no need for going meta
(“don’t know whether there is no information…”).
> For instance, the sum of a result which includes a NULL value is NULL.
Note that this is incompatible with the “value unknown” semantics (noticed in
*1977* by Grant!). Trivial example:
select * from T where x = x;
If x contains NULLs, under the “value unknown” semantics the above query should
still output all the rows, because an existing value, albeit unknown, is
always, surely, equal to itself. Under the “inapplicable” semantics SQL's
result is correct, though, and under the “no information” semantics it may be
considered correct assuming that you want certain answers (i.e., only tuples
that satisfy the condition with certainty). You see that NULL conflate
different things and leaves the user the burden to deal with it.
> But the sum of a result with zero rows is not NULL, it's zero.
So, SQL is wrong there, because it returns NULL (my library returns 0). Note
that an empty table has no NULLs, so the result of any query or function on an
empty set has nothing to do with NULLs.
I am fond of NULLs (as much as I try to avoid them in my databases), so if you
feel like it is interesting discussing this any further (and deemed appropriate
for the list), we may do so in a separate thread.
> For the purposes of a decimal extension to SQLite, I would imitate what
> SQLite3 does with REAL values. If you have a question about how SQLite sees
> NULL it might be answered here:
>
> <https://sqlite.org/nulls.html>
>
> It's especially important that the test script at the end of that page,
> behaves the same if you run it as is, and if you substitute the 'int' type
> with your decimal type. If you have any questions, please don't hesitate to
> ask.§
I have tried the script, adapted for decimals, and it works the same as for
ints. The script is reported at the end of this message. Btw, talking about
consistency:
create table t(n int);
insert into t values (1), (null);
select distinct n from t; -- Result has two rows
select count(distinct n) from t; -- 1
:)
Thanks for the feedback!
Life.
-- Create a test table with data
create table t1(a blob, b blob, c blob);
insert into t1 values (dec(1), dec(0), dec(0));
insert into t1 values (dec(2), dec(0), dec(1));
insert into t1 values (dec(3), dec(1), dec(0));
insert into t1 values (dec(4), dec(1), dec(1));
insert into t1 values (dec(5), null, dec(0));
insert into t1 values (dec(6), null, dec(1));
insert into t1 values (dec(7), null, null);
select decStr(a), decStr(b), decstr(c) from t1;
-- Check to see what CASE does with NULLs in its test expressions
select decStr(a), case when b<>dec(0) then 1 else 0 end from t1;
select decStr(decAdd(a,10)), case when not b<>dec(0) then 1 else 0 end from t1;
select decStr(decAdd(a,20)), case when b<>dec(0) and c<>dec(0) then 1 else 0
end from t1;
select decStr(decAdd(a,30)), case when not (b<>dec(0) and c<>dec(0)) then 1
else 0 end from t1;
select decStr(decAdd(a,40)), case when b<>dec(0) or c<>dec(0) then 1 else 0 end
from t1;
select decStr(decAdd(a,50)), case when not (b<>dec(0) or c<>dec(0)) then 1 else
0 end from t1;
select decStr(decAdd(a,60)), case dec(b) when dec(c) then 1 else 0 end from t1;
select decStr(decAdd(a,70)), case dec(c) when dec(b) then 1 else 0 end from t1;
-- What happens when you multiple a NULL by zero?
select decStr(decAdd(a,80)), decStr(decMul(b,0)) from t1;
select decStr(decAdd(a,90)), decStr(decMul(b,c)) from t1;
-- What happens to NULL for other operators?
select decStr(decAdd(a,100)), decStr(decAdd(b,c)) from t1;
-- Test the treatment of aggregate operators
select count(*), count(dec(b)), decStr(decSum(b)), decStr(decAvg(b)),
decStr(decMin(b)), decStr(decMax(b)) from t1;
-- Check the behavior of NULLs in WHERE clauses
select decStr(decAdd(a,110)) from t1 where b<dec(10);
select decStr(decAdd(a,120)) from t1 where not b>dec(10);
select decStr(decAdd(a,130)) from t1 where b<dec(10) OR c=dec(1);
select decStr(decAdd(a,140)) from t1 where b<dec(10) AND c=dec(1);
select decStr(decAdd(a,150)) from t1 where not (b<dec(10) AND c=dec(1));
select decStr(decAdd(a,160)) from t1 where not (c=dec(1) AND b<dec(10));
-- Check the behavior of NULLs in a DISTINCT query
select distinct decStr(b) from t1;
-- Check the behavior of NULLs in a UNION query
select decStr(b) from t1 union select decStr(b) from t1;
-- Create a new table with a unique column. Check to see if NULLs are
considered
-- to be distinct.
create table t2(a blob, b blob unique);
insert into t2 values(dec(1),dec(1));
insert into t2 values(dec(2),null);
insert into t2 values(dec(3),null);
select decStr(a), decStr(b) from t2;
drop table t1;
drop table t2;
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
