My nested select works in MySQL, not in H2. Can anyone lend a hand?

[James Moger]

I'm not an SQL master and I recognize that MySQL sometimes does things that aren't spec-compliant.  :)

I have a 3x nested query which is supposed to return out-of-date wiki articles where out-of-date is defined as a wiki article that has been edited and not subsequently exported as html to another system.

SELECT * FROM (

 SELECT * FROM (

  SELECT wiki_topics.*, wiki_revisions.linked_revision AS published_rev FROM wiki_topics, wiki_revisions WHERE wiki_topics.topic_id = wiki_revisions.topic_id AND wiki_revisions.revision_type=8 ORDER BY wiki_revisions.revision_date DESC)

 AS all_publication_records GROUP BY topic_id)

AS latest_topic_publications WHERE revision_id != published_rev

This query works as expected in MySQL (db I'm trying to transition from) but fails in H2 with the following (truncated) message:

Message:

    org.h2.jdbc.JdbcSQLException: Column "ALL_PUBLICATION_RECORDS.PUBLISHED_REV" must be in the GROUP BY list; SQL statement:  [90016-150]

Level:

    SEVERE

Stack Trace:

Column "ALL_PUBLICATION_RECORDS.PUBLISHED_REV" must be in the GROUP BY list; SQL statement: SELECT * FROM ( SELECT * FROM ( SELECT wiki_topics.*, wiki_revisions.linked_revision AS published_rev FROM wiki_topics, wiki_revisions WHERE wiki_topics.topic_id = wiki_revisions.topic_id AND wiki_revisions.revision_type=8 ORDER BY wiki_revisions.revision_date DESC) AS all_publication_records GROUP BY topic_id) AS latest_topic_publications WHERE revision_id != published_rev [90016-150]

    org.h2.message.DbException.getJdbcSQLException(DbException.java:327)

    org.h2.message.DbException.get(DbException.java:167)

    org.h2.message.DbException.get(DbException.java:144)

    org.h2.expression.ExpressionColumn.updateAggregate(ExpressionColumn.java:147)

    org.h2.command.dml.Select.queryGroup(Select.java:339)

    org.h2.command.dml.Select.queryWithoutCache(Select.java:592)

    org.h2.command.dml.Query.query(Query.java:256)

    org.h2.command.dml.Query.query(Query.java:226)

    org.h2.index.ViewIndex.find(ViewIndex.java:233)

    org.h2.index.IndexCursor.find(IndexCursor.java:136)

    org.h2.table.TableFilter.next(TableFilter.java:311)

    org.h2.command.dml.Select.queryFlat(Select.java:498)

    org.h2.command.dml.Select.queryWithoutCache(Select.java:597)

    org.h2.command.dml.Query.query(Query.java:256)

    org.h2.command.dml.Query.query(Query.java:226)

    org.h2.command.dml.Query.query(Query.java:37)

    org.h2.command.CommandContainer.query(CommandContainer.java:78)

    org.h2.command.Command.executeQuery(Command.java:178)

    org.h2.jdbc.JdbcStatement.executeInternal(JdbcStatement.java:174)

    org.h2.jdbc.JdbcStatement.execute(JdbcStatement.java:153)

If I add ALL_PUBLICATION_RECORDS.PUBLISHED_REV to the GROUP BY clause I do not get the proper results.

Am I doing something obviously wrong?

Does anyone have a recommendation for a smarter query (hopefully one that is portable between MySQL and H2)?

Thanks,

-J

[Lukas Eder]

Hi James,

Normally, I'm just a reader of this news group but I think I can
answer this one for you, because technically, it's the MySQL query
that doesn't "work as expected". You said it yourself, "MySQL
sometimes does things that aren't spec-compliant". Check out this
question and its answers on stackoverflow:
http://stackoverflow.com/questions/1023347/mysql-selecting-a-column-not-in-group-by

MySQL's documentation thereof:
http://dev.mysql.com/doc/refman/5.0/en/group-by-hidden-columns.html

H2 is behaving like any other RDBMS and does not allow selecting
fields that are not either
- fields from the GROUP BY clause
- aggregated fields

Try either solution (which ever best suits your actual needs):
- add the wiki_revisions.linked_revision to the GROUP BY clause
- select something like MAX(wiki_revisions.linked_revision)

Cheers
Lukas

[James Moger]

Thanks Lukas,

I got sidetracked with other projects but will be able to review this today.  I appreciate your pointers!

-J