Push LIMIT into scan of outer table in uncorrelated subquery

[msirek]

In the following test case, the LIMIT could be pushed into the scan of t1 because the EXISTS subquery executes as a totally separate query block with no references to outer table columns. If the LIMIT were pushed, it would enable the locality optimized scans in #64862.

CREATE TABLE t1 (a INT);
CREATE TABLE t2 (a INT);
EXPLAIN SELECT * FROM t1 WHERE EXISTS (SELECT * FROM t2) LIMIT 1;
                                           info
------------------------------------------------------------------------------------------
  distribution: full
  vectorized: true

  • root
  │
  ├── • limit
  │   │ estimated row count: 0
  │   │ count: 1
  │   │
  │   └── • filter
  │       │ estimated row count: 0
  │       │ filter: @S1
  │       │
  │       └── • scan
  │             estimated row count: 1 (100% of the table; stats collected 1 minute ago)
  │             table: t1@t1_pkey
  │             spans: FULL SCAN
  │
  └── • subquery
      │ id: @S1
      │ original sql: EXISTS (SELECT * FROM t2)
      │ exec mode: exists
      │
      └── • scan
            estimated row count: 1 (100% of the table; stats collected 1 minute ago)
            table: t2@t2_pkey
            spans: LIMITED SCAN
            limit: 1

Jira issue: CRDB-12547

[RaduBerinde]

I think addressing #51820 would fix this case - we would reoptimize the main query with the result of the subquery.

[mgartner]

Closing as a duplicate of #51820. Something like #93829 could also solve this, if that was deemed to be a good solution for #51820.