Odd TSQL Behavior

May 20, 2009 at 2:18 pm (SQL Server 2005, SQL Server 2008, SQLServerPedia Syndication, TSQL) (, )


Before I describe this, let me thank Lynn Pettis over at SQL Server Central for coming up with the answer.

This morning a developer walked up and asked me what would happen if he ran a query that looked like this:

SELECT 1.SomeString

I said that he’d get an error. No, he says, try it. So I try it and I get this:

Somestring
1

Try it yourself. It works just fine. I’d never seen that before and didn’t have a clue what it was. Thinking that Microsoft had supplied some new short hand to define aliases I ran this:

SELECT 'dude'.dudette

Which resulted in the error:

 

Msg 258, Level 15, State 1, Line 1
Cannot call methods on varchar.

 

Which is what I would have expected. I tried a couple of functions and some other bits & pieces of data, but only integers seemed to work… Or was it? What would be the purpose of a function that defines aliases for an integer?

I finally posted a question up on SQL Server Central. One of the local brain trust over there, Lynn Pettis, chewed on it for a bit and identified the issue. SQL Server is “helping” you by assuming that you meant to put a space between the period (dot or . ) and the string, Somestring, as an alias. I then tried this:

SELECT 'dude'dudette

And was rewarded with the same behavior.

Personally, in this case, I don’t see this as TSQL “helping” me nearly so much as it confused the heck out of me. Thanks again for figuring it out Lynn.

2 Comments

  1. Michael Swart said,

    I took a course in University on compilers and I sympathize with the developers. It’s a tricky problem. Whitespace is a convenient way to split up tokens, but it’s not always necessary.

    I think a lot of this would be a lot less confusing if decimal constants required at least one digit after the decimal. I mean, if you’re going to use a decimal, then use it! But it’s too late now. I’m sure someone somewhere is depending on this functionality.

    Also interesting. Now that you have a firm grasp on this mess, try to predict the results of the following before plugging it into SQL Server:
    select 1.e1e2

  2. scarydba said,

    Without running it… e2 since the rest is math. That came up on the discussions over on SSC…

    Yep. Worked. Strange stuff. You could really mess with people’s heads a bit with this.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: