modules/sql-engine/src/integrationTest/sql/group1/explain/scan.test

# description: Tests for printout of various scan operators.
#     Plan around these operators may change in whatever way,
#     the only thing that must be held is an invariant described
#     in comments to every test case.
# group: [explain]

statement ok
CREATE TABLE test_table (c1 INT PRIMARY KEY, c2 INT, c3 INT);

# select all, no renames
explain plan
SELECT * FROM test_table
----
Exchange
    distribution: single
    est: (rows=1)
  TableScan
      table: PUBLIC.TEST_TABLE
      fieldNames: [C1, C2, C3]
      est: (rows=1)

# select with trimming projection, no renames
explain plan
SELECT c1, c2 FROM test_table
----
Exchange
    distribution: single
    est: (rows=1)
  TableScan
      table: PUBLIC.TEST_TABLE
      fieldNames: [C1, C2]
      est: (rows=1)

# select with trimming projection and renames
explain plan
SELECT c1 AS renamed_c1, c2 FROM test_table
----
Exchange
    distribution: single
    est: (rows=1)
  TableScan
      table: PUBLIC.TEST_TABLE
      fieldNames: [RENAMED_C1, C2]
      est: (rows=1)

# select with expression projection, no renames
explain plan
SELECT c1 + c3, c2 FROM test_table
----
Exchange
    distribution: single
    est: (rows=1)
  TableScan
      table: PUBLIC.TEST_TABLE
      fieldNames: [EXPR$0, C2]
      projection: [+(C1, C3), C2]
      est: (rows=1)

# select with expression projection and renames
explain plan
SELECT c1 + c3 AS sum_of_c1_and_c3, c2 FROM test_table
----
Exchange
    distribution: single
    est: (rows=1)
  TableScan
      table: PUBLIC.TEST_TABLE
      fieldNames: [SUM_OF_C1_AND_C3, C2]
      projection: [+(C1, C3), C2]
      est: (rows=1)

# Similar set of tests but for IndexScan
statement ok
CREATE INDEX test_table_idx ON test_table (c1, c2, c3);

# select all, no renames
explain plan
SELECT /*+ FORCE_INDEX(test_table_idx) */ * FROM test_table
----
Exchange
    distribution: single
    est: (rows=1)
  IndexScan
      table: PUBLIC.TEST_TABLE
      index: TEST_TABLE_IDX
      type: SORTED
      fieldNames: [C1, C2, C3]
      collation: [C1 ASC, C2 ASC, C3 ASC]
      est: (rows=1)

# select with trimming projection, no renames
explain plan
SELECT /*+ FORCE_INDEX(test_table_idx) */ c1, c2 FROM test_table
----
Exchange
    distribution: single
    est: (rows=1)
  IndexScan
      table: PUBLIC.TEST_TABLE
      index: TEST_TABLE_IDX
      type: SORTED
      fieldNames: [C1, C2]
      collation: [C1 ASC, C2 ASC]
      est: (rows=1)

# select with trimming projection and renames
explain plan
SELECT /*+ FORCE_INDEX(test_table_idx) */ c1 AS renamed_c1, c2 FROM test_table
----
Exchange
    distribution: single
    est: (rows=1)
  IndexScan
      table: PUBLIC.TEST_TABLE
      index: TEST_TABLE_IDX
      type: SORTED
      fieldNames: [RENAMED_C1, C2]
      collation: [RENAMED_C1 ASC, C2 ASC]
      est: (rows=1)

# select with expression projection, no renames
explain plan
SELECT /*+ FORCE_INDEX(test_table_idx) */ c1 + c3, c2 FROM test_table
----
Exchange
    distribution: single
    est: (rows=1)
  IndexScan
      table: PUBLIC.TEST_TABLE
      index: TEST_TABLE_IDX
      type: SORTED
      fieldNames: [EXPR$0, C2]
      projection: [+(C1, C3), C2]
      collation: []
      est: (rows=1)

# select with expression projection and renames
explain plan
SELECT /*+ FORCE_INDEX(test_table_idx) */ c1 + c3 AS sum_of_c1_and_c3, c2 FROM test_table
----
Exchange
    distribution: single
    est: (rows=1)
  IndexScan
      table: PUBLIC.TEST_TABLE
      index: TEST_TABLE_IDX
      type: SORTED
      fieldNames: [SUM_OF_C1_AND_C3, C2]
      projection: [+(C1, C3), C2]
      collation: []
      est: (rows=1)

# Similar set of tests but for SystemViewScan
# select all, no renames
explain plan
SELECT * FROM system.system_views
----
SystemViewScan
    table: SYSTEM.SYSTEM_VIEWS
    fieldNames: [VIEW_ID, SCHEMA_NAME, VIEW_NAME, VIEW_TYPE, ID, SCHEMA, NAME, TYPE]
    est: (rows=100)

# select with trimming projection, no renames
explain plan
SELECT view_id, view_name FROM system.system_views
----
SystemViewScan
    table: SYSTEM.SYSTEM_VIEWS
    fieldNames: [VIEW_ID, VIEW_NAME]
    est: (rows=100)

# select with trimming projection and renames
explain plan
SELECT view_id AS renamed_view_id, view_name FROM system.system_views
----
SystemViewScan
    table: SYSTEM.SYSTEM_VIEWS
    fieldNames: [RENAMED_VIEW_ID, VIEW_NAME]
    est: (rows=100)

# select with expression projection, no renames
explain plan
SELECT view_id + 5, view_name FROM system.system_views
----
SystemViewScan
    table: SYSTEM.SYSTEM_VIEWS
    fieldNames: [EXPR$0, VIEW_NAME]
    projection: [+(VIEW_ID, 5), VIEW_NAME]
    est: (rows=100)

# select with expression projection and renames
explain plan
SELECT view_id + 5 AS adjusted_view_id, view_name FROM system.system_views
----
SystemViewScan
    table: SYSTEM.SYSTEM_VIEWS
    fieldNames: [ADJUSTED_VIEW_ID, VIEW_NAME]
    projection: [+(VIEW_ID, 5), VIEW_NAME]
    est: (rows=100)

# Complex query where column of source table is renamed, and this renamed column is referenced by
# correlated scalar query. This rename MUST remains in the plan to preserve semantic of the query 
explain plan
SELECT /*+ disable_decorrelation */
  (SELECT COUNT(*)
     FROM test_table inner_t
    WHERE inner_t.c1 = renamed_c1
  )
  FROM (SELECT c1, c1 as renamed_c1 FROM test_table) as t
----
Project
    fieldNames: [EXPR$0]
    projection: [EXPR$0]
    est: (rows=1)
  CorrelatedNestedLoopJoin
      predicate: true
      type: left
      correlates: [$cor0]
      est: (rows=1)
    Exchange
        distribution: single
        est: (rows=1)
      TableScan
          table: PUBLIC.TEST_TABLE
          fieldNames: [RENAMED_C1]
          est: (rows=1)
    ColocatedHashAggregate
        fieldNames: [EXPR$0]
        group: []
        aggregation: [COUNT()]
        est: (rows=1)
      Exchange
          distribution: single
          est: (rows=1)
        TableScan
            table: PUBLIC.TEST_TABLE
            predicate: =(C1, $cor0.RENAMED_C1)
            fieldNames: [C1]
            est: (rows=1)

# Complex query where expression involving a column of source table is renamed, and this renamed expression is referenced by
# correlated scalar query. This rename MUST remains in the plan to preserve semantic of the query 
explain plan
SELECT /*+ disable_decorrelation */
  (SELECT COUNT(*)
     FROM test_table inner_t
    WHERE inner_t.c1 = doubled_c1
  )
  FROM (SELECT c1, c1 * 2 as doubled_c1 FROM test_table) as t
----
Project
    fieldNames: [EXPR$0]
    projection: [EXPR$0]
    est: (rows=1)
  CorrelatedNestedLoopJoin
      predicate: true
      type: left
      correlates: [$cor0]
      est: (rows=1)
    Exchange
        distribution: single
        est: (rows=1)
      TableScan
          table: PUBLIC.TEST_TABLE
          fieldNames: [DOUBLED_C1]
          projection: [*(C1, 2)]
          est: (rows=1)
    ColocatedHashAggregate
        fieldNames: [EXPR$0]
        group: []
        aggregation: [COUNT()]
        est: (rows=1)
      Exchange
          distribution: single
          est: (rows=1)
        TableScan
            table: PUBLIC.TEST_TABLE
            predicate: =(C1, $cor0.DOUBLED_C1)
            fieldNames: [C1]
            est: (rows=1)

# table scan with simple predicate
explain plan
SELECT c1 FROM test_table WHERE c3 = 1 
----
Exchange
    distribution: single
    est: (rows=1)
  TableScan
      table: PUBLIC.TEST_TABLE
      predicate: =(C3, 1)
      fieldNames: [C1]
      est: (rows=1)

# table scan with complex predicate
explain plan
SELECT c1 FROM test_table WHERE c3 IN (1, 2, 3) AND CURRENT_TIMESTAMP > timestamp '1970-01-01' 
----
Exchange
    distribution: single
    est: (rows=1)
  TableScan
      table: PUBLIC.TEST_TABLE
      predicate: AND(SEARCH(C3, Sarg[1, 2, 3]), >(CAST(CURRENT_TIMESTAMP):TIMESTAMP(0) NOT NULL, 1970-01-01 00:00:00))
      fieldNames: [C1]
      est: (rows=1)

# index scan with simple predicate
explain plan
SELECT /*+ FORCE_INDEX(test_table_idx) */ c1 FROM test_table WHERE c1 > 1 
----
Exchange
    distribution: single
    est: (rows=1)
  IndexScan
      table: PUBLIC.TEST_TABLE
      index: TEST_TABLE_IDX
      type: SORTED
      predicate: >(C1, 1)
      searchBounds: (<1>..<null:INTEGER>)
      fieldNames: [C1]
      collation: [C1 ASC]
      est: (rows=1)

# index scan with complex predicate
explain plan
SELECT /*+ FORCE_INDEX(test_table_idx) */ c1 FROM test_table WHERE c1 IN (1, 2, 3) AND CURRENT_TIMESTAMP > timestamp '1970-01-01' 
----
Exchange
    distribution: single
    est: (rows=1)
  IndexScan
      table: PUBLIC.TEST_TABLE
      index: TEST_TABLE_IDX
      type: SORTED
      predicate: AND(SEARCH(C1, Sarg[1, 2, 3]), >(CAST(CURRENT_TIMESTAMP):TIMESTAMP(0) NOT NULL, 1970-01-01 00:00:00))
      searchBounds: <1>, <2>, <3>
      fieldNames: [C1]
      collation: [C1 ASC]
      est: (rows=1)

statement ok
CREATE INDEX hash_idx ON test_table USING HASH (c2);

# look up by hash index
explain plan
SELECT /*+ FORCE_INDEX(hash_idx) */ c1 FROM test_table WHERE c2 = 10; 
----
Exchange
    distribution: single
    est: (rows=1)
  IndexScan
      table: PUBLIC.TEST_TABLE
      index: HASH_IDX
      type: HASH
      predicate: =(C2, 10)
      searchBounds: <10>
      fieldNames: [C1]
      est: (rows=1)

statement ok
CREATE INDEX test_table_different_collation_idx ON test_table USING SORTED (c2 DESC NULLS LAST);

# index scan with different collation
explain plan
SELECT /*+ FORCE_INDEX(test_table_different_collation_idx) */ c1, c2 FROM test_table 
----
Exchange
    distribution: single
    est: (rows=1)
  IndexScan
      table: PUBLIC.TEST_TABLE
      index: TEST_TABLE_DIFFERENT_COLLATION_IDX
      type: SORTED
      fieldNames: [C1, C2]
      collation: [C2 DESC NULLS LAST]
      est: (rows=1)

# system view scan with simple predicate
explain plan
SELECT view_id, view_name FROM system.system_views WHERE view_id = 5
----
SystemViewScan
    table: SYSTEM.SYSTEM_VIEWS
    predicate: =(VIEW_ID, 5)
    fieldNames: [VIEW_ID, VIEW_NAME]
    est: (rows=33)

# table scan with complex predicate
explain plan
SELECT view_id, view_name FROM system.system_views WHERE view_id IN (1, 2, 3) AND CURRENT_TIMESTAMP > timestamp '1970-01-01' 
----
SystemViewScan
    table: SYSTEM.SYSTEM_VIEWS
    predicate: AND(SEARCH(VIEW_ID, Sarg[1, 2, 3]), >(CAST(CURRENT_TIMESTAMP):TIMESTAMP(0) NOT NULL, 1970-01-01 00:00:00))
    fieldNames: [VIEW_ID, VIEW_NAME]
    est: (rows=38)

# table function scan
explain plan
SELECT * FROM system_range(1, 10)
----
TableFunctionScan
    fieldNames: [X]
    invocation: SYSTEM_RANGE(1, 10)
    est: (rows=100)

# another table function scan
explain plan
SELECT * FROM system_range(1, LENGTH(rand_uuid()::VARCHAR))
----
TableFunctionScan
    fieldNames: [X]
    invocation: SYSTEM_RANGE(1, LENGTH(CAST(RAND_UUID()):VARCHAR CHARACTER SET "UTF-8" NOT NULL))
    est: (rows=100)