weird concatenation problem
-
alright... riddle me this...
Check out this small block of code.
--------------------------------
selectcase when 1=1 then
replicate(' ',10)
else
ltrim('4444444444') + '6666666666'
end
+'x'
--------------------
What should happen? Well I would assume I would get this...
"..........x" (without the quotes of course, also please pretend the periods are spaces. For some reason spaces disapear when I post this message.)
but I get this...
".x"
now, if I make a small change in my code to this...
----------------------------
selectcase when 1=1 then
replicate(' ',10)
else
'4444444444' + '6666666666'
end
+'x'
----------------------------
I get the correct output of...
"..........x"
Any ideas? I'm stumped...
-
It is the whole implicit explicit thing getting you.
from BOL ltrim
quote:
Is an expression of character or binary data. character_expression can be a constant, variable, or column. character_expression must be of a data type that is implicitly convertible to varchar. Otherwise, use CAST to explicitly convert character_expression.See ltrim is causing an implicit conversion to varchar to occurr, replicate also reads
quote:
character_expression must be of a data type that is implicitly convertible to varchar. Otherwise, use the CAST function to convert explicitly character_expression.However there are some iteresting things you can do such as
cast(replicate(' ',10) as varchar(10))
and it will be fine. Again the problem is implciti versus explicit typing that is the root of this. Apparently ltrim is triggering replicate to implcitly type as varchar(1) instead of the typing based on the length field and boom. I would send to MS via email just to get their imput but that is where I am sure they will go. I think of it as a bug but ltrims stripping method may be why replicate was quashed when parsed and implcit conversion since 1 is the smallest varchar.
-
Nice example of a bug, I'd say... I have no explanation for it, but I tested a bit and found out that if you
- replace replicate(' ',10) with '(10 spaces)', or
- replace LTRIM with non-string function (e.g. MAX)
- use SUBSTRING instead of LTRIM
- use LTRIM with the second string rather than with the first
result is correct.
Only if certain functions are used on the first string in ELSE (I tried LTRIM, RTRIM, LEFT, RIGHT), you get the strange result. Especially the fact that LEFT and SUBSTRING behaves differently is quite interesting.
-
Antares,
thanks, that brings some light into the problem, but anyway - LTRIM is in the ELSE part, so while you have WHEN 1=1, everything in ELSE should be skipped... or am I wrong? I always thought that as soon as first condition in CASE is evaluated as TRUE, the rest of CASE is dropped without doing anything with it. Apparently that part still can have influence on how the select is processed...
-
quote:
Antares,thanks, that brings some light into the problem, but anyway - LTRIM is in the ELSE part, so while you have WHEN 1=1, everything in ELSE should be skipped... or am I wrong? I always thought that as soon as first condition in CASE is evaluated as TRUE, the rest of CASE is dropped without doing anything with it. Apparently that part still can have influence on how the select is processed...
Try this and you will see otherwise. (note SQL 2000)
select case when 1=1 then
'A'
else
6
end
the result will be the following error
Syntax error converting the varchar value 'A' to a column of data type int.
the reason is no matter which path thru a case code will take it has to implicily decide the output datatype. If there is no built in implicit and the datatypes would not result in the same datatype you get the error (note: I believe the above will work in SQL 7 but will not in 2000 becuase the implicit rules changed).
However do
select case when 1=1 then
'A'
else
cast(6 as char(1))
end
will work becuase I type to a common type explcitily. Under the hood the 'A' was still type to char(1) as by default the datatype is varchar in memory for strings.
So no the types have to evaluate to equal at parse time or they be implicitly typed the same which is what happens in your code example. But even so the issue is a bug since the implicit type obviously causes the results to be errored.
-
Antares,
You're right, I missed this point... It really is interesting, because simple switching of the two values makes the query run OK (I have SQL2000, too):
select
case
when 1=1 then 6
else 'A'
end
Result: 6 !
Looks like the datatype was decided based on what is in ELSE... which is similar to what we have seen in the original example.
-
quote:
Antares,You're right, I missed this point... It really is interesting, because simple switching of the two values makes the query run OK (I have SQL2000, too):
select
case
when 1=1 then 6
else 'A'
end
Result: 6 !
Looks like the datatype was decided based on what is in ELSE... which is similar to what we have seen in the original example.
I didn't even try the other way so that shows definently that it is typing based on the ELSE then.
Except try the example at the begining
select case when 1=2 then
ltrim('4444444444') + '6666666666'
else
replicate(' ',10)
end
+'x'
just change 1 = 2 instead and it doesn't do as is expected, so something in the implicit typing is causing an issue here.
And even more odd is either way if you explicitly do a cast around the replicate like so
select case when 1=2 then
ltrim('4444444444') + '6666666666'
else
cast(replicate(' ',10) as char(10))
end
+'x'
you get 20 blank spaces (either way) unless you cast both explicitly like so
select case when 1=2 then
cast(ltrim('4444444444') + '6666666666' as char(10))
else
cast(replicate(' ',10) as char(10))
end
+'x'
but then that shorts you 10 positions. But add a character to the front of the replicate like so
select case when 1=2 then
ltrim('4444444444') + '6666666666'
else
'a' + replicate(' ',10)
end
+'x'
and you get 10 spaces between a and x. Change space to 'a' and do like in the first example and is right.
Finally CAST just the LTRIM('4444444444') to char(10) and the result is right. So somewhere the ltrim is being interpreted into the mix for typing.
Is for a sure a bug in my book but MS should see this and see if they have a better suggestion or they give mine and say is a feature (good ol MS).
Edited by - antares686 on 09/16/2003 12:21:45 PM
-
Thanks for checking this out. At least I know I'm not crazy.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply