mysql - correlated subquery within CTE is not synchronised

Question

Given the query below,

The issue that I am facing is the following

 WHERE
      initial_categories.id = categories.id 

In the condition above, the categories.id should be synchronised with the outer categories.id.

This works when I use sqlite ( the categories.id is synchronised with the outer value)

BUT, when I use mysql for some reason the categories.id in the condition, takes only the first value from the outer categories.id and it doesn't change after that ( in other words is not synchronised ), and more specifically the categories.id has always the value 1 inside the CTE.

I can't figure out what is the issue. Any thoughts ?

SELECT
   *,
   (
      SELECT
         id 
      FROM
         threads 
      WHERE
         threads.category_id IN 
         (
            WITH recursive recursive_categories AS 
            (
               SELECT
                  initial_categories.id 
               FROM
                  categories AS initial_categories 
               WHERE
                  initial_categories.id = categories.id 
               UNION ALL
               SELECT
                  remaining_categories.id 
               FROM
                  recursive_categories 
                  JOIN
                     categories AS remaining_categories 
                     ON recursive_categories.id = remaining_categories.parent_id 
            )
            SELECT
               id 
            FROM
               recursive_categories 
         )
      ORDER BY
         updated_at DESC limit 1
   )
   AS recently_active_thread_id 
FROM
   "categories" 
WHERE
   "parent_id" IS NULL 
   AND "categories"."group_category_id" IN 
   (
      1,
      2,
      3,
      4,
      5,
      6,
      7,
      8
   )

------ purpose of the query -------

I have the tables

categories

- id
- parent_id
- title

threads

- id
- category_id
- title

Now a category can have sub-categories which are found using the parent_id from the categories table.

A category that has sub-categories is not associated directly with a thread

Only a category that doesn't have sub-categories is associated with threads

To give an example

Computer -> MAC -> MacBook 
Computer -> WINDOWS -> HP

In the example above, only the categories MacBook and HP are associated with threads

What I am trying to achieve with the query below is to find the latest thread among all sub-categories for a given parent category

To continue with the aforementioned example, I want to find the latest thread that is associated indirectly with the Computer category

question from:https://stackoverflow.com/questions/65560267/correlated-subquery-within-cte-is-not-synchronised

Answer 1

If you want to make sure that categories.id will be the outer categories id you can alias the outer categories, something like:

FROM categories AS outer_categories

and the WHERE clause would be:

WHERE initial_categories.id = outer_categories.id 

So change to:

SELECT outer_categories.*,
   (
      SELECT id 
      FROM threads 
      WHERE category_id IN (
            WITH recursive recursive_categories AS (
               SELECT initial_categories.id 
               FROM categories AS initial_categories 
               WHERE initial_categories.id = outer_categories.id 
               UNION ALL
               SELECT remaining_categories.id 
               FROM recursive_categories JOIN categories AS remaining_categories 
               ON recursive_categories.id = remaining_categories.parent_id 
            )
            SELECT id FROM recursive_categories 
         )
      ORDER BY updated_at DESC limit 1
   ) AS recently_active_thread_id 
FROM categories AS outer_categories
WHERE outer_categories.parent_id IS NULL 
  AND outer_categories.group_category_id IN (1, 2, 3, 4, 5, 6, 7, 8)