Friday, April 12, 2019

Dangerous bug in CustCollectionsPoolsListPage form AX2012/D365

There is a form CustCollectionsPoolsListPage where two data sources are outer joined to the root data source with no relations (no links).



If by any reason the initial query does not contain links for these two aforementioned, SQL starts producing a Cartesian product and generating a huge temporary table. The latter can potentially lead to SQL server crash, like it happened in our environment.



The following fix may leave much to be desired but at least it creates needed links in case they are absent.

On CustTable data source we have to add an additional check for the existing query.


public void executeQuery()
{
    element.populateAgingIndicators(selectedCustAging);

    // Use the query from the cue?
    if (!useInitialQuery 
                        // Begin
                        || !this.wblCheckQuery(this.query())
                        // End: 
                        )
    {
        this.query(element.addOriginalPoolQuery(listPageHelper.getCurrentPoolQuery()));
    }


    super();

    element.setButtonAccess();
    element.setGridColumnLabels();
}


// to avoid the cartesian product in case of absent link for this outer join
private boolean wblCheckQuery(Query _query)
{
    QueryBuildDataSource custAgingDs;
    QueryBuildDataSource custAgingLegalEntityDs;
    boolean ret = true;

    custAgingDs             = _query.dataSourceName(#CustAgingDsName);
    custAgingLegalEntityDs  = _query.dataSourceName(#CustAgingLegalEntityName);
    if (!custAgingDs || !custAgingLegalEntityDs || custAgingDs.linkCount() <= 0 || custAgingLegalEntityDs.linkCount() <= 0)
    {
        ret = checkFailed("Saved query is corrupted. Try to recreate the cue");
    }
    return ret;
}

From SQL perspective we can catch such an issue by the following query.


use tempdb
select * from sys.dm_db_session_space_usage spu
join sys.dm_exec_sessions s on s.session_id = spu.session_id
join sys.dm_exec_requests r on s.session_id = r.session_id
cross apply sys.dm_exec_sql_text(sql_handle) t 
order by internal_objects_alloc_page_count desc

No comments: