I am attempting to use the recursive common table expressions to explode a bill of materials, however I am running into a problem where the MariaDB server hangs and eventually fills up the disk if left unchecked, even though there are not any circular references in this expression.
See the following example:
1. Table to hold the bom
2. Add some dummy entries that represent a part/child product structure
3. Attempt to run a recursive common table expression with a path using cast to avoid data truncation. In this query the Path column is created as TEXT because of the CAST to CHAR(513), it never seems to complete and eventually fills the disk.
4. Attempt to run a recursive common table expression with a path using cast to avoid data truncation. This query the Path column is created as VARCHAR(512) because of the CAST to CHAR(512) and DOES complete.
In this particular example the query just continues executing, even though there aren't any circular references. As I mention in the reproduction steps, I believe I have narrowed down part of the issue to the datatype that the Path column eventually becomes. When you CAST the path to anything over CHAR(512) the column type switches from VARCHAR to TEXT, once that happens the never completes. However if you cast to CHAR(512) and below you get the expected result and the Path column is VARCHAR.
In my particular use case, the column must be text as the path gets very long and is key to not only detecting circular references, but also drawing the indented version of the bill of materials.