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

• Type: Bug
• Status: Closed
• Resolution: Fixed
• Affects Version/s: 7.0.X, Master
• Fix Version/s: 7.0.0 DXP FP47, 7.0.0 DXP SP8, 7.0.X, 7.1.0 Beta 1, 7.1.X, Master
• Component/s: Core Infrastructure
• Labels:
  • ee-ts
  • liferay-digital-enterprise-70-sp8
  • liferay-fixpack-de-47-7010
• Branch Version/s:
  7.0.x
• Backported to Branch:
  Committed
• Story Points: 5
• Fix Priority: 3
• Git Pull Request: https://github.com/brianchandotcom/liferay-portal/pull/58425
• Bug Type: Regression Bug

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').