[#41951] - ERROR: column "mfa.mfaRecords" must appear in the GROUP BY clause or be used in an aggregate function
- Closed
- 28 Sep 2023
- Medium
- Build: Joomla! 4.3.4 Stable [ Bora ]
- # 41951
Steps to reproduce the issue
- Log in administrator backend.
- Select Users -> Groups on the menu.
- Click on any entry on the "Enabled Users" or "Blocked Users" column.
Expected result
It shows a list of enabled or blocked users with a group.
Actual result
An error has occurred.
500 42803, 7, ERROR: column "mfa.mfaRecords" must appear in the GROUP BY clause or be used in an aggregate function LINE 1: SELECT a.,"mfa"."mfaRecords" ^ 42803, 7, ERROR: column "mfa.mfaRecords" must appear in the GROUP BY clause or be used in an aggregate function LINE 1: SELECT a.,"mfa"."mfaRecords" ^
System information (as much as possible)
php: Linux Web 5.15.116-1-pve #1 SMP PVE 5.15.116-1 (2023-08-29T13:46Z) x86_64
dbserver: postgresql
dbversion: 15.3
dbcollation: en_US.utf8
dbconnectioncollation: en_US.utf8
dbconnectionencryption:
dbconnencryptsupported: false
phpversion: 8.2.10
server: nginx/1.22.1
sapi_name: fpm-fcgi
version: Joomla! 4.3.4 Stable [ Bora ] 22-August-2023 16:00 GMT
useragent: Mozilla/5.0 (X11; Linux x86_64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/117.0.0.0 Safari/537.36
Additional comments
It might be due to the different behavior of PostgreSQL. My other Joomla installation with MySQL experiences no such errors.
PostgreSQL log shows:
2023-09-27 16:06:32.986 UTC [197864] ERROR: column "mfa.mfaRecords" must appear in the GROUP BY clause or be used in an aggregate function at character 12
2023-09-27 16:06:32.986 UTC [197864] STATEMENT: SELECT a.*,"mfa"."mfaRecords"
FROM "jl4_users" AS a
LEFT JOIN (
SELECT MIN("user_id") AS "uid",COUNT(*) AS "mfaRecords"
FROM "jl4_user_mfa"
GROUP BY "user_id") AS "mfa" ON "mfa"."uid" = "a"."id"
LEFT JOIN jl4_user_usergroup_map AS map2 ON map2.user_id = a.id
WHERE "a"."block" = $1 AND "map2"."group_id" = $2
GROUP BY "a"."id","a"."name","a"."username","a"."password","a"."block","a"."sendEmail","a"."registerDate","a"."lastvisitDate","a"."activation","a"."params","a"."email","a"."lastResetTime","a"."resetCount","a"."otpKey","a"."otep","a"."requireReset"
| Labels |
Removed:
?
|
||
joomla-cms-bot
- | Labels |
Added:
No Code Attached Yet
|
||
Alternatively, change the
GROUP BY "a"."id","a"."name","a"."username","a"."password","a"."block","a"."sendEmail","a"."registerDate","a"."lastvisitDate","a"."activation","a"."params","a"."email","a"."lastResetTime","a"."resetCount","a"."otpKey","a"."otep","a"."requireReset"
to
GROUP BY "a"."id","a"."name","a"."username","a"."password","a"."block","a"."sendEmail","a"."registerDate","a"."lastvisitDate","a"."activation","a"."params","a"."email","a"."lastResetTime","a"."resetCount","a"."otpKey","a"."otep","a"."requireReset","mfa"."mfaRecords"
i've experienced this sometimes ago... sure postgres is more SQL strict
so i'd like the "alternative"
p.s
I thought it was due to my messed up branch
i've experienced this sometimes ago... sure postgres is more SQL strict so i'd like the "alternative"
@alikon The alternative would be more complicated code than my first suggestion. MySQL 8 in strict SQL mode might be the same as PostgreSQL. In fact they are right, in a group by statement all returned columns either have to be in the group by clause, or if not they have to use an aggregate function. Same on Oracle, MS SQL Server.
I am just preparing the PR with the fix.
anyway i think the query is very bad
| Labels |
Added:
bug
|
||
| Status | New | ⇒ | Closed |
| Closed_Date | 0000-00-00 00:00:00 | ⇒ | 2023-09-27 17:02:02 |
| Closed_By | ⇒ | richard67 |
Yes, sorry.
| Status | Closed | ⇒ | New |
| Closed_Date | 2023-09-27 17:02:02 | ⇒ | |
| Closed_By | richard67 | ⇒ |
Reopening the issue. I have a PR which fixes this issue, but then it uncovers other issues of the same type, so I have converted my PR to draft. I think it needs more work on the getListQuery method but I won't have time for that before weekend. Maybe @alikon can have a look?
Used psql and tried both methods, I got the same result with block_id = 0 and group_id = 3:
-[ RECORD 1 ]-+--------------------------------------------------------------------------------------------------------------------------------------------------------
id | 2
name | Test User
username | tester
email | test@example.com
password | $2y$10$UXZLEBgPw.9PJDu4UP5o5uIy7l3Vq5tu98SqecGFOuNqr0w4CHyui
block | 0
sendEmail | 0
registerDate | 2023-09-27 20:58:33
lastvisitDate |
activation |
params | {"admin_style":"","admin_language":"","language":"","editor":"","timezone":"","a11y_mono":"0","a11y_contrast":"0","a11y_highlight":"0","a11y_font":"0"}
lastResetTime |
resetCount | 0
otpKey |
otep |
requireReset | 0
authProvider |
mfaRecords |
-[ RECORD 2 ]-+--------------------------------------------------------------------------------------------------------------------------------------------------------
id | 3
name | Test User 2
username | tester2
email | tester2@example.com
password | $2y$10$NiN/yITuLePYpTSbZaRB0uuJdqJ4JCLW/09P5WJDezKTbClFaPN5u
block | 0
sendEmail | 0
registerDate | 2023-09-27 21:20:14
lastvisitDate |
activation |
params | {"admin_style":"","admin_language":"","language":"","editor":"","timezone":"","a11y_mono":"0","a11y_contrast":"0","a11y_highlight":"0","a11y_font":"0"}
lastResetTime |
resetCount | 0
otpKey |
otep |
requireReset | 0
authProvider |
mfaRecords |
```<hr /><sub>This comment was created with the <a href="https://github.com/joomla/jissues">J!Tracker Application</a> at <a href="https://issues.joomla.org/tracker/joomla-cms/41951">issues.joomla.org/tracker/joomla-cms/41951</a>.</sub>
| Status | New | ⇒ | Closed |
| Closed_Date | 0000-00-00 00:00:00 | ⇒ | 2023-09-28 15:21:14 |
| Closed_By | ⇒ | richard67 |
Right, PostgreSQL is indeed more strict regarding correct GROUP BY statements. Does it help if you change the
SELECT a.*,"mfa"."mfaRecords"TOSELECT a.*,MAX("mfa"."mfaRecords")when testing in a tool like e.g. phpPgAdmin or Dbeaver or another SQL client?