Post reply

TSQL bug ?

  • February 8, 2005 at 11:37 am

    #185062

    HI,

    Can some body tell me why SQL Server don't give me an error when i'am running this query:

    select * from GC_topics_TopicsQuery where itopicsQuery_id  in (

    select iTopicsQuery_id from GC_Topics_Topics  where iuser_id in ( 271,264,331,332,434,438,440,396,421,397))

    In the second query, there's no column name iTopicsQuery_id and SQLServer don't give me an error !!!!!

    If a type gaga instead of iTopicsQuery_id, the SQLServer give me the erreor sayinf that the column does not exists.

    Thank's

  • February 8, 2005 at 11:44 am

    #541207

    An "In () " subquery is not correlated, therefore there is no requirement for the column name in the outer query to match the column name of the subquery resultset. Data types need to macth, but column names don't matter.

    "gaga" on the other hand does not exists as a column name in the subquery table, and the error is valid.

    This is not a T-SQL bug.

     

  • February 8, 2005 at 11:51 am

    #541210

    "iTopicsQuery_Id" is like "gaga", it does'nt exist in the inner query and i select it !!

    I don't want the columns to match.

  • February 8, 2005 at 12:03 pm

    #541217

    Post the DDL for both tables ...

  • February 8, 2005 at 12:28 pm

    #541225

    Does iTopicsQuery_id exist in the outer query????

    Try it like this...

    select * from GC_topics_TopicsQuery where itopicsQuery_id  in (

    select GC_Topics_Topics.iTopicsQuery_id from GC_Topics_Topics  where iuser_id in ( 271,264,331,332,434,438,440,396,421,397))

    Steve

  • February 8, 2005 at 12:35 pm

    #541228

    It will work, i'am sure.

     

    Thank's

  • February 8, 2005 at 12:51 pm

    #541234

    I think that sql server is assuming that you are trying to do a correlation query and since the field exists in the first query it doesn't error out..

    have you tried running this query on its own?

    select iTopicsQuery_id from GC_Topics_Topics where iuser_id in ( 271,264,331,332,434,438,440,396,421,397)

    if "iTopicsQuery_id" doesn't exists it will error (assuming that the table name is valid). If you get an error running this statement it means that sqlserver is running a correlated subquery (for the select part at least).

  • February 8, 2005 at 1:00 pm

    #541237

    Best Pricatices dictate that EVERY FIELD in queries where there are more than one table or view should be qualified with table name like: tblname.fldName.

    Now, Do that on you query and it will fail!

    HTH

     


    * Noel

  • February 8, 2005 at 1:05 pm

    #541241

    Hehe.. straight to the point, thanx for pointing out this best practice and rephrasing my opinion in clearer terms .

  • February 8, 2005 at 1:12 pm

    #541244

    No problem

    I have seen this more than once and I was forced to look for the MSBPA to show some of my fellow developers that it was not something I invented from thin air

     


    * Noel

  • February 8, 2005 at 1:34 pm

    #541254

    Ya that's a nice tool... too bad I don't have enough time to make my project comply with all elements... but at least I try to make every new object compliant and I correct old ones when I have to modify them.

  • February 9, 2005 at 10:42 am

    #541412

    Won't the field "iTopicsQuery_id" be taken from the main query table, instead of the subquery, as you intended? 

    So long, and thanks for all the fish,

    Russell Shilling, MCDBA, MCSA 2K3, MCSE 2K3

Viewing 12 posts - 1 through 11 (of 11 total)

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