Re: boolean function return values

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