May 20, 2009 at 11:13 am
One of the developers came up and asked me what happens if I run this query:
SELECT 1.ColumnName
I said that you'd probably get a syntax error. But when I ran it, I got this as a result:
ColumnName
1
That was strange, so I tried this:
SELECT 1.AString
,2.AnotherString
,3.SomethingElse
and I got this:
AString AnotherString SomethingElse
1 2 3
I then tried it with characters & functions, which all errored out. The only thing I got to work were integers. What the heck is this? Has anyone seen this behavior or have any idea what's happening?
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
May 20, 2009 at 11:18 am
That is interesting. I have no explanation.
May 20, 2009 at 11:21 am
Weird isn't it? I've searched through the syntax in BOL and several books as well as a Google search. I can't find anything that references this type of syntax. I'm assuming that it's attempting to leverage... something, CLR, UDT... I'm not sure.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
May 20, 2009 at 11:27 am
Try this and look at the table created:
SELECT
30.AString
,50.AnotherString
,10.SomethingElse
into dbo.TempTable;
select * from dbo.TempTable;
May 20, 2009 at 11:36 am
rofl!!!
May 20, 2009 at 11:38 am
Yeah, based on the behavior so far, that's about what I would expect. It's like some kind of alias short hand, but it doesn't work for strings. If you try this:
SELECT 'myvalue'.MyColumn
You just get a syntax error, like I expected on the other query.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
May 20, 2009 at 11:41 am
Florian Reischl (5/20/2009)
rofl!!!
Hey! Don't laugh at me. Just tell me what it is.
I'm starting to feel like Earl in Tremors:
"Hell Earl, everyone knows all about these things. We just never told you."
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
May 20, 2009 at 11:41 am
Possibly a SQL Parser problem along with an implicit datatype conversion
SELECT 1.1Number11
Which has results with a column name of "Number11" having a numeric value of "1.1"
Looks like the parser is "seeing" this string as number constant followed by a column alias even when there is no whitespace in the string.
SQL = Scarcely Qualifies as a Language
May 20, 2009 at 11:42 am
This really seems to be an unwanted feature. A CONVERT also doesn't work:
SELECT
CONVERT(INT, 1).Blah
May 20, 2009 at 11:45 am
Grant Fritchey (5/20/2009)
Florian Reischl (5/20/2009)
rofl!!!Hey! Don't laugh at me. Just tell me what it is.
I'm starting to feel like Earl in Tremors:
"Hell Earl, everyone knows all about these things. We just never told you."
Sorry Grant, I really didn't laugh at you. I just laughed at the fact that this works. I have no idea why. Just tried on SSE2k5, works.
May 20, 2009 at 11:48 am
Florian Reischl (5/20/2009)
Grant Fritchey (5/20/2009)
Florian Reischl (5/20/2009)
rofl!!!Hey! Don't laugh at me. Just tell me what it is.
I'm starting to feel like Earl in Tremors:
"Hell Earl, everyone knows all about these things. We just never told you."
Sorry Grant, I really didn't laugh at you. I just laughed at the fact that this works. I have no idea why. Just tried on SSE2k5, works.
No, no, I know you weren't laughing at me. Although, at this point, strange as this is, I was hoping it was something silly that I should have known.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
May 20, 2009 at 11:50 am
YEA!!!! I Figured it out with a little help from a co-worker!!!!!
May 20, 2009 at 11:50 am
I just did several searches on Connect. I don't see this listed anywhere. Weird stuff. I think I'm going to post it to another newsgroup to see if others have seen this.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
May 20, 2009 at 11:51 am
OK. Spill.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
May 20, 2009 at 11:53 am
Now, for those of you waiting impatiently for the explaination, here it is.
SELECT 1.Acolumn is equivalent to SELECT 1. Acolumn. (there is a space between the . (dot) and Acolumn)
SQL Server takes the numeric value up to the first Alpha character as a numeric value (SELECT 1.) and the alpha characters that follow are then the alias for the column.
Viewing 15 posts - 1 through 15 (of 43 total)
You must be logged in to reply to this topic. Login to reply