Piwigo Bugtracker

Viewing Issue Advanced Details Jump to Notes ] Issue History ] Print ]
ID Category Severity Reproducibility Date Submitted Last Update
0001735 [Piwigo] database minor always 2010.06.21 22:56 2010.06.24 22:36
Reporter madman View Status public  
Assigned To nikrou
Priority normal Resolution fixed Platform Postgresql
Status closed   OS CentOS 5.4
Projection none   OS Version Postgresql 8.4.2
ETA none Fixed in Version 2.1.2 Product Version 2.1.1
  Target Version Product Build Trunk r6565
Summary 0001735: Comment page is not PostgreSQL compatible
Description 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'];
Steps To Reproduce New installation -> Open comments page
Additional Information 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 ^
Tags No tags attached.
browser Mozilla
Database engine and version Postgresql 8.4.2
PHP version 5.3.2
Web server Apache 2.2.3
Attached Files

- Relationships

-  Notes
(0003984)
madman (reporter)
2010.06.21 22:57
edited on: 2010.06.21 22:57

This issue is related to 0001722

(0003998)
nikrou (developer)
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 (reporter)
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 (reporter)
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 (reporter)
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

- Issue History
Date Modified Username Field Change
2010.06.21 22:56 madman New Issue
2010.06.21 22:56 madman browser => Mozilla
2010.06.21 22:56 madman MySQL version => Postgresql 8.4.2
2010.06.21 22:56 madman PHP version => 5.3.2
2010.06.21 22:56 madman Web server => Apache 2.2.3
2010.06.21 22:57 madman Note Added: 0003984
2010.06.21 22:57 madman Note Edited: 0003984
2010.06.22 09:19 plg Status new => assigned
2010.06.22 09:19 plg Assigned To => nikrou
2010.06.23 22:15 nikrou Note Added: 0003998
2010.06.24 20:36 svn Checkin
2010.06.24 20:36 svn Note Added: 0003999
2010.06.24 20:39 nikrou Note Edited: 0003998
2010.06.24 22:22 svn Checkin
2010.06.24 22:22 svn Note Added: 0004004
2010.06.24 22:35 svn Checkin
2010.06.24 22:35 svn Note Added: 0004005
2010.06.24 22:36 nikrou Status assigned => closed
2010.06.24 22:36 nikrou Resolution open => fixed
2010.06.24 22:36 nikrou Fixed in Version => 2.1.2


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