Repository: sql-formatter-org/sql-formatter
Pull request #362 (2022-07-30 21:06:09)

Query syntax update
Reviewed supported SELECT statement clauses (based on our wiki docs).

Main changes:

- Moved list of these clauses to top of reservedCommands list.
- Added support for syntax variations like `SELECT [DISTINCT]` and `WITH [RECURSIVE]`.
- Overhauled support for limiting: dropped `LIMIT` clause support from several dialects, added `FETCH FIRST` support to several others.
- Separate tests for `WITH` clause.
- Simplified some old tests - deleted some completely.

Didn't attempt covering all the minor syntax variations with tests. Concentrated on the more substantial differences: like testing `LIMIT`, `FETCH FIRST` and `WITH` support, but not testing `WITH RECURSIVE` and `ORDER SIBLINGS BY` support.


Patch:
diff --git a/src/languages/bigquery/bigquery.formatter.ts b/src/languages/bigquery/bigquery.formatter.ts
index 0a57d54c..a5a78ed8 100644
--- a/src/languages/bigquery/bigquery.formatter.ts
+++ b/src/languages/bigquery/bigquery.formatter.ts
@@ -6,19 +6,19 @@ import { keywords } from './bigquery.keywords';
 import { functions } from './bigquery.functions';
 
 const reservedCommands = expandPhrases([
-  // DQL, https://cloud.google.com/bigquery/docs/reference/standard-sql/query-syntax
-  'SELECT',
+  // Queries: https://cloud.google.com/bigquery/docs/reference/standard-sql/query-syntax
+  'WITH [RECURSIVE]',
+  'SELECT [ALL | DISTINCT] [AS STRUCT | AS VALUE]',
   'FROM',
   'WHERE',
   'GROUP BY',
   'HAVING',
-  'ORDER BY',
   'QUALIFY',
   'WINDOW',
   'PARTITION BY',
+  'ORDER BY',
   'LIMIT',
   'OFFSET',
-  'WITH',
   'OMIT RECORD IF', // legacy
   // DML, https://cloud.google.com/bigquery/docs/reference/standard-sql/dml-syntax
   'INSERT [INTO]',
@@ -27,7 +27,6 @@ const reservedCommands = expandPhrases([
   'TRUNCATE TABLE',
   'UPDATE',
   'MERGE [INTO]',
-  // 'USING',
   // DDL, https://cloud.google.com/bigquery/docs/reference/standard-sql/data-definition-language
   'CREATE SCHEMA [IF NOT EXISTS]',
   'DEFAULT COLLATE',
diff --git a/src/languages/db2/db2.formatter.ts b/src/languages/db2/db2.formatter.ts
index dbb39729..5448697a 100644
--- a/src/languages/db2/db2.formatter.ts
+++ b/src/languages/db2/db2.formatter.ts
@@ -4,8 +4,17 @@ import Tokenizer from 'src/lexer/Tokenizer';
 import { functions } from './db2.functions';
 import { keywords } from './db2.keywords';
 
-// https://www.ibm.com/docs/en/db2-for-zos/11?topic=statements-list-supported
-const reservedCommands = [
+const reservedCommands = expandPhrases([
+  // queries
+  'WITH',
+  'SELECT [ALL | DISTINCT]',
+  'FROM',
+  'WHERE',
+  'GROUP BY',
+  'HAVING',
+  'ORDER BY [INPUT SEQUENCE]',
+  'FETCH FIRST',
+  // https://www.ibm.com/docs/en/db2-for-zos/11?topic=statements-list-supported
   'ALLOCATE CURSOR',
   'ALTER DATABASE',
   'ALTER FUNCTION',
@@ -85,7 +94,6 @@ const reservedCommands = [
   'REVOKE',
   'ROLLBACK',
   'SAVEPOINT',
-  'SELECT',
   'SELECT INTO',
   'SET CONNECTION',
   'SET',
@@ -126,20 +134,10 @@ const reservedCommands = [
   'ALTER COLUMN', // verify
   'AFTER',
   'DROP TABLE', // verify
-  'FETCH FIRST',
-  'FROM',
-  'GROUP BY',
   'GO',
-  'HAVING',
   'INSERT INTO',
-  'LIMIT',
-  'OFFSET',
-  'ORDER BY',
-  'SELECT',
   'SET CURRENT SCHEMA',
-  'WHERE',
-  'WITH',
-];
+]);
 
 const reservedSetOperations = expandPhrases(['UNION [ALL]', 'EXCEPT [ALL]', 'INTERSECT [ALL]']);
 
diff --git a/src/languages/hive/hive.formatter.ts b/src/languages/hive/hive.formatter.ts
index 668ccd73..ec4c6507 100644
--- a/src/languages/hive/hive.formatter.ts
+++ b/src/languages/hive/hive.formatter.ts
@@ -4,8 +4,22 @@ import Tokenizer from 'src/lexer/Tokenizer';
 import { functions } from './hive.functions';
 import { keywords } from './hive.keywords';
 
-const reservedCommands = [
-  // commands
+const reservedCommands = expandPhrases([
+  // queries
+  'WITH',
+  'SELECT [ALL | DISTINCT]',
+  'FROM',
+  'WHERE',
+  'GROUP BY',
+  'HAVING',
+  'WINDOW',
+  'PARTITION BY',
+  'ORDER BY',
+  'SORT BY',
+  'CLUSTER BY',
+  'DISTRIBUTE BY',
+  'LIMIT',
+  // other
   'ALTER',
   'ALTER COLUMN', // added
   'ALTER TABLE', // added
@@ -16,32 +30,19 @@ const reservedCommands = [
   'DROP',
   'DROP TABLE', // added
   'FETCH',
-  'FROM',
-  'GROUP BY',
-  'HAVING',
   'INSERT',
   'INSERT INTO', // added
-  'LIMIT',
-  'OFFSET',
-  'ORDER BY',
-  'SELECT',
   'SET',
   'SET SCHEMA', // added
   'SHOW',
-  'SORT BY',
   'TRUNCATE',
   'UPDATE',
   'VALUES',
-  'WHERE',
-  'WITH',
-  'WINDOW',
-  'PARTITION BY',
-
   // newline keywords
   'STORED AS',
   'STORED BY',
   'ROW FORMAT',
-];
+]);
 
 const reservedSetOperations = expandPhrases(['UNION [ALL | DISTINCT]']);
 
diff --git a/src/languages/mariadb/mariadb.formatter.ts b/src/languages/mariadb/mariadb.formatter.ts
index 98765601..9786388f 100644
--- a/src/languages/mariadb/mariadb.formatter.ts
+++ b/src/languages/mariadb/mariadb.formatter.ts
@@ -5,8 +5,19 @@ import { EOF_TOKEN, isToken, type Token, TokenType } from 'src/lexer/token';
 import { keywords } from './mariadb.keywords';
 import { functions } from './mariadb.functions';
 
-// https://mariadb.com/docs/reference/mdb/sql-statements/
-const reservedCommands = [
+const reservedCommands = expandPhrases([
+  // queries
+  'WITH [RECURSIVE]',
+  'SELECT [ALL | DISTINCT | DISTINCTROW]',
+  'FROM',
+  'WHERE',
+  'GROUP BY',
+  'HAVING',
+  'ORDER BY',
+  'LIMIT',
+  'OFFSET',
+  'FETCH {FIRST | NEXT}',
+  // https://mariadb.com/docs/reference/mdb/sql-statements/
   'ALTER DATABASE',
   'ALTER DATABASE COMMENT',
   'ALTER EVENT',
@@ -200,7 +211,6 @@ const reservedCommands = [
   'UNLOCK TABLE',
   'UPDATE',
   'USE',
-  'WITH',
   'XA BEGIN',
   'XA COMMIT',
   'XA END',
@@ -211,18 +221,10 @@ const reservedCommands = [
   // other
   'ADD',
   'ALTER COLUMN',
-  'FROM',
-  'GROUP BY',
-  'HAVING',
   'INSERT INTO',
   'INSERT',
-  'LIMIT',
-  'OFFSET',
-  'ORDER BY',
-  'SELECT',
   'VALUES',
-  'WHERE',
-];
+]);
 
 const reservedSetOperations = expandPhrases([
   'UNION [ALL | DISTINCT]',
diff --git a/src/languages/mysql/mysql.formatter.ts b/src/languages/mysql/mysql.formatter.ts
index 75ec6ae8..6a932883 100644
--- a/src/languages/mysql/mysql.formatter.ts
+++ b/src/languages/mysql/mysql.formatter.ts
@@ -5,8 +5,20 @@ import { EOF_TOKEN, isToken, type Token, TokenType } from 'src/lexer/token';
 import { keywords } from './mysql.keywords';
 import { functions } from './mysql.functions';
 
-// https://dev.mysql.com/doc/refman/8.0/en/sql-statements.html
-const reservedCommands = [
+const reservedCommands = expandPhrases([
+  // queries
+  'WITH [RECURSIVE]',
+  'SELECT [ALL | DISTINCT | DISTINCTROW]',
+  'FROM',
+  'WHERE',
+  'GROUP BY',
+  'HAVING',
+  'WINDOW',
+  'PARTITION BY',
+  'ORDER BY',
+  'LIMIT',
+  'OFFSET',
+  // https://dev.mysql.com/doc/refman/8.0/en/sql-statements.html
   'ALTER DATABASE',
   'ALTER EVENT',
   'ALTER FUNCTION',
@@ -102,7 +114,6 @@ const reservedCommands = [
   'ROLLBACK',
   'ROLLBACK TO SAVEPOINT',
   'SAVEPOINT',
-  'SELECT',
   'SET',
   'SET CHARACTER SET',
   'SET DEFAULT ROLE',
@@ -172,7 +183,6 @@ const reservedCommands = [
   'UPDATE',
   'USE',
   'VALUES',
-  'WITH',
   'XA',
   // flow control
   // 'IF',
@@ -185,17 +195,8 @@ const reservedCommands = [
   // other
   'ADD',
   'ALTER COLUMN',
-  'FROM',
-  'GROUP BY',
-  'HAVING',
   'INSERT INTO',
-  'LIMIT',
-  'OFFSET',
-  'ORDER BY',
-  'WHERE',
-  'WINDOW',
-  'PARTITION BY',
-];
+]);
 
 const reservedSetOperations = expandPhrases(['UNION [ALL | DISTINCT]']);
 
diff --git a/src/languages/n1ql/n1ql.formatter.ts b/src/languages/n1ql/n1ql.formatter.ts
index ca8084ed..29d22cef 100644
--- a/src/languages/n1ql/n1ql.formatter.ts
+++ b/src/languages/n1ql/n1ql.formatter.ts
@@ -4,8 +4,20 @@ import Tokenizer from 'src/lexer/Tokenizer';
 import { functions } from './n1ql.functions';
 import { keywords } from './n1ql.keywords';
 
-// https://docs.couchbase.com/server/current/n1ql/n1ql-language-reference/reservedwords.html
-const reservedCommands = [
+const reservedCommands = expandPhrases([
+  // queries
+  'WITH',
+  'SELECT [ALL | DISTINCT]',
+  'FROM',
+  'WHERE',
+  'GROUP BY',
+  'HAVING',
+  'WINDOW',
+  'PARTITION BY',
+  'ORDER BY',
+  'LIMIT',
+  'OFFSET',
+  // https://docs.couchbase.com/server/current/n1ql/n1ql-language-reference/reservedwords.html
   'ADVISE',
   'ALTER INDEX',
   'BEGIN TRANSACTION',
@@ -36,22 +48,15 @@ const reservedCommands = [
   'REVOKE',
   'ROLLBACK TRANSACTION',
   'SAVEPOINT',
-  'SELECT',
   'SET TRANSACTION',
   'UPDATE',
   'UPDATE STATISTICS',
   'UPSERT',
   // other
   'DROP TABLE', // verify,
-  'FROM',
-  'GROUP BY',
-  'HAVING',
   'INSERT INTO',
   'LET',
-  'LIMIT',
-  'OFFSET',
   'NEST',
-  'ORDER BY',
   'SET CURRENT SCHEMA',
   'SET SCHEMA',
   'SET',
@@ -59,11 +64,7 @@ const reservedCommands = [
   'UNNEST',
   'USE KEYS',
   'VALUES',
-  'WHERE',
-  'WITH',
-  'WINDOW',
-  'PARTITION BY',
-];
+]);
 
 const reservedSetOperations = expandPhrases(['UNION [ALL]', 'EXCEPT [ALL]', 'INTERSECT [ALL]']);
 
diff --git a/src/languages/plsql/plsql.formatter.ts b/src/languages/plsql/plsql.formatter.ts
index 1a054695..7343220d 100644
--- a/src/languages/plsql/plsql.formatter.ts
+++ b/src/languages/plsql/plsql.formatter.ts
@@ -5,7 +5,18 @@ import { EOF_TOKEN, isReserved, isToken, type Token, TokenType } from 'src/lexer
 import { keywords } from './plsql.keywords';
 import { functions } from './plsql.functions';
 
-const reservedCommands = [
+const reservedCommands = expandPhrases([
+  // queries
+  'WITH',
+  'SELECT [ALL | DISTINCT | UNIQUE]',
+  'FROM',
+  'WHERE',
+  'GROUP BY',
+  'HAVING',
+  'ORDER [SIBLINGS] BY',
+  'OFFSET',
+  'FETCH {FIRST | NEXT}',
+  // other
   'ADD',
   'ALTER COLUMN',
   'ALTER TABLE',
@@ -18,28 +29,18 @@ const reservedCommands = [
   'DELETE FROM',
   'EXCEPT',
   'EXCEPTION',
-  'FETCH FIRST',
-  'FROM',
-  'GROUP BY',
-  'HAVING',
   'INSERT INTO',
   'INSERT',
-  'LIMIT',
-  'OFFSET',
   'LOOP',
   'MODIFY',
-  'ORDER BY',
   'RETURNING',
-  'SELECT',
   'SET CURRENT SCHEMA',
   'SET SCHEMA',
   'SET',
   'START WITH',
   'UPDATE',
   'VALUES',
-  'WHERE',
-  'WITH',
-];
+]);
 
 const reservedSetOperations = expandPhrases(['UNION [ALL]', 'EXCEPT', 'INTERSECT']);
 
diff --git a/src/languages/postgresql/postgresql.formatter.ts b/src/languages/postgresql/postgresql.formatter.ts
index d9038746..82aa9a2c 100644
--- a/src/languages/postgresql/postgresql.formatter.ts
+++ b/src/languages/postgresql/postgresql.formatter.ts
@@ -4,8 +4,21 @@ import Tokenizer from 'src/lexer/Tokenizer';
 import { functions } from './postgresql.functions';
 import { keywords } from './postgresql.keywords';
 
-// https://www.postgresql.org/docs/14/sql-commands.html
-const reservedCommands = [
+const reservedCommands = expandPhrases([
+  // queries
+  'WITH [RECURSIVE]',
+  'SELECT [ALL | DISTINCT]',
+  'FROM',
+  'WHERE',
+  'GROUP BY [ALL | DISTINCT]',
+  'HAVING',
+  'WINDOW',
+  'PARTITION BY',
+  'ORDER BY',
+  'LIMIT',
+  'OFFSET',
+  'FETCH {FIRST | NEXT}',
+  // https://www.postgresql.org/docs/14/sql-commands.html
   'ABORT',
   'ALTER AGGREGATE',
   'ALTER COLLATION',
@@ -175,7 +188,6 @@ const reservedCommands = [
   'ROLLBACK TO SAVEPOINT',
   'SAVEPOINT',
   'SECURITY LABEL',
-  'SELECT',
   'SELECT INTO',
   'SET',
   'SET CONSTRAINTS',
@@ -195,17 +207,7 @@ const reservedCommands = [
   'ALTER COLUMN',
   'INSERT INTO', // verify
   'SET SCHEMA', // verify
-  'FROM',
-  'GROUP BY',
-  'HAVING',
-  'LIMIT',
-  'OFFSET',
-  'ORDER BY',
-  'WHERE',
-  'WITH',
-  'WINDOW',
-  'PARTITION BY',
-];
+]);
 
 const reservedSetOperations = expandPhrases([
   'UNION [ALL | DISTINCT]',
diff --git a/src/languages/redshift/redshift.formatter.ts b/src/languages/redshift/redshift.formatter.ts
index d8ae1629..34d56fc6 100644
--- a/src/languages/redshift/redshift.formatter.ts
+++ b/src/languages/redshift/redshift.formatter.ts
@@ -4,8 +4,18 @@ import Tokenizer from 'src/lexer/Tokenizer';
 import { functions } from './redshift.functions';
 import { keywords } from './redshift.keywords';
 
-// https://docs.aws.amazon.com/redshift/latest/dg/c_SQL_commands.html
-const reservedCommands = [
+const reservedCommands = expandPhrases([
+  // queries
+  'WITH [RECURSIVE]',
+  'SELECT [ALL | DISTINCT]',
+  'FROM',
+  'WHERE',
+  'GROUP BY',
+  'HAVING',
+  'ORDER BY',
+  'LIMIT',
+  'OFFSET',
+  // https://docs.aws.amazon.com/redshift/latest/dg/c_SQL_commands.html
   'ABORT',
   'ALTER DATABASE',
   'ALTER DATASHARE',
@@ -65,9 +75,7 @@ const reservedCommands = [
   'EXECUTE',
   'EXPLAIN',
   'FETCH',
-  'FROM',
   'GRANT',
-  'HAVING',
   'INSERT',
   'LOCK',
   'PREPARE',
@@ -75,7 +83,6 @@ const reservedCommands = [
   'RESET',
   'REVOKE',
   'ROLLBACK',
-  'SELECT',
   'SELECT INTO',
   'SET',
   'SET SESSION AUTHORIZATION',
@@ -92,19 +99,13 @@ const reservedCommands = [
   'UNLOAD',
   'UPDATE',
   'VACUUM',
-  'WHERE',
-  'WITH',
-  // other
-  'GROUP BY',
-  'ORDER BY',
-  'LIMIT',
-  'OFFSET',
   'VALUES',
+  // other
   'MODIFY', // verify
   'INSERT INTO', // verify
   'ALTER COLUMN', // verify
   'SET SCHEMA', // verify
-];
+]);
 
 const reservedSetOperations = expandPhrases(['UNION [ALL]', 'EXCEPT', 'INTERSECT', 'MINUS']);
 
diff --git a/src/languages/spark/spark.formatter.ts b/src/languages/spark/spark.formatter.ts
index 0b6e4fc0..5281b53c 100644
--- a/src/languages/spark/spark.formatter.ts
+++ b/src/languages/spark/spark.formatter.ts
@@ -6,7 +6,20 @@ import { keywords } from './spark.keywords';
 import { functions } from './spark.functions';
 
 // http://spark.apache.org/docs/latest/sql-ref-syntax.html
-const reservedCommands = [
+const reservedCommands = expandPhrases([
+  // queries
+  'WITH',
+  'SELECT [ALL | DISTINCT]',
+  'FROM',
+  'WHERE',
+  'GROUP BY',
+  'HAVING',
+  'WINDOW',
+  'ORDER BY',
+  'SORT BY',
+  'CLUSTER BY',
+  'DISTRIBUTE BY',
+  'LIMIT',
   // DDL
   'ALTER COLUMN',
   'ALTER DATABASE',
@@ -29,19 +42,8 @@ const reservedCommands = [
   'INSERT OVERWRITE DIRECTORY',
   'LOAD',
   // Data Retrieval
-  'SELECT',
-  'WITH',
-  'CLUSTER BY',
-  'DISTRIBUTE BY',
-  'GROUP BY',
-  'HAVING',
   'VALUES',
-  'LIMIT',
-  'OFFSET',
-  'ORDER BY',
-  'SORT BY',
   'TABLESAMPLE',
-  'WHERE',
   'PIVOT',
   'TRANSFORM',
   'EXPLAIN',
@@ -74,12 +76,10 @@ const reservedCommands = [
   'SHOW VIEWS',
   'UNCACHE TABLE',
   // other
-  'FROM',
   'INSERT',
   'LATERAL VIEW',
   'UPDATE',
-  'WINDOW',
-];
+]);
 
 const reservedSetOperations = expandPhrases([
   'UNION [ALL | DISTINCT]',
diff --git a/src/languages/sql/sql.formatter.ts b/src/languages/sql/sql.formatter.ts
index 43add630..80426117 100644
--- a/src/languages/sql/sql.formatter.ts
+++ b/src/languages/sql/sql.formatter.ts
@@ -4,36 +4,33 @@ import Tokenizer from 'src/lexer/Tokenizer';
 import { functions } from './sql.functions';
 import { keywords } from './sql.keywords';
 
-const reservedCommands = [
+const reservedCommands = expandPhrases([
+  // queries
+  'WITH [RECURSIVE]',
+  'SELECT [ALL | DISTINCT]',
+  'FROM',
+  'WHERE',
+  'GROUP BY [ALL | DISTINCT]',
+  'HAVING',
+  'WINDOW',
+  'PARTITION BY',
+  'ORDER BY',
+  'LIMIT',
+  'OFFSET',
+  'FETCH {FIRST | NEXT}',
+  // other
   'ADD',
   'ALTER COLUMN',
   'ALTER TABLE',
   'CREATE TABLE',
   'DROP TABLE',
   'DELETE FROM',
-  'FETCH FIRST',
-  'FETCH NEXT',
-  'FETCH PRIOR',
-  'FETCH LAST',
-  'FETCH ABSOLUTE',
-  'FETCH RELATIVE',
-  'FROM',
-  'GROUP BY',
-  'HAVING',
   'INSERT INTO',
-  'LIMIT',
-  'OFFSET',
-  'ORDER BY',
-  'SELECT',
   'SET SCHEMA',
   'SET',
   'UPDATE',
   'VALUES',
-  'WHERE',
-  'WITH',
-  'WINDOW',
-  'PARTITION BY',
-];
+]);
 
 const reservedSetOperations = expandPhrases([
   'UNION [ALL | DISTINCT]',
diff --git a/src/languages/sqlite/sqlite.formatter.ts b/src/languages/sqlite/sqlite.formatter.ts
index 1e93b4fc..63ecb178 100644
--- a/src/languages/sqlite/sqlite.formatter.ts
+++ b/src/languages/sqlite/sqlite.formatter.ts
@@ -4,7 +4,20 @@ import Tokenizer from 'src/lexer/Tokenizer';
 import { functions } from './sqlite.functions';
 import { keywords } from './sqlite.keywords';
 
-const reservedCommands = [
+const reservedCommands = expandPhrases([
+  // queries
+  'WITH [RECURSIVE]',
+  'SELECT [ALL | DISTINCT]',
+  'FROM',
+  'WHERE',
+  'GROUP BY',
+  'HAVING',
+  'WINDOW',
+  'PARTITION BY',
+  'ORDER BY',
+  'LIMIT',
+  'OFFSET',
+  // other
   'ADD',
   'ALTER COLUMN',
   'ALTER TABLE',
@@ -12,29 +25,12 @@ const reservedCommands = [
   'DROP TABLE',
   'DELETE',
   'DELETE FROM',
-  'FETCH FIRST',
-  'FETCH NEXT',
-  'FETCH PRIOR',
-  'FETCH LAST',
-  'FETCH ABSOLUTE',
-  'FETCH RELATIVE',
-  'FROM',
-  'GROUP BY',
-  'HAVING',
   'INSERT INTO',
-  'LIMIT',
-  'OFFSET',
-  'ORDER BY',
-  'SELECT',
   'SET SCHEMA',
   'SET',
   'UPDATE',
   'VALUES',
-  'WHERE',
-  'WITH',
-  'WINDOW',
-  'PARTITION BY',
-];
+]);
 
 const reservedSetOperations = expandPhrases(['UNION [ALL]', 'EXCEPT', 'INTERSECT']);
 
diff --git a/src/languages/trino/trino.formatter.ts b/src/languages/trino/trino.formatter.ts
index c635756b..8a3d0380 100644
--- a/src/languages/trino/trino.formatter.ts
+++ b/src/languages/trino/trino.formatter.ts
@@ -5,7 +5,20 @@ import { functions } from './trino.functions';
 import { keywords } from './trino.keywords';
 
 // https://github.com/trinodb/trino/blob/432d2897bdef99388c1a47188743a061c4ac1f34/core/trino-parser/src/main/antlr4/io/trino/sql/parser/SqlBase.g4#L41
-const reservedCommands = [
+const reservedCommands = expandPhrases([
+  // queries
+  'WITH [RECURSIVE]',
+  'SELECT [ALL | DISTINCT]',
+  'FROM',
+  'WHERE',
+  'GROUP BY [ALL | DISTINCT]',
+  'HAVING',
+  'WINDOW',
+  'PARTITION BY',
+  'ORDER BY',
+  'LIMIT',
+  'OFFSET',
+  'FETCH {FIRST | NEXT}',
   // DDL
   'ALTER SCHEMA',
   'ALTER TABLE',
@@ -39,21 +52,6 @@ const reservedCommands = [
   'UPDATE',
   'SET',
   'DELETE FROM',
-  // Data Retrieval
-  'WITH',
-  'SELECT',
-  'FROM',
-  'WHERE',
-  'GROUP BY',
-  'HAVING',
-  'WINDOW',
-  'ORDER BY',
-  'OFFSET',
-  'LIMIT',
-  'FETCH',
-
-  'PARTITION BY',
-
   // Auxiliary
   'EXPLAIN',
   'ANALYZE',
@@ -97,7 +95,7 @@ const reservedCommands = [
   'PATTERN',
   'SUBSET',
   'DEFINE',
-];
+]);
 
 // https://github.com/trinodb/trino/blob/432d2897bdef99388c1a47188743a061c4ac1f34/core/trino-parser/src/main/antlr4/io/trino/sql/parser/SqlBase.g4#L231-L235
 // https://github.com/trinodb/trino/blob/432d2897bdef99388c1a47188743a061c4ac1f34/core/trino-parser/src/main/antlr4/io/trino/sql/parser/SqlBase.g4#L288-L291
diff --git a/src/languages/tsql/tsql.formatter.ts b/src/languages/tsql/tsql.formatter.ts
index efb6d9f5..0ff0a549 100644
--- a/src/languages/tsql/tsql.formatter.ts
+++ b/src/languages/tsql/tsql.formatter.ts
@@ -4,8 +4,20 @@ import Tokenizer from 'src/lexer/Tokenizer';
 import { functions } from './tsql.functions';
 import { keywords } from './tsql.keywords';
 
-// https://docs.microsoft.com/en-us/sql/t-sql/statements/statements?view=sql-server-ver15
-const reservedCommands = [
+const reservedCommands = expandPhrases([
+  // queries
+  'WITH',
+  'SELECT [ALL | DISTINCT]',
+  'FROM',
+  'WHERE',
+  'GROUP BY',
+  'HAVING',
+  'WINDOW',
+  'PARTITION BY',
+  'ORDER BY',
+  'OFFSET',
+  'FETCH {FIRST | NEXT}',
+  // https://docs.microsoft.com/en-us/sql/t-sql/statements/statements?view=sql-server-ver15
   'ADD SENSITIVITY CLASSIFICATION',
   'ADD SIGNATURE',
   'AGGREGATE',
@@ -172,22 +184,11 @@ const reservedCommands = [
   'ALTER COLUMN',
   'ALTER TABLE',
   'CREATE TABLE',
-  'FROM',
-  'GROUP BY',
-  'HAVING',
   'INSERT INTO', // verify
   'DROP TABLE', // verify
   'SET SCHEMA', // verify
-  'LIMIT',
-  'OFFSET',
-  'ORDER BY',
-  'SELECT',
   'VALUES',
-  'WHERE',
-  'WITH',
-  'WINDOW',
-  'PARTITION BY',
-];
+]);
 
 const reservedSetOperations = expandPhrases(['UNION [ALL]', 'EXCEPT', 'INTERSECT']);
 


Test patch:
diff --git a/test/behavesLikeSqlFormatter.ts b/test/behavesLikeSqlFormatter.ts
index 905a4094..b21c2986 100644
--- a/test/behavesLikeSqlFormatter.ts
+++ b/test/behavesLikeSqlFormatter.ts
@@ -4,6 +4,7 @@ import { FormatFn } from 'src/sqlFormatter';
 
 import supportsCase from './features/case';
 import supportsNumbers from './features/numbers';
+import supportsWith from './features/with';
 import supportsTabWidth from './options/tabWidth';
 import supportsUseTabs from './options/useTabs';
 import supportsAliasAs from './options/aliasAs';
@@ -15,7 +16,6 @@ import supportsLinesBetweenQueries from './options/linesBetweenQueries';
 import supportsNewlineBeforeSemicolon from './options/newlineBeforeSemicolon';
 import supportsLogicalOperatorNewline from './options/logicalOperatorNewline';
 import supportsTabulateAlias from './options/tabulateAlias';
-import supportsLimit from './features/limit';
 
 /**
  * Core tests for all SQL formatters
@@ -23,6 +23,7 @@ import supportsLimit from './features/limit';
 export default function behavesLikeSqlFormatter(format: FormatFn) {
   supportsCase(format);
   supportsNumbers(format);
+  supportsWith(format);
 
   supportsAliasAs(format);
   supportsTabulateAlias(format);
@@ -35,7 +36,6 @@ export default function behavesLikeSqlFormatter(format: FormatFn) {
   supportsNewlineBeforeSemicolon(format);
   supportsCommaPosition(format);
   supportsLogicalOperatorNewline(format);
-  supportsLimit(format);
 
   it('formats simple SELECT query', () => {
     const result = format('SELECT count(*),Column1 FROM Table1;');
@@ -53,8 +53,8 @@ export default function behavesLikeSqlFormatter(format: FormatFn) {
       "SELECT DISTINCT name, ROUND(age/7) field1, 18 + 20 AS field2, 'some string' FROM foo;"
     );
     expect(result).toBe(dedent`
-      SELECT
-        DISTINCT name,
+      SELECT DISTINCT
+        name,
         ROUND(age / 7) field1,
         18 + 20 AS field2,
         'some string'
@@ -87,7 +87,7 @@ export default function behavesLikeSqlFormatter(format: FormatFn) {
   it('formats SELECT with top level reserved words', () => {
     const result = format(`
       SELECT * FROM foo WHERE name = 'John' GROUP BY some_column
-      HAVING column > 10 ORDER BY other_column LIMIT 5;
+      HAVING column > 10 ORDER BY other_column;
     `);
     expect(result).toBe(dedent`
       SELECT
@@ -101,9 +101,7 @@ export default function behavesLikeSqlFormatter(format: FormatFn) {
       HAVING
         column > 10
       ORDER BY
-        other_column
-      LIMIT
-        5;
+        other_column;
     `);
   });
 
@@ -139,7 +137,7 @@ export default function behavesLikeSqlFormatter(format: FormatFn) {
 
   it('formats SELECT query with SELECT query inside it', () => {
     const result = format(
-      'SELECT *, SUM(*) AS total FROM (SELECT * FROM Posts LIMIT 30) WHERE a > b'
+      'SELECT *, SUM(*) AS total FROM (SELECT * FROM Posts WHERE age > 10) WHERE a > b'
     );
     expect(result).toBe(dedent`
       SELECT
@@ -151,8 +149,8 @@ export default function behavesLikeSqlFormatter(format: FormatFn) {
             *
           FROM
             Posts
-          LIMIT
-            30
+          WHERE
+            age > 10
         )
       WHERE
         a > b
@@ -172,25 +170,18 @@ export default function behavesLikeSqlFormatter(format: FormatFn) {
   });
 
   it('formats open paren after comma', () => {
-    const result = format(
-      'WITH TestIds AS (VALUES (4),(5), (6),(7),(9),(10),(11)) SELECT * FROM TestIds;'
-    );
-    expect(result).toBe(dedent/* sql */ `
-      WITH
-        TestIds AS (
-          VALUES
-            (4),
-            (5),
-            (6),
-            (7),
-            (9),
-            (10),
-            (11)
-        )
-      SELECT
-        *
-      FROM
-        TestIds;
+    const result = format('INSERT INTO TestIds (id) VALUES (4),(5), (6),(7),(9),(10),(11);');
+    expect(result).toBe(dedent`
+      INSERT INTO
+        TestIds (id)
+      VALUES
+        (4),
+        (5),
+        (6),
+        (7),
+        (9),
+        (10),
+        (11);
     `);
   });
 
@@ -346,23 +337,4 @@ export default function behavesLikeSqlFormatter(format: FormatFn) {
         tbl;
     `);
   });
-
-  it('does not split UNION ALL in half', () => {
-    const result = format(`
-      SELECT * FROM tbl1
-      UNION ALL
-      SELECT * FROM tbl2;
-    `);
-    expect(result).toBe(dedent/* sql */ `
-      SELECT
-        *
-      FROM
-        tbl1
-      UNION ALL
-      SELECT
-        *
-      FROM
-        tbl2;
-    `);
-  });
 }
diff --git a/test/bigquery.test.ts b/test/bigquery.test.ts
index ead2cf41..891c83d6 100644
--- a/test/bigquery.test.ts
+++ b/test/bigquery.test.ts
@@ -17,6 +17,7 @@ import supportsIdentifiers from './features/identifiers';
 import supportsParams from './options/param';
 import supportsWindow from './features/window';
 import supportsSetOperations from './features/setOperations';
+import supportsLimiting from './features/limiting';
 
 describe('BigQueryFormatter', () => {
   const language = 'bigquery';
@@ -40,6 +41,7 @@ describe('BigQueryFormatter', () => {
   supportsOperators(format, BigQueryFormatter.operators);
   supportsParams(format, { positional: true, named: ['@'], quoted: ['@``'] });
   supportsWindow(format);
+  supportsLimiting(format, { limit: true, offset: true });
 
   // Note: BigQuery supports single dashes inside identifiers, so my-ident would be
   // detected as identifier, while other SQL dialects would detect it as
@@ -159,6 +161,34 @@ describe('BigQueryFormatter', () => {
     `);
   });
 
+  it('supports QUALIFY clause', () => {
+    expect(
+      format(`
+        SELECT
+          item,
+          RANK() OVER (PARTITION BY category ORDER BY purchases DESC) AS rank
+        FROM Produce
+        WHERE Produce.category = 'vegetable'
+        QUALIFY rank <= 3
+      `)
+    ).toBe(dedent`
+      SELECT
+        item,
+        RANK() OVER (
+          PARTITION BY
+            category
+          ORDER BY
+            purchases DESC
+        ) AS rank
+      FROM
+        Produce
+      WHERE
+        Produce.category = 'vegetable'
+      QUALIFY
+        rank <= 3
+    `);
+  });
+
   it('supports parameterised types', () => {
     const result = format(
       `
diff --git a/test/db2.test.ts b/test/db2.test.ts
index 974dcd05..c854871c 100644
--- a/test/db2.test.ts
+++ b/test/db2.test.ts
@@ -17,6 +17,7 @@ import supportsComments from './features/comments';
 import supportsIdentifiers from './features/identifiers';
 import supportsParams from './options/param';
 import supportsSetOperations from './features/setOperations';
+import supportsLimiting from './features/limiting';
 
 describe('Db2Formatter', () => {
   const language = 'db2';
@@ -43,19 +44,7 @@ describe('Db2Formatter', () => {
     'INTERSECT ALL',
   ]);
   supportsParams(format, { positional: true, named: [':'] });
-
-  it('formats FETCH FIRST like LIMIT', () => {
-    expect(format('SELECT col1 FROM tbl ORDER BY col2 DESC FETCH FIRST 20 ROWS ONLY;')).toBe(dedent`
-        SELECT
-          col1
-        FROM
-          tbl
-        ORDER BY
-          col2 DESC
-        FETCH FIRST
-          20 ROWS ONLY;
-      `);
-  });
+  supportsLimiting(format, { fetchFirst: true });
 
   it('formats only -- as a line comment', () => {
     const result = format(`
diff --git a/test/features/limit.ts b/test/features/limit.ts
deleted file mode 100644
index 9ed35b0a..00000000
--- a/test/features/limit.ts
+++ /dev/null
@@ -1,55 +0,0 @@
-import dedent from 'dedent-js';
-
-import { FormatFn } from 'src/sqlFormatter';
-
-export default function supportsLimit(format: FormatFn) {
-  it('formats LIMIT with two comma-separated values on single line', () => {
-    const result = format('LIMIT 5, 10;');
-    expect(result).toBe(dedent`
-      LIMIT
-        5, 10;
-    `);
-  });
-
-  it('formats LIMIT of single value followed by another SELECT using commas', () => {
-    const result = format('LIMIT 5; SELECT foo, bar;');
-    expect(result).toBe(dedent`
-      LIMIT
-        5;
-
-      SELECT
-        foo,
-        bar;
-    `);
-  });
-
-  it('formats LIMIT of single value and OFFSET', () => {
-    const result = format('LIMIT 5 OFFSET 8;');
-    expect(result).toBe(dedent`
-      LIMIT
-        5
-      OFFSET
-        8;
-    `);
-  });
-
-  // Regression test for #303
-  it('formats LIMIT with complex expressions', () => {
-    const result = format('LIMIT abs(-5) - 1, (2 + 3) * 5;');
-    expect(result).toBe(dedent`
-      LIMIT
-        abs(-5) - 1, (2 + 3) * 5;
-    `);
-  });
-
-  // Regression test for #301
-  it('formats LIMIT with comments', () => {
-    const result = format('LIMIT --comment\n 5,--comment\n6;');
-    expect(result).toBe(dedent`
-      LIMIT
-        --comment
-        5, --comment
-        6;
-    `);
-  });
-}
diff --git a/test/features/limiting.ts b/test/features/limiting.ts
new file mode 100644
index 00000000..fe910ecd
--- /dev/null
+++ b/test/features/limiting.ts
@@ -0,0 +1,114 @@
+import dedent from 'dedent-js';
+
+import { FormatFn } from 'src/sqlFormatter';
+
+interface LimitingTypes {
+  limit?: boolean;
+  offset?: boolean;
+  fetchFirst?: boolean;
+  fetchNext?: boolean;
+}
+
+export default function supportsLimiting(format: FormatFn, types: LimitingTypes) {
+  if (types.limit) {
+    it('formats LIMIT with two comma-separated values on single line', () => {
+      const result = format('SELECT * FROM tbl LIMIT 5, 10;');
+      expect(result).toBe(dedent`
+        SELECT
+          *
+        FROM
+          tbl
+        LIMIT
+          5, 10;
+      `);
+    });
+
+    // Regression test for #303
+    it('formats LIMIT with complex expressions', () => {
+      const result = format('SELECT * FROM tbl LIMIT abs(-5) - 1, (2 + 3) * 5;');
+      expect(result).toBe(dedent`
+        SELECT
+          *
+        FROM
+          tbl
+        LIMIT
+          abs(-5) - 1, (2 + 3) * 5;
+      `);
+    });
+
+    // Regression test for #301
+    it('formats LIMIT with comments', () => {
+      const result = format('SELECT * FROM tbl LIMIT --comment\n 5,--comment\n6;');
+      expect(result).toBe(dedent`
+          SELECT
+            *
+          FROM
+            tbl
+          LIMIT
+            --comment
+            5, --comment
+            6;
+      `);
+    });
+  }
+
+  if (types.limit && types.offset) {
+    it('formats LIMIT of single value and OFFSET', () => {
+      const result = format('SELECT * FROM tbl LIMIT 5 OFFSET 8;');
+      expect(result).toBe(dedent`
+        SELECT
+          *
+        FROM
+          tbl
+        LIMIT
+          5
+        OFFSET
+          8;
+      `);
+    });
+  }
+
+  if (types.fetchFirst) {
+    it('formats FETCH FIRST', () => {
+      const result = format('SELECT * FROM tbl FETCH FIRST 10 ROWS ONLY;');
+      expect(result).toBe(dedent`
+        SELECT
+          *
+        FROM
+          tbl
+        FETCH FIRST
+          10 ROWS ONLY;
+      `);
+    });
+  }
+
+  if (types.fetchNext) {
+    it('formats FETCH NEXT', () => {
+      const result = format('SELECT * FROM tbl FETCH NEXT 1 ROW ONLY;');
+      expect(result).toBe(dedent`
+        SELECT
+          *
+        FROM
+          tbl
+        FETCH NEXT
+          1 ROW ONLY;
+      `);
+    });
+  }
+
+  if (types.fetchFirst && types.offset) {
+    it('formats OFFSET ... FETCH FIRST', () => {
+      const result = format('SELECT * FROM tbl OFFSET 250 ROWS FETCH FIRST 5 ROWS ONLY;');
+      expect(result).toBe(dedent`
+        SELECT
+          *
+        FROM
+          tbl
+        OFFSET
+          250 ROWS
+        FETCH FIRST
+          5 ROWS ONLY;
+      `);
+    });
+  }
+}
diff --git a/test/features/with.ts b/test/features/with.ts
new file mode 100644
index 00000000..66f59afa
--- /dev/null
+++ b/test/features/with.ts
@@ -0,0 +1,79 @@
+import dedent from 'dedent-js';
+
+import { FormatFn } from 'src/sqlFormatter';
+
+export default function supportsWith(format: FormatFn) {
+  it('formats WITH clause with multiple Common Table Expressions (CTE)', () => {
+    const result = format(`
+      WITH
+      cte_1 AS (
+        SELECT a FROM b WHERE c = 1
+      ),
+      cte_2 AS (
+        SELECT c FROM d WHERE e = 2
+      ),
+      final AS (
+        SELECT * FROM cte_1 LEFT JOIN cte_2 ON b = d
+      )
+      SELECT * FROM final;
+    `);
+    expect(result).toBe(dedent`
+      WITH
+        cte_1 AS (
+          SELECT
+            a
+          FROM
+            b
+          WHERE
+            c = 1
+        ),
+        cte_2 AS (
+          SELECT
+            c
+          FROM
+            d
+          WHERE
+            e = 2
+        ),
+        final AS (
+          SELECT
+            *
+          FROM
+            cte_1
+            LEFT JOIN cte_2 ON b = d
+        )
+      SELECT
+        *
+      FROM
+        final;
+    `);
+  });
+
+  it('formats WITH clause with parameterized CTE', () => {
+    const result = format(`
+      WITH cte_1(id, parent_id) AS (
+        SELECT id, parent_id
+        FROM tab1
+        WHERE parent_id IS NULL
+      )
+      SELECT id, parent_id FROM cte_1;
+    `);
+    expect(result).toBe(dedent`
+      WITH
+        cte_1 (id, parent_id) AS (
+          SELECT
+            id,
+            parent_id
+          FROM
+            tab1
+          WHERE
+            parent_id IS NULL
+        )
+      SELECT
+        id,
+        parent_id
+      FROM
+        cte_1;
+    `);
+  });
+}
diff --git a/test/hive.test.ts b/test/hive.test.ts
index 47a9b6e8..05a71a22 100644
--- a/test/hive.test.ts
+++ b/test/hive.test.ts
@@ -17,6 +17,7 @@ import supportsComments from './features/comments';
 import supportsIdentifiers from './features/identifiers';
 import supportsWindow from './features/window';
 import supportsSetOperations from './features/setOperations';
+import supportsLimiting from './features/limiting';
 
 describe('HiveFormatter', () => {
   const language = 'hive';
@@ -39,6 +40,7 @@ describe('HiveFormatter', () => {
   supportsOperators(format, HiveFormatter.operators);
   supportsArrayAndMapAccessors(format);
   supportsWindow(format);
+  supportsLimiting(format, { limit: true });
 
   it('throws error when params option used', () => {
     expect(() => format('SELECT *', { params: ['1', '2', '3'] })).toThrow(
@@ -62,4 +64,22 @@ describe('HiveFormatter', () => {
         name = '\${hivevar:name}';
     `);
   });
+
+  it('supports SORT BY, CLUSTER BY, DISTRIBUTE BY', () => {
+    const result = format(
+      'SELECT value, count DISTRIBUTE BY count CLUSTER BY value SORT BY value, count;'
+    );
+    expect(result).toBe(dedent`
+      SELECT
+        value,
+        count
+      DISTRIBUTE BY
+        count
+      CLUSTER BY
+        value
+      SORT BY
+        value,
+        count;
+    `);
+  });
 });
diff --git a/test/mariadb.test.ts b/test/mariadb.test.ts
index 53eb2b17..37e0909b 100644
--- a/test/mariadb.test.ts
+++ b/test/mariadb.test.ts
@@ -6,6 +6,7 @@ import supportsJoin from './features/join';
 import supportsOperators from './features/operators';
 import supportsReturning from './features/returning';
 import supportsSetOperations, { standardSetOperations } from './features/setOperations';
+import supportsLimiting from './features/limiting';
 
 describe('MariaDbFormatter', () => {
   const language = 'mariadb';
@@ -20,4 +21,5 @@ describe('MariaDbFormatter', () => {
   supportsSetOperations(format, [...standardSetOperations, 'MINUS', 'MINUS ALL', 'MINUS DISTINCT']);
   supportsOperators(format, MariaDbFormatter.operators, ['AND', 'OR', 'XOR']);
   supportsReturning(format);
+  supportsLimiting(format, { limit: true, offset: true, fetchFirst: true, fetchNext: true });
 });
diff --git a/test/mysql.test.ts b/test/mysql.test.ts
index 75392b55..8a7cd8c6 100644
--- a/test/mysql.test.ts
+++ b/test/mysql.test.ts
@@ -8,6 +8,7 @@ import supportsJoin from './features/join';
 import supportsOperators from './features/operators';
 import supportsWindow from './features/window';
 import supportsSetOperations from './features/setOperations';
+import supportsLimiting from './features/limiting';
 
 describe('MySqlFormatter', () => {
   const language = 'mysql';
@@ -22,6 +23,7 @@ describe('MySqlFormatter', () => {
   supportsSetOperations(format, ['UNION', 'UNION ALL', 'UNION DISTINCT']);
   supportsOperators(format, MySqlFormatter.operators, ['AND', 'OR', 'XOR']);
   supportsWindow(format);
+  supportsLimiting(format, { limit: true, offset: true });
 
   // TODO: disabled for now
   it.skip('supports @@ system variables', () => {
diff --git a/test/n1ql.test.ts b/test/n1ql.test.ts
index f7cf143a..5f635553 100644
--- a/test/n1ql.test.ts
+++ b/test/n1ql.test.ts
@@ -18,6 +18,7 @@ import supportsIdentifiers from './features/identifiers';
 import supportsParams from './options/param';
 import supportsWindow from './features/window';
 import supportsSetOperations from './features/setOperations';
+import supportsLimiting from './features/limiting';
 
 describe('N1qlFormatter', () => {
   const language = 'n1ql';
@@ -45,6 +46,7 @@ describe('N1qlFormatter', () => {
   supportsReturning(format);
   supportsParams(format, { positional: true, numbered: ['$'], named: ['$'] });
   supportsWindow(format);
+  supportsLimiting(format, { limit: true, offset: true });
 
   it('formats INSERT with {} object literal', () => {
     const result = format(
diff --git a/test/options/keywordCase.ts b/test/options/keywordCase.ts
index 12b61ba2..b3a7bd51 100644
--- a/test/options/keywordCase.ts
+++ b/test/options/keywordCase.ts
@@ -6,8 +6,8 @@ export default function supportsKeywordCase(format: FormatFn) {
   it('preserves keyword case by default', () => {
     const result = format('select distinct * frOM foo left JOIN bar WHERe cola > 1 and colb = 3');
     expect(result).toBe(dedent`
-      select
-        distinct *
+      select distinct
+        *
       frOM
         foo
         left JOIN bar
@@ -25,8 +25,8 @@ export default function supportsKeywordCase(format: FormatFn) {
       }
     );
     expect(result).toBe(dedent`
-      SELECT
-        DISTINCT *
+      SELECT DISTINCT
+        *
       FROM
         foo
         LEFT JOIN mycol
@@ -41,8 +41,8 @@ export default function supportsKeywordCase(format: FormatFn) {
       keywordCase: 'lower',
     });
     expect(result).toBe(dedent`
-      select
-        distinct *
+      select distinct
+        *
       from
         foo
         left join bar
diff --git a/test/plsql.test.ts b/test/plsql.test.ts
index 03609413..ad6eb342 100644
--- a/test/plsql.test.ts
+++ b/test/plsql.test.ts
@@ -19,6 +19,7 @@ import supportsComments from './features/comments';
 import supportsIdentifiers from './features/identifiers';
 import supportsParams from './options/param';
 import supportsSetOperations from './features/setOperations';
+import supportsLimiting from './features/limiting';
 
 describe('PlSqlFormatter', () => {
   const language = 'plsql';
@@ -40,19 +41,7 @@ describe('PlSqlFormatter', () => {
   supportsSetOperations(format, ['UNION', 'UNION ALL', 'EXCEPT', 'INTERSECT']);
   supportsReturning(format);
   supportsParams(format, { numbered: [':'], named: [':'] });
-
-  it('formats FETCH FIRST like LIMIT', () => {
-    expect(format('SELECT col1 FROM tbl ORDER BY col2 DESC FETCH FIRST 20 ROWS ONLY;')).toBe(dedent`
-      SELECT
-        col1
-      FROM
-        tbl
-      ORDER BY
-        col2 DESC
-      FETCH FIRST
-        20 ROWS ONLY;
-    `);
-  });
+  supportsLimiting(format, { offset: true, fetchFirst: true, fetchNext: true });
 
   it('recognizes _, $, # as part of identifiers', () => {
     const result = format('SELECT my_col$1#, col.a$, type#, procedure$, user# FROM tbl;');
@@ -126,127 +115,23 @@ describe('PlSqlFormatter', () => {
 
   it('formats Oracle recursive sub queries', () => {
     const result = format(`
-      WITH t1(id, parent_id) AS (
-        -- Anchor member.
-        SELECT
-          id,
-          parent_id
-        FROM
-          tab1
-        WHERE
-          parent_id IS NULL
-        UNION
-          -- Recursive member.
-        SELECT
-          t2.id,
-          t2.parent_id
-        FROM
-          tab1 t2,
-          t1
-        WHERE
-          t2.parent_id = t1.id
-      ) SEARCH BREADTH FIRST BY id SET order1,
-      another AS (SELECT * FROM dual)
-      SELECT id, parent_id FROM t1 ORDER BY order1;
+      WITH t1 AS (
+        SELECT * FROM tbl
+      ) SEARCH BREADTH FIRST BY id SET order1
+      SELECT * FROM t1;
     `);
     expect(result).toBe(dedent`
       WITH
-        t1 (id, parent_id) AS (
-          -- Anchor member.
-          SELECT
-            id,
-            parent_id
-          FROM
-            tab1
-          WHERE
-            parent_id IS NULL
-          UNION
-          -- Recursive member.
-          SELECT
-            t2.id,
-            t2.parent_id
-          FROM
-            tab1 t2,
-            t1
-          WHERE
-            t2.parent_id = t1.id
-        ) SEARCH BREADTH FIRST BY id SET order1,
-        another AS (
-          SELECT
-            *
-          FROM
-            dual
-        )
-      SELECT
-        id,
-        parent_id
-      FROM
-        t1
-      ORDER BY
-        order1;
-    `);
-  });
-
-  it('formats Oracle recursive sub queries regardless of capitalization', () => {
-    const result = format(/* sql */ `
-      WITH t1(id, parent_id) AS (
-        -- Anchor member.
-        SELECT
-          id,
-          parent_id
-        FROM
-          tab1
-        WHERE
-          parent_id IS NULL
-        UNION
-          -- Recursive member.
-        SELECT
-          t2.id,
-          t2.parent_id
-        FROM
-          tab1 t2,
-          t1
-        WHERE
-          t2.parent_id = t1.id
-      ) SEARCH BREADTH FIRST by id set order1,
-      another AS (SELECT * FROM dual)
-      SELECT id, parent_id FROM t1 ORDER BY order1;
-    `);
-    expect(result).toBe(dedent/* sql */ `
-      WITH
-        t1 (id, parent_id) AS (
-          -- Anchor member.
-          SELECT
-            id,
-            parent_id
-          FROM
-            tab1
-          WHERE
-            parent_id IS NULL
-          UNION
-          -- Recursive member.
-          SELECT
-            t2.id,
-            t2.parent_id
-          FROM
-            tab1 t2,
-            t1
-          WHERE
-            t2.parent_id = t1.id
-        ) SEARCH BREADTH FIRST by id set order1,
-        another AS (
+        t1 AS (
           SELECT
             *
           FROM
-            dual
-        )
+            tbl
+        ) SEARCH BREADTH FIRST BY id SET order1
       SELECT
-        id,
-        parent_id
+        *
       FROM
-        t1
-      ORDER BY
-        order1;
+        t1;
     `);
   });
 
diff --git a/test/postgresql.test.ts b/test/postgresql.test.ts
index 28207d6f..555daeb0 100644
--- a/test/postgresql.test.ts
+++ b/test/postgresql.test.ts
@@ -20,6 +20,7 @@ import supportsParams from './options/param';
 import supportsArrayAndMapAccessors from './features/arrayAndMapAccessors';
 import supportsWindow from './features/window';
 import supportsSetOperations from './features/setOperations';
+import supportsLimiting from './features/limiting';
 
 describe('PostgreSqlFormatter', () => {
   const language = 'postgresql';
@@ -45,6 +46,7 @@ describe('PostgreSqlFormatter', () => {
   supportsReturning(format);
   supportsParams(format, { numbered: ['$'] });
   supportsWindow(format);
+  supportsLimiting(format, { limit: true, offset: true, fetchFirst: true, fetchNext: true });
 
   it('allows $ character as part of identifiers', () => {
     expect(format('SELECT foo$, some$$ident')).toBe(dedent`
@@ -95,4 +97,16 @@ describe('PostgreSqlFormatter', () => {
         ARRAY[1, 2, 3]
     `);
   });
+
+  // issue #144 (unsolved)
+  // This is currently far from ideal.
+  it('formats SELECT DISTINCT ON () syntax', () => {
+    expect(format('SELECT DISTINCT ON (c1, c2) c1, c2 FROM tbl;')).toBe(dedent`
+      SELECT DISTINCT
+        ON (c1, c2) c1,
+        c2
+      FROM
+        tbl;
+    `);
+  });
 });
diff --git a/test/redshift.test.ts b/test/redshift.test.ts
index 6b8290fe..52d0a312 100644
--- a/test/redshift.test.ts
+++ b/test/redshift.test.ts
@@ -16,6 +16,7 @@ import supportsComments from './features/comments';
 import supportsIdentifiers from './features/identifiers';
 import supportsParams from './options/param';
 import supportsSetOperations from './features/setOperations';
+import supportsLimiting from './features/limiting';
 
 describe('RedshiftFormatter', () => {
   const language = 'redshift';
@@ -34,6 +35,7 @@ describe('RedshiftFormatter', () => {
   supportsJoin(format);
   supportsSetOperations(format, ['UNION', 'UNION ALL', 'EXCEPT', 'INTERSECT', 'MINUS']);
   supportsParams(format, { numbered: ['$'] });
+  supportsLimiting(format, { limit: true, offset: true });
 
   it('formats LIMIT', () => {
     expect(format('SELECT col1 FROM tbl ORDER BY col2 DESC LIMIT 10;')).toBe(dedent`
diff --git a/test/spark.test.ts b/test/spark.test.ts
index 39c4ae04..a6edd7db 100644
--- a/test/spark.test.ts
+++ b/test/spark.test.ts
@@ -15,6 +15,7 @@ import supportsArrayAndMapAccessors from './features/arrayAndMapAccessors';
 import supportsComments from './features/comments';
 import supportsIdentifiers from './features/identifiers';
 import supportsSetOperations from './features/setOperations';
+import supportsLimiting from './features/limiting';
 
 describe('SparkFormatter', () => {
   const language = 'spark';
@@ -45,6 +46,7 @@ describe('SparkFormatter', () => {
     ],
   });
   supportsSetOperations(format);
+  supportsLimiting(format, { limit: true });
 
   it('formats basic WINDOW clause', () => {
     const result = format(`SELECT * FROM tbl WINDOW win1, WINDOW win2, WINDOW win3;`);
@@ -97,4 +99,22 @@ describe('SparkFormatter', () => {
         name = '\${name}';
     `);
   });
+
+  it('supports SORT BY, CLUSTER BY, DISTRIBUTE BY', () => {
+    const result = format(
+      'SELECT value, count DISTRIBUTE BY count CLUSTER BY value SORT BY value, count;'
+    );
+    expect(result).toBe(dedent`
+      SELECT
+        value,
+        count
+      DISTRIBUTE BY
+        count
+      CLUSTER BY
+        value
+      SORT BY
+        value,
+        count;
+    `);
+  });
 });
diff --git a/test/sql.test.ts b/test/sql.test.ts
index c943695b..dd3282c0 100644
--- a/test/sql.test.ts
+++ b/test/sql.test.ts
@@ -18,6 +18,7 @@ import supportsIdentifiers from './features/identifiers';
 import supportsParams from './options/param';
 import supportsWindow from './features/window';
 import supportsSetOperations from './features/setOperations';
+import supportsLimiting from './features/limiting';
 
 describe('SqlFormatter', () => {
   const language = 'sql';
@@ -38,16 +39,7 @@ describe('SqlFormatter', () => {
   supportsOperators(format, SqlFormatter.operators);
   supportsParams(format, { positional: true });
   supportsWindow(format);
-
-  it('formats FETCH FIRST like LIMIT', () => {
-    const result = format('SELECT * FETCH FIRST 2 ROWS ONLY;');
-    expect(result).toBe(dedent`
-      SELECT
-        *
-      FETCH FIRST
-        2 ROWS ONLY;
-    `);
-  });
+  supportsLimiting(format, { limit: true, offset: true, fetchFirst: true, fetchNext: true });
 
   // This is a crappy behavior, but at least we don't crash
   it('does not crash when encountering characters or operators it does not recognize', () => {
diff --git a/test/sqlite.test.ts b/test/sqlite.test.ts
index 38df20ca..e57e83f9 100644
--- a/test/sqlite.test.ts
+++ b/test/sqlite.test.ts
@@ -1,5 +1,3 @@
-import dedent from 'dedent-js';
-
 import { format as originalFormat, FormatFn } from 'src/sqlFormatter';
 import SqliteFormatter from 'src/languages/sqlite/sqlite.formatter';
 import behavesLikeSqlFormatter from './behavesLikeSqlFormatter';
@@ -18,6 +16,7 @@ import supportsIdentifiers from './features/identifiers';
 import supportsParams from './options/param';
 import supportsWindow from './features/window';
 import supportsSetOperations from './features/setOperations';
+import supportsLimiting from './features/limiting';
 
 describe('SqliteFormatter', () => {
   const language = 'sqlite';
@@ -38,14 +37,5 @@ describe('SqliteFormatter', () => {
   supportsOperators(format, SqliteFormatter.operators);
   supportsParams(format, { positional: true, numbered: ['?'], named: [':', '$', '@'] });
   supportsWindow(format);
-
-  it('formats FETCH FIRST like LIMIT', () => {
-    const result = format('SELECT * FETCH FIRST 2 ROWS ONLY;');
-    expect(result).toBe(dedent`
-      SELECT
-        *
-      FETCH FIRST
-        2 ROWS ONLY;
-    `);
-  });
+  supportsLimiting(format, { limit: true, offset: true });
 });
diff --git a/test/trino.test.ts b/test/trino.test.ts
index 22a8833c..3fb41f1f 100644
--- a/test/trino.test.ts
+++ b/test/trino.test.ts
@@ -16,6 +16,8 @@ import supportsComments from './features/comments';
 import supportsIdentifiers from './features/identifiers';
 import supportsParams from './options/param';
 import supportsSetOperations from './features/setOperations';
+import supportsWindow from './features/window';
+import supportsLimiting from './features/limiting';
 
 describe('TrinoFormatter', () => {
   const language = 'trino';
@@ -34,6 +36,8 @@ describe('TrinoFormatter', () => {
   supportsJoin(format);
   supportsSetOperations(format);
   supportsParams(format, { positional: true });
+  supportsWindow(format);
+  supportsLimiting(format, { limit: true, offset: true, fetchFirst: true, fetchNext: true });
 
   it('formats SET SESSION', () => {
     const result = format('SET SESSION foo = 444;');
diff --git a/test/tsql.test.ts b/test/tsql.test.ts
index b9697596..2d6ba292 100644
--- a/test/tsql.test.ts
+++ b/test/tsql.test.ts
@@ -18,6 +18,7 @@ import supportsIdentifiers from './features/identifiers';
 import supportsParams from './options/param';
 import supportsWindow from './features/window';
 import supportsSetOperations from './features/setOperations';
+import supportsLimiting from './features/limiting';
 
 describe('TSqlFormatter', () => {
   const language = 'tsql';
@@ -41,6 +42,7 @@ describe('TSqlFormatter', () => {
   supportsSetOperations(format, ['UNION', 'UNION ALL', 'EXCEPT', 'INTERSECT']);
   supportsParams(format, { named: ['@'], quoted: ['@""', '@[]'] });
   supportsWindow(format);
+  supportsLimiting(format, { offset: true, fetchFirst: true, fetchNext: true });
 
   // TODO: The following are duplicated from StandardSQLFormatter test