Sqlfluff

Highlights

This is primarily a _bugfix_ and _dialect_ release:
* Several bugfixes related to templating and indentation.
* Configurable indentation before `THEN` in `CASE` statements
(see [4598](https://github.com/sqlfluff/sqlfluff/pull/4598)).
* Performance improvements to `TypedParser`, `LT03` & `LT04`.
* Rule timings now appear in the `--persist-timing` option for deeper
performance understanding.
* The introduction of a Greenplum dialect.
* Dialect improvements to TSQL, Athena, Snowflake, MySQL, SparkSQL
BigQuery, Databricks, Clickhouse & Postgres.

We also saw a _huge number of first time contributors_ with **9** contributing
in this release 🎉🏆🎉.

What’s Changed

* Better error message for missing keywords [4676](https://github.com/sqlfluff/sqlfluff/pull/4676) [tunetheweb](https://github.com/tunetheweb)
* Add performance shortcuts to LT03 & LT04 [4672](https://github.com/sqlfluff/sqlfluff/pull/4672) [alanmcruickshank](https://github.com/alanmcruickshank)
* Clickhouse: Add support for [LEFT] ARRAY JOIN [4618](https://github.com/sqlfluff/sqlfluff/pull/4618) [simpl1g](https://github.com/simpl1g)
* Postgres - allow untyped OVERLAPS clauses [4674](https://github.com/sqlfluff/sqlfluff/pull/4674) [tunetheweb](https://github.com/tunetheweb)
* Mark `is_alias_required` as a private class so it doesn't appear in docs [4673](https://github.com/sqlfluff/sqlfluff/pull/4673) [tunetheweb](https://github.com/tunetheweb)
* Fix bug in templated with clauses LT07 [4671](https://github.com/sqlfluff/sqlfluff/pull/4671) [alanmcruickshank](https://github.com/alanmcruickshank)
* TSQL: `OPENJSON()` [4652](https://github.com/sqlfluff/sqlfluff/pull/4652) [keen85](https://github.com/keen85)
* fix(RF06/L059): allows configuring prefer_quoted_keywords to deconflict with L029 [4396](https://github.com/sqlfluff/sqlfluff/pull/4396) [timcosta](https://github.com/timcosta)
* TSQL: `Create External Table` [4642](https://github.com/sqlfluff/sqlfluff/pull/4642) [aly76](https://github.com/aly76)
* Consistent indentation in `MERGE` `INSERT` clause [4666](https://github.com/sqlfluff/sqlfluff/pull/4666) [dmohns](https://github.com/dmohns)
* BigQuery: Fix null assignment in options segment [4669](https://github.com/sqlfluff/sqlfluff/pull/4669) [greg-finley](https://github.com/greg-finley)
* BigQuery: Delete table reference [4668](https://github.com/sqlfluff/sqlfluff/pull/4668) [greg-finley](https://github.com/greg-finley)
* TSQL: `CREATE EXTERNAL FILE FORMAT` [4647](https://github.com/sqlfluff/sqlfluff/pull/4647) [keen85](https://github.com/keen85)
* Remove TIME as reserved keyword in SparkSQL [4662](https://github.com/sqlfluff/sqlfluff/pull/4662) [bmorck](https://github.com/bmorck)
* Start of the Greenplum dialect implementation [4661](https://github.com/sqlfluff/sqlfluff/pull/4661) [JackWolverson](https://github.com/JackWolverson)
* Enable configuring whether to require indent before THEN [4598](https://github.com/sqlfluff/sqlfluff/pull/4598) [fredriv](https://github.com/fredriv)
* Sequence Meta Handling [4622](https://github.com/sqlfluff/sqlfluff/pull/4622) [alanmcruickshank](https://github.com/alanmcruickshank)
* Add support for non-quoted file paths in SparkSQL [4650](https://github.com/sqlfluff/sqlfluff/pull/4650) [bmorck](https://github.com/bmorck)
* Remove three RegexParsers [4658](https://github.com/sqlfluff/sqlfluff/pull/4658) [alanmcruickshank](https://github.com/alanmcruickshank)
* Make parse test readout more helpful [4657](https://github.com/sqlfluff/sqlfluff/pull/4657) [alanmcruickshank](https://github.com/alanmcruickshank)
* TSQL: support for `sqlcmd` commands `:r` and `:setvar` [4653](https://github.com/sqlfluff/sqlfluff/pull/4653) [keen85](https://github.com/keen85)
* Update README with Databricks note [4632](https://github.com/sqlfluff/sqlfluff/pull/4632) [liamperritt](https://github.com/liamperritt)
* Athena: Fix parsing error with aliases starting with underscore [4636](https://github.com/sqlfluff/sqlfluff/pull/4636) [maiarareinaldo](https://github.com/maiarareinaldo)
* Snowflake: Stop ever-increasing indent in CREATE USER [4638](https://github.com/sqlfluff/sqlfluff/pull/4638) [roman-ef](https://github.com/roman-ef)
* TSQL: `PERIOD FOR SYSTEM_TIME` (temporal tables) [4654](https://github.com/sqlfluff/sqlfluff/pull/4654) [keen85](https://github.com/keen85)
* MySQL: SelectStatementSegment in CREATE/ALTER VIEW may be bracketed [4655](https://github.com/sqlfluff/sqlfluff/pull/4655) [yoichi](https://github.com/yoichi)
* TSQL: `CREATE EXTERNAL DATA SOURCE` [4634](https://github.com/sqlfluff/sqlfluff/pull/4634) [keen85](https://github.com/keen85)
* Safety valve on source fixes [4640](https://github.com/sqlfluff/sqlfluff/pull/4640) [alanmcruickshank](https://github.com/alanmcruickshank)
* Add SparkSQL support for LONG primitive type [4639](https://github.com/sqlfluff/sqlfluff/pull/4639) [bmorck](https://github.com/bmorck)
* Fix PIVOT clauses for BigQuery and SparkSQL [4630](https://github.com/sqlfluff/sqlfluff/pull/4630) [tunetheweb](https://github.com/tunetheweb)
* Correct BigQuery WINDOW parsing [4629](https://github.com/sqlfluff/sqlfluff/pull/4629) [tunetheweb](https://github.com/tunetheweb)
* Add Databricks dialect support for Unity Catalog [4568](https://github.com/sqlfluff/sqlfluff/pull/4568) [liamperritt](https://github.com/liamperritt)
* .simple() matching for TypedMatcher [4612](https://github.com/sqlfluff/sqlfluff/pull/4612) [alanmcruickshank](https://github.com/alanmcruickshank)
* --bench output with rule timings [4601](https://github.com/sqlfluff/sqlfluff/pull/4601) [alanmcruickshank](https://github.com/alanmcruickshank)
* MySQL: Unnamed constraints [4616](https://github.com/sqlfluff/sqlfluff/pull/4616) [greg-finley](https://github.com/greg-finley)
* TSQL: Create database scoped credential [4615](https://github.com/sqlfluff/sqlfluff/pull/4615) [greg-finley](https://github.com/greg-finley)
* fix(dialect-clickhouse): Add materialized view statement [4605](https://github.com/sqlfluff/sqlfluff/pull/4605) [germainlefebvre4](https://github.com/germainlefebvre4)
* Nicer formatted dbt errors [4606](https://github.com/sqlfluff/sqlfluff/pull/4606) [alanmcruickshank](https://github.com/alanmcruickshank)
* add parse lambda function Clickhouse [4611](https://github.com/sqlfluff/sqlfluff/pull/4611) [konnectr](https://github.com/konnectr)
* Support `WITH ORDINALITY` clauses in Postgres [4599](https://github.com/sqlfluff/sqlfluff/pull/4599) [tunetheweb](https://github.com/tunetheweb)


New Contributors
* [germainlefebvre4](https://github.com/germainlefebvre4) made their first contribution in [#4605](https://github.com/sqlfluff/sqlfluff/pull/4605)
* [liamperritt](https://github.com/liamperritt) made their first contribution in [#4568](https://github.com/sqlfluff/sqlfluff/pull/4568)
* [bmorck](https://github.com/bmorck) made their first contribution in [#4639](https://github.com/sqlfluff/sqlfluff/pull/4639)
* [keen85](https://github.com/keen85) made their first contribution in [#4634](https://github.com/sqlfluff/sqlfluff/pull/4634)
* [roman-ef](https://github.com/roman-ef) made their first contribution in [#4638](https://github.com/sqlfluff/sqlfluff/pull/4638)
* [maiarareinaldo](https://github.com/maiarareinaldo) made their first contribution in [#4636](https://github.com/sqlfluff/sqlfluff/pull/4636)
* [fredriv](https://github.com/fredriv) made their first contribution in [#4598](https://github.com/sqlfluff/sqlfluff/pull/4598)
* [aly76](https://github.com/aly76) made their first contribution in [#4642](https://github.com/sqlfluff/sqlfluff/pull/4642)
* [simpl1g](https://github.com/simpl1g) made their first contribution in [#4618](https://github.com/sqlfluff/sqlfluff/pull/4618)

Highlights

This is primarily a _bugfix_ release. Most notably this solves some of the
issues introduced in 2.0.1 around spacing within datatypes. Expressions
like `1.0::double precision` should now be spaced correctly.

Beyond that, this contains a selection of smaller bugfixes and dialect
improvements. Even for a relatively small release we saw three new
contributors (thanks [aurany](https://github.com/aurany), [JackWolverson](https://github.com/JackWolverson)
& [mikaeltw](https://github.com/mikaeltw) 🎉).

The one new _feature_ (as such) is being able to now configure `LT05`
(aka `layout.long_lines`) to optionally move trailing comments _after_
the line they are found on, rather than the default behaviour of moving
them up and _before_. Users can enable this with the `trailing_comments`
configuration setting in the `indentation` section.

This release _also_ contains some performance optimisations in the parser,
especially on queries with heavily nested expressions. There will be more
to come in this space, but we hope this leads to a better experience for
many users. 🚀

What’s Changed

* Parse Caching [4576](https://github.com/sqlfluff/sqlfluff/pull/4576) [alanmcruickshank](https://github.com/alanmcruickshank)
* Data type spacing [4592](https://github.com/sqlfluff/sqlfluff/pull/4592) [alanmcruickshank](https://github.com/alanmcruickshank)
* MySQL: allow quoted literal in alias name [4591](https://github.com/sqlfluff/sqlfluff/pull/4591) [yoichi](https://github.com/yoichi)
* Make implicit indents visible in the parse tree [4584](https://github.com/sqlfluff/sqlfluff/pull/4584) [alanmcruickshank](https://github.com/alanmcruickshank)
* Fix 4559: TSQL implicit indents on WHERE [4583](https://github.com/sqlfluff/sqlfluff/pull/4583) [alanmcruickshank](https://github.com/alanmcruickshank)
* Added keywords to DB2 dialect from IBM docs [4575](https://github.com/sqlfluff/sqlfluff/pull/4575) [aurany](https://github.com/aurany)
* Remove matches_target_tuples (3873) [4561](https://github.com/sqlfluff/sqlfluff/pull/4561) [alanmcruickshank](https://github.com/alanmcruickshank)
* Use terminators in BaseExpression [4577](https://github.com/sqlfluff/sqlfluff/pull/4577) [alanmcruickshank](https://github.com/alanmcruickshank)
* Address 1630: Optionally move comments after long line [4558](https://github.com/sqlfluff/sqlfluff/pull/4558) [alanmcruickshank](https://github.com/alanmcruickshank)
* Added schema to set statement [4580](https://github.com/sqlfluff/sqlfluff/pull/4580) [JackWolverson](https://github.com/JackWolverson)
* Refactor lint_line_length and fix comma bug [4564](https://github.com/sqlfluff/sqlfluff/pull/4564) [alanmcruickshank](https://github.com/alanmcruickshank)
* Fix untaken indent bug [4562](https://github.com/sqlfluff/sqlfluff/pull/4562) [alanmcruickshank](https://github.com/alanmcruickshank)
* SQLite: Fix SELECT LIMIT [4566](https://github.com/sqlfluff/sqlfluff/pull/4566) [greg-finley](https://github.com/greg-finley)
* Fix 4453: Snowflake semi-stuctured casts in CV11 [4571](https://github.com/sqlfluff/sqlfluff/pull/4571) [alanmcruickshank](https://github.com/alanmcruickshank)
* Name of LT07 [4557](https://github.com/sqlfluff/sqlfluff/pull/4557) [alanmcruickshank](https://github.com/alanmcruickshank)
* Patch fetch and over [4555](https://github.com/sqlfluff/sqlfluff/pull/4555) [mikaeltw](https://github.com/mikaeltw)

New Contributors

* [mikaeltw](https://github.com/mikaeltw) made their first contribution in [#4555](https://github.com/sqlfluff/sqlfluff/pull/4555)
* [JackWolverson](https://github.com/JackWolverson) made their first contribution in [#4580](https://github.com/sqlfluff/sqlfluff/pull/4580)
* [aurany](https://github.com/aurany) made their first contribution in [#4575](https://github.com/sqlfluff/sqlfluff/pull/4575)

- Add a `sqlfluff.environmentVariables` configuration setting to allow users to set their own environment variables.
- Improve the `lintEntireProject` setup to limit the amount of files that are linted at any one time to 5 files.

- Spacing for (as applied by `LT01`) for datatypes, hyphenated identifiers and
casting operators.
- Several bugs in the indentation routines (`LT02`), in particular with implicit
indents.
- Fixing a conflict between `LT09` and `LT02`, by only limiting `LT09` to bringing
targets onto a single line if there is only one select target **and** that it
contains no newlines.
- Supporting arrays, and the new rules configuration more effectively in `pyproject.toml`.
- Configuring dialects on a file by file basis using inline comments now works.

This release also brings one small new feature in allowing additional flags to
be passed to SQLFluff when called as a `pre-commit` hook.

Thanks especially to [JavierMonton](https://github.com/JavierMonton) and
[LauraRichter](https://github.com/LauraRichter) who made their first contributions
to the project as part of this release! 🎉🏆

What’s Changed

* Add support for arrays in TOML configuration [4387](https://github.com/sqlfluff/sqlfluff/pull/4387) [AidanHarveyNelson](https://github.com/AidanHarveyNelson)
* Rethink test segregation in CI [4547](https://github.com/sqlfluff/sqlfluff/pull/4547) [alanmcruickshank](https://github.com/alanmcruickshank)
* Fix 4515 and add more test cases [4525](https://github.com/sqlfluff/sqlfluff/pull/4525) [alanmcruickshank](https://github.com/alanmcruickshank)
* Add additional flags to `sqlfluff` invocations in pre-commit hooks [4546](https://github.com/sqlfluff/sqlfluff/pull/4546) [borchero](https://github.com/borchero)
* Resolve 4484 (issues with indented_joins indents) [4544](https://github.com/sqlfluff/sqlfluff/pull/4544) [alanmcruickshank](https://github.com/alanmcruickshank)
* Per file dialect selection fix [4518](https://github.com/sqlfluff/sqlfluff/pull/4518) [LauraRichter](https://github.com/LauraRichter)
* MySQL: Add CREATE INDEX [4538](https://github.com/sqlfluff/sqlfluff/pull/4538) [yoichi](https://github.com/yoichi)
* Resolve implicit indent issues when catching negative indents [4543](https://github.com/sqlfluff/sqlfluff/pull/4543) [alanmcruickshank](https://github.com/alanmcruickshank)
* Github Action Deprecations [4545](https://github.com/sqlfluff/sqlfluff/pull/4545) [alanmcruickshank](https://github.com/alanmcruickshank)
* LT09 and multiline select targets [4529](https://github.com/sqlfluff/sqlfluff/pull/4529) [alanmcruickshank](https://github.com/alanmcruickshank)
* Remove Codecov from CI [4535](https://github.com/sqlfluff/sqlfluff/pull/4535) [alanmcruickshank](https://github.com/alanmcruickshank)
* Bigquery hyphentated identifiers [4530](https://github.com/sqlfluff/sqlfluff/pull/4530) [alanmcruickshank](https://github.com/alanmcruickshank)
* Attempt in-house coverage [4532](https://github.com/sqlfluff/sqlfluff/pull/4532) [alanmcruickshank](https://github.com/alanmcruickshank)
* Postgres datatype spacing issues [4528](https://github.com/sqlfluff/sqlfluff/pull/4528) [alanmcruickshank](https://github.com/alanmcruickshank)
* Support new rules config in toml files. [4526](https://github.com/sqlfluff/sqlfluff/pull/4526) [alanmcruickshank](https://github.com/alanmcruickshank)
* Resolve 1146 (log propagation) [4513](https://github.com/sqlfluff/sqlfluff/pull/4513) [alanmcruickshank](https://github.com/alanmcruickshank)
* Snowflake: Optional quotes for `create user` statement [4514](https://github.com/sqlfluff/sqlfluff/pull/4514) [JavierMonton](https://github.com/JavierMonton)

New Contributors

* [JavierMonton](https://github.com/JavierMonton) made their first contribution in [#4514](https://github.com/sqlfluff/sqlfluff/pull/4514)
* [LauraRichter](https://github.com/LauraRichter) made their first contribution in [#4518](https://github.com/sqlfluff/sqlfluff/pull/4518)

- Allow the extension to work while not in a workspace.
- Improve the `View Documentation` hover to more often show when any part of the word is hovered over.

> NOTE: This is effectively a release candidate for testing purposes.
> There are several new features here, and breaking changes to
> configuration. We welcome testing feedback from the community, and
> the intent is that following this release there will be no more
> major breaking changes in the before the 2.0.0 release.

Highlights

This is the sixth alpha release for 2.0.0, and effectively the first release
candidate for 2.0.0. All the intended breaking changes for the upcoming
release have now been made and only bugfixes and non breaking feature
changes should happen between this release and the full release.

It contains:
* A reorganisation of rules. All rules have been recoded, and can now be
referred to by their name, code, alias or group. The legacy code for the
rule is included as an alias for each rule to support some backward
compatibility.
* Configuration files (and inline configuration flags), should now use the
**name** of the rule rather than the **code**. Any configuration files
which reference using legacy rules (or reference unknown rules) should
now display warnings.
* Introduces the the `sqlfluff format` CLI command (a la `sqlfmt` or `black`)
to auto-format sql files using a known set of _fairly safe_ rules.
* Databricks as a distinct new dialect (rather than as previously an alias
for `sparksql`).

There are also numerous dialect improvements to ANSI, Athena, TSQL, Teradata,
SQLite & MySQL.

What’s Changed

* Fix 4367 [4479](https://github.com/sqlfluff/sqlfluff/pull/4479) [alanmcruickshank](https://github.com/alanmcruickshank)
* Teradata: Improve COLLECT STATS parsing [4478](https://github.com/sqlfluff/sqlfluff/pull/4478) [dflem97](https://github.com/dflem97)
* Add a sqlfluff format CLI command [4473](https://github.com/sqlfluff/sqlfluff/pull/4473) [alanmcruickshank](https://github.com/alanmcruickshank)
* Recode and disable L031 -> AL07 [4471](https://github.com/sqlfluff/sqlfluff/pull/4471) [alanmcruickshank](https://github.com/alanmcruickshank)
* Named Config (part 2) [4470](https://github.com/sqlfluff/sqlfluff/pull/4470) [alanmcruickshank](https://github.com/alanmcruickshank)
* Rule config lookup improvements & config warnings [4465](https://github.com/sqlfluff/sqlfluff/pull/4465) [alanmcruickshank](https://github.com/alanmcruickshank)
* Recode L050 [4468](https://github.com/sqlfluff/sqlfluff/pull/4468) [alanmcruickshank](https://github.com/alanmcruickshank)
* Implicit indent fixes 4467 [4469](https://github.com/sqlfluff/sqlfluff/pull/4469) [alanmcruickshank](https://github.com/alanmcruickshank)
* ANSI: Add IfExistsGrammar to DropTrigger [4466](https://github.com/sqlfluff/sqlfluff/pull/4466) [WittierDinosaur](https://github.com/WittierDinosaur)
* Rules Reorg Mopup [4462](https://github.com/sqlfluff/sqlfluff/pull/4462) [alanmcruickshank](https://github.com/alanmcruickshank)
* Layout Rules Recode (part 2) [4456](https://github.com/sqlfluff/sqlfluff/pull/4456) [alanmcruickshank](https://github.com/alanmcruickshank)
* fix(athena): resolve errors parsing around maps, structs, and arrays [4391](https://github.com/sqlfluff/sqlfluff/pull/4391) [timcosta](https://github.com/timcosta)
* Layout Rules Recode (part 1) [4432](https://github.com/sqlfluff/sqlfluff/pull/4432) [alanmcruickshank](https://github.com/alanmcruickshank)
* TSQL: EXEC string literal [4458](https://github.com/sqlfluff/sqlfluff/pull/4458) [jpers36](https://github.com/jpers36)
* Teradata: Added SET QUERY_BAND statement [4459](https://github.com/sqlfluff/sqlfluff/pull/4459) [dflem97](https://github.com/dflem97)
* Teradata: Added TOP select clause modifier [4461](https://github.com/sqlfluff/sqlfluff/pull/4461) [dflem97](https://github.com/dflem97)
* Teradata: Addition of comparison operator extensions [4451](https://github.com/sqlfluff/sqlfluff/pull/4451) [dflem97](https://github.com/dflem97)
* Add extensions and plugin section to the README.md [4454](https://github.com/sqlfluff/sqlfluff/pull/4454) [jared-rimmer](https://github.com/jared-rimmer)
* Convention rules bundle [4448](https://github.com/sqlfluff/sqlfluff/pull/4448) [alanmcruickshank](https://github.com/alanmcruickshank)
* References rule bundle [4446](https://github.com/sqlfluff/sqlfluff/pull/4446) [alanmcruickshank](https://github.com/alanmcruickshank)
* Structure and Ambiguous rule bundles [4444](https://github.com/sqlfluff/sqlfluff/pull/4444) [alanmcruickshank](https://github.com/alanmcruickshank)
* TSQL: Bare functions [4439](https://github.com/sqlfluff/sqlfluff/pull/4439) [jpers36](https://github.com/jpers36)
* Pull dbt CI tests forward to 1.1 and 1.4 [4442](https://github.com/sqlfluff/sqlfluff/pull/4442) [WittierDinosaur](https://github.com/WittierDinosaur)
* Teradata: Added "AND STATS" options when creating table [4440](https://github.com/sqlfluff/sqlfluff/pull/4440) [dflem97](https://github.com/dflem97)
* Add Databricks as a distinct dialect [4438](https://github.com/sqlfluff/sqlfluff/pull/4438) [WittierDinosaur](https://github.com/WittierDinosaur)
* Remove importlib deprecated methods [4437](https://github.com/sqlfluff/sqlfluff/pull/4437) [alanmcruickshank](https://github.com/alanmcruickshank)
* SQLite: Support PRAGMA statements [4431](https://github.com/sqlfluff/sqlfluff/pull/4431) [WittierDinosaur](https://github.com/WittierDinosaur)
* Proposed graceful handling of noqa by L016 (4248) [4424](https://github.com/sqlfluff/sqlfluff/pull/4424) [alanmcruickshank](https://github.com/alanmcruickshank)
* DuckDb: Allow quoted literals as identifiers [4410](https://github.com/sqlfluff/sqlfluff/pull/4410) [WittierDinosaur](https://github.com/WittierDinosaur)
* SQLite Refactor to reduce statement and keyword scope [4409](https://github.com/sqlfluff/sqlfluff/pull/4409) [WittierDinosaur](https://github.com/WittierDinosaur)
* L046 and L056 recode [4430](https://github.com/sqlfluff/sqlfluff/pull/4430) [alanmcruickshank](https://github.com/alanmcruickshank)
* Recode Aliasing Rules [4427](https://github.com/sqlfluff/sqlfluff/pull/4427) [alanmcruickshank](https://github.com/alanmcruickshank)
* Adjust MySQL dialect to support combination of not-null, default and … [4426](https://github.com/sqlfluff/sqlfluff/pull/4426) [FabianScheidt](https://github.com/FabianScheidt)
* Revert some changes to tox [4428](https://github.com/sqlfluff/sqlfluff/pull/4428) [alanmcruickshank](https://github.com/alanmcruickshank)
* Migrate capitalisation rules to plugin and recode [4413](https://github.com/sqlfluff/sqlfluff/pull/4413) [alanmcruickshank](https://github.com/alanmcruickshank)

New Contributors

* [FabianScheidt](https://github.com/FabianScheidt) made their first contribution in [#4426](https://github.com/sqlfluff/sqlfluff/pull/4426)
* [dflem97](https://github.com/dflem97) made their first contribution in [#4440](https://github.com/sqlfluff/sqlfluff/pull/4440)
* [timcosta](https://github.com/timcosta) made their first contribution in [#4391](https://github.com/sqlfluff/sqlfluff/pull/4391)