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

aggregategroup bysqlite

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.

Best Answer

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.

Related Solutions

Mysql – Is it possible to reduce a group to a row based on some criteria

The traditional solution, the one you may find in books, is to do a self join: first find that "max date per group" of yours, then join to self table on rows with said max date.

However, some hacks allow you to avoid that. Consider the following query:

SELECT
  MAX(date_added) AS date_added,
  SUBSTRING_INDEX(GROUP_CONCAT(some_column ORDER BY date_added DESC), ',', 1) AS some_column,
  SUBSTRING_INDEX(GROUP_CONCAT(another_column ORDER BY date_added DESC), ',', 1) AS another_column
FROM t
GROUP BY whatever

GROUP_CONCAT is an aggregation function which implodes values onto one string. It allows for ORDER BY, which we utilize via ORDER BY date_added DESC so as to implode our desired value first. We then slice up the first token in the string via SUBSTRING_INDEX.

The downside here (apart from making the query quite the frightening appearance) is that your numerical values are transformed into texts. Typically no big deal with SQL, but please be aware.

There's another option where you do a semi-self-join, a much lighter one; you will have to give up usage of index. It's quite long to describe; it still uses GROUP_CONCAT and SUBSTRING_INDEX, but only for the purpose of creating a derived table with only relevant keys. This derived table is then joined with original table. See an example in SQL: selecting top N records per group, another solution.

Select all rows with a minimum value

As you have seen, a simple GROUP BY will not work because it would return only one record per group.

Your join works fine. For a large table, it will be efficient only if there is an index on the join columns (num and text).

Alternatively, you could use a correlated subquery:

SELECT *
FROM t
WHERE num = (SELECT MIN(num)
             FROM t AS t2
             WHERE t2.text = t.text);

SQLFiddle

When being executed, this query does not require a temporary table (your query does for the result of u), but will execute the subquery for each record in t, so text should be indexed. (Or use an index on both text and num to get a covering index.)

Best Answer

Related Solutions

Mysql – Is it possible to reduce a group to a row based on some criteria

Select all rows with a minimum value

Related Question