MantisBT - Piwigo
View Issue Details
0001735Piwigodatabasepublic2010.06.21 22:562010.06.24 22:36
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
0001735: Comment page is not PostgreSQL compatible
The query on line 361 in comments.php is not compatible with PostgreSQL (or other databases). If there is an aggregate function (like count, min, max, etc) used in the SELECT, all other columns in the select should be listed in the GROUP BY.

The solution to this problem is a bit unclear to me. At first I would suggest to remove the GROUP BY since there is no need for it. But when I look at the rest of the code, I get the impression that only the first image_category should be displayed. Since an image can be in different categories, and a comment is related to an image, a comment on an image that is more than one category will return multiple rows here. The group by suggests only one category should be returned here. Note that MySQL makes a guess which one that should be! This is why PostgreSQL (and other database) will return an error.

Looking at the code I would suggest the following rewrite of the query. This will return a single category if an image is in more than one.

$query = '
SELECT com.id AS comment_id
     , com.image_id
     , min(ic.category_id)
     , com.author
     , com.author_id
     , com.date
     , com.content
     , com.validated
  FROM '.IMAGE_CATEGORY_TABLE.' AS ic
    INNER JOIN '.COMMENTS_TABLE.' AS com
    ON ic.image_id = com.image_id
    LEFT JOIN '.USERS_TABLE.' As u
    ON u.'.$conf['user_fields']['id'].' = com.author_id
  WHERE '.implode('
    AND ', $page['where_clauses']).'
  GROUP BY comment_id, com.image_id, com.author, com.author_id, com.date, com.content, com.validated
  ORDER BY '.$page['sort_by'].' '.$page['sort_order'];
New installation -> Open comments page
Warning: pg_query() [function.pg-query]: Query failed: ERROR: column "com.image_id" must appear in the GROUP BY clause or be used in an aggregate function LINE 3: , com.image_id ^ in /var/www/piwigo_svn/include/dblayer/functions_pgsql.inc.php on line 122
SELECT com.id AS comment_id , com.image_id , ic.category_id , com.author , com.author_id , com.date , com.content , com.validated FROM piwigo_image_category AS ic INNER JOIN piwigo_comments AS com ON ic.image_id = com.image_id LEFT JOIN piwigo_users As u ON u.id = com.author_id WHERE 1=1 AND 1 = 1 GROUP BY comment_id ORDER BY date DESC LIMIT 10 OFFSET 0 ;
ERROR: column "com.image_id" must appear in the GROUP BY clause or be used in an aggregate function LINE 3: , com.image_id ^
No tags attached.
Issue History
2010.06.21 22:56madmanNew Issue
2010.06.21 22:56madmanbrowser => Mozilla
2010.06.21 22:56madmanMySQL version => Postgresql 8.4.2
2010.06.21 22:56madmanPHP version => 5.3.2
2010.06.21 22:56madmanWeb server => Apache 2.2.3
2010.06.21 22:57madmanNote Added: 0003984
2010.06.21 22:57madmanNote Edited: 0003984
2010.06.22 09:19plgStatusnew => assigned
2010.06.22 09:19plgAssigned To => nikrou
2010.06.23 22:15nikrouNote Added: 0003998
2010.06.24 20:36svnCheckin
2010.06.24 20:36svnNote Added: 0003999
2010.06.24 20:39nikrouNote Edited: 0003998
2010.06.24 22:22svnCheckin
2010.06.24 22:22svnNote Added: 0004004
2010.06.24 22:35svnCheckin
2010.06.24 22:35svnNote Added: 0004005
2010.06.24 22:36nikrouStatusassigned => closed
2010.06.24 22:36nikrouResolutionopen => fixed
2010.06.24 22:36nikrouFixed in Version => 2.1.2

Notes
(0003984)
madman   
2010.06.21 22:57   
This issue is related to 0001722

(0003998)
nikrou   
2010.06.23 22:15   
(edited on: 2010.06.24 20:39)
Thanks for report. I can of course reproduce the problem but your proposition doesn't work.

The group by is here to get unique comment. An image can be in severals categories but the comment is attached to the image only.

(0003999)
svn   
2010.06.24 20:36   
[Subversion] r6596 by nikrou on trunk

-----[Subversion commit log]----------------------------------------------------
Bug 1735 fixed : Comment page is not PostgreSQL compatible
Fixed by adding all fields except category_id in group by clause
category_id is retrieved later in an another query.

Fixed also problem of FROM_UNIXTIME function not POstgreSQL compatible.
(0004004)
svn   
2010.06.24 22:22   
[Subversion] r6601 by nikrou on trunk

-----[Subversion commit log]----------------------------------------------------
Bug 1735 fixed : amend commit 6596
Need to add permissions filter to retrieve categories
(0004005)
svn   
2010.06.24 22:35   
[Subversion] r6602 by nikrou on branch 2.1

-----[Subversion commit log]----------------------------------------------------
Bug 1735 fixed : merge from trunk
Comment page is not PostgreSQL compatible
Fixed by adding all fields except category_id in group by clause
category_id is retrieved later in an another query (with permissions filter)

Fixed also problem of FROM_UNIXTIME function not POstgreSQL compatible