Re: Inconsistent or incomplete behavior obverse in where

Paul,

> "Unable to identify an operator '=' for types 'numeric' and 'double
> precision' You will have to retype this query using an explicit cast"

This is due, as you surmised, to decimal values defaulting to floats.
While there is little problem with an = operator for numeric and
float, you would not want an implicit cast for a / operator with
numeric and float. As a result, I believe that all numeric and float
operators have been left undefined.

> I am aware of the use of type casting to force the desired
> behavior in these situations. I have also started to go down
> the road of creating functions and operators to force numeric
> to numeric comparison operations when comparing numeric to float,
> but realize that this approach is fraught with pitfalls, in fact
> it is interesting to us to note that with an operator in place
> to force numeric = float comparisons to parse as numeric = numeric,
> we started getting the opposite behavior. Queries with 'column
> reference' = 0.0 worked fine, but queries with 'column reference' = 0
>
> threw a variant of the previous exception:
>
> "Unable to identify an operator '=' for types 'numeric' and
> 'integer'"

Now, that's interesting. Why would defining a "numeric = float" have
broken "numeric = integer"? There's no reason I can think of.
Perhaps I will try this myself and see if I encounter the same
problem, or if your team modified the numeric = integer operator by
mistake.

> Overall, this behavior appears to be inconsistent and is not
> the same behavior I have experienced with many other DBMS's.
> Specifically, it seems strange that the parser does not treat
> values 0.0 or 77.5 as numeric(s[,p]) when comparing the values
> to a column reference known to be of type numeric (s,[p]).
>
> Is an unquoted number in the form of NN.N always treated as a
> float?

Yes. I believe that this is from the SQL 92 spec; hopefully someone
on this list with a copy of the Guide to the SQL Standard can quote it
for you.

> If the planner could somehow recognize that the constant/
> literal value was being compared to a column reference of the
> type numeric (s,p) and treat the value accordingly, then would
> operator identification no longer be a problem?

It's an interesting idea, and would be wonderful if it could be made to
work. However, the challenge of getting the program to correctly
recognize the context for all literal values *without* making any wrong
assumptions that would afffect the data could be substantial.

Most other RDBMSs deal with this, not by any kind of data type
context-sensitivity, but simply by supporting a large number of
implicit casts. This approach can have its own perils, as I have
experienced with MS SQL Server, where the average of splits for 120,000
transactions is significantly different if you accidentally let the
database implicitly cast the values as Float instead of Numeric.

As such, there was talk on the Hackers list at one time of *reducing*
the number of implicit casts instead of increasing them. This would
obviously make your particular problem even worse, but the proponents
of reduction point out that implicit casts can get you into real
trouble if you're not aware of them, wheras forcing explicit casts just
gets you error messages.

Hmmm ... in fact, I'd think the perfect solution would be a
compile-time option or contrib package which allows you to
enable/disable implicit casts for many data types.

> We are looking to maintain a high degree of portability in our
> application code, and while "CAST ( expression as type )" is
> fairly portable, no one here feels that it is a portable as
> column reference = literal/constant value. If someone knows
> of a better approach, or can point us to documentation of build or
> run-time configuration that affects the query planner where this
> issue is concerned, it would be much appreciated.

Hopefully someone else will respond to your message as well. I'll
re-phrase one of your questions for the Hackers list:

QUESTION: Is there any way we could distinguish between literals and
column references when processing operators? That is, while we would
*not* want to implicitly convert a float column to numeric for equality
comparison, would it be possible to convert a literal value to match
the column to which it is compared? Or is literal processing completed
before any expressions are evaluated?

-Josh Berkus