nikos dimtrakas - 2023-03-28

This was originally asked at stackoverflow according to the instructions on https://ucanaccess.sourceforge.net/site.html#about but never got answered. I am trying here.

The standard behaviour of SQL is to require all non-aggregated columns appearing in the SELECT clause (or HAVING or ORDER BY) to appear in the GROUP BY clause. For example the following would fail (because of b not being aggregated and not appearing in the GROUP BY clause):

SELECT a, b, COUNT(*) FROM t GROUP BY a

Access will give an error like this:

Your query does not include the specified expression 'b' as part of an aggregate function.

But UCanAccess (v 5.0.1) seems to be trying to be "nice" (or SQL 99 compliant) and allows this. There is a similar behaviour in MySQL and there it can be configured with ONLY_FULL_GROUP_BY. Most other products (SQL Server, DB2, ...) only have the strict mode (as per SQL 92).

Is there a way to configure UCanAccess to use the strict mode? I would recommend that the default should be the strict mode, but at least there should be a way to configure this. Perhaps as a parameter in the connection URL. Is there something like this? Perhaps something like this:

jdbc:ucanaccess://db.accdb?strict_GROUP_BY=true