Repository: SeaQL/sea-query (language: rust)

Issue:
Multiple CTEs should be allowed in recursive WithQuery
## Description

Per https://github.com/SeaQL/sea-query/blob/master/src/backend/query_builder.rs#L780 when you do a recursive with you are only allowed to have one CTE.

In sqlite at least (and I'd expect other dbs as well) you can have any number of CTEs in the with query, per https://sqlite.org/lang_with.html .  Especially see example 3.5 which has 4 recursive CTEs and one non-recursive CTE (`a`).

I'm not sure what the impetus for that line was, but would dropping it be reasonable? Are there other parts of the code that rely on it having only one CTE?
Multiple CTEs should be allowed in recursive WithQuery
## Description

Per https://github.com/SeaQL/sea-query/blob/master/src/backend/query_builder.rs#L780 when you do a recursive with you are only allowed to have one CTE.

In sqlite at least (and I'd expect other dbs as well) you can have any number of CTEs in the with query, per https://sqlite.org/lang_with.html .  Especially see example 3.5 which has 4 recursive CTEs and one non-recursive CTE (`a`).

I'm not sure what the impetus for that line was, but would dropping it be reasonable? Are there other parts of the code that rely on it having only one CTE?


Discussion / hints:

Fix patch:
diff --git a/src/backend/query_builder.rs b/src/backend/query_builder.rs
index 7bf29b55..ae7358b1 100644
--- a/src/backend/query_builder.rs
+++ b/src/backend/query_builder.rs
@@ -777,15 +777,6 @@ pub trait QueryBuilder:
             "Cannot build a with query that has no common table expression!"
         );
 
-        if with_clause.recursive {
-            assert_eq!(
-                with_clause.cte_expressions.len(),
-                1,
-                "Cannot build a recursive query with more than one common table! \
-                A recursive with query must have a single cte inside it that has a union query of \
-                two queries!"
-            );
-        }
         for cte in &with_clause.cte_expressions {
             if !cte_first {
                 write!(sql, ", ").unwrap();


Test patch:
diff --git a/tests/sqlite/query.rs b/tests/sqlite/query.rs
index 9a9c8fcf..48a6f90b 100644
--- a/tests/sqlite/query.rs
+++ b/tests/sqlite/query.rs
@@ -1792,3 +1792,47 @@ fn sub_query_with_fn() {
         r#"SELECT ARRAY((SELECT * FROM "character"))"#
     );
 }
+
+#[test]
+fn recursive_with_multiple_ctes() {
+    let sub_select1 = Query::select()
+        .column(Asterisk)
+        .from(Char::Table)
+        .to_owned();
+    let sub_select1_name = SeaRc::new(Alias::new("sub1"));
+    let mut sub_select1_cte = CommonTableExpression::new();
+    sub_select1_cte.table_name(sub_select1_name.clone());
+    sub_select1_cte.column(SeaRc::new(Alias::new("a")));
+    sub_select1_cte.query(sub_select1);
+    let sub_select2 = Query::select()
+        .column(Asterisk)
+        .from(Char::Table)
+        .to_owned();
+    let sub_select2_name = SeaRc::new(Alias::new("sub2"));
+    let mut sub_select2_cte = CommonTableExpression::new();
+    sub_select2_cte.table_name(sub_select2_name.clone());
+    sub_select2_cte.column(SeaRc::new(Alias::new("b")));
+    sub_select2_cte.query(sub_select2);
+
+    let mut with = WithClause::new();
+    with.recursive(true)
+        .cte(sub_select1_cte)
+        .cte(sub_select2_cte);
+
+    let mut main_sel2 = Query::select();
+    main_sel2
+        .expr(Expr::col(Asterisk))
+        .from(TableRef::Table(sub_select2_name));
+    let mut main_sel1 = Query::select();
+    main_sel1
+        .expr(Expr::col(Asterisk))
+        .from(TableRef::Table(sub_select1_name))
+        .union(UnionType::All, main_sel2);
+
+    let query = with.query(main_sel1);
+
+    assert_eq!(
+        query.to_string(SqliteQueryBuilder),
+        r#"WITH RECURSIVE "sub1" ("a") AS (SELECT * FROM "character") , "sub2" ("b") AS (SELECT * FROM "character") SELECT * FROM "sub1" UNION ALL SELECT * FROM "sub2""#
+    );
+}