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

     1  query I
     2  SELECT generate_series FROM generate_series(1, 100) ORDER BY generate_series LIMIT 5;
     3  ----
     4  1
     5  2
     6  3
     7  4
     8  5
     9  
    10  query I
    11  SELECT generate_series FROM generate_series(1, 100) ORDER BY generate_series FETCH FIRST 5 ROWS ONLY;
    12  ----
    13  1
    14  2
    15  3
    16  4
    17  5
    18  
    19  query I
    20  SELECT generate_series FROM generate_series(1, 100) ORDER BY generate_series FETCH FIRST ROW ONLY;
    21  ----
    22  1
    23  
    24  query I
    25  SELECT generate_series FROM generate_series(1, 100) ORDER BY generate_series OFFSET 3 ROWS FETCH NEXT ROW ONLY;
    26  ----
    27  4
    28  
    29  statement error syntax error
    30  SELECT generate_series FROM generate_series(1, 100) FETCH NEXT ROW ONLY LIMIT 3;
    31  
    32  statement error syntax error
    33  SELECT generate_series FROM generate_series(1, 100) LIMIT 3 FETCH NEXT ROW ONLY;
    34  
    35  statement error syntax error
    36  SELECT generate_series FROM generate_series(1, 100) FETCH NEXT 1 + 1 ROWS ONLY;
    37  
    38  query I
    39  SELECT generate_series FROM generate_series(1, 100) ORDER BY generate_series FETCH FIRST (1 + 1) ROWS ONLY;
    40  ----
    41  1
    42  2
    43  
    44  statement ok
    45  CREATE TABLE t (k INT PRIMARY KEY, v INT, w INT, INDEX(v))
    46  
    47  statement ok
    48  INSERT INTO t VALUES (1, 1, 1), (2, -4, 8), (3, 9, 27), (4, -16, 94), (5, 25, 125), (6, -36, 216)
    49  
    50  # Verify we don't incorrectly impose a hard limit at the index scan level.
    51  query III
    52  SELECT * FROM t WHERE v > -20 AND w > 30 ORDER BY v LIMIT 2
    53  ----
    54  4  -16  94
    55  5  25   125
    56  
    57  query II
    58  SELECT k, v FROM t ORDER BY k LIMIT 5
    59  ----
    60  1  1
    61  2  -4
    62  3  9
    63  4  -16
    64  5  25
    65  
    66  query II
    67  SELECT k, v FROM t ORDER BY k OFFSET 5
    68  ----
    69  6  -36
    70  
    71  query II
    72  SELECT k, v FROM t ORDER BY v LIMIT (1+4) OFFSET 1
    73  ----
    74  4  -16
    75  2  -4
    76  1  1
    77  3  9
    78  5  25
    79  
    80  query II
    81  SELECT k, v FROM t ORDER BY v DESC LIMIT (1+4) OFFSET 1
    82  ----
    83  3  9
    84  1  1
    85  2  -4
    86  4  -16
    87  6  -36
    88  
    89  query R
    90  SELECT sum(w) FROM t GROUP BY k, v ORDER BY v DESC LIMIT 10
    91  ----
    92  125
    93  27
    94  1
    95  8
    96  94
    97  216
    98  
    99  query I
   100  SELECT k FROM (SELECT k, v FROM t ORDER BY v LIMIT 4)
   101  ----
   102  6
   103  4
   104  2
   105  1
   106  
   107  query I
   108  SELECT k FROM (SELECT k, v, w FROM t ORDER BY v LIMIT 4)
   109  ----
   110  6
   111  4
   112  2
   113  1
   114  
   115  # Use expression for LIMIT/OFFSET value.
   116  query II
   117  SELECT k, v FROM t ORDER BY k LIMIT length(pg_typeof(123))
   118  ----
   119  1  1
   120  2  -4
   121  3  9
   122  4  -16
   123  5  25
   124  6  -36
   125  
   126  query II
   127  SELECT k, v FROM t ORDER BY k LIMIT length(pg_typeof(123)) OFFSET length(pg_typeof(123))-2
   128  ----
   129  5  25
   130  6  -36
   131  
   132  query II
   133  SELECT k, v FROM t ORDER BY k OFFSET (SELECT count(*)-3 FROM t)
   134  ----
   135  4  -16
   136  5  25
   137  6  -36
   138  
   139  query II
   140  SELECT k, v FROM t ORDER BY k LIMIT (SELECT count(*)-3 FROM t) OFFSET (SELECT count(*)-5 FROM t)
   141  ----
   142  2  -4
   143  3  9
   144  4  -16
   145  
   146  # Test sort node with both filter and limit. (https://github.com/cockroachdb/cockroach/issues/31163)
   147  statement ok
   148  SET TRACING = ON; SELECT 1; SET TRACING = OFF
   149  
   150  query I
   151  SELECT SPAN FROM [SHOW TRACE FOR SESSION] WHERE span = 1 LIMIT 1
   152  ----
   153  1
   154  
   155  # Regression test for #38659: offset on top of limit was broken.
   156  
   157  query I
   158  SELECT * FROM (select * from generate_series(1,10) a LIMIT 5) OFFSET 3
   159  ----
   160  4
   161  5
   162  
   163  query I
   164  SELECT * FROM (select * from generate_series(1,10) a LIMIT 5) OFFSET 6
   165  ----
   166  
   167  # Regression test for #47283: scan with both hard limit and soft limit.
   168  statement ok
   169  CREATE TABLE t_47283(k INT PRIMARY KEY, a INT)
   170  
   171  statement ok
   172  INSERT INTO t_47283 VALUES (1, 1), (2, 2), (3, 3), (4, 4), (5, 5), (6, 6)
   173  
   174  # This should return no results; if it does, we incorrectly removed the hard
   175  # limit in the scan.
   176  query II
   177  SELECT * FROM (SELECT * FROM t_47283 ORDER BY k LIMIT 4) WHERE a > 5 LIMIT 1
   178  ----