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

[SQL Server] The data types numeric and bigint are incompatible in the '&' operator.

    Details

      Description

      & (Bitwise AND) in SQL Server Transact-SQL performs a bitwise logical AND operation between two integer values.

      See https://docs.microsoft.com/en-us/sql/t-sql/language-elements/bitwise-and-transact-sql

      SQL Server Transact-SQL defines following exact-number data types that use integer data:

      Data type Range Storage
      bigint -2^63 (-9,223,372,036,854,775,808) to 2^63-1 (9,223,372,036,854,775,807) 8 Bytes
      int -2^31 (-2,147,483,648) to 2^31-1 (2,147,483,647) 4 bytes

      Integer constants greater than 2,147,483,647 are converted to the decimal (or numeric) data type, not the bigint data type.

      See https://docs.microsoft.com/en-us/sql/t-sql/data-types/int-bigint-smallint-and-tinyint-transact-sql

      The following table lists the default mappings between the basic SQL Server and Java programming language data types:
      Data type mappings

      SQL Server Types Java Language Types
      int int
      bigint long
      decimal java.math.BigDecimal
      numeric java.math.BigDecimal

      See https://docs.microsoft.com/en-us/sql/connect/jdbc/using-basic-data-types

      Thus, in case of using integer values greater than 2,147,483,647, is mandatory to cast thoses values to bigint data type because neither decimal nor numeric data types can be used in a bitwise logical AND operation.


      We have the following predefined SQL sentence: https://github.com/liferay/liferay-portal/blob/master/portal-impl/src/custom-sql/portal.xml

      <sql id="com.liferay.portal.service.impl.ResourcePermissionLocalServiceImpl.updateActionIds">
      		<![CDATA[
      			UPDATE
      				ResourcePermission
      			SET
      				actionIds = actionIds + ? - BITAND(CAST_LONG(ResourcePermission.actionIds), ?)
      			WHERE
      				(ResourcePermission.name = ?) AND
      				(ResourcePermission.scope = ?) AND
      				(ResourcePermission.roleId IN ([$ROLE_ID$]))
      		]]>
      </sql>
      

      Please, note that it defines a bitwise logical AND operation through BITAND() clause, between a cast value CAST_LONG(ResourcePermission.actionIds) and a non-cast parameter.

      Upgrade process uses it, and then it is possible to get following exception during upgrade:

      2021-03-03 20:04:24.077 INFO  [main][LoggingTimer:83] Starting com.liferay.portal.events.StartupHelperUtil#initResourceActions
      2021-03-03 20:04:24.572 ERROR [main][JDBCExceptionReporter:234] The data types numeric and bigint are incompatible in the '&' operator.
      2021-03-03 20:04:24.640 INFO  [main][LoggingTimer:44] Completed com.liferay.portal.events.StartupHelperUtil#initResourceActions in 563 ms
      com.liferay.portal.kernel.exception.SystemException: com.liferay.portal.kernel.dao.orm.ORMException: org.hibernate.exception.SQLGrammarException: could not execute native bulk manipulation query
      	at com.liferay.portal.service.impl.ResourcePermissionLocalServiceImpl.addResourcePermissions(ResourcePermissionLocalServiceImpl.java:571)
      	at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
      	at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
      	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
      	at java.lang.reflect.Method.invoke(Method.java:498)
      	at com.liferay.portal.spring.aop.AopMethodInvocationImpl.proceed(AopMethodInvocationImpl.java:50)
      	at com.liferay.portal.spring.transaction.TransactionInterceptor.invoke(TransactionInterceptor.java:69)
      	at com.liferay.portal.spring.aop.AopMethodInvocationImpl.proceed(AopMethodInvocationImpl.java:57)
      	at com.liferay.portal.spring.aop.AopInvocationHandler.invoke(AopInvocationHandler.java:49)
      	at com.sun.proxy.$Proxy102.addResourcePermissions(Unknown Source)
      	at com.liferay.portal.service.impl.ResourceActionLocalServiceImpl.checkResourceActions(ResourceActionLocalServiceImpl.java:232)
      	at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
      	at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
      	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
      	at java.lang.reflect.Method.invoke(Method.java:498)
      	at com.liferay.portal.spring.aop.AopMethodInvocationImpl.proceed(AopMethodInvocationImpl.java:50)
      	at com.liferay.portal.spring.transaction.TransactionInterceptor.invoke(TransactionInterceptor.java:69)
      	at com.liferay.portal.spring.aop.AopMethodInvocationImpl.proceed(AopMethodInvocationImpl.java:57)
      	at com.liferay.change.tracking.internal.aop.CTTransactionAdvice.invoke(CTTransactionAdvice.java:80)
      	at com.liferay.portal.spring.aop.AopMethodInvocationImpl.proceed(AopMethodInvocationImpl.java:57)
      	at com.liferay.portal.spring.aop.AopInvocationHandler.invoke(AopInvocationHandler.java:49)
      	at com.sun.proxy.$Proxy103.checkResourceActions(Unknown Source)
      	at com.liferay.portal.kernel.service.ResourceActionLocalServiceUtil.checkResourceActions(ResourceActionLocalServiceUtil.java:76)
      	at com.liferay.portal.events.StartupHelperUtil.initResourceActions(StartupHelperUtil.java:57)
      	at com.liferay.portal.tools.DBUpgrader._checkClassNamesAndResourceActions(DBUpgrader.java:187)
      	at com.liferay.portal.tools.DBUpgrader._upgradePortal(DBUpgrader.java:368)
      	at com.liferay.portal.tools.DBUpgrader.upgrade(DBUpgrader.java:152)
      	at com.liferay.portal.tools.DBUpgrader.upgrade(DBUpgrader.java:144)
      	at com.liferay.portal.tools.DBUpgrader.main(DBUpgrader.java:124)
      	at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
      	at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
      	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
      	at java.lang.reflect.Method.invoke(Method.java:498)
      	at com.liferay.portal.tools.db.upgrade.client.DBUpgraderLauncher.main(DBUpgraderLauncher.java:54)
      Caused by: com.liferay.portal.kernel.dao.orm.ORMException: org.hibernate.exception.SQLGrammarException: could not execute native bulk manipulation query
      	at com.liferay.portal.dao.orm.hibernate.ExceptionTranslator.translate(ExceptionTranslator.java:39)
      	at com.liferay.portal.dao.orm.hibernate.SQLQueryImpl.executeUpdate(SQLQueryImpl.java:127)
      	at com.liferay.portal.service.impl.ResourcePermissionLocalServiceImpl.addResourcePermissions(ResourcePermissionLocalServiceImpl.java:514)
      	... 33 more
      Caused by: org.hibernate.exception.SQLGrammarException: could not execute native bulk manipulation query
      	at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:92)
      	at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:66)
      	at org.hibernate.engine.query.NativeSQLQueryPlan.performExecuteUpdate(NativeSQLQueryPlan.java:219)
      	at org.hibernate.impl.SessionImpl.executeNativeUpdate(SessionImpl.java:1310)
      	at org.hibernate.impl.SQLQueryImpl.executeUpdate(SQLQueryImpl.java:396)
      	at com.liferay.portal.dao.orm.hibernate.SQLQueryImpl.executeUpdate(SQLQueryImpl.java:124)
      	... 34 more
      Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: The data types numeric and bigint are incompatible in the '&' operator.
      	at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(SQLServerException.java:262)
      	at com.microsoft.sqlserver.jdbc.SQLServerStatement.getNextResult(SQLServerStatement.java:1632)
      	at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.doExecutePreparedStatement(SQLServerPreparedStatement.java:602)
      	at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement$PrepStmtExecCmd.doExecute(SQLServerPreparedStatement.java:524)
      	at com.microsoft.sqlserver.jdbc.TDSCommand.execute(IOBuffer.java:7375)
      	at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(SQLServerConnection.java:3206)
      	at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeCommand(SQLServerStatement.java:247)
      	at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeStatement(SQLServerStatement.java:222)
      	at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.executeUpdate(SQLServerPreparedStatement.java:473)
      	at com.zaxxer.hikari.pool.ProxyPreparedStatement.executeUpdate(ProxyPreparedStatement.java:61)
      	at com.zaxxer.hikari.pool.HikariProxyPreparedStatement.executeUpdate(HikariProxyPreparedStatement.java)
      	at org.hibernate.engine.query.NativeSQLQueryPlan.performExecuteUpdate(NativeSQLQueryPlan.java:210)
      	... 37 more
       

      Proposed solution:

      Casting the parameters used with BITAND() would resolve the issue:

      <sql id="com.liferay.portal.service.impl.ResourcePermissionLocalServiceImpl.updateActionIds">
      		<![CDATA[
      			UPDATE
      				ResourcePermission
      			SET
      				actionIds = actionIds + CAST_LONG(?) - BITAND(CAST_LONG(ResourcePermission.actionIds), CAST_LONG(?))
      			WHERE
      				(ResourcePermission.name = ?) AND
      				(ResourcePermission.scope = ?) AND
      				(ResourcePermission.roleId IN ([$ROLE_ID$]))
      		]]>
      </sql>
      

      Attached groovy script SQLServerCast.groovy provides a POC with the proposed solution.
      The POC consists of four different combinations of SQL sentence, using an integer value greater than 2,147,483,647 and without/with casting this value.

      Preconditions to run the script

      1. IMPORTANT NOTE: This script does direct database operations which will end in an unrecovery state of database. Its only purpose is to help testing how SQL server needs casting integer values.
      2. Configure DXP to use a SQL Server database.

      Steps to run the script

      1. Start DXP and navigate to Groovy console. Then execute the script.

      Expected behavior
      Script's output is:

      Begin of script.
      --------
      Following SQL sentence is within SQL Server limits managing integer values:
      	UPDATE ResourcePermission SET actionIds = actionIds + 2147483647 - CAST(ResourcePermission.actionIds AS BIGINT) & 2147483647
      Execution OK !
      --------
      Following SQL sentence is out of the SQL Server limits managing integer values:
      	UPDATE ResourcePermission SET actionIds = actionIds + 2147483648 - CAST(ResourcePermission.actionIds AS BIGINT) & 2147483648
      And causes following exception:
      	Operand data type numeric is invalid for '&' operator.
      --------
      Following SQL sentence is out of the SQL Server limits managing integer values:
      	UPDATE ResourcePermission SET actionIds = actionIds + 2147483648 - CAST(ResourcePermission.actionIds AS BIGINT) &  CAST(2147483648 AS BIGINT)
      And causes following exception:
      	The data types numeric and bigint are incompatible in the '&' operator.
      --------
      Following SQL sentence uses CAST operator, so it is within SQL Server limits managing integer values:
      	UPDATE ResourcePermission SET actionIds = actionIds +  CAST(2147483648 AS BIGINT) - CAST(ResourcePermission.actionIds AS BIGINT) &  CAST(2147483648 AS BIGINT)
      Execution OK !
      --------
      End of script.
      

        Attachments

          Activity

            People

            Assignee:
            summer.zhang Summer Zhang
            Reporter:
            sergio.alonso Sergio Alonso
            Participants of an Issue:
            Recent user:
            Rafaela Nascimento
            Engineering Assignee:
            Sergio Alonso
            Votes:
            0 Vote for this issue
            Watchers:
            0 Start watching this issue

              Dates

              Created:
              Updated:
              Resolved:
              Days since last comment:
              9 weeks, 4 days ago

                Packages

                Version Package
                7.3.X
                7.4.0 CE GA1
                Master