Can't create temporary tables after migration from 5.6.15.Final to 6.2.17.Final

Hibernate ORM
1

Hi all,

I am trying to migrate a springboot project from version 2.7 to version 3.1 and consequently from hibernate version 5.6.15 to version 6.2.17 but I am having problems with a specific query in which as the title mentions a temporary table can’t be created.

This query is targeting a DB2 version 10.5 and the error thrown is the following:

Caused by: com.ibm.db2.jcc.am.SqlException: A temporary table could not be created because there is no available system temporary table space that has a compatible page size… SQLCODE=-1585, SQLSTATE=54048, DRIVER=4.19.77

The final query looks like this:

FROM Profile$Active p LEFT JOIN FETCH p.displayNameAttribute dna WHERE p.client.id = :clientId order by LOWER(COALESCE(dna.value, p.name)) asc

The culprit is that final statement with using dna.value. If I remove it or swap for a different column it works. This is a column of the joined table which has a length of 10240 bytes which is way bigger than the others. Still, my db admin tells me that we have temporary table space of 16kb which should be enough to handle this.

Another important consideration is that the exact same query was working in the previous version of hibernate with the same exact database which leads me to think that this is some misconfiguration in hibernate.

When searching for answers regarding this problem the only thing that seemed that could be related was query.mutation_strategy. Although, to my knowledge, I am not doing a mutation query I gave it a try as described here to use the inline strategy. It produced no effect, I got the same erros with all possible combinations.

One other thing that I wanted to try was to use the CteMutationStrategy as described the migration guide since it, supposedly, does not use temporary tables. Unfortunately I couldn’t even start the application since the class does not have a constructor that is compatible with what spring is trying to instantiate, which is, apparently, passing a Dialect.

As you might have noticed I am far from being an hibernate expert and I’ve spent several days around this problem with no success. I am, currently, out of ideas on how to proceed and any help would be really appreciated.

Let me know if any relevant information is missing from my description and I will add it rightway.

Thank you

2

What happens when you execute the query in the database yourself?

3

Running the query directly in the database throws the same error which leads me to conclude that hibernate 6 is creating a query different than the one created by hibernate 5.

4

Mind you I’m not part of the Hibernate team, but why shouldn’t Hibernate 6 be allowed to create a different translated query if it returns the same result?

5

From my point of view the only problem is that the query in one version runs with no problems and in the other version it breaks because it needs “more” resources. That’s fine in principle, it’s expected for things to change from one version to another but what I am failing to understand and I could not find answers anywhere yet, is how I should adapt my hibernate configuration or query to make it work with the resources available because in this case I have no power over the database configuration.

6

This has nothing to do with mutation strategies or anything like that. Please post the SQL query that was generated with Hibernate ORM 5 and 6.

8

Here are the queries. I replaced all the table names and columns by fake values for security reasons but it should illustrate the overall differences:

Hibernate 5:

SELECT t1.c1 AS c0, t2.c2 AS c1, t3.c3 AS c2, t1.c4 AS c3, t1.c5 AS c4, t1.c6 AS c5, t1.c7 AS c6, t1.c8 AS c7, t1.c9 AS c8, t1.c10 AS c9, t1.c11 AS c10, t1.c12 AS c11, t2.c13 AS c12, t2.c14 AS c13, t2.c15 AS c14, t2.c16 AS c15, t2.c17 AS c16, t2.c18 AS c17, t2.c19 AS c18, t2.c20 AS c19, t2.c21 AS c20, t2.c22 AS c21, t2.c23 AS c22, ROW_NUMBER() OVER (ORDER BY LOWER(COALESCE(t1.c5, t1.c12))) AS rn FROM TABLE1 t1 LEFT JOIN TABLE2 t2 ON t1.c0 = t2.c0 AND t2.c14 = ‘xis.pto’ LEFT JOIN TABLE3 t3 ON t1.c6 = t3.c3 WHERE CASE WHEN t1.c7 IS NOT NULL AND t1.c7 != ‘1970-01-01 01:00:00’ THEN ‘STATUS_2’ WHEN t1.c8 IS NOT NULL AND t1.c8 != ‘1970-01-01 01:00:00’ THEN ‘STATUS_1’ ELSE ‘STATUS_3’ END = ‘STATUS_3’ AND t1.c6 = ‘XXXXX’ ORDER BY CAST(LOWER(COALESCE(t2.c15, t1.c12)) AS VARCHAR(255)) ASC;

Hibernate 6:

SELECT * FROM ( SELECT table1.column1 AS col0, table1.column2 AS col1, table2.column3 AS col2, table2.column4 AS col3, table2.column5 AS col4, table2.column6 AS col5, table1.column7 AS col6, table3.column8 AS col7, CASE WHEN table3.column9 IS NOT NULL AND table3.column9 != ‘1970-01-01 01:00:00’ THEN ‘STATUS_2’ WHEN table3.column10 IS NOT NULL AND table3.column10 != ‘1970-01-01 01:00:00’ THEN ‘STATUS_1’ ELSE ‘STATUS_3’ END AS col8, table3.column11 AS col9, table3.column12 AS col10, table3.column13 AS col11, table3.column14 AS col12, table3.column15 AS col13, table3.column16 AS col14, table3.column17 AS col15, table3.column18 AS col16, table3.column19 AS col17, table1.column20 AS col18, table1.column21 AS col19, table1.column22 AS col20, table1.column23 AS col21, table1.column24 AS col22, table1.column25 AS col23, ROW_NUMBER() OVER (ORDER BY LOWER(COALESCE(table2.column6, table1.column25))) AS rownum FROM table1 LEFT JOIN table2 ON table1.column3 = table2.column3 LEFT JOIN table3 ON table3.column7 = table1.column7 WHERE table1.column7 = ? AND CASE WHEN table1.column21 IS NOT NULL AND table1.column21 != ‘1970-01-01 01:00:00’ THEN ‘STATUS_1’ WHEN table1.column19 IS NOT NULL AND table1.column19 != ‘1970-01-01 01:00:00’ THEN ‘STATUS_2’ ELSE ‘STATUS_3’ END = ‘STATUS_3’ ) resultTable WHERE resultTable.rownum <= ? + ? AND resultTable.rownum > ? ORDER BY resultTable.rownum;

I hope it helps. Thank you.

9

Unfortunately, that doesn’t help at all. I need to see the actual SQL queries and ideally also the actual HQL queries. From the naming it seems like you mixed up the two. In Hibernate ORM 6 the row number is named rn, but what you posted here is exactly the other way around.

10

Unfortunately, I can’t post the actual queries but these altered queries match the version I mentioned with some bad replacing (including those rn), truth be told, but in structure they are correct. The source for these is the same and follows the code:

final String queryStmt = "FROM SomeEntity$StatusOne p LEFT JOIN FETCH p.someAttribute dna WHERE p.some.thing = :someThing”;

final SortPropertiesResolver sortResolver = new SortPropertiesResolver.Builder()
  .addMapping("displayTitle", "LOWER(COALESCE(dna.value, p.name))")
  .build();


final Optional<Sort> sort = Optional.of(page.getSort())
  .map(sortResolver::handleSorting);

final String listQueryStmt = sort
  .map(s -> QueryUtils.applySorting(queryStmt, s))
  .orElse(queryStmt);

final TypedQuery<SomeEntity> query = em.createQuery(listQueryStmt, SomeEntity.class);
query.setParameter(SOME_THING, someThing); 

final List<SomeEntity> result = query.getResultList();

Not sure if this helps but I hope so.

P.S.: I am reposting the queries with a better formatting and fixing that bad replacement you mentioned, because you were right, the hibernate 5 query was messed up and mixed with the 6 one. Now they should be fine and illustrative enough, although the replacements used in one do not necessarily match the other, I just want to highlight the overall structure of the generated queries.

Hibernate 5:

select table0_.PID            as pi1_5_0_,
       table1_.PID            as pi2_4_1_,
       table2_.CN             as cn1_1_2_,
       table0_.LM2            as lm2_5_0_,
       table0_.CT             as ct3_5_0_,
       table0_.CN             as cn10_5_0_,
       table0_.DET            as det4_5_0_,
       table0_.DIT            as dit5_5_0_,
       table0_.LM1            as lm6_5_0_,
       table0_.LM2            as lm7_5_0_,
       table0_.LM3            as lm8_5_0_,
       table0_.PN             as pn9_5_0_,
       table1_.AS             as automati3_4_1_,
       table1_.PK             as pl1_4_1_,
       table1_.PV             as pv4_4_1_,
       table2_.LM2            as lm2_1_2_,
       table2_.CT             as ct3_1_2_,
       table2_.DET            as det4_1_2_,
       table2_.DIT            as dit5_1_2_,
       table2_.LM1            as lm6_1_2_,
       table2_.LM2            as lm7_1_2_,
       table2_.LM3            as lm8_1_2_,
       table2_.NL             as nl9_1_2_,
       table2_.NS             as ns10_1_2_,
       CASE
           WHEN table2_.DET IS NOT NULL AND table2_.DET != '1970-01-01 01:00:00' THEN 'STATUS1'
           WHEN table2_.DIT IS NOT NULL AND table2_.DIT != '1970-01-01 01:00:00' THEN 'STATUS2'
           ELSE 'STATUS3' end as clazz_2_
from TABLE0 table0_
         left outer join TABLE1 table1_ on table0_.PID = table1_.PID and
                                           table1_.PK = 'c.dn'
         left outer join TABLE2 table2_ on table0_.CN = table2_.CN
where CASE
          WHEN table0_.DET IS NOT NULL AND table0_.DET != '1970-01-01 01:00:00' THEN 'STATUS1'
          WHEN table0_.DIT IS NOT NULL AND table0_.DIT != '1970-01-01 01:00:00' THEN 'STATUS2'
          ELSE 'STATUS3' end = 'STATUS3'
  and table0_.CN = 'XXXXX'
order by cast(lower(coalesce(table1_.PV, table0_.PN)) as varchar(255)) asc

Hibernate 6:

SELECT *
FROM (SELECT table1.column1          AS col0,
             table1.column2          AS col1,
             table2.column3          AS col2,
             table2.column4          AS col3,
             table2.column5          AS col4,
             table2.column6          AS col5,
             table1.column7          AS col6,
             table3.column8          AS col7,
             CASE
                 WHEN table3.column9 IS NOT NULL AND table3.column9 != ‘1970-01-01 01:00:00’ THEN ‘STATUS_2’
                 WHEN table3.column10 IS NOT NULL AND table3.column10 != ‘1970-01-01 01:00:00’ THEN ‘STATUS_1’
                 ELSE ‘STATUS_3’ END AS col8,
             table3.column11         AS col9,
             table3.column12         AS col10,
             table3.column13         AS col11,
             table3.column14         AS col12,
             table3.column15         AS col13,
             table3.column16         AS col14,
             table3.column17         AS col15,
             table3.column18         AS col16,
             table3.column19         AS col17,
             table1.column20         AS col18,
             table1.column21         AS col19,
             table1.column22         AS col20,
             table1.column23         AS col21,
             table1.column24         AS col22,
             table1.column25         AS col23,
             row_number()               OVER (ORDER BY LOWER(COALESCE(table2.column6, table1.column25)))  rn
      FROM table1
               LEFT JOIN table2 ON table1.column3 = table2.column3
               LEFT JOIN table3 ON table3.column7 = table1.column7
      WHERE table1.column7 = ?
        AND CASE
                WHEN table1.column21 IS NOT NULL AND table1.column21 != ‘1970-01-01 01:00:00’ THEN ‘STATUS_1’
                WHEN table1.column19 IS NOT NULL AND table1.column19 != ‘1970-01-01 01:00:00’ THEN ‘STATUS_2’
                ELSE ‘STATUS_3’ END = ‘STATUS_3’) resultTable
WHERE resultTable.rn <= ? + ?
  AND resultTable.rn > ?
ORDER BY resultTable.rn
11

Another problem is that you seem to apply pagination with Hibernate 6, but the Hibernate 5 query does not contain pagination related parts. I don’t know what DB2 does internally for queries that use a row_number(), but it might very well just allocate a buffer in a temporary table space to hold data for computing this information.

12

The thing is that these two queries have the same source code. I am not enforcing anything different in the latest version so I am assuming Hibernate is making some decisions on how to convert the query and I would like to understand if I could somehow configure those decisions without changing the source query. Would that be a possibility?

13

To answer that, you will have to share the exact code that you are executing, because the code you have shown so far does not contain any pagination related code, though that’s the only way to get a row_number() in the SQL query.

14

The only thing that is being applied to the initial query is the sorting clause which changes the query from:

FROM SomeEntity$StatusOne p LEFT JOIN FETCH p.someAttribute dna WHERE p.some.thing = :someThing

To:

FROM SomeEntity$StatusOne p LEFT JOIN FETCH p.someAttribute dna WHERE p.some.thing = :someThing” order by LOWER(COALESCE(dna.value, p.name)) asc

This last statement is the one that gets executed with:

final TypedQuery<SomeEntity> query = em.createQuery(listQueryStmt, SomeEntity.class);

And throws the error with:

final List<CBaseDataProfile> result = query.getResultList()

Not sure what else I can add. In the end it’s just that string that gets passed to the entity manager to create the query object, later the error is thrown when we try to get the result list.

15

Please debug into https://github.com/hibernate/hibernate-orm/blob/7c58fe9a16eb15e5d1432e4bb3434c89cf214e16/hibernate-core/src/main/java/org/hibernate/sql/ast/spi/AbstractSqlAstTranslator.java#L5027 i.e. AbstractSqlAstTranslator#emulateFetchOffsetWithWindowFunctions and try to figure out why this method is called in the first place.

16

This is being called from DB2SqlAstTranslator.java line 172.

image
image1198×295 31.7 KB

getQueryPartForRowNumbering() != querySpec && !supportsOffsetClause() && hasOffset( querySpec ) this is the condition that evaluates to true. getQueryPartForRowNumbering() is null which makes it different from querySpec.

I hope that’s what you were asking for.

Not sure if this is relevant or helps at all, but I am almost certain that this has something to do with the sorting clause because without it the query runs without problems.

17

Well, it seems that your query spec has an offset, so you seem to call Query#setFirstResult somewhere. Please show the contents of the limit and querySpec variables.

18

Indeed I have this before getting the result list:

query.setFirstResult(Math.toIntExact(page.getOffset()));
query.setMaxResults(page.getPageSize());

Didn’t added initially because even if I remove it the root exception will always be the temporary table issue.

Screenshot 2024-01-23 at 15.49.11

Anything else you want to see?

19

But this is exactly the problem. The last ORM 5 query you posted does not include row_number(). So I conclude the query you are running on ORM 5 does not use setFirstResult/setMaxResults, hence the SQL is different.

What dialect were you using with ORM 5?

You can try avoid using an offset by using:

int offset = Math.toIntExact(page.getOffset());
if (offset > 0) {
    query.setFirstResult(offset);
}
query.setMaxResults(page.getPageSize());
20

In the end, this won’t change a thing though, because as soon as you specify an offset greater than 0, even ORM 5 will produce such a nested subquery and then you’ll run into the same temporary table space problem.

21

The code didn’t change at all, just springboot and hibernate versions. The exact same code is producing the two different translated queries I posted before. The problem happens with ORM 6, not 5, just to be clear. The dialect is and was DB2, it did not change either.