[CAM-10864] Variable instance query fails when matching a variable value

Type: Bug Report
Resolution: Fixed
Priority: L3 - Default
Fix Version/s: 7.12.0, 7.12.0-alpha5
Affects Version/s: 7.12.0-alpha4
Component/s: engine
Labels:
None

Scenario:

Activated authorization check
Variable instance query
Matching a variable by value (e.g. runtimeService.createVariableInstanceQuery().variableValueEquals("foo", "bar").list());

Current behavior:

The SQL query is invalid and fails with the following exception:

04-Oct-2019 17:27:35.978 FEIN [http-nio-8080-exec-1] org.camunda.commons.logging.BaseLogger.logDebug ENGINE-13005 Starting command -------------------- VariableInstanceQueryImpl ----------------------
04-Oct-2019 17:27:35.978 FEIN [http-nio-8080-exec-1] org.camunda.commons.logging.BaseLogger.logDebug ENGINE-13009 opening new command context
04-Oct-2019 17:27:35.999 FEIN [http-nio-8080-exec-1] org.apache.ibatis.logging.jdbc.BaseJdbcLogger.debug ==>  Preparing: select distinct RES.* from ( select RES.*, ( case when RES.TASK_ID_ is not null and RES.EXECUTION_ID_ is not null then EXECUTION.ACT_INST_ID_ when RES.CASE_EXECUTION_ID_ is not null then RES.CASE_EXECUTION_ID_ when EXECUTION.PARENT_ID_ is null and RES.IS_CONCURRENT_LOCAL_ = 0 then EXECUTION.ID_ when EXECUTION.IS_SCOPE_ = 1 and EXECUTION.PARENT_ID_ is not null and RES.IS_CONCURRENT_LOCAL_ = 0 then PARENT_EXECUTION.ACT_INST_ID_ else EXECUTION.ACT_INST_ID_ end ) ACT_INST_ID_ from ACT_RU_VARIABLE RES left join ACT_RU_EXECUTION EXECUTION on RES.EXECUTION_ID_ = EXECUTION.ID_ left join ACT_RU_EXECUTION PARENT_EXECUTION on EXECUTION.PARENT_ID_ = PARENT_EXECUTION.ID_ left join ACT_RU_EXECUTION PROC_EXECUTION on PROC_EXECUTION.ID_ = RES.PROC_INST_ID_ left join ACT_RE_PROCDEF PROCDEF on PROCDEF.ID_ = PROC_EXECUTION.PROC_DEF_ID_ left JOIN ( SELECT A.* FROM ACT_RU_AUTHORIZATION A WHERE A.TYPE_ < 2 AND ( A.USER_ID_ in ( ?, '*') OR A.GROUP_ID_ IN ( ? , ? , ? , ? ) ) AND ( ( A.RESOURCE_TYPE_ = ? AND BITAND(A.PERMS_,?) = ? OR A.RESOURCE_TYPE_ = ? AND BITAND(A.PERMS_,?) = ? OR A.RESOURCE_TYPE_ = ? AND BITAND(A.PERMS_,?) = ? ) ) ) AUTH ON ( AUTH.RESOURCE_ID_ in ( RES.PROC_INST_ID_, PROC_EXECUTION.ID_, PROCDEF.KEY_, RES.TASK_ID_, '*') ) WHERE NAME_= ? and ( ( RES.TYPE_ is not null and RES.TYPE_ = ? and RES.TEXT_ is not null and RES.TEXT_ = ? ) ) and ( (RES.CASE_EXECUTION_ID_ IS NOT NULL) OR (AUTH.RESOURCE_ID_ IS NOT NULL) ) ) RES order by RES.ID_ asc LIMIT ? OFFSET ? 
04-Oct-2019 17:27:36.003 FEIN [http-nio-8080-exec-1] org.camunda.commons.logging.BaseLogger.logDebug ENGINE-13011 closing existing command context
04-Oct-2019 17:27:36.004 SCHWERWIEGEND [http-nio-8080-exec-1] org.camunda.commons.logging.BaseLogger.logError ENGINE-16004 Exception while closing command context: 
### Error querying database.  Cause: org.h2.jdbc.JdbcSQLException: Mehrdeutiger Feldname "NAME_"
Ambiguous column name "NAME_"; SQL statement:
select distinct RES.*
    
     

    from (
          select
            RES.*,
            ( 
  case
	  when
      RES.TASK_ID_ is not null
      and RES.EXECUTION_ID_ is not null
	  then EXECUTION.ACT_INST_ID_

    when
      RES.CASE_EXECUTION_ID_ is not null
    then RES.CASE_EXECUTION_ID_

    
	  when
	    EXECUTION.PARENT_ID_ is null
      and RES.IS_CONCURRENT_LOCAL_ = 0
	  then EXECUTION.ID_

    
	  when
	    EXECUTION.IS_SCOPE_ = 1
	    and EXECUTION.PARENT_ID_ is not null
      and RES.IS_CONCURRENT_LOCAL_ = 0
	  then PARENT_EXECUTION.ACT_INST_ID_

	  else EXECUTION.ACT_INST_ID_
	end
   ) ACT_INST_ID_

          from ACT_RU_VARIABLE RES

          left join ACT_RU_EXECUTION EXECUTION
          on RES.EXECUTION_ID_ = EXECUTION.ID_

          left join ACT_RU_EXECUTION PARENT_EXECUTION
          on EXECUTION.PARENT_ID_ = PARENT_EXECUTION.ID_

           
            left join ACT_RU_EXECUTION PROC_EXECUTION
            on PROC_EXECUTION.ID_ = RES.PROC_INST_ID_

            left join ACT_RE_PROCDEF PROCDEF
            on PROCDEF.ID_ = PROC_EXECUTION.PROC_DEF_ID_   
                   
               
     
     
     
     
    left JOIN (
    SELECT A.*
    FROM ACT_RU_AUTHORIZATION A
    WHERE A.TYPE_ < 2     
    AND ( A.USER_ID_ in ( ?, '*')           
     
      OR A.GROUP_ID_ IN  (  ? , ? , ? , ? ) 
     
    )
     
      AND (
       
         (  
          A.RESOURCE_TYPE_ = ? AND BITAND(A.PERMS_,?) = ?
         OR 
          A.RESOURCE_TYPE_ = ? AND BITAND(A.PERMS_,?) = ?
         OR 
          A.RESOURCE_TYPE_ = ? AND BITAND(A.PERMS_,?) = ?
         ) 
       
       
      )
       
    )
   
    
              AUTH ON (
              AUTH.RESOURCE_ID_ in (
                                    RES.PROC_INST_ID_,
                                    PROC_EXECUTION.ID_,
                                    PROCDEF.KEY_,
                                    RES.TASK_ID_,
                                    '*')
              )
             
           
           WHERE  
              
    
        NAME_= ?
      
    
  
              
              
              
                and
                

    
    (
    
      (  RES.TYPE_ is not null and RES.TYPE_ = ?
        
         
        
          and RES.TEXT_ is not null and 
		  RES.TEXT_
		  
    =
  
		  ? )
    
    )
  
              
            

            
    
      
          and (
          (RES.CASE_EXECUTION_ID_ IS NOT NULL)
          OR
          (AUTH.RESOURCE_ID_ IS NOT NULL)
            
          ) 
    ) RES
   
    order by RES.ID_ asc
    LIMIT ? OFFSET ? [90059-190]
...

Reason:

Authorization requires to join various tables including ACT_RE_PROCDEF
Both ACT_RE_PROCDEF and ACT_RU_VARIABLE have a NAME_ column
The query references NAME_ without a table prefix

Solution:

Explicitly specify the table prefix in the name matching clause, i.e. WHERE RES.NAME_ = ...
The current clause (without RES_.) was introduced via https://github.com/camunda/camunda-bpm-platform/blob/7.12.0-alpha4/engine/src/main/resources/org/camunda/bpm/engine/impl/mapping/entity/Commons.xml#L86-L96
Note that for the variable value matching we already have similar logic, so maybe we can do this in a similar way (https://github.com/camunda/camunda-bpm-platform/blob/7.12.0-alpha4/engine/src/main/resources/org/camunda/bpm/engine/impl/mapping/entity/VariableInstance.xml#L439)

Side notes:

Also test other queries that allow case-insensitive value matching
This is a regression introduced in ~~CAM-10647~~, so no non-alpha release is affected as of now

This is the controller panel for Smart Panels app

Thorben Lindhauer created issue - 04/Oct/19 5:52 PM

Thorben Lindhauer made changes - 04/Oct/19 5:55 PM

Description

Original: Scenario:

* Activated authorization
* Variable instance query
* Matching a variable by value (e.g. {{runtimeService.createVariableInstanceQuery().variableValueEquals("foo", "bar").list());}}

Current behavior:

* The SQL query is invalid and fails with the following exception:

{code}
04-Oct-2019 17:27:35.978 FEIN [http-nio-8080-exec-1] org.camunda.commons.logging.BaseLogger.logDebug ENGINE-13005 Starting command -------------------- VariableInstanceQueryImpl ----------------------
04-Oct-2019 17:27:35.978 FEIN [http-nio-8080-exec-1] org.camunda.commons.logging.BaseLogger.logDebug ENGINE-13009 opening new command context
04-Oct-2019 17:27:35.999 FEIN [http-nio-8080-exec-1] org.apache.ibatis.logging.jdbc.BaseJdbcLogger.debug ==> Preparing: select distinct RES.* from ( select RES.*, ( case when RES.TASK_ID_ is not null and RES.EXECUTION_ID_ is not null then EXECUTION.ACT_INST_ID_ when RES.CASE_EXECUTION_ID_ is not null then RES.CASE_EXECUTION_ID_ when EXECUTION.PARENT_ID_ is null and RES.IS_CONCURRENT_LOCAL_ = 0 then EXECUTION.ID_ when EXECUTION.IS_SCOPE_ = 1 and EXECUTION.PARENT_ID_ is not null and RES.IS_CONCURRENT_LOCAL_ = 0 then PARENT_EXECUTION.ACT_INST_ID_ else EXECUTION.ACT_INST_ID_ end ) ACT_INST_ID_ from ACT_RU_VARIABLE RES left join ACT_RU_EXECUTION EXECUTION on RES.EXECUTION_ID_ = EXECUTION.ID_ left join ACT_RU_EXECUTION PARENT_EXECUTION on EXECUTION.PARENT_ID_ = PARENT_EXECUTION.ID_ left join ACT_RU_EXECUTION PROC_EXECUTION on PROC_EXECUTION.ID_ = RES.PROC_INST_ID_ left join ACT_RE_PROCDEF PROCDEF on PROCDEF.ID_ = PROC_EXECUTION.PROC_DEF_ID_ left JOIN ( SELECT A.* FROM ACT_RU_AUTHORIZATION A WHERE A.TYPE_ < 2 AND ( A.USER_ID_ in ( ?, '*') OR A.GROUP_ID_ IN ( ? , ? , ? , ? ) ) AND ( ( A.RESOURCE_TYPE_ = ? AND BITAND(A.PERMS_,?) = ? OR A.RESOURCE_TYPE_ = ? AND BITAND(A.PERMS_,?) = ? OR A.RESOURCE_TYPE_ = ? AND BITAND(A.PERMS_,?) = ? ) ) ) AUTH ON ( AUTH.RESOURCE_ID_ in ( RES.PROC_INST_ID_, PROC_EXECUTION.ID_, PROCDEF.KEY_, RES.TASK_ID_, '*') ) WHERE NAME_= ? and ( ( RES.TYPE_ is not null and RES.TYPE_ = ? and RES.TEXT_ is not null and RES.TEXT_ = ? ) ) and ( (RES.CASE_EXECUTION_ID_ IS NOT NULL) OR (AUTH.RESOURCE_ID_ IS NOT NULL) ) ) RES order by RES.ID_ asc LIMIT ? OFFSET ?
04-Oct-2019 17:27:36.003 FEIN [http-nio-8080-exec-1] org.camunda.commons.logging.BaseLogger.logDebug ENGINE-13011 closing existing command context
04-Oct-2019 17:27:36.004 SCHWERWIEGEND [http-nio-8080-exec-1] org.camunda.commons.logging.BaseLogger.logError ENGINE-16004 Exception while closing command context:
### Error querying database. Cause: org.h2.jdbc.JdbcSQLException: Mehrdeutiger Feldname "NAME_"
Ambiguous column name "NAME_"; SQL statement:
select distinct RES.*



    from (
          select
            RES.*,
            (
  case
when
      RES.TASK_ID_ is not null
      and RES.EXECUTION_ID_ is not null
then EXECUTION.ACT_INST_ID_

    when
      RES.CASE_EXECUTION_ID_ is not null
    then RES.CASE_EXECUTION_ID_


when
EXECUTION.PARENT_ID_ is null
      and RES.IS_CONCURRENT_LOCAL_ = 0
then EXECUTION.ID_


when
EXECUTION.IS_SCOPE_ = 1
and EXECUTION.PARENT_ID_ is not null
      and RES.IS_CONCURRENT_LOCAL_ = 0
then PARENT_EXECUTION.ACT_INST_ID_

else EXECUTION.ACT_INST_ID_
end
   ) ACT_INST_ID_

          from ACT_RU_VARIABLE RES

          left join ACT_RU_EXECUTION EXECUTION
          on RES.EXECUTION_ID_ = EXECUTION.ID_

          left join ACT_RU_EXECUTION PARENT_EXECUTION
          on EXECUTION.PARENT_ID_ = PARENT_EXECUTION.ID_


            left join ACT_RU_EXECUTION PROC_EXECUTION
            on PROC_EXECUTION.ID_ = RES.PROC_INST_ID_

            left join ACT_RE_PROCDEF PROCDEF
            on PROCDEF.ID_ = PROC_EXECUTION.PROC_DEF_ID_






    left JOIN (
    SELECT A.*
    FROM ACT_RU_AUTHORIZATION A
    WHERE A.TYPE_ < 2
    AND ( A.USER_ID_ in ( ?, '*')

      OR A.GROUP_ID_ IN ( ? , ? , ? , ? )

    )

      AND (

         (
          A.RESOURCE_TYPE_ = ? AND BITAND(A.PERMS_,?) = ?
         OR
          A.RESOURCE_TYPE_ = ? AND BITAND(A.PERMS_,?) = ?
         OR
          A.RESOURCE_TYPE_ = ? AND BITAND(A.PERMS_,?) = ?
         )


      )

    )


              AUTH ON (
              AUTH.RESOURCE_ID_ in (
                                    RES.PROC_INST_ID_,
                                    PROC_EXECUTION.ID_,
                                    PROCDEF.KEY_,
                                    RES.TASK_ID_,
                                    '*')
              )


           WHERE


        NAME_= ?






                and



    (

      ( RES.TYPE_ is not null and RES.TYPE_ = ?



          and RES.TEXT_ is not null and
RES.TEXT_

    =

? )

    )







          and (
          (RES.CASE_EXECUTION_ID_ IS NOT NULL)
          OR
          (AUTH.RESOURCE_ID_ IS NOT NULL)

          )
    ) RES

    order by RES.ID_ asc
    LIMIT ? OFFSET ? [90059-190]
...
{code}

Reason:

* Authorization requires to join various tables including {{ACT_RE_PROCDEF}}
* Both {{ACT_RE_PROCDEF}} and {{ACT_RU_VARIABLE}} have a {{NAME_}} column

Solution:

* Explicitly specify the variable prefix in the name matching clause, i.e. {{WHERE RES.NAME_ = ...}}
* The current clause (without {{RES_.}}) was introduced via https://github.com/camunda/camunda-bpm-platform/blob/7.12.0-alpha4/engine/src/main/resources/org/camunda/bpm/engine/impl/mapping/entity/Commons.xml#L86-L96
* Note that for the variable value matching we already have logic that

Side notes:

* Also test other queries that allow case-insensitive value matching
* This is a regression introduced in ~~CAM-10647~~.

New: Scenario:

* Activated authorization
* Variable instance query
* Matching a variable by value (e.g. {{runtimeService.createVariableInstanceQuery().variableValueEquals("foo", "bar").list());}}

Current behavior:

* The SQL query is invalid and fails with the following exception:

{code}
04-Oct-2019 17:27:35.978 FEIN [http-nio-8080-exec-1] org.camunda.commons.logging.BaseLogger.logDebug ENGINE-13005 Starting command -------------------- VariableInstanceQueryImpl ----------------------
04-Oct-2019 17:27:35.978 FEIN [http-nio-8080-exec-1] org.camunda.commons.logging.BaseLogger.logDebug ENGINE-13009 opening new command context
04-Oct-2019 17:27:35.999 FEIN [http-nio-8080-exec-1] org.apache.ibatis.logging.jdbc.BaseJdbcLogger.debug ==> Preparing: select distinct RES.* from ( select RES.*, ( case when RES.TASK_ID_ is not null and RES.EXECUTION_ID_ is not null then EXECUTION.ACT_INST_ID_ when RES.CASE_EXECUTION_ID_ is not null then RES.CASE_EXECUTION_ID_ when EXECUTION.PARENT_ID_ is null and RES.IS_CONCURRENT_LOCAL_ = 0 then EXECUTION.ID_ when EXECUTION.IS_SCOPE_ = 1 and EXECUTION.PARENT_ID_ is not null and RES.IS_CONCURRENT_LOCAL_ = 0 then PARENT_EXECUTION.ACT_INST_ID_ else EXECUTION.ACT_INST_ID_ end ) ACT_INST_ID_ from ACT_RU_VARIABLE RES left join ACT_RU_EXECUTION EXECUTION on RES.EXECUTION_ID_ = EXECUTION.ID_ left join ACT_RU_EXECUTION PARENT_EXECUTION on EXECUTION.PARENT_ID_ = PARENT_EXECUTION.ID_ left join ACT_RU_EXECUTION PROC_EXECUTION on PROC_EXECUTION.ID_ = RES.PROC_INST_ID_ left join ACT_RE_PROCDEF PROCDEF on PROCDEF.ID_ = PROC_EXECUTION.PROC_DEF_ID_ left JOIN ( SELECT A.* FROM ACT_RU_AUTHORIZATION A WHERE A.TYPE_ < 2 AND ( A.USER_ID_ in ( ?, '*') OR A.GROUP_ID_ IN ( ? , ? , ? , ? ) ) AND ( ( A.RESOURCE_TYPE_ = ? AND BITAND(A.PERMS_,?) = ? OR A.RESOURCE_TYPE_ = ? AND BITAND(A.PERMS_,?) = ? OR A.RESOURCE_TYPE_ = ? AND BITAND(A.PERMS_,?) = ? ) ) ) AUTH ON ( AUTH.RESOURCE_ID_ in ( RES.PROC_INST_ID_, PROC_EXECUTION.ID_, PROCDEF.KEY_, RES.TASK_ID_, '*') ) WHERE NAME_= ? and ( ( RES.TYPE_ is not null and RES.TYPE_ = ? and RES.TEXT_ is not null and RES.TEXT_ = ? ) ) and ( (RES.CASE_EXECUTION_ID_ IS NOT NULL) OR (AUTH.RESOURCE_ID_ IS NOT NULL) ) ) RES order by RES.ID_ asc LIMIT ? OFFSET ?
04-Oct-2019 17:27:36.003 FEIN [http-nio-8080-exec-1] org.camunda.commons.logging.BaseLogger.logDebug ENGINE-13011 closing existing command context
04-Oct-2019 17:27:36.004 SCHWERWIEGEND [http-nio-8080-exec-1] org.camunda.commons.logging.BaseLogger.logError ENGINE-16004 Exception while closing command context:
### Error querying database. Cause: org.h2.jdbc.JdbcSQLException: Mehrdeutiger Feldname "NAME_"
Ambiguous column name "NAME_"; SQL statement:
select distinct RES.*



    from (
          select
            RES.*,
            (
  case
when
      RES.TASK_ID_ is not null
      and RES.EXECUTION_ID_ is not null
then EXECUTION.ACT_INST_ID_

    when
      RES.CASE_EXECUTION_ID_ is not null
    then RES.CASE_EXECUTION_ID_


when
EXECUTION.PARENT_ID_ is null
      and RES.IS_CONCURRENT_LOCAL_ = 0
then EXECUTION.ID_


when
EXECUTION.IS_SCOPE_ = 1
and EXECUTION.PARENT_ID_ is not null
      and RES.IS_CONCURRENT_LOCAL_ = 0
then PARENT_EXECUTION.ACT_INST_ID_

else EXECUTION.ACT_INST_ID_
end
   ) ACT_INST_ID_

          from ACT_RU_VARIABLE RES

          left join ACT_RU_EXECUTION EXECUTION
          on RES.EXECUTION_ID_ = EXECUTION.ID_

          left join ACT_RU_EXECUTION PARENT_EXECUTION
          on EXECUTION.PARENT_ID_ = PARENT_EXECUTION.ID_


            left join ACT_RU_EXECUTION PROC_EXECUTION
            on PROC_EXECUTION.ID_ = RES.PROC_INST_ID_

            left join ACT_RE_PROCDEF PROCDEF
            on PROCDEF.ID_ = PROC_EXECUTION.PROC_DEF_ID_






    left JOIN (
    SELECT A.*
    FROM ACT_RU_AUTHORIZATION A
    WHERE A.TYPE_ < 2
    AND ( A.USER_ID_ in ( ?, '*')

      OR A.GROUP_ID_ IN ( ? , ? , ? , ? )

    )

      AND (

         (
          A.RESOURCE_TYPE_ = ? AND BITAND(A.PERMS_,?) = ?
         OR
          A.RESOURCE_TYPE_ = ? AND BITAND(A.PERMS_,?) = ?
         OR
          A.RESOURCE_TYPE_ = ? AND BITAND(A.PERMS_,?) = ?
         )


      )

    )


              AUTH ON (
              AUTH.RESOURCE_ID_ in (
                                    RES.PROC_INST_ID_,
                                    PROC_EXECUTION.ID_,
                                    PROCDEF.KEY_,
                                    RES.TASK_ID_,
                                    '*')
              )


           WHERE


        NAME_= ?






                and



    (

      ( RES.TYPE_ is not null and RES.TYPE_ = ?



          and RES.TEXT_ is not null and
RES.TEXT_

    =

? )

    )







          and (
          (RES.CASE_EXECUTION_ID_ IS NOT NULL)
          OR
          (AUTH.RESOURCE_ID_ IS NOT NULL)

          )
    ) RES

    order by RES.ID_ asc
    LIMIT ? OFFSET ? [90059-190]
...
{code}

Reason:

* Authorization requires to join various tables including {{ACT_RE_PROCDEF}}
* Both {{ACT_RE_PROCDEF}} and {{ACT_RU_VARIABLE}} have a {{NAME_}} column
* The query references {{NAME_}} without a table prefix

Solution:

* Explicitly specify the table prefix in the name matching clause, i.e. {{WHERE RES.NAME_ = ...}}
* The current clause (without {{RES_.}}) was introduced via https://github.com/camunda/camunda-bpm-platform/blob/7.12.0-alpha4/engine/src/main/resources/org/camunda/bpm/engine/impl/mapping/entity/Commons.xml#L86-L96
* Note that for the variable value matching we already have similar logic, so maybe we can do this in a similar way (https://github.com/camunda/camunda-bpm-platform/blob/7.12.0-alpha4/engine/src/main/resources/org/camunda/bpm/engine/impl/mapping/entity/VariableInstance.xml#L439)

Side notes:

* Also test other queries that allow case-insensitive value matching
* This is a regression introduced in ~~CAM-10647~~, so no non-alpha release is affected as of now

Thorben Lindhauer made changes - 04/Oct/19 5:57 PM

Fix Version/s		New: 7.12.0 [ 15387 ]
Fix Version/s	Original: 7.12.0-alpha4 [ 15522 ]

Thorben Lindhauer made changes - 04/Oct/19 5:57 PM

Affects Version/s

New: 7.12.0-alpha4 [ 15522 ]

Thorben Lindhauer made changes - 04/Oct/19 5:57 PM

Remote Link

New: This issue links to "Page (camunda confluence)" [ 13704 ]

Thorben Lindhauer made changes - 04/Oct/19 5:59 PM

Description

Original: Scenario:

* Activated authorization
* Variable instance query
* Matching a variable by value (e.g. {{runtimeService.createVariableInstanceQuery().variableValueEquals("foo", "bar").list());}}

Current behavior:

* The SQL query is invalid and fails with the following exception:

{code}
04-Oct-2019 17:27:35.978 FEIN [http-nio-8080-exec-1] org.camunda.commons.logging.BaseLogger.logDebug ENGINE-13005 Starting command -------------------- VariableInstanceQueryImpl ----------------------
04-Oct-2019 17:27:35.978 FEIN [http-nio-8080-exec-1] org.camunda.commons.logging.BaseLogger.logDebug ENGINE-13009 opening new command context
04-Oct-2019 17:27:35.999 FEIN [http-nio-8080-exec-1] org.apache.ibatis.logging.jdbc.BaseJdbcLogger.debug ==> Preparing: select distinct RES.* from ( select RES.*, ( case when RES.TASK_ID_ is not null and RES.EXECUTION_ID_ is not null then EXECUTION.ACT_INST_ID_ when RES.CASE_EXECUTION_ID_ is not null then RES.CASE_EXECUTION_ID_ when EXECUTION.PARENT_ID_ is null and RES.IS_CONCURRENT_LOCAL_ = 0 then EXECUTION.ID_ when EXECUTION.IS_SCOPE_ = 1 and EXECUTION.PARENT_ID_ is not null and RES.IS_CONCURRENT_LOCAL_ = 0 then PARENT_EXECUTION.ACT_INST_ID_ else EXECUTION.ACT_INST_ID_ end ) ACT_INST_ID_ from ACT_RU_VARIABLE RES left join ACT_RU_EXECUTION EXECUTION on RES.EXECUTION_ID_ = EXECUTION.ID_ left join ACT_RU_EXECUTION PARENT_EXECUTION on EXECUTION.PARENT_ID_ = PARENT_EXECUTION.ID_ left join ACT_RU_EXECUTION PROC_EXECUTION on PROC_EXECUTION.ID_ = RES.PROC_INST_ID_ left join ACT_RE_PROCDEF PROCDEF on PROCDEF.ID_ = PROC_EXECUTION.PROC_DEF_ID_ left JOIN ( SELECT A.* FROM ACT_RU_AUTHORIZATION A WHERE A.TYPE_ < 2 AND ( A.USER_ID_ in ( ?, '*') OR A.GROUP_ID_ IN ( ? , ? , ? , ? ) ) AND ( ( A.RESOURCE_TYPE_ = ? AND BITAND(A.PERMS_,?) = ? OR A.RESOURCE_TYPE_ = ? AND BITAND(A.PERMS_,?) = ? OR A.RESOURCE_TYPE_ = ? AND BITAND(A.PERMS_,?) = ? ) ) ) AUTH ON ( AUTH.RESOURCE_ID_ in ( RES.PROC_INST_ID_, PROC_EXECUTION.ID_, PROCDEF.KEY_, RES.TASK_ID_, '*') ) WHERE NAME_= ? and ( ( RES.TYPE_ is not null and RES.TYPE_ = ? and RES.TEXT_ is not null and RES.TEXT_ = ? ) ) and ( (RES.CASE_EXECUTION_ID_ IS NOT NULL) OR (AUTH.RESOURCE_ID_ IS NOT NULL) ) ) RES order by RES.ID_ asc LIMIT ? OFFSET ?
04-Oct-2019 17:27:36.003 FEIN [http-nio-8080-exec-1] org.camunda.commons.logging.BaseLogger.logDebug ENGINE-13011 closing existing command context
04-Oct-2019 17:27:36.004 SCHWERWIEGEND [http-nio-8080-exec-1] org.camunda.commons.logging.BaseLogger.logError ENGINE-16004 Exception while closing command context:
### Error querying database. Cause: org.h2.jdbc.JdbcSQLException: Mehrdeutiger Feldname "NAME_"
Ambiguous column name "NAME_"; SQL statement:
select distinct RES.*



    from (
          select
            RES.*,
            (
  case
when
      RES.TASK_ID_ is not null
      and RES.EXECUTION_ID_ is not null
then EXECUTION.ACT_INST_ID_

    when
      RES.CASE_EXECUTION_ID_ is not null
    then RES.CASE_EXECUTION_ID_


when
EXECUTION.PARENT_ID_ is null
      and RES.IS_CONCURRENT_LOCAL_ = 0
then EXECUTION.ID_


when
EXECUTION.IS_SCOPE_ = 1
and EXECUTION.PARENT_ID_ is not null
      and RES.IS_CONCURRENT_LOCAL_ = 0
then PARENT_EXECUTION.ACT_INST_ID_

else EXECUTION.ACT_INST_ID_
end
   ) ACT_INST_ID_

          from ACT_RU_VARIABLE RES

          left join ACT_RU_EXECUTION EXECUTION
          on RES.EXECUTION_ID_ = EXECUTION.ID_

          left join ACT_RU_EXECUTION PARENT_EXECUTION
          on EXECUTION.PARENT_ID_ = PARENT_EXECUTION.ID_


            left join ACT_RU_EXECUTION PROC_EXECUTION
            on PROC_EXECUTION.ID_ = RES.PROC_INST_ID_

            left join ACT_RE_PROCDEF PROCDEF
            on PROCDEF.ID_ = PROC_EXECUTION.PROC_DEF_ID_






    left JOIN (
    SELECT A.*
    FROM ACT_RU_AUTHORIZATION A
    WHERE A.TYPE_ < 2
    AND ( A.USER_ID_ in ( ?, '*')

      OR A.GROUP_ID_ IN ( ? , ? , ? , ? )

    )

      AND (

         (
          A.RESOURCE_TYPE_ = ? AND BITAND(A.PERMS_,?) = ?
         OR
          A.RESOURCE_TYPE_ = ? AND BITAND(A.PERMS_,?) = ?
         OR
          A.RESOURCE_TYPE_ = ? AND BITAND(A.PERMS_,?) = ?
         )


      )

    )


              AUTH ON (
              AUTH.RESOURCE_ID_ in (
                                    RES.PROC_INST_ID_,
                                    PROC_EXECUTION.ID_,
                                    PROCDEF.KEY_,
                                    RES.TASK_ID_,
                                    '*')
              )


           WHERE


        NAME_= ?






                and



    (

      ( RES.TYPE_ is not null and RES.TYPE_ = ?



          and RES.TEXT_ is not null and
RES.TEXT_

    =

? )

    )







          and (
          (RES.CASE_EXECUTION_ID_ IS NOT NULL)
          OR
          (AUTH.RESOURCE_ID_ IS NOT NULL)

          )
    ) RES

    order by RES.ID_ asc
    LIMIT ? OFFSET ? [90059-190]
...
{code}

Reason:

* Authorization requires to join various tables including {{ACT_RE_PROCDEF}}
* Both {{ACT_RE_PROCDEF}} and {{ACT_RU_VARIABLE}} have a {{NAME_}} column
* The query references {{NAME_}} without a table prefix

Solution:

* Explicitly specify the table prefix in the name matching clause, i.e. {{WHERE RES.NAME_ = ...}}
* The current clause (without {{RES_.}}) was introduced via https://github.com/camunda/camunda-bpm-platform/blob/7.12.0-alpha4/engine/src/main/resources/org/camunda/bpm/engine/impl/mapping/entity/Commons.xml#L86-L96
* Note that for the variable value matching we already have similar logic, so maybe we can do this in a similar way (https://github.com/camunda/camunda-bpm-platform/blob/7.12.0-alpha4/engine/src/main/resources/org/camunda/bpm/engine/impl/mapping/entity/VariableInstance.xml#L439)

Side notes:

* Also test other queries that allow case-insensitive value matching
* This is a regression introduced in ~~CAM-10647~~, so no non-alpha release is affected as of now

New: Scenario:

* Activated authorization
* Variable instance query
* Matching a variable by value (e.g. {{runtimeService.createVariableInstanceQuery().variableValueEquals("foo", "bar").list());}}

Current behavior:

* The SQL query is invalid and fails with the following exception:

{code}
04-Oct-2019 17:27:35.978 FEIN [http-nio-8080-exec-1] org.camunda.commons.logging.BaseLogger.logDebug ENGINE-13005 Starting command -------------------- VariableInstanceQueryImpl ----------------------
04-Oct-2019 17:27:35.978 FEIN [http-nio-8080-exec-1] org.camunda.commons.logging.BaseLogger.logDebug ENGINE-13009 opening new command context
04-Oct-2019 17:27:35.999 FEIN [http-nio-8080-exec-1] org.apache.ibatis.logging.jdbc.BaseJdbcLogger.debug ==> Preparing: select distinct RES.* from ( select RES.*, ( case when RES.TASK_ID_ is not null and RES.EXECUTION_ID_ is not null then EXECUTION.ACT_INST_ID_ when RES.CASE_EXECUTION_ID_ is not null then RES.CASE_EXECUTION_ID_ when EXECUTION.PARENT_ID_ is null and RES.IS_CONCURRENT_LOCAL_ = 0 then EXECUTION.ID_ when EXECUTION.IS_SCOPE_ = 1 and EXECUTION.PARENT_ID_ is not null and RES.IS_CONCURRENT_LOCAL_ = 0 then PARENT_EXECUTION.ACT_INST_ID_ else EXECUTION.ACT_INST_ID_ end ) ACT_INST_ID_ from ACT_RU_VARIABLE RES left join ACT_RU_EXECUTION EXECUTION on RES.EXECUTION_ID_ = EXECUTION.ID_ left join ACT_RU_EXECUTION PARENT_EXECUTION on EXECUTION.PARENT_ID_ = PARENT_EXECUTION.ID_ left join ACT_RU_EXECUTION PROC_EXECUTION on PROC_EXECUTION.ID_ = RES.PROC_INST_ID_ left join ACT_RE_PROCDEF PROCDEF on PROCDEF.ID_ = PROC_EXECUTION.PROC_DEF_ID_ left JOIN ( SELECT A.* FROM ACT_RU_AUTHORIZATION A WHERE A.TYPE_ < 2 AND ( A.USER_ID_ in ( ?, '*') OR A.GROUP_ID_ IN ( ? , ? , ? , ? ) ) AND ( ( A.RESOURCE_TYPE_ = ? AND BITAND(A.PERMS_,?) = ? OR A.RESOURCE_TYPE_ = ? AND BITAND(A.PERMS_,?) = ? OR A.RESOURCE_TYPE_ = ? AND BITAND(A.PERMS_,?) = ? ) ) ) AUTH ON ( AUTH.RESOURCE_ID_ in ( RES.PROC_INST_ID_, PROC_EXECUTION.ID_, PROCDEF.KEY_, RES.TASK_ID_, '*') ) WHERE NAME_= ? and ( ( RES.TYPE_ is not null and RES.TYPE_ = ? and RES.TEXT_ is not null and RES.TEXT_ = ? ) ) and ( (RES.CASE_EXECUTION_ID_ IS NOT NULL) OR (AUTH.RESOURCE_ID_ IS NOT NULL) ) ) RES order by RES.ID_ asc LIMIT ? OFFSET ?
04-Oct-2019 17:27:36.003 FEIN [http-nio-8080-exec-1] org.camunda.commons.logging.BaseLogger.logDebug ENGINE-13011 closing existing command context
04-Oct-2019 17:27:36.004 SCHWERWIEGEND [http-nio-8080-exec-1] org.camunda.commons.logging.BaseLogger.logError ENGINE-16004 Exception while closing command context:
### Error querying database. Cause: org.h2.jdbc.JdbcSQLException: Mehrdeutiger Feldname "NAME_"
Ambiguous column name "NAME_"; SQL statement:
select distinct RES.*



    from (
          select
            RES.*,
            (
  case
when
      RES.TASK_ID_ is not null
      and RES.EXECUTION_ID_ is not null
then EXECUTION.ACT_INST_ID_

    when
      RES.CASE_EXECUTION_ID_ is not null
    then RES.CASE_EXECUTION_ID_


when
EXECUTION.PARENT_ID_ is null
      and RES.IS_CONCURRENT_LOCAL_ = 0
then EXECUTION.ID_


when
EXECUTION.IS_SCOPE_ = 1
and EXECUTION.PARENT_ID_ is not null
      and RES.IS_CONCURRENT_LOCAL_ = 0
then PARENT_EXECUTION.ACT_INST_ID_

else EXECUTION.ACT_INST_ID_
end
   ) ACT_INST_ID_

          from ACT_RU_VARIABLE RES

          left join ACT_RU_EXECUTION EXECUTION
          on RES.EXECUTION_ID_ = EXECUTION.ID_

          left join ACT_RU_EXECUTION PARENT_EXECUTION
          on EXECUTION.PARENT_ID_ = PARENT_EXECUTION.ID_


            left join ACT_RU_EXECUTION PROC_EXECUTION
            on PROC_EXECUTION.ID_ = RES.PROC_INST_ID_

            left join ACT_RE_PROCDEF PROCDEF
            on PROCDEF.ID_ = PROC_EXECUTION.PROC_DEF_ID_






    left JOIN (
    SELECT A.*
    FROM ACT_RU_AUTHORIZATION A
    WHERE A.TYPE_ < 2
    AND ( A.USER_ID_ in ( ?, '*')

      OR A.GROUP_ID_ IN ( ? , ? , ? , ? )

    )

      AND (

         (
          A.RESOURCE_TYPE_ = ? AND BITAND(A.PERMS_,?) = ?
         OR
          A.RESOURCE_TYPE_ = ? AND BITAND(A.PERMS_,?) = ?
         OR
          A.RESOURCE_TYPE_ = ? AND BITAND(A.PERMS_,?) = ?
         )


      )

    )


              AUTH ON (
              AUTH.RESOURCE_ID_ in (
                                    RES.PROC_INST_ID_,
                                    PROC_EXECUTION.ID_,
                                    PROCDEF.KEY_,
                                    RES.TASK_ID_,
                                    '*')
              )


           WHERE


        NAME_= ?






                and



    (

      ( RES.TYPE_ is not null and RES.TYPE_ = ?



          and RES.TEXT_ is not null and
RES.TEXT_

    =

? )

    )







          and (
          (RES.CASE_EXECUTION_ID_ IS NOT NULL)
          OR
          (AUTH.RESOURCE_ID_ IS NOT NULL)

          )
    ) RES

    order by RES.ID_ asc
    LIMIT ? OFFSET ? [90059-190]
...
{code}

Reason:

* Authorization requires to join various tables including {{ACT_RE_PROCDEF}}
* Both {{ACT_RE_PROCDEF}} and {{ACT_RU_VARIABLE}} have a {{NAME_}} column
* The query references {{NAME_}} without a table prefix

Solution:

* Explicitly specify the table prefix in the name matching clause, i.e. {{WHERE RES.NAME_ = ...}}
* The current clause (without {{RES_.}}) was introduced via https://github.com/camunda/camunda-bpm-platform/blob/7.12.0-alpha4/engine/src/main/resources/org/camunda/bpm/engine/impl/mapping/entity/Commons.xml#L86-L96
* Note that for the variable value matching we already have similar logic, so maybe we can do this in a similar way (https://github.com/camunda/camunda-bpm-platform/blob/7.12.0-alpha4/engine/src/main/resources/org/camunda/bpm/engine/impl/mapping/entity/VariableInstance.xml#L439)

Side notes:

* Also test and fix other queries that allow case-insensitive value matching
* This is a regression introduced in ~~CAM-10647~~, so no non-alpha release is affected as of now

Thorben Lindhauer made changes - 04/Oct/19 6:01 PM

Description

Original: Scenario:

* Activated authorization
* Variable instance query
* Matching a variable by value (e.g. {{runtimeService.createVariableInstanceQuery().variableValueEquals("foo", "bar").list());}}

Current behavior:

* The SQL query is invalid and fails with the following exception:

{code}
04-Oct-2019 17:27:35.978 FEIN [http-nio-8080-exec-1] org.camunda.commons.logging.BaseLogger.logDebug ENGINE-13005 Starting command -------------------- VariableInstanceQueryImpl ----------------------
04-Oct-2019 17:27:35.978 FEIN [http-nio-8080-exec-1] org.camunda.commons.logging.BaseLogger.logDebug ENGINE-13009 opening new command context
04-Oct-2019 17:27:35.999 FEIN [http-nio-8080-exec-1] org.apache.ibatis.logging.jdbc.BaseJdbcLogger.debug ==> Preparing: select distinct RES.* from ( select RES.*, ( case when RES.TASK_ID_ is not null and RES.EXECUTION_ID_ is not null then EXECUTION.ACT_INST_ID_ when RES.CASE_EXECUTION_ID_ is not null then RES.CASE_EXECUTION_ID_ when EXECUTION.PARENT_ID_ is null and RES.IS_CONCURRENT_LOCAL_ = 0 then EXECUTION.ID_ when EXECUTION.IS_SCOPE_ = 1 and EXECUTION.PARENT_ID_ is not null and RES.IS_CONCURRENT_LOCAL_ = 0 then PARENT_EXECUTION.ACT_INST_ID_ else EXECUTION.ACT_INST_ID_ end ) ACT_INST_ID_ from ACT_RU_VARIABLE RES left join ACT_RU_EXECUTION EXECUTION on RES.EXECUTION_ID_ = EXECUTION.ID_ left join ACT_RU_EXECUTION PARENT_EXECUTION on EXECUTION.PARENT_ID_ = PARENT_EXECUTION.ID_ left join ACT_RU_EXECUTION PROC_EXECUTION on PROC_EXECUTION.ID_ = RES.PROC_INST_ID_ left join ACT_RE_PROCDEF PROCDEF on PROCDEF.ID_ = PROC_EXECUTION.PROC_DEF_ID_ left JOIN ( SELECT A.* FROM ACT_RU_AUTHORIZATION A WHERE A.TYPE_ < 2 AND ( A.USER_ID_ in ( ?, '*') OR A.GROUP_ID_ IN ( ? , ? , ? , ? ) ) AND ( ( A.RESOURCE_TYPE_ = ? AND BITAND(A.PERMS_,?) = ? OR A.RESOURCE_TYPE_ = ? AND BITAND(A.PERMS_,?) = ? OR A.RESOURCE_TYPE_ = ? AND BITAND(A.PERMS_,?) = ? ) ) ) AUTH ON ( AUTH.RESOURCE_ID_ in ( RES.PROC_INST_ID_, PROC_EXECUTION.ID_, PROCDEF.KEY_, RES.TASK_ID_, '*') ) WHERE NAME_= ? and ( ( RES.TYPE_ is not null and RES.TYPE_ = ? and RES.TEXT_ is not null and RES.TEXT_ = ? ) ) and ( (RES.CASE_EXECUTION_ID_ IS NOT NULL) OR (AUTH.RESOURCE_ID_ IS NOT NULL) ) ) RES order by RES.ID_ asc LIMIT ? OFFSET ?
04-Oct-2019 17:27:36.003 FEIN [http-nio-8080-exec-1] org.camunda.commons.logging.BaseLogger.logDebug ENGINE-13011 closing existing command context
04-Oct-2019 17:27:36.004 SCHWERWIEGEND [http-nio-8080-exec-1] org.camunda.commons.logging.BaseLogger.logError ENGINE-16004 Exception while closing command context:
### Error querying database. Cause: org.h2.jdbc.JdbcSQLException: Mehrdeutiger Feldname "NAME_"
Ambiguous column name "NAME_"; SQL statement:
select distinct RES.*



    from (
          select
            RES.*,
            (
  case
when
      RES.TASK_ID_ is not null
      and RES.EXECUTION_ID_ is not null
then EXECUTION.ACT_INST_ID_

    when
      RES.CASE_EXECUTION_ID_ is not null
    then RES.CASE_EXECUTION_ID_


when
EXECUTION.PARENT_ID_ is null
      and RES.IS_CONCURRENT_LOCAL_ = 0
then EXECUTION.ID_


when
EXECUTION.IS_SCOPE_ = 1
and EXECUTION.PARENT_ID_ is not null
      and RES.IS_CONCURRENT_LOCAL_ = 0
then PARENT_EXECUTION.ACT_INST_ID_

else EXECUTION.ACT_INST_ID_
end
   ) ACT_INST_ID_

          from ACT_RU_VARIABLE RES

          left join ACT_RU_EXECUTION EXECUTION
          on RES.EXECUTION_ID_ = EXECUTION.ID_

          left join ACT_RU_EXECUTION PARENT_EXECUTION
          on EXECUTION.PARENT_ID_ = PARENT_EXECUTION.ID_


            left join ACT_RU_EXECUTION PROC_EXECUTION
            on PROC_EXECUTION.ID_ = RES.PROC_INST_ID_

            left join ACT_RE_PROCDEF PROCDEF
            on PROCDEF.ID_ = PROC_EXECUTION.PROC_DEF_ID_






    left JOIN (
    SELECT A.*
    FROM ACT_RU_AUTHORIZATION A
    WHERE A.TYPE_ < 2
    AND ( A.USER_ID_ in ( ?, '*')

      OR A.GROUP_ID_ IN ( ? , ? , ? , ? )

    )

      AND (

         (
          A.RESOURCE_TYPE_ = ? AND BITAND(A.PERMS_,?) = ?
         OR
          A.RESOURCE_TYPE_ = ? AND BITAND(A.PERMS_,?) = ?
         OR
          A.RESOURCE_TYPE_ = ? AND BITAND(A.PERMS_,?) = ?
         )


      )

    )


              AUTH ON (
              AUTH.RESOURCE_ID_ in (
                                    RES.PROC_INST_ID_,
                                    PROC_EXECUTION.ID_,
                                    PROCDEF.KEY_,
                                    RES.TASK_ID_,
                                    '*')
              )


           WHERE


        NAME_= ?






                and



    (

      ( RES.TYPE_ is not null and RES.TYPE_ = ?



          and RES.TEXT_ is not null and
RES.TEXT_

    =

? )

    )







          and (
          (RES.CASE_EXECUTION_ID_ IS NOT NULL)
          OR
          (AUTH.RESOURCE_ID_ IS NOT NULL)

          )
    ) RES

    order by RES.ID_ asc
    LIMIT ? OFFSET ? [90059-190]
...
{code}

Reason:

* Authorization requires to join various tables including {{ACT_RE_PROCDEF}}
* Both {{ACT_RE_PROCDEF}} and {{ACT_RU_VARIABLE}} have a {{NAME_}} column
* The query references {{NAME_}} without a table prefix

Solution:

* Explicitly specify the table prefix in the name matching clause, i.e. {{WHERE RES.NAME_ = ...}}
* The current clause (without {{RES_.}}) was introduced via https://github.com/camunda/camunda-bpm-platform/blob/7.12.0-alpha4/engine/src/main/resources/org/camunda/bpm/engine/impl/mapping/entity/Commons.xml#L86-L96
* Note that for the variable value matching we already have similar logic, so maybe we can do this in a similar way (https://github.com/camunda/camunda-bpm-platform/blob/7.12.0-alpha4/engine/src/main/resources/org/camunda/bpm/engine/impl/mapping/entity/VariableInstance.xml#L439)

Side notes:

* Also test and fix other queries that allow case-insensitive value matching
* This is a regression introduced in ~~CAM-10647~~, so no non-alpha release is affected as of now

New: Scenario:

* Activated authorization
* Variable instance query
* Matching a variable by value (e.g. {{runtimeService.createVariableInstanceQuery().variableValueEquals("foo", "bar").list());}}

Current behavior:

* The SQL query is invalid and fails with the following exception:

{code}
04-Oct-2019 17:27:35.978 FEIN [http-nio-8080-exec-1] org.camunda.commons.logging.BaseLogger.logDebug ENGINE-13005 Starting command -------------------- VariableInstanceQueryImpl ----------------------
04-Oct-2019 17:27:35.978 FEIN [http-nio-8080-exec-1] org.camunda.commons.logging.BaseLogger.logDebug ENGINE-13009 opening new command context
04-Oct-2019 17:27:35.999 FEIN [http-nio-8080-exec-1] org.apache.ibatis.logging.jdbc.BaseJdbcLogger.debug ==> Preparing: select distinct RES.* from ( select RES.*, ( case when RES.TASK_ID_ is not null and RES.EXECUTION_ID_ is not null then EXECUTION.ACT_INST_ID_ when RES.CASE_EXECUTION_ID_ is not null then RES.CASE_EXECUTION_ID_ when EXECUTION.PARENT_ID_ is null and RES.IS_CONCURRENT_LOCAL_ = 0 then EXECUTION.ID_ when EXECUTION.IS_SCOPE_ = 1 and EXECUTION.PARENT_ID_ is not null and RES.IS_CONCURRENT_LOCAL_ = 0 then PARENT_EXECUTION.ACT_INST_ID_ else EXECUTION.ACT_INST_ID_ end ) ACT_INST_ID_ from ACT_RU_VARIABLE RES left join ACT_RU_EXECUTION EXECUTION on RES.EXECUTION_ID_ = EXECUTION.ID_ left join ACT_RU_EXECUTION PARENT_EXECUTION on EXECUTION.PARENT_ID_ = PARENT_EXECUTION.ID_ left join ACT_RU_EXECUTION PROC_EXECUTION on PROC_EXECUTION.ID_ = RES.PROC_INST_ID_ left join ACT_RE_PROCDEF PROCDEF on PROCDEF.ID_ = PROC_EXECUTION.PROC_DEF_ID_ left JOIN ( SELECT A.* FROM ACT_RU_AUTHORIZATION A WHERE A.TYPE_ < 2 AND ( A.USER_ID_ in ( ?, '*') OR A.GROUP_ID_ IN ( ? , ? , ? , ? ) ) AND ( ( A.RESOURCE_TYPE_ = ? AND BITAND(A.PERMS_,?) = ? OR A.RESOURCE_TYPE_ = ? AND BITAND(A.PERMS_,?) = ? OR A.RESOURCE_TYPE_ = ? AND BITAND(A.PERMS_,?) = ? ) ) ) AUTH ON ( AUTH.RESOURCE_ID_ in ( RES.PROC_INST_ID_, PROC_EXECUTION.ID_, PROCDEF.KEY_, RES.TASK_ID_, '*') ) WHERE NAME_= ? and ( ( RES.TYPE_ is not null and RES.TYPE_ = ? and RES.TEXT_ is not null and RES.TEXT_ = ? ) ) and ( (RES.CASE_EXECUTION_ID_ IS NOT NULL) OR (AUTH.RESOURCE_ID_ IS NOT NULL) ) ) RES order by RES.ID_ asc LIMIT ? OFFSET ?
04-Oct-2019 17:27:36.003 FEIN [http-nio-8080-exec-1] org.camunda.commons.logging.BaseLogger.logDebug ENGINE-13011 closing existing command context
04-Oct-2019 17:27:36.004 SCHWERWIEGEND [http-nio-8080-exec-1] org.camunda.commons.logging.BaseLogger.logError ENGINE-16004 Exception while closing command context:
### Error querying database. Cause: org.h2.jdbc.JdbcSQLException: Mehrdeutiger Feldname "NAME_"
Ambiguous column name "NAME_"; SQL statement:
select distinct RES.*



    from (
          select
            RES.*,
            (
  case
when
      RES.TASK_ID_ is not null
      and RES.EXECUTION_ID_ is not null
then EXECUTION.ACT_INST_ID_

    when
      RES.CASE_EXECUTION_ID_ is not null
    then RES.CASE_EXECUTION_ID_


when
EXECUTION.PARENT_ID_ is null
      and RES.IS_CONCURRENT_LOCAL_ = 0
then EXECUTION.ID_


when
EXECUTION.IS_SCOPE_ = 1
and EXECUTION.PARENT_ID_ is not null
      and RES.IS_CONCURRENT_LOCAL_ = 0
then PARENT_EXECUTION.ACT_INST_ID_

else EXECUTION.ACT_INST_ID_
end
   ) ACT_INST_ID_

          from ACT_RU_VARIABLE RES

          left join ACT_RU_EXECUTION EXECUTION
          on RES.EXECUTION_ID_ = EXECUTION.ID_

          left join ACT_RU_EXECUTION PARENT_EXECUTION
          on EXECUTION.PARENT_ID_ = PARENT_EXECUTION.ID_


            left join ACT_RU_EXECUTION PROC_EXECUTION
            on PROC_EXECUTION.ID_ = RES.PROC_INST_ID_

            left join ACT_RE_PROCDEF PROCDEF
            on PROCDEF.ID_ = PROC_EXECUTION.PROC_DEF_ID_






    left JOIN (
    SELECT A.*
    FROM ACT_RU_AUTHORIZATION A
    WHERE A.TYPE_ < 2
    AND ( A.USER_ID_ in ( ?, '*')

      OR A.GROUP_ID_ IN ( ? , ? , ? , ? )

    )

      AND (

         (
          A.RESOURCE_TYPE_ = ? AND BITAND(A.PERMS_,?) = ?
         OR
          A.RESOURCE_TYPE_ = ? AND BITAND(A.PERMS_,?) = ?
         OR
          A.RESOURCE_TYPE_ = ? AND BITAND(A.PERMS_,?) = ?
         )


      )

    )


              AUTH ON (
              AUTH.RESOURCE_ID_ in (
                                    RES.PROC_INST_ID_,
                                    PROC_EXECUTION.ID_,
                                    PROCDEF.KEY_,
                                    RES.TASK_ID_,
                                    '*')
              )


           WHERE


        NAME_= ?






                and



    (

      ( RES.TYPE_ is not null and RES.TYPE_ = ?



          and RES.TEXT_ is not null and
RES.TEXT_

    =

? )

    )







          and (
          (RES.CASE_EXECUTION_ID_ IS NOT NULL)
          OR
          (AUTH.RESOURCE_ID_ IS NOT NULL)

          )
    ) RES

    order by RES.ID_ asc
    LIMIT ? OFFSET ? [90059-190]
...
{code}

Reason:

* Authorization requires to join various tables including {{ACT_RE_PROCDEF}}
* Both {{ACT_RE_PROCDEF}} and {{ACT_RU_VARIABLE}} have a {{NAME_}} column
* The query references {{NAME_}} without a table prefix

Solution:

* Explicitly specify the table prefix in the name matching clause, i.e. {{WHERE RES.NAME_ = ...}}
* The current clause (without {{RES_.}}) was introduced via https://github.com/camunda/camunda-bpm-platform/blob/7.12.0-alpha4/engine/src/main/resources/org/camunda/bpm/engine/impl/mapping/entity/Commons.xml#L86-L96
* Note that for the variable value matching we already have similar logic, so maybe we can do this in a similar way (https://github.com/camunda/camunda-bpm-platform/blob/7.12.0-alpha4/engine/src/main/resources/org/camunda/bpm/engine/impl/mapping/entity/VariableInstance.xml#L439)

Side notes:

* Also test and fix other queries that allow case-insensitive value matching
* Failing test: https://github.com/camunda/camunda-bpm-platform/commit/e7713794363bb39a352b3c6baf3e6090a30c6b0c
* This is a regression introduced in ~~CAM-10647~~, so no non-alpha release is affected as of now

Thorben Lindhauer made changes - 04/Oct/19 6:05 PM

Description

Original: Scenario:

* Activated authorization
* Variable instance query
* Matching a variable by value (e.g. {{runtimeService.createVariableInstanceQuery().variableValueEquals("foo", "bar").list());}}

Current behavior:

* The SQL query is invalid and fails with the following exception:

{code}
04-Oct-2019 17:27:35.978 FEIN [http-nio-8080-exec-1] org.camunda.commons.logging.BaseLogger.logDebug ENGINE-13005 Starting command -------------------- VariableInstanceQueryImpl ----------------------
04-Oct-2019 17:27:35.978 FEIN [http-nio-8080-exec-1] org.camunda.commons.logging.BaseLogger.logDebug ENGINE-13009 opening new command context
04-Oct-2019 17:27:35.999 FEIN [http-nio-8080-exec-1] org.apache.ibatis.logging.jdbc.BaseJdbcLogger.debug ==> Preparing: select distinct RES.* from ( select RES.*, ( case when RES.TASK_ID_ is not null and RES.EXECUTION_ID_ is not null then EXECUTION.ACT_INST_ID_ when RES.CASE_EXECUTION_ID_ is not null then RES.CASE_EXECUTION_ID_ when EXECUTION.PARENT_ID_ is null and RES.IS_CONCURRENT_LOCAL_ = 0 then EXECUTION.ID_ when EXECUTION.IS_SCOPE_ = 1 and EXECUTION.PARENT_ID_ is not null and RES.IS_CONCURRENT_LOCAL_ = 0 then PARENT_EXECUTION.ACT_INST_ID_ else EXECUTION.ACT_INST_ID_ end ) ACT_INST_ID_ from ACT_RU_VARIABLE RES left join ACT_RU_EXECUTION EXECUTION on RES.EXECUTION_ID_ = EXECUTION.ID_ left join ACT_RU_EXECUTION PARENT_EXECUTION on EXECUTION.PARENT_ID_ = PARENT_EXECUTION.ID_ left join ACT_RU_EXECUTION PROC_EXECUTION on PROC_EXECUTION.ID_ = RES.PROC_INST_ID_ left join ACT_RE_PROCDEF PROCDEF on PROCDEF.ID_ = PROC_EXECUTION.PROC_DEF_ID_ left JOIN ( SELECT A.* FROM ACT_RU_AUTHORIZATION A WHERE A.TYPE_ < 2 AND ( A.USER_ID_ in ( ?, '*') OR A.GROUP_ID_ IN ( ? , ? , ? , ? ) ) AND ( ( A.RESOURCE_TYPE_ = ? AND BITAND(A.PERMS_,?) = ? OR A.RESOURCE_TYPE_ = ? AND BITAND(A.PERMS_,?) = ? OR A.RESOURCE_TYPE_ = ? AND BITAND(A.PERMS_,?) = ? ) ) ) AUTH ON ( AUTH.RESOURCE_ID_ in ( RES.PROC_INST_ID_, PROC_EXECUTION.ID_, PROCDEF.KEY_, RES.TASK_ID_, '*') ) WHERE NAME_= ? and ( ( RES.TYPE_ is not null and RES.TYPE_ = ? and RES.TEXT_ is not null and RES.TEXT_ = ? ) ) and ( (RES.CASE_EXECUTION_ID_ IS NOT NULL) OR (AUTH.RESOURCE_ID_ IS NOT NULL) ) ) RES order by RES.ID_ asc LIMIT ? OFFSET ?
04-Oct-2019 17:27:36.003 FEIN [http-nio-8080-exec-1] org.camunda.commons.logging.BaseLogger.logDebug ENGINE-13011 closing existing command context
04-Oct-2019 17:27:36.004 SCHWERWIEGEND [http-nio-8080-exec-1] org.camunda.commons.logging.BaseLogger.logError ENGINE-16004 Exception while closing command context:
### Error querying database. Cause: org.h2.jdbc.JdbcSQLException: Mehrdeutiger Feldname "NAME_"
Ambiguous column name "NAME_"; SQL statement:
select distinct RES.*



    from (
          select
            RES.*,
            (
  case
when
      RES.TASK_ID_ is not null
      and RES.EXECUTION_ID_ is not null
then EXECUTION.ACT_INST_ID_

    when
      RES.CASE_EXECUTION_ID_ is not null
    then RES.CASE_EXECUTION_ID_


when
EXECUTION.PARENT_ID_ is null
      and RES.IS_CONCURRENT_LOCAL_ = 0
then EXECUTION.ID_


when
EXECUTION.IS_SCOPE_ = 1
and EXECUTION.PARENT_ID_ is not null
      and RES.IS_CONCURRENT_LOCAL_ = 0
then PARENT_EXECUTION.ACT_INST_ID_

else EXECUTION.ACT_INST_ID_
end
   ) ACT_INST_ID_

          from ACT_RU_VARIABLE RES

          left join ACT_RU_EXECUTION EXECUTION
          on RES.EXECUTION_ID_ = EXECUTION.ID_

          left join ACT_RU_EXECUTION PARENT_EXECUTION
          on EXECUTION.PARENT_ID_ = PARENT_EXECUTION.ID_


            left join ACT_RU_EXECUTION PROC_EXECUTION
            on PROC_EXECUTION.ID_ = RES.PROC_INST_ID_

            left join ACT_RE_PROCDEF PROCDEF
            on PROCDEF.ID_ = PROC_EXECUTION.PROC_DEF_ID_






    left JOIN (
    SELECT A.*
    FROM ACT_RU_AUTHORIZATION A
    WHERE A.TYPE_ < 2
    AND ( A.USER_ID_ in ( ?, '*')

      OR A.GROUP_ID_ IN ( ? , ? , ? , ? )

    )

      AND (

         (
          A.RESOURCE_TYPE_ = ? AND BITAND(A.PERMS_,?) = ?
         OR
          A.RESOURCE_TYPE_ = ? AND BITAND(A.PERMS_,?) = ?
         OR
          A.RESOURCE_TYPE_ = ? AND BITAND(A.PERMS_,?) = ?
         )


      )

    )


              AUTH ON (
              AUTH.RESOURCE_ID_ in (
                                    RES.PROC_INST_ID_,
                                    PROC_EXECUTION.ID_,
                                    PROCDEF.KEY_,
                                    RES.TASK_ID_,
                                    '*')
              )


           WHERE


        NAME_= ?






                and



    (

      ( RES.TYPE_ is not null and RES.TYPE_ = ?



          and RES.TEXT_ is not null and
RES.TEXT_

    =

? )

    )







          and (
          (RES.CASE_EXECUTION_ID_ IS NOT NULL)
          OR
          (AUTH.RESOURCE_ID_ IS NOT NULL)

          )
    ) RES

    order by RES.ID_ asc
    LIMIT ? OFFSET ? [90059-190]
...
{code}

Reason:

* Authorization requires to join various tables including {{ACT_RE_PROCDEF}}
* Both {{ACT_RE_PROCDEF}} and {{ACT_RU_VARIABLE}} have a {{NAME_}} column
* The query references {{NAME_}} without a table prefix

Solution:

* Explicitly specify the table prefix in the name matching clause, i.e. {{WHERE RES.NAME_ = ...}}
* The current clause (without {{RES_.}}) was introduced via https://github.com/camunda/camunda-bpm-platform/blob/7.12.0-alpha4/engine/src/main/resources/org/camunda/bpm/engine/impl/mapping/entity/Commons.xml#L86-L96
* Note that for the variable value matching we already have similar logic, so maybe we can do this in a similar way (https://github.com/camunda/camunda-bpm-platform/blob/7.12.0-alpha4/engine/src/main/resources/org/camunda/bpm/engine/impl/mapping/entity/VariableInstance.xml#L439)

Side notes:

* Also test and fix other queries that allow case-insensitive value matching
* Failing test: https://github.com/camunda/camunda-bpm-platform/commit/e7713794363bb39a352b3c6baf3e6090a30c6b0c
* This is a regression introduced in ~~CAM-10647~~, so no non-alpha release is affected as of now

New: Scenario:

* Activated authorization check
* Variable instance query
* Matching a variable by value (e.g. {{runtimeService.createVariableInstanceQuery().variableValueEquals("foo", "bar").list());}}

Current behavior:

* The SQL query is invalid and fails with the following exception:

{code}
04-Oct-2019 17:27:35.978 FEIN [http-nio-8080-exec-1] org.camunda.commons.logging.BaseLogger.logDebug ENGINE-13005 Starting command -------------------- VariableInstanceQueryImpl ----------------------
04-Oct-2019 17:27:35.978 FEIN [http-nio-8080-exec-1] org.camunda.commons.logging.BaseLogger.logDebug ENGINE-13009 opening new command context
04-Oct-2019 17:27:35.999 FEIN [http-nio-8080-exec-1] org.apache.ibatis.logging.jdbc.BaseJdbcLogger.debug ==> Preparing: select distinct RES.* from ( select RES.*, ( case when RES.TASK_ID_ is not null and RES.EXECUTION_ID_ is not null then EXECUTION.ACT_INST_ID_ when RES.CASE_EXECUTION_ID_ is not null then RES.CASE_EXECUTION_ID_ when EXECUTION.PARENT_ID_ is null and RES.IS_CONCURRENT_LOCAL_ = 0 then EXECUTION.ID_ when EXECUTION.IS_SCOPE_ = 1 and EXECUTION.PARENT_ID_ is not null and RES.IS_CONCURRENT_LOCAL_ = 0 then PARENT_EXECUTION.ACT_INST_ID_ else EXECUTION.ACT_INST_ID_ end ) ACT_INST_ID_ from ACT_RU_VARIABLE RES left join ACT_RU_EXECUTION EXECUTION on RES.EXECUTION_ID_ = EXECUTION.ID_ left join ACT_RU_EXECUTION PARENT_EXECUTION on EXECUTION.PARENT_ID_ = PARENT_EXECUTION.ID_ left join ACT_RU_EXECUTION PROC_EXECUTION on PROC_EXECUTION.ID_ = RES.PROC_INST_ID_ left join ACT_RE_PROCDEF PROCDEF on PROCDEF.ID_ = PROC_EXECUTION.PROC_DEF_ID_ left JOIN ( SELECT A.* FROM ACT_RU_AUTHORIZATION A WHERE A.TYPE_ < 2 AND ( A.USER_ID_ in ( ?, '*') OR A.GROUP_ID_ IN ( ? , ? , ? , ? ) ) AND ( ( A.RESOURCE_TYPE_ = ? AND BITAND(A.PERMS_,?) = ? OR A.RESOURCE_TYPE_ = ? AND BITAND(A.PERMS_,?) = ? OR A.RESOURCE_TYPE_ = ? AND BITAND(A.PERMS_,?) = ? ) ) ) AUTH ON ( AUTH.RESOURCE_ID_ in ( RES.PROC_INST_ID_, PROC_EXECUTION.ID_, PROCDEF.KEY_, RES.TASK_ID_, '*') ) WHERE NAME_= ? and ( ( RES.TYPE_ is not null and RES.TYPE_ = ? and RES.TEXT_ is not null and RES.TEXT_ = ? ) ) and ( (RES.CASE_EXECUTION_ID_ IS NOT NULL) OR (AUTH.RESOURCE_ID_ IS NOT NULL) ) ) RES order by RES.ID_ asc LIMIT ? OFFSET ?
04-Oct-2019 17:27:36.003 FEIN [http-nio-8080-exec-1] org.camunda.commons.logging.BaseLogger.logDebug ENGINE-13011 closing existing command context
04-Oct-2019 17:27:36.004 SCHWERWIEGEND [http-nio-8080-exec-1] org.camunda.commons.logging.BaseLogger.logError ENGINE-16004 Exception while closing command context:
### Error querying database. Cause: org.h2.jdbc.JdbcSQLException: Mehrdeutiger Feldname "NAME_"
Ambiguous column name "NAME_"; SQL statement:
select distinct RES.*



    from (
          select
            RES.*,
            (
  case
when
      RES.TASK_ID_ is not null
      and RES.EXECUTION_ID_ is not null
then EXECUTION.ACT_INST_ID_

    when
      RES.CASE_EXECUTION_ID_ is not null
    then RES.CASE_EXECUTION_ID_


when
EXECUTION.PARENT_ID_ is null
      and RES.IS_CONCURRENT_LOCAL_ = 0
then EXECUTION.ID_


when
EXECUTION.IS_SCOPE_ = 1
and EXECUTION.PARENT_ID_ is not null
      and RES.IS_CONCURRENT_LOCAL_ = 0
then PARENT_EXECUTION.ACT_INST_ID_

else EXECUTION.ACT_INST_ID_
end
   ) ACT_INST_ID_

          from ACT_RU_VARIABLE RES

          left join ACT_RU_EXECUTION EXECUTION
          on RES.EXECUTION_ID_ = EXECUTION.ID_

          left join ACT_RU_EXECUTION PARENT_EXECUTION
          on EXECUTION.PARENT_ID_ = PARENT_EXECUTION.ID_


            left join ACT_RU_EXECUTION PROC_EXECUTION
            on PROC_EXECUTION.ID_ = RES.PROC_INST_ID_

            left join ACT_RE_PROCDEF PROCDEF
            on PROCDEF.ID_ = PROC_EXECUTION.PROC_DEF_ID_






    left JOIN (
    SELECT A.*
    FROM ACT_RU_AUTHORIZATION A
    WHERE A.TYPE_ < 2
    AND ( A.USER_ID_ in ( ?, '*')

      OR A.GROUP_ID_ IN ( ? , ? , ? , ? )

    )

      AND (

         (
          A.RESOURCE_TYPE_ = ? AND BITAND(A.PERMS_,?) = ?
         OR
          A.RESOURCE_TYPE_ = ? AND BITAND(A.PERMS_,?) = ?
         OR
          A.RESOURCE_TYPE_ = ? AND BITAND(A.PERMS_,?) = ?
         )


      )

    )


              AUTH ON (
              AUTH.RESOURCE_ID_ in (
                                    RES.PROC_INST_ID_,
                                    PROC_EXECUTION.ID_,
                                    PROCDEF.KEY_,
                                    RES.TASK_ID_,
                                    '*')
              )


           WHERE


        NAME_= ?






                and



    (

      ( RES.TYPE_ is not null and RES.TYPE_ = ?



          and RES.TEXT_ is not null and
RES.TEXT_

    =

? )

    )







          and (
          (RES.CASE_EXECUTION_ID_ IS NOT NULL)
          OR
          (AUTH.RESOURCE_ID_ IS NOT NULL)

          )
    ) RES

    order by RES.ID_ asc
    LIMIT ? OFFSET ? [90059-190]
...
{code}

Reason:

* Authorization requires to join various tables including {{ACT_RE_PROCDEF}}
* Both {{ACT_RE_PROCDEF}} and {{ACT_RU_VARIABLE}} have a {{NAME_}} column
* The query references {{NAME_}} without a table prefix

Solution:

* Explicitly specify the table prefix in the name matching clause, i.e. {{WHERE RES.NAME_ = ...}}
* The current clause (without {{RES_.}}) was introduced via https://github.com/camunda/camunda-bpm-platform/blob/7.12.0-alpha4/engine/src/main/resources/org/camunda/bpm/engine/impl/mapping/entity/Commons.xml#L86-L96
* Note that for the variable value matching we already have similar logic, so maybe we can do this in a similar way (https://github.com/camunda/camunda-bpm-platform/blob/7.12.0-alpha4/engine/src/main/resources/org/camunda/bpm/engine/impl/mapping/entity/VariableInstance.xml#L439)

Side notes:

* Also test other queries that allow case-insensitive value matching
* This is a regression introduced in ~~CAM-10647~~, so no non-alpha release is affected as of now

Yana Vasileva made changes - 16/Oct/19 3:36 PM

Assignee

New: Yana Vasileva [ yana.vasileva ]

Yana Vasileva made changes - 16/Oct/19 3:36 PM

Status

Original: Open [ 1 ]

New: Ready [ 10005 ]

Assignee:: Unassigned

Reporter:: Thorben Lindhauer

Votes:: 0 Vote for this issue

Watchers:: 3 Start watching this issue

Created:: 04/Oct/19 5:52 PM

Updated:: 09/Dec/19 9:59 AM

Resolved:: 18/Oct/19 4:42 PM

Details

Description

mgm-controller-panel

This is the controller panel for Smart Panels app

Attachments

Activity

People

Dates

Salesforce