0

I am trying to migrate MariaDB to MySQL database and the below SQL query is failing for me. I have tried multiple approaches but none of it works for me. The query works perfectly fine in Mariadb but fails in MySQL. Any help is appreciated

SELECT DISTINCT amc_user.* FROM amc_user 
INNER JOIN users_standard ON users_standard.id = amc_user.user_id 
AND (users_standard.client_id <=> 1 OR (users_standard.client_id IN (1))) 
WHERE (committee_id = 11 and users_standard.active = 1 and users_standard.system = 0 and users_standard.global_spawn != 1) 
AND (hidden != 1) AND (amc_user.admin = 1 
    OR amc_user.can_manage LIKE '%collertr%' 
    OR amc_user.can_access LIKE '%collertr%') 
GROUP BY amc_user.user_id 
ORDER BY chair DESC, users_standard.lastname ASC;

ERROR 1055 (42000): Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'localhost.amc_users.committee_user_id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

I have also gone through this answer where the accepted answer seems incorrect to turn off ONLY_FULL_GROUP_BY. And one of the answers suggested using aggregated functions seems correct but not sure how to apply it in the above query

Mariadb version - 10.6.3-MariaDB-1:10.6.3+maria~focal
Mysql Verison - 8.0.33

Aniket Tiwari
  • 3,561
  • 4
  • 21
  • 61
  • What is the query supposed to be doing? BTW...mixing distinct and group by usually makes no sense. – Tim Biegeleisen Jun 06 '23 at 09:49
  • This is an existing query that returns me the amc_users records but now the same query is failing in mysql – Aniket Tiwari Jun 06 '23 at 09:51
  • Remove GROUP BY. You select from `amc_user` only - so DISTINCT is enough. – Akina Jun 06 '23 at 09:52
  • If I remove group by then still I get the error ERROR 3065 (HY000): Expression #2 of ORDER BY clause is not in SELECT list, references column 'localhost.amc_users.committee_user_id' which is not in SELECT list; this is incompatible with DISTINCT – Aniket Tiwari Jun 06 '23 at 09:55
  • @TimBiegeleisen I have also gone through this answer https://stackoverflow.com/questions/41887460/select-list-is-not-in-group-by-clause-and-contains-nonaggregated-column-inc where the accepted answer seems incorrect. And the answer which you shared to use aggregated functions seems correct but not sure how to apply in the above query – Aniket Tiwari Jun 06 '23 at 09:59
  • *If I remove group by then still I get the error* Of course - a lot of different `users_standard.lastname` matches the same row from `amc_user`, and server does not know what of them must be used. Remove this column from ORDER BY at all. – Akina Jun 06 '23 at 10:12
  • That's the genesis of my question; we need to know what you're trying to achieve. – Tim Biegeleisen Jun 06 '23 at 10:14
  • But I don't want to remove both the columns ORDER BY chair DESC, users_standard.lastname ASC as it is required for ordering the records – Aniket Tiwari Jun 06 '23 at 10:15
  • Then add `users_standard.lastname` to the list of selected columns. – slaakso Jun 06 '23 at 15:21

0 Answers0