Uploaded image for project: 'Hibernate ORM'
  1. HHH-10412

case/when in criteria always casts resulting object which fails on mysql with boolean

    Details

    • Type: Bug
    • Status: Closed
    • Priority: Major
    • Resolution: Fixed
    • Affects Version/s: 5.0.6
    • Fix Version/s: 5.0.8
    • Labels:
      None
    • Environment:
      mysql/mariadb
    • Bug Testcase Reminder (view):

      Bug reports should generally be accompanied by a test case!

    • Last commented by a user?:
      true
    • Sprint:

      Description

      test o.h.jpa.test.criteria.simplecase.BasicSimpleCaseTest#testCaseLiteralResult2 fails on mysql/mariadb

      public void testCaseLiteralResult2() {
        EntityManager em = getOrCreateEntityManager();
        em.getTransaction().begin();
        CriteriaBuilder cb = em.getCriteriaBuilder();
        CriteriaQuery<Boolean> cq = cb.createQuery( Boolean.class );
        Root<Customer> expense_ = cq.from( Customer.class );
        em.createQuery(
        	cq.distinct( true ).where(
        	  cb.equal( expense_.get( "email" ), "@hibernate.com" )
        	).multiselect(
        	  cb.selectCase()
        	    .when( cb.gt( cb.count( expense_ ), cb.literal( 0L ) ), true )
        	    .otherwise( false )
        	)
        ).getSingleResult();
      }
      
      org.mariadb.jdbc.internal.common.QueryException: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'bit) else cast(0 as bit) end as col_0_0_ from customer basicsimpl0_ where basics' at line 1
      Query is:
      sql : 'select distinct case when count(basicsimpl0_.id)>0 then cast(1 as bit) else cast(0 as bit) end as col_0_0_ from customer basicsimpl0_ where basicsimpl0_.email=?', parameters : ['@hibernate.com']
      	at org.mariadb.jdbc.internal.mysql.MySQLProtocol.getResult(MySQLProtocol.java:969)
      	at org.mariadb.jdbc.internal.mysql.MySQLProtocol.executeQuery(MySQLProtocol.java:1021)
      	at org.mariadb.jdbc.internal.mysql.MySQLProtocol.executeQuery(MySQLProtocol.java:1003)
      	at org.mariadb.jdbc.MySQLStatement.execute(MySQLStatement.java:271)
      

      cast to bit isn't supported in mysql/mariadb - https://mariadb.com/kb/en/mariadb/convert/

      possible workaround is to enclose resulting object in literal

      this fails:

      when( cb.gt( cb.count( expense_ ), cb.literal( 0L ) ), true )
      

      this passes:

      .when( cb.gt( cb.count( expense_ ), cb.literal( 0L ) ), cb.literal( true ) )
      

        Attachments

          Issue links

            Activity

              People

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

                Dates

                • Created:
                  Updated:
                  Resolved: