Database error during export of profile data

[dragomano]

Description

Database Error: 'smf_home.pm.msgtime' isn't in GROUP BY

Steps to reproduce

1. Go to Profile - Download profile data - Check all checkboxes - Run export
2. After some time we have an error in the Error log:
   

Environment (complete as necessary)

• Version/Git revision: 6cda6d8
• Database Type: MariaDB
• Database Version: 10.4.12
• PHP Version: 7.2.29

[Sesquipedalian]

Hm. I haven't been able to reproduce this locally, so I will need to ask you to run a test for me, @dragomano.

What happens if your go to line 2558 in News.php and make the following change? Does the error still occur when you run the export again?

Find:

GROUP BY pm.id_pm

Replace:

GROUP BY pm.id_pm, pm.msgtime

[sbulen]

Do we need the group by there at all?

[sbulen]

Yes we do need the GROUP BY... Due to the GROUP_CONCATS. Drat.

My suspicion follows...

I believe this is a restriction on MariaDB that is a known limitation, if I read this right:
https://jira.mariadb.org/browse/MDEV-11588

ONLY_FULL_GROUP_BY means that ALL non-aggregate columns must be in your GROUP BY clause.

(The reason is that you can get ambiguous results: https://stackoverflow.com/questions/25800411/mysql-isnt-in-group-by)

MySQL loosened that up a bit, because there are situations - like this one - where you know the results aren't ambiguous. All those columns are dependent on id_pm, & that GROUP BY id_pm sufficient. But MariaDB doesn't know this yet, apparently (per the problem ticket above).

So... There are two possible solutions:

• Add all the columns to the GROUP BY (yes, even the body...)
• Do a correlated subquery. The subquery returns only the GROUP_CONCAT ids & names. Join to that to get the comma-delimited ids and names.

Eh?

[Sesquipedalian]

Yeah, that looks like the cause, @sbulen.

O, the joys of dealing with implementation differences between forks. Reminds me of this:

Anyway, I suspect that adding the other columns to the GROUP BY will cause less of a performance problem than running a subquery (although I could be entirely wrong), so I guess I'll do that. Anyone who knows better can feel free to correct me, though.

[sbulen]

I suspect it's the other way around... It will need to group by the message bodies, which is kinda ridiculous.

I have a subquery one written & tested, let me put it up there for @dragomano to test...