# 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 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 basic linear chains. query T multiline EXPLAIN RAW PLAN AS JSON FOR SELECT a + 1, b, 4 FROM mv WHERE a > 0 ---- { "Project": { "input": { "Map": { "input": { "Filter": { "input": { "Get": { "id": { "Global": { "User": 5 } }, "typ": { "column_types": [ { "scalar_type": "Int32", "nullable": false }, { "scalar_type": "Int32", "nullable": true } ], "keys": [] } } }, "predicates": [ { "CallBinary": { "func": "Gt", "expr1": { "Column": [ { "level": 0, "column": 0 }, "a" ] }, "expr2": { "Literal": [ { "data": [ 44 ] }, { "scalar_type": "Int32", "nullable": false }, null ] }, "name": null } } ] } }, "scalars": [ { "CallBinary": { "func": "AddInt32", "expr1": { "Column": [ { "level": 0, "column": 0 }, "a" ] }, "expr2": { "Literal": [ { "data": [ 45, 1 ] }, { "scalar_type": "Int32", "nullable": false }, null ] }, "name": null } }, { "Literal": [ { "data": [ 45, 4 ] }, { "scalar_type": "Int32", "nullable": false }, null ] } ] } }, "outputs": [ 2, 1, 3 ] } } EOF # Test table functions (CallTable). query T multiline EXPLAIN RAW PLAN AS JSON FOR SELECT * FROM generate_series(1, 7) ---- { "CallTable": { "func": "GenerateSeriesInt32", "exprs": [ { "Literal": [ { "data": [ 45, 1 ] }, { "scalar_type": "Int32", "nullable": false }, null ] }, { "Literal": [ { "data": [ 45, 7 ] }, { "scalar_type": "Int32", "nullable": false }, null ] }, { "Literal": [ { "data": [ 45, 1 ] }, { "scalar_type": "Int32", "nullable": false }, null ] } ] } } EOF # Test Threshold, Union, Distinct, Negate. query T multiline EXPLAIN RAW PLAN AS JSON FOR SELECT a FROM t EXCEPT SELECT b FROM mv ---- { "Threshold": { "input": { "Union": { "base": { "Distinct": { "input": { "Project": { "input": { "Get": { "id": { "Global": { "User": 1 } }, "typ": { "column_types": [ { "scalar_type": "Int32", "nullable": true }, { "scalar_type": "Int32", "nullable": true } ], "keys": [] } } }, "outputs": [ 0 ] } } } }, "inputs": [ { "Negate": { "input": { "Distinct": { "input": { "Project": { "input": { "Get": { "id": { "Global": { "User": 5 } }, "typ": { "column_types": [ { "scalar_type": "Int32", "nullable": false }, { "scalar_type": "Int32", "nullable": true } ], "keys": [] } } }, "outputs": [ 1 ] } } } } } } ] } } } } EOF # Test Finish (TODO). query T multiline EXPLAIN RAW PLAN AS JSON FOR SELECT * FROM t ORDER BY b asc, a desc LIMIT 5 ---- { "Get": { "id": { "Global": { "User": 1 } }, "typ": { "column_types": [ { "scalar_type": "Int32", "nullable": true }, { "scalar_type": "Int32", "nullable": true } ], "keys": [] } } } EOF # Test Reduce (global). query T multiline EXPLAIN RAW PLAN AS JSON FOR SELECT abs(min(a) - max(a)) FROM t ---- { "Project": { "input": { "Map": { "input": { "Reduce": { "input": { "Get": { "id": { "Global": { "User": 1 } }, "typ": { "column_types": [ { "scalar_type": "Int32", "nullable": true }, { "scalar_type": "Int32", "nullable": true } ], "keys": [] } } }, "group_key": [], "aggregates": [ { "func": "MinInt32", "expr": { "Column": [ { "level": 0, "column": 0 }, "a" ] }, "distinct": false }, { "func": "MaxInt32", "expr": { "Column": [ { "level": 0, "column": 0 }, "a" ] }, "distinct": false } ], "expected_group_size": null } }, "scalars": [ { "CallUnary": { "func": { "AbsInt32": null }, "expr": { "CallBinary": { "func": "SubInt32", "expr1": { "Column": [ { "level": 0, "column": 0 }, "?column?" ] }, "expr2": { "Column": [ { "level": 0, "column": 1 }, "?column?" ] }, "name": null } }, "name": null } } ] } }, "outputs": [ 2 ] } } EOF # Test Reduce (local). query T multiline EXPLAIN RAW PLAN AS JSON FOR SELECT abs(min(a) - max(a)) FROM t GROUP BY b ---- { "Project": { "input": { "Map": { "input": { "Reduce": { "input": { "Map": { "input": { "Get": { "id": { "Global": { "User": 1 } }, "typ": { "column_types": [ { "scalar_type": "Int32", "nullable": true }, { "scalar_type": "Int32", "nullable": true } ], "keys": [] } } }, "scalars": [ { "Column": [ { "level": 0, "column": 1 }, "b" ] } ] } }, "group_key": [ 2 ], "aggregates": [ { "func": "MinInt32", "expr": { "Column": [ { "level": 0, "column": 0 }, "a" ] }, "distinct": false }, { "func": "MaxInt32", "expr": { "Column": [ { "level": 0, "column": 0 }, "a" ] }, "distinct": false } ], "expected_group_size": null } }, "scalars": [ { "CallUnary": { "func": { "AbsInt32": null }, "expr": { "CallBinary": { "func": "SubInt32", "expr1": { "Column": [ { "level": 0, "column": 1 }, "?column?" ] }, "expr2": { "Column": [ { "level": 0, "column": 2 }, "?column?" ] }, "name": null } }, "name": null } } ] } }, "outputs": [ 3 ] } } EOF # Test EXISTS subqueries. query T multiline EXPLAIN RAW PLAN WITH(raw syntax) 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": { "name": "subquery-2", "id": 2, "value": { "Filter": { "input": { "Get": { "id": { "Global": { "User": 5 } }, "typ": { "column_types": [ { "scalar_type": "Int32", "nullable": false }, { "scalar_type": "Int32", "nullable": true } ], "keys": [] } } }, "predicates": [ { "CallBinary": { "func": "Gt", "expr1": { "Column": [ { "level": 1, "column": 1 }, "b" ] }, "expr2": { "Column": [ { "level": 0, "column": 1 }, "b" ] }, "name": null } } ] } }, "body": { "Let": { "name": "subquery-1", "id": 1, "value": { "Filter": { "input": { "Get": { "id": { "Global": { "User": 5 } }, "typ": { "column_types": [ { "scalar_type": "Int32", "nullable": false }, { "scalar_type": "Int32", "nullable": true } ], "keys": [] } } }, "predicates": [ { "CallBinary": { "func": "Lt", "expr1": { "Column": [ { "level": 1, "column": 0 }, "a" ] }, "expr2": { "Column": [ { "level": 0, "column": 0 }, "a" ] }, "name": null } } ] } }, "body": { "Filter": { "input": { "Get": { "id": { "Global": { "User": 1 } }, "typ": { "column_types": [ { "scalar_type": "Int32", "nullable": true }, { "scalar_type": "Int32", "nullable": true } ], "keys": [] } } }, "predicates": [ { "CallVariadic": { "func": "And", "exprs": [ { "Exists": [ { "Get": { "id": { "Local": 1 }, "typ": { "column_types": [], "keys": [] } } }, null ] }, { "Exists": [ { "Get": { "id": { "Local": 2 }, "typ": { "column_types": [], "keys": [] } } }, null ] } ], "name": null } } ] } } } } } } EOF # Test SELECT subqueries. query T multiline EXPLAIN RAW PLAN WITH(raw syntax) 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 ---- { "Project": { "input": { "Let": { "name": "subquery-2", "id": 2, "value": { "Project": { "input": { "TopK": { "input": { "Filter": { "input": { "Get": { "id": { "Global": { "User": 5 } }, "typ": { "column_types": [ { "scalar_type": "Int32", "nullable": false }, { "scalar_type": "Int32", "nullable": true } ], "keys": [] } } }, "predicates": [ { "CallBinary": { "func": "Eq", "expr1": { "Column": [ { "level": 0, "column": 1 }, "b" ] }, "expr2": { "Column": [ { "level": 1, "column": 1 }, "b" ] }, "name": null } } ] } }, "group_key": [], "order_key": [], "limit": { "Literal": [ { "data": [ 50, 1 ] }, { "scalar_type": "Int64", "nullable": false }, null ] }, "offset": { "Literal": [ { "data": [ 49 ] }, { "scalar_type": "Int64", "nullable": false }, null ] }, "expected_group_size": null } }, "outputs": [ 0 ] } }, "body": { "Let": { "name": "subquery-1", "id": 1, "value": { "Project": { "input": { "TopK": { "input": { "Filter": { "input": { "Get": { "id": { "Global": { "User": 3 } }, "typ": { "column_types": [ { "scalar_type": "Int32", "nullable": false }, { "scalar_type": "Int32", "nullable": true } ], "keys": [] } } }, "predicates": [ { "CallBinary": { "func": "Eq", "expr1": { "Column": [ { "level": 0, "column": 1 }, "b" ] }, "expr2": { "Column": [ { "level": 1, "column": 1 }, "b" ] }, "name": null } } ] } }, "group_key": [], "order_key": [], "limit": { "Literal": [ { "data": [ 50, 1 ] }, { "scalar_type": "Int64", "nullable": false }, null ] }, "offset": { "Literal": [ { "data": [ 49 ] }, { "scalar_type": "Int64", "nullable": false }, null ] }, "expected_group_size": null } }, "outputs": [ 0 ] } }, "body": { "Map": { "input": { "Get": { "id": { "Global": { "User": 1 } }, "typ": { "column_types": [ { "scalar_type": "Int32", "nullable": true }, { "scalar_type": "Int32", "nullable": true } ], "keys": [] } } }, "scalars": [ { "Select": [ { "Get": { "id": { "Local": 1 }, "typ": { "column_types": [], "keys": [] } } }, null ] }, { "Select": [ { "Get": { "id": { "Local": 2 }, "typ": { "column_types": [], "keys": [] } } }, null ] } ] } } } } } }, "outputs": [ 2, 3 ] } } EOF # Test CrossJoin derived from a comma join without a predicate. query T multiline EXPLAIN RAW PLAN AS JSON FOR SELECT t1.a, t2.a FROM t as t1, t as t2 ---- { "Project": { "input": { "Join": { "left": { "Get": { "id": { "Global": { "User": 1 } }, "typ": { "column_types": [ { "scalar_type": "Int32", "nullable": true }, { "scalar_type": "Int32", "nullable": true } ], "keys": [] } } }, "right": { "Get": { "id": { "Global": { "User": 1 } }, "typ": { "column_types": [ { "scalar_type": "Int32", "nullable": true }, { "scalar_type": "Int32", "nullable": true } ], "keys": [] } } }, "on": { "Literal": [ { "data": [ 2 ] }, { "scalar_type": "Bool", "nullable": false }, null ] }, "kind": "Inner" } }, "outputs": [ 0, 2 ] } } EOF # Test InnerJoin (comma syntax). query T multiline EXPLAIN RAW PLAN 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 ---- { "Project": { "input": { "Filter": { "input": { "Join": { "left": { "Join": { "left": { "Get": { "id": { "Global": { "User": 1 } }, "typ": { "column_types": [ { "scalar_type": "Int32", "nullable": true }, { "scalar_type": "Int32", "nullable": true } ], "keys": [] } } }, "right": { "Get": { "id": { "Global": { "User": 1 } }, "typ": { "column_types": [ { "scalar_type": "Int32", "nullable": true }, { "scalar_type": "Int32", "nullable": true } ], "keys": [] } } }, "on": { "Literal": [ { "data": [ 2 ] }, { "scalar_type": "Bool", "nullable": false }, null ] }, "kind": "Inner" } }, "right": { "Get": { "id": { "Global": { "User": 1 } }, "typ": { "column_types": [ { "scalar_type": "Int32", "nullable": true }, { "scalar_type": "Int32", "nullable": true } ], "keys": [] } } }, "on": { "Literal": [ { "data": [ 2 ] }, { "scalar_type": "Bool", "nullable": false }, null ] }, "kind": "Inner" } }, "predicates": [ { "CallVariadic": { "func": "And", "exprs": [ { "CallBinary": { "func": "Eq", "expr1": { "Column": [ { "level": 0, "column": 1 }, "b" ] }, "expr2": { "Column": [ { "level": 0, "column": 3 }, "b" ] }, "name": null } }, { "CallBinary": { "func": "Eq", "expr1": { "Column": [ { "level": 0, "column": 3 }, "b" ] }, "expr2": { "Column": [ { "level": 0, "column": 5 }, "b" ] }, "name": null } } ], "name": null } } ] } }, "outputs": [ 0, 2 ] } } EOF # Test multiple CTEs: directly nested 'Let' variants are rendered in a flattened way. query T multiline EXPLAIN RAW PLAN AS JSON FOR WITH A AS (SELECT 1 AS a), B as (SELECT a as b FROM A WHERE a > 0) SELECT * FROM A, B; ---- { "Let": { "name": "a", "id": 0, "value": { "Map": { "input": { "Constant": { "rows": [ { "data": [] } ], "typ": { "column_types": [], "keys": [] } } }, "scalars": [ { "Literal": [ { "data": [ 45, 1 ] }, { "scalar_type": "Int32", "nullable": false }, null ] } ] } }, "body": { "Let": { "name": "b", "id": 1, "value": { "Filter": { "input": { "Get": { "id": { "Local": 0 }, "typ": { "column_types": [ { "scalar_type": "Int32", "nullable": false } ], "keys": [] } } }, "predicates": [ { "CallBinary": { "func": "Gt", "expr1": { "Column": [ { "level": 0, "column": 0 }, "a" ] }, "expr2": { "Literal": [ { "data": [ 44 ] }, { "scalar_type": "Int32", "nullable": false }, null ] }, "name": null } } ] } }, "body": { "Join": { "left": { "Get": { "id": { "Local": 0 }, "typ": { "column_types": [ { "scalar_type": "Int32", "nullable": false } ], "keys": [] } } }, "right": { "Get": { "id": { "Local": 1 }, "typ": { "column_types": [ { "scalar_type": "Int32", "nullable": false } ], "keys": [] } } }, "on": { "Literal": [ { "data": [ 2 ] }, { "scalar_type": "Bool", "nullable": false }, null ] }, "kind": "Inner" } } } } } } EOF