MantisBT - Piwigo
View Issue Details
0001740Piwigophotospublic2010.06.23 00:222010.06.30 00:04
madman 
nikrou 
normalminoralways
closedfixed 
PostgresqlCentOS 5.4Postgresql 8.4.2
2.1.1 
2.1.2 
Mozilla
Postgresql 8.4.2
5.3.2
Apache 2.2.3
0001740: [PostgreSQL] Rating page returns database error
This is another query that has an incomplete GROUP BY. This in in admin/rating.php line 162.

Proposed query rewrite:

$query = '
SELECT i.id,
       i.path,
       i.file,
       i.tn_ext,
       i.average_rate,
       MAX(r.date) AS recently_rated,
       COUNT(r.rate) AS nb_rates,
       SUM(r.rate) AS sum_rates
  FROM '.RATE_TABLE.' AS r
    LEFT JOIN '.IMAGES_TABLE.' AS i ON r.element_id = i.id
  WHERE 1 = 1 ' . $page['user_filter'] . '
  GROUP BY i.id, i.path, i.file, i.tn_ext, i.average_rate
  ORDER BY ' . $available_order_by[$order_by_index][1] .'
  LIMIT '.$elements_per_page.' OFFSET '.$start.'
;';
New installation -> Administration -> Photos -> Rating
Warning: pg_query() [function.pg-query]: Query failed: ERROR: column "i.id" must appear in the GROUP BY clause or be used in an aggregate function LINE 2: SELECT i.id, ^ in /var/www/sites/fotos.aarjan.nl/piwigo/piwigo_svn/include/dblayer/functions_pgsql.inc.php on line 122
SELECT i.id, i.path, i.file, i.tn_ext, i.average_rate, MAX(r.date) AS recently_rated, COUNT(r.rate) AS nb_rates, SUM(r.rate) AS sum_rates FROM piwigo_rate AS r LEFT JOIN piwigo_images AS i ON r.element_id = i.id WHERE 1 = 1 GROUP BY r.element_id ORDER BY recently_rated DESC LIMIT 10 OFFSET 0 ;
ERROR: column "i.id" must appear in the GROUP BY clause or be used in an aggregate function LINE 2: SELECT i.id, ^
No tags attached.
Issue History
2010.06.23 00:22madmanNew Issue
2010.06.23 00:22madmanbrowser => Mozilla
2010.06.23 00:22madmanDatabase engine and version => Postgresql 8.4.2
2010.06.23 00:22madmanPHP version => 5.3.2
2010.06.23 00:22madmanWeb server => Apache 2.2.3
2010.06.23 20:18nikrouStatusnew => assigned
2010.06.23 20:18nikrouAssigned To => nikrou
2010.06.25 23:19svnCheckin
2010.06.25 23:19svnNote Added: 0004008
2010.06.25 23:21svnCheckin
2010.06.25 23:21svnNote Added: 0004009
2010.06.25 23:21nikrouStatusassigned => closed
2010.06.25 23:21nikrouResolutionopen => fixed
2010.06.25 23:21nikrouFixed in Version => 2.1.2
2010.06.30 00:04plgSummaryRating page returns database error => [PostgreSQL] Rating page returns database error

Notes
(0004008)
svn   
2010.06.25 23:19   
[Subversion] r6606 by nikrou on branch 2.1

-----[Subversion commit log]----------------------------------------------------
Bug 1740 fixed : Rating page returns database error
Query had incomplete group by clause
(0004009)
svn   
2010.06.25 23:21   
[Subversion] r6607 by nikrou on trunk

-----[Subversion commit log]----------------------------------------------------
Bug 1740 fixed : Bug 1740 fixed : Rating page returns database error
Query had incomplete group by clause
Merge from branch 2.1