Strange T-SQL Behavior

SQLServerCentral is supported by Red Gate Software Ltd.

Home Articles Editorials Forums Scripts Videos Blogs QotD Books Ask SSC SQL Jobs Training Authors About us Contact us Newsletters Advertise Write for us

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » T-SQL (SS2K8) » Strange T-SQL Behavior

44 posts, Page 1 of 5

1 2 3 4 5 »»»

Strange T-SQL Behavior

Rate Topic

Display Mode

Topic Options

Author

Message

Grant Fritchey

Posted Wednesday, May 20, 2009 11:13 AM

SSCertifiable

Group: General Forum Members
Last Login: Tuesday, November 17, 2009 8:32 PM
Points: 6,799, Visits: 13,363

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
The Scary DBA
Author of SQL Server 2008 Query Performance Tuning Distilled

For better & quicker help read:
How to Post Performance Problems

Post #720769

Lynn Pettis

Posted Wednesday, May 20, 2009 11:18 AM

SSCrazy Eights

Group: General Forum Members
Last Login: Today @ 6:49 PM
Points: 9,941, Visits: 9,819

That is interesting. I have no explanation.

Cool

Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)

Post #720771

Grant Fritchey

Posted Wednesday, May 20, 2009 11:21 AM

SSCertifiable

Group: General Forum Members
Last Login: Tuesday, November 17, 2009 8:32 PM
Points: 6,799, Visits: 13,363

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
The Scary DBA
Author of SQL Server 2008 Query Performance Tuning Distilled

For better & quicker help read:
How to Post Performance Problems

Post #720773

Lynn Pettis

Posted Wednesday, May 20, 2009 11:27 AM

SSCrazy Eights

Group: General Forum Members
Last Login: Today @ 6:49 PM
Points: 9,941, Visits: 9,819

Try this and look at the table created:

SELECT
    30.AString
    ,50.AnotherString
    ,10.SomethingElse
into dbo.TempTable;
select * from dbo.TempTable;

Lynn Pettis

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)

Post #720776

Florian Reischl

Posted Wednesday, May 20, 2009 11:36 AM

SSCommitted

Group: General Forum Members
Last Login: Today @ 1:18 PM
Points: 1,966, Visits: 3,359

rofl!!!

The more I learn, the more I know what I do not know

How to Post Data/Code to get the best Help "Numbers" or "Tally" Table
How to Post Performance Problems

Post #720780

Grant Fritchey

Posted Wednesday, May 20, 2009 11:38 AM

SSCertifiable

Group: General Forum Members
Last Login: Tuesday, November 17, 2009 8:32 PM
Points: 6,799, Visits: 13,363

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
The Scary DBA
Author of SQL Server 2008 Query Performance Tuning Distilled

For better & quicker help read:
How to Post Performance Problems

Post #720781

Grant Fritchey

Posted Wednesday, May 20, 2009 11:41 AM

SSCertifiable

Group: General Forum Members
Last Login: Tuesday, November 17, 2009 8:32 PM
Points: 6,799, Visits: 13,363

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
The Scary DBA
Author of SQL Server 2008 Query Performance Tuning Distilled

For better & quicker help read:
How to Post Performance Problems

Post #720784

Carl Federl

Posted Wednesday, May 20, 2009 11:41 AM

SSCommitted

Group: General Forum Members
Last Login: Yesterday @ 5:02 PM
Points: 1,942, Visits: 2,481

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

Post #720787

Florian Reischl

Posted Wednesday, May 20, 2009 11:42 AM

SSCommitted

Group: General Forum Members
Last Login: Today @ 1:18 PM
Points: 1,966, Visits: 3,359

This really seems to be an unwanted feature. A CONVERT also doesn't work:

SELECT
   CONVERT(INT, 1).Blah

The more I learn, the more I know what I do not know

How to Post Data/Code to get the best Help "Numbers" or "Tally" Table
How to Post Performance Problems

Post #720788

Florian Reischl

Posted Wednesday, May 20, 2009 11:45 AM

SSCommitted

Group: General Forum Members
Last Login: Today @ 1:18 PM
Points: 1,966, Visits: 3,359

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.

The more I learn, the more I know what I do not know

How to Post Data/Code to get the best Help "Numbers" or "Tally" Table
How to Post Performance Problems

Post #720790

« Prev Topic | Next Topic »

44 posts, Page 1 of 5

1 2 3 4 5 »»»

Permissions