mysql error 3065: ORDER BY -vs- SELECT list

[binfalse]

I've just downloaded Piwigo 2.8.2 to run it in a Docker environment. However, I get mysql errors that look like this:

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

SELECT DISTINCT(image_id)
	FROM piwigo_image_category
		INNER JOIN piwigo_images ON id = image_id
	WHERE
		category_id = 11

	ORDER BY date_available DESC, file ASC, id ASC
; in /scripts/pics.binfalse.de/include/dblayer/functions_mysqli.inc.php on line 832


Fatal error: Uncaught Error: Call to a member function fetch_assoc() on boolean in /scripts/pics.binfalse.de/include/dblayer/functions_mysqli.inc.php:893 Stack trace: #0 /scripts/pics.binfalse.de/include/section_init.inc.php(318): query2array('\nSELECT DISTINC...', NULL, 'image_id') #1 /scripts/pics.binfalse.de/index.php(27): include('/scripts/pics.b...') #2 {main} thrown in /scripts/pics.binfalse.de/include/dblayer/functions_mysqli.inc.php on line 893

The docker environment consists of

• an nginx:latest container that mounts the Piwigo sources to /usr/share/nginx/html (ro)
• a mysql:latest container, that is linked to the nginx container
• a php:fpm container that also knows about the piwigo sources and has mysqli installed

We're talking about this PHP

• PHP Version => 7.0.11
• Configure Command => './configure' '--with-config-file-path=/usr/local/etc/php' '--with-config-file-scan-dir=/usr/local/etc/php/conf.d' '--disable-cgi' '--enable-ftp' '--enable-mbstring' '--enable-mysqlnd' '--with-curl' '--with-libedit' '--with-openssl' '--with-zlib' '--enable-fpm' '--with-fpm-user=www-data' '--with-fpm-group=www-data'
• PHP API => 20151012
• PHP Extension => 20151012
• Zend Extension => 320151012
• Zend Extension Build => API320151012,NTS
• PHP Extension Build => API20151012,NTS
• Client API library version => mysqlnd 5.0.12-dev - 20150407 - $Id: 241ae00989d1995ffcbbf63d579943635faf9972 $
• Version => mysqlnd 5.0.12-dev - 20150407 - $Id: 241ae00989d1995ffcbbf63d579943635faf9972 $

I've been able to fix the issues by adding the ORDER BY elements to the SELECT list in piwigo/include/section_init.inc.php:

309c309
< SELECT DISTINCT(image_id), date_available, file, id
---
> SELECT DISTINCT(image_id)
446c446
< SELECT DISTINCT(id), date_available
---
> SELECT DISTINCT(id)
485c485
< SELECT DISTINCT(id), hit
---
> SELECT DISTINCT(id)

However, I'm not sure if that's the proper way to go..!?
Do you have any comments? Maybe you want to include the changes to your code base?

[flop25]

#376 (comment)