Re: [SQL] Inconsistent or incomplete behavior obverse in where - Mailing list pgsql-hackers

Josh,
Thanks for the reply.  Much of what you say is as we expected.  
I see that 7.3 has addressed the "Unable to identify an operator 
'=' for types 'numeric' and 'double precision'" problem, but 
I'm not sure how.  Context-sensitive approach? Overloaded operator
approach? Something else ( is there )?

If the release of 7.3 is soon, perhaps we can get by with the 
band-aid approach of overloading the comparison operators 
until such time as the new version is available.  Production
for us is next spring, so maybe we'll be okay on this one.
This approach would certainly allow our development team to
right their code one way.

> 
> 
> 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.
> 

No, we made no modifications to numeric = integer.  In fact, issuing
DROP OPERATOR (numeric,float8); 
cleared that problem right up.  And brought us back to square one.

> > 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.
> 

I think this is a great idea.  We're more of the postgres user class 
than hacker class, so that's out of our scope to undertake, but
we'd sure use it.

> > 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
> 

Thanks for doing that.
- Paul Ogden
> 
> 
> 
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster