Modify

Opened 7 years ago

Closed 7 years ago

Last modified 7 years ago

#6073 closed defect (fixed)

GROUP BY queries break on postgres

Reported by: Russ Brown Owned by: Martin Scharrer
Priority: normal Component: ListOfWikiPagesMacro
Severity: normal Keywords:
Cc: Trac Release: 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!

Attachments (1)

listofwikipagesmacro.patch (1.6 KB) - added by Russ Brown 7 years ago.
Patch to fix GROUP BY in queries for postgres

Download all attachments as: .zip

Change History (7)

Changed 7 years ago by Russ Brown

Attachment: listofwikipagesmacro.patch added

Patch to fix GROUP BY in queries for postgres

comment:1 Changed 7 years ago by Martin Scharrer

Resolution: fixed
Status: newclosed

(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'.

Add Comment


E-mail address and name can be saved in the Preferences.

 
Note: See TracTickets for help on using tickets.