Invalid usage of the option NEXT in the FETCH statement when querying SQL Server and using LIMIT clause

[alvaradojl]

Describe the bug
Querying a SQL Server with drill jdbc fails.
Logs:

Sql: SELECT *
FROM "dbo"."Order"
FETCH NEXT 1000 ROWS ONLY
Plugin: sqlsrv
Fragment: 0:0

[Error Id: 55b002f2-ee01-4412-b979-2f253074fdc9 on drill.internal.cloudapp.net:31010]
	at org.apache.drill.exec.server.rest.RestQueryRunner.submitQuery(RestQueryRunner.java:99)
	at org.apache.drill.exec.server.rest.RestQueryRunner.run(RestQueryRunner.java:54)
	at org.apache.drill.exec.server.rest.QueryResources.submitQuery(QueryResources.java:159)
	at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
	at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
	at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
	at java.base/java.lang.reflect.Method.invoke(Method.java:566)

..... trimmed for readibility ....

Caused by: java.lang.Exception: Invalid usage of the option NEXT in the FETCH statement.
	at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(SQLServerException.java:217)
	at com.microsoft.sqlserver.jdbc.SQLServerStatement.getNextResult(SQLServerStatement.java:1655)
	at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.doExecutePreparedStatement(SQLServerPreparedStatement.java:440)
	at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement$PrepStmtExecCmd.doExecute(SQLServerPreparedStatement.java:385)
	at com.microsoft.sqlserver.jdbc.TDSCommand.execute(IOBuffer.java:7505)
	at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(SQLServerConnection.java:2445)
	at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeCommand(SQLServerStatement.java:191)
	at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeStatement(SQLServerStatement.java:166)
	at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.executeQuery(SQLServerPreparedStatement.java:297)
	at com.zaxxer.hikari.pool.ProxyPreparedStatement.executeQuery(ProxyPreparedStatement.java:52)
	at com.zaxxer.hikari.pool.HikariProxyPreparedStatement.executeQuery(HikariProxyPreparedStatement.java)
	at org.apache.drill.exec.store.jdbc.JdbcRecordReader.setup(JdbcRecordReader.java:192)
	at org.apache.drill.exec.physical.impl.ScanBatch.getNextReaderIfHas(ScanBatch.java:331)
	at org.apache.drill.exec.physical.impl.ScanBatch.internalNext(ScanBatch.java:227)
	at org.apache.drill.exec.physical.impl.ScanBatch.next(ScanBatch.java:298)
	at org.apache.drill.exec.record.AbstractRecordBatch.next(AbstractRecordBatch.java:119)
	at org.apache.drill.exec.record.AbstractRecordBatch.next(AbstractRecordBatch.java:111)
	at org.apache.drill.exec.record.AbstractUnaryRecordBatch.innerNext(AbstractUnaryRecordBatch.java:59)
	at org.apache.drill.exec.physical.impl.project.ProjectRecordBatch.innerNext(ProjectRecordBatch.java:85)
	at org.apache.drill.exec.record.AbstractRecordBatch.next(AbstractRecordBatch.java:170)
	at org.apache.drill.exec.physical.impl.BaseRootExec.next(BaseRootExec.java:103)
	at org.apache.drill.exec.physical.impl.ScreenCreator$ScreenRoot.innerNext(ScreenCreator.java:81)
	at org.apache.drill.exec.physical.impl.BaseRootExec.next(BaseRootExec.java:93)
	at org.apache.drill.exec.work.fragment.FragmentExecutor$1.run(FragmentExecutor.java:323)
	at org.apache.drill.exec.work.fragment.FragmentExecutor$1.run(FragmentExecutor.java:310)
	at .......(:0)
	at org.apache.hadoop.security.UserGroupInformation.doAs(UserGroupInformation.java:1762)
	at org.apache.drill.exec.work.fragment.FragmentExecutor.run(FragmentExecutor.java:310)
	at org.apache.drill.common.SelfCleaningRunnable.run(SelfCleaningRunnable.java:38)
	at .......(:0)

Steps to reproduce the behavior:
Installing sqljdbc42.jar
Succesfully configuring the storage plugin pointing to an Azure SQL Server. e.g.

{
  "type": "jdbc",
  "driver": "com.microsoft.sqlserver.jdbc.SQLServerDriver",
  "url": "jdbc:sqlserver://<hostname>;database=anydb",
  "username": "user",
  "password": "pwd",
  "enabled": true
}

Write a simple query with a specific limit e.g.

SELECT * FROM sqlsrv.dbo.any_table LIMIT 3 --or any number

Expected behavior
A specific number of row set by the LIMIT clause

Additional context
The sql server is deployed in Azure, so the jdbc is querying latest version of SQL Server.

[jnturton]

Thanks for submitting this. We've already promoted it to a Jira issue

https://issues.apache.org/jira/browse/DRILL-8090

so I'll close here.

[jnturton]

P.S. a fix has been merged into master.