MySQL 8.0: implementing Recursive CTEs Not Returning Expected Results
Quick question that's been bugging me - I've been struggling with this for a few days now and could really use some help. I'm currently working with MySQL 8.0 and trying to implement a recursive Common Table Expression (CTE) to generate a hierarchical structure from a `categories` table. The structure is supposed to return all parent and child categories based on a `parent_id` field. However, I'm not getting the expected results; the query is returning an empty set when I believe it should return several rows. Here’s the CTE I’ve written: ```sql WITH RECURSIVE category_tree AS ( SELECT id, name, parent_id FROM categories WHERE parent_id IS NULL UNION ALL SELECT c.id, c.name, c.parent_id FROM categories c INNER JOIN category_tree ct ON ct.id = c.parent_id ) SELECT * FROM category_tree; ``` I’ve verified that there are rows in the `categories` table, and I can confirm that some of them have `NULL` as their `parent_id`. The table structure looks like this: ```sql CREATE TABLE categories ( id INT PRIMARY KEY, name VARCHAR(255), parent_id INT, FOREIGN KEY (parent_id) REFERENCES categories(id) ); ``` The question is that when I run this recursive query, it returns no results, even though I expect to retrieve all top-level categories and their descendants. I’ve tried adding a base case to check for specific IDs directly, and that returns results, but the recursive part seems to be failing. I've also checked for circular references, but there are none in the data. As a sanity check, I executed a simple query: ```sql SELECT * FROM categories; ``` This returns data as expected. My user privileges should also allow executing CTEs. I'm using the default configuration for MySQL 8.0 without any specific optimizations that I know of. Any ideas on what might be causing the recursive part of my CTE to not return results? Am I missing something conceptually here? For context: I'm using Sql on macOS. This issue appeared after updating to Sql LTS.