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 ««12345»»»

TSQL Expand / Collapse
Author
Message
Posted Thursday, March 26, 2009 3:07 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, September 25, 2014 12:38 PM
Points: 1,385, Visits: 1,249
Guys, rather than repeatedly lamenting how terrible it is that you got the question "wrong" (as did I) even though it works in SQL 2000 / 2005, can anyone explain why it works???

if you run the following, then you get an error:

Select 1
union Select 2
union Select 3
union Select 4
union Select 5
union Select 6
union Select 7
UNION Select 'A'
union Select 'B'
union Select 'C'
union Select 'D'

Server: Msg 245, Level 16, State 1, Line 1
Syntax error converting the varchar value 'A' to a column of data type int.


That is normal / expected SQL Server behaviour. I believe the first result set in the UNION defines the data type that will be used for the rest of the UNION statement, but there is probably more information in the link attached to the question.

Now as soon as you add an INSERT (to a table with appropriate type) in SQL 2000, the error goes away:

Create Table Test(col varchar(10))
GO
Insert into Test
Select 1
union Select 2
union Select 3
union Select 4
union Select 5
union Select 6
union Select 7
UNION Select 'A'
union Select 'B'
union Select 'C'
union Select 'D'

(11 row(s) affected)


Is this because of some compiler optimization?? Logically, you would expect the UNION clauses to be evaluated first, any duplicates removed, and then afterwards convert the resulting single resultset into the necessary types for insert into the destination table - but somehow the destination type is being used during the evaluation of the UNION clauses.

Does anyone have details on how this happens?






http://poorsql.com for T-SQL formatting: free as in speech, free as in beer, free to run in SSMS or on your version control server - free however you want it.
Post #683913
Posted Thursday, March 26, 2009 3:13 AM
Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Saturday, March 29, 2014 11:41 AM
Points: 753, Visits: 133
Like all 2000 users, I voted for option 'C' and got it 'wrong'

Would it be possible for future QotD to be validated in 2000, 2005 & 2008 before submission.

As a relatively new boy on the block, with relatively meager points, I do feel relatively cheated of what I thought were 2 points in the bag.


Glen Parker
Post #683918
Posted Thursday, March 26, 2009 3:40 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Tuesday, February 16, 2010 10:43 PM
Points: 78, Visits: 84
Hi

The correct answer is C that is
1
2
3
4
5
6
I run this script in sql server 2005 and check it. Its not give an error. Its give an output.

Please check it.
Post #683935
Posted Thursday, March 26, 2009 3:43 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Tuesday, February 16, 2010 10:43 PM
Points: 78, Visits: 84
Hi,

Suppose if your query will be like this ie (Col='A') instead of (Col=1)

Select Col
From (Select Col
From Test
Where Isnumeric(Col)='A') X
Where Col Between 1 and 6

You can get the error that is syntax error converting the varchar value 'A' to a column of data type int.
Post #683938
Posted Thursday, March 26, 2009 3:50 AM
Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Monday, September 8, 2014 4:40 PM
Points: 682, Visits: 893
Are you sure Ashok? I ran the query with SQL 2005 and got the conversion error.

One thing to check is that it's the version of the server software that matters not the version of the client software.

In other words, running SSMS 2005 when the server is SQL 2000 means that you won't get the error.
Post #683940
Posted Thursday, March 26, 2009 4:11 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Tuesday, July 5, 2011 8:18 AM
Points: 582, Visits: 359
had a little tinker with this one...
ran script in 2005 to confirm that the question was correct for 2005.
Then ran the following

Create Table Test(col varchar(10))
GO
Insert into Test
Select 1
union Select 2
union Select 3
union Select 4
union Select 5
union Select 6
union Select 7


Insert into Test
Select 'A'
union Select 'B'
union Select 'C'
union Select 'D'

Select Col
From (Select Col
From Test
Where Isnumeric(Col)=1) X
Where Col Between 1 and 6

which returns vals 1 to 6...
but it also returns an error message

Msg 245, Level 16, State 1, Line 17
Conversion failed when converting the varchar value 'A' to data type int.

why d'you think that is?
Post #683946
Posted Thursday, March 26, 2009 4:17 AM
Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Monday, September 8, 2014 4:40 PM
Points: 682, Visits: 893
The crucial point with the conversion error can be demonstrated by trying these statements

SELECT 1 UNION SELECT 'A'

and

SELECT 'A' UNION SELECT 1

In both cases, you'll get

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


And you'll get the error whether it's SS 2000 or SS 2005.

The reason lies in the description of the UNION statement and the rules for data type precedence.

The following extracts are from SS2000 BOL but the wording in SS 2005 BOL is very similar.

For the UNION statement,

When different data types are combined in a UNION operation, they are converted using the rules of data type precedence.


For the rules of data type precedence,
When two expressions of different data types are combined by an operator, the data type precedence rules specify which data type is converted to the other. 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.

This is the precedence order for the Microsoft® SQL Server™ 2000 data types:

sql_variant (highest)
...
int
...
char
varbinary
binary (lowest)


So the order of the UNION's doesn't matter. It's the precedence of the data types that counts.

So we have an explanation for the error reported with SS 2005 (and 2008?).

But one puzzle remains.

Why doesn't the error occur when the query in the original question is run on SS 2000?
Post #683953
Posted Thursday, March 26, 2009 4:32 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Tuesday, December 16, 2014 8:26 AM
Points: 2,716, Visits: 2,484
gserdijn (3/26/2009)
When the SELECT statement is compiled for the INSERT, the first value is implicitly taken as an INT. In the UNIONs, as each SELECT is added, they must match this data type.


If that is the correct explanation, would the next statement work?
Insert into test Select 'A' union Select 1;

(Answer: No)





You're quite right. The data type isn't determined by the first value, it's determined by data type precedences

see here for SQL2008 http://msdn.microsoft.com/en-us/library/ms190309.aspx

here for SQL 2005 http://msdn.microsoft.com/en-us/library/ms190309(SQL.90).aspx


int beats varchar hands-down everytime!



Kev
Post #683963
Posted Thursday, March 26, 2009 4:34 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Tuesday, December 16, 2014 8:26 AM
Points: 2,716, Visits: 2,484
I took so long to submit that post that dmw beat me to it!



Kev
Post #683964
Posted Thursday, March 26, 2009 5:02 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: Friday, July 16, 2010 2:10 AM
Points: 863, Visits: 295
I agree that the version should be stated in the question.

This works fine in 2000 so long as you create the table defining the column as varchar. To get the correct answer you need to allow the first entry to define the data type

Select 1 col
into Test
union Select 2
union Select 3
union Select 4
union Select 5
union Select 6
union Select 7
union Select 'A'
union Select 'B'
union Select 'C'
union Select 'D'

will produce the desired result and have gotten us all the deserved points
Post #683976
« Prev Topic | Next Topic »

Add to briefcase ««12345»»»

Permissions Expand / Collapse