Hello. It may be surprising, but validity of some queries depends on the data.
H2 and some other DBMS support optional feature T301, “Functional dependencies” from the SQL Standard. When this feature is supported select expressions and where clause may reference non-aggregated columns when each such column functionally depends on grouping columns (has the same value in all source rows in each particular group). Therefore when your data satisfies this condition, there is no error. When some column has different values within a group such exception is raised. In DBMS without this optional feature queries like that must be rejected unconditionally. SELECT A, COUNT(C) FROM TEST GROUP BY A is a valid query. SELECT A, B, COUNT(C) FROM TEST GROUP BY A, B is an another valid query. SELECT A, B, COUNT(C) FROM TEST GROUP BY A is not valid for DBMS without the mentioned feature, and may be either valid or invalid for DBMS with this feature; it is valid only when rows with the same value of A have the same value of B. You need to fix your query somehow. -- You received this message because you are subscribed to the Google Groups "H2 Database" group. To unsubscribe from this group and stop receiving emails from it, send an email to [email protected]. To view this discussion on the web visit https://groups.google.com/d/msgid/h2-database/8bae9292-985f-40c2-85c8-d35a83565fd0n%40googlegroups.com.
