How do I remove the Default Schema in a recursive query?

Question

I'm trying to run this 'WITH' query on MySQL Workbench:

WITH countUp AS (SELECT 1 as N
        UNION ALL
                SELECT n+1 FROM countUp WHERE n < 3)
SELECT * from countUp;

This recursive select is described here: https://builtin.com/data-science/recursive-sql.

But the Workbench is trying to append the database name on 'countUp'. Showing to me the following error:

Error Code: 1146. Table 'my_test_database.countUp' doesn't exist

How can I disable the Default Schema or at least disable it for this query?

Answer 1

You are trying to run a query with a Recursive CTE in MySQL.

MySQL's syntax requires the keyword RECURSIVE to be added to the CTE clause.

Note: It seems the course material fails to mention that the SQL Standard requires this keyword to always be specified in recursive queries. Omiting it may be permissible in some databases, though. I would keep this in mind if you want to run recursive CTEs in different databases.

The query should look like:

WITH recursive countUp AS (SELECT 1 as N
        UNION ALL
                SELECT n+1 FROM countUp WHERE n < 3)
SELECT * from countUp;

Result:

N
--
1
2
3

See running example at db<>fiddle.

Without the RECURSIVE keyword all CTEs need to be based on actual tables. That's what MySQL is trying to tell you.