Odd TSQL Behavior

Home / SQL Server 2005 / Odd TSQL Behavior

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:

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

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:

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:

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.

5 Comments

  • 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

  • scarydba

    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.

  • Odd SQL

    Why is this ?
    select 1.e10 returns “10000000000”
    select LEN(1.e10) returns “6”
    Why “6”? Can someone throw some light !!!

  • Joben87

    This is fantastically strange. I love coming across syntax like this! Just so I know I can relate to the developers code language across the realms of .net and SQL!

OK, fine, but what do you think?