lookup_join

# LogicTest: 5node

statement ok
CREATE TABLE abc (a INT, b INT, c INT, PRIMARY KEY (a, c))

statement ok
CREATE TABLE def (d INT, e INT, f INT, PRIMARY KEY (f, e))

# Set up the statistics as if the first table is much smaller than the second.
# This will make lookup join into the second table be the best plan.
statement ok
ALTER TABLE abc INJECT STATISTICS '[
  {
    "columns": ["a"],
    "created_at": "2018-01-01 1:00:00.00000+00:00",
    "row_count": 100,
    "distinct_count": 100
  }
]'

statement ok
ALTER TABLE def INJECT STATISTICS '[
  {
    "columns": ["f"],
    "created_at": "2018-01-01 1:00:00.00000+00:00",
    "row_count": 10000,
    "distinct_count": 10000
  }
]'

query T
EXPLAIN (VERBOSE) SELECT * FROM abc JOIN def ON f = b
----
distribution: local
vectorized: true
·
• lookup join (inner)
│ columns: (a, b, c, d, e, f)
│ estimated row count: 99
│ table: def@def_pkey
│ equality: (b) = (f)
│
└── • scan
      columns: (a, b, c)
      estimated row count: 100 (100% of the table; stats collected <hidden> ago)
      table: abc@abc_pkey
      spans: FULL SCAN

query T
EXPLAIN (VERBOSE) SELECT * FROM abc JOIN def ON f = b AND e = c
----
distribution: local
vectorized: true
·
• lookup join (inner)
│ columns: (a, b, c, d, e, f)
│ estimated row count: 0
│ table: def@def_pkey
│ equality: (b, c) = (f,e)
│ equality cols are key
│
└── • scan
      columns: (a, b, c)
      estimated row count: 100 (100% of the table; stats collected <hidden> ago)
      table: abc@abc_pkey
      spans: FULL SCAN

query T
EXPLAIN (VERBOSE) SELECT * FROM abc JOIN def ON f = b WHERE a > 1 AND e > 1
----
distribution: local
vectorized: true
·
• lookup join (inner)
│ columns: (a, b, c, d, e, f)
│ estimated row count: 33
│ table: def@def_pkey
│ lookup condition: (f = b) AND (e > 1)
│
└── • scan
      columns: (a, b, c)
      estimated row count: 33 (33% of the table; stats collected <hidden> ago)
      table: abc@abc_pkey
      spans: /2-

query T
EXPLAIN (VERBOSE) SELECT * FROM abc JOIN def ON f = a WHERE f > 1
----
distribution: local
vectorized: true
·
• lookup join (inner)
│ columns: (a, b, c, d, e, f)
│ estimated row count: 33
│ table: def@def_pkey
│ equality: (a) = (f)
│ pred: f > 1
│
└── • scan
      columns: (a, b, c)
      estimated row count: 33 (33% of the table; stats collected <hidden> ago)
      table: abc@abc_pkey
      spans: /2-

query T
EXPLAIN (VERBOSE) SELECT * FROM abc JOIN def ON f = b WHERE a >= e
----
distribution: local
vectorized: true
·
• lookup join (inner)
│ columns: (a, b, c, d, e, f)
│ estimated row count: 33
│ table: def@def_pkey
│ equality: (b) = (f)
│ pred: a >= e
│
└── • scan
      columns: (a, b, c)
      estimated row count: 100 (100% of the table; stats collected <hidden> ago)
      table: abc@abc_pkey
      spans: FULL SCAN

query T
EXPLAIN (VERBOSE) SELECT * FROM abc JOIN def ON f = b AND a >= e
----
distribution: local
vectorized: true
·
• lookup join (inner)
│ columns: (a, b, c, d, e, f)
│ estimated row count: 33
│ table: def@def_pkey
│ equality: (b) = (f)
│ pred: a >= e
│
└── • scan
      columns: (a, b, c)
      estimated row count: 100 (100% of the table; stats collected <hidden> ago)
      table: abc@abc_pkey
      spans: FULL SCAN

# Verify a distsql plan.
statement ok
CREATE TABLE data (a INT, b INT, c INT, d INT, PRIMARY KEY (a, b, c, d))

# Split into ten parts.
statement ok
ALTER TABLE data SPLIT AT SELECT i FROM generate_series(1, 9) AS g(i)

statement ok
ALTER TABLE data INJECT STATISTICS '[
  {
    "columns": ["a"],
    "created_at": "2018-01-01 1:00:00.00000+00:00",
    "row_count": 100000,
    "distinct_count": 100000
  }
]'

# Relocate the ten parts to the five nodes.
statement ok
ALTER TABLE data EXPERIMENTAL_RELOCATE
  SELECT ARRAY[i%5+1], i FROM generate_series(0, 9) AS g(i)

# Verify data placement.
query TTTI colnames,rowsort
SELECT start_key, end_key, replicas, lease_holder from [SHOW RANGES FROM TABLE data]
----
start_key  end_key  replicas  lease_holder
NULL       /1       {1}       1
/1         /2       {2}       2
/2         /3       {3}       3
/3         /4       {4}       4
/4         /5       {5}       5
/5         /6       {1}       1
/6         /7       {2}       2
/7         /8       {3}       3
/8         /9       {4}       4
/9         NULL     {5}       5

query T
EXPLAIN (VERBOSE)
SELECT *
FROM (SELECT * FROM data WHERE c = 1) AS l
NATURAL JOIN (SELECT * FROM data WHERE c > 0) AS r
----
distribution: full
vectorized: true
·
• project
│ columns: (a, b, c, d)
│ estimated row count: 0
│
└── • lookup join (inner)
    │ columns: (a, b, c, d, a, b, c, d)
    │ estimated row count: 0
    │ table: data@data_pkey
    │ equality: (a, b, c, d) = (a,b,c,d)
    │ equality cols are key
    │ pred: c > 0
    │
    └── • filter
        │ columns: (a, b, c, d)
        │ estimated row count: 10
        │ filter: c = 1
        │
        └── • scan
              columns: (a, b, c, d)
              estimated row count: 100,000 (100% of the table; stats collected <hidden> ago)
              table: data@data_pkey
              spans: FULL SCAN

query T
EXPLAIN (DISTSQL)
SELECT *
FROM (SELECT * FROM data WHERE c = 1) AS l
NATURAL JOIN (SELECT * FROM data WHERE c > 0) AS r
----
distribution: full
vectorized: true
·
• lookup join
│ estimated row count: 0
│ table: data@data_pkey
│ equality: (a, b, c, d) = (a,b,c,d)
│ equality cols are key
│ pred: c > 0
│
└── • filter
    │ estimated row count: 10
    │ filter: c = 1
    │
    └── • scan
          estimated row count: 100,000 (100% of the table; stats collected <hidden> ago)
          table: data@data_pkey
          spans: FULL SCAN
·
Diagram: https://cockroachdb.github.io/distsqlplan/decode.html#eJzElV9vmzwYxe_fT_HouWpfGYH506aWJhF1qZYqI12SaZO2XFCwOjaKmQFpU5XvPgFbGqrWZiHKLm18OMfP70h-wOJ7igwnH29m42kAJ6-ny9Xy3ewUlpPZ5HIF_8PVYv4WTrrLOCxD-PBmsphABK-AnsJ4CSkE49X7xXgG1_P6VwrJ58qyHA5Wo5NIMBMxD8J7XiD7hBQJ2kjQQYIuEvRwTTCXIuJFIWR95KERTOMfyCyCSZZXZb29JhgJyZE9YJmUKUeGq_A25QsexlyaFhKMeRkmaWNTJ_JzmdyH8icSvBRpdZ8VDEICtwQiAjESXOZhvWeYFMIsBgqi_MIlrjcERVU--hZleMeR0Q3pn-0qSUsuuTS9brB2n4Hv1LNljE2D1ehFS_tvLK9Fkv2eBlWPYybEtyqHryLJQGQMfEp8m_gO8Wsk8wD88y3GPxEJzquye_Sl1M6LqR_DVpmQMZc87gRdb565VyAMkZvUe3LyeW-34037F4juXyCTGqa9R4U06bYVOjtchTSWuxVSD-SoFbL7Y7QHYLQN09kDoybdFuP54TBqLHcxqgdyVIxOf4zOAIyOYbp7YNSk22IcHQ6jxnIXo3ogR8Xo9sfoDsDoGqa3B0ZNui3Gi8Nh1FjuYlQP5J-9y8-kXvAiF1nBe726Vv1u8_iOt498ISoZ8RsposamXc4bXbMR86Jsv9J2Mc3aT3XAXTFVim212FaKnY6YPhU76thnamtXqfbUYk8p1jifDbn0uVI8UjuPlOILtfhiSGyq6ZiuZOqWUU3N6KCeUU3RXI25umlUUzWq7trT7OvNf78CAAD__-FRh80=

statement ok
CREATE TABLE books (title STRING, edition INT, shelf INT, PRIMARY KEY (title, edition))

statement ok
CREATE TABLE books2 (title STRING, edition INT, shelf INT, PRIMARY KEY (title, edition))

statement ok
ALTER TABLE books INJECT STATISTICS '[
  {
    "columns": ["title"],
    "created_at": "2018-01-01 1:00:00.00000+00:00",
    "row_count": 100,
    "distinct_count": 100
  }
]'

statement ok
ALTER TABLE books2 INJECT STATISTICS '[
  {
    "columns": ["title"],
    "created_at": "2018-01-01 1:00:00.00000+00:00",
    "row_count": 10000,
    "distinct_count": 1000
  }
]'

query T
EXPLAIN (VERBOSE) SELECT DISTINCT b1.title FROM books as b1 JOIN books2 as b2 ON b1.title = b2.title WHERE b1.shelf <> b2.shelf
----
distribution: local
vectorized: true
·
• distinct
│ columns: (title)
│ estimated row count: 100
│ distinct on: title
│ order key: title
│
└── • project
    │ columns: (title)
    │ ordering: +title
    │
    └── • lookup join (inner)
        │ columns: (title, shelf, title, shelf)
        │ ordering: +title
        │ estimated row count: 327
        │ table: books2@books2_pkey
        │ equality: (title) = (title)
        │ pred: shelf != shelf
        │
        └── • scan
              columns: (title, shelf)
              ordering: +title
              estimated row count: 100 (100% of the table; stats collected <hidden> ago)
              table: books@books_pkey
              spans: FULL SCAN

statement ok
CREATE TABLE authors (name STRING, book STRING)

statement ok
ALTER TABLE authors INJECT STATISTICS '[
  {
    "columns": ["name"],
    "created_at": "2018-01-01 1:00:00.00000+00:00",
    "row_count": 100,
    "distinct_count": 100
  }
]'

query T
EXPLAIN (VERBOSE) SELECT DISTINCT authors.name FROM books AS b1, books2 AS b2, authors WHERE b1.title = b2.title AND authors.book = b1.title AND b1.shelf <> b2.shelf
----
distribution: local
vectorized: true
·
• distinct
│ columns: (name)
│ estimated row count: 96
│ distinct on: name
│
└── • project
    │ columns: (name)
    │
    └── • lookup join (inner)
        │ columns: (title, shelf, name, book, title, shelf)
        │ estimated row count: 323
        │ table: books2@books2_pkey
        │ equality: (book) = (title)
        │ pred: shelf != shelf
        │
        └── • hash join (inner)
            │ columns: (title, shelf, name, book)
            │ estimated row count: 99
            │ equality: (title) = (book)
            │
            ├── • scan
            │     columns: (title, shelf)
            │     estimated row count: 100 (100% of the table; stats collected <hidden> ago)
            │     table: books@books_pkey
            │     spans: FULL SCAN
            │
            └── • scan
                  columns: (name, book)
                  estimated row count: 100 (100% of the table; stats collected <hidden> ago)
                  table: authors@authors_pkey
                  spans: FULL SCAN

# Verify data placement.
query TTTI colnames
SELECT start_key, end_key, replicas, lease_holder from [SHOW RANGES FROM TABLE books]
----
start_key  end_key  replicas  lease_holder
NULL       NULL     {5}       5

query T
EXPLAIN (DISTSQL) SELECT DISTINCT authors.name FROM books AS b1, books2 AS b2, authors WHERE b1.title = b2.title AND authors.book = b1.title AND b1.shelf <> b2.shelf
----
distribution: local
vectorized: true
·
• distinct
│ estimated row count: 96
│ distinct on: name
│
└── • lookup join
    │ estimated row count: 323
    │ table: books2@books2_pkey
    │ equality: (book) = (title)
    │ pred: shelf != shelf
    │
    └── • hash join
        │ estimated row count: 99
        │ equality: (title) = (book)
        │
        ├── • scan
        │     estimated row count: 100 (100% of the table; stats collected <hidden> ago)
        │     table: books@books_pkey
        │     spans: FULL SCAN
        │
        └── • scan
              estimated row count: 100 (100% of the table; stats collected <hidden> ago)
              table: authors@authors_pkey
              spans: FULL SCAN
·
Diagram: https://cockroachdb.github.io/distsqlplan/decode.html#eJyck99v2jAQx9_3V9zuKUheS5zQSZEqBQFTmRh0gLRJEw8JuULWYGe2I21C_O-THboWBFnpW-7H5_y9H9mi_lVghIPv96PucAxefzibz76OWjAbjAa9OVh7OO7NIanMWip9JZINwafp5AukUj5q6M4g9VltcGdx9pQM3-4G0wF4XupfmdwUBLeQ8vqzBd1xH7ynspa30X1iax9O_Su9puIB3jvSfbeQoZAZjZMNaYx-oI8LhqWSS9JaKuvauoRh9hujNsNclJWx7gXDpVSE0RbdKxjhPEkLmlKSkbpuI8OMTJIXrqxrKS5VvknUH2TYk0W1EToCxzJwYpDhrEys9wMudgxlZZ4f0yZZEUb-jr1NkH8oaD-rU5LsVuolvEYQPyvoWUclpMpIUXagYWHJ_6Wc6Oou0evPMhekrvlhUwU9GC_2W7cqX62NF3O73kllIoh9FnMWBywOz3YSXDJaK2A_2eDEqvmLwY6kfKxK-ClzAVJEEIdW1Rhibi8x_vhPY3BWWniJtH6uTS6W5jo8FBb7Z-t33lS_8-r6N5fUn5IupdB0fCwnK7fthVC2ovritKzUku6VXLpnanPiOOfISJs6ymtjKFzI_VYvYf8CmB_DvBEODuD2MRw0wmEzHDbCnWa40wjfHMGL3bu_AQAA__-GCO68

query T
EXPLAIN (VERBOSE) SELECT a.name FROM authors AS a JOIN books2 AS b2 ON a.book = b2.title ORDER BY a.name
----
distribution: local
vectorized: true
·
• project
│ columns: (name)
│ ordering: +name
│ estimated row count: 990
│
└── • lookup join (inner)
    │ columns: (name, book, title)
    │ ordering: +name
    │ estimated row count: 990
    │ table: books2@books2_pkey
    │ equality: (book) = (title)
    │
    └── • sort
        │ columns: (name, book)
        │ ordering: +name
        │ estimated row count: 100
        │ order: +name
        │
        └── • scan
              columns: (name, book)
              estimated row count: 100 (100% of the table; stats collected <hidden> ago)
              table: authors@authors_pkey
              spans: FULL SCAN

# Cross joins should not be planned as lookup joins.
query T
EXPLAIN (VERBOSE) SELECT * FROM books CROSS JOIN books2
----
distribution: local
vectorized: true
·
• cross join (inner)
│ columns: (title, edition, shelf, title, edition, shelf)
│ estimated row count: 1,000,000
│
├── • scan
│     columns: (title, edition, shelf)
│     estimated row count: 10,000 (100% of the table; stats collected <hidden> ago)
│     table: books2@books2_pkey
│     spans: FULL SCAN
│
└── • scan
      columns: (title, edition, shelf)
      estimated row count: 100 (100% of the table; stats collected <hidden> ago)
      table: books@books_pkey
      spans: FULL SCAN

query T
EXPLAIN (DISTSQL) SELECT * FROM authors INNER JOIN books2 ON books2.edition = 1 WHERE books2.title = authors.book
----
distribution: local
vectorized: true
·
• lookup join
│ table: books2@books2_pkey
│ equality: (book, lookup_join_const_col_@7) = (title,edition)
│ equality cols are key
│
└── • render
    │ estimated row count: 100
    │
    └── • scan
          estimated row count: 100 (100% of the table; stats collected <hidden> ago)
          table: authors@authors_pkey
          spans: FULL SCAN
·
Diagram: https://cockroachdb.github.io/distsqlplan/decode.html#eJyUklFr1EAQx9_9FMM8qUzbS05FFgqRGjHlTGruQEHysL0d6trcTtzdgHLcd5ckd9gWerRvmf_k958fIVsMv1tUmH-_WnwoSnj5sViull8Xr2CZL_KLFbyGT3X1BXQff4oPUJRlXsNlVZRwLXIbUqgOT6dsbLTi4BwS-PY5r_PDItrYMpwfSk6HGAmdGC71hgOqH5hgQ9h5WXMI4odoO75QmD-oZoTWdX0c4oZwLZ5RbXHsRYUrfd1yzdqwP5shoeGobTvW7k9mnbcb7f8i4YW0_cYFBU5vmGDvsuz0kJ0gYc3OsFeQKKWKcvWeIEsIshSbHaH08b9HiPqGUSU7errrpVi3V03uq04f647pQuS27-CXWAfiFGRzygam6qOCLKVhfkPZW8rePeqWPsetlBPpztJ7Wo8Vz59TXHPoxAV-UvNs1xCyueHpJwjS-zVfeVmPZ6axGrkxMBzitE2moXDTahC8CydH4fQ4nB6F5w_gZvfiXwAAAP__9_0Uqw==

####################################
#  LOOKUP JOIN ON SECONDARY INDEX  #
####################################

statement ok
CREATE TABLE small (a INT PRIMARY KEY, b INT, c INT, d INT)

statement ok
CREATE TABLE large (a INT, b INT, c INT, d INT, PRIMARY KEY (a, b), INDEX bc (b) STORING (c))

statement ok
ALTER TABLE small SPLIT AT SELECT i FROM generate_series(1, 9) AS g(i)

statement ok
ALTER TABLE small EXPERIMENTAL_RELOCATE
  SELECT ARRAY[i%5+1], i FROM generate_series(0, 9) AS g(i)

statement ok
INSERT INTO small SELECT x, 2*x, 3*x, 4*x FROM
  generate_series(1, 10) AS a(x)

statement ok
ALTER TABLE small INJECT STATISTICS '[
  {
    "columns": ["a"],
    "created_at": "2018-01-01 1:00:00.00000+00:00",
    "row_count": 100,
    "distinct_count": 100
  }
]'

statement ok
ALTER TABLE large INJECT STATISTICS '[
  {
    "columns": ["a"],
    "created_at": "2018-01-01 1:00:00.00000+00:00",
    "row_count": 10000,
    "distinct_count": 10000
  }
]'

# Lookup join on covering secondary index
query T
EXPLAIN (VERBOSE) SELECT small.a, large.c FROM small JOIN large ON small.a = large.b
----
distribution: full
vectorized: true
·
• project
│ columns: (a, c)
│ estimated row count: 1,000
│
└── • lookup join (inner)
    │ columns: (a, b, c)
    │ estimated row count: 1,000
    │ table: large@bc
    │ equality: (a) = (b)
    │
    └── • scan
          columns: (a)
          estimated row count: 100 (100% of the table; stats collected <hidden> ago)
          table: small@small_pkey
          spans: FULL SCAN

# Lookup join on non-covering secondary index
query T
EXPLAIN (VERBOSE) SELECT small.a, large.d FROM small JOIN large ON small.a = large.b
----
distribution: full
vectorized: true
·
• project
│ columns: (a, d)
│ estimated row count: 1,000
│
└── • project
    │ columns: (a, b, d)
    │ estimated row count: 1,000
    │
    └── • lookup join (inner)
        │ columns: (a, a, b, d)
        │ table: large@large_pkey
        │ equality: (a, b) = (a,b)
        │ equality cols are key
        │
        └── • lookup join (inner)
            │ columns: (a, a, b)
            │ estimated row count: 1,000
            │ table: large@bc
            │ equality: (a) = (b)
            │
            └── • scan
                  columns: (a)
                  estimated row count: 100 (100% of the table; stats collected <hidden> ago)
                  table: small@small_pkey
                  spans: FULL SCAN

############################
#  LEFT OUTER LOOKUP JOIN  #
############################

# Left join against primary index
query T
EXPLAIN (VERBOSE) SELECT small.b, large.a FROM small LEFT JOIN large ON small.b = large.a
----
distribution: full
vectorized: true
·
• lookup join (left outer)
│ columns: (b, a)
│ estimated row count: 100
│ table: large@large_pkey
│ equality: (b) = (a)
│
└── • scan
      columns: (b)
      estimated row count: 100 (100% of the table; stats collected <hidden> ago)
      table: small@small_pkey
      spans: FULL SCAN

# Left join should preserve input order.
query T
EXPLAIN (VERBOSE) SELECT t1.a, t2.b FROM small t1 LEFT JOIN large t2 ON t1.a = t2.a AND t2.b % 6 = 0 ORDER BY t1.a
----
distribution: full
vectorized: true
·
• project
│ columns: (a, b)
│ ordering: +a
│ estimated row count: 100
│
└── • lookup join (left outer)
    │ columns: (a, a, b)
    │ ordering: +a
    │ estimated row count: 100
    │ table: large@large_pkey
    │ equality: (a) = (a)
    │ pred: (b % 6) = 0
    │
    └── • scan
          columns: (a)
          ordering: +a
          estimated row count: 100 (100% of the table; stats collected <hidden> ago)
          table: small@small_pkey
          spans: FULL SCAN

query T
EXPLAIN (DISTSQL) SELECT t1.a, t2.b FROM small t1 LEFT JOIN large t2 ON t1.a = t2.a AND t2.b % 6 = 0 ORDER BY t1.a
----
distribution: full
vectorized: true
·
• lookup join (left outer)
│ estimated row count: 100
│ table: large@large_pkey
│ equality: (a) = (a)
│ pred: (b % 6) = 0
│
└── • scan
      estimated row count: 100 (100% of the table; stats collected <hidden> ago)
      table: small@small_pkey
      spans: FULL SCAN
·
Diagram: https://cockroachdb.github.io/distsqlplan/decode.html#eJzMlV9r2zwUxu_fT3E48ELClDqynTQVFNK1KaRkTpd4sDFyocai8-ZYnizDSsl3H5LL6pTFzpyb3Onfo-c5vyPQM-Y_E2Q4-Xw_u5oG0LmZLsPlx1kXlpPZ5DoETc84Ae2ePcDtYv4B8g1PErhagqYwm9yGcDefBpBw9SjsqgvzADpGBZdGxrtwFdxAp2Ov-B-GXbiEfhfmi5vJAt5_sQZIMJWRCPhG5Mi-IkWCLhL0kKCPBAe4IpgpuRZ5LpU58mwF0-gXsj7BOM0KbZZXBNdSCWTPqGOdCGQY8odELASPhHL6SDASmseJtbG1jDMVb7h6QoLXMik2ac7ABFpm3Ax7DgWeRkBB6m9C4WpLUBb61S_X_FEgo1tyeKY7GacvkQa7kcKnTLAS7PxTOFlYvEjQAq5EnUn5o8jgu4xTkCmDsUFmyI89A5kxNg3CkWX9Mjb7hTYnydglY29vJe7eSl4LkCoSSkS72cf0Ha62fyk3kD2ZOfQN_T9p9kfxdqLQwxtN_73RDu05botWN6SqtHp44q12D-frtuDr9hyvBd-GVBW-5yfO1zucr9eCr9dz_BZ8G1JV-I5OnK9_OF-_BV-_5wxa8G1IVeF7ceJ8Gz61hcgzmeZip4p9N_fNPyGiR1H-K7ks1FrcK7m2NuV0bnV2IRK5LndpOZmm5ZYJWBXTWrG7I6ZvxW69c4O1V6v268X-MbkHteJhvfPwGOfzWvGo3nl0jPNFfa_6Dc-k_pG99V5t__sdAAD__3h7gNM=

# Left join against covering secondary index
query T
EXPLAIN (VERBOSE) SELECT small.c, large.c FROM small LEFT JOIN large ON small.c = large.b
----
distribution: full
vectorized: true
·
• project
│ columns: (c, c)
│ estimated row count: 1,000
│
└── • lookup join (left outer)
    │ columns: (c, b, c)
    │ estimated row count: 1,000
    │ table: large@bc
    │ equality: (c) = (b)
    │
    └── • scan
          columns: (c)
          estimated row count: 100 (100% of the table; stats collected <hidden> ago)
          table: small@small_pkey
          spans: FULL SCAN

# Left join against non-covering secondary index
query T
EXPLAIN (VERBOSE) SELECT small.c, large.d FROM small LEFT JOIN large ON small.c = large.b
----
distribution: full
vectorized: true
·
• project
│ columns: (c, d)
│ estimated row count: 1,000
│
└── • project
    │ columns: (c, b, d)
    │ estimated row count: 1,000
    │
    └── • lookup join (left outer)
        │ columns: (c, a, b, d)
        │ table: large@large_pkey
        │ equality: (a, b) = (a,b)
        │ equality cols are key
        │
        └── • lookup join (left outer)
            │ columns: (c, a, b)
            │ estimated row count: 1,000
            │ table: large@bc
            │ equality: (c) = (b)
            │
            └── • scan
                  columns: (c)
                  estimated row count: 100 (100% of the table; stats collected <hidden> ago)
                  table: small@small_pkey
                  spans: FULL SCAN

# Left join with ON filter on covering index
query T
EXPLAIN (VERBOSE) SELECT small.c, large.c FROM small LEFT JOIN large ON small.c = large.b AND large.c < 20
----
distribution: full
vectorized: true
·
• project
│ columns: (c, c)
│ estimated row count: 336
│
└── • lookup join (left outer)
    │ columns: (c, b, c)
    │ estimated row count: 336
    │ table: large@bc
    │ equality: (c) = (b)
    │ pred: c < 20
    │
    └── • scan
          columns: (c)
          estimated row count: 100 (100% of the table; stats collected <hidden> ago)
          table: small@small_pkey
          spans: FULL SCAN

# Left join with ON filter on non-covering index
query T
EXPLAIN (VERBOSE) SELECT small.c, large.d FROM small LEFT JOIN large ON small.c = large.b AND large.d < 30
----
distribution: full
vectorized: true
·
• project
│ columns: (c, d)
│ estimated row count: 336
│
└── • project
    │ columns: (c, b, d)
    │ estimated row count: 336
    │
    └── • lookup join (left outer)
        │ columns: (c, a, b, cont, d)
        │ table: large@large_pkey
        │ equality: (a, b) = (a,b)
        │ equality cols are key
        │ pred: d < 30
        │
        └── • lookup join (left outer)
            │ columns: (c, a, b, cont)
            │ estimated row count: 1,000
            │ table: large@bc
            │ equality: (c) = (b)
            │
            └── • scan
                  columns: (c)
                  estimated row count: 100 (100% of the table; stats collected <hidden> ago)
                  table: small@small_pkey
                  spans: FULL SCAN

# Left semi-join with ON filter on non-covering index
query T
EXPLAIN (VERBOSE) SELECT small.c FROM small WHERE EXISTS(SELECT 1 FROM large WHERE small.c = large.b AND large.d < 30)
----
distribution: full
vectorized: true
·
• project
│ columns: (c)
│ estimated row count: 100
│
└── • lookup join (semi)
    │ columns: (c, a, b, cont)
    │ table: large@large_pkey
    │ equality: (a, b) = (a,b)
    │ equality cols are key
    │ pred: d < 30
    │
    └── • lookup join (inner)
        │ columns: (c, a, b, cont)
        │ estimated row count: 990
        │ table: large@bc
        │ equality: (c) = (b)
        │
        └── • scan
              columns: (c)
              estimated row count: 100 (100% of the table; stats collected <hidden> ago)
              table: small@small_pkey
              spans: FULL SCAN

# Left anti-join with ON filter on non-covering index
query T
EXPLAIN (VERBOSE) SELECT small.c FROM small WHERE NOT EXISTS(SELECT 1 FROM large WHERE small.c = large.b AND large.d < small.a)
----
distribution: full
vectorized: true
·
• project
│ columns: (c)
│ estimated row count: 67
│
└── • project
    │ columns: (a, c)
    │ estimated row count: 67
    │
    └── • lookup join (anti)
        │ columns: (a, c, a, b, cont)
        │ table: large@large_pkey
        │ equality: (a, b) = (a,b)
        │ equality cols are key
        │ pred: d < a
        │
        └── • lookup join (left outer)
            │ columns: (a, c, a, b, cont)
            │ estimated row count: 1,000
            │ table: large@bc
            │ equality: (c) = (b)
            │
            └── • scan
                  columns: (a, c)
                  estimated row count: 100 (100% of the table; stats collected <hidden> ago)
                  table: small@small_pkey
                  spans: FULL SCAN

###########################################################
#  LOOKUP JOINS ON IMPLICIT INDEX KEY COLUMNS             #
#  https://github.com/cockroachdb/cockroach/issues/31777  #
###########################################################
statement ok
CREATE TABLE t (a INT, b INT, c INT, d INT, e INT)

statement ok
CREATE TABLE u (a INT, b INT, c INT, d INT, e INT, PRIMARY KEY (a DESC, b, c))

# Test index with all primary key columns implicit.
statement ok
CREATE INDEX idx ON u (d)

query T
EXPLAIN (VERBOSE) SELECT u.a FROM t JOIN u ON t.d = u.d AND t.a = u.a WHERE t.e = 5
----
distribution: full
vectorized: true
·
• project
│ columns: (a)
│ estimated row count: 1 (missing stats)
│
└── • lookup join (inner)
    │ columns: (a, d, e, a, d)
    │ estimated row count: 1 (missing stats)
    │ table: u@idx
    │ equality: (d, a) = (d,a)
    │
    └── • filter
        │ columns: (a, d, e)
        │ estimated row count: 10 (missing stats)
        │ filter: e = 5
        │
        └── • scan
              columns: (a, d, e)
              estimated row count: 1,000 (missing stats)
              table: t@t_pkey
              spans: FULL SCAN

# Test unique version of same index. (Lookup join should not use column a.)
statement ok
DROP INDEX u@idx

statement ok
CREATE UNIQUE INDEX idx ON u (d)

query T
EXPLAIN (VERBOSE) SELECT u.a FROM t JOIN u ON t.d = u.d AND t.a = u.a WHERE t.e = 5
----
distribution: full
vectorized: true
·
• project
│ columns: (a)
│ estimated row count: 0 (missing stats)
│
└── • lookup join (inner)
    │ columns: (a, d, e, a, d)
    │ estimated row count: 0 (missing stats)
    │ table: u@idx
    │ equality: (d) = (d)
    │ equality cols are key
    │ pred: a = a
    │
    └── • filter
        │ columns: (a, d, e)
        │ estimated row count: 10 (missing stats)
        │ filter: e = 5
        │
        └── • scan
              columns: (a, d, e)
              estimated row count: 1,000 (missing stats)
              table: t@t_pkey
              spans: FULL SCAN

# Test index with first primary key column explicit and the rest implicit.
statement ok
DROP INDEX u@idx CASCADE

statement ok
CREATE INDEX idx ON u (d, a)

query T
EXPLAIN (VERBOSE) SELECT u.a FROM t JOIN u ON t.d = u.d AND t.a = u.a AND t.b = u.b WHERE t.e = 5
----
distribution: full
vectorized: true
·
• project
│ columns: (a)
│ estimated row count: 0 (missing stats)
│
└── • lookup join (inner)
    │ columns: (a, b, d, e, a, b, d)
    │ estimated row count: 0 (missing stats)
    │ table: u@idx
    │ equality: (d, a, b) = (d,a,b)
    │
    └── • filter
        │ columns: (a, b, d, e)
        │ estimated row count: 10 (missing stats)
        │ filter: e = 5
        │
        └── • scan
              columns: (a, b, d, e)
              estimated row count: 1,000 (missing stats)
              table: t@t_pkey
              spans: FULL SCAN

# Test index with middle primary key column explicit and the rest implicit.
statement ok
DROP INDEX u@idx

statement ok
CREATE INDEX idx ON u (d, b)

query T
EXPLAIN (VERBOSE) SELECT u.a FROM t JOIN u ON t.d = u.d AND t.a = u.a AND t.b = u.b WHERE t.e = 5
----
distribution: full
vectorized: true
·
• project
│ columns: (a)
│ estimated row count: 0 (missing stats)
│
└── • lookup join (inner)
    │ columns: (a, b, d, e, a, b, d)
    │ estimated row count: 0 (missing stats)
    │ table: u@idx
    │ equality: (d, b, a) = (d,b,a)
    │
    └── • filter
        │ columns: (a, b, d, e)
        │ estimated row count: 10 (missing stats)
        │ filter: e = 5
        │
        └── • scan
              columns: (a, b, d, e)
              estimated row count: 1,000 (missing stats)
              table: t@t_pkey
              spans: FULL SCAN

# Test index with last primary key column explicit and the rest implicit.
statement ok
DROP INDEX u@idx

statement ok
CREATE INDEX idx ON u (d, c)

query T
EXPLAIN (VERBOSE) SELECT u.a FROM t JOIN u ON t.d = u.d AND t.a = u.a AND t.d = u.d WHERE t.e = 5
----
distribution: full
vectorized: true
·
• project
│ columns: (a)
│ estimated row count: 1 (missing stats)
│
└── • lookup join (inner)
    │ columns: (a, d, e, a, d)
    │ estimated row count: 1 (missing stats)
    │ table: u@idx
    │ equality: (d) = (d)
    │ pred: a = a
    │
    └── • filter
        │ columns: (a, d, e)
        │ estimated row count: 10 (missing stats)
        │ filter: e = 5
        │
        └── • scan
              columns: (a, d, e)
              estimated row count: 1,000 (missing stats)
              table: t@t_pkey
              spans: FULL SCAN

query T
EXPLAIN (VERBOSE) SELECT * FROM def JOIN abc ON a=f ORDER BY a
----
distribution: local
vectorized: true
·
• lookup join (inner)
│ columns: (d, e, f, a, b, c)
│ ordering: +a
│ estimated row count: 100
│ table: def@def_pkey
│ equality: (a) = (f)
│
└── • scan
      columns: (a, b, c)
      ordering: +a
      estimated row count: 100 (100% of the table; stats collected <hidden> ago)
      table: abc@abc_pkey
      spans: FULL SCAN

# Test that we don't get a lookup join if we force a merge join.
query T
EXPLAIN (VERBOSE) SELECT * FROM def INNER MERGE JOIN abc ON a=f ORDER BY a
----
distribution: local
vectorized: true
·
• merge join (inner)
│ columns: (d, e, f, a, b, c)
│ ordering: +f
│ estimated row count: 100
│ equality: (f) = (a)
│ merge ordering: +"(f=a)"
│
├── • scan
│     columns: (d, e, f)
│     ordering: +f
│     estimated row count: 10,000 (100% of the table; stats collected <hidden> ago)
│     table: def@def_pkey
│     spans: FULL SCAN
│
└── • scan
      columns: (a, b, c)
      ordering: +a
      estimated row count: 100 (100% of the table; stats collected <hidden> ago)
      table: abc@abc_pkey
      spans: FULL SCAN

# Test that we don't get a lookup join if we force a hash join.
query T
EXPLAIN (VERBOSE) SELECT * FROM def INNER HASH JOIN abc ON a=f ORDER BY a
----
distribution: local
vectorized: true
·
• sort
│ columns: (d, e, f, a, b, c)
│ ordering: +f
│ estimated row count: 100
│ order: +f
│
└── • hash join (inner)
    │ columns: (d, e, f, a, b, c)
    │ estimated row count: 100
    │ equality: (f) = (a)
    │
    ├── • scan
    │     columns: (d, e, f)
    │     estimated row count: 10,000 (100% of the table; stats collected <hidden> ago)
    │     table: def@def_pkey
    │     spans: FULL SCAN
    │
    └── • scan
          columns: (a, b, c)
          estimated row count: 100 (100% of the table; stats collected <hidden> ago)
          table: abc@abc_pkey
          spans: FULL SCAN

# Test lookup semi and anti join.
query T
EXPLAIN (VERBOSE) SELECT * from abc WHERE EXISTS (SELECT * FROM def WHERE a=f)
----
distribution: local
vectorized: true
·
• lookup join (semi)
│ columns: (a, b, c)
│ estimated row count: 100
│ table: def@def_pkey
│ equality: (a) = (f)
│
└── • scan
      columns: (a, b, c)
      estimated row count: 100 (100% of the table; stats collected <hidden> ago)
      table: abc@abc_pkey
      spans: FULL SCAN

query T
EXPLAIN (VERBOSE) SELECT * from abc WHERE NOT EXISTS (SELECT * FROM def WHERE a=f)
----
distribution: local
vectorized: true
·
• lookup join (anti)
│ columns: (a, b, c)
│ estimated row count: 0
│ table: def@def_pkey
│ equality: (a) = (f)
│
└── • scan
      columns: (a, b, c)
      estimated row count: 100 (100% of the table; stats collected <hidden> ago)
      table: abc@abc_pkey
      spans: FULL SCAN

query T
EXPLAIN (VERBOSE) SELECT * from abc WHERE EXISTS (SELECT * FROM def WHERE a=f AND c=e)
----
distribution: local
vectorized: true
·
• lookup join (semi)
│ columns: (a, b, c)
│ estimated row count: 100
│ table: def@def_pkey
│ equality: (a, c) = (f,e)
│ equality cols are key
│
└── • scan
      columns: (a, b, c)
      estimated row count: 100 (100% of the table; stats collected <hidden> ago)
      table: abc@abc_pkey
      spans: FULL SCAN

query T
EXPLAIN (VERBOSE) SELECT * from abc WHERE NOT EXISTS (SELECT * FROM def WHERE a=f AND c=e)
----
distribution: local
vectorized: true
·
• lookup join (anti)
│ columns: (a, b, c)
│ estimated row count: 0
│ table: def@def_pkey
│ equality: (a, c) = (f,e)
│ equality cols are key
│
└── • scan
      columns: (a, b, c)
      estimated row count: 100 (100% of the table; stats collected <hidden> ago)
      table: abc@abc_pkey
      spans: FULL SCAN

query T
EXPLAIN (VERBOSE) SELECT * from abc WHERE EXISTS (SELECT * FROM def WHERE a=f AND d+b>1)
----
distribution: local
vectorized: true
·
• lookup join (semi)
│ columns: (a, b, c)
│ estimated row count: 33
│ table: def@def_pkey
│ equality: (a) = (f)
│ pred: (d + b) > 1
│
└── • scan
      columns: (a, b, c)
      estimated row count: 100 (100% of the table; stats collected <hidden> ago)
      table: abc@abc_pkey
      spans: FULL SCAN

query T
EXPLAIN (VERBOSE) SELECT * from abc WHERE NOT EXISTS (SELECT * FROM def WHERE a=f AND d+b>1)
----
distribution: local
vectorized: true
·
• lookup join (anti)
│ columns: (a, b, c)
│ estimated row count: 67
│ table: def@def_pkey
│ equality: (a) = (f)
│ pred: (d + b) > 1
│
└── • scan
      columns: (a, b, c)
      estimated row count: 100 (100% of the table; stats collected <hidden> ago)
      table: abc@abc_pkey
      spans: FULL SCAN

query T
 EXPLAIN (DISTSQL)
  SELECT a,b from small WHERE EXISTS (SELECT a FROM data WHERE small.a=data.a) ORDER BY a
----
distribution: full
vectorized: true
·
• lookup join (semi)
│ estimated row count: 100
│ table: data@data_pkey
│ equality: (a) = (a)
│
└── • scan
      estimated row count: 100 (100% of the table; stats collected <hidden> ago)
      table: small@small_pkey
      spans: FULL SCAN
·
Diagram: https://cockroachdb.github.io/distsqlplan/decode.html#eJzMlFFvmzAUhd_3K67uU6uZEgNJU6RJ2VqqUaVJRyKt05QHN1gdG8HMNtKqKP99AjoVqgYQecjesDlH5_BddLeofsfoond_N_3oz-Dkyl8sF1-mp7Dwpt7lEhiBB7gO5regNiyO4etnL_DAu89lcPJPVCpCptmzoBCfMfhQXJ6xU5gHV14An74BQ4KJCPmMbbhC9ztSJGghQRsJOkhwiCuCqRRrrpSQuWRbGPzwD7oDglGSZjq_XhFcC8nR3aKOdMzRxSV7iHnAWcilOUCCIdcsiouYotIkldGGySckeCnibJMot_hEJLhIWX4yTAosCYGC0D-4xNWOoMj0S6TS7JGjS3eke60bESXPrYb1VsunlLsw9a6XsPBufbiZ-7NcwTSrdJ0K8StL4aeIEhCJC5Oc2TzT-ROZWHtLWntLvnQTMuSSh_VaE_oeV7s3vmQmDJGatM52X7xdi6fdR0d7jc6khmn1GF5LscrwRscbntWdntWPnmWYdg96LcUq9M6PR8_uTs_uR882TKcHvZZiFXrj49FzutNz-tFzDHPYg15LsQq9i_9j7b5RMuAqFYninTbqIN_JPHzk5Q5XIpNrfifFuogpj_PCV1yEXOnyLS0PflK-ygtWzbTRbNXM9LXZak5uibYb3U6z2Tmk97DRPGpOHh2SfN5oHjcnjw9Jvmie1aDlN2n-yV5nr3bv_gYAAP__N5hVwQ==

query T
 EXPLAIN (DISTSQL)
  SELECT a,b from small WHERE a+b<20 AND EXISTS (SELECT a FROM data WHERE small.a=data.a AND small.b+data.c>15) ORDER BY a
----
distribution: full
vectorized: true
·
• lookup join (semi)
│ estimated row count: 11
│ table: data@data_pkey
│ equality: (a) = (a)
│ pred: (b + c) > 15
│
└── • filter
    │ estimated row count: 33
    │ filter: (a + b) < 20
    │
    └── • scan
          estimated row count: 100 (100% of the table; stats collected <hidden> ago)
          table: small@small_pkey
          spans: FULL SCAN
·
Diagram: https://cockroachdb.github.io/distsqlplan/decode.html#eJzMll1v2j4Yxe__n-LRcwXCNDgvLY30l9K1qZaKhg6Q1mnjwhCryxbiLC_SqorvPiVpaaiGzVIhcekHn5xj_46EnzD7FaGN7v3d6MLzoXPlTWfTT6MuTN2RezkDRmAB15PxLWQrFkXw-aM7caHTYdCDRRe-FYOBsQR90IUL_wrc-1IOnRdxrQxYzl6E1VdOGPxfTU9Yres8zxfQq-fL509zoFa3C-PJlTuBD1-AIcFYBNxnK56h_RUpEtSRoIEETSRo4ZxgkoolzzKRllueKoEX_EZ7QDCMkyIvx3OCS5FytJ8wD_OIo40ztoj4hLOAp9oACQY8Z2FU2VTxnCQNVyx9RIKXIipWcWZX94MEpwkrV32NAosDoCDy7zzF-ZqgKPJXyyxnDxxtuib7x7oOo5ynPNWs7Uz13IaOQ6EHjt6gYdu258-GO_31f_G_EWH8fCv0zbXMHhNuw8i9nsHUvfXgZuz55Q6Ws8ZljYT4WSTwQ4QxiNgGp4Q29qHj6GVwq8H6JTjBcZGXO4mj7zyFsfMUr-FFGvCUB9uxHdrD-fovR_VFXyQa3b7oXfbmlj3dv1u0Vbc02tf0Fu1SBNu06_RA7VL4N9tFj7dd-v549XZ49b5mtMCrCLbBe3YgvAr_Jl79ePEa--M12uE1-prZAq8i2Abv8EB4Ff5NvMbx4jX3x2u2w2v2NasFXkWwDd7zA-FV-DfxmseLV_GAmfAsEXHG9_pXH5TvAh488PodkYkiXfK7VCwrm3o5rnTVIOBZXv9K64UX1z-VAZtiKhXrcrEuFRtbYvpWbMhjn8qtTanakostqVjhfPqeQ59JxUO581AqPpeLz98Tmyo6piqZvGVUUTP6rp5RRdFMhbm8aVRRNSrv2tvs8_V_fwIAAP__RQG1bA==

# Regression test for #40562.

statement ok
CREATE TABLE public.region
(
    r_regionkey int PRIMARY KEY,
    r_name char(25) NOT NULL,
    r_comment varchar(152)
)

statement ok
ALTER TABLE public.region INJECT STATISTICS '[
  {
    "columns": ["r_regionkey"],
    "created_at": "2018-01-01 1:00:00.00000+00:00",
    "row_count": 5,
    "distinct_count": 5
  },
  {
    "columns": ["r_name"],
    "created_at": "2018-01-01 1:00:00.00000+00:00",
    "row_count": 5,
    "distinct_count": 5
  },
  {
    "columns": ["r_comment"],
    "created_at": "2018-01-01 1:00:00.00000+00:00",
    "row_count": 5,
    "distinct_count": 5
  }
]'

statement ok
CREATE TABLE public.nation
(
    n_nationkey int PRIMARY KEY,
    n_name char(25) NOT NULL,
    n_regionkey int NOT NULL,
    n_comment varchar(152),
    INDEX n_rk (n_regionkey ASC),
    CONSTRAINT nation_fkey_region FOREIGN KEY (n_regionkey) references public.region (r_regionkey)
)

statement ok
ALTER TABLE public.nation INJECT STATISTICS '[
  {
    "columns": ["n_nationkey"],
    "created_at": "2018-01-01 1:00:00.00000+00:00",
    "row_count": 25,
    "distinct_count": 25
  },
  {
    "columns": ["n_name"],
    "created_at": "2018-01-01 1:00:00.00000+00:00",
    "row_count": 25,
    "distinct_count": 25
  },
  {
    "columns": ["n_regionkey"],
    "created_at": "2018-01-01 1:00:00.00000+00:00",
    "row_count": 25,
    "distinct_count": 5
  },
  {
    "columns": ["n_comment"],
    "created_at": "2018-01-01 1:00:00.00000+00:00",
    "row_count": 25,
    "distinct_count": 25
  }
]'

statement ok
CREATE TABLE public.supplier
(
    s_suppkey int PRIMARY KEY,
    s_name char(25) NOT NULL,
    s_address varchar(40) NOT NULL,
    s_nationkey int NOT NULL,
    s_phone char(15) NOT NULL,
    s_acctbal float NOT NULL,
    s_comment varchar(101) NOT NULL,
    INDEX s_nk (s_nationkey ASC),
    CONSTRAINT supplier_fkey_nation FOREIGN KEY (s_nationkey) references public.nation (n_nationkey)
)

statement ok
ALTER TABLE public.supplier INJECT STATISTICS '[
  {
    "columns": ["s_suppkey"],
    "created_at": "2018-01-01 1:00:00.00000+00:00",
    "row_count": 10000,
    "distinct_count": 10000
  },
  {
    "columns": ["s_name"],
    "created_at": "2018-01-01 1:00:00.00000+00:00",
    "row_count": 10000,
    "distinct_count": 10000
  },
  {
    "columns": ["s_address"],
    "created_at": "2018-01-01 1:00:00.00000+00:00",
    "row_count": 10000,
    "distinct_count": 10000
  },
  {
    "columns": ["s_nationkey"],
    "created_at": "2018-01-01 1:00:00.00000+00:00",
    "row_count": 10000,
    "distinct_count": 25
  },
  {
    "columns": ["s_phone"],
    "created_at": "2018-01-01 1:00:00.00000+00:00",
    "row_count": 10000,
    "distinct_count": 10000
  },
  {
    "columns": ["s_acctbal"],
    "created_at": "2018-01-01 1:00:00.00000+00:00",
    "row_count": 10000,
    "distinct_count": 10000
  },
  {
    "columns": ["s_comment"],
    "created_at": "2018-01-01 1:00:00.00000+00:00",
    "row_count": 10000,
    "distinct_count": 10000
  }
]'

statement ok
CREATE TABLE public.part
(
    p_partkey int PRIMARY KEY,
    p_name varchar(55) NOT NULL,
    p_mfgr char(25) NOT NULL,
    p_brand char(10) NOT NULL,
    p_type varchar(25) NOT NULL,
    p_size int NOT NULL,
    p_container char(10) NOT NULL,
    p_retailprice float NOT NULL,
    p_comment varchar(23) NOT NULL
)

statement ok
ALTER TABLE public.part INJECT STATISTICS '[
  {
    "columns": ["p_partkey"],
    "created_at": "2018-01-01 1:00:00.00000+00:00",
    "row_count": 200000,
    "distinct_count": 200000
  },
  {
    "columns": ["p_name"],
    "created_at": "2018-01-01 1:00:00.00000+00:00",
    "row_count": 200000,
    "distinct_count": 200000
  },
  {
    "columns": ["p_mfgr"],
    "created_at": "2018-01-01 1:00:00.00000+00:00",
    "row_count": 200000,
    "distinct_count": 5
  },
  {
    "columns": ["p_brand"],
    "created_at": "2018-01-01 1:00:00.00000+00:00",
    "row_count": 200000,
    "distinct_count": 25
  },
  {
    "columns": ["p_type"],
    "created_at": "2018-01-01 1:00:00.00000+00:00",
    "row_count": 200000,
    "distinct_count": 150
  },
  {
    "columns": ["p_size"],
    "created_at": "2018-01-01 1:00:00.00000+00:00",
    "row_count": 200000,
    "distinct_count": 50
  },
  {
    "columns": ["p_container"],
    "created_at": "2018-01-01 1:00:00.00000+00:00",
    "row_count": 200000,
    "distinct_count": 40
  },
  {
    "columns": ["p_retailprice"],
    "created_at": "2018-01-01 1:00:00.00000+00:00",
    "row_count": 200000,
    "distinct_count": 20000
  },
  {
    "columns": ["p_comment"],
    "created_at": "2018-01-01 1:00:00.00000+00:00",
    "row_count": 200000,
    "distinct_count": 130000
  }
]'

statement ok
CREATE TABLE public.partsupp
(
    ps_partkey int NOT NULL,
    ps_suppkey int NOT NULL,
    ps_availqty int NOT NULL,
    ps_supplycost float NOT NULL,
    ps_comment varchar(199) NOT NULL,
    PRIMARY KEY (ps_partkey, ps_suppkey),
    INDEX ps_sk (ps_suppkey ASC),
    CONSTRAINT partsupp_fkey_part FOREIGN KEY (ps_partkey) references public.part (p_partkey),
    CONSTRAINT partsupp_fkey_supplier FOREIGN KEY (ps_suppkey) references public.supplier (s_suppkey)
)

statement ok
ALTER TABLE public.partsupp INJECT STATISTICS '[
  {
    "columns": ["ps_partkey"],
    "created_at": "2018-01-01 1:00:00.00000+00:00",
    "row_count": 800000,
    "distinct_count": 200000
  },
  {
    "columns": ["ps_suppkey"],
    "created_at": "2018-01-01 1:00:00.00000+00:00",
    "row_count": 800000,
    "distinct_count": 10000
  },
  {
    "columns": ["ps_availqty"],
    "created_at": "2018-01-01 1:00:00.00000+00:00",
    "row_count": 800000,
    "distinct_count": 10000
  },
  {
    "columns": ["ps_supplycost"],
    "created_at": "2018-01-01 1:00:00.00000+00:00",
    "row_count": 800000,
    "distinct_count": 100000
  },
  {
    "columns": ["ps_comment"],
    "created_at": "2018-01-01 1:00:00.00000+00:00",
    "row_count": 800000,
    "distinct_count": 800000
  }
]'

statement ok
CREATE TABLE public.customer
(
    c_custkey int PRIMARY KEY,
    c_name varchar(25) NOT NULL,
    c_address varchar(40) NOT NULL,
    c_nationkey int NOT NULL NOT NULL,
    c_phone char(15) NOT NULL,
    c_acctbal float NOT NULL,
    c_mktsegment char(10) NOT NULL,
    c_comment varchar(117) NOT NULL,
    INDEX c_nk (c_nationkey ASC),
    CONSTRAINT customer_fkey_nation FOREIGN KEY (c_nationkey) references public.nation (n_nationkey)
)

statement ok
ALTER TABLE public.customer INJECT STATISTICS '[
  {
    "columns": ["c_custkey"],
    "created_at": "2018-01-01 1:00:00.00000+00:00",
    "row_count": 150000,
    "distinct_count": 150000
  },
  {
    "columns": ["c_name"],
    "created_at": "2018-01-01 1:00:00.00000+00:00",
    "row_count": 150000,
    "distinct_count": 150000
  },
  {
    "columns": ["c_address"],
    "created_at": "2018-01-01 1:00:00.00000+00:00",
    "row_count": 150000,
    "distinct_count": 150000
  },
  {
    "columns": ["c_nationkey"],
    "created_at": "2018-01-01 1:00:00.00000+00:00",
    "row_count": 150000,
    "distinct_count": 25
  },
  {
    "columns": ["c_phone"],
    "created_at": "2018-01-01 1:00:00.00000+00:00",
    "row_count": 150000,
    "distinct_count": 150000
  },
  {
    "columns": ["c_acctbal"],
    "created_at": "2018-01-01 1:00:00.00000+00:00",
    "row_count": 150000,
    "distinct_count": 150000
  },
  {
    "columns": ["c_mktsegment"],
    "created_at": "2018-01-01 1:00:00.00000+00:00",
    "row_count": 150000,
    "distinct_count": 5
  },
  {
    "columns": ["c_comment"],
    "created_at": "2018-01-01 1:00:00.00000+00:00",
    "row_count": 150000,
    "distinct_count": 150000
  }
]'

statement ok
CREATE TABLE public.orders
(
    o_orderkey int PRIMARY KEY,
    o_custkey int NOT NULL,
    o_orderstatus char(1) NOT NULL,
    o_totalprice float NOT NULL,
    o_orderdate date NOT NULL,
    o_orderpriority char(15) NOT NULL,
    o_clerk char(15) NOT NULL,
    o_shippriority int NOT NULL,
    o_comment varchar(79) NOT NULL,
    INDEX o_ck (o_custkey ASC),
    INDEX o_od (o_orderdate ASC),
    CONSTRAINT orders_fkey_customer FOREIGN KEY (o_custkey) references public.customer (c_custkey)
)

statement ok
ALTER TABLE public.orders INJECT STATISTICS '[
  {
    "columns": ["o_orderkey"],
    "created_at": "2018-01-01 1:00:00.00000+00:00",
    "row_count": 1500000,
    "distinct_count": 1500000
  },
  {
    "columns": ["o_custkey"],
    "created_at": "2018-01-01 1:00:00.00000+00:00",
    "row_count": 1500000,
    "distinct_count": 100000
  },
  {
    "columns": ["o_orderstatus"],
    "created_at": "2018-01-01 1:00:00.00000+00:00",
    "row_count": 1500000,
    "distinct_count": 3
  },
  {
    "columns": ["o_totalprice"],
    "created_at": "2018-01-01 1:00:00.00000+00:00",
    "row_count": 1500000,
    "distinct_count": 1500000
  },
  {
    "columns": ["o_orderdate"],
    "created_at": "2018-01-01 1:00:00.00000+00:00",
    "row_count": 1500000,
    "distinct_count": 2500
  },
  {
    "columns": ["o_orderpriority"],
    "created_at": "2018-01-01 1:00:00.00000+00:00",
    "row_count": 1500000,
    "distinct_count": 5
  },
  {
    "columns": ["o_clerk"],
    "created_at": "2018-01-01 1:00:00.00000+00:00",
    "row_count": 1500000,
    "distinct_count": 1000
  },
  {
    "columns": ["o_shippriority"],
    "created_at": "2018-01-01 1:00:00.00000+00:00",
    "row_count": 1500000,
    "distinct_count": 1
  },
  {
    "columns": ["o_comment"],
    "created_at": "2018-01-01 1:00:00.00000+00:00",
    "row_count": 1500000,
    "distinct_count": 1500000
  }
]'

statement ok
CREATE TABLE public.lineitem
(
    l_orderkey int NOT NULL,
    l_partkey int NOT NULL,
    l_suppkey int NOT NULL,
    l_linenumber int NOT NULL,
    l_quantity float NOT NULL,
    l_extendedprice float NOT NULL,
    l_discount float NOT NULL,
    l_tax float NOT NULL,
    l_returnflag char(1) NOT NULL,
    l_linestatus char(1) NOT NULL,
    l_shipdate date NOT NULL,
    l_commitdate date NOT NULL,
    l_receiptdate date NOT NULL,
    l_shipinstruct char(25) NOT NULL,
    l_shipmode char(10) NOT NULL,
    l_comment varchar(44) NOT NULL,
    PRIMARY KEY (l_orderkey, l_linenumber),
    INDEX l_ok (l_orderkey ASC),
    INDEX l_pk (l_partkey ASC),
    INDEX l_sk (l_suppkey ASC),
    INDEX l_sd (l_shipdate ASC),
    INDEX l_cd (l_commitdate ASC),
    INDEX l_rd (l_receiptdate ASC),
    INDEX l_pk_sk (l_partkey ASC, l_suppkey ASC),
    INDEX l_sk_pk (l_suppkey ASC, l_partkey ASC),
    CONSTRAINT lineitem_fkey_orders FOREIGN KEY (l_orderkey) references public.orders (o_orderkey),
    CONSTRAINT lineitem_fkey_part FOREIGN KEY (l_partkey) references public.part (p_partkey),
    CONSTRAINT lineitem_fkey_supplier FOREIGN KEY (l_suppkey) references public.supplier (s_suppkey)
)

statement ok
ALTER TABLE public.lineitem INJECT STATISTICS '[
  {
    "columns": ["l_orderkey"],
    "created_at": "2018-01-01 1:00:00.00000+00:00",
    "row_count": 6001215,
    "distinct_count": 1500000
  },
  {
    "columns": ["l_partkey"],
    "created_at": "2018-01-01 1:00:00.00000+00:00",
    "row_count": 6001215,
    "distinct_count": 200000
  },
  {
    "columns": ["l_suppkey"],
    "created_at": "2018-01-01 1:00:00.00000+00:00",
    "row_count": 6001215,
    "distinct_count": 10000
  },
  {
    "columns": ["l_linenumber"],
    "created_at": "2018-01-01 1:00:00.00000+00:00",
    "row_count": 6001215,
    "distinct_count": 7
  },
  {
    "columns": ["l_quantity"],
    "created_at": "2018-01-01 1:00:00.00000+00:00",
    "row_count": 6001215,
    "distinct_count": 50
  },
  {
    "columns": ["l_extendedprice"],
    "created_at": "2018-01-01 1:00:00.00000+00:00",
    "row_count": 6001215,
    "distinct_count": 1000000
  },
  {
    "columns": ["l_discount"],
    "created_at": "2018-01-01 1:00:00.00000+00:00",
    "row_count": 6001215,
    "distinct_count": 11
  },
  {
    "columns": ["l_tax"],
    "created_at": "2018-01-01 1:00:00.00000+00:00",
    "row_count": 6001215,
    "distinct_count": 9
  },
  {
    "columns": ["l_returnflag"],
    "created_at": "2018-01-01 1:00:00.00000+00:00",
    "row_count": 6001215,
    "distinct_count": 3
  },
  {
    "columns": ["l_linestatus"],
    "created_at": "2018-01-01 1:00:00.00000+00:00",
    "row_count": 6001215,
    "distinct_count": 2
  },
  {
    "columns": ["l_shipdate"],
    "created_at": "2018-01-01 1:00:00.00000+00:00",
    "row_count": 6001215,
    "distinct_count": 2500
  },
  {
    "columns": ["l_commitdate"],
    "created_at": "2018-01-01 1:00:00.00000+00:00",
    "row_count": 6001215,
    "distinct_count": 2500
  },
  {
    "columns": ["l_receiptdate"],
    "created_at": "2018-01-01 1:00:00.00000+00:00",
    "row_count": 6001215,
    "distinct_count": 2500
  },
  {
    "columns": ["l_shipinstruct"],
    "created_at": "2018-01-01 1:00:00.00000+00:00",
    "row_count": 6001215,
    "distinct_count": 4
  },
  {
    "columns": ["l_shipmode"],
    "created_at": "2018-01-01 1:00:00.00000+00:00",
    "row_count": 6001215,
    "distinct_count": 7
  },
  {
    "columns": ["l_comment"],
    "created_at": "2018-01-01 1:00:00.00000+00:00",
    "row_count": 6001215,
    "distinct_count": 4500000
  }
]'

query T
EXPLAIN SELECT s_name, count(*) AS numwait
    FROM supplier, lineitem AS l1, orders, nation
   WHERE s_suppkey = l1.l_suppkey
     AND o_orderkey = l1.l_orderkey
     AND o_orderstatus = 'F'
     AND l1.l_receiptdate > l1.l_commitdate
     AND EXISTS(
            SELECT *
              FROM lineitem AS l2
             WHERE l2.l_orderkey = l1.l_orderkey
               AND l2.l_suppkey != l1.l_suppkey
         )
     AND NOT EXISTS(
                SELECT *
                  FROM lineitem AS l3
                 WHERE l3.l_orderkey = l1.l_orderkey
                   AND l3.l_receiptdate > l3.l_commitdate
             )
     AND s_nationkey = n_nationkey
     AND n_name = 'SAUDI ARABIA'
GROUP BY s_name
ORDER BY numwait DESC, s_name
   LIMIT 100;
----
distribution: full
vectorized: true
·
• top-k
│ estimated row count: 0
│ order: -count_rows,+s_name
│ k: 100
│
└── • group (hash)
    │ estimated row count: 0
    │ group by: s_name
    │
    └── • lookup join (semi)
        │ estimated row count: 0
        │ table: lineitem@lineitem_pkey
        │ equality: (l_orderkey) = (l_orderkey)
        │ pred: l_suppkey != l_suppkey
        │
        └── • lookup join
            │ estimated row count: 0
            │ table: orders@orders_pkey
            │ equality: (l_orderkey) = (o_orderkey)
            │ equality cols are key
            │ pred: o_orderstatus = 'F'
            │
            └── • lookup join (anti)
                │ estimated row count: 0
                │ table: lineitem@lineitem_pkey
                │ equality: (l_orderkey) = (l_orderkey)
                │ pred: l_receiptdate > l_commitdate
                │
                └── • lookup join
                    │ table: lineitem@lineitem_pkey
                    │ equality: (l_orderkey, l_linenumber) = (l_orderkey,l_linenumber)
                    │ equality cols are key
                    │ pred: l_receiptdate > l_commitdate
                    │
                    └── • lookup join
                        │ estimated row count: 240,049
                        │ table: lineitem@l_sk
                        │ equality: (s_suppkey) = (l_suppkey)
                        │
                        └── • lookup join
                            │ estimated row count: 400
                            │ table: supplier@supplier_pkey
                            │ equality: (s_suppkey) = (s_suppkey)
                            │ equality cols are key
                            │
                            └── • lookup join
                                │ estimated row count: 400
                                │ table: supplier@s_nk
                                │ equality: (n_nationkey) = (s_nationkey)
                                │
                                └── • filter
                                    │ estimated row count: 1
                                    │ filter: n_name = 'SAUDI ARABIA'
                                    │
                                    └── • scan
                                          estimated row count: 25 (100% of the table; stats collected <hidden> ago)
                                          table: nation@nation_pkey
                                          spans: FULL SCAN

# Regression test for #50964.
statement ok
CREATE TABLE tab4 (
  pk INT8 PRIMARY KEY, col0 INT8, col1 FLOAT8, col2 STRING, col3 INT8, col4 FLOAT8, col5 STRING,
  UNIQUE (col3 DESC, col4 DESC)
)

query T
EXPLAIN (VERBOSE)
  SELECT pk FROM tab4 WHERE col0 IN (SELECT col3 FROM tab4 WHERE col4 = 495.6) AND (col3 IS NULL)
----
distribution: full
vectorized: true
·
• project
│ columns: (pk)
│ estimated row count: 10 (missing stats)
│
└── • project
    │ columns: (pk, col0, col3)
    │ estimated row count: 10 (missing stats)
    │
    └── • lookup join (semi)
        │ columns: ("lookup_join_const_col_@15", pk, col0, col3)
        │ table: tab4@tab4_col3_col4_key
        │ equality: (col0, lookup_join_const_col_@15) = (col3,col4)
        │ equality cols are key
        │
        └── • render
            │ columns: ("lookup_join_const_col_@15", pk, col0, col3)
            │ estimated row count: 10 (missing stats)
            │ render lookup_join_const_col_@15: 495.6
            │ render pk: pk
            │ render col0: col0
            │ render col3: col3
            │
            └── • index join
                │ columns: (pk, col0, col3)
                │ estimated row count: 10 (missing stats)
                │ table: tab4@tab4_pkey
                │ key columns: pk
                │
                └── • scan
                      columns: (pk, col3)
                      estimated row count: 10 (missing stats)
                      table: tab4@tab4_col3_col4_key
                      spans: /NULL-

###########################################################
#  LOOKUP JOINS WITH LOOKUP EXPRESSIONS                   #
#  https://github.com/cockroachdb/cockroach/issues/59615  #
###########################################################

# Regression test for #59615. Ensure that invalid lookup joins are not created
# for left and anti joins.
statement ok
CREATE TABLE t59615 (
  x INT NOT NULL CHECK (x in (1, 3)),
  y INT NOT NULL,
  z INT,
  PRIMARY KEY (x, y)
)

query T
EXPLAIN (DISTSQL) SELECT * FROM (VALUES (1), (2)) AS u(y) LEFT JOIN t59615 t ON u.y = t.y
----
distribution: local
vectorized: true
·
• lookup join (left outer)
│ table: t59615@t59615_pkey
│ lookup condition: (column1 = y) AND (x IN (1, 3))
│
└── • values
      size: 1 column, 2 rows
·
Diagram: https://cockroachdb.github.io/distsqlplan/decode.html#eJyUkU9r3DAQxe_9FMOcpCKSlTcprSCgbeOAg2unaycUig_GHlI3G8nVH-iy7HcvtgttCinNTTNPb55-owP67ztUmH6-yTdZAewyq-rqU86hSvP0Qw2v4WpbfgR2t8lv0wqY5AJYwjlsKojA9hzy9KqG6zIrIJy_eyPPJyVAWUA82cMFhJM9CjS2p6J9JI_qC0psBI7OduS9dVPrMF_I-h-oVgIHM8YwtRuBnXWE6oBhCDtChXftLpI_XaHAnkI77OaJZ_AeWALd12gePMfmKNDG8HuKD-09oVodxf8nXdvBbKntyZ3Kp2n1fiS1cJe3dbqd6VHgwq9HNzy2bqLOrX2II3yzgwFrFDAt4QL0msOmuASmE5hWLpVSWVG_FbD-deIcBZYxKNBS6ETotdBnz1LJl1BtyY_WeHpC9Py-GoHU39PyR95G19GNs90cs5Tl7JsbPfmwqHIpMrNI0wP_NMt_mpO_zM3x1c8AAAD__1P0zvM=

query T
EXPLAIN (DISTSQL) SELECT * FROM (VALUES (1), (2)) AS u(y) WHERE NOT EXISTS (
  SELECT * FROM t59615 t WHERE u.y = t.y
)
----
distribution: local
vectorized: true
·
• lookup join (anti)
│ table: t59615@t59615_pkey
│ lookup condition: (column1 = y) AND (x IN (1, 3))
│
└── • values
      size: 1 column, 2 rows
·
Diagram: https://cockroachdb.github.io/distsqlplan/decode.html#eJyUkd9r1EAQx9_9K4Z52pWlvb1a0YVCok0xJSb1EmtB8hCSocZed-P-AMNx_7skKegJJ_Zt58dnZr_f2aH7sUWFyd1NFqc5sMu0rMpPGYcyyZL3FbyEq03xEdhtnH1OSmCSC2BrziEuIQAbOXz5kGwSyIsKkrsJBnaI-vO3r-X51O-fesPJCBfgT0aOArXpKG8eyaH6ihJrgYM1LTln7JTazQ1p9xPVSmCvh-CndC2wNZZQ7dD3fkuo8LbZBnKnKxTYkW_67TzxFbwDtob2W9APjmO9F2iC_z3F-eaeUK324v83XZteb6jpyJ7Kw23VOJCCLLmqIM6rFK6LNEeBiwXRYPvHxo4oMDPmIQzw3fQajFbAIgkXEJ1xiPNLYNEapltIpVSaV28EnD29-ORYEbyCSB7VIp-jZUNuMNrRgY7jLtUCqbun5TLOBNvSjTXtvGYJi5mbEx05v1TlEqR6KU0f_BOW_4TXf8H1_sWvAAAA__87BtNx

statement ok
CREATE TABLE lookup_expr (
  r STRING NOT NULL CHECK (r IN ('east', 'west')),
  v INT NOT NULL,
  w INT,
  x INT,
  y INT NOT NULL CHECK (y IN (10, 20)),
  z INT NOT NULL CHECK (z = 5),
  PRIMARY KEY (r, v),
  INDEX (r, x, y, z, w)
)

query T
EXPLAIN (DISTSQL) SELECT * FROM (VALUES (1, 10), (2, 20), (3, NULL)) AS u(w, x) LEFT JOIN lookup_expr t
ON u.w = t.w AND u.x = t.x
----
distribution: local
vectorized: true
·
• lookup join (left outer)
│ table: lookup_expr@lookup_expr_r_x_y_z_w_idx
│ lookup condition: ((((column2 = x) AND (column1 = w)) AND (r IN ('east', 'west'))) AND (y IN (10, 20))) AND (z = 5)
│
└── • values
      size: 2 columns, 3 rows
·
Diagram: https://cockroachdb.github.io/distsqlplan/decode.html#eJyUUmFr2zAQ_b5fcdyXyONoo2RpgyCgbHWHi2d3sVMGIxgTi85rZnmWjN2F_Pdhu13TQcf67d679-7EO-3R_NyhQPfLtb_0AmAXXhRHn30HItd3P8TwFi5X4SdgN0t_7UbAOAEfOwRsQjDpiylBsPZ9x4FlBDWwhqB1wHcvY7gKvQB2Wt_VZaLasuoUFsIAWH3SwALsSePAMrjocNvj1kHCQmcqSH8og-IrctwQlpXeKmN01VH7XuBlLYoxYV6Ute3oDeFWVwrFHm1udwoF3qS7WpnTMRJmyqb5bpjI4T2wKWy_1cWdcXBzINS1fRpjbHqrUIwP9P-rrnRerFSaqeqUP18X35dKDHmE69hd9akg4VEu8qhOqqRN7pNfSZPkWYuEft-D7zovQBcCGGNMTmAB8uwhPMk7NHMe4RS6S45UauxICBHFKy_4SDBq1DHh_NGf93o-FkJ4QTzvDvtQPmnmsIDZI4uEYW0FSE5yQnJK8h3JGckzkuck5y8myl-T6EqZUhdGPUvz5VttCFV2q4YPYnRdbdV1pbf9mgGGva8nMmXs0OUD8Iqh1T3w2Mz_aZ78Zd4c3vwOAAD___N7_WA=

query T
EXPLAIN (DISTSQL) SELECT * FROM (VALUES (1, 10), (2, 20), (3, NULL)) AS u(w, x) WHERE NOT EXISTS (
  SELECT * FROM lookup_expr t WHERE u.w = t.w AND u.x = t.x
)
----
distribution: local
vectorized: true
·
• lookup join (anti)
│ table: lookup_expr@lookup_expr_r_x_y_z_w_idx
│ lookup condition: ((((column2 = x) AND (column1 = w)) AND (r IN ('east', 'west'))) AND (y IN (10, 20))) AND (z = 5)
│
└── • values
      size: 2 columns, 3 rows
·
Diagram: https://cockroachdb.github.io/distsqlplan/decode.html#eJyUUlFr20wQfP9-xbIvvvs4Ep9N2nBgkNsorYIqp5aSBooRQlpSNapO1Z2QUuP_XiQ5jV1Iad92Zndmj9nbovleoEL37tpfegGwCy-Mwo8-h9D13bcR_A-X69UHYLdL_8YNgUkBcsoFsJmA2VDMBQQ3vs85LENogLUCOg6f3rtrF4JVBO5dbwns2LDQ-qGpYuqqutfZvYA1Jy0swJ60HJbBRY-7AXeco8BSZxQk38ig-owSNwKrWqdkjK57ajsMeFmHaiowL6vG9vRGYKprQrVFm9uCUOFtUjRkTqcoMCOb5MXoKOENsDmkX5rywXDc7ATqxj7bGJvcE6rpTvz9qiudl2tKMqpP5fG66LEiBb57GcEyiDy4WnkBCjyIxjmo4zru4sf4R9zGedahQH_owVedl6BLBYwx5sxgAc6rfXiO7NEZf4Jz6E88ocTYiVIqjNZe8E7ApKVDgv-afz3My6lSygui8_7i-_J55hwWcPbEosBVYxU4UjizF-OT_xLfmkylS0NH0b18mI1Ayu5p_A1GN3VK17VOhzUjXA26gcjI2LErR-CVY6t_4KFY_lE8-0282f33MwAA__9ovQAB