We have accelerated the release of version 3.44.0. It was originally schedule for late December and was suppose to include additional features (such as support for JSONB). But there is already enough new material in the source tree to warrant an early release. Those other features will be delayed until a subsequent release.

See the release change summary at https://sqlite.org/draft/releaselog/3_44_0.html

As you are able, please download and test the new code. Compile from canonical sources if you are able, or use one of the snapshot builds. Try out new language features (such as support for ORDER BY clauses on aggregate functions) on Fiddle.

Snapshot builds of Fossil containing the latest SQLite code are available on the Fossil download page.

Report any issues or concerns to this forum, as replies to this post. Or send reports directly to me at drh at sqlite dot org.

Thank you for aggregate ORDER BY!

A couple typos:

• GEOPLOY => GEOPOLY (twice in the text)
• depending one => depending on

Regarding JSONB, we didn't hear anything back after the RFC for comments.
Would be nice to read your thoughts on the various remarks / comments made.

Typo fixes uploaded.

Regarding JSONB: Many of the replies seemed to miss the whole point of why I'm working on JSONB. In their defense, I didn't explain myself particularly well. So my response has been to update the preliminary JSONB documentation to better explain its purpose, scope, and limitations.

The enhanced JSONB documentation is available in the documentation source tree but is not currently viewable on-line, since the "draft" website is occupied with the 3.44.0 release. Once 3.44.0 gets out the door, I'll switch the draft website back over to displaying the JSONB documentation.

Regarding JSONB: Many of the replies seemed to miss the whole point [...]. [...] better explain its purpose, scope, and limitations.

Ouch :). Fair enough.

One thing I'd like to note though, regarding this paragraph to the doc:

The SQLite JSONB format is intended to be private to SQLite
and is for use by the built-in SQLite functions only.
The JSONB format is not intended as an interchange format.
Nevertheless, JSONB is stored in database files which are intended to
be readable and writable for many decades into the future.
To that end, the JSONB format is well-defined and stable. The separate
[SQLite JSONB format] document provides details of the JSONB format
for the curious reader.

I don't think you can have it both ways.

If it's stored in the DB files, it's basically public.
And will naturally become an interchange format.

In fact, as I already mentioned, PostgreSQL's jsonb binary serialization
is currently a version byte (with value 0 or 1, I don't recall), and the JSON
text (re-serialized from the trully internal representation server-side).

Once your JSONB is released, I'd argue that bumping the byte version and
returning your JSONB format would make sense. That won't happen, as it
would break many clients that never expected the byte version to change,
but it would be faster and more compact. Same on the ingestion side.

I think it's inevitable that what you do in SQLite around JSONB will be
used more broadly. And why it's all the more important to get it right :).

My comments regarding JSONB were precisely to figure that out, and scoping out the optimal implementation for a user of SQLite (or a wrapper library).

We can/should move this discussion there, but would you say that the intention is for an app to always provide JSON at the edge (i.e. to the C API), and for SQLite itself to perform the conversion?

So would you say that the statement to insert some JSONB into the database should be something like:

  INSERT INTO test VALUES (jsonb(?))

Then we provide standard JSON and let SQLite do the conversion, and to extract JSON from the database we do just the opposite:

  SELECT json(col) FROM test

Is that how you expect JSONB to be used, since there doesn't seem to be anything outside of SQL where it is exposed?

If so, this would've been the kind of tidbit that I'd have found useful to see in the docs.

Thanks!

PS: the improvements to jsonb.in are much appreciated. I still think The payload does not include string delimiters is a bit unclear. I assume it means the delimiters (") at the start/finish are omited, but it says nothing on whether " can appear unquoted in the payload.

Another release note typo: "subsqueries".

(Edited to dispel a brain fart.)

that's the only built-in function where it matters at the moment

It can easily matter for min() and max() sum() and avg() too. (I would say only for unreasonable uses of those functions, but we've seen such arcania discussed in this forum.)

it should also make it into the railroad diagram

That's on my plate, soon to be done. Thanks, done. The syntax is applicable to aggregate functions in general, including UDFs registered as aggregate-capable.

As for documenting the effect for specific aggregate functions, we will take that under advisement.

I found a typo at https://sqlite.org/draft/c3ref/get_clientdata.html

The database connection closes. SQLite does not make any guarantees about the order in which destructors are called, only that all destructors will be called exactly once at some point during the database connection closingi process.

closingi -> closing

Very nice, I really like the ORDER BY in aggregates. After playing around with it in the fiddle, I found some interesting "features" (or only glitches maybe?). Using a subset of pragma_function_list as test table

create table mf as select * from pragma_function_list where name like 'm%';

A: first tests

All as I expected, random (=storage) order, order defined and changed separator

select group_concat(name) from mf;
select group_concat(name order by name) from mf;
select group_concat(name, '|' order by name desc) from mf;

mod,max,max,min,min,match,matchinfo,matchinfo
match,matchinfo,matchinfo,max,max,min,min,mod
mod|min|min|max|max|matchinfo|matchinfo|match

B: lets add DISTINCT

which works unless the order is not by the same value

select group_concat(distinct name) from mf;
select group_concat(distinct name order by name) from mf;
select group_concat(distinct name order by narg) from mf;
select group_concat(distinct name order by type) from mf;

mod,max,min,match,matchinfo
match,matchinfo,max,min,mod
max,min,matchinfo,max,min,match,matchinfo,mod
match,matchinfo,max,min,mod,max,min

Notice how the last one collapses 'matchinfo', as both entries are of the same type 's'

C: add in a separator

Nice, as this was a no-no up to now

select group_concat(distinct name, '|' order by name) from mf;
select group_concat(distinct name, '|' order by narg) from mf;
select group_concat(distinct name, '|' order by type) from mf;

match|matchinfo|max|min|mod
max|min|matchinfo|max|min|match|matchinfo|mod
match|matchinfo|max|min|mod|max|min

and still is, if no ordering done...

select group_concat(distinct name, '|') from mf;

Parse error near line 1: DISTINCT aggregates must have exactly one argument

So if the above is not a bug, this should probably be allowed as well.

D: what about non-constant separator

Is that allowed? - yes.

select group_concat(name, upper(type)) from mf;
select group_concat(distinct name, upper(type) order by name) from mf;

modSmaxWmaxSminWminSmatchSmatchinfoSmatchinfo
matchSmatchinfoSmaxWmaxSminWminSmod

the first one is the same in older versions of sqlite. Shouldn't a constant separator better be enforced?

In summary: Is it a glitch or a feature, that DISTINCT is combineable with ORDER BY? Is it a glitch or a feature, that DISTINCT is now usable with a second argument (separator) now?

Those are all features. One can easily imagine cases where any one of them would be useful.

Try
putting
two
spaces
at
the
end
of
the
line.

As long as you only order by stuff that is purly a function of the value like eg

... group_concat(distinct name order by length(name) desc, substr(name,3,1) ) ...
--> matchinfo,match,mod,min,max

there seem to be always 5 things in the result, ordered exactly as one would expect.

Thinking about it, the process of ordering first - distincting second is more universal in that sense. Would one do the other way round of distincing first - ordering second, there would nothing be left to order by than the distincted values. And something like

group_concat(distinct name order by narg)

would be an error (no narg left to order by after distincted name).

Sorry if distincting and distincted are no real english words (verb, participe, whatever), non-native speaker here ...

I agree with this. I'm not seeing why the ORDER BY clause should affect anything other than the order. As is, the query using DISTINCT Name ORDER BY Type is an unintuitive way of essentially doing SELECT GROUP_CONCAT(Name ORDER BY Type, Name) FROM (SELECT DISTINCT Name, Type FROM m).

To achieve the more expected result ("match,matchinfo,max,min,mod") requires SELECT GROUP_CONCAT(Name ORDER BY Type) FROM (SELECT Name, MIN(Type) AS Type FROM m GROUP BY Name) (or maybe just SELECT GROUP_CONCAT(Name ORDER BY Type) FROM (SELECT Name, Type FROM m GROUP BY Name) if the value for Type is picked from the first/an arbitrary row of a distinct value for Name).

Unless there is somehow something the current way can do that otherwise cannot be done (or more likely, cannot be done in a way that isn't painful), I'd say it would make sense to change it

PS: You can use <br/> to add a linebreak wherever you want.

using DISTINCT Name ORDER BY Type is an unintuitive way of essentially doing ...

That may be, depending upon whose intuition is at work. However, the aggregate function ordered/uniquified argument syntax is more compact. I would also suggest that, as people use that syntax, it will become more intuitive. It certainly is more direct, which ought to aid comprehension.

The syntax is not something dreamed up specifically for SQLite. PostgreSQL has had the same syntax for aggregate functions for quite some time. Having similar syntax between a great embedded DBMS and one of the great client/server DBMSs is a favor to users who use both, and all the more so when they wish to move queries among DBMSs.

Edit: This is fixed now; thanks

I'm not saying the syntax is unintuitive (though it is a miniscule amount weird to me, but by no means a problem), I'm saying the result being produced by GROUP_CONCAT(DISTINCT Name ORDER BY Type) is unintuitive. There's no reason to expect it to implement the distinct part by factoring in the order by, when it doesn't do that for normal queries.

As an extra example, you can completely negate the DISTINCT by ordering by the rowid of the table. It just looks like a bug.

sqlite> SELECT GROUP_CONCAT(DISTINCT Name ORDER BY rowid) FROM m;
mod,max,max,min,min,matchinfo,matchinfo,match

sqlite> SELECT DISTINCT Name FROM m ORDER BY rowid;
mod
max
min
matchinfo
match

According to their documentation, PostgreSQL does not allow to order by something that is not part of the DISTINCT argument:

https://www.postgresql.org/docs/9.5/sql-expressions.html#SYNTAX-AGGREGATES

If DISTINCT is specified in addition to an order_by_clause, then all the ORDER BY expressions must match regular arguments of the aggregate; that is, you cannot sort on an expression that is not included in the DISTINCT list.

Might be an idea to adhere to that too ?

I noticed that the behavior of latest trunk is different now: Part B, C and D of my post behave differently now than before, so it was not all 'features' then ;-)

The fiddle is still on an older version, btw.

Thank you.

Should ... group_concat(distinct name, '|') ... still through the error Parse error: DISTINCT aggregates must have exactly one argument then or should that also be allowed now? In any case, the wording in the error is not correct. If it keeps being an error it should be something like 'DISTINCT aggregates must have exactly one argument or an ORDER BY term' then.

And the wording in the docs (lang_aggfunc.html) about when DISTINCT is possible needs some rework to.

Another nice niche usage of the ORDER BY might be for SUMming potentially cancelling values of huge magnitude differences. Like the example from https://sqlite.org/draft/lang_aggfunc.html#sumunc, naively ordering the values by ABS(value) DESC beforehand:

CREATE TABLE t1(x REAL);
INSERT INTO t1 VALUES(1.55e+308),(1.23),(3.2e-16),(-1.23),(-1.55e308);
SELECT sum(x), decimal_sum(x), sum(x order by abs(x) desc) FROM t1;

2.22044604925031e-16|0.00000000000000032|3.2e-16

Spot on in that case.

In the function list I don't recall which year something was last experimental. Is it really a thing any more?

The structure at the bottom of implementation 1.2 doesn't show xIntegrity.

The doc for xIntegrity doesn't guarantee that the error message pointer is NULL on function entry. (There are some other functions where I have to check & free before overwriting.)

My integration and testing of xIntegrity worked as expected. However the string I provide is used as the entire message return from the pragma which is extremely unhelpful. For example I would have no idea what virtual table or module provided the message, or even if it came from a virtual table. I suggest that virtual table integrity check errors have the module name and table name included by SQLite code. (BTW I don't know either of those pieces of information from my code implementing xIntegrity as there are layers of Python and C working together.)

pragma quick_check also currently invokes xIntegrity. Perhaps xIntegrity should take an int saying if it is a quick check versus a full check?

The xIntegrity interface has been revised. Roger, please peruse the latest changes and report back whether or not your concerns have been adequately addressed.

The updates worked as expected, and I'm very happy happy with the results.

A low priority cosmetic issue is that some function parameters are unnamed in the struct sqlite3_module definition in section 1.2 as well as in the sqlite3.h header file. This means it takes a bit more effort to find out what the parameters are, and being missing in the header file means that the IDE completion can't help.

Add support for the concat() and concat_ws() scalar SQL functions.

I had to search the net for what _ws is short for, as the current SQLite description does not clearly explain the mnemonic. Perhaps the phrase "with separator" (italicized?) could be added to the documentation?

Perhaps concat_sep() would have been a more intuitively understandable name. Maybe except for those who immediately think seppuku.

I would also prefer the function be called something else. It is however already in use by other DBMSs it seems, and there are benefits to conforming to other implementations. Perhaps an alias...

"concat_ws" is a standard name used by PostgreSQL, SQLServer, and MySQL. I did not make the name up. The new function is provided in order to improve compatibility with those other database engines.

There's a typo in the strftime documentation:

"%H without leading zero: 1-12" should be "hour without…" or "%I without…"

For those who want to follow the action, the release checklist for SQLite 3.44.0 has started to change color. Once everything goes green, we will cut the release.

I went to look at the release checklist, and it was all green.

The files are available on the download page, and my system is updated and running.

Happy day!

Release note item 13: s/long long double/long double/

The link to http://opengroup.org/onlinepubs/007908799/xsh/strftime.html on page https://sqlite.org/lang_datefunc.html is broken.

The function strftime() may be inspired by the one of the C library, but at least two important placeholders are missing:

%V The ISO 8601 week number of the current year as a decimal number, range 01 to 53, where week 1 is the first week that has at least 4 days in the new year.

%G The ISO 8601 week-based year with century as a decimal number. The 4-digit year corresponding to the ISO week number (see %V). This has the same format and value as %Y, except that if the ISO week number belongs to the previous or next year, that year is used instead.

And no, %U and %Y are no replacement for this.