github.com/cockroachdb/cockroach@v20.2.0-alpha.1+incompatible/pkg/sql/opt/optbuilder/testdata/srfs (about) 1 # tests adapted from logictest -- srfs 2 3 # generate_series 4 5 build 6 SELECT * FROM generate_series(1, 3) 7 ---- 8 project-set 9 ├── columns: generate_series:1 10 ├── values 11 │ └── () 12 └── zip 13 └── generate_series(1, 3) 14 15 build 16 SELECT * FROM generate_series(1, 2), generate_series(1, 2) 17 ---- 18 inner-join-apply 19 ├── columns: generate_series:1 generate_series:2 20 ├── project-set 21 │ ├── columns: generate_series:1 22 │ ├── values 23 │ │ └── () 24 │ └── zip 25 │ └── generate_series(1, 2) 26 ├── project-set 27 │ ├── columns: generate_series:2 28 │ ├── values 29 │ │ └── () 30 │ └── zip 31 │ └── generate_series(1, 2) 32 └── filters (true) 33 34 build 35 SELECT * FROM pg_catalog.generate_series(1, 3) 36 ---- 37 project-set 38 ├── columns: generate_series:1 39 ├── values 40 │ └── () 41 └── zip 42 └── generate_series(1, 3) 43 44 build 45 SELECT * FROM generate_series(1, 1) AS c(x) 46 ---- 47 project-set 48 ├── columns: x:1 49 ├── values 50 │ └── () 51 └── zip 52 └── generate_series(1, 1) 53 54 build 55 SELECT * FROM generate_series(1, 1) WITH ORDINALITY AS c(x, y) 56 ---- 57 ordinality 58 ├── columns: x:1 y:2!null 59 └── project-set 60 ├── columns: generate_series:1 61 ├── values 62 │ └── () 63 └── zip 64 └── generate_series(1, 1) 65 66 build 67 SELECT * FROM (VALUES (1)) LIMIT generate_series(1, 3) 68 ---- 69 error (0A000): generate_series(): generator functions are not allowed in LIMIT 70 71 # multiple_SRFs 72 73 build 74 SELECT generate_series(1, 2), generate_series(3, 4) 75 ---- 76 project-set 77 ├── columns: generate_series:1 generate_series:2 78 ├── values 79 │ └── () 80 └── zip 81 ├── generate_series(1, 2) 82 └── generate_series(3, 4) 83 84 exec-ddl 85 CREATE TABLE t (a string) 86 ---- 87 88 exec-ddl 89 CREATE TABLE u (b string) 90 ---- 91 92 build 93 SELECT t.*, u.*, a.*, b.* FROM t, u, generate_series(1, 2) AS a, generate_series(3, 4) AS b 94 ---- 95 project 96 ├── columns: a:1 b:3 a:5 b:6 97 └── inner-join-apply 98 ├── columns: a:1 t.rowid:2!null b:3 u.rowid:4!null generate_series:5 generate_series:6 99 ├── inner-join-apply 100 │ ├── columns: a:1 t.rowid:2!null b:3 u.rowid:4!null generate_series:5 101 │ ├── inner-join-apply 102 │ │ ├── columns: a:1 t.rowid:2!null b:3 u.rowid:4!null 103 │ │ ├── scan t 104 │ │ │ └── columns: a:1 t.rowid:2!null 105 │ │ ├── scan u 106 │ │ │ └── columns: b:3 u.rowid:4!null 107 │ │ └── filters (true) 108 │ ├── project-set 109 │ │ ├── columns: generate_series:5 110 │ │ ├── values 111 │ │ │ └── () 112 │ │ └── zip 113 │ │ └── generate_series(1, 2) 114 │ └── filters (true) 115 ├── project-set 116 │ ├── columns: generate_series:6 117 │ ├── values 118 │ │ └── () 119 │ └── zip 120 │ └── generate_series(3, 4) 121 └── filters (true) 122 123 build 124 SELECT 3 + x FROM generate_series(1,2) AS a(x) 125 ---- 126 project 127 ├── columns: "?column?":2 128 ├── project-set 129 │ ├── columns: generate_series:1 130 │ ├── values 131 │ │ └── () 132 │ └── zip 133 │ └── generate_series(1, 2) 134 └── projections 135 └── 3 + generate_series:1 [as="?column?":2] 136 137 build 138 SELECT 3 + (3 * generate_series(1,3)) 139 ---- 140 project 141 ├── columns: "?column?":2 142 ├── project-set 143 │ ├── columns: generate_series:1 144 │ ├── values 145 │ │ └── () 146 │ └── zip 147 │ └── generate_series(1, 3) 148 └── projections 149 └── 3 + (3 * generate_series:1) [as="?column?":2] 150 151 # unnest 152 153 build 154 SELECT * from unnest(ARRAY[1,2]) 155 ---- 156 project-set 157 ├── columns: unnest:1 158 ├── values 159 │ └── () 160 └── zip 161 └── unnest(ARRAY[1,2]) 162 163 build 164 SELECT unnest(ARRAY[1,2]), unnest(ARRAY['a', 'b']) 165 ---- 166 project-set 167 ├── columns: unnest:1 unnest:2 168 ├── values 169 │ └── () 170 └── zip 171 ├── unnest(ARRAY[1,2]) 172 └── unnest(ARRAY['a','b']) 173 174 build 175 SELECT unnest(ARRAY[3,4]) - 2 176 ---- 177 project 178 ├── columns: "?column?":2 179 ├── project-set 180 │ ├── columns: unnest:1 181 │ ├── values 182 │ │ └── () 183 │ └── zip 184 │ └── unnest(ARRAY[3,4]) 185 └── projections 186 └── unnest:1 - 2 [as="?column?":2] 187 188 build 189 SELECT 1 + generate_series(0, 1), unnest(ARRAY[2, 4]) - 1 190 ---- 191 project 192 ├── columns: "?column?":3 "?column?":4 193 ├── project-set 194 │ ├── columns: generate_series:1 unnest:2 195 │ ├── values 196 │ │ └── () 197 │ └── zip 198 │ ├── generate_series(0, 1) 199 │ └── unnest(ARRAY[2,4]) 200 └── projections 201 ├── 1 + generate_series:1 [as="?column?":3] 202 └── unnest:2 - 1 [as="?column?":4] 203 204 build 205 SELECT ascii(unnest(ARRAY['a', 'b', 'c'])); 206 ---- 207 project 208 ├── columns: ascii:2 209 ├── project-set 210 │ ├── columns: unnest:1 211 │ ├── values 212 │ │ └── () 213 │ └── zip 214 │ └── unnest(ARRAY['a','b','c']) 215 └── projections 216 └── ascii(unnest:1) [as=ascii:2] 217 218 # Regression test for #36501: don't rename the SRF column because of a 219 # higher-level table alias. 220 build 221 SELECT * FROM (SELECT unnest(ARRAY[1])) AS tablealias 222 ---- 223 project-set 224 ├── columns: unnest:1 225 ├── values 226 │ └── () 227 └── zip 228 └── unnest(ARRAY[1]) 229 230 build 231 SELECT * FROM (SELECT unnest(ARRAY[1]) AS colalias) AS tablealias 232 ---- 233 project-set 234 ├── columns: colalias:1 235 ├── values 236 │ └── () 237 └── zip 238 └── unnest(ARRAY[1]) 239 240 build 241 SELECT * FROM 242 (SELECT unnest(ARRAY[1]) AS filter_id2) AS uq 243 JOIN 244 (SELECT unnest(ARRAY[1]) AS filter_id) AS ab 245 ON uq.filter_id2 = ab.filter_id 246 ---- 247 inner-join (hash) 248 ├── columns: filter_id2:1!null filter_id:2!null 249 ├── project-set 250 │ ├── columns: unnest:1 251 │ ├── values 252 │ │ └── () 253 │ └── zip 254 │ └── unnest(ARRAY[1]) 255 ├── project-set 256 │ ├── columns: unnest:2 257 │ ├── values 258 │ │ └── () 259 │ └── zip 260 │ └── unnest(ARRAY[1]) 261 └── filters 262 └── unnest:1 = unnest:2 263 264 265 # nested_SRF 266 # See #20511 267 268 build 269 SELECT generate_series(generate_series(1, 3), 3) 270 ---- 271 error (0A000): generate_series(): unimplemented: nested set-returning functions 272 273 build 274 SELECT generate_series(1, 3) + generate_series(1, 3) 275 ---- 276 project 277 ├── columns: "?column?":3 278 ├── project-set 279 │ ├── columns: generate_series:1 generate_series:2 280 │ ├── values 281 │ │ └── () 282 │ └── zip 283 │ ├── generate_series(1, 3) 284 │ └── generate_series(1, 3) 285 └── projections 286 └── generate_series:1 + generate_series:2 [as="?column?":3] 287 288 build 289 SELECT generate_series(1, 3) FROM t WHERE generate_series > 3 290 ---- 291 error (42703): column "generate_series" does not exist 292 293 # Regressions for #15900: ensure that null parameters to generate_series don't 294 # cause issues. 295 296 build 297 SELECT * from generate_series(1, (select * from generate_series(1, 0))) 298 ---- 299 project-set 300 ├── columns: generate_series:2 301 ├── values 302 │ └── () 303 └── zip 304 └── function: generate_series 305 ├── 1 306 └── subquery 307 └── max1-row 308 ├── columns: generate_series:1 309 └── project-set 310 ├── columns: generate_series:1 311 ├── values 312 │ └── () 313 └── zip 314 └── generate_series(1, 0) 315 316 # The following query is designed to produce a null array argument to unnest 317 # in a way that the type system can't detect before evaluation. 318 build 319 SELECT unnest((SELECT current_schemas((SELECT isnan((SELECT round(3.4, (SELECT generate_series(1, 0))))))))); 320 ---- 321 project-set 322 ├── columns: unnest:5 323 ├── values 324 │ └── () 325 └── zip 326 └── function: unnest 327 └── subquery 328 └── max1-row 329 ├── columns: current_schemas:4 330 └── project 331 ├── columns: current_schemas:4 332 ├── values 333 │ └── () 334 └── projections 335 └── function: current_schemas [as=current_schemas:4] 336 └── subquery 337 └── max1-row 338 ├── columns: isnan:3 339 └── project 340 ├── columns: isnan:3 341 ├── values 342 │ └── () 343 └── projections 344 └── function: isnan [as=isnan:3] 345 └── subquery 346 └── max1-row 347 ├── columns: round:2 348 └── project 349 ├── columns: round:2 350 ├── values 351 │ └── () 352 └── projections 353 └── function: round [as=round:2] 354 ├── 3.4 355 └── subquery 356 └── max1-row 357 ├── columns: generate_series:1 358 └── project-set 359 ├── columns: generate_series:1 360 ├── values 361 │ └── () 362 └── zip 363 └── generate_series(1, 0) 364 365 # pg_get_keywords 366 367 # pg_get_keywords for compatibility (#10291) 368 build 369 SELECT * FROM pg_get_keywords() WHERE word IN ('alter', 'and', 'between', 'cross') ORDER BY word 370 ---- 371 sort 372 ├── columns: word:1!null catcode:2 catdesc:3 373 ├── ordering: +1 374 └── select 375 ├── columns: word:1!null catcode:2 catdesc:3 376 ├── project-set 377 │ ├── columns: word:1 catcode:2 catdesc:3 378 │ ├── values 379 │ │ └── () 380 │ └── zip 381 │ └── pg_get_keywords() 382 └── filters 383 └── word:1 IN ('alter', 'and', 'between', 'cross') 384 385 # Postgres enables renaming both the source and the column name for 386 # single-column generators, but not for multi-column generators. 387 build 388 SELECT a.*, b.*, c.* FROM generate_series(1,1) a, unnest(ARRAY[1]) b, pg_get_keywords() c LIMIT 0 389 ---- 390 limit 391 ├── columns: a:1 b:2 word:3 catcode:4 catdesc:5 392 ├── inner-join-apply 393 │ ├── columns: generate_series:1 unnest:2 word:3 catcode:4 catdesc:5 394 │ ├── limit hint: 1.00 395 │ ├── inner-join-apply 396 │ │ ├── columns: generate_series:1 unnest:2 397 │ │ ├── project-set 398 │ │ │ ├── columns: generate_series:1 399 │ │ │ ├── values 400 │ │ │ │ └── () 401 │ │ │ └── zip 402 │ │ │ └── generate_series(1, 1) 403 │ │ ├── project-set 404 │ │ │ ├── columns: unnest:2 405 │ │ │ ├── values 406 │ │ │ │ └── () 407 │ │ │ └── zip 408 │ │ │ └── unnest(ARRAY[1]) 409 │ │ └── filters (true) 410 │ ├── project-set 411 │ │ ├── columns: word:3 catcode:4 catdesc:5 412 │ │ ├── values 413 │ │ │ └── () 414 │ │ └── zip 415 │ │ └── pg_get_keywords() 416 │ └── filters (true) 417 └── 0 418 419 # Beware of multi-valued SRFs in render position (#19149) 420 build 421 SELECT 'a', pg_get_keywords(), 'c' LIMIT 1 422 ---- 423 limit 424 ├── columns: "?column?":4!null pg_get_keywords:5 "?column?":6!null 425 ├── project 426 │ ├── columns: "?column?":4!null pg_get_keywords:5 "?column?":6!null 427 │ ├── limit hint: 1.00 428 │ ├── project-set 429 │ │ ├── columns: word:1 catcode:2 catdesc:3 430 │ │ ├── limit hint: 1.00 431 │ │ ├── values 432 │ │ │ ├── limit hint: 1.00 433 │ │ │ └── () 434 │ │ └── zip 435 │ │ └── pg_get_keywords() 436 │ └── projections 437 │ ├── 'a' [as="?column?":4] 438 │ ├── ((word:1, catcode:2, catdesc:3) AS word, catcode, catdesc) [as=pg_get_keywords:5] 439 │ └── 'c' [as="?column?":6] 440 └── 1 441 442 build 443 SELECT 'a', pg_get_keywords() b, 'c' LIMIT 1 444 ---- 445 limit 446 ├── columns: "?column?":4!null b:5 "?column?":6!null 447 ├── project 448 │ ├── columns: "?column?":4!null b:5 "?column?":6!null 449 │ ├── limit hint: 1.00 450 │ ├── project-set 451 │ │ ├── columns: word:1 catcode:2 catdesc:3 452 │ │ ├── limit hint: 1.00 453 │ │ ├── values 454 │ │ │ ├── limit hint: 1.00 455 │ │ │ └── () 456 │ │ └── zip 457 │ │ └── pg_get_keywords() 458 │ └── projections 459 │ ├── 'a' [as="?column?":4] 460 │ ├── ((word:1, catcode:2, catdesc:3) AS word, catcode, catdesc) [as=b:5] 461 │ └── 'c' [as="?column?":6] 462 └── 1 463 464 # unary_table 465 466 build 467 SELECT 'a', crdb_internal.unary_table() b, 'c' LIMIT 1 468 ---- 469 limit 470 ├── columns: "?column?":1!null b:2!null "?column?":3!null 471 ├── project 472 │ ├── columns: "?column?":1!null b:2!null "?column?":3!null 473 │ ├── limit hint: 1.00 474 │ ├── project-set 475 │ │ ├── limit hint: 1.00 476 │ │ ├── values 477 │ │ │ ├── limit hint: 1.00 478 │ │ │ └── () 479 │ │ └── zip 480 │ │ └── crdb_internal.unary_table() 481 │ └── projections 482 │ ├── 'a' [as="?column?":1] 483 │ ├── () [as=b:2] 484 │ └── 'c' [as="?column?":3] 485 └── 1 486 487 # upper 488 489 # Regular scalar functions can be used as functions too. #22312 490 build 491 SELECT * FROM upper('abc') 492 ---- 493 project-set 494 ├── columns: upper:1 495 ├── values 496 │ └── () 497 └── zip 498 └── upper('abc') 499 500 # current_schema 501 502 build 503 SELECT * FROM current_schema() WITH ORDINALITY AS a(b) 504 ---- 505 ordinality 506 ├── columns: b:1 ordinality:2!null 507 └── project-set 508 ├── columns: current_schema:1 509 ├── values 510 │ └── () 511 └── zip 512 └── current_schema() 513 514 # expandArray 515 516 build 517 SELECT information_schema._pg_expandarray(ARRAY['b', 'a']) 518 ---- 519 project 520 ├── columns: information_schema._pg_expandarray:3 521 ├── project-set 522 │ ├── columns: x:1 n:2 523 │ ├── values 524 │ │ └── () 525 │ └── zip 526 │ └── information_schema._pg_expandarray(ARRAY['b','a']) 527 └── projections 528 └── ((x:1, n:2) AS x, n) [as=information_schema._pg_expandarray:3] 529 530 build 531 SELECT * FROM information_schema._pg_expandarray(ARRAY['b', 'a']) 532 ---- 533 project-set 534 ├── columns: x:1 n:2 535 ├── values 536 │ └── () 537 └── zip 538 └── information_schema._pg_expandarray(ARRAY['b','a']) 539 540 # srf_accessor 541 542 build 543 SELECT (1).* 544 ---- 545 error (42809): type int is not composite 546 547 build 548 SELECT ('a').* 549 ---- 550 error (42809): type string is not composite 551 552 build 553 SELECT (unnest(ARRAY[]:::INT[])).* 554 ---- 555 error (42809): type int is not composite 556 557 build 558 SELECT (information_schema._pg_expandarray(ARRAY['c', 'b', 'a'])).* 559 ---- 560 project 561 ├── columns: x:3 n:4 562 ├── project-set 563 │ ├── columns: x:1 n:2 564 │ ├── values 565 │ │ └── () 566 │ └── zip 567 │ └── information_schema._pg_expandarray(ARRAY['c','b','a']) 568 └── projections 569 ├── (((x:1, n:2) AS x, n)).x [as=x:3] 570 └── (((x:1, n:2) AS x, n)).n [as=n:4] 571 572 build 573 SELECT (information_schema._pg_expandarray(ARRAY['c', 'b', 'a'])).x 574 ---- 575 project 576 ├── columns: x:3 577 ├── project-set 578 │ ├── columns: x:1 n:2 579 │ ├── values 580 │ │ └── () 581 │ └── zip 582 │ └── information_schema._pg_expandarray(ARRAY['c','b','a']) 583 └── projections 584 └── (((x:1, n:2) AS x, n)).x [as=x:3] 585 586 build 587 SELECT (information_schema._pg_expandarray(ARRAY['c', 'b', 'a'])).@2 588 ---- 589 project 590 ├── columns: "?column?":3 591 ├── project-set 592 │ ├── columns: x:1 n:2 593 │ ├── values 594 │ │ └── () 595 │ └── zip 596 │ └── information_schema._pg_expandarray(ARRAY['c','b','a']) 597 └── projections 598 └── (((x:1, n:2) AS x, n)).n [as="?column?":3] 599 600 build 601 SELECT (information_schema._pg_expandarray(ARRAY['c', 'b', 'a'])).other 602 ---- 603 error (42804): could not identify column "other" in tuple{string AS x, int AS n} 604 605 build 606 SELECT (information_schema._pg_expandarray(ARRAY['c', 'b', 'a'])).@4 607 ---- 608 error (42601): tuple column 4 does not exist 609 610 build 611 SELECT temp.n from information_schema._pg_expandarray(ARRAY['c','b','a']) AS temp; 612 ---- 613 project 614 ├── columns: n:2 615 └── project-set 616 ├── columns: x:1 n:2 617 ├── values 618 │ └── () 619 └── zip 620 └── information_schema._pg_expandarray(ARRAY['c','b','a']) 621 622 build 623 SELECT temp.* from information_schema._pg_expandarray(ARRAY['c','b','a']) AS temp; 624 ---- 625 project-set 626 ├── columns: x:1 n:2 627 ├── values 628 │ └── () 629 └── zip 630 └── information_schema._pg_expandarray(ARRAY['c','b','a']) 631 632 build 633 SELECT * from information_schema._pg_expandarray(ARRAY['c','b','a']) AS temp; 634 ---- 635 project-set 636 ├── columns: x:1 n:2 637 ├── values 638 │ └── () 639 └── zip 640 └── information_schema._pg_expandarray(ARRAY['c','b','a']) 641 642 # generate_subscripts 643 644 build 645 SELECT * FROM generate_subscripts(ARRAY[3,2,1]) 646 ---- 647 project-set 648 ├── columns: generate_subscripts:1 649 ├── values 650 │ └── () 651 └── zip 652 └── generate_subscripts(ARRAY[3,2,1]) 653 654 # Zip with multiple SRFs. 655 build 656 SELECT * FROM 657 ROWS FROM (generate_series(0, 1), generate_series(1, 3), pg_get_keywords(), unnest(ARRAY['a', 'b', 'c'])) 658 ---- 659 project-set 660 ├── columns: generate_series:1 generate_series:2 word:3 catcode:4 catdesc:5 unnest:6 661 ├── values 662 │ └── () 663 └── zip 664 ├── generate_series(0, 1) 665 ├── generate_series(1, 3) 666 ├── pg_get_keywords() 667 └── unnest(ARRAY['a','b','c']) 668 669 # Don't rename columns if the zip contains two functions. 670 build 671 SELECT a.*, b.*, c.* FROM upper('abc') a 672 JOIN ROWS FROM (upper('def'), generate_series(1, 3)) b ON true 673 JOIN generate_series(1, 4) c ON true 674 ---- 675 inner-join (cross) 676 ├── columns: a:1 upper:2 generate_series:3 c:4 677 ├── inner-join (cross) 678 │ ├── columns: upper:1 upper:2 generate_series:3 679 │ ├── project-set 680 │ │ ├── columns: upper:1 681 │ │ ├── values 682 │ │ │ └── () 683 │ │ └── zip 684 │ │ └── upper('abc') 685 │ ├── project-set 686 │ │ ├── columns: upper:2 generate_series:3 687 │ │ ├── values 688 │ │ │ └── () 689 │ │ └── zip 690 │ │ ├── upper('def') 691 │ │ └── generate_series(1, 3) 692 │ └── filters 693 │ └── true 694 ├── project-set 695 │ ├── columns: generate_series:4 696 │ ├── values 697 │ │ └── () 698 │ └── zip 699 │ └── generate_series(1, 4) 700 └── filters 701 └── true 702 703 build 704 SELECT * FROM ROWS FROM (generate_series(generate_series(1,2),3)) 705 ---- 706 error (0A000): generate_series(): generate_series(): set-returning functions must appear at the top level of FROM 707 708 # SRFs not allowed in HAVING, unless they are part of a subquery. 709 build 710 SELECT max(a) FROM t HAVING max(a::int) > generate_series(0, a::int) 711 ---- 712 error (0A000): generate_series(): generator functions are not allowed in HAVING 713 714 build 715 SELECT max(a) FROM t HAVING max(a::int) > (SELECT generate_series(0, b::int) FROM u limit 1) 716 ---- 717 project 718 ├── columns: max:3 719 └── select 720 ├── columns: max:3 max:5!null 721 ├── scalar-group-by 722 │ ├── columns: max:3 max:5 723 │ ├── project 724 │ │ ├── columns: column4:4 a:1 725 │ │ ├── scan t 726 │ │ │ └── columns: a:1 t.rowid:2!null 727 │ │ └── projections 728 │ │ └── a:1::INT8 [as=column4:4] 729 │ └── aggregations 730 │ ├── max [as=max:3] 731 │ │ └── a:1 732 │ └── max [as=max:5] 733 │ └── column4:4 734 └── filters 735 └── gt 736 ├── max:5 737 └── subquery 738 └── max1-row 739 ├── columns: generate_series:8 740 └── limit 741 ├── columns: generate_series:8 742 ├── project 743 │ ├── columns: generate_series:8 744 │ ├── limit hint: 1.00 745 │ └── project-set 746 │ ├── columns: b:6 u.rowid:7!null generate_series:8 747 │ ├── limit hint: 1.00 748 │ ├── scan u 749 │ │ ├── columns: b:6 u.rowid:7!null 750 │ │ └── limit hint: 1.00 751 │ └── zip 752 │ └── generate_series(0, b:6::INT8) 753 └── 1 754 755 build 756 SELECT generate_series((SELECT generate_subscripts(ARRAY[a, a||b]) FROM t, u), 100) FROM t 757 ---- 758 project 759 ├── columns: generate_series:8 760 └── project-set 761 ├── columns: a:1 t.rowid:2!null generate_series:8 762 ├── scan t 763 │ └── columns: a:1 t.rowid:2!null 764 └── zip 765 └── function: generate_series 766 ├── subquery 767 │ └── max1-row 768 │ ├── columns: generate_subscripts:7 769 │ └── project 770 │ ├── columns: generate_subscripts:7 771 │ └── project-set 772 │ ├── columns: a:3 t.rowid:4!null b:5 u.rowid:6!null generate_subscripts:7 773 │ ├── inner-join (cross) 774 │ │ ├── columns: a:3 t.rowid:4!null b:5 u.rowid:6!null 775 │ │ ├── scan t 776 │ │ │ └── columns: a:3 t.rowid:4!null 777 │ │ ├── scan u 778 │ │ │ └── columns: b:5 u.rowid:6!null 779 │ │ └── filters (true) 780 │ └── zip 781 │ └── generate_subscripts(ARRAY[a:3, a:3 || b:5]) 782 └── 100 783 784 exec-ddl 785 CREATE TABLE a (x INT PRIMARY KEY, j JSON, k JSON, m JSON, n JSON) 786 ---- 787 788 build 789 SELECT 790 json_array_elements(j), 791 (SELECT jsonb_each(k)), 792 (SELECT jsonb_object_keys(m) FROM a), 793 (SELECT generate_series((SELECT generate_series(x, 100) FROM jsonb_array_elements_text(n)), 1000)) 794 FROM a 795 ---- 796 project 797 ├── columns: json_array_elements:6 jsonb_each:19 jsonb_object_keys:20 generate_series:21 798 ├── project-set 799 │ ├── columns: x:1!null j:2 k:3 m:4 n:5 json_array_elements:6 800 │ ├── scan a 801 │ │ └── columns: x:1!null j:2 k:3 m:4 n:5 802 │ └── zip 803 │ └── json_array_elements(j:2) 804 └── projections 805 ├── subquery [as=jsonb_each:19] 806 │ └── max1-row 807 │ ├── columns: jsonb_each:9 808 │ └── project 809 │ ├── columns: jsonb_each:9 810 │ ├── project-set 811 │ │ ├── columns: key:7 value:8 812 │ │ ├── values 813 │ │ │ └── () 814 │ │ └── zip 815 │ │ └── jsonb_each(k:3) 816 │ └── projections 817 │ └── ((key:7, value:8) AS key, value) [as=jsonb_each:9] 818 ├── subquery [as=jsonb_object_keys:20] 819 │ └── max1-row 820 │ ├── columns: jsonb_object_keys:15 821 │ └── project 822 │ ├── columns: jsonb_object_keys:15 823 │ └── project-set 824 │ ├── columns: x:10!null j:11 k:12 m:13 n:14 jsonb_object_keys:15 825 │ ├── scan a 826 │ │ └── columns: x:10!null j:11 k:12 m:13 n:14 827 │ └── zip 828 │ └── jsonb_object_keys(m:13) 829 └── subquery [as=generate_series:21] 830 └── max1-row 831 ├── columns: generate_series:18 832 └── project-set 833 ├── columns: generate_series:18 834 ├── values 835 │ └── () 836 └── zip 837 └── function: generate_series 838 ├── subquery 839 │ └── max1-row 840 │ ├── columns: generate_series:17 841 │ └── project 842 │ ├── columns: generate_series:17 843 │ └── project-set 844 │ ├── columns: value:16 generate_series:17 845 │ ├── project-set 846 │ │ ├── columns: value:16 847 │ │ ├── values 848 │ │ │ └── () 849 │ │ └── zip 850 │ │ └── jsonb_array_elements_text(n:5) 851 │ └── zip 852 │ └── generate_series(x:1, 100) 853 └── 1000 854 855 # Regression test for #30412. 856 build 857 SELECT 0, unnest(ARRAY[0]) GROUP BY 1 858 ---- 859 error (42803): column "unnest" must appear in the GROUP BY clause or be used in an aggregate function 860 861 build 862 SELECT 0, unnest(ARRAY[0]) GROUP BY 1, 2 863 ---- 864 error (0A000): unnest(): generator functions are not allowed in GROUP BY 865 866 build 867 SELECT 0, information_schema._pg_expandarray(ARRAY[0]) GROUP BY 1 868 ---- 869 error (42803): column "x" must appear in the GROUP BY clause or be used in an aggregate function 870 871 # Regression test for #31755. 872 exec-ddl 873 CREATE TABLE tab31755 (a STRING) 874 ---- 875 876 build 877 SELECT * FROM ROWS FROM (CAST((SELECT a FROM tab31755 LIMIT 1) AS SERIAL2[])) AS ident 878 ---- 879 project-set 880 ├── columns: ident:3 881 ├── values 882 │ └── () 883 └── zip 884 └── cast: INT2[] 885 └── subquery 886 └── max1-row 887 ├── columns: tab31755.a:1 888 └── limit 889 ├── columns: tab31755.a:1 890 ├── project 891 │ ├── columns: tab31755.a:1 892 │ ├── limit hint: 1.00 893 │ └── scan tab31755 894 │ ├── columns: tab31755.a:1 rowid:2!null 895 │ └── limit hint: 1.00 896 └── 1