Thread: boolean function return values

Can someone explain why I would get different results when using "WHERE
x()"  v.s. using "WHERE x() = true" for functions that return a
boolean?  We have also seen the query optimizer behave differently
between the two uses when using functional indices.

I am using postgres 7.4.2 and here are the real details:

CREATE OR REPLACE FUNCTION validlivesitecontent(text, text, integer)
RETURNS boolean
    AS '
DECLARE
   Publisher ALIAS for $1;
   ContentType ALIAS for $2;
   Status ALIAS for $3;
BEGIN
   return (Status & 3 = 0) and (Publisher != ''DataSource'' and
ContentType != ''Packages'' and ContentType != ''SpecialDeals'');
END;
'
    LANGUAGE plpgsql IMMUTABLE STRICT;


user1=# select count(*) from t_content where
validlivesitecontent(publisher, contenttype, status);
 count
--------
 770403
(1 row)

user1=# select count(*) from t_content where
validlivesitecontent(publisher, contenttype, status) = true;
  count
---------
 1258365
(1 row)



Thanks!
--Eugene Barlow
TripAdvisor - www.tripadvisor.com

Eugene Barlow <barlow@tripadvisor.com> writes:
> Can someone explain why I would get different results when using "WHERE
> x()"  v.s. using "WHERE x() = true" for functions that return a
> boolean?  We have also seen the query optimizer behave differently
> between the two uses when using functional indices.

Well, x()=true will match a functional index on x(), whereas the other
will not.

> user1=# select count(*) from t_content where
> validlivesitecontent(publisher, contenttype, status);
>  count
> --------
>  770403
> (1 row)

> user1=# select count(*) from t_content where
> validlivesitecontent(publisher, contenttype, status) = true;
>   count
> ---------
>  1258365
> (1 row)

Is there anything you haven't told us here?  Like, say, whether there is
a functional index on validlivesitecontent(publisher, contenttype, status)?
I'm suspicious that there is one, the second query is using it (you
could verify that with EXPLAIN) and the index is broken for some reason.

One fairly likely way for such an index to get broken is that you
redefine the function's behavior without REINDEXing the index afterward.

            regards, tom lane