How to resolve "ORDER BY clause is not in SELECT list" caused MySQL 5.7 with SELECT DISTINCT and ORDER BY

Question

I installed the new Ubuntu and my code has got a problem with MySQL.

( ! ) Warning: PDOStatement::execute(): SQLSTATE[HY000]: General error: 3065  Expression #2 of ORDER BY clause is not in SELECT list, references column 'clicshopping_test_ui.p.products_date_added' which is not in SELECT list; this is incompatible with DISTINCT  in /home/www//boutique/includes/OM/DbStatement.php on line 97s 

It seems MySQL 5.7 does'nt allow a request like:

select .... distinct with  order by rand(), p.products_date_added DESC 

If I use this it works:

select distinct .... with  order by rand(),  

How to resolve this situation ?

My SQL request in PHP

 $Qproduct = $OSCOM_PDO->prepare('select distinct p.products_id,             p.products_price             from :table_products p left join :table_specials s on p.products_id = s.products_id             where products_status = :products_status             and products_view = :products_view             and p.products_archive = :products_archive             order by rand(),             p.products_date_added DESC             limit :products_limit');                   $Qproduct->bindInt(':products_status', 1);                   $Qproduct->bindInt(':products_view', 1);                   $Qproduct->bindInt(':products_archive', 0);                   $Qproduct->bindInt(':products_limit',                    (int)MODULE_FRONT_PAGE_NEW_PRODUCTS_MAX_DISPLAY); 

Answer 1

If you have control of the server and you are running legacy code you can't easily change, you can adjust the SQL mode of the server and remove "only_full_group_by" either for the duration of boot, by running the query

SET GLOBAL sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''));

or by adding sql_mode='' to your my.cnf file.

Obviously its better to change your code if you have the possibility, but if not, this will disable that warning.

Answer 2

In order to fix the issue open the following file:

/etc/mysql/mysql.conf.d/mysqld.cnf 

and add the following line under [mysqld] block

sql-mode=""