JDBC: Add per‑plugin configurable LIMIT pushdown and dialect override (e.g., NetSuite/OpenAccess, Vertica, SQL Server)

Start with JdbcLimitRule and its registration through DrillJdbcConvention, then trace how JDBC storage-plugin configuration reaches planning. Compare Calcite’s current FETCH NEXT rendering with the proposed TOP, LIMIT, and disabled-pushdown cases, including OFFSET handling. Done means the per-plugin options have defined validation and the selected behavior is covered for each supported dialect.

Is your feature request related to a problem? Please describe:
When using the JDBC storage plugin, Drill currently pushes down LIMIT to the remote source and (for many targets) renders it as:

... FETCH NEXT <n> ROWS ONLY

A number of JDBC drivers don’t support that Top‑N syntax and fail with a syntax error. This makes simple “preview 10 rows” queries unusable against those sources and forces users either to fetch full tables or to avoid Drill for sampling.
Concrete evidence of this common failure pattern:

• Vertica: SQLSyntaxErrorException: Syntax error at or near "NEXT" when Drill sends FETCH NEXT (issue shows Drill’s emitted SQL) apache/drill#2802
• SQL Server: “Invalid usage of the option NEXT in the FETCH statement” when Drill pushes FETCH NEXT (stack trace from JdbcRecordReader) apache/drill#2418

For NetSuite SuiteAnalytics Connect (OpenAccess JDBC), the supported Top‑N form is SELECT TOP n … in SuiteQL, not FETCH NEXT—see NetSuite’s own SuiteQL docs and examples. As a result, the OpenAccess SQL engine returns a generic syntax error 10104 when it receives unknown syntax.

Today, there is no user‑visible Drill option to change the pushed‑down LIMIT form or to disable JDBC LIMIT pushdown for an individual storage plugin. Planner/exec option docs list many toggles, but none for JDBC LIMIT pushdown.

Describe the solution you’d like:
Add per‑JDBC‑plugin configurability for LIMIT pushdown so administrators can match the dialect of their target system or disable the pushdown entirely.

A. Storage plugin JSON options (proposal)

{ "type": "jdbc", "driver": "com.netsuite.jdbc.openaccess.OpenAccessDriver", "url": "jdbc:ns://<host>:1708;ServerDataSource=NetSuite2.com;Encrypted=1;NegotiateSSLClose=false", // NEW: "disableLimitPushdown": true, // default: false "limitSyntax": "TOP", // enum: "FETCH_NEXT" (default), "TOP", "LIMIT" "limitKeywordPosition": "SELECT" // for "TOP" targets; else ignored }

Behavioral details

• If disableLimitPushdown = true, Drill keeps LIMIT in the physical plan and enforces it locally (no FETCH NEXT or TOP sent to the source).
• If limitSyntax = "TOP", Drill renders SELECT TOP … when pushing LIMIT.
• If limitSyntax = "LIMIT", Drill renders … LIMIT (for engines that prefer that MySQL/Postgres‑style form).
• If omitted, Drill retains the current behavior (FETCH NEXT per Calcite’s default dialect).

B. Planner/runtime

• Extend JdbcLimitRule (and registration via DrillJdbcConvention) to consult the plugin configuration and pick the appropriate rendering or to skip pushdown entirely.

• Validation: If a user sets limitSyntax="TOP" but the SQL contains OFFSET, either:

• fall back to no pushdown, or

• return a clear planning error that the chosen dialect combination is unsupported.