topic Re: PROC SQL - outermost query can see fields through nested querys in SAS Programming

PROC SQL - outermost query can see fields through nested querys

EvoluZion3 — Wed, 11 Jul 2018 09:30:49 GMT

Hi all, we have come across this interesting feature in PROC SQL and wondered what your thoughts were. We're currently using EG5.1 and SAS9.3.

We are finding that an outer query can see fieldnames from deep inside nested queries, whereas a SELECT should only be able to see the results immediately inside the FROM. For all other SQL platforms I've used (MS SQL, Oracle, Access) this shouldn't be allowed, indeed they error when I replicate the same problem.

To see what I mean, please run the below.

Example 1A works (or rather errors) as I'd expect, however example 1B shows that just by aliasing the example table's fieldname (even using the same name) the outer query can see "through" the middle query into the inner query. I've tried this with a query nested four times with the same result.


/* Build an example table */

data WORK.MYDATA;
input MYID;
datalines; 
1
2
3
;




/* Example 1A - this gives a "column not found" error as expected, as the outermost SELECT cannot see "MYID", only "NEWFIELDNAME777" */
PROC SQL;
SELECT MYID FROM (
SELECT MYID AS NEWFIELDNAME777 FROM (
SELECT MYID FROM WORK.MYDATA
)
);
QUIT;




/* Example 1B - just by aliasing the fieldname in the innermost query, this now returns records */
PROC SQL;
SELECT MYID FROM (
SELECT MYID AS NEWFIELDNAME777 FROM (
SELECT MYID AS MYID FROM WORK.MYDATA
)
);
QUIT;




/* Example 2A - if the middle query aggregates the data, the aggregate is returned alongside each record (the outer query can see both the middle query and the innermost query) */
PROC SQL;
SELECT MYID, NEWFIELDNAME777 FROM (
SELECT SUM(MYID) AS NEWFIELDNAME777 FROM (
SELECT MYID AS MYID FROM WORK.MYDATA
)
);
QUIT;




/* Example 2B - if the middle query aggregates the data, but I use UNION to replicate the table, only the last record is returned, alongside the aggregate. This should return the same results as 2A */
PROC SQL;
SELECT MYID, NEWFIELDNAME777 FROM (
SELECT SUM(MYID) AS NEWFIELDNAME777 FROM (
SELECT 1 AS MYID FROM WORK.MYDATA WHERE MYID = 1 UNION
SELECT 2 AS MYID FROM WORK.MYDATA WHERE MYID = 2 UNION
SELECT 3 AS MYID FROM WORK.MYDATA WHERE MYID = 3
)
);
QUIT;

Re: PROC SQL - outermost query can see fields through nested querys

Quentin — Wed, 11 Jul 2018 21:52:24 GMT

Well that's surprising to me. Seems like a bug. You don't even need the query in the middle to reference MYID. If you use the _tree option, you can see that the _tree that is shown when the problem occurs, is almost the same as if you just did select * from mydata. It's as if the compiler is completely skipping the the middle query.

I'd suggest sending it in to tech support.

data mydata ;
  myid=1 ;
run ;

*errors as expected ;
proc sql _tree ;
  select myid from
    (select 0 as foo from
      (select myid from mydata)
     )
 ;
quit ;

*surprising bug (?). Should error, but returns myid from inner most query ; 
*Note the short tree ;
proc sql _tree ;
  select myid from
    (select 0 as foo from
      (select myid as myid from mydata)
     )
 ;
quit ;

*works as expected ;
proc sql _tree ;
  select * from
    (select 0 as foo from
      (select myid from mydata)
     )
 ;
quit ;

Re: PROC SQL - outermost query can see fields through nested querys

EvoluZion3 — Fri, 13 Jul 2018 09:15:29 GMT

Thanks Quentin, glad it's not just me. I've submitted a ticket and will update this thread with their response.

Re: PROC SQL - outermost query can see fields through nested querys

EvoluZion3 — Tue, 17 Jul 2018 08:09:31 GMT

I received this email from SAS Technical Support:

Thank you for your email regarding the nested SQL select queries.

I've just been running the code and I see what you mean.

I can't find any previous mention of this in our knowledge base or Defect system, so what I will do is raise this with my Global Support team as a candidate for a Defect.

I'll be in touch as soon as I have more information for you .

Re: PROC SQL - outermost query can see fields through nested querys

EvoluZion3 — Thu, 26 Jul 2018 15:16:16 GMT

This has been raised by SAS as a software fault, and will be fixed in the future. I'm surprised no-one else has hit this problem before.