github.com/cockroachdb/cockroach@v20.2.0-alpha.1+incompatible/pkg/sql/sem/tree/testdata/eval/cast (about) 1 # Cast from bytes to string uses the current value of bytea_output. 2 3 eval 4 x'636174'::bytes::string 5 ---- 6 e'\\x636174' 7 8 eval 9 e'\\x636174'::BYTES 10 ---- 11 '\x636174' 12 13 eval 14 e'\\X636174'::BYTES 15 ---- 16 '\x636174' 17 18 eval 19 e'\\x636174'::STRING::BYTES 20 ---- 21 '\x636174' 22 23 eval 24 e'\\x636174'::STRING 25 ---- 26 e'\\x636174' 27 28 # Cast expressions. 29 eval 30 true::boolean 31 ---- 32 true 33 34 eval 35 true::int 36 ---- 37 1 38 39 eval 40 true::float 41 ---- 42 1.0 43 44 eval 45 length(true::text) 46 ---- 47 4 48 49 eval 50 false::boolean 51 ---- 52 false 53 54 eval 55 false::int 56 ---- 57 0 58 59 eval 60 false::float 61 ---- 62 0.0 63 64 eval 65 true::decimal 66 ---- 67 1 68 69 eval 70 false::decimal 71 ---- 72 0 73 74 eval 75 length(false::text) 76 ---- 77 5 78 79 eval 80 1::boolean 81 ---- 82 true 83 84 eval 85 0::boolean 86 ---- 87 false 88 89 eval 90 1::int 91 ---- 92 1 93 94 eval 95 '1'::int 96 ---- 97 1 98 99 eval 100 1::float 101 ---- 102 1.0 103 104 eval 105 '1'::float 106 ---- 107 1.0 108 109 eval 110 1::decimal 111 ---- 112 1 113 114 eval 115 '1'::decimal 116 ---- 117 1 118 119 eval 120 length(123::text) 121 ---- 122 3 123 124 eval 125 1.1::boolean 126 ---- 127 true 128 129 eval 130 0.0::boolean 131 ---- 132 false 133 134 eval 135 (1.1::decimal)::int 136 ---- 137 1 138 139 eval 140 (1.9::decimal)::int 141 ---- 142 2 143 144 eval 145 (1.1::decimal)::float 146 ---- 147 1.1 148 149 eval 150 (1.1::decimal)::boolean 151 ---- 152 true 153 154 eval 155 (0.0::decimal)::boolean 156 ---- 157 false 158 159 eval 160 (1e300::decimal)::float 161 ---- 162 1e+300 163 164 eval 165 (9223372036854775807::decimal)::int 166 ---- 167 9223372036854775807 168 169 # The two largest floats that can be converted to an int, albeit inexactly. 170 eval 171 9223372036854775295::float::int 172 ---- 173 9223372036854774784 174 175 eval 176 -9223372036854775295::float::int 177 ---- 178 -9223372036854774784 179 180 eval 181 1.1::int 182 ---- 183 1 184 185 eval 186 1.5::int 187 ---- 188 2 189 190 eval 191 1.9::int 192 ---- 193 2 194 195 eval 196 2.5::int 197 ---- 198 3 199 200 eval 201 3.5::int 202 ---- 203 4 204 205 eval 206 -1.5::int 207 ---- 208 -2 209 210 eval 211 -2.5::int 212 ---- 213 -3 214 215 eval 216 1.1::float 217 ---- 218 1.1 219 220 eval 221 '1.1'::float 222 ---- 223 1.1 224 225 eval 226 -1e+06::float 227 ---- 228 -1e+06 229 230 eval 231 -9.99999e+05 232 ---- 233 -999999 234 235 eval 236 999999.0 237 ---- 238 999999.0 239 240 eval 241 1000000.0 242 ---- 243 1000000.0 244 245 eval 246 -1e+06 247 ---- 248 -1E+6 249 250 eval 251 -9.99999e+05::decimal 252 ---- 253 -999999 254 255 eval 256 999999.0::decimal 257 ---- 258 999999.0 259 260 eval 261 '999999.0'::decimal 262 ---- 263 999999.0 264 265 eval 266 1000000.0::decimal 267 ---- 268 1000000.0 269 270 eval 271 length(1.23::text) 272 ---- 273 4 274 275 eval 276 't'::boolean 277 ---- 278 true 279 280 eval 281 'T'::boolean 282 ---- 283 true 284 285 eval 286 'true'::boolean 287 ---- 288 true 289 290 eval 291 'True'::boolean 292 ---- 293 true 294 295 eval 296 'TRUE'::boolean 297 ---- 298 true 299 300 eval 301 '1'::boolean 302 ---- 303 true 304 305 eval 306 'f'::boolean 307 ---- 308 false 309 310 eval 311 'F'::boolean 312 ---- 313 false 314 315 eval 316 'false'::boolean 317 ---- 318 false 319 320 eval 321 'False'::boolean 322 ---- 323 false 324 325 eval 326 'FALSE'::boolean 327 ---- 328 false 329 330 eval 331 '0'::boolean 332 ---- 333 false 334 335 eval 336 '123'::int + 1 337 ---- 338 124 339 340 eval 341 NULL::int 342 ---- 343 NULL 344 345 eval 346 '0x123'::int + 1 347 ---- 348 292 349 350 eval 351 '0123'::int + 1 352 ---- 353 84 354 355 eval 356 '1.23'::float + 1.0 357 ---- 358 2.23 359 360 eval 361 'hello'::text 362 ---- 363 'hello' 364 365 eval 366 CAST('123' AS int) + 1 367 ---- 368 124 369 370 eval 371 CAST(NULL AS int) 372 ---- 373 NULL 374 375 eval 376 'hello'::char(2) 377 ---- 378 'he' 379 380 eval 381 'hello'::bytes 382 ---- 383 '\x68656c6c6f' 384 385 eval 386 b'hello'::string 387 ---- 388 'hello' 389 390 # Casting a byte array to string uses the current value of 391 # bytea_output, which is hex by default in this test. 392 eval 393 b'hello'::bytes::string 394 ---- 395 e'\\x68656c6c6f' 396 397 eval 398 b'\xff' 399 ---- 400 '\xff' 401 402 eval 403 123::text 404 ---- 405 '123' 406 407 eval 408 date '2010-09-28' 409 ---- 410 '2010-09-28' 411 412 eval 413 CAST('2010-09-28' AS date) 414 ---- 415 '2010-09-28' 416 417 eval 418 '2010-09-28'::date 419 ---- 420 '2010-09-28' 421 422 eval 423 '2010-09-28'::date::text 424 ---- 425 '2010-09-28' 426 427 eval 428 ('2010-09-28'::date)::date 429 ---- 430 '2010-09-28' 431 432 eval 433 '2010-09-28T12:00:00Z'::date 434 ---- 435 '2010-09-28' 436 437 eval 438 '1969-12-30 01:00:00'::timestamp::date 439 ---- 440 '1969-12-30' 441 442 eval 443 time '12:00:00' 444 ---- 445 '12:00:00' 446 447 eval 448 CAST('12:00:00' AS time) 449 ---- 450 '12:00:00' 451 452 eval 453 '12:00:00'::time 454 ---- 455 '12:00:00' 456 457 eval 458 '12:00:00'::time::text 459 ---- 460 '12:00:00' 461 462 eval 463 timetz '12:00:00' 464 ---- 465 '12:00:00+00:00:00' 466 467 eval 468 timetz '12:00:00+01' 469 ---- 470 '12:00:00+01:00:00' 471 472 eval 473 CAST('12:00:00+01' AS timetz) 474 ---- 475 '12:00:00+01:00:00' 476 477 eval 478 '12:00:00+01'::timetz 479 ---- 480 '12:00:00+01:00:00' 481 482 eval 483 '12:00:00+01'::timetz::text 484 ---- 485 '12:00:00+01:00:00' 486 487 eval 488 timestamp '2010-09-28' 489 ---- 490 '2010-09-28 00:00:00+00:00' 491 492 eval 493 CAST('2010-09-28' AS timestamp) 494 ---- 495 '2010-09-28 00:00:00+00:00' 496 497 eval 498 '2010-09-28'::timestamp 499 ---- 500 '2010-09-28 00:00:00+00:00' 501 502 eval 503 timestamptz '2010-09-28' 504 ---- 505 '2010-09-28 00:00:00+00:00' 506 507 eval 508 CAST('2010-09-28' AS timestamptz) 509 ---- 510 '2010-09-28 00:00:00+00:00' 511 512 eval 513 '2010-09-28'::timestamptz 514 ---- 515 '2010-09-28 00:00:00+00:00' 516 517 eval 518 ('2010-09-28 12:00:00.1'::timestamp)::date 519 ---- 520 '2010-09-28' 521 522 eval 523 '2010-09-28 12:00:00.1'::timestamp 524 ---- 525 '2010-09-28 12:00:00.1+00:00' 526 527 eval 528 '2010-09-28 12:00:00.1+02:00'::timestamp 529 ---- 530 '2010-09-28 12:00:00.1+00:00' 531 532 eval 533 '2010-09-28 12:00:00.524000 +02:00:00'::timestamp 534 ---- 535 '2010-09-28 12:00:00.524+00:00' 536 537 eval 538 '2010-09-28 12:00:00.1-07:00'::timestamp 539 ---- 540 '2010-09-28 12:00:00.1+00:00' 541 542 eval 543 '2010-09-28T12:00:00'::timestamp 544 ---- 545 '2010-09-28 12:00:00+00:00' 546 547 eval 548 '2010-09-28T12:00:00Z'::timestamp 549 ---- 550 '2010-09-28 12:00:00+00:00' 551 552 eval 553 '2010-09-28T12:00:00.1'::timestamp 554 ---- 555 '2010-09-28 12:00:00.1+00:00' 556 557 eval 558 ('2010-09-28'::date)::timestamp 559 ---- 560 '2010-09-28 00:00:00+00:00' 561 562 eval 563 '2010-09-28 12:00:00.1-04'::timestamp 564 ---- 565 '2010-09-28 12:00:00.1+00:00' 566 567 eval 568 '2010-09-28 12:00:00.1-04'::timestamp::text 569 ---- 570 '2010-09-28 12:00:00.1+00:00' 571 572 eval 573 '2010-09-28 12:00:00.1-04'::timestamptz::text 574 ---- 575 '2010-09-28 16:00:00.1+00:00' 576 577 eval 578 '12h2m1s23ms'::interval 579 ---- 580 '12:02:01.023' 581 582 eval 583 '12h2m1s23ms'::interval::text 584 ---- 585 '12:02:01.023' 586 587 eval 588 '12:01:02.123456789'::interval::text 589 ---- 590 '12:01:02.123457' 591 592 eval 593 '12:01:02.1234564'::interval 594 ---- 595 '12:01:02.123456' 596 597 eval 598 '12:01:02.1234565'::interval 599 ---- 600 '12:01:02.123457' 601 602 eval 603 '12:01:02.1234566'::interval 604 ---- 605 '12:01:02.123457' 606 607 eval 608 interval '1' 609 ---- 610 '00:00:01' 611 612 eval 613 CAST('1' AS interval) 614 ---- 615 '00:00:01' 616 617 eval 618 '1'::interval 619 ---- 620 '00:00:01' 621 622 eval 623 1::interval 624 ---- 625 '00:00:01' 626 627 eval 628 (1::interval)::interval 629 ---- 630 '00:00:01' 631 632 eval 633 '00:00:01.123456'::interval::interval(3) 634 ---- 635 '00:00:01.123' 636 637 eval 638 '00:00:01.123'::interval::interval(3) 639 ---- 640 '00:00:01.123' 641 642 eval 643 '00:00:01.123456'::interval::interval second(3) 644 ---- 645 '00:00:01.123' 646 647 eval 648 '00:00:01.123'::interval::interval second(3) 649 ---- 650 '00:00:01.123' 651 652 eval 653 '00:02:01.123456'::interval::interval minute 654 ---- 655 '00:02:00' 656 657 eval 658 '00:02:01.123456'::interval minute 659 ---- 660 '00:02:00' 661 662 eval 663 interval '1 year 1 day 02:03:04.567'::int 664 ---- 665 31651384 666 667 eval 668 interval '1 year 1 day 02:03:04.567'::float 669 ---- 670 3.1651384567e+07 671 672 eval 673 '2010-09-28'::date + 3 674 ---- 675 '2010-10-01' 676 677 eval 678 3 + '2010-09-28'::date 679 ---- 680 '2010-10-01' 681 682 eval 683 '2010-09-28'::date - 3 684 ---- 685 '2010-09-25' 686 687 eval 688 '2010-09-28'::date - '2010-10-21'::date 689 ---- 690 -23 691 692 eval 693 '12:00:00'::time + '1s'::interval 694 ---- 695 '12:00:01' 696 697 eval 698 '1s'::interval + '12:00:00'::time 699 ---- 700 '12:00:01' 701 702 eval 703 '12:00:01'::time - '12:00:00'::time 704 ---- 705 '00:00:01' 706 707 eval 708 '2010-09-28 12:00:00.1-04:00'::timestamp + '12h2m'::interval 709 ---- 710 '2010-09-29 00:02:00.1+00:00' 711 712 eval 713 '12h2m'::interval + '2010-09-28 12:00:00.1-04:00'::timestamp 714 ---- 715 '2010-09-29 00:02:00.1+00:00' 716 717 eval 718 '12 hours 2 minutes'::interval + '2010-09-28 12:00:00.1-04:00'::timestamp 719 ---- 720 '2010-09-29 00:02:00.1+00:00' 721 722 eval 723 'PT12H2M'::interval + '2010-09-28 12:00:00.1-04:00'::timestamp 724 ---- 725 '2010-09-29 00:02:00.1+00:00' 726 727 eval 728 '12:2'::interval + '2010-09-28 12:00:00.1-04:00'::timestamp 729 ---- 730 '2010-09-29 00:02:00.1+00:00' 731 732 eval 733 '2010-09-28 12:00:00.1-04:00'::timestamp - '12h2m'::interval 734 ---- 735 '2010-09-27 23:58:00.1+00:00' 736 737 eval 738 '2010-09-28 12:00:00.1-04:00'::timestamp - '12 hours 2 minutes'::interval 739 ---- 740 '2010-09-27 23:58:00.1+00:00' 741 742 eval 743 '2010-09-28 12:00:00.1-04:00'::timestamp - 'PT12H2M'::interval 744 ---- 745 '2010-09-27 23:58:00.1+00:00' 746 747 eval 748 '2010-09-28 12:00:00.1-04:00'::timestamp - '12:2'::interval 749 ---- 750 '2010-09-27 23:58:00.1+00:00' 751 752 eval 753 '2010-09-28 12:00:00.1-04:00'::timestamp - '2010-09-28 12:00:00.1+00:00'::timestamp 754 ---- 755 '00:00:00' 756 757 eval 758 '2010-09-28 12:00:00.1-04:00'::timestamptz - '2010-09-28 16:00:00.1+00:00'::timestamp 759 ---- 760 '00:00:00' 761 762 eval 763 '2010-09-28 12:00:00.1-04:00'::timestamp - '2010-09-28 12:00:00.1+00:00'::timestamptz 764 ---- 765 '00:00:00' 766 767 eval 768 '1970-01-01 00:01:00.123456-00:00'::timestamp::int 769 ---- 770 60 771 772 eval 773 '1970-01-01 00:01:00.123456-00:00'::timestamptz::int 774 ---- 775 60 776 777 # Ensure timezone is stripped when casting to timestamp. 778 eval 779 '1970-01-01 00:01:00.1-01:00'::timestamptz::timestamp 780 ---- 781 '1970-01-01 01:01:00.1+00:00' 782 783 eval 784 '1970-01-01 00:01:00.1-01:00'::timestamp 785 ---- 786 '1970-01-01 00:01:00.1+00:00' 787 788 eval 789 '1970-01-10'::date::int 790 ---- 791 9 792 793 eval 794 '2h3s4us'::interval::int 795 ---- 796 7203 797 798 eval 799 '2h3s4us'::interval::int::interval 800 ---- 801 '02:00:03' 802 803 eval 804 '-2h-3s-4us'::interval::int::interval 805 ---- 806 '-02:00:03' 807 808 eval 809 '1mon2d3h4s5us'::interval::int 810 ---- 811 2775604 812 813 eval 814 (-2775604)::int::interval 815 ---- 816 '-1 mons -2 days -03:00:04' 817 818 eval 819 '1mon2d3h4s5us'::interval::int::interval 820 ---- 821 '1 mon 2 days 03:00:04' 822 823 eval 824 '-1mon-2d-3h-4s-5us'::interval::int::interval 825 ---- 826 '-1 mons -2 days -03:00:04' 827 828 eval 829 '1970-01-01 00:01:00.123456-00:00'::timestamp::decimal 830 ---- 831 60.123456 832 833 eval 834 '1970-01-01 00:01:00.123456-00:00'::timestamptz::decimal 835 ---- 836 60.123456 837 838 eval 839 '1970-01-10'::date::decimal 840 ---- 841 9 842 843 eval 844 '2h3s4us'::interval::decimal 845 ---- 846 7203.000004000 847 848 eval 849 7203.000004005::decimal::interval 850 ---- 851 '02:00:03.000004' 852 853 eval 854 '2h3s4us'::interval::decimal::interval 855 ---- 856 '02:00:03.000004' 857 858 eval 859 '-2h-3s-4us'::interval::decimal::interval 860 ---- 861 '-02:00:03.000004' 862 863 eval 864 '1mon2d3h4s5us'::interval::decimal 865 ---- 866 2775604.000005000 867 868 eval 869 '1mon2d3h4s5us'::interval::decimal::interval 870 ---- 871 '1 mon 2 days 03:00:04.000005' 872 873 eval 874 (-2775604.000005006)::decimal::interval 875 ---- 876 '-1 mons -2 days -03:00:04.000005' 877 878 eval 879 '-1mon-2d-3h-4s-5us'::interval::decimal::interval 880 ---- 881 '-1 mons -2 days -03:00:04.000005' 882 883 # MaxInt64 884 eval 885 (decimal '9223372036854775807.000000001')::interval::decimal 886 ---- 887 9223372036854775807.000000000 888 889 # MinInt64 890 eval 891 (decimal '-9223372036854775808.000000001')::interval::decimal 892 ---- 893 -9223372036854775808.000000000 894 895 # MaxInt64 896 eval 897 '296533308798y20d15h30m7s'::interval::decimal::interval 898 ---- 899 '296533308798 years 20 days 15:30:07' 900 901 # MinInt64 902 eval 903 '-296533308798y-20d-15h-30m-8s'::interval::decimal::interval 904 ---- 905 '-296533308798 years -20 days -15:30:08' 906 907 eval 908 '1970-01-01 00:01:00.123456-00:00'::timestamp::float 909 ---- 910 60.123456 911 912 eval 913 '1970-01-01 00:01:00.123456-00:00'::timestamptz::float 914 ---- 915 60.123456 916 917 eval 918 '1970-01-10'::date::float 919 ---- 920 9.0 921 922 eval 923 '2h3s4us'::interval::float 924 ---- 925 7203.000004 926 927 eval 928 '2h3s4us'::interval::float::interval 929 ---- 930 '02:00:03.000004' 931 932 eval 933 '1mon2d3h4s5us'::interval::float 934 ---- 935 2.775604000005e+06 936 937 eval 938 '1mon2d3h4s5us'::interval::float::interval 939 ---- 940 '1 mon 2 days 03:00:04.000005' 941 942 eval 943 (-2775604.000005006)::float::interval 944 ---- 945 '-1 mons -2 days -03:00:04.000005' 946 947 eval 948 '-1mon-2d-3h-4s-5us'::interval::float::interval 949 ---- 950 '-1 mons -2 days -03:00:04.000005' 951 952 eval 953 10::int::date 954 ---- 955 '1970-01-11' 956 957 eval 958 10::int::timestamp 959 ---- 960 '1970-01-01 00:00:10+00:00' 961 962 eval 963 10::int::timestamptz 964 ---- 965 '1970-01-01 00:00:10+00:00' 966 967 eval 968 10123456::int::interval 969 ---- 970 '3 mons 27 days 04:04:16' 971 972 eval 973 ARRAY[NULL]::string[] 974 ---- 975 ARRAY[NULL] 976 977 eval 978 ARRAY[1,2,3]::string[] 979 ---- 980 ARRAY['1','2','3'] 981 982 eval 983 ARRAY['1','2','3']::int[] 984 ---- 985 ARRAY[1,2,3] 986 987 eval 988 ARRAY['1','2','3']::name[] 989 ---- 990 ARRAY['1','2','3'] 991 992 eval 993 ARRAY[1,2,3]::decimal[] 994 ---- 995 ARRAY[1,2,3] 996 997 eval 998 ARRAY[1.2,2.4,3.5]::float[] 999 ---- 1000 ARRAY[1.2,2.4,3.5] 1001 1002 eval 1003 ARRAY[19620326,19931223]::timestamp[] 1004 ---- 1005 ARRAY['1970-08-16 02:05:26+00:00','1970-08-19 16:27:03+00:00'] 1006 1007 eval 1008 ARRAY[1.2,2.4,3.5]::decimal[]::float[] 1009 ---- 1010 ARRAY[1.2,2.4,3.5] 1011 1012 eval 1013 ARRAY['3h3us']::interval[]::decimal[] 1014 ---- 1015 ARRAY[10800.000003000] 1016 1017 eval 1018 ARRAY[1,NULL,3]::string[] 1019 ---- 1020 ARRAY['1',NULL,'3'] 1021 1022 eval 1023 ARRAY['hello','world']::char(2)[] 1024 ---- 1025 ARRAY['he','wo'] 1026 1027 # regression for #45850 1028 eval 1029 cast(B'11011' || B'00' as bit varying(8)) 1030 ---- 1031 B'1101100' 1032 1033 eval 1034 cast(B'11011' || B'00' as bit varying(6)) 1035 ---- 1036 B'110110' 1037 1038 eval 1039 cast(B'11011' || B'00' as bit(8)) 1040 ---- 1041 B'11011000' 1042 1043 eval 1044 cast(B'11011' || B'00' as bit(6)) 1045 ---- 1046 B'110110' 1047 1048 eval 1049 '0101000000000000000000F03F000000000000F03F'::geography::geometry 1050 ---- 1051 '0101000020E6100000000000000000F03F000000000000F03F' 1052 1053 eval 1054 '0101000000000000000000F03F000000000000F03F'::geometry::geography 1055 ---- 1056 '0101000020E6100000000000000000F03F000000000000F03F' 1057 1058 # Test that decimals are correctly cast when the original value is negative. 1059 eval 1060 '-2020-10-10'::timestamp::decimal 1061 ---- 1062 -125887824000.000000 1063 1064 eval 1065 '-2020-10-10-2020 10:10:00.11111111111111111'::timestamptz::decimal 1066 ---- 1067 -125887714199.888889 1068 1069 eval 1070 '-10'::interval::decimal 1071 ---- 1072 -10.000000000