4
-- Transact SQL:  case when 1=1 then 0.5 else ceiling(sh) end   /* returns 1  (!) why? */
declare @T table (h decimal(2,1)) 
insert @T (h) values (1.0)
select 
case when 1=1 then 0.5 else ceiling(sh) end   /* returns 1  (!) why? */
from @T T1
join (select sum(h) as sh from @T )T2  on 1 = 1
Mureinik
  • 297,002
  • 52
  • 306
  • 350
Petr Kral
  • 43
  • 3

3 Answers3

3

The answer is nothing to do with the int datatype

  • The literal 0.5 has a dataype of decimal(1,1)
  • Applying CEILING on a decimal(p,s) returns a result of type decimal(p,0)
  • Applying SUM on a decimal(p,s) returns a result of type decimal(38,s)
  • With a mixed CASE expression that can return decimal(p1,s1) or decimal(p2,s2) the result will use the same rules as when UNION-ing these data types and have precision (*) of max(s1, s2) + max(p1-s1, p2-s2) and scale of max(s1, s2)

* The result precision and scale have an absolute maximum of 38. When a result precision is greater than 38, it is reduced to 38, and the corresponding scale is reduced to try to prevent the integral part of a result from being truncated. (source)

So your column h has datatype of decimal(2,1), the datatype when SUM is applied is decimal(38,1), the datatype of CEILING applied to that is decimal(38,0). Then you use that in a CASE expression with decimal(1,1)

max(s1, s2) + max(p1-s1, p2-s2)
max( 0,  1) + max(   38,     0) = 1 + 38 = 39

And 

max(s1, s2) = max(0, 1) = 1

So the desired result datatype would be decimal(39,1). This is larger than 38 so you get the scale reduction described above and end up with decimal(38,0) - 0.5 is rounded to 1 when cast to that datatype.

If you would rather keep the precision of the final result you can use

case when 1=1 then 0.5 else CAST(ceiling(sh) AS decimal(38,1)) end

There is a miniscule additional risk of overflow with this but to be hit by it the sum would need to add up to one of the following values

  • 9999999999999999999999999999999999999.5
  • 9999999999999999999999999999999999999.6
  • 9999999999999999999999999999999999999.7
  • 9999999999999999999999999999999999999.8
  • 9999999999999999999999999999999999999.9

such that the SUM itself fits into 38,1 but CEILING doesn't.

Martin Smith
  • 438,706
  • 87
  • 741
  • 845
0

More explanation:

select CEILING(170.00/6.00),CEILING(170/6),170.00/6.00,170/6

Result:

29  28  28.333333   28
colo
  • 185
  • 1
  • 1
  • 10
-3

As explain in the documentation

When an operator combines two expressions of different data types, the rules for data type precedence specify that the data type with the lower precedence is converted to the data type with the higher precedence. If the conversion is not a supported implicit conversion, an error is returned. When both operand expressions have the same data type, the result of the operation has that data type.

In your case is converted to int because has the bigger precedence.

sql demo

declare @T table (h decimal(2,1)) 
insert @T (h) values (1.0)
select 
case when 1=1 then 0.5 else ceiling(sh)*1.0 end  -- << convert to float
from @T T1
join (select sum(h) as sh from @T )T2  on 1 = 1
Juan Carlos Oropeza
  • 47,252
  • 12
  • 78
  • 118
  • 1
    two downvotes? Maybe because you did not explain why it worked? – Hogan Nov 30 '18 at 15:52
  • Not sure, but it might have to do something with the `float`. A proper solution would not leave the `decimal` domain. – GSerg Nov 30 '18 at 15:52
  • @GSerg So maybe use cast on both statement to make sure has the same type? Just wondering how you can know what type is 0.5 converted in this case? I cant tell in my demo – Juan Carlos Oropeza Nov 30 '18 at 16:03
  • 1
    No, it is converted to `decimal(38,0)` because [this is what `ceiling` returns](https://stackoverflow.com/questions/53560374/transact-sql-case-when-1-1-then-0-5-else-ceilingsh-end-returns-1-why#comment93987029_53560374). You are supposed to cast only the `ceiling` part back to a `decimal` with a decimal position. – GSerg Nov 30 '18 at 16:03
  • @GSerg According to [documentation](https://learn.microsoft.com/en-us/sql/t-sql/functions/ceiling-transact-sql?view=sql-server-2017) ceiling `Return values have the same type as numeric_expression.` how you know is `decimal(38,0)` ? – Juan Carlos Oropeza Nov 30 '18 at 16:05
  • 4
    I know because Martin Smith [said so](https://stackoverflow.com/questions/53560374/transact-sql-case-when-1-1-then-0-5-else-ceilingsh-end-returns-1-why#comment93987029_53560374) and because `exec sp_describe_first_result_set N'select ceiling(sum(h)) from (values (cast(0.5 as decimal(2,1)))) T1(h)';` confirms it. Ceiling keeps the base type (numeric) but changes the precision. First `sum()` bumps it up to `(38,1)`, then `ceiling` rounds it down to `(38,0)`. – GSerg Nov 30 '18 at 16:07
  • @GSerg ok, didnt knew about `exec sp_describe_first_result_set` still not sure why ceiling remove the precision when the documentation said will keep the same data type. Or why people keep downvoting :/ – Juan Carlos Oropeza Nov 30 '18 at 16:12
  • 2
    After replacing ceiling(sh) by ceiling(h), the result is ok, 0.5. The question is, why ceiling(sh) works not equally to ceiling(h) – Petr Kral Nov 30 '18 at 16:16
  • Ok, let me dig a litle big. But you ask why return 1 and I already answer that isnt? – Juan Carlos Oropeza Nov 30 '18 at 16:22
  • Are you sure? `ceiling(h)` is also 1 – Juan Carlos Oropeza Nov 30 '18 at 16:26
  • 3
    @PetrKral Because `ceiling(h)` gives `decimal(2,0)`, and between `(2,0)` and `(2,1)` the resulting type is `(3,1)`. On contrary, `sum()` pushes the size up to the maximum `(38)`, there cannot be `(39,1)`, so it ends up being `(38,0)`. – GSerg Nov 30 '18 at 16:48