project

exec-ddl
CREATE TABLE a (x INT PRIMARY KEY, y INT, f FLOAT, s STRING)
----

exec-ddl
CREATE TABLE b (x INT PRIMARY KEY, z INT, j JSON)
----

exec-ddl
CREATE TABLE xy (x INT, y INT, PRIMARY KEY (x, y))
----

exec-ddl
CREATE TABLE fks
(
    k INT PRIMARY KEY,
    v INT,
    v2 INT,
    r1 INT NOT NULL REFERENCES a (x),
    r2 INT REFERENCES a (x),
    UNIQUE INDEX (v, v2)
)
----

exec-ddl
CREATE TABLE assn_cast (
    c CHAR,
    qc "char",
    i INT,
    s STRING
)
----

exec-ddl
CREATE TABLE rls (x INT PRIMARY KEY, y INT, alice_has_access BOOL);
----

exec-ddl
CREATE INDEX ON rls(y);
----

exec-ddl
ALTER TABLE rls ENABLE ROW LEVEL SECURITY;
----

exec-ddl
CREATE POLICY select_policy_alice
ON rls
FOR SELECT
TO alice
USING (alice_has_access);
----

exec-ddl
CREATE ROLE alice;
----

# --------------------------------------------------
# EliminateJoinUnderProjectLeft
# --------------------------------------------------

# LeftJoin case with equality of primary keys.
norm expect=EliminateJoinUnderProjectLeft
SELECT b.x, b.z FROM b LEFT JOIN a ON b.x = a.x
----
scan b
 ├── columns: x:1!null z:2
 ├── key: (1)
 └── fd: (1)-->(2)

# LeftJoin case with not-null foreign key.
norm expect=EliminateJoinUnderProjectLeft
SELECT k, v FROM fks LEFT JOIN a ON r1 = x
----
scan fks
 ├── columns: k:1!null v:2
 ├── key: (1)
 └── fd: (1)-->(2)

# LeftJoin case with nullable foreign key.
norm expect=EliminateJoinUnderProjectLeft
SELECT k, v FROM fks LEFT JOIN a ON r2 = x
----
scan fks
 ├── columns: k:1!null v:2
 ├── key: (1)
 └── fd: (1)-->(2)

# InnerJoin case with not-null foreign key (nullable is no-op below).
norm expect=EliminateJoinUnderProjectLeft
SELECT k, v FROM fks INNER JOIN a ON r1 = x
----
scan fks
 ├── columns: k:1!null v:2
 ├── key: (1)
 └── fd: (1)-->(2)

# InnerJoin case with self join.
norm expect=EliminateJoinUnderProjectLeft
SELECT b.x, b.z FROM b INNER JOIN b AS b1 ON b.x = b1.x
----
scan b
 ├── columns: x:1!null z:2
 ├── key: (1)
 └── fd: (1)-->(2)

# Case with equality between multicolumn keys.
norm expect=EliminateJoinUnderProjectLeft
SELECT k, v FROM fks LEFT JOIN xy ON v2 = x AND v = y
----
scan fks
 ├── columns: k:1!null v:2
 ├── key: (1)
 └── fd: (1)-->(2)

# Case with no passthrough columns.
norm expect=EliminateJoinUnderProjectLeft
SELECT 1+b.x FROM b LEFT JOIN a ON b.x = a.x
----
project
 ├── columns: "?column?":12!null
 ├── immutable
 ├── scan b
 │    ├── columns: b.x:1!null
 │    └── key: (1)
 └── projections
      └── b.x:1 + 1 [as="?column?":12, outer=(1), immutable]

# Case with no references to the left side.
norm expect=EliminateJoinUnderProjectLeft
SELECT 1 FROM b LEFT JOIN a ON b.x = a.x
----
project
 ├── columns: "?column?":12!null
 ├── fd: ()-->(12)
 ├── scan b
 └── projections
      └── 1 [as="?column?":12]

# The right column can be remapped to a left column.
norm expect=EliminateJoinUnderProjectLeft
SELECT b.j, b1.x FROM b INNER JOIN b AS b1 ON b.x = b1.x
----
project
 ├── columns: j:3 x:6!null
 ├── key: (6)
 ├── fd: (6)-->(3)
 ├── scan b
 │    ├── columns: b.x:1!null b.j:3
 │    ├── key: (1)
 │    └── fd: (1)-->(3)
 └── projections
      └── b.x:1 [as=b1.x:6, outer=(1)]

# No-op case because the cross join may duplicate left rows.
norm expect-not=EliminateJoinUnderProjectLeft
SELECT 1 FROM b LEFT JOIN a ON True
----
project
 ├── columns: "?column?":12!null
 ├── fd: ()-->(12)
 ├── left-join (cross)
 │    ├── scan b
 │    ├── scan a
 │    └── filters (true)
 └── projections
      └── 1 [as="?column?":12]

# No-op case with a projection that references the right input.
norm expect-not=EliminateJoinUnderProjectLeft
SELECT b.x, b.z, 1+a.x FROM b LEFT JOIN a ON b.x = a.x
----
project
 ├── columns: x:1!null z:2 "?column?":12
 ├── immutable
 ├── key: (1)
 ├── fd: (1)-->(2,12)
 ├── left-join (hash)
 │    ├── columns: b.x:1!null z:2 a.x:6
 │    ├── multiplicity: left-rows(exactly-one), right-rows(zero-or-one)
 │    ├── key: (1)
 │    ├── fd: (1)-->(2,6)
 │    ├── scan b
 │    │    ├── columns: b.x:1!null z:2
 │    │    ├── key: (1)
 │    │    └── fd: (1)-->(2)
 │    ├── scan a
 │    │    ├── columns: a.x:6!null
 │    │    └── key: (6)
 │    └── filters
 │         └── b.x:1 = a.x:6 [outer=(1,6), constraints=(/1: (/NULL - ]; /6: (/NULL - ]), fd=(1)==(6), (6)==(1)]
 └── projections
      └── a.x:6 + 1 [as="?column?":12, outer=(6), immutable]

# No-op case because r2 is nullable, and therefore rows may not match despite
# the fact that it is a foreign key.
norm expect-not=EliminateJoinUnderProjectLeft
SELECT k, v FROM fks INNER JOIN a ON r2 = x
----
project
 ├── columns: k:1!null v:2
 ├── key: (1)
 ├── fd: (1)-->(2)
 └── inner-join (hash)
      ├── columns: k:1!null v:2 r2:5!null x:8!null
      ├── multiplicity: left-rows(zero-or-one), right-rows(zero-or-more)
      ├── key: (1)
      ├── fd: (1)-->(2,5), (5)==(8), (8)==(5)
      ├── scan fks
      │    ├── columns: k:1!null v:2 r2:5
      │    ├── key: (1)
      │    └── fd: (1)-->(2,5)
      ├── scan a
      │    ├── columns: x:8!null
      │    └── key: (8)
      └── filters
           └── r2:5 = x:8 [outer=(5,8), constraints=(/5: (/NULL - ]; /8: (/NULL - ]), fd=(5)==(8), (8)==(5)]

# No-op case because r1 is not unique.
# InnerJoin case with not-null foreign key.
norm expect-not=EliminateJoinUnderProjectLeft
SELECT x, y FROM a INNER JOIN fks ON x = r1
----
project
 ├── columns: x:1!null y:2
 ├── fd: (1)-->(2)
 └── inner-join (hash)
      ├── columns: x:1!null y:2 r1:10!null
      ├── multiplicity: left-rows(zero-or-more), right-rows(exactly-one)
      ├── fd: (1)-->(2), (1)==(10), (10)==(1)
      ├── scan a
      │    ├── columns: x:1!null y:2
      │    ├── key: (1)
      │    └── fd: (1)-->(2)
      ├── scan fks
      │    └── columns: r1:10!null
      └── filters
           └── x:1 = r1:10 [outer=(1,10), constraints=(/1: (/NULL - ]; /10: (/NULL - ]), fd=(1)==(10), (10)==(1)]

# No-op case because r1 is not unique.
# LeftJoin case with not-null foreign key.
norm expect-not=EliminateJoinUnderProjectLeft
SELECT x, y FROM a LEFT JOIN fks ON x = r1
----
project
 ├── columns: x:1!null y:2
 ├── fd: (1)-->(2)
 └── left-join (hash)
      ├── columns: x:1!null y:2 r1:10
      ├── multiplicity: left-rows(one-or-more), right-rows(exactly-one)
      ├── fd: (1)-->(2)
      ├── scan a
      │    ├── columns: x:1!null y:2
      │    ├── key: (1)
      │    └── fd: (1)-->(2)
      ├── scan fks
      │    └── columns: r1:10!null
      └── filters
           └── x:1 = r1:10 [outer=(1,10), constraints=(/1: (/NULL - ]; /10: (/NULL - ]), fd=(1)==(10), (10)==(1)]

# Regression test for #102614 - parent.id should be mapped to child.id to allow
# join elimination to proceed.
exec-ddl
CREATE TABLE parent (
  id INT PRIMARY KEY
)
----

exec-ddl
CREATE TABLE child (
  id INT PRIMARY KEY,
  parent_id INT NOT NULL REFERENCES parent(id)
)
----

norm expect=EliminateJoinUnderProjectLeft
SELECT
  id,
  (SELECT child.parent_id FROM parent WHERE id = child.parent_id)
FROM child
----
project
 ├── columns: id:1!null parent_id:9!null
 ├── key: (1)
 ├── fd: (1)-->(9)
 ├── scan child
 │    ├── columns: child.id:1!null child.parent_id:2!null
 │    ├── key: (1)
 │    └── fd: (1)-->(2)
 └── projections
      └── child.parent_id:2 [as=parent_id:9, outer=(2)]

exec-ddl
DROP TABLE child
----

exec-ddl
DROP TABLE parent
----

# The join can be eliminated as long as it doesn't require remapping with
# OptimizerUseImprovedJoinElimination disabled.
norm set=optimizer_use_improved_join_elimination=false expect=EliminateJoinUnderProjectLeft
SELECT b.x, b.z FROM b LEFT JOIN a ON b.x = a.x
----
scan b
 ├── columns: x:1!null z:2
 ├── key: (1)
 └── fd: (1)-->(2)

# The join cannot be eliminated with OptimizerUseImprovedJoinElimination
# disabled.
norm set=optimizer_use_improved_join_elimination=false expect-not=EliminateJoinUnderProjectLeft
SELECT b.j, b1.x FROM b INNER JOIN b AS b1 ON b.x = b1.x
----
project
 ├── columns: j:3 x:6!null
 ├── key: (6)
 ├── fd: (6)-->(3)
 └── inner-join (hash)
      ├── columns: b.x:1!null b.j:3 b1.x:6!null
      ├── multiplicity: left-rows(exactly-one), right-rows(exactly-one)
      ├── key: (6)
      ├── fd: (1)-->(3), (1)==(6), (6)==(1)
      ├── scan b
      │    ├── columns: b.x:1!null b.j:3
      │    ├── key: (1)
      │    └── fd: (1)-->(3)
      ├── scan b [as=b1]
      │    ├── columns: b1.x:6!null
      │    └── key: (6)
      └── filters
           └── b.x:1 = b1.x:6 [outer=(1,6), constraints=(/1: (/NULL - ]; /6: (/NULL - ]), fd=(1)==(6), (6)==(1)]

# --------------------------------------------------
# EliminateJoinUnderProjectRight
# --------------------------------------------------

# No self-join case because EliminateJoinUnderProjectLeft can always match.
# InnerJoin case with not-null foreign key.
norm expect=EliminateJoinUnderProjectRight
SELECT k, v FROM a INNER JOIN fks ON r1 = x
----
scan fks
 ├── columns: k:7!null v:8
 ├── key: (7)
 └── fd: (7)-->(8)

# The left column can be remapped to a right column.
norm expect=EliminateJoinUnderProjectRight
SELECT x, k, v FROM a INNER JOIN fks ON r1 = x
----
project
 ├── columns: x:1!null k:7!null v:8
 ├── key: (7)
 ├── fd: (7)-->(1,8)
 ├── scan fks
 │    ├── columns: k:7!null v:8 r1:10!null
 │    ├── key: (7)
 │    └── fd: (7)-->(8,10)
 └── projections
      └── r1:10 [as=x:1, outer=(10)]

# No-op case because the left input of a LeftJoin cannot be removed.
norm expect-not=EliminateJoinUnderProjectRight
SELECT x, k, v FROM a LEFT JOIN fks ON r1 = x
----
project
 ├── columns: x:1!null k:7 v:8
 ├── key: (1,7)
 ├── fd: (7)-->(8)
 └── left-join (hash)
      ├── columns: x:1!null k:7 v:8 r1:10
      ├── multiplicity: left-rows(one-or-more), right-rows(exactly-one)
      ├── key: (1,7)
      ├── fd: (7)-->(8,10)
      ├── scan a
      │    ├── columns: x:1!null
      │    └── key: (1)
      ├── scan fks
      │    ├── columns: k:7!null v:8 r1:10!null
      │    ├── key: (7)
      │    └── fd: (7)-->(8,10)
      └── filters
           └── r1:10 = x:1 [outer=(1,10), constraints=(/1: (/NULL - ]; /10: (/NULL - ]), fd=(1)==(10), (10)==(1)]

# Regression test for #105687 - don't attempt to remap columns that are
# synthesized by a subquery.
norm expect=EliminateJoinUnderProjectRight
SELECT (
  SELECT NULL
  FROM (VALUES (0)) AS t2 (c)
  JOIN (VALUES (0), (0)) AS t1 (c) ON t2.c = t1.c
)
FROM (VALUES (1)), (VALUES (0), (1));
----
project
 ├── columns: "?column?":6
 ├── cardinality: [2 - 2]
 ├── values
 │    ├── cardinality: [2 - 2]
 │    ├── ()
 │    └── ()
 └── projections
      └── subquery [as="?column?":6, subquery]
           └── max1-row
                ├── columns: "?column?":5
                ├── error: "more than one row returned by a subquery used as an expression"
                ├── cardinality: [0 - 1]
                ├── key: ()
                ├── fd: ()-->(5)
                └── project
                     ├── columns: "?column?":5
                     ├── cardinality: [0 - 2]
                     ├── fd: ()-->(5)
                     ├── select
                     │    ├── columns: column1:4!null
                     │    ├── cardinality: [0 - 2]
                     │    ├── fd: ()-->(4)
                     │    ├── values
                     │    │    ├── columns: column1:4!null
                     │    │    ├── cardinality: [2 - 2]
                     │    │    ├── (0,)
                     │    │    └── (0,)
                     │    └── filters
                     │         └── column1:4 = 0 [outer=(4), constraints=(/4: [/0 - /0]; tight), fd=()-->(4)]
                     └── projections
                          └── NULL [as="?column?":5]

# Regression test for #107850 - columns should not be mapped if their types are
# not identical.
exec-ddl
CREATE TABLE parent (
  id DECIMAL(10, 2) PRIMARY KEY
)
----

exec-ddl
CREATE TABLE child (
  id INT PRIMARY KEY,
  parent_id DECIMAL(10, 0) NOT NULL REFERENCES parent(id),
  parent_id2 DECIMAL(10, 2) NOT NULL REFERENCES parent(id)
)
----

norm expect-not=EliminateJoinUnderProjectRight
SELECT parent.id FROM parent JOIN child ON parent.id = child.parent_id
----
project
 ├── columns: id:1!null
 ├── immutable
 └── inner-join (hash)
      ├── columns: parent.id:1!null parent_id:5!null
      ├── multiplicity: left-rows(zero-or-more), right-rows(exactly-one)
      ├── immutable
      ├── fd: (1)==(5), (5)==(1)
      ├── scan parent
      │    ├── columns: parent.id:1!null
      │    └── key: (1)
      ├── scan child
      │    └── columns: parent_id:5!null
      └── filters
           └── parent.id:1 = parent_id:5 [outer=(1,5), immutable, constraints=(/1: (/NULL - ]; /5: (/NULL - ]), fd=(1)==(5), (5)==(1)]

# The projected column with the identical type, child.parent_id2, should be
# remapped if there are multiple equivalent columns.
norm
SELECT parent.id FROM parent JOIN child ON parent.id = child.parent_id AND child.parent_id = child.parent_id2
----
project
 ├── columns: id:1!null
 ├── immutable
 ├── select
 │    ├── columns: parent_id:5!null parent_id2:6!null
 │    ├── immutable
 │    ├── fd: (5)==(6), (6)==(5)
 │    ├── scan child
 │    │    └── columns: parent_id:5!null parent_id2:6!null
 │    └── filters
 │         └── parent_id:5 = parent_id2:6 [outer=(5,6), immutable, constraints=(/5: (/NULL - ]; /6: (/NULL - ]), fd=(5)==(6), (6)==(5)]
 └── projections
      └── parent_id2:6 [as=parent.id:1, outer=(6)]

# In a projection expression the column with the identical type,
# child.parent_id2, should be remapped if there are multiple equivalent columns.
norm
SELECT 1 + parent.id FROM parent JOIN child ON parent.id = child.parent_id AND child.parent_id = child.parent_id2
----
project
 ├── columns: "?column?":9!null
 ├── immutable
 ├── select
 │    ├── columns: parent_id:5!null parent_id2:6!null
 │    ├── immutable
 │    ├── fd: (5)==(6), (6)==(5)
 │    ├── scan child
 │    │    └── columns: parent_id:5!null parent_id2:6!null
 │    └── filters
 │         └── parent_id:5 = parent_id2:6 [outer=(5,6), immutable, constraints=(/5: (/NULL - ]; /6: (/NULL - ]), fd=(5)==(6), (6)==(5)]
 └── projections
      └── parent_id2:6 + 1 [as="?column?":9, outer=(6), immutable]

exec-ddl
DROP TABLE child
----

exec-ddl
DROP TABLE parent
----

# --------------------------------------------------
# EliminateProject
# --------------------------------------------------

# Same order, same names.
norm expect=EliminateProject
SELECT x, y FROM a
----
scan a
 ├── columns: x:1!null y:2
 ├── key: (1)
 └── fd: (1)-->(2)

# Different order, aliased names.
norm expect=EliminateProject
SELECT a.y AS aliasy, a.x FROM a
----
scan a
 ├── columns: aliasy:2 x:1!null
 ├── key: (1)
 └── fd: (1)-->(2)

# Reordered, duplicate, aliased columns.
norm expect=EliminateProject
SELECT a.y AS alias1, a.x, a.y AS alias1, a.x FROM a
----
scan a
 ├── columns: alias1:2 x:1!null alias1:2 x:1!null
 ├── key: (1)
 └── fd: (1)-->(2)

# Added column (projection should not be eliminated).
norm expect-not=EliminateProject
SELECT *, 1 r FROM a
----
project
 ├── columns: x:1!null y:2 f:3 s:4 r:7!null
 ├── key: (1)
 ├── fd: ()-->(7), (1)-->(2-4)
 ├── scan a
 │    ├── columns: x:1!null y:2 f:3 s:4
 │    ├── key: (1)
 │    └── fd: (1)-->(2-4)
 └── projections
      └── 1 [as=r:7]

# --------------------------------------------------
# MergeProjects
# --------------------------------------------------

# Inner project has no synthesized columns.
norm expect=MergeProjects
SELECT y+1 AS r FROM (SELECT a.y FROM a, b WHERE a.x=b.x) a
----
project
 ├── columns: r:12
 ├── immutable
 ├── inner-join (hash)
 │    ├── columns: a.x:1!null y:2 b.x:7!null
 │    ├── multiplicity: left-rows(zero-or-one), right-rows(zero-or-one)
 │    ├── key: (7)
 │    ├── fd: (1)-->(2), (1)==(7), (7)==(1)
 │    ├── scan a
 │    │    ├── columns: a.x:1!null y:2
 │    │    ├── key: (1)
 │    │    └── fd: (1)-->(2)
 │    ├── scan b
 │    │    ├── columns: b.x:7!null
 │    │    └── key: (7)
 │    └── filters
 │         └── a.x:1 = b.x:7 [outer=(1,7), constraints=(/1: (/NULL - ]; /7: (/NULL - ]), fd=(1)==(7), (7)==(1)]
 └── projections
      └── y:2 + 1 [as=r:12, outer=(2), immutable]

# Outer and inner projections have synthesized columns.
norm expect=MergeProjects
SELECT y1, f+1 FROM (SELECT y+1 AS y1, f FROM a)
----
project
 ├── columns: y1:7 "?column?":8
 ├── immutable
 ├── scan a
 │    └── columns: y:2 f:3
 └── projections
      ├── f:3 + 1.0 [as="?column?":8, outer=(3), immutable]
      └── y:2 + 1 [as=y1:7, outer=(2), immutable]

# Multiple synthesized columns in both outer and inner projections.
norm expect=MergeProjects
SELECT y1, f+1, x2, s||'foo' FROM (SELECT y+1 AS y1, f, s, x*2 AS x2 FROM a)
----
project
 ├── columns: y1:7 "?column?":9 x2:8!null "?column?":10
 ├── immutable
 ├── scan a
 │    ├── columns: x:1!null y:2 f:3 s:4
 │    ├── key: (1)
 │    └── fd: (1)-->(2-4)
 └── projections
      ├── f:3 + 1.0 [as="?column?":9, outer=(3), immutable]
      ├── s:4 || 'foo' [as="?column?":10, outer=(4), immutable]
      ├── y:2 + 1 [as=y1:7, outer=(2), immutable]
      └── x:1 * 2 [as=x2:8, outer=(1), immutable]

# Outer project selects subset of inner columns.
norm expect=MergeProjects
SELECT y1 FROM (SELECT y+1 AS y1, f*2 AS f2 FROM a)
----
project
 ├── columns: y1:7
 ├── immutable
 ├── scan a
 │    └── columns: y:2
 └── projections
      └── y:2 + 1 [as=y1:7, outer=(2), immutable]

# Don't merge, since outer depends on inner.
norm expect-not=MergeProjects
SELECT y1*2, y1/2 FROM (SELECT y+1 AS y1 FROM a)
----
project
 ├── columns: "?column?":8 "?column?":9
 ├── immutable
 ├── project
 │    ├── columns: y1:7
 │    ├── immutable
 │    ├── scan a
 │    │    └── columns: y:2
 │    └── projections
 │         └── y:2 + 1 [as=y1:7, outer=(2), immutable]
 └── projections
      ├── y1:7 * 2 [as="?column?":8, outer=(7), immutable]
      └── y1:7 / 2 [as="?column?":9, outer=(7)]

# Discard all inner columns.
norm expect=MergeProjects
SELECT 1 r FROM (SELECT y+1, x FROM a) a
----
project
 ├── columns: r:8!null
 ├── fd: ()-->(8)
 ├── scan a
 └── projections
      └── 1 [as=r:8]

# --------------------------------------------------
# MergeProjectWithValues
# --------------------------------------------------

norm expect=MergeProjectWithValues
SELECT column1, 3 FROM (VALUES (1, 2))
----
values
 ├── columns: column1:1!null "?column?":3!null
 ├── cardinality: [1 - 1]
 ├── key: ()
 ├── fd: ()-->(1,3)
 └── (1, 3)

# Only passthrough columns.
norm expect=MergeProjectWithValues
SELECT column1, column3 FROM (VALUES (1, 2, 3))
----
values
 ├── columns: column1:1!null column3:3!null
 ├── cardinality: [1 - 1]
 ├── key: ()
 ├── fd: ()-->(1,3)
 └── (1, 3)

# Only synthesized columns.
norm expect=MergeProjectWithValues
SELECT 4, 5 FROM (VALUES (1, 2, 3))
----
values
 ├── columns: "?column?":4!null "?column?":5!null
 ├── cardinality: [1 - 1]
 ├── key: ()
 ├── fd: ()-->(4,5)
 └── (4, 5)

# Don't trigger rule when there is more than one Values row.
norm expect-not=MergeProjectWithValues
SELECT column1, 3 FROM (VALUES (1, 2), (1, 4))
----
project
 ├── columns: column1:1!null "?column?":3!null
 ├── cardinality: [2 - 2]
 ├── fd: ()-->(3)
 ├── values
 │    ├── columns: column1:1!null
 │    ├── cardinality: [2 - 2]
 │    ├── (1,)
 │    └── (1,)
 └── projections
      └── 3 [as="?column?":3]

# Don't trigger rule when Project column depends on Values column.
norm expect-not=MergeProjectWithValues
SELECT column1+1, 3 FROM (VALUES ($1::int, $2::int))
----
project
 ├── columns: "?column?":3 "?column?":4!null
 ├── cardinality: [1 - 1]
 ├── immutable, has-placeholder
 ├── key: ()
 ├── fd: ()-->(3,4)
 ├── values
 │    ├── columns: column1:1
 │    ├── cardinality: [1 - 1]
 │    ├── has-placeholder
 │    ├── key: ()
 │    ├── fd: ()-->(1)
 │    └── ($1,)
 └── projections
      ├── column1:1 + 1 [as="?column?":3, outer=(1), immutable]
      └── 3 [as="?column?":4]

# --------------------------------------------------
# FoldTupleAccessIntoValues
# --------------------------------------------------

# Simple case with VALUES operator.
norm expect=FoldTupleAccessIntoValues
SELECT (tup).@1, (tup).@2 FROM (VALUES ((1,2)), ((3,4))) AS v(tup)
----
values
 ├── columns: "?column?":2!null "?column?":3!null
 ├── cardinality: [2 - 2]
 ├── (1, 2)
 └── (3, 4)

# Simple case with unnest function.
norm expect=FoldTupleAccessIntoValues
SELECT (Tuples).@1, (Tuples).@2 FROM unnest(ARRAY[(1,2),(3,4)]) AS Tuples
----
values
 ├── columns: "?column?":2!null "?column?":3!null
 ├── cardinality: [2 - 2]
 ├── (1, 2)
 └── (3, 4)

# Case with tuples containing multiple types.
norm expect=FoldTupleAccessIntoValues
SELECT (tup).@1, (tup).@2, (tup).@3 FROM (VALUES ((1,'2',3.0)), ((4,'5',NULL::DECIMAL))) AS v(tup)
----
values
 ├── columns: "?column?":2!null "?column?":3!null "?column?":4
 ├── cardinality: [2 - 2]
 ├── (1, '2', 3.0)
 └── (4, '5', NULL)

# Case with one tuple field referenced zero times, one field referenced once,
# and one field referenced twice.
norm expect=FoldTupleAccessIntoValues
SELECT (tup).@2, (tup).@3, ARRAY[(tup).@3] FROM (VALUES ((1,2,3))) AS v(tup)
----
values
 ├── columns: "?column?":2!null "?column?":3!null array:4!null
 ├── cardinality: [1 - 1]
 ├── key: ()
 ├── fd: ()-->(2-4)
 └── (2, 3, ARRAY[3])

# Case with tuples of empty tuples.
norm expect=FoldTupleAccessIntoValues
SELECT (Tuples).@1, (Tuples).@2 FROM unnest(ARRAY[((),()),((),())]) AS Tuples
----
values
 ├── columns: "?column?":2!null "?column?":3!null
 ├── cardinality: [2 - 2]
 ├── ((), ())
 └── ((), ())

# Case with subquery projection.
norm expect=FoldTupleAccessIntoValues
SELECT (SELECT (tup).@1 * x FROM b) FROM (VALUES ((1,2)), ((3,4))) AS v(tup)
----
project
 ├── columns: "?column?":8
 ├── cardinality: [1 - ]
 ├── immutable
 ├── ensure-distinct-on
 │    ├── columns: "?column?":7 rownum:11!null
 │    ├── grouping columns: rownum:11!null
 │    ├── error: "more than one row returned by a subquery used as an expression"
 │    ├── cardinality: [1 - ]
 │    ├── immutable
 │    ├── key: (11)
 │    ├── fd: (11)-->(7)
 │    ├── left-join-apply
 │    │    ├── columns: "?column?":7 column1_1:9!null rownum:11!null
 │    │    ├── cardinality: [2 - ]
 │    │    ├── immutable
 │    │    ├── fd: (11)-->(9)
 │    │    ├── ordinality
 │    │    │    ├── columns: column1_1:9!null rownum:11!null
 │    │    │    ├── cardinality: [2 - 2]
 │    │    │    ├── key: (11)
 │    │    │    ├── fd: (11)-->(9)
 │    │    │    └── values
 │    │    │         ├── columns: column1_1:9!null
 │    │    │         ├── cardinality: [2 - 2]
 │    │    │         ├── (1,)
 │    │    │         └── (3,)
 │    │    ├── project
 │    │    │    ├── columns: "?column?":7
 │    │    │    ├── outer: (9)
 │    │    │    ├── immutable
 │    │    │    ├── scan b
 │    │    │    │    ├── columns: x:2!null
 │    │    │    │    └── key: (2)
 │    │    │    └── projections
 │    │    │         └── x:2 * column1_1:9 [as="?column?":7, outer=(2,9), immutable]
 │    │    └── filters (true)
 │    └── aggregations
 │         └── const-agg [as="?column?":7, outer=(7)]
 │              └── "?column?":7
 └── projections
      └── "?column?":7 [as="?column?":8, outer=(7)]

# Case where columns are unnested and then pruned away because the surrounding
# project only references an outer column.
norm expect=FoldTupleAccessIntoValues
SELECT (SELECT ((x).@1) FROM (VALUES ((5,6)),((7,8)))) FROM (VALUES ((1,2)), ((3,4))) v(x);
----
project
 ├── columns: "?column?":6!null
 ├── cardinality: [1 - 4]
 ├── ensure-distinct-on
 │    ├── columns: "?column?":3!null rownum:9!null
 │    ├── grouping columns: rownum:9!null
 │    ├── error: "more than one row returned by a subquery used as an expression"
 │    ├── cardinality: [1 - 4]
 │    ├── key: (9)
 │    ├── fd: (9)-->(3)
 │    ├── project
 │    │    ├── columns: "?column?":3!null rownum:9!null
 │    │    ├── cardinality: [4 - 4]
 │    │    ├── fd: (9)-->(3)
 │    │    ├── inner-join (cross)
 │    │    │    ├── columns: column1_1:7!null rownum:9!null
 │    │    │    ├── cardinality: [4 - 4]
 │    │    │    ├── multiplicity: left-rows(one-or-more), right-rows(one-or-more)
 │    │    │    ├── fd: (9)-->(7)
 │    │    │    ├── ordinality
 │    │    │    │    ├── columns: column1_1:7!null rownum:9!null
 │    │    │    │    ├── cardinality: [2 - 2]
 │    │    │    │    ├── key: (9)
 │    │    │    │    ├── fd: (9)-->(7)
 │    │    │    │    └── values
 │    │    │    │         ├── columns: column1_1:7!null
 │    │    │    │         ├── cardinality: [2 - 2]
 │    │    │    │         ├── (1,)
 │    │    │    │         └── (3,)
 │    │    │    ├── values
 │    │    │    │    ├── cardinality: [2 - 2]
 │    │    │    │    ├── ()
 │    │    │    │    └── ()
 │    │    │    └── filters (true)
 │    │    └── projections
 │    │         └── column1_1:7 [as="?column?":3, outer=(7)]
 │    └── aggregations
 │         └── const-agg [as="?column?":3, outer=(3)]
 │              └── "?column?":3
 └── projections
      └── "?column?":3 [as="?column?":6, outer=(3)]

# Case with named tuple access.
norm expect=FoldTupleAccessIntoValues
SELECT (tup).a, (tup).b
FROM (VALUES
        (((1,2) AS a,b)),
        (((3,4) AS a,b))
     ) v(tup)
----
values
 ├── columns: a:2!null b:3!null
 ├── cardinality: [2 - 2]
 ├── ((1, 2) AS a, b)
 └── ((3, 4) AS a, b)

# Case with wildcard tuple access on a named tuple.
norm expect=FoldTupleAccessIntoValues
SELECT (tup).*
FROM (VALUES
        (((1,2) AS a,b)),
        (((3,4) AS a,b))
     ) v(tup)
----
values
 ├── columns: a:2!null b:3!null
 ├── cardinality: [2 - 2]
 ├── ((1, 2) AS a, b)
 └── ((3, 4) AS a, b)

# Case with wildcard tuple access on an unnamed tuple.
norm expect=FoldTupleAccessIntoValues
SELECT (tup).*
FROM (VALUES
        ((1,2)),
        ((3,4))
     ) v(tup)
----
values
 ├── columns: "?column?":2!null "?column?":3!null
 ├── cardinality: [2 - 2]
 ├── (1, 2)
 └── (3, 4)

# No-op case because the Values operator has more than one column.
norm expect-not=FoldTupleAccessIntoValues
SELECT (col1).@1, (col2).@1 FROM (VALUES ((1,2),(3,4)), ((5,6),(7,8))) AS v(col1, col2)
----
project
 ├── columns: "?column?":3 "?column?":4
 ├── cardinality: [2 - 2]
 ├── values
 │    ├── columns: column1:1 column2:2
 │    ├── cardinality: [2 - 2]
 │    ├── ((1, 2), (3, 4))
 │    └── ((5, 6), (7, 8))
 └── projections
      ├── (column1:1).@1 [as="?column?":3, outer=(1)]
      └── (column2:2).@1 [as="?column?":4, outer=(2)]

# No-op case because the single column in Values is not of type tuple.
norm expect-not=FoldTupleAccessIntoValues
SELECT col[1], col[2] FROM (VALUES (ARRAY[1,2]), (ARRAY[3,4])) AS v(col)
----
project
 ├── columns: col:2 col:3
 ├── cardinality: [2 - 2]
 ├── values
 │    ├── columns: column1:1!null
 │    ├── cardinality: [2 - 2]
 │    ├── (ARRAY[1,2],)
 │    └── (ARRAY[3,4],)
 └── projections
      ├── column1:1[1] [as=col:2, outer=(1)]
      └── column1:1[2] [as=col:3, outer=(1)]

# No-op case because one of the tuple rows in Values can only be determined at
# run-time. Put dynamic tuple expression at end of list to ensure that all rows
# are checked.
norm expect-not=FoldTupleAccessIntoValues
SELECT (tup).@1, (tup).@2 FROM (VALUES ((3,4)), ((SELECT (x, z) FROM b))) AS v(tup)
----
project
 ├── columns: "?column?":8 "?column?":9
 ├── cardinality: [2 - 2]
 ├── values
 │    ├── columns: column1:7
 │    ├── cardinality: [2 - 2]
 │    ├── ((3, 4),)
 │    └── tuple
 │         └── subquery
 │              └── max1-row
 │                   ├── columns: "?column?":6
 │                   ├── error: "more than one row returned by a subquery used as an expression"
 │                   ├── cardinality: [0 - 1]
 │                   ├── key: ()
 │                   ├── fd: ()-->(6)
 │                   └── project
 │                        ├── columns: "?column?":6
 │                        ├── scan b
 │                        │    ├── columns: x:1!null z:2
 │                        │    ├── key: (1)
 │                        │    └── fd: (1)-->(2)
 │                        └── projections
 │                             └── (x:1, z:2) [as="?column?":6, outer=(1,2)]
 └── projections
      ├── (column1:7).@1 [as="?column?":8, outer=(7)]
      └── (column1:7).@2 [as="?column?":9, outer=(7)]

# No-op case because the tuple itself is referenced rather than just its fields.
norm expect-not=FoldTupleAccessIntoValues
SELECT (tup).@1, (tup).@2, ARRAY[tup] FROM (VALUES ((1,2)), ((3,4))) AS v(tup)
----
project
 ├── columns: "?column?":2 "?column?":3 array:4
 ├── cardinality: [2 - 2]
 ├── values
 │    ├── columns: column1:1
 │    ├── cardinality: [2 - 2]
 │    ├── ((1, 2),)
 │    └── ((3, 4),)
 └── projections
      ├── (column1:1).@1 [as="?column?":2, outer=(1)]
      ├── (column1:1).@2 [as="?column?":3, outer=(1)]
      └── ARRAY[column1:1] [as=array:4, outer=(1)]

# No-op case because the tuple itself is referenced. Make sure that a reference
# inside the input of a ColumnAccess is detected.
norm expect-not=FoldTupleAccessIntoValues
SELECT (least(tup, (1,2))).a FROM (VALUES (((1,2) AS a,b), ((3,4) AS a,b))) v(tup)
----
project
 ├── columns: a:3
 ├── cardinality: [1 - 1]
 ├── immutable
 ├── key: ()
 ├── fd: ()-->(3)
 ├── values
 │    ├── columns: column1:1
 │    ├── cardinality: [1 - 1]
 │    ├── key: ()
 │    ├── fd: ()-->(1)
 │    └── (((1, 2) AS a, b),)
 └── projections
      └── (least(column1:1, (1, 2))).a [as=a:3, outer=(1), immutable]

# --------------------------------------------------
# PushColumnRemappingIntoValues
# --------------------------------------------------

# With clause case. This works because InlineWith creates a simple remapping
# projection on the Values output column.
norm expect=PushColumnRemappingIntoValues
WITH a AS (SELECT x FROM (VALUES (1), (2)) f(x)) SELECT x FROM a
----
values
 ├── columns: x:2!null
 ├── cardinality: [2 - 2]
 ├── (1,)
 └── (2,)

# Multiplication by one case. This works because after FoldMultOne and
# EliminateCast fire, the x*1 projection does no more than rename its input
# column.
norm expect=PushColumnRemappingIntoValues
SELECT x*1 FROM (VALUES (1), (2)) f(x)
----
values
 ├── columns: "?column?":2!null
 ├── cardinality: [2 - 2]
 ├── (1,)
 └── (2,)

# Tuple access case. This works because FoldTupleAccessIntoValues creates new
# columns that reference the tuple fields, and so the surrounding Project that
# references those fields becomes a remapping of the new columns.
norm expect=PushColumnRemappingIntoValues
SELECT (tup).@1, (tup).@2 FROM (VALUES ((1,2)), ((3,4))) AS v(tup)
----
values
 ├── columns: "?column?":2!null "?column?":3!null
 ├── cardinality: [2 - 2]
 ├── (1, 2)
 └── (3, 4)

# Case with multiple remappings of the same column.
norm expect=PushColumnRemappingIntoValues
WITH a AS (SELECT x, x FROM (VALUES (1), (2)) f(x)) SELECT * FROM a
----
project
 ├── columns: x:2!null x:3!null
 ├── cardinality: [2 - 2]
 ├── fd: (2)==(3), (3)==(2)
 ├── values
 │    ├── columns: x:2!null
 │    ├── cardinality: [2 - 2]
 │    ├── (1,)
 │    └── (2,)
 └── projections
      └── x:2 [as=x:3, outer=(2)]

# Case with a projection on a column only determined at run-time.
norm expect=PushColumnRemappingIntoValues
WITH a AS (SELECT v FROM (VALUES (1), ((SELECT z FROM b WHERE z=1))) f(v)) SELECT v FROM a
----
values
 ├── columns: v:7
 ├── cardinality: [2 - 2]
 ├── (1,)
 └── tuple
      └── subquery
           └── max1-row
                ├── columns: z:2!null
                ├── error: "more than one row returned by a subquery used as an expression"
                ├── cardinality: [0 - 1]
                ├── key: ()
                ├── fd: ()-->(2)
                └── select
                     ├── columns: z:2!null
                     ├── fd: ()-->(2)
                     ├── scan b
                     │    └── columns: z:2
                     └── filters
                          └── z:2 = 1 [outer=(2), constraints=(/2: [/1 - /1]; tight), fd=()-->(2)]

# Case with a non-VariableExpr reference to a remapped column.
norm expect=PushColumnRemappingIntoValues
SELECT x*1, x+1 FROM (VALUES (1), (2)) f(x)
----
project
 ├── columns: "?column?":2!null "?column?":3!null
 ├── cardinality: [2 - 2]
 ├── immutable
 ├── fd: (2)-->(3)
 ├── values
 │    ├── columns: "?column?":2!null
 │    ├── cardinality: [2 - 2]
 │    ├── (1,)
 │    └── (2,)
 └── projections
      └── "?column?":2 + 1 [as="?column?":3, outer=(2), immutable]

# Case with a subquery reference to a remapped column.
norm expect=PushColumnRemappingIntoValues
SELECT
    x*1,
    (SELECT * FROM (Values (1), (2), (3), (4)) WHERE x=12)
FROM
    (VALUES (11), (12)) f(x)
----
project
 ├── columns: "?column?":3!null "?column?":4
 ├── cardinality: [1 - 8]
 ├── ensure-distinct-on
 │    ├── columns: column1:2 "?column?":3!null rownum:5!null
 │    ├── grouping columns: rownum:5!null
 │    ├── error: "more than one row returned by a subquery used as an expression"
 │    ├── cardinality: [1 - 8]
 │    ├── key: (5)
 │    ├── fd: (5)-->(2,3)
 │    ├── left-join (cross)
 │    │    ├── columns: column1:2 "?column?":3!null rownum:5!null
 │    │    ├── cardinality: [2 - 8]
 │    │    ├── fd: (5)-->(3)
 │    │    ├── ordinality
 │    │    │    ├── columns: "?column?":3!null rownum:5!null
 │    │    │    ├── cardinality: [2 - 2]
 │    │    │    ├── key: (5)
 │    │    │    ├── fd: (5)-->(3)
 │    │    │    └── values
 │    │    │         ├── columns: "?column?":3!null
 │    │    │         ├── cardinality: [2 - 2]
 │    │    │         ├── (11,)
 │    │    │         └── (12,)
 │    │    ├── values
 │    │    │    ├── columns: column1:2!null
 │    │    │    ├── cardinality: [4 - 4]
 │    │    │    ├── (1,)
 │    │    │    ├── (2,)
 │    │    │    ├── (3,)
 │    │    │    └── (4,)
 │    │    └── filters
 │    │         └── "?column?":3 = 12 [outer=(3), constraints=(/3: [/12 - /12]; tight), fd=()-->(3)]
 │    └── aggregations
 │         ├── const-agg [as=column1:2, outer=(2)]
 │         │    └── column1:2
 │         └── const-agg [as="?column?":3, outer=(3)]
 │              └── "?column?":3
 └── projections
      └── column1:2 [as="?column?":4, outer=(2)]

# PushColumnRemappingIntoValues should only fold one projection into the
# passthrough columns because all the projections refer to the same column.
norm expect=PushColumnRemappingIntoValues
SELECT x*1*1, x*1 FROM (VALUES (1), (2)) v(x)
----
project
 ├── columns: "?column?":2!null "?column?":3!null
 ├── cardinality: [2 - 2]
 ├── fd: (2)==(3), (3)==(2)
 ├── values
 │    ├── columns: "?column?":2!null
 │    ├── cardinality: [2 - 2]
 │    ├── (1,)
 │    └── (2,)
 └── projections
      └── "?column?":2 [as="?column?":3, outer=(2)]

# Case with only one column that can be replaced (The z*1 column can replace the
# original z column).
norm expect=PushColumnRemappingIntoValues
SELECT x, x*1, y, y*1, z*1 FROM (VALUES (1,2,3), (2,3,6)) v(x,y,z)
----
project
 ├── columns: x:1!null "?column?":4!null y:2!null "?column?":5!null "?column?":6!null
 ├── cardinality: [2 - 2]
 ├── fd: (1)==(4), (4)==(1), (2)==(5), (5)==(2)
 ├── values
 │    ├── columns: column1:1!null column2:2!null "?column?":6!null
 │    ├── cardinality: [2 - 2]
 │    ├── (1, 2, 3)
 │    └── (2, 3, 6)
 └── projections
      ├── column1:1 [as="?column?":4, outer=(1)]
      └── column2:2 [as="?column?":5, outer=(2)]

# No-op case because no columns from the input ValuesExpr are being remapped.
norm expect-not=PushColumnRemappingIntoValues
SELECT (SELECT x FROM (VALUES (1), (2)) f(x)) FROM (VALUES (2), (3))
----
project
 ├── columns: x:3
 ├── cardinality: [2 - 2]
 ├── values
 │    ├── cardinality: [2 - 2]
 │    ├── ()
 │    └── ()
 └── projections
      └── subquery [as=x:3, subquery]
           └── max1-row
                ├── columns: column1:2!null
                ├── error: "more than one row returned by a subquery used as an expression"
                ├── cardinality: [1 - 1]
                ├── key: ()
                ├── fd: ()-->(2)
                └── values
                     ├── columns: column1:2!null
                     ├── cardinality: [2 - 2]
                     ├── (1,)
                     └── (2,)

# No-op case because a passthrough column is being remapped.
norm expect-not=PushColumnRemappingIntoValues
SELECT x, x*1 FROM (VALUES (1), (2)) v(x)
----
project
 ├── columns: x:1!null "?column?":2!null
 ├── cardinality: [2 - 2]
 ├── fd: (1)==(2), (2)==(1)
 ├── values
 │    ├── columns: column1:1!null
 │    ├── cardinality: [2 - 2]
 │    ├── (1,)
 │    └── (2,)
 └── projections
      └── column1:1 [as="?column?":2, outer=(1)]

# No-op case because the Project is on a Scan rather than a Values operator.
norm expect-not=PushColumnRemappingIntoValues
WITH t AS (SELECT * FROM a) SELECT x FROM t
----
project
 ├── columns: x:7!null
 ├── key: (7)
 ├── scan a
 │    ├── columns: a.x:1!null
 │    └── key: (1)
 └── projections
      └── a.x:1 [as=x:7, outer=(1)]

# No-op case with no projections on the Project surrounding the Values operator.
# A Project with no projections is created when PruneUnionAllCols fires, and is
# then removed by EliminateProject.
norm expect-not=PushColumnRemappingIntoValues
WITH a AS
(
  SELECT * FROM (VALUES (1,2)) AS f(x,y)
  UNION ALL (VALUES (3,4))
)
SELECT x FROM a
----
project
 ├── columns: x:7!null
 ├── cardinality: [2 - 2]
 ├── union-all
 │    ├── columns: x:5!null
 │    ├── left columns: column1:1
 │    ├── right columns: column1:3
 │    ├── cardinality: [2 - 2]
 │    ├── values
 │    │    ├── columns: column1:1!null
 │    │    ├── cardinality: [1 - 1]
 │    │    ├── key: ()
 │    │    ├── fd: ()-->(1)
 │    │    └── (1,)
 │    └── values
 │         ├── columns: column1:3!null
 │         ├── cardinality: [1 - 1]
 │         ├── key: ()
 │         ├── fd: ()-->(3)
 │         └── (3,)
 └── projections
      └── x:5 [as=x:7, outer=(5)]

# --------------------------------------------------
# PushAssignmentCastsIntoValues
# --------------------------------------------------

norm expect=PushAssignmentCastsIntoValues
INSERT INTO assn_cast (c, qc, i, s) VALUES (' ', 'foo', '1', 2), ('bar', 'baz', '10', 20)
----
insert assn_cast
 ├── columns: <none>
 ├── insert-mapping:
 │    ├── c_cast:12 => c:1
 │    ├── qc_cast:13 => qc:2
 │    ├── column3:10 => i:3
 │    ├── s_cast:14 => s:4
 │    └── rowid_default:15 => rowid:5
 ├── cardinality: [0 - 0]
 ├── volatile, mutations
 └── project
      ├── columns: rowid_default:15 column3:10!null c_cast:12 qc_cast:13!null s_cast:14!null
      ├── cardinality: [2 - 2]
      ├── volatile
      ├── values
      │    ├── columns: c_cast:12 qc_cast:13!null column3:10!null s_cast:14!null
      │    ├── cardinality: [2 - 2]
      │    ├── immutable
      │    ├── ('', 'f', 1, '2')
      │    └── tuple
      │         ├── assignment-cast: CHAR
      │         │    └── 'bar'
      │         ├── 'b'
      │         ├── 10
      │         └── '20'
      └── projections
           └── unique_rowid() [as=rowid_default:15, volatile]

# Push assignment cast projections if all the values are not constant.
norm expect=PushAssignmentCastsIntoValues
INSERT INTO assn_cast (c, qc, i, s) VALUES (' ', 'foo', 1, 'bar'), ($1, $2, $3, $4)
----
insert assn_cast
 ├── columns: <none>
 ├── insert-mapping:
 │    ├── c_cast:12 => c:1
 │    ├── qc_cast:13 => qc:2
 │    ├── column3:10 => i:3
 │    ├── column4:11 => s:4
 │    └── rowid_default:14 => rowid:5
 ├── cardinality: [0 - 0]
 ├── volatile, mutations, has-placeholder
 └── project
      ├── columns: rowid_default:14 column3:10 column4:11 c_cast:12 qc_cast:13
      ├── cardinality: [2 - 2]
      ├── volatile, has-placeholder
      ├── values
      │    ├── columns: c_cast:12 qc_cast:13 column3:10 column4:11
      │    ├── cardinality: [2 - 2]
      │    ├── immutable, has-placeholder
      │    ├── ('', 'f', 1, 'bar')
      │    └── tuple
      │         ├── assignment-cast: CHAR
      │         │    └── $1
      │         ├── assignment-cast: "char"
      │         │    └── $2
      │         ├── $3
      │         └── $4
      └── projections
           └── unique_rowid() [as=rowid_default:14, volatile]

# Push assignment cast projections if there are other projections that are not
# assignment casts.
norm expect=PushAssignmentCastsIntoValues
INSERT INTO assn_cast (c, qc, i, s) (SELECT ' ', 'foo', '1', unnest(ARRAY[1, 2, 3]:::INT8[]))
----
insert assn_cast
 ├── columns: <none>
 ├── insert-mapping:
 │    ├── c_cast:12 => c:1
 │    ├── qc_cast:13 => qc:2
 │    ├── "?column?":11 => i:3
 │    ├── s_cast:14 => s:4
 │    └── rowid_default:15 => rowid:5
 ├── cardinality: [0 - 0]
 ├── volatile, mutations
 └── project
      ├── columns: rowid_default:15 c_cast:12!null qc_cast:13!null "?column?":11!null s_cast:14!null
      ├── cardinality: [3 - 3]
      ├── volatile
      ├── fd: ()-->(11-13)
      ├── values
      │    ├── columns: s_cast:14!null
      │    ├── cardinality: [3 - 3]
      │    ├── ('1',)
      │    ├── ('2',)
      │    └── ('3',)
      └── projections
           ├── unique_rowid() [as=rowid_default:15, volatile]
           ├── '' [as=c_cast:12]
           ├── 'f' [as=qc_cast:13]
           └── 1 [as="?column?":11]

# Do not push assignment casts into values when other projections reference the
# column being casted.
norm expect-not=PushAssignmentCastsIntoValues
INSERT INTO assn_cast (i, s) SELECT x, (x + 1)::STRING FROM (VALUES (1), (2)) AS v (x)
----
insert assn_cast
 ├── columns: <none>
 ├── insert-mapping:
 │    ├── c_default:10 => c:1
 │    ├── qc_default:11 => qc:2
 │    ├── column1:8 => i:3
 │    ├── text:9 => s:4
 │    └── rowid_default:12 => rowid:5
 ├── cardinality: [0 - 0]
 ├── volatile, mutations
 └── project
      ├── columns: c_default:10 qc_default:11 rowid_default:12 text:9!null column1:8!null
      ├── cardinality: [2 - 2]
      ├── volatile
      ├── fd: ()-->(10,11), (8)-->(9)
      ├── values
      │    ├── columns: column1:8!null
      │    ├── cardinality: [2 - 2]
      │    ├── (1,)
      │    └── (2,)
      └── projections
           ├── CAST(NULL AS CHAR) [as=c_default:10]
           ├── CAST(NULL AS "char") [as=qc_default:11]
           ├── unique_rowid() [as=rowid_default:12, volatile]
           └── (column1:8 + 1)::STRING [as=text:9, outer=(8), immutable]

# Do not match if there are no assignment cast projections.
norm expect-not=PushAssignmentCastsIntoValues
SELECT i + 10 FROM (VALUES (1), (2), (3)) AS v(i)
----
project
 ├── columns: "?column?":2!null
 ├── cardinality: [3 - 3]
 ├── immutable
 ├── values
 │    ├── columns: column1:1!null
 │    ├── cardinality: [3 - 3]
 │    ├── (1,)
 │    ├── (2,)
 │    └── (3,)
 └── projections
      └── column1:1 + 10 [as="?column?":2, outer=(1), immutable]

# Regression test for #74241. Do not push assignment casts into values when the
# casted column is also a passthrough column.
exec-ddl
CREATE TABLE t74241 (
  a INT2,
  b INT,
  c INT2,
  d INT
)
----

norm disable=(EliminateZeroCardSelect,EliminateZeroCardProject)
INSERT INTO t74241 (a, b, c)
SELECT tmp.b, tmp.b, tmp.d FROM t74241 AS tmp
WHERE false
----
insert t74241
 ├── columns: <none>
 ├── insert-mapping:
 │    ├── a_cast:15 => t74241.a:1
 │    ├── tmp.b:9 => t74241.b:2
 │    ├── c_cast:16 => t74241.c:3
 │    ├── d_default:17 => t74241.d:4
 │    └── rowid_default:18 => t74241.rowid:5
 ├── cardinality: [0 - 0]
 ├── volatile, mutations
 └── project
      ├── columns: d_default:17 rowid_default:18 a_cast:15!null tmp.b:9!null c_cast:16!null
      ├── cardinality: [0 - 0]
      ├── volatile
      ├── key: ()
      ├── fd: ()-->(9,15-18)
      ├── values
      │    ├── columns: tmp.b:9!null c_cast:16!null
      │    ├── cardinality: [0 - 0]
      │    ├── key: ()
      │    └── fd: ()-->(9,16)
      └── projections
           ├── CAST(NULL AS INT8) [as=d_default:17]
           ├── unique_rowid() [as=rowid_default:18, volatile]
           └── assignment-cast: INT2 [as=a_cast:15, outer=(9), immutable]
                └── tmp.b:9

# --------------------------------------------------
# FoldJSONAccessIntoValues
# --------------------------------------------------

# Basic case: all rows have the same single key.
norm expect=FoldJSONAccessIntoValues
SELECT j->'x' AS x
FROM
(VALUES
    ('{"x": "one"}'::JSON),
    ('{"x": "two"}'::JSON),
    ('{"x": "three"}'::JSON)
) v(j)
----
values
 ├── columns: x:2!null
 ├── cardinality: [3 - 3]
 ├── ('"one"',)
 ├── ('"two"',)
 └── ('"three"',)

# Case with three fields and all rows have the same schema; order of key-value
# pairs does not matter.
norm expect=FoldJSONAccessIntoValues
SELECT j->'x' AS x, j->'y' AS y, j->'z' AS z
FROM
(VALUES
    ('{"y": "red", "z": 1, "x": "one"}'::JSON),
    ('{"z": 2, "y": "yellow", "x": "two"}'::JSON),
    ('{"x": "three", "y": "blue", "z": 3}'::JSON)
) v(j)
----
values
 ├── columns: x:2!null y:3!null z:4!null
 ├── cardinality: [3 - 3]
 ├── ('"one"', '"red"', '1')
 ├── ('"two"', '"yellow"', '2')
 └── ('"three"', '"blue"', '3')

# Case where not all first row fields are referenced. The unreferenced columns
# are later pruned away by a different rule.
norm expect=FoldJSONAccessIntoValues
SELECT j->'x' AS x
FROM
(VALUES
    ('{"y": "red", "z": 1, "x": "one"}'::JSON),
    ('{"z": 2, "y": "yellow", "x": "two"}'::JSON),
    ('{"x": "three", "y": "blue", "z": 3}'::JSON)
) v(j)
----
values
 ├── columns: x:2!null
 ├── cardinality: [3 - 3]
 ├── ('"one"',)
 ├── ('"two"',)
 └── ('"three"',)

# Case with various value types.
norm expect=FoldJSONAccessIntoValues
SELECT j->'x' AS x
FROM
(VALUES
    ('{"x": 1}'::JSON),
    ('{"x": [1,2,3]}'::JSON),
    ('{"x": {"a": "three"}}'::JSON),
    ('{"x": [{"b": 1}, {"b": 2}]}'::JSON),
    ('{"x": null}'::JSON)
) v(j)
----
values
 ├── columns: x:2!null
 ├── cardinality: [5 - 5]
 ├── ('1',)
 ├── ('[1, 2, 3]',)
 ├── ('{"a": "three"}',)
 ├── ('[{"b": 1}, {"b": 2}]',)
 └── ('null',)

# Case where fields that are not present in the first row are filtered out.
norm expect=FoldJSONAccessIntoValues
SELECT j->'x' AS x
FROM
(VALUES
    ('{"x": "one"}'::JSON),
    ('{"z": "2", "y": "yellow", "x": "two"}'::JSON),
    ('{"x": "three", "y": "blue", "z": "3"}'::JSON)
) v(j)
----
values
 ├── columns: x:2!null
 ├── cardinality: [3 - 3]
 ├── ('"one"',)
 ├── ('"two"',)
 └── ('"three"',)

# Case with one projection referencing an outer JSON column (j) that should not
# be folded and the other referencing an inner column (c) that should be folded.
norm expect=FoldJSONAccessIntoValues
SELECT *
FROM b
INNER JOIN LATERAL
(
    SELECT j->'x' AS x, c->'x'
    FROM
    (VALUES
        ('{"x": "zero"}'::JSON),
        ('{"x": "one"}'::JSON)
    ) v(c)
)
ON True
----
project
 ├── columns: x:1!null z:2 j:3 x:7 "?column?":8!null
 ├── immutable
 ├── fd: (1)-->(2,3)
 ├── inner-join (cross)
 │    ├── columns: b.x:1!null z:2 j:3 "?column?":8!null
 │    ├── multiplicity: left-rows(one-or-more), right-rows(zero-or-more)
 │    ├── fd: (1)-->(2,3)
 │    ├── scan b
 │    │    ├── columns: b.x:1!null z:2 j:3
 │    │    ├── key: (1)
 │    │    └── fd: (1)-->(2,3)
 │    ├── values
 │    │    ├── columns: "?column?":8!null
 │    │    ├── cardinality: [2 - 2]
 │    │    ├── ('"zero"',)
 │    │    └── ('"one"',)
 │    └── filters (true)
 └── projections
      └── j:3->'x' [as=x:7, outer=(3), immutable]

# Rule fires harmlessly when none of the JSON fields are accessed; the columns
# are later pruned away by PruneValuesCols.
norm expect=FoldJSONAccessIntoValues
SELECT
    (
        SELECT j->'x'
        FROM
        (VALUES
            ('{"x": "zero"}'::JSON),
            ('{"x": "one"}'::JSON)
        ) v(c)
    )
FROM b
----
project
 ├── columns: "?column?":9
 ├── immutable
 ├── ensure-distinct-on
 │    ├── columns: x:1!null "?column?":7
 │    ├── grouping columns: x:1!null
 │    ├── error: "more than one row returned by a subquery used as an expression"
 │    ├── immutable
 │    ├── key: (1)
 │    ├── fd: (1)-->(7)
 │    ├── project
 │    │    ├── columns: "?column?":7 x:1!null
 │    │    ├── immutable
 │    │    ├── inner-join (cross)
 │    │    │    ├── columns: x:1!null j:3
 │    │    │    ├── multiplicity: left-rows(one-or-more), right-rows(zero-or-more)
 │    │    │    ├── fd: (1)-->(3)
 │    │    │    ├── scan b
 │    │    │    │    ├── columns: x:1!null j:3
 │    │    │    │    ├── key: (1)
 │    │    │    │    └── fd: (1)-->(3)
 │    │    │    ├── values
 │    │    │    │    ├── cardinality: [2 - 2]
 │    │    │    │    ├── ()
 │    │    │    │    └── ()
 │    │    │    └── filters (true)
 │    │    └── projections
 │    │         └── j:3->'x' [as="?column?":7, outer=(3), immutable]
 │    └── aggregations
 │         └── const-agg [as="?column?":7, outer=(7)]
 │              └── "?column?":7
 └── projections
      └── "?column?":7 [as="?column?":9, outer=(7)]

# No-op case because the Values column contains strings, not JSON expressions.
norm expect-not=FoldJSONAccessIntoValues
SELECT j::JSON->'x' AS x
FROM
(VALUES
    ('{"x": "one"}'),
    ('{"x": "two"}'),
    ('{"x": "three"}')
) v(j)
----
project
 ├── columns: x:2
 ├── cardinality: [3 - 3]
 ├── immutable
 ├── values
 │    ├── columns: column1:1!null
 │    ├── cardinality: [3 - 3]
 │    ├── ('{"x": "one"}',)
 │    ├── ('{"x": "two"}',)
 │    └── ('{"x": "three"}',)
 └── projections
      └── column1:1::JSONB->'x' [as=x:2, outer=(1), immutable]

# No-op case with SQL null row.
norm expect-not=FoldJSONAccessIntoValues
SELECT j->'x' AS x
FROM
(VALUES
    ('{"x": "two"}'::JSON),
    (NULL::JSON),
    ('{"x": "three"}'::JSON)
) v(j)
----
project
 ├── columns: x:2
 ├── cardinality: [3 - 3]
 ├── immutable
 ├── values
 │    ├── columns: column1:1
 │    ├── cardinality: [3 - 3]
 │    ├── ('{"x": "two"}',)
 │    ├── (NULL,)
 │    └── ('{"x": "three"}',)
 └── projections
      └── column1:1->'x' [as=x:2, outer=(1), immutable]

# No-op case with JSON null row.
norm expect-not=FoldJSONAccessIntoValues
SELECT j->'x' As x
FROM
(VALUES
    ('null'::JSON),
    ('{"x": "two"}'::JSON),
    ('{"x": "three"}'::JSON)
) v(j)
----
project
 ├── columns: x:2
 ├── cardinality: [3 - 3]
 ├── immutable
 ├── values
 │    ├── columns: column1:1!null
 │    ├── cardinality: [3 - 3]
 │    ├── ('null',)
 │    ├── ('{"x": "two"}',)
 │    └── ('{"x": "three"}',)
 └── projections
      └── column1:1->'x' [as=x:2, outer=(1), immutable]

# No-op case because the Values operator has more than one column.
norm expect-not=FoldJSONAccessIntoValues
SELECT j1->'x' AS x1, j2->'x' AS x2
FROM
(VALUES
    ('{"x": "one"}'::JSON, '{"x": "two"}'::JSON),
    ('{"x": "three"}'::JSON, '{"x": "four"}'::JSON)
) v(j1, j2)
----
project
 ├── columns: x1:3 x2:4
 ├── cardinality: [2 - 2]
 ├── immutable
 ├── values
 │    ├── columns: column1:1!null column2:2!null
 │    ├── cardinality: [2 - 2]
 │    ├── ('{"x": "one"}', '{"x": "two"}')
 │    └── ('{"x": "three"}', '{"x": "four"}')
 └── projections
      ├── column1:1->'x' [as=x1:3, outer=(1), immutable]
      └── column2:2->'x' [as=x2:4, outer=(2), immutable]

# No-op case because the JSON column is directly referenced.
norm expect-not=FoldJSONAccessIntoValues
SELECT j->'x' AS x, j
FROM
(VALUES
    ('{"x": "one"}'::JSON),
    ('{"x": "two"}'::JSON)
) v(j)
----
project
 ├── columns: x:2 j:1!null
 ├── cardinality: [2 - 2]
 ├── immutable
 ├── values
 │    ├── columns: column1:1!null
 │    ├── cardinality: [2 - 2]
 │    ├── ('{"x": "one"}',)
 │    └── ('{"x": "two"}',)
 └── projections
      └── column1:1->'x' [as=x:2, outer=(1), immutable]

# No-op case because one of the Values rows is a reference to an outer column.
norm expect-not=FoldJSONAccessIntoValues
SELECT j->'x' AS x
FROM
(VALUES
    ('{"x": "one"}'::JSON),
    ((SELECT j FROM b))
) v(j)
----
project
 ├── columns: x:7
 ├── cardinality: [2 - 2]
 ├── immutable
 ├── values
 │    ├── columns: column1:6
 │    ├── cardinality: [2 - 2]
 │    ├── ('{"x": "one"}',)
 │    └── tuple
 │         └── subquery
 │              └── max1-row
 │                   ├── columns: j:3
 │                   ├── error: "more than one row returned by a subquery used as an expression"
 │                   ├── cardinality: [0 - 1]
 │                   ├── key: ()
 │                   ├── fd: ()-->(3)
 │                   └── scan b
 │                        └── columns: j:3
 └── projections
      └── column1:6->'x' [as=x:7, outer=(6), immutable]

# No-op case because the key being used to access JSON fields is not a constant
# string.
norm expect-not=FoldJSONAccessIntoValues
SELECT
    (
        SELECT v.j->a.s
        FROM
        (VALUES
           ('{"x": "one"}'::JSON),
           ('{"x": "two"}'::JSON)
        ) v(j)
    )
FROM a
----
project
 ├── columns: "?column?":9
 ├── immutable
 ├── ensure-distinct-on
 │    ├── columns: x:1!null "?column?":8
 │    ├── grouping columns: x:1!null
 │    ├── error: "more than one row returned by a subquery used as an expression"
 │    ├── immutable
 │    ├── key: (1)
 │    ├── fd: (1)-->(8)
 │    ├── project
 │    │    ├── columns: "?column?":8 x:1!null
 │    │    ├── immutable
 │    │    ├── inner-join (cross)
 │    │    │    ├── columns: x:1!null s:4 column1:7!null
 │    │    │    ├── multiplicity: left-rows(one-or-more), right-rows(zero-or-more)
 │    │    │    ├── fd: (1)-->(4)
 │    │    │    ├── scan a
 │    │    │    │    ├── columns: x:1!null s:4
 │    │    │    │    ├── key: (1)
 │    │    │    │    └── fd: (1)-->(4)
 │    │    │    ├── values
 │    │    │    │    ├── columns: column1:7!null
 │    │    │    │    ├── cardinality: [2 - 2]
 │    │    │    │    ├── ('{"x": "one"}',)
 │    │    │    │    └── ('{"x": "two"}',)
 │    │    │    └── filters (true)
 │    │    └── projections
 │    │         └── column1:7->s:4 [as="?column?":8, outer=(4,7), immutable]
 │    └── aggregations
 │         └── const-agg [as="?column?":8, outer=(8)]
 │              └── "?column?":8
 └── projections
      └── "?column?":8 [as="?column?":9, outer=(8)]

# No-op case because one of the projections attempts to access a key that is not
# in any of the rows.
norm expect-not=FoldJSONAccessIntoValues
SELECT j::JSON->'x' AS x, j::JSON->'y' AS y
FROM
(VALUES
    ('{"x": "one"}'),
    ('{"x": "two"}'),
    ('{"x": "three"}')
) v(j)
----
project
 ├── columns: x:2 y:3
 ├── cardinality: [3 - 3]
 ├── immutable
 ├── values
 │    ├── columns: column1:1!null
 │    ├── cardinality: [3 - 3]
 │    ├── ('{"x": "one"}',)
 │    ├── ('{"x": "two"}',)
 │    └── ('{"x": "three"}',)
 └── projections
      ├── column1:1::JSONB->'x' [as=x:2, outer=(1), immutable]
      └── column1:1::JSONB->'y' [as=y:3, outer=(1), immutable]

# No-op case because one of the projections attempts to access a key that is not
# in the first row.
norm expect-not=FoldJSONAccessIntoValues
SELECT j::JSON->'x' AS x, j::JSON->'y' AS y
FROM
(VALUES
    ('{"x": "one"}'),
    ('{"x": "two", "y": "blue"}'),
    ('{"x": "three", "y": "red"}')
) v(j)
----
project
 ├── columns: x:2 y:3
 ├── cardinality: [3 - 3]
 ├── immutable
 ├── values
 │    ├── columns: column1:1!null
 │    ├── cardinality: [3 - 3]
 │    ├── ('{"x": "one"}',)
 │    ├── ('{"x": "two", "y": "blue"}',)
 │    └── ('{"x": "three", "y": "red"}',)
 └── projections
      ├── column1:1::JSONB->'x' [as=x:2, outer=(1), immutable]
      └── column1:1::JSONB->'y' [as=y:3, outer=(1), immutable]

# No-op case because one of the rows does not have all the keys that are present
# in the first row.
norm expect-not=FoldJSONAccessIntoValues
SELECT j::JSON->'x' AS x
FROM
(VALUES
    ('{"x": "one"}'),
    ('{"x": "two"}'),
    ('{"y": "three"}')
) v(j)
----
project
 ├── columns: x:2
 ├── cardinality: [3 - 3]
 ├── immutable
 ├── values
 │    ├── columns: column1:1!null
 │    ├── cardinality: [3 - 3]
 │    ├── ('{"x": "one"}',)
 │    ├── ('{"x": "two"}',)
 │    └── ('{"y": "three"}',)
 └── projections
      └── column1:1::JSONB->'x' [as=x:2, outer=(1), immutable]

# Verify that the rule doesn't fire because of an array that has the right key
# as an element (#60522).
norm expect-not=FoldJSONAccessIntoValues
SELECT j->'foo' FROM (VALUES ('{"foo": "bar"}'::JSONB), ('["foo", "baz"]'::JSONB)) AS v(j)
----
project
 ├── columns: "?column?":2
 ├── cardinality: [2 - 2]
 ├── immutable
 ├── values
 │    ├── columns: column1:1!null
 │    ├── cardinality: [2 - 2]
 │    ├── ('{"foo": "bar"}',)
 │    └── ('["foo", "baz"]',)
 └── projections
      └── column1:1->'foo' [as="?column?":2, outer=(1), immutable]

# --------------------------------------------------
# FoldIsNullProject
# --------------------------------------------------

norm expect=FoldIsNullProject
SELECT x IS NULL FROM a
----
project
 ├── columns: "?column?":7!null
 ├── fd: ()-->(7)
 ├── scan a
 └── projections
      └── false [as="?column?":7]

norm expect=FoldIsNullProject
SELECT k IS NULL, v IS NULL, r1 IS NULL FROM fks
----
project
 ├── columns: "?column?":8!null "?column?":9!null "?column?":10!null
 ├── fd: ()-->(8,10)
 ├── scan fks
 │    └── columns: v:2
 └── projections
      ├── false [as="?column?":8]
      ├── v:2 IS NULL [as="?column?":9, outer=(2)]
      └── false [as="?column?":10]

norm expect-not=FoldIsNullProject
SELECT y IS NULL FROM a
----
project
 ├── columns: "?column?":7!null
 ├── scan a
 │    └── columns: y:2
 └── projections
      └── y:2 IS NULL [as="?column?":7, outer=(2)]

# The column "b.x" is not nullable in the base table, but could be NULL after
# the left-join, so b.x IS NULL cannot be folded.
norm expect=FoldIsNullProject
SELECT a.x IS NULL, b.x IS NULL FROM a LEFT JOIN b ON a.x = b.x;
----
project
 ├── columns: "?column?":12!null "?column?":13!null
 ├── fd: ()-->(12)
 ├── left-join (hash)
 │    ├── columns: a.x:1!null b.x:7
 │    ├── multiplicity: left-rows(exactly-one), right-rows(zero-or-one)
 │    ├── key: (1)
 │    ├── fd: (1)-->(7)
 │    ├── scan a
 │    │    ├── columns: a.x:1!null
 │    │    └── key: (1)
 │    ├── scan b
 │    │    ├── columns: b.x:7!null
 │    │    └── key: (7)
 │    └── filters
 │         └── a.x:1 = b.x:7 [outer=(1,7), constraints=(/1: (/NULL - ]; /7: (/NULL - ]), fd=(1)==(7), (7)==(1)]
 └── projections
      ├── false [as="?column?":12]
      └── b.x:7 IS NULL [as="?column?":13, outer=(7)]

# FoldIsNullProject helps eliminate NULL reordering for ASC NULLS LAST if the
# order by column is not nullable.
norm expect=FoldIsNullProject
SELECT x FROM a ORDER BY x ASC NULLS LAST
----
scan a
 ├── columns: x:1!null
 ├── key: (1)
 └── ordering: +1

# FoldIsNullProject helps eliminate NULL reordering for DESC NULLS LAST if the
# order by column is not nullable.
norm expect=FoldIsNullProject
SELECT x FROM a ORDER BY x DESC NULLS FIRST
----
scan a,rev
 ├── columns: x:1!null
 ├── key: (1)
 └── ordering: -1

# Null reordering is needed for "y" but not for "x".
norm expect=FoldIsNullProject
SELECT x FROM a ORDER BY y DESC NULLS FIRST, x DESC NULLS FIRST
----
sort
 ├── columns: x:1!null  [hidden: y:2 nulls_ordering_y:7!null]
 ├── key: (1)
 ├── fd: (1)-->(2), (2)-->(7)
 ├── ordering: -7,-2,-1
 └── project
      ├── columns: nulls_ordering_y:7!null x:1!null y:2
      ├── key: (1)
      ├── fd: (1)-->(2), (2)-->(7)
      ├── scan a
      │    ├── columns: x:1!null y:2
      │    ├── key: (1)
      │    └── fd: (1)-->(2)
      └── projections
           └── y:2 IS NULL [as=nulls_ordering_y:7, outer=(2)]

# --------------------------------------------------
# PushLeakproofProjectionsIntoPermeableBarrier
# --------------------------------------------------

exec-ddl
SET ROLE alice;
----

# All expressions in projection are leakproof
norm expect=PushLeakproofProjectionsIntoPermeableBarrier
SELECT CASE WHEN y = 20 THEN 10 ELSE 0 END FROM rls
----
project
 ├── columns: case:6
 ├── select
 │    ├── columns: y:2 alice_has_access:3!null
 │    ├── fd: ()-->(3)
 │    ├── scan rls
 │    │    └── columns: y:2 alice_has_access:3
 │    └── filters
 │         └── alice_has_access:3 [outer=(3), constraints=(/3: [/true - /true]; tight), fd=()-->(3)]
 └── projections
      └── CASE WHEN y:2 = 20 THEN 10 ELSE 0 END [as=case:6, outer=(2)]

# Only some expressions in projection are leakproof
norm expect-not=PushLeakproofProjectionsIntoPermeableBarrier
SELECT CASE WHEN y = 20 THEN 10 ELSE 0 END, y/0 FROM rls
----
project
 ├── columns: case:6 "?column?":7
 ├── immutable
 ├── barrier
 │    ├── columns: x:1!null y:2 alice_has_access:3!null crdb_internal_mvcc_timestamp:4 tableoid:5
 │    ├── key: (1)
 │    ├── fd: ()-->(3), (1)-->(2,4,5)
 │    └── select
 │         ├── columns: x:1!null y:2 alice_has_access:3!null crdb_internal_mvcc_timestamp:4 tableoid:5
 │         ├── key: (1)
 │         ├── fd: ()-->(3), (1)-->(2,4,5)
 │         ├── scan rls
 │         │    ├── columns: x:1!null y:2 alice_has_access:3 crdb_internal_mvcc_timestamp:4 tableoid:5
 │         │    ├── key: (1)
 │         │    └── fd: (1)-->(2-5)
 │         └── filters
 │              └── alice_has_access:3 [outer=(3), constraints=(/3: [/true - /true]; tight), fd=()-->(3)]
 └── projections
      ├── CASE WHEN y:2 = 20 THEN 10 ELSE 0 END [as=case:6, outer=(2)]
      └── y:2 / 0 [as="?column?":7, outer=(2), immutable]

# All expressions in projection are not leakproof
norm expect-not=PushLeakproofProjectionsIntoPermeableBarrier
SELECT y/0 FROM rls
----
project
 ├── columns: "?column?":6
 ├── immutable
 ├── barrier
 │    ├── columns: x:1!null y:2 alice_has_access:3!null crdb_internal_mvcc_timestamp:4 tableoid:5
 │    ├── key: (1)
 │    ├── fd: ()-->(3), (1)-->(2,4,5)
 │    └── select
 │         ├── columns: x:1!null y:2 alice_has_access:3!null crdb_internal_mvcc_timestamp:4 tableoid:5
 │         ├── key: (1)
 │         ├── fd: ()-->(3), (1)-->(2,4,5)
 │         ├── scan rls
 │         │    ├── columns: x:1!null y:2 alice_has_access:3 crdb_internal_mvcc_timestamp:4 tableoid:5
 │         │    ├── key: (1)
 │         │    └── fd: (1)-->(2-5)
 │         └── filters
 │              └── alice_has_access:3 [outer=(3), constraints=(/3: [/true - /true]; tight), fd=()-->(3)]
 └── projections
      └── y:2 / 0 [as="?column?":6, outer=(2), immutable]

# All expressions in projection are leakproof and we have passthrough columns
norm expect=PushLeakproofProjectionsIntoPermeableBarrier
SELECT x, y, CASE WHEN y = 20 THEN 10 ELSE 0 END FROM rls
----
project
 ├── columns: x:1!null y:2 case:6
 ├── key: (1)
 ├── fd: (1)-->(2), (2)-->(6)
 ├── select
 │    ├── columns: x:1!null y:2 alice_has_access:3!null
 │    ├── key: (1)
 │    ├── fd: ()-->(3), (1)-->(2)
 │    ├── scan rls
 │    │    ├── columns: x:1!null y:2 alice_has_access:3
 │    │    ├── key: (1)
 │    │    └── fd: (1)-->(2,3)
 │    └── filters
 │         └── alice_has_access:3 [outer=(3), constraints=(/3: [/true - /true]; tight), fd=()-->(3)]
 └── projections
      └── CASE WHEN y:2 = 20 THEN 10 ELSE 0 END [as=case:6, outer=(2)]