We're updating the issue view to help you get more done. 

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

Description

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 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(); }
1 2 3 4 5 6 7 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:

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

this passes:

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

Environment

mysql/mariadb

Status

Assignee

Gail Badner

Reporter

Martin Šimka

Fix versions

Labels

None

backPortable

None

Suitable for new contributors

None

Requires Release Note

None

Pull Request

None

backportDecision

None

Components

Affects versions

5.0.6

Priority

Major