MariaDB / Intermediate: concurrency and reusable SQL / Lesson 9

Views and common table expressions

Break complex reports into named relations. Distinguish a persistent view definition from a statement-local CTE and reason about recursive termination.

About 4 min readContent reviewed 2026-10-23

On this page

Lesson goals

Use views and CTEs to express reporting stages.
Bound recursive work and understand type inference.

Names improve structure, not automatic speed

A view stores a query definition and can offer a stable interface to a subset of columns. It does not normally store a refreshed physical copy of the result like a materialized reporting table. A common table expression names a relation for one statement, which helps separate filtering, grouping and final presentation. MariaDB introduced CTEs in the 10.2 series. Optimizer merging or materialization decisions still depend on query shape and version, so readability is not a performance guarantee.

Expose a deliberate view interface

This definition assumes a training bookings table containing the named columns. SQL SECURITY INVOKER uses the caller privilege context for referenced objects, while DEFINER uses a different security model that must be reviewed. MariaDB freezes SELECT * to the columns present when a view is created. Explicit selection still makes the interface and later recreation easier to review. The query below should show only paid bookings and the exposed fields. Actual rows depend on the fixture; a view is not proof that an application authorization rule has been enforced.

sql Illustrative SQL; expected results are reasoned, not measured.

CREATE SQL SECURITY INVOKER VIEW paid_bookings AS
SELECT booking_id, customer_id, total
FROM bookings WHERE state = 'paid';
SELECT booking_id, total FROM paid_bookings
WHERE customer_id = 7 ORDER BY booking_id;

A persistent view definition is DDL. Use only an isolated fixture when rehearsing creation.

Aggregate once at a documented grain

A CTE lets a report first compute totals per customer, then filter or join that summary without repeating a long subquery. The name does not promise that the server executes it exactly once; inspect a plan if this matters. Prefer stages with explicit column names and grain. In migration work, MariaDB CTE syntax in SELECT is not a blanket promise that every MySQL form of WITH attached to UPDATE or DELETE is supported in the deployed release.

A recursive CTE must stop

The anchor creates day number one. Each recursive step adds one only while the prior number is below three, so the result is exactly 1, 2 and 3. UNION ALL is intentional because the bound supplies termination. Real hierarchy traversals need cycle handling and a depth policy; merely adding a depth column means rows can remain distinct even when node identifiers repeat. MariaDB has a recursion limit as a guard, but exhausting that limit is not a correct traversal design.

sql Illustrative SQL; expected results are reasoned, not measured.

WITH RECURSIVE days(n) AS (
 SELECT 1
 UNION ALL
 SELECT n + 1 FROM days WHERE n < 3
)
SELECT n FROM days ORDER BY n;

Expected output

The recursive predicate is the stopping condition; ORDER BY controls presentation.

Review types and dependencies

Recursive column types are determined from the anchor, so explicitly CAST a growing path string wide enough for its intended depth. Older releases could truncate some recursive results; patched versions reject incompatible growth, so test the exact release and data bounds. A view can become invalid after underlying schema changes or refer to a missing definer account. Inventory dependencies before altering a base table, and keep view ownership and security context part of the migration review.

Common errors and fixes

A CTE is queried in a later statement

Why it happens: A statement-local name was treated as a persistent table.
How to fix it: Repeat the WITH definition or use an appropriate persistent view.
Prevent it next time: Document the lifetime of every named relation.

ERROR 1406: Data too long during recursion

Why it happens: The growing value exceeded the type inferred from the anchor.
How to fix it: Cast the anchor to an adequate bounded type and verify release behavior.
Prevent it next time: Test maximum path depth and length, not only one-level trees.

A hierarchy query reaches max_recursive_iterations

Why it happens: Cycles or a missing termination rule kept producing rows.
How to fix it: Add explicit depth and cycle semantics and inspect the graph.
Prevent it next time: Include a cyclic fixture and review whether duplicates must be retained.

Check your understanding

1. Use a CTE to total synthetic payments of 10 and 15 for customer 7, then keep totals above 20.

Show worked solution

The inner relation contains two payment rows. Grouping produces one row for customer 7 with total 25, and the final WHERE retains it. This separates row-level data from summary filtering. A HAVING inside the grouped CTE could express the same threshold; choose the structure that makes later joins and grain easiest to review.

sql Worked solution for the stated synthetic assumptions.

WITH totals AS (
 SELECT customer_id, SUM(amount) AS total
 FROM (SELECT 7 AS customer_id, 10 AS amount
       UNION ALL SELECT 7, 15) AS p
 GROUP BY customer_id
) SELECT customer_id, total FROM totals WHERE total > 20;