lookup_join_spans

# LogicTest: local

statement ok
CREATE TABLE metrics (
  id       SERIAL PRIMARY KEY,
  nullable INT,
  name     STRING,
  INDEX    name_index (name)
)

statement ok
insert into metrics (id,nullable,name) values (1,NULL,'cpu'), (2,1,'cpu'), (3,NULL,'mem'), (4,2,'disk')

statement ok
CREATE TABLE metric_values (
  metric_id INT8,
  time      TIMESTAMPTZ,
  nullable  INT,
  value     INT8,
  PRIMARY KEY (metric_id, time),
  INDEX secondary (metric_id, nullable, time)
)

statement ok
insert into metric_values (metric_id, time, nullable, value) values
 (1,'2020-01-01 00:00:00+00:00',NULL,0),
 (1,'2020-01-01 00:00:01+00:00',1,1),
 (2,'2020-01-01 00:00:00+00:00',NULL,2),
 (2,'2020-01-01 00:00:01+00:00',2,3),
 (3,'2020-01-01 00:01:00+00:00',NULL,4),
 (3,'2020-01-01 00:01:01+00:00',3,5)

# metric_values_desc is a descending time version of metric_values.
statement ok
CREATE TABLE metric_values_desc (
  metric_id INT8,
  time      TIMESTAMPTZ,
  nullable  INT,
  value     INT8,
  PRIMARY KEY (metric_id, time DESC),
  INDEX secondary (metric_id, nullable, time DESC)
)

statement ok
insert into metric_values_desc select * from metric_values

# The final statements below need some stats to chose the lookup join.
statement ok
ALTER TABLE metric_values INJECT STATISTICS
'[
 {
   "columns": ["metric_id"],
   "created_at": "2018-01-01 1:00:00.00000+00:00",
   "row_count": 1000,
   "distinct_count": 10
 },
 {
   "columns": ["time"],
   "created_at": "2018-01-01 1:30:00.00000+00:00",
   "row_count": 1000,
   "distinct_count": 1000
 },
 {
   "columns": ["nullable"],
   "created_at": "2018-01-01 1:30:00.00000+00:00",
   "row_count": 1000,
   "distinct_count": 10,
    "histo_buckets": [
      {"num_eq": 0, "num_range": 0, "distinct_range": 0, "upper_bound": "-10"},
      {"num_eq": 0, "num_range": 1000, "distinct_range": 1000, "upper_bound": "0"}
    ],
    "histo_col_type": "INT"
 },
 {
    "columns": ["value"],
    "created_at": "2018-01-01 1:30:00.00000+00:00",
    "row_count": 1000,
    "distinct_count": 1000
  }
]'

statement ok
ALTER TABLE metrics INJECT STATISTICS
'[
 {
   "columns": ["id"],
   "created_at": "2018-01-01 1:00:00.00000+00:00",
   "row_count": 10,
   "distinct_count": 10
 },
 {
   "columns": ["nullable"],
   "created_at": "2018-01-01 1:30:00.00000+00:00",
   "row_count": 10,
   "distinct_count": 10
 },
 {
   "columns": ["name"],
   "created_at": "2018-01-01 1:30:00.00000+00:00",
   "row_count": 10,
   "distinct_count": 10
 }
]'

query T
EXPLAIN SELECT *
FROM metric_values as v
INNER JOIN metrics as m
ON metric_id=id
WHERE
  time > '2020-01-01 00:00:00+00:00' AND
  name='cpu'
ORDER BY value
----
distribution: local
vectorized: true
·
• sort
│ estimated row count: 33
│ order: +value
│
└── • lookup join
    │ estimated row count: 33
    │ table: metric_values@primary
    │ lookup condition: (metric_id = id) AND ("time" > '2020-01-01 00:00:00+00:00')
    │
    └── • index join
        │ estimated row count: 1
        │ table: metrics@primary
        │
        └── • scan
              estimated row count: 1 (10% of the table; stats collected <hidden> ago)
              table: metrics@name_index
              spans: [/'cpu' - /'cpu']

query T
EXPLAIN SELECT *
FROM metric_values_desc
INNER JOIN metrics
ON metric_id=id
WHERE
  time > '2020-01-01 00:00:00+00:00' AND
  name='cpu'
ORDER BY value
----
distribution: local
vectorized: true
·
• sort
│ order: +value
│
└── • lookup join
    │ table: metric_values_desc@primary
    │ lookup condition: (metric_id = id) AND ("time" > '2020-01-01 00:00:00+00:00')
    │
    └── • index join
        │ estimated row count: 1
        │ table: metrics@primary
        │
        └── • scan
              estimated row count: 1 (10% of the table; stats collected <hidden> ago)
              table: metrics@name_index
              spans: [/'cpu' - /'cpu']

query T
EXPLAIN SELECT *
FROM metric_values
INNER JOIN metrics
ON metric_id=id
WHERE
  time >= '2020-01-01 00:00:00+00:00' AND
  name='cpu'
ORDER BY value
----
distribution: local
vectorized: true
·
• sort
│ estimated row count: 33
│ order: +value
│
└── • lookup join
    │ estimated row count: 33
    │ table: metric_values@primary
    │ lookup condition: (metric_id = id) AND ("time" >= '2020-01-01 00:00:00+00:00')
    │
    └── • index join
        │ estimated row count: 1
        │ table: metrics@primary
        │
        └── • scan
              estimated row count: 1 (10% of the table; stats collected <hidden> ago)
              table: metrics@name_index
              spans: [/'cpu' - /'cpu']

query T
EXPLAIN SELECT *
FROM metric_values_desc
INNER JOIN metrics
ON metric_id=id
WHERE
  time >= '2020-01-01 00:00:00+00:00' AND
  name='cpu'
ORDER BY value
----
distribution: local
vectorized: true
·
• sort
│ order: +value
│
└── • lookup join
    │ table: metric_values_desc@primary
    │ lookup condition: (metric_id = id) AND ("time" >= '2020-01-01 00:00:00+00:00')
    │
    └── • index join
        │ estimated row count: 1
        │ table: metrics@primary
        │
        └── • scan
              estimated row count: 1 (10% of the table; stats collected <hidden> ago)
              table: metrics@name_index
              spans: [/'cpu' - /'cpu']

query T
EXPLAIN SELECT *
FROM metric_values
INNER JOIN metrics
ON metric_id=id
WHERE
  time < '2020-01-01 00:00:00+00:00' AND
  name='cpu'
----
distribution: local
vectorized: true
·
• lookup join
│ estimated row count: 33
│ table: metric_values@primary
│ lookup condition: (metric_id = id) AND ("time" < '2020-01-01 00:00:00+00:00')
│
└── • index join
    │ estimated row count: 1
    │ table: metrics@primary
    │
    └── • scan
          estimated row count: 1 (10% of the table; stats collected <hidden> ago)
          table: metrics@name_index
          spans: [/'cpu' - /'cpu']

query T
EXPLAIN SELECT *
FROM metric_values_desc
INNER JOIN metrics
ON metric_id=id
WHERE
  time < '2020-01-01 00:00:00+00:00' AND
  name='cpu'
----
distribution: local
vectorized: true
·
• lookup join
│ table: metric_values_desc@primary
│ lookup condition: (metric_id = id) AND ("time" < '2020-01-01 00:00:00+00:00')
│
└── • index join
    │ estimated row count: 1
    │ table: metrics@primary
    │
    └── • scan
          estimated row count: 1 (10% of the table; stats collected <hidden> ago)
          table: metrics@name_index
          spans: [/'cpu' - /'cpu']

query T
EXPLAIN SELECT *
FROM metric_values
INNER JOIN metrics
ON metric_id=id
WHERE
  time <= '2020-01-01 00:00:00+00:00' AND
  name='cpu'
ORDER BY value
----
distribution: local
vectorized: true
·
• sort
│ estimated row count: 33
│ order: +value
│
└── • lookup join
    │ estimated row count: 33
    │ table: metric_values@primary
    │ lookup condition: (metric_id = id) AND ("time" <= '2020-01-01 00:00:00+00:00')
    │
    └── • index join
        │ estimated row count: 1
        │ table: metrics@primary
        │
        └── • scan
              estimated row count: 1 (10% of the table; stats collected <hidden> ago)
              table: metrics@name_index
              spans: [/'cpu' - /'cpu']

query T
EXPLAIN SELECT *
FROM metric_values_desc
INNER JOIN metrics
ON metric_id=id
WHERE
  time <= '2020-01-01 00:00:00+00:00' AND
  name='cpu'
ORDER BY value
----
distribution: local
vectorized: true
·
• sort
│ order: +value
│
└── • lookup join
    │ table: metric_values_desc@primary
    │ lookup condition: (metric_id = id) AND ("time" <= '2020-01-01 00:00:00+00:00')
    │
    └── • index join
        │ estimated row count: 1
        │ table: metrics@primary
        │
        └── • scan
              estimated row count: 1 (10% of the table; stats collected <hidden> ago)
              table: metrics@name_index
              spans: [/'cpu' - /'cpu']

query T
EXPLAIN SELECT *
FROM metric_values
INNER JOIN metrics
ON metric_id=id
WHERE
  time < '2020-01-01 00:00:10+00:00' AND
  name='cpu'
ORDER BY value
----
distribution: local
vectorized: true
·
• sort
│ estimated row count: 33
│ order: +value
│
└── • lookup join
    │ estimated row count: 33
    │ table: metric_values@primary
    │ lookup condition: (metric_id = id) AND ("time" < '2020-01-01 00:00:10+00:00')
    │
    └── • index join
        │ estimated row count: 1
        │ table: metrics@primary
        │
        └── • scan
              estimated row count: 1 (10% of the table; stats collected <hidden> ago)
              table: metrics@name_index
              spans: [/'cpu' - /'cpu']

query T
EXPLAIN SELECT *
FROM metric_values_desc
INNER JOIN metrics
ON metric_id=id
WHERE
  time < '2020-01-01 00:00:10+00:00' AND
  name='cpu'
ORDER BY value
----
distribution: local
vectorized: true
·
• sort
│ order: +value
│
└── • lookup join
    │ table: metric_values_desc@primary
    │ lookup condition: (metric_id = id) AND ("time" < '2020-01-01 00:00:10+00:00')
    │
    └── • index join
        │ estimated row count: 1
        │ table: metrics@primary
        │
        └── • scan
              estimated row count: 1 (10% of the table; stats collected <hidden> ago)
              table: metrics@name_index
              spans: [/'cpu' - /'cpu']

query T
EXPLAIN SELECT *
FROM metric_values
INNER JOIN metrics
ON metric_id=id
WHERE
  time BETWEEN '2020-01-01 00:00:00+00:00' AND '2020-01-01 00:10:00+00:00' AND
  name='cpu'
ORDER BY value
----
distribution: local
vectorized: true
·
• sort
│ estimated row count: 11
│ order: +value
│
└── • lookup join
    │ estimated row count: 11
    │ table: metric_values@primary
    │ lookup condition: (metric_id = id) AND (("time" >= '2020-01-01 00:00:00+00:00') AND ("time" <= '2020-01-01 00:10:00+00:00'))
    │
    └── • index join
        │ estimated row count: 1
        │ table: metrics@primary
        │
        └── • scan
              estimated row count: 1 (10% of the table; stats collected <hidden> ago)
              table: metrics@name_index
              spans: [/'cpu' - /'cpu']

query T
EXPLAIN SELECT *
FROM metric_values_desc
INNER JOIN metrics
ON metric_id=id
WHERE
  time BETWEEN '2020-01-01 00:00:00+00:00' AND '2020-01-01 00:10:00+00:00' AND
  name='cpu'
ORDER BY value
----
distribution: local
vectorized: true
·
• sort
│ order: +value
│
└── • lookup join
    │ table: metric_values_desc@primary
    │ lookup condition: (metric_id = id) AND (("time" >= '2020-01-01 00:00:00+00:00') AND ("time" <= '2020-01-01 00:10:00+00:00'))
    │
    └── • index join
        │ estimated row count: 1
        │ table: metrics@primary
        │
        └── • scan
              estimated row count: 1 (10% of the table; stats collected <hidden> ago)
              table: metrics@name_index
              spans: [/'cpu' - /'cpu']

# Test lookup conditions w/ a left join
query T
EXPLAIN SELECT *
FROM metrics
LEFT JOIN metric_values
ON metric_id=id
AND time BETWEEN '2020-01-01 00:00:00+00:00' AND '2020-01-01 00:10:00+00:00'
AND name='cpu'
----
distribution: local
vectorized: true
·
• lookup join (left outer)
│ estimated row count: 11
│ table: metric_values@primary
│ lookup condition: (metric_id = id) AND (("time" >= '2020-01-01 00:00:00+00:00') AND ("time" <= '2020-01-01 00:10:00+00:00'))
│ pred: name = 'cpu'
│
└── • scan
      estimated row count: 10 (100% of the table; stats collected <hidden> ago)
      table: metrics@primary
      spans: FULL SCAN

# Test lookup conditions w/ a semi join
query T
EXPLAIN SELECT *
FROM metrics m
WHERE EXISTS (SELECT * FROM metric_values mv WHERE mv.metric_id = m.id AND time BETWEEN '2020-01-01 00:00:00+00:00' AND '2020-01-01 00:10:00+00:00')
----
distribution: local
vectorized: true
·
• lookup join (semi)
│ estimated row count: 10
│ table: metric_values@primary
│ lookup condition: (metric_id = id) AND (("time" >= '2020-01-01 00:00:00+00:00') AND ("time" <= '2020-01-01 00:10:00+00:00'))
│
└── • scan
      estimated row count: 10 (100% of the table; stats collected <hidden> ago)
      table: metrics@primary
      spans: FULL SCAN

# Test NULL values in pre-join where conditions
query T
EXPLAIN SELECT *
FROM metric_values as v
INNER JOIN metrics as m
ON metric_id=id
AND v.nullable = m.nullable
WHERE
  time > '2020-01-01 00:00:00+00:00' AND
  name='cpu'
ORDER BY value
----
distribution: local
vectorized: true
·
• sort
│ estimated row count: 3
│ order: +value
│
└── • lookup join
    │ estimated row count: 3
    │ table: metric_values@primary
    │ equality: (metric_id, time) = (metric_id,time)
    │ equality cols are key
    │
    └── • lookup join
        │ estimated row count: 3
        │ table: metric_values@secondary
        │ lookup condition: ((metric_id = id) AND (nullable = nullable)) AND ("time" > '2020-01-01 00:00:00+00:00')
        │
        └── • index join
            │ estimated row count: 1
            │ table: metrics@primary
            │
            └── • scan
                  estimated row count: 1 (10% of the table; stats collected <hidden> ago)
                  table: metrics@name_index
                  spans: [/'cpu' - /'cpu']

# Test NULL values in WHERE equality conditions
query T
EXPLAIN SELECT *
FROM metric_values as v
INNER JOIN metrics as m
ON metric_id=id
WHERE
  time > '2020-01-01 00:00:00+00:00' AND
  name='cpu' AND
  v.nullable = m.nullable
ORDER BY value
----
distribution: local
vectorized: true
·
• sort
│ estimated row count: 3
│ order: +value
│
└── • lookup join
    │ estimated row count: 3
    │ table: metric_values@primary
    │ equality: (metric_id, time) = (metric_id,time)
    │ equality cols are key
    │
    └── • lookup join
        │ estimated row count: 3
        │ table: metric_values@secondary
        │ lookup condition: ((metric_id = id) AND (nullable = nullable)) AND ("time" > '2020-01-01 00:00:00+00:00')
        │
        └── • index join
            │ estimated row count: 1
            │ table: metrics@primary
            │
            └── • scan
                  estimated row count: 1 (10% of the table; stats collected <hidden> ago)
                  table: metrics@name_index
                  spans: [/'cpu' - /'cpu']


# Test NULL values in simple equality condition
query T
EXPLAIN SELECT *
FROM metric_values as v
INNER JOIN metrics as m
ON metric_id=id
WHERE
  time > '2020-01-01 00:00:00+00:00' AND
  name='cpu' AND
  v.nullable = 1
ORDER BY value
----
distribution: local
vectorized: true
·
• lookup join
│ estimated row count: 0
│ table: metrics@primary
│ equality: (id) = (id)
│ equality cols are key
│
└── • sort
    │ estimated row count: 0
    │ order: +value
    │
    └── • lookup join
        │ estimated row count: 0
        │ table: metric_values@primary
        │ equality: (metric_id, time) = (metric_id,time)
        │ equality cols are key
        │
        └── • lookup join
            │ table: metric_values@secondary
            │ lookup condition: ((metric_id = id) AND (nullable = 1)) AND ("time" > '2020-01-01 00:00:00+00:00')
            │
            └── • render
                │ estimated row count: 1
                │
                └── • scan
                      estimated row count: 1 (10% of the table; stats collected <hidden> ago)
                      table: metrics@name_index
                      spans: [/'cpu' - /'cpu']

# Test NULL values in range inequality condition
query T
EXPLAIN SELECT *
FROM metric_values as v
INNER JOIN metrics as m
ON metric_id=id
WHERE
  time > '2020-01-01 00:00:00+00:00' AND
  name='cpu' AND
  v.nullable BETWEEN 1 AND 5
ORDER BY value
----
distribution: local
vectorized: true
·
• lookup join
│ estimated row count: 0
│ table: metrics@primary
│ equality: (id) = (id)
│ equality cols are key
│
└── • sort
    │ estimated row count: 0
    │ order: +value
    │
    └── • lookup join
        │ estimated row count: 0
        │ table: metric_values@primary
        │ equality: (metric_id, time) = (metric_id,time)
        │ equality cols are key
        │
        └── • lookup join
            │ estimated row count: 0
            │ table: metric_values@secondary
            │ lookup condition: (metric_id = id) AND ((nullable >= 1) AND (nullable <= 5))
            │ pred: "time" > '2020-01-01 00:00:00+00:00'
            │
            └── • scan
                  estimated row count: 1 (10% of the table; stats collected <hidden> ago)
                  table: metrics@name_index
                  spans: [/'cpu' - /'cpu']

query T
EXPLAIN SELECT *
FROM metric_values as v
INNER JOIN metrics as m
ON metric_id=id
WHERE
  time > '2020-01-01 00:00:00+00:00' AND
  name='cpu' AND
  v.nullable >= 1
----
distribution: local
vectorized: true
·
• lookup join
│ estimated row count: 0
│ table: metrics@primary
│ equality: (id) = (id)
│ equality cols are key
│
└── • lookup join
    │ estimated row count: 0
    │ table: metric_values@primary
    │ equality: (metric_id, time) = (metric_id,time)
    │ equality cols are key
    │
    └── • lookup join
        │ estimated row count: 0
        │ table: metric_values@secondary
        │ lookup condition: (metric_id = id) AND (nullable >= 1)
        │ pred: "time" > '2020-01-01 00:00:00+00:00'
        │
        └── • scan
              estimated row count: 1 (10% of the table; stats collected <hidden> ago)
              table: metrics@name_index
              spans: [/'cpu' - /'cpu']

query T
EXPLAIN SELECT *
FROM metric_values as v
INNER JOIN metrics as m
ON metric_id=id
WHERE
  time > '2020-01-01 00:00:00+00:00' AND
  name='cpu' AND
  v.nullable < -10
----
distribution: local
vectorized: true
·
• lookup join
│ estimated row count: 0
│ table: metrics@primary
│ equality: (id) = (id)
│ equality cols are key
│
└── • lookup join
    │ estimated row count: 0
    │ table: metric_values@primary
    │ equality: (metric_id, time) = (metric_id,time)
    │ equality cols are key
    │
    └── • lookup join
        │ estimated row count: 0
        │ table: metric_values@secondary
        │ lookup condition: (metric_id = id) AND (nullable < -10)
        │ pred: "time" > '2020-01-01 00:00:00+00:00'
        │
        └── • scan
              estimated row count: 1 (10% of the table; stats collected <hidden> ago)
              table: metrics@name_index
              spans: [/'cpu' - /'cpu']