Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Misses parenthesis on union with inner subquery #310

Closed
Ghalleb opened this issue Dec 11, 2012 · 3 comments
Closed

Misses parenthesis on union with inner subquery #310

Ghalleb opened this issue Dec 11, 2012 · 3 comments
Milestone

Comments

@Ghalleb
Copy link

@Ghalleb Ghalleb commented Dec 11, 2012

Hi,

When I use a subquery inside an union I have a SQL error

Here is the code:

    final SQLTemplates templates = new PostgresTemplates();
    final JPASQLQuery queryS = new JPASQLQuery(entityManager, templates);
    final Map<String, ListSubQuery> sourcemap = new HashMap<String, ListSubQuery>();

    sourcemap.put("farmer", new SQLSubQuery()
            .from(SFarmer.farmer)
            .list(SFarmer.farmer.id.as("id")));

    final ListSubQuery<Integer> subqueryFarmerA = new SQLSubQuery()
            .from(SFarmer.farmer)
            .list(SFarmer.farmer.id.as("id"));

    final PathBuilder<Object[]> farmerA = new PathBuilder<Object[]>(Object[].class, "farmerA");

    sourcemap.put("farmer2", new SQLSubQuery()
            .from(SFarmer.farmer)
            .leftJoin(subqueryFarmerA, farmerA)
            .on(SFarmer.farmer.id.eq(farmerA.getNumber("id", Integer.class)))
            .list(SFarmer.farmer.id.as("id")));

    final ListSubQuery[] res = sourcemap.values().toArray(new ListSubQuery[sourcemap.values().size()]);

    final PathBuilder<String> subAlias = new PathBuilder<String>(String.class, "sub");

    final List<Integer> results = queryS.union(subAlias, res)
            .list(subAlias.getNumber("id", Integer.class));

Here is the ouput of the generated SQL execution:

    14:09:16,486 INFO  [STDOUT] Hibernate: 
    14:09:16,486 INFO  [STDOUT]     select
    14:09:16,487 INFO  [STDOUT]         sub.id 
    14:09:16,487 INFO  [STDOUT]     from
    14:09:16,488 INFO  [STDOUT]         (select
    14:09:16,488 INFO  [STDOUT]             farmer.id as id 
    14:09:16,489 INFO  [STDOUT]         from
    14:09:16,489 INFO  [STDOUT]             farmer farmer 
    14:09:16,489 INFO  [STDOUT]         union
    14:09:16,490 INFO  [STDOUT]         select
    14:09:16,490 INFO  [STDOUT]             farmer.id as id 
    14:09:16,491 INFO  [STDOUT]         from
    14:09:16,491 INFO  [STDOUT]             farmer farmer 
    14:09:16,492 INFO  [STDOUT]         left join
    14:09:16,492 INFO  [STDOUT]             select
    14:09:16,493 INFO  [STDOUT]                 farmer.id as id 
    14:09:16,493 INFO  [STDOUT]             from
    14:09:16,494 INFO  [STDOUT]                 farmer farmer as farmerA 
    14:09:16,494 INFO  [STDOUT]                     on farmer.id = farmerA.id
    14:09:16,495 INFO  [STDOUT]             ) as sub
    14:09:16,499 WARN  [org.hibernate.util.JDBCExceptionReporter] SQL Error: 0, SQLState: 42601
    14:09:16,499 ERROR [org.hibernate.util.JDBCExceptionReporter] ERROR: syntax error at or near "select"

If res contains only one element there is no problem. But union often use more than one table ;-)

@timowest
Copy link
Member

@timowest timowest commented Dec 12, 2012

Which Querydsl version are you using?

@Ghalleb
Copy link
Author

@Ghalleb Ghalleb commented Dec 12, 2012

2.9.0

timowest added a commit that referenced this issue Dec 12, 2012
@timowest
Copy link
Member

@timowest timowest commented Jan 13, 2013

Released in 3.0.0.BETA1

@timowest timowest closed this Jan 13, 2013
@timowest timowest added this to the 3.0.0 milestone Apr 14, 2014
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
None yet
Projects
None yet
Linked pull requests

Successfully merging a pull request may close this issue.

None yet
2 participants