Date:         Thu, 30 Oct 2008 12:42:18 -0700
Reply-To:     "Pardee, Roy" <pardee.r@GHC.ORG>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         "Pardee, Roy" <pardee.r@GHC.ORG>
Subject:      Re: bug in proc sql?
Comments: To: Mary <mlhoward@avalon.net>
In-Reply-To:  <077301c93ac6$2137cb10$832fa8c0@HP82083701405>
Content-Type: text/plain; charset="us-ascii"

Holy!  MSSQL does it too!  And so does MySQL.

You only get the error I would expect if you give a full table_name.field_name spec in the subselect.

So--are others at least as *surprised* as I am that we don't get errors here?

I would like to think of the parens on the subselect as having something like the same effect they do in arithmetic expressions.  But it seems not...

________________________________

From: Mary [mailto:mlhoward@avalon.net]
Sent: Thursday, October 30, 2008 12:32 PM
To: Pardee, Roy; SAS-L@LISTSERV.UGA.EDU
Subject: Re: bug in proc sql?


I can replicate it, but I can also replicate it in Oracle!  Oracle returns all the boys too.

So I'm not sure if there is a standard to SQL that is missing here.

-Mary

        ----- Original Message -----
        From: Pardee, Roy <mailto:pardee.r@GHC.ORG>
        To: SAS-L@LISTSERV.UGA.EDU
        Sent: Thursday, October 30, 2008 2:24 PM
        Subject: bug in proc sql?

        Running SAS 9.1.3 Service Pack 4 (TS1M3) on the NET_SRV platform, the second sql statement below returns the entire sashelp.class dataset, instead of an error about the subselect not being valid.

          proc sql ;

            * Create a table with just the ages of the girls. ;
            create table class_girls as
            select age
            from sashelp.class
            where sex = 'F'
            ;

            * Why doesn't this complain about the lack of a name field in the class_girls dset? ;
            * Instead it just returns everyone in sashelp.class. ;
            select *
            from sashelp.class
            where name in (select name from class_girls)
            ;

          quit ;

        That's a bug, isn't it?  Can others replicate?

        Thanks!

        -Roy

        Roy Pardee
        Research Analyst/Programmer
        Group Health Center For Health Studies (Cancer Research Network)
        (206) 287-2078
        Google Talk: rpardee