Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Fixed
-
10.2.2
-
None
-
Centos 7
Description
Using the following simple table and dataset from the extensive CTE examples here: https://inviqa.com/blog/graphs-database-sql-meets-social-network
DROP TABLE IF EXISTS `edges`; |
CREATE TABLE `edges` ( |
`a` int(10) unsigned NOT NULL, |
`b` int(10) unsigned NOT NULL, |
PRIMARY KEY (`a`,`b`), |
KEY `b` (`b`) |
) ENGINE=InnoDB DEFAULT CHARSET=utf8; |
|
|
INSERT INTO `edges` VALUES (1,3),(2,1),(2,4),(3,4),(3,5),(3,6),(4,7),(5,1),(5,6),(6,1); |
|
|
DROP TABLE IF EXISTS `edges2`; |
CREATE VIEW edges2 (a, b) AS SELECT a, b FROM edges UNION ALL SELECT b, a FROM edges; |
When trying any of the examples the server either crashes hard, or the query runs but aborts with ERROR 1114 (HY000): The table '/tmp/#sql_4b24_1' is full.
Some of the queries I ran are:
Causes a "table full":
WITH RECURSIVE transitive_closure(a, b, distance, path_string) AS |
( SELECT a, b, 1 AS distance, |
a || '.' || b || '.' AS path_string |
FROM edges |
|
UNION ALL |
|
SELECT tc.a, e.b, tc.distance + 1, |
tc.path_string || e.b || '.' AS path_string |
FROM edges AS e |
JOIN transitive_closure AS tc |
ON e.a = tc.b |
WHERE tc.path_string NOT LIKE '%' || e.b || '.%' |
)
|
SELECT * FROM transitive_closure |
ORDER BY a, b, distance; |
WITH RECURSIVE transitive_closure(a, b, distance, path_string) AS |
( SELECT a, b, 1 AS distance, |
a || '.' || b || '.' AS path_string |
FROM edges |
WHERE a = 1 -- source |
|
UNION ALL |
|
SELECT tc.a, e.b, tc.distance + 1, |
tc.path_string || e.b || '.' AS path_string |
FROM edges AS e |
JOIN transitive_closure AS tc ON e.a = tc.b |
WHERE tc.path_string NOT LIKE '%' || e.b || '.%' |
)
|
SELECT * FROM transitive_closure |
WHERE b=6 -- destination |
ORDER BY a, b, distance; |
Causes a hard server crash (suspect due to CTE against view):
WITH RECURSIVE transitive_closure(a, b, distance, path_string) AS |
( SELECT a, b, 1 AS distance, |
a || '.' || b || '.' AS path_string |
FROM edges2 |
|
UNION ALL |
|
SELECT tc.a, e.b, tc.distance + 1, |
tc.path_string || e.b || '.' AS path_string |
FROM edges2 AS e |
JOIN transitive_closure AS tc ON e.a = tc.b |
WHERE tc.path_string NOT LIKE '%' || e.b || '.%' |
)
|
SELECT * FROM transitive_closure |
ORDER BY a, b, distance; |
WITH RECURSIVE transitive_closure(a, b, distance, path_string) |
AS
|
( SELECT a, b, 1 AS distance, |
a || '.' || b || '.' AS path_string |
FROM edges2 |
|
UNION ALL |
|
SELECT tc.a, e.b, tc.distance + 1, |
tc.path_string || e.b || '.' AS path_string |
FROM edges2 AS e |
JOIN transitive_closure AS tc ON e.a = tc.b |
WHERE tc.path_string NOT LIKE '%' || e.b || '.%' |
)
|
SELECT a, b, min(distance) AS dist FROM transitive_closure |
--WHERE a = 1 AND b=6
|
GROUP BY a, b |
ORDER BY a, dist, b; |
I expect all the queries to behave as per the examples given in the article.