0

This one has us perplexed ...

We have a query that uses CAST to convert a float to a decimal, this query joins a number of tables to find the rows to return. One of the rows in one of the tables contains a value that when CAST to a decimal causes an Arithmetic Overflow Error.

The strange thing is that the row that has this value is NOT one of the rows that is being returned in the result set.

Overly simplified example:

ID  Value
1   1.1
2   11.1
3   11111.1

Query:

SELECT Id, CAST(value as decimal(4,1))
FROM <complex number of joins>
WHERE <conditions that don't return row with Id 3>

... Arithmetic Error

If we explicitly exclude that row in the WHERE clause then the error goes away. Eg. WHERE ... AND Id <> 3

.. Works fine

Does anyone know how this is possible?

NOTE: The issue here is not that the CAST fails on row with Id 3! The issue is that the WHERE clause excludes the row with Id 3, and yet the query still fails. How can the query fail if the row with value 11111.1 is not being returned by the WHERE clause?

Jack
  • 3,444
  • 5
  • 34
  • 50
  • Please provide the execution plan. I have seen that sometime plan evaluates a row even if it won't be returned after all steps are executed. If it CAST on that row it will error even if the row is supposed to be filtered out. – Salim Jan 17 '20 at 05:03
  • @Salim In the table involved there may be a column value which can not be converted to the specified cast as in the query. – Suraj Kumar Jan 17 '20 at 05:05
  • Yes there is a value but why is it being cast when it is not in the resulset? Any doco that explain this? – Jack Jan 17 '20 at 05:07
  • @Jack, what is the data type of each of the columns before casting to decimal? In this particular case, what is the data type of the table in question? – Attie Wagner Jan 17 '20 at 06:19
  • Id is int, Value is float. – Jack Jan 17 '20 at 23:06

3 Answers3

0

The type DECIMAL(4, 1) means a total of four places of precision, one of which is to the right of the decimal place. So, to accommodate the value 11111.1, you would need at least DECIMAL(6, 1). The following query should work:

SELECT Id, CAST(value AS DECIMAL(6,1))
FROM <complex number of joins>
WHERE <conditions that don't return row with Id 3>

At least, the above would work for the three points of sample data you provided.

Demo

Tim Biegeleisen
  • 502,043
  • 27
  • 286
  • 360
  • Please read the question again. You have not grasped the issue. – Jack Jan 17 '20 at 05:02
  • @Jack No, I think I grasp your question correctly. You should check the demo link I included in my answer, which is now working. Change the precision back to `DECIMAL(4,1)` to see it start failing again. – Tim Biegeleisen Jan 17 '20 at 05:03
  • The issue is that the query fails even though the resultset doesn't include the row with Id 3. Since row 3 is not in the result set, it shouldn't be casting 11111.1 ... this has something to do with the way SQL creates query plans. – Jack Jan 17 '20 at 05:05
  • Not likely. My first guess is your `WHERE` clause is still including some _other_ value which can't fit in `DECIMAL(4,1)`. Did you think to check for that? – Tim Biegeleisen Jan 17 '20 at 05:06
  • Yep, and there isn't. There is only one value in one field in one row in that table that cannot be cast to dec(4,1) and the where clause excludes that row. – Jack Jan 17 '20 at 05:12
0

It seems that the filter is being applied after the operation, not the other way around. Take a look at the execution plan for your query to help you understand the order of operations.

STLDev
  • 5,950
  • 25
  • 36
  • The table with the value that cannot be cast is the table from the complex join being evaluated first in the query plan. Still doesn't answer why it would do the cast at that point. Seems counter intuitive. – Jack Jan 17 '20 at 05:41
  • The filtering out of unwanted computed results could be the last step before delivery of desired results. – STLDev Jan 17 '20 at 05:43
  • can you elaborate? I think you mean that the CAST should have been the last step - which is my understanding also, yet the evidence suggests it is done earlier. – Jack Jan 17 '20 at 05:48
0

it is not because of your where condition which filters data, but it is because you have chosen less data length in cast. You should change it to DECIMAL(8,2) or maximum length of your column data is there. You can try following example which will explain you how it works.

Following will work as it doesn't fetch any data

WITH yourTable AS (
    SELECT 1 AS ID, '1.1' AS Value UNION ALL
    SELECT 2, '11.1' UNION ALL
    SELECT 3, '11111.1313'
)

SELECT Id, CAST(value as decimal(4,1)) AS Id_casted
FROM yourTable WHERE yourTable.ID=4

Following won't work as decimal value exceed than conversion length

WITH yourTable AS (
    SELECT 1 AS ID, '1.1' AS Value UNION ALL
    SELECT 2, '11.1' UNION ALL
    SELECT 3, '11111.1313'
)

SELECT Id, CAST(value as decimal(4,1)) AS Id_casted
FROM yourTable WHERE yourTable.ID=3

You can solve this by changing Decimal(4,1) = 3 digit to Decimal(8,2) = 6 digit 

WITH yourTable AS (
    SELECT 1 AS ID, '1.1' AS Value UNION ALL
    SELECT 2, '11.1' UNION ALL
    SELECT 3, '11111.1313'
)

SELECT Id, CAST(value as DECIMAL(8,2)) AS Id_casted
FROM yourTable WHERE yourTable.ID=3

Simply following code shows it throws exception as max value will be 999.99 for numeric(5,2) and when you assign 1000 it will throw exception

DECLARE @aritherror NUMERIC(5,2)
SET @aritherror = 1000.554
SELECT @aritherror
Ketan Kotak
  • 942
  • 10
  • 18
  • Please read question again. You have misunderstood the true issue here. – Jack Jan 17 '20 at 05:51
  • I totally understand your question. your 3rd row having value 11111 which cannot be converted into decimal(4,1) as decimal(4,1) allows maximum 999.9 value only – Ketan Kotak Jan 17 '20 at 05:55
  • you try to convert decimal(8,1) in query and check it should work – Ketan Kotak Jan 17 '20 at 05:55
  • Row #3 is not being returned, so how is it failing the CAST? – Jack Jan 17 '20 at 05:56
  • it may be possible in some joins sql internally create temp result set and perform where condition on second step. it can be identify with execution plan only. but did it work if you change it to (8,1) ?try it once atleast – Ketan Kotak Jan 17 '20 at 06:06
  • Please show your entire query which will be more helpful. else it should work if it doesn't return value for id=3 – Ketan Kotak Jan 17 '20 at 06:16
  • Yes it works if I change the decimal precision. I would expect it to work if row 3 is excluded, but as you say it must be some internal temp result set. Unfortunately the query has IP and my employer would freak if I posted it or the query plan. – Jack Jan 17 '20 at 23:05
  • ok. I totally understand and try to exclude it=3 condition to put in join instead of where condition and check if it works. which will change execution plan as join and where to where and join – Ketan Kotak Jan 18 '20 at 05:55
  • Thank you Ketan. There are many different ways to 'avoid' or work around this issue, however my question is about why it happens and what predictable conditions/rules exist that SQL Server is using that shed some light on this. – Jack Jan 20 '20 at 22:14