MIN function behavior changed on Oracle databases after SAS Upgrade to 9.4M7-CodePudding

I have a program that has been working for years. Today, we upgraded from SAS 9.4M3 to 9.4M7.

proc setinit
Current version: 9.04.01M7P080520

Since then, I am not able to get the same results as before the upgrade.

Please note that I am querying on Oracle databases directly.

Trying to replicate the issue with a minimal, reproducible SAS table example, I found that the issue disappear when querying on a SAS table instead of on Oracle databases.

Let's say I have the following dataset:

data have;
infile datalines delimiter="|";
input name :$8. id $1. value :$8. t1 :$10.;
datalines;
Joe|A|TLO
Joe|B|IKSK
Joe|C|Yes
;

Using the temporary table:

proc sql;
    create table want as
    select name,
    min(case when id = "A" then value else "" end) as A length 8
    from have
    group by name;
quit; 

Results:

name   A
Joe   TLO

However, when running the very same query on the oracle database directly I get a missing value instead:

proc sql;
    create table want as
      select name, 
      min(case when id = "A" then value else "" end) as A length 8
      from have_oracle
      group by name;
quit;

name     A
Joe

As per documentation, the min() function is behaving properly when used on the SAS table

The MIN function returns a missing value (.) only if all arguments are missing.

I believe this happens when Oracle don't understand the function that SAS is passing it - the min functions in SAS and Oracle are very different and the equivalent in SAS would be LEAST().

So my guess is that the upgrade messed up how is translates the SAS min function to Oracle, but it remains a guess. Does anyone ran into this type of behavior?

EDIT: @Richard's comment

options sastrace=',,,d' sastraceloc=saslog nostsuffix;

proc sql;
    create table want as
    select t1.name,
    min(case when id = 'A' then value else "" end) as A length 8
    from oracle_db.names t1 inner join oracle_db.ids t2 on (t1.tid = t2.tid)
    group by t1.name;
    
ORACLE_26: Prepared: on connection 0
SELECT * FROM NAMES
 
ORACLE_27: Prepared: on connection 1
SELECT  UI.INDEX_NAME, UIC.COLUMN_NAME FROM  USER_INDEXES UI,USER_IND_COLUMNS UIC WHERE  UI.TABLE_NAME='NAMES' AND 
UIC.TABLE_NAME='NAMES' AND  UI.INDEX_NAME=UIC.INDEX_NAME
 
ORACLE_28: Executed: on connection 1
SELECT statement  ORACLE_27
 
ORACLE_29: Prepared: on connection 0
SELECT * FROM IDS
 
ORACLE_30: Prepared: on connection 1
SELECT  UI.INDEX_NAME, UIC.COLUMN_NAME FROM  USER_INDEXES UI,USER_IND_COLUMNS UIC WHERE  UI.TABLE_NAME='IDS' AND 
UIC.TABLE_NAME='IDS' AND  UI.INDEX_NAME=UIC.INDEX_NAME
 
ORACLE_31: Executed: on connection 1
SELECT statement  ORACLE_30
 
ORACLE_32: Prepared: on connection 0
select t1."NAME", MIN(case  when t2."ID" = 'A' then t1."VALUE" else ' ' end) as A from 
NAMES t1 inner join IDS t2 on t1."TID" = t2."TID" group by t1."NAME"
 
ORACLE_33: Executed: on connection 0
SELECT statement  ORACLE_32
 
ACCESS ENGINE:  SQL statement was passed to the DBMS for fetching data. 
NOTE: Table WORK.SELECTED_ATTR created, with 1 row and 2 columns.

!                              quit;
NOTE: PROCEDURE SQL used (Total process time):
      real time           0.34 seconds
      cpu time            0.09 seconds

CodePudding user response：

Use the SASTRACE= system option to log SQL statements sent to the DBMS.

options SASTRACE=',,,d';

will provide the most detailed logging.

CodePudding user response：

Why are you using ' ' or '' as the ELSE value? Perhaps Oracle is treating a string with blanks in it differently than a null string.

Why not use null in the ELSE clause?
or just leave off the ELSE clause and let it default to null?

libname mylib oracle .... ;
proc sql;
  create table want as
    select name
         , min(case when id = "A" then value else null end) as A length 8
    from mylib.have_oracle
    group by name
  ;
quit;

Also try running the Oracle code yourself, instead of using implicit pass thru.

proc sql;
  connect to oracle ..... ;
  create table want as
    select * from connection to oracle
     (
      select name, 
      min(case when id = "A" then value else null end) as A length 8
      from have_oracle
      group by name
     )
  ;
quit;

CodePudding user response：

When I try to reproduce this in Oracle I get the result you are looking for so I suspect it has something to do with SAS (which I'm not familiar with).

with t as (
  select 'Joe' name, 'A' id, 'TLO' value from dual union all
  select 'Joe' name, 'B' id, 'IKSK' value from dual union all
  select 'Joe' name, 'C' id, 'Yes' value from dual
)
select name
, min(case when id = 'A' then value else '' end) as a
from t
group by name;


NAME A   
---- ---- 
Joe  TLO

Unrelated, if you are only interested in id = 'A' then a better query would be:

select name
, min(value) as a
from t
where id = 'A'
group by name;