# Failing SQL statements after upgrade from v4 -\> 6.1.7

**URL:** <https://discourse.hibernate.org/t/failing-sql-statements-after-upgrade-from-v4-6-1-7/9726>\
**Category:** Hibernate ORM\
**Created:** [June 11, 2024, 7:28pm UTC](https://discourse.hibernate.org/t/failing-sql-statements-after-upgrade-from-v4-6-1-7/9726 "2024-06-11T19:28:56Z")\
**Posts on this page:** 10\
**Page:** 1

<div class="post-metadata">

**Author:** ![trueMiskin](https://yyz1.discourse-cdn.com/flex035/user_avatar/discourse.hibernate.org/truemiskin/32/2284_2.png) [@trueMiskin](https://discourse.hibernate.org/u/trueMiskin)\
**Post date:** [June 11, 2024, 7:28pm UTC](https://discourse.hibernate.org/t/failing-sql-statements-after-upgrade-from-v4-6-1-7/9726/1 "2024-06-11T19:28:56Z")

</div>

Hi,

during the upgrade, I encountered that some SQL statements don’t work anymore. For example:

```auto
SELECT s.id FROM Table s WHERE (:findDeleted = true OR s.deleted = false)

```

Before the upgrade, the SQL statement is translated into this:

```auto
select table0_.id as id1_132_0_ from Table table0_ where (?=1 or table0_.deleted=0)

```

After the upgrade, the SQL statement is translated into this:

```auto
select s1_0.id from SkupinaStravniku s1_0 where (?=true or s1_0.deleted=0)

```

And throw this error: `Dynamic SQL Error; SQL error code = -206; Column unknown; TRUE; At line 1, column 32 [SQLState:42S22, ISC error code:335544578]`

You can see that I use **BooleanToInt Converter** but I don’t know why the **true keyword is not translated into 1**. I use the Firebird 2 database which does not support the boolean type, so true is interpreted as a column name that does not exist.

Then I updated the SQL statement:

```auto
SELECT s.id FROM Table s WHERE (:findDeleted = 1 OR s.deleted = false)

```

The Hibernate throws this exception: `Parameter value [false] did not match expected type [basicType@3(java.lang.Integer,4)]`

Finally, I updated the SQL once more:

```auto
SELECT s.id FROM Table s WHERE (cast(:findDeleted as integer) = 1 OR s.deleted = false)

```

This works but it is a bit nasty solution (converting the boolean named parameter and value in SQL statement to Integer).

The second example is this:

```auto
SELECT c.id FROM Table c WHERE true = ANY(SELECT d.visibility FROM Table d WHERE d.table = c)

```

Before the upgrade, the SQL statement is translated into this:

```auto
select table0_.id as id1_12_ from Table table0_ where 1=any (select table20_.visibility from Table2 table20_ where table20_.table_id=table0_.id)

```

But after the upgrade, the true keyword is not translated into 1. If I substitute 1 for true, the SQL works as expected.

What is the supposed solution for the SQL statements?

---

<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:** [June 12, 2024, 9:08am UTC](https://discourse.hibernate.org/t/failing-sql-statements-after-upgrade-from-v4-6-1-7/9726/2 "2024-06-12T09:08:06Z")

</div>

This is handled by the dialect in `org.hibernate.community.dialect.FirebirdDialect#appendBooleanValueString`. Since this is a community dialect, you will have to debug this yourself. The Hibernate team does not maintain does dialects.

---

<div class="post-metadata">

**Author:** ![trueMiskin](https://yyz1.discourse-cdn.com/flex035/user_avatar/discourse.hibernate.org/truemiskin/32/2284_2.png) [@trueMiskin](https://discourse.hibernate.org/u/trueMiskin)\
**Post date:** [June 12, 2024, 3:25pm UTC](https://discourse.hibernate.org/t/failing-sql-statements-after-upgrade-from-v4-6-1-7/9726/3 "2024-06-12T15:25:55Z")

</div>

Thanks. Indeed there was a bug in 6.1. I Updated Hibernate (and community dialects) to 6.2.25 but it throws this error:

```auto
org.hibernate.tool.schema.spi.SchemaManagementException: Schema-validation: wrong column type encountered in column [column] in table [Table]; found [smallint (Types#SMALLINT)], but expecting [integer (Types#INTEGER)]

```

And I don’t know, why hibernate expects an integer. Skeleton of class:

```auto
@Entity
public class Table{
    @Id
    @GeneratedValue(strategy = GenerationType.TABLE)
    private Integer id;
    
    @NotNull
    private Boolean column;

```

I set `hibernate.type.preferred_boolean_jdbc_type=SMALLINT` but the error persists.

---

<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:** [June 13, 2024, 3:44pm UTC](https://discourse.hibernate.org/t/failing-sql-statements-after-upgrade-from-v4-6-1-7/9726/4 "2024-06-13T15:44:03Z")

</div>

This was fixed in newer versions. As of 6.4 I believe, so please upgrade. It’s best to upgrade to the latest version. Also see [Maintenance Policy - Hibernate](https://hibernate.org/community/maintenance-policy/)

---

<div class="post-metadata">

**Author:** ![trueMiskin](https://yyz1.discourse-cdn.com/flex035/user_avatar/discourse.hibernate.org/truemiskin/32/2284_2.png) [@trueMiskin](https://discourse.hibernate.org/u/trueMiskin)\
**Post date:** [June 13, 2024, 6:32pm UTC](https://discourse.hibernate.org/t/failing-sql-statements-after-upgrade-from-v4-6-1-7/9726/5 "2024-06-13T18:32:10Z")

</div>

I cannot upgrade to 6.5 because of this problem [[HHH-18108] - Hibernate JIRA](https://hibernate.atlassian.net/browse/HHH-18108).  
I tried 6.4.9, but it throws new errors with SQL queries: `Operand of + is of type 'java.sql.Time' which is not a temporal amount (it is not an instance of 'java.time.TemporalAmount')`

We have this query + entity (simplified):

```auto
@Entity
@NamedQueries({
    @NamedQuery(name = "Table.Q", query = "SELECT h FROM Table h WHERE h.date + h.time BETWEEN :dateFrom AND :dateTo")
})
public class Table{
    @Id
    private Integer id;

    @Temporal(TemporalType.DATE)
    private Date date;

    @Temporal(TemporalType.TIME)
    private Date time;
}

```

How should I resolve this problem?

---

<div class="post-metadata">

**Author:** ![trueMiskin](https://yyz1.discourse-cdn.com/flex035/user_avatar/discourse.hibernate.org/truemiskin/32/2284_2.png) [@trueMiskin](https://discourse.hibernate.org/u/trueMiskin)\
**Post date:** [June 13, 2024, 6:53pm UTC](https://discourse.hibernate.org/t/failing-sql-statements-after-upgrade-from-v4-6-1-7/9726/6 "2024-06-13T18:53:14Z")

</div>

I tried to comment out these queries and the problem before with boolean still occurs.

Still, I am setting `hibernate.type.preferred_boolean_jdbc_type=SMALLINT`.

PS. I tried the `@Column(columnDefinition = "smallint")` and this works but we have a lot of boolean columns.

UPDATE: I tried to annotate some fields with `@Column` and I found out that another problem with an enum: `Schema-validation: wrong column type encountered in column [enumColumn] in table [Table]; found [integer (Types#INTEGER)], but expecting [smallint (Types#TINYINT)]`

---

<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:** [June 14, 2024, 1:00pm UTC](https://discourse.hibernate.org/t/failing-sql-statements-after-upgrade-from-v4-6-1-7/9726/7 "2024-06-14T13:00:53Z")

</div>

Can you reproduce the problem with e.g. H2? If not, it’s going to be hard for us to work on this. It would be nice if you could create a reproducer based on our [test case template](https://github.com/hibernate/hibernate-test-case-templates/blob/master/orm/hibernate-orm-6/src/test/java/org/hibernate/bugs/JPAUnitTestCase.java) and if you are able to reproduce the issue, create a bug ticket in our [issue tracker](https://hibernate.atlassian.net) and attach that reproducer.

---

<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:** [June 14, 2024, 1:03pm UTC](https://discourse.hibernate.org/t/failing-sql-statements-after-upgrade-from-v4-6-1-7/9726/8 "2024-06-14T13:03:13Z")

</div>

> [@trueMiskin](#):
>
> How should I resolve this problem?

That’s tough, I don’t know if `date + time` can be implemented nicely on all databases. That’s kind of an improvement request which you should log as Jira issue. In the meantime, you can create a custom function for adding time to date and use that instead of `+` directly, though the question is, why are you storing date and time separately?

---

<div class="post-metadata">

**Author:** ![trueMiskin](https://yyz1.discourse-cdn.com/flex035/user_avatar/discourse.hibernate.org/truemiskin/32/2284_2.png) [@trueMiskin](https://discourse.hibernate.org/u/trueMiskin)\
**Post date:** [June 15, 2024, 10:45pm UTC](https://discourse.hibernate.org/t/failing-sql-statements-after-upgrade-from-v4-6-1-7/9726/9 "2024-06-15T22:45:18Z")

</div>

I caught the bug! The problem is with the application `NumericBooleanConverter`, this converter changes the type from smallint to integer.

A test project with one test file and a slightly modified configuration file: [hibernate-orm-6 - Google Drive](https://drive.google.com/drive/folders/1nKOO8cqDd66NAsXcVC5rny8uijY9T67N?usp=sharing)

---

<div class="post-metadata">

**Author:** ![trueMiskin](https://yyz1.discourse-cdn.com/flex035/user_avatar/discourse.hibernate.org/truemiskin/32/2284_2.png) [@trueMiskin](https://discourse.hibernate.org/u/trueMiskin)\
**Post date:** [June 15, 2024, 10:59pm UTC](https://discourse.hibernate.org/t/failing-sql-statements-after-upgrade-from-v4-6-1-7/9726/10 "2024-06-15T22:59:25Z")

</div>

> [@beikov](#):
>
> though the question is, why are you storing date and time separately?

It is a little bit complicated. In reality, we have two tables. In one table we store the date and in the second one time. These tables are in one-to-many relation.

With this setup, we can edit the date but don’t modify the second tables with times (and other stuff).