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 On 4 Feb., 14:38, James Moger <[email protected]> wrote: > 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.
