SQLite Forum

SQLite version 3.44.0 now in beta

SQLite version 3.44.0 now in beta

(1) By Richard Hipp (drh) on 2023-10-24 13:04:20 [link] [source]

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.

(2) By ddevienne on 2023-10-24 13:12:11 in reply to 1 [link] [source]

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.

(4) By Richard Hipp (drh) on 2023-10-24 13:21:08 in reply to 2 [link] [source]

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.

(7) By ddevienne on 2023-10-24 14:46:07 in reply to 4 [link] [source]

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 :).

(9.2) By Nuno Cruces (ncruces) on 2023-10-24 16:57:39 edited from 9.1 in reply to 4 [link] [source]

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.


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.

(3) By anonymous on 2023-10-24 13:20:15 in reply to 1 [link] [source]

Another release note typo: "subsqueries".

(5) By David Raymond (dvdraymond) on 2023-10-24 14:14:34 in reply to 1 [link] [source]

The aggregate ORDER BY is mentioned in the patch notes, but on the aggregate functions page it's only mentioned in the last sentence of the group_concat()/string_agg() function.

Granted that's the only built-in function where it matters at the moment, but I think it should also make it into the railroad diagram at the top as it's allowed for any aggregate function (that isn't using *). And maybe a quick sentence at the top similar to the quick sentence for the filter-clause.

Thank you for this feature, it's definitely appreciated.

(6.2) By Larry Brasfield (larrybr) on 2023-10-24 23:21:13 edited from 6.1 in reply to 5 [link] [source]

(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.

(8) By Code Hz (codehz) on 2023-10-24 14:55:28 in reply to 1 [link] [source]

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

(10.2) By flavorjones on 2023-10-24 22:22:26 edited from 10.1 in reply to 1 [source]

On windows, seeing this compilation error:

shell.c: In function 'utf8_printf':
Provided configuration options:
shell.c:749:61: error: 'console_utf8' undeclared (first use in this function)
  749 |   if( stdout_is_console && (out==stdout || out==stderr) && !console_utf8 ){
      |                                                             ^~~~~~~~~~~~

which looks like it might have been caused by this commit:

Date:   2023-10-06 12:22:14 +0000

    Remove and unused static variable from shell.c.in in order to silence a
    harmless compiler warning.
    FossilOrigin-Name: 477577120b897bf15edc2a85e220d5c64a1d5a245354939269f6753d8140ac95

See this log for the full build.

(11) By mgr (mgrmgr) on 2023-10-24 19:27:06 in reply to 1 [link] [source]

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;

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;
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;
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;
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?

(12) By Richard Hipp (drh) on 2023-10-24 19:43:26 in reply to 11 [link] [source]

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

(13) By David Raymond (dvdraymond) on 2023-10-24 20:19:38 in reply to 12 [link] [source]

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

This will need some serious explaining in the docs if it's true.

The idea that ordering by a different field will give a different number of things concatenated is mind-boggling.

> 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

Why does ordering by something else suddenly mess up distinct? Why aren't there 5 things in every output?
It (intuitively "should be")  (DISTINCT name) ... take those distinct names and _then_  (ORDER BY narg)
DISTINCT (name, narg) ORDER BY narg

The ORDER BY term should not be taken into account in the DISTINCT part.

(P.S. Off topic: Does someone know how to get Markdown format to respect newlines or blank lines? It keeps eating those when I preview it)

(14) By TripeHound on 2023-10-24 21:30:05 in reply to 13 [link] [source]


(15) By mgr (mgrmgr) on 2023-10-24 22:39:50 in reply to 13 [link] [source]

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 ...

(19) By SeverKetor on 2023-10-25 00:59:39 in reply to 13 [link] [source]

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.

(20) By Larry Brasfield (larrybr) on 2023-10-25 01:31:35 in reply to 19 [link] [source]

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.

(21.3) By SeverKetor on 2023-10-25 20:43:28 edited from 21.2 in reply to 20 [link] [source]

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.



(29) By mgr (mgrmgr) on 2023-10-27 15:21:11 in reply to 20 [link] [source]

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


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.

(16) By mgr (mgrmgr) on 2023-10-24 22:45:57 in reply to 12 [link] [source]

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.

(17) By mgr (mgrmgr) on 2023-10-24 23:15:09 in reply to 11 [link] [source]

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:

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;


Spot on in that case.

(18) By Roger Binns (rogerbinns) on 2023-10-25 00:00:06 in reply to 1 [link] [source]

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?

(26) By Richard Hipp (drh) on 2023-10-25 11:39:08 in reply to 18 [link] [source]

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

(27) By Roger Binns (rogerbinns) on 2023-10-25 14:40:11 in reply to 26 [link] [source]

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.

(22) By Mark Lawrence (mark) on 2023-10-25 09:04:43 in reply to 1 [link] [source]

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?

(23) By jchd (jchd18) on 2023-10-25 09:32:23 in reply to 22 [link] [source]

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

(24) By Mark Lawrence (mark) on 2023-10-25 09:59:29 in reply to 23 [link] [source]

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...

(25) By Richard Hipp (drh) on 2023-10-25 11:27:31 in reply to 22 [link] [source]

"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.

(28) By Nuno Cruces (ncruces) on 2023-10-26 00:56:17 in reply to 1 [link] [source]

There's a typo in the strftime documentation:

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

(30) By Richard Hipp (drh) on 2023-10-30 19:47:33 in reply to 1 [link] [source]

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.

(33) By Gerry Snyder (GSnyder) on 2023-11-01 15:29:53 in reply to 30 [link] [source]

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!

(31) By anonymous on 2023-10-31 05:32:43 in reply to 1 [link] [source]

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

(32) By Holger J (holgerj) on 2023-11-01 14:42:15 in reply to 1 [link] [source]

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.

(34.1) By Nuno Cruces (ncruces) on 2023-11-02 09:48:41 edited from 34.0 in reply to 32 [link] [source]