Define rule for GROUP BY

[ImeevMA]

Consider such query:

CREATE TABLE t1(i INT PRIMARY KEY, p INT, q INT);
INSERT INTO t1 VALUES(1,100,2), (2,5,2), (3,123,4), (4,17,4);
SELECT p, min(q) FROM t1;
SELECT p, max(q) FROM t1;
SELECT p, q FROM t1 GROUP BY q;

Results:

PostgreSQL:

ERROR: column "t1.p" must appear in the GROUP BY clause or be used in an aggregate function

MS SQL Server:

Column 't1.p' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

SQLite:

p   min(q)
100   2

p   max(q)
123   4

p   q
5   2
17   4

MySQL:

p   min(q)
100   2

p   max(q)
100   4

p   q
100   2
123   4

Currently we have the same results as SQLite3. But I am not sure, that this is right. What should we do in this case? @pgulutzan @unera @Mons

[Mons]

I'm convinced, that the behaviour of Pg and MSSQL is the most correct.

[igormunkin]

Nobody asked me, but I believe that MySQL and SQLite results are both invalid. Hence, I'm for PG and MSSQL behaviour. What does standard say for it, BTW?

[unera]

Agree @igormunkin

[pgulutzan]

MySQL's behaviour is described (and in my opinion it is described well) in the manual:
https://dev.mysql.com/doc/refman/8.0/en/group-by-handling.html
The standard feature that it refers to, T301, is an optional feature and you'll notice that what the MySQL manual is really saying (without saying it) is: it's the user's responsibility to make sure the functional dependency is there, or to turn the feature off.

In the Tarantool manual,
https://www.tarantool.io/en/doc/latest/reference/reference_sql/sql_statements_and_clauses/#group-by-clause
I mentioned our behaviour thus:
"
Limitations:
SELECT s1, s2 FROM t GROUP BY s1; is legal.
...
"
That is, we don't regard this behaviour as a desirable feature. But being compatible with MySQL might be okay, provided that we go into the same level of detailed explanation.