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

ESCAPE clause does not work correctly on Oracle 11

    Details

    • Type: Bug Report
    • Status: Closed
    • Priority: L3 - Default
    • Resolution: Fixed
    • Affects Version/s: 7.7.0, 7.6.3, 7.5.9, 7.8.0
    • Fix Version/s: 7.9.0, 7.9.0-alpha1, 7.8.2, 7.7.7, 7.6.12
    • Component/s: None
    • Labels:
    • Environment:
      Oracle 11g (11.2.0.1)

      Description

      Having

      • Oracle 11 database
      • Camunda version with changes implemented within CAM-7479

      When

      • I search for process definition with process name like 'someValue'

      Current result

      • An SQL Exception with ORA-01425 is thrown

      Expected result

      • Search is correctly performed

      Additional information:

      • Exception is not always happening and probably is related with charset on application server side
      • All like queries (Engine + Cockpit) are affected by the problem
      1. CAM-8443.txt
        14 kB
        Svetlana Dorokhova
      1. camundabug1.png
        69 kB
      2. camundabug2.png
        21 kB

        Issue Links

          Activity

          Hide
          neerajn Neeraj Narayan added a comment - - edited

          Thanks Svetlana.

          Show
          neerajn Neeraj Narayan added a comment - - edited Thanks Svetlana.
          Hide
          svetlana.dorokhova Svetlana Dorokhova added a comment - - edited

          Hi Neeraj Narayan,

          I tested Oracle 11 and could not reproduce the issue. But I have found this interesting discussion: https://community.oracle.com/thread/3890726

          It is pointed out that on some charsets this kind of error can happen, because the backslash character is represented by 2 bytes and it looks like Oracle is complaining about this. Can you may be check this. What charset are you using in your database:
          SELECT value FROM NLS_DATABASE_PARAMETERS where parameter = 'NLS_CHARACTERSET';

          Can you try this query:

          SELECT 1 FROM DUAL WHERE 'foo%bar' LIKE 'foo\%bar' ESCAPE '\';
          Show
          svetlana.dorokhova Svetlana Dorokhova added a comment - - edited Hi Neeraj Narayan , I tested Oracle 11 and could not reproduce the issue. But I have found this interesting discussion: https://community.oracle.com/thread/3890726 It is pointed out that on some charsets this kind of error can happen, because the backslash character is represented by 2 bytes and it looks like Oracle is complaining about this. Can you may be check this. What charset are you using in your database: SELECT value FROM NLS_DATABASE_PARAMETERS where parameter = 'NLS_CHARACTERSET'; Can you try this query: SELECT 1 FROM DUAL WHERE 'foo%bar' LIKE 'foo\%bar' ESCAPE '\';
          Hide
          neerajn Neeraj Narayan added a comment -

          WE8MSWIN1252 - result for the characterset query.

          The query worked and returned 1.

          Show
          neerajn Neeraj Narayan added a comment - WE8MSWIN1252 - result for the characterset query. The query worked and returned 1.
          Hide
          svetlana.dorokhova Svetlana Dorokhova added a comment -

          Neeraj Narayan, is it the same server that gives you the exception from ticket description? Is the exception still there?

          Show
          svetlana.dorokhova Svetlana Dorokhova added a comment - Neeraj Narayan , is it the same server that gives you the exception from ticket description? Is the exception still there?
          Hide
          svetlana.dorokhova Svetlana Dorokhova added a comment -

          Following was found out in discussion with the customer:

          1.

          On Oracle support was found "Bug 9689594 ORA-1425 using binds in ESCAPE clause of a LIKE predicate" with description "ORA-1425 errors are possible during optimization of queries with LIKE having bind variables under operators in the ESCAPE clause".


          2. The bug seems to be present in Oracle v. 11.2.0.1.0 and fixed in v. 11.2.0.4.0
          3. We can fix the problem on Camunda side by inlining escape character in MyBatis mapping files using

          $escapeChar

          syntax, instead of passing them as a parameters to the prepared statement.

          Show
          svetlana.dorokhova Svetlana Dorokhova added a comment - Following was found out in discussion with the customer: 1. On Oracle support was found "Bug 9689594 ORA-1425 using binds in ESCAPE clause of a LIKE predicate" with description "ORA-1425 errors are possible during optimization of queries with LIKE having bind variables under operators in the ESCAPE clause". 2. The bug seems to be present in Oracle v. 11.2.0.1.0 and fixed in v. 11.2.0.4.0 3. We can fix the problem on Camunda side by inlining escape character in MyBatis mapping files using $escapeChar syntax, instead of passing them as a parameters to the prepared statement.

            People

            • Assignee:
              yana.vasileva Yana Vasileva
              Reporter:
              neerajn Neeraj Narayan
            • Votes:
              1 Vote for this issue
              Watchers:
              4 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved:

                Development