Opened 7 years ago

Closed 7 years ago

GROUP BY queries break on postgres

Reported by: Owned by: Russ Brown Martin Scharrer normal ListOfWikiPagesMacro normal 0.11

Description

Error: Macro ListOfWikiPages(None) failed

column "wiki.author" must appear in the GROUP BY clause or be used in an aggregate function


This is because GROUP BY can't guarantee what values you'll get for these other columns unless you group by them too, and of course if you do that, you won't get the uniqueness that you're actually looking for.

I've attached a patch that fixes it by replacing the GROUP BY on a correlated subquery on which fetches the maximum version for the page.

Also, I removed a query which appeared to be completely redundant, since it was being run and then forgotten about as another query was run immediately afterwards.

Hope this helps!

Changed 7 years ago by Russ Brown

Patch to fix GROUP BY in queries for postgres

comment:1 Changed 7 years ago by Martin Scharrer

Resolution: → fixed new → closed

(In [6931]) Applied patch from [pickscrape@gmail.com]. This fixes #6073.

comment:2 Changed 7 years ago by Martin Scharrer

Thank you for providing this patch. I still have to get a postgres installation to test my plugins.

comment:3 Changed 7 years ago by Martin Scharrer

Are you also using the WatchlistPlugin? It is quite similar and I already became some complaints from a postgres user.

comment:4 Changed 7 years ago by Russ Brown

I didn't test my patch against any other databases, so hopefully they're still OK. :) It should be fine though on SQLite and MySQL >= 4.1, as it's pretty much standard SQL.

WatchlistPlugin is actually on my list of plugins to investigate, so I will probably be looking at it tomorrow. I will let you know if I have any trouble, and will try and provide a patch if I do.

Thanks for your hard work. :)

comment:5 Changed 7 years ago by Martin Scharrer

(In [6934]) Added order=reverse option also to ListChangesBy macro. See #6073.

comment:6 Changed 7 years ago by Martin Scharrer

(In [6935]) Removed MAX from version in SQL command of ListChangesBy macro. See #6073.

Modify Ticket

Action
as closed The owner will remain Martin Scharrer.
The resolution will be deleted. Next status will be 'reopened'.