SQLite 3.44: Interactive release notes | Hacker News Reader

SQLite 3.44: Interactive release notes

antonz.org

162 pointsmarcobambini35 comments

I can definitely see where ORDER BY in aggregates would be useful - I recently used it with Postgres, and didn't even know it's not available in SQLite. Often you can do the sorting in the app code, but it's nice to do directly in the query.

Other niche but useful features I've found with GROUP BY:

1. You can filter the rows used for the aggregate using a FILTER clause [1]: `group_concat(name order by name desc) filter where(salary > 100)`. While you could do the same filter in the main query, this form would still give you departments where all employees are filtered out.

2. When selecting non-aggregate values in a GROUP BY, an arbitrary row is typically picked. You can control which one is picked by using min() or max() on a different column [2]. Example: `select depertment, name as best_earner, max(salary) as best_salary from employees group by department`

[1]: https://www.sqlite.org/lang_aggfunc.html

[2]: https://www.sqlite.org/lang_select.html#bare_columns_in_an_a...

Surprised they let you pick something without defining the aggregate as it seems really dangerous.

Not entirely sure how far it extends but I’ve noticed that Postgres will let you select any rows from the table that way if you’ve included the primary key in the group by. Back when I used sql server / MySQL you needed to specify them all in the group by.

SQL Server forces determinism here: anything projected when there is a GROUP BY must be one of the grouping properties, an aggregate, or a constant.

mySql does allow other columns to be projected, which has always felt wrong to me as I like my results from the DB to be dertemanistic. I wasn't aware sqlite allowed it too, though I've not directly used sqlite much. It is something that causes issues migrating to other DBs (or when trying to be cross-DB compatible). Often it is used accidentally and by chance gives good results, where is is used deliberately IME it is where the same value will always return due to properties of the that data being queried (in which case the "fix" for other DBs is to use an aggregate like MIN or MAX). Sometimes accidentally use leads to subtle bugs which, as you say, can be rather dangerous.

Note though that these days this can only be used dangerously if the ONLY_FULL_GROUP_BY option is on, and it is off by default. Allowing it in safe circumstances actual makes modern mySql more standards compliant in this respect, see https://dev.mysql.com/doc/refman/8.0/en/group-by-handling.ht... for more detail.

Sometimes I'd like to pick a non-aggregated, non-grouped column that I know will be the same for the whole group. (I often write SQL in GCP's BigQuery that forbids indeterminate results in group bys) I'd like to have an aggregate function something like assert_same(), that picks the value, and errors when some of the values in the group are unexpectedly different.

You can do that with standard aggregates by comparing min() and max().

Or sometimes you can do something like FIRST() OVER (ORDER BY NULL).

I don't think that errors if the group contains different values...?

In postgres you can array_agg(distinct) and error on the application side if there is more than one value.

Snowflake has ANY_VALUE() for this: https://docs.snowflake.com/en/sql-reference/functions/any_va...

From the full release notes:

> The PRAGMA case_sensitive_like statement is deprecated, as its use when the schema contains LIKE operators can lead to reports of database corruption by PRAGMA integrity_check.

Without enabling that pragma, you get the default behavior where 'a' LIKE 'A' is true.

This is unfortunate, since case-sensitive comparisons seem like an important tool.

I'm not sure there's another built-in option for this. SQLite doesn't ship with a regex engine (you have to wire up an application-defined SQL function, if your driver supports it).

TIL sqlite's LIKE is case insensitive, I had no idea. That's almost as annoying as FKs having to be enabled on a per-connection basis.

> I'm not sure there's another built-in option for this. SQLite doesn't ship with a regex engine

Apparently sqlite has a GLOB operator (and function) which is case sensitive, and is slightly more expressive.

The docs for the pragma are confusing me about whether or not GLOB's behavior is also affected:

> This pragma uses sqlite3_create_function() to overload the LIKE and GLOB functions, which may override previous implementations of LIKE and GLOB registered by the application. This pragma only changes the behavior of the SQL LIKE operator. It does not change the behavior of the sqlite3_strlike() C-language interface, which is always case insensitive.

You're right though, GLOB defaults to being case-sensitive!

> GLOB is case sensitive, unlike LIKE.

Indeed the docs are very confusing but I think what it's saying is it uses sqlite3_create_function to replace the "default" implementation of LIKE (which calls the like function: https://www.sqlite.org/lang_corefunc.html#like).

It might be mentioning GLOB because it also resets its function (to the default https://www.sqlite.org/lang_corefunc.html#glob) so a client which installed its own glob() function would get an odd behaviour. I can't say I understand why it resets glob though.

From looking at archive.org, until 2011 the doc said:

> This pragma only works if the built-in like() SQL function has not been overloaded using sqlite3_create_function().

then in mid 2011 it changes to

> This pragma uses sqlite3_create_function() to overload the LIKE and GLOB functions, which may override previous implementations of LIKE and GLOB registered by the application.

but the only changelog entry about case_sensitive_like before then was its introduction back in 2005. 3.0.6 beta (september 2004) does indicate

> Combine the implementations of LIKE and GLOB into a single pattern-matching subroutine.

but the link still seems debatable, surely overriding glob() does not normally change the behaviour of like(), it's only the default impls which are shared?

Anyway if someone wants to code-dive sqlite, that's an open question.

> Aggregate functions can now include an ORDER BY clause after their last parameter.

Hopefully that means we'll soon get support for `DISTINCT` in `GROUP_CONCAT`.

Atm you can do `group_concat(x, '|')` and `group_concat(distinct x)`, but you can't do `group_concat(distinct x, '|')`.

Yeah this would be nice

I found SQLite grow bigger and bigger: - For 3.7.8, its default build on x86 is only 350KiB (1). - Now it is 650 KB (2).

This is really worrying, especially for the field of embedded development.

[1] https://www.hwaci.com/sw/sqlite/footprint.html [2] https://www.sqlite.org/footprint.html

Version 3.7.8 is over a decade old and the 64-bit version of it is actually 409KB. I'd say the rate of growth is impressively small. Are you locked in to using only default builds for embedded?

> As you probably know, the group_concat function is SQLite (and MySQL) specific. In other databases (namely, PostgreSQL and SQL Server) the function is called string_agg. As of 3.44, SQLite also supports string_agg as an alias for group_concat(expr, sep) (...) Now the only black sheep is Oracle, where the poor function is called listagg.

Minor nitpick, but why not go all the way and add the Oracle syntax while they were at it? Is there a technical reason not to? (I've never used Oracle)

I don't know why exacly, but there's a good general reason not to touch anything made by Oracle: lawsuits.

I don't think they're doing it to match a DB vendor consensus I suspect they're doing it because postgres specifically does.

I'm not aware of any official expression of this, but for the past several years at least sqlite seems to be treating what postgres does as a sort of spec, and aligning with its behavior unless they have a specific reason not to. Which is great imo.

Ah yes that would explain it, thanks.

Nice... SQLite remains an exceptional piece of software !!

Is there any chance SQLite will sometime work stored under CIFS/SMB shares? I wanted to store all my container /config directories under NAS but can't with the ones using SQLite.

It will sometimes work. Eventually it’ll get hopelessly corrupted because that’s what network filesystems do to shared files.

Depending what NAS you’re using maybe it supports iSCSI? It can be a better option for things where you need to make sure only one system accesses the data at a time.

It probably works if you don't have concurrent access.

Can we please get array support and aggregates like collect_set

SQLite's json1 works pretty well for arrays

SQlite is good as software, but PR for it is annoying and to much.

SQLite comes up a lot on HN, but it's not PR - it's content that others find interesting, myself included. In most cases it's not even official SQLite content being posted, but rather other projects or blogs around it.

If anything, SQLite deserves _more_ PR. It's a fantastic piece of software, and at my company we use it _a lot_; mostly for testing, but also for some micro-services where data amount is small-ish, and almost never change (postal codes, country data etc.)

I hate it when I see .NET Core tutorials (as an example) where they fire up MSSQL in EF just for tiny amounts of data, and then using a bloated DB-viewer GUI to show what's in the database, instead of just going to the CLI asking SQLite "directly."

SQLite works pretty great for managing larger or more dynamic datasets, too; it’s just where concurrent access begins to increase that it stops being appropriate.

Concurrent writes, not concurrent accesses. Especially after enabling wal mode.

It's almost as if a site for tech has bias for tech commonly enjoyed by the readers of the said site.

I'm shocked, truly.

As far as I can tell the SQLite team's main PR strategy is consistently and quietly releasing software that's so good that large numbers of people do PR for them, for free.

Reply on news.ycombinator.com