Uploaded image for project: 'camunda BPM'
  1. camunda BPM
  2. CAM-11395

Use left join to test variable value conditions in historic process instance query

    XMLWordPrintable

    Details

      Description

      Currently, a query like historyService.createHistoricProcessInstanceQuery().variableValueEquals("foo", "a").list(); creates the following SQL:

      SELECT DISTINCT RES.*
      FROM (
      	SELECT SELF.*
      		,DEF.NAME_
      		,DEF.VERSION_
      	FROM ACT_HI_PROCINST SELF
      	LEFT JOIN ACT_RE_PROCDEF DEF ON SELF.PROC_DEF_ID_ = DEF.ID_
      	WHERE (
      			1 = 1
      			AND EXISTS (
      				SELECT ID_
      				FROM ACT_HI_VARINST
      				WHERE NAME_ = ?
      					AND PROC_INST_ID_ = SELF.PROC_INST_ID_
      					AND (
      						(
      							VAR_TYPE_ IS NOT NULL
      							AND VAR_TYPE_ = ?
      							AND TEXT_ IS NOT NULL
      							AND TEXT_ = ?
      							)
      						)
      				)
      			)
      	) RES
      ORDER BY RES.ID_ ASC LIMIT ? OFFSET ?
      

      The EXISTS part is a dependent subquery, i.e. a query that references a value of the outer result set. Such queries are slow on MariaDB and MySQL, because these systems are not able to rewrite this to a join.

      Proposed solution:

      • Rewrite the exists to a JOIN, probably LEFT JOIN

      Implementation notes:

      • variableValueEquals can be used multiple times in succession. We need to join ACT_HI_VARINST once for each of the conditions. Joining only once is not sufficient.
      • This also applies when using or queries, i.e. we must join once for any variable condition, be it in an or query or in the "main" query
      • This should be done in the core API as well as the webapp backend

        Attachments

          Issue Links

            Activity

              People

              Assignee:
              Unassigned
              Reporter:
              thorben.lindhauer Thorben Lindhauer
              Votes:
              0 Vote for this issue
              Watchers:
              0 Start watching this issue

                Dates

                Created:
                Updated:
                Resolved: