Infinity loop in recursive sql query-CodePudding

Maybe someone will advise me on how to solve my problem. I have no idea why it happens and how to solve it. In my opinion the reason that my sql code is not working is that it becomes to infinitive loop. I have table:

CREATE TABLE `c_logistics_tran_group3` (
  `ltrgr_id` int(10) UNSIGNED NOT NULL,
  `ltrgr_lagr_id` int(10) UNSIGNED NOT NULL,
  `ltrgr_ltran_id` int(10) UNSIGNED NOT NULL,
  `ltrgr_created` timestamp NOT NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

ALTER TABLE `c_logistics_tran_group3`
  ADD PRIMARY KEY (`ltrgr_id`),
  ADD UNIQUE KEY `ltrgr_lagr_id` (`ltrgr_lagr_id`,`ltrgr_ltran_id`),
  ADD KEY `c_logistics_tran_group3_ibfk_2` (`ltrgr_ltran_id`);

ALTER TABLE `c_logistics_tran_group3`
  MODIFY `ltrgr_id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT;

and data:

INSERT INTO `c_logistics_tran_group3` 
(`ltrgr_id`, `ltrgr_lagr_id`, `ltrgr_ltran_id`, `ltrgr_created`) 
VALUES
(2373, 2154, 2312, '2021-09-09 07:54:55'),
(2378, 2154, 2314, '2021-09-09 08:05:25'),
(2382, 2154, 2318, '2021-09-09 10:37:37'),
(2450, 2154, 2386, '2021-09-17 11:44:58'),
(2375, 2156, 2312, '2021-09-09 07:57:14'),
(2380, 2156, 2316, '2021-09-09 10:25:01'),
(2381, 2156, 2317, '2021-09-09 10:37:07'),
(2451, 2156, 2387, '2021-09-17 11:45:37'),
(2376, 2157, 2312, '2021-09-09 08:03:10'),
(2387, 2157, 2323, '2021-09-10 10:36:15'),
(2388, 2157, 2324, '2021-09-10 10:42:59'),
(2449, 2157, 2385, '2021-09-17 11:41:36'),
(2377, 2158, 2312, '2021-09-09 08:04:35');
COMMIT;

my sql code:

with
  recursive 
      edges as (
          select t1.ltrgr_lagr_id as lagr_id1, t2.ltrgr_lagr_id as lagr_id2
          from c_logistics_tran_group3 t1 
          inner join c_logistics_tran_group3 t2 on t2.ltrgr_ltran_id = t1.ltrgr_ltran_id
          where 1 = 1
          and t1.ltrgr_lagr_id in(2154, 2156, 2157, 2158) 
          and t2.ltrgr_lagr_id in(2154, 2156, 2157, 2158)
      ),
      cte as (
          select lagr_id1, lagr_id2, concat(lagr_id1, ',', lagr_id2) as visited
          from edges
          union all
          select c.lagr_id1, e.lagr_id2, concat(c.visited, ',', e.lagr_id2)
          from cte c
          inner join edges e on e.lagr_id1 = c.lagr_id2
          where not find_in_set(e.lagr_id2, c.visited)
      )
select * from cte;

This SQL code performs the task described here: Select keys connected by its values

If I remove from the list any lagr_id every thing works fine. For eg.:

          and t1.ltrgr_lagr_id in(2154, 2156, 2157) 
          and t2.ltrgr_lagr_id in(2154, 2156, 2157)

When thee are all 4 lagr_id in the list my sql code hangs up. Only server restarting helps :( Have someone any ideas on how to solve this problem? How to avoid infinity loop in my sql code? MariaDB version 10.5.12

CodePudding user response：

It is not an infinite loop, but you are running out of some resource.

You can improve your query this way:

I add DISTINCT clause in edges subquery to avoid duplicated rows.
I add CASE statement in cte subquery to avoid set twice the same value (when lagr_id1 = lagr_id2) in visited column.
I replace UNION ALL by UNION to avoid duplicated rows.

WITH
  RECURSIVE 
      edges AS (
          SELECT DISTINCT t1.ltrgr_lagr_id AS lagr_id1, t2.ltrgr_lagr_id AS lagr_id2
          FROM c_logistics_tran_group3 t1 
          INNER JOIN c_logistics_tran_group3 t2 ON t2.ltrgr_ltran_id = t1.ltrgr_ltran_id
          WHERE t1.ltrgr_lagr_id in(2154, 2156, 2157, 2158) 
                AND t2.ltrgr_lagr_id in(2154, 2156, 2157, 2158)
      ),

      cte AS (
          SELECT lagr_id1, lagr_id2, CASE WHEN lagr_id1 = lagr_id2 THEN lagr_id1 ELSE concat(lagr_id1, ',', lagr_id2) END AS visited
          FROM edges
          
          UNION
          
          SELECT c.lagr_id1, e.lagr_id2, concat(c.visited, ',', e.lagr_id2)
          FROM cte c
          INNER JOIN edges e ON e.lagr_id1 = c.lagr_id2
          WHERE NOT find_in_set(e.lagr_id2, c.visited)
      )

SELECT * FROM cte;

The number of rows decreased considerably, but I think my query can be improve, for example visited value '2154,2156,2158,2157' is the same as '2154,2156,2157,2158', because the order of the combination is not important in the result, so the query should discard one of them.