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
--
You received this message because you are subscribed to the Google Groups "H2
Database" group.
To post to this group, send email to [email protected].
To unsubscribe from this group, send email to
[email protected].
For more options, visit this group at
http://groups.google.com/group/h2-database?hl=en.
