"Glen Parker" <glenebob@nwlink.com> writes:
> This popped up because we're doing dev work against a 7.4 server but
> deploying against a 7.2 server. To make matters worse, here's one of the
> errors we get on 7.2 after doing the casts and re-deploying:
> DB=# select 1234::numeric > 1234::float;
> ERROR: Unable to identify an operator '>' for types 'numeric' and 'double
> precision'
> You will have to retype this query using an explicit cast
> Again, WTF??? Since this is on 7.2 (and fixed on >= 7.3), I'm not too
> worried about it, but it, too, is quite broken.
This sort of problem is exactly why we tightened the implicit casting
rules.
In the above example, the parser has to choose between casting the
numeric to float and applying float greater-than, or casting the float
to numeric and applying numeric greater-than. In 7.2 and before these
two alternatives look equally good and the parser cannot make a choice,
so it fails as above. In 7.3 and later, only the numeric-to-float cast
is allowed to be applied implicitly, so the parser is forced down the
road that leads to choosing float comparison.
(If you're wondering why we like that direction better than the other,
it's because the SQL spec says that operations combining exact and
inexact numeric values yield inexact results. So numeric + float has to
be implemented as float addition.)
> The result is that development work is suddenly a big pain in the a$$ where
> these queries are concerned.
It's taken us quite a while to get these things right...
regards, tom lane