[Tickets #5006] postgresql 8.2.1: Errormessage "IN types character varying and integer cannot be matched"

[bugs@bugs.horde.org]

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

Ticket URL: http://bugs.horde.org/ticket/?id=5006
-----------------------------------------------------------------------
 Ticket             | 5006
 Created By         | mussi at snoop.alphanet.ch
 Summary            | postgresql 8.2.1: Errormessage "IN types character varying and integer
                    | cannot be matched"
 Queue              | Horde Base
 Version            | HEAD
 Type               | Bug
 State              | Unconfirmed
 Priority           | 2. Medium
 Owners             | 
-----------------------------------------------------------------------


mussi at snoop.alphanet.ch (2007-02-15 16:51) wrote:

I have just installed the latest HEAD versions of horde and found a rather
annoying database misbehavior (tested with trean HEAD and mnemo HEAD),
with always the same error message and a select like this:

========================================================

horde=> SELECT c.datatree_id, c.datatree_name FROM horde_datatree c LEFT
JOIN horde_datatree_attributes a1 ON a1.datatree_id = c.datatree_id LEFT
JOIN horde_datatree_attributes a2 ON a2.datatree_id = c.datatree_id WHERE
c.group_uid = 'horde.shares.trean' AND (a1.attribute_name = 'perm_groups'
AND a1.attribute_key IN (CAST(425 AS INT), CAST(421 AS INT),CAST(423 AS
INT)) AND CASE WHEN CAST(a1.attribute_value AS VARCHAR) ~ '^-?[0-9]+$'
THEN (CAST(a1.attribute_value AS int4) & 8) <> 0 ELSE FALSE END)  AND
a2.attribute_name = 'name'  GROUP BY c.datatree_id, c.datatree_name,
c.datatree_order, a2.attribute_value ORDER BY a2.attribute_value ASC;
ERROR:  IN types character varying and integer cannot be matched

STATEMENT:  SELECT c.datatree_id, c.datatree_name FROM horde_datatree c
LEFT JOIN horde_datatree_attributes a1 ON a1.datatree_id = c.datatree_id
WHERE c.group_uid = 'horde.shares.mnemo' AND (a1.attribute_name =
'perm_groups' AND a1.attribute_key IN (425, 421, 423) AND CASE WHEN
CAST(a1.attribute_value AS VARCHAR) ~ '^-?[0-9]+$' THEN
(CAST(a1.attribute_value AS INTEGER) & 2) <> 0 ELSE FALSE END)   GROUP BY
c.datatree_id, c.datatree_name, c.datatree_order ORDER BY
c.datatree_order, c.datatree_name, c.datatree_id


=======================================================

However, if I alter the SQL statement slightly:

=======================================================

horde=> SELECT c.datatree_id, c.datatree_name FROM horde_datatree c LEFT
JOIN horde_datatree_attributes a1 ON a1.datatree_id = c.datatree_id LEFT
JOIN horde_datatree_attributes a2 ON a2.datatree_id = c.datatree_id WHERE
c.group_uid = 'horde.shares.trean' AND (a1.attribute_name = 'perm_groups'
AND a1.attribute_key IN (CAST(425 AS VARCHAR), CAST(421 AS
VARCHAR),CAST(423 AS VARCHAR)) AND CASE WHEN CAST(a1.attribute_value AS
VARCHAR) ~ '^-?[0-9]+$' THEN (CAST(a1.attribute_value AS int4) & 8) <> 0
ELSE FALSE END)  AND a2.attribute_name = 'name'  GROUP BY c.datatree_id,
c.datatree_name, c.datatree_order, a2.attribute_value ORDER BY
a2.attribute_value ASC;

=========================================================
or
=========================================================

horde=> SELECT c.datatree_id, c.datatree_name FROM horde_datatree c LEFT
JOIN horde_datatree_attributes a1 ON a1.datatree_id = c.datatree_id LEFT
JOIN horde_datatree_attributes a2 ON a2.datatree_id = c.datatree_id WHERE
c.group_uid = 'horde.shares.trean' AND (a1.attribute_name = 'perm_groups'
AND a1.attribute_key IN ('425', '421', '423') AND CASE WHEN
CAST(a1.attribute_value AS VARCHAR) ~ '^-?[0-9]+$' THEN
(CAST(a1.attribute_value AS int4) & 8) <> 0 ELSE FALSE END)  AND
a2.attribute_name = 'name'  GROUP BY c.datatree_id, c.datatree_name,
c.datatree_order, a2.attribute_value ORDER BY a2.attribute_value ASC;

=======================================================

It doesn't produce an error message anymore.

Postgresql 8.2.1 is used here, as well as the latest horde versions and
php 5.2.1.