Getting all ancestors/ Super types of a descendent/ Sub type from table in MySQL

Question

I have a table with hierarchical data that looks like this:

ObjectType	ObjectSubType
Everything	Solid
Solid	Solid
Solid	Metal
Metal	Metal
Metal	Iron
Iron	Iron

I'm trying to get all the ancestors/supertypes of any given objectSubType.

so if I want all the parents of Iron, it would look something like this: Iron, Metal, Solid, Everything

I've tried using recursion by referring this

recursion code:

WITH RECURSIVE
  ancestorCategories  AS
  (
    SELECT ObjectType, ObjectSubType, 0 AS depth
      FROM ObjectTypeHierarchyTable
      WHERE ObjectType = "Iron"
    UNION ALL
    SELECT c.ObjectType, c.ObjectSubType, ac.depth - 2
      FROM ancestorCategories AS ac 
        JOIN ObjectTypeHierarchyTable AS c ON ac.ObjectSubType = c.ObjectType
  )
SELECT * FROM ancestorCategories;

This does not seem to work though (possibly missing a termination condition?) - I get this error message when trying it out on db-fiddle:

Recursive query aborted after 1001 iterations. Try increasing @@cte_max_recursion_depth to a larger value.

Any help is appreciated!

Answer 1

Didn't solve it using recursion but managed to use a WHILE loop and a temporary table to get all the ancestors:

DELIMITER $$
CREATE PROCEDURE get_ancestors(IN value VARCHAR(50))
BEGIN
  -- create a temporary table to hold the ancestors, once and truncate it
  CREATE TABLE IF NOT EXISTS ancestors (
    ObjectType VARCHAR(50) NOT NULL
  );
  TRUNCATE TABLE ancestors;
  -- insert the initial value into the ancestors table
  INSERT INTO ancestors (ObjectType) VALUES (value);
  
  -- Loop until no more ancestors are found
  SET @new_rows = 1;
  SET @iterations = 0;
  SET @max_iterations = 100;
  WHILE (@new_rows > 0) AND (@iterations < @max_iterations) DO
      INSERT INTO ancestors (ObjectType)
      SELECT DISTINCT ObjectType
      FROM ObjectTypeHierarchyTable
      WHERE ObjectSubType IN (SELECT ObjectType FROM ancestors) 
      AND ObjectType NOT IN (SELECT ObjectType FROM ancestors);
      
      SET @new_rows = ROW_COUNT();
      SET @iterations = @iterations + 1;
  END WHILE;

  -- select the ancestors from the temporary table
  SELECT * FROM ancestors;
END$$
DELIMITER ;

CALL get_ancestors('Metal');

A procedure is also being used to wrap the WHILE statement because WHILE statements can't be used directly.

Optionally TEMPORARY keyword can be used while creating the ancestors table depending on the type and version of MySQL(it works in PyMySQL which is my use-case).