Hi,
Look at the simple set of SQL statements below: Execute the following on
Query Analyzer.
declare @.a float
declare @.b float
set @.a=24353.02821769137
set @.b=36459.95
exec('select '+@.a+'/'+@.b)
Ans=.6679392
Now, if I change the value of @.b to 36459.96,
I get a value of 0. Integer division is performed just because the value of
@.b increased by .01
This looks like bug with the internal workings of SQL. This is very
dangerous cause basic SQL assumption is that if a value is a float, the
division will also be a float division. Integer division on the other hand
gives erroneous results in this case.
Please solve this problem as soon as possible.
-Ling Yu.Ling Yu,
This is not a bug, but it is surprising, and in my opinion, the query
should actually cause a syntax error. I'm glad you raised the question,
and I bet other people have been affected by this behavior.
Here's why it occurs:
The query processor must evaluate the query string 'select '+@.a+'/'+@.b
as its first step. When strings are concatenated inside exec(), the +
operator is not a T-SQL + operator. The + is applied by the front-end
application and only after the entire query string is assembled does
that string go to the query processor to be interpreted as T-SQL.
Query Analyzer (or the system it uses to prepare queries for the engine)
follows different rules than T-SQL to interpret the + that puts strings
together within exec(). In T-SQL, the + operation would fail, because
it tries to convert 'select ' and '/' into floats and add. But here, @.a
and @.b are converted to strings. The way in which they are converted to
strings is not obvious, but you can see it if you execute
exec('select ' + @.a) and exec('select ' + @.b)
What's important to realize is that exec() receives only a string,
without any type information about the parameters. Unfortunately,
floats are converted to strings by the front-end using at most 6 decimal
digits of precision. Since @.a is converted in both cases to the string
'24353', how the + in the query (not the concatenation + in the exec) is
understood depends on the string @.b converts to. If @.b is converted to
the string '36459.9', the addition is performed using decimal
arithmetic. If @.b is converted to '36460', the query executed is
select 24353/36460, with 0 as the result. The types of @.a and @.b are
unknown to the query processor, which sees only the string
representations of these numbers.
While at first glance, you might think the select query here recognizes
that it is receiving floats, it is not. exec() can only receive a
string and must interpret the types of numbers from their string
representations. A safer approach is to use sp_executesql, with
parameters, or to add typecasts in your query string.
I hope that helps.
PAI wrote:
>Hi,
>Look at the simple set of SQL statements below: Execute the following on
>Query Analyzer.
>declare @.a float
>declare @.b float
>set @.a=24353.02821769137
>set @.b=36459.95
>exec('select '+@.a+'/'+@.b)
>Ans=.6679392
>Now, if I change the value of @.b to 36459.96,
>I get a value of 0. Integer division is performed just because the value of
>@.b increased by .01
>This looks like bug with the internal workings of SQL. This is very
>dangerous cause basic SQL assumption is that if a value is a float, the
>division will also be a float division. Integer division on the other hand
>gives erroneous results in this case.
>Please solve this problem as soon as possible.
>-Ling Yu.
>|||Thanks Steve. Your answer makes sense but the whole point of exec () now does
not. Why arbitrarily choose 6 precision. The disturbing this is even if you
convert it to a string and do an exec(@.sqlstring), you get the same problem.
For example:
declare @.a float
declare @.b float
set @.a=24353.02821769137
set @.b=36459.96
declare @.sql varchar(1000)
set @.sql='select '+cast(@.a as varchar(100))+'/'+cast(@.b as varchar(100))
exec('select '+@.a+'/'+@.b)
exec (@.sql)
Logically speaking when i cast 36459.96 to a varchar(100) it should retain
its decimal points and not round it. The only thing that helps is
exec('select cast('+@.a+' as float)/'+@.b)
This is so counterintuitive that its frustrating.
Thanks for your reply though
"Steve Kass" wrote:
> Ling Yu,
> This is not a bug, but it is surprising, and in my opinion, the query
> should actually cause a syntax error. I'm glad you raised the question,
> and I bet other people have been affected by this behavior.
> Here's why it occurs:
> The query processor must evaluate the query string 'select '+@.a+'/'+@.b
> as its first step. When strings are concatenated inside exec(), the +
> operator is not a T-SQL + operator. The + is applied by the front-end
> application and only after the entire query string is assembled does
> that string go to the query processor to be interpreted as T-SQL.
> Query Analyzer (or the system it uses to prepare queries for the engine)
> follows different rules than T-SQL to interpret the + that puts strings
> together within exec(). In T-SQL, the + operation would fail, because
> it tries to convert 'select ' and '/' into floats and add. But here, @.a
> and @.b are converted to strings. The way in which they are converted to
> strings is not obvious, but you can see it if you execute
> exec('select ' + @.a) and exec('select ' + @.b)
> What's important to realize is that exec() receives only a string,
> without any type information about the parameters. Unfortunately,
> floats are converted to strings by the front-end using at most 6 decimal
> digits of precision. Since @.a is converted in both cases to the string
> '24353', how the + in the query (not the concatenation + in the exec) is
> understood depends on the string @.b converts to. If @.b is converted to
> the string '36459.9', the addition is performed using decimal
> arithmetic. If @.b is converted to '36460', the query executed is
> select 24353/36460, with 0 as the result. The types of @.a and @.b are
> unknown to the query processor, which sees only the string
> representations of these numbers.
> While at first glance, you might think the select query here recognizes
> that it is receiving floats, it is not. exec() can only receive a
> string and must interpret the types of numbers from their string
> representations. A safer approach is to use sp_executesql, with
> parameters, or to add typecasts in your query string.
> I hope that helps.
>
> PAI wrote:
> >Hi,
> >
> >Look at the simple set of SQL statements below: Execute the following on
> >Query Analyzer.
> >
> >declare @.a float
> >declare @.b float
> >set @.a=24353.02821769137
> >set @.b=36459.95
> >exec('select '+@.a+'/'+@.b)
> >
> >Ans=.6679392
> >
> >Now, if I change the value of @.b to 36459.96,
> >I get a value of 0. Integer division is performed just because the value of
> >@.b increased by .01
> >
> >This looks like bug with the internal workings of SQL. This is very
> >dangerous cause basic SQL assumption is that if a value is a float, the
> >division will also be a float division. Integer division on the other hand
> >gives erroneous results in this case.
> >
> >Please solve this problem as soon as possible.
> >
> >-Ling Yu.
> >
> >
>|||Ling Yu,
While the default conversion from float to string is not very useful,
you can use CONVERT with a format code. Use convert(varchar(100),@.a,2),
and the resulting string will always be in scientific notation with 16
decimal places of accuracy. Then it will always be interpreted as a
float, and never with too-low precision.
SK
PAI wrote:
>Thanks Steve. Your answer makes sense but the whole point of exec () now does
>not. Why arbitrarily choose 6 precision. The disturbing this is even if you
>convert it to a string and do an exec(@.sqlstring), you get the same problem.
>For example:
>declare @.a float
>declare @.b float
>set @.a=24353.02821769137
>set @.b=36459.96
>declare @.sql varchar(1000)
>set @.sql='select '+cast(@.a as varchar(100))+'/'+cast(@.b as varchar(100))
>exec('select '+@.a+'/'+@.b)
>exec (@.sql)
>Logically speaking when i cast 36459.96 to a varchar(100) it should retain
>its decimal points and not round it. The only thing that helps is
>exec('select cast('+@.a+' as float)/'+@.b)
>This is so counterintuitive that its frustrating.
>Thanks for your reply though
>"Steve Kass" wrote:
>
>>Ling Yu,
>> This is not a bug, but it is surprising, and in my opinion, the query
>>should actually cause a syntax error. I'm glad you raised the question,
>>and I bet other people have been affected by this behavior.
>>Here's why it occurs:
>>The query processor must evaluate the query string 'select '+@.a+'/'+@.b
>>as its first step. When strings are concatenated inside exec(), the +
>>operator is not a T-SQL + operator. The + is applied by the front-end
>>application and only after the entire query string is assembled does
>>that string go to the query processor to be interpreted as T-SQL.
>>Query Analyzer (or the system it uses to prepare queries for the engine)
>>follows different rules than T-SQL to interpret the + that puts strings
>>together within exec(). In T-SQL, the + operation would fail, because
>>it tries to convert 'select ' and '/' into floats and add. But here, @.a
>>and @.b are converted to strings. The way in which they are converted to
>>strings is not obvious, but you can see it if you execute
>>exec('select ' + @.a) and exec('select ' + @.b)
>>What's important to realize is that exec() receives only a string,
>>without any type information about the parameters. Unfortunately,
>>floats are converted to strings by the front-end using at most 6 decimal
>>digits of precision. Since @.a is converted in both cases to the string
>>'24353', how the + in the query (not the concatenation + in the exec) is
>>understood depends on the string @.b converts to. If @.b is converted to
>>the string '36459.9', the addition is performed using decimal
>>arithmetic. If @.b is converted to '36460', the query executed is
>>select 24353/36460, with 0 as the result. The types of @.a and @.b are
>>unknown to the query processor, which sees only the string
>>representations of these numbers.
>>While at first glance, you might think the select query here recognizes
>>that it is receiving floats, it is not. exec() can only receive a
>>string and must interpret the types of numbers from their string
>>representations. A safer approach is to use sp_executesql, with
>>parameters, or to add typecasts in your query string.
>>I hope that helps.
>>
>>PAI wrote:
>>
>>Hi,
>>Look at the simple set of SQL statements below: Execute the following on
>>Query Analyzer.
>>declare @.a float
>>declare @.b float
>>set @.a=24353.02821769137
>>set @.b=36459.95
>>exec('select '+@.a+'/'+@.b)
>>Ans=.6679392
>>Now, if I change the value of @.b to 36459.96,
>>I get a value of 0. Integer division is performed just because the value of
>>@.b increased by .01
>>This looks like bug with the internal workings of SQL. This is very
>>dangerous cause basic SQL assumption is that if a value is a float, the
>>division will also be a float division. Integer division on the other hand
>>gives erroneous results in this case.
>>Please solve this problem as soon as possible.
>>-Ling Yu.
>>
>>
