Error only if there is some value

[Vito De Tullio]

Hi

this is a simplified example:

CREATE TABLE foo (id number PRIMARY KEY, label varchar2)
CREATE TABLE bar (id number PRIMARY KEY, label varchar2)

INSERT INTO foo(id, label) VALUES (1,'one')
INSERT INTO foo(id, label) VALUES (2,'one')
INSERT INTO foo(id, label) VALUES (3,'two')
-- INSERT INTO bar(id, label) VALUES (2, 'two')
INSERT INTO bar(id, label) VALUES (4, 'three')

SELECT foo.label, max(foo.id) AS maxid, bar.label
FROM foo
left outer join bar on foo.id=bar.id
GROUP BY foo.label

with the commented out insert, I have no errors (and the obvious result of

LABEL|MAXID|LABEL|

-----|-----|-----|
one  |    2|     |
two  |    3|     |

but with the insert the join will have the error "SQL Error [90016] [90016]: Column "BAR.LABEL" must be in the GROUP BY list; SQL statement:
SELECT foo.label, max(foo.id) AS maxid, bar.label
FROM foo
left outer join bar on foo.id=bar.id
GROUP BY foo.label [90016-200]"

I know that the error is in the query (i need to add bar.label to the GROUP BY or use a reducing function in the column)

but WHY I don't have this check "statically" before the query has been effectively run?

Is there a way to force the check even on empty tables?

[Evgenij Ryazanov]

Hello.

There is an optional feature T301, “Functional dependencies” in the SQL Standard. H2 and some other DBMS support this feature.

Only DBMS without its support may throw an exception immediately.

In DBMS with its support select expressions and where cause may reference non-aggregated columns when each such column is functionally dependent on grouping columns (has the same value for each row in the group). In these DBMS, including the H2, validity of your query depends on the data in the table.

This feature can't be disabled.