[Tickets #10633] DB query with output name in HAVING clause

[bugs at horde.org]

DO NOT REPLY TO THIS MESSAGE. THIS EMAIL ADDRESS IS NOT MONITORED.

Ticket URL: http://bugs.horde.org/ticket/10633
------------------------------------------------------------------------------
  Ticket             | 10633
  Created By         | thpo+horde at dotrc.de
  Summary            | DB query with output name in HAVING clause
  Queue              | Content
  Version            | 1.0.1
  Type               | Bug
  State              | Unconfirmed
  Priority           | 1. Low
  Milestone          |
  Patch              |
  Owners             |
------------------------------------------------------------------------------


thpo+horde at dotrc.de (2011-10-11 21:05) wrote:

Note: this is actually Content 1.0.2 (but I could not select this for  
the ticket.

In function getSimilarObjects the HAVING clause uses the output name  
of a selected column. But according to the PostgreSQL doku, HAVING  
operates on the rows created by GROUP BY and thus can not use the  
output name.

As a result I get a fatal error with PostgreSQL:

2011-10-11T22:59:10+02:00 ERR: HORDE [ansel] SQL QUERY FAILED:  
SQLSTATE[42703]: Undefined column: 7 ERROR:  column "num_common_tags"  
does not exist
LINE 1: ...ct_id <> 2285 GROUP BY objects.object_name HAVING num_common...
                                                              ^
         SELECT objects.object_name, COUNT(matches.object_id) AS
           num_common_tags FROM "rampage_tagged" matches INNER JOIN
           "rampage_tags" tags ON (tags.tag_id = matches.tag_id) INNER JOIN
           "rampage_users" users ON users.user_id = matches.user_id AND
           users.user_name = 'thpo' INNER JOIN "rampage_objects" objects ON
           objects.object_id = matches.object_id INNER JOIN  
"rampage_types" types
           ON types.type_id=objects.type_id AND types.type_name = 'image' WHERE
           tags.tag_id IN (7) AND matches.object_id <> 2285 GROUP BY
           objects.object_name HAVING num_common_tags >= 1 ORDER BY
           num_common_tags DESC LIMIT 10 [pid 29125 on line 812 of  
"/usr/share/php/Horde/Db/Adapter/Base.php"]
2011-10-11T22:59:10+02:00 EMERG: HORDE [ansel] SQLSTATE[42703]:  
Undefined column: 7 ERROR:  column "num_common_tags" does not exist
LINE 1: ...ct_id <> 2285 GROUP BY objects.object_name HAVING num_common...
                                                              ^ [pid  
29125 on line 393 of "/srv/horde4/ansel/lib/Tagger.php"]