Modify

Opened 5 years ago

Closed 5 years ago

Last modified 5 years ago

#6073 closed defect (fixed)

GROUP BY queries break on postgres

Reported by: pickscrape@… Owned by: martin_s
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 pickscrape@… 5 years ago.
Patch to fix GROUP BY in queries for postgres

Download all attachments as: .zip

Change History (7)

Changed 5 years ago by pickscrape@…

Patch to fix GROUP BY in queries for postgres

comment:1 Changed 5 years ago by martin_s

  • Resolution set to fixed
  • Status changed from new to closed

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

comment:2 Changed 5 years ago by martin_s

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

comment:3 Changed 5 years ago by martin_s

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

comment:4 Changed 5 years ago by pickscrape@…

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 5 years ago by martin_s

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

comment:6 Changed 5 years ago by martin_s

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

Add Comment

Modify Ticket

Action
as closed The owner will remain martin_s.
The resolution will be deleted. Next status will be 'reopened'.
Author


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

 
Note: See TracTickets for help on using tickets.