Uploaded image for project: 'Nuxeo Platform'
  1. Nuxeo Platform
  2. NXP-5183

Fix queries using operator IN in their clauses for MySQL

    XMLWordPrintable

    Details

    • Type: Bug
    • Status: Resolved
    • Priority: Minor
    • Resolution: Fixed
    • Affects Version/s: 5.3.1
    • Fix Version/s: 5.3.2
    • Component/s: None
    • Impact type:
      Content model Change
    • Upgrade notes:
      Hide

      Columns where this is a problem must be changed to have the BINARY flag, for instance:
      ALTER TABLE hierarchy MODIFY primarytype VARCHAR(255) BINARY;
      ALTER TABLE hierarchy MODIFY name VARCHAR(255) BINARY;

      Show
      Columns where this is a problem must be changed to have the BINARY flag, for instance: ALTER TABLE hierarchy MODIFY primarytype VARCHAR(255) BINARY; ALTER TABLE hierarchy MODIFY name VARCHAR(255) BINARY;

      Description

      In MySQL, operator IN is case insensitive.

      However NXQLQueryMaker produces queries of the form
      ... WHERE hierarchy.primarytype in ('...', 'File', '...') AND ...

      This will incorrectly match a complex type 'file'.

      You get errors like:
      ERROR [SQLQueryResult] Could not fetch documents for ids: [9e247dc7-001f-46c8-b40c-5b2ff3b6c977, c2e8a9c1-5adc-4fbb-b188-c1a54797d4f9]
      org.nuxeo.ecm.core.api.DocumentException: Unknown document type: file
      at org.nuxeo.ecm.core.storage.sql.coremodel.SQLSession.newDocument(SQLSession.java:560)
      at org.nuxeo.ecm.core.storage.sql.coremodel.SQLSession.newDocument(SQLSession.java:531)
      at org.nuxeo.ecm.core.storage.sql.coremodel.SQLSession.getDocumentsById(SQLSession.java:596)
      at org.nuxeo.ecm.core.storage.sql.coremodel.SQLQueryResult.getDocumentModels(SQLQueryResult.java:98)

      We need to rewrite the query for MySQL when we use the operator IN, to mark the primarytype column BINARY (or mark it directly in the table schema).

        Attachments

          Issue Links

            Activity

              People

              • Votes:
                0 Vote for this issue
                Watchers:
                0 Start watching this issue

                Dates

                • Created:
                  Updated:
                  Resolved:

                  PagerDuty

                  Error rendering 'com.pagerduty.jira-server-plugin:PagerDuty'. Please contact your Jira administrators.