Piwigo Bugtracker

Viewing Issue Advanced Details Jump to Notes ] Issue History ] Print ]
ID Category Severity Reproducibility Date Submitted Last Update
0001818 [Piwigo] database major have not tried 2010.08.24 16:09 2011.04.17 00:08
Reporter Marcin Miroslaw View Status public  
Assigned To plg
Priority normal Resolution fixed Platform Linux
Status closed   OS Gentoo
Projection none   OS Version 2.6.34
ETA none Fixed in Version 2.2.1 Product Version
  Target Version 2.2.1 Product Build
Summary 0001818: Blocking errors with sql queries on PostgreSQL
Description 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... ^"
Steps To Reproduce
Additional Information
Tags No tags attached.
browser any
Database engine and version Postgresql-8.4.4
PHP version Php-5.3.3
Web server Apache 2.2.x
Attached Files ? file icon functions_pgsql.inc.php.patch [^] (2,595 bytes) 2011.04.16 18:35 [Show Content]

- Relationships

-  Notes
(0004131)
Marcin Miroslaw (reporter)
2010.08.24 16:09

piwigo revision r6779, branch 2.1
(0004241)
adrianw (reporter)
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 (reporter)
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 (reporter)
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 (reporter)
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 (manager)
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)

- Issue History
Date Modified Username Field Change
2010.08.24 16:09 Marcin Miroslaw New Issue
2010.08.24 16:09 Marcin Miroslaw browser => any
2010.08.24 16:09 Marcin Miroslaw Database engine and version => Postgresql-8.4.4
2010.08.24 16:09 Marcin Miroslaw PHP version => Php-5.3.3
2010.08.24 16:09 Marcin Miroslaw Web server => Apache 2.2.x
2010.08.24 16:09 Marcin Miroslaw Note Added: 0004131
2010.09.26 09:24 adrianw Note Added: 0004241
2011.04.16 18:30 leloupv Note Added: 0005007
2011.04.16 18:34 leloupv Note Edited: 0005007
2011.04.16 18:35 leloupv File Added: functions_pgsql.inc.php.patch
2011.04.16 18:36 leloupv Note Edited: 0005007
2011.04.16 23:22 svn Checkin
2011.04.16 23:22 svn Note Added: 0005013
2011.04.16 23:22 svn Checkin
2011.04.16 23:22 svn Note Added: 0005014
2011.04.16 23:23 plg Note Added: 0005015
2011.04.16 23:23 plg Assigned To => plg
2011.04.16 23:23 plg Status new => closed
2011.04.16 23:23 plg Resolution open => reopened
2011.04.16 23:23 plg Fixed in Version => 2.2.1
2011.04.16 23:23 plg Target Version => 2.2.1
2011.04.16 23:24 plg Resolution reopened => fixed
2011.04.17 00:08 plg Summary Invalid sql queries on a few pages => Blocking errors with sql queries on PostgreSQL


Mantis 1.1.6[^]
Copyright © 2000 - 2008 Mantis Group
Contact
Powered by Mantis Bugtracker