Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Fixed
-
10.3.1, 10.2(EOL)
-
None
-
CentOS Linux release 7.4.1708 (Core)
10.3.1-MariaDB
Description
I have two seperate recursive queries that run up and down a tree of nodes to collect information.
For visualization you can compare this to a binary tree, pick a random node in the tree, for this node you get all parents (grandparents etc.) and get all children (grandchildren etc.)
For this I run two queries, one for upwards and one for downwards.
When I want to show the results for this, I can either show one or show the other, but when trying to UNION the results the server stops responding. The only way to use the server again after using this query is to reset the mysql server using 'mysqld service restart'.
'SELECT * FROM temp' shows the correct results for that table.
All code used:
WITH recursive temp as ( |
SELECT current_template, current_iteration, parent_template, parent_iteration, tkey, val |
FROM templatevalues |
WHERE tkey='linnumber' AND val='1' AND guid='ffb7e0cd-5dff-4b83-be88-ebf66d5557be' -- this is the starting point |
UNION ALL |
SELECT c.current_template, c.current_iteration, c.parent_template, c.parent_iteration, c.tkey, c.val |
FROM templatevalues c |
JOIN temp p ON p.parent_template = c.current_template AND p.parent_iteration = c.current_iteration AND guid='ffb7e0cd-5dff-4b83-be88-ebf66d5557be' -- this is the recursion |
),
|
temp2 as ( |
SELECT current_template, current_iteration, parent_template, parent_iteration, tkey, val |
FROM templatevalues |
WHERE tkey='linnumber' AND val='1' AND guid='ffb7e0cd-5dff-4b83-be88-ebf66d5557be' -- this is the starting point |
UNION ALL |
SELECT c.current_template, c.current_iteration, c.parent_template, c.parent_iteration, c.tkey, c.val |
FROM templatevalues c |
JOIN temp2 p ON c.parent_template = p.current_template AND c.parent_iteration = p.current_iteration AND guid='ffb7e0cd-5dff-4b83-be88-ebf66d5557be' -- this is the recursion |
)
|
SELECT * |
FROM temp UNION ALL (SELECT * FROM temp2); |
I included a file with a dump of a few lines of the database I used. Use this to test on other environments. Even with these two lines in the table it still times out, which should not happen.
Attachments
Issue Links
- relates to
-
-
- Closed
-