# Copyright Materialize, Inc. and contributors. All rights reserved. # # Use of this software is governed by the Business Source License # included in the LICENSE file at the root of this repository. # # As of the Change Date specified in that file, in accordance with # the Business Source License, use of this software will be governed # by the Apache License, Version 2.0. # Test requires stable object IDs reset-server statement ok CREATE TABLE t ( a int, b int ) statement ok CREATE TABLE u ( c int, d int ) statement ok CREATE TABLE v ( e int, f int ) statement ok CREATE INDEX t_a_idx ON T(a); statement ok CREATE INDEX t_b_idx ON T(b); statement ok CREATE VIEW ov AS SELECT * FROM t ORDER BY b asc, a desc LIMIT 5 statement ok CREATE MATERIALIZED VIEW mv AS SELECT * FROM t WHERE a IS NOT NULL statement ok CREATE VIEW hierarchical_group_by AS SELECT a, MIN(b), MAX(DISTINCT b) FROM t GROUP BY a statement ok CREATE MATERIALIZED VIEW hierarchical_group_by_mv AS SELECT * FROM hierarchical_group_by statement ok CREATE VIEW hierarchical_global AS SELECT MIN(b), MAX(DISTINCT b) FROM t statement ok CREATE MATERIALIZED VIEW hierarchical_global_mv AS SELECT * FROM hierarchical_global statement ok CREATE VIEW collated_group_by AS SELECT a, COUNT(DISTINCT b), STRING_AGG(b::text || '1', ',') AS b1, MIN(b), MAX(DISTINCT b), SUM(b), STRING_AGG(b::text || '2', ',') AS b2 FROM t GROUP BY a statement ok CREATE MATERIALIZED VIEW collated_group_by_mv AS SELECT * FROM collated_group_by statement ok CREATE VIEW collated_global AS SELECT COUNT(DISTINCT b), STRING_AGG(b::text || '1', ',') AS b1, MIN(b), MAX(DISTINCT b), SUM(b), STRING_AGG(b::text || '2', ',') AS b2 FROM t statement ok CREATE MATERIALIZED VIEW collated_global_mv AS SELECT * FROM collated_global mode cockroach # Test constant error. query T multiline EXPLAIN PHYSICAL PLAN WITH(no fast path) AS JSON FOR SELECT 1 / 0 ---- { "plans": [ { "id": "Explained Query", "plan": { "lir_id": 1, "node": { "Constant": { "rows": { "Err": "DivisionByZero" } } } } } ], "sources": [] } EOF # Test constant with two elements. query T multiline EXPLAIN PHYSICAL PLAN WITH(no fast path) AS JSON FOR (SELECT 1, 2) UNION ALL (SELECT 1, 2) UNION ALL (SELECT 3, 4) ---- { "plans": [ { "id": "Explained Query", "plan": { "lir_id": 1, "node": { "Constant": { "rows": { "Ok": [ [ { "data": [ 45, 1, 45, 2 ] }, 0, 2 ], [ { "data": [ 45, 3, 45, 4 ] }, 0, 1 ] ] } } } } } ], "sources": [] } EOF # Test basic linear chains. # PassArrangements plan (identity transform on an arranged input). query T multiline EXPLAIN PHYSICAL PLAN WITH(no fast path) AS JSON FOR SELECT * FROM t ---- { "plans": [ { "id": "Explained Query", "plan": { "lir_id": 1, "node": { "Get": { "id": { "Global": { "User": 1 } }, "keys": { "raw": false, "arranged": [ [ [ { "Column": [ 0, "a" ] } ], [ 0, 1 ], [ 1 ] ] ], "types": [ { "scalar_type": "Int32", "nullable": true }, { "scalar_type": "Int32", "nullable": true } ] }, "plan": "PassArrangements" } } } } ], "sources": [] } EOF # PassArrangements plan (identity transform on a raw input). query T multiline EXPLAIN PHYSICAL PLAN WITH(no fast path) AS JSON FOR SELECT * FROM u ---- { "plans": [ { "id": "Explained Query", "plan": { "lir_id": 1, "node": { "Get": { "id": { "Global": { "User": 2 } }, "keys": { "raw": true, "arranged": [], "types": null }, "plan": "PassArrangements" } } } } ], "sources": [ { "id": { "User": 2 }, "op": null } ] } EOF # GetArrangement plan (linear transform of an arranged input). query T multiline EXPLAIN PHYSICAL PLAN WITH(no fast path) AS JSON FOR SELECT a + b, 1 FROM t ---- { "plans": [ { "id": "Explained Query", "plan": { "lir_id": 1, "node": { "Get": { "id": { "Global": { "User": 1 } }, "keys": { "raw": false, "arranged": [ [ [ { "Column": [ 0, "a" ] } ], [ 0, 1 ], [ 1 ] ] ], "types": [ { "scalar_type": "Int32", "nullable": true }, { "scalar_type": "Int32", "nullable": true } ] }, "plan": { "Arrangement": [ [ { "Column": [ 0, "a" ] } ], null, { "expressions": [ { "CallBinary": { "func": "AddInt32", "expr1": { "Column": [ 0, "a" ] }, "expr2": { "Column": [ 1, "b" ] } } }, { "Literal": [ { "Ok": { "data": [ 45, 1 ] } }, { "scalar_type": "Int32", "nullable": false } ] } ], "predicates": [], "projection": [ 2, 3 ], "input_arity": 2 } ] } } } } } ], "sources": [] } EOF # GetCollection plan (linear transform of a raw input). query T multiline EXPLAIN PHYSICAL PLAN WITH(no fast path) AS JSON FOR SELECT c + d, 1 FROM u ---- { "plans": [ { "id": "Explained Query", "plan": { "lir_id": 1, "node": { "Get": { "id": { "Global": { "User": 2 } }, "keys": { "raw": true, "arranged": [], "types": null }, "plan": { "Collection": { "expressions": [], "predicates": [], "projection": [ 0, 1 ], "input_arity": 2 } } } } } } ], "sources": [ { "id": { "User": 2 }, "op": { "expressions": [ { "CallBinary": { "func": "AddInt32", "expr1": { "Column": [ 0, "c" ] }, "expr2": { "Column": [ 1, "d" ] } } }, { "Literal": [ { "Ok": { "data": [ 45, 1 ] } }, { "scalar_type": "Int32", "nullable": false } ] } ], "predicates": [], "projection": [ 2, 3 ], "input_arity": 2 } } ] } EOF # MonotonicTopK plan (one-shot). query T multiline EXPLAIN PHYSICAL PLAN WITH(no fast path) AS JSON FOR SELECT * FROM ov ---- { "plans": [ { "id": "Explained Query", "plan": { "lir_id": 3, "node": { "TopK": { "input": { "lir_id": 2, "node": { "ArrangeBy": { "input": { "lir_id": 1, "node": { "Get": { "id": { "Global": { "User": 1 } }, "keys": { "raw": false, "arranged": [ [ [ { "Column": [ 0, "a" ] } ], [ 0, 1 ], [ 1 ] ] ], "types": [ { "scalar_type": "Int32", "nullable": true }, { "scalar_type": "Int32", "nullable": true } ] }, "plan": "PassArrangements" } } }, "forms": { "raw": true, "arranged": [], "types": null }, "input_key": [ { "Column": [ 0, "a" ] } ], "input_mfp": { "expressions": [], "predicates": [], "projection": [ 0, 1 ], "input_arity": 2 } } } }, "top_k_plan": { "MonotonicTopK": { "group_key": [], "order_key": [ { "column": 1, "desc": false, "nulls_last": true }, { "column": 0, "desc": true, "nulls_last": false } ], "limit": { "Literal": [ { "Ok": { "data": [ 50, 5 ] } }, { "scalar_type": "Int64", "nullable": false } ] }, "arity": 2, "must_consolidate": true } } } } } } ], "sources": [] } EOF # Test Threshold, Union, Distinct, Negate. query T multiline EXPLAIN PHYSICAL PLAN AS JSON FOR SELECT a FROM t EXCEPT ALL SELECT b FROM mv ---- { "plans": [ { "id": "Explained Query", "plan": { "lir_id": 6, "node": { "Threshold": { "input": { "lir_id": 5, "node": { "ArrangeBy": { "input": { "lir_id": 4, "node": { "Union": { "inputs": [ { "lir_id": 1, "node": { "Get": { "id": { "Global": { "User": 1 } }, "keys": { "raw": false, "arranged": [ [ [ { "Column": [ 0, "a" ] } ], [ 0, 1 ], [ 1 ] ] ], "types": [ { "scalar_type": "Int32", "nullable": true }, { "scalar_type": "Int32", "nullable": true } ] }, "plan": { "Arrangement": [ [ { "Column": [ 0, "a" ] } ], null, { "expressions": [], "predicates": [], "projection": [ 0 ], "input_arity": 2 } ] } } } }, { "lir_id": 3, "node": { "Negate": { "input": { "lir_id": 2, "node": { "Get": { "id": { "Global": { "User": 7 } }, "keys": { "raw": true, "arranged": [], "types": null }, "plan": { "Collection": { "expressions": [], "predicates": [], "projection": [ 0 ], "input_arity": 1 } } } } } } } } ], "consolidate_output": true } } }, "forms": { "raw": false, "arranged": [ [ [ { "Column": [ 0, null ] } ], [ 0 ], [] ] ], "types": [ { "scalar_type": "Int32", "nullable": true } ] }, "input_key": null, "input_mfp": { "expressions": [], "predicates": [], "projection": [ 0 ], "input_arity": 1 } } } }, "threshold_plan": { "Basic": { "ensure_arrangement": [ [ { "Column": [ 0, null ] } ], [ 0 ], [] ] } } } } } } ], "sources": [ { "id": { "User": 7 }, "op": { "expressions": [], "predicates": [], "projection": [ 1 ], "input_arity": 2 } } ] } EOF query T multiline EXPLAIN PHYSICAL PLAN WITH (TYPES) AS JSON FOR SELECT * FROM ov ---- { "plans": [ { "id": "Explained Query", "plan": { "lir_id": 3, "node": { "TopK": { "input": { "lir_id": 2, "node": { "ArrangeBy": { "input": { "lir_id": 1, "node": { "Get": { "id": { "Global": { "User": 1 } }, "keys": { "raw": false, "arranged": [ [ [ { "Column": [ 0, "a" ] } ], [ 0, 1 ], [ 1 ] ] ], "types": [ { "scalar_type": "Int32", "nullable": true }, { "scalar_type": "Int32", "nullable": true } ] }, "plan": "PassArrangements" } } }, "forms": { "raw": true, "arranged": [], "types": null }, "input_key": [ { "Column": [ 0, "a" ] } ], "input_mfp": { "expressions": [], "predicates": [], "projection": [ 0, 1 ], "input_arity": 2 } } } }, "top_k_plan": { "MonotonicTopK": { "group_key": [], "order_key": [ { "column": 1, "desc": false, "nulls_last": true }, { "column": 0, "desc": true, "nulls_last": false } ], "limit": { "Literal": [ { "Ok": { "data": [ 50, 5 ] } }, { "scalar_type": "Int64", "nullable": false } ] }, "arity": 2, "must_consolidate": true } } } } } } ], "sources": [] } EOF # Test CTEs. query T multiline EXPLAIN PHYSICAL PLAN AS JSON FOR WITH cte(x) as (SELECT a FROM t EXCEPT ALL SELECT b FROM mv) (SELECT x + 1 FROM cte UNION ALL SELECT x - 1 FROM cte) ---- { "plans": [ { "id": "Explained Query", "plan": { "lir_id": 10, "node": { "Let": { "id": 0, "value": { "lir_id": 6, "node": { "Threshold": { "input": { "lir_id": 5, "node": { "ArrangeBy": { "input": { "lir_id": 4, "node": { "Union": { "inputs": [ { "lir_id": 1, "node": { "Get": { "id": { "Global": { "User": 1 } }, "keys": { "raw": false, "arranged": [ [ [ { "Column": [ 0, "a" ] } ], [ 0, 1 ], [ 1 ] ] ], "types": [ { "scalar_type": "Int32", "nullable": true }, { "scalar_type": "Int32", "nullable": true } ] }, "plan": { "Arrangement": [ [ { "Column": [ 0, "a" ] } ], null, { "expressions": [], "predicates": [], "projection": [ 0 ], "input_arity": 2 } ] } } } }, { "lir_id": 3, "node": { "Negate": { "input": { "lir_id": 2, "node": { "Get": { "id": { "Global": { "User": 7 } }, "keys": { "raw": true, "arranged": [], "types": null }, "plan": { "Collection": { "expressions": [], "predicates": [], "projection": [ 0 ], "input_arity": 1 } } } } } } } } ], "consolidate_output": true } } }, "forms": { "raw": false, "arranged": [ [ [ { "Column": [ 0, null ] } ], [ 0 ], [] ] ], "types": [ { "scalar_type": "Int32", "nullable": true } ] }, "input_key": null, "input_mfp": { "expressions": [], "predicates": [], "projection": [ 0 ], "input_arity": 1 } } } }, "threshold_plan": { "Basic": { "ensure_arrangement": [ [ { "Column": [ 0, null ] } ], [ 0 ], [] ] } } } } }, "body": { "lir_id": 9, "node": { "Union": { "inputs": [ { "lir_id": 7, "node": { "Get": { "id": { "Local": 0 }, "keys": { "raw": false, "arranged": [ [ [ { "Column": [ 0, null ] } ], [ 0 ], [] ] ], "types": [ { "scalar_type": "Int32", "nullable": true } ] }, "plan": { "Arrangement": [ [ { "Column": [ 0, null ] } ], null, { "expressions": [ { "CallBinary": { "func": "AddInt32", "expr1": { "Column": [ 0, "x" ] }, "expr2": { "Literal": [ { "Ok": { "data": [ 45, 1 ] } }, { "scalar_type": "Int32", "nullable": false } ] } } } ], "predicates": [], "projection": [ 1 ], "input_arity": 1 } ] } } } }, { "lir_id": 8, "node": { "Get": { "id": { "Local": 0 }, "keys": { "raw": false, "arranged": [ [ [ { "Column": [ 0, null ] } ], [ 0 ], [] ] ], "types": [ { "scalar_type": "Int32", "nullable": true } ] }, "plan": { "Arrangement": [ [ { "Column": [ 0, null ] } ], null, { "expressions": [ { "CallBinary": { "func": "SubInt32", "expr1": { "Column": [ 0, "x" ] }, "expr2": { "Literal": [ { "Ok": { "data": [ 45, 1 ] } }, { "scalar_type": "Int32", "nullable": false } ] } } } ], "predicates": [], "projection": [ 1 ], "input_arity": 1 } ] } } } } ], "consolidate_output": false } } } } } } } ], "sources": [ { "id": { "User": 7 }, "op": { "expressions": [], "predicates": [], "projection": [ 1 ], "input_arity": 2 } } ] } EOF # Test Mfp. query T multiline EXPLAIN PHYSICAL PLAN AS JSON FOR WITH cte(x) as (SELECT a FROM t EXCEPT ALL SELECT b FROM mv) SELECT x * 5 FROM cte WHERE x = 5 ---- { "plans": [ { "id": "Explained Query", "plan": { "lir_id": 10, "node": { "Mfp": { "input": { "lir_id": 9, "node": { "Threshold": { "input": { "lir_id": 8, "node": { "ArrangeBy": { "input": { "lir_id": 7, "node": { "Union": { "inputs": [ { "lir_id": 4, "node": { "Join": { "inputs": [ { "lir_id": 1, "node": { "Get": { "id": { "Global": { "User": 1 } }, "keys": { "raw": false, "arranged": [ [ [ { "Column": [ 0, "a" ] } ], [ 0, 1 ], [ 1 ] ] ], "types": [ { "scalar_type": "Int32", "nullable": true }, { "scalar_type": "Int32", "nullable": true } ] }, "plan": "PassArrangements" } } }, { "lir_id": 3, "node": { "ArrangeBy": { "input": { "lir_id": 2, "node": { "Constant": { "rows": { "Ok": [ [ { "data": [ 45, 5 ] }, 0, 1 ] ] } } } }, "forms": { "raw": true, "arranged": [ [ [ { "Column": [ 0, null ] } ], [ 0 ], [] ] ], "types": [ { "scalar_type": "Int32", "nullable": false } ] }, "input_key": null, "input_mfp": { "expressions": [], "predicates": [], "projection": [ 0 ], "input_arity": 1 } } } } ], "plan": { "Linear": { "source_relation": 1, "source_key": [ { "Column": [ 0, null ] } ], "initial_closure": null, "stage_plans": [ { "lookup_relation": 0, "stream_key": [ { "Column": [ 0, null ] } ], "stream_thinning": [], "lookup_key": [ { "Column": [ 0, "a" ] } ], "closure": { "ready_equivalences": [], "before": { "mfp": { "expressions": [], "predicates": [], "projection": [ 0 ], "input_arity": 2 } } } } ], "final_closure": null } } } } }, { "lir_id": 6, "node": { "Negate": { "input": { "lir_id": 5, "node": { "Get": { "id": { "Global": { "User": 7 } }, "keys": { "raw": true, "arranged": [], "types": null }, "plan": { "Collection": { "expressions": [], "predicates": [], "projection": [ 0 ], "input_arity": 1 } } } } } } } } ], "consolidate_output": true } } }, "forms": { "raw": false, "arranged": [ [ [ { "Column": [ 0, null ] } ], [ 0 ], [] ] ], "types": [ { "scalar_type": "Int32", "nullable": false } ] }, "input_key": null, "input_mfp": { "expressions": [], "predicates": [], "projection": [ 0 ], "input_arity": 1 } } } }, "threshold_plan": { "Basic": { "ensure_arrangement": [ [ { "Column": [ 0, null ] } ], [ 0 ], [] ] } } } } }, "mfp": { "expressions": [ { "Literal": [ { "Ok": { "data": [ 45, 25 ] } }, { "scalar_type": "Int32", "nullable": false } ] } ], "predicates": [], "projection": [ 1 ], "input_arity": 1 }, "input_key_val": [ [ { "Column": [ 0, null ] } ], null ] } } } } ], "sources": [ { "id": { "User": 7 }, "op": { "expressions": [], "predicates": [ [ 2, { "CallBinary": { "func": "Eq", "expr1": { "Column": [ 1, "x" ] }, "expr2": { "Literal": [ { "Ok": { "data": [ 45, 5 ] } }, { "scalar_type": "Int32", "nullable": false } ] } } } ] ], "projection": [ 1 ], "input_arity": 2 } } ] } EOF # Test FlatMap. query T multiline EXPLAIN PHYSICAL PLAN AS JSON FOR SELECT generate_series(a, b) from t ---- { "plans": [ { "id": "Explained Query", "plan": { "lir_id": 2, "node": { "FlatMap": { "input": { "lir_id": 1, "node": { "Get": { "id": { "Global": { "User": 1 } }, "keys": { "raw": false, "arranged": [ [ [ { "Column": [ 0, "a" ] } ], [ 0, 1 ], [ 1 ] ] ], "types": [ { "scalar_type": "Int32", "nullable": true }, { "scalar_type": "Int32", "nullable": true } ] }, "plan": "PassArrangements" } } }, "func": "GenerateSeriesInt32", "exprs": [ { "Column": [ 0, "a" ] }, { "Column": [ 1, "b" ] }, { "Literal": [ { "Ok": { "data": [ 45, 1 ] } }, { "scalar_type": "Int32", "nullable": false } ] } ], "mfp_after": { "expressions": [], "predicates": [], "projection": [ 2 ], "input_arity": 3 }, "input_key": [ { "Column": [ 0, "a" ] } ] } } } } ], "sources": [] } EOF # Test Reduce::Distinct. query T multiline EXPLAIN PHYSICAL PLAN AS JSON FOR SELECT DISTINCT a, b FROM t ---- { "plans": [ { "id": "Explained Query", "plan": { "lir_id": 2, "node": { "Reduce": { "input": { "lir_id": 1, "node": { "Get": { "id": { "Global": { "User": 1 } }, "keys": { "raw": false, "arranged": [ [ [ { "Column": [ 0, "a" ] } ], [ 0, 1 ], [ 1 ] ] ], "types": [ { "scalar_type": "Int32", "nullable": true }, { "scalar_type": "Int32", "nullable": true } ] }, "plan": "PassArrangements" } } }, "key_val_plan": { "key_plan": { "mfp": { "expressions": [], "predicates": [], "projection": [ 0, 1 ], "input_arity": 2 } }, "val_plan": { "mfp": { "expressions": [], "predicates": [], "projection": [], "input_arity": 2 } } }, "plan": "Distinct", "input_key": [ { "Column": [ 0, "a" ] } ], "mfp_after": { "expressions": [], "predicates": [], "projection": [ 0, 1 ], "input_arity": 2 } } } } } ], "sources": [] } EOF # Test Reduce::Accumulable (with GROUP BY). query T multiline EXPLAIN PHYSICAL PLAN AS JSON FOR SELECT a, SUM(b), COUNT(DISTINCT b) FROM t GROUP BY a ---- { "plans": [ { "id": "Explained Query", "plan": { "lir_id": 2, "node": { "Reduce": { "input": { "lir_id": 1, "node": { "Get": { "id": { "Global": { "User": 1 } }, "keys": { "raw": false, "arranged": [ [ [ { "Column": [ 0, "a" ] } ], [ 0, 1 ], [ 1 ] ] ], "types": [ { "scalar_type": "Int32", "nullable": true }, { "scalar_type": "Int32", "nullable": true } ] }, "plan": "PassArrangements" } } }, "key_val_plan": { "key_plan": { "mfp": { "expressions": [], "predicates": [], "projection": [ 0 ], "input_arity": 2 } }, "val_plan": { "mfp": { "expressions": [], "predicates": [], "projection": [ 1, 1 ], "input_arity": 2 } } }, "plan": { "Accumulable": { "full_aggrs": [ { "func": "SumInt32", "expr": { "Column": [ 1, "b" ] }, "distinct": false }, { "func": "Count", "expr": { "Column": [ 1, "b" ] }, "distinct": true } ], "simple_aggrs": [ [ 0, 0, { "func": "SumInt32", "expr": { "Column": [ 1, "b" ] }, "distinct": false } ] ], "distinct_aggrs": [ [ 1, 1, { "func": "Count", "expr": { "Column": [ 1, "b" ] }, "distinct": true } ] ] } }, "input_key": [ { "Column": [ 0, "a" ] } ], "mfp_after": { "expressions": [], "predicates": [], "projection": [ 0, 1, 2 ], "input_arity": 3 } } } } } ], "sources": [] } EOF # Test Reduce::Accumulable (global aggregate). query T multiline EXPLAIN PHYSICAL PLAN AS JSON FOR SELECT SUM(b), COUNT(DISTINCT b) FROM t ---- { "plans": [ { "id": "Explained Query", "plan": { "lir_id": 11, "node": { "Let": { "id": 0, "value": { "lir_id": 2, "node": { "Reduce": { "input": { "lir_id": 1, "node": { "Get": { "id": { "Global": { "User": 1 } }, "keys": { "raw": false, "arranged": [ [ [ { "Column": [ 0, "a" ] } ], [ 0, 1 ], [ 1 ] ] ], "types": [ { "scalar_type": "Int32", "nullable": true }, { "scalar_type": "Int32", "nullable": true } ] }, "plan": { "Arrangement": [ [ { "Column": [ 0, "a" ] } ], null, { "expressions": [], "predicates": [], "projection": [ 1 ], "input_arity": 2 } ] } } } }, "key_val_plan": { "key_plan": { "mfp": { "expressions": [], "predicates": [], "projection": [], "input_arity": 1 } }, "val_plan": { "mfp": { "expressions": [], "predicates": [], "projection": [ 0, 0 ], "input_arity": 1 } } }, "plan": { "Accumulable": { "full_aggrs": [ { "func": "SumInt32", "expr": { "Column": [ 0, "b" ] }, "distinct": false }, { "func": "Count", "expr": { "Column": [ 0, "b" ] }, "distinct": true } ], "simple_aggrs": [ [ 0, 0, { "func": "SumInt32", "expr": { "Column": [ 0, "b" ] }, "distinct": false } ] ], "distinct_aggrs": [ [ 1, 1, { "func": "Count", "expr": { "Column": [ 0, "b" ] }, "distinct": true } ] ] } }, "input_key": null, "mfp_after": { "expressions": [], "predicates": [], "projection": [ 0, 1 ], "input_arity": 2 } } } }, "body": { "lir_id": 10, "node": { "Union": { "inputs": [ { "lir_id": 9, "node": { "ArrangeBy": { "input": { "lir_id": 3, "node": { "Get": { "id": { "Local": 0 }, "keys": { "raw": false, "arranged": [ [ [], [ 0, 1 ], [ 0, 1 ] ] ], "types": null }, "plan": "PassArrangements" } } }, "forms": { "raw": true, "arranged": [], "types": null }, "input_key": [], "input_mfp": { "expressions": [], "predicates": [], "projection": [ 0, 1 ], "input_arity": 2 } } } }, { "lir_id": 8, "node": { "Mfp": { "input": { "lir_id": 7, "node": { "Union": { "inputs": [ { "lir_id": 5, "node": { "Negate": { "input": { "lir_id": 4, "node": { "Get": { "id": { "Local": 0 }, "keys": { "raw": false, "arranged": [ [ [], [ 0, 1 ], [ 0, 1 ] ] ], "types": null }, "plan": { "Arrangement": [ [], null, { "expressions": [], "predicates": [], "projection": [], "input_arity": 2 } ] } } } } } } }, { "lir_id": 6, "node": { "Constant": { "rows": { "Ok": [ [ { "data": [] }, 0, 1 ] ] } } } } ], "consolidate_output": true } } }, "mfp": { "expressions": [ { "Literal": [ { "Ok": { "data": [ 0 ] } }, { "scalar_type": "Int64", "nullable": true } ] }, { "Literal": [ { "Ok": { "data": [ 49 ] } }, { "scalar_type": "Int64", "nullable": false } ] } ], "predicates": [], "projection": [ 0, 1 ], "input_arity": 0 }, "input_key_val": null } } } ], "consolidate_output": false } } } } } } } ], "sources": [] } EOF # Test Reduce::Hierarchical (with GROUP BY, one-shot). query T multiline EXPLAIN PHYSICAL PLAN AS JSON FOR SELECT * FROM hierarchical_group_by ---- { "plans": [ { "id": "Explained Query", "plan": { "lir_id": 2, "node": { "Reduce": { "input": { "lir_id": 1, "node": { "Get": { "id": { "Global": { "User": 1 } }, "keys": { "raw": false, "arranged": [ [ [ { "Column": [ 0, "a" ] } ], [ 0, 1 ], [ 1 ] ] ], "types": [ { "scalar_type": "Int32", "nullable": true }, { "scalar_type": "Int32", "nullable": true } ] }, "plan": "PassArrangements" } } }, "key_val_plan": { "key_plan": { "mfp": { "expressions": [], "predicates": [], "projection": [ 0 ], "input_arity": 2 } }, "val_plan": { "mfp": { "expressions": [], "predicates": [], "projection": [ 1, 1 ], "input_arity": 2 } } }, "plan": { "Hierarchical": { "Monotonic": { "aggr_funcs": [ "MinInt32", "MaxInt32" ], "skips": [ 0, 0 ], "must_consolidate": true } } }, "input_key": [ { "Column": [ 0, "a" ] } ], "mfp_after": { "expressions": [], "predicates": [], "projection": [ 0, 1, 2 ], "input_arity": 3 } } } } } ], "sources": [] } EOF # Test Reduce::Hierarchical (global aggregate). query T multiline EXPLAIN PHYSICAL PLAN AS JSON FOR MATERIALIZED VIEW hierarchical_global_mv ---- { "plans": [ { "id": "materialize.public.hierarchical_global_mv", "plan": { "lir_id": 11, "node": { "Let": { "id": 0, "value": { "lir_id": 2, "node": { "Reduce": { "input": { "lir_id": 1, "node": { "Get": { "id": { "Global": { "User": 1 } }, "keys": { "raw": false, "arranged": [ [ [ { "Column": [ 0, "a" ] } ], [ 0, 1 ], [ 1 ] ] ], "types": [ { "scalar_type": "Int32", "nullable": true }, { "scalar_type": "Int32", "nullable": true } ] }, "plan": { "Arrangement": [ [ { "Column": [ 0, "a" ] } ], null, { "expressions": [], "predicates": [], "projection": [ 1 ], "input_arity": 2 } ] } } } }, "key_val_plan": { "key_plan": { "mfp": { "expressions": [], "predicates": [], "projection": [], "input_arity": 1 } }, "val_plan": { "mfp": { "expressions": [], "predicates": [], "projection": [ 0, 0 ], "input_arity": 1 } } }, "plan": { "Hierarchical": { "Bucketed": { "aggr_funcs": [ "MinInt32", "MaxInt32" ], "skips": [ 0, 0 ], "buckets": [ 268435456, 16777216, 1048576, 65536, 4096, 256, 16 ] } } }, "input_key": null, "mfp_after": { "expressions": [], "predicates": [], "projection": [ 0, 1 ], "input_arity": 2 } } } }, "body": { "lir_id": 10, "node": { "Union": { "inputs": [ { "lir_id": 9, "node": { "ArrangeBy": { "input": { "lir_id": 3, "node": { "Get": { "id": { "Local": 0 }, "keys": { "raw": false, "arranged": [ [ [], [ 0, 1 ], [ 0, 1 ] ] ], "types": null }, "plan": "PassArrangements" } } }, "forms": { "raw": true, "arranged": [], "types": null }, "input_key": [], "input_mfp": { "expressions": [], "predicates": [], "projection": [ 0, 1 ], "input_arity": 2 } } } }, { "lir_id": 8, "node": { "Mfp": { "input": { "lir_id": 7, "node": { "Union": { "inputs": [ { "lir_id": 5, "node": { "Negate": { "input": { "lir_id": 4, "node": { "Get": { "id": { "Local": 0 }, "keys": { "raw": false, "arranged": [ [ [], [ 0, 1 ], [ 0, 1 ] ] ], "types": null }, "plan": { "Arrangement": [ [], null, { "expressions": [], "predicates": [], "projection": [], "input_arity": 2 } ] } } } } } } }, { "lir_id": 6, "node": { "Constant": { "rows": { "Ok": [ [ { "data": [] }, 0, 1 ] ] } } } } ], "consolidate_output": true } } }, "mfp": { "expressions": [ { "Literal": [ { "Ok": { "data": [ 0 ] } }, { "scalar_type": "Int32", "nullable": true } ] }, { "Literal": [ { "Ok": { "data": [ 0 ] } }, { "scalar_type": "Int32", "nullable": true } ] } ], "predicates": [], "projection": [ 0, 1 ], "input_arity": 0 }, "input_key_val": null } } } ], "consolidate_output": false } } } } } } } ], "sources": [] } EOF # Test Reduce::Hierarchical (global aggregate, one-shot). query T multiline EXPLAIN PHYSICAL PLAN AS JSON FOR SELECT * FROM hierarchical_global ---- { "plans": [ { "id": "Explained Query", "plan": { "lir_id": 11, "node": { "Let": { "id": 0, "value": { "lir_id": 2, "node": { "Reduce": { "input": { "lir_id": 1, "node": { "Get": { "id": { "Global": { "User": 1 } }, "keys": { "raw": false, "arranged": [ [ [ { "Column": [ 0, "a" ] } ], [ 0, 1 ], [ 1 ] ] ], "types": [ { "scalar_type": "Int32", "nullable": true }, { "scalar_type": "Int32", "nullable": true } ] }, "plan": { "Arrangement": [ [ { "Column": [ 0, "a" ] } ], null, { "expressions": [], "predicates": [], "projection": [ 1 ], "input_arity": 2 } ] } } } }, "key_val_plan": { "key_plan": { "mfp": { "expressions": [], "predicates": [], "projection": [], "input_arity": 1 } }, "val_plan": { "mfp": { "expressions": [], "predicates": [], "projection": [ 0, 0 ], "input_arity": 1 } } }, "plan": { "Hierarchical": { "Monotonic": { "aggr_funcs": [ "MinInt32", "MaxInt32" ], "skips": [ 0, 0 ], "must_consolidate": true } } }, "input_key": null, "mfp_after": { "expressions": [], "predicates": [], "projection": [ 0, 1 ], "input_arity": 2 } } } }, "body": { "lir_id": 10, "node": { "Union": { "inputs": [ { "lir_id": 9, "node": { "ArrangeBy": { "input": { "lir_id": 3, "node": { "Get": { "id": { "Local": 0 }, "keys": { "raw": false, "arranged": [ [ [], [ 0, 1 ], [ 0, 1 ] ] ], "types": null }, "plan": "PassArrangements" } } }, "forms": { "raw": true, "arranged": [], "types": null }, "input_key": [], "input_mfp": { "expressions": [], "predicates": [], "projection": [ 0, 1 ], "input_arity": 2 } } } }, { "lir_id": 8, "node": { "Mfp": { "input": { "lir_id": 7, "node": { "Union": { "inputs": [ { "lir_id": 5, "node": { "Negate": { "input": { "lir_id": 4, "node": { "Get": { "id": { "Local": 0 }, "keys": { "raw": false, "arranged": [ [ [], [ 0, 1 ], [ 0, 1 ] ] ], "types": null }, "plan": { "Arrangement": [ [], null, { "expressions": [], "predicates": [], "projection": [], "input_arity": 2 } ] } } } } } } }, { "lir_id": 6, "node": { "Constant": { "rows": { "Ok": [ [ { "data": [] }, 0, 1 ] ] } } } } ], "consolidate_output": true } } }, "mfp": { "expressions": [ { "Literal": [ { "Ok": { "data": [ 0 ] } }, { "scalar_type": "Int32", "nullable": true } ] }, { "Literal": [ { "Ok": { "data": [ 0 ] } }, { "scalar_type": "Int32", "nullable": true } ] } ], "predicates": [], "projection": [ 0, 1 ], "input_arity": 0 }, "input_key_val": null } } } ], "consolidate_output": false } } } } } } } ], "sources": [] } EOF # Test Reduce::Basic (with GROUP BY). query T multiline EXPLAIN PHYSICAL PLAN AS JSON FOR SELECT a, STRING_AGG(b::text || '1', ','), STRING_AGG(b::text || '2', ',') FROM t GROUP BY a ---- { "plans": [ { "id": "Explained Query", "plan": { "lir_id": 2, "node": { "Reduce": { "input": { "lir_id": 1, "node": { "Get": { "id": { "Global": { "User": 1 } }, "keys": { "raw": false, "arranged": [ [ [ { "Column": [ 0, "a" ] } ], [ 0, 1 ], [ 1 ] ] ], "types": [ { "scalar_type": "Int32", "nullable": true }, { "scalar_type": "Int32", "nullable": true } ] }, "plan": "PassArrangements" } } }, "key_val_plan": { "key_plan": { "mfp": { "expressions": [], "predicates": [], "projection": [ 0 ], "input_arity": 2 } }, "val_plan": { "mfp": { "expressions": [ { "CallUnary": { "func": { "CastInt32ToString": null }, "expr": { "Column": [ 1, "b" ] } } }, { "CallVariadic": { "func": { "RecordCreate": { "field_names": [ "" ] } }, "exprs": [ { "CallVariadic": { "func": { "RecordCreate": { "field_names": [ "value", "sep" ] } }, "exprs": [ { "CallBinary": { "func": "TextConcat", "expr1": { "Column": [ 2, null ] }, "expr2": { "Literal": [ { "Ok": { "data": [ 19, 1, 49 ] } }, { "scalar_type": "String", "nullable": false } ] } } }, { "Literal": [ { "Ok": { "data": [ 19, 1, 44 ] } }, { "scalar_type": "String", "nullable": false } ] } ] } } ] } }, { "CallVariadic": { "func": { "RecordCreate": { "field_names": [ "" ] } }, "exprs": [ { "CallVariadic": { "func": { "RecordCreate": { "field_names": [ "value", "sep" ] } }, "exprs": [ { "CallBinary": { "func": "TextConcat", "expr1": { "Column": [ 2, null ] }, "expr2": { "Literal": [ { "Ok": { "data": [ 19, 1, 50 ] } }, { "scalar_type": "String", "nullable": false } ] } } }, { "Literal": [ { "Ok": { "data": [ 19, 1, 44 ] } }, { "scalar_type": "String", "nullable": false } ] } ] } } ] } } ], "predicates": [], "projection": [ 3, 4 ], "input_arity": 2 } } }, "plan": { "Basic": { "Multiple": [ [ 0, { "func": { "StringAgg": { "order_by": [] } }, "expr": { "CallVariadic": { "func": { "RecordCreate": { "field_names": [ "" ] } }, "exprs": [ { "CallVariadic": { "func": { "RecordCreate": { "field_names": [ "value", "sep" ] } }, "exprs": [ { "CallBinary": { "func": "TextConcat", "expr1": { "CallUnary": { "func": { "CastInt32ToString": null }, "expr": { "Column": [ 1, "b" ] } } }, "expr2": { "Literal": [ { "Ok": { "data": [ 19, 1, 49 ] } }, { "scalar_type": "String", "nullable": false } ] } } }, { "Literal": [ { "Ok": { "data": [ 19, 1, 44 ] } }, { "scalar_type": "String", "nullable": false } ] } ] } } ] } }, "distinct": false } ], [ 1, { "func": { "StringAgg": { "order_by": [] } }, "expr": { "CallVariadic": { "func": { "RecordCreate": { "field_names": [ "" ] } }, "exprs": [ { "CallVariadic": { "func": { "RecordCreate": { "field_names": [ "value", "sep" ] } }, "exprs": [ { "CallBinary": { "func": "TextConcat", "expr1": { "CallUnary": { "func": { "CastInt32ToString": null }, "expr": { "Column": [ 1, "b" ] } } }, "expr2": { "Literal": [ { "Ok": { "data": [ 19, 1, 50 ] } }, { "scalar_type": "String", "nullable": false } ] } } }, { "Literal": [ { "Ok": { "data": [ 19, 1, 44 ] } }, { "scalar_type": "String", "nullable": false } ] } ] } } ] } }, "distinct": false } ] ] } }, "input_key": [ { "Column": [ 0, "a" ] } ], "mfp_after": { "expressions": [], "predicates": [], "projection": [ 0, 1, 2 ], "input_arity": 3 } } } } } ], "sources": [] } EOF # Test Reduce::Basic (global aggregate). query T multiline EXPLAIN PHYSICAL PLAN AS JSON FOR SELECT STRING_AGG(b::text || '1', ','), STRING_AGG(b::text || '2', ',') FROM t ---- { "plans": [ { "id": "Explained Query", "plan": { "lir_id": 11, "node": { "Let": { "id": 0, "value": { "lir_id": 2, "node": { "Reduce": { "input": { "lir_id": 1, "node": { "Get": { "id": { "Global": { "User": 1 } }, "keys": { "raw": false, "arranged": [ [ [ { "Column": [ 0, "a" ] } ], [ 0, 1 ], [ 1 ] ] ], "types": [ { "scalar_type": "Int32", "nullable": true }, { "scalar_type": "Int32", "nullable": true } ] }, "plan": { "Arrangement": [ [ { "Column": [ 0, "a" ] } ], null, { "expressions": [], "predicates": [], "projection": [ 1 ], "input_arity": 2 } ] } } } }, "key_val_plan": { "key_plan": { "mfp": { "expressions": [], "predicates": [], "projection": [], "input_arity": 1 } }, "val_plan": { "mfp": { "expressions": [ { "CallUnary": { "func": { "CastInt32ToString": null }, "expr": { "Column": [ 0, "b" ] } } }, { "CallVariadic": { "func": { "RecordCreate": { "field_names": [ "" ] } }, "exprs": [ { "CallVariadic": { "func": { "RecordCreate": { "field_names": [ "value", "sep" ] } }, "exprs": [ { "CallBinary": { "func": "TextConcat", "expr1": { "Column": [ 1, null ] }, "expr2": { "Literal": [ { "Ok": { "data": [ 19, 1, 49 ] } }, { "scalar_type": "String", "nullable": false } ] } } }, { "Literal": [ { "Ok": { "data": [ 19, 1, 44 ] } }, { "scalar_type": "String", "nullable": false } ] } ] } } ] } }, { "CallVariadic": { "func": { "RecordCreate": { "field_names": [ "" ] } }, "exprs": [ { "CallVariadic": { "func": { "RecordCreate": { "field_names": [ "value", "sep" ] } }, "exprs": [ { "CallBinary": { "func": "TextConcat", "expr1": { "Column": [ 1, null ] }, "expr2": { "Literal": [ { "Ok": { "data": [ 19, 1, 50 ] } }, { "scalar_type": "String", "nullable": false } ] } } }, { "Literal": [ { "Ok": { "data": [ 19, 1, 44 ] } }, { "scalar_type": "String", "nullable": false } ] } ] } } ] } } ], "predicates": [], "projection": [ 2, 3 ], "input_arity": 1 } } }, "plan": { "Basic": { "Multiple": [ [ 0, { "func": { "StringAgg": { "order_by": [] } }, "expr": { "CallVariadic": { "func": { "RecordCreate": { "field_names": [ "" ] } }, "exprs": [ { "CallVariadic": { "func": { "RecordCreate": { "field_names": [ "value", "sep" ] } }, "exprs": [ { "CallBinary": { "func": "TextConcat", "expr1": { "CallUnary": { "func": { "CastInt32ToString": null }, "expr": { "Column": [ 0, "b" ] } } }, "expr2": { "Literal": [ { "Ok": { "data": [ 19, 1, 49 ] } }, { "scalar_type": "String", "nullable": false } ] } } }, { "Literal": [ { "Ok": { "data": [ 19, 1, 44 ] } }, { "scalar_type": "String", "nullable": false } ] } ] } } ] } }, "distinct": false } ], [ 1, { "func": { "StringAgg": { "order_by": [] } }, "expr": { "CallVariadic": { "func": { "RecordCreate": { "field_names": [ "" ] } }, "exprs": [ { "CallVariadic": { "func": { "RecordCreate": { "field_names": [ "value", "sep" ] } }, "exprs": [ { "CallBinary": { "func": "TextConcat", "expr1": { "CallUnary": { "func": { "CastInt32ToString": null }, "expr": { "Column": [ 0, "b" ] } } }, "expr2": { "Literal": [ { "Ok": { "data": [ 19, 1, 50 ] } }, { "scalar_type": "String", "nullable": false } ] } } }, { "Literal": [ { "Ok": { "data": [ 19, 1, 44 ] } }, { "scalar_type": "String", "nullable": false } ] } ] } } ] } }, "distinct": false } ] ] } }, "input_key": null, "mfp_after": { "expressions": [], "predicates": [], "projection": [ 0, 1 ], "input_arity": 2 } } } }, "body": { "lir_id": 10, "node": { "Union": { "inputs": [ { "lir_id": 9, "node": { "ArrangeBy": { "input": { "lir_id": 3, "node": { "Get": { "id": { "Local": 0 }, "keys": { "raw": false, "arranged": [ [ [], [ 0, 1 ], [ 0, 1 ] ] ], "types": null }, "plan": "PassArrangements" } } }, "forms": { "raw": true, "arranged": [], "types": null }, "input_key": [], "input_mfp": { "expressions": [], "predicates": [], "projection": [ 0, 1 ], "input_arity": 2 } } } }, { "lir_id": 8, "node": { "Mfp": { "input": { "lir_id": 7, "node": { "Union": { "inputs": [ { "lir_id": 5, "node": { "Negate": { "input": { "lir_id": 4, "node": { "Get": { "id": { "Local": 0 }, "keys": { "raw": false, "arranged": [ [ [], [ 0, 1 ], [ 0, 1 ] ] ], "types": null }, "plan": { "Arrangement": [ [], null, { "expressions": [], "predicates": [], "projection": [], "input_arity": 2 } ] } } } } } } }, { "lir_id": 6, "node": { "Constant": { "rows": { "Ok": [ [ { "data": [] }, 0, 1 ] ] } } } } ], "consolidate_output": true } } }, "mfp": { "expressions": [ { "Literal": [ { "Ok": { "data": [ 0 ] } }, { "scalar_type": "String", "nullable": true } ] }, { "Literal": [ { "Ok": { "data": [ 0 ] } }, { "scalar_type": "String", "nullable": true } ] } ], "predicates": [], "projection": [ 0, 1 ], "input_arity": 0 }, "input_key_val": null } } } ], "consolidate_output": false } } } } } } } ], "sources": [] } EOF # Test Reduce::Collated (with GROUP BY). query T multiline EXPLAIN PHYSICAL PLAN AS JSON FOR MATERIALIZED VIEW collated_group_by_mv ---- { "plans": [ { "id": "materialize.public.collated_group_by_mv", "plan": { "lir_id": 2, "node": { "Reduce": { "input": { "lir_id": 1, "node": { "Get": { "id": { "Global": { "User": 1 } }, "keys": { "raw": false, "arranged": [ [ [ { "Column": [ 0, "a" ] } ], [ 0, 1 ], [ 1 ] ] ], "types": [ { "scalar_type": "Int32", "nullable": true }, { "scalar_type": "Int32", "nullable": true } ] }, "plan": "PassArrangements" } } }, "key_val_plan": { "key_plan": { "mfp": { "expressions": [], "predicates": [], "projection": [ 0 ], "input_arity": 2 } }, "val_plan": { "mfp": { "expressions": [ { "CallUnary": { "func": { "CastInt32ToString": null }, "expr": { "Column": [ 1, "b" ] } } }, { "CallVariadic": { "func": { "RecordCreate": { "field_names": [ "" ] } }, "exprs": [ { "CallVariadic": { "func": { "RecordCreate": { "field_names": [ "value", "sep" ] } }, "exprs": [ { "CallBinary": { "func": "TextConcat", "expr1": { "Column": [ 2, null ] }, "expr2": { "Literal": [ { "Ok": { "data": [ 19, 1, 49 ] } }, { "scalar_type": "String", "nullable": false } ] } } }, { "Literal": [ { "Ok": { "data": [ 19, 1, 44 ] } }, { "scalar_type": "String", "nullable": false } ] } ] } } ] } }, { "CallVariadic": { "func": { "RecordCreate": { "field_names": [ "" ] } }, "exprs": [ { "CallVariadic": { "func": { "RecordCreate": { "field_names": [ "value", "sep" ] } }, "exprs": [ { "CallBinary": { "func": "TextConcat", "expr1": { "Column": [ 2, null ] }, "expr2": { "Literal": [ { "Ok": { "data": [ 19, 1, 50 ] } }, { "scalar_type": "String", "nullable": false } ] } } }, { "Literal": [ { "Ok": { "data": [ 19, 1, 44 ] } }, { "scalar_type": "String", "nullable": false } ] } ] } } ] } } ], "predicates": [], "projection": [ 1, 3, 1, 1, 1, 4 ], "input_arity": 2 } } }, "plan": { "Collation": { "accumulable": { "full_aggrs": [ { "func": "Count", "expr": { "Column": [ 1, "b" ] }, "distinct": true }, { "func": "SumInt32", "expr": { "Column": [ 1, "b" ] }, "distinct": false } ], "simple_aggrs": [ [ 1, 4, { "func": "SumInt32", "expr": { "Column": [ 1, "b" ] }, "distinct": false } ] ], "distinct_aggrs": [ [ 0, 0, { "func": "Count", "expr": { "Column": [ 1, "b" ] }, "distinct": true } ] ] }, "hierarchical": { "Bucketed": { "aggr_funcs": [ "MinInt32", "MaxInt32" ], "skips": [ 2, 0 ], "buckets": [ 268435456, 16777216, 1048576, 65536, 4096, 256, 16 ] } }, "basic": { "Multiple": [ [ 1, { "func": { "StringAgg": { "order_by": [] } }, "expr": { "CallVariadic": { "func": { "RecordCreate": { "field_names": [ "" ] } }, "exprs": [ { "CallVariadic": { "func": { "RecordCreate": { "field_names": [ "value", "sep" ] } }, "exprs": [ { "CallBinary": { "func": "TextConcat", "expr1": { "CallUnary": { "func": { "CastInt32ToString": null }, "expr": { "Column": [ 1, "b" ] } } }, "expr2": { "Literal": [ { "Ok": { "data": [ 19, 1, 49 ] } }, { "scalar_type": "String", "nullable": false } ] } } }, { "Literal": [ { "Ok": { "data": [ 19, 1, 44 ] } }, { "scalar_type": "String", "nullable": false } ] } ] } } ] } }, "distinct": false } ], [ 5, { "func": { "StringAgg": { "order_by": [] } }, "expr": { "CallVariadic": { "func": { "RecordCreate": { "field_names": [ "" ] } }, "exprs": [ { "CallVariadic": { "func": { "RecordCreate": { "field_names": [ "value", "sep" ] } }, "exprs": [ { "CallBinary": { "func": "TextConcat", "expr1": { "CallUnary": { "func": { "CastInt32ToString": null }, "expr": { "Column": [ 1, "b" ] } } }, "expr2": { "Literal": [ { "Ok": { "data": [ 19, 1, 50 ] } }, { "scalar_type": "String", "nullable": false } ] } } }, { "Literal": [ { "Ok": { "data": [ 19, 1, 44 ] } }, { "scalar_type": "String", "nullable": false } ] } ] } } ] } }, "distinct": false } ] ] }, "aggregate_types": [ "Accumulable", "Basic", "Hierarchical", "Hierarchical", "Accumulable", "Basic" ] } }, "input_key": [ { "Column": [ 0, "a" ] } ], "mfp_after": { "expressions": [], "predicates": [], "projection": [ 0, 1, 2, 3, 4, 5, 6 ], "input_arity": 7 } } } } } ], "sources": [] } EOF # Test Reduce::Collated (with GROUP BY, one-shot). query T multiline EXPLAIN PHYSICAL PLAN AS JSON FOR SELECT * FROM collated_group_by ---- { "plans": [ { "id": "Explained Query", "plan": { "lir_id": 2, "node": { "Reduce": { "input": { "lir_id": 1, "node": { "Get": { "id": { "Global": { "User": 1 } }, "keys": { "raw": false, "arranged": [ [ [ { "Column": [ 0, "a" ] } ], [ 0, 1 ], [ 1 ] ] ], "types": [ { "scalar_type": "Int32", "nullable": true }, { "scalar_type": "Int32", "nullable": true } ] }, "plan": "PassArrangements" } } }, "key_val_plan": { "key_plan": { "mfp": { "expressions": [], "predicates": [], "projection": [ 0 ], "input_arity": 2 } }, "val_plan": { "mfp": { "expressions": [ { "CallUnary": { "func": { "CastInt32ToString": null }, "expr": { "Column": [ 1, "b" ] } } }, { "CallVariadic": { "func": { "RecordCreate": { "field_names": [ "" ] } }, "exprs": [ { "CallVariadic": { "func": { "RecordCreate": { "field_names": [ "value", "sep" ] } }, "exprs": [ { "CallBinary": { "func": "TextConcat", "expr1": { "Column": [ 2, null ] }, "expr2": { "Literal": [ { "Ok": { "data": [ 19, 1, 49 ] } }, { "scalar_type": "String", "nullable": false } ] } } }, { "Literal": [ { "Ok": { "data": [ 19, 1, 44 ] } }, { "scalar_type": "String", "nullable": false } ] } ] } } ] } }, { "CallVariadic": { "func": { "RecordCreate": { "field_names": [ "" ] } }, "exprs": [ { "CallVariadic": { "func": { "RecordCreate": { "field_names": [ "value", "sep" ] } }, "exprs": [ { "CallBinary": { "func": "TextConcat", "expr1": { "Column": [ 2, null ] }, "expr2": { "Literal": [ { "Ok": { "data": [ 19, 1, 50 ] } }, { "scalar_type": "String", "nullable": false } ] } } }, { "Literal": [ { "Ok": { "data": [ 19, 1, 44 ] } }, { "scalar_type": "String", "nullable": false } ] } ] } } ] } } ], "predicates": [], "projection": [ 1, 3, 1, 1, 1, 4 ], "input_arity": 2 } } }, "plan": { "Collation": { "accumulable": { "full_aggrs": [ { "func": "Count", "expr": { "Column": [ 1, "b" ] }, "distinct": true }, { "func": "SumInt32", "expr": { "Column": [ 1, "b" ] }, "distinct": false } ], "simple_aggrs": [ [ 1, 4, { "func": "SumInt32", "expr": { "Column": [ 1, "b" ] }, "distinct": false } ] ], "distinct_aggrs": [ [ 0, 0, { "func": "Count", "expr": { "Column": [ 1, "b" ] }, "distinct": true } ] ] }, "hierarchical": { "Monotonic": { "aggr_funcs": [ "MinInt32", "MaxInt32" ], "skips": [ 2, 0 ], "must_consolidate": true } }, "basic": { "Multiple": [ [ 1, { "func": { "StringAgg": { "order_by": [] } }, "expr": { "CallVariadic": { "func": { "RecordCreate": { "field_names": [ "" ] } }, "exprs": [ { "CallVariadic": { "func": { "RecordCreate": { "field_names": [ "value", "sep" ] } }, "exprs": [ { "CallBinary": { "func": "TextConcat", "expr1": { "CallUnary": { "func": { "CastInt32ToString": null }, "expr": { "Column": [ 1, "b" ] } } }, "expr2": { "Literal": [ { "Ok": { "data": [ 19, 1, 49 ] } }, { "scalar_type": "String", "nullable": false } ] } } }, { "Literal": [ { "Ok": { "data": [ 19, 1, 44 ] } }, { "scalar_type": "String", "nullable": false } ] } ] } } ] } }, "distinct": false } ], [ 5, { "func": { "StringAgg": { "order_by": [] } }, "expr": { "CallVariadic": { "func": { "RecordCreate": { "field_names": [ "" ] } }, "exprs": [ { "CallVariadic": { "func": { "RecordCreate": { "field_names": [ "value", "sep" ] } }, "exprs": [ { "CallBinary": { "func": "TextConcat", "expr1": { "CallUnary": { "func": { "CastInt32ToString": null }, "expr": { "Column": [ 1, "b" ] } } }, "expr2": { "Literal": [ { "Ok": { "data": [ 19, 1, 50 ] } }, { "scalar_type": "String", "nullable": false } ] } } }, { "Literal": [ { "Ok": { "data": [ 19, 1, 44 ] } }, { "scalar_type": "String", "nullable": false } ] } ] } } ] } }, "distinct": false } ] ] }, "aggregate_types": [ "Accumulable", "Basic", "Hierarchical", "Hierarchical", "Accumulable", "Basic" ] } }, "input_key": [ { "Column": [ 0, "a" ] } ], "mfp_after": { "expressions": [], "predicates": [], "projection": [ 0, 1, 2, 3, 4, 5, 6 ], "input_arity": 7 } } } } } ], "sources": [] } EOF # Test Reduce::Collated (global aggregate). query T multiline EXPLAIN PHYSICAL PLAN AS JSON FOR MATERIALIZED VIEW collated_global_mv ---- { "plans": [ { "id": "materialize.public.collated_global_mv", "plan": { "lir_id": 11, "node": { "Let": { "id": 0, "value": { "lir_id": 2, "node": { "Reduce": { "input": { "lir_id": 1, "node": { "Get": { "id": { "Global": { "User": 1 } }, "keys": { "raw": false, "arranged": [ [ [ { "Column": [ 0, "a" ] } ], [ 0, 1 ], [ 1 ] ] ], "types": [ { "scalar_type": "Int32", "nullable": true }, { "scalar_type": "Int32", "nullable": true } ] }, "plan": { "Arrangement": [ [ { "Column": [ 0, "a" ] } ], null, { "expressions": [], "predicates": [], "projection": [ 1 ], "input_arity": 2 } ] } } } }, "key_val_plan": { "key_plan": { "mfp": { "expressions": [], "predicates": [], "projection": [], "input_arity": 1 } }, "val_plan": { "mfp": { "expressions": [ { "CallUnary": { "func": { "CastInt32ToString": null }, "expr": { "Column": [ 0, "b" ] } } }, { "CallVariadic": { "func": { "RecordCreate": { "field_names": [ "" ] } }, "exprs": [ { "CallVariadic": { "func": { "RecordCreate": { "field_names": [ "value", "sep" ] } }, "exprs": [ { "CallBinary": { "func": "TextConcat", "expr1": { "Column": [ 1, null ] }, "expr2": { "Literal": [ { "Ok": { "data": [ 19, 1, 49 ] } }, { "scalar_type": "String", "nullable": false } ] } } }, { "Literal": [ { "Ok": { "data": [ 19, 1, 44 ] } }, { "scalar_type": "String", "nullable": false } ] } ] } } ] } }, { "CallVariadic": { "func": { "RecordCreate": { "field_names": [ "" ] } }, "exprs": [ { "CallVariadic": { "func": { "RecordCreate": { "field_names": [ "value", "sep" ] } }, "exprs": [ { "CallBinary": { "func": "TextConcat", "expr1": { "Column": [ 1, null ] }, "expr2": { "Literal": [ { "Ok": { "data": [ 19, 1, 50 ] } }, { "scalar_type": "String", "nullable": false } ] } } }, { "Literal": [ { "Ok": { "data": [ 19, 1, 44 ] } }, { "scalar_type": "String", "nullable": false } ] } ] } } ] } } ], "predicates": [], "projection": [ 0, 2, 0, 0, 0, 3 ], "input_arity": 1 } } }, "plan": { "Collation": { "accumulable": { "full_aggrs": [ { "func": "Count", "expr": { "Column": [ 0, "b" ] }, "distinct": true }, { "func": "SumInt32", "expr": { "Column": [ 0, "b" ] }, "distinct": false } ], "simple_aggrs": [ [ 1, 4, { "func": "SumInt32", "expr": { "Column": [ 0, "b" ] }, "distinct": false } ] ], "distinct_aggrs": [ [ 0, 0, { "func": "Count", "expr": { "Column": [ 0, "b" ] }, "distinct": true } ] ] }, "hierarchical": { "Bucketed": { "aggr_funcs": [ "MinInt32", "MaxInt32" ], "skips": [ 2, 0 ], "buckets": [ 268435456, 16777216, 1048576, 65536, 4096, 256, 16 ] } }, "basic": { "Multiple": [ [ 1, { "func": { "StringAgg": { "order_by": [] } }, "expr": { "CallVariadic": { "func": { "RecordCreate": { "field_names": [ "" ] } }, "exprs": [ { "CallVariadic": { "func": { "RecordCreate": { "field_names": [ "value", "sep" ] } }, "exprs": [ { "CallBinary": { "func": "TextConcat", "expr1": { "CallUnary": { "func": { "CastInt32ToString": null }, "expr": { "Column": [ 0, "b" ] } } }, "expr2": { "Literal": [ { "Ok": { "data": [ 19, 1, 49 ] } }, { "scalar_type": "String", "nullable": false } ] } } }, { "Literal": [ { "Ok": { "data": [ 19, 1, 44 ] } }, { "scalar_type": "String", "nullable": false } ] } ] } } ] } }, "distinct": false } ], [ 5, { "func": { "StringAgg": { "order_by": [] } }, "expr": { "CallVariadic": { "func": { "RecordCreate": { "field_names": [ "" ] } }, "exprs": [ { "CallVariadic": { "func": { "RecordCreate": { "field_names": [ "value", "sep" ] } }, "exprs": [ { "CallBinary": { "func": "TextConcat", "expr1": { "CallUnary": { "func": { "CastInt32ToString": null }, "expr": { "Column": [ 0, "b" ] } } }, "expr2": { "Literal": [ { "Ok": { "data": [ 19, 1, 50 ] } }, { "scalar_type": "String", "nullable": false } ] } } }, { "Literal": [ { "Ok": { "data": [ 19, 1, 44 ] } }, { "scalar_type": "String", "nullable": false } ] } ] } } ] } }, "distinct": false } ] ] }, "aggregate_types": [ "Accumulable", "Basic", "Hierarchical", "Hierarchical", "Accumulable", "Basic" ] } }, "input_key": null, "mfp_after": { "expressions": [], "predicates": [], "projection": [ 0, 1, 2, 3, 4, 5 ], "input_arity": 6 } } } }, "body": { "lir_id": 10, "node": { "Union": { "inputs": [ { "lir_id": 9, "node": { "ArrangeBy": { "input": { "lir_id": 3, "node": { "Get": { "id": { "Local": 0 }, "keys": { "raw": false, "arranged": [ [ [], [ 0, 1, 2, 3, 4, 5 ], [ 0, 1, 2, 3, 4, 5 ] ] ], "types": null }, "plan": "PassArrangements" } } }, "forms": { "raw": true, "arranged": [], "types": null }, "input_key": [], "input_mfp": { "expressions": [], "predicates": [], "projection": [ 0, 1, 2, 3, 4, 5 ], "input_arity": 6 } } } }, { "lir_id": 8, "node": { "Mfp": { "input": { "lir_id": 7, "node": { "Union": { "inputs": [ { "lir_id": 5, "node": { "Negate": { "input": { "lir_id": 4, "node": { "Get": { "id": { "Local": 0 }, "keys": { "raw": false, "arranged": [ [ [], [ 0, 1, 2, 3, 4, 5 ], [ 0, 1, 2, 3, 4, 5 ] ] ], "types": null }, "plan": { "Arrangement": [ [], null, { "expressions": [], "predicates": [], "projection": [], "input_arity": 6 } ] } } } } } } }, { "lir_id": 6, "node": { "Constant": { "rows": { "Ok": [ [ { "data": [] }, 0, 1 ] ] } } } } ], "consolidate_output": true } } }, "mfp": { "expressions": [ { "Literal": [ { "Ok": { "data": [ 49 ] } }, { "scalar_type": "Int64", "nullable": false } ] }, { "Literal": [ { "Ok": { "data": [ 0 ] } }, { "scalar_type": "String", "nullable": true } ] }, { "Literal": [ { "Ok": { "data": [ 0 ] } }, { "scalar_type": "Int32", "nullable": true } ] }, { "Literal": [ { "Ok": { "data": [ 0 ] } }, { "scalar_type": "Int32", "nullable": true } ] }, { "Literal": [ { "Ok": { "data": [ 0 ] } }, { "scalar_type": "Int64", "nullable": true } ] }, { "Literal": [ { "Ok": { "data": [ 0 ] } }, { "scalar_type": "String", "nullable": true } ] } ], "predicates": [], "projection": [ 0, 1, 2, 3, 4, 5 ], "input_arity": 0 }, "input_key_val": null } } } ], "consolidate_output": false } } } } } } } ], "sources": [] } EOF # Test Reduce::Collated (global aggregate, one-shot). query T multiline EXPLAIN PHYSICAL PLAN AS JSON FOR SELECT * FROM collated_global ---- { "plans": [ { "id": "Explained Query", "plan": { "lir_id": 11, "node": { "Let": { "id": 0, "value": { "lir_id": 2, "node": { "Reduce": { "input": { "lir_id": 1, "node": { "Get": { "id": { "Global": { "User": 1 } }, "keys": { "raw": false, "arranged": [ [ [ { "Column": [ 0, "a" ] } ], [ 0, 1 ], [ 1 ] ] ], "types": [ { "scalar_type": "Int32", "nullable": true }, { "scalar_type": "Int32", "nullable": true } ] }, "plan": { "Arrangement": [ [ { "Column": [ 0, "a" ] } ], null, { "expressions": [], "predicates": [], "projection": [ 1 ], "input_arity": 2 } ] } } } }, "key_val_plan": { "key_plan": { "mfp": { "expressions": [], "predicates": [], "projection": [], "input_arity": 1 } }, "val_plan": { "mfp": { "expressions": [ { "CallUnary": { "func": { "CastInt32ToString": null }, "expr": { "Column": [ 0, "b" ] } } }, { "CallVariadic": { "func": { "RecordCreate": { "field_names": [ "" ] } }, "exprs": [ { "CallVariadic": { "func": { "RecordCreate": { "field_names": [ "value", "sep" ] } }, "exprs": [ { "CallBinary": { "func": "TextConcat", "expr1": { "Column": [ 1, null ] }, "expr2": { "Literal": [ { "Ok": { "data": [ 19, 1, 49 ] } }, { "scalar_type": "String", "nullable": false } ] } } }, { "Literal": [ { "Ok": { "data": [ 19, 1, 44 ] } }, { "scalar_type": "String", "nullable": false } ] } ] } } ] } }, { "CallVariadic": { "func": { "RecordCreate": { "field_names": [ "" ] } }, "exprs": [ { "CallVariadic": { "func": { "RecordCreate": { "field_names": [ "value", "sep" ] } }, "exprs": [ { "CallBinary": { "func": "TextConcat", "expr1": { "Column": [ 1, null ] }, "expr2": { "Literal": [ { "Ok": { "data": [ 19, 1, 50 ] } }, { "scalar_type": "String", "nullable": false } ] } } }, { "Literal": [ { "Ok": { "data": [ 19, 1, 44 ] } }, { "scalar_type": "String", "nullable": false } ] } ] } } ] } } ], "predicates": [], "projection": [ 0, 2, 0, 0, 0, 3 ], "input_arity": 1 } } }, "plan": { "Collation": { "accumulable": { "full_aggrs": [ { "func": "Count", "expr": { "Column": [ 0, "b" ] }, "distinct": true }, { "func": "SumInt32", "expr": { "Column": [ 0, "b" ] }, "distinct": false } ], "simple_aggrs": [ [ 1, 4, { "func": "SumInt32", "expr": { "Column": [ 0, "b" ] }, "distinct": false } ] ], "distinct_aggrs": [ [ 0, 0, { "func": "Count", "expr": { "Column": [ 0, "b" ] }, "distinct": true } ] ] }, "hierarchical": { "Monotonic": { "aggr_funcs": [ "MinInt32", "MaxInt32" ], "skips": [ 2, 0 ], "must_consolidate": true } }, "basic": { "Multiple": [ [ 1, { "func": { "StringAgg": { "order_by": [] } }, "expr": { "CallVariadic": { "func": { "RecordCreate": { "field_names": [ "" ] } }, "exprs": [ { "CallVariadic": { "func": { "RecordCreate": { "field_names": [ "value", "sep" ] } }, "exprs": [ { "CallBinary": { "func": "TextConcat", "expr1": { "CallUnary": { "func": { "CastInt32ToString": null }, "expr": { "Column": [ 0, "b" ] } } }, "expr2": { "Literal": [ { "Ok": { "data": [ 19, 1, 49 ] } }, { "scalar_type": "String", "nullable": false } ] } } }, { "Literal": [ { "Ok": { "data": [ 19, 1, 44 ] } }, { "scalar_type": "String", "nullable": false } ] } ] } } ] } }, "distinct": false } ], [ 5, { "func": { "StringAgg": { "order_by": [] } }, "expr": { "CallVariadic": { "func": { "RecordCreate": { "field_names": [ "" ] } }, "exprs": [ { "CallVariadic": { "func": { "RecordCreate": { "field_names": [ "value", "sep" ] } }, "exprs": [ { "CallBinary": { "func": "TextConcat", "expr1": { "CallUnary": { "func": { "CastInt32ToString": null }, "expr": { "Column": [ 0, "b" ] } } }, "expr2": { "Literal": [ { "Ok": { "data": [ 19, 1, 50 ] } }, { "scalar_type": "String", "nullable": false } ] } } }, { "Literal": [ { "Ok": { "data": [ 19, 1, 44 ] } }, { "scalar_type": "String", "nullable": false } ] } ] } } ] } }, "distinct": false } ] ] }, "aggregate_types": [ "Accumulable", "Basic", "Hierarchical", "Hierarchical", "Accumulable", "Basic" ] } }, "input_key": null, "mfp_after": { "expressions": [], "predicates": [], "projection": [ 0, 1, 2, 3, 4, 5 ], "input_arity": 6 } } } }, "body": { "lir_id": 10, "node": { "Union": { "inputs": [ { "lir_id": 9, "node": { "ArrangeBy": { "input": { "lir_id": 3, "node": { "Get": { "id": { "Local": 0 }, "keys": { "raw": false, "arranged": [ [ [], [ 0, 1, 2, 3, 4, 5 ], [ 0, 1, 2, 3, 4, 5 ] ] ], "types": null }, "plan": "PassArrangements" } } }, "forms": { "raw": true, "arranged": [], "types": null }, "input_key": [], "input_mfp": { "expressions": [], "predicates": [], "projection": [ 0, 1, 2, 3, 4, 5 ], "input_arity": 6 } } } }, { "lir_id": 8, "node": { "Mfp": { "input": { "lir_id": 7, "node": { "Union": { "inputs": [ { "lir_id": 5, "node": { "Negate": { "input": { "lir_id": 4, "node": { "Get": { "id": { "Local": 0 }, "keys": { "raw": false, "arranged": [ [ [], [ 0, 1, 2, 3, 4, 5 ], [ 0, 1, 2, 3, 4, 5 ] ] ], "types": null }, "plan": { "Arrangement": [ [], null, { "expressions": [], "predicates": [], "projection": [], "input_arity": 6 } ] } } } } } } }, { "lir_id": 6, "node": { "Constant": { "rows": { "Ok": [ [ { "data": [] }, 0, 1 ] ] } } } } ], "consolidate_output": true } } }, "mfp": { "expressions": [ { "Literal": [ { "Ok": { "data": [ 49 ] } }, { "scalar_type": "Int64", "nullable": false } ] }, { "Literal": [ { "Ok": { "data": [ 0 ] } }, { "scalar_type": "String", "nullable": true } ] }, { "Literal": [ { "Ok": { "data": [ 0 ] } }, { "scalar_type": "Int32", "nullable": true } ] }, { "Literal": [ { "Ok": { "data": [ 0 ] } }, { "scalar_type": "Int32", "nullable": true } ] }, { "Literal": [ { "Ok": { "data": [ 0 ] } }, { "scalar_type": "Int64", "nullable": true } ] }, { "Literal": [ { "Ok": { "data": [ 0 ] } }, { "scalar_type": "String", "nullable": true } ] } ], "predicates": [], "projection": [ 0, 1, 2, 3, 4, 5 ], "input_arity": 0 }, "input_key_val": null } } } ], "consolidate_output": false } } } } } } } ], "sources": [] } EOF # Test Join::Differential (acyclic). query T multiline EXPLAIN PHYSICAL PLAN WITH(non negative) AS JSON FOR SELECT b + d, c + e, a + e FROM t, u, v WHERE a = c AND d = e AND b + d > 42 ---- { "plans": [ { "id": "Explained Query", "plan": { "lir_id": 6, "node": { "Join": { "inputs": [ { "lir_id": 1, "node": { "Get": { "id": { "Global": { "User": 1 } }, "keys": { "raw": false, "arranged": [ [ [ { "Column": [ 0, "a" ] } ], [ 0, 1 ], [ 1 ] ] ], "types": [ { "scalar_type": "Int32", "nullable": true }, { "scalar_type": "Int32", "nullable": true } ] }, "plan": "PassArrangements" } } }, { "lir_id": 3, "node": { "ArrangeBy": { "input": { "lir_id": 2, "node": { "Get": { "id": { "Global": { "User": 2 } }, "keys": { "raw": true, "arranged": [], "types": null }, "plan": { "Collection": { "expressions": [], "predicates": [], "projection": [ 0, 1 ], "input_arity": 2 } } } } }, "forms": { "raw": true, "arranged": [ [ [ { "Column": [ 0, "c" ] } ], [ 0, 1 ], [ 1 ] ], [ [ { "Column": [ 1, "d" ] } ], [ 1, 0 ], [ 0 ] ] ], "types": [ { "scalar_type": "Int32", "nullable": false }, { "scalar_type": "Int32", "nullable": false } ] }, "input_key": null, "input_mfp": { "expressions": [], "predicates": [], "projection": [ 0, 1 ], "input_arity": 2 } } } }, { "lir_id": 5, "node": { "ArrangeBy": { "input": { "lir_id": 4, "node": { "Get": { "id": { "Global": { "User": 3 } }, "keys": { "raw": true, "arranged": [], "types": null }, "plan": { "Collection": { "expressions": [], "predicates": [], "projection": [ 0 ], "input_arity": 1 } } } } }, "forms": { "raw": true, "arranged": [ [ [ { "Column": [ 0, "e" ] } ], [ 0 ], [] ] ], "types": [ { "scalar_type": "Int32", "nullable": false } ] }, "input_key": null, "input_mfp": { "expressions": [], "predicates": [], "projection": [ 0 ], "input_arity": 1 } } } } ], "plan": { "Delta": { "path_plans": [ { "source_relation": 0, "source_key": [ { "Column": [ 0, "a" ] } ], "initial_closure": { "ready_equivalences": [], "before": { "mfp": { "expressions": [], "predicates": [], "projection": [ 0, 1 ], "input_arity": 2 } } }, "stage_plans": [ { "lookup_relation": 1, "stream_key": [ { "Column": [ 0, "a" ] } ], "stream_thinning": [ 1 ], "lookup_key": [ { "Column": [ 0, "c" ] } ], "closure": { "ready_equivalences": [], "before": { "mfp": { "expressions": [ { "CallBinary": { "func": "AddInt32", "expr1": { "Column": [ 1, "b" ] }, "expr2": { "Column": [ 2, "d" ] } } }, { "CallBinary": { "func": "AddInt32", "expr1": { "Column": [ 0, "a" ] }, "expr2": { "Column": [ 2, "d" ] } } } ], "predicates": [ [ 4, { "CallBinary": { "func": "Gt", "expr1": { "Column": [ 3, null ] }, "expr2": { "Literal": [ { "Ok": { "data": [ 45, 42 ] } }, { "scalar_type": "Int32", "nullable": false } ] } } } ] ], "projection": [ 2, 3, 4 ], "input_arity": 3 } } } }, { "lookup_relation": 2, "stream_key": [ { "Column": [ 0, "d" ] } ], "stream_thinning": [ 1, 2 ], "lookup_key": [ { "Column": [ 0, "e" ] } ], "closure": { "ready_equivalences": [], "before": { "mfp": { "expressions": [], "predicates": [], "projection": [ 1, 2 ], "input_arity": 3 } } } } ], "final_closure": { "ready_equivalences": [], "before": { "mfp": { "expressions": [], "predicates": [], "projection": [ 0, 1, 1 ], "input_arity": 2 } } } }, { "source_relation": 1, "source_key": [ { "Column": [ 0, "c" ] } ], "initial_closure": { "ready_equivalences": [], "before": { "mfp": { "expressions": [], "predicates": [], "projection": [ 0, 1 ], "input_arity": 2 } } }, "stage_plans": [ { "lookup_relation": 0, "stream_key": [ { "Column": [ 0, "c" ] } ], "stream_thinning": [ 1 ], "lookup_key": [ { "Column": [ 0, "a" ] } ], "closure": { "ready_equivalences": [], "before": { "mfp": { "expressions": [ { "CallBinary": { "func": "AddInt32", "expr1": { "Column": [ 2, "b" ] }, "expr2": { "Column": [ 1, "d" ] } } }, { "CallBinary": { "func": "AddInt32", "expr1": { "Column": [ 0, "a" ] }, "expr2": { "Column": [ 1, "d" ] } } } ], "predicates": [ [ 4, { "CallBinary": { "func": "Gt", "expr1": { "Column": [ 3, null ] }, "expr2": { "Literal": [ { "Ok": { "data": [ 45, 42 ] } }, { "scalar_type": "Int32", "nullable": false } ] } } } ] ], "projection": [ 1, 3, 4 ], "input_arity": 3 } } } }, { "lookup_relation": 2, "stream_key": [ { "Column": [ 0, "d" ] } ], "stream_thinning": [ 1, 2 ], "lookup_key": [ { "Column": [ 0, "e" ] } ], "closure": { "ready_equivalences": [], "before": { "mfp": { "expressions": [], "predicates": [], "projection": [ 1, 2 ], "input_arity": 3 } } } } ], "final_closure": { "ready_equivalences": [], "before": { "mfp": { "expressions": [], "predicates": [], "projection": [ 0, 1, 1 ], "input_arity": 2 } } } }, { "source_relation": 2, "source_key": [ { "Column": [ 0, "e" ] } ], "initial_closure": { "ready_equivalences": [], "before": { "mfp": { "expressions": [], "predicates": [], "projection": [ 0 ], "input_arity": 1 } } }, "stage_plans": [ { "lookup_relation": 1, "stream_key": [ { "Column": [ 0, "e" ] } ], "stream_thinning": [], "lookup_key": [ { "Column": [ 1, "d" ] } ], "closure": { "ready_equivalences": [], "before": { "mfp": { "expressions": [], "predicates": [], "projection": [ 1, 0 ], "input_arity": 2 } } } }, { "lookup_relation": 0, "stream_key": [ { "Column": [ 0, "c" ] } ], "stream_thinning": [ 1 ], "lookup_key": [ { "Column": [ 0, "a" ] } ], "closure": { "ready_equivalences": [], "before": { "mfp": { "expressions": [ { "CallBinary": { "func": "AddInt32", "expr1": { "Column": [ 2, "b" ] }, "expr2": { "Column": [ 1, "d" ] } } }, { "CallBinary": { "func": "AddInt32", "expr1": { "Column": [ 0, "a" ] }, "expr2": { "Column": [ 1, "d" ] } } } ], "predicates": [ [ 4, { "CallBinary": { "func": "Gt", "expr1": { "Column": [ 3, null ] }, "expr2": { "Literal": [ { "Ok": { "data": [ 45, 42 ] } }, { "scalar_type": "Int32", "nullable": false } ] } } } ] ], "projection": [ 3, 4 ], "input_arity": 3 } } } } ], "final_closure": { "ready_equivalences": [], "before": { "mfp": { "expressions": [], "predicates": [], "projection": [ 0, 1, 1 ], "input_arity": 2 } } } } ] } } } } } } ], "sources": [ { "id": { "User": 2 }, "op": { "expressions": [], "predicates": [ [ 1, { "CallUnary": { "func": { "Not": null }, "expr": { "CallUnary": { "func": { "IsNull": null }, "expr": { "Column": [ 0, "c" ] } } } } } ], [ 2, { "CallUnary": { "func": { "Not": null }, "expr": { "CallUnary": { "func": { "IsNull": null }, "expr": { "Column": [ 1, "d" ] } } } } } ] ], "projection": [ 0, 1 ], "input_arity": 2 } }, { "id": { "User": 3 }, "op": { "expressions": [], "predicates": [ [ 1, { "CallUnary": { "func": { "Not": null }, "expr": { "CallUnary": { "func": { "IsNull": null }, "expr": { "Column": [ 0, "e" ] } } } } } ] ], "projection": [ 0 ], "input_arity": 2 } } ] } EOF # Create indexes required for differential join tests statement ok CREATE INDEX u_c_idx ON U(c); statement ok CREATE INDEX u_d_idx ON U(d); statement ok CREATE INDEX v_e_idx ON V(e); # Test Join::Differential (cyclic). query T multiline EXPLAIN PHYSICAL PLAN WITH(non negative) AS JSON FOR SELECT a, b, c, d, e, f FROM t, u, v WHERE a = c AND d = e AND f = a ---- { "plans": [ { "id": "Explained Query", "plan": { "lir_id": 6, "node": { "Join": { "inputs": [ { "lir_id": 1, "node": { "Get": { "id": { "Global": { "User": 1 } }, "keys": { "raw": false, "arranged": [ [ [ { "Column": [ 0, "a" ] } ], [ 0, 1 ], [ 1 ] ] ], "types": [ { "scalar_type": "Int32", "nullable": true }, { "scalar_type": "Int32", "nullable": true } ] }, "plan": "PassArrangements" } } }, { "lir_id": 3, "node": { "ArrangeBy": { "input": { "lir_id": 2, "node": { "Get": { "id": { "Global": { "User": 2 } }, "keys": { "raw": false, "arranged": [ [ [ { "Column": [ 0, "c" ] } ], [ 0, 1 ], [ 1 ] ] ], "types": [ { "scalar_type": "Int32", "nullable": true }, { "scalar_type": "Int32", "nullable": true } ] }, "plan": { "Arrangement": [ [ { "Column": [ 0, "c" ] } ], null, { "expressions": [], "predicates": [ [ 1, { "CallUnary": { "func": { "Not": null }, "expr": { "CallUnary": { "func": { "IsNull": null }, "expr": { "Column": [ 0, "c" ] } } } } } ], [ 2, { "CallUnary": { "func": { "Not": null }, "expr": { "CallUnary": { "func": { "IsNull": null }, "expr": { "Column": [ 1, "d" ] } } } } } ] ], "projection": [ 0, 1 ], "input_arity": 2 } ] } } } }, "forms": { "raw": true, "arranged": [ [ [ { "Column": [ 0, "c" ] } ], [ 0, 1 ], [ 1 ] ], [ [ { "Column": [ 0, "c" ] }, { "Column": [ 1, "d" ] } ], [ 0, 1 ], [] ] ], "types": [ { "scalar_type": "Int32", "nullable": false }, { "scalar_type": "Int32", "nullable": false } ] }, "input_key": null, "input_mfp": { "expressions": [], "predicates": [], "projection": [ 0, 1 ], "input_arity": 2 } } } }, { "lir_id": 5, "node": { "ArrangeBy": { "input": { "lir_id": 4, "node": { "Get": { "id": { "Global": { "User": 3 } }, "keys": { "raw": false, "arranged": [ [ [ { "Column": [ 0, "e" ] } ], [ 0, 1 ], [ 1 ] ] ], "types": [ { "scalar_type": "Int32", "nullable": true }, { "scalar_type": "Int32", "nullable": true } ] }, "plan": { "Arrangement": [ [ { "Column": [ 0, "e" ] } ], null, { "expressions": [], "predicates": [ [ 1, { "CallUnary": { "func": { "Not": null }, "expr": { "CallUnary": { "func": { "IsNull": null }, "expr": { "Column": [ 0, "e" ] } } } } } ], [ 2, { "CallUnary": { "func": { "Not": null }, "expr": { "CallUnary": { "func": { "IsNull": null }, "expr": { "Column": [ 1, "f" ] } } } } } ] ], "projection": [ 0, 1 ], "input_arity": 2 } ] } } } }, "forms": { "raw": true, "arranged": [ [ [ { "Column": [ 0, "e" ] }, { "Column": [ 1, "f" ] } ], [ 0, 1 ], [] ] ], "types": [ { "scalar_type": "Int32", "nullable": false }, { "scalar_type": "Int32", "nullable": false } ] }, "input_key": null, "input_mfp": { "expressions": [], "predicates": [], "projection": [ 0, 1 ], "input_arity": 2 } } } } ], "plan": { "Delta": { "path_plans": [ { "source_relation": 0, "source_key": [ { "Column": [ 0, "a" ] } ], "initial_closure": { "ready_equivalences": [], "before": { "mfp": { "expressions": [], "predicates": [], "projection": [ 0, 1 ], "input_arity": 2 } } }, "stage_plans": [ { "lookup_relation": 1, "stream_key": [ { "Column": [ 0, "a" ] } ], "stream_thinning": [ 1 ], "lookup_key": [ { "Column": [ 0, "c" ] } ], "closure": { "ready_equivalences": [], "before": { "mfp": { "expressions": [], "predicates": [], "projection": [ 0, 1, 2 ], "input_arity": 3 } } } }, { "lookup_relation": 2, "stream_key": [ { "Column": [ 2, "d" ] }, { "Column": [ 0, "a" ] } ], "stream_thinning": [ 1 ], "lookup_key": [ { "Column": [ 0, "e" ] }, { "Column": [ 1, "f" ] } ], "closure": { "ready_equivalences": [], "before": { "mfp": { "expressions": [], "predicates": [], "projection": [ 1, 2, 0 ], "input_arity": 3 } } } } ], "final_closure": { "ready_equivalences": [], "before": { "mfp": { "expressions": [], "predicates": [], "projection": [ 0, 1, 0, 2, 2, 0 ], "input_arity": 3 } } } }, { "source_relation": 1, "source_key": [ { "Column": [ 0, "c" ] } ], "initial_closure": { "ready_equivalences": [], "before": { "mfp": { "expressions": [], "predicates": [], "projection": [ 0, 1 ], "input_arity": 2 } } }, "stage_plans": [ { "lookup_relation": 2, "stream_key": [ { "Column": [ 1, "d" ] }, { "Column": [ 0, "c" ] } ], "stream_thinning": [], "lookup_key": [ { "Column": [ 0, "e" ] }, { "Column": [ 1, "f" ] } ], "closure": { "ready_equivalences": [], "before": { "mfp": { "expressions": [], "predicates": [], "projection": [ 1, 0 ], "input_arity": 2 } } } }, { "lookup_relation": 0, "stream_key": [ { "Column": [ 0, "c" ] } ], "stream_thinning": [ 1 ], "lookup_key": [ { "Column": [ 0, "a" ] } ], "closure": { "ready_equivalences": [], "before": { "mfp": { "expressions": [], "predicates": [], "projection": [ 0, 2, 1 ], "input_arity": 3 } } } } ], "final_closure": { "ready_equivalences": [], "before": { "mfp": { "expressions": [], "predicates": [], "projection": [ 0, 1, 0, 2, 2, 0 ], "input_arity": 3 } } } }, { "source_relation": 2, "source_key": [ { "Column": [ 0, "e" ] }, { "Column": [ 1, "f" ] } ], "initial_closure": { "ready_equivalences": [], "before": { "mfp": { "expressions": [], "predicates": [], "projection": [ 0, 1 ], "input_arity": 2 } } }, "stage_plans": [ { "lookup_relation": 1, "stream_key": [ { "Column": [ 1, "f" ] }, { "Column": [ 0, "e" ] } ], "stream_thinning": [], "lookup_key": [ { "Column": [ 0, "c" ] }, { "Column": [ 1, "d" ] } ], "closure": { "ready_equivalences": [], "before": { "mfp": { "expressions": [], "predicates": [], "projection": [ 1, 0 ], "input_arity": 2 } } } }, { "lookup_relation": 0, "stream_key": [ { "Column": [ 1, "f" ] } ], "stream_thinning": [ 0 ], "lookup_key": [ { "Column": [ 0, "a" ] } ], "closure": { "ready_equivalences": [], "before": { "mfp": { "expressions": [], "predicates": [], "projection": [ 0, 2, 1 ], "input_arity": 3 } } } } ], "final_closure": { "ready_equivalences": [], "before": { "mfp": { "expressions": [], "predicates": [], "projection": [ 0, 1, 0, 2, 2, 0 ], "input_arity": 3 } } } } ] } } } } } } ], "sources": [] } EOF # Test Join::Delta (star). query T multiline EXPLAIN PHYSICAL PLAN WITH(non negative) AS JSON FOR SELECT a, b, c, d, e, f FROM t, u, v WHERE a = c and a = e ---- { "plans": [ { "id": "Explained Query", "plan": { "lir_id": 4, "node": { "Join": { "inputs": [ { "lir_id": 1, "node": { "Get": { "id": { "Global": { "User": 1 } }, "keys": { "raw": false, "arranged": [ [ [ { "Column": [ 0, "a" ] } ], [ 0, 1 ], [ 1 ] ] ], "types": [ { "scalar_type": "Int32", "nullable": true }, { "scalar_type": "Int32", "nullable": true } ] }, "plan": "PassArrangements" } } }, { "lir_id": 2, "node": { "Get": { "id": { "Global": { "User": 2 } }, "keys": { "raw": false, "arranged": [ [ [ { "Column": [ 0, "c" ] } ], [ 0, 1 ], [ 1 ] ] ], "types": [ { "scalar_type": "Int32", "nullable": true }, { "scalar_type": "Int32", "nullable": true } ] }, "plan": "PassArrangements" } } }, { "lir_id": 3, "node": { "Get": { "id": { "Global": { "User": 3 } }, "keys": { "raw": false, "arranged": [ [ [ { "Column": [ 0, "e" ] } ], [ 0, 1 ], [ 1 ] ] ], "types": [ { "scalar_type": "Int32", "nullable": true }, { "scalar_type": "Int32", "nullable": true } ] }, "plan": "PassArrangements" } } } ], "plan": { "Delta": { "path_plans": [ { "source_relation": 0, "source_key": [ { "Column": [ 0, "a" ] } ], "initial_closure": { "ready_equivalences": [], "before": { "mfp": { "expressions": [], "predicates": [ [ 1, { "CallUnary": { "func": { "Not": null }, "expr": { "CallUnary": { "func": { "IsNull": null }, "expr": { "Column": [ 0, "a" ] } } } } } ] ], "projection": [ 0, 1 ], "input_arity": 2 } } }, "stage_plans": [ { "lookup_relation": 1, "stream_key": [ { "Column": [ 0, "a" ] } ], "stream_thinning": [ 1 ], "lookup_key": [ { "Column": [ 0, "c" ] } ], "closure": { "ready_equivalences": [], "before": { "mfp": { "expressions": [], "predicates": [], "projection": [ 0, 1, 2 ], "input_arity": 3 } } } }, { "lookup_relation": 2, "stream_key": [ { "Column": [ 0, "a" ] } ], "stream_thinning": [ 1, 2 ], "lookup_key": [ { "Column": [ 0, "e" ] } ], "closure": { "ready_equivalences": [], "before": { "mfp": { "expressions": [], "predicates": [], "projection": [ 0, 1, 2, 3 ], "input_arity": 4 } } } } ], "final_closure": { "ready_equivalences": [], "before": { "mfp": { "expressions": [], "predicates": [], "projection": [ 0, 1, 0, 2, 0, 3 ], "input_arity": 4 } } } }, { "source_relation": 1, "source_key": [ { "Column": [ 0, "c" ] } ], "initial_closure": { "ready_equivalences": [], "before": { "mfp": { "expressions": [], "predicates": [], "projection": [ 0, 1 ], "input_arity": 2 } } }, "stage_plans": [ { "lookup_relation": 0, "stream_key": [ { "Column": [ 0, "c" ] } ], "stream_thinning": [ 1 ], "lookup_key": [ { "Column": [ 0, "a" ] } ], "closure": { "ready_equivalences": [], "before": { "mfp": { "expressions": [], "predicates": [ [ 1, { "CallUnary": { "func": { "Not": null }, "expr": { "CallUnary": { "func": { "IsNull": null }, "expr": { "Column": [ 0, "a" ] } } } } } ] ], "projection": [ 0, 2, 0, 1 ], "input_arity": 3 } } } }, { "lookup_relation": 2, "stream_key": [ { "Column": [ 2, "c" ] } ], "stream_thinning": [ 0, 1, 3 ], "lookup_key": [ { "Column": [ 0, "e" ] } ], "closure": { "ready_equivalences": [], "before": { "mfp": { "expressions": [], "predicates": [], "projection": [ 1, 2, 3, 4 ], "input_arity": 5 } } } } ], "final_closure": { "ready_equivalences": [], "before": { "mfp": { "expressions": [], "predicates": [], "projection": [ 0, 1, 0, 2, 0, 3 ], "input_arity": 4 } } } }, { "source_relation": 2, "source_key": [ { "Column": [ 0, "e" ] } ], "initial_closure": { "ready_equivalences": [], "before": { "mfp": { "expressions": [], "predicates": [], "projection": [ 0, 1 ], "input_arity": 2 } } }, "stage_plans": [ { "lookup_relation": 0, "stream_key": [ { "Column": [ 0, "e" ] } ], "stream_thinning": [ 1 ], "lookup_key": [ { "Column": [ 0, "a" ] } ], "closure": { "ready_equivalences": [], "before": { "mfp": { "expressions": [], "predicates": [ [ 1, { "CallUnary": { "func": { "Not": null }, "expr": { "CallUnary": { "func": { "IsNull": null }, "expr": { "Column": [ 0, "a" ] } } } } } ] ], "projection": [ 0, 2, 0, 1 ], "input_arity": 3 } } } }, { "lookup_relation": 1, "stream_key": [ { "Column": [ 2, "e" ] } ], "stream_thinning": [ 0, 1, 3 ], "lookup_key": [ { "Column": [ 0, "c" ] } ], "closure": { "ready_equivalences": [], "before": { "mfp": { "expressions": [], "predicates": [], "projection": [ 1, 2, 4, 3 ], "input_arity": 5 } } } } ], "final_closure": { "ready_equivalences": [], "before": { "mfp": { "expressions": [], "predicates": [], "projection": [ 0, 1, 0, 2, 0, 3 ], "input_arity": 4 } } } } ] } } } } } } ], "sources": [] } EOF