Compare lists with !=

1
We had encounterd some unintended behaviour during development when comparing a ReferenceSet Association with a not equals operator '!='. We came to the conclusion that when using the operator '!=' we did not get the results we wanted where as when using the operator 'not()' we did get our expected results. When looking deeper into this issue we stumbled upon the difference in the actual queries that made me wonder is the operator '!=' valid for a list comparison? Example In our case we want to retrieve all location types that are not connected to our currently selected commercial product. Here are two xpaths which we would expect to give the same results but they don't: The not xpath RequestAnalyzer: incoming request XPathTextGetRequest (depth = -1, amount = 20): //MasterData.LocationType[not(MasterData.CommercialProduct_LocationType = '28991922601197569')] SQL: SELECT COUNT(*) FROM "masterdata$locationtype" WHERE NOT EXISTS (SELECT "c1masterdata$commercialproduct_locationtype"."masterdata$locationtypeid" FROM "masterdata$commercialproduct_locationtype" "c1masterdata$commercialproduct_locationtype" WHERE "c1masterdata$commercialproduct_locationtype"."masterdata$locationtypeid" = "masterdata$locationtype"."id" AND "c1masterdata$commercialproduct_locationtype"."masterdata$commercialproductid" = 28991922601197569) SQL: SELECT "masterdata$locationtype"."id", "masterdata$locationtype"."code", "masterdata$locationtype"."name", "masterdata$locationtype"."shortname" FROM "masterdata$locationtype" WHERE NOT EXISTS (SELECT "c1masterdata$commercialproduct_locationtype"."masterdata$locationtypeid" FROM "masterdata$commercialproduct_locationtype" "c1masterdata$commercialproduct_locationtype" WHERE "c1masterdata$commercialproduct_locationtype"."masterdata$locationtypeid" = "masterdata$locationtype"."id" AND "c1masterdata$commercialproduct_locationtype"."masterdata$commercialproductid" = 28991922601197569) ORDER BY "masterdata$locationtype"."id" ASC LIMIT 20 The != xpath RequestAnalyzer: incoming request XPathTextGetRequest (depth = -1, amount = 20): //MasterData.LocationType[MasterData.CommercialProduct_LocationType != '28991922601197569'] SQL: SELECT COUNT(*) FROM "masterdata$locationtype" WHERE "masterdata$locationtype"."id" IN (SELECT "c1masterdata$commercialproduct_locationtype"."masterdata$locationtypeid" FROM "masterdata$commercialproduct_locationtype" "c1masterdata$commercialproduct_locationtype" WHERE ("c1masterdata$commercialproduct_locationtype"."masterdata$commercialproductid" != 28991922601197569 OR "c1masterdata$commercialproduct_locationtype"."masterdata$commercialproductid" IS NULL)) SQL: SELECT "masterdata$locationtype"."id", "masterdata$locationtype"."code", "masterdata$locationtype"."name", "masterdata$locationtype"."shortname" FROM "masterdata$locationtype" WHERE "masterdata$locationtype"."id" IN (SELECT "c1masterdata$commercialproduct_locationtype"."masterdata$locationtypeid" FROM "masterdata$commercialproduct_locationtype" "c1masterdata$commercialproduct_locationtype" WHERE ("c1masterdata$commercialproduct_locationtype"."masterdata$commercialproductid" != 28991922601197569 OR "c1masterdata$commercialproduct_locationtype"."masterdata$commercialproductid" IS NULL)) ORDER BY "masterdata$locationtype"."id" ASC LIMIT 20 The not xpath gives us exactly that what we want, all locationtypes that do not have a association with a commercial product that has the provided id. The != xpath gives us all locationtypes that have an association with a commercial product that does not have the provided id, or where the id is null. The latter part seems invalid as it should not be possible to have an entry in an association table that has a null value? And the first part is also not what i would expect? Is this behaviour intended this way? And if so what do you Mendixites say of the comparrision $ObjectList != $ObjectA ? I personally feel that it is more logical to handle the != operator as not contains rather then unequals, since the = operator is also handled as contains and not as equals. Update after reading Rene's answer i think he is right. That makes the human readable xpath something like: Select all entities that have an association to 1 //EntityA[EntityA_EntityB = 1] Select all entities that have an association that is not 1 //EntityA[EntityA_EntityB != 1] Where as the not statement would do what you actually want. Select all entities that have do not have an association to 1 //EntityA[not(EntityA_EntityB = 1)]
asked
1 answers
1

I do not agree with you that the = operator is a contain comparison. Is is an equal comparison. the contents of statement on the left should be exactly the same as the right side of the = operator.

The scenario you mention is; //EntityA[EntityA_EntityB != id]

both != and = will test if there is an association and act upon. So in both cases there must be an association set. Otherwise the return of the XPath is false, in that case the tested record is not included. In human language it is; I want al EntityA records, which have an association to EntityB, but the id should not be 'x'

The not() function reverses the XPath. //EntityA[not(EntityA_EntityB = id)] is evaluated like; I want all EntityA records, but it should not have a association to EntityB record with id 'x'

answered