Common misconception about Exists and Notexists joins

As I was looking over some code doing exists joins (and the corresponding non exists joins), I say a lot of cases the were troublesome. There are two cases that could mean that the author has an expectation that is not met by the X++ runtime when it comes to these statements. Let's look at an example (This code was chosen at random among a lot of examples in our application code):

        UserInfo                    userInfo;

        while select User, SecurityRole from securityUserRole
        where securityUserRole.SecurityRole == securityRole.RecId
        exists join Id, Enable from userInfo
            where userInfo.Id == securityUserRole.User && userInfo.Enable == true
        exists join securityUserRoleCondition
            where securityUserRoleCondition.SecurityUserRole == securityUserRole.RecId
            && securityUserRoleCondition.DataArea == curext()
        {
            userList.add(securityUserRole.User);    
        }

In this case the developer is including two fields, Id and Enable in the projection from the userinfo table. This does not make sense. In exists and notexists joins the system does not actually fetch any records of the joined type - It just checks if they exist or not. The Id and Enable fields would have their default values in this case, not related to any data in the database.

The syntax of the language does not forbid this, for practical reasons. However, we will probably introduce a diagnostic (i.e. a warning or even an error message) going forward to at least make the developer aware that he is not getting what he thinks he is. 

You can find out if you have the first problem in your code by simple inspection or by using this Socratex query:

<ExistJoinsWithFields>
{
  for $a in /*
  for $q in $a//Query//JoinSpecification[@Kind=('ExistsJoin', 'NotExistsJoin')]/Query/ExplicitSelection
  order by $a/@Package
  return <ExistJoinsWithField Artifact='{$a/@Artifact}' Package='{$a/@Package}'
      StartLine='{$q/@StartLine}' EndLine='{$q/@EndLine}' 
      StartCol='{$q/@StartCol}' EndCol='{$q/@EndCol}' /> 
}
</ExistJoinsWithFields>

The query is included in the set of sample queries on the github site. Please refer to the blog posts about Socratex if you are in any doubt about how to run these queries.

As it happens there is an even worse problem lurking here, and it is not as easy to detect. Some code will actually read the values of the buffer (irrespective of whether an explicit projection was provided or not). To illustrate the problem please consider:

while select f1 from T1 where T.f1 > 2
exists join T2 where ...
{
    var a = T2.fn;
}

In this case the user is referring to the value of a field on the exists join'ed buffer. The value is the default value, of course. Now, it is not easy for the compiler to detect this case. The reason for this is shown in the example below:

while select f1 from T1 where T.f1 > 2
exists join T2 where ...
{

    T2 = T2::find('Banana');
    var a = T2.fn;
}

Now the reference to T2.fn is not an error, since a value was assigned to the buffer T2 after the exists join. Solving this problem in the compiler is not easy. It essentially is the problem of generally knowing, at each point in the code, what variables are guaranteed to have been assigned a value. This problem was not addressed by the X++ compiler because everything always has a value.

You can use the query called UseOfExistJoinBuffers in the sample queries to analyze your code for this problem, but the analysis is not going to be perfect: It will just catch references to fields on the buffers join in exists/notexists clauses. There are likely to be many false positives, for the reasons I described above.

We may, or may not, fix the compiler to diagnose correctly this issue. In the mean time, please do a manual inspection of your code to make sure you are not falling into this trap!