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