Skip to content

Latest commit

 

History

History
375 lines (328 loc) · 20.9 KB

AGENTS.md

File metadata and controls

375 lines (328 loc) · 20.9 KB

databricks-sql-quickcheck

Guidance for anyone, human or agent, working in this repo. Read CONTRIBUTING.md first: its rules bind everyone, and rule 1 outranks everything here. Never put SQL you may not publish in any file, issue or commit.

This file is public and permanent. Don't name people, CLI profiles, workspaces, warehouses or anyone's machine. Record a decision by what was decided, not by who decided it. Maintainers keep private notes outside the repo.

Starting a session

  1. docs/scope.md sorts SQL mistakes into rungs, by how much information you need to know the SQL is wrong. It holds the current measured numbers. Every piece of work is scoped against it.
  2. The pinned roadmap, gh issue view 14, tracks the open work against that ladder.
  3. Close an issue in the commit that finishes it (Closes #n). If the commit moves a number, update docs/scope.md and the "Where things stand" line of #14 in the same commit, so the two never disagree.

What this is

A fast, shallow syntax checker for Databricks SQL. It is a single-pass lexer plus structural checks, written in TypeScript with no runtime dependencies. It ships as a VS Code extension, a CLI and a library. It is the as-you-type tier. A full grammar, such as SQLFluff, is the tier that runs on save or in CI.

src/lexer.ts       token boundaries: strings, quoted identifiers, comments, $$ bodies,
                   templates, notebook cell separators. Nothing statement-level
src/check.ts       token-level checks (brackets, commas, operators, lists), per fragment
                   (the tokens between `;` and cell separators)
src/statements.ts  statement-shape checks: keyword typos, missing `;`, missing commas,
                   clause order, CASE structure, dangling and truncated statements, empty
                   clause lists, forbidden keyword pairs, exclusive alternatives, a query
                   that lost its SELECT or WHERE
src/blocks.ts      SQL scripting block structure across the fragments of a cell
src/common.ts      shared types and token helpers
src/keywords.ts    GENERATED by scripts/gen_keywords.py (`npm run keywords`); don't edit
src/cli.ts         dbsql-quickcheck: files, --stdin, --json, --jsonl batch mode
src/extension.ts   VS Code: diagnostics on open and change (40 ms debounce), in-process
src/language.ts    which documents switch to the `databricks-sql` mode, and how a user's
                   own switch back sticks
syntaxes/, language-configuration.json   the `databricks-sql` language mode
src/test/          node:test unit tests; grammar-corpus.ts is the grammar/lexer agreement gate
eval/evaluate.py   the corpus evaluation: the false-positive gate and the catch rates
eval/baseline.json, eval/compare_baseline.py   the lost-catch gate
eval/scripting/    scripting cases with the engine's verdicts (see "SQL scripting blocks")
scripts/derive_shape_sets.py   re-derives and safety-checks the keyword sets and pairs
docs/scope.md      the ladder and the numbers
docs/design-notes.md   why silence is the default, measured; the forbidden-adjacency design
docs/scripting.md  the scripting pass: design, traps, evidence, known gaps
examples/mistakes.sql  one mistake per statement: the README's sample output

The invariant

An error must never fire on valid SQL. A false positive on every keystroke is worse than no checker. A change that trades a false positive for recall is the wrong trade.

Unrecognized input produces silence, not a finding. A finding needs positive evidence of a violation. The absence of a matching rule isn't evidence. Productions, recursive descent and parse trees are all allowed. What is forbidden is any design whose default verdict on syntax it hasn't been taught is "invalid". docs/design-notes.md measures the two designs that fail this: a full grammar, and "unseen, therefore wrong". Read it before adding a check that generalizes, and before relaxing this rule.

Checking a change

The commands are in CONTRIBUTING.md under "Running the checks", and CI runs them all. Before any change to the lexer, a check or the keyword list is done:

  • evaluate.py reports 0 error files and 0 warning files on the valid corpus, and every must-parse reference case clean. It exits 1 on any false positive, and 2 if its known-bad control stops firing.
  • compare_baseline.py exits 1 on a lost catch. The false-positive gate can't see a change that stops catching things. The comparison names each lost reference case, and each mutation kind whose caught count dropped. A PR that gains catches updates the baseline in the same commit: run evaluate.py against the pinned corpus, then python eval/compare_baseline.py --update. The comparison's own tests need no corpus: python3 -m unittest discover -s eval -p 'test_*.py'.
  • CORPUS_REF in ci.yml pins the corpus. Bump it in a PR of its own, which regenerates the baseline, so a corpus change is never mixed with a checker change.
  • After touching NEEDS_FOLLOWER, EMPTY_LIST_OWNERS or FORBIDDEN_PAIRS, run scripts/derive_shape_sets.py. It fails if a shipped word or pair now appears in valid SQL anywhere in the corpus.
  • Run the checks over Spark's own test suite (spark-sql-tests in the corpus) as an adversarial pass. It is unscored, because it mixes valid and invalid statements, but it has unusual syntax that no other source has, and it has caught false positives in nearly every new check. Every error it reports must be one you have read and confirmed. The current ones sit under their files' own negative-test headings.

What the catch rates mean

shape_of in the evaluation buckets the reference corpus's must-reject cases. A single rate over all of them misleads, because many are out of this checker's reach:

bucket meaning whose
truncated stops on a keyword or operator still owing an operand ours
empty-list a clause bracket with nothing in it ours
conflict two exclusive alternatives both written ours
untriaged no shape rule places it; mostly rungs 3 to 6 read case by case to triage (#10)

The -> target line (the three buckets that are ours) is the one to watch. conflict comes from the case's own reason: exclusive-alternative, not from the tokens: SELECT ALL DISTINCT looks well formed at this depth, and only the docs forbid it. empty-list counts a bracket only when a keyword owns it, because CREATE FUNCTION f() and SELECT uuid() write an empty pair legitimately. A bucket name is a claim: untriaged was once called grammar and written off as out of scope, and nobody had checked.

Measure against mistakes people make (everyday_mutants: keyword typos, deleted ;, deleted column commas), not only against mutate.py. That generator makes only bracket, quote, comma and operator damage, and a checker tuned to it alone misses most everyday typing mistakes.

Severity

  • error: no grammar can accept it (brackets, quotes, ,,, a dangling operator, CASE without END, a scripting block left open).
  • warning: the parser might accept it, because Spark's non-reserved keywords let SELECT a, FROM t read as a column named from. Or it is valid SQL that does the wrong thing (SELECT a\n b).

Heuristic checks are warnings, and they run only on fragments with no errors, because they depend on bracket depths.

Adding a check

The three gates in CONTRIBUTING.md rule 4 apply to every rule and every deny-list entry. The second gate, the docs, is yours to do by hand, and derive_shape_sets.py can't do it for you. The page's verbatim syntax: block must show the case's omits: sitting between the words you key on, or its conflicts: naming them both. Zero hits in the corpus is not evidence of invalidity. SELECT 1 WHERE true is valid, and the corpus never writes it.

Forbidden adjacency (FORBIDDEN_PAIRS) is an ordered pair of adjacent keywords at bracket depth 0. One mechanism covers exclusive alternatives (ALL DISTINCT), an empty mandatory slot (GRANT ON), a missing modifier (OR VIEW) and compound-statement shape (IF THEN). It is an explicit deny-list and must stay one, so new Databricks syntax can never make it fire; only editing the list can. derive_shape_sets.py prints candidates and never admits one. A pair whose words could be names carries a lead, the statement's first keyword. That is the positional evidence that keeps the pair off names. RUN FULL is a conflict under VACUUM, but in SELECT * FROM logs run FULL JOIN b, run is a table alias.

Non-adjacent exclusive alternatives (Stmt.exclusiveAlternatives()) are clause-scoped, and the scope boundary is the whole safety argument. NATURAL JOIN b ON ... stops scanning at the next JOIN, because a NATURAL JOIN b JOIN c ON ... is valid.

Refusals are the design working. Some shapes the docs forbid can't be told apart from valid SQL at this depth. Those are refused, and docs/scope.md lists them. Examples: TABLE COMPUTE (ANALYZE TABLE compute COMPUTE STATISTICS), GROUP BY ALL, a (all is a legal column name), and CREATE OR REPLACE ... IF NOT EXISTS, which valid corpus files write.

Every check ships with two tests: a case that must fire, and the valid construct that looks most like the mistake, which must stay quiet. Examples: ARRAY<INT>) next to a >), and count(*) total next to a b c.

Traps

Each of these has already cost a false positive or a silent loss of catches.

  • Nearly every keyword can be a name. Real schemas have columns called form, pull, target and location, and Spark's suite writes DROP VIEW view. So a word one letter off a keyword proves nothing on its own. A typo is reported only with positional evidence: between two complete operands (SELECT a FORM t), before a word that only follows a keyword (ORDRE BY), or after a keyword whose next word is fixed (LEFT JION). CONTINUES and STRUCTURAL in statements.ts define "operand" as a blocklist for the same reason.
  • A keyword at the end of a statement is often the object's name. NEEDS_FOLLOWER holds only syntax glue that owes something whatever precedes it (AS, RENAME TO, LIMIT). An extra word there is a false positive, not a lost catch. NEEDS_FOLLOWER_AFTER_NAME (COMMENT, LOCATION) fires only in DDL, after a name or ). The object-type rule needs the type right after the verb or a known modifier, and nothing named. CATALOG is not an object type, because its name is optional (USE CATALOG parses). ON, IN and TO are not modifiers, because each also precedes a name.
  • KEYWORDS decides the missing-comma heuristics. A word in it is never treated as a bare identifier, so a word missing from it can cause false positives. OFFSET and MEASURES did. Add words to EXTRA in scripts/gen_keywords.py, but never common column names (ID, NAME, VALUE).
  • isKw(i, t.upper) is not "is a keyword". It checks that the word is one of those passed and not in name position, so passing the word's own text is true for any identifier. Anything that means "a real keyword" needs KEYWORDS.has(...) as well.
  • A guard can reach further than it means to, and every gate stays green while it does. If a check that should fire doesn't, look for a guard first. Three real cases:
    • checkStatements once returned early for GRANT/REVOKE/DENY, above checks it wasn't protecting. Now only the comma and clause-order heuristics skip privilege lists, and danglingKeywords() must stay inside that skip.
    • The alias rule read VERSION AS OF 3 as an alias named of.
    • emptyLists keyed on the one token before (, which for ZORDER BY () is BY (bare BY is vetoed by group by ()). EMPTY_LIST_OWNER_PAIRS exists for that.
  • The FROM-list scan must stop early. It stops at LIKE and IN (SHOW TABLES FROM db LIKE 'x'), and at TIMESTAMP and VERSION (TIMESTAMP AS OF <expr>). Nothing after CREATE ... AS is checked, because CREATE FLOW f AS AUTO CDC INTO t FROM stream(s) has a FROM and no SELECT. Inside a function call, FROM is ordinary syntax (extract(YEAR FROM d)), so queryWithoutSelect runs only in brackets owned by words in QUERY_BRACKET_OWNERS.
  • Notebook cells are hard boundaries. Each cell runs on its own, so the lexer ends strings and comments at -- COMMAND ----------, but only in files with the -- Databricks notebook source header.
  • Raw statements get lexical checks only. SET foo = (1, 3 is valid, because Spark reads SET, RESET, ADD, LIST, REFRESH and DFS as raw text. SET VAR is real SQL and is checked.
  • Hints are parsed. /*+ BROADCAST(t) */ is a comment to the lexer, but its brackets are checked when it follows SELECT/INSERT/UPDATE/MERGE/DELETE.
  • Implicit aliases are valid. A same-line implicit alias (SELECT a b, c) warns only with the opt-in explicitAliases, since valid published files use it deliberately. An alias on a new line warns only after a bare column, since TPC-DS writes sum(case … end) with its alias on the next line on purpose.
  • spark-sql-tests is a source of false positives, not a validity oracle. It deliberately contains invalid SQL under headings like -- Errors. Its veto is advisory in derive_shape_sets.py, and a must-reject case overrides it.
  • KNOWN_BROKEN in the evaluation lists valid-corpus files that are broken as published, which quickcheck must flag. Add one only with independent confirmation (the corpus's docs/gaps.md, and SQLFluff agreeing).
  • mutate.py mis-tiers SET mutants after a notebook header. The evaluation excludes them with a printed count. The fix belongs in databricks-sql-corpus.

SQL scripting blocks

src/blocks.ts keeps a stack over the fragments of each notebook cell. It reads openers only at statement starts, and it gives up for the rest of the cell at anything it doesn't understand. It reports errors only, each a shape the engine rejects. The design, the traps and the known gaps are in docs/scripting.md.

The published corpus has no scripting, so the engine is the oracle. Every script in eval/scripting/ was run on a Databricks SQL warehouse, and the file records the engine's verdict. valid.json must produce no findings. invalid.json must produce an error on each mistake and none on its repaired twin, with checker_misses as a two-way ratchet. To add a case, run it on a warehouse first and record what the engine said. Never write a verdict from memory or from the docs alone: the engine has overruled both, for example on BEGIN ATOMIC END.

Running scripts on a warehouse. Use a workspace you are allowed to test on, and pass its CLI profile explicitly every time. An agent never picks a profile itself. One way to run a statement:

databricks experimental aitools tools query "<sql>" --profile <profile>
  • Keep every script free of side effects: no DDL, no DML, no table access.
  • Make every loop terminate. Change the counter first in the body, and never write WHILE true without a LEAVE on the first pass.
  • A runaway loop keeps running on the warehouse after its CLI process is killed. Find it with databricks api get "/api/2.0/sql/history/queries?filter_by.statuses=RUNNING" and stop it with databricks api post /api/2.0/sql/statements/<query_id>/cancel.
  • A stopped serverless starter warehouse sometimes refuses to start from the CLI ("Cannot create the resource"). Starting it from the workspace UI works.

Hand-written tests miss the keyword-as-name cases. An adversarial pass, which tried to write scripts the engine runs but the checker flags, found 94 false positives in 364 valid scripts, all now fixed and kept as fixtures. then, do, end, case, begin and nearly any other keyword can be a label, a variable, an alias or a JSON key. Run a change to a block rule against every fixture before opening a PR. Treat a new false positive as a missing fixture first.

The language mode

The extension contributes a databricks-sql language: a TextMate grammar, a language configuration, and a switch that moves exported notebooks into it on open. It produces no findings, only coloring, brackets and comment toggling. It exists because VS Code's built-in sql grammar is T-SQL's, and it gets Databricks strings wrong ('it\'s', r'C:\', one unclosed quote running through every later cell).

  • The grammar and the lexer must agree on boundaries. Where a string, quoted identifier, comment, $$ body or template begins and ends is decided twice, and a disagreement means the editor shows a string the checks don't see. grammar.test.ts asserts agreement case by case. node out/test/grammar-corpus.js <corpus cache>, also run in CI, compares them character by character on every corpus file. Change one, change the other.
  • vscode-textmate drops a rule whose only pattern is a missing grammar, boundaries and all. #embedded-fallback, a pattern that never matches, keeps every embedding rule alive when Python or YAML support isn't loaded.
  • $$ is not always Python. The body is opaque by default. Only a preceding LANGUAGE PYTHON or LANGUAGE YAML embeds a language.
  • Every multi-line rule ends at a cell separator, header or not. The grammar can't see the notebook header from line 40, so it always stops.
  • A manifest firstLine loses to the .sql association, so notebooks are switched at runtime with setTextDocumentLanguage. VS Code reports any language change as a close and an open of the same document in one tick, which is how language.ts tells a user's switch back to sql from a reopened file.
  • The language carries its own file icon (icons/databricks-sql.svg). Explorer, tab and Source Control icons follow an open document's language, and no file icon theme knows databricks-sql, so without it a switched notebook shows the generic file icon. VS Code uses a language's icon only where the theme has none. .vscodeignore drops images/*.svg, so keep it out of images/.
  • The grammar's keyword list leaves out common column names (NAME, TYPE, KEY, VALUE, DATA, YEAR). A word after . is never a keyword.
  • Keywords are colored by what they do, in four groups: clause words (keyword.other), branching (keyword.control.flow), predicates (keyword.operator.wordlike.predicate) and the connectives AND, OR, NOT (keyword.operator.wordlike.logical). The scope names are chosen for the colors default themes give them, so that a condition reads as one color between the clause and branch words around it. Look at a change in a real theme before shipping it. Phrases are matched whole (IS NOT DISTINCT FROM, WHEN NOT MATCHED BY SOURCE, NOT NULL, CREATE OR REPLACE), and a word left out of the lists as a common name (SOURCE, KEY, HOUR) is colored only inside a phrase that rules out a name.
  • A function call needs the bracket to touch the name. orders (id BIGINT) is a column list. Published SQL writes over 17,000 calls tight and under 100 with a space.
  • Folding per cell was left out on purpose. A folding-range provider replaces indentation folding for the language rather than adding to it.

How a change lands

main is protected by a ruleset, so nothing, agent or human, pushes to it directly. Branch, push, gh pr create, and wait for the three checks (both unit-test jobs and the corpus false-positive gate) to pass on a branch that is up to date with main. Then gh pr merge --rebase (or --squash). History is linear, and force pushes to main and its deletion are blocked. Everything pushed here is permanent public history.

Releases

The extension is on the Visual Studio Marketplace under the publisher in package.json. It isn't on npm or Open VSX. It is free: no sponsor field and no paid tier. The publisher ID and extension name are permanent, and a removed extension's name stays reserved.

  1. In a PR, bump version in package.json and add that version's section to CHANGELOG.md.
  2. Once it is merged, a maintainer tags that commit on main as vX.Y.Z and pushes the tag.
  3. .github/workflows/release.yml checks that the tag is on main and matches package.json, then tests, packages and attests the VSIX.
  4. It waits in the marketplace environment for a maintainer's approval, then publishes with vsce publish --oidc and makes a GitHub release with the VSIX and the changelog section.

Agents prepare the release PR. They don't push release tags or approve deployments. No token is stored: the Marketplace's trusted-publishing policy names this repository, release.yml and the marketplace environment, so renaming any of them stops publishing. --oidc needs vsce 4.1.0 or later.

npm run package builds dist/databricks-sql-quickcheck.vsix, and code --install-extension dist/databricks-sql-quickcheck.vsix --force installs a branch build over the published one.