Return Type of COALESCE?

[Steffen Vulpius]

Hi everybody,

VARCHAR has a higher precedence than CHAR. If you have a query

SELECT COALESCE(c1,c2) FROM T1

where c1 is CHAR(5) and c2 is VARCHAR(10), I would expect the return
type to be varchar(10) similiar to UNION. However it turns out to
be CHAR(10). Does anyone know why that is so?

Thanks,
Steffen

[Thomas R. Hummel]

I'm not sure why you are seeing this behavior. According to BoL
COALESCE acts just like a CASE statement. According to the
documentation there the precedence order is supposed to be followed for
data type conversions. Yet, when I run the code below I get
contradictory results:

DECLARE @test INT, @char CHAR(10), @varchar VARCHAR(10)
SET @test = 1
SET @char = 'char'
SET @varchar = 'varchar'
SELECT 'x' + CASE @test WHEN 1 THEN @varchar ELSE @char END + 'x'

I ran this on a system with ANSI_PADDING set to ON and it had spaces
between the string and the final 'x'. If I make both arguments VARCHAR
the trailing spaces are not there.

When I tested this with an INT and DECIMAL I saw the results that I
would expect there - the result is always returned as a DECIMAL.

-Tom.

[--CELKO--]

COALESCE() is defined in terms of a CASE expression, so it should
follow the same rules about returning the highest data type in the
list. COALESCE correctly promotes its arguments to the highest data
type in the expression:

13 / COALESCE(CAST(NULL AS INTEGER), 2.00) = 6.5

The proprietary ISNULL() uses the first data type and gets things wrong

13 / ISNULL(CAST(NULL AS INTEGER), 2.00) = 6

You would need to write:

13 / ISNULL(CAST(NULL AS DECIMAL(4,2)), 2.00)

It sounds like the compiler is giving you an ISNULL().

[Steffen Vulpius]

--CELKO-- wrote:

COALESCE() is defined in terms of a CASE expression, so it should
follow the same rules about returning the highest data type in the
list. COALESCE correctly promotes its arguments to the highest data
type in the expression:

13 / COALESCE(CAST(NULL AS INTEGER), 2.00) = 6.5

The proprietary ISNULL() uses the first data type and gets things wrong

13 / ISNULL(CAST(NULL AS INTEGER), 2.00) = 6

You would need to write:

13 / ISNULL(CAST(NULL AS DECIMAL(4,2)), 2.00)

It sounds like the compiler is giving you an ISNULL().

I dont think so.
SELECT COALESCE(CAST (NULL AS CHAR(5)), CAST('a' as VARCHAR(10)) returns
'a ', a fixed length char.
SELECT COALESCE(CAST('a' as VARCHAR(10), CAST (NULL AS CHAR(5))) returns
the same. The order of the arguments doesnt change anything.
Only if all arguments are varchar, do I get varchar back.

Regards,
Steffen