MariaDB 10.6

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE product_groups
    (`id` int, `parent_id` varchar(4))
;
    
INSERT INTO product_groups
    (`id`, `parent_id`)
VALUES
    (1, NULL),
    (2, '1'),
    (3, '2'),
    (4, '3'),
    (5, NULL),
    (6, '5'),
    (7, '6'),
    (8, '7')
;

Records: 8  Duplicates: 0  Warnings: 0


CREATE TABLE specification_to_group
    (`id` int, `product_group_id` int, `specification_id` int)
;
    
INSERT INTO specification_to_group
    (`id`, `product_group_id`, `specification_id`)
VALUES
    (1, 1, 1),
    (2, 1, 2),
    (3, 4, 10),
    (4, 4, 11),
    (5, 4, 12),
    (6, 5, 1),
    (7, 5, 3),
    (8, 7, 12)
;

Records: 8  Duplicates: 0  Warnings: 0


WITH recursive  CTE AS(
SELECT `id`, `parent_id`, ROW_NUMBER() OVER (ORDER By id) rn, 1 lvl
FROM  product_groups
WHERE `parent_id` IS NULL
  UNION ALL

  SELECT P1.id, P1.parent_id , M.rn, M.lvl+1 AS LVL
  FROM product_groups P1  
  INNER JOIN CTE M
  ON M.id = P1.parent_id),
  getmax as(
SELECT `id`, MAX(id) OVER(PARTITION BY rn) m FROM CTE
ORDER BY rn)
SELECT sp.id, g.m, sp.`specification_id`
  FROM specification_to_group sp JOIN getmax g ON sp.`product_group_id` = g.id
ORDER By sp.id


id
m
specification_id


1
4
1

2
4
2

3
4
10

4
4
11

5
4
12

6
8
1

7
8
3

8
8
12