KB441377: Full Outer Join Optimization implemented in MicroStrategy 11.0 for databases that do not support Full Outer Joins

SYMPTOM:
This implementation is specific to databases that do not support full outer joins and has been implemented only in Strategy 11.0 and later. Previously, when VLDB setting 'Preserve all final pass result elements' is set to 'Preserve all final pass result elements' (second option), a full outer join would be created to satisfy the requirements of needing to left outer join to left side of lookup table and right outer join to the right side of fact tables. For databases that do not support full outer joins, this would result into a syntax error thrown by database.
 
CAUSE:
This is a known defect in Strategy releases before 11.0.
ACTION/RESOLUTION:
This defect has been in addressed in Strategy 11.0 release.
If you are upgrading to Strategy 11.0 or above, to pick up the fix, enable project level setting "Data Engine Version" to 11. This property can only be enabled through Workstation. For more details on enabling the setting, refer to Change Data Engine Version.
If you are upgrading to Strategy 10.9 / 10.10 / 10.11: To pick up the fix, enable the Feature Flag "Defect fixes that may impact Data, SQL, MDX, CSI" within the Web Preferences -> Project Defaults. For more details on enabling Feature Flags refer to What are the Feature Flag options and what do they accomplish in MicroStrategy Web 10.8 and up. Starting Strategy 11.0, this Feature Flag is removed and replaced with project level setting called "Data Engine Version".
In 11.0 or later, SQL Engine creates necessary intermediate table(s) and uses left outer join to mimic the same behavior as full outer join. Here is a sample SQL:
SQL BEFORE 11.0:

select      a01.CATEGORY_ID  CATEGORY_ID,
a01.CATEGORY_DESC  CATEGORY_DESC0
from      LU_CATEGORY      a01

create table TE4H49Q2POP000(
CATEGORY_ID NUMBER(38, 0),
CATEGORY_DESC0 VARCHAR2(50))
     
insert into TE4H49Q2POP000 values ('insert_values')
[The rest of the INSERT statements have been omitted from display].

create table T1LJPODNLOL001 nologging as
select      a12.YEAR_ID  YEAR_ID,
a11.CATEGORY_ID  CATEGORY_ID
from      TE4H49Q2POP000      a11
cross join      LU_YEAR      a12

select      coalesce(pa11.YEAR_ID, a12.YEAR_ID)  YEAR_ID,
coalesce(pa11.CATEGORY_ID, a12.CATEGORY_ID)  CATEGORY_ID,
a13.CATEGORY_DESC0  CATEGORY_DESC0,
a12.TOT_COST  WJXBFS1
from      T1LJPODNLOL001      pa11
full outer join   YR_CATEGORY_SLS      a12
on       (pa11.CATEGORY_ID = a12.CATEGORY_ID and
pa11.YEAR_ID = a12.YEAR_ID)
left outer join      TE4H49Q2POP000      a13
on       (coalesce(pa11.CATEGORY_ID, a12.CATEGORY_ID) = a13.CATEGORY_ID)
     
[Analytical engine calculation steps:
     
]

SQL IN 11.0 AND LATER:

select      a01.CATEGORY_ID  CATEGORY_ID,
a01.CATEGORY_DESC  CATEGORY_DESC0
from      LU_CATEGORY      a01
CREATE TEMPORARY TABLE T3S2BWBHLOP000(
CATEGORY_ID   DECIMAL(38, 0),
CATEGORY_DESC0  VARCHAR(50))
     
insert into T3S2BWBHLOP000 values ('insert_values')
[The rest of the INSERT statements have been omitted from display].

create index T3S2BWBHLOP000_i on T3S2BWBHLOP000 (CATEGORY_ID, CATEGORY_DESC0)
     
CREATE TEMPORARY TABLE TYX5GJI9LOL001(
YEAR_ID   DECIMAL(38, 0),
CATEGORY_ID  DECIMAL(38, 0))
     
insert into TYX5GJI9LOL001
select      a12.YEAR_ID  YEAR_ID,
a11.CATEGORY_ID  CATEGORY_ID
from      T3S2BWBHLOP000      a11
cross join      `LU_YEAR`      a12
     
create index TYX5GJI9LOL001_i on TYX5GJI9LOL001 (YEAR_ID, CATEGORY_ID)
     
CREATE TEMPORARY TABLE T438EWGBTOJ002(
YEAR_ID      DECIMAL(38, 0),
CATEGORY_ID      DECIMAL(38, 0))

insert into T438EWGBTOJ002
select      pa11.YEAR_ID  YEAR_ID,
pa11.CATEGORY_ID  CATEGORY_ID
from      TYX5GJI9LOL001      pa11

insert into T438EWGBTOJ002
select      a11.YEAR_ID  YEAR_ID,
a11.CATEGORY_ID  CATEGORY_ID
from      `YR_CATEGORY_SLS`      a11
     
create index T438EWGBTOJ002_i on T438EWGBTOJ002 (YEAR_ID, CATEGORY_ID)
     
CREATE TEMPORARY TABLE TYGCJGG21OD003(
YEAR_ID      DECIMAL(38, 0),
CATEGORY_ID      DECIMAL(38, 0))
     
insert into TYGCJGG21OD003
select      distinct pa11.YEAR_ID  YEAR_ID,
pa11.CATEGORY_ID  CATEGORY_ID
from      T438EWGBTOJ002      pa11
     
create index TYGCJGG21OD003_i on TYGCJGG21OD003 (YEAR_ID, CATEGORY_ID)

select      pa11.YEAR_ID  YEAR_ID,
pa11.CATEGORY_ID  CATEGORY_ID,
a14.CATEGORY_DESC0  CATEGORY_DESC0,
a13.TOT_COST  WJXBFS1
from      TYGCJGG21OD003      pa11
left outer join      TYX5GJI9LOL001      pa12
on       (pa11.CATEGORY_ID = pa12.CATEGORY_ID and
pa11.YEAR_ID = pa12.YEAR_ID)
left outer join      `YR_CATEGORY_SLS`      a13
on       (pa11.CATEGORY_ID = a13.CATEGORY_ID and
pa11.YEAR_ID = a13.YEAR_ID)
left outer join      T3S2BWBHLOP000      a14
on       (pa11.CATEGORY_ID = a14.CATEGORY_ID)
     
[Analytical engine calculation steps:
]

 
Strategy reference numbers for this technical note are KB441377 and DE99286.