sql: nested square brackets are not sufficiently spooled

[justinj]

When we have a [...] mutation, we generally insert a spoolNode on top of it so that its results are completely consumed regardless of whether all of its results are used. However, we have some logic around eliminating spools we deem unnecessary. There are cases in which this logic erroneously eliminates necessary spools. Consider the following interaction:

root@127.0.0.1:52697/defaultdb> create table x (a int);
CREATE TABLE

root@127.0.0.1:52697/defaultdb> EXPLAIN SELECT * FROM [INSERT INTO x (SELECT * FROM [INSERT INTO x (SELECT * FROM generate_series(1, 1000000)) RETURNING a] LIMIT 0) RETURNING a] LIMIT 0;
                             tree                            | field | description
+------------------------------------------------------------+-------+-------------+
  limit                                                      |       |
   └── spool                                                 |       |
        └── render                                           |       |
             └── run                                         |       |
                  └── insert                                 |       |
                       │                                     | into  | x(a, rowid)
                       └── limit                             |       |
                            └── render                       |       |
                                 └── run                     |       |
                                      └── insert             |       |
                                           │                 | into  | x(a, rowid)
                                           └── project set   |       |
                                                └── emptyrow |       |
(13 rows)

root@127.0.0.1:52697/defaultdb> SELECT * FROM [INSERT INTO x (SELECT * FROM [INSERT INTO x (SELECT * FROM generate_series(1, 1000000)) RETURNING a] LIMIT 0) RETURNING a] LIMIT 0;
  a
+---+
(0 rows)

root@127.0.0.1:52697/defaultdb> select count(*) from x;
  count
+-------+
  10000
(1 row)

Since there was no spool on top of the inner INSERT, not all of the rows were inserted. Thanks to the run operator, which performs some batching, this problem generally doesn't manifest for small insertions.

[jordanlewis]

Hey @justinj, this is something that the optimizer can support today using spoolNode, right?

[justinj]

This shouldn't be a problem with the optimizer, given the limited scope of the problem and the switch over to the optimizer we can safely close this.