Space missing in SELECT statement

  • Good trick question. Monday morning, brain wasn't working yet.


    Thanks,

    ToddR

  • +1

    ---------------
    Mel. 😎

  • Wow! Didn't see that coming. Good Question.

  • I admit it, I got caught by the "fromTest" 😀

    This is yet another example of how badly designed SQL syntax really is, from an ambiguity standpoint. If the "AS" keyword was required this trap wouldn't exist. (laughing)

  • paul.knibbs (10/21/2013)


    Seems I get caught out by this alias behaviour every time they ask a question about it... :pinch:

    +1

    Great question!

    Kenneth FisherI was once offered a wizards hat but it got in the way of my dunce cap.--------------------------------------------------------------------------------For better, quicker answers on T-SQL questions, click on the following... http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]For better answers on performance questions, click on the following... http://www.sqlservercentral.com/articles/SQLServerCentral/66909/[/url]Link to my Blog Post --> www.SQLStudies.com[/url]

  • Nice question. I think it's a more interesting question if you try this select:

    SELECT 1

    FROM TEST

    or even this select:

    SELECT [1]

    FROM TEST

    [font="Verdana"]Please don't go. The drones need you. They look up to you.[/font]
    Connect to me on LinkedIn

  • You got me on that one! These questions are kinda fun once in a while. Maybe it should have been saved for April 1.

    Tony
    ------------------------------------
    Are you suggesting coconuts migrate?

  • Hah hah hah! Neat! Thanks, Ashish!

  • crussell-931424 (10/21/2013)


    I can't win on these. If you read it exactly as it is written then people complain that we should have understood what the question was really asking in spite of the "accidental" typo. So I figure this must just be another "accidental" type and I correct it, separating the "from" from the the "TEST." But no, this time the question is attempting to trick us. If it wasn't then why use fromTEST as the alias? No one would ever use that intentionally. So I guess I don't know what the lesson is here. If it is to identify errors in code, then I passed. I found the error and corrected it. If it is to pretend that someone would actually name an alias "fromTEST" and want to know the results, then I would say, don't waste my time.

    Here the lesson is about handling of alias in sql server.

  • Revenant (10/21/2013)


    Hah hah hah! Neat! Thanks, Ashish!

    Welcome.

  • Fun question. Thanks Ashish.

  • crussell-931424 (10/21/2013)


    I can't win on these. If you read it exactly as it is written then people complain that we should have understood what the question was really asking in spite of the "accidental" typo. So I figure this must just be another "accidental" type and I correct it, separating the "from" from the the "TEST." But no, this time the question is attempting to trick us. If it wasn't then why use fromTEST as the alias? No one would ever use that intentionally. So I guess I don't know what the lesson is here. If it is to identify errors in code, then I passed. I found the error and corrected it. If it is to pretend that someone would actually name an alias "fromTEST" and want to know the results, then I would say, don't waste my time.

    When I saw the question in the newsletter, I assumed the missing space was a typo. But after seeing that one of the answer options actually mentioned the missing space, I knew that this must have been deliberate. I fail to see how you can think the question author could have accidentally missed a space he intended to be there.

    As to what can be learned from this question - have you never stared for half an hour or more at an inexplicable error message being returned from what appears to be perfectly valid SQL before finally noticing the missing comma in

    INSERT INTO dbo.TargetTable (ColA, ColB, ColC, ColD, ColE)

    SELECT (Complicated + Expression) / That

    * Nevertheless * (1 - IsSyntacticallyValid) -- ColA

    ColB, -- ColB

    'String constant', -- ColC

    17.0 * (1 - ColD), -- ColD

    NULL -- ColE

    FROM dbo.SourceTable;


    Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
    Visit my SQL Server blog: https://sqlserverfast.com/blog/
    SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/

  • Hugo Kornelis (10/21/2013)


    crussell-931424 (10/21/2013)


    I can't win on these. If you read it exactly as it is written then people complain that we should have understood what the question was really asking in spite of the "accidental" typo. So I figure this must just be another "accidental" type and I correct it, separating the "from" from the the "TEST." But no, this time the question is attempting to trick us. If it wasn't then why use fromTEST as the alias? No one would ever use that intentionally. So I guess I don't know what the lesson is here. If it is to identify errors in code, then I passed. I found the error and corrected it. If it is to pretend that someone would actually name an alias "fromTEST" and want to know the results, then I would say, don't waste my time.

    When I saw the question in the newsletter, I assumed the missing space was a typo. But after seeing that one of the answer options actually mentioned the missing space, I knew that this must have been deliberate. I fail to see how you can think the question author could have accidentally missed a space he intended to be there.

    As to what can be learned from this question - have you never stared for half an hour or more at an inexplicable error message being returned from what appears to be perfectly valid SQL before finally noticing the missing comma in

    INSERT INTO dbo.TargetTable (ColA, ColB, ColC, ColD, ColE)

    SELECT (Complicated + Expression) / That

    * Nevertheless * (1 - IsSyntacticallyValid) -- ColA

    ColB, -- ColB

    'String constant', -- ColC

    17.0 * (1 - ColD), -- ColD

    NULL -- ColE

    FROM dbo.SourceTable;

    One more Nice example

  • For once I didn't fall for the trick!

    -------------------
    A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
    Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html

  • T.Ashish (10/21/2013)


    crussell-931424 (10/21/2013)


    I can't win on these. If you read it exactly as it is written then people complain that we should have understood what the question was really asking in spite of the "accidental" typo. So I figure this must just be another "accidental" type and I correct it, separating the "from" from the the "TEST." But no, this time the question is attempting to trick us. If it wasn't then why use fromTEST as the alias? No one would ever use that intentionally. So I guess I don't know what the lesson is here. If it is to identify errors in code, then I passed. I found the error and corrected it. If it is to pretend that someone would actually name an alias "fromTEST" and want to know the results, then I would say, don't waste my time.

    Here the lesson is about handling of alias in sql server.

    +1 Ashish.

    Crussell, I would have said exactly the same but the subject of the question clears the doubt... 🙂

Viewing 15 posts - 16 through 30 (of 34 total)

You must be logged in to reply to this topic. Login to reply