# 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. # Requires stable object IDs reset-server statement ok CREATE TABLE t ( a int, b int ) statement ok CREATE VIEW ov AS SELECT * FROM t ORDER BY b asc, a desc LIMIT 5 statement ok CREATE VIEW v AS SELECT * FROM t WHERE a IS NOT NULL statement ok CREATE DEFAULT INDEX ON v statement ok CREATE MATERIALIZED VIEW mv AS SELECT * FROM t WHERE a IS NOT NULL mode cockroach # Test constant error. query T multiline EXPLAIN DECORRELATED PLAN WITH(raw) AS JSON FOR SELECT 1 / 0 ---- { "Let": { "id": 0, "value": { "Constant": { "rows": { "Ok": [ [ { "data": [] }, 1 ] ] }, "typ": { "column_types": [], "keys": [] } } }, "body": { "Project": { "input": { "Map": { "input": { "Get": { "id": { "Local": 0 }, "typ": { "column_types": [], "keys": [ [] ] }, "access_strategy": "UnknownOrLocal" } }, "scalars": [ { "CallBinary": { "func": "DivInt32", "expr1": { "Literal": [ { "Ok": { "data": [ 45, 1 ] } }, { "scalar_type": "Int32", "nullable": false } ] }, "expr2": { "Literal": [ { "Ok": { "data": [ 44 ] } }, { "scalar_type": "Int32", "nullable": false } ] } } } ] } }, "outputs": [ 0 ] } } } } EOF # Test constant with two elements. query T multiline EXPLAIN DECORRELATED PLAN WITH(raw) AS JSON FOR (SELECT 1, 2) UNION ALL (SELECT 1, 2) UNION ALL (SELECT 3, 4) ---- { "Let": { "id": 0, "value": { "Constant": { "rows": { "Ok": [ [ { "data": [] }, 1 ] ] }, "typ": { "column_types": [], "keys": [] } } }, "body": { "Union": { "base": { "Project": { "input": { "Map": { "input": { "Get": { "id": { "Local": 0 }, "typ": { "column_types": [], "keys": [ [] ] }, "access_strategy": "UnknownOrLocal" } }, "scalars": [ { "Literal": [ { "Ok": { "data": [ 45, 1 ] } }, { "scalar_type": "Int32", "nullable": false } ] }, { "Literal": [ { "Ok": { "data": [ 45, 2 ] } }, { "scalar_type": "Int32", "nullable": false } ] } ] } }, "outputs": [ 0, 1 ] } }, "inputs": [ { "Project": { "input": { "Map": { "input": { "Get": { "id": { "Local": 0 }, "typ": { "column_types": [], "keys": [ [] ] }, "access_strategy": "UnknownOrLocal" } }, "scalars": [ { "Literal": [ { "Ok": { "data": [ 45, 1 ] } }, { "scalar_type": "Int32", "nullable": false } ] }, { "Literal": [ { "Ok": { "data": [ 45, 2 ] } }, { "scalar_type": "Int32", "nullable": false } ] } ] } }, "outputs": [ 0, 1 ] } }, { "Project": { "input": { "Map": { "input": { "Get": { "id": { "Local": 0 }, "typ": { "column_types": [], "keys": [ [] ] }, "access_strategy": "UnknownOrLocal" } }, "scalars": [ { "Literal": [ { "Ok": { "data": [ 45, 3 ] } }, { "scalar_type": "Int32", "nullable": false } ] }, { "Literal": [ { "Ok": { "data": [ 45, 4 ] } }, { "scalar_type": "Int32", "nullable": false } ] } ] } }, "outputs": [ 0, 1 ] } } ] } } } } EOF # Test basic linear chains. query T multiline EXPLAIN DECORRELATED PLAN WITH(raw) AS JSON FOR SELECT 1, a + b as c FROM mv WHERE a > 0 and b < 0 and a + b > 0 ---- { "Let": { "id": 0, "value": { "Constant": { "rows": { "Ok": [ [ { "data": [] }, 1 ] ] }, "typ": { "column_types": [], "keys": [] } } }, "body": { "Project": { "input": { "Map": { "input": { "Let": { "id": 1, "value": { "Filter": { "input": { "Join": { "inputs": [ { "Get": { "id": { "Local": 0 }, "typ": { "column_types": [], "keys": [ [] ] }, "access_strategy": "UnknownOrLocal" } }, { "Get": { "id": { "Global": { "User": 5 } }, "typ": { "column_types": [ { "scalar_type": "Int32", "nullable": false }, { "scalar_type": "Int32", "nullable": true } ], "keys": [] }, "access_strategy": "UnknownOrLocal" } } ], "equivalences": [], "implementation": "Unimplemented" } }, "predicates": [ { "CallVariadic": { "func": "And", "exprs": [ { "CallVariadic": { "func": "And", "exprs": [ { "CallBinary": { "func": "Gt", "expr1": { "Column": [ 0, "a" ] }, "expr2": { "Literal": [ { "Ok": { "data": [ 44 ] } }, { "scalar_type": "Int32", "nullable": false } ] } } }, { "CallBinary": { "func": "Lt", "expr1": { "Column": [ 1, "b" ] }, "expr2": { "Literal": [ { "Ok": { "data": [ 44 ] } }, { "scalar_type": "Int32", "nullable": false } ] } } } ] } }, { "CallBinary": { "func": "Gt", "expr1": { "CallBinary": { "func": "AddInt32", "expr1": { "Column": [ 0, "a" ] }, "expr2": { "Column": [ 1, "b" ] } } }, "expr2": { "Literal": [ { "Ok": { "data": [ 44 ] } }, { "scalar_type": "Int32", "nullable": false } ] } } } ] } } ] } }, "body": { "Get": { "id": { "Local": 1 }, "typ": { "column_types": [ { "scalar_type": "Int32", "nullable": false }, { "scalar_type": "Int32", "nullable": true } ], "keys": [] }, "access_strategy": "UnknownOrLocal" } } } }, "scalars": [ { "Literal": [ { "Ok": { "data": [ 45, 1 ] } }, { "scalar_type": "Int32", "nullable": false } ] }, { "CallBinary": { "func": "AddInt32", "expr1": { "Column": [ 0, "a" ] }, "expr2": { "Column": [ 1, "b" ] } } } ] } }, "outputs": [ 2, 3 ] } } } } EOF # Test table functions in the select clause (FlatMap). query T multiline EXPLAIN DECORRELATED PLAN WITH(raw) AS JSON FOR SELECT generate_series(a, b) from t ---- { "Let": { "id": 0, "value": { "Constant": { "rows": { "Ok": [ [ { "data": [] }, 1 ] ] }, "typ": { "column_types": [], "keys": [] } } }, "body": { "Project": { "input": { "Let": { "id": 1, "value": { "Join": { "inputs": [ { "Get": { "id": { "Local": 0 }, "typ": { "column_types": [], "keys": [ [] ] }, "access_strategy": "UnknownOrLocal" } }, { "Get": { "id": { "Global": { "User": 1 } }, "typ": { "column_types": [ { "scalar_type": "Int32", "nullable": true }, { "scalar_type": "Int32", "nullable": true } ], "keys": [] }, "access_strategy": "UnknownOrLocal" } } ], "equivalences": [], "implementation": "Unimplemented" } }, "body": { "FlatMap": { "input": { "Get": { "id": { "Local": 1 }, "typ": { "column_types": [ { "scalar_type": "Int32", "nullable": true }, { "scalar_type": "Int32", "nullable": true } ], "keys": [] }, "access_strategy": "UnknownOrLocal" } }, "func": "GenerateSeriesInt32", "exprs": [ { "Column": [ 0, "a" ] }, { "Column": [ 1, "b" ] }, { "Literal": [ { "Ok": { "data": [ 45, 1 ] } }, { "scalar_type": "Int32", "nullable": false } ] } ] } } } }, "outputs": [ 2 ] } } } } EOF # Test Threshold, Union, Distinct, Negate. query T multiline EXPLAIN DECORRELATED PLAN WITH(raw) AS JSON FOR SELECT a FROM t EXCEPT SELECT b FROM mv ---- { "Let": { "id": 0, "value": { "Constant": { "rows": { "Ok": [ [ { "data": [] }, 1 ] ] }, "typ": { "column_types": [], "keys": [] } } }, "body": { "Threshold": { "input": { "Union": { "base": { "Reduce": { "input": { "Project": { "input": { "Join": { "inputs": [ { "Get": { "id": { "Local": 0 }, "typ": { "column_types": [], "keys": [ [] ] }, "access_strategy": "UnknownOrLocal" } }, { "Get": { "id": { "Global": { "User": 1 } }, "typ": { "column_types": [ { "scalar_type": "Int32", "nullable": true }, { "scalar_type": "Int32", "nullable": true } ], "keys": [] }, "access_strategy": "UnknownOrLocal" } } ], "equivalences": [], "implementation": "Unimplemented" } }, "outputs": [ 0 ] } }, "group_key": [ { "Column": [ 0, null ] } ], "aggregates": [], "monotonic": false, "expected_group_size": null } }, "inputs": [ { "Negate": { "input": { "Reduce": { "input": { "Project": { "input": { "Join": { "inputs": [ { "Get": { "id": { "Local": 0 }, "typ": { "column_types": [], "keys": [ [] ] }, "access_strategy": "UnknownOrLocal" } }, { "Get": { "id": { "Global": { "User": 5 } }, "typ": { "column_types": [ { "scalar_type": "Int32", "nullable": false }, { "scalar_type": "Int32", "nullable": true } ], "keys": [] }, "access_strategy": "UnknownOrLocal" } } ], "equivalences": [], "implementation": "Unimplemented" } }, "outputs": [ 1 ] } }, "group_key": [ { "Column": [ 0, null ] } ], "aggregates": [], "monotonic": false, "expected_group_size": null } } } } ] } } } } } } EOF # Test Threshold, Union, Distinct, Negate. query T multiline EXPLAIN DECORRELATED PLAN WITH(raw) AS JSON FOR SELECT a FROM t EXCEPT ALL SELECT b FROM mv ---- { "Let": { "id": 0, "value": { "Constant": { "rows": { "Ok": [ [ { "data": [] }, 1 ] ] }, "typ": { "column_types": [], "keys": [] } } }, "body": { "Threshold": { "input": { "Union": { "base": { "Project": { "input": { "Join": { "inputs": [ { "Get": { "id": { "Local": 0 }, "typ": { "column_types": [], "keys": [ [] ] }, "access_strategy": "UnknownOrLocal" } }, { "Get": { "id": { "Global": { "User": 1 } }, "typ": { "column_types": [ { "scalar_type": "Int32", "nullable": true }, { "scalar_type": "Int32", "nullable": true } ], "keys": [] }, "access_strategy": "UnknownOrLocal" } } ], "equivalences": [], "implementation": "Unimplemented" } }, "outputs": [ 0 ] } }, "inputs": [ { "Negate": { "input": { "Project": { "input": { "Join": { "inputs": [ { "Get": { "id": { "Local": 0 }, "typ": { "column_types": [], "keys": [ [] ] }, "access_strategy": "UnknownOrLocal" } }, { "Get": { "id": { "Global": { "User": 5 } }, "typ": { "column_types": [ { "scalar_type": "Int32", "nullable": false }, { "scalar_type": "Int32", "nullable": true } ], "keys": [] }, "access_strategy": "UnknownOrLocal" } } ], "equivalences": [], "implementation": "Unimplemented" } }, "outputs": [ 1 ] } } } } ] } } } } } } EOF # Test Finish. query T multiline EXPLAIN DECORRELATED PLAN WITH(raw) AS JSON FOR SELECT * FROM t ORDER BY b asc, a desc LIMIT 5 ---- { "Let": { "id": 0, "value": { "Constant": { "rows": { "Ok": [ [ { "data": [] }, 1 ] ] }, "typ": { "column_types": [], "keys": [] } } }, "body": { "Join": { "inputs": [ { "Get": { "id": { "Local": 0 }, "typ": { "column_types": [], "keys": [ [] ] }, "access_strategy": "UnknownOrLocal" } }, { "Get": { "id": { "Global": { "User": 1 } }, "typ": { "column_types": [ { "scalar_type": "Int32", "nullable": true }, { "scalar_type": "Int32", "nullable": true } ], "keys": [] }, "access_strategy": "UnknownOrLocal" } } ], "equivalences": [], "implementation": "Unimplemented" } } } } EOF # Test Reduce (global). query T multiline EXPLAIN DECORRELATED PLAN WITH(raw) AS JSON FOR SELECT abs(min(a) - max(a)) FROM t ---- { "Let": { "id": 0, "value": { "Constant": { "rows": { "Ok": [ [ { "data": [] }, 1 ] ] }, "typ": { "column_types": [], "keys": [] } } }, "body": { "Project": { "input": { "Map": { "input": { "Let": { "id": 2, "value": { "Let": { "id": 1, "value": { "Reduce": { "input": { "Join": { "inputs": [ { "Get": { "id": { "Local": 0 }, "typ": { "column_types": [], "keys": [ [] ] }, "access_strategy": "UnknownOrLocal" } }, { "Get": { "id": { "Global": { "User": 1 } }, "typ": { "column_types": [ { "scalar_type": "Int32", "nullable": true }, { "scalar_type": "Int32", "nullable": true } ], "keys": [] }, "access_strategy": "UnknownOrLocal" } } ], "equivalences": [], "implementation": "Unimplemented" } }, "group_key": [], "aggregates": [ { "func": "MinInt32", "expr": { "Column": [ 0, "a" ] }, "distinct": false }, { "func": "MaxInt32", "expr": { "Column": [ 0, "a" ] }, "distinct": false } ], "monotonic": false, "expected_group_size": null } }, "body": { "Union": { "base": { "Get": { "id": { "Local": 1 }, "typ": { "column_types": [ { "scalar_type": "Int32", "nullable": true }, { "scalar_type": "Int32", "nullable": true } ], "keys": [ [] ] }, "access_strategy": "UnknownOrLocal" } }, "inputs": [ { "Join": { "inputs": [ { "Project": { "input": { "Join": { "inputs": [ { "Union": { "base": { "Negate": { "input": { "Reduce": { "input": { "Get": { "id": { "Local": 1 }, "typ": { "column_types": [ { "scalar_type": "Int32", "nullable": true }, { "scalar_type": "Int32", "nullable": true } ], "keys": [ [] ] }, "access_strategy": "UnknownOrLocal" } }, "group_key": [], "aggregates": [], "monotonic": false, "expected_group_size": null } } } }, "inputs": [ { "Reduce": { "input": { "Get": { "id": { "Local": 0 }, "typ": { "column_types": [], "keys": [ [] ] }, "access_strategy": "UnknownOrLocal" } }, "group_key": [], "aggregates": [], "monotonic": false, "expected_group_size": null } } ] } }, { "Get": { "id": { "Local": 0 }, "typ": { "column_types": [], "keys": [ [] ] }, "access_strategy": "UnknownOrLocal" } } ], "equivalences": [], "implementation": "Unimplemented" } }, "outputs": [] } }, { "Constant": { "rows": { "Ok": [ [ { "data": [ 0, 0 ] }, 1 ] ] }, "typ": { "column_types": [ { "scalar_type": "Int32", "nullable": true }, { "scalar_type": "Int32", "nullable": true } ], "keys": [] } } } ], "equivalences": [], "implementation": "Unimplemented" } } ] } } } }, "body": { "Get": { "id": { "Local": 2 }, "typ": { "column_types": [ { "scalar_type": "Int32", "nullable": true }, { "scalar_type": "Int32", "nullable": true } ], "keys": [] }, "access_strategy": "UnknownOrLocal" } } } }, "scalars": [ { "CallUnary": { "func": { "AbsInt32": null }, "expr": { "CallBinary": { "func": "SubInt32", "expr1": { "Column": [ 0, "?column?" ] }, "expr2": { "Column": [ 1, "?column?" ] } } } } } ] } }, "outputs": [ 2 ] } } } } EOF # Test Reduce (local). query T multiline EXPLAIN DECORRELATED PLAN WITH(raw) AS JSON FOR SELECT abs(min(a) - max(a)) FROM t GROUP BY b ---- { "Let": { "id": 0, "value": { "Constant": { "rows": { "Ok": [ [ { "data": [] }, 1 ] ] }, "typ": { "column_types": [], "keys": [] } } }, "body": { "Project": { "input": { "Map": { "input": { "Let": { "id": 2, "value": { "Reduce": { "input": { "Project": { "input": { "Map": { "input": { "Let": { "id": 1, "value": { "Join": { "inputs": [ { "Get": { "id": { "Local": 0 }, "typ": { "column_types": [], "keys": [ [] ] }, "access_strategy": "UnknownOrLocal" } }, { "Get": { "id": { "Global": { "User": 1 } }, "typ": { "column_types": [ { "scalar_type": "Int32", "nullable": true }, { "scalar_type": "Int32", "nullable": true } ], "keys": [] }, "access_strategy": "UnknownOrLocal" } } ], "equivalences": [], "implementation": "Unimplemented" } }, "body": { "Get": { "id": { "Local": 1 }, "typ": { "column_types": [ { "scalar_type": "Int32", "nullable": true }, { "scalar_type": "Int32", "nullable": true } ], "keys": [] }, "access_strategy": "UnknownOrLocal" } } } }, "scalars": [ { "Column": [ 1, "b" ] } ] } }, "outputs": [ 0, 1, 2 ] } }, "group_key": [ { "Column": [ 2, null ] } ], "aggregates": [ { "func": "MinInt32", "expr": { "Column": [ 0, "a" ] }, "distinct": false }, { "func": "MaxInt32", "expr": { "Column": [ 0, "a" ] }, "distinct": false } ], "monotonic": false, "expected_group_size": null } }, "body": { "Get": { "id": { "Local": 2 }, "typ": { "column_types": [ { "scalar_type": "Int32", "nullable": true }, { "scalar_type": "Int32", "nullable": true }, { "scalar_type": "Int32", "nullable": true } ], "keys": [ [ 0 ] ] }, "access_strategy": "UnknownOrLocal" } } } }, "scalars": [ { "CallUnary": { "func": { "AbsInt32": null }, "expr": { "CallBinary": { "func": "SubInt32", "expr1": { "Column": [ 1, "?column?" ] }, "expr2": { "Column": [ 2, "?column?" ] } } } } } ] } }, "outputs": [ 3 ] } } } } EOF # Test EXISTS subqueries. query T multiline EXPLAIN DECORRELATED PLAN WITH(raw) AS JSON FOR SELECT * FROM t WHERE EXISTS(SELECT * FROM mv WHERE t.a < mv.a) AND EXISTS(SELECT * FROM mv WHERE t.b > mv.b) ---- { "Let": { "id": 0, "value": { "Constant": { "rows": { "Ok": [ [ { "data": [] }, 1 ] ] }, "typ": { "column_types": [], "keys": [] } } }, "body": { "Project": { "input": { "Filter": { "input": { "Let": { "id": 4, "value": { "Project": { "input": { "Filter": { "input": { "Let": { "id": 1, "value": { "Filter": { "input": { "Join": { "inputs": [ { "Get": { "id": { "Local": 0 }, "typ": { "column_types": [], "keys": [ [] ] }, "access_strategy": "UnknownOrLocal" } }, { "Get": { "id": { "Global": { "User": 1 } }, "typ": { "column_types": [ { "scalar_type": "Int32", "nullable": true }, { "scalar_type": "Int32", "nullable": true } ], "keys": [] }, "access_strategy": "UnknownOrLocal" } } ], "equivalences": [], "implementation": "Unimplemented" } }, "predicates": [ { "CallVariadic": { "func": "And", "exprs": [ { "Literal": [ { "Ok": { "data": [ 2 ] } }, { "scalar_type": "Bool", "nullable": false } ] }, { "Literal": [ { "Ok": { "data": [ 2 ] } }, { "scalar_type": "Bool", "nullable": false } ] } ] } } ] } }, "body": { "Let": { "id": 2, "value": { "Reduce": { "input": { "Get": { "id": { "Local": 1 }, "typ": { "column_types": [ { "scalar_type": "Int32", "nullable": true }, { "scalar_type": "Int32", "nullable": true } ], "keys": [] }, "access_strategy": "UnknownOrLocal" } }, "group_key": [ { "Column": [ 0, null ] } ], "aggregates": [], "monotonic": false, "expected_group_size": null } }, "body": { "Project": { "input": { "Join": { "inputs": [ { "Get": { "id": { "Local": 1 }, "typ": { "column_types": [ { "scalar_type": "Int32", "nullable": true }, { "scalar_type": "Int32", "nullable": true } ], "keys": [] }, "access_strategy": "UnknownOrLocal" } }, { "Let": { "id": 3, "value": { "Map": { "input": { "Reduce": { "input": { "Filter": { "input": { "Join": { "inputs": [ { "Get": { "id": { "Local": 2 }, "typ": { "column_types": [ { "scalar_type": "Int32", "nullable": true } ], "keys": [ [ 0 ] ] }, "access_strategy": "UnknownOrLocal" } }, { "Get": { "id": { "Global": { "User": 5 } }, "typ": { "column_types": [ { "scalar_type": "Int32", "nullable": false }, { "scalar_type": "Int32", "nullable": true } ], "keys": [] }, "access_strategy": "UnknownOrLocal" } } ], "equivalences": [], "implementation": "Unimplemented" } }, "predicates": [ { "CallBinary": { "func": "Lt", "expr1": { "Column": [ 0, "a" ] }, "expr2": { "Column": [ 1, "a" ] } } } ] } }, "group_key": [ { "Column": [ 0, null ] } ], "aggregates": [], "monotonic": false, "expected_group_size": null } }, "scalars": [ { "Literal": [ { "Ok": { "data": [ 2 ] } }, { "scalar_type": "Bool", "nullable": false } ] } ] } }, "body": { "Union": { "base": { "Get": { "id": { "Local": 3 }, "typ": { "column_types": [ { "scalar_type": "Int32", "nullable": false }, { "scalar_type": "Bool", "nullable": false } ], "keys": [ [ 0 ] ] }, "access_strategy": "UnknownOrLocal" } }, "inputs": [ { "Join": { "inputs": [ { "Project": { "input": { "Join": { "inputs": [ { "Union": { "base": { "Negate": { "input": { "Reduce": { "input": { "Get": { "id": { "Local": 3 }, "typ": { "column_types": [ { "scalar_type": "Int32", "nullable": false }, { "scalar_type": "Bool", "nullable": false } ], "keys": [ [ 0 ] ] }, "access_strategy": "UnknownOrLocal" } }, "group_key": [ { "Column": [ 0, null ] } ], "aggregates": [], "monotonic": false, "expected_group_size": null } } } }, "inputs": [ { "Reduce": { "input": { "Get": { "id": { "Local": 2 }, "typ": { "column_types": [ { "scalar_type": "Int32", "nullable": true } ], "keys": [ [ 0 ] ] }, "access_strategy": "UnknownOrLocal" } }, "group_key": [ { "Column": [ 0, null ] } ], "aggregates": [], "monotonic": false, "expected_group_size": null } } ] } }, { "Get": { "id": { "Local": 2 }, "typ": { "column_types": [ { "scalar_type": "Int32", "nullable": true } ], "keys": [ [ 0 ] ] }, "access_strategy": "UnknownOrLocal" } } ], "equivalences": [ [ { "Column": [ 0, null ] }, { "Column": [ 1, null ] } ] ], "implementation": "Unimplemented" } }, "outputs": [ 0 ] } }, { "Constant": { "rows": { "Ok": [ [ { "data": [ 1 ] }, 1 ] ] }, "typ": { "column_types": [ { "scalar_type": "Bool", "nullable": false } ], "keys": [] } } } ], "equivalences": [], "implementation": "Unimplemented" } } ] } } } } ], "equivalences": [ [ { "Column": [ 0, null ] }, { "Column": [ 2, null ] } ] ], "implementation": "Unimplemented" } }, "outputs": [ 0, 1, 3 ] } } } } } }, "predicates": [ { "Column": [ 2, null ] } ] } }, "outputs": [ 0, 1 ] } }, "body": { "Let": { "id": 5, "value": { "Reduce": { "input": { "Get": { "id": { "Local": 4 }, "typ": { "column_types": [ { "scalar_type": "Int32", "nullable": true }, { "scalar_type": "Int32", "nullable": true } ], "keys": [] }, "access_strategy": "UnknownOrLocal" } }, "group_key": [ { "Column": [ 1, null ] } ], "aggregates": [], "monotonic": false, "expected_group_size": null } }, "body": { "Project": { "input": { "Join": { "inputs": [ { "Get": { "id": { "Local": 4 }, "typ": { "column_types": [ { "scalar_type": "Int32", "nullable": true }, { "scalar_type": "Int32", "nullable": true } ], "keys": [] }, "access_strategy": "UnknownOrLocal" } }, { "Let": { "id": 6, "value": { "Map": { "input": { "Reduce": { "input": { "Filter": { "input": { "Join": { "inputs": [ { "Get": { "id": { "Local": 5 }, "typ": { "column_types": [ { "scalar_type": "Int32", "nullable": true } ], "keys": [ [ 0 ] ] }, "access_strategy": "UnknownOrLocal" } }, { "Get": { "id": { "Global": { "User": 5 } }, "typ": { "column_types": [ { "scalar_type": "Int32", "nullable": false }, { "scalar_type": "Int32", "nullable": true } ], "keys": [] }, "access_strategy": "UnknownOrLocal" } } ], "equivalences": [], "implementation": "Unimplemented" } }, "predicates": [ { "CallBinary": { "func": "Gt", "expr1": { "Column": [ 0, "b" ] }, "expr2": { "Column": [ 2, "b" ] } } } ] } }, "group_key": [ { "Column": [ 0, null ] } ], "aggregates": [], "monotonic": false, "expected_group_size": null } }, "scalars": [ { "Literal": [ { "Ok": { "data": [ 2 ] } }, { "scalar_type": "Bool", "nullable": false } ] } ] } }, "body": { "Union": { "base": { "Get": { "id": { "Local": 6 }, "typ": { "column_types": [ { "scalar_type": "Int32", "nullable": false }, { "scalar_type": "Bool", "nullable": false } ], "keys": [ [ 0 ] ] }, "access_strategy": "UnknownOrLocal" } }, "inputs": [ { "Join": { "inputs": [ { "Project": { "input": { "Join": { "inputs": [ { "Union": { "base": { "Negate": { "input": { "Reduce": { "input": { "Get": { "id": { "Local": 6 }, "typ": { "column_types": [ { "scalar_type": "Int32", "nullable": false }, { "scalar_type": "Bool", "nullable": false } ], "keys": [ [ 0 ] ] }, "access_strategy": "UnknownOrLocal" } }, "group_key": [ { "Column": [ 0, null ] } ], "aggregates": [], "monotonic": false, "expected_group_size": null } } } }, "inputs": [ { "Reduce": { "input": { "Get": { "id": { "Local": 5 }, "typ": { "column_types": [ { "scalar_type": "Int32", "nullable": true } ], "keys": [ [ 0 ] ] }, "access_strategy": "UnknownOrLocal" } }, "group_key": [ { "Column": [ 0, null ] } ], "aggregates": [], "monotonic": false, "expected_group_size": null } } ] } }, { "Get": { "id": { "Local": 5 }, "typ": { "column_types": [ { "scalar_type": "Int32", "nullable": true } ], "keys": [ [ 0 ] ] }, "access_strategy": "UnknownOrLocal" } } ], "equivalences": [ [ { "Column": [ 0, null ] }, { "Column": [ 1, null ] } ] ], "implementation": "Unimplemented" } }, "outputs": [ 0 ] } }, { "Constant": { "rows": { "Ok": [ [ { "data": [ 1 ] }, 1 ] ] }, "typ": { "column_types": [ { "scalar_type": "Bool", "nullable": false } ], "keys": [] } } } ], "equivalences": [], "implementation": "Unimplemented" } } ] } } } } ], "equivalences": [ [ { "Column": [ 1, null ] }, { "Column": [ 2, null ] } ] ], "implementation": "Unimplemented" } }, "outputs": [ 0, 1, 3 ] } } } } } }, "predicates": [ { "Column": [ 2, null ] } ] } }, "outputs": [ 0, 1 ] } } } } EOF # Test SELECT subqueries. query T multiline EXPLAIN DECORRELATED PLAN WITH(raw) AS JSON FOR SELECT (SELECT v.a FROM v WHERE v.b = t.b LIMIT 1), (SELECT mv.a FROM mv WHERE mv.b = t.b LIMIT 1) FROM t ---- { "Let": { "id": 0, "value": { "Constant": { "rows": { "Ok": [ [ { "data": [] }, 1 ] ] }, "typ": { "column_types": [], "keys": [] } } }, "body": { "Project": { "input": { "Map": { "input": { "Let": { "id": 1, "value": { "Join": { "inputs": [ { "Get": { "id": { "Local": 0 }, "typ": { "column_types": [], "keys": [ [] ] }, "access_strategy": "UnknownOrLocal" } }, { "Get": { "id": { "Global": { "User": 1 } }, "typ": { "column_types": [ { "scalar_type": "Int32", "nullable": true }, { "scalar_type": "Int32", "nullable": true } ], "keys": [] }, "access_strategy": "UnknownOrLocal" } } ], "equivalences": [], "implementation": "Unimplemented" } }, "body": { "Join": { "inputs": [ { "Get": { "id": { "Local": 1 }, "typ": { "column_types": [ { "scalar_type": "Int32", "nullable": true }, { "scalar_type": "Int32", "nullable": true } ], "keys": [] }, "access_strategy": "UnknownOrLocal" } }, { "Let": { "id": 2, "value": { "Reduce": { "input": { "Get": { "id": { "Local": 1 }, "typ": { "column_types": [ { "scalar_type": "Int32", "nullable": true }, { "scalar_type": "Int32", "nullable": true } ], "keys": [] }, "access_strategy": "UnknownOrLocal" } }, "group_key": [ { "Column": [ 0, null ] }, { "Column": [ 1, null ] } ], "aggregates": [], "monotonic": false, "expected_group_size": null } }, "body": { "Let": { "id": 3, "value": { "Reduce": { "input": { "Get": { "id": { "Local": 2 }, "typ": { "column_types": [ { "scalar_type": "Int32", "nullable": true }, { "scalar_type": "Int32", "nullable": true } ], "keys": [ [ 0, 1 ] ] }, "access_strategy": "UnknownOrLocal" } }, "group_key": [ { "Column": [ 1, null ] } ], "aggregates": [], "monotonic": false, "expected_group_size": null } }, "body": { "Project": { "input": { "Join": { "inputs": [ { "Get": { "id": { "Local": 2 }, "typ": { "column_types": [ { "scalar_type": "Int32", "nullable": true }, { "scalar_type": "Int32", "nullable": true } ], "keys": [ [ 0, 1 ] ] }, "access_strategy": "UnknownOrLocal" } }, { "Let": { "id": 5, "value": { "Let": { "id": 4, "value": { "Project": { "input": { "TopK": { "input": { "Filter": { "input": { "Join": { "inputs": [ { "Get": { "id": { "Local": 3 }, "typ": { "column_types": [ { "scalar_type": "Int32", "nullable": true } ], "keys": [ [ 0 ] ] }, "access_strategy": "UnknownOrLocal" } }, { "Get": { "id": { "Global": { "User": 3 } }, "typ": { "column_types": [ { "scalar_type": "Int32", "nullable": false }, { "scalar_type": "Int32", "nullable": true } ], "keys": [] }, "access_strategy": "UnknownOrLocal" } } ], "equivalences": [], "implementation": "Unimplemented" } }, "predicates": [ { "CallBinary": { "func": "Eq", "expr1": { "Column": [ 2, "b" ] }, "expr2": { "Column": [ 0, "b" ] } } } ] } }, "group_key": [ 0 ], "order_key": [], "limit": { "Literal": [ { "Ok": { "data": [ 50, 1 ] } }, { "scalar_type": "Int64", "nullable": false } ] }, "offset": 0, "monotonic": false, "expected_group_size": null } }, "outputs": [ 0, 1 ] } }, "body": { "Union": { "base": { "Get": { "id": { "Local": 4 }, "typ": { "column_types": [ { "scalar_type": "Int32", "nullable": false }, { "scalar_type": "Int32", "nullable": false } ], "keys": [ [ 0 ] ] }, "access_strategy": "UnknownOrLocal" } }, "inputs": [ { "Project": { "input": { "FlatMap": { "input": { "Reduce": { "input": { "Get": { "id": { "Local": 4 }, "typ": { "column_types": [ { "scalar_type": "Int32", "nullable": false }, { "scalar_type": "Int32", "nullable": false } ], "keys": [ [ 0 ] ] }, "access_strategy": "UnknownOrLocal" } }, "group_key": [ { "Column": [ 0, null ] } ], "aggregates": [ { "func": "Count", "expr": { "Literal": [ { "Ok": { "data": [ 2 ] } }, { "scalar_type": "Bool", "nullable": false } ] }, "distinct": false } ], "monotonic": false, "expected_group_size": null } }, "func": { "GuardSubquerySize": { "column_type": "Int32" } }, "exprs": [ { "Column": [ 1, null ] } ] } }, "outputs": [ 0, 2 ] } } ] } } } }, "body": { "Union": { "base": { "Get": { "id": { "Local": 5 }, "typ": { "column_types": [ { "scalar_type": "Int32", "nullable": false }, { "scalar_type": "Int32", "nullable": false } ], "keys": [] }, "access_strategy": "UnknownOrLocal" } }, "inputs": [ { "Join": { "inputs": [ { "Project": { "input": { "Join": { "inputs": [ { "Union": { "base": { "Negate": { "input": { "Reduce": { "input": { "Get": { "id": { "Local": 5 }, "typ": { "column_types": [ { "scalar_type": "Int32", "nullable": false }, { "scalar_type": "Int32", "nullable": false } ], "keys": [] }, "access_strategy": "UnknownOrLocal" } }, "group_key": [ { "Column": [ 0, null ] } ], "aggregates": [], "monotonic": false, "expected_group_size": null } } } }, "inputs": [ { "Reduce": { "input": { "Get": { "id": { "Local": 3 }, "typ": { "column_types": [ { "scalar_type": "Int32", "nullable": true } ], "keys": [ [ 0 ] ] }, "access_strategy": "UnknownOrLocal" } }, "group_key": [ { "Column": [ 0, null ] } ], "aggregates": [], "monotonic": false, "expected_group_size": null } } ] } }, { "Get": { "id": { "Local": 3 }, "typ": { "column_types": [ { "scalar_type": "Int32", "nullable": true } ], "keys": [ [ 0 ] ] }, "access_strategy": "UnknownOrLocal" } } ], "equivalences": [ [ { "Column": [ 0, null ] }, { "Column": [ 1, null ] } ] ], "implementation": "Unimplemented" } }, "outputs": [ 0 ] } }, { "Constant": { "rows": { "Ok": [ [ { "data": [ 0 ] }, 1 ] ] }, "typ": { "column_types": [ { "scalar_type": "Int32", "nullable": true } ], "keys": [] } } } ], "equivalences": [], "implementation": "Unimplemented" } } ] } } } } ], "equivalences": [ [ { "Column": [ 1, null ] }, { "Column": [ 2, null ] } ] ], "implementation": "Unimplemented" } }, "outputs": [ 0, 1, 3 ] } } } } } }, { "Let": { "id": 6, "value": { "Reduce": { "input": { "Get": { "id": { "Local": 1 }, "typ": { "column_types": [ { "scalar_type": "Int32", "nullable": true }, { "scalar_type": "Int32", "nullable": true } ], "keys": [] }, "access_strategy": "UnknownOrLocal" } }, "group_key": [ { "Column": [ 0, null ] }, { "Column": [ 1, null ] } ], "aggregates": [], "monotonic": false, "expected_group_size": null } }, "body": { "Let": { "id": 7, "value": { "Reduce": { "input": { "Get": { "id": { "Local": 6 }, "typ": { "column_types": [ { "scalar_type": "Int32", "nullable": true }, { "scalar_type": "Int32", "nullable": true } ], "keys": [ [ 0, 1 ] ] }, "access_strategy": "UnknownOrLocal" } }, "group_key": [ { "Column": [ 1, null ] } ], "aggregates": [], "monotonic": false, "expected_group_size": null } }, "body": { "Project": { "input": { "Join": { "inputs": [ { "Get": { "id": { "Local": 6 }, "typ": { "column_types": [ { "scalar_type": "Int32", "nullable": true }, { "scalar_type": "Int32", "nullable": true } ], "keys": [ [ 0, 1 ] ] }, "access_strategy": "UnknownOrLocal" } }, { "Let": { "id": 9, "value": { "Let": { "id": 8, "value": { "Project": { "input": { "TopK": { "input": { "Filter": { "input": { "Join": { "inputs": [ { "Get": { "id": { "Local": 7 }, "typ": { "column_types": [ { "scalar_type": "Int32", "nullable": true } ], "keys": [ [ 0 ] ] }, "access_strategy": "UnknownOrLocal" } }, { "Get": { "id": { "Global": { "User": 5 } }, "typ": { "column_types": [ { "scalar_type": "Int32", "nullable": false }, { "scalar_type": "Int32", "nullable": true } ], "keys": [] }, "access_strategy": "UnknownOrLocal" } } ], "equivalences": [], "implementation": "Unimplemented" } }, "predicates": [ { "CallBinary": { "func": "Eq", "expr1": { "Column": [ 2, "b" ] }, "expr2": { "Column": [ 0, "b" ] } } } ] } }, "group_key": [ 0 ], "order_key": [], "limit": { "Literal": [ { "Ok": { "data": [ 50, 1 ] } }, { "scalar_type": "Int64", "nullable": false } ] }, "offset": 0, "monotonic": false, "expected_group_size": null } }, "outputs": [ 0, 1 ] } }, "body": { "Union": { "base": { "Get": { "id": { "Local": 8 }, "typ": { "column_types": [ { "scalar_type": "Int32", "nullable": false }, { "scalar_type": "Int32", "nullable": false } ], "keys": [ [ 0 ] ] }, "access_strategy": "UnknownOrLocal" } }, "inputs": [ { "Project": { "input": { "FlatMap": { "input": { "Reduce": { "input": { "Get": { "id": { "Local": 8 }, "typ": { "column_types": [ { "scalar_type": "Int32", "nullable": false }, { "scalar_type": "Int32", "nullable": false } ], "keys": [ [ 0 ] ] }, "access_strategy": "UnknownOrLocal" } }, "group_key": [ { "Column": [ 0, null ] } ], "aggregates": [ { "func": "Count", "expr": { "Literal": [ { "Ok": { "data": [ 2 ] } }, { "scalar_type": "Bool", "nullable": false } ] }, "distinct": false } ], "monotonic": false, "expected_group_size": null } }, "func": { "GuardSubquerySize": { "column_type": "Int32" } }, "exprs": [ { "Column": [ 1, null ] } ] } }, "outputs": [ 0, 2 ] } } ] } } } }, "body": { "Union": { "base": { "Get": { "id": { "Local": 9 }, "typ": { "column_types": [ { "scalar_type": "Int32", "nullable": false }, { "scalar_type": "Int32", "nullable": false } ], "keys": [] }, "access_strategy": "UnknownOrLocal" } }, "inputs": [ { "Join": { "inputs": [ { "Project": { "input": { "Join": { "inputs": [ { "Union": { "base": { "Negate": { "input": { "Reduce": { "input": { "Get": { "id": { "Local": 9 }, "typ": { "column_types": [ { "scalar_type": "Int32", "nullable": false }, { "scalar_type": "Int32", "nullable": false } ], "keys": [] }, "access_strategy": "UnknownOrLocal" } }, "group_key": [ { "Column": [ 0, null ] } ], "aggregates": [], "monotonic": false, "expected_group_size": null } } } }, "inputs": [ { "Reduce": { "input": { "Get": { "id": { "Local": 7 }, "typ": { "column_types": [ { "scalar_type": "Int32", "nullable": true } ], "keys": [ [ 0 ] ] }, "access_strategy": "UnknownOrLocal" } }, "group_key": [ { "Column": [ 0, null ] } ], "aggregates": [], "monotonic": false, "expected_group_size": null } } ] } }, { "Get": { "id": { "Local": 7 }, "typ": { "column_types": [ { "scalar_type": "Int32", "nullable": true } ], "keys": [ [ 0 ] ] }, "access_strategy": "UnknownOrLocal" } } ], "equivalences": [ [ { "Column": [ 0, null ] }, { "Column": [ 1, null ] } ] ], "implementation": "Unimplemented" } }, "outputs": [ 0 ] } }, { "Constant": { "rows": { "Ok": [ [ { "data": [ 0 ] }, 1 ] ] }, "typ": { "column_types": [ { "scalar_type": "Int32", "nullable": true } ], "keys": [] } } } ], "equivalences": [], "implementation": "Unimplemented" } } ] } } } } ], "equivalences": [ [ { "Column": [ 1, null ] }, { "Column": [ 2, null ] } ] ], "implementation": "Unimplemented" } }, "outputs": [ 0, 1, 3 ] } } } } } } ], "equivalences": [ [ { "Column": [ 0, null ] }, { "Column": [ 2, null ] }, { "Column": [ 5, null ] } ], [ { "Column": [ 1, null ] }, { "Column": [ 3, null ] }, { "Column": [ 6, null ] } ] ], "implementation": "Unimplemented" } } } }, "scalars": [ { "Column": [ 4, null ] }, { "Column": [ 7, null ] } ] } }, "outputs": [ 8, 9 ] } } } } EOF # Test CrossJoin derived from a comma join without a predicate. query T multiline EXPLAIN DECORRELATED PLAN WITH(raw) AS JSON FOR SELECT t1.a, t2.a FROM t as t1, t as t2 ---- { "Let": { "id": 0, "value": { "Constant": { "rows": { "Ok": [ [ { "data": [] }, 1 ] ] }, "typ": { "column_types": [], "keys": [] } } }, "body": { "Project": { "input": { "Let": { "id": 1, "value": { "Join": { "inputs": [ { "Get": { "id": { "Local": 0 }, "typ": { "column_types": [], "keys": [ [] ] }, "access_strategy": "UnknownOrLocal" } }, { "Get": { "id": { "Global": { "User": 1 } }, "typ": { "column_types": [ { "scalar_type": "Int32", "nullable": true }, { "scalar_type": "Int32", "nullable": true } ], "keys": [] }, "access_strategy": "UnknownOrLocal" } } ], "equivalences": [], "implementation": "Unimplemented" } }, "body": { "Let": { "id": 2, "value": { "Join": { "inputs": [ { "Get": { "id": { "Local": 0 }, "typ": { "column_types": [], "keys": [ [] ] }, "access_strategy": "UnknownOrLocal" } }, { "Get": { "id": { "Global": { "User": 1 } }, "typ": { "column_types": [ { "scalar_type": "Int32", "nullable": true }, { "scalar_type": "Int32", "nullable": true } ], "keys": [] }, "access_strategy": "UnknownOrLocal" } } ], "equivalences": [], "implementation": "Unimplemented" } }, "body": { "Let": { "id": 3, "value": { "Project": { "input": { "Join": { "inputs": [ { "Get": { "id": { "Local": 1 }, "typ": { "column_types": [ { "scalar_type": "Int32", "nullable": true }, { "scalar_type": "Int32", "nullable": true } ], "keys": [] }, "access_strategy": "UnknownOrLocal" } }, { "Get": { "id": { "Local": 2 }, "typ": { "column_types": [ { "scalar_type": "Int32", "nullable": true }, { "scalar_type": "Int32", "nullable": true } ], "keys": [] }, "access_strategy": "UnknownOrLocal" } } ], "equivalences": [], "implementation": "Unimplemented" } }, "outputs": [ 0, 1, 2, 3 ] } }, "body": { "Get": { "id": { "Local": 3 }, "typ": { "column_types": [ { "scalar_type": "Int32", "nullable": true }, { "scalar_type": "Int32", "nullable": true }, { "scalar_type": "Int32", "nullable": true }, { "scalar_type": "Int32", "nullable": true } ], "keys": [] }, "access_strategy": "UnknownOrLocal" } } } } } } } }, "outputs": [ 0, 2 ] } } } } EOF # Test CrossJoin derived from an INNER JOIN with a trivial ON clause. query T multiline EXPLAIN DECORRELATED PLAN WITH(raw) AS JSON FOR SELECT t1.a, t2.a FROM t as t1 INNER JOIN t as t2 ON true ---- { "Let": { "id": 0, "value": { "Constant": { "rows": { "Ok": [ [ { "data": [] }, 1 ] ] }, "typ": { "column_types": [], "keys": [] } } }, "body": { "Project": { "input": { "Let": { "id": 1, "value": { "Join": { "inputs": [ { "Get": { "id": { "Local": 0 }, "typ": { "column_types": [], "keys": [ [] ] }, "access_strategy": "UnknownOrLocal" } }, { "Get": { "id": { "Global": { "User": 1 } }, "typ": { "column_types": [ { "scalar_type": "Int32", "nullable": true }, { "scalar_type": "Int32", "nullable": true } ], "keys": [] }, "access_strategy": "UnknownOrLocal" } } ], "equivalences": [], "implementation": "Unimplemented" } }, "body": { "Let": { "id": 2, "value": { "Join": { "inputs": [ { "Get": { "id": { "Local": 0 }, "typ": { "column_types": [], "keys": [ [] ] }, "access_strategy": "UnknownOrLocal" } }, { "Get": { "id": { "Global": { "User": 1 } }, "typ": { "column_types": [ { "scalar_type": "Int32", "nullable": true }, { "scalar_type": "Int32", "nullable": true } ], "keys": [] }, "access_strategy": "UnknownOrLocal" } } ], "equivalences": [], "implementation": "Unimplemented" } }, "body": { "Let": { "id": 3, "value": { "Project": { "input": { "Join": { "inputs": [ { "Get": { "id": { "Local": 1 }, "typ": { "column_types": [ { "scalar_type": "Int32", "nullable": true }, { "scalar_type": "Int32", "nullable": true } ], "keys": [] }, "access_strategy": "UnknownOrLocal" } }, { "Get": { "id": { "Local": 2 }, "typ": { "column_types": [ { "scalar_type": "Int32", "nullable": true }, { "scalar_type": "Int32", "nullable": true } ], "keys": [] }, "access_strategy": "UnknownOrLocal" } } ], "equivalences": [], "implementation": "Unimplemented" } }, "outputs": [ 0, 1, 2, 3 ] } }, "body": { "Get": { "id": { "Local": 3 }, "typ": { "column_types": [ { "scalar_type": "Int32", "nullable": true }, { "scalar_type": "Int32", "nullable": true }, { "scalar_type": "Int32", "nullable": true }, { "scalar_type": "Int32", "nullable": true } ], "keys": [] }, "access_strategy": "UnknownOrLocal" } } } } } } } }, "outputs": [ 0, 2 ] } } } } EOF # Test InnerJoin (comma syntax). query T multiline EXPLAIN DECORRELATED PLAN WITH(raw) AS JSON FOR SELECT t1.a, t2.a FROM t as t1, t as t2, t as t3 WHERE t1.b = t2.b AND t2.b = t3.b ---- { "Let": { "id": 0, "value": { "Constant": { "rows": { "Ok": [ [ { "data": [] }, 1 ] ] }, "typ": { "column_types": [], "keys": [] } } }, "body": { "Project": { "input": { "Filter": { "input": { "Let": { "id": 4, "value": { "Let": { "id": 1, "value": { "Join": { "inputs": [ { "Get": { "id": { "Local": 0 }, "typ": { "column_types": [], "keys": [ [] ] }, "access_strategy": "UnknownOrLocal" } }, { "Get": { "id": { "Global": { "User": 1 } }, "typ": { "column_types": [ { "scalar_type": "Int32", "nullable": true }, { "scalar_type": "Int32", "nullable": true } ], "keys": [] }, "access_strategy": "UnknownOrLocal" } } ], "equivalences": [], "implementation": "Unimplemented" } }, "body": { "Let": { "id": 2, "value": { "Join": { "inputs": [ { "Get": { "id": { "Local": 0 }, "typ": { "column_types": [], "keys": [ [] ] }, "access_strategy": "UnknownOrLocal" } }, { "Get": { "id": { "Global": { "User": 1 } }, "typ": { "column_types": [ { "scalar_type": "Int32", "nullable": true }, { "scalar_type": "Int32", "nullable": true } ], "keys": [] }, "access_strategy": "UnknownOrLocal" } } ], "equivalences": [], "implementation": "Unimplemented" } }, "body": { "Let": { "id": 3, "value": { "Project": { "input": { "Join": { "inputs": [ { "Get": { "id": { "Local": 1 }, "typ": { "column_types": [ { "scalar_type": "Int32", "nullable": true }, { "scalar_type": "Int32", "nullable": true } ], "keys": [] }, "access_strategy": "UnknownOrLocal" } }, { "Get": { "id": { "Local": 2 }, "typ": { "column_types": [ { "scalar_type": "Int32", "nullable": true }, { "scalar_type": "Int32", "nullable": true } ], "keys": [] }, "access_strategy": "UnknownOrLocal" } } ], "equivalences": [], "implementation": "Unimplemented" } }, "outputs": [ 0, 1, 2, 3 ] } }, "body": { "Get": { "id": { "Local": 3 }, "typ": { "column_types": [ { "scalar_type": "Int32", "nullable": true }, { "scalar_type": "Int32", "nullable": true }, { "scalar_type": "Int32", "nullable": true }, { "scalar_type": "Int32", "nullable": true } ], "keys": [] }, "access_strategy": "UnknownOrLocal" } } } } } } } }, "body": { "Let": { "id": 5, "value": { "Join": { "inputs": [ { "Get": { "id": { "Local": 0 }, "typ": { "column_types": [], "keys": [ [] ] }, "access_strategy": "UnknownOrLocal" } }, { "Get": { "id": { "Global": { "User": 1 } }, "typ": { "column_types": [ { "scalar_type": "Int32", "nullable": true }, { "scalar_type": "Int32", "nullable": true } ], "keys": [] }, "access_strategy": "UnknownOrLocal" } } ], "equivalences": [], "implementation": "Unimplemented" } }, "body": { "Let": { "id": 6, "value": { "Project": { "input": { "Join": { "inputs": [ { "Get": { "id": { "Local": 4 }, "typ": { "column_types": [ { "scalar_type": "Int32", "nullable": true }, { "scalar_type": "Int32", "nullable": true }, { "scalar_type": "Int32", "nullable": true }, { "scalar_type": "Int32", "nullable": true } ], "keys": [] }, "access_strategy": "UnknownOrLocal" } }, { "Get": { "id": { "Local": 5 }, "typ": { "column_types": [ { "scalar_type": "Int32", "nullable": true }, { "scalar_type": "Int32", "nullable": true } ], "keys": [] }, "access_strategy": "UnknownOrLocal" } } ], "equivalences": [], "implementation": "Unimplemented" } }, "outputs": [ 0, 1, 2, 3, 4, 5 ] } }, "body": { "Get": { "id": { "Local": 6 }, "typ": { "column_types": [ { "scalar_type": "Int32", "nullable": true }, { "scalar_type": "Int32", "nullable": true }, { "scalar_type": "Int32", "nullable": true }, { "scalar_type": "Int32", "nullable": true }, { "scalar_type": "Int32", "nullable": true }, { "scalar_type": "Int32", "nullable": true } ], "keys": [] }, "access_strategy": "UnknownOrLocal" } } } } } } } }, "predicates": [ { "CallVariadic": { "func": "And", "exprs": [ { "CallBinary": { "func": "Eq", "expr1": { "Column": [ 1, "b" ] }, "expr2": { "Column": [ 3, "b" ] } } }, { "CallBinary": { "func": "Eq", "expr1": { "Column": [ 3, "b" ] }, "expr2": { "Column": [ 5, "b" ] } } } ] } } ] } }, "outputs": [ 0, 2 ] } } } } EOF # Test InnerJoin (ON syntax). query T multiline EXPLAIN DECORRELATED PLAN WITH(raw) AS JSON FOR SELECT t1.a, t2.a FROM t as t1 INNER JOIN t as t2 ON t1.b = t2.b INNER JOIN t as t3 ON t2.b = t3.b ---- { "Let": { "id": 0, "value": { "Constant": { "rows": { "Ok": [ [ { "data": [] }, 1 ] ] }, "typ": { "column_types": [], "keys": [] } } }, "body": { "Project": { "input": { "Let": { "id": 4, "value": { "Let": { "id": 1, "value": { "Join": { "inputs": [ { "Get": { "id": { "Local": 0 }, "typ": { "column_types": [], "keys": [ [] ] }, "access_strategy": "UnknownOrLocal" } }, { "Get": { "id": { "Global": { "User": 1 } }, "typ": { "column_types": [ { "scalar_type": "Int32", "nullable": true }, { "scalar_type": "Int32", "nullable": true } ], "keys": [] }, "access_strategy": "UnknownOrLocal" } } ], "equivalences": [], "implementation": "Unimplemented" } }, "body": { "Let": { "id": 2, "value": { "Join": { "inputs": [ { "Get": { "id": { "Local": 0 }, "typ": { "column_types": [], "keys": [ [] ] }, "access_strategy": "UnknownOrLocal" } }, { "Get": { "id": { "Global": { "User": 1 } }, "typ": { "column_types": [ { "scalar_type": "Int32", "nullable": true }, { "scalar_type": "Int32", "nullable": true } ], "keys": [] }, "access_strategy": "UnknownOrLocal" } } ], "equivalences": [], "implementation": "Unimplemented" } }, "body": { "Let": { "id": 3, "value": { "Filter": { "input": { "Project": { "input": { "Join": { "inputs": [ { "Get": { "id": { "Local": 1 }, "typ": { "column_types": [ { "scalar_type": "Int32", "nullable": true }, { "scalar_type": "Int32", "nullable": true } ], "keys": [] }, "access_strategy": "UnknownOrLocal" } }, { "Get": { "id": { "Local": 2 }, "typ": { "column_types": [ { "scalar_type": "Int32", "nullable": true }, { "scalar_type": "Int32", "nullable": true } ], "keys": [] }, "access_strategy": "UnknownOrLocal" } } ], "equivalences": [], "implementation": "Unimplemented" } }, "outputs": [ 0, 1, 2, 3 ] } }, "predicates": [ { "CallBinary": { "func": "Eq", "expr1": { "Column": [ 1, "b" ] }, "expr2": { "Column": [ 3, "b" ] } } } ] } }, "body": { "Get": { "id": { "Local": 3 }, "typ": { "column_types": [ { "scalar_type": "Int32", "nullable": true }, { "scalar_type": "Int32", "nullable": false }, { "scalar_type": "Int32", "nullable": true }, { "scalar_type": "Int32", "nullable": false } ], "keys": [] }, "access_strategy": "UnknownOrLocal" } } } } } } } }, "body": { "Let": { "id": 5, "value": { "Join": { "inputs": [ { "Get": { "id": { "Local": 0 }, "typ": { "column_types": [], "keys": [ [] ] }, "access_strategy": "UnknownOrLocal" } }, { "Get": { "id": { "Global": { "User": 1 } }, "typ": { "column_types": [ { "scalar_type": "Int32", "nullable": true }, { "scalar_type": "Int32", "nullable": true } ], "keys": [] }, "access_strategy": "UnknownOrLocal" } } ], "equivalences": [], "implementation": "Unimplemented" } }, "body": { "Let": { "id": 6, "value": { "Filter": { "input": { "Project": { "input": { "Join": { "inputs": [ { "Get": { "id": { "Local": 4 }, "typ": { "column_types": [ { "scalar_type": "Int32", "nullable": true }, { "scalar_type": "Int32", "nullable": false }, { "scalar_type": "Int32", "nullable": true }, { "scalar_type": "Int32", "nullable": false } ], "keys": [] }, "access_strategy": "UnknownOrLocal" } }, { "Get": { "id": { "Local": 5 }, "typ": { "column_types": [ { "scalar_type": "Int32", "nullable": true }, { "scalar_type": "Int32", "nullable": true } ], "keys": [] }, "access_strategy": "UnknownOrLocal" } } ], "equivalences": [], "implementation": "Unimplemented" } }, "outputs": [ 0, 1, 2, 3, 4, 5 ] } }, "predicates": [ { "CallBinary": { "func": "Eq", "expr1": { "Column": [ 3, "b" ] }, "expr2": { "Column": [ 5, "b" ] } } } ] } }, "body": { "Get": { "id": { "Local": 6 }, "typ": { "column_types": [ { "scalar_type": "Int32", "nullable": true }, { "scalar_type": "Int32", "nullable": false }, { "scalar_type": "Int32", "nullable": true }, { "scalar_type": "Int32", "nullable": false }, { "scalar_type": "Int32", "nullable": true }, { "scalar_type": "Int32", "nullable": false } ], "keys": [] }, "access_strategy": "UnknownOrLocal" } } } } } } } }, "outputs": [ 0, 2 ] } } } } EOF # Test InnerJoin (ON syntax). query T multiline EXPLAIN DECORRELATED PLAN WITH(raw) AS JSON FOR SELECT t1.a, t2.a FROM t as t1 LEFT JOIN t as t2 ON t1.b = t2.b RIGHT JOIN t as t3 ON t2.b = t3.b ---- { "Let": { "id": 0, "value": { "Constant": { "rows": { "Ok": [ [ { "data": [] }, 1 ] ] }, "typ": { "column_types": [], "keys": [] } } }, "body": { "Project": { "input": { "Let": { "id": 5, "value": { "Let": { "id": 1, "value": { "Join": { "inputs": [ { "Get": { "id": { "Local": 0 }, "typ": { "column_types": [], "keys": [ [] ] }, "access_strategy": "UnknownOrLocal" } }, { "Get": { "id": { "Global": { "User": 1 } }, "typ": { "column_types": [ { "scalar_type": "Int32", "nullable": true }, { "scalar_type": "Int32", "nullable": true } ], "keys": [] }, "access_strategy": "UnknownOrLocal" } } ], "equivalences": [], "implementation": "Unimplemented" } }, "body": { "Let": { "id": 2, "value": { "Join": { "inputs": [ { "Get": { "id": { "Local": 0 }, "typ": { "column_types": [], "keys": [ [] ] }, "access_strategy": "UnknownOrLocal" } }, { "Get": { "id": { "Global": { "User": 1 } }, "typ": { "column_types": [ { "scalar_type": "Int32", "nullable": true }, { "scalar_type": "Int32", "nullable": true } ], "keys": [] }, "access_strategy": "UnknownOrLocal" } } ], "equivalences": [], "implementation": "Unimplemented" } }, "body": { "Let": { "id": 3, "value": { "Filter": { "input": { "Project": { "input": { "Join": { "inputs": [ { "Get": { "id": { "Local": 1 }, "typ": { "column_types": [ { "scalar_type": "Int32", "nullable": true }, { "scalar_type": "Int32", "nullable": true } ], "keys": [] }, "access_strategy": "UnknownOrLocal" } }, { "Get": { "id": { "Local": 2 }, "typ": { "column_types": [ { "scalar_type": "Int32", "nullable": true }, { "scalar_type": "Int32", "nullable": true } ], "keys": [] }, "access_strategy": "UnknownOrLocal" } } ], "equivalences": [], "implementation": "Unimplemented" } }, "outputs": [ 0, 1, 2, 3 ] } }, "predicates": [ { "CallBinary": { "func": "Eq", "expr1": { "Column": [ 1, "b" ] }, "expr2": { "Column": [ 3, "b" ] } } } ] } }, "body": { "Let": { "id": 4, "value": { "Reduce": { "input": { "Project": { "input": { "Get": { "id": { "Local": 3 }, "typ": { "column_types": [ { "scalar_type": "Int32", "nullable": true }, { "scalar_type": "Int32", "nullable": false }, { "scalar_type": "Int32", "nullable": true }, { "scalar_type": "Int32", "nullable": false } ], "keys": [] }, "access_strategy": "UnknownOrLocal" } }, "outputs": [ 1 ] } }, "group_key": [ { "Column": [ 0, null ] } ], "aggregates": [], "monotonic": false, "expected_group_size": null } }, "body": { "Union": { "base": { "Map": { "input": { "Union": { "base": { "Negate": { "input": { "Project": { "input": { "Join": { "inputs": [ { "Filter": { "input": { "Get": { "id": { "Local": 1 }, "typ": { "column_types": [ { "scalar_type": "Int32", "nullable": true }, { "scalar_type": "Int32", "nullable": true } ], "keys": [] }, "access_strategy": "UnknownOrLocal" } }, "predicates": [ { "CallUnary": { "func": { "Not": null }, "expr": { "CallUnary": { "func": { "IsNull": null }, "expr": { "Column": [ 1, "b" ] } } } } } ] } }, { "Get": { "id": { "Local": 4 }, "typ": { "column_types": [ { "scalar_type": "Int32", "nullable": false } ], "keys": [ [ 0 ] ] }, "access_strategy": "UnknownOrLocal" } } ], "equivalences": [ [ { "Column": [ 1, "b" ] }, { "Column": [ 2, null ] } ] ], "implementation": "Unimplemented" } }, "outputs": [ 0, 1 ] } } } }, "inputs": [ { "Get": { "id": { "Local": 1 }, "typ": { "column_types": [ { "scalar_type": "Int32", "nullable": true }, { "scalar_type": "Int32", "nullable": true } ], "keys": [] }, "access_strategy": "UnknownOrLocal" } } ] } }, "scalars": [ { "Literal": [ { "Ok": { "data": [ 0 ] } }, { "scalar_type": "Int32", "nullable": true } ] }, { "Literal": [ { "Ok": { "data": [ 0 ] } }, { "scalar_type": "Int32", "nullable": true } ] } ] } }, "inputs": [ { "Get": { "id": { "Local": 3 }, "typ": { "column_types": [ { "scalar_type": "Int32", "nullable": true }, { "scalar_type": "Int32", "nullable": false }, { "scalar_type": "Int32", "nullable": true }, { "scalar_type": "Int32", "nullable": false } ], "keys": [] }, "access_strategy": "UnknownOrLocal" } } ] } } } } } } } } } }, "body": { "Let": { "id": 6, "value": { "Join": { "inputs": [ { "Get": { "id": { "Local": 0 }, "typ": { "column_types": [], "keys": [ [] ] }, "access_strategy": "UnknownOrLocal" } }, { "Get": { "id": { "Global": { "User": 1 } }, "typ": { "column_types": [ { "scalar_type": "Int32", "nullable": true }, { "scalar_type": "Int32", "nullable": true } ], "keys": [] }, "access_strategy": "UnknownOrLocal" } } ], "equivalences": [], "implementation": "Unimplemented" } }, "body": { "Let": { "id": 7, "value": { "Filter": { "input": { "Project": { "input": { "Join": { "inputs": [ { "Get": { "id": { "Local": 5 }, "typ": { "column_types": [ { "scalar_type": "Int32", "nullable": true }, { "scalar_type": "Int32", "nullable": true }, { "scalar_type": "Int32", "nullable": true }, { "scalar_type": "Int32", "nullable": true } ], "keys": [] }, "access_strategy": "UnknownOrLocal" } }, { "Get": { "id": { "Local": 6 }, "typ": { "column_types": [ { "scalar_type": "Int32", "nullable": true }, { "scalar_type": "Int32", "nullable": true } ], "keys": [] }, "access_strategy": "UnknownOrLocal" } } ], "equivalences": [], "implementation": "Unimplemented" } }, "outputs": [ 0, 1, 2, 3, 4, 5 ] } }, "predicates": [ { "CallBinary": { "func": "Eq", "expr1": { "Column": [ 3, "b" ] }, "expr2": { "Column": [ 5, "b" ] } } } ] } }, "body": { "Let": { "id": 8, "value": { "Reduce": { "input": { "Project": { "input": { "Get": { "id": { "Local": 7 }, "typ": { "column_types": [ { "scalar_type": "Int32", "nullable": true }, { "scalar_type": "Int32", "nullable": true }, { "scalar_type": "Int32", "nullable": true }, { "scalar_type": "Int32", "nullable": false }, { "scalar_type": "Int32", "nullable": true }, { "scalar_type": "Int32", "nullable": false } ], "keys": [] }, "access_strategy": "UnknownOrLocal" } }, "outputs": [ 3 ] } }, "group_key": [ { "Column": [ 0, null ] } ], "aggregates": [], "monotonic": false, "expected_group_size": null } }, "body": { "Union": { "base": { "Project": { "input": { "Map": { "input": { "Union": { "base": { "Negate": { "input": { "Project": { "input": { "Join": { "inputs": [ { "Filter": { "input": { "Get": { "id": { "Local": 6 }, "typ": { "column_types": [ { "scalar_type": "Int32", "nullable": true }, { "scalar_type": "Int32", "nullable": true } ], "keys": [] }, "access_strategy": "UnknownOrLocal" } }, "predicates": [ { "CallUnary": { "func": { "Not": null }, "expr": { "CallUnary": { "func": { "IsNull": null }, "expr": { "Column": [ 1, "b" ] } } } } } ] } }, { "Get": { "id": { "Local": 8 }, "typ": { "column_types": [ { "scalar_type": "Int32", "nullable": false } ], "keys": [ [ 0 ] ] }, "access_strategy": "UnknownOrLocal" } } ], "equivalences": [ [ { "Column": [ 1, "b" ] }, { "Column": [ 2, null ] } ] ], "implementation": "Unimplemented" } }, "outputs": [ 0, 1 ] } } } }, "inputs": [ { "Get": { "id": { "Local": 6 }, "typ": { "column_types": [ { "scalar_type": "Int32", "nullable": true }, { "scalar_type": "Int32", "nullable": true } ], "keys": [] }, "access_strategy": "UnknownOrLocal" } } ] } }, "scalars": [ { "Literal": [ { "Ok": { "data": [ 0 ] } }, { "scalar_type": "Int32", "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": "Int32", "nullable": true } ] } ] } }, "outputs": [ 2, 3, 4, 5, 0, 1 ] } }, "inputs": [ { "Get": { "id": { "Local": 7 }, "typ": { "column_types": [ { "scalar_type": "Int32", "nullable": true }, { "scalar_type": "Int32", "nullable": true }, { "scalar_type": "Int32", "nullable": true }, { "scalar_type": "Int32", "nullable": false }, { "scalar_type": "Int32", "nullable": true }, { "scalar_type": "Int32", "nullable": false } ], "keys": [] }, "access_strategy": "UnknownOrLocal" } } ] } } } } } } } } } }, "outputs": [ 0, 2 ] } } } } EOF # Test a single CTE. query T multiline EXPLAIN DECORRELATED PLAN WITH(raw) AS JSON FOR WITH x AS (SELECT t.a * t.b as v from t) SELECT a.v + b.v FROM x as a, x as b ---- { "Let": { "id": 0, "value": { "Constant": { "rows": { "Ok": [ [ { "data": [] }, 1 ] ] }, "typ": { "column_types": [], "keys": [] } } }, "body": { "Project": { "input": { "Let": { "id": 2, "value": { "Project": { "input": { "Map": { "input": { "Let": { "id": 1, "value": { "Join": { "inputs": [ { "Get": { "id": { "Local": 0 }, "typ": { "column_types": [], "keys": [ [] ] }, "access_strategy": "UnknownOrLocal" } }, { "Get": { "id": { "Global": { "User": 1 } }, "typ": { "column_types": [ { "scalar_type": "Int32", "nullable": true }, { "scalar_type": "Int32", "nullable": true } ], "keys": [] }, "access_strategy": "UnknownOrLocal" } } ], "equivalences": [], "implementation": "Unimplemented" } }, "body": { "Get": { "id": { "Local": 1 }, "typ": { "column_types": [ { "scalar_type": "Int32", "nullable": true }, { "scalar_type": "Int32", "nullable": true } ], "keys": [] }, "access_strategy": "UnknownOrLocal" } } } }, "scalars": [ { "CallBinary": { "func": "MulInt32", "expr1": { "Column": [ 0, "a" ] }, "expr2": { "Column": [ 1, "b" ] } } } ] } }, "outputs": [ 2 ] } }, "body": { "Project": { "input": { "Map": { "input": { "Let": { "id": 4, "value": { "Let": { "id": 3, "value": { "Project": { "input": { "Join": { "inputs": [ { "Get": { "id": { "Local": 2 }, "typ": { "column_types": [ { "scalar_type": "Int32", "nullable": true } ], "keys": [] }, "access_strategy": "UnknownOrLocal" } }, { "Get": { "id": { "Local": 2 }, "typ": { "column_types": [ { "scalar_type": "Int32", "nullable": true } ], "keys": [] }, "access_strategy": "UnknownOrLocal" } } ], "equivalences": [], "implementation": "Unimplemented" } }, "outputs": [ 0, 1 ] } }, "body": { "Get": { "id": { "Local": 3 }, "typ": { "column_types": [ { "scalar_type": "Int32", "nullable": true }, { "scalar_type": "Int32", "nullable": true } ], "keys": [] }, "access_strategy": "UnknownOrLocal" } } } }, "body": { "Get": { "id": { "Local": 4 }, "typ": { "column_types": [ { "scalar_type": "Int32", "nullable": true }, { "scalar_type": "Int32", "nullable": true } ], "keys": [] }, "access_strategy": "UnknownOrLocal" } } } }, "scalars": [ { "CallBinary": { "func": "AddInt32", "expr1": { "Column": [ 0, "v" ] }, "expr2": { "Column": [ 1, "v" ] } } } ] } }, "outputs": [ 0, 1, 2 ] } } } }, "outputs": [ 2 ] } } } } EOF # Test multiple CTEs: a case where we cannot pull the let statement up through # the join because the local l0 is correlated against the lhs of the enclosing join. query T multiline EXPLAIN DECORRELATED PLAN WITH(raw) AS JSON FOR SELECT * FROM ( SELECT * FROM t ) as r1 CROSS JOIN LATERAL ( WITH r2 as ( SELECT MAX(r1.a * t.a) AS m FROM t ) SELECT * FROM r2 WHERE r2.m != r1.a ) as r3 CROSS JOIN LATERAL ( WITH r4 as ( SELECT MAX(r1.a * t.a) AS m FROM t ) SELECT * FROM r4 WHERE r4.m != r1.a OR (r4.m IS NOT NULL AND r1.a IS NULL) ) as r5; ---- { "Let": { "id": 0, "value": { "Constant": { "rows": { "Ok": [ [ { "data": [] }, 1 ] ] }, "typ": { "column_types": [], "keys": [] } } }, "body": { "Let": { "id": 5, "value": { "Let": { "id": 1, "value": { "Join": { "inputs": [ { "Get": { "id": { "Local": 0 }, "typ": { "column_types": [], "keys": [ [] ] }, "access_strategy": "UnknownOrLocal" } }, { "Get": { "id": { "Global": { "User": 1 } }, "typ": { "column_types": [ { "scalar_type": "Int32", "nullable": true }, { "scalar_type": "Int32", "nullable": true } ], "keys": [] }, "access_strategy": "UnknownOrLocal" } } ], "equivalences": [], "implementation": "Unimplemented" } }, "body": { "Let": { "id": 2, "value": { "Reduce": { "input": { "Get": { "id": { "Local": 1 }, "typ": { "column_types": [ { "scalar_type": "Int32", "nullable": true }, { "scalar_type": "Int32", "nullable": true } ], "keys": [] }, "access_strategy": "UnknownOrLocal" } }, "group_key": [ { "Column": [ 0, null ] } ], "aggregates": [], "monotonic": false, "expected_group_size": null } }, "body": { "Project": { "input": { "Join": { "inputs": [ { "Get": { "id": { "Local": 1 }, "typ": { "column_types": [ { "scalar_type": "Int32", "nullable": true }, { "scalar_type": "Int32", "nullable": true } ], "keys": [] }, "access_strategy": "UnknownOrLocal" } }, { "Let": { "id": 4, "value": { "Let": { "id": 3, "value": { "Reduce": { "input": { "Join": { "inputs": [ { "Get": { "id": { "Local": 2 }, "typ": { "column_types": [ { "scalar_type": "Int32", "nullable": true } ], "keys": [ [ 0 ] ] }, "access_strategy": "UnknownOrLocal" } }, { "Get": { "id": { "Global": { "User": 1 } }, "typ": { "column_types": [ { "scalar_type": "Int32", "nullable": true }, { "scalar_type": "Int32", "nullable": true } ], "keys": [] }, "access_strategy": "UnknownOrLocal" } } ], "equivalences": [], "implementation": "Unimplemented" } }, "group_key": [ { "Column": [ 0, null ] } ], "aggregates": [ { "func": "MaxInt32", "expr": { "CallBinary": { "func": "MulInt32", "expr1": { "Column": [ 0, "a" ] }, "expr2": { "Column": [ 1, "a" ] } } }, "distinct": false } ], "monotonic": false, "expected_group_size": null } }, "body": { "Union": { "base": { "Get": { "id": { "Local": 3 }, "typ": { "column_types": [ { "scalar_type": "Int32", "nullable": true }, { "scalar_type": "Int32", "nullable": true } ], "keys": [ [ 0 ] ] }, "access_strategy": "UnknownOrLocal" } }, "inputs": [ { "Join": { "inputs": [ { "Project": { "input": { "Join": { "inputs": [ { "Union": { "base": { "Negate": { "input": { "Reduce": { "input": { "Get": { "id": { "Local": 3 }, "typ": { "column_types": [ { "scalar_type": "Int32", "nullable": true }, { "scalar_type": "Int32", "nullable": true } ], "keys": [ [ 0 ] ] }, "access_strategy": "UnknownOrLocal" } }, "group_key": [ { "Column": [ 0, null ] } ], "aggregates": [], "monotonic": false, "expected_group_size": null } } } }, "inputs": [ { "Reduce": { "input": { "Get": { "id": { "Local": 2 }, "typ": { "column_types": [ { "scalar_type": "Int32", "nullable": true } ], "keys": [ [ 0 ] ] }, "access_strategy": "UnknownOrLocal" } }, "group_key": [ { "Column": [ 0, null ] } ], "aggregates": [], "monotonic": false, "expected_group_size": null } } ] } }, { "Get": { "id": { "Local": 2 }, "typ": { "column_types": [ { "scalar_type": "Int32", "nullable": true } ], "keys": [ [ 0 ] ] }, "access_strategy": "UnknownOrLocal" } } ], "equivalences": [ [ { "Column": [ 0, null ] }, { "Column": [ 1, null ] } ] ], "implementation": "Unimplemented" } }, "outputs": [ 0 ] } }, { "Constant": { "rows": { "Ok": [ [ { "data": [ 0 ] }, 1 ] ] }, "typ": { "column_types": [ { "scalar_type": "Int32", "nullable": true } ], "keys": [] } } } ], "equivalences": [], "implementation": "Unimplemented" } } ] } } } }, "body": { "Filter": { "input": { "Get": { "id": { "Local": 4 }, "typ": { "column_types": [ { "scalar_type": "Int32", "nullable": true }, { "scalar_type": "Int32", "nullable": true } ], "keys": [] }, "access_strategy": "UnknownOrLocal" } }, "predicates": [ { "CallBinary": { "func": "NotEq", "expr1": { "Column": [ 1, "m" ] }, "expr2": { "Column": [ 0, "a" ] } } } ] } } } } ], "equivalences": [ [ { "Column": [ 0, null ] }, { "Column": [ 2, null ] } ] ], "implementation": "Unimplemented" } }, "outputs": [ 0, 1, 3 ] } } } } } }, "body": { "Let": { "id": 6, "value": { "Reduce": { "input": { "Get": { "id": { "Local": 5 }, "typ": { "column_types": [ { "scalar_type": "Int32", "nullable": false }, { "scalar_type": "Int32", "nullable": true }, { "scalar_type": "Int32", "nullable": false } ], "keys": [] }, "access_strategy": "UnknownOrLocal" } }, "group_key": [ { "Column": [ 0, null ] } ], "aggregates": [], "monotonic": false, "expected_group_size": null } }, "body": { "Project": { "input": { "Join": { "inputs": [ { "Get": { "id": { "Local": 5 }, "typ": { "column_types": [ { "scalar_type": "Int32", "nullable": false }, { "scalar_type": "Int32", "nullable": true }, { "scalar_type": "Int32", "nullable": false } ], "keys": [] }, "access_strategy": "UnknownOrLocal" } }, { "Let": { "id": 8, "value": { "Let": { "id": 7, "value": { "Reduce": { "input": { "Join": { "inputs": [ { "Get": { "id": { "Local": 6 }, "typ": { "column_types": [ { "scalar_type": "Int32", "nullable": false } ], "keys": [ [ 0 ] ] }, "access_strategy": "UnknownOrLocal" } }, { "Get": { "id": { "Global": { "User": 1 } }, "typ": { "column_types": [ { "scalar_type": "Int32", "nullable": true }, { "scalar_type": "Int32", "nullable": true } ], "keys": [] }, "access_strategy": "UnknownOrLocal" } } ], "equivalences": [], "implementation": "Unimplemented" } }, "group_key": [ { "Column": [ 0, null ] } ], "aggregates": [ { "func": "MaxInt32", "expr": { "CallBinary": { "func": "MulInt32", "expr1": { "Column": [ 0, "a" ] }, "expr2": { "Column": [ 1, "a" ] } } }, "distinct": false } ], "monotonic": false, "expected_group_size": null } }, "body": { "Union": { "base": { "Get": { "id": { "Local": 7 }, "typ": { "column_types": [ { "scalar_type": "Int32", "nullable": false }, { "scalar_type": "Int32", "nullable": true } ], "keys": [ [ 0 ] ] }, "access_strategy": "UnknownOrLocal" } }, "inputs": [ { "Join": { "inputs": [ { "Project": { "input": { "Join": { "inputs": [ { "Union": { "base": { "Negate": { "input": { "Reduce": { "input": { "Get": { "id": { "Local": 7 }, "typ": { "column_types": [ { "scalar_type": "Int32", "nullable": false }, { "scalar_type": "Int32", "nullable": true } ], "keys": [ [ 0 ] ] }, "access_strategy": "UnknownOrLocal" } }, "group_key": [ { "Column": [ 0, null ] } ], "aggregates": [], "monotonic": false, "expected_group_size": null } } } }, "inputs": [ { "Reduce": { "input": { "Get": { "id": { "Local": 6 }, "typ": { "column_types": [ { "scalar_type": "Int32", "nullable": false } ], "keys": [ [ 0 ] ] }, "access_strategy": "UnknownOrLocal" } }, "group_key": [ { "Column": [ 0, null ] } ], "aggregates": [], "monotonic": false, "expected_group_size": null } } ] } }, { "Get": { "id": { "Local": 6 }, "typ": { "column_types": [ { "scalar_type": "Int32", "nullable": false } ], "keys": [ [ 0 ] ] }, "access_strategy": "UnknownOrLocal" } } ], "equivalences": [ [ { "Column": [ 0, null ] }, { "Column": [ 1, null ] } ] ], "implementation": "Unimplemented" } }, "outputs": [ 0 ] } }, { "Constant": { "rows": { "Ok": [ [ { "data": [ 0 ] }, 1 ] ] }, "typ": { "column_types": [ { "scalar_type": "Int32", "nullable": true } ], "keys": [] } } } ], "equivalences": [], "implementation": "Unimplemented" } } ] } } } }, "body": { "Filter": { "input": { "Get": { "id": { "Local": 8 }, "typ": { "column_types": [ { "scalar_type": "Int32", "nullable": false }, { "scalar_type": "Int32", "nullable": true } ], "keys": [] }, "access_strategy": "UnknownOrLocal" } }, "predicates": [ { "CallVariadic": { "func": "Or", "exprs": [ { "CallBinary": { "func": "NotEq", "expr1": { "Column": [ 1, "m" ] }, "expr2": { "Column": [ 0, "a" ] } } }, { "CallVariadic": { "func": "And", "exprs": [ { "CallUnary": { "func": { "Not": null }, "expr": { "CallUnary": { "func": { "IsNull": null }, "expr": { "Column": [ 1, "m" ] } } } } }, { "CallUnary": { "func": { "IsNull": null }, "expr": { "Column": [ 0, "a" ] } } } ] } } ] } } ] } } } } ], "equivalences": [ [ { "Column": [ 0, null ] }, { "Column": [ 3, null ] } ] ], "implementation": "Unimplemented" } }, "outputs": [ 0, 1, 2, 4 ] } } } } } } } } EOF # Test multiple CTEs: a case where we cannot pull the let statement up # through the join because the local l0 is correlated against the lhs of # the enclosing join. query T multiline EXPLAIN DECORRELATED PLAN WITH(raw) AS JSON FOR SELECT * FROM ( SELECT * FROM t ) as r1 CROSS JOIN LATERAL ( WITH r4 as ( SELECT MAX(r1.a * t.a) AS m FROM t ) SELECT * FROM r4 CROSS JOIN LATERAL ( WITH r2 as ( SELECT MAX(r1.a * t.a) AS m FROM t ) SELECT * FROM r2 WHERE r1.a = r4.m AND r2.m > 5 ) as r3 WHERE a != r1.a ) as r5; ---- { "Let": { "id": 0, "value": { "Constant": { "rows": { "Ok": [ [ { "data": [] }, 1 ] ] }, "typ": { "column_types": [], "keys": [] } } }, "body": { "Let": { "id": 1, "value": { "Join": { "inputs": [ { "Get": { "id": { "Local": 0 }, "typ": { "column_types": [], "keys": [ [] ] }, "access_strategy": "UnknownOrLocal" } }, { "Get": { "id": { "Global": { "User": 1 } }, "typ": { "column_types": [ { "scalar_type": "Int32", "nullable": true }, { "scalar_type": "Int32", "nullable": true } ], "keys": [] }, "access_strategy": "UnknownOrLocal" } } ], "equivalences": [], "implementation": "Unimplemented" } }, "body": { "Let": { "id": 2, "value": { "Reduce": { "input": { "Get": { "id": { "Local": 1 }, "typ": { "column_types": [ { "scalar_type": "Int32", "nullable": true }, { "scalar_type": "Int32", "nullable": true } ], "keys": [] }, "access_strategy": "UnknownOrLocal" } }, "group_key": [ { "Column": [ 0, null ] } ], "aggregates": [], "monotonic": false, "expected_group_size": null } }, "body": { "Project": { "input": { "Join": { "inputs": [ { "Get": { "id": { "Local": 1 }, "typ": { "column_types": [ { "scalar_type": "Int32", "nullable": true }, { "scalar_type": "Int32", "nullable": true } ], "keys": [] }, "access_strategy": "UnknownOrLocal" } }, { "Let": { "id": 4, "value": { "Let": { "id": 3, "value": { "Reduce": { "input": { "Join": { "inputs": [ { "Get": { "id": { "Local": 2 }, "typ": { "column_types": [ { "scalar_type": "Int32", "nullable": true } ], "keys": [ [ 0 ] ] }, "access_strategy": "UnknownOrLocal" } }, { "Get": { "id": { "Global": { "User": 1 } }, "typ": { "column_types": [ { "scalar_type": "Int32", "nullable": true }, { "scalar_type": "Int32", "nullable": true } ], "keys": [] }, "access_strategy": "UnknownOrLocal" } } ], "equivalences": [], "implementation": "Unimplemented" } }, "group_key": [ { "Column": [ 0, null ] } ], "aggregates": [ { "func": "MaxInt32", "expr": { "CallBinary": { "func": "MulInt32", "expr1": { "Column": [ 0, "a" ] }, "expr2": { "Column": [ 1, "a" ] } } }, "distinct": false } ], "monotonic": false, "expected_group_size": null } }, "body": { "Union": { "base": { "Get": { "id": { "Local": 3 }, "typ": { "column_types": [ { "scalar_type": "Int32", "nullable": true }, { "scalar_type": "Int32", "nullable": true } ], "keys": [ [ 0 ] ] }, "access_strategy": "UnknownOrLocal" } }, "inputs": [ { "Join": { "inputs": [ { "Project": { "input": { "Join": { "inputs": [ { "Union": { "base": { "Negate": { "input": { "Reduce": { "input": { "Get": { "id": { "Local": 3 }, "typ": { "column_types": [ { "scalar_type": "Int32", "nullable": true }, { "scalar_type": "Int32", "nullable": true } ], "keys": [ [ 0 ] ] }, "access_strategy": "UnknownOrLocal" } }, "group_key": [ { "Column": [ 0, null ] } ], "aggregates": [], "monotonic": false, "expected_group_size": null } } } }, "inputs": [ { "Reduce": { "input": { "Get": { "id": { "Local": 2 }, "typ": { "column_types": [ { "scalar_type": "Int32", "nullable": true } ], "keys": [ [ 0 ] ] }, "access_strategy": "UnknownOrLocal" } }, "group_key": [ { "Column": [ 0, null ] } ], "aggregates": [], "monotonic": false, "expected_group_size": null } } ] } }, { "Get": { "id": { "Local": 2 }, "typ": { "column_types": [ { "scalar_type": "Int32", "nullable": true } ], "keys": [ [ 0 ] ] }, "access_strategy": "UnknownOrLocal" } } ], "equivalences": [ [ { "Column": [ 0, null ] }, { "Column": [ 1, null ] } ] ], "implementation": "Unimplemented" } }, "outputs": [ 0 ] } }, { "Constant": { "rows": { "Ok": [ [ { "data": [ 0 ] }, 1 ] ] }, "typ": { "column_types": [ { "scalar_type": "Int32", "nullable": true } ], "keys": [] } } } ], "equivalences": [], "implementation": "Unimplemented" } } ] } } } }, "body": { "Filter": { "input": { "Let": { "id": 5, "value": { "Reduce": { "input": { "Get": { "id": { "Local": 4 }, "typ": { "column_types": [ { "scalar_type": "Int32", "nullable": true }, { "scalar_type": "Int32", "nullable": true } ], "keys": [] }, "access_strategy": "UnknownOrLocal" } }, "group_key": [ { "Column": [ 1, null ] }, { "Column": [ 0, null ] } ], "aggregates": [], "monotonic": false, "expected_group_size": null } }, "body": { "Project": { "input": { "Join": { "inputs": [ { "Get": { "id": { "Local": 4 }, "typ": { "column_types": [ { "scalar_type": "Int32", "nullable": true }, { "scalar_type": "Int32", "nullable": true } ], "keys": [] }, "access_strategy": "UnknownOrLocal" } }, { "Let": { "id": 7, "value": { "Let": { "id": 6, "value": { "Reduce": { "input": { "Join": { "inputs": [ { "Get": { "id": { "Local": 5 }, "typ": { "column_types": [ { "scalar_type": "Int32", "nullable": true }, { "scalar_type": "Int32", "nullable": true } ], "keys": [ [ 0, 1 ] ] }, "access_strategy": "UnknownOrLocal" } }, { "Get": { "id": { "Global": { "User": 1 } }, "typ": { "column_types": [ { "scalar_type": "Int32", "nullable": true }, { "scalar_type": "Int32", "nullable": true } ], "keys": [] }, "access_strategy": "UnknownOrLocal" } } ], "equivalences": [], "implementation": "Unimplemented" } }, "group_key": [ { "Column": [ 0, null ] }, { "Column": [ 1, null ] } ], "aggregates": [ { "func": "MaxInt32", "expr": { "CallBinary": { "func": "MulInt32", "expr1": { "Column": [ 1, "a" ] }, "expr2": { "Column": [ 2, "a" ] } } }, "distinct": false } ], "monotonic": false, "expected_group_size": null } }, "body": { "Union": { "base": { "Get": { "id": { "Local": 6 }, "typ": { "column_types": [ { "scalar_type": "Int32", "nullable": true }, { "scalar_type": "Int32", "nullable": true }, { "scalar_type": "Int32", "nullable": true } ], "keys": [ [ 0, 1 ] ] }, "access_strategy": "UnknownOrLocal" } }, "inputs": [ { "Join": { "inputs": [ { "Project": { "input": { "Join": { "inputs": [ { "Union": { "base": { "Negate": { "input": { "Reduce": { "input": { "Get": { "id": { "Local": 6 }, "typ": { "column_types": [ { "scalar_type": "Int32", "nullable": true }, { "scalar_type": "Int32", "nullable": true }, { "scalar_type": "Int32", "nullable": true } ], "keys": [ [ 0, 1 ] ] }, "access_strategy": "UnknownOrLocal" } }, "group_key": [ { "Column": [ 0, null ] }, { "Column": [ 1, null ] } ], "aggregates": [], "monotonic": false, "expected_group_size": null } } } }, "inputs": [ { "Reduce": { "input": { "Get": { "id": { "Local": 5 }, "typ": { "column_types": [ { "scalar_type": "Int32", "nullable": true }, { "scalar_type": "Int32", "nullable": true } ], "keys": [ [ 0, 1 ] ] }, "access_strategy": "UnknownOrLocal" } }, "group_key": [ { "Column": [ 0, null ] }, { "Column": [ 1, null ] } ], "aggregates": [], "monotonic": false, "expected_group_size": null } } ] } }, { "Get": { "id": { "Local": 5 }, "typ": { "column_types": [ { "scalar_type": "Int32", "nullable": true }, { "scalar_type": "Int32", "nullable": true } ], "keys": [ [ 0, 1 ] ] }, "access_strategy": "UnknownOrLocal" } } ], "equivalences": [ [ { "Column": [ 0, null ] }, { "Column": [ 2, null ] } ], [ { "Column": [ 1, null ] }, { "Column": [ 3, null ] } ] ], "implementation": "Unimplemented" } }, "outputs": [ 0, 1 ] } }, { "Constant": { "rows": { "Ok": [ [ { "data": [ 0 ] }, 1 ] ] }, "typ": { "column_types": [ { "scalar_type": "Int32", "nullable": true } ], "keys": [] } } } ], "equivalences": [], "implementation": "Unimplemented" } } ] } } } }, "body": { "Filter": { "input": { "Get": { "id": { "Local": 7 }, "typ": { "column_types": [ { "scalar_type": "Int32", "nullable": true }, { "scalar_type": "Int32", "nullable": true }, { "scalar_type": "Int32", "nullable": true } ], "keys": [] }, "access_strategy": "UnknownOrLocal" } }, "predicates": [ { "CallVariadic": { "func": "And", "exprs": [ { "CallBinary": { "func": "Eq", "expr1": { "Column": [ 1, "a" ] }, "expr2": { "Column": [ 0, "m" ] } } }, { "CallBinary": { "func": "Gt", "expr1": { "Column": [ 2, "m" ] }, "expr2": { "Literal": [ { "Ok": { "data": [ 45, 5 ] } }, { "scalar_type": "Int32", "nullable": false } ] } } } ] } } ] } } } } ], "equivalences": [ [ { "Column": [ 1, null ] }, { "Column": [ 2, null ] } ], [ { "Column": [ 0, null ] }, { "Column": [ 3, null ] } ] ], "implementation": "Unimplemented" } }, "outputs": [ 0, 1, 4 ] } } } }, "predicates": [ { "CallBinary": { "func": "NotEq", "expr1": { "Column": [ 0, "a" ] }, "expr2": { "Column": [ 0, "a" ] } } } ] } } } } ], "equivalences": [ [ { "Column": [ 0, null ] }, { "Column": [ 2, null ] } ] ], "implementation": "Unimplemented" } }, "outputs": [ 0, 1, 3, 4 ] } } } } } } } } EOF