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

Variable instance query fails when matching a variable value

    XMLWordPrintable

Details

    • Bug Report
    • Resolution: Fixed
    • L3 - Default
    • 7.12.0, 7.12.0-alpha5
    • 7.12.0-alpha4
    • engine
    • None

    Description

      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:

      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

      mgm-controller-panel

        This is the controller panel for Smart Panels app

        Attachments

          Activity

            People

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

              Dates

                Created:
                Updated:
                Resolved:

                Salesforce