sql: delete from query hangs

[rharding6373]

Describe the problem

During some randomized testing I encountered a delete query that appears to hang (though it's more likely unnecessarily slow).

To Reproduce

CREATE TABLE t (i INT);
INSERT INTO t(i) SELECT * FROM generate_series(1,100);
DELETE FROM
            t AS tab_1
        USING
            t AS tab_2,
            t AS tab_3,
            t AS tab_4,
            t AS tab_5
        WHERE
            true;

In the explain, it looks like there are a lot of inner joins and full table scans:

root@127.0.0.1:26257/defaultdb> EXPLAIN(OPT) DELETE FROM                                   
                             ->             t AS tab_1                                     
                             ->         USING                                              
                             ->             t AS tab_2,                                    
                             ->             t AS tab_3,                                    
                             ->             t AS tab_4,                                    
                             ->             t AS tab_5                                     
                             ->         WHERE                                              
                             ->             true;                                          
                           info
----------------------------------------------------------
  delete t [as=tab_1]
   └── distinct-on
        ├── inner-join (cross)
        │    ├── inner-join (cross)
        │    │    ├── inner-join (cross)
        │    │    │    ├── inner-join (cross)
        │    │    │    │    ├── scan t [as=tab_4]
        │    │    │    │    ├── scan t [as=tab_5]
        │    │    │    │    └── filters (true)
        │    │    │    ├── scan t [as=tab_3]
        │    │    │    └── filters (true)
        │    │    ├── scan t [as=tab_2]
        │    │    └── filters (true)
        │    ├── scan t [as=tab_1]
        │    └── filters (true)
        └── aggregations
             ├── first-agg
             │    └── tab_2.i
             ├── first-agg
             │    └── tab_2.rowid
             ├── first-agg
             │    └── tab_2.crdb_internal_mvcc_timestamp
             ├── first-agg
             │    └── tab_2.tableoid
             ├── first-agg
             │    └── tab_3.i
             ├── first-agg
             │    └── tab_3.rowid
             ├── first-agg
             │    └── tab_3.crdb_internal_mvcc_timestamp
             ├── first-agg
             │    └── tab_3.tableoid
             ├── first-agg
             │    └── tab_4.i
             ├── first-agg
             │    └── tab_4.rowid
             ├── first-agg
             │    └── tab_4.crdb_internal_mvcc_timestamp
             ├── first-agg
             │    └── tab_4.tableoid
             ├── first-agg
             │    └── tab_5.i
             ├── first-agg
             │    └── tab_5.rowid
             ├── first-agg
             │    └── tab_5.crdb_internal_mvcc_timestamp
             └── first-agg
                  └── tab_5.tableoid

Expected behavior This query seems like it could be optimized, since the query is not actually using any data from any of the USING tables. Then the cross joins and scans would be eliminated from the plan.

Environment: This behavior exists on v23.2+ (didn't test on v23.1)

Jira issue: CRDB-41364

[blathers-crl[bot]]

Hi @rharding6373, please add branch-* labels to identify which branch(es) this C-bug affects.

_{:owl: Hoot! I am a Blathers, a bot for CockroachDB. My owner is dev-inf.}

[rytaft]

Is this blocking anything for with testing, @rharding6373?

[rharding6373]

This kind of query is generated a lot while developing randomized testing for CDC (generating only queries with mutations). I haven't worked on it since filing this issue, and I haven't spent a lot of time looking for a workaround. Which is to say yes, it's blocking, unless I find a workaround this week.

[rytaft]

Looks like this would require writing a new transformation rule to eliminate joins not needed by the DELETE's WHERE clause. I don't think this would be a trivial amount of work, and this doesn't seem like something that too many customers would run into, so unfortunately I don't think we'll be able to prioritize this soon. Any chance you can try to update the test to avoid generating this type of query?

[rharding6373]

I'll work on a workaround like you suggested.