Temporary table handling

Hibernate ORM
1

Hey guys,

I have a few questions regarding HQL. I have following entities:

@Entity
public class MyObject {
    @Id
    private String id;
    @Embedded
    private PolyString name;
    ...
}

@Embeddable
public class PolyString {
    private String orig;
    private String norm;
    ...
}

This code has to run on top of h2, mysql, mariadb, oracle, postgresql, sqlserver - so I’m happy to use hibernate, dialects and so on. I’m using Hibernate 5.2.2.Final.

Question 1:
I’ve tried to write HQL to update my objects like this:

Query query = session.createQuery("update MyObject o set o.name = :name where o.id = :id");
query.setParameter("name", new PolyString("a", "a"));
query.setParameter("id", "123");
query.executeUpdate();

This query will fail, as it’s not able to handle that embedded entity correctly - exception:

Caused by: org.hibernate.QueryException: assignment in set-clause not associated with equals [update     com.example.MyObject o set o.name = :name where o.id = :id]
	at org.hibernate.QueryException.generateQueryException(QueryException.java:120)
    at org.hibernate.QueryException.wrapWithQueryString(QueryException.java:103)
    at org.hibernate.hql.internal.ast.QueryTranslatorImpl.doCompile(QueryTranslatorImpl.java:218)
    at org.hibernate.hql.internal.ast.QueryTranslatorImpl.compile(QueryTranslatorImpl.java:142)
    at org.hibernate.engine.query.spi.HQLQueryPlan.<init>(HQLQueryPlan.java:115)
    at org.hibernate.engine.query.spi.HQLQueryPlan.<init>(HQLQueryPlan.java:77)
Caused by: org.hibernate.QueryException: assignment in set-clause not associated with equals
    at org.hibernate.hql.internal.ast.tree.AssignmentSpecification.<init>(AssignmentSpecification.java:43)
    at org.hibernate.hql.internal.ast.HqlSqlWalker.evaluateAssignment(HqlSqlWalker.java:1278)
    at org.hibernate.hql.internal.ast.HqlSqlWalker.evaluateAssignment(HqlSqlWalker.java:1272)
    at org.hibernate.hql.internal.antlr.HqlSqlBaseWalker.assignment(HqlSqlBaseWalker.java:1063)
    at org.hibernate.hql.internal.antlr.HqlSqlBaseWalker.setClause(HqlSqlBaseWalker.java:763)
    at org.hibernate.hql.internal.antlr.HqlSqlBaseWalker.updateStatement(HqlSqlBaseWalker.java:379)
    at org.hibernate.hql.internal.antlr.HqlSqlBaseWalker.statement(HqlSqlBaseWalker.java:267)

Am I doing something wrong? I know I can do set o.name.orig = :orig, o.name.norm = :norm but that looks kinda stupid. I thought that hibernate will know what to do.

Question 2:
If if overcome previous issue, then this query doesn’t generate only SQL update but it will create temp table, insert ids using select based on where clause from update, then does update using this temp table and at last it drops temp table. Now the question is - if I know that that HQL update has to update only 1 row (or maybe just a few rows) how can I disable that temp table magic, but just for this query, not globaly?

I expect that MultiTableBulkIdStrategy handles all temp table stuff. Is it possible to send some query hints there? I can’t create custom implementation as for different DB vendors hibernate uses different multitable strategies so I would have to reimplement almost all of them. Even if id did. I couldn’t find a place where I would be able to read some query hint or something passed from that query code.

Question 3:

Dialect.supportsTemporaryTables();
Dialect.generateTemporaryTableName();
Dialect.dropTemporaryTableAfterUse();
Dialect.getDropTemporaryTableString();

These methods disapeared from Dialect class, MultiTableBulkIdStrategy was introduced. If I want to do some custom SQL and I need temp tables - is it possible to get that SQL “parts” somewhere? For now I overcomed this with nasty hack - I’ve created class hierarchy TempTableDialect and copied there implementations for these methods from other dialects. I don’t like it but I don’t know how to handle it. No docs for it either.

Bigger picture
I’m trying to implement relative changes processing on top of hibernate. I want to get rid of session.merge() calls. It causes a lot of selects to fetch detached instances and then also unnecessary updates/deletes. I do understand that it’s necessary to have robust algorithm. In our case we do get relative change description even at repository layer like:

  • add some reference to XY
  • update givenName to “abc”

So that we know we want to insert row here or delete row there, update some column and so on.

@sebersole you’re mentioned as an author all around that multitable code, maybe you can help a bit?

Thank you very much.

2

Regarding question 1:

Caused by: org.hibernate.QueryException: assignment in set-clause not associated with equals
    at org.hibernate.hql.internal.ast.tree.AssignmentSpecification.<init>(AssignmentSpecification.java:43)

This validation is actually checking whether the relational operator is an equals as opposed to another type (greater-than, less-than, etc.). It should not really matter if the operands are composite or not. Either there is a bug in the code or the query you pasted is not what you actually pass - I’d have to see a test case or you could post the HQL syntax/AST tree.

Regarding question 2:

The Dialect is the thing that hands Hibernate the default MultiTableBulkIdStrategy to use (org.hibernate.dialect.Dialect#getDefaultMultiTableBulkIdStrategy). Or there is a setting. The setting is “static”. If you need to support multiple databases and need to handle the id-table stuff in a non-standard way, then you’d have to provide custom Dialect for each and have them return appropriate strategy.

Another option would be to hook into the SessionFactory boot strap process. Ultimately you want to affect org.hibernate.boot.SessionFactoryBuilder#applyMultiTableBulkIdStrategy. By the time the SessionFactoryBuilder is used the Dialect is already known, so you could use that to set your Dialect-specific custom strategy.

Regarding question 3:

See above. Dialect’s instead return their “native” strategy, which would encapsulate all of this information - you know OO - encapsulation

“No docs for it either.” - no docs for what exactly?

3

If you get this error, I think you should build a replicating test case and open a new Jira issue.

Meanwhile, you can rewrite the JPQL query like this:

Query query = session.createQuery(
    "update MyObject o set o.name.orig = :orig, o.name.norm= :norm where o.id = :id");
query.setParameter("orig", "a");
query.setParameter("norm", "a");
query.setParameter("id", "123");
query.executeUpdate();

This one should work better for you.

4

Hi Steve,

first of all thanks for quick response. Posted question contains a bit simplified example. Here’s the AST from my test:

2018-01-08 14:56:08,024 [main] DEBUG (o.h.hql.internal.ast.QueryTranslatorImpl): parse() - HQL: update com.evolveum.midpoint.repo.sql.data.common.RUser u set u.givenName = :p where u.oid = :pp
2018-01-08 14:56:08,027 [main] DEBUG (o.h.hql.internal.ast.QueryTranslatorImpl): --- HQL AST ---
 \-[UPDATE] Node: 'update'
    +-[FROM] Node: 'FROM'
    |  \-[RANGE] Node: 'RANGE'
    |     +-[DOT] Node: '.'
    |     |  +-[DOT] Node: '.'
    |     |  |  +-[DOT] Node: '.'
    |     |  |  |  +-[DOT] Node: '.'
    |     |  |  |  |  +-[DOT] Node: '.'
    |     |  |  |  |  |  +-[DOT] Node: '.'
    |     |  |  |  |  |  |  +-[DOT] Node: '.'
    |     |  |  |  |  |  |  |  +-[IDENT] Node: 'com'
    |     |  |  |  |  |  |  |  \-[IDENT] Node: 'evolveum'
    |     |  |  |  |  |  |  \-[IDENT] Node: 'midpoint'
    |     |  |  |  |  |  \-[IDENT] Node: 'repo'
    |     |  |  |  |  \-[IDENT] Node: 'sql'
    |     |  |  |  \-[IDENT] Node: 'data'
    |     |  |  \-[IDENT] Node: 'common'
    |     |  \-[IDENT] Node: 'RUser'
    |     \-[ALIAS] Node: 'u'
    +-[SET] Node: 'set'
    |  \-[EQ] Node: '='
    |     +-[DOT] Node: '.'
    |     |  +-[IDENT] Node: 'u'
    |     |  \-[IDENT] Node: 'givenName'
    |     \-[COLON] Node: ':'
    |        \-[IDENT] Node: 'p'
    \-[WHERE] Node: 'where'
       \-[EQ] Node: '='
          +-[DOT] Node: '.'
          |  +-[IDENT] Node: 'u'
          |  \-[IDENT] Node: 'oid'
          \-[COLON] Node: ':'
             \-[IDENT] Node: 'pp'

2018-01-08 14:56:08,027 [main] DEBUG (org.hibernate.hql.internal.ast.ErrorCounter): throwQueryException() : no errors
2018-01-08 14:56:08,031 [main] DEBUG (o.h.hql.internal.antlr.HqlSqlBaseWalker): update << begin [level=1, statement=update]
2018-01-08 14:56:08,031 [main] DEBUG (o.hibernate.hql.internal.ast.tree.FromElement): FromClause{level=1} : com.evolveum.midpoint.repo.sql.data.common.RUser (u) -> ruser0_
2018-01-08 14:56:08,031 [main] DEBUG (o.h.hql.internal.ast.tree.FromReferenceNode): Resolved : u -> oid
2018-01-08 14:56:08,032 [main] DEBUG (org.hibernate.hql.internal.ast.tree.DotNode): getDataType() : givenName -> org.hibernate.type.ComponentType@76876421
2018-01-08 14:56:08,032 [main] DEBUG (org.hibernate.hql.internal.ast.tree.DotNode): Terminal getPropertyPath = [givenName]
2018-01-08 14:56:08,032 [main] DEBUG (o.h.hql.internal.ast.tree.FromReferenceNode): Resolved : u.givenName -> givenName_norm, givenName_orig

Entities in this test (github):
RUser.java
RPolyString.java

Question 2:
I’ll have a look at org.hibernate.boot.SessionFactoryBuilder#applyMultiTableBulkIdStrategy, but I guess that setting will be used globally. I hope that I’ll be able to set some kind of query hint to specific queries and then use it in bulk id strategy later on (custom impl).

Question 3:
I’m not even sure. I’m probably trying to push hibernate too hard and use it for cases where I should just drop to native sql.

I’ve also edited original question, added more description.

5

Your “updated” question really just adds a completely unrelated question. Its unrelated at all to what else is being discussed, so please start a new discussion for that.

This is the check that is causing you trouble:

		if ( eq.getType() != HqlSqlTokenTypes.EQ ) {
			throw new QueryException( "assignment in set-clause not associated with equals" );
		}

so it clearly seems like something is just not “matching up”. Clearly the assignment in your AST is [EQ]

Well first and foremost, your real desire here is to contextually not use temp tables. The trouble is that Hibernate cannot inherently know this[1].

[1] Actually there are 2 cases where Hibernate could conceivably know this: simple restriction on id and simple restriction on natural-id - and we have discussed special handling for those cases.

Beyond that, I really just do not understand positions like this that want multiple settings to define parts of something rather than a singular encapsulation (you know, OO ftw) of that whole. But regardless, that is not how this code works and its not something I am going to do.

I don’t see how this is pushing Hibernate too hard. I think you just don’t want to write an if / else if statement, which of course is your prerogative.

6

so it clearly seems like something is just not “matching up”. Clearly the assignment in your AST is [EQ]

cool, so query as such is probably fine and should work.

Well first and foremost, your real desire here is to contextually not use temp tables. The trouble is that Hibernate cannot inherently know this[1].
[1] Actually there are 2 cases where Hibernate could conceivably know this: simple restriction on id and simple restriction on natural-id - and we have discussed special handling for those cases.

This is what I meant. My implementation will hopefully use case [1], so I’ll check again MultiTableBulkIdStrategy and it’s implementations to understand it better.

Beyond that, I really just do not understand positions like this that want multiple settings to define parts of something rather than a singular encapsulation (you know, OO ftw) of that whole. But regardless, that is not how this code works and its not something I am going to do.

I’m sorry, I probably sounded a bit offensive. I do agree with cleaning up Dialect class, temp table handling and encapsulation of that code (I also went through all those commits), I just got lost during upgrade from some ancient hibernate version.

Thanks again for explanation.

7

Correct - it should work. If it does not, then as Vlad says open a Jira with a reproducible test case.

The handling for this would be outside of MultiTableBulkIdStrategy. Basically the idea there would be to recognize these situations in the code that handles UPDATE/DELETE HQL queries and to somehow do something different in these 2 cases. The specifics of what exactly to do differently are still being defined. The change would be part of 6.0 to take advantage of the new query syntax trees. You can take a sneak peek at the 6.0 branch at org.hibernate.query.sqm.consume.multitable.spi.IdTableStrategy and impls. The specific handling for simple id/natural-id restriction handling is not there yet in the impls, but you can see how easy it is from the signatures

8

To clarify a bit… updating composites is currently not supported - its just not something I have gotten around to implementing. But it should lead to a much different exception: QueryException( "Components currently not assignable in update statements" );