Recursive CTE fails when executed using JDBC driver

[knutwannheden]

The following correct recursive MySQL CTE query fails when executed against Dolt:

WITH recursive Numbers(n) AS
          (
              SELECT 1
              UNION ALL
              SELECT n + 1
              FROM Numbers
              WHERE n+1 <= 10
          )
 SELECT n
 FROM Numbers;

For reference see https://www.db-fiddle.com/f/tZTFJfXi7U8RnKYKaiLKUF/0

The error being reported is:

2022-08-23T20:45:06+02:00 WARN [conn 1] error running query {connectTime=2022-08-23T20:45:06+02:00, connectionDb=lobbywatch_public, error=recursive cte top-level query must be a union; found: SubqueryAlias(Numbers)
 └─ Limit(501)
     └─ Union
         ├─ Limit(501)
         │   └─ Project(1)
         │       └─ UnresolvedTable(dual)
         └─ Limit(501)
             └─ Project((n + 1))
                 └─ Filter((n + 1) <= 10)
                     └─ UnresolvedTable(Numbers)

[max-hoffman]

Can you confirm that you pasted the query associated with the plan? The query passes when i try to reproduce the error, and also the plan has a limit it. It looks like we do fail to support limit clauses in recursive CTEs, an overlooked top-level node, which is a bug we should fix:

> WITH recursive Numbers(n) AS
          (
              SELECT 1
              UNION ALL
              SELECT n + 1
              FROM Numbers
              WHERE n+1 <= 10
              LIMIT 5
          )
 SELECT n
 FROM Numbers;
 
 recursive cte top-level query must be a union; found: SubqueryAlias(Numbers)
 └─ Limit(5)
     └─ Union
         ├─ Project(1)
         │   └─ UnresolvedTable(dual)
         └─ Project((n + 1))
             └─ Filter((n + 1) <= 10)
                 └─ UnresolvedTable(Numbers)

[knutwannheden]

I just tested it again and I can confirm that I am using the "correct" query (can also be seen in log message):

❯ dolt sql-server --u root
Starting server with Config HP="localhost:3306"|T="28800000"|R="false"|L="info"
2022-08-26T15:16:19+02:00 INFO [conn 1] NewConnection {DisableClientMultiStatements=false}
2022-08-26T15:16:19+02:00 WARN [conn 1] unable to load "mysql" as a database revision {connectTime=2022-08-26T15:16:19+02:00, connectionDb=mysql, query=/* ApplicationName=IntelliJ IDEA 2022.2.1 */ select database()}
2022-08-26T15:16:19+02:00 WARN [conn 1] error running query {connectTime=2022-08-26T15:16:19+02:00, connectionDb=mysql, error=recursive cte top-level query must be a union; found: SubqueryAlias(Numbers)
 └─ Limit(501)
     └─ Union
         ├─ Limit(501)
         │   └─ Project(1)
         │       └─ UnresolvedTable(dual)
         └─ Limit(501)
             └─ Project((n + 1))
                 └─ Filter((n + 1) <= 10)
                     └─ UnresolvedTable(Numbers)
, query=/* ApplicationName=IntelliJ IDEA 2022.2.1 */ WITH recursive Numbers(n) AS ( SELECT 1 UNION ALL SELECT n + 1 FROM Numbers WHERE n+1 <= 10 ) SELECT n FROM Numbers}

What is interesting is that the same query works fine when I don't use IDEA (with the MySQL JDBC driver):

❯ dolt sql -q "
WITH recursive Numbers(n) AS
                   (
                       SELECT 1
                       UNION ALL
                       SELECT n + 1
                       FROM Numbers
                       WHERE n+1 <= 10
                   )
SELECT n
FROM Numbers
"
+----+
| n  |
+----+
| 1  |
| 2  |
| 3  |
| 4  |
| 5  |
| 6  |
| 7  |
| 8  |
| 9  |
| 10 |
+----+

I hope that helps.

[max-hoffman]

Thanks, it makes sense that the IDEA would paginate.

The fix for this will to remove the restrictions I placed on root nodes for recursive CTEs. One approach will allow top-level LIMIT, FILTER, ORDER_BY, HAVING clauses, and others I am missing. Another approach would disallow specific top-level queries we cannot easily resolve today, like WINDOW, SUBQUERY, CTE, etc.

[max-hoffman]

We also have a reported issue where outer limits fail to resolve, which may or may not be related

dolt_db> with recursive a as (select 1 union select 2) select * from a union select * from a limit 50;
table not found: a

[dphil]

We also have a reported issue where outer limits fail to resolve, which may or may not be related

dolt_db> with recursive a as (select 1 union select 2) select * from a union select * from a limit 50;
table not found: a

Issue filed here: #4230

[max-hoffman]

This PR moves us towards arbitrary recursive CTEs clause support.
dolthub/go-mysql-server#1226

[timsehn]

We think this is resolved with 0.41.0. Open a new issue if you have any other troubles with CTEs.

[fulghum]

Closing this issue since this should be resolved with Max's CTE improvements. Feel free to reopen if this query is still giving you problems with the latest versions of Dolt, or cut us a new issue if you find anything else strange and we'll be happy to investigate.