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);

Sorry I don't understand, could show me an example because it's two different function select distinct .... and select .....from ... group by — kurama, Apr 25 '16 at 03:34
`group by` does not work here as suggested by user557846. If you use `group by` you can't order by the other field. — Adam, Oct 06 '21 at 18:07

score 92 · Answer 1 · edited Nov 19 '19 at 13:38

92

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.

edited Nov 19 '19 at 13:38

Jonathan Pasquier

2,561
1
19
17

answered Sep 06 '16 at 15:52

Justin Cherniak

1,666
13
10

7

For reference, if you go the route of adding `sql_mode=''` to your my.cnf file, be sure to add this line under a new line with the `[mysqld]` heading. For example: `[mysqld]` (new line) `sql_mode = ''`. Otherwise, you'll get an error when attempting to restart `mysql`, like: `Job for mysql.service failed because the control process exited with error code.` – Tom Catullo Mar 29 '18 at 03:44
Thank you! I had 2 computers and in one of them the code worked and in the other one not, and thanks to you I solved it – Esteban Castro Sola Sep 19 '18 at 09:45
The `SET GLOBAL` query did not help. – Valerian Pereira Mar 11 '19 at 10:37
4

@ValerianPereira The following worked for me: SET @@sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY','')); – pvgoran Mar 30 '19 at 08:51
4

As us developers are often working with legacy systems, I wanted to know if there were performance issues associated with turning 'ONLY_FULL_GROUP_BY' off. I found that the offending queries did not run any faster when I fixed the syntax to be compatible compared to running them with 'ONLY_FULL_GROUP_BY' off. I could not see any obvious performance benefit then to fixing the queries or issues with disabling the mode. – Gordon Rouse Jun 11 '20 at 01:13
>>> Obviously its better to change your code if you have the possibility >>> Is there any performance penalty if I remove "only_full_group_by" – Imtiaz Shakil Siddique Feb 08 '21 at 15:47
2

`Obviously its better to change your code if you have the possibility,` why? And how would you suggest to change it? – Adam Oct 06 '21 at 18:07
@Adam According to https://stackoverflow.com/a/43668890/2336725, the fix is that anything in ORDER BY also needs to be in SELECT. – Teepeemm May 16 '22 at 20:34

score 17 · Answer 2 · answered Oct 08 '18 at 10:34

17

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=""

answered Oct 08 '18 at 10:34

Wolfack

2,667
1
26
50

1

`sql-mode` or `sql_mode`? or doesn't matter? – shamaseen May 06 '21 at 19:36
1

it should be sql-mode – Wolfack May 08 '21 at 07:47

Paul EDANGE · Answer 3 · 2022-05-17T07:40:34.047

You can run as said :

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

But if you restart your serveur, you may have to re run it.

You can update the mysql server configuration:

on Windows, the ${MY_SQL_HOME}/my.cnf (or my.ini)
on Linux, one of the following: etc/my.cnf, /etc/mysql/my.cnf, /usr/etc/my.cnf or ~/.my.cnf

[mysqld]

# RESOLVE order-by-clause-is-not-in-select-list by removing ONLY_FULL_GROUP_BY from the sql-mode list
sql-mode="STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION"

score 7 · Answer 4 · answered May 06 '19 at 23:55

7

If you have phpMyAdmin:

1-go to the Variables tabs

2-search label "sql mode"

3-edit the content and delete the mode : "ONLY_FULL_GROUP_BY"

4-save

NB: don't forget to verify the comma separator

answered May 06 '19 at 23:55

rapaelec

1,238
12
10

2

This works but if super privilege is not turned on it would return error. – Shasha Jul 19 '20 at 17:37

score 3 · Answer 5 · answered Apr 23 '19 at 11:19

3

With MAMP PRO

You cannot edit your my.cnf file directly. You must use the MAMP PRO interface to edit your my.cnf file. In the menu go to File > Edit Template > MySQL > my.cnf. Then add sql_mode='' under the [mysqld] key

answered Apr 23 '19 at 11:19

Kaizoku Gambare

3,143
3
29
41

With MAMP, create the file my.cnf in your /Applications/MAMP/conf folder, add these lines and restart the server: `[mysqld]` `sql_mode=''` – Nico Schefer Oct 22 '19 at 18:52

score 2 · Answer 6 · answered Jan 06 '20 at 19:18

2

This worked for me that @pvgoran suggested

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

You can run this when you want.

answered Jan 06 '20 at 19:18

Sarvagna Shukla

21
4

score 2 · Answer 7 · answered Jan 09 '20 at 03:18

To get ->distinct('some_column') to work for me, I needed to disable ONLY_FULL_GROUP_BY option in mysql modes.

Rather than edit the mysql config file on the filesystem, I followed the instructions at Laravel : Syntax error or access violation: 1055 Error and added this to config/database.php:

        'strict' => true,
        'modes' => [
            // 'ONLY_FULL_GROUP_BY', // disabled to allow grouping by one column
            'STRICT_TRANS_TABLES',
            'NO_ZERO_IN_DATE',
            'NO_ZERO_DATE',
            'ERROR_FOR_DIVISION_BY_ZERO',
            'NO_AUTO_CREATE_USER',
            'NO_ENGINE_SUBSTITUTION'
        ],

In my project, the modes property was non-existent, so I simply pasted that bad boy in there.

NOTE: Make sure you understand that wiping the modes entirely is undesirable, and basically not strict mode, so you will lose debugging warning/error messages about stuff like varchar length exceeded. The trick is to avoid sql_mode="". Notice how above I am using 6 modes and explicitly omitting ONLY_FULL_GROUP_BY.

score 0 · Answer 8 · edited Oct 08 '18 at 11:18

Try this:

    SELECT 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
  GROUP BY p.products_id,p.products_price 
     LIMIT :products_limit

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

8 Answers8

Linked