How to Work Around MySQL's View Subquery Limitation (Bug #11472)

That Moment Your VIEW Refuses to Compile

You're refactoring a reporting query. It's gnarly — three layers of aggregation, a couple of LEFT JOINs, the works. You wrap it in a subquery in the FROM clause, run it, get the right numbers. Nice. Now you decide to encapsulate the whole thing in a VIEW so the analytics folks don't have to copy-paste it.

You run CREATE VIEW. MySQL throws this in your face:

ERROR 1349 (HY000): View's SELECT contains a subquery in the FROM clause

If you've worked with MySQL long enough, this error has shown up at least once. It's the symptom of one of MySQL's oldest documented limitations — tracked as Bug #11472 — and developers have been finding workarounds for it for two decades.

I want to walk through why this happens, how to work around it cleanly with the tools that exist today, and what the reportedly-landed fix might change for your codebase.

What's Actually Going On

MySQL's VIEW implementation resolves views in one of two modes: MERGE and TEMPTABLE. MERGE rewrites the view's definition into the calling query. TEMPTABLE materializes the view into a temporary table first.

The catch: a subquery in the FROM clause — what the SQL spec calls a derived table — historically couldn't sit inside a view definition. The parser would let you create some shapes, but as soon as it hit a FROM (SELECT ...) AS something inside CREATE VIEW, you'd get error 1349.

The official VIEW docs describe the restriction. According to community discussion, a fix has reportedly landed — I haven't tested it against the official changelog myself, so I'm hedging there. Either way, the workarounds below are what most production codebases have been using.

The Classic Workaround: Nested Views

The most common trick is to split the view into two. The inner subquery becomes its own view, and the outer view selects from it.

-- Step 1: the inner view does the heavy lifting
CREATE VIEW order_totals_per_customer AS
SELECT
    customer_id,
    SUM(amount) AS total_spent,
    COUNT(*)    AS order_count
FROM orders
WHERE status = 'completed'
GROUP BY customer_id;

-- Step 2: the outer view joins to the inner one
-- (instead of inlining a derived table)
CREATE VIEW customer_revenue_report AS
SELECT
    c.id,
    c.name,
    COALESCE(ot.total_spent, 0) AS lifetime_value,
    COALESCE(ot.order_count, 0) AS orders
FROM customers c
LEFT JOIN order_totals_per_customer ot
    ON ot.customer_id = c.id;

This works. It's also a pain — you now have two views to maintain, and the names tend to multiply when the query is more complex. I've seen schemas with 40+ "helper" views that exist solely to dodge this restriction.

A Cleaner Approach: Common Table Expressions

If you're on MySQL 8.0 or later, CTEs are your friend. They were added in 8.0 (see the WITH syntax docs) and they're allowed inside view definitions.

CREATE VIEW customer_revenue_report AS
WITH order_totals AS (
    SELECT
        customer_id,
        SUM(amount) AS total_spent,
        COUNT(*)    AS order_count
    FROM orders
    WHERE status = 'completed'
    GROUP BY customer_id
)
SELECT
    c.id,
    c.name,
    COALESCE(ot.total_spent, 0) AS lifetime_value,
    COALESCE(ot.order_count, 0) AS orders
FROM customers c
LEFT JOIN order_totals ot ON ot.customer_id = c.id;

Same logic, one view, no extra schema noise. I migrated a reporting pipeline from the nested-view approach to CTEs last year — went from 18 supporting views to four. Made the on-call rotation much happier.

A couple of caveats from that migration:

• CTEs in MySQL aren't always inlined the way you'd expect. The optimizer can choose to materialize them, and that may or may not be what you want for a given workload.
• If you need recursion (parent/child trees, for example), WITH RECURSIVE works inside views too — just watch the cte_max_recursion_depth setting.

Debugging When You Still Hit Error 1349

If you've wrapped your query in a CTE and you're still hitting the error, there are a few usual suspects:

1. You're on MySQL 5.7 or earlier. CTEs are 8.0+. There's no backport. Upgrade or stick with the nested-view workaround.
2. The view definer lacks permission on the underlying tables. This isn't error 1349 specifically, but it shows up at view-creation time and the message can be misleading.
3. You've got a derived table hiding inside the CTE. A correlated subquery in the SELECT list is fine; a FROM (SELECT ...) inside the CTE body can still trip the historic restriction.

To check which algorithm MySQL picked for your view, query information_schema.VIEWS:

SELECT
    table_name,
    is_updatable,
    -- 'MERGE', 'TEMPTABLE', or 'UNDEFINED'
    algorithm
FROM information_schema.views
WHERE table_schema = DATABASE();

If you see TEMPTABLE where you expected MERGE, that's a hint that something in the view forced materialization — non-deterministic functions, aggregates, DISTINCT, or until recently, a derived table.

What the Fix Might Change

Based on what's circulating in the bug tracker thread, the fix reportedly relaxes the derived-table restriction inside view definitions. I'll be honest — I haven't tested it thoroughly yet, and the exact MySQL version that ships the fix matters a lot for whether you can adopt it.

A few things I'd want to verify before refactoring production code:

• Which exact MySQL release contains the change (check the official changelog, not blog posts)
• Whether ALGORITHM = MERGE works with derived tables, or if everything falls back to TEMPTABLE
• How the optimizer handles the inlined derived table compared to an equivalent CTE

Until you can confirm those on your own deployment, the CTE pattern above is the safer bet. It's been in 8.0 since launch and the behavior is well-documented.

Prevention: Habits That Save Future-You

A few rules I've ended up living by after too many "why doesn't this view work" debugging sessions:

• Write the query as a plain SELECT first, get it returning the right rows, then wrap it in a view. Don't debug DDL and query logic at the same time.
• Prefer CTEs over nested views when you're on 8.0+. They're easier to read and they don't pollute the schema.
• Check algorithm in information_schema. If TEMPTABLE shows up where you didn't expect it, your view won't be updatable and may not perform the way the planner makes it look.
• Don't bury joins inside derived tables when a CTE would do the same job. It reads better and the optimizer has more to work with.

Twenty years is a long time for a bug to sit open. The workarounds are well-worn, the tooling around CTEs is solid, and most of the pain this caused has been quietly absorbed into MySQL muscle memory at this point. Worth knowing both the old patterns and the new ones — you'll run into both in any sufficiently old codebase.