Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««1234»»»

What will be output? Expand / Collapse
Author
Message
Posted Thursday, March 25, 2010 5:42 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Friday, August 15, 2014 12:54 PM
Points: 320, Visits: 1,480
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
Post #889682
Posted Thursday, March 25, 2010 6:16 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Yesterday @ 8:24 AM
Points: 854, Visits: 860
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
Post #889708
Posted Thursday, March 25, 2010 7:31 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 8:39 AM
Points: 2,818, Visits: 2,558
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.
Post #889801
Posted Thursday, March 25, 2010 8:07 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, July 22, 2010 8:59 AM
Points: 110, Visits: 952
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.
Post #889839
Posted Thursday, March 25, 2010 9:00 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 3:12 PM
Points: 2,351, Visits: 2,696
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 varchar(2)

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

select @a + @b

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


-------------------
"Operator! Give me the number for 911!" - Homer Simpson

"A SQL query walks into a bar and sees two tables. He walks up to them and says 'Can I join you?'"
Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html
Post #889882
Posted Thursday, March 25, 2010 9:05 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Thursday, July 10, 2014 12:49 PM
Points: 1,676, Visits: 1,754
Thank you for an excellent explanation Hugo. The code like this should not be present anywhere near production of course, but the question is definitely a good one. One of the interesting side effects of implicit int to varchar conversions is that if the int value does not fit then varchar is set to *. For example, in the original example, if @a is set to 100 instead of 10 then it cannot fit into @b and this will cause @b = *, which can be somewhat confusing for someone who is unaware of this side effect. For example:

declare @a int;
declare @b varchar(2);

set @a = 100;
set @b = ' ' + @a + 2;

select @b;

The result is

----
*

This behavior is specific to int to varchar conversions only, other types such as smallint or bigint will cause the code to raise error.

Oleg
Post #889892
Posted Thursday, March 25, 2010 10:00 AM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: 2 days ago @ 11:27 PM
Points: 3,901, Visits: 3,634
So given the following:

 
declare @a int
declare @b varchar(2)

set @a = 10
set @b = ' '
IF ISNUMERIC(@b) = 1
select @b +@a + 2;

ELSE Print 'Unknown'

-- Even though the following is the result
select @b +@a + 2;


The result is 'Unknown' after which the actual result, 12.
Post #889954
Posted Thursday, March 25, 2010 10:38 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Friday, July 22, 2011 4:13 AM
Points: 1,149, Visits: 603
As per Cliff Jones
The answer should be 12 not 22
(I see I missed the Set followed by Select
set @b = ' '+@a + 2
select @a + @b)

And as per Oleg Netchaev
With SSMS 2008 I got an * but not with SSMS 2005
Post #889994
Posted Thursday, March 25, 2010 10:50 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Yesterday @ 7:45 PM
Points: 1,521, Visits: 3,037
Hugo Kornelis (3/25/2010)
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.

. . . .
Order of precedence dictates conversion of varchar to int, and ' ' is converted to the value 0
. . . .

Thanks for the detailed explanation, Hugo. You beat me to it in pointing out that spaces (or an empty string) cast to numeric as value zero. A slight twist on the QOD code would dramatically illustrate that the leading spaces are not at all ignored:
declare @a int
declare @b varchar(2)
set @a = 10
set @b = ' '+@a + 2
select @a + @b as Original
set @b = ' '*@a + 2 --Multiply instead
select @a + @b as Revised

Results are:
Original
-----------
22

(1 row(s) affected)

Revised
-----------
12

(1 row(s) affected)
Post #890016
Posted Thursday, March 25, 2010 11:01 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 3:27 PM
Points: 21,631, Visits: 15,289
john.arnott (3/25/2010)
Hugo Kornelis (3/25/2010)
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.

. . . .
Order of precedence dictates conversion of varchar to int, and ' ' is converted to the value 0
. . . .

Thanks for the detailed explanation, Hugo. You beat me to it in pointing out that spaces (or an empty string) cast to numeric as value zero. A slight twist on the QOD code would dramatically illustrate that the leading spaces are not at all ignored:
declare @a int
declare @b varchar(2)
set @a = 10
set @b = ' '+@a + 2
select @a + @b as Original
set @b = ' '*@a + 2 --Multiply instead
select @a + @b as Revised

Results are:
Original
-----------
22

(1 row(s) affected)

Revised
-----------
12

(1 row(s) affected)


Another fine explanation of the conversion.




Jason AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server


SQL RNNR

Posting Performance Based Questions - Gail Shaw
Posting Data Etiquette - Jeff Moden
Hidden RBAR - Jeff Moden
VLFs and the Tran Log - Kimberly Tripp
Post #890028
« Prev Topic | Next Topic »

Add to briefcase ««1234»»»

Permissions Expand / Collapse