Re: Problem with index in OR'd expression

Tom Lane wrote:
> Well, you could update --- 8.2 contains code to recognize that the IS
> NULL expression is constant, but prior releases do not.

That's excellent to hear -- I'd missed that in my perusing of the
changelogs between 8.0.x and 8.2. That does give me one more reason to
upgrade. It appears I did not mention what version I was running --
sorry for that, though you guessed it was < 8.2. It's actually 8.0.x.

> However, if you're hoping to do this:
>
>> ((vC1 IS NULL) OR (C1 = vC1)) AND
>> ((vC2 IS NULL) OR (C2 = vC2)) ...
>
> you're still gonna lose because those are variables not constants ...

Well, that *is* what I'm hoping to do. I understand how (0 IS NULL) is
different from (variable IS NULL), but isn't it reasonable to expect
that PG could evaluate that expression only once (knowing that the
variable couldn't change during the course of the query execution) and
then treat that expression as constant? I appreciate that you're saying
that it won't work even in 8.2, but what I'm getting at is would it be
possible to add it in the future?

As I mentioned, I'm pretty sure that that must be what MSSQL (6.5, 7,
2000 and 2005 [all of which I've had some experience with]) seem to be
doing.

Now failing all of this, does any one have a better idea for what I'm
trying to do? A simple syntax for optionally including WHERE criteria
depending on the null-ness of variables (w/o having to go to dynamic
execution)?

Thanks for your reply Tom.

jl