MantisBT - Piwigo
View Issue Details
0001818Piwigodatabasepublic2010.08.24 16:092011.04.17 00:08
Marcin Miroslaw 
plg 
normalmajorhave not tried
closedfixed 
LinuxGentoo2.6.34
 
2.2.12.2.1 
any
Postgresql-8.4.4
Php-5.3.3
Apache 2.2.x
0001818: Blocking errors with sql queries on PostgreSQL
I'm clicking on "Random pictures" and i'm getting:
"Warning: pg_query(): Query failed: ERROR: for SELECT DISTINCT, ORDER BY expressions must appear in select list LINE 7: ORDER BY date_available DESC, file ASC, id ASC ^ in /dane/domeny/xx.xxx/htdocs/piwigo/include/dblayer/functions_pgsql.inc.php on line 122 SELECT DISTINCT(id) FROM piwigo_images INNER JOIN piwigo_image_category AS ic ON id = ic.image_id WHERE image_id IN (-1) ORDER BY date_available DESC, file ASC, id ASC ;
ERROR: for SELECT DISTINCT, ORDER BY expressions must appear in select list LINE 7: ORDER BY date_available DESC, file ASC, id ASC ^"

On "recent pictures":
"Warning: pg_query(): Query failed: ERROR: for SELECT DISTINCT, ORDER BY expressions must appear in select list LINE 8: ORDER BY date_available DESC,date_available DESC, file AS... ^ in /dane/domeny/xxx.xxx/htdocs/piwigo/include/dblayer/functions_pgsql.inc.php on line 122 SELECT DISTINCT(id) FROM piwigo_images INNER JOIN piwigo_image_category AS ic ON id = ic.image_id WHERE date_available >= (CURRENT_DATE - '7 DAY'::interval)::date ORDER BY date_available DESC,date_available DESC, file ASC, id ASC ;
ERROR: for SELECT DISTINCT, ORDER BY expressions must appear in select list LINE 8: ORDER BY date_available DESC,date_available DESC, file AS... ^
"

On the left side i choose "Calendar", on the right side i choose "Monthly list":
"Warning: pg_query(): Query failed: ERROR: for SELECT DISTINCT, ORDER BY expressions must appear in select list LINE 5: ORDER BY date_creation DESC, date_available DESC, file AS... ^ in /dane/domeny/xxx.xxx/htdocs/piwigo/include/dblayer/functions_pgsql.inc.php on line 122 SELECT DISTINCT id ,date_creation FROM piwigo_images INNER JOIN piwigo_image_category ON id = image_id WHERE 1 = 1 AND date_creation IS NOT NULL ORDER BY date_creation DESC, date_available DESC, file ASC, id ASC
ERROR: for SELECT DISTINCT, ORDER BY expressions must appear in select list LINE 5: ORDER BY date_creation DESC, date_available DESC, file AS... ^
"
Calendar->"weekly list"-> gives:
"Warning: pg_query(): Query failed: ERROR: for SELECT DISTINCT, ORDER BY expressions must appear in select list LINE 5: ORDER BY date_creation DESC, date_available DESC, file AS... ^ in /dane/domeny/xx.xxx/htdocs/piwigo/include/dblayer/functions_pgsql.inc.php on line 122 SELECT DISTINCT id ,date_creation FROM piwigo_images INNER JOIN piwigo_image_category ON id = image_id WHERE 1 = 1 AND date_creation IS NOT NULL ORDER BY date_creation DESC, date_available DESC, file ASC, id ASC
ERROR: for SELECT DISTINCT, ORDER BY expressions must appear in select list LINE 5: ORDER BY date_creation DESC, date_available DESC, file AS... ^"
No tags attached.
patch functions_pgsql.inc.php.patch (2,595) 2011.04.16 18:35
http://piwigo.org/bugs/file_download.php?file_id=127&type=bug
Issue History
2010.08.24 16:09Marcin MiroslawNew Issue
2010.08.24 16:09Marcin Miroslawbrowser => any
2010.08.24 16:09Marcin MiroslawDatabase engine and version => Postgresql-8.4.4
2010.08.24 16:09Marcin MiroslawPHP version => Php-5.3.3
2010.08.24 16:09Marcin MiroslawWeb server => Apache 2.2.x
2010.08.24 16:09Marcin MiroslawNote Added: 0004131
2010.09.26 09:24adrianwNote Added: 0004241
2011.04.16 18:30leloupvNote Added: 0005007
2011.04.16 18:34leloupvNote Edited: 0005007
2011.04.16 18:35leloupvFile Added: functions_pgsql.inc.php.patch
2011.04.16 18:36leloupvNote Edited: 0005007
2011.04.16 23:22svnCheckin
2011.04.16 23:22svnNote Added: 0005013
2011.04.16 23:22svnCheckin
2011.04.16 23:22svnNote Added: 0005014
2011.04.16 23:23plgNote Added: 0005015
2011.04.16 23:23plgAssigned To => plg
2011.04.16 23:23plgStatusnew => closed
2011.04.16 23:23plgResolutionopen => reopened
2011.04.16 23:23plgFixed in Version => 2.2.1
2011.04.16 23:23plgTarget Version => 2.2.1
2011.04.16 23:24plgResolutionreopened => fixed
2011.04.17 00:08plgSummaryInvalid sql queries on a few pages => Blocking errors with sql queries on PostgreSQL

Notes
(0004131)
Marcin Miroslaw   
2010.08.24 16:09   
piwigo revision r6779, branch 2.1
(0004241)
adrianw   
2010.09.26 09:24   
Have had the same issue with a recent install. Quick and dirty work-around is:

$conf['order_by'] = "";
$conf['order_by_inside_category'] = $conf['order_by'];

in local/config/config.inc.php

Really only fixes the problem by hiding it though.
(0005007)
leloupv   
2011.04.16 18:30   
(edited on: 2011.04.16 18:36)
Proposed patch on file include/dblayer/functions_pgsql.inc.php for version 2.2.0 of piwigo attached : functions_pgsql.inc.php.patch.
This patch fix all reported issues.

(0005013)
svn   
2011.04.16 23:22   
[Subversion] r10430 by plg on branch 2.2

-----[Subversion commit log]----------------------------------------------------
bug 1818 fixed: fix "select distinct" queries for PostgreSQL. Patch by leloupv
(0005014)
svn   
2011.04.16 23:22   
[Subversion] r10431 by plg on trunk

-----[Subversion commit log]----------------------------------------------------
merge r10430 from branch 2.2 to trunk

bug 1818 fixed: fix "select distinct" queries for PostgreSQL. Patch by leloupv

(0005015)
plg   
2011.04.16 23:23   
Thank you leloupv, I've patched my local copy, made a few tests and I confirm the bugs are removed (there are still some other problems with PostgreSQL but less blocking)