select

exec-ddl
CREATE TABLE a (k INT PRIMARY KEY, i INT, f FLOAT, s STRING, j JSON)
----

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

exec-ddl
CREATE TABLE uv (u INT PRIMARY KEY, v INT)
----

exec-ddl
CREATE TABLE c (a BOOL, b BOOL, c BOOL, d BOOL, e BOOL)
----

exec-ddl
CREATE TABLE e
(
    k INT PRIMARY KEY,
    i INT,
    t TIMESTAMP,
    tz TIMESTAMPTZ,
    d DATE
)
----

# --------------------------------------------------
# SimplifySelectFilters
# --------------------------------------------------
norm expect=SimplifySelectFilters
SELECT * FROM a WHERE Null
----
values
 ├── columns: k:1!null i:2!null f:3!null s:4!null j:5!null
 ├── cardinality: [0 - 0]
 ├── key: ()
 └── fd: ()-->(1-5)

norm expect=SimplifySelectFilters
SELECT * FROM a WHERE i=1 AND Null
----
values
 ├── columns: k:1!null i:2!null f:3!null s:4!null j:5!null
 ├── cardinality: [0 - 0]
 ├── key: ()
 └── fd: ()-->(1-5)

norm expect=SimplifySelectFilters
SELECT * FROM a WHERE k=1 AND (i=2 AND (f=3.5 AND s='foo')) AND true
----
select
 ├── columns: k:1!null i:2!null f:3!null s:4!null j:5
 ├── cardinality: [0 - 1]
 ├── key: ()
 ├── fd: ()-->(1-5)
 ├── scan a
 │    ├── columns: k:1!null i:2 f:3 s:4 j:5
 │    ├── key: (1)
 │    └── fd: (1)-->(2-5)
 └── filters
      ├── k:1 = 1 [outer=(1), constraints=(/1: [/1 - /1]; tight), fd=()-->(1)]
      ├── i:2 = 2 [outer=(2), constraints=(/2: [/2 - /2]; tight), fd=()-->(2)]
      ├── f:3 = 3.5 [outer=(3), constraints=(/3: [/3.5 - /3.5]; tight), fd=()-->(3)]
      └── s:4 = 'foo' [outer=(4), constraints=(/4: [/'foo' - /'foo']; tight), fd=()-->(4)]

norm expect=SimplifySelectFilters
SELECT * FROM a WHERE k=1 OR NULL
----
select
 ├── columns: k:1!null i:2 f:3 s:4 j:5
 ├── cardinality: [0 - 1]
 ├── key: ()
 ├── fd: ()-->(1-5)
 ├── scan a
 │    ├── columns: k:1!null i:2 f:3 s:4 j:5
 │    ├── key: (1)
 │    └── fd: (1)-->(2-5)
 └── filters
      └── k:1 = 1 [outer=(1), constraints=(/1: [/1 - /1]; tight), fd=()-->(1)]

norm expect-not=SimplifySelectFilters
SELECT * FROM a WHERE k=1 OR i=2
----
select
 ├── columns: k:1!null i:2 f:3 s:4 j:5
 ├── key: (1)
 ├── fd: (1)-->(2-5)
 ├── scan a
 │    ├── columns: k:1!null i:2 f:3 s:4 j:5
 │    ├── key: (1)
 │    └── fd: (1)-->(2-5)
 └── filters
      └── (k:1 = 1) OR (i:2 = 2) [outer=(1,2)]

# Simplify IS True to its left input.
norm expect=SimplifySelectFilters
SELECT * FROM c WHERE a IS True
----
select
 ├── columns: a:1!null b:2 c:3 d:4 e:5
 ├── fd: ()-->(1)
 ├── scan c
 │    └── columns: a:1 b:2 c:3 d:4 e:5
 └── filters
      └── a:1 [outer=(1), constraints=(/1: [/true - /true]; tight), fd=()-->(1)]

# Simplify IS False to the negation of its left input.
norm expect=SimplifySelectFilters
SELECT * FROM c WHERE a IS False
----
select
 ├── columns: a:1!null b:2 c:3 d:4 e:5
 ├── fd: ()-->(1)
 ├── scan c
 │    └── columns: a:1 b:2 c:3 d:4 e:5
 └── filters
      └── NOT a:1 [outer=(1), constraints=(/1: [/false - /false]; tight), fd=()-->(1)]

# Simplify a deeply nested IS True.
norm expect=SimplifySelectFilters
Select * FROM c WHERE a AND (b AND (c AND (d AND (e IS True))))
----
select
 ├── columns: a:1!null b:2!null c:3!null d:4!null e:5!null
 ├── fd: ()-->(1-5)
 ├── scan c
 │    └── columns: a:1 b:2 c:3 d:4 e:5
 └── filters
      ├── a:1 [outer=(1), constraints=(/1: [/true - /true]; tight), fd=()-->(1)]
      ├── b:2 [outer=(2), constraints=(/2: [/true - /true]; tight), fd=()-->(2)]
      ├── c:3 [outer=(3), constraints=(/3: [/true - /true]; tight), fd=()-->(3)]
      ├── d:4 [outer=(4), constraints=(/4: [/true - /true]; tight), fd=()-->(4)]
      └── e:5 [outer=(5), constraints=(/5: [/true - /true]; tight), fd=()-->(5)]

# Don't simplify the IS because the IS expression is a projection, not a Select
# filter.
norm expect-not=SimplifySelectFilters
Select a IS True FROM c WHERE b
----
project
 ├── columns: "?column?":9!null
 ├── select
 │    ├── columns: a:1 b:2!null
 │    ├── fd: ()-->(2)
 │    ├── scan c
 │    │    └── columns: a:1 b:2
 │    └── filters
 │         └── b:2 [outer=(2), constraints=(/2: [/true - /true]; tight), fd=()-->(2)]
 └── projections
      └── a:1 IS true [as="?column?":9, outer=(1)]

# Don't simplify an IS NOT expression.
norm expect-not=SimplifySelectFilters
SELECT * FROM c WHERE a IS NOT True
----
select
 ├── columns: a:1 b:2 c:3 d:4 e:5
 ├── scan c
 │    └── columns: a:1 b:2 c:3 d:4 e:5
 └── filters
      └── a:1 IS NOT true [outer=(1), constraints=(/1: [ - /false]; tight)]

# Don't simplify the IS because its right argument is Null.
norm expect-not=SimplifySelectFilters
SELECT * FROM c WHERE a IS Null
----
select
 ├── columns: a:1 b:2 c:3 d:4 e:5
 ├── fd: ()-->(1)
 ├── scan c
 │    └── columns: a:1 b:2 c:3 d:4 e:5
 └── filters
      └── a:1 IS NULL [outer=(1), constraints=(/1: [/NULL - /NULL]; tight), fd=()-->(1)]

# --------------------------------------------------
# ConsolidateSelectFilters
# --------------------------------------------------

norm expect=ConsolidateSelectFilters
SELECT * FROM a WHERE i >= 5 AND i < 10 AND i IN (0, 2, 4, 6, 8, 10, 12)
----
select
 ├── columns: k:1!null i:2!null f:3 s:4 j:5
 ├── key: (1)
 ├── fd: (1)-->(2-5)
 ├── scan a
 │    ├── columns: k:1!null i:2 f:3 s:4 j:5
 │    ├── key: (1)
 │    └── fd: (1)-->(2-5)
 └── filters
      └── ((i:2 >= 5) AND (i:2 < 10)) AND (i:2 IN (0, 2, 4, 6, 8, 10, 12)) [outer=(2), constraints=(/2: [/6 - /6] [/8 - /8]; tight)]

norm expect-not=ConsolidateSelectFilters
SELECT * FROM a WHERE k >= 5 AND i < 10
----
select
 ├── columns: k:1!null i:2!null f:3 s:4 j:5
 ├── key: (1)
 ├── fd: (1)-->(2-5)
 ├── scan a
 │    ├── columns: k:1!null i:2 f:3 s:4 j:5
 │    ├── key: (1)
 │    └── fd: (1)-->(2-5)
 └── filters
      ├── k:1 >= 5 [outer=(1), constraints=(/1: [/5 - ]; tight)]
      └── i:2 < 10 [outer=(2), constraints=(/2: (/NULL - /9]; tight)]

norm expect=ConsolidateSelectFilters
SELECT * FROM c WHERE a AND a=true AND b AND b=c
----
select
 ├── columns: a:1!null b:2!null c:3!null d:4 e:5
 ├── fd: ()-->(1-3), (2)==(3), (3)==(2)
 ├── scan c
 │    └── columns: a:1 b:2 c:3 d:4 e:5
 └── filters
      ├── a:1 [outer=(1), constraints=(/1: [/true - /true]; tight), fd=()-->(1)]
      ├── b:2 [outer=(2), constraints=(/2: [/true - /true]; tight), fd=()-->(2)]
      └── b:2 = c:3 [outer=(2,3), constraints=(/2: (/NULL - ]; /3: (/NULL - ]), fd=(2)==(3), (3)==(2)]

norm expect=ConsolidateSelectFilters disable=InlineConstVar
SELECT * FROM a WHERE i IS NOT NULL AND i = 3
AND f > 5 AND f < 15 AND s >= 'bar' AND s <= 'foo'
----
select
 ├── columns: k:1!null i:2!null f:3!null s:4!null j:5
 ├── key: (1)
 ├── fd: ()-->(2), (1)-->(3-5)
 ├── scan a
 │    ├── columns: k:1!null i:2 f:3 s:4 j:5
 │    ├── key: (1)
 │    └── fd: (1)-->(2-5)
 └── filters
      ├── (i:2 IS NOT NULL) AND (i:2 = 3) [outer=(2), constraints=(/2: [/3 - /3]; tight), fd=()-->(2)]
      ├── (f:3 > 5.0) AND (f:3 < 15.0) [outer=(3), constraints=(/3: [/5.000000000000001 - /14.999999999999998]; tight)]
      └── (s:4 >= 'bar') AND (s:4 <= 'foo') [outer=(4), constraints=(/4: [/'bar' - /'foo']; tight)]

norm expect=ConsolidateSelectFilters
SELECT * FROM a WHERE i IS NULL AND i IS DISTINCT FROM 5
----
select
 ├── columns: k:1!null i:2 f:3 s:4 j:5
 ├── key: (1)
 ├── fd: ()-->(2), (1)-->(3-5)
 ├── scan a
 │    ├── columns: k:1!null i:2 f:3 s:4 j:5
 │    ├── key: (1)
 │    └── fd: (1)-->(2-5)
 └── filters
      └── (i:2 IS NULL) AND (i:2 IS DISTINCT FROM 5) [outer=(2), constraints=(/2: [/NULL - /NULL]; tight), fd=()-->(2)]

norm expect=ConsolidateSelectFilters disable=InlineConstVar
SELECT * FROM a WHERE s LIKE 'a%' AND s SIMILAR TO 'a_' AND s = 'aa'
----
select
 ├── columns: k:1!null i:2 f:3 s:4!null j:5
 ├── key: (1)
 ├── fd: ()-->(4), (1)-->(2,3,5)
 ├── scan a
 │    ├── columns: k:1!null i:2 f:3 s:4 j:5
 │    ├── key: (1)
 │    └── fd: (1)-->(2-5)
 └── filters
      ├── (s:4 LIKE 'a%') AND (s:4 = 'aa') [outer=(4), constraints=(/4: [/'aa' - /'aa']; tight), fd=()-->(4)]
      └── s:4 SIMILAR TO 'a_' [outer=(4), constraints=(/4: [/'a' - /'b'))]

# One of the constraints is not tight, so it should not be consolidated.
norm expect-not=ConsolidateSelectFilters
SELECT k FROM e WHERE d > '2018-07-01' AND d < '2018-07-01'::DATE + '1w1s'::INTERVAL
----
project
 ├── columns: k:1!null
 ├── immutable
 ├── key: (1)
 └── select
      ├── columns: k:1!null d:5!null
      ├── immutable
      ├── key: (1)
      ├── fd: (1)-->(5)
      ├── scan e
      │    ├── columns: k:1!null d:5
      │    ├── key: (1)
      │    └── fd: (1)-->(5)
      └── filters
           ├── d:5 > '2018-07-01' [outer=(5), constraints=(/5: [/'2018-07-02' - ]; tight)]
           └── d:5 < '2018-07-08 00:00:01' [outer=(5), immutable, constraints=(/5: (/NULL - ])]

# Ranges can be merged with other filters to create new ranges.
norm expect=ConsolidateSelectFilters disable=InlineConstVar
SELECT * FROM (SELECT * FROM a WHERE k = 5) AS a, e WHERE a.k = e.k AND a.k > 1 AND e.k < 10
----
inner-join (hash)
 ├── columns: k:1!null i:2 f:3 s:4 j:5 k:8!null i:9 t:10 tz:11 d:12
 ├── cardinality: [0 - 1]
 ├── multiplicity: left-rows(zero-or-one), right-rows(zero-or-one)
 ├── key: ()
 ├── fd: ()-->(1-5,8-12), (8)==(1), (1)==(8)
 ├── select
 │    ├── columns: a.k:1!null a.i:2 f:3 s:4 j:5
 │    ├── cardinality: [0 - 1]
 │    ├── key: ()
 │    ├── fd: ()-->(1-5)
 │    ├── scan a
 │    │    ├── columns: a.k:1!null a.i:2 f:3 s:4 j:5
 │    │    ├── key: (1)
 │    │    └── fd: (1)-->(2-5)
 │    └── filters
 │         └── ((a.k:1 = 5) AND (a.k:1 > 1)) AND (a.k:1 < 10) [outer=(1), constraints=(/1: [/5 - /5]; tight), fd=()-->(1)]
 ├── select
 │    ├── columns: e.k:8!null e.i:9 t:10 tz:11 d:12
 │    ├── cardinality: [0 - 8]
 │    ├── key: (8)
 │    ├── fd: (8)-->(9-12)
 │    ├── scan e
 │    │    ├── columns: e.k:8!null e.i:9 t:10 tz:11 d:12
 │    │    ├── key: (8)
 │    │    └── fd: (8)-->(9-12)
 │    └── filters
 │         └── (e.k:8 < 10) AND (e.k:8 > 1) [outer=(8), constraints=(/8: [/2 - /9]; tight)]
 └── filters
      └── a.k:1 = e.k:8 [outer=(1,8), constraints=(/1: (/NULL - ]; /8: (/NULL - ]), fd=(1)==(8), (8)==(1)]

# The duplicate filter i >= 5 should be eliminated.
norm expect=ConsolidateSelectFilters
SELECT * FROM (SELECT * FROM a WHERE i >= 5 AND i < 10) AS a, xy WHERE i >= 5
----
inner-join (cross)
 ├── columns: k:1!null i:2!null f:3 s:4 j:5 x:8!null y:9
 ├── key: (1,8)
 ├── fd: (1)-->(2-5), (8)-->(9)
 ├── select
 │    ├── columns: k:1!null i:2!null f:3 s:4 j:5
 │    ├── key: (1)
 │    ├── fd: (1)-->(2-5)
 │    ├── scan a
 │    │    ├── columns: k:1!null i:2 f:3 s:4 j:5
 │    │    ├── key: (1)
 │    │    └── fd: (1)-->(2-5)
 │    └── filters
 │         └── (i:2 >= 5) AND (i:2 < 10) [outer=(2), constraints=(/2: [/5 - /9]; tight)]
 ├── scan xy
 │    ├── columns: x:8!null y:9
 │    ├── key: (8)
 │    └── fd: (8)-->(9)
 └── filters (true)

norm expect=ConsolidateSelectFilters
SELECT * FROM (SELECT * FROM a WHERE i < 10 AND i >= 5) AS a, xy WHERE i >= 5
----
inner-join (cross)
 ├── columns: k:1!null i:2!null f:3 s:4 j:5 x:8!null y:9
 ├── key: (1,8)
 ├── fd: (1)-->(2-5), (8)-->(9)
 ├── select
 │    ├── columns: k:1!null i:2!null f:3 s:4 j:5
 │    ├── key: (1)
 │    ├── fd: (1)-->(2-5)
 │    ├── scan a
 │    │    ├── columns: k:1!null i:2 f:3 s:4 j:5
 │    │    ├── key: (1)
 │    │    └── fd: (1)-->(2-5)
 │    └── filters
 │         └── (i:2 < 10) AND (i:2 >= 5) [outer=(2), constraints=(/2: [/5 - /9]; tight)]
 ├── scan xy
 │    ├── columns: x:8!null y:9
 │    ├── key: (8)
 │    └── fd: (8)-->(9)
 └── filters (true)

norm expect=ConsolidateSelectFilters
SELECT * FROM (SELECT * FROM a WHERE i < 10 AND i >= 5 AND i IN (0, 2, 4, 6, 8, 10, 12)) AS a, xy
WHERE i >= 5 AND i < 10
----
inner-join (cross)
 ├── columns: k:1!null i:2!null f:3 s:4 j:5 x:8!null y:9
 ├── key: (1,8)
 ├── fd: (1)-->(2-5), (8)-->(9)
 ├── select
 │    ├── columns: k:1!null i:2!null f:3 s:4 j:5
 │    ├── key: (1)
 │    ├── fd: (1)-->(2-5)
 │    ├── scan a
 │    │    ├── columns: k:1!null i:2 f:3 s:4 j:5
 │    │    ├── key: (1)
 │    │    └── fd: (1)-->(2-5)
 │    └── filters
 │         └── ((i:2 < 10) AND (i:2 >= 5)) AND (i:2 IN (0, 2, 4, 6, 8, 10, 12)) [outer=(2), constraints=(/2: [/6 - /6] [/8 - /8]; tight)]
 ├── scan xy
 │    ├── columns: x:8!null y:9
 │    ├── key: (8)
 │    └── fd: (8)-->(9)
 └── filters (true)

# --------------------------------------------------
# EliminateSelect
# --------------------------------------------------
norm expect=EliminateSelect
SELECT * FROM a WHERE True
----
scan a
 ├── columns: k:1!null i:2 f:3 s:4 j:5
 ├── key: (1)
 └── fd: (1)-->(2-5)

exec-ddl
CREATE INDEX partial_idx ON a (s) WHERE true
----

# Building the partial index predicate should trigger the rule.
norm expect=EliminateSelect
SELECT * FROM a
----
scan a
 ├── columns: k:1!null i:2 f:3 s:4 j:5
 ├── partial index predicates
 │    └── partial_idx: filters (true)
 ├── key: (1)
 └── fd: (1)-->(2-5)

exec-ddl
DROP INDEX partial_idx
----

# --------------------------------------------------
# MergeSelects
# --------------------------------------------------
norm expect=MergeSelects
SELECT * FROM (SELECT * FROM a WHERE k=3) WHERE s='foo'
----
select
 ├── columns: k:1!null i:2 f:3 s:4!null j:5
 ├── cardinality: [0 - 1]
 ├── key: ()
 ├── fd: ()-->(1-5)
 ├── scan a
 │    ├── columns: k:1!null i:2 f:3 s:4 j:5
 │    ├── key: (1)
 │    └── fd: (1)-->(2-5)
 └── filters
      ├── k:1 = 3 [outer=(1), constraints=(/1: [/3 - /3]; tight), fd=()-->(1)]
      └── s:4 = 'foo' [outer=(4), constraints=(/4: [/'foo' - /'foo']; tight), fd=()-->(4)]

norm expect=MergeSelects
SELECT * FROM (SELECT * FROM a WHERE i=1) WHERE False
----
values
 ├── columns: k:1!null i:2!null f:3!null s:4!null j:5!null
 ├── cardinality: [0 - 0]
 ├── key: ()
 └── fd: ()-->(1-5)

norm expect=MergeSelects
SELECT * FROM (SELECT * FROM a WHERE i<5) WHERE s='foo'
----
select
 ├── columns: k:1!null i:2!null f:3 s:4!null j:5
 ├── key: (1)
 ├── fd: ()-->(4), (1)-->(2,3,5)
 ├── scan a
 │    ├── columns: k:1!null i:2 f:3 s:4 j:5
 │    ├── key: (1)
 │    └── fd: (1)-->(2-5)
 └── filters
      ├── i:2 < 5 [outer=(2), constraints=(/2: (/NULL - /4]; tight)]
      └── s:4 = 'foo' [outer=(4), constraints=(/4: [/'foo' - /'foo']; tight), fd=()-->(4)]

norm expect=MergeSelects
SELECT * FROM (SELECT * FROM a WHERE i>1 AND i<10) WHERE s='foo' OR k=5
----
select
 ├── columns: k:1!null i:2!null f:3 s:4 j:5
 ├── key: (1)
 ├── fd: (1)-->(2-5)
 ├── scan a
 │    ├── columns: k:1!null i:2 f:3 s:4 j:5
 │    ├── key: (1)
 │    └── fd: (1)-->(2-5)
 └── filters
      ├── (i:2 > 1) AND (i:2 < 10) [outer=(2), constraints=(/2: [/2 - /9]; tight)]
      └── (s:4 = 'foo') OR (k:1 = 5) [outer=(1,4)]

# --------------------------------------------------
# PushSelectIntoProject
# --------------------------------------------------
norm expect=PushSelectIntoProject
SELECT * FROM (SELECT i, i+1 AS r, f FROM a) a WHERE f=10.0
----
project
 ├── columns: i:2 r:8 f:3!null
 ├── immutable
 ├── fd: ()-->(3), (2)-->(8)
 ├── select
 │    ├── columns: i:2 f:3!null
 │    ├── fd: ()-->(3)
 │    ├── scan a
 │    │    └── columns: i:2 f:3
 │    └── filters
 │         └── f:3 = 10.0 [outer=(3), constraints=(/3: [/10.0 - /10.0]; tight), fd=()-->(3)]
 └── projections
      └── i:2 + 1 [as=r:8, outer=(2), immutable]

# Don't push down select if it depends on computed column that can't be inlined.
norm expect-not=PushSelectIntoProject
SELECT * FROM (SELECT i, i/2 div, f FROM a) a WHERE div=2
----
select
 ├── columns: i:2 div:8!null f:3
 ├── immutable
 ├── fd: ()-->(8)
 ├── project
 │    ├── columns: div:8 i:2 f:3
 │    ├── fd: (2)-->(8)
 │    ├── scan a
 │    │    └── columns: i:2 f:3
 │    └── projections
 │         └── i:2 / 2 [as=div:8, outer=(2)]
 └── filters
      └── div:8 = 2 [outer=(8), immutable, constraints=(/8: [/2 - /2]; tight), fd=()-->(8)]

# Push down some conjuncts, but not others.
norm expect=PushSelectIntoProject
SELECT * FROM (SELECT i, i/2 div, f FROM a) a WHERE 10.0=f AND 2=div AND i=1
----
select
 ├── columns: i:2!null div:8!null f:3!null
 ├── immutable
 ├── fd: ()-->(2,3,8)
 ├── project
 │    ├── columns: div:8!null i:2!null f:3!null
 │    ├── fd: ()-->(2,3,8)
 │    ├── select
 │    │    ├── columns: i:2!null f:3!null
 │    │    ├── fd: ()-->(2,3)
 │    │    ├── scan a
 │    │    │    └── columns: i:2 f:3
 │    │    └── filters
 │    │         ├── f:3 = 10.0 [outer=(3), constraints=(/3: [/10.0 - /10.0]; tight), fd=()-->(3)]
 │    │         └── i:2 = 1 [outer=(2), constraints=(/2: [/1 - /1]; tight), fd=()-->(2)]
 │    └── projections
 │         └── i:2 / 2 [as=div:8, outer=(2)]
 └── filters
      └── div:8 = 2 [outer=(8), immutable, constraints=(/8: [/2 - /2]; tight), fd=()-->(8)]

# Detect PushSelectIntoProject and FilterUnusedSelectCols dependency cycle.
norm
SELECT f, f+1.1 AS r FROM (SELECT f, i FROM a GROUP BY f, i HAVING sum(f)=10.0) a
----
project
 ├── columns: f:3 r:9
 ├── immutable
 ├── fd: (3)-->(9)
 ├── select
 │    ├── columns: i:2 f:3 sum:8!null
 │    ├── key: (2,3)
 │    ├── fd: ()-->(8)
 │    ├── group-by
 │    │    ├── columns: i:2 f:3 sum:8
 │    │    ├── grouping columns: i:2 f:3
 │    │    ├── key: (2,3)
 │    │    ├── fd: (2,3)-->(8)
 │    │    ├── scan a
 │    │    │    └── columns: i:2 f:3
 │    │    └── aggregations
 │    │         └── sum [as=sum:8, outer=(3)]
 │    │              └── f:3
 │    └── filters
 │         └── sum:8 = 10.0 [outer=(8), constraints=(/8: [/10.0 - /10.0]; tight), fd=()-->(8)]
 └── projections
      └── f:3 + 1.1 [as=r:9, outer=(3), immutable]

# --------------------------------------
# PushSelectCondLeftIntoJoinLeftAndRight
# --------------------------------------

# Only the filters bound by the left side are mapped and pushed down.
norm expect=PushSelectCondLeftIntoJoinLeftAndRight
SELECT * FROM a LEFT JOIN xy ON a.k=xy.x WHERE a.k > 5 AND (xy.x = 6 OR xy.x IS NULL)
----
select
 ├── columns: k:1!null i:2 f:3 s:4 j:5 x:8 y:9
 ├── key: (1)
 ├── fd: (1)-->(2-5,8,9), (8)-->(9)
 ├── left-join (hash)
 │    ├── columns: k:1!null i:2 f:3 s:4 j:5 x:8 y:9
 │    ├── multiplicity: left-rows(exactly-one), right-rows(zero-or-one)
 │    ├── key: (1)
 │    ├── fd: (1)-->(2-5,8,9), (8)-->(9)
 │    ├── select
 │    │    ├── columns: k:1!null i:2 f:3 s:4 j:5
 │    │    ├── key: (1)
 │    │    ├── fd: (1)-->(2-5)
 │    │    ├── scan a
 │    │    │    ├── columns: k:1!null i:2 f:3 s:4 j:5
 │    │    │    ├── key: (1)
 │    │    │    └── fd: (1)-->(2-5)
 │    │    └── filters
 │    │         └── k:1 > 5 [outer=(1), constraints=(/1: [/6 - ]; tight)]
 │    ├── select
 │    │    ├── columns: x:8!null y:9
 │    │    ├── key: (8)
 │    │    ├── fd: (8)-->(9)
 │    │    ├── scan xy
 │    │    │    ├── columns: x:8!null y:9
 │    │    │    ├── key: (8)
 │    │    │    └── fd: (8)-->(9)
 │    │    └── filters
 │    │         └── x:8 > 5 [outer=(8), constraints=(/8: [/6 - ]; tight)]
 │    └── filters
 │         └── k:1 = x:8 [outer=(1,8), constraints=(/1: (/NULL - ]; /8: (/NULL - ]), fd=(1)==(8), (8)==(1)]
 └── filters
      └── (x:8 = 6) OR (x:8 IS NULL) [outer=(8), constraints=(/8: [/NULL - /NULL] [/6 - /6]; tight)]

norm expect=PushSelectCondLeftIntoJoinLeftAndRight
SELECT * FROM a WHERE EXISTS (SELECT * FROM xy WHERE a.k=xy.x) AND a.k > 5
----
semi-join (hash)
 ├── columns: k:1!null i:2 f:3 s:4 j:5
 ├── key: (1)
 ├── fd: (1)-->(2-5)
 ├── select
 │    ├── columns: k:1!null i:2 f:3 s:4 j:5
 │    ├── key: (1)
 │    ├── fd: (1)-->(2-5)
 │    ├── scan a
 │    │    ├── columns: k:1!null i:2 f:3 s:4 j:5
 │    │    ├── key: (1)
 │    │    └── fd: (1)-->(2-5)
 │    └── filters
 │         └── k:1 > 5 [outer=(1), constraints=(/1: [/6 - ]; tight)]
 ├── select
 │    ├── columns: x:8!null
 │    ├── key: (8)
 │    ├── scan xy
 │    │    ├── columns: x:8!null
 │    │    └── key: (8)
 │    └── filters
 │         └── x:8 > 5 [outer=(8), constraints=(/8: [/6 - ]; tight)]
 └── filters
      └── k:1 = x:8 [outer=(1,8), constraints=(/1: (/NULL - ]; /8: (/NULL - ]), fd=(1)==(8), (8)==(1)]

norm expect=PushSelectCondLeftIntoJoinLeftAndRight
SELECT * FROM a WHERE NOT EXISTS (SELECT * FROM xy WHERE a.k=xy.x) AND a.k > 5
----
anti-join (hash)
 ├── columns: k:1!null i:2 f:3 s:4 j:5
 ├── key: (1)
 ├── fd: (1)-->(2-5)
 ├── select
 │    ├── columns: k:1!null i:2 f:3 s:4 j:5
 │    ├── key: (1)
 │    ├── fd: (1)-->(2-5)
 │    ├── scan a
 │    │    ├── columns: k:1!null i:2 f:3 s:4 j:5
 │    │    ├── key: (1)
 │    │    └── fd: (1)-->(2-5)
 │    └── filters
 │         └── k:1 > 5 [outer=(1), constraints=(/1: [/6 - ]; tight)]
 ├── select
 │    ├── columns: x:8!null
 │    ├── key: (8)
 │    ├── scan xy
 │    │    ├── columns: x:8!null
 │    │    └── key: (8)
 │    └── filters
 │         └── x:8 > 5 [outer=(8), constraints=(/8: [/6 - ]; tight)]
 └── filters
      └── k:1 = x:8 [outer=(1,8), constraints=(/1: (/NULL - ]; /8: (/NULL - ]), fd=(1)==(8), (8)==(1)]

# --------------------------------------------------
# PushSelectIntoJoinLeft
# --------------------------------------------------
norm expect=PushSelectIntoJoinLeft
SELECT * FROM a LEFT JOIN xy ON a.k=xy.x WHERE a.f=1.1
----
left-join (hash)
 ├── columns: k:1!null i:2 f:3!null s:4 j:5 x:8 y:9
 ├── multiplicity: left-rows(exactly-one), right-rows(zero-or-one)
 ├── key: (1)
 ├── fd: ()-->(3), (1)-->(2,4,5,8,9), (8)-->(9)
 ├── select
 │    ├── columns: k:1!null i:2 f:3!null s:4 j:5
 │    ├── key: (1)
 │    ├── fd: ()-->(3), (1)-->(2,4,5)
 │    ├── scan a
 │    │    ├── columns: k:1!null i:2 f:3 s:4 j:5
 │    │    ├── key: (1)
 │    │    └── fd: (1)-->(2-5)
 │    └── filters
 │         └── f:3 = 1.1 [outer=(3), constraints=(/3: [/1.1 - /1.1]; tight), fd=()-->(3)]
 ├── scan xy
 │    ├── columns: x:8!null y:9
 │    ├── key: (8)
 │    └── fd: (8)-->(9)
 └── filters
      └── k:1 = x:8 [outer=(1,8), constraints=(/1: (/NULL - ]; /8: (/NULL - ]), fd=(1)==(8), (8)==(1)]

norm expect=PushSelectIntoJoinLeft
SELECT * FROM a LEFT JOIN xy ON a.k=xy.x
WHERE a.f=1.1 AND (a.i<xy.y OR xy.y IS NULL) AND (a.s='foo' OR a.s='bar')
----
select
 ├── columns: k:1!null i:2 f:3!null s:4!null j:5 x:8 y:9
 ├── key: (1)
 ├── fd: ()-->(3), (1)-->(2,4,5,8,9), (8)-->(9)
 ├── left-join (hash)
 │    ├── columns: k:1!null i:2 f:3!null s:4!null j:5 x:8 y:9
 │    ├── multiplicity: left-rows(exactly-one), right-rows(zero-or-one)
 │    ├── key: (1)
 │    ├── fd: ()-->(3), (1)-->(2,4,5,8,9), (8)-->(9)
 │    ├── select
 │    │    ├── columns: k:1!null i:2 f:3!null s:4!null j:5
 │    │    ├── key: (1)
 │    │    ├── fd: ()-->(3), (1)-->(2,4,5)
 │    │    ├── scan a
 │    │    │    ├── columns: k:1!null i:2 f:3 s:4 j:5
 │    │    │    ├── key: (1)
 │    │    │    └── fd: (1)-->(2-5)
 │    │    └── filters
 │    │         ├── f:3 = 1.1 [outer=(3), constraints=(/3: [/1.1 - /1.1]; tight), fd=()-->(3)]
 │    │         └── (s:4 = 'foo') OR (s:4 = 'bar') [outer=(4), constraints=(/4: [/'bar' - /'bar'] [/'foo' - /'foo']; tight)]
 │    ├── scan xy
 │    │    ├── columns: x:8!null y:9
 │    │    ├── key: (8)
 │    │    └── fd: (8)-->(9)
 │    └── filters
 │         └── k:1 = x:8 [outer=(1,8), constraints=(/1: (/NULL - ]; /8: (/NULL - ]), fd=(1)==(8), (8)==(1)]
 └── filters
      └── (i:2 < y:9) OR (y:9 IS NULL) [outer=(2,9)]

# Pushdown constant condition.
norm expect=PushSelectIntoJoinLeft
SELECT * FROM a LEFT JOIN xy ON True WHERE a.i=100 AND $1>'2000-01-01T1:00:00'
----
left-join (cross)
 ├── columns: k:1!null i:2!null f:3 s:4 j:5 x:8 y:9
 ├── has-placeholder
 ├── key: (1,8)
 ├── fd: ()-->(2), (1)-->(3-5), (8)-->(9)
 ├── select
 │    ├── columns: k:1!null i:2!null f:3 s:4 j:5
 │    ├── has-placeholder
 │    ├── key: (1)
 │    ├── fd: ()-->(2), (1)-->(3-5)
 │    ├── scan a
 │    │    ├── columns: k:1!null i:2 f:3 s:4 j:5
 │    │    ├── key: (1)
 │    │    └── fd: (1)-->(2-5)
 │    └── filters
 │         ├── $1 > '2000-01-01T1:00:00'
 │         └── i:2 = 100 [outer=(2), constraints=(/2: [/100 - /100]; tight), fd=()-->(2)]
 ├── select
 │    ├── columns: x:8!null y:9
 │    ├── has-placeholder
 │    ├── key: (8)
 │    ├── fd: (8)-->(9)
 │    ├── scan xy
 │    │    ├── columns: x:8!null y:9
 │    │    ├── key: (8)
 │    │    └── fd: (8)-->(9)
 │    └── filters
 │         └── $1 > '2000-01-01T1:00:00'
 └── filters (true)

# Don't push down conditions in case of RIGHT JOIN.
norm
SELECT * FROM a RIGHT JOIN xy ON a.k=xy.x WHERE a.i=100 OR a.i IS NULL
----
select
 ├── columns: k:1 i:2 f:3 s:4 j:5 x:8!null y:9
 ├── key: (8)
 ├── fd: (8)-->(1-5,9), (1)-->(2-5)
 ├── left-join (hash)
 │    ├── columns: k:1 i:2 f:3 s:4 j:5 x:8!null y:9
 │    ├── multiplicity: left-rows(exactly-one), right-rows(zero-or-one)
 │    ├── key: (8)
 │    ├── fd: (8)-->(1-5,9), (1)-->(2-5)
 │    ├── scan xy
 │    │    ├── columns: x:8!null y:9
 │    │    ├── key: (8)
 │    │    └── fd: (8)-->(9)
 │    ├── scan a
 │    │    ├── columns: k:1!null i:2 f:3 s:4 j:5
 │    │    ├── key: (1)
 │    │    └── fd: (1)-->(2-5)
 │    └── filters
 │         └── k:1 = x:8 [outer=(1,8), constraints=(/1: (/NULL - ]; /8: (/NULL - ]), fd=(1)==(8), (8)==(1)]
 └── filters
      └── (i:2 = 100) OR (i:2 IS NULL) [outer=(2), constraints=(/2: [/NULL - /NULL] [/100 - /100]; tight)]

# Don't push down conditions in case of FULL JOIN.
norm
SELECT * FROM a FULL JOIN xy ON a.k=xy.x WHERE a.i=100 OR a.i IS NULL
----
select
 ├── columns: k:1 i:2 f:3 s:4 j:5 x:8 y:9
 ├── key: (1,8)
 ├── fd: (1)-->(2-5), (8)-->(9)
 ├── full-join (hash)
 │    ├── columns: k:1 i:2 f:3 s:4 j:5 x:8 y:9
 │    ├── multiplicity: left-rows(exactly-one), right-rows(exactly-one)
 │    ├── key: (1,8)
 │    ├── fd: (1)-->(2-5), (8)-->(9)
 │    ├── scan a
 │    │    ├── columns: k:1!null i:2 f:3 s:4 j:5
 │    │    ├── key: (1)
 │    │    └── fd: (1)-->(2-5)
 │    ├── scan xy
 │    │    ├── columns: x:8!null y:9
 │    │    ├── key: (8)
 │    │    └── fd: (8)-->(9)
 │    └── filters
 │         └── k:1 = x:8 [outer=(1,8), constraints=(/1: (/NULL - ]; /8: (/NULL - ]), fd=(1)==(8), (8)==(1)]
 └── filters
      └── (i:2 = 100) OR (i:2 IS NULL) [outer=(2), constraints=(/2: [/NULL - /NULL] [/100 - /100]; tight)]

# Push into semi-join.
norm expect=PushSelectIntoJoinLeft
SELECT * FROM a WHERE EXISTS(SELECT * FROM xy WHERE k=x) AND a.i=0
----
semi-join (hash)
 ├── columns: k:1!null i:2!null f:3 s:4 j:5
 ├── key: (1)
 ├── fd: ()-->(2), (1)-->(3-5)
 ├── select
 │    ├── columns: k:1!null i:2!null f:3 s:4 j:5
 │    ├── key: (1)
 │    ├── fd: ()-->(2), (1)-->(3-5)
 │    ├── scan a
 │    │    ├── columns: k:1!null i:2 f:3 s:4 j:5
 │    │    ├── key: (1)
 │    │    └── fd: (1)-->(2-5)
 │    └── filters
 │         └── i:2 = 0 [outer=(2), constraints=(/2: [/0 - /0]; tight), fd=()-->(2)]
 ├── scan xy
 │    ├── columns: x:8!null
 │    └── key: (8)
 └── filters
      └── k:1 = x:8 [outer=(1,8), constraints=(/1: (/NULL - ]; /8: (/NULL - ]), fd=(1)==(8), (8)==(1)]

# Push into anti-join.
norm expect=PushSelectIntoJoinLeft
SELECT * FROM a WHERE NOT EXISTS(SELECT * FROM xy WHERE k=x) AND a.i=0
----
anti-join (hash)
 ├── columns: k:1!null i:2!null f:3 s:4 j:5
 ├── key: (1)
 ├── fd: ()-->(2), (1)-->(3-5)
 ├── select
 │    ├── columns: k:1!null i:2!null f:3 s:4 j:5
 │    ├── key: (1)
 │    ├── fd: ()-->(2), (1)-->(3-5)
 │    ├── scan a
 │    │    ├── columns: k:1!null i:2 f:3 s:4 j:5
 │    │    ├── key: (1)
 │    │    └── fd: (1)-->(2-5)
 │    └── filters
 │         └── i:2 = 0 [outer=(2), constraints=(/2: [/0 - /0]; tight), fd=()-->(2)]
 ├── scan xy
 │    ├── columns: x:8!null
 │    └── key: (8)
 └── filters
      └── k:1 = x:8 [outer=(1,8), constraints=(/1: (/NULL - ]; /8: (/NULL - ]), fd=(1)==(8), (8)==(1)]

# Don't push down conditions in case of LEFT JOIN.
norm
SELECT * FROM xy LEFT JOIN a ON a.k=xy.x WHERE a.i=100 OR a.i IS NULL
----
select
 ├── columns: x:1!null y:2 k:5 i:6 f:7 s:8 j:9
 ├── key: (1)
 ├── fd: (1)-->(2,5-9), (5)-->(6-9)
 ├── left-join (hash)
 │    ├── columns: x:1!null y:2 k:5 i:6 f:7 s:8 j:9
 │    ├── multiplicity: left-rows(exactly-one), right-rows(zero-or-one)
 │    ├── key: (1)
 │    ├── fd: (1)-->(2,5-9), (5)-->(6-9)
 │    ├── scan xy
 │    │    ├── columns: x:1!null y:2
 │    │    ├── key: (1)
 │    │    └── fd: (1)-->(2)
 │    ├── scan a
 │    │    ├── columns: k:5!null i:6 f:7 s:8 j:9
 │    │    ├── key: (5)
 │    │    └── fd: (5)-->(6-9)
 │    └── filters
 │         └── k:5 = x:1 [outer=(1,5), constraints=(/1: (/NULL - ]; /5: (/NULL - ]), fd=(1)==(5), (5)==(1)]
 └── filters
      └── (i:6 = 100) OR (i:6 IS NULL) [outer=(6), constraints=(/6: [/NULL - /NULL] [/100 - /100]; tight)]

# Don't push down conditions in case of FULL JOIN.
norm
SELECT * FROM xy FULL JOIN a ON a.k=xy.x WHERE a.i=100 OR a.i IS NULL
----
select
 ├── columns: x:1 y:2 k:5 i:6 f:7 s:8 j:9
 ├── key: (1,5)
 ├── fd: (1)-->(2), (5)-->(6-9)
 ├── full-join (hash)
 │    ├── columns: x:1 y:2 k:5 i:6 f:7 s:8 j:9
 │    ├── multiplicity: left-rows(exactly-one), right-rows(exactly-one)
 │    ├── key: (1,5)
 │    ├── fd: (1)-->(2), (5)-->(6-9)
 │    ├── scan xy
 │    │    ├── columns: x:1!null y:2
 │    │    ├── key: (1)
 │    │    └── fd: (1)-->(2)
 │    ├── scan a
 │    │    ├── columns: k:5!null i:6 f:7 s:8 j:9
 │    │    ├── key: (5)
 │    │    └── fd: (5)-->(6-9)
 │    └── filters
 │         └── k:5 = x:1 [outer=(1,5), constraints=(/1: (/NULL - ]; /5: (/NULL - ]), fd=(1)==(5), (5)==(1)]
 └── filters
      └── (i:6 = 100) OR (i:6 IS NULL) [outer=(6), constraints=(/6: [/NULL - /NULL] [/100 - /100]; tight)]

# --------------------------------------------------
# MergeSelectInnerJoin
# --------------------------------------------------
norm expect=MergeSelectInnerJoin
SELECT * FROM a, xy WHERE a.k=xy.x AND (a.s='foo' OR xy.y<100)
----
inner-join (hash)
 ├── columns: k:1!null i:2 f:3 s:4 j:5 x:8!null y:9
 ├── multiplicity: left-rows(zero-or-one), right-rows(zero-or-one)
 ├── key: (8)
 ├── fd: (1)-->(2-5), (8)-->(9), (1)==(8), (8)==(1)
 ├── scan a
 │    ├── columns: k:1!null i:2 f:3 s:4 j:5
 │    ├── key: (1)
 │    └── fd: (1)-->(2-5)
 ├── scan xy
 │    ├── columns: x:8!null y:9
 │    ├── key: (8)
 │    └── fd: (8)-->(9)
 └── filters
      ├── k:1 = x:8 [outer=(1,8), constraints=(/1: (/NULL - ]; /8: (/NULL - ]), fd=(1)==(8), (8)==(1)]
      └── (s:4 = 'foo') OR (y:9 < 100) [outer=(4,9)]

norm expect=MergeSelectInnerJoin
SELECT * FROM a INNER JOIN xy ON a.k=xy.x WHERE (a.s='foo' OR xy.y<100)
----
inner-join (hash)
 ├── columns: k:1!null i:2 f:3 s:4 j:5 x:8!null y:9
 ├── multiplicity: left-rows(zero-or-one), right-rows(zero-or-one)
 ├── key: (8)
 ├── fd: (1)-->(2-5), (8)-->(9), (1)==(8), (8)==(1)
 ├── scan a
 │    ├── columns: k:1!null i:2 f:3 s:4 j:5
 │    ├── key: (1)
 │    └── fd: (1)-->(2-5)
 ├── scan xy
 │    ├── columns: x:8!null y:9
 │    ├── key: (8)
 │    └── fd: (8)-->(9)
 └── filters
      ├── k:1 = x:8 [outer=(1,8), constraints=(/1: (/NULL - ]; /8: (/NULL - ]), fd=(1)==(8), (8)==(1)]
      └── (s:4 = 'foo') OR (y:9 < 100) [outer=(4,9)]

norm expect=MergeSelectInnerJoin
SELECT * FROM a INNER JOIN xy ON a.k=xy.x WHERE False
----
values
 ├── columns: k:1!null i:2!null f:3!null s:4!null j:5!null x:8!null y:9!null
 ├── cardinality: [0 - 0]
 ├── key: ()
 └── fd: ()-->(1-5,8,9)

# Don't merge with LEFT JOIN.
norm expect-not=MergeSelectInnerJoin
SELECT * FROM a LEFT JOIN xy ON True WHERE a.k=xy.x OR xy.x IS NULL
----
select
 ├── columns: k:1!null i:2 f:3 s:4 j:5 x:8 y:9
 ├── key: (1,8)
 ├── fd: (1)-->(2-5), (8)-->(9)
 ├── left-join (cross)
 │    ├── columns: k:1!null i:2 f:3 s:4 j:5 x:8 y:9
 │    ├── key: (1,8)
 │    ├── fd: (1)-->(2-5), (8)-->(9)
 │    ├── scan a
 │    │    ├── columns: k:1!null i:2 f:3 s:4 j:5
 │    │    ├── key: (1)
 │    │    └── fd: (1)-->(2-5)
 │    ├── scan xy
 │    │    ├── columns: x:8!null y:9
 │    │    ├── key: (8)
 │    │    └── fd: (8)-->(9)
 │    └── filters (true)
 └── filters
      └── (k:1 = x:8) OR (x:8 IS NULL) [outer=(1,8)]

# Don't merge with RIGHT JOIN.
norm expect-not=MergeSelectInnerJoin
SELECT * FROM a RIGHT JOIN xy ON True WHERE a.k=xy.x OR a.k IS NULL
----
select
 ├── columns: k:1 i:2 f:3 s:4 j:5 x:8!null y:9
 ├── key: (1,8)
 ├── fd: (8)-->(9), (1)-->(2-5)
 ├── left-join (cross)
 │    ├── columns: k:1 i:2 f:3 s:4 j:5 x:8!null y:9
 │    ├── key: (1,8)
 │    ├── fd: (8)-->(9), (1)-->(2-5)
 │    ├── scan xy
 │    │    ├── columns: x:8!null y:9
 │    │    ├── key: (8)
 │    │    └── fd: (8)-->(9)
 │    ├── scan a
 │    │    ├── columns: k:1!null i:2 f:3 s:4 j:5
 │    │    ├── key: (1)
 │    │    └── fd: (1)-->(2-5)
 │    └── filters (true)
 └── filters
      └── (k:1 = x:8) OR (k:1 IS NULL) [outer=(1,8)]

# Don't merge with FULL JOIN.
norm expect-not=MergeSelectInnerJoin
SELECT * FROM a FULL JOIN xy ON True WHERE a.k=xy.x OR a.k IS NULL OR xy.x IS NULL
----
select
 ├── columns: k:1 i:2 f:3 s:4 j:5 x:8 y:9
 ├── key: (1,8)
 ├── fd: (1)-->(2-5), (8)-->(9)
 ├── full-join (cross)
 │    ├── columns: k:1 i:2 f:3 s:4 j:5 x:8 y:9
 │    ├── key: (1,8)
 │    ├── fd: (1)-->(2-5), (8)-->(9)
 │    ├── scan a
 │    │    ├── columns: k:1!null i:2 f:3 s:4 j:5
 │    │    ├── key: (1)
 │    │    └── fd: (1)-->(2-5)
 │    ├── scan xy
 │    │    ├── columns: x:8!null y:9
 │    │    ├── key: (8)
 │    │    └── fd: (8)-->(9)
 │    └── filters (true)
 └── filters
      └── ((k:1 = x:8) OR (k:1 IS NULL)) OR (x:8 IS NULL) [outer=(1,8)]

# --------------------------------------------------
# PushSelectIntoJoinLeft + MergeSelectInnerJoin
# --------------------------------------------------
norm
SELECT * FROM a INNER JOIN xy ON a.k=xy.x WHERE a.f=1.1 AND s='foo' AND xy.y=10 AND a.i<xy.y
----
inner-join (hash)
 ├── columns: k:1!null i:2!null f:3!null s:4!null j:5 x:8!null y:9!null
 ├── multiplicity: left-rows(zero-or-one), right-rows(zero-or-one)
 ├── key: (8)
 ├── fd: ()-->(3,4,9), (1)-->(2,5), (1)==(8), (8)==(1)
 ├── select
 │    ├── columns: k:1!null i:2!null f:3!null s:4!null j:5
 │    ├── key: (1)
 │    ├── fd: ()-->(3,4), (1)-->(2,5)
 │    ├── scan a
 │    │    ├── columns: k:1!null i:2 f:3 s:4 j:5
 │    │    ├── key: (1)
 │    │    └── fd: (1)-->(2-5)
 │    └── filters
 │         ├── f:3 = 1.1 [outer=(3), constraints=(/3: [/1.1 - /1.1]; tight), fd=()-->(3)]
 │         ├── s:4 = 'foo' [outer=(4), constraints=(/4: [/'foo' - /'foo']; tight), fd=()-->(4)]
 │         └── i:2 < 10 [outer=(2), constraints=(/2: (/NULL - /9]; tight)]
 ├── select
 │    ├── columns: x:8!null y:9!null
 │    ├── key: (8)
 │    ├── fd: ()-->(9)
 │    ├── scan xy
 │    │    ├── columns: x:8!null y:9
 │    │    ├── key: (8)
 │    │    └── fd: (8)-->(9)
 │    └── filters
 │         └── y:9 = 10 [outer=(9), constraints=(/9: [/10 - /10]; tight), fd=()-->(9)]
 └── filters
      └── k:1 = x:8 [outer=(1,8), constraints=(/1: (/NULL - ]; /8: (/NULL - ]), fd=(1)==(8), (8)==(1)]

norm
SELECT * FROM a, xy WHERE a.i=100 AND $1>'2000-01-01T1:00:00' AND xy.x=a.k
----
inner-join (hash)
 ├── columns: k:1!null i:2!null f:3 s:4 j:5 x:8!null y:9
 ├── multiplicity: left-rows(zero-or-one), right-rows(zero-or-one)
 ├── has-placeholder
 ├── key: (8)
 ├── fd: ()-->(2), (1)-->(3-5), (8)-->(9), (1)==(8), (8)==(1)
 ├── select
 │    ├── columns: k:1!null i:2!null f:3 s:4 j:5
 │    ├── has-placeholder
 │    ├── key: (1)
 │    ├── fd: ()-->(2), (1)-->(3-5)
 │    ├── scan a
 │    │    ├── columns: k:1!null i:2 f:3 s:4 j:5
 │    │    ├── key: (1)
 │    │    └── fd: (1)-->(2-5)
 │    └── filters
 │         ├── $1 > '2000-01-01T1:00:00'
 │         └── i:2 = 100 [outer=(2), constraints=(/2: [/100 - /100]; tight), fd=()-->(2)]
 ├── select
 │    ├── columns: x:8!null y:9
 │    ├── has-placeholder
 │    ├── key: (8)
 │    ├── fd: (8)-->(9)
 │    ├── scan xy
 │    │    ├── columns: x:8!null y:9
 │    │    ├── key: (8)
 │    │    └── fd: (8)-->(9)
 │    └── filters
 │         └── $1 > '2000-01-01T1:00:00'
 └── filters
      └── x:8 = k:1 [outer=(1,8), constraints=(/1: (/NULL - ]; /8: (/NULL - ]), fd=(1)==(8), (8)==(1)]

# --------------------------------------------------
# PushSelectIntoGroupBy
# --------------------------------------------------

# Push down into GroupBy with aggregations.
norm expect=PushSelectIntoGroupBy
SELECT * FROM (SELECT i, count(*) FROM a GROUP BY i) a WHERE i=1
----
group-by
 ├── columns: i:2!null count:8!null
 ├── cardinality: [0 - 1]
 ├── key: ()
 ├── fd: ()-->(2,8)
 ├── select
 │    ├── columns: i:2!null
 │    ├── fd: ()-->(2)
 │    ├── scan a
 │    │    └── columns: i:2
 │    └── filters
 │         └── i:2 = 1 [outer=(2), constraints=(/2: [/1 - /1]; tight), fd=()-->(2)]
 └── aggregations
      ├── count-rows [as=count_rows:8]
      └── const-agg [as=i:2, outer=(2)]
           └── i:2

# Push down into GroupBy with no aggregations.
norm expect=PushSelectIntoGroupBy
SELECT * FROM (SELECT i FROM a GROUP BY i) a WHERE i=1
----
limit
 ├── columns: i:2!null
 ├── cardinality: [0 - 1]
 ├── key: ()
 ├── fd: ()-->(2)
 ├── select
 │    ├── columns: i:2!null
 │    ├── fd: ()-->(2)
 │    ├── limit hint: 1.00
 │    ├── scan a
 │    │    ├── columns: i:2
 │    │    └── limit hint: 100.00
 │    └── filters
 │         └── i:2 = 1 [outer=(2), constraints=(/2: [/1 - /1]; tight), fd=()-->(2)]
 └── 1

# Push down only conditions that do not depend on aggregations.
norm expect=PushSelectIntoGroupBy
SELECT * FROM (SELECT k, i, max(s) m FROM a GROUP BY k, i) a WHERE i=k AND m='foo'
----
select
 ├── columns: k:1!null i:2!null m:8!null
 ├── key: (1)
 ├── fd: ()-->(8), (1)==(2), (2)==(1), (1)-->(2)
 ├── group-by
 │    ├── columns: k:1!null i:2!null max:8
 │    ├── grouping columns: k:1!null
 │    ├── key: (1)
 │    ├── fd: (1)==(2), (2)==(1), (1)-->(2,8)
 │    ├── select
 │    │    ├── columns: k:1!null i:2!null s:4
 │    │    ├── key: (1)
 │    │    ├── fd: (1)-->(4), (1)==(2), (2)==(1)
 │    │    ├── scan a
 │    │    │    ├── columns: k:1!null i:2 s:4
 │    │    │    ├── key: (1)
 │    │    │    └── fd: (1)-->(2,4)
 │    │    └── filters
 │    │         └── i:2 = k:1 [outer=(1,2), constraints=(/1: (/NULL - ]; /2: (/NULL - ]), fd=(1)==(2), (2)==(1)]
 │    └── aggregations
 │         ├── max [as=max:8, outer=(4)]
 │         │    └── s:4
 │         └── const-agg [as=i:2, outer=(2)]
 │              └── i:2
 └── filters
      └── max:8 = 'foo' [outer=(8), constraints=(/8: [/'foo' - /'foo']; tight), fd=()-->(8)]

# DistinctOn case.
norm expect=PushSelectIntoGroupBy
SELECT * FROM (SELECT DISTINCT ON (i, f) i, s, f FROM a) WHERE i>f
----
distinct-on
 ├── columns: i:2!null s:4 f:3!null
 ├── grouping columns: i:2!null f:3!null
 ├── key: (2,3)
 ├── fd: (2,3)-->(4)
 ├── select
 │    ├── columns: i:2!null f:3!null s:4
 │    ├── scan a
 │    │    └── columns: i:2 f:3 s:4
 │    └── filters
 │         └── i:2 > f:3 [outer=(2,3), constraints=(/2: (/NULL - ]; /3: (/NULL - ])]
 └── aggregations
      └── first-agg [as=s:4, outer=(4)]
           └── s:4

# DistinctOn case with a ConstAgg.
norm expect=PushSelectIntoGroupBy
SELECT * FROM (SELECT DISTINCT ON (k, f, s) k, i, f, x FROM a JOIN xy ON i=y) WHERE k > f
----
distinct-on
 ├── columns: k:1!null i:2!null f:3!null x:8!null
 ├── grouping columns: k:1!null
 ├── key: (1)
 ├── fd: (1)-->(2,3,8), (8)-->(2)
 ├── inner-join (hash)
 │    ├── columns: k:1!null i:2!null f:3!null x:8!null y:9!null
 │    ├── key: (1,8)
 │    ├── fd: (1)-->(2,3), (8)-->(9), (2)==(9), (9)==(2)
 │    ├── select
 │    │    ├── columns: k:1!null i:2 f:3!null
 │    │    ├── key: (1)
 │    │    ├── fd: (1)-->(2,3)
 │    │    ├── scan a
 │    │    │    ├── columns: k:1!null i:2 f:3
 │    │    │    ├── key: (1)
 │    │    │    └── fd: (1)-->(2,3)
 │    │    └── filters
 │    │         └── k:1 > f:3 [outer=(1,3), constraints=(/1: (/NULL - ]; /3: (/NULL - ])]
 │    ├── scan xy
 │    │    ├── columns: x:8!null y:9
 │    │    ├── key: (8)
 │    │    └── fd: (8)-->(9)
 │    └── filters
 │         └── i:2 = y:9 [outer=(2,9), constraints=(/2: (/NULL - ]; /9: (/NULL - ]), fd=(2)==(9), (9)==(2)]
 └── aggregations
      ├── first-agg [as=i:2, outer=(2)]
      │    └── i:2
      ├── first-agg [as=x:8, outer=(8)]
      │    └── x:8
      └── const-agg [as=f:3, outer=(3)]
           └── f:3

# Do *not* push down into scalar GroupBy.
norm expect-not=PushSelectIntoGroupBy
SELECT * FROM (SELECT count(*) c FROM a) a WHERE $1<'2000-01-01T10:00:00' AND c=0
----
select
 ├── columns: c:8!null
 ├── cardinality: [0 - 1]
 ├── has-placeholder
 ├── key: ()
 ├── fd: ()-->(8)
 ├── scalar-group-by
 │    ├── columns: count_rows:8!null
 │    ├── cardinality: [1 - 1]
 │    ├── key: ()
 │    ├── fd: ()-->(8)
 │    ├── scan a
 │    └── aggregations
 │         └── count-rows [as=count_rows:8]
 └── filters
      ├── $1 < '2000-01-01T10:00:00'
      └── count_rows:8 = 0 [outer=(8), constraints=(/8: [/0 - /0]; tight), fd=()-->(8)]

# --------------------------------------------------
# RemoveNotNullCondition
# --------------------------------------------------
exec-ddl
CREATE TABLE b (k INT PRIMARY KEY, i INT, f FLOAT, s STRING NOT NULL, j JSON)
----

norm expect=RemoveNotNullCondition
SELECT k FROM b WHERE k IS NOT NULL AND k > 4
----
select
 ├── columns: k:1!null
 ├── key: (1)
 ├── scan b
 │    ├── columns: k:1!null
 │    └── key: (1)
 └── filters
      └── k:1 > 4 [outer=(1), constraints=(/1: [/5 - ]; tight)]

norm
SELECT k FROM b WHERE k IS NULL
----
select
 ├── columns: k:1!null
 ├── cardinality: [0 - 1]
 ├── key: ()
 ├── fd: ()-->(1)
 ├── scan b
 │    ├── columns: k:1!null
 │    └── key: (1)
 └── filters
      └── k:1 IS NULL [outer=(1), constraints=(/1: [/NULL - /NULL]; tight), fd=()-->(1)]

norm expect=RemoveNotNullCondition
SELECT k,i FROM b WHERE k IS NOT NULL AND k > 4 AND i < 100 AND i IS NOT NULL
----
select
 ├── columns: k:1!null i:2!null
 ├── key: (1)
 ├── fd: (1)-->(2)
 ├── scan b
 │    ├── columns: k:1!null i:2
 │    ├── key: (1)
 │    └── fd: (1)-->(2)
 └── filters
      ├── (i:2 < 100) AND (i:2 IS NOT NULL) [outer=(2), constraints=(/2: (/NULL - /99]; tight)]
      └── k:1 > 4 [outer=(1), constraints=(/1: [/5 - ]; tight)]

norm expect=RemoveNotNullCondition
SELECT k,s FROM b WHERE k IS NOT NULL AND s IS NOT NULL
----
scan b
 ├── columns: k:1!null s:4!null
 ├── key: (1)
 └── fd: (1)-->(4)

# RemoveNotNullCondition partially applied
norm expect=RemoveNotNullCondition
SELECT k,s,i FROM b WHERE k IS NOT NULL AND s IS NOT NULL AND i IS NOT NULL
----
select
 ├── columns: k:1!null s:4!null i:2!null
 ├── key: (1)
 ├── fd: (1)-->(2,4)
 ├── scan b
 │    ├── columns: k:1!null i:2 s:4!null
 │    ├── key: (1)
 │    └── fd: (1)-->(2,4)
 └── filters
      └── i:2 IS NOT NULL [outer=(2), constraints=(/2: (/NULL - ]; tight)]

# RemoveNotNullCondition rule is not applied
norm expect-not=RemoveNotNullCondition
SELECT i FROM b WHERE i IS NOT NULL
----
select
 ├── columns: i:2!null
 ├── scan b
 │    └── columns: i:2
 └── filters
      └── i:2 IS NOT NULL [outer=(2), constraints=(/2: (/NULL - ]; tight)]

# RemoveNotNullCondition rule is not applied
norm expect-not=RemoveNotNullCondition
SELECT k FROM b WHERE i+k IS NOT NULL
----
project
 ├── columns: k:1!null
 ├── immutable
 ├── key: (1)
 └── select
      ├── columns: k:1!null i:2
      ├── immutable
      ├── key: (1)
      ├── fd: (1)-->(2)
      ├── scan b
      │    ├── columns: k:1!null i:2
      │    ├── key: (1)
      │    └── fd: (1)-->(2)
      └── filters
           └── (i:2 + k:1) IS NOT NULL [outer=(1,2), immutable]

exec-ddl
CREATE INDEX partial_idx ON b (f) WHERE s IS NOT NULL
----

# Building the partial index predicate should trigger the rule.
norm expect=RemoveNotNullCondition
SELECT * FROM b
----
scan b
 ├── columns: k:1!null i:2 f:3 s:4!null j:5
 ├── partial index predicates
 │    └── partial_idx: filters (true)
 ├── key: (1)
 └── fd: (1)-->(2-5)

exec-ddl
DROP INDEX partial_idx
----

# --------------------------------------------------
# PushSelectIntoProjectSet
# --------------------------------------------------
norm expect=PushSelectIntoProjectSet
SELECT k, g FROM a, generate_series(0, a.k, 10) AS g WHERE k = 1
----
project-set
 ├── columns: k:1!null g:8
 ├── immutable
 ├── fd: ()-->(1)
 ├── select
 │    ├── columns: k:1!null
 │    ├── cardinality: [0 - 1]
 │    ├── key: ()
 │    ├── fd: ()-->(1)
 │    ├── scan a
 │    │    ├── columns: k:1!null
 │    │    └── key: (1)
 │    └── filters
 │         └── k:1 = 1 [outer=(1), constraints=(/1: [/1 - /1]; tight), fd=()-->(1)]
 └── zip
      └── generate_series(0, k:1, 10) [outer=(1), immutable]

# Make sure that filters aren't pushed down when not bound by the input, so PushSelectIntoProjectSet is not triggered.
norm expect-not=PushSelectIntoProjectSet
SELECT k, g FROM a, generate_series(0, a.k, 10) AS g WHERE g > 1
----
select
 ├── columns: k:1!null g:8!null
 ├── immutable
 ├── project-set
 │    ├── columns: k:1!null generate_series:8
 │    ├── immutable
 │    ├── scan a
 │    │    ├── columns: k:1!null
 │    │    └── key: (1)
 │    └── zip
 │         └── generate_series(0, k:1, 10) [outer=(1), immutable]
 └── filters
      └── generate_series:8 > 1 [outer=(8), constraints=(/8: [/2 - ]; tight)]

# Expect that only the applicable filters are pushed down into the project-set.
norm expect=PushSelectIntoProjectSet
SELECT k, g FROM a, generate_series(0, a.k, 10) AS g WHERE g > 1 AND k = 1
----
select
 ├── columns: k:1!null g:8!null
 ├── immutable
 ├── fd: ()-->(1)
 ├── project-set
 │    ├── columns: k:1!null generate_series:8
 │    ├── immutable
 │    ├── fd: ()-->(1)
 │    ├── select
 │    │    ├── columns: k:1!null
 │    │    ├── cardinality: [0 - 1]
 │    │    ├── key: ()
 │    │    ├── fd: ()-->(1)
 │    │    ├── scan a
 │    │    │    ├── columns: k:1!null
 │    │    │    └── key: (1)
 │    │    └── filters
 │    │         └── k:1 = 1 [outer=(1), constraints=(/1: [/1 - /1]; tight), fd=()-->(1)]
 │    └── zip
 │         └── generate_series(0, k:1, 10) [outer=(1), immutable]
 └── filters
      └── generate_series:8 > 1 [outer=(8), constraints=(/8: [/2 - ]; tight)]


# --------------------------------------------------
# PushFilterIntoSetOp
# --------------------------------------------------

norm expect=PushFilterIntoSetOp
SELECT k FROM
  ((SELECT k FROM b)
  UNION ALL
  (SELECT k FROM b))
WHERE k < 10
----
union-all
 ├── columns: k:15!null
 ├── left columns: b.k:1
 ├── right columns: b.k:8
 ├── select
 │    ├── columns: b.k:1!null
 │    ├── key: (1)
 │    ├── scan b
 │    │    ├── columns: b.k:1!null
 │    │    └── key: (1)
 │    └── filters
 │         └── b.k:1 < 10 [outer=(1), constraints=(/1: (/NULL - /9]; tight)]
 └── select
      ├── columns: b.k:8!null
      ├── key: (8)
      ├── scan b
      │    ├── columns: b.k:8!null
      │    └── key: (8)
      └── filters
           └── b.k:8 < 10 [outer=(8), constraints=(/8: (/NULL - /9]; tight)]

norm expect=PushFilterIntoSetOp
SELECT k FROM
((SELECT k FROM b)
  UNION
  (SELECT i FROM a))
WHERE k < 10 AND k > 1
----
union
 ├── columns: k:15!null
 ├── left columns: b.k:1
 ├── right columns: a.i:9
 ├── key: (15)
 ├── select
 │    ├── columns: b.k:1!null
 │    ├── cardinality: [0 - 8]
 │    ├── key: (1)
 │    ├── scan b
 │    │    ├── columns: b.k:1!null
 │    │    └── key: (1)
 │    └── filters
 │         └── (b.k:1 < 10) AND (b.k:1 > 1) [outer=(1), constraints=(/1: [/2 - /9]; tight)]
 └── select
      ├── columns: a.i:9!null
      ├── scan a
      │    └── columns: a.i:9
      └── filters
           └── (a.i:9 < 10) AND (a.i:9 > 1) [outer=(9), constraints=(/9: [/2 - /9]; tight)]

norm expect=PushFilterIntoSetOp
SELECT k FROM
((SELECT k FROM b)
  EXCEPT
  (SELECT i FROM a))
WHERE k < 10 AND k > 1
----
except-all
 ├── columns: k:1!null
 ├── left columns: b.k:1!null
 ├── right columns: a.i:9
 ├── cardinality: [0 - 8]
 ├── key: (1)
 ├── select
 │    ├── columns: b.k:1!null
 │    ├── cardinality: [0 - 8]
 │    ├── key: (1)
 │    ├── scan b
 │    │    ├── columns: b.k:1!null
 │    │    └── key: (1)
 │    └── filters
 │         └── (b.k:1 < 10) AND (b.k:1 > 1) [outer=(1), constraints=(/1: [/2 - /9]; tight)]
 └── select
      ├── columns: a.i:9!null
      ├── scan a
      │    └── columns: a.i:9
      └── filters
           └── (a.i:9 < 10) AND (a.i:9 > 1) [outer=(9), constraints=(/9: [/2 - /9]; tight)]

norm expect=PushFilterIntoSetOp
SELECT k FROM
((SELECT k FROM b)
  EXCEPT ALL
  (SELECT i FROM a))
WHERE k < 10 AND k > 1
----
except-all
 ├── columns: k:1!null
 ├── left columns: b.k:1!null
 ├── right columns: a.i:9
 ├── cardinality: [0 - 8]
 ├── key: (1)
 ├── select
 │    ├── columns: b.k:1!null
 │    ├── cardinality: [0 - 8]
 │    ├── key: (1)
 │    ├── scan b
 │    │    ├── columns: b.k:1!null
 │    │    └── key: (1)
 │    └── filters
 │         └── (b.k:1 < 10) AND (b.k:1 > 1) [outer=(1), constraints=(/1: [/2 - /9]; tight)]
 └── select
      ├── columns: a.i:9!null
      ├── scan a
      │    └── columns: a.i:9
      └── filters
           └── (a.i:9 < 10) AND (a.i:9 > 1) [outer=(9), constraints=(/9: [/2 - /9]; tight)]

norm expect=PushFilterIntoSetOp
SELECT k FROM
((SELECT k FROM b)
  INTERSECT
  (SELECT i FROM a))
WHERE k < 10 AND k > 1
----
intersect-all
 ├── columns: k:1!null
 ├── left columns: b.k:1!null
 ├── right columns: a.i:9
 ├── cardinality: [0 - 8]
 ├── key: (1)
 ├── select
 │    ├── columns: b.k:1!null
 │    ├── cardinality: [0 - 8]
 │    ├── key: (1)
 │    ├── scan b
 │    │    ├── columns: b.k:1!null
 │    │    └── key: (1)
 │    └── filters
 │         └── (b.k:1 < 10) AND (b.k:1 > 1) [outer=(1), constraints=(/1: [/2 - /9]; tight)]
 └── select
      ├── columns: a.i:9!null
      ├── scan a
      │    └── columns: a.i:9
      └── filters
           └── (a.i:9 < 10) AND (a.i:9 > 1) [outer=(9), constraints=(/9: [/2 - /9]; tight)]

norm expect=PushFilterIntoSetOp
SELECT k FROM
((SELECT k FROM b)
  INTERSECT ALL
  (SELECT i FROM a))
WHERE k < 10 AND k > 1
----
intersect-all
 ├── columns: k:1!null
 ├── left columns: b.k:1!null
 ├── right columns: a.i:9
 ├── cardinality: [0 - 8]
 ├── key: (1)
 ├── select
 │    ├── columns: b.k:1!null
 │    ├── cardinality: [0 - 8]
 │    ├── key: (1)
 │    ├── scan b
 │    │    ├── columns: b.k:1!null
 │    │    └── key: (1)
 │    └── filters
 │         └── (b.k:1 < 10) AND (b.k:1 > 1) [outer=(1), constraints=(/1: [/2 - /9]; tight)]
 └── select
      ├── columns: a.i:9!null
      ├── scan a
      │    └── columns: a.i:9
      └── filters
           └── (a.i:9 < 10) AND (a.i:9 > 1) [outer=(9), constraints=(/9: [/2 - /9]; tight)]

norm expect=PushFilterIntoSetOp
SELECT k FROM
((SELECT k FROM b)
  UNION
  (SELECT i FROM a))
WHERE k < 10 AND k > 1 AND random() < 0.5
----
union
 ├── columns: k:15!null
 ├── left columns: b.k:1
 ├── right columns: a.i:9
 ├── volatile
 ├── key: (15)
 ├── select
 │    ├── columns: b.k:1!null
 │    ├── cardinality: [0 - 8]
 │    ├── volatile
 │    ├── key: (1)
 │    ├── scan b
 │    │    ├── columns: b.k:1!null
 │    │    └── key: (1)
 │    └── filters
 │         ├── (b.k:1 < 10) AND (b.k:1 > 1) [outer=(1), constraints=(/1: [/2 - /9]; tight)]
 │         └── random() < 0.5 [volatile]
 └── select
      ├── columns: a.i:9!null
      ├── volatile
      ├── scan a
      │    └── columns: a.i:9
      └── filters
           ├── (a.i:9 < 10) AND (a.i:9 > 1) [outer=(9), constraints=(/9: [/2 - /9]; tight)]
           └── random() < 0.5 [volatile]

norm expect=PushFilterIntoSetOp
SELECT * FROM
  (SELECT k FROM b
    UNION ALL
  SELECT k FROM b) t1
  WHERE EXISTS(
  SELECT * FROM a WHERE k=1)
----
union-all
 ├── columns: k:15!null
 ├── left columns: b.k:1
 ├── right columns: b.k:8
 ├── select
 │    ├── columns: b.k:1!null
 │    ├── key: (1)
 │    ├── scan b
 │    │    ├── columns: b.k:1!null
 │    │    └── key: (1)
 │    └── filters
 │         └── exists [subquery]
 │              └── select
 │                   ├── columns: a.k:16!null a.i:17 a.f:18 a.s:19 a.j:20
 │                   ├── cardinality: [0 - 1]
 │                   ├── key: ()
 │                   ├── fd: ()-->(16-20)
 │                   ├── scan a
 │                   │    ├── columns: a.k:16!null a.i:17 a.f:18 a.s:19 a.j:20
 │                   │    ├── key: (16)
 │                   │    └── fd: (16)-->(17-20)
 │                   └── filters
 │                        └── a.k:16 = 1 [outer=(16), constraints=(/16: [/1 - /1]; tight), fd=()-->(16)]
 └── select
      ├── columns: b.k:8!null
      ├── key: (8)
      ├── scan b
      │    ├── columns: b.k:8!null
      │    └── key: (8)
      └── filters
           └── exists [subquery]
                └── select
                     ├── columns: a.k:16!null a.i:17 a.f:18 a.s:19 a.j:20
                     ├── cardinality: [0 - 1]
                     ├── key: ()
                     ├── fd: ()-->(16-20)
                     ├── scan a
                     │    ├── columns: a.k:16!null a.i:17 a.f:18 a.s:19 a.j:20
                     │    ├── key: (16)
                     │    └── fd: (16)-->(17-20)
                     └── filters
                          └── a.k:16 = 1 [outer=(16), constraints=(/16: [/1 - /1]; tight), fd=()-->(16)]

norm expect=PushFilterIntoSetOp
SELECT * FROM
(SELECT k FROM (SELECT k FROM b UNION ALL SELECT k FROM b)
  UNION ALL
  SELECT k FROM (SELECT k FROM b UNION ALL SELECT k FROM b)) t1
WHERE EXISTS(
  SELECT * FROM a WHERE k=1) AND random() < 0.5
----
union-all
 ├── columns: k:31!null
 ├── left columns: k:15
 ├── right columns: k:30
 ├── volatile
 ├── union-all
 │    ├── columns: k:15!null
 │    ├── left columns: b.k:1
 │    ├── right columns: b.k:8
 │    ├── volatile
 │    ├── select
 │    │    ├── columns: b.k:1!null
 │    │    ├── volatile
 │    │    ├── key: (1)
 │    │    ├── scan b
 │    │    │    ├── columns: b.k:1!null
 │    │    │    └── key: (1)
 │    │    └── filters
 │    │         ├── exists [subquery]
 │    │         │    └── select
 │    │         │         ├── columns: a.k:32!null a.i:33 a.f:34 a.s:35 a.j:36
 │    │         │         ├── cardinality: [0 - 1]
 │    │         │         ├── key: ()
 │    │         │         ├── fd: ()-->(32-36)
 │    │         │         ├── scan a
 │    │         │         │    ├── columns: a.k:32!null a.i:33 a.f:34 a.s:35 a.j:36
 │    │         │         │    ├── key: (32)
 │    │         │         │    └── fd: (32)-->(33-36)
 │    │         │         └── filters
 │    │         │              └── a.k:32 = 1 [outer=(32), constraints=(/32: [/1 - /1]; tight), fd=()-->(32)]
 │    │         └── random() < 0.5 [volatile]
 │    └── select
 │         ├── columns: b.k:8!null
 │         ├── volatile
 │         ├── key: (8)
 │         ├── scan b
 │         │    ├── columns: b.k:8!null
 │         │    └── key: (8)
 │         └── filters
 │              ├── exists [subquery]
 │              │    └── select
 │              │         ├── columns: a.k:32!null a.i:33 a.f:34 a.s:35 a.j:36
 │              │         ├── cardinality: [0 - 1]
 │              │         ├── key: ()
 │              │         ├── fd: ()-->(32-36)
 │              │         ├── scan a
 │              │         │    ├── columns: a.k:32!null a.i:33 a.f:34 a.s:35 a.j:36
 │              │         │    ├── key: (32)
 │              │         │    └── fd: (32)-->(33-36)
 │              │         └── filters
 │              │              └── a.k:32 = 1 [outer=(32), constraints=(/32: [/1 - /1]; tight), fd=()-->(32)]
 │              └── random() < 0.5 [volatile]
 └── union-all
      ├── columns: k:30!null
      ├── left columns: b.k:16
      ├── right columns: b.k:23
      ├── volatile
      ├── select
      │    ├── columns: b.k:16!null
      │    ├── volatile
      │    ├── key: (16)
      │    ├── scan b
      │    │    ├── columns: b.k:16!null
      │    │    └── key: (16)
      │    └── filters
      │         ├── exists [subquery]
      │         │    └── select
      │         │         ├── columns: a.k:32!null a.i:33 a.f:34 a.s:35 a.j:36
      │         │         ├── cardinality: [0 - 1]
      │         │         ├── key: ()
      │         │         ├── fd: ()-->(32-36)
      │         │         ├── scan a
      │         │         │    ├── columns: a.k:32!null a.i:33 a.f:34 a.s:35 a.j:36
      │         │         │    ├── key: (32)
      │         │         │    └── fd: (32)-->(33-36)
      │         │         └── filters
      │         │              └── a.k:32 = 1 [outer=(32), constraints=(/32: [/1 - /1]; tight), fd=()-->(32)]
      │         └── random() < 0.5 [volatile]
      └── select
           ├── columns: b.k:23!null
           ├── volatile
           ├── key: (23)
           ├── scan b
           │    ├── columns: b.k:23!null
           │    └── key: (23)
           └── filters
                ├── exists [subquery]
                │    └── select
                │         ├── columns: a.k:32!null a.i:33 a.f:34 a.s:35 a.j:36
                │         ├── cardinality: [0 - 1]
                │         ├── key: ()
                │         ├── fd: ()-->(32-36)
                │         ├── scan a
                │         │    ├── columns: a.k:32!null a.i:33 a.f:34 a.s:35 a.j:36
                │         │    ├── key: (32)
                │         │    └── fd: (32)-->(33-36)
                │         └── filters
                │              └── a.k:32 = 1 [outer=(32), constraints=(/32: [/1 - /1]; tight), fd=()-->(32)]
                └── random() < 0.5 [volatile]

# No-op case because the filter references outer columns.
norm expect-not=PushFilterIntoSetOp
SELECT
  (
    SELECT k
    FROM ((SELECT k FROM b) UNION ALL (SELECT k FROM b))
    WHERE k < i
  )
FROM a
----
project
 ├── columns: k:23
 ├── ensure-distinct-on
 │    ├── columns: a.k:1!null k:22
 │    ├── grouping columns: a.k:1!null
 │    ├── error: "more than one row returned by a subquery used as an expression"
 │    ├── key: (1)
 │    ├── fd: (1)-->(22)
 │    ├── left-join (cross)
 │    │    ├── columns: a.k:1!null a.i:2 k:22
 │    │    ├── fd: (1)-->(2)
 │    │    ├── scan a
 │    │    │    ├── columns: a.k:1!null a.i:2
 │    │    │    ├── key: (1)
 │    │    │    └── fd: (1)-->(2)
 │    │    ├── union-all
 │    │    │    ├── columns: k:22!null
 │    │    │    ├── left columns: b.k:8
 │    │    │    ├── right columns: b.k:15
 │    │    │    ├── scan b
 │    │    │    │    ├── columns: b.k:8!null
 │    │    │    │    └── key: (8)
 │    │    │    └── scan b
 │    │    │         ├── columns: b.k:15!null
 │    │    │         └── key: (15)
 │    │    └── filters
 │    │         └── k:22 < a.i:2 [outer=(2,22), constraints=(/2: (/NULL - ]; /22: (/NULL - ])]
 │    └── aggregations
 │         └── const-agg [as=k:22, outer=(22)]
 │              └── k:22
 └── projections
      └── k:22 [as=k:23, outer=(22)]

norm
SELECT * FROM ((values (1,2))
  EXCEPT (values (0,1)))
WHERE 1 / column1 > 0
----
except-all
 ├── columns: column1:1!null column2:2!null
 ├── left columns: column1:1!null column2:2!null
 ├── right columns: column1:3 column2:4
 ├── cardinality: [0 - 1]
 ├── immutable
 ├── key: ()
 ├── fd: ()-->(1,2)
 ├── values
 │    ├── columns: column1:1!null column2:2!null
 │    ├── cardinality: [1 - 1]
 │    ├── key: ()
 │    ├── fd: ()-->(1,2)
 │    └── (1, 2)
 └── select
      ├── columns: column1:3!null column2:4!null
      ├── cardinality: [0 - 1]
      ├── immutable
      ├── key: ()
      ├── fd: ()-->(3,4)
      ├── values
      │    ├── columns: column1:3!null column2:4!null
      │    ├── cardinality: [1 - 1]
      │    ├── key: ()
      │    ├── fd: ()-->(3,4)
      │    └── (0, 1)
      └── filters
           └── (1 / 0) > 0 [immutable]

# The filter is composite-sensitive.
norm expect-not=PushFilterIntoSetOp
SELECT * FROM ((VALUES (1.0::DECIMAL)) EXCEPT (VALUES (1.00::DECIMAL))) WHERE column1::STRING != '1.00';
----
select
 ├── columns: column1:1!null
 ├── cardinality: [0 - 1]
 ├── immutable
 ├── key: ()
 ├── fd: ()-->(1)
 ├── except-all
 │    ├── columns: column1:1!null
 │    ├── left columns: column1:1!null
 │    ├── right columns: column1:2
 │    ├── cardinality: [0 - 1]
 │    ├── key: ()
 │    ├── fd: ()-->(1)
 │    ├── values
 │    │    ├── columns: column1:1!null
 │    │    ├── cardinality: [1 - 1]
 │    │    ├── key: ()
 │    │    ├── fd: ()-->(1)
 │    │    └── (1.0,)
 │    └── values
 │         ├── columns: column1:2!null
 │         ├── cardinality: [1 - 1]
 │         ├── key: ()
 │         ├── fd: ()-->(2)
 │         └── (1.00,)
 └── filters
      └── column1:1::STRING != '1.00' [outer=(1), immutable]

# The filter is not composite-sensitive (even if it involves decimals) and can be pushed down.
norm expect=PushFilterIntoSetOp
SELECT * FROM ((VALUES (1.0::DECIMAL)) EXCEPT (VALUES (1.00::DECIMAL))) WHERE column1 > 0;
----
except-all
 ├── columns: column1:1!null
 ├── left columns: column1:1!null
 ├── right columns: column1:2
 ├── cardinality: [0 - 1]
 ├── key: ()
 ├── fd: ()-->(1)
 ├── values
 │    ├── columns: column1:1!null
 │    ├── cardinality: [1 - 1]
 │    ├── key: ()
 │    ├── fd: ()-->(1)
 │    └── (1.0,)
 └── values
      ├── columns: column1:2!null
      ├── cardinality: [1 - 1]
      ├── key: ()
      ├── fd: ()-->(2)
      └── (1.00,)