MariaDB SQL Recursive query as subquery

Question

I have a data structure where:

a vendor has a contingent (multiple vendors can use the same contingent, that's why it is different tables)
A contingent can also have a parent contingent (unlimited in depth)
If one of the contingents in the chain has nothing left, the vendor is considered to no contingent left
If the remains are NULL, there is no limit set, and it counts as contingent available

A redesign of the data structure to solve this, sadly, is not possible.

The recursive part and all contigent-avalible rules cann be coded like this:

WITH RECURSIVE rec_contigents as (
  SELECT 
    id, IFNULL(daily_remain, 1) > 0 as row_contigent, parent_id
  FROM contingents
  Where id = 1
  UNION
  select pc.id, IFNULL(pc.daily_remain, 1) > 0 as row_contigent, pc.parent_id
  FROM contingents pc, rec_contigents c
  where pc.id = c.parent_id)
select id, SUM(row_contigent) = count(row_contigent) as contigent from rec_contigents;

I can easily get all my vendors, and then query the above for them one by one. Though, I thought let's reduce Database calls, and use the above as a subquery. (simplified, in reality this contains multiple JOINS on Vendor and some where-clauses)

SELECT
  v.vendor_id,
  (
        WITH RECURSIVE rec_contigents as (
            SELECT 
            id, IFNULL(daily_remain, 1) > 0 as row_contigent, parent_id
            FROM contingents
            Where id = v.contingent_id
            UNION
            select pc.id, IFNULL(pc.daily_remain, 1) > 0 as row_contigent, pc.parent_id
            FROM contingents pc, rec_contigents c
            where pc.id = c.parent_id
        )
        select SUM(row_contigent) = count(row_contigent) as contigent from rec_contigents
    ) as contingent_left
  FROM vendors AS v

This results in the error: Unknown column 'v.contingent_id' in 'where clause'.

Following example data:


CREATE TABLE `contingents` (
  `id` int(11) NOT NULL,
  `daily` int(11) DEFAULT NULL,
  `daily_remain` int(11) DEFAULT NULL,
  `parent_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) 

INSERT INTO `contingents` (`id`, `daily`, `daily_remain`, `parent_id`)
VALUES
    (1,10,10,NULL),
    (2,10,5,1),
    (3,10,NULL,2),
    (4,10,0,NULL),
    (5,10,10,4);

CREATE TABLE `vendors` (
  `id` int(11) NOT NULL,
  `contingent_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
)

INSERT INTO `vendors` (`id`, `contingent_id`)
VALUES
    (1,3),
    (2,5);

Expected outcome:

+-----------+-----------------+
| vendor_id | contingent_left |
+-----------+-----------------+
|     1     |        1        |
|     2     |        0        |
+-----------+-----------------+

Vendor 1: Has Contingent, because he and all his parents all have Continent left (or have unlimited contingent). Vendor 2: Does not have continent, because its parent does not have continent left.

I have already looked at the following posts, though these solutions don't work for me:

Mysql Mariadb recursive query - uses a JOIN on the recursive table, that I can't use as I first need to sum it
SQL Recursive query as subquery - says to remove the Where id = v.contingent_id which results in to much data and the select SUM(row_contigent) = count(row_contigent) as contigent from rec_contigents resulting in the wrong result

CTE shouldn't be a part of **sub**query - move it to the whole query. And apply `Where id = v.contingent_id` in outer query. — Akina, Mar 01 '23 at 13:13
[Tips for asking a good Structured Query Language (SQL) question](//meta.stackoverflow.com/questions/271055), #5 and #3. — Akina, Mar 01 '23 at 13:15
See [How do comment replies work?](https://meta.stackexchange.com/q/43019/266284) to learn to use `@x` to notify 1 non-sole non-poster commenter `x` per comment about that comment. Posters, sole commenters & followers of posts always get notified. Without `@` other commenters get no notification. — philipxy, Mar 03 '23 at 03:01
Debug questions require a [mre]--cut & paste & runnable code including initialization; desired & actual output (including verbatim error messages); tags & versions; clear specification & explanation. For SQL include DDL & tabular initialization code. For debug that includes the least code you can give that is code that you show is OK extended by code that you show is not OK. [ask] [Help] When you get a result you don't expect, pause your overall goal, chop to the 1st subexpression with unexpected result & say what you expected & why, justified by documentation. (Debugging fundamental.) — philipxy, Mar 03 '23 at 03:11
There is no question in this post. PS MySQL<>MariaDB. Which? Also that query is not minimal. — philipxy, Mar 03 '23 at 04:11
"`SELECT v.vendor_id, ( ... ) as contingent_left FROM vendors AS v`" There is no vendor_id column in vendors/v. Again: *A [mre] is code you cut & pasted & ran from your post.* — philipxy, Mar 03 '23 at 04:15
If you replace v.vendor_id by v.id, your query works in MySQL 8.0 & PostgreSQL 10 but same old problem in MariaDB 10.6. So seems like a bug. PS You have a bunch of contigents that should be contingents. — philipxy, Mar 03 '23 at 07:31

score 0 · Accepted Answer · answered Mar 02 '23 at 06:17

WITH RECURSIVE
cte AS (
  SELECT ven.id,
         CASE WHEN con.daily_remain = 0 THEN 1 ELSE 0 END have_zero,
         con.parent_id
  FROM vendors ven
  JOIN contingents con ON ven.contingent_id = con.id
  UNION ALL
  SELECT cte.id, 
         CASE WHEN con.daily_remain = 0 THEN 1 ELSE 0 END,
         con.parent_id
  FROM cte
  JOIN contingents con ON cte.parent_id = con.id
  WHERE NOT have_zero
)
-- SELECT * FROM cte
SELECT id, NOT MAX(have_zero) contingent_left
FROM cte
GROUP BY id

https://dbfiddle.uk/wVJDsp4V

MariaDB SQL Recursive query as subquery

1 Answers1