What will be output?

Question

What will be output?

Sunil Chandurkar

Right there with Babe

Points: 791
More actions
March 24, 2010 at 9:02 pm

#222340

Comments posted to this topic are about the item What will be output?

Viewing 15 posts - 1 through 15 (of 34 total)

You must be logged in to reply to this topic. Login to reply

SQLRNNR SSC Guru Points: 281334 More actions · Answer 1

Thanks for the Question.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

ziangij SSCrazy Eights Points: 8066 More actions · Answer 2

ziangij

SSCrazy Eights

Points: 8066

March 24, 2010 at 10:39 pm

#1139395

thanks :-)... its a good interview question.

SRatnaparkhi SSC-Addicted Points: 474 More actions · Answer 3

Thanks good question :-). For info rmation if we write question like this

declare @a int

declare @b-2 varchar(2)

set @a = 10

set @b-2 = ' '+@a +' '+ 2+' '

select @a +' '+ @b-2

then also output will be same.

Hugo Kornelis SSC Guru Points: 64790 More actions · Answer 4

Good question, but unfortunately the explanation is not entirely correct. Especially this part is very misleading:

Also the preceding space is not considered while performing the operation.

Let's look in detail what happens to [font="Courier New"]set @b-2 = ' '+@a + 2[/font]. Since all operators are the same, evaluation order is left to right. So the first partial expression to evaluate is [font="Courier New"]' '+@a[/font]. This mixes two datatypes: ' ' is varchar(1), and @a is int. Order of precedence dictates conversion of varchar to int, and ' ' is converted to the value 0 (run [font="Courier New"]SELECT CAST(' ' AS int);[/font] if you don't believe me). Since @a is set to 10, the result of this partial expression is 0 + 10, or 10; still types as int.

The next step is to evaluate [font="Courier New"](' '+@a) + 2[/font] (parentheses added to emphasize evaluation order). We have already seen that [font="Courier New"](' '+@a)[/font] is equal to the integer value 10, so this is now reduced to [font="Courier New"]10 + 2[/font].

As you have seen, there is no leading space to be considered anywhere. However, even if there was, the words "is not considered" is misleading. I think we would all agree that converting the values '5', ' 5' and ' 5 ' to integer should all result in the value 5 - not because we expect the leading and trailing spaces to be "not considered", but because considering them or not is irrelevant, as they have no impact on the interpretation of the string as a number.

Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
Visit my SQL Server blog: https://sqlserverfast.com/blog/
SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/

Hugo Kornelis SSC Guru Points: 64790 More actions · Answer 5

ziangij (3/24/2010)
thanks :-)... its a good interview question.

I would not be too happy with these things being asked in an interview. My answer would be that mixing data types like this and taking a dependency hit on implied evaluation order and implicit conversion rules should not be done in production code.

Code like this would get a red mark from me if I caught it in code review.

Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
Visit my SQL Server blog: https://sqlserverfast.com/blog/
SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/

Bob JH Cullen SSCrazy Points: 2082 More actions · Answer 6

I would certainly agree with Hugo - this just smells like bad practice if it ends up in production code. OK, the question is fine as an academic exercise, but just because you can do something like this doesn't mean you should, IMHO.

btw - I got it wrong - probably because my style of coding doesn't give me enough practice at this kind of dubious T-SQL 😉

vk-kirov SSCertifiable Points: 7686 More actions · Answer 7

Hugo Kornelis (3/25/2010)
' ' is converted to the value 0 (run [font="Courier New"]SELECT CAST(' ' AS int);[/font] if you don't believe me).

This is very interesting because [font="Courier New"]ISNUMERIC(' ')[/font] returns 0.

On the one hand, SQL Server doesn't consider whitespaces as numeric values; on the other hand, it converts whitespaces to zero integer values. This is a little bit strange 🙂

ganeshanmsc SSC Veteran Points: 226 More actions · Answer 8

ganeshanmsc

SSC Veteran

Points: 226

March 25, 2010 at 4:37 am

#1139493

The answer is 22

Sunil Chandurkar Right there with Babe Points: 791 More actions · Answer 9

Thanks Hugo! your explaination is very helpful. also thanks for pointing out the misleading pharse. in future i can now be more careful for writing any stuff on SSC

Henry_Lee SSCrazy Points: 2714 More actions · Answer 10

thanks ... its a good interview question

IMHO the only way this a good interview question is if the answer you're looking for is "I'm not sure of the correct answer because I would not rely on this implicit conversion. If I'm performing calculations I would explicitly cast / convert to the appropriate datatype."

Of course, I could just be bummed I got the question wrong 🙁

ronmoses@gmail.com SSCarpal Tunnel Points: 4480 More actions · Answer 11

Hugo, your explanation is excellent, thank you. I wouldn't have thought it was any of those answers; my first guess was 112. I forgot about type precedence in the implicit conversion and simply read left to right, so I figured ' ' + @a would convert to ' 10' and then adding the 2 would result in ' 102'. So I've got type precedence pretty well hammered into my head now, thanks!

Of course that's the secondary lesson. The primary lesson is never to allow code like this in your database.

-----
a haiku...

NULL is not zero
NULL is not an empty string
NULL is the unknown

Daniel Bowlin SSC-Dedicated Points: 34566 More actions · Answer 12

Interesting question and follow up discussion. Hugo thanks for the additional explanation. I find it very interesting that ' ' is converted to a zero during a confused typing operation such as this.

Mike Dougherty Ten Centuries Points: 1112 More actions · Answer 13

Good question for challenging what we know to be right with an edge case.

I second the opinion that code like this found in production warrants a red-card. Unfortunately we usually only find this stuff when whoever wrote it has left the company.

I also got this question wrong. I assumed it was a trick. Had I just done the obvious math I would have selected the correct answer.

webrunner SSC-Dedicated Points: 31862 More actions · Answer 14

Great question. I got it wrong because I didn't know about the implicit conversion of string values containing only numbers.

For example, this code throws the error I expected:

declare @a int

declare @b-2 varchar(2)

set @a = 10

set @b-2 = ' a'+@a + 2

select @a + @b-2

Messages:

Server: Msg 245, Level 16, State 1, Line 5

Syntax error converting the varchar value ' a' to a column of data type int.

While answering the question, I thought only of those cases (probably the vast majority) where I had used code like the code above (for concatenation in printing test values only, not for adding numbers), and in variably it included at least one non-numeric character. So I was biased to think that such code would always throw an error.

This question is a nice corrective for my ignorance in that regard.

Thanks,

webrunner

-------------------
A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html