H2 complains about the bad grammar, MySQL accepts it - However, bad syntax gives the right results: Column ... must be in the GROUP BY list;

Question

Note: Question was edited to include the tables and show what exactly is expected as a result.

Lets say we have an SQL Table generated by:

CREATE TABLE T1 (
  `a` INTEGER,
  `b` DATETIME,
  `c` VARCHAR(5)
);

INSERT INTO T1
  (`a`, `b`, `c`)
VALUES
  ('5678', '2008-01-01 12:00', '12.34'),
  ('5678', '2008-01-01 12:01', NULL),
  ('5678', '2008-01-01 12:02', NULL),
  ('5678', '2008-01-01 12:03', '23.45'),
  ('5678', '2008-01-01 12:04', NULL);

What I need to execute is

SELECT * FROM(
  SELECT a, b, c  from T1
)AS Q GROUP BY c ORDER BY a, b;

Which gives:

5678    2008-01-01 12:00:00     12.34
5678    2008-01-01 12:01:00     NULL
5678    2008-01-01 12:03:00     23.45

What H2 suggests (and accepts) is

SELECT * FROM(
  SELECT a, b, c  from T1
)AS Q GROUP BY a,b,c ORDER BY a, b, c;

Which gives

5678    2008-01-01 12:00:00     12.34
5678    2008-01-01 12:01:00     NULL
5678    2008-01-01 12:02:00     NULL
5678    2008-01-01 12:03:00     23.45
5678    2008-01-01 12:04:00     NULL

Per some of your suggestions, these are the queries and results.

Suggestion 1:

SELECT  max(a) as a, max(b) as b, c
FROM (
  SELECT a, b, c  from T1
) AS Q 
GROUP BY c 
ORDER BY a, b;

Gives

5678    2008-01-01 12:00:00     12.34
5678    2008-01-01 12:03:00     23.45
5678    2008-01-01 12:04:00     NULL

Suggestion 2:

SELECT * 
FROM (
  SELECT a, b, c from T1
) AS Q 
GROUP BY c, a, b 
ORDER BY a, b;

Gives

5678    2008-01-01 12:00:00     12.34
5678    2008-01-01 12:01:00     NULL
5678    2008-01-01 12:02:00     NULL
5678    2008-01-01 12:03:00     23.45
5678    2008-01-01 12:04:00     NULL

======================================

I need to obtain the results executed by the first query.

How should I properly change the syntax to accommodate the H2 demands while getting a desired result ?

This is unfortunate behaviour in MySQL. Clearly, the first query is gibberish. For further help, please see: [Why should I provide an MCRE for what seems to me to be a very simple SQL query?](https://meta.stackoverflow.com/questions/333952/why-should-i-provide-a-minimal-reproducible-example-for-a-very-simple-sql-query) — Strawberry, Aug 11 '20 at 14:13
Why do you consider the results of the 1st query correct? Mysql returns just *any* value for the column c in this case. — forpas, Aug 11 '20 at 14:16

score 1 · Accepted Answer · answered Aug 11 '20 at 16:03

Now, for the modified question. According to the sample data, in H2 you can do:

select t1.*
from t1
join (
  select c, min(b) as min_b from t1 group by c
) x on t1.c is not distinct from x.c and t1.b = x.min_b
order by t1.b;

Result:

A     B                      C     
----  ---------------------  ------
5678  2008-01-01 12:00:00.0  12.34 
5678  2008-01-01 12:01:00.0  <null>
5678  2008-01-01 12:03:00.0  23.45

The sample data script to reproduce the case is:

create table t1 (
  a integer,
  b datetime,
  c varchar(5)
);

insert into t1 (a, b, c) values
  ('5678', timestamp '2008-01-01 12:00:00', '12.34'),
  ('5678', timestamp '2008-01-01 12:01:00', null),
  ('5678', timestamp '2008-01-01 12:02:00', null),
  ('5678', timestamp '2008-01-01 12:03:00', '23.45'),
  ('5678', timestamp '2008-01-01 12:04:00', null);

score 0 · Answer 2 · answered Aug 11 '20 at 14:36

H2 is behaving correctly. Older versions of MySQL allow the query to execute even though it is invalid according to ANSI/ISO SQL and nearly all other implementations of SQL (except SQLite).

I wrote an example explaining it in Reason for Column is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause

You must correct your query to work with H2.

The rule is that every column of the select list must either be inside an aggregate function or else named in the GROUP BY clause.

You could solve it this way:

SELECT MAX(a) AS a, MAX(b) AS b, c FROM(
  SELECT a, b, c  from T1
)AS Q GROUP BY c ORDER BY a, b;

This satisfies the rule because a and b are in aggregate functions, whereas c is in the GROUP BY.

MySQL 5.7 and later default to behaving correctly, enforcing the group by semantics.

score 0 · Answer 3 · answered Aug 11 '20 at 14:36

0

You are probably using MySQL 5.7.5 or older that used to accept these kinds of queries. If you are using a newer MySQL you probably enabled the old/malformed syntax.

The query:

SELECT * 
FROM (
  SELECT a, b, c  from T1
) AS Q 
GROUP BY c 
ORDER BY a, b;

is malformed. Why? Because columns that are not included in the GROUP BY clause (a and b in this case) must be aggregated in the select list. Your select list includes all columns, and none aggregated since you used *.

This malformed MySQL query does not adhere to the SQL Standard, and produces random values for the non-aggregated columns. It's an actual bug of your application.

H2, however, rightfully rejects it and requires you to fix it. You can do any valid alternative such as:

SELECT c, max(a) as a, max(b) as b
FROM (
  SELECT a, b, c  from T1
) AS Q 
GROUP BY c 
ORDER BY a, b;

or maybe:

SELECT * 
FROM (
  SELECT a, b, c from T1
) AS Q 
GROUP BY c, a, b 
ORDER BY a, b;

answered Aug 11 '20 at 14:36

The Impaler

45,731
9
39
76

Tried, but gives different results – 3xCh1_23 Aug 11 '20 at 14:55
@DamirOlejar By "different" you probably mean "correct". – The Impaler Aug 11 '20 at 15:02
No, I mean, not desired. :) Syntax is correct but results are not what I need. I need the same results the bad syntax produces. – 3xCh1_23 Aug 11 '20 at 15:11
@DamirOlejar I know... it was a joke ;-) I think you probably will need to include some sample data and the expected result to help you rewrite the best query for your needs. This answer only addresses the malformed syntax of the query. – The Impaler Aug 11 '20 at 15:12

score 0 · Answer 4 · answered Aug 11 '20 at 14:50

0

If you need a query for H2 only, you can use non-standard PostgreSQL-style DISTINCT ON clause instead of grouped query:

SELECT DISTINCT ON(C) A, B, C FROM T1 ORDER BY A, B;

A and B values will be chosen based on ORDER BY clause.

answered Aug 11 '20 at 14:50

Evgenij Ryazanov

6,960
2
10
18

Interesting, will try. – 3xCh1_23 Aug 11 '20 at 14:55
1

You need a recent version of H2, BTW, different outdated versions don't support it. Such query will also work in the PostgreSQL and its forks, but not in other DBMS. – Evgenij Ryazanov Aug 11 '20 at 15:01
Problem is that I have a very complex query and going from MySQL to PostgreSQL mode would be too much. However, this solution is why I was using the GROUP BY – 3xCh1_23 Aug 11 '20 at 15:10

H2 complains about the bad grammar, MySQL accepts it - However, bad syntax gives the right results: Column ... must be in the GROUP BY list;

4 Answers4