GROUP BY constant vs empty grouping set

[AnyhowStep]

MySQL 5.7 and SQLite 3.30 do not support the empty grouping set specified by the SQL standard.

The syntax for it is,

GROUP BY ()

---

The correct behavior for GROUP BY () is that it should always produce one row.

And it means that no column may be referenced in a non-aggregate expression.

---

If you Google for a workaround, you'll invariably encounter these,

• https://blog.jooq.org/2018/05/25/how-to-group-by-nothing-in-sql/
• https://www.jooq.org/translate/

However, the information in the article is wrong. The translator is also wrong.

In MySQL and SQLite, the following are not the same as GROUP BY (),

• GROUP BY <non-integer-constant>
• GROUP BY (SELECT 1 FROM DUAL)
• (SELECT 1 x) AS dummy GROUP BY dummy.x

They cause zero rows to be produced, sometimes. The solution for MySQL and SQLite is to just... Not use a GROUP BY clause, and not reference any columns in a non-aggregate expression.

[AnyhowStep]

However, if you don't use the GROUP BY clause, you may accidentally write a query that uses columns in a non-aggregate expression!

This is usually a bad thing, and being able to use GROUP BY () explicitly would be nice.

But this library should be enforcing these for you, and you shouldn't need to write SQL strings, normally. So, this should not be a problem.