JPASQLQuery : Projection columns collision #633

Closed
nithril opened this Issue Jan 20, 2014 · 9 comments

Comments

Projects
None yet
2 participants
@nithril
Contributor

nithril commented Jan 20, 2014

Hello,

I have created this type of query:

query = new JPASQLQuery(entityManager, templates);
List<Tuple> list = query.from(cat).innerJoin(father).on(cat.id.eq(father.id)).list(catEntity, fatherEntity);

It generates this SQL:

select cat.*, father.*
from CAT cat
inner join CAT father on father.id = cat.id

Columns collide. father entity contains the name of cat entity.

@timowest

This comment has been minimized.

Show comment
Hide comment
@timowest

timowest Jan 20, 2014

Member

The Hibernate docs describe this workaround http://docs.jboss.org/hibernate/orm/4.3/manual/en-US/html/ch18.html#d5e8292

But it seems to work only if you use the Hibernate API directly, and not the JPA Hibernate wrapper.

Do you have any other idea how this could be fixed?

Member

timowest commented Jan 20, 2014

The Hibernate docs describe this workaround http://docs.jboss.org/hibernate/orm/4.3/manual/en-US/html/ch18.html#d5e8292

But it seems to work only if you use the Hibernate API directly, and not the JPA Hibernate wrapper.

Do you have any other idea how this could be fixed?

@nithril

This comment has been minimized.

Show comment
Hide comment
@nithril

nithril Jan 20, 2014

Contributor

Curly braces workaround do not work with Oracle driver (see #620).

Maybe the issue could be fixed by expanding the columns automaticly.

Contributor

nithril commented Jan 20, 2014

Curly braces workaround do not work with Oracle driver (see #620).

Maybe the issue could be fixed by expanding the columns automaticly.

@timowest

This comment has been minimized.

Show comment
Hide comment
@timowest

timowest Jan 20, 2014

Member

Curly braces workaround do not work with Oracle driver (see #620).

I think when you use the Hibernate API the curly braces content is handled specially to avoid the column name clashes, but it doesn't seem to work with the JPA API, where the curly braces are not handled.

Maybe the issue could be fixed by expanding the columns automaticly.

Yes, but that might change the semantics of what's returned, because then Querydsl needs to populate the entities.

Member

timowest commented Jan 20, 2014

Curly braces workaround do not work with Oracle driver (see #620).

I think when you use the Hibernate API the curly braces content is handled specially to avoid the column name clashes, but it doesn't seem to work with the JPA API, where the curly braces are not handled.

Maybe the issue could be fixed by expanding the columns automaticly.

Yes, but that might change the semantics of what's returned, because then Querydsl needs to populate the entities.

@nithril

This comment has been minimized.

Show comment
Hide comment
@nithril

nithril Jan 20, 2014

Contributor

I think when you use the Hibernate API the curly braces content is handled specially to avoid the column name clashes, but it doesn't seem to work with the JPA API, where the curly braces are not handled.

I have try their example and I got an NPE:

org.hibernate.internal.SessionImpl session = entityManager.getDelegate();
session.createSQLQuery("select {cat.*} ...");

Yes, but that might change the semantics of what's returned, because then Querydsl needs to populate the entities.

I don't undertstand your statement. I do not have enough knowledge of how JPA/QueryDSL runs together. But explainations are welcome ;)

EDIT: Ok, after digging, QueryDSL constructs the query and hibernate hydrates the entities from the query.

Contributor

nithril commented Jan 20, 2014

I think when you use the Hibernate API the curly braces content is handled specially to avoid the column name clashes, but it doesn't seem to work with the JPA API, where the curly braces are not handled.

I have try their example and I got an NPE:

org.hibernate.internal.SessionImpl session = entityManager.getDelegate();
session.createSQLQuery("select {cat.*} ...");

Yes, but that might change the semantics of what's returned, because then Querydsl needs to populate the entities.

I don't undertstand your statement. I do not have enough knowledge of how JPA/QueryDSL runs together. But explainations are welcome ;)

EDIT: Ok, after digging, QueryDSL constructs the query and hibernate hydrates the entities from the query.

@nithril

This comment has been minimized.

Show comment
Hide comment
@nithril

nithril Jan 20, 2014

Contributor

Ok, I hate that, but I misuse the createSQLQuery (wrong addEntity table alias). You right, curly braces is handled specically and expanded.

Strange that #620 curly brace test gave an error. It seems to follow the same execution path : SQLCustomQuery constructor is called in both cases executing the following statement which expands curly brace:

        SQLQueryParser parser = new SQLQueryParser( sqlQuery, new ParserContext( aliasContext ), factory );
        this.sql = parser.process();

I only got an NPE when the final query contains curly brace. The final query contains a curly braces because alias is not correctly defined.

Contributor

nithril commented Jan 20, 2014

Ok, I hate that, but I misuse the createSQLQuery (wrong addEntity table alias). You right, curly braces is handled specically and expanded.

Strange that #620 curly brace test gave an error. It seems to follow the same execution path : SQLCustomQuery constructor is called in both cases executing the following statement which expands curly brace:

        SQLQueryParser parser = new SQLQueryParser( sqlQuery, new ParserContext( aliasContext ), factory );
        this.sql = parser.process();

I only got an NPE when the final query contains curly brace. The final query contains a curly braces because alias is not correctly defined.

@nithril

This comment has been minimized.

Show comment
Hide comment
@nithril

nithril Jan 20, 2014

Contributor

I have set Conversions.ALL template to {{0}.*}

        new JPASQLQuery(entityManager , new H2Templates()).from(sCat).list(qCat);
select {cat.*}
from CAT cat

The substition of curly brace is not done because the alias is set to alias1 by AbstractEntityManagerImpl.createNativeQuery.

I think all come from AbstractJPASQLQuery.createQuery. With one entity projection, QueryDSL lets Hibernate add the entity (alias is not ok). With more than one, QueryDSL adds the entities with correct aliases.

Hope it helps

Contributor

nithril commented Jan 20, 2014

I have set Conversions.ALL template to {{0}.*}

        new JPASQLQuery(entityManager , new H2Templates()).from(sCat).list(qCat);
select {cat.*}
from CAT cat

The substition of curly brace is not done because the alias is set to alias1 by AbstractEntityManagerImpl.createNativeQuery.

I think all come from AbstractJPASQLQuery.createQuery. With one entity projection, QueryDSL lets Hibernate add the entity (alias is not ok). With more than one, QueryDSL adds the entities with correct aliases.

Hope it helps

timowest added a commit that referenced this issue Jan 21, 2014

@timowest

This comment has been minimized.

Show comment
Hide comment
@timowest

timowest Jan 21, 2014

Member

Thanks for the investigation. Could you try again?

Member

timowest commented Jan 21, 2014

Thanks for the investigation. Could you try again?

@nithril

This comment has been minimized.

Show comment
Hide comment
@nithril

nithril Jan 22, 2014

Contributor

It works. Thanks!

Contributor

nithril commented Jan 22, 2014

It works. Thanks!

@timowest

This comment has been minimized.

Show comment
Hide comment
@timowest

timowest Feb 8, 2014

Member

Released in 3.3.1

Member

timowest commented Feb 8, 2014

Released in 3.3.1

@timowest timowest closed this Feb 8, 2014

@timowest timowest added this to the 3.3.1 milestone Apr 13, 2014

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment