Conflicts please

@wilsonge conflicts solved

With "Link to Author's Contact Page" set to yes I get the following error in frontend:

1140 In aggregated query without GROUP BY, expression #2 of SELECT list contains nonaggregated column 'joomla_db40.contact.alias'; this is incompatible with sql_mode=only_full_group_by

I am using a bitnami package with mysql 8.0.17 and I don´t know whether this sql_mode configuration is exotic.

@richard67 or @alikon i suspect this means we have an issue here in postgresql too. Can either of you too take a look?

I am using a bitnami package with mysql 8.0.17 and I don´t know whether this sql_mode configuration is exotic.

No, is not more exotic, is more standard conmforming. We have to check, but today is too late for me now.

@wilsonge @HLeithner In drone I only see PHPCS errors related to this PR.

@wilsonge What I can see in code looks as if this group by error was there also before this PR.

@HLeithner In general when you have a select statement which contains aggregate functions like MAX() and some other columns not being aggregated in the select list, then the statement has to be grouped by there other columns.

E.g. SELECT MAX(thiscolumn), thatcolumn FROM sometable=> error in oracle or postgres or any other normal database, and since mysql 8 with new defaults also there.

SELECT MAX(thiscolumn), thatcolumn FROM sometable GROUP BY thatcolumn => correct.

As I said, to me it seems this was also wrong before this PR.

P.S.: And of course COUNT() is an aggregate function, too.

@wilsonge The error has definitely been there long before this PR, and to add group by is not the right solution. The right way is to get the MAX(contact.id) in a separate query, either a subquery of or a join to a 2nd query, which then gets the other columns for that particular contact.id. Subqueries normally shall be avoided and joins shall be used if possible for performance reasons, at least that's what I've learned in my Oracle expert trainings.

It is definitely wrong SQL like it is done now, and the only reason why it worked was that some server variable was set to allow that and automatically add a group by (which is wrong anyway) for compatibility with older MySQL versions.

I'll try to fix that today but it is not easy for me as being used to Oracle SQL, where syntax for joins is a bit different to what we have in MySQL or PG. So I might need reviewers and testers later.

I think we may have this error at several other places, too, not sure if I can find time to find them all.

Update: I've just checked, it seems we have this error in this way only here. There might be other places, especially tags, where usage of aggregate functions like MAX() or COUNT() has to be checked, but it might be different there.

@HLeithner @wilsonge Please check HLeithner#28.

All tests passed in Drone, including all mysql8 and postgresql related ?

I have tested this item ✅ successfully on c2760ad

Tested with MySQL 5.7 and PostgreSQL 10.10.

@waader Could you test this PR again and see if you still have this issue you've mentioned in your comment #25052 (comment)? It should be fixed now. The server variable setiing is not exotic, it is more standard. Before MySQL 8, the default value was for compatibility with older MySQL versions, but with MySQL 8 they want to have default settings closed to standards, that's why issues appear more with 8. I would be happy if you could find some time for a test. Thanks in advance.

The error message changed a little bit to:
1140 In aggregated query without GROUP BY, expression #2 of SELECT list contains nonaggregated column 'joomla4.contact_2.alias'; this is incompatible with sql_mode=only_full_group_by

Tested with mysql 8.0.17

@waader @HLeithner @wilsonge Hmm, it seems then I have to change it to use a subquery. Stay tuned, it may take a while because have other stuff to do right now.

@SharkyKZ Do you have an idea how to change the SQL statement handled with this PR to using a subquery in an elegant way?

We don't support ONLY_FULL_GROUP_BY. See #12494 for reference.

But I think we could fix it here at least by using a subquery for the "contact_1" part in that statement.

I think MAX() can be removed. Instead order by ID descending and set 1 limit.

I think MAX() can be removed. Instead order by ID descending and set 1 limit.

@SharkyKZ Good idea. Could you make a code proposal here as comment or a PR against Harald's branch? That would be fastest solution. I would have to look that up, limit and so on.

OK, I will. This will also correct the issue where data from different contacts is being selected. I think the idea here was to use the newest contact.

Submitted HLeithner#30.

@waader Sorry to bother you again. Could you test this PR again? With the last change it should work now. I will test, too, but because I don't have MySQL 8 I could not reproduce the issue you had.

I have tested this item ✅ successfully on acbbd4e

I have tested this item ✅ successfully on acbbd4e

Works now for mysql8 and postgresql. Thank you Richard et al.

I have tested this item ✅ successfully on acbbd4e

Works now for mysql8 and postgresql. Thank you Richard et al.

Thanks to @HLeithner and @SharkyKZ , they fixed it. I've only tried and failed.

RTC

Good teamwork guys and thanks for the test spotting this @waader !

thx for testing and fixing!