Thread: Minor mathematical error in documentation

Hi,

I've had this minor bugbear with this part of the docs for a while and
am finally reporting it.

http://www.postgresql.org/docs/8.3/static/sql-expressions.html (And all
back branch documentation)

SELECT ... WHERE x <> 0 AND y/x > 1.5;

But this is safe:

SELECT ... WHERE CASE WHEN x <> 0 THEN y/x > 1.5 ELSE false END;

A CASE construct used in this fashion will defeat optimization attempts,
so it should only be done when necessary. (In this particular example,
it would be best to sidestep the problem by writing y > 1.5*x instead.)


In-equality transformations do not guarantee that y > 1.5x == y/x >
1.5.  This is only true for x>0, y < 1.5*x for x<0.  I have not posted a
patch as I'm not sure what is the best way to change the example.

Regards

Russell Smith

Russell Smith wrote:
> SELECT ... WHERE CASE WHEN x <> 0 THEN y/x > 1.5 ELSE false END;
>
> A CASE construct used in this fashion will defeat optimization attempts,
> so it should only be done when necessary. (In this particular example,
> it would be best to sidestep the problem by writing y > 1.5*x instead.)
>
>
> In-equality transformations do not guarantee that y > 1.5x == y/x >
> 1.5.  This is only true for x>0

So the proper expression would be

SELECT ... WHERE CASE WHEN x >= 0 THEN y > 1.5*x ELSE y < 1.5*x END;

or

SELECT ... WHERE (x >= 0 AND y > 1.5*x) OR y < 1.5*x;

which obviously isn't simpler.  So I suggest that we just delete the
parenthetical note.

--
Peter Eisentraut
http://developer.postgresql.org/~petere/

Russell Smith <mr-russ@pws.com.au> writes:
> In-equality transformations do not guarantee that y > 1.5x == y/x >
> 1.5.  This is only true for x>0, y < 1.5*x for x<0.  I have not posted a
> patch as I'm not sure what is the best way to change the example.

Seems a bit nit-picky, but we could change the example to

SELECT ... WHERE x > 0 AND y/x > 1.5;
becomes
SELECT ... WHERE CASE WHEN x > 0 THEN y/x > 1.5 ELSE false END;

            regards, tom lane

Tom Lane wrote:
> Russell Smith <mr-russ@pws.com.au> writes:
>
>> In-equality transformations do not guarantee that y > 1.5x == y/x >
>> 1.5.  This is only true for x>0, y < 1.5*x for x<0.  I have not posted a
>> patch as I'm not sure what is the best way to change the example.
>>
>
> Seems a bit nit-picky, but we could change the example to
>
I would agree, it's nit-picky.  But lots of people rely on the truth of
the documentation.  People say I have any y/x > z situation, PostgreSQL
manual says it works better if I do y > z*x.  So they do it, no
questions asked. Which is the real fear from my point of view.
> SELECT ... WHERE x > 0 AND y/x > 1.5;
> becomes
> SELECT ... WHERE CASE WHEN x > 0 THEN y/x > 1.5 ELSE false END;
>
>             regards, tom lane
>
> ---------------------------(end of broadcast)---------------------------
> TIP 9: In versions below 8.0, the planner will ignore your desire to
>        choose an index scan if your joining column's datatypes do not
>        match
>
>