Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]

Groups > comp.databases.ms-sqlserver > #2122

Re: Strage difference between precision in literals

Show all headers | View raw

Erland Sommarskog:

> Anton Sheplev:
>
> > Can you please tell me why this query:
> >
> >    SELECT     POWER(0.5, 2), POWER(0.50, 2)
> >    Returns    0.3            0.25
> >    Instead of 0.25           0.25
>
> All  say after me: "SQL Server is designed for maximum
> confusion!".

Indeed, and after your epic  article  about  error  han-
dling.

> I  know how it feels, I've run into exactly this thing
> myself.  And I thought, what!?  Then I read the  manu-
> al ->

I  was  misled by the remark phrase in the documentation
that the first artument "is an expression of type  float
or of a type that can be implicitly converted to float."
So I thought that since

      CAST( 0.5 AS FLOAT) = CAST( 0.50 AS FLOAT )

the should be no difference between 0.5 and 0.50.

> -> and learnt this:
>
> The retun type of power() is the type of the first ar-
> gument. The 0.3 has the type decimal(1, 1), and so the
> return type is numeric(1,1) and you  get  0.3.   Intu-
> itive? Maybe not.  But this is what the manual says.

Thank you, Erland.  That explains it.

> By the way, this is how you can tell the type of a nu-
> meric literal:
> DECLARE @s sql_variant = 0.3
> SELECT sql_variant_property(@s, 'Basetype'),
>        sql_variant_property(@s, 'Precision'),
>        sql_variant_property(@s, 'Scale')

Yes, quite useful in debugging implicit  and/or  unintu-
itive type conversions.

> To avoid such surpises, say something like:
>   SELECT cast(power(cast @val as float) as decimal(10,3))

That is what I did, albeit a bit plainer:

   DECLARE @C_TWO FLOAT = 2 -- float constatnt two!
   --- ...
   POWER( @C_TWO, @lambda )

I think your outer `CAST' is superfluous because it can-
not increase actual precision.

-- 
()  ascii ribbon campaign - against html e-mail
/\  http://preview.tinyurl.com/qcy6mjc [archived]

Back to comp.databases.ms-sqlserver | Previous | Next — Previous in thread | Next in thread | Find similar

Thread

Strage difference between precision in literals Anton Shepelev <anton.txt@gmail.com> - 2022-04-23 19:04 +0300
  Re: Strage difference between precision in literals Erland Sommarskog <esquel@sommarskog.se> - 2022-04-25 21:58 +0200
    Re: Strage difference between precision in literals Anton Shepelev <anton.txt@g{oogle}mail.com> - 2022-04-26 13:21 +0300
      Re: Strage difference between precision in literals Erland Sommarskog <esquel@sommarskog.se> - 2022-04-26 20:26 +0200

csiph-web