1. Forum
  2. Usenet
  3. COMP.DATABASES.MS-SQLSERV

  • Strage difference between precision in literals

    From Anton Shepelev@21:1/5 to All on Sat Apr 23 19:04:12 2022
    Hello, all

    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

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

    --- SoupGate-Win32 v1.05
    * Origin: fsxNet Usenet Gateway (21:1/5)
  • From Erland Sommarskog@21:1/5 to Anton Shepelev on Mon Apr 25 21:58:30 2022
    Anton Shepelev (anton.txt@gmail.com) writes:
    Hello, all

    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!".

    I know how it feels, I've run into exactly this thing myself. And
    I thought, what!? Then I read the manual and learnt this:

    The retun type of power() is the type of the first argument. The 0.3
    has the type decimal(1, 1), and so the return type is numeric(1,1)
    and you get 0.3. Intuitive? Maybe not. But this is what the manual
    says.

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

    To avoid such surpises, say something like:

    SELECT cast(power(cast @val as float) as decimal(10,3))

    --- SoupGate-Win32 v1.05
    * Origin: fsxNet Usenet Gateway (21:1/5)
  • From Anton Shepelev@21:1/5 to All on Tue Apr 26 13:21:46 2022
    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]

    --- SoupGate-Win32 v1.05
    * Origin: fsxNet Usenet Gateway (21:1/5)
  • From Erland Sommarskog@21:1/5 to Anton Shepelev on Tue Apr 26 20:26:49 2022
    Anton Shepelev (anton.txt@g{oogle}mail.com) writes:
    I think your outer `CAST' is superfluous because it can-
    not increase actual precision.


    Yeah, but from a distance I couldn't tell whether you want a decimal
    or a float result, so I added the cast.

    --- SoupGate-Win32 v1.05
    * Origin: fsxNet Usenet Gateway (21:1/5)