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

SQL Server 2012: exists() should generate "ORDER BY" clause, not only "offset ? rows fetch next ? rows only" #853

Closed
danile42 opened this Issue Jul 16, 2014 · 5 comments

Comments

Projects
None yet
2 participants
@danile42

Hello,

I have code using an SQLQuery like the following:

SQLQuery sqlQuery = new SQLQuery(connection, new SQLServer2012Templates());
sqlQuery.from(item, transaction)
  .where(item.date.eq(transaction.getDate())
    .and(item.id.eq(transaction.id))
    .and(new SQLSubQuery().from(innertransaction)
      .where(transaction.id.eq(innertransaction.id)
        .and(transaction.name.eq(innertransaction.name)))
      .notExists()));
sqlQuery.exists();

(I tried to simplify my original query, but not too much, since I'm not sure what is essential for the problem.)

The call to "exists()" in the last line leads to the creation of a query starting with "select 1
from ITEM, TRANSACTION", followed by all the join conditions and the sub-query, and then "offset ? rows fetch next ? rows only". As discussed here: http://technet.microsoft.com/de-de/library/gg699618%28v=sql.110%29.aspx and here: #406, the "offset etc." part is only allowed in conjunction with an "order by" clause. Since my code does not talk about and should not know about these details, I think QueryDSL would have to generate an "order by" clause, or force me to define one if I want to use "exists()". Since it doesn't, I get a syntax error near "offset" as soon as I try to execute the query.

Thanks for any help, with best regards,
Daniel

@danile42

This comment has been minimized.

Show comment
Hide comment
@danile42

danile42 Jul 16, 2014

P.S.: I'm using QueryDSL 3.3.2 (I don't see any fix for this issue in earlier versions, though I haven't tried them yet) and Microsoft SQL Server 2012.

P.S.: I'm using QueryDSL 3.3.2 (I don't see any fix for this issue in earlier versions, though I haven't tried them yet) and Microsoft SQL Server 2012.

@timowest timowest added bug labels Jul 17, 2014

@timowest timowest added this to the 3.4.2 milestone Jul 18, 2014

@timowest

This comment has been minimized.

Show comment
Hide comment
@timowest

timowest Jul 18, 2014

Member

Thanks for the bug report. I added a fix which uses the TOP clause if limit and no order by is used.

Member

timowest commented Jul 18, 2014

Thanks for the bug report. I added a fix which uses the TOP clause if limit and no order by is used.

@timowest

This comment has been minimized.

Show comment
Hide comment
Member

timowest commented Jul 19, 2014

@danile42 Could you try with the latest snapshot from https://oss.sonatype.org/content/repositories/snapshots/

@timowest timowest modified the milestone: 3.4.2 Jul 19, 2014

@timowest timowest removed the fixed label Jul 19, 2014

@timowest timowest closed this Jul 19, 2014

@timowest

This comment has been minimized.

Show comment
Hide comment
@timowest

timowest Jul 29, 2014

Member

Released in 3.4.2

Member

timowest commented Jul 29, 2014

Released in 3.4.2

@danile42

This comment has been minimized.

Show comment
Hide comment
@danile42

danile42 Aug 22, 2014

@timowest It seems to work now with QueryDSL 3.4.2. Thanks!

@timowest It seems to work now with QueryDSL 3.4.2. Thanks!

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