unable to cast value as float

Question

We have a SQL that performs a CAST function (to FLOAT) on ColumnA. The SQL has a filter which in the end will indirectly filter out those rows that have non numeric values in ColumnA. However, because of what I believe is due to running portions of the SQL in paralell, I believe that the CAST is even applied to rows that are filtered out and this causes the SQL to fail on "unable to cast value as float..."

I know that if I run with one proc by adding the query hint

OPTION (MAXDOP 1)

that the SQL runs as expected. I suspect that running on 1 proc forces the filter to be applied to weed out the row with the non numeric values in columnA so that the CASTING of its values succeeds. I also found that using the query hint

OPTION (FORCE ORDER)

fixes the issue, I am assuming becausethis too ensures that the filter is applied first and I get much better query performance that one running on one cylinder.

I am leaning torwards fixing the issue using the 2nd option. If I have any misconceptions about what is going on here or if someone would like to expound on my general understand or make a recommendation, I would appreciate it.

I am running on

Microsoft SQL Server 2008 R2 (RTM) - 10.50.1720.0 (X64) Jun 12 2010 01:34:59 Copyright (c) Microsoft Corporation Enterprise Edition (64-bit) on Windows NT 5.2 (Build 3790: Service Pack 2)

An afterthought:

It seems that it would be nice is T-SQL had the following functions to check to see if a string could be converted to a particular datatype.

IsFloat IsNumeric IsInteger etc

I really am annoyed at how many columns of all sorts of data that I find in our database that are defined as varchar(255). I guess the solution is "not to do that!"

Answer 1

Regarding your after thought.

It seems that it would be nice is T-SQL had the following functions to check to see if a string could be converted to a particular datatype.

SQL Server 2012 does introduce TRY_CONVERT for this need. So the following query would return NULL rather than an error.

SELECT TRY_CONVERT ( FLOAT, 'Fish')

There is no guarantee even with serial plans that the WHERE clause will happen before the SELECT is evaluated. As explained in this blog post from SQL Server 2005 onwards this is more likely to happen than in previous versions. The Behavior Changes to Database Engine Features in SQL Server 2005 specifically calls this out as follows.

SQL Server 2005 sometimes evaluates expressions in queries sooner than when they are evaluated in SQL Server 2000. This behavior provides the following important benefits:

• The ability to match indexes on computed columns to expressions in a query that are the same as the computed column expression.
• The prevention of redundant computation of expression results.

More discussion about this behaviour is in another good blog post by Craig Freedman Conversion and Arithmetic Errors.

On versions prior to 2012 and TRY_CONVERT you need to wrap the CAST AS FLOAT in a CASE statement. e.g.

  SELECT CASE WHEN ISNUMERIC(Col)=1 THEN CAST(Col AS FLOAT) END AS Col
  FROM Table
  WHERE ISNUMERIC(Col)=1

This still isn't absolutely guaranteed to prevent you getting errors as ISNUMERIC itself just checks that the value would cast to one of the numeric datatypes rather than specifically to float An example of an input that would fail is '.'

CASE is documented to mostly short circuit in books online (some exceptions are discussed here)

You can also find additional discussion/complaints about this in the connect item SQL Server should not raise illogical errors and a good explanation of a similar issue by SQLKiwi

Answer 2

I believe you are correct. The CONVERT() function is being applied before the predicates "indirectly filter" out rows.

To avoid the exception, you are correct, one approach is to attempt to gain some modicum of control over the order of operations in the execution plan. If the statement hint is working for you, then you can go with that. (Personally, hints are a last resort for me.)

Note that SQL Server does have an IsNumeric function.

The IsNumeric function is somewhat inadequate, in the sense that there are some values which will "pass" an IsNumeric test but which will raise an exception when they are converted to a numeric datatype.

Personally, I would tend to go with this approach:

select convert(float,case when isnumeric( t.foo )=1 then t.foo else null end)

rather than statement level hints.

Or, I would specify the other predicates that should "filter" out values that should not be converted.

select convert(float,case when t.fi in ('fo','fum') then t.foo else null end)