SQLite 3.44: Interactive release notes

by marcobambini • 3 years ago

162 35 3 years ago

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

matharmin3• 3 years ago

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.

mpixel• 3 years ago

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.

thatwasunusual• 3 years ago

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

simonw• 3 years ago

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.

matharmin3• 3 years ago

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

aidos• 3 years ago

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.

dspillett• 3 years ago

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.

GolDDranks2• 3 years ago

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.

pixel_tracing• 3 years ago

Can we please get array support and aggregates like collect_set

matharmin3• 3 years ago

SQLite's json1 works pretty well for arrays

karolist• 3 years ago

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.

masklinn5• 3 years ago

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

dgl• 3 years ago

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.

remram• 3 years ago

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

bambax2• 3 years ago

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)

ranting-moth• 3 years ago

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

giraffe_lady• 3 years ago

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.

bambax2• 3 years ago

Ah yes that would explain it, thanks.

stef-13013• 3 years ago

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

howtofly• 3 years ago

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

HumanOstrich• 3 years ago

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?

spiffytech2• 3 years ago

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

masklinn5• 3 years ago

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.

spiffytech2• 3 years ago

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.

stux• 3 years ago

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, '|')`.

jddj• 3 years ago

Yeah this would be nice

SQLite 3.44: Interactive release notes

Related Stories