Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

[MySQL 5.7] ORDER BY clause is not in SELECT list #376

Closed
rp1428 opened this issue Nov 30, 2015 · 27 comments
Closed

[MySQL 5.7] ORDER BY clause is not in SELECT list #376

rp1428 opened this issue Nov 30, 2015 · 27 comments
Assignees
Milestone

Comments

@rp1428
Copy link

@rp1428 rp1428 commented Nov 30, 2015

I get an error 3065 regardless of which ordering option I select in configuration. I narrowed it down to:

mysql> SELECT DISTINCT(image_id)
-> FROM piwigo_image_category
-> INNER JOIN piwigo_images ON id = image_id
-> WHERE
-> category_id = 6178
->
-> ORDER BY piwigo_images.file ASC;
ERROR 3065 (HY000): Expression #1 of ORDER BY clause is not in SELECT list, references column 'piwigo.piwigo_images.file' which is not in SELECT list; this is incompatible with DISTINCT

The above is from mysql - the browser reports a similar error. To get past this, I changed section_init.inc.php and added piwigo_images.file to the select line to force it for my site.

SELECT DISTINCT(image_id),piwigo_images.file
FROM '.IMAGE_CATEGORY_TABLE.'
INNER JOIN '.IMAGES_TABLE.' ON id = image_id
WHERE
'.$where_sql.'
'.$forbidden.'
'.$conf['order_by'].'
;';

Server version: 5.7.9 MySQL Community Server (GPL)
Piwigo 2.7.4

@plegall
Copy link
Member

@plegall plegall commented Nov 30, 2015

Hi @rp1428

so MySQL 5.7 has added such a constraint... (I think PostrgreSQL already had it). We will have to manage it seriously!

As you can see on the SQL query you have found, the order_by is a variable, so we will have to select rows as a variable...

@mistic100
Copy link
Member

@mistic100 mistic100 commented Nov 30, 2015

much simpler solution : do not query with DISTINCT and remove duplicates in PHP

@plegall
Copy link
Member

@plegall plegall commented Nov 30, 2015

@mistic100 of course, much simpler. And I doubt it would make things much slower (fetching some ids should be fast)

@modus75
Copy link
Contributor

@modus75 modus75 commented Nov 30, 2015

I think for large galleries it will definitely be slower (I'm thinking of
flat view for example).

And I'm definitely sure there are many other places...

Do you know if this is going to be the future mysql standard or just some
kind of strict sql option?
On Nov 30, 2015 4:24 PM, "Pierrick Le Gall" notifications@github.com
wrote:

@mistic100 https://github.com/mistic100 of course, much simpler. And I
doubt it would make things much slower (fetching some ids should be fast)


Reply to this email directly or view it on GitHub
#376 (comment).

@rp1428
Copy link
Author

@rp1428 rp1428 commented Nov 30, 2015

Researching, I came across:

As of MySQL 5.7.5, the default SQL mode includes ONLY_FULL_GROUP_BY. (Before 5.7.5, MySQL does not detect functional dependency and ONLY_FULL_GROUP_BY is not enabled by default. For a description of pre-5.7.5 behavior, see the MySQL 5.6 Reference Manual.)

And my database has:
mysql> SELECT @@global.sql_mode;
+-------------------------------------------------------------------------------------------------------------------------------------------+
| @@global.sql_mode |
+-------------------------------------------------------------------------------------------------------------------------------------------+
| ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
+-------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

So perhaps this is the culprit, the default is ONLY_FULL_GROUP_BY. I can try changing this tonight to see if it removes the issue.

@plegall
Copy link
Member

@plegall plegall commented Nov 30, 2015

It makes me think of http://piwigo.org/bugs/view.php?id=3205 which shows that MySQL 5.7 has new default settings, with more restrictions. But I doubt hosting providers will keep these default settings, or else many web applications will be broken.

@rp1428
Copy link
Author

@rp1428 rp1428 commented Dec 1, 2015

I set sql-mode="" in /etc/my.cnf and that resolved the issue for me.


This email has been checked for viruses by Avast antivirus software.
https://www.avast.com/antivirus

@jradwan
Copy link

@jradwan jradwan commented Apr 19, 2016

I just ran into this after upgrading my server today to Ubuntu 16.04, which came with MySQL 5.7.11-0ubuntu6. When I try to browse my albums I get:

Warning:  [mysql error 3065] Expression #1 of ORDER BY clause is not in SELECT list, references column 'piwigo.images.date_creation' which is not in SELECT list; this is incompatible with DISTINCT

SELECT DISTINCT(image_id)
  FROM image_category
    INNER JOIN images ON id = image_id
  WHERE
    category_id = 1
AND (category_id NOT IN (228,269,327,331,351,387,394,396,397,398,399,400,401,402,403,404,405,406,407,408,409,410,411,412,413,414,415,416,417,418,419,420,421,422,423,424,425,426,427,428,429,430,431,432,433,434,435,436,437,438,439,440,441,442,443,444,445,446,447,448,449,450,451,452,453,454,455,456,457,458,459,460,461,462,463,464,465,466,467,468,469,470,471,472,473,474,475,476,477,478,479,480,481,482,483,484,485,486,487,488,489,490,491,492,493,494,495,496,497,498,499,500,501,502,503,504,505,506,507,508,509,510,511,512,513,514,515,516,517,518,519,520,521,522,523,524,525,526,527,528,529,530,531,532,533,534,535,536,537,538,539,540,541,542,543,544,545,546,547,548,549,550,551,552,553,554,555,556,557,558,559,560,561,562, in /usr/local/piwigo/include/dblayer/functions_mysqli.inc.php on line 832


Fatal error: Call to a member function fetch_assoc() on boolean in /usr/local/piwigo/include/dblayer/functions_mysqli.inc.php on line 893
@jradwan
Copy link

@jradwan jradwan commented Apr 19, 2016

Adding

[mysqld]
sql-mode=""

to my.cnf resolved my issue. The problem seems to be related to the default ONLY_FULL_GROUP_BY setting in mysql 5.7.

@mistic100
Copy link
Member

@mistic100 mistic100 commented Apr 20, 2016

That's exactly what was said above :-)

@jradwan
Copy link

@jradwan jradwan commented Apr 20, 2016

Yep, I know. I was just confirming that workaround since this issue is still marked as open.

@madmaze
Copy link

@madmaze madmaze commented May 25, 2016

This still seems to be an issue (fresh install on ubuntu 16.04), I can confirm @jradwan 's fix works.

@loppis
Copy link

@loppis loppis commented Jun 2, 2016

^ Same here, on Ubuntu 16.04. The "sql-mode=""" fix helped me as well though.

@NonlinearFruit
Copy link

@NonlinearFruit NonlinearFruit commented Jul 11, 2016

Add this:

[mysqld]
sql-mode=""

To:
/etc/mysql/my.cnf

Worked for me as well, on Ubuntu 16.04

@fistons
Copy link

@fistons fistons commented Jul 11, 2016

Works for me on Debian 8, but I guess this is just a temporary work around?

@plegall
Copy link
Member

@plegall plegall commented Jul 13, 2016

@plegall plegall changed the title Mysql error 3065 when ordering files [MySQL 5.7] ORDER BY clause is not in SELECT list Jul 26, 2016
@plegall plegall added the Type: Bug label Jul 26, 2016
@plegall plegall added this to the 2.8.3 milestone Jul 26, 2016
@plegall plegall self-assigned this Jul 26, 2016
@plegall plegall closed this in b87094a Aug 29, 2016
@w0www
Copy link

@w0www w0www commented Oct 21, 2016

Add this:

[mysqld]
sql-mode=""

To:
/etc/mysql/my.cnf

Worked for me as well, on Ubuntu 16.04

brendo referenced this issue in symphonycms/symphonycms May 3, 2017
This is needed when sorting on a column that is not part of the projection.
In MySQL 5.7 strict mode, it is now required to add all columns in the ORDER BY clause in the SELECT's projection.

I could not find another way of fixing it, then adding a new parameter
which can inject the required missing piece in the select clause.

It's not a backward incompatible change on PHP 5.6, but can break
extensions on PHP 7.0+ since method signature must remain intact.
At least, the core is not broken anymore on MySQL 5.7 strict.

cc @brendo
cc @michael-e
@kamlesh131986
Copy link

@kamlesh131986 kamlesh131986 commented May 11, 2017

Only Wamp Server
Add code at bottom in my.ini file:

[mysqld]
sql-mode=""

Folder Path:
C:\wamp\bin\mysql\mysql5.7.14\my.ini

It is working in window 7 and 8.

@GeekStocks
Copy link

@GeekStocks GeekStocks commented May 30, 2017

For me, its simpler to fix the "offending" SQL statement. This is an easy change from:

SELECT DISTINCT YEAR(my_date) as 'years' FROM my_tableorder bymy_date;

to:

SELECT DISTINCT YEAR(my_date) as 'years' FROM my_table order by YEAR(my_date);

Just match the select with the order by and you're good to go.

@plegall
Copy link
Member

@plegall plegall commented May 31, 2017

The problem was fixed with b87094a

@donaldong
Copy link

@donaldong donaldong commented Jun 17, 2017

Another solution:
mysql > SET @@global.sql_mode=(SELECT REPLACE(@@global.sql_mode, 'ONLY_FULL_GROUP_BY', ''));

@awangmatx
Copy link

@awangmatx awangmatx commented Oct 9, 2017

*Another solution:
mysql > SET @@global.sql_mode=(SELECT REPLACE(@@global.sql_mode, 'ONLY_FULL_GROUP_BY', ''));

This work for me.

*The problem was fixed with b87094a = Result Error 500

  • Cannot start mysql
    [mysqld]
    sql-mode=""
@cilf
Copy link

@cilf cilf commented Oct 15, 2017

DON'T USE:

[mysqld]
sql-mode=""

From documentation:
The default SQL mode in MySQL 5.7 includes these modes: ONLY_FULL_GROUP_BY, STRICT_TRANS_TABLES, NO_ZERO_IN_DATE, NO_ZERO_DATE, ERROR_FOR_DIVISION_BY_ZERO, NO_AUTO_CREATE_USER, and NO_ENGINE_SUBSTITUTION.

By setting sql-mode='', you'd loose MySQL strictness - allowing you to put 60 chars long string in a varchar(50), truncating it and being completely silent about it.

I don't care about the other settings so I ended up with:

sql-mode="STRICT_ALL_TABLES"
@mbaric
Copy link

@mbaric mbaric commented Sep 13, 2018

Hey gang,

MySQL version: Server version: 5.7.23-0ubuntu0.16.04.1 (Ubuntu)

Listen I hope it's not too late, but as many of you I wouldn't like to temper with the workaround, I confirm it's working but I would rather fix my query to work. So from the simple query which was earlier:

SELECT rmc_description 
FROM   rad_mammo_calendars 
WHERE  rmc_year = 2018 
ORDER  BY rmc_id 

I got the error: Error Code: 3065 Expression #1 of ORDER BY clause is not in SELECT list, references column 'rirads_wrdp1.rad_mammo_calendars.rmc_id' which is not in SELECT list; this is incompatible with DISTINCT

So I rewrote it with the subquery.

My solution would be:

SELECT a.rmc_description
FROM
(
    SELECT rmc_description, rmc_id
    FROM rad_mammo_calendars
    WHERE rmc_year = 2018
) a
ORDER BY a.rmc_id

I hope it will help somebody.

@batristio
Copy link

@batristio batristio commented Mar 5, 2019

I edited this file /etc/mysql/my.cnf appending the following lines:

[mysqld]
sql_mode = 'STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION'

Compared with the default sql_mode, I just removed 'ONLY_FULL_GROUP_BY' mode and it fixed the problem for me.

@viraljetani
Copy link

@viraljetani viraljetani commented Mar 28, 2019

This worked for me:
[mysqld]
sql-mode="" .

For people using MAMP (free)(MacOS): Steps -> Stop your servers -> you might need to create a the file my.conf in your /MAMP/conf/ directory if its not already there and put this lines and then try starting servers..

Hope it helps.

@zulicheg
Copy link

@zulicheg zulicheg commented Oct 8, 2020

Adding

[mysqld]
sql-mode=""

to my.cnf resolved my issue. The problem seems to be related to the default ONLY_FULL_GROUP_BY setting in mysql 5.7.

working 4 me to

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Projects
None yet
Linked pull requests

Successfully merging a pull request may close this issue.

None yet