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

Postgresql - "in with union" is missing parenthesis #593

Closed
magnuskvalheim opened this issue Dec 12, 2013 · 2 comments
Closed

Postgresql - "in with union" is missing parenthesis #593

magnuskvalheim opened this issue Dec 12, 2013 · 2 comments
Milestone

Comments

@magnuskvalheim
Copy link

@magnuskvalheim magnuskvalheim commented Dec 12, 2013

Related version 3.3.0.beta2.

Was testing beta2 and looks like it's missing a parenthesis - at least in my testing...
Postgresql 9.3.1

SMovella smovella = SMovella.movella;
SCoAuthor sco = SCoAuthor.coAuthor;

ListSubQuery<Long> subMovellaByCoAuthorId = new SQLSubQuery()
    .from(sco)
    .where(sco.userId.eq(userId))
    .list(sco.userId);

ListSubQuery<Long> subMovellaByAuthorId = new SQLSubQuery()
    .from(smovella)
    .where(smovella.userId.eq(userId))
    .list(smovella.userId);             

List<Movella> mList = new JPASQLQuery(getEm(), PostgresTemplates.builder().printSchema().build())
    .from(smovella)
    .where(smovella.userId.in(new SQLSubQuery().union(subMovellaByAuthorId, subMovellaByCoAuthorId)))
    .list(QMovella.movella);

Generates SQL

select movella.*
    from movella.movella movella
    where movella.user_id in (select movella.user_id
    from movella.movella movella
    where movella.user_id = $1)
    union
    (select co_author.user_id
    from movella.co_author co_author
    where co_author.user_id = $2)

This is failing with "each UNION query must have the same number of columns at character 167"

The query can execute if the union is enclosed in parenthesis.

select movella.*
    from movella.movella movella
    where movella.user_id in ((select movella.user_id
    from movella.movella movella
    where movella.user_id = $1)
    union
    (select co_author.user_id
    from movella.co_author co_author
    where co_author.user_id = $2))
@magnuskvalheim
Copy link
Author

@magnuskvalheim magnuskvalheim commented Dec 13, 2013

Great, High fives :-)

@timowest
Copy link
Member

@timowest timowest commented Dec 22, 2013

Released in 3.3.0

@timowest timowest closed this Dec 22, 2013
@timowest timowest added this to the 3.3.0 milestone Apr 13, 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