Uploaded image for project: 'OpenIDM'
  1. OpenIDM
  2. OPENIDM-14014

Not query expression inconsistency with optional JSON field

    Details

    • Target Version/s:
    • Story Points:
      3
    • Sprint:
      2020.06 - IDM

      Description

      When storing JSON data in IDM /repo/mything there is an inconsistency with how the following query behaves between DS and MySQL (possibly all relational databases):

      field1 eq "x" and !(field2 eq "y")
      

      Note that field2 is sometimes not present in the JSON object and that is when this query behaves differently. For DS, the missing field-value is detected, but for MySQL the query does not bring the record into the query result set.

      MySQL currently produces this SQL:

      SELECT          obj.fullobject AS fullobject, 
                      obj.objectid   AS objectid, 
                      obj.rev        AS rev, 
                      obj.id         AS id, 
                      obj.objectid   AS s_0 
      FROM            openidm.genericobjects obj 
      INNER JOIN      openidm.objecttypes objobjecttypes 
      ON              (( 
                                                      obj.objecttypes_id = objobjecttypes.id) 
                      AND             ( 
                                                      objobjecttypes.objecttype = 'import/managed/user'))
      LEFT OUTER JOIN openidm.genericobjectproperties propgenericobjectproperties1 
      ON              (( 
                                                      propgenericobjectproperties1.genericobjects_id = obj.id)
                      AND             ( 
                                                      propgenericobjectproperties1.propkey = '/_importUUID'))
      LEFT OUTER JOIN openidm.genericobjectproperties propgenericobjectproperties2 
      ON              (( 
                                                      propgenericobjectproperties2.genericobjects_id = obj.id)
                      AND             ( 
                                                      propgenericobjectproperties2.propkey = '/_importStatus'))
      WHERE           ( 
                                      propgenericobjectproperties1.propvalue = '6ead0cb7-dcb9-45dd-a450-50f53034bd58' 
                      AND             NOT propgenericobjectproperties2.propvalue = 'error') 
      ORDER BY        s_0 ASC 
      LIMIT           '5001' 
      offset          '0'
      

      by changing the line

                      AND             NOT propgenericobjectproperties2.propvalue = 'error') 
      

      to

                      AND             (propgenericobjectproperties2.propvalue IS NULL OR NOT propgenericobjectproperties2.propvalue = 'error')); 
      

      it can be made to behave closer to the way that DS does.

      I say "closer", because I found that "field2" : null doesn't work with this, but that also works with DS.

        Attachments

          Activity

            People

            • Assignee:
              dhogan Dirk Hogan
              Reporter:
              travis.haagen Travis Haagen
            • Votes:
              0 Vote for this issue
              Watchers:
              3 Start watching this issue

              Dates

              • Created:
                Updated: