Inline trivial subquery

[findepi]

Consider:

presto> EXPLAIN (TYPE LOGICAL) SELECT * FROM tpch.tiny.nation WHERE nationkey = (SELECT 3);
                                                               Query Plan
----------------------------------------------------------------------------------------------------------------------------------------
 Output[nationkey, name, regionkey, comment]
 │   Layout: [nationkey:bigint, name:varchar(25), regionkey:bigint, comment:varchar(152)]
 │   Estimates: {rows: 1 (109B), cpu: 5.55k, memory: 0B, network: 109B}
 └─ RemoteExchange[GATHER]
    │   Layout: [nationkey:bigint, name:varchar(25), regionkey:bigint, comment:varchar(152)]
    │   Estimates: {rows: 1 (109B), cpu: 5.55k, memory: 0B, network: 109B}
    └─ CrossJoin
       │   Layout: [nationkey:bigint, name:varchar(25), regionkey:bigint, comment:varchar(152)]
       │   Estimates: {rows: 1 (109B), cpu: 5.55k, memory: 0B, network: 0B}
       │   Distribution: REPLICATED
       ├─ ScanFilter[table = tpch:nation:sf0.01, filterPredicate = ("nationkey" = BIGINT '3')]
       │      Layout: [nationkey:bigint, name:varchar(25), regionkey:bigint, comment:varchar(152)]
       │      Estimates: {rows: 25 (2.67kB), cpu: 2.67k, memory: 0B, network: 0B}/{rows: 1 (109B), cpu: 5.34k, memory: 0B, network: 0B}
       │      nationkey := tpch:nationkey
       │      name := tpch:name
       │      regionkey := tpch:regionkey
       │      comment := tpch:comment
       └─ LocalExchange[SINGLE] ()
          │   Layout: []
          │   Estimates: {rows: 1 (0B), cpu: 0, memory: 0B, network: 0B}
          └─ RemoteExchange[REPLICATE]
             │   Layout: []
             │   Estimates: {rows: 1 (0B), cpu: 0, memory: 0B, network: 0B}
             └─ Values
                    Layout: []
                    Estimates: {rows: 1 (0B), cpu: 0, memory: 0B, network: 0B}
                    ()

there should be no join, the subquery should get inlined.

[martint]

The RemoveRedundantCrossJoin rule should take care of that, but it's probably not firing in the right place.

[findepi]

RemoveRedundantCrossJoin can remove scalar subquery when it has no projections.

[martint]

Exactly. That's the case in the example above. I tried adding the rule somewhere after

                new IterativeOptimizer(
                        ruleStats,
                        statsCalculator,
                        estimatedExchangesCostCalculator,
                        ImmutableSet.<Rule<?>>builder()
                                // add UnaliasSymbolReferences when it's ported
                                .add(new RemoveRedundantIdentityProjections())
                                .addAll(GatherAndMergeWindows.rules())
                                .build()),

and it does get optimized.

[findepi]

With 356

trino:tiny> EXPLAIN (TYPE LOGICAL) SELECT * FROM tpch.tiny.nation WHERE nationkey = (SELECT 3);
                                                             Query Plan
-------------------------------------------------------------------------------------------------------------------------------------
 Output[nationkey, name, regionkey, comment]
 │   Layout: [nationkey:bigint, name:varchar(25), regionkey:bigint, comment:varchar(152)]
 │   Estimates: {rows: 1 (109B), cpu: 5.34k, memory: 0B, network: 109B}
 └─ RemoteExchange[GATHER]
    │   Layout: [nationkey:bigint, name:varchar(25), regionkey:bigint, comment:varchar(152)]
    │   Estimates: {rows: 1 (109B), cpu: 5.34k, memory: 0B, network: 109B}
    └─ ScanFilter[table = tpch:nation:sf0.01, filterPredicate = ("nationkey" = BIGINT '3')]
           Layout: [nationkey:bigint, name:varchar(25), regionkey:bigint, comment:varchar(152)]
           Estimates: {rows: 25 (2.67kB), cpu: 2.67k, memory: 0B, network: 0B}/{rows: 1 (109B), cpu: 5.34k, memory: 0B, network: 0B}
           nationkey := tpch:nationkey
           regionkey := tpch:regionkey
           name := tpch:name
           comment := tpch:comment

cc @kasiafi