# JPA derived query methods failing when boolean mapping using YesNoConverter

**URL:** <https://discourse.hibernate.org/t/jpa-derived-query-methods-failing-when-boolean-mapping-using-yesnoconverter/7256>\
**Category:** Hibernate ORM\
**Created:** [February 14, 2023, 12:07pm UTC](https://discourse.hibernate.org/t/jpa-derived-query-methods-failing-when-boolean-mapping-using-yesnoconverter/7256 "2023-02-14T12:07:21Z")\
**Posts on this page:** 3\
**Page:** 1

<div class="post-metadata">

**Author:** ![Chacko\_Mathew](https://yyz1.discourse-cdn.com/flex035/user_avatar/discourse.hibernate.org/chacko_mathew/32/2259_2.png) [@Chacko\_Mathew](https://discourse.hibernate.org/u/Chacko_Mathew)\
**Post date:** [February 14, 2023, 12:07pm UTC](https://discourse.hibernate.org/t/jpa-derived-query-methods-failing-when-boolean-mapping-using-yesnoconverter/7256/1 "2023-02-14T12:07:21Z")

</div>

I have the below entity where the `deleted` column in database is of type character with expected values `'Y'` or `'N'` . I’m using the [`YesNoConverter`](https://docs.jboss.org/hibernate/orm/6.0/userguide/html_single/Hibernate_User_Guide.html#basic-boolean) to encode the boolean value as `'Y'` or `'N'` .

```auto
public class User {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;
    .
    .
    
    @Column(name = "deleted")
    @Convert(converter= YesNoConverter.class)
    private boolean deleted;

}

```

But this code is giving error when I have a derived query method querying on the boolean property like below

```auto
public interface UserRepository extends JpaRepository<User,Long> {

    List<User> findByDeletedFalse();
}

```

This repository method throws below exception

```auto
2023-02-14T10:30:30.160Z WARN 5796 --- [nio-9000-exec-5] o.h.engine.jdbc.spi.SqlExceptionHelper : SQL Error: 0, SQLState: 42804
2023-02-14T10:30:30.160Z ERROR 5796 --- [nio-9000-exec-5] o.h.engine.jdbc.spi.SqlExceptionHelper : ERROR: argument of NOT must be type boolean, not type character
  Position: 128
2023-02-14T10:30:30.161Z ERROR 5796 --- [nio-9000-exec-5] o.a.c.c.C.[.[.[/].[dispatcherServlet] : Servlet.service() for servlet [dispatcherServlet] in context with path [] threw exception [Request processing failed: org.springframework.dao.InvalidDataAccessResourceUsageException: JDBC exception executing SQL [select u1_0.id,u1_0.deleted,u1_0.first_name,u1_0.home_address_id,u1_0.last_name,u1_0.work_address_id from users u1_0 where not(u1_0.deleted)]; SQL [n/a]] with root cause

org.postgresql.util.PSQLException: ERROR: argument of NOT must be type boolean, not type character
  Position: 128
        at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2676) ~[postgresql-42.5.1.jar:42.5.1]
        at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2366) ~[postgresql-42.5.1.jar:42.5.1]
        at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:356) ~[postgresql-42.5.1.jar:42.5.1]
        at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:496) ~[postgresql-42.5.1.jar:42.5.1]
        at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:413) ~[postgresql-42.5.1.jar:42.5.1]
        at org.postgresql.jdbc.PgPreparedStatement.executeWithFlags(PgPreparedStatement.java:190) ~[postgresql-42.5.1.jar:42.5.1]
        at org.postgresql.jdbc.PgPreparedStatement.executeQuery(PgPreparedStatement.java:134) ~[postgresql-42.5.1.jar:42.5.1]
        at com.zaxxer.hikari.pool.ProxyPreparedStatement.executeQuery(ProxyPreparedStatement.java:52) ~[HikariCP-5.0.1.jar:na]
        at com.zaxxer.hikari.pool.HikariProxyPreparedStatement.executeQuery(HikariProxyPreparedStatement.java) ~[HikariCP-5.0.1.jar:na]
        at org.hibernate.sql.results.jdbc.internal.DeferredResultSetAccess.executeQuery(DeferredResultSetAccess.java:217) ~[hibernate-core-6.1.7.Final.jar:6.1.7.Final]
        at org.hibernate.sql.results.jdbc.internal.DeferredResultSetAccess.getResultSet(DeferredResultSetAccess.java:146) ~[hibernate-core-6.1.7.Final.jar:6.1.7.Final]
        at org.hibernate.sql.results.jdbc.internal.JdbcValuesResultSetImpl.advanceNext(JdbcValuesResultSetImpl.java:205) ~[hibernate-core-6.1.7.Final.jar:6.1.7.Final]
        at org.hibernate.sql.results.jdbc.internal.JdbcValuesResultSetImpl.processNext(JdbcValuesResultSetImpl.java:85) ~[hibernate-core-6.1.7.Final.jar:6.1.7.Final]
        at org.hibernate.sql.results.jdbc.internal.AbstractJdbcValues.next(AbstractJdbcValues.java:29) ~[hibernate-core-6.1.7.Final.jar:6.1.7.Final]
        at org.hibernate.sql.results.internal.RowProcessingStateStandardImpl.next(RowProcessingStateStandardImpl.java:88) ~[hibernate-core-6.1.7.Final.jar:6.1.7.Final]
        at org.hibernate.sql.results.spi.ListResultsConsumer.consume(ListResultsConsumer.java:197) ~[hibernate-core-6.1.7.Final.jar:6.1.7.Final]
        at org.hibernate.sql.results.spi.ListResultsConsumer.consume(ListResultsConsumer.java:33) ~[hibernate-core-6.1.7.Final.jar:6.1.7.Final]
        at org.hibernate.sql.exec.internal.JdbcSelectExecutorStandardImpl.doExecuteQuery(JdbcSelectExecutorStandardImpl.java:443) ~[hibernate-core-6.1.7.Final.jar:6.1.7.Final]
        at org.hibernate.sql.exec.internal.JdbcSelectExecutorStandardImpl.executeQuery(JdbcSelectExecutorStandardImpl.java:166) ~[hibernate-core-6.1.7.Final.jar:6.1.7.Final]
        at org.hibernate.sql.exec.internal.JdbcSelectExecutorStandardImpl.list(JdbcSelectExecutorStandardImpl.java:91) ~[hibernate-core-6.1.7.Final.jar:6.1.7.Final]
        at org.hibernate.sql.exec.spi.JdbcSelectExecutor.list(JdbcSelectExecutor.java:31) ~[hibernate-core-6.1.7.Final.jar:6.1.7.Final]

Hibernate: select u1_0.id,u1_0.deleted,u1_0.first_name,u1_0.home_address_id,u1_0.last_name,u1_0.work_address_id from users u1_0 where not(u1_0.deleted)

```

Code with this issue: [GitHub - chackomathew/spring-boot3-hibernate6 at boolean-converter-issue](https://github.com/chackomathew/spring-boot3-hibernate6/tree/boolean-converter-issue)

Spring Boot v3.0.2

Hibernate v6.1.7.Final

If I provide JPQL query like below it’s working fine.

```auto
 @Query("select u from User u where u.deleted=false")
 List<User> findByDeletedFalse();

```

---

<div class="post-metadata">

**Author:** ![beikov](https://yyz1.discourse-cdn.com/flex035/user_avatar/discourse.hibernate.org/beikov/32/258_2.png) [@beikov](https://discourse.hibernate.org/u/beikov)\
**Post date:** [February 14, 2023, 12:09pm UTC](https://discourse.hibernate.org/t/jpa-derived-query-methods-failing-when-boolean-mapping-using-yesnoconverter/7256/2 "2023-02-14T12:09:29Z")

</div>

This is a bug. Thanks for reporting it. Please create an issue in the issue tracker([https://hibernate.atlassian.net](https://hibernate.atlassian.net)) and link to this post.

---

<div class="post-metadata">

**Author:** ![Chacko\_Mathew](https://yyz1.discourse-cdn.com/flex035/user_avatar/discourse.hibernate.org/chacko_mathew/32/2259_2.png) [@Chacko\_Mathew](https://discourse.hibernate.org/u/Chacko_Mathew)\
**Post date:** [February 14, 2023, 12:13pm UTC](https://discourse.hibernate.org/t/jpa-derived-query-methods-failing-when-boolean-mapping-using-yesnoconverter/7256/3 "2023-02-14T12:13:22Z")

</div>

Issue created [[HHH-16182] - Hibernate JIRA](https://hibernate.atlassian.net/browse/HHH-16182)