Uploaded image for project: 'Confluence Server and Data Center'
  1. Confluence Server and Data Center
  2. CONFSERVER-17030

Query to find page alternatives fails on DB2 because we are invoking SELECT DISTINCT on the VERSIONCOMMENT clob column




      The query produces this error:

      [sf.hibernate.util.JDBCExceptionReporter] logExceptions DB2 SQL error: SQLCODE: -134, SQLSTATE: 42907, SQLERRMC: VERSIONC9_

      The root cause is that we are doing something like this: select DISTINCT p from Page p. This is problematic because select distinct p translates to a SELECT distinct on all the columns of Page which includes the VERSIONCOMMENT clob column. DB2 does not support select distinct over string values larger than 255 characters (let alone a clob column sized at 10,000).

      Here's the actual explanation of SQLCODE -134 from IBM's documentation:

      Improper use of a string column, host variable, constant, or function name.
      The use of the string name is not permitted.
      An expression resulting in a string data type with a maximum length greater than 255 bytes is not permitted in:
          * A SELECT DISTINCT statement
          * A GROUP BY clause
          * An ORDER BY clause
          * A column function with DISTINCT
          * A SELECT or VALUES statement of a set operator other than UNION ALL. 
      An expression resulting in a LONG VARCHAR or LONG VARGRAPHIC data type is not permitted in:
          * A predicate other than EXISTS or NULL
          * A column function
          * The SELECT clause of a subquery of a predicate other than EXISTS or NULL
          * The SELECT clause of a subselect in an INSERT statement
          * The value expression of a SET clause in an UPDATE statement unless the expression is a LONG VARCHAR or LONG VARGRAPHIC host variable
          * A SELECT statement of a set operator (except UNION ALL)
          * VARGRAPHIC scalar function. 
      Federated system users: in a pass-through session, a data source-specific restriction can cause this error. See the SQL Reference documentation for the failing data sources.
      The statement cannot be processed.
      User Response:
      The requested operation on the string is not supported.
      If it is unclear as to how the 255 byte limit is being exceeded, consider that codepage conversion operations may be required to evaluate the string expression. Depending on the source and target codepages, the target may have a greater length attribute than the source. For more information, refer to the SQL Reference for discussions on string restrictions and string conversions.
      sqlcode: -134
      sqlstate: 42907


        Issue Links



              dave@atlassian.com dave (Inactive)
              dave@atlassian.com dave (Inactive)
              0 Vote for this issue
              0 Start watching this issue