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

     1  statement ok
     2  CREATE TABLE foo(a INT, b CHAR)
     3  
     4  query I
     5  INSERT INTO foo(a, b) VALUES (1,'c'), (2,'b'), (3,'a') RETURNING @1
     6  ----
     7  1
     8  2
     9  3
    10  
    11  query error invalid column ordinal
    12  SELECT @0 FROM foo
    13  
    14  query error invalid column ordinal
    15  SELECT @42 FROM foo
    16  
    17  query TI rowsort
    18  SELECT @2, @1 FROM foo
    19  ----
    20  c 1
    21  b 2
    22  a 3
    23  
    24  # Traditional SQL ordinals refer to the render list.
    25  query TI
    26  SELECT b, a FROM foo ORDER BY 1
    27  ----
    28  a 3
    29  b 2
    30  c 1
    31  
    32  # CockroachDB column ordinals refer to the data source.
    33  query TI
    34  SELECT b, a FROM foo ORDER BY @1
    35  ----
    36  c 1
    37  b 2
    38  a 3
    39  
    40  query TI
    41  SELECT b, a FROM foo ORDER BY @1 % 2, a
    42  ----
    43  b 2
    44  c 1
    45  a 3
    46  
    47  statement ok
    48  INSERT INTO foo(a, b) VALUES (4, 'c'), (5, 'c'), (6, 'c')
    49  
    50  query R
    51  SELECT sum(a) AS s FROM foo GROUP BY @1 ORDER BY s
    52  ----
    53  1
    54  2
    55  3
    56  4
    57  5
    58  6
    59  
    60  query R
    61  SELECT sum(a) AS s FROM foo GROUP BY @2 ORDER BY s
    62  ----
    63  2
    64  3
    65  16
    66  
    67  statement error column reference @1 not allowed in this context
    68  INSERT INTO foo(a, b) VALUES (@1, @2)
    69  
    70  query error column reference @485 not allowed in this context
    71  VALUES (@485)
    72  
    73  query error column reference @1 not allowed in this context
    74  SELECT * FROM foo LIMIT @1
    75  
    76  query error column reference @1 not allowed in this context
    77  SELECT * FROM foo OFFSET @1