github.com/cockroachdb/cockroach@v20.2.0-alpha.1+incompatible/pkg/sql/opt/exec/execbuilder/testdata/ordinality (about)

     1  # LogicTest: local
     2  
     3  statement ok
     4  CREATE TABLE foo (x CHAR PRIMARY KEY); INSERT INTO foo(x) VALUES ('a'), ('b')
     5  
     6  query TTTTT
     7  EXPLAIN (VERBOSE) SELECT max(ordinality) FROM foo WITH ORDINALITY
     8  ----
     9  ·                distributed  false            ·               ·
    10  ·                vectorized   true             ·               ·
    11  group            ·            ·                (max)           ·
    12   │               aggregate 0  max(ordinality)  ·               ·
    13   │               scalar       ·                ·               ·
    14   └── ordinality  ·            ·                ("ordinality")  ·
    15        └── scan   ·            ·                ()              ·
    16  ·                table        foo@primary      ·               ·
    17  ·                spans        FULL SCAN        ·               ·
    18  
    19  query TTTTT
    20  EXPLAIN (VERBOSE) SELECT * FROM foo WITH ORDINALITY WHERE ordinality > 1 ORDER BY ordinality
    21  ----
    22  ·                distributed  false             ·                  ·
    23  ·                vectorized   true              ·                  ·
    24  filter           ·            ·                 (x, "ordinality")  +"ordinality"
    25   │               filter       "ordinality" > 1  ·                  ·
    26   └── ordinality  ·            ·                 (x, "ordinality")  ·
    27        └── scan   ·            ·                 (x)                ·
    28  ·                table        foo@primary       ·                  ·
    29  ·                spans        FULL SCAN         ·                  ·
    30  
    31  query TTTTT
    32  EXPLAIN (VERBOSE) SELECT * FROM foo WITH ORDINALITY WHERE ordinality > 1 ORDER BY ordinality DESC
    33  ----
    34  ·                     distributed  false             ·                  ·
    35  ·                     vectorized   true              ·                  ·
    36  sort                  ·            ·                 (x, "ordinality")  -"ordinality"
    37   │                    order        -"ordinality"     ·                  ·
    38   └── filter           ·            ·                 (x, "ordinality")  ·
    39        │               filter       "ordinality" > 1  ·                  ·
    40        └── ordinality  ·            ·                 (x, "ordinality")  ·
    41             └── scan   ·            ·                 (x)                ·
    42  ·                     table        foo@primary       ·                  ·
    43  ·                     spans        FULL SCAN         ·                  ·
    44  
    45  # Show that the primary key is used under ordinalityNode.
    46  query TTTTT
    47  EXPLAIN (VERBOSE) SELECT * FROM (SELECT * FROM foo WHERE x > 'a') WITH ORDINALITY
    48  ----
    49  ·           distributed  false        ·                  ·
    50  ·           vectorized   true         ·                  ·
    51  ordinality  ·            ·            (x, "ordinality")  ·
    52   └── scan   ·            ·            (x)                ·
    53  ·           table        foo@primary  ·                  ·
    54  ·           spans        /"a\x00"-    ·                  ·
    55  
    56  # Show that the primary key cannot be used with a PK predicate
    57  # outside of ordinalityNode.
    58  query TTTTT
    59  EXPLAIN (VERBOSE) SELECT * FROM foo WITH ORDINALITY WHERE x > 'a'
    60  ----
    61  ·                distributed  false        ·                  ·
    62  ·                vectorized   true         ·                  ·
    63  filter           ·            ·            (x, "ordinality")  ·
    64   │               filter       x > 'a'      ·                  ·
    65   └── ordinality  ·            ·            (x, "ordinality")  ·
    66        └── scan   ·            ·            (x)                ·
    67  ·                table        foo@primary  ·                  ·
    68  ·                spans        FULL SCAN    ·                  ·