Re: Typecast bug?

Tom Lane wrote:
> Craig James <craig_james@emolecules.com> writes:
>> This seems like a bug to me, but it shows up as a performance problem.
>
>> emol_warehouse_1=> explain analyze select version_id, parent_id from version where version_id =
999999999999999999999999999;
>
> If you actually *need* so many 9's here as to force it out of the range
> of bigint, then why is your id column not declared numeric?
>
> This seems to me to be about on par with complaining that "intcol = 4.2e1"
> won't be indexed.  We have a numeric data type hierarchy, learn to
> work with it ...

Your suggestion of "learn to work with it" doesn't fly.  A good design separates the database schema details from the
applicationto the greatest extent possible.  What you're suggesting is that every application that queries against a
Postgresdatabase should know the exact range of every numeric data type of every indexed column in the schema, simply
becausePostgres can't recognize an out-of-range numeric value. 

In this case, the optimizer could have instantly returned zero results with no further work, since the query was out of
rangefor that column. 

This seems like a pretty simple optimization to me, and it seems like a helpful suggestion to make to this forum.

BTW, this query came from throwing lots of junk at a web app in an effort to uncover exactly this sort of problem.
It'snot a real query, but then, hackers don't use real queries.  The app checks that its input is a well-formed integer
expression,but then assumes Postgres can deal with it from there. 

Craig