Re: BUG #5358: Throwing unexpected ERROR

On Wed, Mar 3, 2010 at 8:48 AM, Gurjeet Singh <singh(dot)gurjeet(at)gmail(dot)com> wrote:
> On Wed, Mar 3, 2010 at 8:37 AM, Robert Haas <robertmhaas(at)gmail(dot)com> wrote:
>>
>> On Wed, Mar 3, 2010 at 7:29 AM, Gurjeet Singh <singh(dot)gurjeet(at)gmail(dot)com>
>> wrote:
>> > I just realized that it is the subquery pull-up that is leading to this
>> > problem, not predicate push-down. Sleeping over it does really help I
>> > guess
>> > :)
>> >
>> > So instead of the LIMIT 1000, OFFSET 0 clause is the right choice for
>> > preventing subquery pull-up without affecting the results.
>> >
>> > I don't think the optimizer has the push-down capabiity; I may be wrong.
>>
>> Maybe I'm just dense, but I don't understand what you're complaining
>> about here.  The SELECT DISTINCT already acts as an optimization
>> fence, so why would you need another one?  And what problem would you
>> expect it to solve?
>>
>
> I am complaining about the ERROR when I don't specify OFFSET or LIMIT.
>
> The query isn't relevant. It is there just to illustrate the fact that two
> supposedly equivalent forms of a query are not treated equivalent after all
> by Postgres.
>
> You don't put that OFFSET clause, you get an ERROR. You put in that OFFSET
> clause and you get proper results.
>
> I hope my complain is clearer now.

It does seem a little weird, but I don't think we're likely to change
the behavior. The optimizer is allowed to reorder quals, and I don't
think we want to change that. Consider a very large table which has
an index on column b but not on column a, and the following query:

SELECT * FROM some_huge_table WHERE a = 1 AND b = 1

All other things being equal, we'll want to execute this query by
doing an index scan for rows with b = 1 and then checking whatever
comes back to see whether we also have a = 1. If we insisted that a =
1 had to be evaluated first, we'd have to scan the whole table.

Normally this kind of reordering doesn't actually affect the result of
the query because normally the quals that are being evaluated don't
have any side-effects, but in your query you've chosen something that
can throw an exception, so it's user-visible.

...Robert