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

     1  # LogicTest: !3node-tenant
     2  query TI colnames
     3  SELECT * FROM (VALUES ('a'), ('b')) WITH ORDINALITY AS x(name, i)
     4  ----
     5  name i
     6  a 1
     7  b 2
     8  
     9  query I colnames
    10  SELECT ordinality FROM (VALUES ('a'), ('b')) WITH ORDINALITY
    11  ----
    12  ordinality
    13  1
    14  2
    15  
    16  statement ok
    17  CREATE TABLE foo (x CHAR PRIMARY KEY); INSERT INTO foo(x) VALUES ('a'), ('b')
    18  
    19  query TI
    20  SELECT * FROM foo WITH ORDINALITY
    21  ----
    22  a 1
    23  b 2
    24  
    25  query TI
    26  SELECT * FROM foo WITH ORDINALITY LIMIT 1
    27  ----
    28  a 1
    29  
    30  query I
    31  SELECT max(ordinality) FROM foo WITH ORDINALITY
    32  ----
    33  2
    34  
    35  query TITI rowsort
    36  SELECT * FROM foo WITH ORDINALITY AS a, foo WITH ORDINALITY AS b
    37  ----
    38  a 1 a 1
    39  a 1 b 2
    40  b 2 a 1
    41  b 2 b 2
    42  
    43  query TI
    44  SELECT * FROM (SELECT x||x FROM foo) WITH ORDINALITY
    45  ----
    46  aa 1
    47  bb 2
    48  
    49  query TII rowsort
    50  SELECT * FROM (SELECT x, ordinality*2 FROM foo WITH ORDINALITY AS a) JOIN foo WITH ORDINALITY AS b USING(x)
    51  ----
    52  a 2 1
    53  b 4 2
    54  
    55  query TI
    56  SELECT * FROM (SELECT * FROM foo ORDER BY x DESC) WITH ORDINALITY LIMIT 1
    57  ----
    58  b 1
    59  
    60  query TI
    61  SELECT * FROM (SELECT * FROM foo ORDER BY x) WITH ORDINALITY ORDER BY x DESC LIMIT 1
    62  ----
    63  b 2
    64  
    65  query TI
    66  SELECT * FROM (SELECT * FROM foo ORDER BY x) WITH ORDINALITY ORDER BY ordinality DESC LIMIT 1
    67  ----
    68  b 2
    69  
    70  statement ok
    71  INSERT INTO foo(x) VALUES ('c')
    72  
    73  query TI
    74  SELECT * FROM foo WITH ORDINALITY WHERE x > 'a'
    75  ----
    76  b 2
    77  c 3
    78  
    79  query TI
    80  SELECT * FROM foo WITH ORDINALITY WHERE ordinality > 1 ORDER BY ordinality DESC
    81  ----
    82  c 3
    83  b 2
    84  
    85  query TI
    86  SELECT * FROM (SELECT * FROM foo WHERE x > 'a') WITH ORDINALITY
    87  ----
    88  b 1
    89  c 2
    90  
    91  query B
    92  SELECT ordinality = row_number() OVER () FROM foo WITH ORDINALITY
    93  ----
    94  true
    95  true
    96  true
    97  
    98  # Regression test for #33659
    99  statement ok
   100  TABLE [SHOW ZONE CONFIGURATIONS] WITH ORDINALITY
   101  
   102  # Regression test for #41760
   103  query TI
   104  SELECT * FROM (SELECT * FROM foo LIMIT 1) WITH ORDINALITY
   105  ----
   106  a 1