Joins: InnerJoin / OuterJoin combined ?

SELECT STATEMENT
Consider this statement:

while
select  *
from    prodTable
    where prodTable.ADUProdClusterId == "CL00000001"
join    *
from    clusterTable
    where clusterTable.ClusterId == prodTable.ADUProdClusterId
outer join *
from    prodTableRef
    where prodTableRef.ProdId == prodTable.CollectRefProdId

It results in an error:

Cannot select a record in Works orders (ProdTable).
The join does not contain any link between the joined tables in the WHERE clause.

The cause is that we use a combination of inner join and outer join on the same source (prodTable)
Axapta doesn’t seem to be able to handle this.

The solution is to use all inner or all outer joins.
In our case, we changed the inner join to an outer join, and the query ran fine.

QUERY OBJECT
We also tried to replicate this query in a Query object.
(click pictures to enlarge)

ProdTable properties:

ADUProdClusterTable properties:

ProdTableRef properties:

We found that AX ignores the inner join when combined with an outer join (ProdTableRef enabled)
When we disabled ProdTableRef, effectively making this an inner join between just 2 tables, AX took the join into account correctly.

See the queries AX builds when executing through the Query object:

ProdTableRef ENABLED
ProdTable:           SELECT * FROM ProdTable
                     WHERE ((ADUProdClusterId = N'CL00000001'))
ADUProdClusterTable: SELECT * FROM ADUProdClusterTable
                     WHERE ProdTable.ADUProdClusterId = ADUProdClusterTable.ClusterId
ProdTableRef:        SELECT *
                     FROM ProdTable
                     WHERE ProdTable.CollectRefProdId = ProdTable.ProdId

ProdTableRef DISABLED
ProdTable:           SELECT * FROM ProdTable 
                     WHERE ((ADUProdClusterId = N'CL00000001'))
                     JOIN * FROM ADUProdClusterTable 
                     WHERE ProdTable.ADUProdClusterId = ADUProdClusterTable.ClusterId
ADUProdClusterTable: SELECT * FROM ADUProdClusterTable 
                     WHERE ProdTable.ADUProdClusterId = ADUProdClusterTable.ClusterId
ProdTableRef:        (ignored)

Changing the ADUProdClusterTable JoinMode property to OuterJoin doesn’t seem to make a difference:

ProdTableRef ENABLED
ProdTable:           SELECT * FROM ProdTable
                     WHERE ((ADUProdClusterId = N'CL00000001'))
ADUProdClusterTable: SELECT * FROM ADUProdClusterTable
                     WHERE ProdTable.ADUProdClusterId = ADUProdClusterTable.ClusterId
ProdTableRef:        SELECT *
                     FROM ProdTable
                     WHERE ProdTable.CollectRefProdId = ProdTable.ProdId

ProdTableRef DISABLED
ProdTable:           SELECT * FROM ProdTable 
                     WHERE ((ADUProdClusterId = N'CL00000001'))
                     OUTER JOIN * FROM ADUProdClusterTable 
                     WHERE ProdTable.ADUProdClusterId = ADUProdClusterTable.ClusterId
ADUProdClusterTable: SELECT * FROM ADUProdClusterTable 
                     WHERE ProdTable.ADUProdClusterId = ADUProdClusterTable.ClusterId
ProdTableRef:        (ignored)

CONCLUSION:

1. The select statement in X++ will work as long as you don’t mix inner and outer joins.
2. The select statement cannot be built with a Query object, since the resulting select statements are wrong,
   regardless of whether or not you mix inner and outer joins.