github.com/cockroachdb/cockroach@v20.2.0-alpha.1+incompatible/pkg/sql/opt/memo/testdata/stats/join

exec-ddl
CREATE TABLE xysd (x INT PRIMARY KEY, y INT, s STRING, d DECIMAL NOT NULL, UNIQUE (s DESC, d))
----

exec-ddl
CREATE TABLE uv (u INT, v INT NOT NULL)
----

exec-ddl
ALTER TABLE xysd INJECT STATISTICS '[
  {
    "columns": ["x"],
    "created_at": "2018-01-01 1:00:00.00000+00:00",
    "row_count": 5000,
    "distinct_count": 5000
  },
  {
    "columns": ["y"],
    "created_at": "2018-01-01 1:30:00.00000+00:00",
    "row_count": 5000,
    "distinct_count": 400
  }
]'
----

exec-ddl
ALTER TABLE uv INJECT STATISTICS '[
  {
    "columns": ["u"],
    "created_at": "2018-01-01 1:00:00.00000+00:00",
    "row_count": 10000,
    "distinct_count": 500
  },
  {
    "columns": ["v"],
    "created_at": "2018-01-01 1:30:00.00000+00:00",
    "row_count": 10000,
    "distinct_count": 100
  },
  {
    "columns": ["rowid"],
    "created_at": "2018-01-01 1:30:00.00000+00:00",
    "row_count": 10000,
    "distinct_count": 10000
  }
]'
----

norm
SELECT * FROM xysd JOIN uv ON true
----
inner-join (cross)
 ├── columns: x:1(int!null) y:2(int) s:3(string) d:4(decimal!null) u:5(int) v:6(int!null)
 ├── stats: [rows=50000000]
 ├── fd: (1)-->(2-4), (3,4)~~>(1,2)
 ├── scan xysd
 │    ├── columns: x:1(int!null) y:2(int) s:3(string) d:4(decimal!null)
 │    ├── stats: [rows=5000]
 │    ├── key: (1)
 │    └── fd: (1)-->(2-4), (3,4)~~>(1,2)
 ├── scan uv
 │    ├── columns: u:5(int) v:6(int!null)
 │    └── stats: [rows=10000]
 └── filters (true)

norm colstat=1 colstat=2 colstat=3 colstat=4 colstat=5 colstat=6 colstat=(2,5,6)
SELECT * FROM xysd JOIN uv ON true
----
inner-join (cross)
 ├── columns: x:1(int!null) y:2(int) s:3(string) d:4(decimal!null) u:5(int) v:6(int!null)
 ├── stats: [rows=50000000, distinct(1)=5000, null(1)=0, distinct(2)=400, null(2)=0, distinct(3)=500, null(3)=500000, distinct(4)=500, null(4)=0, distinct(5)=500, null(5)=0, distinct(6)=100, null(6)=0, distinct(2,5,6)=4000000, null(2,5,6)=0]
 ├── fd: (1)-->(2-4), (3,4)~~>(1,2)
 ├── scan xysd
 │    ├── columns: x:1(int!null) y:2(int) s:3(string) d:4(decimal!null)
 │    ├── stats: [rows=5000, distinct(1)=5000, null(1)=0, distinct(2)=400, null(2)=0, distinct(3)=500, null(3)=50, distinct(4)=500, null(4)=0]
 │    ├── key: (1)
 │    └── fd: (1)-->(2-4), (3,4)~~>(1,2)
 ├── scan uv
 │    ├── columns: u:5(int) v:6(int!null)
 │    └── stats: [rows=10000, distinct(5)=500, null(5)=0, distinct(6)=100, null(6)=0, distinct(5,6)=10000, null(5,6)=0]
 └── filters (true)

norm
SELECT * FROM xysd JOIN uv ON false
----
values
 ├── columns: x:1(int!null) y:2(int!null) s:3(string!null) d:4(decimal!null) u:5(int!null) v:6(int!null)
 ├── cardinality: [0 - 0]
 ├── stats: [rows=0]
 ├── key: ()
 └── fd: ()-->(1-6)

build colstat=2
SELECT *, rowid FROM xysd INNER JOIN uv ON x=u
----
inner-join (hash)
 ├── columns: x:1(int!null) y:2(int) s:3(string) d:4(decimal!null) u:5(int!null) v:6(int!null) rowid:7(int!null)
 ├── stats: [rows=10000, distinct(1)=500, null(1)=0, distinct(2)=400, null(2)=0, distinct(5)=500, null(5)=0]
 ├── key: (7)
 ├── fd: (1)-->(2-4), (3,4)~~>(1,2), (7)-->(5,6), (1)==(5), (5)==(1)
 ├── scan xysd
 │    ├── columns: x:1(int!null) y:2(int) s:3(string) d:4(decimal!null)
 │    ├── stats: [rows=5000, distinct(1)=5000, null(1)=0, distinct(2)=400, null(2)=0, distinct(4)=500, null(4)=0]
 │    ├── key: (1)
 │    └── fd: (1)-->(2-4), (3,4)~~>(1,2)
 ├── scan uv
 │    ├── columns: u:5(int) v:6(int!null) rowid:7(int!null)
 │    ├── stats: [rows=10000, distinct(5)=500, null(5)=0, distinct(6)=100, null(6)=0, distinct(7)=10000, null(7)=0]
 │    ├── key: (7)
 │    └── fd: (7)-->(5,6)
 └── filters
      └── x:1 = u:5 [type=bool, outer=(1,5), constraints=(/1: (/NULL - ]; /5: (/NULL - ]), fd=(1)==(5), (5)==(1)]

build
SELECT *, rowid FROM xysd LEFT JOIN uv ON x=u
----
left-join (hash)
 ├── columns: x:1(int!null) y:2(int) s:3(string) d:4(decimal!null) u:5(int) v:6(int) rowid:7(int)
 ├── stats: [rows=10000, distinct(5)=500, null(5)=0]
 ├── key: (1,7)
 ├── fd: (1)-->(2-4), (3,4)~~>(1,2), (7)-->(5,6)
 ├── scan xysd
 │    ├── columns: x:1(int!null) y:2(int) s:3(string) d:4(decimal!null)
 │    ├── stats: [rows=5000, distinct(1)=5000, null(1)=0, distinct(4)=500, null(4)=0]
 │    ├── key: (1)
 │    └── fd: (1)-->(2-4), (3,4)~~>(1,2)
 ├── scan uv
 │    ├── columns: u:5(int) v:6(int!null) rowid:7(int!null)
 │    ├── stats: [rows=10000, distinct(5)=500, null(5)=0]
 │    ├── key: (7)
 │    └── fd: (7)-->(5,6)
 └── filters
      └── x:1 = u:5 [type=bool, outer=(1,5), constraints=(/1: (/NULL - ]; /5: (/NULL - ]), fd=(1)==(5), (5)==(1)]

build
SELECT *, rowid FROM xysd RIGHT JOIN uv ON x=u
----
right-join (hash)
 ├── columns: x:1(int) y:2(int) s:3(string) d:4(decimal) u:5(int) v:6(int!null) rowid:7(int!null)
 ├── stats: [rows=10000, distinct(1)=500, null(1)=0]
 ├── key: (7)
 ├── fd: (1)-->(2-4), (3,4)~~>(1,2), (7)-->(1-6)
 ├── scan xysd
 │    ├── columns: x:1(int!null) y:2(int) s:3(string) d:4(decimal!null)
 │    ├── stats: [rows=5000, distinct(1)=5000, null(1)=0]
 │    ├── key: (1)
 │    └── fd: (1)-->(2-4), (3,4)~~>(1,2)
 ├── scan uv
 │    ├── columns: u:5(int) v:6(int!null) rowid:7(int!null)
 │    ├── stats: [rows=10000, distinct(5)=500, null(5)=0, distinct(6)=100, null(6)=0, distinct(7)=10000, null(7)=0]
 │    ├── key: (7)
 │    └── fd: (7)-->(5,6)
 └── filters
      └── x:1 = u:5 [type=bool, outer=(1,5), constraints=(/1: (/NULL - ]; /5: (/NULL - ]), fd=(1)==(5), (5)==(1)]

build
SELECT *, rowid FROM xysd FULL JOIN uv ON x=u
----
full-join (hash)
 ├── columns: x:1(int) y:2(int) s:3(string) d:4(decimal) u:5(int) v:6(int) rowid:7(int)
 ├── stats: [rows=10000]
 ├── key: (1,7)
 ├── fd: (1)-->(2-4), (3,4)~~>(1,2), (7)-->(5,6)
 ├── scan xysd
 │    ├── columns: x:1(int!null) y:2(int) s:3(string) d:4(decimal!null)
 │    ├── stats: [rows=5000, distinct(1)=5000, null(1)=0]
 │    ├── key: (1)
 │    └── fd: (1)-->(2-4), (3,4)~~>(1,2)
 ├── scan uv
 │    ├── columns: u:5(int) v:6(int!null) rowid:7(int!null)
 │    ├── stats: [rows=10000, distinct(5)=500, null(5)=0]
 │    ├── key: (7)
 │    └── fd: (7)-->(5,6)
 └── filters
      └── x:1 = u:5 [type=bool, outer=(1,5), constraints=(/1: (/NULL - ]; /5: (/NULL - ]), fd=(1)==(5), (5)==(1)]

build
SELECT * FROM xysd, uv
----
project
 ├── columns: x:1(int!null) y:2(int) s:3(string) d:4(decimal!null) u:5(int) v:6(int!null)
 ├── stats: [rows=50000000]
 ├── fd: (1)-->(2-4), (3,4)~~>(1,2)
 └── inner-join (cross)
      ├── columns: x:1(int!null) y:2(int) s:3(string) d:4(decimal!null) u:5(int) v:6(int!null) rowid:7(int!null)
      ├── stats: [rows=50000000]
      ├── key: (1,7)
      ├── fd: (1)-->(2-4), (3,4)~~>(1,2), (7)-->(5,6)
      ├── scan xysd
      │    ├── columns: x:1(int!null) y:2(int) s:3(string) d:4(decimal!null)
      │    ├── stats: [rows=5000]
      │    ├── key: (1)
      │    └── fd: (1)-->(2-4), (3,4)~~>(1,2)
      ├── scan uv
      │    ├── columns: u:5(int) v:6(int!null) rowid:7(int!null)
      │    ├── stats: [rows=10000]
      │    ├── key: (7)
      │    └── fd: (7)-->(5,6)
      └── filters (true)

build
SELECT * FROM xysd, xysd AS xysd
----
inner-join (cross)
 ├── columns: x:1(int!null) y:2(int) s:3(string) d:4(decimal!null) x:5(int!null) y:6(int) s:7(string) d:8(decimal!null)
 ├── stats: [rows=25000000]
 ├── key: (1,5)
 ├── fd: (1)-->(2-4), (3,4)~~>(1,2), (5)-->(6-8), (7,8)~~>(5,6)
 ├── scan xysd
 │    ├── columns: x:1(int!null) y:2(int) s:3(string) d:4(decimal!null)
 │    ├── stats: [rows=5000]
 │    ├── key: (1)
 │    └── fd: (1)-->(2-4), (3,4)~~>(1,2)
 ├── scan xysd
 │    ├── columns: x:5(int!null) y:6(int) s:7(string) d:8(decimal!null)
 │    ├── stats: [rows=5000]
 │    ├── key: (5)
 │    └── fd: (5)-->(6-8), (7,8)~~>(5,6)
 └── filters (true)

build
SELECT * FROM xysd, uv WHERE v = 5
----
project
 ├── columns: x:1(int!null) y:2(int) s:3(string) d:4(decimal!null) u:5(int) v:6(int!null)
 ├── stats: [rows=500000]
 ├── fd: ()-->(6), (1)-->(2-4), (3,4)~~>(1,2)
 └── select
      ├── columns: x:1(int!null) y:2(int) s:3(string) d:4(decimal!null) u:5(int) v:6(int!null) rowid:7(int!null)
      ├── stats: [rows=500000, distinct(6)=1, null(6)=0]
      ├── key: (1,7)
      ├── fd: ()-->(6), (1)-->(2-4), (3,4)~~>(1,2), (7)-->(5)
      ├── inner-join (cross)
      │    ├── columns: x:1(int!null) y:2(int) s:3(string) d:4(decimal!null) u:5(int) v:6(int!null) rowid:7(int!null)
      │    ├── stats: [rows=50000000, distinct(1)=5000, null(1)=0, distinct(4)=500, null(4)=0, distinct(6)=100, null(6)=0, distinct(7)=10000, null(7)=0]
      │    ├── key: (1,7)
      │    ├── fd: (1)-->(2-4), (3,4)~~>(1,2), (7)-->(5,6)
      │    ├── scan xysd
      │    │    ├── columns: x:1(int!null) y:2(int) s:3(string) d:4(decimal!null)
      │    │    ├── stats: [rows=5000, distinct(1)=5000, null(1)=0, distinct(4)=500, null(4)=0]
      │    │    ├── key: (1)
      │    │    └── fd: (1)-->(2-4), (3,4)~~>(1,2)
      │    ├── scan uv
      │    │    ├── columns: u:5(int) v:6(int!null) rowid:7(int!null)
      │    │    ├── stats: [rows=10000, distinct(6)=100, null(6)=0, distinct(7)=10000, null(7)=0]
      │    │    ├── key: (7)
      │    │    └── fd: (7)-->(5,6)
      │    └── filters (true)
      └── filters
           └── v:6 = 5 [type=bool, outer=(6), constraints=(/6: [/5 - /5]; tight), fd=()-->(6)]

# Force calculation of the distinct count for the column set spanning both
# tables in the join.
build
SELECT sum(v), x, v FROM xysd, uv GROUP BY x, v
----
group-by
 ├── columns: sum:8(decimal!null) x:1(int!null) v:6(int!null)
 ├── grouping columns: x:1(int!null) v:6(int!null)
 ├── stats: [rows=500000, distinct(1,6)=500000, null(1,6)=0]
 ├── key: (1,6)
 ├── fd: (1,6)-->(8)
 ├── project
 │    ├── columns: x:1(int!null) v:6(int!null)
 │    ├── stats: [rows=50000000, distinct(1,6)=500000, null(1,6)=0]
 │    └── inner-join (cross)
 │         ├── columns: x:1(int!null) y:2(int) s:3(string) d:4(decimal!null) u:5(int) v:6(int!null) rowid:7(int!null)
 │         ├── stats: [rows=50000000, distinct(1,6)=500000, null(1,6)=0]
 │         ├── key: (1,7)
 │         ├── fd: (1)-->(2-4), (3,4)~~>(1,2), (7)-->(5,6)
 │         ├── scan xysd
 │         │    ├── columns: x:1(int!null) y:2(int) s:3(string) d:4(decimal!null)
 │         │    ├── stats: [rows=5000, distinct(1)=5000, null(1)=0]
 │         │    ├── key: (1)
 │         │    └── fd: (1)-->(2-4), (3,4)~~>(1,2)
 │         ├── scan uv
 │         │    ├── columns: u:5(int) v:6(int!null) rowid:7(int!null)
 │         │    ├── stats: [rows=10000, distinct(6)=100, null(6)=0]
 │         │    ├── key: (7)
 │         │    └── fd: (7)-->(5,6)
 │         └── filters (true)
 └── aggregations
      └── sum [as=sum:8, type=decimal, outer=(6)]
           └── v:6 [type=int]

# Join selectivity: 1/max(distinct(x), distinct(u)) = 1/5000.
norm
SELECT sum(v), x, v FROM xysd, uv WHERE x=u GROUP BY x, v
----
group-by
 ├── columns: sum:8(decimal!null) x:1(int!null) v:6(int!null)
 ├── grouping columns: x:1(int!null) v:6(int!null)
 ├── stats: [rows=10000, distinct(1,6)=10000, null(1,6)=0]
 ├── key: (1,6)
 ├── fd: (1,6)-->(8)
 ├── inner-join (hash)
 │    ├── columns: x:1(int!null) u:5(int!null) v:6(int!null)
 │    ├── stats: [rows=10000, distinct(1)=500, null(1)=0, distinct(5)=500, null(5)=0, distinct(1,6)=10000, null(1,6)=0]
 │    ├── fd: (1)==(5), (5)==(1)
 │    ├── scan xysd
 │    │    ├── columns: x:1(int!null)
 │    │    ├── stats: [rows=5000, distinct(1)=5000, null(1)=0]
 │    │    └── key: (1)
 │    ├── scan uv
 │    │    ├── columns: u:5(int) v:6(int!null)
 │    │    └── stats: [rows=10000, distinct(5)=500, null(5)=0, distinct(6)=100, null(6)=0]
 │    └── filters
 │         └── x:1 = u:5 [type=bool, outer=(1,5), constraints=(/1: (/NULL - ]; /5: (/NULL - ]), fd=(1)==(5), (5)==(1)]
 └── aggregations
      └── sum [as=sum:8, type=decimal, outer=(6)]
           └── v:6 [type=int]

# Semi-join.
norm
SELECT * FROM xysd WHERE EXISTS (SELECT * FROM uv WHERE x=u)
----
semi-join (hash)
 ├── columns: x:1(int!null) y:2(int) s:3(string) d:4(decimal!null)
 ├── stats: [rows=500, distinct(1)=500, null(1)=0]
 ├── key: (1)
 ├── fd: (1)-->(2-4), (3,4)~~>(1,2)
 ├── scan xysd
 │    ├── columns: x:1(int!null) y:2(int) s:3(string) d:4(decimal!null)
 │    ├── stats: [rows=5000, distinct(1)=5000, null(1)=0, distinct(4)=500, null(4)=0]
 │    ├── key: (1)
 │    └── fd: (1)-->(2-4), (3,4)~~>(1,2)
 ├── scan uv
 │    ├── columns: u:5(int)
 │    └── stats: [rows=10000, distinct(5)=500, null(5)=0]
 └── filters
      └── x:1 = u:5 [type=bool, outer=(1,5), constraints=(/1: (/NULL - ]; /5: (/NULL - ]), fd=(1)==(5), (5)==(1)]

# Anti-join.
norm
SELECT * FROM xysd WHERE NOT EXISTS (SELECT * FROM uv WHERE x=u)
----
anti-join (hash)
 ├── columns: x:1(int!null) y:2(int) s:3(string) d:4(decimal!null)
 ├── stats: [rows=4500]
 ├── key: (1)
 ├── fd: (1)-->(2-4), (3,4)~~>(1,2)
 ├── scan xysd
 │    ├── columns: x:1(int!null) y:2(int) s:3(string) d:4(decimal!null)
 │    ├── stats: [rows=5000, distinct(1)=5000, null(1)=0, distinct(4)=500, null(4)=0]
 │    ├── key: (1)
 │    └── fd: (1)-->(2-4), (3,4)~~>(1,2)
 ├── scan uv
 │    ├── columns: u:5(int)
 │    └── stats: [rows=10000, distinct(5)=500, null(5)=0]
 └── filters
      └── x:1 = u:5 [type=bool, outer=(1,5), constraints=(/1: (/NULL - ]; /5: (/NULL - ]), fd=(1)==(5), (5)==(1)]

# Multiple equality conditions.
norm
SELECT * FROM xysd JOIN uv ON x=u AND y=v
----
inner-join (hash)
 ├── columns: x:1(int!null) y:2(int!null) s:3(string) d:4(decimal!null) u:5(int!null) v:6(int!null)
 ├── stats: [rows=25, distinct(1)=25, null(1)=0, distinct(2)=25, null(2)=0, distinct(5)=25, null(5)=0, distinct(6)=25, null(6)=0]
 ├── fd: (1)-->(2-4), (3,4)~~>(1,2), (1)==(5), (5)==(1), (2)==(6), (6)==(2)
 ├── scan xysd
 │    ├── columns: x:1(int!null) y:2(int) s:3(string) d:4(decimal!null)
 │    ├── stats: [rows=5000, distinct(1)=5000, null(1)=0, distinct(2)=400, null(2)=0, distinct(4)=500, null(4)=0]
 │    ├── key: (1)
 │    └── fd: (1)-->(2-4), (3,4)~~>(1,2)
 ├── scan uv
 │    ├── columns: u:5(int) v:6(int!null)
 │    └── stats: [rows=10000, distinct(5)=500, null(5)=0, distinct(6)=100, null(6)=0]
 └── filters
      ├── x:1 = u:5 [type=bool, outer=(1,5), constraints=(/1: (/NULL - ]; /5: (/NULL - ]), fd=(1)==(5), (5)==(1)]
      └── y:2 = v:6 [type=bool, outer=(2,6), constraints=(/2: (/NULL - ]; /6: (/NULL - ]), fd=(2)==(6), (6)==(2)]

# Equality condition + extra filters.
norm
SELECT * FROM xysd JOIN uv ON x=u AND y+v=5 AND y > 0 AND y < 300
----
inner-join (hash)
 ├── columns: x:1(int!null) y:2(int!null) s:3(string) d:4(decimal!null) u:5(int!null) v:6(int!null)
 ├── stats: [rows=3333.33333, distinct(1)=500, null(1)=0, distinct(5)=500, null(5)=0]
 ├── fd: (1)-->(2-4), (3,4)~~>(1,2), (1)==(5), (5)==(1)
 ├── select
 │    ├── columns: x:1(int!null) y:2(int!null) s:3(string) d:4(decimal!null)
 │    ├── stats: [rows=3737.5, distinct(1)=3737.5, null(1)=0, distinct(2)=299, null(2)=0, distinct(4)=499.999473, null(4)=0]
 │    ├── key: (1)
 │    ├── fd: (1)-->(2-4), (3,4)~~>(1,2)
 │    ├── scan xysd
 │    │    ├── columns: x:1(int!null) y:2(int) s:3(string) d:4(decimal!null)
 │    │    ├── stats: [rows=5000, distinct(1)=5000, null(1)=0, distinct(2)=400, null(2)=0, distinct(4)=500, null(4)=0]
 │    │    ├── key: (1)
 │    │    └── fd: (1)-->(2-4), (3,4)~~>(1,2)
 │    └── filters
 │         └── (y:2 > 0) AND (y:2 < 300) [type=bool, outer=(2), constraints=(/2: [/1 - /299]; tight)]
 ├── scan uv
 │    ├── columns: u:5(int) v:6(int!null)
 │    └── stats: [rows=10000, distinct(5)=500, null(5)=0, distinct(6)=100, null(6)=0]
 └── filters
      ├── x:1 = u:5 [type=bool, outer=(1,5), constraints=(/1: (/NULL - ]; /5: (/NULL - ]), fd=(1)==(5), (5)==(1)]
      └── (y:2 + v:6) = 5 [type=bool, outer=(2,6)]

# Force column statistics calculation for semi-join.
norm
SELECT count(*)
FROM (SELECT * FROM xysd WHERE EXISTS (SELECT * FROM uv WHERE x=u AND y+v=5)) AS a
GROUP BY y
----
project
 ├── columns: count:8(int!null)
 ├── stats: [rows=138.170075]
 └── group-by
      ├── columns: y:2(int) count_rows:8(int!null)
      ├── grouping columns: y:2(int)
      ├── stats: [rows=138.170075, distinct(2)=138.170075, null(2)=0]
      ├── key: (2)
      ├── fd: (2)-->(8)
      ├── semi-join (hash)
      │    ├── columns: x:1(int!null) y:2(int)
      │    ├── stats: [rows=166.666667, distinct(1)=166.666667, null(1)=0, distinct(2)=138.170075, null(2)=0]
      │    ├── key: (1)
      │    ├── fd: (1)-->(2)
      │    ├── scan xysd
      │    │    ├── columns: x:1(int!null) y:2(int)
      │    │    ├── stats: [rows=5000, distinct(1)=5000, null(1)=0, distinct(2)=400, null(2)=0]
      │    │    ├── key: (1)
      │    │    └── fd: (1)-->(2)
      │    ├── scan uv
      │    │    ├── columns: u:5(int) v:6(int!null)
      │    │    └── stats: [rows=10000, distinct(5)=500, null(5)=0, distinct(6)=100, null(6)=0]
      │    └── filters
      │         ├── x:1 = u:5 [type=bool, outer=(1,5), constraints=(/1: (/NULL - ]; /5: (/NULL - ]), fd=(1)==(5), (5)==(1)]
      │         └── (y:2 + v:6) = 5 [type=bool, outer=(2,6)]
      └── aggregations
           └── count-rows [as=count_rows:8, type=int]

# Force column statistics calculation for anti-join.
norm
SELECT count(*)
FROM (SELECT * FROM xysd WHERE NOT EXISTS (SELECT * FROM uv WHERE x=u AND y+v=5)) AS a
GROUP BY y
----
project
 ├── columns: count:8(int!null)
 ├── stats: [rows=400]
 └── group-by
      ├── columns: y:2(int) count_rows:8(int!null)
      ├── grouping columns: y:2(int)
      ├── stats: [rows=400, distinct(2)=400, null(2)=0]
      ├── key: (2)
      ├── fd: (2)-->(8)
      ├── anti-join (hash)
      │    ├── columns: x:1(int!null) y:2(int)
      │    ├── stats: [rows=4833.33333, distinct(2)=400, null(2)=0]
      │    ├── key: (1)
      │    ├── fd: (1)-->(2)
      │    ├── scan xysd
      │    │    ├── columns: x:1(int!null) y:2(int)
      │    │    ├── stats: [rows=5000, distinct(1)=5000, null(1)=0, distinct(2)=400, null(2)=0]
      │    │    ├── key: (1)
      │    │    └── fd: (1)-->(2)
      │    ├── scan uv
      │    │    ├── columns: u:5(int) v:6(int!null)
      │    │    └── stats: [rows=10000, distinct(5)=500, null(5)=0, distinct(6)=100, null(6)=0]
      │    └── filters
      │         ├── x:1 = u:5 [type=bool, outer=(1,5), constraints=(/1: (/NULL - ]; /5: (/NULL - ]), fd=(1)==(5), (5)==(1)]
      │         └── (y:2 + v:6) = 5 [type=bool, outer=(2,6)]
      └── aggregations
           └── count-rows [as=count_rows:8, type=int]

# Force column statistics calculation for left join.
norm
SELECT count(*)
FROM (SELECT * FROM xysd LEFT OUTER JOIN uv ON x=u AND y+v=5) AS a
GROUP BY y
----
project
 ├── columns: count:8(int!null)
 ├── stats: [rows=400]
 └── group-by
      ├── columns: y:2(int) count_rows:8(int!null)
      ├── grouping columns: y:2(int)
      ├── stats: [rows=400, distinct(2)=400, null(2)=0]
      ├── key: (2)
      ├── fd: (2)-->(8)
      ├── left-join (hash)
      │    ├── columns: x:1(int!null) y:2(int) u:5(int) v:6(int)
      │    ├── stats: [rows=5000, distinct(2)=400, null(2)=0, distinct(5)=500, null(5)=1666.66667]
      │    ├── fd: (1)-->(2)
      │    ├── scan xysd
      │    │    ├── columns: x:1(int!null) y:2(int)
      │    │    ├── stats: [rows=5000, distinct(1)=5000, null(1)=0, distinct(2)=400, null(2)=0]
      │    │    ├── key: (1)
      │    │    └── fd: (1)-->(2)
      │    ├── scan uv
      │    │    ├── columns: u:5(int) v:6(int!null)
      │    │    └── stats: [rows=10000, distinct(5)=500, null(5)=0]
      │    └── filters
      │         ├── x:1 = u:5 [type=bool, outer=(1,5), constraints=(/1: (/NULL - ]; /5: (/NULL - ]), fd=(1)==(5), (5)==(1)]
      │         └── (y:2 + v:6) = 5 [type=bool, outer=(2,6)]
      └── aggregations
           └── count-rows [as=count_rows:8, type=int]

# Force column statistics calculation for right join.
norm
SELECT count(*)
FROM (SELECT * FROM xysd RIGHT OUTER JOIN uv ON x=u AND y+v=5) AS a
GROUP BY y
----
project
 ├── columns: count:8(int!null)
 ├── stats: [rows=399.903879]
 └── group-by
      ├── columns: y:2(int) count_rows:8(int!null)
      ├── grouping columns: y:2(int)
      ├── stats: [rows=399.903879, distinct(2)=399.903879, null(2)=1]
      ├── key: (2)
      ├── fd: (2)-->(8)
      ├── left-join (hash)
      │    ├── columns: x:1(int) y:2(int) u:5(int) v:6(int!null)
      │    ├── stats: [rows=10000, distinct(1)=500, null(1)=6666.66667, distinct(2)=399.903879, null(2)=6666.66667]
      │    ├── fd: (1)-->(2)
      │    ├── scan uv
      │    │    ├── columns: u:5(int) v:6(int!null)
      │    │    └── stats: [rows=10000, distinct(5)=500, null(5)=0, distinct(6)=100, null(6)=0]
      │    ├── scan xysd
      │    │    ├── columns: x:1(int!null) y:2(int)
      │    │    ├── stats: [rows=5000, distinct(1)=5000, null(1)=0, distinct(2)=400, null(2)=0]
      │    │    ├── key: (1)
      │    │    └── fd: (1)-->(2)
      │    └── filters
      │         ├── x:1 = u:5 [type=bool, outer=(1,5), constraints=(/1: (/NULL - ]; /5: (/NULL - ]), fd=(1)==(5), (5)==(1)]
      │         └── (y:2 + v:6) = 5 [type=bool, outer=(2,6)]
      └── aggregations
           └── count-rows [as=count_rows:8, type=int]

# Force column statistics calculation for outer join.
norm
SELECT count(*)
FROM (SELECT * FROM xysd FULL OUTER JOIN uv ON x=u AND y+v=5) AS a
GROUP BY y
----
project
 ├── columns: count:8(int!null)
 ├── stats: [rows=400]
 └── group-by
      ├── columns: y:2(int) count_rows:8(int!null)
      ├── grouping columns: y:2(int)
      ├── stats: [rows=400, distinct(2)=400, null(2)=1]
      ├── key: (2)
      ├── fd: (2)-->(8)
      ├── full-join (hash)
      │    ├── columns: x:1(int) y:2(int) u:5(int) v:6(int)
      │    ├── stats: [rows=11666.6667, distinct(2)=400, null(2)=6666.66667]
      │    ├── fd: (1)-->(2)
      │    ├── scan xysd
      │    │    ├── columns: x:1(int!null) y:2(int)
      │    │    ├── stats: [rows=5000, distinct(1)=5000, null(1)=0, distinct(2)=400, null(2)=0]
      │    │    ├── key: (1)
      │    │    └── fd: (1)-->(2)
      │    ├── scan uv
      │    │    ├── columns: u:5(int) v:6(int!null)
      │    │    └── stats: [rows=10000, distinct(5)=500, null(5)=0]
      │    └── filters
      │         ├── x:1 = u:5 [type=bool, outer=(1,5), constraints=(/1: (/NULL - ]; /5: (/NULL - ]), fd=(1)==(5), (5)==(1)]
      │         └── (y:2 + v:6) = 5 [type=bool, outer=(2,6)]
      └── aggregations
           └── count-rows [as=count_rows:8, type=int]

exec-ddl
CREATE TABLE uvw (u INT, v INT, w INT)
----

exec-ddl
CREATE TABLE xyz (x INT, y INT, z INT)
----

# Verify that two equivalent formulations of a join lead to similar statistics.
# In the first case, x=10 is pushed down; in the second case it is part of the
# ON condition. The latter formulation happens in practice when we convert to
# lookup join (we incorporate the filter back into the ON condition).

norm disable=(PushFilterIntoJoinLeftAndRight,PushFilterIntoJoinLeft,PushFilterIntoJoinRight,MapFilterIntoJoinLeft,MapFilterIntoJoinRight)
SELECT * FROM (SELECT * FROM uvw WHERE w=1) JOIN (SELECT * FROM xyz WHERE x=10) ON u=x
----
inner-join (hash)
 ├── columns: u:1(int!null) v:2(int) w:3(int!null) x:5(int!null) y:6(int) z:7(int)
 ├── stats: [rows=10.3537072, distinct(1)=1, null(1)=0, distinct(5)=1, null(5)=0]
 ├── fd: ()-->(1,3,5), (1)==(5), (5)==(1)
 ├── select
 │    ├── columns: u:1(int) v:2(int) w:3(int!null)
 │    ├── stats: [rows=10, distinct(1)=9.5617925, null(1)=0.1, distinct(3)=1, null(3)=0]
 │    ├── fd: ()-->(3)
 │    ├── scan uvw
 │    │    ├── columns: u:1(int) v:2(int) w:3(int)
 │    │    └── stats: [rows=1000, distinct(1)=100, null(1)=10, distinct(3)=100, null(3)=10]
 │    └── filters
 │         └── w:3 = 1 [type=bool, outer=(3), constraints=(/3: [/1 - /1]; tight), fd=()-->(3)]
 ├── select
 │    ├── columns: x:5(int!null) y:6(int) z:7(int)
 │    ├── stats: [rows=10, distinct(5)=1, null(5)=0]
 │    ├── fd: ()-->(5)
 │    ├── scan xyz
 │    │    ├── columns: x:5(int) y:6(int) z:7(int)
 │    │    └── stats: [rows=1000, distinct(5)=100, null(5)=10]
 │    └── filters
 │         └── x:5 = 10 [type=bool, outer=(5), constraints=(/5: [/10 - /10]; tight), fd=()-->(5)]
 └── filters
      └── u:1 = x:5 [type=bool, outer=(1,5), constraints=(/1: (/NULL - ]; /5: (/NULL - ]), fd=(1)==(5), (5)==(1)]

norm disable=(PushFilterIntoJoinLeftAndRight,PushFilterIntoJoinLeft,PushFilterIntoJoinRight,MapFilterIntoJoinLeft,MapFilterIntoJoinRight)
SELECT * FROM (SELECT * FROM uvw WHERE w=1) JOIN xyz ON u=x AND x=10
----
inner-join (hash)
 ├── columns: u:1(int!null) v:2(int) w:3(int!null) x:5(int!null) y:6(int) z:7(int)
 ├── stats: [rows=10.3537072, distinct(1)=1, null(1)=0, distinct(5)=1, null(5)=0]
 ├── fd: ()-->(1,3,5), (1)==(5), (5)==(1)
 ├── select
 │    ├── columns: u:1(int) v:2(int) w:3(int!null)
 │    ├── stats: [rows=10, distinct(1)=9.5617925, null(1)=0.1, distinct(3)=1, null(3)=0]
 │    ├── fd: ()-->(3)
 │    ├── scan uvw
 │    │    ├── columns: u:1(int) v:2(int) w:3(int)
 │    │    └── stats: [rows=1000, distinct(1)=100, null(1)=10, distinct(3)=100, null(3)=10]
 │    └── filters
 │         └── w:3 = 1 [type=bool, outer=(3), constraints=(/3: [/1 - /1]; tight), fd=()-->(3)]
 ├── scan xyz
 │    ├── columns: x:5(int) y:6(int) z:7(int)
 │    └── stats: [rows=1000, distinct(5)=100, null(5)=10]
 └── filters
      ├── u:1 = x:5 [type=bool, outer=(1,5), constraints=(/1: (/NULL - ]; /5: (/NULL - ]), fd=(1)==(5), (5)==(1)]
      └── x:5 = 10 [type=bool, outer=(5), constraints=(/5: [/10 - /10]; tight), fd=()-->(5)]

# Bump up null counts.
exec-ddl
ALTER TABLE xysd INJECT STATISTICS '[
  {
    "columns": ["x"],
    "created_at": "2018-01-01 2:00:00.00000+00:00",
    "row_count": 5000,
    "distinct_count": 5000
  },
  {
    "columns": ["y"],
    "created_at": "2018-01-01 2:00:00.00000+00:00",
    "row_count": 5000,
    "distinct_count": 400,
    "null_count": 2500
  }
]'
----

exec-ddl
ALTER TABLE uv INJECT STATISTICS '[
  {
    "columns": ["u"],
    "created_at": "2018-01-01 2:00:00.00000+00:00",
    "row_count": 10000,
    "distinct_count": 500,
    "null_count": 5000
  },
  {
    "columns": ["v"],
    "created_at": "2018-01-01 2:00:00.00000+00:00",
    "row_count": 10000,
    "distinct_count": 100
  },
  {
    "columns": ["rowid"],
    "created_at": "2018-01-01 2:00:00.00000+00:00",
    "row_count": 10000,
    "distinct_count": 10000
  }
]'
----

build colstat=2 colstat=(1,2,7) colstat=(2,3) colstat=3 colstat=(3,5) colstat=5
SELECT *, rowid FROM xysd INNER JOIN uv ON x=u
----
inner-join (hash)
 ├── columns: x:1(int!null) y:2(int) s:3(string) d:4(decimal!null) u:5(int!null) v:6(int!null) rowid:7(int!null)
 ├── stats: [rows=5000, distinct(1)=499, null(1)=0, distinct(2)=399.99851, null(2)=2500, distinct(3)=499.977311, null(3)=50, distinct(5)=499, null(5)=0, distinct(2,3)=3160.69477, null(2,3)=25, distinct(3,5)=5000, null(3,5)=0, distinct(1,2,7)=5000, null(1,2,7)=0]
 ├── key: (7)
 ├── fd: (1)-->(2-4), (3,4)~~>(1,2), (7)-->(5,6), (1)==(5), (5)==(1)
 ├── scan xysd
 │    ├── columns: x:1(int!null) y:2(int) s:3(string) d:4(decimal!null)
 │    ├── stats: [rows=5000, distinct(1)=5000, null(1)=0, distinct(2)=400, null(2)=2500, distinct(3)=500, null(3)=50, distinct(4)=500, null(4)=0, distinct(1,2)=5000, null(1,2)=0, distinct(2,3)=5000, null(2,3)=25]
 │    ├── key: (1)
 │    └── fd: (1)-->(2-4), (3,4)~~>(1,2)
 ├── scan uv
 │    ├── columns: u:5(int) v:6(int!null) rowid:7(int!null)
 │    ├── stats: [rows=10000, distinct(5)=500, null(5)=5000, distinct(6)=100, null(6)=0, distinct(7)=10000, null(7)=0]
 │    ├── key: (7)
 │    └── fd: (7)-->(5,6)
 └── filters
      └── x:1 = u:5 [type=bool, outer=(1,5), constraints=(/1: (/NULL - ]; /5: (/NULL - ]), fd=(1)==(5), (5)==(1)]

build colstat=2 colstat=(1,2,7) colstat=(2,3) colstat=3 colstat=(3,5) colstat=5
SELECT *, rowid FROM xysd LEFT JOIN uv ON x=u
----
left-join (hash)
 ├── columns: x:1(int!null) y:2(int) s:3(string) d:4(decimal!null) u:5(int) v:6(int) rowid:7(int)
 ├── stats: [rows=10000, distinct(2)=400, null(2)=5000, distinct(3)=500, null(3)=100, distinct(5)=500, null(5)=0, distinct(2,3)=5000, null(2,3)=50, distinct(3,5)=10000, null(3,5)=50, distinct(1,2,7)=10000, null(1,2,7)=0]
 ├── key: (1,7)
 ├── fd: (1)-->(2-4), (3,4)~~>(1,2), (7)-->(5,6)
 ├── scan xysd
 │    ├── columns: x:1(int!null) y:2(int) s:3(string) d:4(decimal!null)
 │    ├── stats: [rows=5000, distinct(1)=5000, null(1)=0, distinct(2)=400, null(2)=2500, distinct(3)=500, null(3)=50, distinct(4)=500, null(4)=0, distinct(1,2)=5000, null(1,2)=0, distinct(2,3)=5000, null(2,3)=25]
 │    ├── key: (1)
 │    └── fd: (1)-->(2-4), (3,4)~~>(1,2)
 ├── scan uv
 │    ├── columns: u:5(int) v:6(int!null) rowid:7(int!null)
 │    ├── stats: [rows=10000, distinct(5)=500, null(5)=5000, distinct(7)=10000, null(7)=0]
 │    ├── key: (7)
 │    └── fd: (7)-->(5,6)
 └── filters
      └── x:1 = u:5 [type=bool, outer=(1,5), constraints=(/1: (/NULL - ]; /5: (/NULL - ]), fd=(1)==(5), (5)==(1)]

build colstat=2 colstat=(1,2,7) colstat=(2,3) colstat=3 colstat=(3,5) colstat=5
SELECT *, rowid FROM xysd RIGHT JOIN uv ON x=u
----
right-join (hash)
 ├── columns: x:1(int) y:2(int) s:3(string) d:4(decimal) u:5(int) v:6(int!null) rowid:7(int!null)
 ├── stats: [rows=10000, distinct(1)=500, null(1)=0, distinct(2)=400, null(2)=5000, distinct(3)=499.999999, null(3)=100, distinct(5)=500, null(5)=5000, distinct(2,3)=4323.45892, null(2,3)=50, distinct(3,5)=10000, null(3,5)=50, distinct(1,2,7)=10000, null(1,2,7)=0]
 ├── key: (7)
 ├── fd: (1)-->(2-4), (3,4)~~>(1,2), (7)-->(1-6)
 ├── scan xysd
 │    ├── columns: x:1(int!null) y:2(int) s:3(string) d:4(decimal!null)
 │    ├── stats: [rows=5000, distinct(1)=5000, null(1)=0, distinct(2)=400, null(2)=2500, distinct(3)=500, null(3)=50, distinct(1,2)=5000, null(1,2)=0, distinct(2,3)=5000, null(2,3)=25]
 │    ├── key: (1)
 │    └── fd: (1)-->(2-4), (3,4)~~>(1,2)
 ├── scan uv
 │    ├── columns: u:5(int) v:6(int!null) rowid:7(int!null)
 │    ├── stats: [rows=10000, distinct(5)=500, null(5)=5000, distinct(6)=100, null(6)=0, distinct(7)=10000, null(7)=0]
 │    ├── key: (7)
 │    └── fd: (7)-->(5,6)
 └── filters
      └── x:1 = u:5 [type=bool, outer=(1,5), constraints=(/1: (/NULL - ]; /5: (/NULL - ]), fd=(1)==(5), (5)==(1)]

build colstat=2 colstat=(1,2,7) colstat=(2,3) colstat=3 colstat=(3,5) colstat=5
SELECT *, rowid FROM xysd FULL JOIN uv ON x=u
----
full-join (hash)
 ├── columns: x:1(int) y:2(int) s:3(string) d:4(decimal) u:5(int) v:6(int) rowid:7(int)
 ├── stats: [rows=10000, distinct(2)=400, null(2)=5000, distinct(3)=500, null(3)=100, distinct(5)=500, null(5)=5000, distinct(2,3)=5000, null(2,3)=50, distinct(3,5)=10000, null(3,5)=50, distinct(1,2,7)=10000, null(1,2,7)=0]
 ├── key: (1,7)
 ├── fd: (1)-->(2-4), (3,4)~~>(1,2), (7)-->(5,6)
 ├── scan xysd
 │    ├── columns: x:1(int!null) y:2(int) s:3(string) d:4(decimal!null)
 │    ├── stats: [rows=5000, distinct(1)=5000, null(1)=0, distinct(2)=400, null(2)=2500, distinct(3)=500, null(3)=50, distinct(1,2)=5000, null(1,2)=0, distinct(2,3)=5000, null(2,3)=25]
 │    ├── key: (1)
 │    └── fd: (1)-->(2-4), (3,4)~~>(1,2)
 ├── scan uv
 │    ├── columns: u:5(int) v:6(int!null) rowid:7(int!null)
 │    ├── stats: [rows=10000, distinct(5)=500, null(5)=5000, distinct(7)=10000, null(7)=0]
 │    ├── key: (7)
 │    └── fd: (7)-->(5,6)
 └── filters
      └── x:1 = u:5 [type=bool, outer=(1,5), constraints=(/1: (/NULL - ]; /5: (/NULL - ]), fd=(1)==(5), (5)==(1)]

# Set one of the columns to non-nullable and see impact on multi-column null counts.
build colstat=2 colstat=(1,2,7) colstat=(2,3) colstat=3 colstat=(3,5) colstat=5
SELECT *, rowid FROM xysd FULL JOIN uv ON x=u WHERE s IS NOT NULL
----
select
 ├── columns: x:1(int) y:2(int) s:3(string!null) d:4(decimal) u:5(int) v:6(int) rowid:7(int)
 ├── stats: [rows=9900, distinct(2)=400, null(2)=4950, distinct(3)=500, null(3)=0, distinct(5)=500, null(5)=4950, distinct(2,3)=4999.5, null(2,3)=0, distinct(3,5)=9900, null(3,5)=0, distinct(1,2,7)=9900, null(1,2,7)=0]
 ├── key: (1,7)
 ├── fd: (1)-->(2-4), (3,4)~~>(1,2), (7)-->(5,6)
 ├── full-join (hash)
 │    ├── columns: x:1(int) y:2(int) s:3(string) d:4(decimal) u:5(int) v:6(int) rowid:7(int)
 │    ├── stats: [rows=10000, distinct(2)=400, null(2)=5000, distinct(3)=500, null(3)=100, distinct(5)=500, null(5)=5000, distinct(2,3)=5000, null(2,3)=50, distinct(3,5)=10000, null(3,5)=50, distinct(1,2,7)=10000, null(1,2,7)=0]
 │    ├── key: (1,7)
 │    ├── fd: (1)-->(2-4), (3,4)~~>(1,2), (7)-->(5,6)
 │    ├── scan xysd
 │    │    ├── columns: x:1(int!null) y:2(int) s:3(string) d:4(decimal!null)
 │    │    ├── stats: [rows=5000, distinct(1)=5000, null(1)=0, distinct(2)=400, null(2)=2500, distinct(3)=500, null(3)=50, distinct(1,2)=5000, null(1,2)=0, distinct(2,3)=5000, null(2,3)=25]
 │    │    ├── key: (1)
 │    │    └── fd: (1)-->(2-4), (3,4)~~>(1,2)
 │    ├── scan uv
 │    │    ├── columns: u:5(int) v:6(int!null) rowid:7(int!null)
 │    │    ├── stats: [rows=10000, distinct(5)=500, null(5)=5000, distinct(7)=10000, null(7)=0]
 │    │    ├── key: (7)
 │    │    └── fd: (7)-->(5,6)
 │    └── filters
 │         └── x:1 = u:5 [type=bool, outer=(1,5), constraints=(/1: (/NULL - ]; /5: (/NULL - ]), fd=(1)==(5), (5)==(1)]
 └── filters
      └── s:3 IS NOT NULL [type=bool, outer=(3), constraints=(/3: (/NULL - ]; tight)]

# Do a full join on a condition that results in 0 rows on one side. All null counts
# on the right side should be greater due to expected null-extension of columns.
build colstat=2 colstat=(1,2,7) colstat=(2,3) colstat=3 colstat=(3,5) colstat=5
SELECT *, rowid FROM xysd FULL JOIN uv ON u > 4 AND u < 2
----
full-join (cross)
 ├── columns: x:1(int) y:2(int) s:3(string) d:4(decimal) u:5(int) v:6(int) rowid:7(int)
 ├── stats: [rows=50000000, distinct(2)=400, null(2)=25000000, distinct(3)=500, null(3)=500000, distinct(5)=500, null(5)=25000000, distinct(2,3)=5000, null(2,3)=250000, distinct(3,5)=250000, null(3,5)=250000, distinct(1,2,7)=50000000, null(1,2,7)=0]
 ├── key: (1,7)
 ├── fd: (1)-->(2-4), (3,4)~~>(1,2), (7)-->(5,6)
 ├── scan xysd
 │    ├── columns: x:1(int!null) y:2(int) s:3(string) d:4(decimal!null)
 │    ├── stats: [rows=5000, distinct(2)=400, null(2)=2500, distinct(3)=500, null(3)=50, distinct(1,2)=5000, null(1,2)=0, distinct(2,3)=5000, null(2,3)=25]
 │    ├── key: (1)
 │    └── fd: (1)-->(2-4), (3,4)~~>(1,2)
 ├── scan uv
 │    ├── columns: u:5(int) v:6(int!null) rowid:7(int!null)
 │    ├── stats: [rows=10000, distinct(5)=500, null(5)=5000, distinct(7)=10000, null(7)=0]
 │    ├── key: (7)
 │    └── fd: (7)-->(5,6)
 └── filters
      └── (u:5 > 4) AND (u:5 < 2) [type=bool, outer=(5), constraints=(contradiction; tight)]

build colstat=2 colstat=(1,2,7)
SELECT * FROM xysd, uv
----
project
 ├── columns: x:1(int!null) y:2(int) s:3(string) d:4(decimal!null) u:5(int) v:6(int!null)
 ├── stats: [rows=50000000, distinct(2)=400, null(2)=25000000, distinct(1,2,7)=50000000, null(1,2,7)=0]
 ├── fd: (1)-->(2-4), (3,4)~~>(1,2)
 └── inner-join (cross)
      ├── columns: x:1(int!null) y:2(int) s:3(string) d:4(decimal!null) u:5(int) v:6(int!null) rowid:7(int!null)
      ├── stats: [rows=50000000, distinct(2)=400, null(2)=25000000, distinct(1,2,7)=50000000, null(1,2,7)=0]
      ├── key: (1,7)
      ├── fd: (1)-->(2-4), (3,4)~~>(1,2), (7)-->(5,6)
      ├── scan xysd
      │    ├── columns: x:1(int!null) y:2(int) s:3(string) d:4(decimal!null)
      │    ├── stats: [rows=5000, distinct(2)=400, null(2)=2500, distinct(1,2)=5000, null(1,2)=0]
      │    ├── key: (1)
      │    └── fd: (1)-->(2-4), (3,4)~~>(1,2)
      ├── scan uv
      │    ├── columns: u:5(int) v:6(int!null) rowid:7(int!null)
      │    ├── stats: [rows=10000, distinct(7)=10000, null(7)=0]
      │    ├── key: (7)
      │    └── fd: (7)-->(5,6)
      └── filters (true)

norm
SELECT * FROM xysd WHERE EXISTS(SELECT * FROM uv WHERE x=u)
----
semi-join (hash)
 ├── columns: x:1(int!null) y:2(int) s:3(string) d:4(decimal!null)
 ├── stats: [rows=500, distinct(1)=500, null(1)=0]
 ├── key: (1)
 ├── fd: (1)-->(2-4), (3,4)~~>(1,2)
 ├── scan xysd
 │    ├── columns: x:1(int!null) y:2(int) s:3(string) d:4(decimal!null)
 │    ├── stats: [rows=5000, distinct(1)=5000, null(1)=0, distinct(4)=500, null(4)=0]
 │    ├── key: (1)
 │    └── fd: (1)-->(2-4), (3,4)~~>(1,2)
 ├── scan uv
 │    ├── columns: u:5(int)
 │    └── stats: [rows=10000, distinct(5)=500, null(5)=5000]
 └── filters
      └── x:1 = u:5 [type=bool, outer=(1,5), constraints=(/1: (/NULL - ]; /5: (/NULL - ]), fd=(1)==(5), (5)==(1)]

norm
SELECT * FROM uv WHERE EXISTS(SELECT * FROM xysd WHERE x=u)
----
semi-join (hash)
 ├── columns: u:1(int) v:2(int!null)
 ├── stats: [rows=10000, distinct(1)=500, null(1)=0]
 ├── scan uv
 │    ├── columns: u:1(int) v:2(int!null)
 │    └── stats: [rows=10000, distinct(1)=500, null(1)=5000, distinct(2)=100, null(2)=0]
 ├── scan xysd
 │    ├── columns: x:4(int!null)
 │    ├── stats: [rows=5000, distinct(4)=5000, null(4)=0]
 │    └── key: (4)
 └── filters
      └── x:4 = u:1 [type=bool, outer=(1,4), constraints=(/1: (/NULL - ]; /4: (/NULL - ]), fd=(1)==(4), (4)==(1)]

# Merge join (inner).
expr colstat=2 colstat=(1,2,7) colstat=(2,3) colstat=3 colstat=(3,5) colstat=5
(MergeJoin
    (Scan [ (Table "xysd") (Cols "x,y,s,d") ])
    (Sort (Scan [ (Table "uv") (Cols "u,v,rowid") ]))
    [ ]
    [
        (JoinType "inner-join")
        (LeftEq "+x")
        (RightEq "+u")
        (LeftOrdering "+x")
        (RightOrdering "+u")
    ]
)
----
inner-join (merge)
 ├── columns: x:1(int!null) y:2(int) s:3(string) d:4(decimal!null) u:5(int!null) v:6(int!null) rowid:7(int!null)
 ├── left ordering: +1
 ├── right ordering: +5
 ├── stats: [rows=5000, distinct(1)=499, null(1)=0, distinct(2)=400, null(2)=2500, distinct(3)=500, null(3)=50, distinct(5)=499, null(5)=0, distinct(2,3)=5000, null(2,3)=25, distinct(3,5)=5000, null(3,5)=0, distinct(1,2,7)=5000, null(1,2,7)=0]
 ├── key: (7)
 ├── fd: (1)-->(2-4), (3,4)~~>(1,2), (7)-->(5,6), (1)==(5), (5)==(1)
 ├── scan xysd
 │    ├── columns: x:1(int!null) y:2(int) s:3(string) d:4(decimal!null)
 │    ├── stats: [rows=5000, distinct(1)=5000, null(1)=0, distinct(2)=400, null(2)=2500, distinct(3)=500, null(3)=50, distinct(4)=500, null(4)=0, distinct(1,2)=5000, null(1,2)=0, distinct(2,3)=5000, null(2,3)=25]
 │    ├── key: (1)
 │    ├── fd: (1)-->(2-4), (3,4)~~>(1,2)
 │    └── ordering: +1
 ├── sort
 │    ├── columns: u:5(int) v:6(int!null) rowid:7(int!null)
 │    ├── stats: [rows=10000, distinct(5)=500, null(5)=5000, distinct(6)=100, null(6)=0, distinct(7)=10000, null(7)=0]
 │    ├── key: (7)
 │    ├── fd: (7)-->(5,6)
 │    ├── ordering: +5
 │    └── scan uv
 │         ├── columns: u:5(int) v:6(int!null) rowid:7(int!null)
 │         ├── stats: [rows=10000, distinct(5)=500, null(5)=5000, distinct(6)=100, null(6)=0, distinct(7)=10000, null(7)=0]
 │         ├── key: (7)
 │         └── fd: (7)-->(5,6)
 └── filters (true)

# Merge join (left) with extra ON condition.
expr colstat=2 colstat=(1,2,7) colstat=(2,3) colstat=3 colstat=(3,5) colstat=5
(MergeJoin
    (Scan [ (Table "xysd") (Cols "x,y,s,d") ])
    (Sort (Scan [ (Table "uv") (Cols "u,v,rowid") ]))
    [ (Gt (Var "y") (Var "v")) ]
    [
        (JoinType "left-join")
        (LeftEq "+x")
        (RightEq "+u")
        (LeftOrdering "+x")
        (RightOrdering "+u")
    ]
)
----
left-join (merge)
 ├── columns: x:1(int!null) y:2(int) s:3(string) d:4(decimal!null) u:5(int) v:6(int) rowid:7(int)
 ├── left ordering: +1
 ├── right ordering: +5
 ├── stats: [rows=5000, distinct(2)=400, null(2)=2500, distinct(3)=500, null(3)=50, distinct(5)=500, null(5)=1666.66667, distinct(6)=100, null(6)=1666.66667, distinct(2,3)=5000, null(2,3)=25, distinct(3,5)=5000, null(3,5)=25, distinct(1,2,7)=5000, null(1,2,7)=0]
 ├── key: (1,7)
 ├── fd: (1)-->(2-4), (3,4)~~>(1,2), (7)-->(5,6)
 ├── scan xysd
 │    ├── columns: x:1(int!null) y:2(int) s:3(string) d:4(decimal!null)
 │    ├── stats: [rows=5000, distinct(1)=5000, null(1)=0, distinct(2)=400, null(2)=2500, distinct(3)=500, null(3)=50, distinct(4)=500, null(4)=0, distinct(1,2)=5000, null(1,2)=0, distinct(2,3)=5000, null(2,3)=25]
 │    ├── key: (1)
 │    ├── fd: (1)-->(2-4), (3,4)~~>(1,2)
 │    └── ordering: +1
 ├── sort
 │    ├── columns: u:5(int) v:6(int!null) rowid:7(int!null)
 │    ├── stats: [rows=10000, distinct(5)=500, null(5)=5000, distinct(6)=100, null(6)=0, distinct(7)=10000, null(7)=0]
 │    ├── key: (7)
 │    ├── fd: (7)-->(5,6)
 │    ├── ordering: +5
 │    └── scan uv
 │         ├── columns: u:5(int) v:6(int!null) rowid:7(int!null)
 │         ├── stats: [rows=10000, distinct(5)=500, null(5)=5000, distinct(6)=100, null(6)=0, distinct(7)=10000, null(7)=0]
 │         ├── key: (7)
 │         └── fd: (7)-->(5,6)
 └── filters
      └── y:2 > v:6 [type=bool, outer=(2,6), constraints=(/2: (/NULL - ]; /6: (/NULL - ])]

# Check that true filters are handled correctly for all join types.
norm
SELECT * FROM (SELECT 1) JOIN (SELECT 1 WHERE false) ON true
----
values
 ├── columns: "?column?":1(int!null) "?column?":2(int!null)
 ├── cardinality: [0 - 0]
 ├── stats: [rows=0]
 ├── key: ()
 └── fd: ()-->(1,2)

norm
SELECT * FROM (SELECT 1) LEFT JOIN (SELECT 1 WHERE false) ON true
----
left-join (cross)
 ├── columns: "?column?":1(int!null) "?column?":2(int)
 ├── cardinality: [1 - 1]
 ├── stats: [rows=1]
 ├── key: ()
 ├── fd: ()-->(1,2)
 ├── values
 │    ├── columns: "?column?":1(int!null)
 │    ├── cardinality: [1 - 1]
 │    ├── stats: [rows=1]
 │    ├── key: ()
 │    ├── fd: ()-->(1)
 │    └── (1,) [type=tuple{int}]
 ├── values
 │    ├── columns: "?column?":2(int!null)
 │    ├── cardinality: [0 - 0]
 │    ├── stats: [rows=0]
 │    ├── key: ()
 │    └── fd: ()-->(2)
 └── filters (true)

norm
SELECT * FROM (SELECT 1) RIGHT JOIN (SELECT 1 WHERE false) ON true
----
values
 ├── columns: "?column?":1(int!null) "?column?":2(int!null)
 ├── cardinality: [0 - 0]
 ├── stats: [rows=0]
 ├── key: ()
 └── fd: ()-->(1,2)

norm
SELECT * FROM (SELECT 1) FULL JOIN (SELECT 1 WHERE false) ON true
----
left-join (cross)
 ├── columns: "?column?":1(int!null) "?column?":2(int)
 ├── cardinality: [1 - 1]
 ├── stats: [rows=1]
 ├── key: ()
 ├── fd: ()-->(1,2)
 ├── values
 │    ├── columns: "?column?":1(int!null)
 │    ├── cardinality: [1 - 1]
 │    ├── stats: [rows=1]
 │    ├── key: ()
 │    ├── fd: ()-->(1)
 │    └── (1,) [type=tuple{int}]
 ├── values
 │    ├── columns: "?column?":2(int!null)
 │    ├── cardinality: [0 - 0]
 │    ├── stats: [rows=0]
 │    ├── key: ()
 │    └── fd: ()-->(2)
 └── filters (true)

norm
SELECT * FROM (SELECT 1 WHERE false) JOIN (SELECT 1) ON true
----
values
 ├── columns: "?column?":1(int!null) "?column?":2(int!null)
 ├── cardinality: [0 - 0]
 ├── stats: [rows=0]
 ├── key: ()
 └── fd: ()-->(1,2)

norm
SELECT * FROM (SELECT 1 WHERE false) LEFT JOIN (SELECT 1) ON true
----
values
 ├── columns: "?column?":1(int!null) "?column?":2(int!null)
 ├── cardinality: [0 - 0]
 ├── stats: [rows=0]
 ├── key: ()
 └── fd: ()-->(1,2)

norm
SELECT * FROM (SELECT 1 WHERE false) RIGHT JOIN (SELECT 1) ON true
----
left-join (cross)
 ├── columns: "?column?":1(int) "?column?":2(int!null)
 ├── cardinality: [1 - 1]
 ├── stats: [rows=1]
 ├── key: ()
 ├── fd: ()-->(1,2)
 ├── values
 │    ├── columns: "?column?":2(int!null)
 │    ├── cardinality: [1 - 1]
 │    ├── stats: [rows=1]
 │    ├── key: ()
 │    ├── fd: ()-->(2)
 │    └── (1,) [type=tuple{int}]
 ├── values
 │    ├── columns: "?column?":1(int!null)
 │    ├── cardinality: [0 - 0]
 │    ├── stats: [rows=0]
 │    ├── key: ()
 │    └── fd: ()-->(1)
 └── filters (true)

norm
SELECT * FROM (SELECT 1 WHERE false) FULL JOIN (SELECT 1) ON true
----
left-join (cross)
 ├── columns: "?column?":1(int) "?column?":2(int!null)
 ├── cardinality: [1 - 1]
 ├── stats: [rows=1]
 ├── key: ()
 ├── fd: ()-->(1,2)
 ├── values
 │    ├── columns: "?column?":2(int!null)
 │    ├── cardinality: [1 - 1]
 │    ├── stats: [rows=1]
 │    ├── key: ()
 │    ├── fd: ()-->(2)
 │    └── (1,) [type=tuple{int}]
 ├── values
 │    ├── columns: "?column?":1(int!null)
 │    ├── cardinality: [0 - 0]
 │    ├── stats: [rows=0]
 │    ├── key: ()
 │    └── fd: ()-->(1)
 └── filters (true)

norm
SELECT * FROM (SELECT 1) FULL JOIN (VALUES (1), (2)) ON true
----
inner-join (cross)
 ├── columns: "?column?":1(int!null) column1:2(int!null)
 ├── cardinality: [2 - 2]
 ├── stats: [rows=2]
 ├── fd: ()-->(1)
 ├── values
 │    ├── columns: column1:2(int!null)
 │    ├── cardinality: [2 - 2]
 │    ├── stats: [rows=2]
 │    ├── (1,) [type=tuple{int}]
 │    └── (2,) [type=tuple{int}]
 ├── values
 │    ├── columns: "?column?":1(int!null)
 │    ├── cardinality: [1 - 1]
 │    ├── stats: [rows=1]
 │    ├── key: ()
 │    ├── fd: ()-->(1)
 │    └── (1,) [type=tuple{int}]
 └── filters (true)

exec-ddl
CREATE TABLE table0 (
    col0 INT4,
    col1 BOOL NULL,
    col2 BIT(40) NOT NULL
)
----

exec-ddl
CREATE TABLE table1 (
    col0 BIT(23) NULL,
    col1 INET NULL
)
----

# Regression test for #38091.
norm
SELECT (
        SELECT 1
          FROM table1
               LEFT JOIN table1 AS t1
                INNER JOIN table0 ON false ON t0.col1
       )
  FROM table0 AS t0
----
project
 ├── columns: "?column?":16(int)
 ├── stats: [rows=1000000]
 ├── ensure-distinct-on
 │    ├── columns: t0.rowid:4(int!null) "?column?":15(int)
 │    ├── grouping columns: t0.rowid:4(int!null)
 │    ├── error: "more than one row returned by a subquery used as an expression"
 │    ├── stats: [rows=1000000]
 │    ├── key: (4)
 │    ├── fd: (4)-->(15)
 │    ├── left-join-apply
 │    │    ├── columns: t0.col1:2(bool) t0.rowid:4(int!null) "?column?":15(int)
 │    │    ├── stats: [rows=1000000]
 │    │    ├── fd: (4)-->(2)
 │    │    ├── scan t0
 │    │    │    ├── columns: t0.col1:2(bool) t0.rowid:4(int!null)
 │    │    │    ├── stats: [rows=1000]
 │    │    │    ├── key: (4)
 │    │    │    └── fd: (4)-->(2)
 │    │    ├── project
 │    │    │    ├── columns: "?column?":15(int!null)
 │    │    │    ├── outer: (2)
 │    │    │    ├── stats: [rows=1000]
 │    │    │    ├── fd: ()-->(15)
 │    │    │    ├── left-join (cross)
 │    │    │    │    ├── outer: (2)
 │    │    │    │    ├── stats: [rows=1000, distinct(2)=1, null(2)=0]
 │    │    │    │    ├── scan table1
 │    │    │    │    │    └── stats: [rows=1000]
 │    │    │    │    ├── values
 │    │    │    │    │    ├── cardinality: [0 - 0]
 │    │    │    │    │    ├── stats: [rows=0]
 │    │    │    │    │    └── key: ()
 │    │    │    │    └── filters
 │    │    │    │         └── t0.col1:2 [type=bool, outer=(2), constraints=(/2: [/true - /true]; tight), fd=()-->(2)]
 │    │    │    └── projections
 │    │    │         └── 1 [as="?column?":15, type=int]
 │    │    └── filters (true)
 │    └── aggregations
 │         └── const-agg [as="?column?":15, type=int, outer=(15)]
 │              └── "?column?":15 [type=int]
 └── projections
      └── "?column?":15 [as="?column?":16, type=int, outer=(15)]

norm colstat=1 colstat=2
SELECT * FROM (SELECT 1) AS a(x) LEFT JOIN (SELECT 2) AS b(x) ON a.x = b.x
----
left-join (cross)
 ├── columns: x:1(int!null) x:2(int)
 ├── cardinality: [1 - 1]
 ├── stats: [rows=1, distinct(1)=1, null(1)=0, distinct(2)=1, null(2)=1]
 ├── key: ()
 ├── fd: ()-->(1,2)
 ├── values
 │    ├── columns: "?column?":1(int!null)
 │    ├── cardinality: [1 - 1]
 │    ├── stats: [rows=1, distinct(1)=1, null(1)=0]
 │    ├── key: ()
 │    ├── fd: ()-->(1)
 │    └── (1,) [type=tuple{int}]
 ├── values
 │    ├── columns: "?column?":2(int!null)
 │    ├── cardinality: [0 - 0]
 │    ├── stats: [rows=0, distinct(2)=0, null(2)=0]
 │    ├── key: ()
 │    └── fd: ()-->(2)
 └── filters (true)

exec-ddl
CREATE TABLE abc (a INT, b INT, c INT, PRIMARY KEY (a, b));
----

exec-ddl
CREATE TABLE def (d INT, e INT, f INT, PRIMARY KEY (d, e));
----

exec-ddl
ALTER TABLE abc INJECT STATISTICS '[
  {
    "columns": ["a"],
    "created_at": "2018-01-01 1:00:00.00000+00:00",
    "row_count": 100,
    "distinct_count": 100
  },
  {
    "columns": ["b"],
    "created_at": "2018-01-01 1:00:00.00000+00:00",
    "row_count": 100,
    "distinct_count": 10
  }
]'
----

exec-ddl
ALTER TABLE def INJECT STATISTICS '[
  {
    "columns": ["d"],
    "created_at": "2018-01-01 1:00:00.00000+00:00",
    "row_count": 10000,
    "distinct_count": 10000
  },
  {
    "columns": ["e"],
    "created_at": "2018-01-01 1:00:00.00000+00:00",
    "row_count": 10000,
    "distinct_count": 10000
  }
]'
----

# TODO(rytaft): The cardinality estimates here are unrealistically low.
# Maybe revisit this? I doubt there's anything we can do there though.
expr format=show-all colstat=5 colstat=6 colstat=(5, 6) colstat=1 colstat=2 colstat=3 colstat=(1, 2, 3)
(MakeLookupJoin
  (Scan [ (Table "abc") (Cols "a,b,c") ])
  [ (JoinType "inner-join") (Table "def") (Index "def@primary") (KeyCols "a,b") (Cols "a,b,c,d,f,e") ]
  [ ]
)
----
inner-join (lookup def)
 ├── columns: t.public.abc.a:1(int!null) t.public.abc.b:2(int!null) t.public.abc.c:3(int) t.public.def.d:4(int!null) t.public.def.e:5(int!null) t.public.def.f:6(int)
 ├── key columns: [1 2] = [4 5]
 ├── stats: [rows=0.01, distinct(1)=0.01, null(1)=0, distinct(2)=0.01, null(2)=0, distinct(3)=0.00999500175, null(3)=0.0001, distinct(4)=0.01, null(4)=0, distinct(5)=0.01, null(5)=0, distinct(6)=0.00999995009, null(6)=0.0001, distinct(5,6)=0.00999999509, null(5,6)=0, distinct(1-3)=0.0099995001, null(1-3)=0]
 ├── cost: 2106.0506
 ├── key: (4,5)
 ├── fd: (1,2)-->(3), (4,5)-->(6), (1)==(4), (4)==(1), (2)==(5), (5)==(2)
 ├── interesting orderings: (+1,+2)
 ├── scan t.public.abc
 │    ├── columns: t.public.abc.a:1(int!null) t.public.abc.b:2(int!null) t.public.abc.c:3(int)
 │    ├── stats: [rows=100, distinct(1)=100, null(1)=0, distinct(2)=10, null(2)=0, distinct(3)=10, null(3)=1, distinct(1-3)=100, null(1-3)=0]
 │    ├── cost: 106.02
 │    ├── key: (1,2)
 │    ├── fd: (1,2)-->(3)
 │    ├── prune: (1-3)
 │    └── interesting orderings: (+1,+2)
 └── filters (true)

# TODO(rytaft): The cardinality estimates for the semi-join are the same as the table.
# The semi-join currently ignores the selectivities of the filters in the On condition.
# We should fix this.
expr format=show-all colstat=5 colstat=6 colstat=(5, 6) colstat=1 colstat=2 colstat=3 colstat=(1, 2, 3)
(MakeLookupJoin
  (Scan [ (Table "abc") (Cols "a,b,c") ])
  [ (JoinType "semi-join") (Table "def") (Index "def@primary") (KeyCols "a,b") (Cols "a,b,c,d,e,f") ]
  [ ]
)
----
semi-join (lookup def)
 ├── columns: t.public.abc.a:1(int!null) t.public.abc.b:2(int!null) t.public.abc.c:3(int)
 ├── key columns: [1 2] = [4 5]
 ├── stats: [rows=100, distinct(1)=100, null(1)=0, distinct(2)=10, null(2)=0, distinct(3)=10, null(3)=1, distinct(5)=1, null(5)=0, distinct(6)=1, null(6)=0, distinct(5,6)=1, null(5,6)=0, distinct(1-3)=100, null(1-3)=0]
 ├── cost: 2106.0506
 ├── key: (1,2)
 ├── fd: (1,2)-->(3)
 ├── interesting orderings: (+1,+2)
 ├── scan t.public.abc
 │    ├── columns: t.public.abc.a:1(int!null) t.public.abc.b:2(int!null) t.public.abc.c:3(int)
 │    ├── stats: [rows=100, distinct(1)=100, null(1)=0, distinct(2)=10, null(2)=0, distinct(3)=10, null(3)=1, distinct(1-3)=100, null(1-3)=0]
 │    ├── cost: 106.02
 │    ├── key: (1,2)
 │    ├── fd: (1,2)-->(3)
 │    ├── prune: (1-3)
 │    └── interesting orderings: (+1,+2)
 └── filters (true)

expr format=show-all colstat=5 colstat=6 colstat=(5, 6) colstat=1 colstat=2 colstat=3 colstat=(1, 2, 3)
(MakeLookupJoin
  (Scan [ (Table "abc") (Cols "a,b,c") ])
  [ (JoinType "anti-join") (Table "def") (Index "def@primary") (KeyCols "a,b") (Cols "a,b,c,d,e,f") ]
  [ ]
)
----
anti-join (lookup def)
 ├── columns: t.public.abc.a:1(int!null) t.public.abc.b:2(int!null) t.public.abc.c:3(int)
 ├── key columns: [1 2] = [4 5]
 ├── stats: [rows=1e-10, distinct(1)=1e-10, null(1)=0, distinct(2)=1e-10, null(2)=0, distinct(3)=1e-10, null(3)=1e-10, distinct(5)=1e-10, null(5)=0, distinct(6)=1e-10, null(6)=0, distinct(5,6)=1e-10, null(5,6)=0, distinct(1-3)=1e-10, null(1-3)=0]
 ├── cost: 2106.0506
 ├── key: (1,2)
 ├── fd: (1,2)-->(3)
 ├── interesting orderings: (+1,+2)
 ├── scan t.public.abc
 │    ├── columns: t.public.abc.a:1(int!null) t.public.abc.b:2(int!null) t.public.abc.c:3(int)
 │    ├── stats: [rows=100, distinct(1)=100, null(1)=0, distinct(2)=10, null(2)=0, distinct(3)=10, null(3)=1, distinct(1-3)=100, null(1-3)=0]
 │    ├── cost: 106.02
 │    ├── key: (1,2)
 │    ├── fd: (1,2)-->(3)
 │    ├── prune: (1-3)
 │    └── interesting orderings: (+1,+2)
 └── filters (true)

expr format=show-all colstat=5 colstat=6 colstat=(5, 6) colstat=1 colstat=2 colstat=3 colstat=(1, 2, 3)
(MakeLookupJoin
  (Scan [ (Table "abc") (Cols "a,b,c") ])
  [ (JoinType "semi-join") (Table "def") (Index "def@primary") (KeyCols "a,b") (Cols "a,b,c,d,e,f") ]
  [ (False) ]
)
----
semi-join (lookup def)
 ├── columns: t.public.abc.a:1(int!null) t.public.abc.b:2(int!null) t.public.abc.c:3(int)
 ├── key columns: [1 2] = [4 5]
 ├── stats: [rows=0, distinct(1)=0, null(1)=0, distinct(2)=0, null(2)=0, distinct(3)=0, null(3)=0, distinct(5)=0, null(5)=0, distinct(6)=0, null(6)=0, distinct(5,6)=0, null(5,6)=0, distinct(1-3)=0, null(1-3)=0]
 ├── cost: 2106.0606
 ├── key: (1,2)
 ├── fd: (1,2)-->(3)
 ├── interesting orderings: (+1,+2)
 ├── scan t.public.abc
 │    ├── columns: t.public.abc.a:1(int!null) t.public.abc.b:2(int!null) t.public.abc.c:3(int)
 │    ├── stats: [rows=100, distinct(1)=100, null(1)=0, distinct(2)=10, null(2)=0, distinct(3)=10, null(3)=1, distinct(1-3)=100, null(1-3)=0]
 │    ├── cost: 106.02
 │    ├── key: (1,2)
 │    ├── fd: (1,2)-->(3)
 │    ├── prune: (1-3)
 │    └── interesting orderings: (+1,+2)
 └── filters
      └── false [type=bool]

expr format=show-all colstat=5 colstat=6 colstat=(5, 6) colstat=1 colstat=2 colstat=3 colstat=(1, 2, 3)
(MakeLookupJoin
  (Scan [ (Table "abc") (Cols "a,b,c") ])
  [ (JoinType "anti-join") (Table "def") (Index "def@primary") (KeyCols "a,b") (Cols "a,b,c,d,e,f") ]
  [ (False) ]
)
----
anti-join (lookup def)
 ├── columns: t.public.abc.a:1(int!null) t.public.abc.b:2(int!null) t.public.abc.c:3(int)
 ├── key columns: [1 2] = [4 5]
 ├── stats: [rows=100, distinct(1)=100, null(1)=0, distinct(2)=10, null(2)=0, distinct(3)=10, null(3)=1, distinct(5)=1, null(5)=0, distinct(6)=1, null(6)=0, distinct(5,6)=1, null(5,6)=0, distinct(1-3)=100, null(1-3)=0]
 ├── cost: 2106.0606
 ├── key: (1,2)
 ├── fd: (1,2)-->(3)
 ├── interesting orderings: (+1,+2)
 ├── scan t.public.abc
 │    ├── columns: t.public.abc.a:1(int!null) t.public.abc.b:2(int!null) t.public.abc.c:3(int)
 │    ├── stats: [rows=100, distinct(1)=100, null(1)=0, distinct(2)=10, null(2)=0, distinct(3)=10, null(3)=1, distinct(1-3)=100, null(1-3)=0]
 │    ├── cost: 106.02
 │    ├── key: (1,2)
 │    ├── fd: (1,2)-->(3)
 │    ├── prune: (1-3)
 │    └── interesting orderings: (+1,+2)
 └── filters
      └── false [type=bool]

# Regression test for #40460.
opt
SELECT
    *
FROM
    abc
    FULL JOIN (SELECT * FROM abc WHERE false) ON
            false
            IS NOT DISTINCT FROM not_like_escape(
                    '',
                    NULL::STRING,
                    (SELECT NULL)::STRING
                );
----
full-join (cross)
 ├── columns: a:1(int) b:2(int) c:3(int) a:4(int) b:5(int) c:6(int)
 ├── immutable
 ├── stats: [rows=100]
 ├── key: (1,2)
 ├── fd: (1,2)-->(3-6)
 ├── scan abc
 │    ├── columns: a:1(int!null) b:2(int!null) c:3(int)
 │    ├── stats: [rows=100]
 │    ├── key: (1,2)
 │    └── fd: (1,2)-->(3)
 ├── values
 │    ├── columns: a:4(int!null) b:5(int!null) c:6(int!null)
 │    ├── cardinality: [0 - 0]
 │    ├── stats: [rows=0]
 │    ├── key: ()
 │    └── fd: ()-->(4-6)
 └── filters
      └── is [type=bool, immutable, subquery]
           ├── function: not_like_escape [type=bool]
           │    ├── '' [type=string]
           │    ├── CAST(NULL AS STRING) [type=string]
           │    └── cast: STRING [type=string]
           │         └── subquery [type=unknown]
           │              └── values
           │                   ├── columns: "?column?":7(unknown)
           │                   ├── cardinality: [1 - 1]
           │                   ├── stats: [rows=1]
           │                   ├── key: ()
           │                   ├── fd: ()-->(7)
           │                   └── (NULL,) [type=tuple{unknown}]
           └── false [type=bool]

expr
(SemiJoin
    (Values
      [ (Tuple [ (Const 1 "int") (Const 2 "int") ] "tuple{int}" ) ]
      [ (Cols [ (NewColumn "a" "int") (NewColumn "b" "int") ]) ]
    )
    (Scan [ (Table "uv") (Cols "u,v,rowid") ])
    []
    []
)
----
semi-join (cross)
 ├── columns: a:1(int!null) b:2(int!null)
 ├── cardinality: [0 - 1]
 ├── stats: [rows=1]
 ├── key: ()
 ├── fd: ()-->(1,2)
 ├── values
 │    ├── columns: a:1(int!null) b:2(int!null)
 │    ├── cardinality: [1 - 1]
 │    ├── stats: [rows=1]
 │    ├── key: ()
 │    ├── fd: ()-->(1,2)
 │    └── (1, 2) [type=tuple{int}]
 ├── scan uv
 │    ├── columns: u:3(int) v:4(int!null) rowid:5(int!null)
 │    ├── stats: [rows=10000]
 │    ├── key: (5)
 │    └── fd: (5)-->(3,4)
 └── filters (true)

expr
(AntiJoin
    (Values
      [ (Tuple [ (Const 1 "int") (Const 2 "int") ] "tuple{int}" ) ]
      [ (Cols [ (NewColumn "a" "int") (NewColumn "b" "int") ]) ]
    )
    (Scan [ (Table "uv") (Cols "u,v,rowid") ])
    []
    []
)
----
anti-join (cross)
 ├── columns: a:1(int!null) b:2(int!null)
 ├── cardinality: [0 - 1]
 ├── stats: [rows=1e-10]
 ├── key: ()
 ├── fd: ()-->(1,2)
 ├── values
 │    ├── columns: a:1(int!null) b:2(int!null)
 │    ├── cardinality: [1 - 1]
 │    ├── stats: [rows=1]
 │    ├── key: ()
 │    ├── fd: ()-->(1,2)
 │    └── (1, 2) [type=tuple{int}]
 ├── scan uv
 │    ├── columns: u:3(int) v:4(int!null) rowid:5(int!null)
 │    ├── stats: [rows=10000]
 │    ├── key: (5)
 │    └── fd: (5)-->(3,4)
 └── filters (true)

exec-ddl
ALTER TABLE xysd INJECT STATISTICS '[
  {
    "columns": ["x"],
    "created_at": "2018-01-01 1:00:00.00000+00:00",
    "row_count": 5000,
    "distinct_count": 5000
  },
  {
    "columns": ["y"],
    "created_at": "2018-01-01 1:30:00.00000+00:00",
    "row_count": 5000,
    "distinct_count": 400
  },
  {
    "columns": ["s"],
    "created_at": "2018-01-01 1:30:00.00000+00:00",
    "row_count": 5000,
    "distinct_count": 10
  }
]'
----

exec-ddl
ALTER TABLE uv INJECT STATISTICS '[
  {
    "columns": ["u"],
    "created_at": "2018-01-01 1:00:00.00000+00:00",
    "row_count": 10000,
    "distinct_count": 500
  },
  {
    "columns": ["v"],
    "created_at": "2018-01-01 1:30:00.00000+00:00",
    "row_count": 10000,
    "distinct_count": 100
  },
  {
    "columns": ["u","v"],
    "created_at": "2018-01-01 1:30:00.00000+00:00",
    "row_count": 10000,
    "distinct_count": 550
  },
  {
    "columns": ["rowid"],
    "created_at": "2018-01-01 1:30:00.00000+00:00",
    "row_count": 10000,
    "distinct_count": 10000
  }
]'
----

# We use multi-column stats split across the join to estimate the selectivity
# here.
opt
SELECT * FROM xysd, uv WHERE (s = 'foo' AND u = 3 AND v = 4) OR (s = 'bar' AND u = 5 AND v = 6)
----
inner-join (cross)
 ├── columns: x:1(int!null) y:2(int) s:3(string!null) d:4(decimal!null) u:5(int!null) v:6(int!null)
 ├── stats: [rows=11979.6897, distinct(3)=2, null(3)=0, distinct(5)=2, null(5)=0, distinct(6)=2, null(6)=0, distinct(5,6)=2.19138756, null(5,6)=0]
 ├── fd: (1)-->(2-4), (3,4)-->(1,2)
 ├── scan uv
 │    ├── columns: u:5(int) v:6(int!null)
 │    └── stats: [rows=10000, distinct(5)=500, null(5)=0, distinct(6)=100, null(6)=0, distinct(5,6)=550, null(5,6)=0]
 ├── scan xysd
 │    ├── columns: x:1(int!null) y:2(int) s:3(string) d:4(decimal!null)
 │    ├── stats: [rows=5000, distinct(1)=5000, null(1)=0, distinct(3)=10, null(3)=0, distinct(4)=500, null(4)=0]
 │    ├── key: (1)
 │    └── fd: (1)-->(2-4), (3,4)~~>(1,2)
 └── filters
      └── (((s:3 = 'foo') AND (u:5 = 3)) AND (v:6 = 4)) OR (((s:3 = 'bar') AND (u:5 = 5)) AND (v:6 = 6)) [type=bool, outer=(3,5,6), constraints=(/3: [/'bar' - /'bar'] [/'foo' - /'foo']; /5: [/3 - /3] [/5 - /5]; /6: [/4 - /4] [/6 - /6])]