Failing SQL statements after upgrade from v4 -> 6.1.7

Hibernate ORM
1

Hi,

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

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

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

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:

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:

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:

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:

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:

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?

2

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.

3

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

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:

@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.

4

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

5

I cannot upgrade to 6.5 because of this problem [HHH-18108] - Hibernate JIRA.
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):

@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?

6

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)]

7

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 and if you are able to reproduce the issue, create a bug ticket in our issue tracker and attach that reproducer.

8

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?

9

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

10

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).