Why does this sql not return an error on execution?

• j.a.c

  SSCrazy

  Points: 2283

  More actions

  June 21, 2011 at 11:06 am

  #238316

  Hi,

  Why does this sql below not return an 'Invalid column' error when executed?

  Is it a bug or by design?

  CREATE TABLE #apples ( apple_id BIGINT )

  CREATE TABLE #oranges ( orange_id BIGINT )

  SELECT

  *

  FROM

  #apples

  WHERE

  apple_id IN (

  SELECT

  apple_id -- This should return an 'Invalid column' error!!

  FROM

  #oranges )

  IF OBJECT_ID(N'tempdb..#apples' , N'U') IS NOT NULL

  BEGIN

  DROP TABLE #apples

  END

  IF OBJECT_ID(N'tempdb..#oranges' , N'U') IS NOT NULL

  BEGIN

  DROP TABLE #oranges

  END

  Thanks,

  j.a.c

• mister.magoo

  SSC-Forever

  Points: 47174

  More actions

  June 21, 2011 at 11:19 am

  #1341984

  It is because apple_id is valid, compare to this, which is equivalent:

  CREATE TABLE #apples ( apple_id BIGINT )

  CREATE TABLE #oranges ( orange_id BIGINT )

  SELECT

  *

  FROM

  #apples as apples

  WHERE

  apple_id IN (

  SELECT

  apples.apple_id -- This should not return an 'Invalid column' error - you just didn't realise apple_id would be referenced from the #apples table

  FROM

  #oranges )

  IF OBJECT_ID(N'tempdb..#apples' , N'U') IS NOT NULL

  BEGIN

  DROP TABLE #apples

  END

  IF OBJECT_ID(N'tempdb..#oranges' , N'U') IS NOT NULL

  BEGIN

  DROP TABLE #oranges

  END

  MM
  

  ---

  
  select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);

• Forum Etiquette: How to post Reporting Services problems
[/url]
• Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
[/url]
• How to Post Performance Problems - by Gail Shaw
[/url]