Invalid query when using a JPASQLQuery with an entity and column projection #620

Closed
nithril opened this Issue Jan 8, 2014 · 16 comments

Comments

Projects
None yet
2 participants
@nithril
Contributor

nithril commented Jan 8, 2014

Hello,

I have created this type of query :

query = new JPASQLQuery(entityManager, templates);
List<Cat> cats = query.from(cat).list(catEntity, cat.id);

But the generated query looks like the following:

select cat col__1_1, cat.id col__1_2
from CAT cat

Which is not valid because of cat col__1_1

Without cat.id projection I got the valid one :

select cat.*
from CAT cat

Thanks,

Nicolas

@timowest

This comment has been minimized.

Show comment
Hide comment
@timowest

timowest Jan 8, 2014

Member

Could you try with the latest SNAPSHOT? https://oss.sonatype.org/content/repositories/snapshots/

Member

timowest commented Jan 8, 2014

Could you try with the latest SNAPSHOT? https://oss.sonatype.org/content/repositories/snapshots/

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

@nithril

This comment has been minimized.

Show comment
Hide comment
@nithril

nithril Jan 9, 2014

Contributor

My query is similar to the cat one, but my table name is COMPANY_AIFM_AIF

I get the following sql:

select companyAifmAif.*, companyAifmAif.IDCOMPANYAIFMAIF col__1_2
from COMPANY_AIFM_AIF companyAifmAif

IDCOMPANYAIFMAIF is the PK.

But the returned tuple contains:

  • the COMPANY_AIFM_AIF primary key instead of the entity
  • another columns entry instead of the primary key (which is too a number).

The following statement throws a class cast exception

new JPASQLQuery(entityManager, oracleTemplates).from(sCompanyAifmAif).list(qCompanyAifmAif, sCompanyAifmAif.idcompanyaifmaif,  sCompanyAifmAif.idcompanyaifmaif)

Java.lang.String cannot be cast to java.lang.Number.

Contributor

nithril commented Jan 9, 2014

My query is similar to the cat one, but my table name is COMPANY_AIFM_AIF

I get the following sql:

select companyAifmAif.*, companyAifmAif.IDCOMPANYAIFMAIF col__1_2
from COMPANY_AIFM_AIF companyAifmAif

IDCOMPANYAIFMAIF is the PK.

But the returned tuple contains:

  • the COMPANY_AIFM_AIF primary key instead of the entity
  • another columns entry instead of the primary key (which is too a number).

The following statement throws a class cast exception

new JPASQLQuery(entityManager, oracleTemplates).from(sCompanyAifmAif).list(qCompanyAifmAif, sCompanyAifmAif.idcompanyaifmaif,  sCompanyAifmAif.idcompanyaifmaif)

Java.lang.String cannot be cast to java.lang.Number.

@nithril

This comment has been minimized.

Show comment
Hide comment
@nithril

nithril Jan 9, 2014

Contributor

I reproduce the issue with AbstractSQLTest.EntityQueries4 with H2SuiteTest

tuple.get(catEntity) return C. Looks like the discriminator value.
tuple.get(cat.id) return the id

List<Tuple> cats = query().from(cat).list(catEntity, cat.id, cat.name);
assertNotNull(tuple.get(cat.name));

return a boolean

Contributor

nithril commented Jan 9, 2014

I reproduce the issue with AbstractSQLTest.EntityQueries4 with H2SuiteTest

tuple.get(catEntity) return C. Looks like the discriminator value.
tuple.get(cat.id) return the id

List<Tuple> cats = query().from(cat).list(catEntity, cat.id, cat.name);
assertNotNull(tuple.get(cat.name));

return a boolean

@timowest

This comment has been minimized.

Show comment
Hide comment
@timowest

timowest Jan 9, 2014

Member

Yes, I noticed it now myself.

Member

timowest commented Jan 9, 2014

Yes, I noticed it now myself.

@timowest

This comment has been minimized.

Show comment
Hide comment
@timowest

timowest Jan 10, 2014

Member

It appears this is generally not possible. What works now are

  • fully scalar projections
  • single entity projections
  • multiple entity projections
Member

timowest commented Jan 10, 2014

It appears this is generally not possible. What works now are

  • fully scalar projections
  • single entity projections
  • multiple entity projections
@nithril

This comment has been minimized.

Show comment
Hide comment
@nithril

nithril Jan 10, 2014

Contributor

Annoying :(

Have you any advice to create a query with a subquery in the inner join and a projection from the subquery :

select t.*, sq.max
from T t
inner join (sq) sq on sq.id = t.id

where T is an entity

Contributor

nithril commented Jan 10, 2014

Annoying :(

Have you any advice to create a query with a subquery in the inner join and a projection from the subquery :

select t.*, sq.max
from T t
inner join (sq) sq on sq.id = t.id

where T is an entity

@timowest

This comment has been minimized.

Show comment
Hide comment
@timowest

timowest Jan 11, 2014

Member

Got it working now. Could you try with the latest SNAPSHOT?

Member

timowest commented Jan 11, 2014

Got it working now. Could you try with the latest SNAPSHOT?

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

@nithril

This comment has been minimized.

Show comment
Hide comment
@nithril

nithril Jan 13, 2014

Contributor

Test 1 : List of an entity field KO

I don't know if it is a valid statement but I have test this query:

List<Tuple> cats = query().from(cat).list(catEntity.id);

Assume that cat id is mapped to column CATID instead of ID.

The generated query contains: SELECT CAT.ID instead of SELECT CAT.CATID

Test 2 : List of entity KO

I run my original query

new JPASQLQuery(entityManager, oracleTemplates).from(sCompanyAifmAif).list(qCompanyAifmAif)

Generated query contains curly brace:

/* dynamic native SQL query */ 
select {companyAifmAif.*}
from COMPANY_AIFM_AIF companyAifmAif

Oracle throws an NPE. It seems there is a bug with Oracle and curly brace
http://www.coderanch.com/t/304220/JDBC/databases/Create-Java-source-Oracle-jdbc

I'm not even sure this query is valid

If I test this query with a jdbc statement and statement.setEscapeProcessing(false) I got ORA-00911: invalid character
If I test this query with a jdbc statement and statement.setEscapeProcessing(true) I got an NPE

Test 3 : List of tuple OK

new JPASQLQuery(entityManager, oracleTemplates).from(sCompanyAifmAif).list(qCompanyAifmAif, sCompanyAifmAif.name)

Generated query

select companyAifmAif.IDCOMPANYAIFMAIF as IDCOMPAN1_6_0_, companyAifmAif.ACTIVE as ACTIVE2_6_0_, companyAifmAif.IDCOMPANYAIFM as IDCOMPAN7_6_0_, companyAifmAif.FREQUENCY as FREQUENC3_6_0_, companyAifmAif.AIFNAME as AIFNAME4_6_0_, companyAifmAif.NATIONALCODE as NATIONAL5_6_0_, companyAifmAif.REGISTRATIONDATE as REGISTRA6_6_0_, companyAifmAif.AIFNAME
from COMPANY_AIFM_AIF companyAifmAif

I got a tuple list of companyAifmAif entity and aifName.

Test 4 : List of DTO containing only an Entity KO

new JPASQLQuery(entityManager, oracleTemplates).from(sCompanyAifmAif).list(ConstructorExpression.create(MyDTO.class, qCompanyAifmAif));

Generate:

select companyAifmAif
from COMPANY_AIFM_AIF companyAifmAif
Contributor

nithril commented Jan 13, 2014

Test 1 : List of an entity field KO

I don't know if it is a valid statement but I have test this query:

List<Tuple> cats = query().from(cat).list(catEntity.id);

Assume that cat id is mapped to column CATID instead of ID.

The generated query contains: SELECT CAT.ID instead of SELECT CAT.CATID

Test 2 : List of entity KO

I run my original query

new JPASQLQuery(entityManager, oracleTemplates).from(sCompanyAifmAif).list(qCompanyAifmAif)

Generated query contains curly brace:

/* dynamic native SQL query */ 
select {companyAifmAif.*}
from COMPANY_AIFM_AIF companyAifmAif

Oracle throws an NPE. It seems there is a bug with Oracle and curly brace
http://www.coderanch.com/t/304220/JDBC/databases/Create-Java-source-Oracle-jdbc

I'm not even sure this query is valid

If I test this query with a jdbc statement and statement.setEscapeProcessing(false) I got ORA-00911: invalid character
If I test this query with a jdbc statement and statement.setEscapeProcessing(true) I got an NPE

Test 3 : List of tuple OK

new JPASQLQuery(entityManager, oracleTemplates).from(sCompanyAifmAif).list(qCompanyAifmAif, sCompanyAifmAif.name)

Generated query

select companyAifmAif.IDCOMPANYAIFMAIF as IDCOMPAN1_6_0_, companyAifmAif.ACTIVE as ACTIVE2_6_0_, companyAifmAif.IDCOMPANYAIFM as IDCOMPAN7_6_0_, companyAifmAif.FREQUENCY as FREQUENC3_6_0_, companyAifmAif.AIFNAME as AIFNAME4_6_0_, companyAifmAif.NATIONALCODE as NATIONAL5_6_0_, companyAifmAif.REGISTRATIONDATE as REGISTRA6_6_0_, companyAifmAif.AIFNAME
from COMPANY_AIFM_AIF companyAifmAif

I got a tuple list of companyAifmAif entity and aifName.

Test 4 : List of DTO containing only an Entity KO

new JPASQLQuery(entityManager, oracleTemplates).from(sCompanyAifmAif).list(ConstructorExpression.create(MyDTO.class, qCompanyAifmAif));

Generate:

select companyAifmAif
from COMPANY_AIFM_AIF companyAifmAif
@nithril

This comment has been minimized.

Show comment
Hide comment
@nithril

nithril Jan 13, 2014

Contributor

I create a test project available here: https://github.com/nithril/querydsl-620

Hope it helps

Contributor

nithril commented Jan 13, 2014

I create a test project available here: https://github.com/nithril/querydsl-620

Hope it helps

@timowest

This comment has been minimized.

Show comment
Hide comment
@timowest

timowest Jan 13, 2014

Member

All except for the last one are fixed now. I believe the remaining issue is the same like #626.

Member

timowest commented Jan 13, 2014

All except for the last one are fixed now. I believe the remaining issue is the same like #626.

@nithril

This comment has been minimized.

Show comment
Hide comment
@nithril

nithril Jan 13, 2014

Contributor

Great, thank you very much. I will test tomorrow

Contributor

nithril commented Jan 13, 2014

Great, thank you very much. I will test tomorrow

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

@timowest

This comment has been minimized.

Show comment
Hide comment
@timowest

timowest Jan 14, 2014

Member

Could you try again with the latest SNAPSHOT?

Member

timowest commented Jan 14, 2014

Could you try again with the latest SNAPSHOT?

@nithril

This comment has been minimized.

Show comment
Hide comment
@nithril

nithril Jan 14, 2014

Contributor

Test with 3.3.1.BUILD-20140114.190813-8
Test 2, 3, 4 : success
Test 1 : fail

Contributor

nithril commented Jan 14, 2014

Test with 3.3.1.BUILD-20140114.190813-8
Test 2, 3, 4 : success
Test 1 : fail

@timowest

This comment has been minimized.

Show comment
Hide comment
@timowest

timowest Jan 14, 2014

Member

Yes, sorry, I forgot to mention that the test case 1 is not valid. Querydsl can't deduce that you want the column and not the property. Q-types based on entities should only be used for entity projections in JPA native queries.

Member

timowest commented Jan 14, 2014

Yes, sorry, I forgot to mention that the test case 1 is not valid. Querydsl can't deduce that you want the column and not the property. Q-types based on entities should only be used for entity projections in JPA native queries.

@nithril

This comment has been minimized.

Show comment
Hide comment
@nithril

nithril Jan 14, 2014

Contributor

Thank you very much for this fix!

Contributor

nithril commented Jan 14, 2014

Thank you very much for this fix!

@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