greptimedb/tests/cases/standalone/optimizer/lateral_join_guard.result

-- Guard the remote scan boundary for an aliased SQL LATERAL query.
-- DataFusion 55 decorrelates this shape into an inner join. The resulting
-- scan-local IS NOT NULL predicates may be pushed down, but no outer_ref
-- expression may be advertised as a remote TableScan.partial_filters predicate.
CREATE TABLE lateral_fact (
    ts TIMESTAMP(3) TIME INDEX,
    k STRING,
    val DOUBLE,
    PRIMARY KEY (k)
) ENGINE = mito;

Affected Rows: 0

CREATE TABLE lateral_dim (
    ts TIMESTAMP(3) TIME INDEX,
    k STRING,
    threshold DOUBLE,
    PRIMARY KEY (k)
) ENGINE = mito;

Affected Rows: 0

INSERT INTO lateral_fact VALUES
    ('2024-01-30 00:00:00', 'a', 10.0),
    ('2024-01-30 01:00:00', 'b', 20.0);

Affected Rows: 2

INSERT INTO lateral_dim VALUES
    ('2024-01-30 00:00:00', 'a', 5.0),
    ('2024-01-30 01:00:00', 'b', 25.0);

Affected Rows: 2

ADMIN FLUSH_TABLE('lateral_fact');

+-----------------------------------+
| ADMIN FLUSH_TABLE('lateral_fact') |
+-----------------------------------+
| 0                                 |
+-----------------------------------+

ADMIN FLUSH_TABLE('lateral_dim');

+----------------------------------+
| ADMIN FLUSH_TABLE('lateral_dim') |
+----------------------------------+
| 0                                |
+----------------------------------+

-- SQLNESS REPLACE region=\d+\(\d+,\s+\d+\) region=REDACTED
-- SQLNESS REPLACE (peers.*) REDACTED
-- SQLNESS REPLACE partitioning=Hash\(\[k@(\d+)\],\s*\d+\) partitioning=Hash([k@$1], REDACTED)
-- SQLNESS REPLACE input_partitions=\d+ input_partitions=REDACTED
-- SQLNESS REPLACE (input_partitions=REDACTED)(\s+)\| $1|
EXPLAIN SELECT f.k, d.threshold
FROM lateral_fact f,
LATERAL (
    SELECT threshold FROM lateral_dim d WHERE d.k = f.k
) d
WHERE f.val > d.threshold
ORDER BY f.k;

+---------------+---------------------------------------------------------------------------------------------------------------------------------+
| plan_type     | plan                                                                                                                            |
+---------------+---------------------------------------------------------------------------------------------------------------------------------+
| logical_plan  | Sort: f.k ASC NULLS LAST                                                                                                        |
|               |   Projection: f.k, d.threshold                                                                                                  |
|               |     Inner Join: f.k = d.k Filter: f.val > d.threshold                                                                           |
|               |       Projection: f.k, f.val                                                                                                    |
|               |         MergeScan [is_placeholder=false, remote_input=[                                                                         |
|               | SubqueryAlias: f                                                                                                                |
|               |   Filter: lateral_fact.k IS NOT NULL                                                                                            |
|               |     TableScan: lateral_fact, partial_filters=[lateral_fact.k IS NOT NULL]                                                       |
|               | ]]                                                                                                                              |
|               |       MergeScan [is_placeholder=false, remote_input=[                                                                           |
|               | SubqueryAlias: d                                                                                                                |
|               |   Projection: d.threshold, d.k                                                                                                  |
|               |     SubqueryAlias: d                                                                                                            |
|               |       Filter: lateral_dim.k IS NOT NULL                                                                                         |
|               |         TableScan: lateral_dim, partial_filters=[lateral_dim.k IS NOT NULL]                                                     |
|               | ]]                                                                                                                              |
| physical_plan | SortPreservingMergeExec: [k@0 ASC NULLS LAST]                                                                                   |
|               |   SortExec: expr=[k@0 ASC NULLS LAST], preserve_partitioning=[true]                                                             |
|               |     HashJoinExec: mode=Partitioned, join_type=Inner, on=[(k@0, k@1)], filter=val@0 > threshold@1, projection=[k@0, threshold@2] |
|               |       RepartitionExec: partitioning=Hash([k@0], REDACTED), input_partitions=REDACTED|
|               |         ProjectionExec: expr=[k@1 as k, val@2 as val]                                                                           |
|               |           MergeScanExec: REDACTED
|               |       RepartitionExec: partitioning=Hash([k@1], REDACTED), input_partitions=REDACTED|
|               |         MergeScanExec: REDACTED
|               |                                                                                                                                 |
+---------------+---------------------------------------------------------------------------------------------------------------------------------+

DROP TABLE lateral_fact;

Affected Rows: 0

DROP TABLE lateral_dim;

Affected Rows: 0