Bizarre SQL error when building table over itself

[EvoluZion3]

Hi, I've come across this unexpected behaviour when writing a table over itself when referencing itself. I know this is generally bad practice and I avoid it whenever I can, however it'd be interesting to see what SAS is trying to do when running the below code.

If you run the below code (I know the join isn't complete for the Shoes table, but no matter) it should be successful.

PROC SQL;

  CREATE TABLE WORK.DEV AS SELECT Product FROM SASHELP.SHOES;

  CREATE TABLE WORK.DEV AS
  SELECT
    DEV.Product
  FROM
    WORK.DEV
    LEFT JOIN ( SELECT Product FROM WORK.DEV WHERE 1 = 1 ) NQ
    ON DEV.Product = NQ.Product;
QUIT;

However if you change the "1 = 1" to "1 = 0" and rerun, my SAS returns

"ERROR: Close of OUTPUT file WORK.DEV.DATA failed; another copy of the file is still open for INPUT; file not replaced."

I wouldn't expect this to error (certainly not driven by the trueness/falsehood of the clause).

I'm using EG7.1 and 9.4, but the above doesn't error on EG5.1/9.3 (we've recently upgraded).

[Quentin]

Interesting.  Replicated on 9.4M3.  I wonder if it's a problem of the SQL compiler doing bad short-circuiting when there is an obviously false where clause.

This seems to work okay (ignoring the warning about possibility integrity problems, which seems risky to ignore : ) .  I use RANUNI to filter:

PROC SQL;
  CREATE TABLE WORK.DEV AS SELECT Product FROM SASHELP.SHOES;
  CREATE TABLE WORK.DEV AS
  SELECT
    DEV.Product
  FROM
    WORK.DEV
    LEFT JOIN ( SELECT Product FROM WORK.DEV where ranuni(0)<.5 ) NQ
    ON DEV.Product = NQ.Product;
QUIT;

If you change to an expression which will always be false, but the compiler isn't smart enough to know it will always be false, it works:

PROC SQL;
  CREATE TABLE WORK.DEV AS SELECT Product FROM SASHELP.SHOES;
  CREATE TABLE WORK.DEV AS
  SELECT
    DEV.Product
  FROM
    WORK.DEV
    LEFT JOIN ( SELECT Product FROM WORK.DEV where ranuni(0)< -1 ) NQ
    ON DEV.Product = NQ.Product;
QUIT;

I would think specifying obs=0 would be logically equivalent to WHERE 0, but obs=0 does not cause a problem:

PROC SQL;
  CREATE TABLE WORK.DEV AS SELECT Product FROM SASHELP.SHOES;
  CREATE TABLE WORK.DEV AS
  SELECT
    DEV.Product
  FROM
    WORK.DEV
    LEFT JOIN ( SELECT Product FROM WORK.DEV (obs=0)) NQ
    ON DEV.Product = NQ.Product;
QUIT;

[Quentin]

You don't actually need a join for this to happen.  A simpler test case is:

PROC SQL;
  CREATE TABLE WORK.DEV AS SELECT Product FROM SASHELP.SHOES;

  CREATE TABLE WORK.DEV AS
  SELECT
    DEV.Product
  FROM
    WORK.DEV
  WHERE 0
  ;

QUIT;

[Kurt_Bremser]

Good catch. Bring it to the attention of SAS technical support.

[EvoluZion3]

Ok I'll raise this with SAS. Quentin I hope you don't mind if I use the shortened version which you discovered.

This will be the second fault I've found with SAS and it makes me worry about the quality of my SQL haha.

[EvoluZion3 · Accepted solution]

Apparently this has already been raised:
http://support.sas.com/techsup/notes/v8/12/330.html

[Quentin]

Thanks for posting the resolution.  I suggest you accept your post with the link to the tech support note as the solution.  At least an explanation. : )