different behavior between h2 and oracle

[Vito De Tullio]

Hi.

I'm using h2 with ";MODE=Oracle" in the jdbc uri

I'm struggling with a difference in behavior between h2 (that I use in local tests) and oracle db.

simplified example:

CREATE TABLE foo (id number PRIMARY KEY, label_one varchar2(100));
CREATE TABLE bar (foo_id number, n number);
CREATE TABLE baz (label_two varchar2(100) PRIMARY KEY, ord_date date);

INSERT INTO foo(id, label_one) VALUES (1, 'A');
INSERT INTO foo(id, label_one) VALUES (2, 'B');
INSERT INTO bar(foo_id, n) VALUES (1, 0);
INSERT INTO bar(foo_id, n) VALUES (2, 0);
INSERT INTO baz(label_two, ord_date) VALUES ('A', DATE '2019-01-01');

SELECT foo.id, max(bar.n), baz.ord_date, foo.label_one
FROM foo
INNER JOIN bar ON foo.id = bar.foo_id
LEFT OUTER JOIN baz ON foo.label_one = baz.label_two
GROUP BY foo.id, foo.label_one
ORDER BY baz.ord_date

this query returns

ID|MAX(BAR.N)|ORD_DATE           |LABEL_ONE|
--|----------|-------------------|---------|
 2|         0|                   |B        |
 1|         0|2019-01-01 00:00:00|A        |

on h2, but gives me "ORA-00979: not a GROUP BY expression" on oracle.

Is there some additional flag that can tell h2 to behave like oracle?

[Evgenij Ryazanov]

Hello.

Your question was already answered in another topic:

https://groshouldn'tups.google.com/forum/#!topic/h2-database/mricrmhHQ8k

You can't use H2 to check validity of your queries for Oracle, each DBMS is different.