# 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 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 VIEW ov AS SELECT * FROM t ORDER BY b asc, a desc LIMIT 5 statement ok CREATE VIEW iv AS SELECT * FROM t WHERE a IS NOT NULL statement ok CREATE DEFAULT INDEX ON iv statement ok CREATE MATERIALIZED VIEW mv AS SELECT * FROM t WHERE a IS NOT NULL mode cockroach # Test constant error. query T multiline EXPLAIN OPTIMIZED PLAN WITH(raw) AS JSON FOR SELECT 1 / 0 ---- { "plans": [ { "id": "Explained Query (fast path)", "plan": { "Constant": [ { "Err": "DivisionByZero" }, { "column_types": [ { "scalar_type": "Int32", "nullable": false } ], "keys": [] } ] } } ], "sources": [] } EOF # Test constant with two elements. query T multiline EXPLAIN OPTIMIZED PLAN WITH(raw) AS JSON FOR (SELECT 1, 2) UNION ALL (SELECT 1, 2) UNION ALL (SELECT 3, 4) ---- { "plans": [ { "id": "Explained Query (fast path)", "plan": { "Constant": [ { "Ok": [ [ { "data": [ 45, 1, 45, 2 ] }, 2 ], [ { "data": [ 45, 3, 45, 4 ] }, 1 ] ] }, { "column_types": [ { "scalar_type": "Int32", "nullable": false }, { "scalar_type": "Int32", "nullable": false } ], "keys": [] } ] } } ], "sources": [] } EOF # Test basic linear chains (fast path). query T multiline EXPLAIN OPTIMIZED PLAN WITH(raw) AS JSON FOR SELECT 1, a + b as c FROM t WHERE a > 0 and b < 0 and a + b > 0 ---- { "plans": [ { "id": "Explained Query (fast path)", "plan": { "PeekExisting": [ { "User": 1 }, { "User": 4 }, null, { "mfp": { "expressions": [ { "CallBinary": { "func": "AddInt32", "expr1": { "Column": [ 0, "a" ] }, "expr2": { "Column": [ 1, "b" ] } } }, { "Literal": [ { "Ok": { "data": [ 45, 1 ] } }, { "scalar_type": "Int32", "nullable": false } ] } ], "predicates": [ [ 1, { "CallBinary": { "func": "Gt", "expr1": { "Column": [ 0, "a" ] }, "expr2": { "Literal": [ { "Ok": { "data": [ 44 ] } }, { "scalar_type": "Int32", "nullable": false } ] } } } ], [ 2, { "CallBinary": { "func": "Lt", "expr1": { "Column": [ 1, "b" ] }, "expr2": { "Literal": [ { "Ok": { "data": [ 44 ] } }, { "scalar_type": "Int32", "nullable": false } ] } } } ], [ 3, { "CallBinary": { "func": "Gt", "expr1": { "Column": [ 2, null ] }, "expr2": { "Literal": [ { "Ok": { "data": [ 44 ] } }, { "scalar_type": "Int32", "nullable": false } ] } } } ] ], "projection": [ 3, 2 ], "input_arity": 2 } } ] } } ], "sources": [] } EOF # Test basic linear chains (slow path). query T multiline EXPLAIN OPTIMIZED PLAN WITH(raw) AS JSON FOR SELECT 1, a + b as c FROM mv WHERE a > 0 and b < 0 and a + b > 0 ---- { "plans": [ { "id": "Explained Query", "plan": { "Project": { "input": { "Filter": { "input": { "Map": { "input": { "Get": { "id": { "Global": { "User": 8 } }, "typ": { "column_types": [ { "scalar_type": "Int32", "nullable": false }, { "scalar_type": "Int32", "nullable": true } ], "keys": [] }, "access_strategy": "Persist" } }, "scalars": [ { "CallBinary": { "func": "AddInt32", "expr1": { "Column": [ 0, "a" ] }, "expr2": { "Column": [ 1, "b" ] } } }, { "Literal": [ { "Ok": { "data": [ 45, 1 ] } }, { "scalar_type": "Int32", "nullable": false } ] } ] } }, "predicates": [ { "CallBinary": { "func": "Lt", "expr1": { "Column": [ 1, "b" ] }, "expr2": { "Literal": [ { "Ok": { "data": [ 44 ] } }, { "scalar_type": "Int32", "nullable": false } ] } } }, { "CallBinary": { "func": "Gt", "expr1": { "Column": [ 0, "a" ] }, "expr2": { "Literal": [ { "Ok": { "data": [ 44 ] } }, { "scalar_type": "Int32", "nullable": false } ] } } }, { "CallBinary": { "func": "Gt", "expr1": { "Column": [ 2, null ] }, "expr2": { "Literal": [ { "Ok": { "data": [ 44 ] } }, { "scalar_type": "Int32", "nullable": false } ] } } } ] } }, "outputs": [ 3, 2 ] } } } ], "sources": [ { "id": { "User": 8 }, "op": { "expressions": [], "predicates": [ [ 1, { "CallBinary": { "func": "Gt", "expr1": { "Column": [ 0, "a" ] }, "expr2": { "Literal": [ { "Ok": { "data": [ 44 ] } }, { "scalar_type": "Int32", "nullable": false } ] } } } ], [ 2, { "CallBinary": { "func": "Lt", "expr1": { "Column": [ 1, "b" ] }, "expr2": { "Literal": [ { "Ok": { "data": [ 44 ] } }, { "scalar_type": "Int32", "nullable": false } ] } } } ], [ 2, { "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 } ] } } } ] ], "projection": [ 0, 1 ], "input_arity": 2 } } ] } EOF # Test table functions in the select clause (FlatMap). query T multiline EXPLAIN OPTIMIZED PLAN WITH(raw) AS JSON FOR SELECT generate_series(a, b) from t ---- { "plans": [ { "id": "Explained Query", "plan": { "Project": { "input": { "FlatMap": { "input": { "Get": { "id": { "Global": { "User": 1 } }, "typ": { "column_types": [ { "scalar_type": "Int32", "nullable": true }, { "scalar_type": "Int32", "nullable": true } ], "keys": [] }, "access_strategy": { "Index": [ [ { "User": 4 }, "FullScan" ] ] } } }, "func": "GenerateSeriesInt32", "exprs": [ { "Column": [ 0, "a" ] }, { "Column": [ 1, "b" ] }, { "Literal": [ { "Ok": { "data": [ 45, 1 ] } }, { "scalar_type": "Int32", "nullable": false } ] } ] } }, "outputs": [ 2 ] } } } ], "sources": [] } EOF # Test Threshold, Union, Distinct, Negate. query T multiline EXPLAIN OPTIMIZED PLAN WITH(raw) AS JSON FOR SELECT a FROM t EXCEPT SELECT b FROM mv ---- { "plans": [ { "id": "Explained Query", "plan": { "Threshold": { "input": { "Union": { "base": { "Reduce": { "input": { "Project": { "input": { "Get": { "id": { "Global": { "User": 1 } }, "typ": { "column_types": [ { "scalar_type": "Int32", "nullable": true }, { "scalar_type": "Int32", "nullable": true } ], "keys": [] }, "access_strategy": { "Index": [ [ { "User": 4 }, "FullScan" ] ] } } }, "outputs": [ 0 ] } }, "group_key": [ { "Column": [ 0, null ] } ], "aggregates": [], "monotonic": false, "expected_group_size": null } }, "inputs": [ { "Negate": { "input": { "Reduce": { "input": { "Project": { "input": { "Get": { "id": { "Global": { "User": 8 } }, "typ": { "column_types": [ { "scalar_type": "Int32", "nullable": false }, { "scalar_type": "Int32", "nullable": true } ], "keys": [] }, "access_strategy": "Persist" } }, "outputs": [ 1 ] } }, "group_key": [ { "Column": [ 0, null ] } ], "aggregates": [], "monotonic": false, "expected_group_size": null } } } } ] } } } } } ], "sources": [ { "id": { "User": 8 }, "op": null } ] } EOF # Test Threshold, Union, Distinct, Negate. query T multiline EXPLAIN OPTIMIZED PLAN WITH(raw) AS JSON FOR SELECT a FROM t EXCEPT ALL SELECT b FROM mv ---- { "plans": [ { "id": "Explained Query", "plan": { "Threshold": { "input": { "Union": { "base": { "Project": { "input": { "Get": { "id": { "Global": { "User": 1 } }, "typ": { "column_types": [ { "scalar_type": "Int32", "nullable": true }, { "scalar_type": "Int32", "nullable": true } ], "keys": [] }, "access_strategy": { "Index": [ [ { "User": 4 }, "FullScan" ] ] } } }, "outputs": [ 0 ] } }, "inputs": [ { "Negate": { "input": { "Project": { "input": { "Get": { "id": { "Global": { "User": 8 } }, "typ": { "column_types": [ { "scalar_type": "Int32", "nullable": false }, { "scalar_type": "Int32", "nullable": true } ], "keys": [] }, "access_strategy": "Persist" } }, "outputs": [ 1 ] } } } } ] } } } } } ], "sources": [ { "id": { "User": 8 }, "op": null } ] } EOF # Test TopK. query T multiline EXPLAIN OPTIMIZED PLAN WITH(raw) AS JSON FOR SELECT * FROM ov ---- { "plans": [ { "id": "Explained Query", "plan": { "TopK": { "input": { "Get": { "id": { "Global": { "User": 1 } }, "typ": { "column_types": [ { "scalar_type": "Int32", "nullable": true }, { "scalar_type": "Int32", "nullable": true } ], "keys": [] }, "access_strategy": { "Index": [ [ { "User": 4 }, "FullScan" ] ] } } }, "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 } ] }, "offset": 0, "monotonic": false, "expected_group_size": null } } } ], "sources": [] } EOF # Test Finish. query T multiline EXPLAIN OPTIMIZED PLAN WITH(raw) AS JSON FOR SELECT * FROM t ORDER BY b asc, a desc LIMIT 5 ---- { "plans": [ { "id": "Explained Query (fast path)", "plan": { "PeekExisting": [ { "User": 1 }, { "User": 4 }, null, { "mfp": { "expressions": [], "predicates": [], "projection": [ 0, 1 ], "input_arity": 2 } } ] } } ], "sources": [] } EOF # Test Reduce (global). query T multiline EXPLAIN OPTIMIZED PLAN WITH(raw) AS JSON FOR SELECT abs(min(a) - max(a)) FROM t ---- { "plans": [ { "id": "Explained Query", "plan": { "Let": { "id": 0, "value": { "Reduce": { "input": { "Project": { "input": { "Get": { "id": { "Global": { "User": 1 } }, "typ": { "column_types": [ { "scalar_type": "Int32", "nullable": true }, { "scalar_type": "Int32", "nullable": true } ], "keys": [] }, "access_strategy": { "Index": [ [ { "User": 4 }, "FullScan" ] ] } } }, "outputs": [ 0 ] } }, "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": { "Project": { "input": { "Map": { "input": { "Union": { "base": { "Get": { "id": { "Local": 0 }, "typ": { "column_types": [ { "scalar_type": "Int32", "nullable": true }, { "scalar_type": "Int32", "nullable": true } ], "keys": [ [] ] }, "access_strategy": "UnknownOrLocal" } }, "inputs": [ { "Map": { "input": { "Union": { "base": { "Negate": { "input": { "Project": { "input": { "Get": { "id": { "Local": 0 }, "typ": { "column_types": [ { "scalar_type": "Int32", "nullable": true }, { "scalar_type": "Int32", "nullable": true } ], "keys": [ [] ] }, "access_strategy": "UnknownOrLocal" } }, "outputs": [] } } } }, "inputs": [ { "Constant": { "rows": { "Ok": [ [ { "data": [] }, 1 ] ] }, "typ": { "column_types": [], "keys": [] } } } ] } }, "scalars": [ { "Literal": [ { "Ok": { "data": [ 0 ] } }, { "scalar_type": "Int32", "nullable": true } ] }, { "Literal": [ { "Ok": { "data": [ 0 ] } }, { "scalar_type": "Int32", "nullable": true } ] } ] } } ] } }, "scalars": [ { "CallUnary": { "func": { "AbsInt32": null }, "expr": { "CallBinary": { "func": "SubInt32", "expr1": { "Column": [ 0, "?column?" ] }, "expr2": { "Column": [ 1, "?column?" ] } } } } } ] } }, "outputs": [ 2 ] } } } } } ], "sources": [] } EOF # Test Reduce (local). query T multiline EXPLAIN OPTIMIZED PLAN WITH(raw) AS JSON FOR SELECT abs(min(a) - max(a)) FROM t GROUP BY b ---- { "plans": [ { "id": "Explained Query", "plan": { "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": [] }, "access_strategy": { "Index": [ [ { "User": 4 }, "FullScan" ] ] } } }, "group_key": [ { "Column": [ 1, null ] } ], "aggregates": [ { "func": "MinInt32", "expr": { "Column": [ 0, "a" ] }, "distinct": false }, { "func": "MaxInt32", "expr": { "Column": [ 0, "a" ] }, "distinct": false } ], "monotonic": false, "expected_group_size": null } }, "scalars": [ { "CallUnary": { "func": { "AbsInt32": null }, "expr": { "CallBinary": { "func": "SubInt32", "expr1": { "Column": [ 1, "?column?" ] }, "expr2": { "Column": [ 2, "?column?" ] } } } } } ] } }, "outputs": [ 3 ] } } } ], "sources": [] } EOF # Test EXISTS subqueries. query T multiline EXPLAIN OPTIMIZED 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) ---- { "plans": [ { "id": "Explained Query", "plan": { "Let": { "id": 0, "value": { "Project": { "input": { "Join": { "inputs": [ { "ArrangeBy": { "input": { "Get": { "id": { "Global": { "User": 1 } }, "typ": { "column_types": [ { "scalar_type": "Int32", "nullable": true }, { "scalar_type": "Int32", "nullable": true } ], "keys": [] }, "access_strategy": { "Index": [ [ { "User": 4 }, "DifferentialJoin" ] ] } } }, "keys": [ [ { "Column": [ 0, "a" ] } ] ] } }, { "ArrangeBy": { "input": { "Reduce": { "input": { "Project": { "input": { "Filter": { "input": { "Join": { "inputs": [ { "ArrangeBy": { "input": { "Reduce": { "input": { "Project": { "input": { "Get": { "id": { "Global": { "User": 1 } }, "typ": { "column_types": [ { "scalar_type": "Int32", "nullable": true }, { "scalar_type": "Int32", "nullable": true } ], "keys": [] }, "access_strategy": { "Index": [ [ { "User": 4 }, "FullScan" ] ] } } }, "outputs": [ 0 ] } }, "group_key": [ { "Column": [ 0, null ] } ], "aggregates": [], "monotonic": false, "expected_group_size": null } }, "keys": [ [] ] } }, { "ArrangeBy": { "input": { "Project": { "input": { "Get": { "id": { "Global": { "User": 8 } }, "typ": { "column_types": [ { "scalar_type": "Int32", "nullable": false }, { "scalar_type": "Int32", "nullable": true } ], "keys": [] }, "access_strategy": "Persist" } }, "outputs": [ 0 ] } }, "keys": [ [] ] } } ], "equivalences": [], "implementation": { "Differential": [ [ 0, [], { "V1": { "unique_key": false, "key_length": 0, "arranged": false, "cardinality": null, "filters": { "literal_equality": false, "like": false, "is_null": false, "literal_inequality": 0, "any_filter": false }, "input": 0 } } ], [ [ 1, [], { "V1": { "unique_key": false, "key_length": 0, "arranged": false, "cardinality": null, "filters": { "literal_equality": false, "like": false, "is_null": false, "literal_inequality": 0, "any_filter": false }, "input": 1 } } ] ] ] } } }, "predicates": [ { "CallBinary": { "func": "Lt", "expr1": { "Column": [ 0, "a" ] }, "expr2": { "Column": [ 1, "a" ] } } } ] } }, "outputs": [ 0 ] } }, "group_key": [ { "Column": [ 0, null ] } ], "aggregates": [], "monotonic": false, "expected_group_size": null } }, "keys": [ [ { "Column": [ 0, null ] } ] ] } } ], "equivalences": [ [ { "Column": [ 0, null ] }, { "Column": [ 2, null ] } ] ], "implementation": { "Differential": [ [ 1, [ { "Column": [ 0, null ] } ], { "V1": { "unique_key": true, "key_length": 1, "arranged": true, "cardinality": null, "filters": { "literal_equality": false, "like": false, "is_null": false, "literal_inequality": 0, "any_filter": false }, "input": 1 } } ], [ [ 0, [ { "Column": [ 0, "a" ] } ], { "V1": { "unique_key": false, "key_length": 1, "arranged": true, "cardinality": null, "filters": { "literal_equality": false, "like": false, "is_null": false, "literal_inequality": 0, "any_filter": false }, "input": 0 } } ] ] ] } } }, "outputs": [ 0, 1 ] } }, "body": { "Project": { "input": { "Join": { "inputs": [ { "ArrangeBy": { "input": { "Get": { "id": { "Local": 0 }, "typ": { "column_types": [ { "scalar_type": "Int32", "nullable": false }, { "scalar_type": "Int32", "nullable": true } ], "keys": [] }, "access_strategy": "UnknownOrLocal" } }, "keys": [ [ { "Column": [ 1, null ] } ] ] } }, { "ArrangeBy": { "input": { "Reduce": { "input": { "Project": { "input": { "Filter": { "input": { "Join": { "inputs": [ { "ArrangeBy": { "input": { "Reduce": { "input": { "Project": { "input": { "Get": { "id": { "Local": 0 }, "typ": { "column_types": [ { "scalar_type": "Int32", "nullable": false }, { "scalar_type": "Int32", "nullable": true } ], "keys": [] }, "access_strategy": "UnknownOrLocal" } }, "outputs": [ 1 ] } }, "group_key": [ { "Column": [ 0, null ] } ], "aggregates": [], "monotonic": false, "expected_group_size": null } }, "keys": [ [] ] } }, { "ArrangeBy": { "input": { "Project": { "input": { "Get": { "id": { "Global": { "User": 8 } }, "typ": { "column_types": [ { "scalar_type": "Int32", "nullable": false }, { "scalar_type": "Int32", "nullable": true } ], "keys": [] }, "access_strategy": "Persist" } }, "outputs": [ 1 ] } }, "keys": [ [] ] } } ], "equivalences": [], "implementation": { "Differential": [ [ 0, [], { "V1": { "unique_key": false, "key_length": 0, "arranged": false, "cardinality": null, "filters": { "literal_equality": false, "like": false, "is_null": false, "literal_inequality": 0, "any_filter": false }, "input": 0 } } ], [ [ 1, [], { "V1": { "unique_key": false, "key_length": 0, "arranged": false, "cardinality": null, "filters": { "literal_equality": false, "like": false, "is_null": false, "literal_inequality": 0, "any_filter": false }, "input": 1 } } ] ] ] } } }, "predicates": [ { "CallBinary": { "func": "Gt", "expr1": { "Column": [ 0, "b" ] }, "expr2": { "Column": [ 1, "b" ] } } } ] } }, "outputs": [ 0 ] } }, "group_key": [ { "Column": [ 0, null ] } ], "aggregates": [], "monotonic": false, "expected_group_size": null } }, "keys": [ [ { "Column": [ 0, null ] } ] ] } } ], "equivalences": [ [ { "Column": [ 1, null ] }, { "Column": [ 2, null ] } ] ], "implementation": { "Differential": [ [ 1, [ { "Column": [ 0, null ] } ], { "V1": { "unique_key": true, "key_length": 1, "arranged": true, "cardinality": null, "filters": { "literal_equality": false, "like": false, "is_null": false, "literal_inequality": 0, "any_filter": false }, "input": 1 } } ], [ [ 0, [ { "Column": [ 1, null ] } ], { "V1": { "unique_key": false, "key_length": 1, "arranged": false, "cardinality": null, "filters": { "literal_equality": false, "like": false, "is_null": false, "literal_inequality": 0, "any_filter": false }, "input": 0 } } ] ] ] } } }, "outputs": [ 0, 1 ] } } } } } ], "sources": [ { "id": { "User": 8 }, "op": null } ] } EOF # Test SELECT subqueries. query T multiline EXPLAIN OPTIMIZED PLAN WITH(raw) AS JSON FOR SELECT (SELECT iv.a FROM iv WHERE iv.b = t.b LIMIT 1), (SELECT mv.a FROM mv WHERE mv.b = t.b LIMIT 1) FROM t ---- { "plans": [ { "id": "Explained Query", "plan": { "Let": { "id": 0, "value": { "Project": { "input": { "Get": { "id": { "Global": { "User": 1 } }, "typ": { "column_types": [ { "scalar_type": "Int32", "nullable": true }, { "scalar_type": "Int32", "nullable": true } ], "keys": [] }, "access_strategy": { "Index": [ [ { "User": 4 }, "FullScan" ] ] } } }, "outputs": [ 1 ] } }, "body": { "Let": { "id": 1, "value": { "Reduce": { "input": { "Get": { "id": { "Local": 0 }, "typ": { "column_types": [ { "scalar_type": "Int32", "nullable": true } ], "keys": [] }, "access_strategy": "UnknownOrLocal" } }, "group_key": [ { "Column": [ 0, null ] } ], "aggregates": [], "monotonic": false, "expected_group_size": null } }, "body": { "Let": { "id": 2, "value": { "ArrangeBy": { "input": { "Get": { "id": { "Local": 1 }, "typ": { "column_types": [ { "scalar_type": "Int32", "nullable": true } ], "keys": [ [ 0 ] ] }, "access_strategy": "UnknownOrLocal" } }, "keys": [ [ { "Column": [ 0, "b" ] } ] ] } }, "body": { "Let": { "id": 3, "value": { "TopK": { "input": { "Project": { "input": { "Join": { "inputs": [ { "Get": { "id": { "Local": 2 }, "typ": { "column_types": [ { "scalar_type": "Int32", "nullable": true } ], "keys": [ [ 0 ] ] }, "access_strategy": "UnknownOrLocal" } }, { "ArrangeBy": { "input": { "Filter": { "input": { "Get": { "id": { "Global": { "User": 6 } }, "typ": { "column_types": [ { "scalar_type": "Int32", "nullable": false }, { "scalar_type": "Int32", "nullable": true } ], "keys": [] }, "access_strategy": { "Index": [ [ { "User": 7 }, "FullScan" ] ] } } }, "predicates": [ { "CallUnary": { "func": { "Not": null }, "expr": { "CallUnary": { "func": { "IsNull": null }, "expr": { "Column": [ 1, "b" ] } } } } } ] } }, "keys": [ [ { "Column": [ 1, "b" ] } ] ] } } ], "equivalences": [ [ { "Column": [ 0, "b" ] }, { "Column": [ 2, "b" ] } ] ], "implementation": { "Differential": [ [ 0, [ { "Column": [ 0, "b" ] } ], { "V1": { "unique_key": true, "key_length": 1, "arranged": true, "cardinality": null, "filters": { "literal_equality": false, "like": false, "is_null": false, "literal_inequality": 0, "any_filter": false }, "input": 0 } } ], [ [ 1, [ { "Column": [ 1, "b" ] } ], { "V1": { "unique_key": false, "key_length": 1, "arranged": false, "cardinality": null, "filters": { "literal_equality": false, "like": false, "is_null": false, "literal_inequality": 0, "any_filter": false }, "input": 1 } } ] ] ] } } }, "outputs": [ 0, 1 ] } }, "group_key": [ 0 ], "order_key": [], "limit": { "Literal": [ { "Ok": { "data": [ 50, 1 ] } }, { "scalar_type": "Int64", "nullable": false } ] }, "offset": 0, "monotonic": false, "expected_group_size": null } }, "body": { "Let": { "id": 4, "value": { "TopK": { "input": { "Project": { "input": { "Join": { "inputs": [ { "Get": { "id": { "Local": 2 }, "typ": { "column_types": [ { "scalar_type": "Int32", "nullable": true } ], "keys": [ [ 0 ] ] }, "access_strategy": "UnknownOrLocal" } }, { "ArrangeBy": { "input": { "Filter": { "input": { "Get": { "id": { "Global": { "User": 8 } }, "typ": { "column_types": [ { "scalar_type": "Int32", "nullable": false }, { "scalar_type": "Int32", "nullable": true } ], "keys": [] }, "access_strategy": "Persist" } }, "predicates": [ { "CallUnary": { "func": { "Not": null }, "expr": { "CallUnary": { "func": { "IsNull": null }, "expr": { "Column": [ 1, "b" ] } } } } } ] } }, "keys": [ [ { "Column": [ 1, "b" ] } ] ] } } ], "equivalences": [ [ { "Column": [ 0, "b" ] }, { "Column": [ 2, "b" ] } ] ], "implementation": { "Differential": [ [ 0, [ { "Column": [ 0, "b" ] } ], { "V1": { "unique_key": true, "key_length": 1, "arranged": true, "cardinality": null, "filters": { "literal_equality": false, "like": false, "is_null": false, "literal_inequality": 0, "any_filter": false }, "input": 0 } } ], [ [ 1, [ { "Column": [ 1, "b" ] } ], { "V1": { "unique_key": false, "key_length": 1, "arranged": false, "cardinality": null, "filters": { "literal_equality": false, "like": false, "is_null": false, "literal_inequality": 0, "any_filter": false }, "input": 1 } } ] ] ] } } }, "outputs": [ 0, 1 ] } }, "group_key": [ 0 ], "order_key": [], "limit": { "Literal": [ { "Ok": { "data": [ 50, 1 ] } }, { "scalar_type": "Int64", "nullable": false } ] }, "offset": 0, "monotonic": false, "expected_group_size": null } }, "body": { "Project": { "input": { "Join": { "inputs": [ { "ArrangeBy": { "input": { "Get": { "id": { "Local": 0 }, "typ": { "column_types": [ { "scalar_type": "Int32", "nullable": true } ], "keys": [] }, "access_strategy": "UnknownOrLocal" } }, "keys": [ [ { "Column": [ 0, null ] } ] ] } }, { "ArrangeBy": { "input": { "Union": { "base": { "Get": { "id": { "Local": 3 }, "typ": { "column_types": [ { "scalar_type": "Int32", "nullable": false }, { "scalar_type": "Int32", "nullable": false } ], "keys": [ [ 0 ] ] }, "access_strategy": "UnknownOrLocal" } }, "inputs": [ { "Map": { "input": { "Union": { "base": { "Negate": { "input": { "Project": { "input": { "Get": { "id": { "Local": 3 }, "typ": { "column_types": [ { "scalar_type": "Int32", "nullable": false }, { "scalar_type": "Int32", "nullable": false } ], "keys": [ [ 0 ] ] }, "access_strategy": "UnknownOrLocal" } }, "outputs": [ 0 ] } } } }, "inputs": [ { "Get": { "id": { "Local": 1 }, "typ": { "column_types": [ { "scalar_type": "Int32", "nullable": true } ], "keys": [ [ 0 ] ] }, "access_strategy": "UnknownOrLocal" } } ] } }, "scalars": [ { "Literal": [ { "Ok": { "data": [ 0 ] } }, { "scalar_type": "Int32", "nullable": true } ] } ] } } ] } }, "keys": [ [ { "Column": [ 0, null ] } ] ] } }, { "ArrangeBy": { "input": { "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": [ { "Map": { "input": { "Union": { "base": { "Negate": { "input": { "Project": { "input": { "Get": { "id": { "Local": 4 }, "typ": { "column_types": [ { "scalar_type": "Int32", "nullable": false }, { "scalar_type": "Int32", "nullable": false } ], "keys": [ [ 0 ] ] }, "access_strategy": "UnknownOrLocal" } }, "outputs": [ 0 ] } } } }, "inputs": [ { "Get": { "id": { "Local": 1 }, "typ": { "column_types": [ { "scalar_type": "Int32", "nullable": true } ], "keys": [ [ 0 ] ] }, "access_strategy": "UnknownOrLocal" } } ] } }, "scalars": [ { "Literal": [ { "Ok": { "data": [ 0 ] } }, { "scalar_type": "Int32", "nullable": true } ] } ] } } ] } }, "keys": [ [ { "Column": [ 0, null ] } ] ] } } ], "equivalences": [ [ { "Column": [ 0, null ] }, { "Column": [ 1, null ] }, { "Column": [ 3, null ] } ] ], "implementation": { "DeltaQuery": [ [ [ 1, [ { "Column": [ 0, null ] } ], { "V1": { "unique_key": true, "key_length": 1, "arranged": false, "cardinality": null, "filters": { "literal_equality": false, "like": false, "is_null": false, "literal_inequality": 0, "any_filter": false }, "input": 1 } } ], [ 2, [ { "Column": [ 0, null ] } ], { "V1": { "unique_key": true, "key_length": 1, "arranged": false, "cardinality": null, "filters": { "literal_equality": false, "like": false, "is_null": false, "literal_inequality": 0, "any_filter": false }, "input": 2 } } ] ], [ [ 2, [ { "Column": [ 0, null ] } ], { "V1": { "unique_key": true, "key_length": 1, "arranged": false, "cardinality": null, "filters": { "literal_equality": false, "like": false, "is_null": false, "literal_inequality": 0, "any_filter": false }, "input": 2 } } ], [ 0, [ { "Column": [ 0, null ] } ], { "V1": { "unique_key": false, "key_length": 1, "arranged": false, "cardinality": null, "filters": { "literal_equality": false, "like": false, "is_null": false, "literal_inequality": 0, "any_filter": false }, "input": 0 } } ] ], [ [ 1, [ { "Column": [ 0, null ] } ], { "V1": { "unique_key": true, "key_length": 1, "arranged": false, "cardinality": null, "filters": { "literal_equality": false, "like": false, "is_null": false, "literal_inequality": 0, "any_filter": false }, "input": 1 } } ], [ 0, [ { "Column": [ 0, null ] } ], { "V1": { "unique_key": false, "key_length": 1, "arranged": false, "cardinality": null, "filters": { "literal_equality": false, "like": false, "is_null": false, "literal_inequality": 0, "any_filter": false }, "input": 0 } } ] ] ] } } }, "outputs": [ 2, 4 ] } } } } } } } } } } } } } ], "sources": [ { "id": { "User": 8 }, "op": { "expressions": [], "predicates": [ [ 2, { "CallUnary": { "func": { "Not": null }, "expr": { "CallUnary": { "func": { "IsNull": null }, "expr": { "Column": [ 1, "b" ] } } } } } ] ], "projection": [ 0, 1 ], "input_arity": 2 } } ] } EOF # Test outer joins (ON syntax). query T multiline EXPLAIN OPTIMIZED 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 ---- { "plans": [ { "id": "Explained Query", "plan": { "Let": { "id": 0, "value": { "Filter": { "input": { "Get": { "id": { "Global": { "User": 1 } }, "typ": { "column_types": [ { "scalar_type": "Int32", "nullable": true }, { "scalar_type": "Int32", "nullable": true } ], "keys": [] }, "access_strategy": { "Index": [ [ { "User": 4 }, "FullScan" ] ] } } }, "predicates": [ { "CallUnary": { "func": { "Not": null }, "expr": { "CallUnary": { "func": { "IsNull": null }, "expr": { "Column": [ 1, "b" ] } } } } } ] } }, "body": { "Let": { "id": 1, "value": { "ArrangeBy": { "input": { "Get": { "id": { "Local": 0 }, "typ": { "column_types": [ { "scalar_type": "Int32", "nullable": true }, { "scalar_type": "Int32", "nullable": false } ], "keys": [] }, "access_strategy": "UnknownOrLocal" } }, "keys": [ [ { "Column": [ 1, "b" ] } ] ] } }, "body": { "Let": { "id": 2, "value": { "ArrangeBy": { "input": { "Project": { "input": { "Get": { "id": { "Local": 0 }, "typ": { "column_types": [ { "scalar_type": "Int32", "nullable": true }, { "scalar_type": "Int32", "nullable": false } ], "keys": [] }, "access_strategy": "UnknownOrLocal" } }, "outputs": [ 1 ] } }, "keys": [ [ { "Column": [ 0, "b" ] } ] ] } }, "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": false } ], "keys": [] }, "access_strategy": "UnknownOrLocal" } }, { "Get": { "id": { "Local": 1 }, "typ": { "column_types": [ { "scalar_type": "Int32", "nullable": true }, { "scalar_type": "Int32", "nullable": false } ], "keys": [] }, "access_strategy": "UnknownOrLocal" } }, { "Get": { "id": { "Local": 2 }, "typ": { "column_types": [ { "scalar_type": "Int32", "nullable": false } ], "keys": [] }, "access_strategy": "UnknownOrLocal" } } ], "equivalences": [ [ { "Column": [ 1, "b" ] }, { "Column": [ 3, "b" ] }, { "Column": [ 4, "b" ] } ] ], "implementation": { "DeltaQuery": [ [ [ 1, [ { "Column": [ 1, "b" ] } ], { "V1": { "unique_key": false, "key_length": 1, "arranged": false, "cardinality": null, "filters": { "literal_equality": false, "like": false, "is_null": false, "literal_inequality": 0, "any_filter": false }, "input": 1 } } ], [ 2, [ { "Column": [ 0, "b" ] } ], { "V1": { "unique_key": false, "key_length": 1, "arranged": false, "cardinality": null, "filters": { "literal_equality": false, "like": false, "is_null": false, "literal_inequality": 0, "any_filter": false }, "input": 2 } } ] ], [ [ 0, [ { "Column": [ 1, "b" ] } ], { "V1": { "unique_key": false, "key_length": 1, "arranged": false, "cardinality": null, "filters": { "literal_equality": false, "like": false, "is_null": false, "literal_inequality": 0, "any_filter": false }, "input": 0 } } ], [ 2, [ { "Column": [ 0, "b" ] } ], { "V1": { "unique_key": false, "key_length": 1, "arranged": false, "cardinality": null, "filters": { "literal_equality": false, "like": false, "is_null": false, "literal_inequality": 0, "any_filter": false }, "input": 2 } } ] ], [ [ 0, [ { "Column": [ 1, "b" ] } ], { "V1": { "unique_key": false, "key_length": 1, "arranged": false, "cardinality": null, "filters": { "literal_equality": false, "like": false, "is_null": false, "literal_inequality": 0, "any_filter": false }, "input": 0 } } ], [ 1, [ { "Column": [ 1, "b" ] } ], { "V1": { "unique_key": false, "key_length": 1, "arranged": false, "cardinality": null, "filters": { "literal_equality": false, "like": false, "is_null": false, "literal_inequality": 0, "any_filter": false }, "input": 1 } } ] ] ] } } }, "outputs": [ 0, 1, 2 ] } }, "body": { "Union": { "base": { "Map": { "input": { "Union": { "base": { "Negate": { "input": { "Project": { "input": { "Join": { "inputs": [ { "Get": { "id": { "Local": 2 }, "typ": { "column_types": [ { "scalar_type": "Int32", "nullable": false } ], "keys": [] }, "access_strategy": "UnknownOrLocal" } }, { "ArrangeBy": { "input": { "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 } ], "keys": [] }, "access_strategy": "UnknownOrLocal" } }, "outputs": [ 1 ] } }, "group_key": [ { "Column": [ 0, null ] } ], "aggregates": [], "monotonic": false, "expected_group_size": null } }, "keys": [ [ { "Column": [ 0, null ] } ] ] } } ], "equivalences": [ [ { "Column": [ 0, "b" ] }, { "Column": [ 1, null ] } ] ], "implementation": { "Differential": [ [ 1, [ { "Column": [ 0, null ] } ], { "V1": { "unique_key": true, "key_length": 1, "arranged": true, "cardinality": null, "filters": { "literal_equality": false, "like": false, "is_null": false, "literal_inequality": 0, "any_filter": false }, "input": 1 } } ], [ [ 0, [ { "Column": [ 0, "b" ] } ], { "V1": { "unique_key": false, "key_length": 1, "arranged": false, "cardinality": null, "filters": { "literal_equality": false, "like": false, "is_null": false, "literal_inequality": 0, "any_filter": false }, "input": 0 } } ] ] ] } } }, "outputs": [] } } } }, "inputs": [ { "Project": { "input": { "Get": { "id": { "Global": { "User": 1 } }, "typ": { "column_types": [ { "scalar_type": "Int32", "nullable": true }, { "scalar_type": "Int32", "nullable": true } ], "keys": [] }, "access_strategy": { "Index": [ [ { "User": 4 }, "FullScan" ] ] } } }, "outputs": [] } } ] } }, "scalars": [ { "Literal": [ { "Ok": { "data": [ 0 ] } }, { "scalar_type": "Int32", "nullable": true } ] }, { "Literal": [ { "Ok": { "data": [ 0 ] } }, { "scalar_type": "Int32", "nullable": true } ] } ] } }, "inputs": [ { "Project": { "input": { "Get": { "id": { "Local": 3 }, "typ": { "column_types": [ { "scalar_type": "Int32", "nullable": true }, { "scalar_type": "Int32", "nullable": false }, { "scalar_type": "Int32", "nullable": true } ], "keys": [] }, "access_strategy": "UnknownOrLocal" } }, "outputs": [ 0, 2 ] } } ] } } } } } } } } } } } ], "sources": [] } 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 OPTIMIZED 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; ---- { "plans": [ { "id": "Explained Query", "plan": { "Let": { "id": 0, "value": { "Project": { "input": { "Get": { "id": { "Global": { "User": 1 } }, "typ": { "column_types": [ { "scalar_type": "Int32", "nullable": true }, { "scalar_type": "Int32", "nullable": true } ], "keys": [] }, "access_strategy": { "Index": [ [ { "User": 4 }, "FullScan" ] ] } } }, "outputs": [ 0 ] } }, "body": { "Let": { "id": 1, "value": { "ArrangeBy": { "input": { "Get": { "id": { "Local": 0 }, "typ": { "column_types": [ { "scalar_type": "Int32", "nullable": true } ], "keys": [] }, "access_strategy": "UnknownOrLocal" } }, "keys": [ [] ] } }, "body": { "Let": { "id": 2, "value": { "Project": { "input": { "Filter": { "input": { "Join": { "inputs": [ { "ArrangeBy": { "input": { "Get": { "id": { "Global": { "User": 1 } }, "typ": { "column_types": [ { "scalar_type": "Int32", "nullable": true }, { "scalar_type": "Int32", "nullable": true } ], "keys": [] }, "access_strategy": { "Index": [ [ { "User": 4 }, "DifferentialJoin" ] ] } } }, "keys": [ [ { "Column": [ 0, "a" ] } ] ] } }, { "ArrangeBy": { "input": { "Reduce": { "input": { "Join": { "inputs": [ { "ArrangeBy": { "input": { "Reduce": { "input": { "Get": { "id": { "Local": 0 }, "typ": { "column_types": [ { "scalar_type": "Int32", "nullable": true } ], "keys": [] }, "access_strategy": "UnknownOrLocal" } }, "group_key": [ { "Column": [ 0, null ] } ], "aggregates": [], "monotonic": false, "expected_group_size": null } }, "keys": [ [] ] } }, { "Get": { "id": { "Local": 1 }, "typ": { "column_types": [ { "scalar_type": "Int32", "nullable": true } ], "keys": [] }, "access_strategy": "UnknownOrLocal" } } ], "equivalences": [], "implementation": { "Differential": [ [ 0, [], { "V1": { "unique_key": false, "key_length": 0, "arranged": false, "cardinality": null, "filters": { "literal_equality": false, "like": false, "is_null": false, "literal_inequality": 0, "any_filter": false }, "input": 0 } } ], [ [ 1, [], { "V1": { "unique_key": false, "key_length": 0, "arranged": false, "cardinality": null, "filters": { "literal_equality": false, "like": false, "is_null": false, "literal_inequality": 0, "any_filter": false }, "input": 1 } } ] ] ] } } }, "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 } }, "keys": [ [ { "Column": [ 0, null ] } ] ] } } ], "equivalences": [ [ { "Column": [ 0, null ] }, { "Column": [ 2, null ] } ] ], "implementation": { "Differential": [ [ 1, [ { "Column": [ 0, null ] } ], { "V1": { "unique_key": true, "key_length": 1, "arranged": true, "cardinality": null, "filters": { "literal_equality": false, "like": false, "is_null": false, "literal_inequality": 0, "any_filter": true }, "input": 1 } } ], [ [ 0, [ { "Column": [ 0, "a" ] } ], { "V1": { "unique_key": false, "key_length": 1, "arranged": true, "cardinality": null, "filters": { "literal_equality": false, "like": false, "is_null": false, "literal_inequality": 0, "any_filter": true }, "input": 0 } } ] ] ] } } }, "predicates": [ { "CallBinary": { "func": "NotEq", "expr1": { "Column": [ 0, null ] }, "expr2": { "Column": [ 3, "m" ] } } } ] } }, "outputs": [ 0, 1, 3 ] } }, "body": { "Project": { "input": { "Filter": { "input": { "Join": { "inputs": [ { "ArrangeBy": { "input": { "Get": { "id": { "Local": 2 }, "typ": { "column_types": [ { "scalar_type": "Int32", "nullable": false }, { "scalar_type": "Int32", "nullable": true }, { "scalar_type": "Int32", "nullable": false } ], "keys": [] }, "access_strategy": "UnknownOrLocal" } }, "keys": [ [ { "Column": [ 0, null ] } ] ] } }, { "ArrangeBy": { "input": { "Reduce": { "input": { "Join": { "inputs": [ { "ArrangeBy": { "input": { "Reduce": { "input": { "Project": { "input": { "Get": { "id": { "Local": 2 }, "typ": { "column_types": [ { "scalar_type": "Int32", "nullable": false }, { "scalar_type": "Int32", "nullable": true }, { "scalar_type": "Int32", "nullable": false } ], "keys": [] }, "access_strategy": "UnknownOrLocal" } }, "outputs": [ 0 ] } }, "group_key": [ { "Column": [ 0, null ] } ], "aggregates": [], "monotonic": false, "expected_group_size": null } }, "keys": [ [] ] } }, { "Get": { "id": { "Local": 1 }, "typ": { "column_types": [ { "scalar_type": "Int32", "nullable": true } ], "keys": [] }, "access_strategy": "UnknownOrLocal" } } ], "equivalences": [], "implementation": { "Differential": [ [ 0, [], { "V1": { "unique_key": false, "key_length": 0, "arranged": false, "cardinality": null, "filters": { "literal_equality": false, "like": false, "is_null": false, "literal_inequality": 0, "any_filter": false }, "input": 0 } } ], [ [ 1, [], { "V1": { "unique_key": false, "key_length": 0, "arranged": false, "cardinality": null, "filters": { "literal_equality": false, "like": false, "is_null": false, "literal_inequality": 0, "any_filter": false }, "input": 1 } } ] ] ] } } }, "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 } }, "keys": [ [ { "Column": [ 0, null ] } ] ] } } ], "equivalences": [ [ { "Column": [ 0, null ] }, { "Column": [ 3, null ] } ] ], "implementation": { "Differential": [ [ 1, [ { "Column": [ 0, null ] } ], { "V1": { "unique_key": true, "key_length": 1, "arranged": true, "cardinality": null, "filters": { "literal_equality": false, "like": false, "is_null": false, "literal_inequality": 0, "any_filter": true }, "input": 1 } } ], [ [ 0, [ { "Column": [ 0, null ] } ], { "V1": { "unique_key": false, "key_length": 1, "arranged": false, "cardinality": null, "filters": { "literal_equality": false, "like": false, "is_null": false, "literal_inequality": 0, "any_filter": true }, "input": 0 } } ] ] ] } } }, "predicates": [ { "CallBinary": { "func": "NotEq", "expr1": { "Column": [ 0, null ] }, "expr2": { "Column": [ 4, "m" ] } } } ] } }, "outputs": [ 0, 1, 2, 4 ] } } } } } } } } } ], "sources": [] } EOF # Test cross join. query T multiline EXPLAIN OPTIMIZED PLAN WITH(raw) AS JSON FOR SELECT t1.a, t2.a FROM t as t1, t as t2 ---- { "plans": [ { "id": "Explained Query", "plan": { "Let": { "id": 0, "value": { "ArrangeBy": { "input": { "Project": { "input": { "Get": { "id": { "Global": { "User": 1 } }, "typ": { "column_types": [ { "scalar_type": "Int32", "nullable": true }, { "scalar_type": "Int32", "nullable": true } ], "keys": [] }, "access_strategy": { "Index": [ [ { "User": 4 }, "FullScan" ] ] } } }, "outputs": [ 0 ] } }, "keys": [ [] ] } }, "body": { "Join": { "inputs": [ { "Get": { "id": { "Local": 0 }, "typ": { "column_types": [ { "scalar_type": "Int32", "nullable": true } ], "keys": [] }, "access_strategy": "UnknownOrLocal" } }, { "Get": { "id": { "Local": 0 }, "typ": { "column_types": [ { "scalar_type": "Int32", "nullable": true } ], "keys": [] }, "access_strategy": "UnknownOrLocal" } } ], "equivalences": [], "implementation": { "Differential": [ [ 0, [], { "V1": { "unique_key": false, "key_length": 0, "arranged": false, "cardinality": null, "filters": { "literal_equality": false, "like": false, "is_null": false, "literal_inequality": 0, "any_filter": false }, "input": 0 } } ], [ [ 1, [], { "V1": { "unique_key": false, "key_length": 0, "arranged": false, "cardinality": null, "filters": { "literal_equality": false, "like": false, "is_null": false, "literal_inequality": 0, "any_filter": false }, "input": 1 } } ] ] ] } } } } } } ], "sources": [] } EOF # Test cyclic join. query T multiline EXPLAIN OPTIMIZED 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 ---- { "plans": [ { "id": "Explained Query", "plan": { "Let": { "id": 0, "value": { "Filter": { "input": { "Get": { "id": { "Global": { "User": 1 } }, "typ": { "column_types": [ { "scalar_type": "Int32", "nullable": true }, { "scalar_type": "Int32", "nullable": true } ], "keys": [] }, "access_strategy": { "Index": [ [ { "User": 4 }, "FullScan" ] ] } } }, "predicates": [ { "CallUnary": { "func": { "Not": null }, "expr": { "CallUnary": { "func": { "IsNull": null }, "expr": { "Column": [ 1, "b" ] } } } } } ] } }, "body": { "Let": { "id": 1, "value": { "ArrangeBy": { "input": { "Get": { "id": { "Local": 0 }, "typ": { "column_types": [ { "scalar_type": "Int32", "nullable": true }, { "scalar_type": "Int32", "nullable": false } ], "keys": [] }, "access_strategy": "UnknownOrLocal" } }, "keys": [ [ { "Column": [ 1, "b" ] } ] ] } }, "body": { "Project": { "input": { "Join": { "inputs": [ { "Get": { "id": { "Local": 1 }, "typ": { "column_types": [ { "scalar_type": "Int32", "nullable": true }, { "scalar_type": "Int32", "nullable": false } ], "keys": [] }, "access_strategy": "UnknownOrLocal" } }, { "Get": { "id": { "Local": 1 }, "typ": { "column_types": [ { "scalar_type": "Int32", "nullable": true }, { "scalar_type": "Int32", "nullable": false } ], "keys": [] }, "access_strategy": "UnknownOrLocal" } }, { "ArrangeBy": { "input": { "Project": { "input": { "Get": { "id": { "Local": 0 }, "typ": { "column_types": [ { "scalar_type": "Int32", "nullable": true }, { "scalar_type": "Int32", "nullable": false } ], "keys": [] }, "access_strategy": "UnknownOrLocal" } }, "outputs": [ 1 ] } }, "keys": [ [ { "Column": [ 0, "b" ] } ] ] } } ], "equivalences": [ [ { "Column": [ 1, "b" ] }, { "Column": [ 3, "b" ] }, { "Column": [ 4, "b" ] } ] ], "implementation": { "DeltaQuery": [ [ [ 1, [ { "Column": [ 1, "b" ] } ], { "V1": { "unique_key": false, "key_length": 1, "arranged": false, "cardinality": null, "filters": { "literal_equality": false, "like": false, "is_null": false, "literal_inequality": 0, "any_filter": false }, "input": 1 } } ], [ 2, [ { "Column": [ 0, "b" ] } ], { "V1": { "unique_key": false, "key_length": 1, "arranged": false, "cardinality": null, "filters": { "literal_equality": false, "like": false, "is_null": false, "literal_inequality": 0, "any_filter": false }, "input": 2 } } ] ], [ [ 0, [ { "Column": [ 1, "b" ] } ], { "V1": { "unique_key": false, "key_length": 1, "arranged": false, "cardinality": null, "filters": { "literal_equality": false, "like": false, "is_null": false, "literal_inequality": 0, "any_filter": false }, "input": 0 } } ], [ 2, [ { "Column": [ 0, "b" ] } ], { "V1": { "unique_key": false, "key_length": 1, "arranged": false, "cardinality": null, "filters": { "literal_equality": false, "like": false, "is_null": false, "literal_inequality": 0, "any_filter": false }, "input": 2 } } ] ], [ [ 0, [ { "Column": [ 1, "b" ] } ], { "V1": { "unique_key": false, "key_length": 1, "arranged": false, "cardinality": null, "filters": { "literal_equality": false, "like": false, "is_null": false, "literal_inequality": 0, "any_filter": false }, "input": 0 } } ], [ 1, [ { "Column": [ 1, "b" ] } ], { "V1": { "unique_key": false, "key_length": 1, "arranged": false, "cardinality": null, "filters": { "literal_equality": false, "like": false, "is_null": false, "literal_inequality": 0, "any_filter": false }, "input": 1 } } ] ] ] } } }, "outputs": [ 0, 2 ] } } } } } } } ], "sources": [] } 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 a differential join. query T multiline EXPLAIN OPTIMIZED PLAN WITH(raw) AS JSON FOR SELECT a, b, c, d, e, f FROM t, u, v WHERE a = c and d = e and b = f ---- { "plans": [ { "id": "Explained Query", "plan": { "Project": { "input": { "Join": { "inputs": [ { "ArrangeBy": { "input": { "Filter": { "input": { "Get": { "id": { "Global": { "User": 1 } }, "typ": { "column_types": [ { "scalar_type": "Int32", "nullable": true }, { "scalar_type": "Int32", "nullable": true } ], "keys": [] }, "access_strategy": { "Index": [ [ { "User": 4 }, "FullScan" ] ] } } }, "predicates": [ { "CallUnary": { "func": { "Not": null }, "expr": { "CallUnary": { "func": { "IsNull": null }, "expr": { "Column": [ 0, "a" ] } } } } }, { "CallUnary": { "func": { "Not": null }, "expr": { "CallUnary": { "func": { "IsNull": null }, "expr": { "Column": [ 1, "b" ] } } } } } ] } }, "keys": [ [ { "Column": [ 0, "a" ] } ], [ { "Column": [ 0, "a" ] }, { "Column": [ 1, "b" ] } ] ] } }, { "ArrangeBy": { "input": { "Get": { "id": { "Global": { "User": 2 } }, "typ": { "column_types": [ { "scalar_type": "Int32", "nullable": true }, { "scalar_type": "Int32", "nullable": true } ], "keys": [] }, "access_strategy": { "Index": [ [ { "User": 9 }, { "DeltaJoin": "Lookup" } ], [ { "User": 10 }, { "DeltaJoin": "Lookup" } ] ] } } }, "keys": [ [ { "Column": [ 0, "c" ] } ], [ { "Column": [ 1, "d" ] } ] ] } }, { "ArrangeBy": { "input": { "Filter": { "input": { "Get": { "id": { "Global": { "User": 3 } }, "typ": { "column_types": [ { "scalar_type": "Int32", "nullable": true }, { "scalar_type": "Int32", "nullable": true } ], "keys": [] }, "access_strategy": { "Index": [ [ { "User": 11 }, "FullScan" ] ] } } }, "predicates": [ { "CallUnary": { "func": { "Not": null }, "expr": { "CallUnary": { "func": { "IsNull": null }, "expr": { "Column": [ 0, "e" ] } } } } }, { "CallUnary": { "func": { "Not": null }, "expr": { "CallUnary": { "func": { "IsNull": null }, "expr": { "Column": [ 1, "f" ] } } } } } ] } }, "keys": [ [ { "Column": [ 0, "e" ] }, { "Column": [ 1, "f" ] } ] ] } } ], "equivalences": [ [ { "Column": [ 0, "a" ] }, { "Column": [ 2, "c" ] } ], [ { "Column": [ 1, "b" ] }, { "Column": [ 5, "f" ] } ], [ { "Column": [ 3, "d" ] }, { "Column": [ 4, "e" ] } ] ], "implementation": { "DeltaQuery": [ [ [ 1, [ { "Column": [ 0, "c" ] } ], { "V1": { "unique_key": false, "key_length": 1, "arranged": true, "cardinality": null, "filters": { "literal_equality": false, "like": false, "is_null": false, "literal_inequality": 0, "any_filter": false }, "input": 1 } } ], [ 2, [ { "Column": [ 0, "e" ] }, { "Column": [ 1, "f" ] } ], { "V1": { "unique_key": false, "key_length": 2, "arranged": false, "cardinality": null, "filters": { "literal_equality": false, "like": false, "is_null": false, "literal_inequality": 0, "any_filter": false }, "input": 2 } } ] ], [ [ 0, [ { "Column": [ 0, "a" ] } ], { "V1": { "unique_key": false, "key_length": 1, "arranged": true, "cardinality": null, "filters": { "literal_equality": false, "like": false, "is_null": false, "literal_inequality": 0, "any_filter": false }, "input": 0 } } ], [ 2, [ { "Column": [ 0, "e" ] }, { "Column": [ 1, "f" ] } ], { "V1": { "unique_key": false, "key_length": 2, "arranged": false, "cardinality": null, "filters": { "literal_equality": false, "like": false, "is_null": false, "literal_inequality": 0, "any_filter": false }, "input": 2 } } ] ], [ [ 1, [ { "Column": [ 1, "d" ] } ], { "V1": { "unique_key": false, "key_length": 1, "arranged": true, "cardinality": null, "filters": { "literal_equality": false, "like": false, "is_null": false, "literal_inequality": 0, "any_filter": false }, "input": 1 } } ], [ 0, [ { "Column": [ 0, "a" ] }, { "Column": [ 1, "b" ] } ], { "V1": { "unique_key": false, "key_length": 2, "arranged": false, "cardinality": null, "filters": { "literal_equality": false, "like": false, "is_null": false, "literal_inequality": 0, "any_filter": false }, "input": 0 } } ] ] ] } } }, "outputs": [ 0, 1, 0, 3, 3, 1 ] } } } ], "sources": [] } EOF # Create indexes required for delta join tests statement ok CREATE INDEX t_b_idx ON T(b); # Test a delta join WITH. query T multiline EXPLAIN OPTIMIZED PLAN WITH(raw) AS JSON FOR SELECT a, b, c, d, e, f FROM t, u, v WHERE b = c and d = e ---- { "plans": [ { "id": "Explained Query", "plan": { "Project": { "input": { "Filter": { "input": { "Join": { "inputs": [ { "ArrangeBy": { "input": { "Get": { "id": { "Global": { "User": 1 } }, "typ": { "column_types": [ { "scalar_type": "Int32", "nullable": true }, { "scalar_type": "Int32", "nullable": true } ], "keys": [] }, "access_strategy": { "Index": [ [ { "User": 12 }, { "DeltaJoin": "FirstInputFullScan" } ] ] } } }, "keys": [ [ { "Column": [ 1, "b" ] } ] ] } }, { "ArrangeBy": { "input": { "Get": { "id": { "Global": { "User": 2 } }, "typ": { "column_types": [ { "scalar_type": "Int32", "nullable": true }, { "scalar_type": "Int32", "nullable": true } ], "keys": [] }, "access_strategy": { "Index": [ [ { "User": 9 }, { "DeltaJoin": "Lookup" } ], [ { "User": 10 }, { "DeltaJoin": "Lookup" } ] ] } } }, "keys": [ [ { "Column": [ 0, "c" ] } ], [ { "Column": [ 1, "d" ] } ] ] } }, { "ArrangeBy": { "input": { "Get": { "id": { "Global": { "User": 3 } }, "typ": { "column_types": [ { "scalar_type": "Int32", "nullable": true }, { "scalar_type": "Int32", "nullable": true } ], "keys": [] }, "access_strategy": { "Index": [ [ { "User": 11 }, { "DeltaJoin": "Lookup" } ] ] } } }, "keys": [ [ { "Column": [ 0, "e" ] } ] ] } } ], "equivalences": [ [ { "Column": [ 1, "b" ] }, { "Column": [ 2, "c" ] } ], [ { "Column": [ 3, "d" ] }, { "Column": [ 4, "e" ] } ] ], "implementation": { "DeltaQuery": [ [ [ 1, [ { "Column": [ 0, "c" ] } ], { "V1": { "unique_key": false, "key_length": 1, "arranged": true, "cardinality": null, "filters": { "literal_equality": false, "like": false, "is_null": false, "literal_inequality": 0, "any_filter": false }, "input": 1 } } ], [ 2, [ { "Column": [ 0, "e" ] } ], { "V1": { "unique_key": false, "key_length": 1, "arranged": true, "cardinality": null, "filters": { "literal_equality": false, "like": false, "is_null": false, "literal_inequality": 0, "any_filter": false }, "input": 2 } } ] ], [ [ 0, [ { "Column": [ 1, "b" ] } ], { "V1": { "unique_key": false, "key_length": 1, "arranged": true, "cardinality": null, "filters": { "literal_equality": false, "like": false, "is_null": false, "literal_inequality": 0, "any_filter": false }, "input": 0 } } ], [ 2, [ { "Column": [ 0, "e" ] } ], { "V1": { "unique_key": false, "key_length": 1, "arranged": true, "cardinality": null, "filters": { "literal_equality": false, "like": false, "is_null": false, "literal_inequality": 0, "any_filter": false }, "input": 2 } } ] ], [ [ 1, [ { "Column": [ 1, "d" ] } ], { "V1": { "unique_key": false, "key_length": 1, "arranged": true, "cardinality": null, "filters": { "literal_equality": false, "like": false, "is_null": false, "literal_inequality": 0, "any_filter": false }, "input": 1 } } ], [ 0, [ { "Column": [ 1, "b" ] } ], { "V1": { "unique_key": false, "key_length": 1, "arranged": true, "cardinality": null, "filters": { "literal_equality": false, "like": false, "is_null": false, "literal_inequality": 0, "any_filter": false }, "input": 0 } } ] ] ] } } }, "predicates": [ { "CallUnary": { "func": { "Not": null }, "expr": { "CallUnary": { "func": { "IsNull": null }, "expr": { "Column": [ 1, "b" ] } } } } }, { "CallUnary": { "func": { "Not": null }, "expr": { "CallUnary": { "func": { "IsNull": null }, "expr": { "Column": [ 3, "d" ] } } } } } ] } }, "outputs": [ 0, 1, 1, 3, 3, 5 ] } } } ], "sources": [] } EOF statement ok CREATE TABLE t2(s string, like_pat string, regex_pat string); statement ok INSERT INTO t2 VALUES ('abc', 'a%', 'a.*'), ('ABC', 'a%', 'a.*'), ('ccc', 'a%', 'a.*'); # Our Regex wrapper struct's serde is currently not serializing the Regex itself, but all info should be visible in # the other fields. query T multiline EXPLAIN OPTIMIZED PLAN AS JSON FOR SELECT s FROM t2 WHERE s ~ 'a.*'; ---- { "plans": [ { "id": "Explained Query", "plan": { "Project": { "input": { "Filter": { "input": { "Get": { "id": { "Global": { "User": 13 } }, "typ": { "column_types": [ { "scalar_type": "String", "nullable": true }, { "scalar_type": "String", "nullable": true }, { "scalar_type": "String", "nullable": true } ], "keys": [] }, "access_strategy": "Persist" } }, "predicates": [ { "CallUnary": { "func": { "IsRegexpMatch": { "pattern": "a.*", "case_insensitive": false, "dot_matches_new_line": true } }, "expr": { "Column": [ 0, "s" ] } } } ] } }, "outputs": [ 0 ] } } } ], "sources": [ { "id": { "User": 13 }, "op": { "expressions": [], "predicates": [ [ 1, { "CallUnary": { "func": { "IsRegexpMatch": { "pattern": "a.*", "case_insensitive": false, "dot_matches_new_line": true } }, "expr": { "Column": [ 0, "s" ] } } } ] ], "projection": [ 0, 1, 2 ], "input_arity": 3 } } ] } EOF