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

      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

        This is the controller panel for Smart Panels app

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

                Created:
                Updated:
                Resolved: