Troubleshooting

What the common errors mean and how to fix them, from a deploy the loader rejects to a query the planner refuses. 0sql refuses a query it cannot answer correctly instead of returning a wrong number; the message names the field or combination it could not resolve, and that is the thing to fix in the model, not something to work around in the request.

Every HTTP error has the same shape, and zsql prints it as <class>: <message>:

{"error": {"class": "Planner::ResolutionError", "message": "No universe can resolve the query within datasource tpcds."}}

Quick reference

Deploy errors

zsql deploy tars the project directory and POSTs it; zsql check does the same against /validate and stores nothing. Both fail with a 400 DeployError whose message says which stage refused.

no project.yml here or above; run zsql init to start a project

Local. zsql walks up from the current directory looking for project.yml. Run it inside the project, or pass --project <dir>.

the body must be a tar.gz of the project directory, unpacking the archive: ..., the archive holds no project.yml

Only reachable when you POST to /deploy yourself. The body must be a gzip tar with project.yml at its root or inside one top-level folder. zsql deploy builds this for you.

Error in <file>: <message>

The loader rejected a file. The message is one of the model validation messages below; fix the file and run zsql check until it is clean, then zsql deploy. Nothing is deployed when any file fails: the branch keeps its previous model.

snapshot has N integrity problems:

A compiled model that references something missing. Each line is of the form expression <e> references missing field, join <j> left table <id> is missing, field <f> mapped twice on table <t>, table <t> snapshot date <f> is not a dimension, partition <p> dimension <d> is missing or not a dimension, or duplicate <kind> uid <uid>. Almost always a rename: a table or field was renamed in one file and still referenced by its old name in a relation, a snapshot:, a partition or a formula.

query keys are read only; deploy with your personal key

403. The key in .zsql or ZSQL_API_KEY starts with zqk_. Deploy with a zsk_ key. See Auth errors.

Deploy succeeded but zsql deploy exited with tests failed

The server returns 200 even when tests fail; the model is live. zsql prints PASSED, FAILED or ERROR per test with --- generated and --- expected blocks, then exits non-zero. A failing assert_sql usually means the model changed and the expected SQL is stale: compare, update the test, redeploy. ERROR means the test’s projections no longer plan (a renamed field) or its assert_regex does not compile. See Tests.

Warnings

A deploy can succeed with warning: lines. They are worth fixing:

Model validation

Loader messages arrive as Error in <file>: followed by one of these. The ones people hit most:

Plan errors

POST .../sql, /explain and /explore answer 400 for a malformed request, 404 when the branch is not deployed, and 422 when the request is well formed but the model cannot answer it.

zsql explain is the fastest way to see why: it prints the node tree, which table each node reads and the paths it joined. See Explain and the full list in Query errors.

Query resolution

No universe can resolve the query within datasource <ds>.

The request asked for a measure grouped or filtered by a dimension its fact cannot reach, and no blend between facts covers it. 0sql refuses rather than guess a join. Check, in order:

1. Is there a relation? The dimension’s table must be reachable from the measure’s fact through many_to_one (or one_to_one) joins. Add the missing rel.*.yml.
2. Is the change deployed? zsql deploy, then zsql tables and zsql fields <name> show what the branch has.
3. Is it the wrong concept? If the dimension exists under a different name on the fact’s side (Ship Country vs Country), it is not the same field. Unify the names if they are one concept, or ask for the right one.
4. Should the measure ignore it? If the measure is meant to be grouped only by some dimensions, model that with an exclusion.

Remove fields one at a time to find the culprit, or ask zsql explore --expr "<the fields that work>" which dimensions and measures can still be added.

Every plan runs in one datasource

A query is answered from exactly one datasource; results are never merged across them. If the fields you need live in two, define the missing measure on a table in the other datasource under the same name, and routing picks whichever datasource can serve everything. See Semantic routing.

Security policy context dimension '<d>' is not reachable in the universe for this query.

A policy fired (the query projects a tagged or named field) but the node’s universe has no path to the policy’s context_dimension. The query is refused because the filter or mask could not be applied. Either give that table a join path to the context dimension, or pick a context dimension every table with a triggered field can reach. See Security.

Fuzzy field corrections

A field reference in a spec or an expr is matched by name, uid or synonym, case-insensitively. When nothing matches exactly, the planner scores every field of the wanted kind by trigram overlap with the reference and applies this rule:

• A reference shorter than four characters never corrects; it resolves exactly or fails.
• If the best candidate scores at least 0.5 and leads the runner-up by at least 0.2, it is used silently and reported in the response’s corrections array.
• Otherwise the request fails with Semantic::NotFound, with up to three candidates scoring at least 0.25 appended as Did you mean: A, B, C?.

{"sql": "...",
 "corrections": [{"term": "departmnt", "field_uid": "department", "field_name": "Department", "score": 0.8}]}

corrections is omitted when empty. zsql sql prints each one to stderr as departmnt → Department (~0.8). If a correction surprises you, use the exact name or the uid, and consider adding the misspelling as a synonym on the field so it resolves exactly.

Auth errors

See Accounts, keys and access.

Numbers look wrong

0sql returns SQL, so a wrong number is a wrong model or a wrong request. zsql explain shows which table and joins produced it.

Numbers are too high (double counting)

• Cardinality is wrong. A relation declared many_to_one that is really one-to-many multiplies the measure. Check the data and fix cardinality. See Cardinality.
• allow_measure_expansion: true on a join that is not safe. Expansion lets a measure be grouped by dimensions on the many side. Remove it unless the join really is safe.
• Many-to-many modelled as a direct join. Use a junction table with two relations.

The same measure gives different numbers in two requests

When a measure is defined on more than one table, routing chooses per request by the requested dimensions, then by cost. If two definitions of Store Net Paid disagree, they are not the same concept or one has a bug. Run zsql explain on both requests to see which table served each; give a distinct name to anything that means something different.

A balance or inventory total is wrong across months

Summing a daily balance over a month is meaningless. Model it as a snapshot measure with snapshot: ending (or beginning) on a table that declares its snapshot: date dimension.

A cross-domain compound measure repeats a value across rows

Expected. When a request groups by a dimension only some components of a compound measure can reach, the others are auto-levelled: aggregated without that dimension and repeated across it. For a rate this is the fixed denominator you want. See Compound measures.

Getting help

1. zsql check for the full list of loader errors and warnings.
2. zsql explain --json --expr "..." for the plan of a refused or surprising request.
3. Narrow it down: git diff HEAD~5 -- models/ shows what changed; drop fields until the request plans.
4. Send the Error in lines, the explain JSON, the relevant YAML and what you expected.

Next steps

• Query errors
• Deploy and Tests
• Accounts, keys and access