SUPPORT / SAMPLES & SAS NOTES
Problem Note 65125: A PROC FEDSQL left join query returns randomly missing values in a massively parallel processing (MPP) environment for SAS® Viya® 3.4
 |  |  |  |  |
A FEDSQL procedure query that is executed in an MPP environment can return missing values erroneously instead of the correct numeric values. The following syntax is an example of a query that can cause this issue:
proc fedsql sessref=mySession;
create table public.join_target {options copies=0 replace=true} as
select l.PREDICT_DT,
l.GEO_CUSTOMER_ID,
l.GEO_SUPPLIER_ID,
l.ID,
l.PREDICT,
l.PROD_ID,
l.START_DT,
l.VISION,
l.VALUE as HIST_VALUE,
r.VALUE as CURR_VALUE
from casdata.history l left join casdata.current r
on (l.ID=r.ID and
l.START_DT=r.START_DT and
l.GEO_CUSTOMER_ID=r.GEO_CUSTOMER_ID and
l.GEO_SUPPLIER_ID=r.GEO_SUPPLIER_ID)
where l.START_DT=date '2019-11-15';
quit;
create table public.join_target {options copies=0 replace=true} as
select l.PREDICT_DT,
l.GEO_CUSTOMER_ID,
l.GEO_SUPPLIER_ID,
l.ID,
l.PREDICT,
l.PROD_ID,
l.START_DT,
l.VISION,
l.VALUE as HIST_VALUE,
r.VALUE as CURR_VALUE
from casdata.history l left join casdata.current r
on (l.ID=r.ID and
l.START_DT=r.START_DT and
l.GEO_CUSTOMER_ID=r.GEO_CUSTOMER_ID and
l.GEO_SUPPLIER_ID=r.GEO_SUPPLIER_ID)
where l.START_DT=date '2019-11-15';
quit;
This same code example works as expected, and values are not missing, when it executes in a symmetric multiprocessing (SMP) environment.
Click the Hot Fix tab in this note for a link to instructions about accessing and applying the software update.
Operating System and Release Information
| Product Family | Product | System | Product Release | SAS Release | ||
| Reported | Fixed* | Reported | Fixed* | |||
| SAS System | SAS Viya | Microsoft Windows Server 2012 R2 Datacenter | 3.4 | 3.4 | Viya | |
| Microsoft Windows Server 2012 R2 Std | 3.4 | 3.4 | Viya | |||
| Microsoft Windows Server 2016 | 3.4 | 3.4 | Viya | |||
| Linux for x64 | 3.4 | 3.4 | Viya | |||