Path: csiph.com!eternal-september.org!reader02.eternal-september.org!.POSTED!not-for-mail
From: Erland Sommarskog <esquel@sommarskog.se>
Newsgroups: comp.databases.ms-sqlserver
Subject: Re: Strage difference between precision in literals
Date: Mon, 25 Apr 2022 21:58:30 +0200
Organization: Erland Sommarskog
Lines: 33
Message-ID: <XnsAE84DF8AE8C27Yazorman@127.0.0.1>
References: <20220423190412.a205fc87d4859bba4556212f@gmail.com>
Mime-Version: 1.0
Content-Type: text/plain; charset=windows-1252
Content-Transfer-Encoding: 8bit
Injection-Info: reader02.eternal-september.org; posting-host="f2ff05d3720508fb7dd9e211a7c6ac1c"; logging-data="27692"; mail-complaints-to="abuse@eternal-september.org";	posting-account="U2FsdGVkX196ZW4z0z7cJRRX/Ahbai3D"
User-Agent: Xnews/2006.08.24 Mime-proxy/2.1.c.0 (Win32)
Cancel-Lock: sha1:WPSzeYwMFI584BsHRR86hG153sw=
Xref: csiph.com comp.databases.ms-sqlserver:2121

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))