Recursive CTE in SQLite

Question

I'm trying to learn how to use common table expressions as introduced in SQL 1999 to solve a generic bill of materials problem. I assume given the following tables:

create table part(id int, name string, price int)
create table assembly(part_id int, subpart_id int, quantity int)

A row in the assembly table represents the fact that a part contains several instances of a subpart, which may in turn have its own subparts, and so on. The assembly table represents a structure, a tree, with a given part_id as its root.

I'd like to compute the total number of occurrences of each part in the structure.

Here is my attempt using common table expressions:

WITH RECURSIVE bom(part_id, component_id, total) AS
    (SELECT id, id, 1 FROM part 
        UNION
     SELECT assembly.part_id, bom.component_id, sum(assembly.quantity * bom.total)
     FROM assembly, bom
     WHERE assembly.subpart_id = bom.part_id
     GROUP BY assembly.part_id, bom.component_id)
SELECT component_id, total FROM bom WHERE part_id = root;

Under SQLite 3.9.1 on Mac OS X 10.9.5, I get the following error message: Error: near line 78: recursive aggregate queries not supported

So, is this an SQLite restriction, or is it an SQL standard restriction?

In either case, can anyone advise me how to rewrite this solution to accumulate more information in the common table expression and then perform the aggregate in the query that uses the resulting table?

Many thanks, Rodney

Answer 1

Here is a simple way to solve the problem by moving the aggregation to the outer query:

WITH RECURSIVE bom(part_id, component_id, total) AS
    (SELECT id, id, 1 FROM part 
        UNION ALL
     SELECT assembly.part_id, bom.component_id, assembly.quantity * bom.total
     FROM assembly, bom
     WHERE assembly.subpart_id = bom.part_id)
SELECT component_id, SUM(total) FROM bom 
WHERE part_id = root
GROUP BY component_id;

This solution requires more space and potentially more time than the initial attempt.