github.com/cockroachdb/cockroach@v20.2.0-alpha.1+incompatible/pkg/sql/logictest/testdata/logic_test/srfs (about) 1 subtest generate_series 2 3 query I 4 SELECT * FROM generate_series(1, NULL) 5 ---- 6 7 query I colnames 8 SELECT * FROM generate_series(1, 3) 9 ---- 10 generate_series 11 1 12 2 13 3 14 15 query T colnames 16 SELECT * FROM generate_series('2017-11-11 00:00:00'::TIMESTAMP, '2017-11-11 03:00:00'::TIMESTAMP, '1 hour') 17 ---- 18 generate_series 19 2017-11-11 00:00:00 +0000 +0000 20 2017-11-11 01:00:00 +0000 +0000 21 2017-11-11 02:00:00 +0000 +0000 22 2017-11-11 03:00:00 +0000 +0000 23 24 query T colnames 25 SELECT * FROM generate_series('2017-11-11 03:00:00'::TIMESTAMP, '2017-11-11 00:00:00'::TIMESTAMP, '-1 hour') 26 ---- 27 generate_series 28 2017-11-11 03:00:00 +0000 +0000 29 2017-11-11 02:00:00 +0000 +0000 30 2017-11-11 01:00:00 +0000 +0000 31 2017-11-11 00:00:00 +0000 +0000 32 33 query T colnames 34 SELECT * FROM generate_series('2017-11-11 03:00:00'::TIMESTAMP, '2017-11-15 00:00:00'::TIMESTAMP, '1 day') 35 ---- 36 generate_series 37 2017-11-11 03:00:00 +0000 +0000 38 2017-11-12 03:00:00 +0000 +0000 39 2017-11-13 03:00:00 +0000 +0000 40 2017-11-14 03:00:00 +0000 +0000 41 42 query T colnames 43 SELECT * FROM generate_series('2017-01-15 03:00:00'::TIMESTAMP, '2017-12-15 00:00:00'::TIMESTAMP, '1 month') 44 ---- 45 generate_series 46 2017-01-15 03:00:00 +0000 +0000 47 2017-02-15 03:00:00 +0000 +0000 48 2017-03-15 03:00:00 +0000 +0000 49 2017-04-15 03:00:00 +0000 +0000 50 2017-05-15 03:00:00 +0000 +0000 51 2017-06-15 03:00:00 +0000 +0000 52 2017-07-15 03:00:00 +0000 +0000 53 2017-08-15 03:00:00 +0000 +0000 54 2017-09-15 03:00:00 +0000 +0000 55 2017-10-15 03:00:00 +0000 +0000 56 2017-11-15 03:00:00 +0000 +0000 57 58 # Check what happens when we step through February in a leap year, starting on Jan 31. 59 # This output is consistent with PostgreSQL 10. 60 query T colnames 61 SELECT * FROM generate_series('2016-01-31 03:00:00'::TIMESTAMP, '2016-12-31 00:00:00'::TIMESTAMP, '1 month') 62 ---- 63 generate_series 64 2016-01-31 03:00:00 +0000 +0000 65 2016-02-29 03:00:00 +0000 +0000 66 2016-03-29 03:00:00 +0000 +0000 67 2016-04-29 03:00:00 +0000 +0000 68 2016-05-29 03:00:00 +0000 +0000 69 2016-06-29 03:00:00 +0000 +0000 70 2016-07-29 03:00:00 +0000 +0000 71 2016-08-29 03:00:00 +0000 +0000 72 2016-09-29 03:00:00 +0000 +0000 73 2016-10-29 03:00:00 +0000 +0000 74 2016-11-29 03:00:00 +0000 +0000 75 2016-12-29 03:00:00 +0000 +0000 76 77 # Similar to the previous, but we don't hit a 30-day month until July. 78 query T colnames 79 SELECT * FROM generate_series('2016-01-31 03:00:00'::TIMESTAMP, '2016-12-31 00:00:00'::TIMESTAMP, '2 month') 80 ---- 81 generate_series 82 2016-01-31 03:00:00 +0000 +0000 83 2016-03-31 03:00:00 +0000 +0000 84 2016-05-31 03:00:00 +0000 +0000 85 2016-07-31 03:00:00 +0000 +0000 86 2016-09-30 03:00:00 +0000 +0000 87 2016-11-30 03:00:00 +0000 +0000 88 89 # Verify rollover when we're adding by months, days, and hours 90 query T colnames 91 SELECT * FROM generate_series('2016-01-30 22:00:00'::TIMESTAMP, '2016-12-31 00:00:00'::TIMESTAMP, '1 month 1 day 1 hour') 92 ---- 93 generate_series 94 2016-01-30 22:00:00 +0000 +0000 95 2016-03-01 23:00:00 +0000 +0000 96 2016-04-03 00:00:00 +0000 +0000 97 2016-05-04 01:00:00 +0000 +0000 98 2016-06-05 02:00:00 +0000 +0000 99 2016-07-06 03:00:00 +0000 +0000 100 2016-08-07 04:00:00 +0000 +0000 101 2016-09-08 05:00:00 +0000 +0000 102 2016-10-09 06:00:00 +0000 +0000 103 2016-11-10 07:00:00 +0000 +0000 104 2016-12-11 08:00:00 +0000 +0000 105 106 query T colnames 107 SELECT * FROM generate_series('1996-02-29 22:00:00'::TIMESTAMP, '2004-03-01 00:00:00'::TIMESTAMP, '4 year') 108 ---- 109 generate_series 110 1996-02-29 22:00:00 +0000 +0000 111 2000-02-29 22:00:00 +0000 +0000 112 2004-02-29 22:00:00 +0000 +0000 113 114 query T colnames 115 SELECT * FROM generate_series('2017-11-11 00:00:00'::TIMESTAMP, '2017-11-11 03:00:00'::TIMESTAMP, '-1 hour') 116 ---- 117 generate_series 118 119 query II colnames,rowsort 120 SELECT * FROM generate_series(1, 2), generate_series(1, 2) 121 ---- 122 generate_series generate_series 123 1 1 124 1 2 125 2 1 126 2 2 127 128 query I colnames 129 SELECT * FROM generate_series(3, 1, -1) 130 ---- 131 generate_series 132 3 133 2 134 1 135 136 query I colnames 137 SELECT * FROM generate_series(3, 1) 138 ---- 139 generate_series 140 141 query error step cannot be 0 142 SELECT * FROM generate_series(1, 3, 0) 143 144 query I colnames 145 SELECT * FROM PG_CATALOG.generate_series(1, 3) 146 ---- 147 generate_series 148 1 149 2 150 3 151 152 query I colnames 153 SELECT * FROM generate_series(1, 1) AS c(x) 154 ---- 155 x 156 1 157 158 query II colnames 159 SELECT * FROM generate_series(1, 1) WITH ORDINALITY 160 ---- 161 generate_series ordinality 162 1 1 163 164 query II colnames 165 SELECT * FROM generate_series(1, 1) WITH ORDINALITY AS c(x, y) 166 ---- 167 x y 168 1 1 169 170 query error generator functions are not allowed in LIMIT 171 SELECT * FROM (VALUES (1)) LIMIT generate_series(1, 3) 172 173 query I colnames 174 SELECT generate_series(1, 2) 175 ---- 176 generate_series 177 1 178 2 179 180 subtest multiple_SRFs 181 182 query II colnames 183 SELECT generate_series(1, 2), generate_series(3, 4) 184 ---- 185 generate_series generate_series 186 1 3 187 2 4 188 189 query II 190 SELECT generate_series(1, 2), generate_series(3, 4) 191 ---- 192 1 3 193 2 4 194 195 statement ok 196 CREATE TABLE t (a string) 197 198 statement ok 199 CREATE TABLE u (b string) 200 201 statement ok 202 INSERT INTO t VALUES ('cat') 203 204 statement ok 205 INSERT INTO u VALUES ('bird') 206 207 query TTII colnames,rowsort 208 SELECT t.*, u.*, generate_series(1,2), generate_series(3, 4) FROM t, u 209 ---- 210 a b generate_series generate_series 211 cat bird 1 3 212 cat bird 2 4 213 214 query TTII colnames,rowsort 215 SELECT t.*, u.*, a.*, b.* FROM t, u, generate_series(1, 2) AS a, generate_series(3, 4) AS b 216 ---- 217 a b a b 218 cat bird 1 3 219 cat bird 1 4 220 cat bird 2 3 221 cat bird 2 4 222 223 query I colnames 224 SELECT 3 + x AS r FROM generate_series(1,2) AS a(x) 225 ---- 226 r 227 4 228 5 229 230 query I colnames 231 SELECT 3 + generate_series(1,2) AS r 232 ---- 233 r 234 4 235 5 236 237 query I colnames 238 SELECT 3 + (3 * generate_series(1,3)) AS r 239 ---- 240 r 241 6 242 9 243 12 244 245 subtest srf_ordering 246 247 statement ok 248 CREATE TABLE ordered_t(x INT PRIMARY KEY); 249 INSERT INTO ordered_t VALUES (0), (1) 250 251 query II colnames 252 SELECT x, generate_series(3, x, -1) FROM ordered_t ORDER BY 1, 2; 253 ---- 254 x generate_series 255 0 0 256 0 1 257 0 2 258 0 3 259 1 1 260 1 2 261 1 3 262 263 subtest unnest 264 265 statement error could not determine polymorphic type 266 SELECT * FROM unnest(NULL) 267 268 statement error could not determine polymorphic type 269 SELECT unnest(NULL) 270 271 query I colnames 272 SELECT * from unnest(ARRAY[1,2]) 273 ---- 274 unnest 275 1 276 2 277 278 query IT 279 SELECT unnest(ARRAY[1,2]), unnest(ARRAY['a', 'b']) 280 ---- 281 1 a 282 2 b 283 284 query I colnames 285 SELECT unnest(ARRAY[3,4]) - 2 AS r 286 ---- 287 r 288 1 289 2 290 291 query II colnames 292 SELECT 1 + generate_series(0, 1) AS r, unnest(ARRAY[2, 4]) - 1 AS t 293 ---- 294 r t 295 1 1 296 2 3 297 298 query II 299 SELECT 1 + generate_series(0, 1), unnest(ARRAY[2, 4]) - 1 300 ---- 301 1 1 302 2 3 303 304 query I colnames 305 SELECT ascii(unnest(ARRAY['a', 'b', 'c'])); 306 ---- 307 ascii 308 97 309 98 310 99 311 312 subtest nested_SRF 313 # See #20511 314 315 query error unimplemented: nested set-returning functions 316 SELECT generate_series(generate_series(1, 3), 3) 317 318 query I 319 SELECT generate_series(1, 3) + generate_series(1, 3) 320 ---- 321 2 322 4 323 6 324 325 query error pq: column "generate_series" does not exist 326 SELECT generate_series(1, 3) FROM t WHERE generate_series > 3 327 328 # Regressions for #15900: ensure that null parameters to generate_series don't 329 # cause issues. 330 331 query T colnames 332 SELECT * from generate_series(1, (select * from generate_series(1, 0))) 333 ---- 334 generate_series 335 336 # The following query is designed to produce a null array argument to unnest 337 # in a way that the type system can't detect before evaluation. 338 query T colnames 339 SELECT unnest((SELECT current_schemas((SELECT isnan((SELECT round(3.4, (SELECT generate_series(1, 0))))))))); 340 ---- 341 unnest 342 343 query T colnames 344 SELECT information_schema._pg_expandarray((SELECT current_schemas((SELECT isnan((SELECT round(3.4, (SELECT generate_series(1, 0))))))))); 345 ---- 346 information_schema._pg_expandarray 347 348 # Regression for #18021. 349 query I colnames 350 SELECT generate_series(9223372036854775807::int, -9223372036854775807::int, -9223372036854775807::int) 351 ---- 352 generate_series 353 9223372036854775807 354 0 355 -9223372036854775807 356 357 subtest pg_get_keywords 358 359 # pg_get_keywords for compatibility (#10291) 360 query TTT colnames 361 SELECT * FROM pg_get_keywords() WHERE word IN ('alter', 'and', 'between', 'cross') ORDER BY word 362 ---- 363 word catcode catdesc 364 alter U unreserved 365 and R reserved 366 between C unreserved (cannot be function or type name) 367 cross T reserved (can be function or type name) 368 369 # Postgres enables renaming both the source and the column name for 370 # single-column generators, but not for multi-column generators. 371 query IITTT colnames 372 SELECT a.*, b.*, c.* FROM generate_series(1,1) a, unnest(ARRAY[1]) b, pg_get_keywords() c LIMIT 0 373 ---- 374 a b word catcode catdesc 375 376 # Regression for #36501: the column from a single-column SRF should not be 377 # renamed because of a higher-level table alias. 378 query I colnames 379 SELECT * FROM (SELECT * FROM generate_series(1, 2)) AS a 380 ---- 381 generate_series 382 1 383 2 384 385 query I colnames 386 SELECT * FROM (SELECT unnest(ARRAY[1])) AS tablealias 387 ---- 388 unnest 389 1 390 391 query I colnames 392 SELECT * FROM (SELECT unnest(ARRAY[1]) AS colalias) AS tablealias 393 ---- 394 colalias 395 1 396 397 query II 398 SELECT * FROM 399 (SELECT unnest(ARRAY[1]) AS filter_id2) AS uq 400 JOIN 401 (SELECT unnest(ARRAY[1]) AS filter_id) AS ab 402 ON uq.filter_id2 = ab.filter_id 403 ---- 404 1 1 405 406 # Beware of multi-valued SRFs in render position (#19149) 407 query TTT colnames 408 SELECT 'a' AS a, pg_get_keywords(), 'c' AS c LIMIT 1 409 ---- 410 a pg_get_keywords c 411 a (abort,U,unreserved) c 412 413 query TTT colnames 414 SELECT 'a' AS a, pg_get_keywords() AS b, 'c' AS c LIMIT 1 415 ---- 416 a b c 417 a (abort,U,unreserved) c 418 419 subtest unary_table 420 421 query TTT colnames 422 SELECT 'a' AS a, crdb_internal.unary_table() AS b, 'c' AS c LIMIT 1 423 ---- 424 a b c 425 a () c 426 427 subtest upper 428 429 # Regular scalar functions can be used as functions too. #22312 430 query T colnames 431 SELECT * FROM upper('abc') 432 ---- 433 upper 434 ABC 435 436 subtest current_schema 437 438 query TI colnames 439 SELECT * FROM current_schema() WITH ORDINALITY AS a(b) 440 ---- 441 b ordinality 442 public 1 443 444 subtest expandArray 445 446 query error pq: unknown signature: information_schema._pg_expandarray() 447 SELECT information_schema._pg_expandarray() 448 449 query error pq: unknown signature: information_schema._pg_expandarray() 450 SELECT * FROM information_schema._pg_expandarray() 451 452 query error pq: information_schema\._pg_expandarray\(\): cannot determine type of empty array\. Consider annotating with the desired type, for example ARRAY\[\]:::int\[\] 453 SELECT information_schema._pg_expandarray(ARRAY[]) 454 455 query error pq: information_schema\._pg_expandarray\(\): cannot determine type of empty array\. Consider annotating with the desired type, for example ARRAY\[\]:::int\[\] 456 SELECT * FROM information_schema._pg_expandarray(ARRAY[]) 457 458 statement error could not determine polymorphic type 459 SELECT * FROM information_schema._pg_expandarray(NULL) 460 461 statement error could not determine polymorphic type 462 SELECT information_schema._pg_expandarray(NULL) 463 464 query I colnames 465 SELECT information_schema._pg_expandarray(ARRAY[]:::int[]) 466 ---- 467 information_schema._pg_expandarray 468 469 query II colnames 470 SELECT * FROM information_schema._pg_expandarray(ARRAY[]:::int[]) 471 ---- 472 x n 473 474 query T colnames 475 SELECT information_schema._pg_expandarray(ARRAY[100]) 476 ---- 477 information_schema._pg_expandarray 478 (100,1) 479 480 query II colnames 481 SELECT * FROM information_schema._pg_expandarray(ARRAY[100]) 482 ---- 483 x n 484 100 1 485 486 query T colnames 487 SELECT information_schema._pg_expandarray(ARRAY[2, 1]) 488 ---- 489 information_schema._pg_expandarray 490 (2,1) 491 (1,2) 492 493 query II colnames 494 SELECT * FROM information_schema._pg_expandarray(ARRAY[2, 1]) 495 ---- 496 x n 497 2 1 498 1 2 499 500 query T colnames 501 SELECT information_schema._pg_expandarray(ARRAY[3, 2, 1]) 502 ---- 503 information_schema._pg_expandarray 504 (3,1) 505 (2,2) 506 (1,3) 507 508 query II colnames 509 SELECT * FROM information_schema._pg_expandarray(ARRAY[3, 2, 1]) 510 ---- 511 x n 512 3 1 513 2 2 514 1 3 515 516 query T colnames 517 SELECT information_schema._pg_expandarray(ARRAY['a']) 518 ---- 519 information_schema._pg_expandarray 520 (a,1) 521 522 query TI colnames 523 SELECT * FROM information_schema._pg_expandarray(ARRAY['a']) 524 ---- 525 x n 526 a 1 527 528 query T colnames 529 SELECT information_schema._pg_expandarray(ARRAY['b', 'a']) 530 ---- 531 information_schema._pg_expandarray 532 (b,1) 533 (a,2) 534 535 query TI colnames 536 SELECT * FROM information_schema._pg_expandarray(ARRAY['b', 'a']) 537 ---- 538 x n 539 b 1 540 a 2 541 542 query T colnames 543 SELECT information_schema._pg_expandarray(ARRAY['c', 'b', 'a']) 544 ---- 545 information_schema._pg_expandarray 546 (c,1) 547 (b,2) 548 (a,3) 549 550 query TI colnames 551 SELECT * FROM information_schema._pg_expandarray(ARRAY['c', 'b', 'a']) 552 ---- 553 x n 554 c 1 555 b 2 556 a 3 557 558 subtest srf_accessor 559 560 query error pq: type int is not composite 561 SELECT (1).* 562 563 query error pq: type int is not composite 564 SELECT ((1)).* 565 566 query error pq: type int is not composite 567 SELECT (1).x 568 569 query error pq: type int is not composite 570 SELECT ((1)).x 571 572 query error pq: type string is not composite 573 SELECT ('a').* 574 575 query error pq: type string is not composite 576 SELECT (('a')).* 577 578 query error pq: type string is not composite 579 SELECT ('a').x 580 581 query error pq: type string is not composite 582 SELECT (('a')).x 583 584 query error pq: unnest\(\): cannot determine type of empty array. Consider annotating with the desired type, for example ARRAY\[\]:::int\[\] 585 SELECT (unnest(ARRAY[])).* 586 587 query error type int is not composite 588 SELECT (unnest(ARRAY[]:::INT[])).* 589 590 subtest multi_column 591 592 query TI colnames 593 SELECT (information_schema._pg_expandarray(ARRAY['c', 'b', 'a'])).* 594 ---- 595 x n 596 c 1 597 b 2 598 a 3 599 600 query T colnames 601 SELECT (information_schema._pg_expandarray(ARRAY['c', 'b', 'a'])).x 602 ---- 603 x 604 c 605 b 606 a 607 608 query I colnames 609 SELECT (information_schema._pg_expandarray(ARRAY['c', 'b', 'a'])).n 610 ---- 611 n 612 1 613 2 614 3 615 616 query error pq: could not identify column "other" in tuple{string AS x, int AS n} 617 SELECT (information_schema._pg_expandarray(ARRAY['c', 'b', 'a'])).other 618 619 query T colnames 620 SELECT temp.x from information_schema._pg_expandarray(array['c','b','a']) AS temp; 621 ---- 622 x 623 c 624 b 625 a 626 627 query I colnames 628 SELECT temp.n from information_schema._pg_expandarray(array['c','b','a']) AS temp; 629 ---- 630 n 631 1 632 2 633 3 634 635 query error pq: column "temp.other" does not exist 636 SELECT temp.other from information_schema._pg_expandarray(array['c','b','a']) AS temp; 637 638 query TI colnames 639 SELECT temp.* from information_schema._pg_expandarray(array['c','b','a']) AS temp; 640 ---- 641 x n 642 c 1 643 b 2 644 a 3 645 646 query TI colnames 647 SELECT * from information_schema._pg_expandarray(array['c','b','a']) AS temp; 648 ---- 649 x n 650 c 1 651 b 2 652 a 3 653 654 query I colnames 655 SELECT (i.keys).n FROM (SELECT information_schema._pg_expandarray(ARRAY[3,2,1]) AS keys) AS i 656 ---- 657 n 658 1 659 2 660 3 661 662 query II colnames 663 SELECT (i.keys).* FROM (SELECT information_schema._pg_expandarray(ARRAY[3,2,1]) AS keys) AS i 664 ---- 665 x n 666 3 1 667 2 2 668 1 3 669 670 query T 671 SELECT ((i.keys).*, 123) FROM (SELECT information_schema._pg_expandarray(ARRAY[3,2,1]) AS keys) AS i 672 ---- 673 ("(3,1)",123) 674 ("(2,2)",123) 675 ("(1,3)",123) 676 677 subtest generate_subscripts 678 679 # Basic use cases 680 681 query I colnames 682 SELECT * FROM generate_subscripts(ARRAY[3,2,1]) 683 ---- 684 generate_subscripts 685 1 686 2 687 3 688 689 query I colnames 690 SELECT * FROM generate_subscripts(ARRAY[3,2,1], 1) 691 ---- 692 generate_subscripts 693 1 694 2 695 3 696 697 query I colnames 698 SELECT * FROM generate_subscripts(ARRAY[3,2,1], 1, false) 699 ---- 700 generate_subscripts 701 1 702 2 703 3 704 705 query I colnames 706 SELECT * FROM generate_subscripts(ARRAY[3,2,1], 1, true) 707 ---- 708 generate_subscripts 709 3 710 2 711 1 712 713 query I colnames 714 SELECT generate_subscripts('{NULL,1,NULL,2}'::int[], 1) AS s 715 ---- 716 s 717 1 718 2 719 3 720 4 721 722 query I colnames 723 SELECT generate_subscripts('{NULL,1,NULL,2}'::int[], 1, true) AS s 724 ---- 725 s 726 4 727 3 728 2 729 1 730 731 # With a non-valid dimension (only 1 should return any rows) 732 733 query I colnames 734 SELECT * FROM generate_subscripts(ARRAY[3,2,1], 2) 735 ---- 736 generate_subscripts 737 738 query I colnames 739 SELECT * FROM generate_subscripts(ARRAY[3,2,1], 2, false) 740 ---- 741 generate_subscripts 742 743 query I colnames 744 SELECT * FROM generate_subscripts(ARRAY[3,2,1], 2, true) 745 ---- 746 generate_subscripts 747 748 query I colnames 749 SELECT * FROM generate_subscripts(ARRAY[3,2,1], 0) 750 ---- 751 generate_subscripts 752 753 query I colnames 754 SELECT * FROM generate_subscripts(ARRAY[3,2,1], 0, false) 755 ---- 756 generate_subscripts 757 758 query I colnames 759 SELECT * FROM generate_subscripts(ARRAY[3,2,1], 0, true) 760 ---- 761 generate_subscripts 762 763 query I colnames 764 SELECT * FROM generate_subscripts(ARRAY[3,2,1], -1) 765 ---- 766 generate_subscripts 767 768 query I colnames 769 SELECT * FROM generate_subscripts(ARRAY[3,2,1], -1, false) 770 ---- 771 generate_subscripts 772 773 query I colnames 774 SELECT * FROM generate_subscripts(ARRAY[3,2,1], -1, true) 775 ---- 776 generate_subscripts 777 778 # With an empty array 779 query I colnames 780 SELECT * FROM generate_subscripts(ARRAY[]:::int[]) 781 ---- 782 generate_subscripts 783 784 query I colnames 785 SELECT * FROM generate_subscripts(ARRAY[]:::int[], 1) 786 ---- 787 generate_subscripts 788 789 query I colnames 790 SELECT * FROM generate_subscripts(ARRAY[]:::string[], 1, false) 791 ---- 792 generate_subscripts 793 794 query I colnames 795 SELECT * FROM generate_subscripts(ARRAY[]:::bool[], 1, true) 796 ---- 797 generate_subscripts 798 799 query I colnames 800 SELECT * FROM generate_subscripts(ARRAY[]:::int[], 0) 801 ---- 802 generate_subscripts 803 804 query I colnames 805 SELECT * FROM generate_subscripts(ARRAY[]:::string[], -1, false) 806 ---- 807 generate_subscripts 808 809 query I colnames 810 SELECT * FROM generate_subscripts(ARRAY[]:::bool[], 2, true) 811 ---- 812 generate_subscripts 813 814 # With an array with only one value 815 query I colnames 816 SELECT * FROM generate_subscripts(ARRAY[100]) 817 ---- 818 generate_subscripts 819 1 820 821 query I colnames 822 SELECT * FROM generate_subscripts(ARRAY[100], 1) 823 ---- 824 generate_subscripts 825 1 826 827 query I colnames 828 SELECT * FROM generate_subscripts(ARRAY['b'], 1, false) 829 ---- 830 generate_subscripts 831 1 832 833 query I colnames 834 SELECT * FROM generate_subscripts(ARRAY[true], 1, true) 835 ---- 836 generate_subscripts 837 1 838 839 subtest srf_errors 840 841 query error generator functions are not allowed in ORDER BY 842 SELECT * FROM t ORDER BY generate_series(1, 3) 843 844 query error generator functions are not allowed in WHERE 845 SELECT * FROM t WHERE generate_series(1, 3) < 3 846 847 query error generator functions are not allowed in HAVING 848 SELECT * FROM t HAVING generate_series(1, 3) < 3 849 850 query error generator functions are not allowed in LIMIT 851 SELECT * FROM t LIMIT generate_series(1, 3) 852 853 query error generator functions are not allowed in OFFSET 854 SELECT * FROM t OFFSET generate_series(1, 3) 855 856 query error generator functions are not allowed in VALUES 857 VALUES (generate_series(1,3)) 858 859 statement error generator functions are not allowed in DEFAULT 860 CREATE TABLE uu (x INT DEFAULT generate_series(1, 3)) 861 862 statement error generator functions are not allowed in CHECK 863 CREATE TABLE uu (x INT CHECK (generate_series(1, 3) < 3)) 864 865 statement error generator functions are not allowed in computed column 866 CREATE TABLE uu (x INT AS (generate_series(1, 3)) STORED) 867 868 subtest correlated_srf 869 870 statement ok 871 CREATE TABLE vals (x INT, y INT, INDEX woo (x, y)); 872 INSERT INTO vals VALUES (3, 4), (NULL, NULL), (5, 6); 873 874 query III colnames 875 SELECT x, generate_series(1,x), generate_series(1,2) FROM vals ORDER BY 1,2,3 876 ---- 877 x generate_series generate_series 878 NULL NULL 1 879 NULL NULL 2 880 3 1 1 881 3 2 2 882 3 3 NULL 883 5 1 1 884 5 2 2 885 5 3 NULL 886 5 4 NULL 887 5 5 NULL 888 889 # Check that the expression is still valid if the dependent name 890 # is not otherwise rendered (needed column elision). 891 query I colnames,rowsort 892 SELECT generate_series(1,x) FROM vals 893 ---- 894 generate_series 895 1 896 2 897 3 898 1 899 2 900 3 901 4 902 5 903 904 # Check that the number of rows is still correct 905 # even if the SRF is not needed. 906 query I 907 SELECT count(*) FROM (SELECT generate_series(1,x) FROM vals) 908 ---- 909 8 910 911 query TI colnames 912 SELECT relname, unnest(indkey) FROM pg_class, pg_index WHERE pg_class.oid = pg_index.indrelid ORDER BY relname, unnest 913 ---- 914 relname unnest 915 ordered_t 1 916 t 2 917 u 2 918 vals 1 919 vals 2 920 vals 3 921 922 query TT colnames 923 SELECT relname, information_schema._pg_expandarray(indkey) FROM pg_class, pg_index WHERE pg_class.oid = pg_index.indrelid ORDER BY relname, x, n 924 ---- 925 relname information_schema._pg_expandarray 926 ordered_t (1,1) 927 t (2,1) 928 u (2,1) 929 vals (1,1) 930 vals (2,2) 931 vals (3,1) 932 933 # The following query needs indclass to become an oidvector. 934 # See bug #26504. 935 # query III 936 # SELECT 937 # indexrelid, 938 # (information_schema._pg_expandarray(indclass)).x AS operator_argument_type_oid, 939 # (information_schema._pg_expandarray(indclass)).n AS operator_argument_position 940 # FROM 941 # pg_index 942 # ---- 943 944 subtest correlated_json_object_keys 945 946 statement ok 947 CREATE TABLE j(x INT PRIMARY KEY, y JSON); 948 INSERT INTO j VALUES 949 (1, '{"a":123,"b":456}'), 950 (2, '{"c":111,"d":222}') 951 952 query IT rowsort 953 SELECT x, y->>json_object_keys(y) FROM j 954 ---- 955 1 123 956 1 456 957 2 111 958 2 222 959 960 subtest correlated_multi_column 961 962 query TTI colnames 963 SELECT tbl, idx, (i.keys).n 964 FROM (SELECT ct.relname AS tbl, ct2.relname AS idx, information_schema._pg_expandarray(indkey) AS keys 965 FROM pg_index ix 966 JOIN pg_class ct ON ix.indrelid = ct.oid AND ct.relname = 'vals' 967 JOIN pg_class ct2 ON ix.indexrelid = ct2.oid) AS i 968 ORDER BY 1,2,3 969 ---- 970 tbl idx n 971 vals primary 1 972 vals woo 1 973 vals woo 2 974 975 subtest dbviz_example_query 976 977 # DbVisualizer query from #24649 listed in #16971. 978 query TTI 979 SELECT a.attname, a.atttypid, atttypmod 980 FROM pg_catalog.pg_class ct 981 JOIN pg_catalog.pg_attribute a ON (ct.oid = a.attrelid) 982 JOIN pg_catalog.pg_namespace n ON (ct.relnamespace = n.oid) 983 JOIN ( 984 SELECT i.indexrelid, i.indrelid, i.indisprimary, 985 information_schema._pg_expandarray(i.indkey) AS keys 986 FROM pg_catalog.pg_index i 987 ) i ON (a.attnum = (i.keys).x AND a.attrelid = i.indrelid) 988 WHERE true 989 AND n.nspname = 'public' 990 AND ct.relname = 'j' 991 AND i.indisprimary 992 ORDER BY a.attnum 993 ---- 994 x 20 -1 995 996 subtest metabase_confluent_example_query 997 998 # Test from metabase listed on #16971. 999 # Also Kafka Confluent sink query from #25854. 1000 query TTTTIT 1001 SELECT NULL AS TABLE_CAT, 1002 n.nspname AS TABLE_SCHEM, 1003 ct.relname AS TABLE_NAME, 1004 a.attname AS COLUMN_NAME, 1005 (i.keys).n AS KEY_SEQ, 1006 ci.relname AS PK_NAME 1007 FROM pg_catalog.pg_class ct 1008 JOIN pg_catalog.pg_attribute a ON (ct.oid = a.attrelid) 1009 JOIN pg_catalog.pg_namespace n ON (ct.relnamespace = n.oid) 1010 JOIN (SELECT i.indexrelid, 1011 i.indrelid, 1012 i.indisprimary, 1013 information_schema._pg_expandarray(i.indkey) AS keys 1014 FROM pg_catalog.pg_index i) i ON (a.attnum = (i.keys).x AND a.attrelid = i.indrelid) 1015 JOIN pg_catalog.pg_class ci ON (ci.oid = i.indexrelid) 1016 WHERE true AND ct.relname = 'j' AND i.indisprimary 1017 ORDER BY table_name, pk_name, key_seq 1018 ---- 1019 NULL public j x 1 primary 1020 1021 subtest liquibase_example_query 1022 1023 # # Test from #24713 (Liquibase) listed on #16971. 1024 # # TODO(knz) Needs support for pg_get_indexdef with 3 arguments, 1025 # # see #26629. 1026 # query TTTBTTIITTTTT 1027 # SELECT NULL AS table_cat, 1028 # n.nspname AS table_schem, 1029 # ct.relname AS TABLE_NAME, 1030 # NOT i.indisunique AS non_unique, 1031 # NULL AS index_qualifier, 1032 # ci.relname AS index_name, 1033 # CASE i.indisclustered 1034 # WHEN TRUE THEN 1 1035 # ELSE CASE am.amname 1036 # WHEN 'hash' THEN 2 1037 # ELSE 3 1038 # END 1039 # END AS TYPE, 1040 # (i.KEYS).n AS ordinal_position, 1041 # trim(BOTH '"' FROM pg_catalog.pg_get_indexdef(ci.oid, (i.KEYS).n, FALSE)) AS COLUMN_NAME, 1042 # CASE am.amcanorder 1043 # WHEN TRUE THEN CASE i.indoption[(i.keys).n - 1] & 1 1044 # WHEN 1 THEN 'D' 1045 # ELSE 'A' 1046 # END 1047 # ELSE NULL 1048 # END AS asc_or_desc, 1049 # ci.reltuples AS CARDINALITY, 1050 # ci.relpages AS pages, 1051 # pg_catalog.pg_get_expr(i.indpred, i.indrelid) AS filter_condition 1052 # FROM pg_catalog.pg_class ct 1053 # JOIN pg_catalog.pg_namespace n ON (ct.relnamespace = n.oid) 1054 # JOIN ( 1055 # SELECT i.indexrelid, 1056 # i.indrelid, 1057 # i.indoption, 1058 # i.indisunique, 1059 # i.indisclustered, 1060 # i.indpred, 1061 # i.indexprs, 1062 # information_schema._pg_expandarray(i.indkey) AS KEYS 1063 # FROM pg_catalog.pg_index i 1064 # ) i 1065 # ON (ct.oid = i.indrelid) 1066 # JOIN pg_catalog.pg_class ci ON (ci.oid = i.indexrelid) 1067 # JOIN pg_catalog.pg_am am ON (ci.relam = am.oid) 1068 # WHERE TRUE 1069 # AND n.nspname = 'public' 1070 # AND ct.relname = 'j' 1071 # ORDER BY non_unique, 1072 # TYPE, 1073 # index_name, 1074 # ordinal_position 1075 # ---- 1076 1077 subtest unnest_with_tuple_types 1078 1079 query T colnames 1080 SELECT unnest(ARRAY[(1,2),(3,4)]) 1081 ---- 1082 unnest 1083 (1,2) 1084 (3,4) 1085 1086 query II colnames 1087 SELECT (unnest(ARRAY[(1,2),(3,4)])).* 1088 ---- 1089 ?column? ?column? 1090 1 2 1091 3 4 1092 1093 query T colnames 1094 SELECT * FROM unnest(ARRAY[(1,2),(3,4)]) 1095 ---- 1096 unnest 1097 (1,2) 1098 (3,4) 1099 1100 query T colnames 1101 SELECT t.* FROM unnest(ARRAY[(1,2),(3,4)]) AS t 1102 ---- 1103 t 1104 (1,2) 1105 (3,4) 1106 1107 1108 subtest variadic_unnest 1109 1110 query T 1111 SELECT unnest(ARRAY[1,2], ARRAY['a','b']) 1112 ---- 1113 (1,a) 1114 (2,b) 1115 1116 query T 1117 SELECT unnest(ARRAY[1,2], ARRAY['a'], ARRAY[1.1, 2.2, 3.3]) 1118 ---- 1119 (1,a,1.1) 1120 (2,,2.2) 1121 (,,3.3) 1122 1123 query IT colnames 1124 SELECT * FROM unnest(ARRAY[1,2], ARRAY['a', 'b']) 1125 ---- 1126 unnest unnest 1127 1 a 1128 2 b 1129 1130 query ITT colnames 1131 SELECT * FROM unnest(ARRAY[1,2], ARRAY['a'], ARRAY[1.1, 2.2, 3.3]) 1132 ---- 1133 unnest unnest unnest 1134 1 a 1.1 1135 2 NULL 2.2 1136 NULL NULL 3.3 1137 1138 query II colnames 1139 SELECT * FROM unnest(array[1,2], array[3,4,5]) AS t(a, b); 1140 ---- 1141 a b 1142 1 3 1143 2 4 1144 NULL 5 1145 1146 query I rowsort 1147 SELECT unnest(ARRAY[1,2,3]) FROM unnest(ARRAY[4,5,6]) 1148 ---- 1149 1 1150 1 1151 1 1152 2 1153 2 1154 2 1155 3 1156 3 1157 3 1158 1159 query I rowsort 1160 SELECT unnest(ARRAY[NULL,2,3]) FROM unnest(ARRAY[NULL,NULL,NULL]) 1161 ---- 1162 NULL 1163 NULL 1164 NULL 1165 2 1166 2 1167 2 1168 3 1169 3 1170 3 1171 1172 query I rowsort 1173 SELECT unnest(ARRAY[1,2,NULL]) FROM unnest(ARRAY[NULL,NULL,NULL]) 1174 ---- 1175 1 1176 1 1177 1 1178 2 1179 2 1180 2 1181 NULL 1182 NULL 1183 NULL 1184 1185 query I rowsort 1186 SELECT unnest(ARRAY[NULL,NULL,NULL]) FROM unnest(ARRAY[NULL,NULL,NULL]) 1187 ---- 1188 NULL 1189 NULL 1190 NULL 1191 NULL 1192 NULL 1193 NULL 1194 NULL 1195 NULL 1196 NULL 1197 1198 statement ok 1199 CREATE TABLE xy (x INT PRIMARY KEY, y INT) 1200 1201 statement ok 1202 INSERT INTO xy (VALUES (1,1), (2,2), (3,4), (4,8), (5,NULL)) 1203 1204 query II rowsort 1205 SELECT * FROM xy WHERE x IN (SELECT unnest(ARRAY[NULL,x])) 1206 ---- 1207 1 1 1208 2 2 1209 3 4 1210 4 8 1211 5 NULL 1212 1213 query II rowsort 1214 SELECT * FROM xy 1215 WHERE EXISTS 1216 (SELECT t 1217 FROM unnest(ARRAY[NULL,2,NULL,4,5,x]) 1218 AS f(t) 1219 WHERE t=y 1220 ) 1221 ---- 1222 1 1 1223 2 2 1224 3 4 1225 1226 query IT rowsort 1227 SELECT unnest(ARRAY[1,2,3,4]), unnest(ARRAY['one','two']) 1228 ---- 1229 1 one 1230 2 two 1231 3 NULL 1232 4 NULL 1233 1234 query error expected 1 to be of type varbit, found type int 1235 SELECT unnest(ARRAY[1,2,3::varbit]) 1236 1237 query error expected 2 to be of type varbit, found type int 1238 SELECT unnest(ARRAY[NULL,2,3::varbit]) 1239 1240 query error pq: could not determine polymorphic type 1241 SELECT unnest(NULL, NULL) 1242 1243 query error pq: could not determine polymorphic type 1244 SELECT unnest(ARRAY[1,2], NULL) 1245 1246 query error pq: could not determine polymorphic type 1247 SELECT * FROM unnest(NULL, NULL) 1248 1249 query error pq: column reference "unnest" is ambiguous 1250 SELECT unnest FROM unnest(array[1,2], array[3,4,5])