How to enforce strict handling of GROUP BY with non-aggregate / bare columns or UPDATE with multiple-row SELECT in SQLite

Question

Unlike some other RDBMS, SQLite does allow for bare columns in SELECT querys with GROUP BY, i.e. not all selected columns need to be part of the group by or aggregate functions otherwise. In this case, the first value in the result set will be selected for the grouped output row, regardless of whether this value is constant across all entries in the result set.

Something similar seems to happen in an UPDATE query with SELECT where the SELECT returns multiple rows, where only one row can be updated. In this case the first result from the SELECT is used for updating the target row.

I'm wondering if it's possible to force SQLite to be more strict in such cases and throw an error.

For illustration, consider this fiddle. Which will update both B properties with the first result from the SELECT. What I would like instead is that an error is generated.

Answer 1

SQLite implicitly adds a "LIMIT 1" to all scalar subqueries. There is no built-in mechanism to change this; you would have to modify SQLite's source code.