select_index

# LogicTest: local-opt

statement ok
CREATE TABLE t (
  a INT,
  b CHAR,
  c INT,
  d CHAR,
  PRIMARY KEY (a, b),
  INDEX bc (b, c),
  INDEX dc (d, c),
  INDEX a_desc (a DESC),
  FAMILY (a, b),
  FAMILY (c),
  FAMILY (d)
)

statement ok
INSERT INTO t VALUES
  (1, 'one', 11, 'foo'),
  (2, 'two', 22, 'bar'),
  (3, 'three', 33, 'blah')

query T
SELECT message FROM [SHOW KV TRACE FOR SELECT * FROM t WHERE a = 2]
 WHERE message LIKE 'fetched:%' OR message LIKE 'output row%'
----
fetched: /t/primary/2/'two' -> NULL
fetched: /t/primary/2/'two'/c -> 22
fetched: /t/primary/2/'two'/d -> 'bar'
output row: [2 'two' 22 'bar']

query T
SELECT message FROM [SHOW KV TRACE FOR SELECT * FROM t WHERE a IN (1, 3)]
 WHERE message LIKE 'fetched:%' OR message LIKE 'output row%'
----
fetched: /t/primary/1/'one' -> NULL
fetched: /t/primary/1/'one'/c -> 11
fetched: /t/primary/1/'one'/d -> 'foo'
output row: [1 'one' 11 'foo']
fetched: /t/primary/3/'three' -> NULL
fetched: /t/primary/3/'three'/c -> 33
fetched: /t/primary/3/'three'/d -> 'blah'
output row: [3 'three' 33 'blah']

query T
SELECT message FROM [SHOW KV TRACE FOR SELECT * FROM t WHERE d = 'foo' OR d = 'bar']
 WHERE message LIKE 'fetched:%' OR message LIKE 'output row%'
----
fetched: /t/dc/'bar'/22/2/'two' -> NULL
output row: [2 'two' 22 'bar']
fetched: /t/dc/'foo'/11/1/'one' -> NULL
output row: [1 'one' 11 'foo']

query T
SELECT message FROM [SHOW KV TRACE FOR SELECT * FROM t WHERE (d, c) IN (('foo', 11), ('bar', 22))]
 WHERE message LIKE 'fetched:%' OR message LIKE 'output row%'
----
fetched: /t/dc/'bar'/22/2/'two' -> NULL
output row: [2 'two' 22 'bar']
fetched: /t/dc/'foo'/11/1/'one' -> NULL
output row: [1 'one' 11 'foo']

query T
SELECT message FROM [SHOW KV TRACE FOR SELECT * FROM t WHERE (d, c) = ('foo', 11)]
 WHERE message LIKE 'fetched:%' OR message LIKE 'output row%'
----
fetched: /t/dc/'foo'/11/1/'one' -> NULL
output row: [1 'one' 11 'foo']

query T
SELECT message FROM [SHOW KV TRACE FOR SELECT * FROM t WHERE a < 2]
 WHERE message LIKE 'fetched:%' OR message LIKE 'output row%'
----
fetched: /t/primary/1/'one' -> NULL
fetched: /t/primary/1/'one'/c -> 11
fetched: /t/primary/1/'one'/d -> 'foo'
output row: [1 'one' 11 'foo']

query T
SELECT message FROM [SHOW KV TRACE FOR SELECT * FROM t WHERE a <= (1 + 1)]
 WHERE message LIKE 'fetched:%' OR message LIKE 'output row%'
----
fetched: /t/primary/1/'one' -> NULL
fetched: /t/primary/1/'one'/c -> 11
fetched: /t/primary/1/'one'/d -> 'foo'
output row: [1 'one' 11 'foo']
fetched: /t/primary/2/'two' -> NULL
fetched: /t/primary/2/'two'/c -> 22
fetched: /t/primary/2/'two'/d -> 'bar'
output row: [2 'two' 22 'bar']

query T
SELECT message FROM [SHOW KV TRACE FOR SELECT a, b FROM t WHERE b > 't']
 WHERE message LIKE 'fetched:%' OR message LIKE 'output row%'
----
fetched: /t/bc/'three'/33/3 -> NULL
output row: [3 'three']
fetched: /t/bc/'two'/22/2 -> NULL
output row: [2 'two']

query T
SELECT message FROM [SHOW KV TRACE FOR SELECT * FROM t WHERE d < ('b' || 'l')]
 WHERE message LIKE 'fetched:%' OR message LIKE 'output row%'
----
fetched: /t/dc/'bar'/22/2/'two' -> NULL
output row: [2 'two' 22 'bar']

# TODO(radu): Need to select "dc" index
# The where-clause does not contain columns matching a prefix of any
# index. Note that the index "dc" was chosen because it contains fewer
# keys per row than the primary key index while still containing all
# of the needed columns.
query T
SELECT message FROM [SHOW KV TRACE FOR SELECT * FROM t WHERE c = 22]
 WHERE message LIKE 'fetched:%' OR message LIKE 'output row%'
----
fetched: /t/primary/1/'one' -> NULL
fetched: /t/primary/1/'one'/c -> 11
fetched: /t/primary/1/'one'/d -> 'foo'
fetched: /t/primary/2/'two' -> NULL
fetched: /t/primary/2/'two'/c -> 22
fetched: /t/primary/2/'two'/d -> 'bar'
output row: [2 'two' 22 'bar']
fetched: /t/primary/3/'three' -> NULL
fetched: /t/primary/3/'three'/c -> 33
fetched: /t/primary/3/'three'/d -> 'blah'

# Use the descending index
query T
SELECT message FROM [SHOW KV TRACE FOR SELECT a FROM t ORDER BY a DESC]
 WHERE message LIKE 'fetched:%' OR message LIKE 'output row%'
----
fetched: /t/a_desc/3/'three' -> NULL
output row: [3]
fetched: /t/a_desc/2/'two' -> NULL
output row: [2]
fetched: /t/a_desc/1/'one' -> NULL
output row: [1]

# Use the descending index with multiple spans.
query T
SELECT message FROM [SHOW KV TRACE FOR SELECT a FROM t WHERE a in (2, 3) ORDER BY a DESC]
 WHERE message LIKE 'fetched:%' OR message LIKE 'output row%'
----
fetched: /t/a_desc/3/'three' -> NULL
output row: [3]
fetched: /t/a_desc/2/'two' -> NULL
output row: [2]

# Index selection occurs in direct join operands too.
# TODO(radu): except that it doesn't in this case with new optimizer
query TTTTT
EXPLAIN (VERBOSE) SELECT * FROM t x JOIN t y USING(b) WHERE x.b < '3'
----
render          ·         ·          (b, a, c, d, a, c, d)     ·
 │              render 0  b          ·                         ·
 │              render 1  a          ·                         ·
 │              render 2  c          ·                         ·
 │              render 3  d          ·                         ·
 │              render 4  a          ·                         ·
 │              render 5  c          ·                         ·
 │              render 6  d          ·                         ·
 └── join       ·         ·          (a, b, c, d, a, b, c, d)  ·
      │         type      inner      ·                         ·
      │         equality  (b) = (b)  ·                         ·
      ├── scan  ·         ·          (a, b, c, d)              ·
      │         table     t@primary  ·                         ·
      │         spans     ALL        ·                         ·
      │         filter    b < '3'    ·                         ·
      └── scan  ·         ·          (a, b, c, d)              ·
·               table     t@primary  ·                         ·
·               spans     ALL        ·                         ·

statement ok
TRUNCATE TABLE t

statement ok
INSERT INTO t VALUES
  (1, 'a', NULL, NULL),
  (1, 'b', NULL, NULL),
  (1, 'c', NULL, NULL)

query T
SELECT message FROM [SHOW KV TRACE FOR SELECT * FROM t WHERE a = 1 AND b > 'b']
 WHERE message LIKE 'fetched:%' OR message LIKE 'output row%'
----
fetched: /t/primary/1/'c' -> NULL
output row: [1 'c' NULL NULL]

query T
SELECT message FROM [SHOW KV TRACE FOR SELECT * FROM t WHERE a > 0 AND b > 'b']
 WHERE message LIKE 'fetched:%' OR message LIKE 'output row%'
----
fetched: /t/primary/1/'c' -> NULL
output row: [1 'c' NULL NULL]

query T
SELECT message FROM [SHOW KV TRACE FOR SELECT * FROM t WHERE a > 1 AND b > 'b']
 WHERE message LIKE 'fetched:%' OR message LIKE 'output row%'
----

query TTT
EXPLAIN SELECT * FROM t WHERE a > 1 AND a < 2
----
norows  ·  ·

query T
SELECT message FROM [SHOW KV TRACE FOR SELECT * FROM t WHERE a = 1 AND 'a' < b AND 'c' > b]
 WHERE message LIKE 'fetched:%' OR message LIKE 'output row%'
----
fetched: /t/primary/1/'b' -> NULL
output row: [1 'b' NULL NULL]

statement ok
DROP TABLE t

statement ok
CREATE TABLE t (
  a INT PRIMARY KEY,
  b INT,
  INDEX ab (a, b)
)

statement ok
INSERT INTO t VALUES (1, 2), (3, 4), (5, 6)

query T
SELECT message FROM [SHOW KV TRACE FOR SELECT * FROM t@ab WHERE a >= 3 AND a < 5]
 WHERE message LIKE 'fetched:%' OR message LIKE 'output row%'
----
fetched: /t/ab/3/4 -> NULL
output row: [3 4]

query T
SELECT message FROM [SHOW KV TRACE FOR SELECT * FROM t@ab WHERE a BETWEEN 3 AND 4]
 WHERE message LIKE 'fetched:%' OR message LIKE 'output row%'
----
fetched: /t/ab/3/4 -> NULL
output row: [3 4]

query T
SELECT message FROM [SHOW KV TRACE FOR SELECT * FROM t@ab WHERE a BETWEEN 3 AND 5]
 WHERE message LIKE 'fetched:%' OR message LIKE 'output row%'
----
fetched: /t/ab/3/4 -> NULL
output row: [3 4]
fetched: /t/ab/5/6 -> NULL
output row: [5 6]

query T
SELECT message FROM [SHOW KV TRACE FOR SELECT * FROM t@ab WHERE a < 2 OR a < 4]
 WHERE message LIKE 'fetched:%' OR message LIKE 'output row%'
----
fetched: /t/ab/1/2 -> NULL
output row: [1 2]
fetched: /t/ab/3/4 -> NULL
output row: [3 4]

query T
SELECT message FROM [SHOW KV TRACE FOR SELECT * FROM t@ab WHERE a < 3 OR a <= 3]
 WHERE message LIKE 'fetched:%' OR message LIKE 'output row%'
----
fetched: /t/ab/1/2 -> NULL
output row: [1 2]
fetched: /t/ab/3/4 -> NULL
output row: [3 4]

query T
SELECT message FROM [SHOW KV TRACE FOR SELECT * FROM t@ab WHERE a <= 3 OR a < 3]
 WHERE message LIKE 'fetched:%' OR message LIKE 'output row%'
----
fetched: /t/ab/1/2 -> NULL
output row: [1 2]
fetched: /t/ab/3/4 -> NULL
output row: [3 4]

query T
SELECT message FROM [SHOW KV TRACE FOR SELECT * FROM t@ab WHERE a > 3 OR a >= 3]
 WHERE message LIKE 'fetched:%' OR message LIKE 'output row%'
----
fetched: /t/ab/3/4 -> NULL
output row: [3 4]
fetched: /t/ab/5/6 -> NULL
output row: [5 6]

query T
SELECT message FROM [SHOW KV TRACE FOR SELECT * FROM t@ab WHERE a >= 3 OR a > 3]
 WHERE message LIKE 'fetched:%' OR message LIKE 'output row%'
----
fetched: /t/ab/3/4 -> NULL
output row: [3 4]
fetched: /t/ab/5/6 -> NULL
output row: [5 6]

query T
SELECT message FROM [SHOW KV TRACE FOR SELECT * FROM t@ab WHERE a = 3 OR a = 5]
 WHERE message LIKE 'fetched:%' OR message LIKE 'output row%'
----
fetched: /t/ab/3/4 -> NULL
output row: [3 4]
fetched: /t/ab/5/6 -> NULL
output row: [5 6]

query T
SELECT message FROM [SHOW KV TRACE FOR SELECT * FROM t@ab WHERE a < 3 OR a > 3]
 WHERE message LIKE 'fetched:%' OR message LIKE 'output row%'
----
fetched: /t/ab/1/2 -> NULL
output row: [1 2]
fetched: /t/ab/5/6 -> NULL
output row: [5 6]

query T
SELECT message FROM [SHOW KV TRACE FOR SELECT * FROM t@ab WHERE a + 1 = 4]
 WHERE message LIKE 'fetched:%' OR message LIKE 'output row%'
----
fetched: /t/ab/3/4 -> NULL
output row: [3 4]

query TTT
EXPLAIN SELECT * FROM t WHERE a = 1 AND false
----
norows  ·  ·

query TTT
EXPLAIN SELECT * FROM t WHERE a = 1 AND NULL
----
norows  ·  ·

query TTT
EXPLAIN SELECT * FROM t WHERE a = NULL AND a != NULL
----
norows  ·  ·

# Make sure that mixed type comparison operations are not used
# for selecting indexes.

statement ok
DROP TABLE t

statement ok
CREATE TABLE t (
  a INT PRIMARY KEY,
  b INT,
  c INT,
  INDEX b_desc (b DESC),
  INDEX bc (b, c)
)

query TTTTT
EXPLAIN (VERBOSE) SELECT a FROM t WHERE c > 1
----
render     ·         ·          (a)     ·
 │         render 0  a          ·       ·
 └── scan  ·         ·          (a, c)  ·
·          table     t@primary  ·       ·
·          spans     ALL        ·       ·
·          filter    c > 1      ·       ·

query TTTTT
EXPLAIN (VERBOSE) SELECT a FROM t WHERE c < 1 AND b < 5
----
render     ·         ·            (a)        ·
 │         render 0  a            ·          ·
 └── scan  ·         ·            (a, b, c)  ·
·          table     t@bc         ·          ·
·          spans     /!NULL-/4/1  ·          ·
·          filter    c < 1        ·          ·

query TTTTT
EXPLAIN (VERBOSE) SELECT a FROM t WHERE c > 1.0
----
render     ·         ·          (a)     ·
 │         render 0  a          ·       ·
 └── scan  ·         ·          (a, c)  ·
·          table     t@primary  ·       ·
·          spans     ALL        ·       ·
·          filter    c > 1      ·       ·

query TTTTT
EXPLAIN (VERBOSE) SELECT a FROM t WHERE c < 1.0
----
render     ·         ·          (a)     ·
 │         render 0  a          ·       ·
 └── scan  ·         ·          (a, c)  ·
·          table     t@primary  ·       ·
·          spans     ALL        ·       ·
·          filter    c < 1      ·       ·

query TTTTT
EXPLAIN (VERBOSE) SELECT a FROM t WHERE c > 1.0 AND b < 5
----
render     ·         ·          (a)        ·
 │         render 0  a          ·          ·
 └── scan  ·         ·          (a, b, c)  ·
·          table     t@bc       ·          ·
·          spans     /!NULL-/5  ·          ·
·          filter    c > 1      ·          ·

query TTTTT
EXPLAIN (VERBOSE) SELECT a FROM t WHERE b < 5.0 AND c < 1
----
render     ·         ·            (a)        ·
 │         render 0  a            ·          ·
 └── scan  ·         ·            (a, b, c)  ·
·          table     t@bc         ·          ·
·          spans     /!NULL-/4/1  ·          ·
·          filter    c < 1        ·          ·

query TTTTT
EXPLAIN (VERBOSE) SELECT a FROM t WHERE (b, c) = (5, 1)
----
render     ·         ·          (a)        ·
 │         render 0  a          ·          ·
 └── scan  ·         ·          (a, b, c)  ·
·          table     t@bc       ·          ·
·          spans     /5/1-/5/2  ·          ·

query TTTTT
EXPLAIN (VERBOSE) SELECT a FROM t WHERE (b, c) = (5.0, 1)
----
render     ·         ·          (a)        ·
 │         render 0  a          ·          ·
 └── scan  ·         ·          (a, b, c)  ·
·          table     t@bc       ·          ·
·          spans     /5/1-/5/2  ·          ·

query TTTTT
EXPLAIN (VERBOSE) SELECT a FROM t WHERE (b, c) = (5.1, 1)
----
render     ·         ·                      (a)        ·
 │         render 0  a                      ·          ·
 └── scan  ·         ·                      (a, b, c)  ·
·          table     t@bc                   ·          ·
·          spans     /!NULL-                ·          ·
·          filter    (b = 5.1) AND (c = 1)  ·          ·

# Note the span is reversed because of #20203.
query TTTTT
EXPLAIN (VERBOSE) SELECT a FROM t WHERE b IN (5.0, 1)
----
render     ·         ·            (a)     ·
 │         render 0  a            ·       ·
 └── scan  ·         ·            (a, b)  ·
·          table     t@b_desc     ·       ·
·          spans     /5-/4 /1-/0  ·       ·

statement ok
CREATE TABLE abcd (
  a INT,
  b INT,
  c INT,
  d INT,
  INDEX adb (a, d, b),
  INDEX abcd (a, b, c, d)
)

# Verify that we prefer the index where more columns are constrained, even if it
# has more keys per row.
query TTTTT
EXPLAIN (VERBOSE) SELECT b FROM abcd WHERE (a, b) = (1, 4)
----
render     ·         ·          (b)     ·
 │         render 0  b          ·       ·
 └── scan  ·         ·          (a, b)  ·
·          table     abcd@abcd  ·       ·
·          spans     /1/4-/1/5  ·       ·

# TODO(radu): in this case, we're not preferring abcd, unlike 2.0
query TTTTT
EXPLAIN (VERBOSE) SELECT b FROM abcd WHERE (a, b) IN ((1, 4), (2, 9))
----
render     ·         ·                     (b)     ·
 │         render 0  b                     ·       ·
 └── scan  ·         ·                     (a, b)  ·
·          table     abcd@abcd             ·       ·
·          spans     /1/4-/1/5 /2/9-/2/10  ·       ·

statement ok
CREATE TABLE ab (
  s STRING,
  i INT
);

query TTTTT
EXPLAIN (VERBOSE) SELECT i, s FROM ab WHERE (i, s) < (1, 'c')
----
render     ·         ·                  (i, s)  ·
 │         render 0  i                  ·       ·
 │         render 1  s                  ·       ·
 └── scan  ·         ·                  (s, i)  ·
·          table     ab@primary         ·       ·
·          spans     ALL                ·       ·
·          filter    (i, s) < (1, 'c')  ·       ·

statement ok
CREATE INDEX baz ON ab (i, s)

query TTTTT
EXPLAIN (VERBOSE) SELECT i, s FROM ab@baz WHERE (i, s) < (1, 'c')
----
render     ·         ·                  (i, s)                         ·
 │         render 0  test.public.ab.i   ·                              ·
 │         render 1  test.public.ab.s   ·                              ·
 └── scan  ·         ·                  (s, i, rowid[hidden,omitted])  rowid!=NULL; weak-key(s,i,rowid)
·          table     ab@baz             ·                              ·
·          spans     /!NULL-/1/"c"      ·                              ·
·          filter    (i, s) < (1, 'c')  ·                              ·

# Check that primary key definitions can indicate index ordering,
# and this information is subsequently used during index selection
# and span generation. #13882
query TTBITTBB
CREATE TABLE abz(a INT, b INT, c INT, PRIMARY KEY (a DESC, b ASC), UNIQUE(c DESC, b ASC)); SHOW INDEX FROM abz
----
abz    primary      true      1  a       DESC       false    false
abz    primary      true      2  b       ASC        false    false
abz    abz_c_b_key  true      1  c       DESC       false    false
abz    abz_c_b_key  true      2  b       ASC        false    false
abz    abz_c_b_key  true      3  a       ASC        false    true

query TTTTT
EXPLAIN (VERBOSE) SELECT a FROM abz ORDER BY a DESC LIMIT 1
----
scan  ·      ·            (a)  -a
·     table  abz@primary  ·    ·
·     spans  ALL          ·    ·
·     limit  1            ·    ·

query TTTTT
EXPLAIN (VERBOSE) SELECT c FROM abz ORDER BY c DESC LIMIT 1
----
scan  ·      ·                (c)  -c
·     table  abz@abz_c_b_key  ·    ·
·     spans  ALL              ·    ·
·     limit  1                ·    ·

# Issue #14426: verify we don't have an internal filter that contains "a IN ()"
# (which causes an error in DistSQL due to expression serialization).
statement ok
CREATE TABLE tab0(
  k INT PRIMARY KEY,
  a INT,
  b INT
)

query TTTTT
EXPLAIN (VERBOSE) SELECT k FROM tab0 WHERE (a IN (6) AND a > 6) OR b >= 4
----
render     ·         ·                                     (k)        ·
 │         render 0  k                                     ·          ·
 └── scan  ·         ·                                     (k, a, b)  ·
·          table     tab0@primary                          ·          ·
·          spans     ALL                                   ·          ·
·          filter    ((a IN (6)) AND (a > 6)) OR (b >= 4)  ·          ·

# Check that no extraneous rows are fetched due to excessive batching (#15910)
# The test is composed of three parts: populate a table, check
# that the problematic plan is properly derived from the test query,
# then test the results.

statement ok
CREATE TABLE test2 (id BIGSERIAL PRIMARY KEY, k TEXT UNIQUE, v INT DEFAULT 42);
INSERT INTO test2(k)
     VALUES ('001'),('002'),('003'),('004'),('005'),('006'),('007'),('008'),('009'),('010'),
            ('011'),('012'),('013'),('014'),('015'),('016'),('017'),('018'),('019'),('020'),
            ('021'),('022'),('023'),('024'),('025'),('026'),('027'),('028'),('029'),('030')

# Plan check:
# The query is using an index-join and the limit is propagated to the scan.

# TODO(radu): need revscan, index-join, and limit pushdown
query TTTTT
EXPLAIN (VERBOSE) SELECT * FROM test2 WHERE k <= '100' ORDER BY k DESC LIMIT 20
----
limit           ·       ·              (id, k, v)  -k
 │              count   20             ·           ·
 └── sort       ·       ·              (id, k, v)  -k
      │         order   -k             ·           ·
      └── scan  ·       ·              (id, k, v)  ·
·               table   test2@primary  ·           ·
·               spans   ALL            ·           ·
·               filter  k <= '100'     ·           ·

# Result check: The following query must not issue more than the
# requested LIMIT K/V reads, even though an index join batches 100
# rows at a time -- the limit should be enforced by the scan.  We are
# reading from the end (ORDER BY k DESC) so we should see 20 values
# from 030 to 011 (thus not 001-010).

query T
SELECT regexp_replace(message, '\d\d\d\d\d+', '...PK...')
  FROM [SHOW KV TRACE FOR SELECT * FROM test2 WHERE k <= '100' ORDER BY k DESC LIMIT 20]
 WHERE message LIKE 'fetched:%'
----
fetched: /test2/primary/...PK.../k/v -> /'001'/42
fetched: /test2/primary/...PK.../k/v -> /'002'/42
fetched: /test2/primary/...PK.../k/v -> /'003'/42
fetched: /test2/primary/...PK.../k/v -> /'004'/42
fetched: /test2/primary/...PK.../k/v -> /'005'/42
fetched: /test2/primary/...PK.../k/v -> /'006'/42
fetched: /test2/primary/...PK.../k/v -> /'007'/42
fetched: /test2/primary/...PK.../k/v -> /'008'/42
fetched: /test2/primary/...PK.../k/v -> /'009'/42
fetched: /test2/primary/...PK.../k/v -> /'010'/42
fetched: /test2/primary/...PK.../k/v -> /'011'/42
fetched: /test2/primary/...PK.../k/v -> /'012'/42
fetched: /test2/primary/...PK.../k/v -> /'013'/42
fetched: /test2/primary/...PK.../k/v -> /'014'/42
fetched: /test2/primary/...PK.../k/v -> /'015'/42
fetched: /test2/primary/...PK.../k/v -> /'016'/42
fetched: /test2/primary/...PK.../k/v -> /'017'/42
fetched: /test2/primary/...PK.../k/v -> /'018'/42
fetched: /test2/primary/...PK.../k/v -> /'019'/42
fetched: /test2/primary/...PK.../k/v -> /'020'/42
fetched: /test2/primary/...PK.../k/v -> /'021'/42
fetched: /test2/primary/...PK.../k/v -> /'022'/42
fetched: /test2/primary/...PK.../k/v -> /'023'/42
fetched: /test2/primary/...PK.../k/v -> /'024'/42
fetched: /test2/primary/...PK.../k/v -> /'025'/42
fetched: /test2/primary/...PK.../k/v -> /'026'/42
fetched: /test2/primary/...PK.../k/v -> /'027'/42
fetched: /test2/primary/...PK.../k/v -> /'028'/42
fetched: /test2/primary/...PK.../k/v -> /'029'/42
fetched: /test2/primary/...PK.../k/v -> /'030'/42

# Regression test for #20035.
statement ok
CREATE TABLE favorites (
  id INT NOT NULL DEFAULT unique_rowid(),
  resource_type STRING(30) NOT NULL,
  resource_key STRING(255) NOT NULL,
  device_group STRING(30) NOT NULL,
  customerid INT NOT NULL,
  jurisdiction STRING(2) NOT NULL,
  brand STRING(255) NOT NULL,
  created_ts TIMESTAMP NULL,
  guid_id STRING(100) NOT NULL,
  locale STRING(10) NOT NULL DEFAULT NULL,
  CONSTRAINT "primary" PRIMARY KEY (id ASC),
  UNIQUE INDEX favorites_idx (resource_type ASC, device_group ASC, resource_key ASC, customerid ASC),
  INDEX favorites_guid_idx (guid_id ASC),
  INDEX favorites_glob_fav_idx (resource_type ASC, device_group ASC, jurisdiction ASC, brand ASC, locale ASC, resource_key ASC),
  FAMILY "primary" (id, resource_type, resource_key, device_group, customerid, jurisdiction, brand, created_ts, guid_id, locale)
)

statement ok
INSERT INTO favorites (customerid, guid_id, resource_type, device_group, jurisdiction, brand, locale, resource_key)
  VALUES (1, '1', 'GAME', 'web', 'MT', 'xxx', 'en_GB', 'tp'),
         (2, '2', 'GAME', 'web', 'MT', 'xxx', 'en_GB', 'ts'),
         (3, '3', 'GAME', 'web', 'MT', 'xxx', 'en_GB', 'ts1'),
         (4, '4', 'GAME', 'web', 'MT', 'xxx', 'en_GB', 'ts2'),
         (5, '5', 'GAME', 'web', 'MT', 'xxx', 'en_GB', 'ts3'),
         (6, '6', 'GAME', 'web', 'MT', 'xxx', 'en_GB', 'ts4')

query TTT
EXPLAIN SELECT
  resource_key,
  count(resource_key) total
FROM favorites f1
WHERE f1.jurisdiction   = 'MT'
AND   f1.brand          = 'xxx'
AND   f1.resource_type  = 'GAME'
AND   f1.device_group   = 'web'
AND   f1.locale         = 'en_GB'
AND   f1.resource_key IN ('ts', 'ts2', 'ts3')
GROUP BY resource_key
ORDER BY total DESC
----
sort            ·            ·
 │              order        -total
 └── group      ·            ·
      │         aggregate 0  resource_key
      │         aggregate 1  count(resource_key)
      │         group by     @2
      └── scan  ·            ·
·               table        favorites@favorites_glob_fav_idx
·               spans        /"GAME"/"web"/"MT"/"xxx"/"en_GB"/"ts"-/"GAME"/"web"/"MT"/"xxx"/"en_GB"/"ts"/PrefixEnd /"GAME"/"web"/"MT"/"xxx"/"en_GB"/"ts2"-/"GAME"/"web"/"MT"/"xxx"/"en_GB"/"ts2"/PrefixEnd /"GAME"/"web"/"MT"/"xxx"/"en_GB"/"ts3"-/"GAME"/"web"/"MT"/"xxx"/"en_GB"/"ts3"/PrefixEnd

query TI rowsort
SELECT
  resource_key,
  count(resource_key) total
FROM favorites f1
WHERE f1.jurisdiction   = 'MT'
AND   f1.brand          = 'xxx'
AND   f1.resource_type  = 'GAME'
AND   f1.device_group   = 'web'
AND   f1.locale         = 'en_GB'
AND   f1.resource_key IN ('ts', 'ts2', 'ts3')
GROUP BY resource_key
ORDER BY total DESC
----
ts 1
ts2 1
ts3 1

# Regression tests for #20362 (IS NULL handling).
query TTTTT
EXPLAIN (VERBOSE) SELECT * FROM abcd@abcd WHERE a IS NULL AND b > 5
----
render     ·         ·                   (a, b, c, d)                         a=CONST; b!=NULL
 │         render 0  test.public.abcd.a  ·                                    ·
 │         render 1  test.public.abcd.b  ·                                    ·
 │         render 2  test.public.abcd.c  ·                                    ·
 │         render 3  test.public.abcd.d  ·                                    ·
 └── scan  ·         ·                   (a, b, c, d, rowid[hidden,omitted])  a=CONST; b!=NULL; rowid!=NULL; weak-key(b,c,d,rowid)
·          table     abcd@abcd           ·                                    ·
·          spans     /NULL/6-/!NULL      ·                                    ·

query TTTTT
EXPLAIN (VERBOSE) SELECT * FROM abcd@abcd WHERE a IS NULL AND b < 5
----
render     ·         ·                    (a, b, c, d)                         a=CONST; b!=NULL
 │         render 0  test.public.abcd.a   ·                                    ·
 │         render 1  test.public.abcd.b   ·                                    ·
 │         render 2  test.public.abcd.c   ·                                    ·
 │         render 3  test.public.abcd.d   ·                                    ·
 └── scan  ·         ·                    (a, b, c, d, rowid[hidden,omitted])  a=CONST; b!=NULL; rowid!=NULL; weak-key(b,c,d,rowid)
·          table     abcd@abcd            ·                                    ·
·          spans     /NULL/!NULL-/NULL/5  ·                                    ·

query TTTTT
EXPLAIN (VERBOSE) SELECT * FROM abcd@abcd WHERE a IS NULL ORDER BY b
----
render     ·         ·                   (a, b, c, d)                         a=CONST; +b
 │         render 0  test.public.abcd.a  ·                                    ·
 │         render 1  test.public.abcd.b  ·                                    ·
 │         render 2  test.public.abcd.c  ·                                    ·
 │         render 3  test.public.abcd.d  ·                                    ·
 └── scan  ·         ·                   (a, b, c, d, rowid[hidden,omitted])  a=CONST; rowid!=NULL; weak-key(b,c,d,rowid); +b
·          table     abcd@abcd           ·                                    ·
·          spans     /NULL-/!NULL        ·                                    ·

query TTTTT
EXPLAIN (VERBOSE) SELECT * FROM abcd@abcd WHERE a = 1 AND b IS NULL AND c > 0 AND c < 10 ORDER BY c
----
render     ·         ·                     (a, b, c, d)                         a=CONST; b=CONST; c!=NULL; +c
 │         render 0  test.public.abcd.a    ·                                    ·
 │         render 1  test.public.abcd.b    ·                                    ·
 │         render 2  test.public.abcd.c    ·                                    ·
 │         render 3  test.public.abcd.d    ·                                    ·
 └── scan  ·         ·                     (a, b, c, d, rowid[hidden,omitted])  a=CONST; b=CONST; c!=NULL; rowid!=NULL; weak-key(c,d,rowid); +c
·          table     abcd@abcd             ·                                    ·
·          spans     /1/NULL/1-/1/NULL/10  ·                                    ·

# Regression test for #3548: verify we create constraints on implicit columns
# when they are part of the key (non-unique index).
statement ok
CREATE TABLE abc (a INT, b INT, c INT, PRIMARY KEY(a,b), INDEX(c))

query TTTTT
EXPLAIN (VERBOSE) SELECT c FROM abc WHERE c = 1 and a = 3
----
render     ·         ·              (c)     ·
 │         render 0  c              ·       ·
 └── scan  ·         ·              (a, c)  ·
·          table     abc@abc_c_idx  ·       ·
·          spans     /1/3-/1/4      ·       ·

# Verify we don't create constraints on implicit columns when they are not part
# of the key (unique index).
statement ok
CREATE TABLE def (d INT, e INT, f INT, PRIMARY KEY(d,e), UNIQUE INDEX(f))

query TTTTT
EXPLAIN (VERBOSE) SELECT f FROM def WHERE f = 1 and d = 3
----
render     ·         ·            (f)     ·
 │         render 0  f            ·       ·
 └── scan  ·         ·            (d, f)  ·
·          table     def@primary  ·       ·
·          spans     /3-/4        ·       ·
·          filter    f = 1        ·       ·

# Regression test for #20504.
query TTTTT
EXPLAIN (VERBOSE) SELECT a, b FROM abc WHERE (a, b) BETWEEN (1, 2) AND (3, 4)
----
scan  ·      ·            (a, b)  ·
·     table  abc@primary  ·       ·
·     spans  /1/2-/3/4/#  ·       ·

# Regression test for #21831.
statement ok
CREATE TABLE str (k INT PRIMARY KEY, v STRING, INDEX(v))

query TTTTT
EXPLAIN (VERBOSE) SELECT k, v FROM str WHERE v LIKE 'ABC%'
----
scan  ·      ·              (k, v)  ·
·     table  str@str_v_idx  ·       ·
·     spans  /"ABC"-/"ABD"  ·       ·

query TTTTT
EXPLAIN (VERBOSE) SELECT k, v FROM str WHERE v LIKE 'ABC%Z'
----
scan  ·       ·               (k, v)  ·
·     table   str@str_v_idx   ·       ·
·     spans   /"ABC"-/"ABD"   ·       ·
·     filter  v LIKE 'ABC%Z'  ·       ·

query TTTTT
EXPLAIN (VERBOSE) SELECT k, v FROM str WHERE v SIMILAR TO 'ABC_*'
----
scan  ·       ·                     (k, v)  ·
·     table   str@str_v_idx         ·       ·
·     spans   /"ABC"-/"ABD"         ·       ·
·     filter  v SIMILAR TO 'ABC_*'  ·       ·

# Test that we generate spans for IS (NOT) DISTINCT FROM.
statement ok
CREATE TABLE xy (x INT, y INT, INDEX (y))

query TTTTT
EXPLAIN (VERBOSE) SELECT * FROM xy WHERE y IS NOT DISTINCT FROM NULL
----
render           ·         ·             (x, y)                 ·
 │               render 0  x             ·                      ·
 │               render 1  y             ·                      ·
 └── index-join  ·         ·             (x, y, rowid[hidden])  ·
      ├── scan   ·         ·             (y, rowid[hidden])     ·
      │          table     xy@xy_y_idx   ·                      ·
      │          spans     /NULL-/!NULL  ·                      ·
      └── scan   ·         ·             (x, y, rowid[hidden])  ·
·                table     xy@primary    ·                      ·

query TTTTT
EXPLAIN (VERBOSE) SELECT * FROM xy WHERE y IS NOT DISTINCT FROM 4
----
render           ·         ·            (x, y)                 ·
 │               render 0  x            ·                      ·
 │               render 1  y            ·                      ·
 └── index-join  ·         ·            (x, y, rowid[hidden])  ·
      ├── scan   ·         ·            (y, rowid[hidden])     ·
      │          table     xy@xy_y_idx  ·                      ·
      │          spans     /4-/5        ·                      ·
      └── scan   ·         ·            (x, y, rowid[hidden])  ·
·                table     xy@primary   ·                      ·

query TTTTT
EXPLAIN (VERBOSE) SELECT x FROM xy WHERE y > 0 AND y < 2 ORDER BY y
----
render           ·         ·            (x)                    ·
 │               render 0  x            ·                      ·
 └── index-join  ·         ·            (x, y, rowid[hidden])  +y
      ├── scan   ·         ·            (y, rowid[hidden])     +y
      │          table     xy@xy_y_idx  ·                      ·
      │          spans     /1-/2        ·                      ·
      └── scan   ·         ·            (x, y, rowid[hidden])  ·
·                table     xy@primary   ·                      ·

query TTTTT
EXPLAIN (VERBOSE) SELECT * FROM xy WHERE y IS DISTINCT FROM NULL
----
scan  ·       ·              (x, y)  ·
·     table   xy@primary     ·       ·
·     spans   ALL            ·       ·
·     filter  y IS NOT NULL  ·       ·

query TTTTT
EXPLAIN (VERBOSE) SELECT * FROM xy WHERE y IS DISTINCT FROM 4
----
scan  ·       ·                     (x, y)  ·
·     table   xy@primary            ·       ·
·     spans   ALL                   ·       ·
·     filter  y IS DISTINCT FROM 4  ·       ·

# Regression tests for #22670.
statement ok
CREATE INDEX xy_idx ON xy (x, y)

statement ok
INSERT INTO xy VALUES (NULL, NULL), (1, NULL), (NULL, 1), (1, 1)

query TTTTT
EXPLAIN (VERBOSE) SELECT * FROM xy WHERE x IN (NULL, 1, 2)
----
scan  ·      ·          (x, y)  ·
·     table  xy@xy_idx  ·       ·
·     spans  /1-/3      ·       ·

query TTTTT
EXPLAIN (VERBOSE) SELECT * FROM xy WHERE (x, y) IN ((NULL, NULL), (1, NULL), (NULL, 1), (1, 1), (1, 2))
----
scan  ·      ·          (x, y)  ·
·     table  xy@xy_idx  ·       ·
·     spans  /1/1-/1/3  ·       ·

# ------------------------------------------------------------------------------
# Non-covering index
# ------------------------------------------------------------------------------
statement ok
CREATE TABLE noncover (
  a INT PRIMARY KEY,
  b INT,
  c INT,
  d INT,
  INDEX b (b),
  UNIQUE INDEX c (c),
  FAMILY (a),
  FAMILY (b),
  FAMILY (c),
  FAMILY (d)
)

statement ok
INSERT INTO noncover VALUES (1, 2, 3, 4), (5, 6, 7, 8)

query TTT
EXPLAIN SELECT * FROM noncover WHERE b = 2
----
index-join  ·      ·
 ├── scan   ·      ·
 │          table  noncover@b
 │          spans  /2-/3
 └── scan   ·      ·
·           table  noncover@primary

query T
SELECT message FROM [SHOW KV TRACE FOR SELECT * FROM noncover WHERE b = 2]
 WHERE message LIKE 'fetched:%' OR message LIKE 'output row%'
----
fetched: /noncover/b/2/1 -> NULL
fetched: /noncover/primary/1 -> NULL
fetched: /noncover/primary/1/b -> 2
fetched: /noncover/primary/1/c -> 3
fetched: /noncover/primary/1/d -> 4
output row: [1 2 3 4]

query TTT
EXPLAIN SELECT * FROM noncover WHERE c = 6
----
index-join  ·      ·
 ├── scan   ·      ·
 │          table  noncover@c
 │          spans  /6-/7
 └── scan   ·      ·
·           table  noncover@primary

query T
SELECT message FROM [SHOW KV TRACE FOR SELECT * FROM noncover WHERE c = 7]
 WHERE message LIKE 'fetched:%' OR message LIKE 'output row%'
----
fetched: /noncover/c/7 -> /5
fetched: /noncover/primary/5 -> NULL
fetched: /noncover/primary/5/b -> 6
fetched: /noncover/primary/5/c -> 7
fetched: /noncover/primary/5/d -> 8
output row: [5 6 7 8]

query TTTTT
EXPLAIN (VERBOSE) SELECT * FROM noncover WHERE c > 0 ORDER BY c DESC
----
sort       ·       ·                 (a, b, c, d)  -c
 │         order   -c                ·             ·
 └── scan  ·       ·                 (a, b, c, d)  ·
·          table   noncover@primary  ·             ·
·          spans   ALL               ·             ·
·          filter  c > 0             ·             ·

query TTTTT
EXPLAIN (VERBOSE) SELECT * FROM noncover WHERE c > 0 ORDER BY c
----
sort       ·       ·                 (a, b, c, d)  +c
 │         order   +c                ·             ·
 └── scan  ·       ·                 (a, b, c, d)  ·
·          table   noncover@primary  ·             ·
·          spans   ALL               ·             ·
·          filter  c > 0             ·             ·

query TTTTT
EXPLAIN (VERBOSE) SELECT * FROM noncover WHERE c > 0 AND d = 8
----
scan  ·       ·                    (a, b, c, d)  ·
·     table   noncover@primary     ·             ·
·     spans   ALL                  ·             ·
·     filter  (c > 0) AND (d = 8)  ·             ·

# The following testcases verify that when we have a small limit, we prefer an
# order-matching index.

query TTT
EXPLAIN SELECT * FROM noncover ORDER BY c
----
sort       ·      ·
 │         order  +c
 └── scan  ·      ·
·          table  noncover@primary
·          spans  ALL

# TODO(radu): need to prefer the order-matching index
query TTT
EXPLAIN SELECT * FROM noncover ORDER BY c LIMIT 5
----
limit           ·      ·
 └── sort       ·      ·
      │         order  +c
      └── scan  ·      ·
·               table  noncover@primary
·               spans  ALL

query TTT
SELECT "Tree", "Field", "Description" FROM [
EXPLAIN (VERBOSE) SELECT * FROM noncover ORDER BY c OFFSET 5
]
----
limit           ·       ·
 │              offset  5
 └── sort       ·       ·
      │         order   +c
      └── scan  ·       ·
·               table   noncover@primary
·               spans   ALL

# TODO(radu): need to prefer the order-matching index
query TTT
SELECT "Tree", "Field", "Description" FROM [
EXPLAIN (VERBOSE) SELECT * FROM noncover ORDER BY c LIMIT 5 OFFSET 5
]
----
limit           ·       ·
 │              count   5
 │              offset  5
 └── sort       ·       ·
      │         order   +c
      └── scan  ·       ·
·               table   noncover@primary
·               spans   ALL

query TTT
SELECT "Tree", "Field", "Description" FROM [
EXPLAIN (VERBOSE) SELECT * FROM noncover ORDER BY c LIMIT 1000000
]
----
limit           ·      ·
 │              count  1000000
 └── sort       ·      ·
      │         order  +c
      └── scan  ·      ·
·               table  noncover@primary
·               spans  ALL

# ------------------------------------------------------------------------------
# These tests verify that while we are joining an index with the table, we
# evaluate what parts of the filter we can using the columns in the index
# to avoid unnecessary lookups in the table.
# ------------------------------------------------------------------------------
statement ok
CREATE TABLE t2 (
  a INT PRIMARY KEY,
  b INT,
  c INT,
  s STRING,
  INDEX bc (b, c),
  FAMILY (a),
  FAMILY (b),
  FAMILY (c),
  FAMILY (s)
)

statement ok
INSERT INTO t2 VALUES
  (1, 1, 1, '11'),
  (2, 1, 2, '12'),
  (3, 1, 3, '13'),
  (4, 2, 1, '21'),
  (5, 2, 2, '22'),
  (6, 2, 3, '23'),
  (7, 3, 1, '31'),
  (8, 3, 2, '32'),
  (9, 3, 3, '33')

query TTT
SELECT "Tree", "Field", "Description" FROM [
EXPLAIN (VERBOSE) SELECT * FROM t2 WHERE b = 2 AND c % 2 = 0
]
----
filter           ·       ·
 │               filter  (c % 2) = 0
 └── index-join  ·       ·
      ├── scan   ·       ·
      │          table   t2@bc
      │          spans   /2-/3
      └── scan   ·       ·
·                table   t2@primary

# We do NOT look up the table row for '21' and '23'.
# TODO(justin): we need to push the c % 2 into the index scan.
query T
SELECT message FROM [SHOW KV TRACE FOR SELECT * FROM t2 WHERE b = 2 AND c % 2 = 0]
 WHERE message LIKE 'fetched:%' OR message LIKE 'output row%'
----
fetched: /t2/bc/2/1/4 -> NULL
fetched: /t2/bc/2/2/5 -> NULL
fetched: /t2/bc/2/3/6 -> NULL
fetched: /t2/primary/4 -> NULL
fetched: /t2/primary/4/b -> 2
fetched: /t2/primary/4/c -> 1
fetched: /t2/primary/4/s -> '21'
fetched: /t2/primary/5 -> NULL
fetched: /t2/primary/5/b -> 2
fetched: /t2/primary/5/c -> 2
fetched: /t2/primary/5/s -> '22'
output row: [5 2 2 '22']
fetched: /t2/primary/6 -> NULL
fetched: /t2/primary/6/b -> 2
fetched: /t2/primary/6/c -> 3
fetched: /t2/primary/6/s -> '23'

query TTT
SELECT "Tree", "Field", "Description" FROM [
EXPLAIN (VERBOSE) SELECT * FROM t2 WHERE b = 2 AND c != b
]
----
filter           ·       ·
 │               filter  c != b
 └── index-join  ·       ·
      ├── scan   ·       ·
      │          table   t2@bc
      │          spans   /2/!NULL-/3
      └── scan   ·       ·
·                table   t2@primary

# We do NOT look up the table row for '22'.
# TODO(justin): we need to push the filter into the index scan.
query T
SELECT message FROM [SHOW KV TRACE FOR SELECT * FROM t2 WHERE b = 2 AND c != b]
 WHERE message LIKE 'fetched:%' OR message LIKE 'output row%'
----
fetched: /t2/bc/2/1/4 -> NULL
fetched: /t2/bc/2/2/5 -> NULL
fetched: /t2/bc/2/3/6 -> NULL
fetched: /t2/primary/4 -> NULL
fetched: /t2/primary/4/b -> 2
fetched: /t2/primary/4/c -> 1
fetched: /t2/primary/4/s -> '21'
output row: [4 2 1 '21']
fetched: /t2/primary/5 -> NULL
fetched: /t2/primary/5/b -> 2
fetched: /t2/primary/5/c -> 2
fetched: /t2/primary/5/s -> '22'
fetched: /t2/primary/6 -> NULL
fetched: /t2/primary/6/b -> 2
fetched: /t2/primary/6/c -> 3
fetched: /t2/primary/6/s -> '23'
output row: [6 2 3 '23']

# We do NOT look up the table row for '22'.
# TODO(justin): we need to push the filter into the index scan.
query T
SELECT message FROM [SHOW KV TRACE FOR SELECT * FROM t2 WHERE b = 2 AND c != b AND s <> '21']
 WHERE message LIKE 'fetched:%' OR message LIKE 'output row%'
----
fetched: /t2/bc/2/1/4 -> NULL
fetched: /t2/bc/2/2/5 -> NULL
fetched: /t2/bc/2/3/6 -> NULL
fetched: /t2/primary/4 -> NULL
fetched: /t2/primary/4/b -> 2
fetched: /t2/primary/4/c -> 1
fetched: /t2/primary/4/s -> '21'
fetched: /t2/primary/5 -> NULL
fetched: /t2/primary/5/b -> 2
fetched: /t2/primary/5/c -> 2
fetched: /t2/primary/5/s -> '22'
fetched: /t2/primary/6 -> NULL
fetched: /t2/primary/6/b -> 2
fetched: /t2/primary/6/c -> 3
fetched: /t2/primary/6/s -> '23'
output row: [6 2 3 '23']

# We only look up the table rows where c = b+1 or a > b+4: '23', '32', '33'.
# TODO(justin): we need to push the filter into the index scan.
query T
SELECT message FROM [SHOW KV TRACE FOR SELECT * FROM t2 WHERE b > 1 AND ((c = b+1 AND s != '23') OR (a > b+4 AND s != '32'))]
 WHERE message LIKE 'fetched:%' OR message LIKE 'output row%'
----
fetched: /t2/primary/1 -> NULL
fetched: /t2/primary/1/b -> 1
fetched: /t2/primary/1/c -> 1
fetched: /t2/primary/1/s -> '11'
fetched: /t2/primary/2 -> NULL
fetched: /t2/primary/2/b -> 1
fetched: /t2/primary/2/c -> 2
fetched: /t2/primary/2/s -> '12'
fetched: /t2/primary/3 -> NULL
fetched: /t2/primary/3/b -> 1
fetched: /t2/primary/3/c -> 3
fetched: /t2/primary/3/s -> '13'
fetched: /t2/primary/4 -> NULL
fetched: /t2/primary/4/b -> 2
fetched: /t2/primary/4/c -> 1
fetched: /t2/primary/4/s -> '21'
fetched: /t2/primary/5 -> NULL
fetched: /t2/primary/5/b -> 2
fetched: /t2/primary/5/c -> 2
fetched: /t2/primary/5/s -> '22'
fetched: /t2/primary/6 -> NULL
fetched: /t2/primary/6/b -> 2
fetched: /t2/primary/6/c -> 3
fetched: /t2/primary/6/s -> '23'
fetched: /t2/primary/7 -> NULL
fetched: /t2/primary/7/b -> 3
fetched: /t2/primary/7/c -> 1
fetched: /t2/primary/7/s -> '31'
fetched: /t2/primary/8 -> NULL
fetched: /t2/primary/8/b -> 3
fetched: /t2/primary/8/c -> 2
fetched: /t2/primary/8/s -> '32'
fetched: /t2/primary/9 -> NULL
fetched: /t2/primary/9/b -> 3
fetched: /t2/primary/9/c -> 3
fetched: /t2/primary/9/s -> '33'
output row: [9 3 3 '33']

# Check that splitting of the expression filter does not mistakenly
# bring non-indexed columns (s) under the index scanNode. (#12582)
# To test this we need an expression containing non-indexed
# columns that disappears during range simplification.
query TTTTT
EXPLAIN (VERBOSE) SELECT a FROM t2 WHERE b = 2 OR ((b BETWEEN 2 AND 1) AND ((s != 'a') OR (s = 'a')))
----
render           ·         ·           (a)        ·
 │               render 0  a           ·          ·
 └── index-join  ·         ·           (a, b, s)  ·
      ├── scan   ·         ·           (a, b)     ·
      │          table     t2@bc       ·          ·
      │          spans     /2-/3       ·          ·
      └── scan   ·         ·           (a, b, s)  ·
·                table     t2@primary  ·          ·

statement ok
CREATE TABLE t3 (k INT PRIMARY KEY, v INT, w INT, INDEX v(v))

query TTTTT
EXPLAIN (VERBOSE) SELECT w FROM t3 WHERE v > 0 AND v < 100 ORDER BY v
----
render           ·         ·           (w)        ·
 │               render 0  w           ·          ·
 └── index-join  ·         ·           (k, v, w)  +v
      ├── scan   ·         ·           (k, v)     +v
      │          table     t3@v        ·          ·
      │          spans     /1-/100     ·          ·
      └── scan   ·         ·           (k, v, w)  ·
·                table     t3@primary  ·          ·


statement ok
CREATE TABLE tab1 (
      pk INTEGER NOT NULL,
      col0 INTEGER NULL,
      col1 FLOAT NULL,
      col2 STRING NULL,
      col3 INTEGER NULL,
      col4 FLOAT NULL,
      col5 STRING NULL,
      CONSTRAINT "primary" PRIMARY KEY (pk ASC),
      INDEX idx_tab1_0 (col0 ASC),
      INDEX idx_tab1_1 (col1 ASC),
      INDEX idx_tab1_3 (col3 ASC),
      INDEX idx_tab1_4 (col4 ASC),
      FAMILY "primary" (pk, col0, col1, col2, col3, col4, col5)
)

# This test verifies the hack in execbuilder to move the sort above the
# lookup join, specifically that the index join node doesn't advertise an
# ordering.
#
# We include the EXPLAIN (OPT) output because this test is fragile: we currently
# prefer the sort under the lookup-join only because of the way the floating
# costs happen to add up (the plan with the sort above the lookup join has the
# same cost).
query T
EXPLAIN (OPT) SELECT pk, col0 FROM tab1 WHERE (col3 BETWEEN 66 AND 87) ORDER BY 1 DESC
----
project
 ├── columns: pk:1(int!null) col0:2(int)
 ├── stats: [rows=31]
 ├── cost: 156.85
 ├── keys: (1)
 ├── ordering: -1
 ├── prune: (1,2)
 └── lookup-join tab1
      ├── columns: tab1.pk:1(int!null) tab1.col0:2(int) tab1.col3:5(int!null)
      ├── key columns: [1]
      ├── stats: [rows=31, distinct(5)=22]
      ├── cost: 156.85
      ├── keys: (1)
      ├── ordering: -1
      ├── prune: (1,2)
      └── sort
           ├── columns: tab1.pk:1(int!null) tab1.col3:5(int)
           ├── stats: [rows=31, distinct(5)=22]
           ├── cost: 32.54
           ├── keys: (1)
           ├── ordering: -1
           ├── prune: (1,5)
           └── scan tab1@idx_tab1_3
                ├── columns: tab1.pk:1(int!null) tab1.col3:5(int)
                ├── constraint: /5/1: [/66 - /87]
                ├── stats: [rows=31, distinct(5)=22]
                ├── cost: 31.00
                ├── keys: (1)
                └── prune: (1,5)

# THe index-join node should not advertise an ordering (it can cause invalid
# distributed plans).
query TTTTT
EXPLAIN (VERBOSE) SELECT pk, col0 FROM tab1 WHERE (col3 BETWEEN 66 AND 87) ORDER BY 1 DESC
----
render                ·         ·                (pk, col0)        ·
 │                    render 0  pk               ·                 ·
 │                    render 1  col0             ·                 ·
 └── sort             ·         ·                (pk, col0, col3)  -pk
      │               order     -pk              ·                 ·
      └── index-join  ·         ·                (pk, col0, col3)  ·
           ├── scan   ·         ·                (pk, col3)        ·
           │          table     tab1@idx_tab1_3  ·                 ·
           │          spans     /66-/88          ·                 ·
           └── scan   ·         ·                (pk, col0, col3)  ·
·                     table     tab1@primary     ·                 ·