What is the "same query"?

  • robert.diley

    SSC Eights!

    Points: 860

    Comments posted to this topic are about the item What is the "same query"?

  • pmadhavapeddi22

    SSCertifiable

    Points: 5306

    I have checked the rows in sys.syscacheobjects before/after running the selects, found 5 new rows with all the selects mentioned in QOD.

    select * from sys.syscacheobjects where objtype ='adhoc' and sql like '%bob%'

    I answered 5 but the answer is 4, dont know where should I get the data for adhoc cache objects

    Thanks for the intereting question.

  • Carlo Romagnano

    SSC-Insane

    Points: 21712

    pmadhavapeddi22 (2/11/2015)


    I have checked the rows in sys.syscacheobjects before/after running the selects, found 5 new rows with all the selects mentioned in QOD.

    select * from sys.syscacheobjects where objtype ='adhoc' and sql like '%bob%'

    I answered 5 but the answer is 4, dont know where should I get the data for adhoc cache objects

    +1

    I run this query:

    SELECT usecounts, cacheobjtype, objtype, a.*

    FROM sys.dm_exec_cached_plans

    CROSS APPLY sys.dm_exec_sql_text(plan_handle) as a

    WHERE cacheobjtype = 'Compiled Plan'

    AND [text] NOT LIKE '%dm_exec_cached_plans%'

    AND [text] LIKE '%PlanName%'

    and objtype='Adhoc'

    ORDER BY usecounts DESC;

    It returns 5 rows. The bug is in the comment:

    SELECT *

    FROM Plans

    WHERE PlanName = 'Bob'

    -----------------------

    SELECT *

    FROM Plans

    WHERE PlanName = 'Bob'

    /* Same as first select */

    -----------------------

    SELECT *

    FROM Plans

    WHERE PlanName = 'Bob'

    -----------------------

    select *

    from Plans

    where PlanName = 'Bob'

    -----------------------

    SELECT *

    FROM Plans

    WHERE PlanName = 'Bob'

    -----------------------

  • rob.bailey 52577

    Newbie

    Points: 9

    Answer is 5, the first statement, there is a space after the *, not in the last.

  • Xavon

    SSCrazy

    Points: 2280

    Okay, good to know I am not nuts (or at least more so than usual). I didn't run it to check the actual count, but I had thought that extra space mattered...

  • robert.diley

    SSC Eights!

    Points: 860

    rob.bailey 52577 (2/11/2015)


    Answer is 5, the first statement, there is a space after the *, not in the last.

    Good eye. I am uncertain how that space got in there, but it would be enough to make the first different from the last.... I hope that points are awarded for those who find the space and give the answer as 5. I find this odd because I tested the code in 2008R2 and 2012 before submitting it.

    I meant not to include terminal spaces on a line as part of the problem because that makes the question not just tricky but downright deceptive: I like a QOD to be solvable on inspection, and the space is not apparent when viewing the browser.

    This flaw in my question does highlight the potential impact of characters we don't see.... Can anyone think of other cases where unseen characters cause unexpected results? One comes to my mind....

  • Hany Helmy

    SSChampion

    Points: 13321

    pmadhavapeddi22 (2/11/2015)


    I have checked the rows in sys.syscacheobjects before/after running the selects, found 5 new rows with all the selects mentioned in QOD.

    select * from sys.syscacheobjects where objtype ='adhoc' and sql like '%bob%'

    I answered 5 but the answer is 4, dont know where should I get the data for adhoc cache objects

    Thanks for the intereting question.

    Got 5 rows, although the answer might be wrong, but the question is interesting & different, thanx a lot.

  • Hany Helmy

    SSChampion

    Points: 13321

    Can anyone think of other cases where unseen characters cause unexpected results? One comes to my mind....

    Yeah, I remember once upon a time a tested piece of code (turned out to be not tested @ all) went to production server in the Constants table with an extra trailing space for 1 critical parameter, bad news came very fast, application is crashing, spent some time to figure out that extra space @ the end of the parameter, this is what I call: Learning, the hard way. 🙂

  • RLilj33

    SSCrazy

    Points: 2146

    rob.bailey 52577 (2/11/2015)


    Answer is 5, the first statement, there is a space after the *, not in the last.

    +1

  • jshahan

    SSCarpal Tunnel

    Points: 4622

    This is an excellent question. Simple example that teaches a very important point.

  • RLilj33

    SSCrazy

    Points: 2146

    jshahan (2/11/2015)


    This is an excellent question. Simple example that teaches a very important point.

    Agreed. It would be nice if the actual answer was one of the options 😀

  • SQLRNNR

    SSC Guru

    Points: 281210

    Carlo Romagnano (2/11/2015)


    pmadhavapeddi22 (2/11/2015)


    I have checked the rows in sys.syscacheobjects before/after running the selects, found 5 new rows with all the selects mentioned in QOD.

    select * from sys.syscacheobjects where objtype ='adhoc' and sql like '%bob%'

    I answered 5 but the answer is 4, dont know where should I get the data for adhoc cache objects

    +1

    I run this query:

    SELECT usecounts, cacheobjtype, objtype, a.*

    FROM sys.dm_exec_cached_plans

    CROSS APPLY sys.dm_exec_sql_text(plan_handle) as a

    WHERE cacheobjtype = 'Compiled Plan'

    AND [text] NOT LIKE '%dm_exec_cached_plans%'

    AND [text] LIKE '%PlanName%'

    and objtype='Adhoc'

    ORDER BY usecounts DESC;

    It returns 5 rows. The bug is in the comment:

    SELECT *

    FROM Plans

    WHERE PlanName = 'Bob'

    -----------------------

    SELECT *

    FROM Plans

    WHERE PlanName = 'Bob'

    /* Same as first select */

    -----------------------

    SELECT *

    FROM Plans

    WHERE PlanName = 'Bob'

    -----------------------

    select *

    from Plans

    where PlanName = 'Bob'

    -----------------------

    SELECT *

    FROM Plans

    WHERE PlanName = 'Bob'

    -----------------------

    Not just the presence of the comment. There is also a space after 'Bob' in that same query.

    The correct answer should be 5. But because the OP meant for us to consider that buggy query to be exactly the same as the first, I could see how the answer might be 4.

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • Toreador

    SSChampion

    Points: 11225

    One of those occasions when it pays to answer the question without running the code!

  • Ken Wymore

    SSCoach

    Points: 16354

    I should have gone with my gut. Was going to answer 4 because of the comment in the code about the one select being the same as the first. But then I ran it all and checked the cached objects and saw 5 so I figured that had to be correct.

    Nice question though!

  • rhythmk

    SSCertifiable

    Points: 7162

    Very Interesting question.Thanks.

    --rhythmk
    ------------------------------------------------------------------
    To post your question use below link

    https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help
    🙂

Viewing 15 posts - 1 through 15 (of 20 total)

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