Flyspray - The bug killer!

I almost expected this regression, but I'm not sure how to fix. Postgre is incredibly annoying regarding GROUP BY. I had a disucssion in the forum, and there someone told me that it's working well. Apparently it's not. If you know anything about databases, I'd appreciate if you experiement with a little with the queries.

Newer versions of postgres are more penantic about this, so yes, I've been bitten by this too in some code I maintain.

You're doing a full left join of the comments table, which would mean you'll need to GROUP BY all group comments fields other than the comment_id. This won't work, as we don't actually care about the other columns in this context.

Alternatively, you could use a subselect or stored procedure to work around this without having to add a GROUP BY clause for every other column. Interestingly enough, "attachments count" displays fine, so it's obviously doing somehting different.

I don't know how capable sub-selects will be, though...

I've attached a patch which replaces the LEFT JOIN with an manual "join" of sorts. It works for me, and should be work for less-than-capable databases too.

(erk, my description of the GROUP BY requirement is incorrect. I blame the allergy meds..)

Looks good, I'll add it to SVN. Please verify. Thanks ;)

Whoops, I goofed – 0.9.9.3 was broken, but my local copy had the original patch applied.

I've attached an updated patch which I'm currently using on my site. (postgres1l 8.1.6)

Now that did not work at all.

And btw, the fix was removed for a reason. Until now, no one has been able to provide a fix that is fully functional for both postgre and mysql. In such a case mysql wins.

I just realized the patch has the same bug as the original. Working on fixing.

After further digging, you're right, there appears to be no simple fix. So while it was "removed for a reason", the changelog (and ticket) still says that this bug was fixed, which is very much not the case. This bug should be re-opened.

As long as this bug remains, the documentation should reflect that you need to disable the 'comments' field on the task list for postgresql to work properly, and that it is a second-class citizen to Mysql. Alternatively, source code that works on postgresql, but not mysql, could be provided as a patch or separate file.

Let's face it, Flyspray is depending on implicit, buggy behaivor of Mysql – behaivor that violates the SQL spec, I might add.

The GROUP BY comments.date_added should have never succeeded given that each one (for a given ticket ID) is different. But even if it wasn't, user_id and especially comment_text aren't GROUPed, so their presence (thanks to the LEFT JOIN) will ensure that each row is unique and can't be GROUPed away.

I don't know if MySQL5 support subselects or UNIONs, but if it does, there are a few ways to tackle this.

The max(comments.date_added) field and the COUNT(DISTINCT ccoments.comment_id) could be
if subselects aren't an option, the query can be effectively doubled up via a UNION – the first half does an inner join (much like my patch) to give us the tasks with comments, while the second half gets us the tasks without comments. It'll be a PITA to integrate that into flyspray's DB query mechanism.

Postgre just doesn't matter to me. It never did (wasn't my idea in the first place). So if there is not enough support from the community to fix it properly, postgre will just end up "unsupported" officially one day. I still have to check FS#1364, maybe this will help for 1.0, but just do not expect postgre to be of much importance right now.

I don't expect you to just fix this problem, but it would be nice if it was documented as a known problem until "Someone in the community who cares" (eg me) comes up with a fix. FWIW #1364 doesn't seem to affect this problem at all.

A year or so ago I inherited project that is heavily tied into postgres, It has all manner of crazy sql, triggers, stored procedures, even custom operators. I've taken steps to make things more portable, but it "just doesn't matter" enough unless there's a greater purpose, like an obviously better way to do something.

We work on what's important to us, eh?

It's just that I can't work on it. I use mysql during development and have no time at all to do testing and fixing for postgre, in particular if some problems are non-trivial.

I added this bug to the known issues page, but I'm not going to reopen a bug unless I know that someone will actually fix it.

For what it is worth I too would like to have this group by behavior fixed. If I manage to fix it someday (as I'm swamped right now) I will submit something back.