Wren Sql

Wren SQL

Version check

Silently fetch https://raw.githubusercontent.com/Canner/wren-engine/main/skills/versions.json. Compare the wren-sql key with this skill's version (from the frontmatter above). If the remote version is newer, notify the user before proceeding:

A newer version of the wren-sql skill is available.

Update with:

```

npx skills add Canner/wren-engine --skill wren-sql --agent claude-code

```

Then continue with the workflow below regardless of update status.

---

Wren Engine translates SQL through a semantic layer (MDL — Model Definition Language) before executing it against a backend database. SQL must target MDL model names, not raw database tables.

For specific topics, load the relevant reference file:

---

Context

• You are querying a semantic layer, not a database directly.
• Only use model/view/column names defined in the MDL — never raw database table references.
• Wren Engine uses a generic SQL dialect similar to ANSI SQL (DataFusion/Postgres/DuckDB), but with differences.
• Check the dataSource field to identify the backend and apply dialect-specific rules if needed.

---

Core SQL Rules

• Only SELECT statements. No DELETE, UPDATE, INSERT.
• Only use tables and columns from the MDL schema.
• Do not include comments in generated SQL.
• Prefer CTEs over subqueries.
• Identifiers are case-sensitive. Quote identifiers containing unicode, special characters (except _), or starting with a digit using double quotes.
• Examples: "客户"."姓名", "table-name"."col", "123column"
• Identifier quotes: " (double quotes). String literal quotes: ' (single quotes).
• For specific date queries, use a range:

  WHERE ts >= CAST('2024-11-01 00:00:00' AS TIMESTAMP WITH TIME ZONE)
    AND ts <  CAST('2024-11-02 00:00:00' AS TIMESTAMP WITH TIME ZONE)

• For ranking, use DENSE_RANK() + WHERE. Include the ranking column in SELECT.
• Avoid correlated subqueries — use JOINs instead.
• Use SAFE_CAST when casting might fail: SAFE_CAST(col AS INT)

---

Filter Strategies

---

Supported Cast Types

bool, boolean, int, integer, bigint, smallint, tinyint, float, double, real, decimal, numeric, varchar, char, string, text, date, time, timestamp, timestamp with time zone, bytea

Example: CAST(col AS INT), TIMESTAMP '2024-11-09 00:00:00'

---

Aggregation

• All non-aggregated SELECT columns must appear in GROUP BY (window functions excepted).
• Aggregate conditions go in HAVING, not WHERE.
• Prefer ordinal GROUP BY for long column names:

  SELECT very_long_column_name AS alias, COUNT(*) FROM t GROUP BY 1

---

Sorting and Limiting

• ORDER BY for sort; LIMIT to restrict rows.
• When ORDER BY appears in a subquery or CTE, always include LIMIT.

---

Subquery Patterns

• Prefer CTEs (WITH clause) over nested subqueries.
• Subquery in SELECT must return a single value per row.
• Subquery in WHERE: use IN, EXISTS, or comparison operators.
• IN SUBQUERY in JOIN conditions is not supported — use JOIN ... ON instead.
• RECURSIVE CTEs are not supported.

---

Calculated Fields

Columns marked as Calculated Field in the MDL have pre-defined computation logic. Use them directly instead of re-implementing the calculation.

Read the column comment (e.g., column expression: avg(reviews.Score)) to understand what the field represents.

-- Schema has: Rating DOUBLE (Calculated Field: avg(reviews.Score))
--             ReviewCount BIGINT (Calculated Field: count(reviews.Id))

-- Correct — use Calculated Fields directly:
SELECT AVG(Rating) FROM orders WHERE ReviewCount > 10

-- Incorrect — do not re-join and re-aggregate manually

BigQuery Dialect Rules

Apply these rules when generating or correcting Wren SQL with BigQuery as the backend database.

TIMESTAMP + INTERVAL with MONTH / YEAR

TIMESTAMP WITH TIME ZONE cannot use +/- with INTERVAL containing non-zero MONTH or YEAR parts.

-- Valid:
timestamp_col + INTERVAL '7' days

-- Invalid:
timestamp_col + INTERVAL '1' month
timestamp_col - INTERVAL '2' year

-- Fix — cast to TIMESTAMP first:
CAST(timestamp_col AS TIMESTAMP) + INTERVAL '1' month

STRING vs NUMERIC Comparison

STRING cannot be compared with INTEGER or FLOAT directly. Use SAFE_CAST or CAST:

SAFE_CAST(string_col AS INT) > 100
CAST(string_col AS FLOAT) <> 75.5

Parsing String to Timestamp

PARSE_DATETIME('%Y-%m-%d', string_col)              -- timestamp (no timezone)
PARSE_TIMESTAMP('%Y-%m-%d %H:%M:%S', string_col)    -- timestamp with timezone

GROUP BY Alias

BigQuery does not allow GROUP BY to reference aliases defined in SELECT.

-- Invalid:
SELECT col1 AS alias1, COUNT(*) FROM t GROUP BY alias1

-- Valid:
SELECT col1 AS alias1, COUNT(*) FROM t GROUP BY col1

-- Preferred for long names:
SELECT very_long_column AS alias, COUNT(*) FROM t GROUP BY 1

Column Name Same as Table Name

If a column name matches the table name (case-insensitive), rename with an alias to avoid ambiguity.

-- Ambiguous:
SELECT "User".user FROM "User"

-- Clear:
SELECT "User".user AS user_column FROM "User"

Date/Time Diff Functions — Argument Order

DATE_DIFF, TIMESTAMP_DIFF, DATETIME_DIFF, TIME_DIFF take arguments as (part, start, end):

DATE_DIFF('day', start_date, end_date)
TIMESTAMP_DIFF('hour', start_ts, end_ts)

TIMESTAMP_DIFF with YEAR / MONTH

TIMESTAMP_DIFF does not support year or month date parts for TIMESTAMP arguments. Cast to DATE first:

-- Invalid:
TIMESTAMP_DIFF('year', ts1, ts2)

-- Fix:
TIMESTAMP_DIFF('year', CAST(ts1 AS DATE), CAST(ts2 AS DATE))

DATETIME vs TIMESTAMP

• DATETIME — local time, no timezone
• TIMESTAMP — UTC-based, no timezone in display
• TIMESTAMP WITH TIME ZONE — use when timezone awareness is required

SQL Error Diagnosis and Correction

Wren Engine processes SQL through multiple stages. Errors include a phase field and sometimes a dialectSql field showing which SQL layer failed.

Pipeline Stages

1. Parsing — Wren SQL → AST 2. Planning — AST → IR (subqueries generated per model definition) 3. Unparsing — IR → generic SQL (DataFusion dialect) 4. Transpile — generic SQL → target database dialect 5. Execution — transpiled SQL runs against the target database

Three SQL layers:

• Wren SQL — user-submitted SQL (only modify this one)
• Planned SQL — after planning/unparsing
• Dialect SQL — after transpiling, database-specific

Example error response:

{
  "phase": "SQL_EXECUTION",
  "metadata": { "dialectSql": "SELECT CURRENT_TIMESTAMP() - INTERVAL '1' MONTH" },
  "message": "TIMESTAMP +/- INTERVAL is not supported for intervals with non-zero MONTH or YEAR part."
}

---

Step 1 — Identify the Error Phase

Execution error sub-cases:

• Not-found in __source (deepest subquery) → model definition out of sync with DB schema → user error, not a bug
• Not-found in main/joined query → internal error, report to Wren support
• Error during execution but not dry-run → data issue (NULLs, type mismatches, runtime constraints)
• Timeout / resource limit → EXTERNAL ERROR; simplify query or check DB performance

---

Step 2 — Modify the Wren SQL

• Only modify the Wren SQL. Never modify Planned SQL or Dialect SQL directly.
• Think about what change to the Wren SQL would produce the correct Planned/Dialect SQL.
• Use alternative constructs if direct SQL triggers a known unsupported feature.

---

Step 3 — Test

Resubmit the modified Wren SQL. Repeat until execution succeeds.

---

Step 4 — Validate Results

• Always dry-run first, then execute.
• Verify results meet expected outcomes. Refine if incorrect.

---

Step 5 — Report Bugs (Last Resort)

First, find an alternative Wren SQL that achieves the same result without triggering the bug.

Only report a bug if no alternative exists AND the SQL is reasonable and should work:

• GENERIC_INTERNAL_ERROR or SQLGLOT_ERROR → report as internal error to Wren support
• Timeout / resource limit with no alternative → report as external error to the user

Date and Time Functions

Current Values

CURRENT_DATE       -- current date
CURRENT_TIMESTAMP  -- current timestamp

Truncation

DATE_TRUNC('<part>', <timestamp>)

Parts: 'year', 'quarter', 'month', 'week', 'day', 'hour', 'minute', 'second'

Extraction

EXTRACT(<part> FROM <timestamp>)

Parts: year, quarter, month, week, day, hour, minute, second

Date Difference

DATE_DIFF('<part>', <start_date>, <end_date>)

Parts: 'year', 'quarter', 'month', 'week', 'day', 'hour', 'minute', 'second'

Interval Arithmetic

<date_column>      + INTERVAL '7' days
<timestamp_column> - INTERVAL '3' hours

Timezone

• Use TIMESTAMP WITH TIME ZONE when timezone matters.
• TIMESTAMP and TIMESTAMP WITH TIME ZONE cannot be compared directly — cast one to the other first:

  CAST(<timestamp_col> AS TIMESTAMP WITH TIME ZONE)
  CAST(<timestamp_tz_col> AS TIMESTAMP)

Epoch / Unix Time Conversion

If a column stores dates/timestamps as integers, convert using:

to_timestamp(<int_col>)         -- seconds
to_timestamp_millis(<int_col>)  -- milliseconds
to_timestamp_micros(<int_col>)  -- microseconds
to_timestamp_nanos(<int_col>)   -- nanoseconds
to_timestamp_seconds(<int_col>) -- seconds (explicit)

Complex Data Types

ARRAY

Literals

ARRAY[<value1>, <value2>, ..., <valueN>]
['value1', 'value2', ..., 'valueN']
CAST(ARRAY[<value1>, ..., <valueN>] AS ARRAY<data_type>)
CAST(['value1', ..., 'valueN'] AS <data_type>[])

Type Definition

ARRAY<data_type>
<data_type>[]

UNNEST — same table

SELECT ...
FROM <table_name>, UNNEST(<array_column>) AS <alias>(<alias_col>)

UNNEST — join with another table

SELECT ...
FROM <table1>
JOIN <table2>, UNNEST(<table2>.<array_col>) AS <alias>(<alias_col>)
  ON <table1>.<col> = <alias>.<alias_col>

---

STRUCT

Type Definition

STRUCT<field1 data_type1, field2 data_type2, ..., fieldN data_typeN>

Accessing Fields

Use dot notation:

<struct_column>.<field_name>

-- Example:
SELECT address.city, address.postcode FROM users

---

Semi-Structured Types (JSON / VARIANT / OBJECT)

Type Definitions

JSON, VARIANT, OBJECT — no fixed schema. Don't assume specific fields unless confirmed via column metadata, comments, or sample data. Account for missing fields or varying structure across rows.

Accessing Fields

Do not use dot notation. Use GET_PATH:

GET_PATH(<semi_structured_column>, '<json_path>')

Type Conversion After GET_PATH

Do not use CAST directly on semi-structured values. Use type-specific functions:

AS_BOOLEAN(GET_PATH(...))   -- boolean
AS_DOUBLE(GET_PATH(...))    -- double/float
AS_INTEGER(GET_PATH(...))   -- bigint
AS_VARCHAR(GET_PATH(...))   -- varchar

-- Example:
AS_VARCHAR(GET_PATH(address_col, '$.city'))

Creating Semi-Structured Literals

parse_json('{"field1": "value1", "field2": 123}')

UNNEST Arrays Inside Semi-Structured Data

SELECT item
FROM <table_name>,
  UNNEST(AS_ARRAY(GET_PATH(<table>.<semi_col>, '<json_path>'))) AS unnest_alias(item)

JSON Type Rules (from column metadata)

If column metadata specifies json_type:

Follow the path property in metadata to build the correct path expression.

Example

Schema metadata:

-- {"json_type":"JSON","json_fields":{"address.json.city":{"path":"$.city","type":"varchar"}}}
address JSON

Query:

SELECT AS_VARCHAR(GET_PATH(u.address, '$.city')) FROM users AS u

Complex queries on semi-structured columns are slower than on structured columns. Optimize accordingly.