Piwigo Bugtracker

View Issue Details Jump to Notes ] Issue History ] Print ]
IDProjectCategoryView StatusDate SubmittedLast Update
0001735Piwigodatabasepublic2010.06.21 22:562010.06.24 22:36
Reportermadman 
Assigned Tonikrou 
PrioritynormalSeverityminorReproducibilityalways
StatusclosedResolutionfixed 
PlatformPostgresqlOSCentOS 5.4OS VersionPostgresql 8.4.2
Product Version2.1.1 
Target VersionFixed in Version2.1.2 
Summary0001735: Comment page is not PostgreSQL compatible
DescriptionThe 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 ReproduceNew installation -> Open comments page
Additional InformationWarning: 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 ^
TagsNo tags attached.
browserMozilla
Database engine and versionPostgresql 8.4.2
PHP version5.3.2
Web serverApache 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


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