Support for offset() in mssql 2012 #406

Closed
zzhu8192 opened this Issue Apr 28, 2013 · 7 comments

Comments

Projects
None yet
2 participants
@zzhu8192

SQLServer 2012 finally introduced a reasonably clean way to do limit/offset expressions without the use of analytical functions. In SQLServerTemplates.java, instead of "offset not supported", the following code can be used to do offset. This is used in lieu of the top(x) expression, which cannot be used in conjunction with OFFSET/FETCH.

           metadata.addFlag(new QueryFlag(Position.AFTER_ORDER, " OFFSET " + mod.getOffset() + " ROWS FETCH NEXT " + mod.getLimit() + " ROWS ONLY"));

See: http://msdn.microsoft.com/en-us/library/gg699618.aspx
for more details.

The only gotcha is that an order by is required in order to use this, which is a bit goofy, but not a super restrictive requirement. I figure it's possible to generate an order by if one doesn't exist. In our use case, we always have order by's when using limit/offset expressions, so this isn't a huge concern.

@zzhu8192

This comment has been minimized.

Show comment
Hide comment
@zzhu8192

zzhu8192 Apr 28, 2013

I just wanted to say thanks for the excellent work on Querydsl! We've experimented with a multitude of type safe sql DSL frameworks (in C# and Java), and this is the most sensible and intuitive one by far.

My one suggestion/request is to provide more detailed examples in the documentation. The querydsl framework is quite powerful, though from my experience it's been difficult to find specific code examples of many of the supported functionalites.

I just wanted to say thanks for the excellent work on Querydsl! We've experimented with a multitude of type safe sql DSL frameworks (in C# and Java), and this is the most sensible and intuitive one by far.

My one suggestion/request is to provide more detailed examples in the documentation. The querydsl framework is quite powerful, though from my experience it's been difficult to find specific code examples of many of the supported functionalites.

@timowest

This comment has been minimized.

Show comment
Hide comment
@timowest

timowest Apr 29, 2013

Member

SQLServer2005Templates is for SQL Server 2005 and later. Could you try it?

Member

timowest commented Apr 29, 2013

SQLServer2005Templates is for SQL Server 2005 and later. Could you try it?

@zzhu8192

This comment has been minimized.

Show comment
Hide comment
@zzhu8192

zzhu8192 Apr 29, 2013

Thanks for the response, I'll try it later today.

On Mon, Apr 29, 2013 at 12:35 AM, Timo Westkämper
notifications@github.comwrote:

SQLServer2005Templates is for SQL Server 2005 and later. Could you try it?


Reply to this email directly or view it on GitHubhttps://github.com/mysema/querydsl/issues/406#issuecomment-17150779
.

Thanks for the response, I'll try it later today.

On Mon, Apr 29, 2013 at 12:35 AM, Timo Westkämper
notifications@github.comwrote:

SQLServer2005Templates is for SQL Server 2005 and later. Could you try it?


Reply to this email directly or view it on GitHubhttps://github.com/mysema/querydsl/issues/406#issuecomment-17150779
.

@timowest

This comment has been minimized.

Show comment
Hide comment
@timowest

timowest Apr 30, 2013

Member

Any update?

Member

timowest commented Apr 30, 2013

Any update?

@zzhu8192

This comment has been minimized.

Show comment
Hide comment
@zzhu8192

zzhu8192 Apr 30, 2013

I tried out the SQLServer2005Templates version , and see that it implements
offset/limit with rownum. It works under some scenarios, but fails under
some other scenarios. Queries that work using the new SQLServer 2012
offset/fetch syntax do not work using the SQLServer2005Templates generated
query.

So I basically implemented a SQLServer2012Templates extension using
offset/fetch with the approach that I posted, and it works in all of our
query scenarios (given there's an order by).

See one example of failing query below.
e.g.

com.mysema.query.QueryException: Caught SQLServerException for with
inner_query as
(
select upc.subcategory_id, upc.start_date, upc.upc_id, upc.upc_type_id,
upc.manufacturer_id, upc.category_id, upc.last_screening_date,
upc.upc_length, upc.end_date, upc.operation_status_id, upc.upc_number,
upc.version, upc.screening_status_id, upc.image_list, upc.default_filtered,
upc.upc_number_short, upc.upc_nutritional_info, ua_national.start_date,
ua_national.agency_short_product_name, ua_national.uom_quantity,
ua_national.agency_custom_attributes, ua_national.upc_id,
ua_national.agency_subcategory_id, ua_national.agency_broadband_flag,
ua_national.agency_container_size, ua_national.end_date,
ua_national.agency_rebate_flag, ua_national.agency_id,
ua_national.agency_container_type, ua_national.agency_effective_date,
ua_national.upc_unit_of_measure_id, ua_national.version,
ua_national.agency_filtered, ua_national.upc_agency_id,
ua_national.agency_category_id, ua_national.agency_operation_status_id,
ua_national.product_name, ua_national.agency_voucher_flag,
ua_national.agency_authorization_status_id, row_number() over (order by
upc.upc_number) as row_number
from upc upc
inner join upc_agency ua_national
on ua_national.upc_id = upc.upc_id and ua_national.agency_id = ?
inner join category category
on category.category_id = upc.category_id
inner join subcategory subcategory
on subcategory.subcategory_id = upc.subcategory_id
left join upc_agency ua_search
on ua_search.upc_id = upc.upc_id and ua_search.agency_id = ?
where upc.end_date = ? and upc.screening_status_id = ? and
upc.default_filtered = ? and (ua_search.agency_filtered = ? or
ua_search.agency_filtered is null)
)
select *
from inner_query
where row_number <= ?

at com.mysema.query.sql.AbstractSQLQuery.list(AbstractSQLQuery.java:485)
at

com.ndg.nupc.services.search.UpcSearchService.searchUpcHelper(UpcSearchService.java:212)
at
com.ndg.nupc.services.search.UpcSearchService.searchUpc(UpcSearchService.java:69)
at
com.ndg.nupc.services.search.UpcSearchService.searchUpc(UpcSearchService.java:63)
at
com.ndg.nupc.services.search.UpcSearchExecutorIntegrationTest.testSearchUpc_ScreeningStatus(UpcSearchExecutorIntegrationTest.java:195)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at
sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)
at
sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:601)
at
org.junit.runners.model.FrameworkMethod$1.runReflectiveCall(FrameworkMethod.java:47)
at
org.junit.internal.runners.model.ReflectiveCallable.run(ReflectiveCallable.java:12)
at
org.junit.runners.model.FrameworkMethod.invokeExplosively(FrameworkMethod.java:44)
at
org.junit.internal.runners.statements.InvokeMethod.evaluate(InvokeMethod.java:17)
at
org.junit.internal.runners.statements.RunBefores.evaluate(RunBefores.java:26)
at
org.junit.internal.runners.statements.RunAfters.evaluate(RunAfters.java:27)
at org.junit.runners.ParentRunner.runLeaf(ParentRunner.java:271)
at
org.junit.runners.BlockJUnit4ClassRunner.runChild(BlockJUnit4ClassRunner.java:70)
at
org.junit.runners.BlockJUnit4ClassRunner.runChild(BlockJUnit4ClassRunner.java:50)
at org.junit.runners.ParentRunner$3.run(ParentRunner.java:238)
at org.junit.runners.ParentRunner$1.schedule(ParentRunner.java:63)
at org.junit.runners.ParentRunner.runChildren(ParentRunner.java:236)
at org.junit.runners.ParentRunner.access$000(ParentRunner.java:53)
at org.junit.runners.ParentRunner$2.evaluate(ParentRunner.java:229)
at org.junit.runners.ParentRunner.run(ParentRunner.java:309)
at org.ops4j.pax.exam.junit.PaxExam.run(PaxExam.java:111)
at
org.eclipse.jdt.internal.junit4.runner.JUnit4TestReference.run(JUnit4TestReference.java:50)
at
org.eclipse.jdt.internal.junit.runner.TestExecution.run(TestExecution.java:38)
at
org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:467)
at
org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:683)
at
org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.run(RemoteTestRunner.java:390)
at
org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.main(RemoteTestRunner.java:197)
Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: The column
'start_date' was specified multiple times for 'inner_query'.
at
com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(SQLServerException.java:216)
at
com.microsoft.sqlserver.jdbc.SQLServerStatement.getNextResult(SQLServerStatement.java:1515)
at
com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.doExecutePreparedStatement(SQLServerPreparedStatement.java:404)
at
com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement$PrepStmtExecCmd.doExecute(SQLServerPreparedStatement.java:350)
at com.microsoft.sqlserver.jdbc.TDSCommand.execute(IOBuffer.java:5696)
at
com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(SQLServerConnection.java:1715)
at
com.microsoft.sqlserver.jdbc.SQLServerStatement.executeCommand(SQLServerStatement.java:180)
at
com.microsoft.sqlserver.jdbc.SQLServerStatement.executeStatement(SQLServerStatement.java:155)
at
com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.executeQuery(SQLServerPreparedStatement.java:285)
at com.mysema.query.sql.AbstractSQLQuery.list(AbstractSQLQuery.java:447)
... 30 more

I tried out the SQLServer2005Templates version , and see that it implements
offset/limit with rownum. It works under some scenarios, but fails under
some other scenarios. Queries that work using the new SQLServer 2012
offset/fetch syntax do not work using the SQLServer2005Templates generated
query.

So I basically implemented a SQLServer2012Templates extension using
offset/fetch with the approach that I posted, and it works in all of our
query scenarios (given there's an order by).

See one example of failing query below.
e.g.

com.mysema.query.QueryException: Caught SQLServerException for with
inner_query as
(
select upc.subcategory_id, upc.start_date, upc.upc_id, upc.upc_type_id,
upc.manufacturer_id, upc.category_id, upc.last_screening_date,
upc.upc_length, upc.end_date, upc.operation_status_id, upc.upc_number,
upc.version, upc.screening_status_id, upc.image_list, upc.default_filtered,
upc.upc_number_short, upc.upc_nutritional_info, ua_national.start_date,
ua_national.agency_short_product_name, ua_national.uom_quantity,
ua_national.agency_custom_attributes, ua_national.upc_id,
ua_national.agency_subcategory_id, ua_national.agency_broadband_flag,
ua_national.agency_container_size, ua_national.end_date,
ua_national.agency_rebate_flag, ua_national.agency_id,
ua_national.agency_container_type, ua_national.agency_effective_date,
ua_national.upc_unit_of_measure_id, ua_national.version,
ua_national.agency_filtered, ua_national.upc_agency_id,
ua_national.agency_category_id, ua_national.agency_operation_status_id,
ua_national.product_name, ua_national.agency_voucher_flag,
ua_national.agency_authorization_status_id, row_number() over (order by
upc.upc_number) as row_number
from upc upc
inner join upc_agency ua_national
on ua_national.upc_id = upc.upc_id and ua_national.agency_id = ?
inner join category category
on category.category_id = upc.category_id
inner join subcategory subcategory
on subcategory.subcategory_id = upc.subcategory_id
left join upc_agency ua_search
on ua_search.upc_id = upc.upc_id and ua_search.agency_id = ?
where upc.end_date = ? and upc.screening_status_id = ? and
upc.default_filtered = ? and (ua_search.agency_filtered = ? or
ua_search.agency_filtered is null)
)
select *
from inner_query
where row_number <= ?

at com.mysema.query.sql.AbstractSQLQuery.list(AbstractSQLQuery.java:485)
at

com.ndg.nupc.services.search.UpcSearchService.searchUpcHelper(UpcSearchService.java:212)
at
com.ndg.nupc.services.search.UpcSearchService.searchUpc(UpcSearchService.java:69)
at
com.ndg.nupc.services.search.UpcSearchService.searchUpc(UpcSearchService.java:63)
at
com.ndg.nupc.services.search.UpcSearchExecutorIntegrationTest.testSearchUpc_ScreeningStatus(UpcSearchExecutorIntegrationTest.java:195)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at
sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)
at
sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:601)
at
org.junit.runners.model.FrameworkMethod$1.runReflectiveCall(FrameworkMethod.java:47)
at
org.junit.internal.runners.model.ReflectiveCallable.run(ReflectiveCallable.java:12)
at
org.junit.runners.model.FrameworkMethod.invokeExplosively(FrameworkMethod.java:44)
at
org.junit.internal.runners.statements.InvokeMethod.evaluate(InvokeMethod.java:17)
at
org.junit.internal.runners.statements.RunBefores.evaluate(RunBefores.java:26)
at
org.junit.internal.runners.statements.RunAfters.evaluate(RunAfters.java:27)
at org.junit.runners.ParentRunner.runLeaf(ParentRunner.java:271)
at
org.junit.runners.BlockJUnit4ClassRunner.runChild(BlockJUnit4ClassRunner.java:70)
at
org.junit.runners.BlockJUnit4ClassRunner.runChild(BlockJUnit4ClassRunner.java:50)
at org.junit.runners.ParentRunner$3.run(ParentRunner.java:238)
at org.junit.runners.ParentRunner$1.schedule(ParentRunner.java:63)
at org.junit.runners.ParentRunner.runChildren(ParentRunner.java:236)
at org.junit.runners.ParentRunner.access$000(ParentRunner.java:53)
at org.junit.runners.ParentRunner$2.evaluate(ParentRunner.java:229)
at org.junit.runners.ParentRunner.run(ParentRunner.java:309)
at org.ops4j.pax.exam.junit.PaxExam.run(PaxExam.java:111)
at
org.eclipse.jdt.internal.junit4.runner.JUnit4TestReference.run(JUnit4TestReference.java:50)
at
org.eclipse.jdt.internal.junit.runner.TestExecution.run(TestExecution.java:38)
at
org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:467)
at
org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:683)
at
org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.run(RemoteTestRunner.java:390)
at
org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.main(RemoteTestRunner.java:197)
Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: The column
'start_date' was specified multiple times for 'inner_query'.
at
com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(SQLServerException.java:216)
at
com.microsoft.sqlserver.jdbc.SQLServerStatement.getNextResult(SQLServerStatement.java:1515)
at
com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.doExecutePreparedStatement(SQLServerPreparedStatement.java:404)
at
com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement$PrepStmtExecCmd.doExecute(SQLServerPreparedStatement.java:350)
at com.microsoft.sqlserver.jdbc.TDSCommand.execute(IOBuffer.java:5696)
at
com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(SQLServerConnection.java:1715)
at
com.microsoft.sqlserver.jdbc.SQLServerStatement.executeCommand(SQLServerStatement.java:180)
at
com.microsoft.sqlserver.jdbc.SQLServerStatement.executeStatement(SQLServerStatement.java:155)
at
com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.executeQuery(SQLServerPreparedStatement.java:285)
at com.mysema.query.sql.AbstractSQLQuery.list(AbstractSQLQuery.java:447)
... 30 more

@zzhu8192 zzhu8192 closed this Apr 30, 2013

@zzhu8192 zzhu8192 reopened this Apr 30, 2013

timowest added a commit that referenced this issue Apr 30, 2013

@timowest

This comment has been minimized.

Show comment
Hide comment
@timowest

timowest Apr 30, 2013

Member

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

SQLServer2012Templates works like you suggested.

Member

timowest commented Apr 30, 2013

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

SQLServer2012Templates works like you suggested.

timowest added a commit that referenced this issue Apr 30, 2013

timowest added a commit that referenced this issue Apr 30, 2013

@timowest

This comment has been minimized.

Show comment
Hide comment
@timowest

timowest May 27, 2013

Member

Released in 3.2.0

Member

timowest commented May 27, 2013

Released in 3.2.0

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