Piwigo Bugtracker

View Issue Details Jump to Notes ] Issue History ] Print ]
IDProjectCategoryView StatusDate SubmittedLast Update
0001818Piwigodatabasepublic2010.08.24 16:092011.04.17 00:08
ReporterMarcin Miroslaw 
Assigned Toplg 
PrioritynormalSeveritymajorReproducibilityhave not tried
StatusclosedResolutionfixed 
PlatformLinuxOSGentooOS Version2.6.34
Product Version 
Target Version2.2.1Fixed in Version2.2.1 
Summary0001818: Blocking errors with sql queries on PostgreSQL
DescriptionI'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... ^"
TagsNo tags attached.
browserany
Database engine and versionPostgresql-8.4.4
PHP versionPhp-5.3.3
Web serverApache 2.2.x
Attached Filespatch 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


Copyright © 2000 - 2015 MantisBT Team
Contact
Powered by Mantis Bugtracker