Support the latest BigQuery syntax

[nene]

• Review BigQuery changelog and implement the new bits of syntax that we don't support yet.
• Also note down the BigQuery version (release date) which we support. Currently it's sort of unknown.
• The original BigQuery support was implemented between 15 Nov 2022 and 19 Dec 2022.

  • 2

  • 6

See also: https://github.com/nene/prettier-plugin-sql-cst/issues/22

List of changes to check:

• [x] In a session, statements that include the TEMP keyword can also include the OR REPLACEand IF NOT EXISTS keywords.
• [x] There's a whole separate BigQuery ML. Not sure if we should support it at all. Not supporting. At least not before somebody actualy asks for a support.
• [x] The HAVING MAX and HAVING MIN clause for the ANY_VALUE function
• [x] The ALTER TABLE RENAME COLUMN statement and the ALTER TABLE DROP COLUMN statement
• [x] Primary and foreign key table constraints.
  • [x] CREATE TABLE statement
  • [x] ALTER TABLE ADD PRIMARY KEY statement
  • [x] ALTER TABLE ADD FOREIGN KEY statement.
    • [x] IF NOT EXISTS
  • [x] ALTER TABLE DROP CONSTRAINT
  • [x] ALTER TABLE DROP PRIMARY KEY
• [x] You can set default values on columns in your BigQuery tables.
• [x] The WITH RECURSIVE clause.
• [x] MIN, MAX, COUNT with DISTINCT, and PERCENTILE_DISC windows functions
• [x] ORDER BY and PARTITION BY in the WINDOWS clause
• [x] IS [NOT] LIKE operator (documentation bug. It's the plain old LIKE operator).
• [x] Views
• [x] Materialized views with limitations
• [x] CREATE MATERIALIZED VIEW AS REPLICA OF
• [x] Table functions with limitations
• [x] The CREATE TABLE AS SELECT statement
• [x] Unicode column naming. Looks like unicode in unquoted column names is not supported.
• [x] descriptions for the columns of a view:
  • [x] CREATE VIEW (col_name OPTIONS(), ...)
  • [x] ALTER COLUMN SET OPTIONS
• [x] CREATE TABLE CLONE
• [x] You can now GRANT or REVOKE access to materialized views with a SQL statement.
• [x] Quantitive LIKE operator
• [x] array subscript operator
• [x] struct subscript operator
• [x] Queries now support additional ways to work with grouping sets, which include:
  • [x] GROUP BY GROUPING SETS clause
  • [x] GROUP BY CUBE clause
  • [x] GROUP BY ROLLUP clause
  • [x] GROUPING function
• [x] CREATE VECTOR INDEX
• [x] VECTOR_SEARCH() function
• [x] Latest release checked: January 31, 2024