7

With a table like:

first_name    last_name
------------------------
jack          frost
john          wayne
betty         white
null          jackson

And this query:

select first_name, COUNT(first_name) from people;

MySQL and Postgres won't even run it. They somehow know that this requires a GROUP BY.

SQLite produces a result with a single row: jack 3

How do MySQL and Postgres know that this requires a GROUP BY? Why is GROUP BY even required?

Why is SQLite's result a single row? I would have expected the output to be something like:

jack 3
john 3
betty 3
null 3
5
  • 5
    Why is GROUP BY even required? Because the output list contains both aggregated and non-aggregated expressions. For query's logical correctness all non-aggregated expressions must be included into GROUP BY expression.
    – Akina
    Nov 15, 2022 at 18:38
  • 1
    It doesn't work, really. The parser fails to pick up on a syntax error which should just be rejected, and passes the query onto the rest of the engine which tries to do something useful with it. What you are seeing is an undefined behaviour, so any explanation of how/why it works is not guaranteed to remain the case in future versions/forks/patches of sqlite. Nov 16, 2022 at 10:26
  • 1
    @DavidSpillett "undefined behaviour" I agree 100%. There is a mention of it though in SQLite documentation: sqlite.org/… Nov 16, 2022 at 16:38
  • @ypercubeᵀᴹ - ah, in that case it is defined behaviour in the case of sqlite, and not likely to change in a breaking manner in future versions (as IIRC the project is usually pretty good wrt backwards compatibility), at least for the couple of cases list there (which doesn't include the COUNT() aggregate mentioned here and quite a few other cases). I'd much prefer the parser errored out in the undefined cases, to avoid confusion/bugs, but every DB has a few things I don't like! Nov 16, 2022 at 17:22
  • 2
    Just found that there is more detail in SQLite SELECT, sections 2.4 and especially 2.5 Nov 16, 2022 at 17:27

3 Answers 3

13

SQLite does not adhere to the standard in this regard. See https://www.sqlite.org/quirks.html#aggregate_queries_can_contain_non_aggregate_result_columns_that_are_not_in_the_group_by_clause

SQLite refers to first_name outside of the aggregate as a "bare" column, see section 2.5. in https://www.sqlite.org/lang_select.html#resultset

You can think of your query as if it looks like:

SELECT first_name, COUNT(first_name)
FROM tbl
GROUP BY () -- empty set

This means that your aggregate function applies to all rows in the result set, i.e.

jack, count({jack, john, betty, null})
john, count({jack, john, betty, null})
betty,count({jack, john, betty, null})
null, count({jack, john, betty, null})

null is not taken into consideration by count, so we end up with:

jack, 3
john, 3
betty,3
null, 3

Since an aggregate function (in this case COUNT) is supposed to aggregate per group, we should get 1 row in the result (we only have 1 group, the group for the empty set). Therefore, one row is randomly picked, say

john, 3

One might ask why not deviate further from the standard and allow a 4-row result? It's just a guess, but I suspect that the intention is to fix the first deviation eventually (probably via a setting similar to MySQL). I therefore suspect that they have no intention to add more fuel to the fire, when they will eventually try to fix the root cause.

If your intention was to count all rows (excluding nulls) for each first_name, you can use a window function:

select first_name, count(first_name) over () from tbl;

Fiddle

3
  • I'm with you up until from there, one row is randomly picked. What makes it need to only return one row, instead of all 4 rows?
    – John
    Nov 15, 2022 at 23:25
  • 3
    COUNT returns 1 row per group. Without a group by (or group by the empty set), only one row is returned. Nov 16, 2022 at 5:17
  • 1
    @Lennart-SlavaUkraini excellent explanation. There is more documentation you might want to use (or add a link to it in the answer): SQLite SELECT sections 2.4 and especially 2.5 Nov 16, 2022 at 17:00
5

This is not how the COUNT() function is supposed to be used.

According to the SQLite Documentation, COUNT() is an aggregate function just like MIN(), MAX(), SUM(), AVERAGE(), and GROUP_CONCAT().

Without the GROUP BY clause, COUNT() would aggregate the entire table, treating it as a group.

The latest version of MySQL and PostgreSQL would not allow for that. Very old versions of MySQL would.

In your case, what SQLite evidently did was

  • give you the first non-null value it saw for first_name
  • perform COUNT() aggregation of all non-null values of first_name
7
  • lol Rolando with "very old". Is version 5.6 really very old? ;) Nov 15, 2022 at 18:30
  • The latest version of MySQL and PostgreSQL would not allow for that. Very old versions of MySQL would. About MySQL - no. Any MySQL version (starting at least from 4.0) may either accept or reject such query. This depends on MySQL server settings, more precisely on the presence of ONLY_FULL_GROUP_BY setting in current SQL Mode.
    – Akina
    Nov 15, 2022 at 18:35
  • 1
    This does not answer the question. OP asked about "How do MySQL and Postgres know that this requires a GROUP BY?". Whereas both select first_name from people; and select COUNT(first_name) from people; does not need in GROUP BY - i.e. this is not related to aggregate function presence.
    – Akina
    Nov 15, 2022 at 18:41
  • @Akina the default for MySQL was to accept this nonsense though, up to and including 5.6. Then the default changed to not accepting it. So Rolando is correct (assuming he is talking about the default behaviour). Nov 16, 2022 at 15:52
  • 1
    It's also ironic that SQLite probably copied this behaviour to be compatible with MySQL ;) Nov 16, 2022 at 15:52
3
  • Without a GROUP BY, COUNT (or SUM, etc) scans the entire table and delivers and summarizes the tally in a single row.

  • Since there will be only one row in your query, which first_name should it show? (This quandary leads to the "invalidity" of the SQL.)

  • COUNT(x) checks x for being NOT NULL. This is usually not needed, so say simply COUNT(*).

  • Here's a sample of a 'good' query:

    SELECT first_name,
           COUNT(*) AS "num people with that first_name"
        FROM tbl
        GROUP BY first_name;
    
  • To get jack 3, do something like

    SELECT ANY_VALUE(first_name),
           SUM(IF(first_name IS NULL, 0, 1))
        FROM tbl
    
  • There are various other ways to phrase the above. But note that every column has an aggregate and there is no GROUP BY -- hence 1 row summarizing the entire table.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.