Skip to main content

Notifications

Announcements

No record found.

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!

 

Comments

*This post is locked for comments

  • THCTCCA Profile Picture THCTCCA
    Posted at
    A place to start might be with the documentation for the use of exists where the example includes 'exists join * from'. The description is somewhat misleading as well, 'The exists keyword is a method that returns a Boolean value and a join clause', there is no actual join. I agree 100% that it really is syntactically incorrect, impact of being allowed depends on intended use. In your first example it's a no-harm no-foul since no attempt was made to utilize the buffer of the exist join table.
  • Peter Villadsen Profile Picture Peter Villadsen
    Posted at
    I would really like to introduce something (i.e. either best practice checks for outright compiler diagnostics) to diagnose these cases, but to do that we would have to have a better flow analysis engine built into the compiler. It is an investment we have wanted to make for a while, but one that is not insignificant. We do have a flow analysis engine, but it handles few scenarios (like making sure that super is invariably called in constructors). The reason we do not have a better one, is that we do not need it(!) because variables are guaranteed to always have a value in X++, even before a value is explicitly assigned to them. The flow analysis engine would help us in other ways too.
  • Community Member Profile Picture Community Member Microsoft Employee
    Posted at
    Can Microsoft just introduce a new Best Practice check that can handle both your examples? Or such issues can't be detected using the Best Practice check framework?