Why is this query not working? (MySQL 8.0.28)

Question

I have the following 2 tables and attempting a recursive query. Below is the tables and the query which is giving me an error.

CREATE TABLE tags (
    tag_id INTEGER,
    tag_name VARCHAR(50) NOT NULL,
    tag_descrip VARCHAR(200),
    tag VARCHAR(200) NOT NULL,
    CONSTRAINT tags_pk PRIMARY KEY (tag_id )
);

CREATE TABLE tag_tree (
    tag_id INTEGER,
    parent_tag_id INTEGER,
    CONSTRAINT tag_tree_pk UNIQUE (tag_id, parent_tag_id),
    CONSTRAINT tag_tree_tags_fk FOREIGN KEY (tag_id)
    REFERENCES tags(tag_id),
    CONSTRAINT parent_tag_tags FOREIGN KEY (parent_tag_id)
    REFERENCES tags(tag_id),
    CONSTRAINT parent_tag_tag_tree_fk FOREIGN KEY (parent_tag_id)
    REFERENCES tag_tree(tag_id)
);

WITH RECURSIVE tags_and_their_parents (
    tag_id,
    parent_tag_id,
    depth)
AS (
    SELECT tag_id,
           parent_tag_id,
           parent_tag_id,
           0 AS depth
    FROM tag_tree
    WHERE parent_tag_id IS NULL

    UNION ALL

    SELECT tag_tree.tag_id,
           tag_tree.parent_tag_id,
           tags_and_their_parents.tag_id,
           tags_and_their_parents.depth + 1
    FROM tag_tree
    INNER JOIN tags_and_their_parents
    ON tag_tree.parent_tag_id = tags_and_their_parents.tag_id
)

SELECT * FROM tags_and_their_parents
ORDER BY depth;

The error:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 18 (Line 18 is the INNER JOIN).

I've followed the exact syntax from another post (How to create a MySQL hierarchical recursive query?), so I cannot figure out where I am going wrong. Any help would be greatly appreciated!

At least it is ANSI SQL syntax compliant. – jarlh Mar 30 '22 at 10:28 — jarlh, Mar 30 '22 at 10:28

score 2 · Accepted Answer · answered Mar 30 '22 at 10:51

Your linked item shows exactly three columns in three parts of its query. But you have three columns here...

WITH RECURSIVE tags_and_their_parents (
    tag_id,
    parent_tag_id,
    depth)
AS ( ...

and four columns here ...

   SELECT tag_id,
           parent_tag_id,
           parent_tag_id,
           0 AS depth

and here

    SELECT tag_tree.tag_id,
           tag_tree.parent_tag_id,
           tags_and_their_parents.tag_id,
           tags_and_their_parents.depth + 1

Removing the extra column fixes your syntax error fiddle.

WITH RECURSIVE tags_and_their_parents (
    tag_id,
    parent_tag_id,
    depth)
AS (
    SELECT tag_id,
           parent_tag_id,
   --        parent_tag_id,
           0 AS depth
    FROM tag_tree
    WHERE parent_tag_id IS NULL

    UNION ALL

    SELECT tag_tree.tag_id,
           tag_tree.parent_tag_id,
    ---       tags_and_their_parents.tag_id,
           tags_and_their_parents.depth + 1
    FROM tag_tree
    INNER JOIN tags_and_their_parents
    ON tag_tree.parent_tag_id = tags_and_their_parents.tag_id

You may want to give that fourth column its own name (ancestor_tag_id maybe?) like this. fiddle

WITH RECURSIVE tags_and_their_parents (
    tag_id,
    parent_tag_id,
    ancestor_tag_id,  -- ADDED
    depth)
AS (
    SELECT tag_id,
           parent_tag_id,
           parent_tag_id ancestor_tag_id,  --CHANGED
           0 AS depth
    FROM tag_tree
    WHERE parent_tag_id IS NULL

    UNION ALL

    SELECT tag_tree.tag_id,
           tag_tree.parent_tag_id,
           tags_and_their_parents.tag_id ancestor_tag_id,  --CHANGED
           tags_and_their_parents.depth + 1
    FROM tag_tree
    INNER JOIN tags_and_their_parents

The syntax error reports for these recursive CTEs are maddening and useless, eh? :-)

Why is this query not working? (MySQL 8.0.28)

1 Answers1