github.com/cockroachdb/cockroach@v20.2.0-alpha.1+incompatible/pkg/sql/opt/optbuilder/testdata/limit (about) 1 # tests adapted from logictest -- limit 2 3 exec-ddl 4 CREATE TABLE t (k INT PRIMARY KEY, v INT, w INT, INDEX(v)) 5 ---- 6 7 build 8 SELECT k, v FROM t ORDER BY k LIMIT 5 9 ---- 10 limit 11 ├── columns: k:1!null v:2 12 ├── internal-ordering: +1 13 ├── ordering: +1 14 ├── project 15 │ ├── columns: k:1!null v:2 16 │ ├── ordering: +1 17 │ ├── limit hint: 5.00 18 │ └── scan t 19 │ ├── columns: k:1!null v:2 w:3 20 │ ├── ordering: +1 21 │ └── limit hint: 5.00 22 └── 5 23 24 build 25 SELECT k, v FROM t ORDER BY v FETCH FIRST 5 ROWS ONLY 26 ---- 27 limit 28 ├── columns: k:1!null v:2 29 ├── internal-ordering: +2 30 ├── ordering: +2 31 ├── sort 32 │ ├── columns: k:1!null v:2 33 │ ├── ordering: +2 34 │ ├── limit hint: 5.00 35 │ └── project 36 │ ├── columns: k:1!null v:2 37 │ └── scan t 38 │ └── columns: k:1!null v:2 w:3 39 └── 5 40 41 build 42 SELECT k, v FROM t LIMIT (1+2) 43 ---- 44 limit 45 ├── columns: k:1!null v:2 46 ├── project 47 │ ├── columns: k:1!null v:2 48 │ ├── limit hint: 3.00 49 │ └── scan t 50 │ ├── columns: k:1!null v:2 w:3 51 │ └── limit hint: 3.00 52 └── 3 53 54 build 55 SELECT k FROM t ORDER BY k FETCH FIRST ROW ONLY 56 ---- 57 limit 58 ├── columns: k:1!null 59 ├── internal-ordering: +1 60 ├── ordering: +1 61 ├── project 62 │ ├── columns: k:1!null 63 │ ├── ordering: +1 64 │ ├── limit hint: 1.00 65 │ └── scan t 66 │ ├── columns: k:1!null v:2 w:3 67 │ ├── ordering: +1 68 │ └── limit hint: 1.00 69 └── 1 70 71 build 72 SELECT k FROM t ORDER BY k OFFSET 3 ROWS FETCH NEXT ROW ONLY 73 ---- 74 limit 75 ├── columns: k:1!null 76 ├── internal-ordering: +1 77 ├── ordering: +1 78 ├── offset 79 │ ├── columns: k:1!null 80 │ ├── internal-ordering: +1 81 │ ├── ordering: +1 82 │ ├── limit hint: 1.00 83 │ ├── project 84 │ │ ├── columns: k:1!null 85 │ │ ├── ordering: +1 86 │ │ ├── limit hint: 4.00 87 │ │ └── scan t 88 │ │ ├── columns: k:1!null v:2 w:3 89 │ │ ├── ordering: +1 90 │ │ └── limit hint: 4.00 91 │ └── 3 92 └── 1 93 94 build 95 SELECT k, v FROM t ORDER BY k OFFSET 5 96 ---- 97 offset 98 ├── columns: k:1!null v:2 99 ├── internal-ordering: +1 100 ├── ordering: +1 101 ├── project 102 │ ├── columns: k:1!null v:2 103 │ ├── ordering: +1 104 │ └── scan t 105 │ ├── columns: k:1!null v:2 w:3 106 │ └── ordering: +1 107 └── 5 108 109 build 110 SELECT k FROM t ORDER BY k FETCH FIRST (1+1) ROWS ONLY 111 ---- 112 limit 113 ├── columns: k:1!null 114 ├── internal-ordering: +1 115 ├── ordering: +1 116 ├── project 117 │ ├── columns: k:1!null 118 │ ├── ordering: +1 119 │ ├── limit hint: 2.00 120 │ └── scan t 121 │ ├── columns: k:1!null v:2 w:3 122 │ ├── ordering: +1 123 │ └── limit hint: 2.00 124 └── 2 125 126 build 127 SELECT k FROM T LIMIT k 128 ---- 129 error (42703): column "k" does not exist 130 131 build 132 SELECT k FROM T LIMIT v 133 ---- 134 error (42703): column "v" does not exist 135 136 build 137 SELECT sum(w) FROM t GROUP BY k, v ORDER BY v DESC LIMIT 10 138 ---- 139 limit 140 ├── columns: sum:4 [hidden: v:2] 141 ├── internal-ordering: -2 142 ├── ordering: -2 143 ├── project 144 │ ├── columns: v:2 sum:4 145 │ ├── ordering: -2 146 │ ├── limit hint: 10.00 147 │ └── group-by 148 │ ├── columns: k:1!null v:2 sum:4 149 │ ├── grouping columns: k:1!null v:2 150 │ ├── ordering: -2 151 │ ├── limit hint: 10.00 152 │ ├── sort 153 │ │ ├── columns: k:1!null v:2 w:3 154 │ │ ├── ordering: -2 155 │ │ └── scan t 156 │ │ └── columns: k:1!null v:2 w:3 157 │ └── aggregations 158 │ └── sum [as=sum:4] 159 │ └── w:3 160 └── 10 161 162 build 163 SELECT DISTINCT v FROM T ORDER BY v LIMIT 10 164 ---- 165 limit 166 ├── columns: v:2 167 ├── internal-ordering: +2 168 ├── ordering: +2 169 ├── sort 170 │ ├── columns: v:2 171 │ ├── ordering: +2 172 │ ├── limit hint: 10.00 173 │ └── distinct-on 174 │ ├── columns: v:2 175 │ ├── grouping columns: v:2 176 │ └── project 177 │ ├── columns: v:2 178 │ └── scan t 179 │ └── columns: k:1!null v:2 w:3 180 └── 10 181 182 build 183 VALUES (1,1), (2,2) ORDER BY 1 LIMIT 1 184 ---- 185 limit 186 ├── columns: column1:1!null column2:2!null 187 ├── internal-ordering: +1 188 ├── ordering: +1 189 ├── sort 190 │ ├── columns: column1:1!null column2:2!null 191 │ ├── ordering: +1 192 │ ├── limit hint: 1.00 193 │ └── values 194 │ ├── columns: column1:1!null column2:2!null 195 │ ├── (1, 1) 196 │ └── (2, 2) 197 └── 1 198 199 build 200 (VALUES (1), (1), (1), (2), (2) UNION ALL VALUES (1), (3), (1)) ORDER BY 1 DESC LIMIT 2 201 ---- 202 limit 203 ├── columns: column1:3!null 204 ├── internal-ordering: -3 205 ├── ordering: -3 206 ├── sort 207 │ ├── columns: column1:3!null 208 │ ├── ordering: -3 209 │ ├── limit hint: 2.00 210 │ └── union-all 211 │ ├── columns: column1:3!null 212 │ ├── left columns: column1:1 213 │ ├── right columns: column1:2 214 │ ├── values 215 │ │ ├── columns: column1:1!null 216 │ │ ├── (1,) 217 │ │ ├── (1,) 218 │ │ ├── (1,) 219 │ │ ├── (2,) 220 │ │ └── (2,) 221 │ └── values 222 │ ├── columns: column1:2!null 223 │ ├── (1,) 224 │ ├── (3,) 225 │ └── (1,) 226 └── 2 227 228 # The ORDER BY and LIMIT apply to the UNION, not the last VALUES. 229 build 230 VALUES (1), (1), (1), (2), (2) UNION ALL VALUES (1), (3), (1) ORDER BY 1 DESC LIMIT 2 231 ---- 232 limit 233 ├── columns: column1:3!null 234 ├── internal-ordering: -3 235 ├── ordering: -3 236 ├── sort 237 │ ├── columns: column1:3!null 238 │ ├── ordering: -3 239 │ ├── limit hint: 2.00 240 │ └── union-all 241 │ ├── columns: column1:3!null 242 │ ├── left columns: column1:1 243 │ ├── right columns: column1:2 244 │ ├── values 245 │ │ ├── columns: column1:1!null 246 │ │ ├── (1,) 247 │ │ ├── (1,) 248 │ │ ├── (1,) 249 │ │ ├── (2,) 250 │ │ └── (2,) 251 │ └── values 252 │ ├── columns: column1:2!null 253 │ ├── (1,) 254 │ ├── (3,) 255 │ └── (1,) 256 └── 2 257 258 build 259 SELECT k FROM (SELECT k, v FROM t ORDER BY v LIMIT 10) 260 ---- 261 project 262 ├── columns: k:1!null 263 └── limit 264 ├── columns: k:1!null v:2 265 ├── internal-ordering: +2 266 ├── sort 267 │ ├── columns: k:1!null v:2 268 │ ├── ordering: +2 269 │ ├── limit hint: 10.00 270 │ └── project 271 │ ├── columns: k:1!null v:2 272 │ └── scan t 273 │ └── columns: k:1!null v:2 w:3 274 └── 10 275 276 # This kind of query can be used to work around memory usage limits. We need to 277 # choose the "hard" limit of 100 over the "soft" limit of 25 (with the hard 278 # limit we will only store 100 rows in the sort node). See #19677. 279 build 280 SELECT DISTINCT w FROM (SELECT w FROM t ORDER BY w LIMIT 100) ORDER BY w LIMIT 25 281 ---- 282 limit 283 ├── columns: w:3 284 ├── internal-ordering: +3 285 ├── ordering: +3 286 ├── distinct-on 287 │ ├── columns: w:3 288 │ ├── grouping columns: w:3 289 │ ├── ordering: +3 290 │ ├── limit hint: 25.00 291 │ └── limit 292 │ ├── columns: w:3 293 │ ├── internal-ordering: +3 294 │ ├── ordering: +3 295 │ ├── limit hint: 40.39 296 │ ├── sort 297 │ │ ├── columns: w:3 298 │ │ ├── ordering: +3 299 │ │ ├── limit hint: 100.00 300 │ │ └── project 301 │ │ ├── columns: w:3 302 │ │ └── scan t 303 │ │ └── columns: k:1!null v:2 w:3 304 │ └── 100 305 └── 25 306 307 build 308 SELECT * FROM t LIMIT @1 309 ---- 310 error (42703): column reference @1 not allowed in this context 311 312 build 313 SELECT * FROM t OFFSET @1 314 ---- 315 error (42703): column reference @1 not allowed in this context 316 317 build 318 SELECT * FROM t LIMIT count(*) 319 ---- 320 error (42803): count_rows(): aggregate functions are not allowed in LIMIT 321 322 build 323 SELECT * FROM t OFFSET count(*) 324 ---- 325 error (42803): count_rows(): aggregate functions are not allowed in OFFSET 326 327 build 328 SELECT * FROM t LIMIT count(w) 329 ---- 330 error (42703): column "w" does not exist 331 332 build 333 SELECT * FROM t OFFSET count(w) 334 ---- 335 error (42703): column "w" does not exist 336 337 build 338 SELECT sum(v) FROM t GROUP BY k LIMIT count(*) OVER () 339 ---- 340 error (42P20): count_rows(): window functions are not allowed in LIMIT 341 342 build 343 SELECT sum(v) FROM t GROUP BY k OFFSET count(*) OVER () 344 ---- 345 error (42P20): count_rows(): window functions are not allowed in OFFSET