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

          is required by

          Bug - A problem which impairs or prevents the functions of the product. NXP-7312 Fix queries using operator IN in their clauses for SQL Server

          • Critical - Crashes, loss of data, severe memory leak.
          • Resolved

            Activity

              People

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

                Dates

                • Created:
                  Updated:
                  Resolved: