search

apache / drill

Apache Drill is a distributed MPP query layer for self describing data
https://drill.apache.org/
Apache License 2.0
1.93k stars 980 forks source link

Vertica: JDBC Driver does not support FETCH NEXT, only LIMIT #2802

Open Deklin opened 1 year ago

Deklin commented 1 year ago

Describe the bug When connecting a RDBMS datasource that is based on Vertica, Drill attempts to use the FETCH NEXT approach for query optimization, however vertica JDBC doesn't support FETCH NEXT but only supports limit. https://docs.vertica.com/12.0.x/en/sql-reference/statements/select/limit-clause/

To Reproduce Steps to reproduce the behavior:

  1. Connect a Vertica based RDBMS Datasource
  2. Once successful, validate with SHOW SCHEMAS;
  3. Attempt to run a query against one of the vertica tables
  4. Observe the error

Expected behavior Results should appear properly and not use fetch next

Error detail, log output or screenshots org.apache.drill.common.exceptions.UserRemoteException: DATA_READ ERROR: The JDBC storage plugin failed while trying setup the SQL query.

Sql: SELECT * FROM "VerticaDB"."xxxxxxxxx"."yyyyyyyyy" FETCH NEXT 1000 ROWS ONLY Fragment: 0:0

[Error Id: d142a3d5-f5b3-4f9c-b850-a39e72604953 on a68ba75bf84a:31010]

SQL LOG:

Caused by: java.sql.SQLSyntaxErrorException: [Vertica]VJDBC ERROR: Syntax error at or near "NEXT" at character 59 at com.vertica.util.ServerErrorData.buildException() at com.vertica.io.ProtocolStream.readExpectedMessage() at com.vertica.dataengine.VDataEngine.prepareImpl() at com.vertica.dataengine.VDataEngine.prepare()

Drill version 1.21.1

Additional context