Merged to FRAMEWORK_3 (Horde 3.1.5).

FYI: ran into this problem with horde3.1.4 (as of today 14.7 the most 
recent version). Installing the patch against the sql.php in 3.1.4 
seems to have solved the problem so far.

Fixed:

http://lists.horde.org/archives/cvs/Week-of-Mon-20070219/065546.html

I just updated to postgres 8.2.3 and can reproduce this now, but your 
patch simply breaks the SQL statement and avoids the problem that way. 
It doesn't fix the root problem.

I have just tried it again.



Update horde and framework. Type 'php -q install-packages.php' on my 
box, wait, and test Bookmarks and mnemo.



Lo' and behold, the same bug with the (421, 425, 423) without quotes 
appear again, and I don't see a single bookmark. Apply the patch in 
the horde directory against DataTree/sql.php and then rerun the 
install, and click 'reload' in the browser button. Bookmarks visible 
again.



I suspect the bug is only visible if one runs postgresql 8.2.1, so 
there might be some version dependency.

I am VERY sure.



I just removed the escaped-quotes construct mentioned in the tiff, did 
a reload of the horde site, clicked one on "Organizing", once on 
"Bookbarks", and clicked once onto the "Browse" in the tob bar button 
to browse my bookmarks and got the following error message:



Feb 20 21:16:12 HORDE [error] [trean] DB Error: unknown error: 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 INTEGER) & 4) <> 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 
[nativecode=ERROR:  IN types character varying and integer cannot be 
matched] [on line 1196 of "/usr/share/pear/Horde/DataTree/sql.php"]



When I put the quotes back in, it works. Coincidence?



The version string of sql.php of the DataTree package reads:



$Horde: framework/DataTree/DataTree/sql.php,v 1.225 2007/01/26 
05:50:34 chuck Exp $



I got it from snaps.horde.org which should be good enough for our purposes.

Are you sure you are using latest HEAD code? The line that you patch 
is responsible for a completely different SQL statement, and the code 
that produces the statement that causes your error is already putting 
quotes around the values.

The Diff is attached and has been tested somewhat and it doesn't look too bad.

Where is the code snipped that produces the SQL code
?

Can you please provide these changes as a patch to Horde_SQL?

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.