On 2016/02/05 6:34 AM, TJ O'Donnell wrote: > I can't argue for the correctness of including nulls in aggregate functions > or not. > It truly is an arbitrary decision meant for standards-makers. Yet, most > aggregate > function do not include nulls. Interestingly, some SQL's do include them > in count() > but sqlite does not. In my example table, select count(a) from x returns 3, > but select count(*) from x returns 4 even though a is the only column. > I haven't tried every sqlite agg, but I think they all exclude null, except > json_group_array > and json_group_object.
I think you are mistaken in your understanding. While JSON has some rules and some conventions, when used inside an SQL engine, the rules of SQL needs to be adhered to before any "convention" of JSON. Not showing Null values in JSON is a convention, not a rule. (Else, why else would json even need the 'NULL' construct?) Further to this, in SQL, how would you know how many elements are present in a json array and which of them are null if there is no way to output them? Agreed, sometimes it isn't needed to know, but then you are welcome to exclude them via the WHERE clause. You are also mistaken about the SQL convention and SQLite-specific operations re. Nulls - If I have a table t with one single column "a" with 3 rows (2 text values and one null value) then doing SELECT COUNT(a) FROM t; will show 2 and SELECT COUNT(*) FROM t; will show 3, as it should - yes, even though a is the only column. The * doesn't mean "a", even if the only column is "a". It means "all the DB rows" and so include nulls. (The standard might be hazy on this, I didn't check, but this is definitely how SQLite works, and not as you suggested). This is also very important. Sometimes we'd want to know how many rows are in the DB, not JUST which non-null rows are in the only column in the DB - that is why we can decide to use either COUNT(a) or COUNT(*), or more deliberate with an explicit GROUP BY clause. I would never want this convention to be altered. > As a side issue here, but important still I think, what should json(null) > mean? > In my table x, select json(a) from x returns valid json integers for > non-null rows, > but return a sql null (a blank from command-llne sqlite) not a json null > (which would > be the string null) when a is null. In other words, json(null) returns > null, > not 'null'. Here I'm with you - the null should output 'null' (Devs: I'm guessing this might be an oversight in the CLI rather than the SQL engine?) > I know the json stuff is new in sqlite, but I think it's worth getting > these issues worked > out, considering how useful json has become. Right you are, but first the issues need discovery - which is what is happening in this very thread. :)
