Skip to content

Commit 1bba19a

Browse files
FabioMalpezziFabioMalpezzi
committed
feat(transactsql): support final OPTION (query_hint, ...) clause
SQL Server accepts an optional `OPTION (query_hint [, ...])` clause at the end of SELECT, UPDATE, DELETE and MERGE statements. Previously this clause caused a parse failure in the `transactsql` dialect. This extends `select_stmt_nake` to accept the optional query hints clause after `FOR XML/JSON`, adds dedicated grammar rules for the hints, and round-trips them back to SQL preserving canonical upper-case form. Covered hints in this initial pass: - no-argument: RECOMPILE, FORCE ORDER, LOOP JOIN, HASH JOIN, MERGE JOIN, KEEP PLAN, KEEPFIXED PLAN, OPTIMIZE FOR UNKNOWN - numeric-argument: MAXDOP n, MAXRECURSION n, FAST n More complex hints like `OPTIMIZE FOR (@p = value)` and `USE HINT('...')` are intentionally left out of this first round and can be added as a follow-up. Also refines the T-SQL `alias_clause` with a negative lookahead so that `SELECT col FROM t OPTION (...)` is not mis-parsed with `OPTION` as an implicit table alias. - pegjs/transactsql.pegjs: extended `select_stmt_nake`; added `tsql_query_hints` and `tsql_query_hint`; refined `alias_clause` to exclude `OPTION (` as a candidate implicit alias. - src/select.js: added `queryHintsToSQL`; destructured `query_hints` and emitted the OPTION clause at the end of SELECT. - test/transactsql.spec.js: added round-trip tests covering every hint variant and a combined case with TOP + ORDER BY + OPTION. Reference: https://learn.microsoft.com/sql/t-sql/queries/option-clause-transact-sql
1 parent 23f817f commit 1bba19a

3 files changed

Lines changed: 95 additions & 3 deletions

File tree

‎pegjs/transactsql.pegjs‎

Lines changed: 37 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -1344,7 +1344,8 @@ select_stmt_nake
13441344
h:having_clause? __
13451345
o:order_by_clause? __
13461346
l:limit_clause? __
1347-
fx:for_expr? {
1347+
fx:for_expr? __
1348+
qh:tsql_query_hints? {
13481349
if(f) f.forEach(info => info.table && tableList.add(`select::${[info.server, info.db, info.schema].filter(Boolean).join('.') || null}::${info.table}`));
13491350
return {
13501351
with: cte,
@@ -1363,7 +1364,8 @@ select_stmt_nake
13631364
having: h,
13641365
top,
13651366
orderby: o,
1366-
limit: l
1367+
limit: l,
1368+
query_hints: qh
13671369
};
13681370
}
13691371

@@ -1382,6 +1384,35 @@ top_clause
13821384
}
13831385
}
13841386

1387+
// T-SQL: final OPTION (query_hint [, ...]) clause, accepted at the end of
1388+
// SELECT / UPDATE / DELETE / MERGE statements. Covers the common query
1389+
// hints with either no argument or a single numeric argument. More complex
1390+
// hints like `OPTIMIZE FOR (@p = value)` or `USE HINT('...')` are left out
1391+
// of this first pass and can be added in a follow-up.
1392+
tsql_query_hints
1393+
= 'OPTION'i __ LPAREN __ head:tsql_query_hint tail:(__ COMMA __ tsql_query_hint)* __ RPAREN {
1394+
const hints = [head];
1395+
for (let i = 0, l = tail.length; i < l; ++i) {
1396+
hints.push(tail[i][3]);
1397+
}
1398+
return hints;
1399+
}
1400+
1401+
// Note: KEEPFIXED PLAN must be listed before KEEP PLAN, otherwise the shorter
1402+
// keyword would greedily match the `KEEP` prefix of `KEEPFIXED`.
1403+
tsql_query_hint
1404+
= 'RECOMPILE'i { return { type: 'query_hint', name: 'RECOMPILE' } }
1405+
/ 'FORCE'i __ 'ORDER'i { return { type: 'query_hint', name: 'FORCE ORDER' } }
1406+
/ 'LOOP'i __ 'JOIN'i { return { type: 'query_hint', name: 'LOOP JOIN' } }
1407+
/ 'HASH'i __ 'JOIN'i { return { type: 'query_hint', name: 'HASH JOIN' } }
1408+
/ 'MERGE'i __ 'JOIN'i { return { type: 'query_hint', name: 'MERGE JOIN' } }
1409+
/ 'KEEPFIXED'i __ 'PLAN'i { return { type: 'query_hint', name: 'KEEPFIXED PLAN' } }
1410+
/ 'KEEP'i __ 'PLAN'i { return { type: 'query_hint', name: 'KEEP PLAN' } }
1411+
/ 'OPTIMIZE'i __ 'FOR'i __ 'UNKNOWN'i { return { type: 'query_hint', name: 'OPTIMIZE FOR UNKNOWN' } }
1412+
/ 'MAXDOP'i __ v:number { return { type: 'query_hint', name: 'MAXDOP', value: v } }
1413+
/ 'MAXRECURSION'i __ v:number { return { type: 'query_hint', name: 'MAXRECURSION', value: v } }
1414+
/ 'FAST'i __ v:number { return { type: 'query_hint', name: 'FAST', value: v } }
1415+
13851416
// MySQL extensions to standard SQL
13861417
option_clause
13871418
= head:query_option tail:(__ query_option)* {
@@ -1446,7 +1477,10 @@ value_alias_clause
14461477

14471478
alias_clause
14481479
= KW_AS __ i:alias_ident { return i; }
1449-
/ KW_AS? __ i:ident { return i; }
1480+
// Without an explicit `AS`, `OPTION (...)` must not be parsed as an implicit
1481+
// table alias: it is the T-SQL final query-hint clause handled by
1482+
// `tsql_query_hints` in `select_stmt_nake`.
1483+
/ KW_AS? __ !('OPTION'i __ LPAREN) i:ident { return i; }
14501484

14511485
into_clause
14521486
= KW_INTO __ f:ident {

‎src/select.js‎

Lines changed: 14 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -54,6 +54,18 @@ function forXmlToSQL(stmt) {
5454
return `${result.join(' ')}(${exprToSQL(expr)})`
5555
}
5656

57+
// T-SQL: render the final OPTION (query_hint [, ...]) clause.
58+
// Returns undefined when there are no hints, so the caller can
59+
// safely filter it out of the assembled SELECT string.
60+
function queryHintsToSQL(hints) {
61+
if (!Array.isArray(hints) || hints.length === 0) return
62+
const parts = hints.map(hint => {
63+
const suffix = hint.value !== undefined && hint.value !== null ? ` ${hint.value}` : ''
64+
return `${hint.name}${suffix}`
65+
})
66+
return `OPTION (${parts.join(', ')})`
67+
}
68+
5769
function selectToSQL(stmt) {
5870
const {
5971
as_struct_val: asStructVal,
@@ -73,6 +85,7 @@ function selectToSQL(stmt) {
7385
orderby,
7486
parentheses_symbol: parentheses,
7587
qualify,
88+
query_hints: queryHints,
7689
top,
7790
window: windowInfo,
7891
with: withInfo,
@@ -105,6 +118,7 @@ function selectToSQL(stmt) {
105118
clauses.push(toUpper(lockingRead))
106119
if (position === 'end') clauses.push(intoSQL)
107120
clauses.push(forXmlToSQL(forXml))
121+
clauses.push(queryHintsToSQL(queryHints))
108122
const sql = clauses.filter(hasVal).join(' ')
109123
return parentheses ? `(${sql})` : sql
110124
}

‎test/transactsql.spec.js‎

Lines changed: 44 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -27,6 +27,50 @@ describe('transactsql', () => {
2727
expect(getParsedSql(sql)).to.equal('SELECT TOP 10 * FROM [myTable]')
2828
})
2929

30+
it('should support select OPTION query hints', () => {
31+
// Argument-less hints
32+
let sql = 'SELECT col FROM myTable OPTION (RECOMPILE)'
33+
expect(getParsedSql(sql)).to.equal('SELECT [col] FROM [myTable] OPTION (RECOMPILE)')
34+
sql = 'SELECT col FROM myTable OPTION (FORCE ORDER)'
35+
expect(getParsedSql(sql)).to.equal('SELECT [col] FROM [myTable] OPTION (FORCE ORDER)')
36+
sql = 'SELECT col FROM myTable OPTION (LOOP JOIN)'
37+
expect(getParsedSql(sql)).to.equal('SELECT [col] FROM [myTable] OPTION (LOOP JOIN)')
38+
sql = 'SELECT col FROM myTable OPTION (HASH JOIN)'
39+
expect(getParsedSql(sql)).to.equal('SELECT [col] FROM [myTable] OPTION (HASH JOIN)')
40+
sql = 'SELECT col FROM myTable OPTION (MERGE JOIN)'
41+
expect(getParsedSql(sql)).to.equal('SELECT [col] FROM [myTable] OPTION (MERGE JOIN)')
42+
sql = 'SELECT col FROM myTable OPTION (OPTIMIZE FOR UNKNOWN)'
43+
expect(getParsedSql(sql)).to.equal('SELECT [col] FROM [myTable] OPTION (OPTIMIZE FOR UNKNOWN)')
44+
45+
// KEEPFIXED PLAN must be parsed before KEEP PLAN; verify both variants.
46+
sql = 'SELECT col FROM myTable OPTION (KEEPFIXED PLAN)'
47+
expect(getParsedSql(sql)).to.equal('SELECT [col] FROM [myTable] OPTION (KEEPFIXED PLAN)')
48+
sql = 'SELECT col FROM myTable OPTION (KEEP PLAN)'
49+
expect(getParsedSql(sql)).to.equal('SELECT [col] FROM [myTable] OPTION (KEEP PLAN)')
50+
51+
// Numeric-argument hints
52+
sql = 'SELECT col FROM myTable OPTION (MAXDOP 8)'
53+
expect(getParsedSql(sql)).to.equal('SELECT [col] FROM [myTable] OPTION (MAXDOP 8)')
54+
sql = 'SELECT col FROM myTable OPTION (MAXRECURSION 100)'
55+
expect(getParsedSql(sql)).to.equal('SELECT [col] FROM [myTable] OPTION (MAXRECURSION 100)')
56+
sql = 'SELECT col FROM myTable OPTION (FAST 10)'
57+
expect(getParsedSql(sql)).to.equal('SELECT [col] FROM [myTable] OPTION (FAST 10)')
58+
59+
// Multiple hints in a single OPTION clause
60+
sql = 'SELECT col FROM myTable OPTION (RECOMPILE, MAXDOP 4)'
61+
expect(getParsedSql(sql)).to.equal('SELECT [col] FROM [myTable] OPTION (RECOMPILE, MAXDOP 4)')
62+
sql = 'SELECT col FROM myTable ORDER BY col OPTION (RECOMPILE, MAXDOP 8, FORCE ORDER)'
63+
expect(getParsedSql(sql)).to.equal('SELECT [col] FROM [myTable] ORDER BY [col] ASC OPTION (RECOMPILE, MAXDOP 8, FORCE ORDER)')
64+
65+
// OPTION must appear after ORDER BY
66+
sql = 'SELECT TOP 10 col FROM myTable ORDER BY col OPTION (MAXRECURSION 0)'
67+
expect(getParsedSql(sql)).to.equal('SELECT TOP 10 [col] FROM [myTable] ORDER BY [col] ASC OPTION (MAXRECURSION 0)')
68+
69+
// Case-insensitive input is normalized to canonical upper-case form
70+
sql = 'select col from myTable option (recompile, maxdop 2)'
71+
expect(getParsedSql(sql)).to.equal('SELECT [col] FROM [myTable] OPTION (RECOMPILE, MAXDOP 2)')
72+
})
73+
3074
it('should support select count', () => {
3175
let sql = 'select count(*);'
3276
expect(getParsedSql(sql)).to.equal('SELECT COUNT(*)')

0 commit comments

Comments
 (0)