Uploaded image for project: 'PUBLIC - Liferay Portal Community Edition'
  1. PUBLIC - Liferay Portal Community Edition
  2. LPS-80698

Transformation of SQL queries containing CONCAT operator does not always work for some databases

    Details

      Description

      A "concat" transformer function was introduced, for SQL Server, Oracle, DB2 and all databases logic implementations that transform the CONCAT operator, in fixpack-40 by ------------LPS-75081------------.

      But one valid case throws an IllegalArgumentException. This is the full query that has stopped working:

       

      SELECT
       fullNameSelect.userId, fullNameSelect.fullName, fullNameSelect.emailAddress
      FROM 
           (
             SELECT CONCAT (
                        LOWER(firstName),' ',
                              CASE 
                                   WHEN middleName != '' 
                                            THEN CONCAT(LOWER(middleName),' ')
                                   ELSE
                                       ''
                              END,
                      LOWER(lastName)
                      ) as fullName, userId, emailAddress
             FROM User_
             WHERE (companyId = ?) AND (status = ?)
        ) AS 
              fullNameSelect
      WHERE
            (fullName LIKE ?) OR (emailAddress LIKE ?)
      ORDER BY
            fullNameSelect.fullName

      And this is the error: 

      java.lang.IllegalArgumentException: Unclosed string literal in: SELECT fullNameSelect.userId, fullNameSelect.fullName, fullNameSelect.emailAddress FROM (SELECT CONCAT (LOWER(firstName),' ',(CASE WHEN (middleName != '') THEN CONCAT(LOWER(middleName),' ') END) , LOWER(lastName)) as fullName, userId, emailAddress FROM User_ WHERE (companyId = 20115) AND (status = 0)) AS fullNameSelect WHERE (fullName LIKE '%Esteban%') OR (emailAddress LIKE '%Esteban%') ORDER BY fullNameSelect.fullName;
       at com.liferay.portal.internal.dao.sql.transformer.SQLFunctionTransformer.transform(SQLFunctionTransformer.java:100)
       at com.liferay.portal.dao.sql.transformer.DefaultSQLTransformer.transform(DefaultSQLTransformer.java:41)
       at com.liferay.portal.dao.orm.common.SQLTransformer._transformFromJPQLToHQL(SQLTransformer.java:125)
       at com.liferay.portal.dao.orm.common.SQLTransformer.transformFromJPQLToHQL(SQLTransformer.java:68)
       
      

      According to our tests it seems that the transformer method "needs" a blank space before any argument whose first character is an apostrophe.

      This is the line where the error occurs:

      https://github.com/liferay/liferay-portal/blob/da939d7d6e2579378f7a9c9c6d4095b55a7f4b51/portal-impl/src/com/liferay/portal/internal/dao/sql/transformer/SQLFunctionTransformer.java#L99

      And it seem this line is the one to blame (the extra next++ for this case):

      https://github.com/liferay/liferay-portal/blob/da939d7d6e2579378f7a9c9c6d4095b55a7f4b51/portal-impl/src/com/liferay/portal/internal/dao/sql/transformer/SQLFunctionTransformer.java#L105

      Also, when the transformation does not throw any exception, the transformer adds one extra blank space before the apostrophe:

      CONCAT(a, 'hello') is transformed into

      a +  'hello'

      (note the extra space before 'hello'). 

       

        Attachments

          Issue Links

            Activity

              People

              Assignee:
              raven.song Raven Song
              Reporter:
              cristina.rodriguez Cristina Rodriguez
              Participants of an Issue:
              Recent user:
              Csaba Turcsan
              Votes:
              0 Vote for this issue
              Watchers:
              2 Start watching this issue

                Dates

                Created:
                Updated:
                Resolved:
                Days since last comment:
                3 years, 5 weeks ago

                  Packages

                  Version Package
                  7.0.0 DXP FP47
                  7.0.0 DXP SP8
                  7.0.X
                  7.1.0 Beta 1
                  7.1.X
                  Master