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.
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.
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`
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.
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.
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.
> 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 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.
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?
> 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).
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!
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.
37 comments
[ 1.8 ms ] story [ 85.9 ms ] threadI'm shocked, truly.
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."
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...
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.
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.
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'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.
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
> 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).
> 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.
> 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.
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.
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, '|')`.