Thread: 7.4.7: due to optimizing of my query logik breaks

hi,

i have a zip code field in a table that is used by "international"
customers, which lead to very "random" data there. but a query should be
possible "by number" if there is only a number in the field. for
queriing the table an admin user can generate queries with a visual
interface - but the optimizier then kills my logic:

[fragment from the query - plz is the zip-code field]

...
) AND (
    eintrag.email like '%example.com'     -- #1
    OR (
        eintrag.plz ~ '^[0-9]{1,9}$'
        AND
        int4(eintrag.plz) = '0'
       ) -- #2
      )
...

an EXPLAIN shows me the result:

...
) AND (
    (
        (plz)::text ~ '^[0-9]{1,9}$'::text
    ) OR (
        email ~~ '%example.com'::text
    )
) AND (
    (
        int4((plz)::text) = 0
    ) OR (
        email ~~ '%example.com'::text
    )
)
...

of course this somehow "correct" assuming only boolean-logic - but not
for someone thinking in terms of a C-programmer.

please note: i can not reorder the #1 and #2 query parts, as the user
gives the order. i can only influence the generated sql-code to some
extends. of course another option would be to "fix the cast, if its
numbers" - but i have lots of other query parts, that contain more then
one compare itself that depend on each other.

--
cu

Christoph Frick <frick@sc-networks.com> writes:
> i have a zip code field in a table that is used by "international"
> customers, which lead to very "random" data there. but a query should be
> possible "by number" if there is only a number in the field. for
> queriing the table an admin user can generate queries with a visual
> interface - but the optimizier then kills my logic:

Please see
http://www.postgresql.org/docs/7.4/static/sql-expressions.html#SYNTAX-EXPRESS-EVAL
which specifically disclaims any promise of left-to-right evaluation
order.

Having said that, 8.0 and up no longer try to force the WHERE clause
into canonical AND-of-ORs form, so it's possible that what you would
like would happen in a newer version.

But my advice is to find another way to do it (maybe put the logic into
a plpgsql function?).  You will get absolutely zero sympathy for any
request to constrain evaluation order of a WHERE clause --- if we did
that it would completely defeat the ability to do query optimization of
any kind.  Accordingly, if future changes break your code again, you
won't have any recourse.

            regards, tom lane

On Wed, Sep 14, 2005 at 10:03:37AM -0400, Tom Lane wrote:

> http://www.postgresql.org/docs/7.4/static/sql-expressions.html#SYNTAX-EXPRESS-EVAL

thanks for this url - i can use the described CASE syntax for my case. i
just have to assure using the regexp, that the later cast to int4 will
work. i was not aware of the possibility to use the CASE also "behind"
the WHERE and not only "before".

--
cu