Thread: Inconsistent or incomplete behavior obverse in where clause
Hello, Our application development group has observed what we feel is inconsistent behavior when comparing numeric column references to constant/literal values in SQL. I would appreciate comments on the best approach to this problem that will allow for the highest portability of our application code. I have searched the archives and online docs, but so far have not found anyone addressing the problem quite this way. Assume wuActive is a numeric field ( with scale but no precision ) in the table WU: select count(wuid) from WU where wuActive = 0 --works fine select count(wuid) from WU wherewuActive = '0' --works fine select count(wuid) from WU where wuActive = '0.0' --works fine select count(wuid) from WUwhere wuActive = 0.0 --throws the following exception: "Unable to identify an operator '=' for types 'numeric' and 'double precision' You will have to retype this query using an explicit cast" Second, assume tPct is a numeric field ( having scale of 4 and precision of 1 ) in the table T select count(tid) from T where tPct > 77 --works fine select count(tid) from T where tPct> '77' --works fine select count(tid) from T where tPct > '77.5' --works fine select count(tid) from T where tPct > 77.5-- again throws the exception: "Unable to identify an operator '>' for types 'numeric' and 'double precision' You will have to retype this query using an explicit cast" This seems to occur regardless of connectivity drivers used (ODBC, JDBC, etc..) 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'" 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? 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? 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. Thanks, Paul Ogden Database Administrator/Programmer Claresco Corporation (510) 549-2290
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 fornumeric 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 thesame 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
Paul Ogden <pogden@claresco.com> writes: > select count(wuid) from WU where wuActive = 0.0 --throws the > following exception: > "Unable to identify an operator '=' for types 'numeric' and 'double > precision' You will have to retype this query using an explicit cast" This is fixed as of 7.3. (We still have related issues for smallint and bigint columns, unfortunately.) regards, tom lane
"Josh Berkus" <josh@agliodbs.com> writes: > Now, that's interesting. Why would defining a "numeric = float" have > broken "numeric = integer"? There's no reason I can think of. The problem probably is that the parser now finds two possible interpretations that look equally good to it, so it can't choose. It could coerce the integer constant to numeric (and use numeric=numeric) or to float (and use the added numeric=float operator), and there's no rule that can break the tie. In 7.3 and 7.4 we are actually going in the direction of removing cross-data-type operators, not adding them, because they tend to create too many options for the parser to choose from. regards, tom lane
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
Paul, > 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 )? A modification of the operators available. > 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. 7.3 final is expected before December. -- -Josh Berkus