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

* Your Email Address
* Spam protection	Enter the letters below: .___..__ .__..__..__ _/ [ __\| \|\| \|[__) ./__.[_./\|__\|\|__\|\| \
Comment	> 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. > >
Attachment
Watch this ticket

Saved Queries