Diagnostic reference

Every diagnostic SQLQuack prints has one of these codes. A code is never reused or renumbered. See Reading diagnostics for the output format.

Code	Name	Meaning
SQ0001	parse_fidelity	the SQL does not parse
SQ0002	missing_rule	no verified rule converts this construct
SQ0003	type_condition	the operand types fall outside every rule’s proven conditions
SQ0004	unresolved_name	a name does not resolve
SQ0005	not_expressible	DuckDB cannot express this construct’s Snowflake meaning
SQ0006	conversion_budget	conversion exceeded its budget
SQ0007	session_parameters	the session parameters match no recorded session profile
SQ0008	divergence_refused	the conversion needs a known divergence the policy refuses
SQ0009	unaliased_output_name	this column has no alias, and SQLQuack has not verified the name Snowflake gives it
SQ0101	input_too_large	the input is larger than the parser accepts
SQ0102	token_budget_exceeded	the input has more tokens than the parser accepts
SQ0103	nesting_depth_exceeded	brackets nest deeper than the parser accepts
SQ0104	parser_depth_exceeded	the statement nests deeper than the parser accepts
SQ0105	ast_depth_exceeded	the parsed statement nests deeper than SQLQuack accepts
SQ0106	ast_budget_exceeded	the parsed statement is larger than SQLQuack accepts
SQ0107	function_nesting_depth_exceeded	function calls nest deeper than SQLQuack accepts
SQ0201	set_operation_all	Snowflake does not support INTERSECT, EXCEPT or MINUS with ALL
SQ0202	lock_wait	Snowflake does not support WAIT in FOR UPDATE
SQ0203	qualify_without_window	QUALIFY needs a window function
SQ0204	star_filter_drops_named_column	REPLACE or RENAME names a column the star’s ILIKE pattern removed

Conversion

Why a statement was not converted.

SQ0001

the SQL does not parse (parse_fidelity)

SQLQuack’s Snowflake parser could not read the SQL, so nothing in it was converted. The span points where the parser stopped and the label carries the parser’s message. Check the SQL at that point against Snowflake’s grammar; if Snowflake accepts the statement, SQLQuack’s parser is missing the syntax and the statement should be reported with the label.

Example:

echo "SELECT a, FROM orders WHERE (b = 1" | sqlquack check

SQ0002

no verified rule converts this construct (missing_rule)

Every construct SQLQuack converts comes from a rule verified against recorded Snowflake results. No such rule covers the construct at the span yet, so the statement is refused rather than converted to SQL that might mean something else in DuckDB. Rewrite the statement without the construct, or wait for a rule; sqlquack census shows which constructs a body of SQL needs.

Example:

echo "SELECT WEEKISO(CURRENT_DATE()) AS w" | sqlquack convert --builtin-policy

SQ0003

the operand types fall outside every rule’s proven conditions (type_condition)

A rule covers the construct, but only for operand types its recorded cases prove. The operands at the span have other types, or types SQLQuack could not determine. Declaring the relation’s columns and types in the schema often lets the types resolve; an explicit cast to a covered type also works when it keeps the statement’s meaning.

Help: declare the operands’ Snowflake types in the schema, or cast them to a type the rule covers

Example:

echo "SELECT FLOOR('abc') AS f" | sqlquack convert --builtin-policy

SQ0004

a name does not resolve (unresolved_name)

Converting a statement needs to know what each relation and column name refers to and what type it has. The name at the span is unknown, or resolves to more than one column. Add the relation and its columns to the schema file, or qualify an ambiguous column with its relation.

Help: declare the relation and its columns in the schema (--schema on the command line)

Example:

echo "SELECT a FROM orders" | sqlquack convert --builtin-policy

SQ0005

DuckDB cannot express this construct’s Snowflake meaning (not_expressible)

The construct at the span has a Snowflake meaning DuckDB has no way to reproduce, for the reason in the label. No future rule will convert it as written. Rewrite the statement so it does not depend on that behaviour.

Example: no construct is declared inexpressible yet; one would be refused with the reason as its label

SQ0006

conversion exceeded its budget (conversion_budget)

Converting the statement took longer, or produced more DuckDB SQL, than the conversion budget allows. The budget keeps a pathological input from stalling a caller. Splitting the statement, for example into common table expressions or separate statements, usually brings it under the budget.

Help: split the statement into smaller statements

Example: a statement whose conversion runs past the time budget or emits more DuckDB SQL than it allows

SQ0007

the session parameters match no recorded session profile (session_parameters)

Conversions are proven under recorded Snowflake session profiles. The session parameters given match none of them, so no conversion is proven for that session. The label names the parameters; unset parameters take Snowflake’s documented defaults.

Help: pass only session parameters a recorded profile covers, or leave them unset

Example:

echo "SELECT 1 AS one" | sqlquack convert --builtin-policy --session WEEK_START=1

SQ0008

the conversion needs a known divergence the policy refuses (divergence_refused)

The construct at the span converts only by relying on the known divergence the label names, where DuckDB may raise or differ from Snowflake as the policy file describes. The policy refuses that divergence in the current mode, so the statement is refused. Allow it in the policy file, use a mode that allows it, or confirm the assumption it rests on; otherwise rewrite the statement without the construct.

Help: the help names the divergence and the fixes that work in the current mode: allow it in the policy file, drop –strict or –loud-divergences-only, or confirm its assumption; the built-in policy refuses every one but the clock outside strict mode

Example:

echo "SELECT HASH(1) AS h" | sqlquack convert --builtin-policy

SQ0009

this column has no alias, and SQLQuack has not verified the name Snowflake gives it (unaliased_output_name)

Snowflake names an unaliased select item after the expression’s text, and a converted query must return the same column names. SQLQuack emits Snowflake’s name only for the shapes recorded results verify, named by their text upper-cased as written: literals, columns and COUNT, SUM, MAX, AVG, LOWER, UPPER and COALESCE of columns, ROUND of a column, a column plus, minus, times, divided by or concatenated with a literal, a column cast to INTEGER, and CURRENT_DATE, CURRENT_DATE() or CURRENT_TIMESTAMP(), written with single spaces and at most one line break. Other items, such as paths, CASE, nested calls or other functions, are refused rather than converted with a different column name. An alias fixes the name in both engines.

Help: add an alias, e.g. AS one

Example:

echo "SELECT 'café' || 'b'" | sqlquack convert --builtin-policy

Input limits

Limits that keep parsing and conversion bounded.

SQ0101

the input is larger than the parser accepts (input_too_large)

The parser refuses inputs beyond a fixed size so that one input cannot exhaust memory. Split the SQL into several inputs and convert each.

Help: split the input into smaller files

Example:

python3 -c "print('SELECT 1;' * 2000000)" | sqlquack check

Also reported by its guard name, E_GUARD_INPUT_TOO_LARGE.

SQ0102

the input has more tokens than the parser accepts (token_budget_exceeded)

The parser refuses inputs with more tokens than its budget so that parsing time stays bounded. Split the SQL into several statements or inputs.

Help: split the input into smaller statements or files

Example:

python3 -c "print('SELECT 1;' * 400000)" | sqlquack check

Also reported by its guard name, E_GUARD_TOKEN_BUDGET_EXCEEDED.

SQ0103

brackets nest deeper than the parser accepts (nesting_depth_exceeded)

Parentheses, brackets and braces nest deeper at the span than the parser’s limit. The limit keeps parsing from exhausting the stack. Redundant parentheses can be removed; deeply nested subqueries can usually become common table expressions.

Help: remove redundant parentheses, or move nested parts into common table expressions

Example:

python3 -c "print('SELECT ' + '(' * 600 + '1' + ')' * 600)" | sqlquack check

Also reported by its guard name, E_GUARD_NESTING_DEPTH_EXCEEDED.

SQ0104

the statement nests deeper than the parser accepts (parser_depth_exceeded)

The parser’s recursion reached its depth limit at the span. The limit keeps parsing from exhausting the stack. Restructure the deepest part of the statement, for example with common table expressions.

Help: move nested parts into common table expressions

Example:

python3 -c "print('SELECT ' + 'NOT ' * 2000 + 'TRUE')" | sqlquack check

Also reported by its guard name, E_GUARD_PARSER_DEPTH_EXCEEDED.

SQ0105

the parsed statement nests deeper than SQLQuack accepts (ast_depth_exceeded)

The statement parsed, but its tree nests deeper than the limit SQLQuack places on later analysis, which keeps conversion from exhausting the stack. Restructure the deepest part of the statement.

Help: move nested parts into common table expressions

Example:

python3 -c "print('SELECT ' + 'CASE WHEN TRUE THEN ' * 600 + '1' + ' END' * 600)" | sqlquack check

Also reported by its guard name, E_GUARD_AST_DEPTH_EXCEEDED.

SQ0106

the parsed statement is larger than SQLQuack accepts (ast_budget_exceeded)

The statement parsed into more tree nodes than SQLQuack’s budget, which keeps conversion time and memory bounded. Split it into smaller statements.

Help: split the statement into smaller statements

Example: a statement that parses into more than a million tree nodes

Also reported by its guard name, E_GUARD_AST_BUDGET_EXCEEDED.

SQ0107

function calls nest deeper than SQLQuack accepts (function_nesting_depth_exceeded)

Function calls nest inside each other deeper than SQLQuack’s limit at the span. Compute the innermost calls in a common table expression or a subquery and refer to their results.

Help: compute inner calls in a common table expression first

Example:

python3 -c "print('SELECT ' + 'ABS(' * 300 + '1' + ')' * 300)" | sqlquack check

Also reported by its guard name, E_GUARD_FUNCTION_NESTING_DEPTH_EXCEEDED.

SQL Snowflake rejects

SQL that parses but that Snowflake rejects when it runs.

SQ0201

Snowflake does not support INTERSECT, EXCEPT or MINUS with ALL (set_operation_all)

Snowflake accepts ALL only after UNION. INTERSECT ALL, EXCEPT ALL and MINUS ALL parse, but Snowflake rejects them as an unsupported feature, so there is no Snowflake meaning to convert. Without ALL the operators compare distinct rows; keeping duplicates needs a rewrite, for example numbering each row with ROW_NUMBER() over all columns and comparing those pairs.

Help: drop ALL to compare distinct rows; only UNION accepts ALL in Snowflake

Example:

echo "SELECT id FROM orders INTERSECT ALL SELECT id FROM returns" | sqlquack check

SQ0202

Snowflake does not support WAIT in FOR UPDATE (lock_wait)

FOR UPDATE WAIT n parses, but Snowflake rejects it as an unsupported feature, so the statement never runs there. Remove the WAIT clause; Snowflake’s lock timeout comes from the LOCK_TIMEOUT session parameter instead.

Help: remove the WAIT clause

Example:

echo "SELECT order_id FROM orders FOR UPDATE WAIT 7" | sqlquack check

SQ0203

QUALIFY needs a window function (qualify_without_window)

Snowflake evaluates QUALIFY after window functions, and rejects a QUALIFY clause when neither the SELECT list nor QUALIFY itself calls a window function (Snowflake error 992). A window function the QUALIFY condition reaches through a SELECT-list alias counts. A condition on plain columns belongs in WHERE, and one on aggregates in HAVING.

Help: move the condition to WHERE or HAVING, or filter on a window function’s result

Example:

echo "SELECT 1 QUALIFY TRUE" | sqlquack check

SQ0204

REPLACE or RENAME names a column the star’s ILIKE pattern removed (star_filter_drops_named_column)

In * ILIKE 'pattern' REPLACE (...) or RENAME (...), Snowflake applies the pattern first, ignoring case, and rejects a REPLACE or RENAME of a column the pattern did not keep (Snowflake error 1431). A name that fails the pattern ignoring case fails it whatever the table holds, so no schema is needed to see it.

Help: name a column the ILIKE pattern keeps, or widen the pattern

Example:

echo "SELECT * ILIKE '%_id' REPLACE (price * 2 AS price) FROM orders" | sqlquack check