github.com/cockroachdb/cockroach@v20.2.0-alpha.1+incompatible/pkg/sql/opt/optbuilder/testdata/scalar (about) 1 build-scalar 2 1 3 ---- 4 const: 1 [type=int] 5 6 build-scalar 7 1 + 2 8 ---- 9 const: 3 [type=int] 10 11 build-scalar vars=(string) 12 @1 13 ---- 14 variable: "@1":1 [type=string] 15 16 build-scalar vars=(int) 17 @1 + 2 18 ---- 19 plus [type=int] 20 ├── variable: "@1":1 [type=int] 21 └── const: 2 [type=int] 22 23 build-scalar vars=(int, int) 24 @1 >= 5 AND @1 <= 10 AND @2 < 4 25 ---- 26 and [type=bool] 27 ├── and [type=bool] 28 │ ├── ge [type=bool] 29 │ │ ├── variable: "@1":1 [type=int] 30 │ │ └── const: 5 [type=int] 31 │ └── le [type=bool] 32 │ ├── variable: "@1":1 [type=int] 33 │ └── const: 10 [type=int] 34 └── lt [type=bool] 35 ├── variable: "@2":2 [type=int] 36 └── const: 4 [type=int] 37 38 build-scalar vars=(int, int) 39 (@1, @2) = (1, 2) 40 ---- 41 eq [type=bool] 42 ├── tuple [type=tuple{int, int}] 43 │ ├── variable: "@1":1 [type=int] 44 │ └── variable: "@2":2 [type=int] 45 └── tuple [type=tuple{int, int}] 46 ├── const: 1 [type=int] 47 └── const: 2 [type=int] 48 49 build-scalar vars=(int) 50 @1 IN (1, 2) 51 ---- 52 in [type=bool] 53 ├── variable: "@1":1 [type=int] 54 └── tuple [type=tuple{int, int}] 55 ├── const: 1 [type=int] 56 └── const: 2 [type=int] 57 58 build-scalar vars=(int, int) 59 (@1, @2) IN ((1, 2), (3, 4)) 60 ---- 61 in [type=bool] 62 ├── tuple [type=tuple{int, int}] 63 │ ├── variable: "@1":1 [type=int] 64 │ └── variable: "@2":2 [type=int] 65 └── tuple [type=tuple{tuple{int, int}, tuple{int, int}}] 66 ├── tuple [type=tuple{int, int}] 67 │ ├── const: 1 [type=int] 68 │ └── const: 2 [type=int] 69 └── tuple [type=tuple{int, int}] 70 ├── const: 3 [type=int] 71 └── const: 4 [type=int] 72 73 build-scalar vars=(int, int, int, int) 74 (@1, @2 + @3, 5 + @4 * 2) = (@2 + @3, 8, @1 - @4) 75 ---- 76 eq [type=bool] 77 ├── tuple [type=tuple{int, int, int}] 78 │ ├── variable: "@1":1 [type=int] 79 │ ├── plus [type=int] 80 │ │ ├── variable: "@2":2 [type=int] 81 │ │ └── variable: "@3":3 [type=int] 82 │ └── plus [type=int] 83 │ ├── const: 5 [type=int] 84 │ └── mult [type=int] 85 │ ├── variable: "@4":4 [type=int] 86 │ └── const: 2 [type=int] 87 └── tuple [type=tuple{int, int, int}] 88 ├── plus [type=int] 89 │ ├── variable: "@2":2 [type=int] 90 │ └── variable: "@3":3 [type=int] 91 ├── const: 8 [type=int] 92 └── minus [type=int] 93 ├── variable: "@1":1 [type=int] 94 └── variable: "@4":4 [type=int] 95 96 build-scalar vars=(int, int, int, int) 97 ((@1, @2), (@3, @4)) = ((1, 2), (3, 4)) 98 ---- 99 eq [type=bool] 100 ├── tuple [type=tuple{tuple{int, int}, tuple{int, int}}] 101 │ ├── tuple [type=tuple{int, int}] 102 │ │ ├── variable: "@1":1 [type=int] 103 │ │ └── variable: "@2":2 [type=int] 104 │ └── tuple [type=tuple{int, int}] 105 │ ├── variable: "@3":3 [type=int] 106 │ └── variable: "@4":4 [type=int] 107 └── tuple [type=tuple{tuple{int, int}, tuple{int, int}}] 108 ├── tuple [type=tuple{int, int}] 109 │ ├── const: 1 [type=int] 110 │ └── const: 2 [type=int] 111 └── tuple [type=tuple{int, int}] 112 ├── const: 3 [type=int] 113 └── const: 4 [type=int] 114 115 build-scalar vars=(int, int, int, string) 116 (@1, (@2, 'a'), (@3, 'b', 5)) = (9, (@1 + @3, @4), (5, @4, @1)) 117 ---- 118 eq [type=bool] 119 ├── tuple [type=tuple{int, tuple{int, string}, tuple{int, string, int}}] 120 │ ├── variable: "@1":1 [type=int] 121 │ ├── tuple [type=tuple{int, string}] 122 │ │ ├── variable: "@2":2 [type=int] 123 │ │ └── const: 'a' [type=string] 124 │ └── tuple [type=tuple{int, string, int}] 125 │ ├── variable: "@3":3 [type=int] 126 │ ├── const: 'b' [type=string] 127 │ └── const: 5 [type=int] 128 └── tuple [type=tuple{int, tuple{int, string}, tuple{int, string, int}}] 129 ├── const: 9 [type=int] 130 ├── tuple [type=tuple{int, string}] 131 │ ├── plus [type=int] 132 │ │ ├── variable: "@1":1 [type=int] 133 │ │ └── variable: "@3":3 [type=int] 134 │ └── variable: "@4":4 [type=string] 135 └── tuple [type=tuple{int, string, int}] 136 ├── const: 5 [type=int] 137 ├── variable: "@4":4 [type=string] 138 └── variable: "@1":1 [type=int] 139 140 build-scalar vars=(int, int) 141 @1 IS NULL 142 ---- 143 is [type=bool] 144 ├── variable: "@1":1 [type=int] 145 └── null [type=unknown] 146 147 build-scalar vars=(int, int) 148 @1 IS NOT DISTINCT FROM NULL 149 ---- 150 is [type=bool] 151 ├── variable: "@1":1 [type=int] 152 └── null [type=unknown] 153 154 build-scalar vars=(int, int) 155 @1 IS NOT DISTINCT FROM @2 156 ---- 157 is [type=bool] 158 ├── variable: "@1":1 [type=int] 159 └── variable: "@2":2 [type=int] 160 161 build-scalar vars=(int, int) 162 @1 IS NOT NULL 163 ---- 164 is-not [type=bool] 165 ├── variable: "@1":1 [type=int] 166 └── null [type=unknown] 167 168 build-scalar vars=(int, int) 169 @1 IS DISTINCT FROM NULL 170 ---- 171 is-not [type=bool] 172 ├── variable: "@1":1 [type=int] 173 └── null [type=unknown] 174 175 build-scalar vars=(int, int) 176 @1 IS DISTINCT FROM @2 177 ---- 178 is-not [type=bool] 179 ├── variable: "@1":1 [type=int] 180 └── variable: "@2":2 [type=int] 181 182 build-scalar vars=(int, int) 183 (@1, @2) IS NULL 184 ---- 185 is-tuple-null [type=bool] 186 └── tuple [type=tuple{int, int}] 187 ├── variable: "@1":1 [type=int] 188 └── variable: "@2":2 [type=int] 189 190 build-scalar vars=(int, int) 191 (@1, @2) IS NOT NULL 192 ---- 193 is-tuple-not-null [type=bool] 194 └── tuple [type=tuple{int, int}] 195 ├── variable: "@1":1 [type=int] 196 └── variable: "@2":2 [type=int] 197 198 build-scalar vars=(int, int) 199 + @1 + (- @2) 200 ---- 201 plus [type=int] 202 ├── variable: "@1":1 [type=int] 203 └── unary-minus [type=int] 204 └── variable: "@2":2 [type=int] 205 206 build-scalar vars=(int, int) 207 CASE WHEN @1 = 2 THEN 1 ELSE 2 END 208 ---- 209 case [type=int] 210 ├── true [type=bool] 211 ├── when [type=int] 212 │ ├── eq [type=bool] 213 │ │ ├── variable: "@1":1 [type=int] 214 │ │ └── const: 2 [type=int] 215 │ └── const: 1 [type=int] 216 └── const: 2 [type=int] 217 218 build-scalar 219 if(true, 1, 2) 220 ---- 221 case [type=int] 222 ├── true [type=bool] 223 ├── when [type=int] 224 │ ├── true [type=bool] 225 │ └── const: 1 [type=int] 226 └── const: 2 [type=int] 227 228 build-scalar 229 if(false, NULL, 1) 230 ---- 231 case [type=int] 232 ├── false [type=bool] 233 ├── when [type=unknown] 234 │ ├── true [type=bool] 235 │ └── null [type=unknown] 236 └── const: 1 [type=int] 237 238 build-scalar 239 if(true, NULL, 1) 240 ---- 241 case [type=int] 242 ├── true [type=bool] 243 ├── when [type=unknown] 244 │ ├── true [type=bool] 245 │ └── null [type=unknown] 246 └── const: 1 [type=int] 247 248 build-scalar 249 if(false, 1, NULL) 250 ---- 251 case [type=int] 252 ├── false [type=bool] 253 ├── when [type=int] 254 │ ├── true [type=bool] 255 │ └── const: 1 [type=int] 256 └── null [type=unknown] 257 258 build-scalar 259 nullif(1, 2) 260 ---- 261 case [type=int] 262 ├── const: 1 [type=int] 263 ├── when [type=unknown] 264 │ ├── const: 2 [type=int] 265 │ └── null [type=unknown] 266 └── const: 1 [type=int] 267 268 build-scalar 269 nullif(NULL, 0) 270 ---- 271 case [type=int] 272 ├── cast: INT8 [type=int] 273 │ └── null [type=unknown] 274 ├── when [type=unknown] 275 │ ├── const: 0 [type=int] 276 │ └── null [type=unknown] 277 └── cast: INT8 [type=int] 278 └── null [type=unknown] 279 280 build-scalar 281 nullif(0, NULL) 282 ---- 283 case [type=int] 284 ├── const: 0 [type=int] 285 ├── when [type=unknown] 286 │ ├── null [type=unknown] 287 │ └── null [type=unknown] 288 └── const: 0 [type=int] 289 290 build-scalar vars=(string) 291 length(@1) = 2 292 ---- 293 eq [type=bool] 294 ├── function: length [type=int] 295 │ └── variable: "@1":1 [type=string] 296 └── const: 2 [type=int] 297 298 build-scalar vars=(jsonb) 299 @1 @> '{"a":1}' 300 ---- 301 contains [type=bool] 302 ├── variable: "@1":1 [type=jsonb] 303 └── const: '{"a": 1}' [type=jsonb] 304 305 build-scalar vars=(jsonb) 306 '{"a":1}' <@ @1 307 ---- 308 contains [type=bool] 309 ├── variable: "@1":1 [type=jsonb] 310 └── const: '{"a": 1}' [type=jsonb] 311 312 build-scalar vars=(jsonb) 313 @1 ? 'a' 314 ---- 315 json-exists [type=bool] 316 ├── variable: "@1":1 [type=jsonb] 317 └── const: 'a' [type=string] 318 319 build-scalar vars=(jsonb) 320 @1 ?| ARRAY['a', 'b', 'c'] 321 ---- 322 json-some-exists [type=bool] 323 ├── variable: "@1":1 [type=jsonb] 324 └── array: [type=string[]] 325 ├── const: 'a' [type=string] 326 ├── const: 'b' [type=string] 327 └── const: 'c' [type=string] 328 329 build-scalar vars=(jsonb) 330 @1 ?& ARRAY['a', 'b', 'c'] 331 ---- 332 json-all-exists [type=bool] 333 ├── variable: "@1":1 [type=jsonb] 334 └── array: [type=string[]] 335 ├── const: 'a' [type=string] 336 ├── const: 'b' [type=string] 337 └── const: 'c' [type=string] 338 339 build-scalar 340 TRUE 341 ---- 342 true [type=bool] 343 344 345 build-scalar 346 FALSE 347 ---- 348 false [type=bool] 349 350 build-scalar 351 1::decimal 352 ---- 353 cast: DECIMAL [type=decimal] 354 └── const: 1 [type=decimal] 355 356 build-scalar 357 1::float 358 ---- 359 cast: FLOAT8 [type=float] 360 └── const: 1.0 [type=float] 361 362 build-scalar 363 1.1::int 364 ---- 365 cast: INT8 [type=int] 366 └── const: 1.1 [type=decimal] 367 368 build-scalar 369 '2010-05-12'::timestamp 370 ---- 371 const: '2010-05-12 00:00:00+00:00' [type=timestamp] 372 373 build-scalar 374 '123'::int 375 ---- 376 cast: INT8 [type=int] 377 └── const: 123 [type=int] 378 379 build-scalar vars=(int, int) 380 IFNULL(@1, @2) 381 ---- 382 coalesce [type=int] 383 ├── variable: "@1":1 [type=int] 384 └── variable: "@2":2 [type=int] 385 386 build-scalar vars=(int, int, int) 387 COALESCE(@1, @2, @3) 388 ---- 389 coalesce [type=int] 390 ├── variable: "@1":1 [type=int] 391 ├── variable: "@2":2 [type=int] 392 └── variable: "@3":3 [type=int] 393 394 build-scalar vars=(int) 395 CASE WHEN @1 > 5 THEN 1 ELSE -1 END 396 ---- 397 case [type=int] 398 ├── true [type=bool] 399 ├── when [type=int] 400 │ ├── gt [type=bool] 401 │ │ ├── variable: "@1":1 [type=int] 402 │ │ └── const: 5 [type=int] 403 │ └── const: 1 [type=int] 404 └── const: -1 [type=int] 405 406 build-scalar vars=(int) 407 CASE WHEN @1 > 5 THEN 1 WHEN @1 < 0 THEN 2 ELSE -1 END 408 ---- 409 case [type=int] 410 ├── true [type=bool] 411 ├── when [type=int] 412 │ ├── gt [type=bool] 413 │ │ ├── variable: "@1":1 [type=int] 414 │ │ └── const: 5 [type=int] 415 │ └── const: 1 [type=int] 416 ├── when [type=int] 417 │ ├── lt [type=bool] 418 │ │ ├── variable: "@1":1 [type=int] 419 │ │ └── const: 0 [type=int] 420 │ └── const: 2 [type=int] 421 └── const: -1 [type=int] 422 423 build-scalar vars=(int) 424 CASE @1 WHEN 5 THEN 1 ELSE -1 END 425 ---- 426 case [type=int] 427 ├── variable: "@1":1 [type=int] 428 ├── when [type=int] 429 │ ├── const: 5 [type=int] 430 │ └── const: 1 [type=int] 431 └── const: -1 [type=int] 432 433 build-scalar vars=(int, int) 434 CASE @1 + 3 WHEN 5 * @2 THEN 1 % @2 WHEN 6 THEN 2 ELSE -1 END 435 ---- 436 case [type=int] 437 ├── plus [type=int] 438 │ ├── variable: "@1":1 [type=int] 439 │ └── const: 3 [type=int] 440 ├── when [type=int] 441 │ ├── mult [type=int] 442 │ │ ├── const: 5 [type=int] 443 │ │ └── variable: "@2":2 [type=int] 444 │ └── mod [type=int] 445 │ ├── const: 1 [type=int] 446 │ └── variable: "@2":2 [type=int] 447 ├── when [type=int] 448 │ ├── const: 6 [type=int] 449 │ └── const: 2 [type=int] 450 └── const: -1 [type=int] 451 452 # Tests for CASE with no ELSE statement 453 build-scalar vars=(int) 454 CASE WHEN @1 > 5 THEN 1 END 455 ---- 456 case [type=int] 457 ├── true [type=bool] 458 ├── when [type=int] 459 │ ├── gt [type=bool] 460 │ │ ├── variable: "@1":1 [type=int] 461 │ │ └── const: 5 [type=int] 462 │ └── const: 1 [type=int] 463 └── null [type=unknown] 464 465 build-scalar vars=(int) 466 CASE @1 WHEN 5 THEN 1 END 467 ---- 468 case [type=int] 469 ├── variable: "@1":1 [type=int] 470 ├── when [type=int] 471 │ ├── const: 5 [type=int] 472 │ └── const: 1 [type=int] 473 └── null [type=unknown] 474 475 build-scalar vars=(int) 476 @1 BETWEEN 1 AND 4 477 ---- 478 and [type=bool] 479 ├── ge [type=bool] 480 │ ├── variable: "@1":1 [type=int] 481 │ └── const: 1 [type=int] 482 └── le [type=bool] 483 ├── variable: "@1":1 [type=int] 484 └── const: 4 [type=int] 485 486 build-scalar vars=(int) 487 @1 NOT BETWEEN 1 AND 4 488 ---- 489 not [type=bool] 490 └── and [type=bool] 491 ├── ge [type=bool] 492 │ ├── variable: "@1":1 [type=int] 493 │ └── const: 1 [type=int] 494 └── le [type=bool] 495 ├── variable: "@1":1 [type=int] 496 └── const: 4 [type=int] 497 498 build-scalar vars=(int) 499 @1 BETWEEN SYMMETRIC 1 AND 4 500 ---- 501 or [type=bool] 502 ├── and [type=bool] 503 │ ├── ge [type=bool] 504 │ │ ├── variable: "@1":1 [type=int] 505 │ │ └── const: 1 [type=int] 506 │ └── le [type=bool] 507 │ ├── variable: "@1":1 [type=int] 508 │ └── const: 4 [type=int] 509 └── and [type=bool] 510 ├── ge [type=bool] 511 │ ├── variable: "@1":1 [type=int] 512 │ └── const: 4 [type=int] 513 └── le [type=bool] 514 ├── variable: "@1":1 [type=int] 515 └── const: 1 [type=int] 516 517 build-scalar vars=(int) 518 @1 NOT BETWEEN SYMMETRIC 1 AND 4 519 ---- 520 not [type=bool] 521 └── or [type=bool] 522 ├── and [type=bool] 523 │ ├── ge [type=bool] 524 │ │ ├── variable: "@1":1 [type=int] 525 │ │ └── const: 1 [type=int] 526 │ └── le [type=bool] 527 │ ├── variable: "@1":1 [type=int] 528 │ └── const: 4 [type=int] 529 └── and [type=bool] 530 ├── ge [type=bool] 531 │ ├── variable: "@1":1 [type=int] 532 │ └── const: 4 [type=int] 533 └── le [type=bool] 534 ├── variable: "@1":1 [type=int] 535 └── const: 1 [type=int] 536 537 build-scalar vars=(int, int, int) 538 @1 BETWEEN @2 AND @3 539 ---- 540 and [type=bool] 541 ├── ge [type=bool] 542 │ ├── variable: "@1":1 [type=int] 543 │ └── variable: "@2":2 [type=int] 544 └── le [type=bool] 545 ├── variable: "@1":1 [type=int] 546 └── variable: "@3":3 [type=int] 547 548 build-scalar vars=(int, int, int) 549 (@1 + @2) BETWEEN (@2 + @3) AND (@3 + @1) 550 ---- 551 and [type=bool] 552 ├── ge [type=bool] 553 │ ├── plus [type=int] 554 │ │ ├── variable: "@1":1 [type=int] 555 │ │ └── variable: "@2":2 [type=int] 556 │ └── plus [type=int] 557 │ ├── variable: "@2":2 [type=int] 558 │ └── variable: "@3":3 [type=int] 559 └── le [type=bool] 560 ├── plus [type=int] 561 │ ├── variable: "@1":1 [type=int] 562 │ └── variable: "@2":2 [type=int] 563 └── plus [type=int] 564 ├── variable: "@3":3 [type=int] 565 └── variable: "@1":1 [type=int] 566 567 build-scalar vars=(int, int, int) 568 (@1 + @2) BETWEEN SYMMETRIC (@2 + @3) AND (@3 + @1) 569 ---- 570 or [type=bool] 571 ├── and [type=bool] 572 │ ├── ge [type=bool] 573 │ │ ├── plus [type=int] 574 │ │ │ ├── variable: "@1":1 [type=int] 575 │ │ │ └── variable: "@2":2 [type=int] 576 │ │ └── plus [type=int] 577 │ │ ├── variable: "@2":2 [type=int] 578 │ │ └── variable: "@3":3 [type=int] 579 │ └── le [type=bool] 580 │ ├── plus [type=int] 581 │ │ ├── variable: "@1":1 [type=int] 582 │ │ └── variable: "@2":2 [type=int] 583 │ └── plus [type=int] 584 │ ├── variable: "@3":3 [type=int] 585 │ └── variable: "@1":1 [type=int] 586 └── and [type=bool] 587 ├── ge [type=bool] 588 │ ├── plus [type=int] 589 │ │ ├── variable: "@1":1 [type=int] 590 │ │ └── variable: "@2":2 [type=int] 591 │ └── plus [type=int] 592 │ ├── variable: "@3":3 [type=int] 593 │ └── variable: "@1":1 [type=int] 594 └── le [type=bool] 595 ├── plus [type=int] 596 │ ├── variable: "@1":1 [type=int] 597 │ └── variable: "@2":2 [type=int] 598 └── plus [type=int] 599 ├── variable: "@2":2 [type=int] 600 └── variable: "@3":3 [type=int] 601 602 build-scalar vars=(int, int, int) 603 (@1, @2) BETWEEN (1, 2) AND (3, 4) 604 ---- 605 and [type=bool] 606 ├── ge [type=bool] 607 │ ├── tuple [type=tuple{int, int}] 608 │ │ ├── variable: "@1":1 [type=int] 609 │ │ └── variable: "@2":2 [type=int] 610 │ └── tuple [type=tuple{int, int}] 611 │ ├── const: 1 [type=int] 612 │ └── const: 2 [type=int] 613 └── le [type=bool] 614 ├── tuple [type=tuple{int, int}] 615 │ ├── variable: "@1":1 [type=int] 616 │ └── variable: "@2":2 [type=int] 617 └── tuple [type=tuple{int, int}] 618 ├── const: 3 [type=int] 619 └── const: 4 [type=int] 620 621 build-scalar vars=(int, int, int) 622 (@1, @2) NOT BETWEEN SYMMETRIC (1, 2) AND (3, 4) 623 ---- 624 not [type=bool] 625 └── or [type=bool] 626 ├── and [type=bool] 627 │ ├── ge [type=bool] 628 │ │ ├── tuple [type=tuple{int, int}] 629 │ │ │ ├── variable: "@1":1 [type=int] 630 │ │ │ └── variable: "@2":2 [type=int] 631 │ │ └── tuple [type=tuple{int, int}] 632 │ │ ├── const: 1 [type=int] 633 │ │ └── const: 2 [type=int] 634 │ └── le [type=bool] 635 │ ├── tuple [type=tuple{int, int}] 636 │ │ ├── variable: "@1":1 [type=int] 637 │ │ └── variable: "@2":2 [type=int] 638 │ └── tuple [type=tuple{int, int}] 639 │ ├── const: 3 [type=int] 640 │ └── const: 4 [type=int] 641 └── and [type=bool] 642 ├── ge [type=bool] 643 │ ├── tuple [type=tuple{int, int}] 644 │ │ ├── variable: "@1":1 [type=int] 645 │ │ └── variable: "@2":2 [type=int] 646 │ └── tuple [type=tuple{int, int}] 647 │ ├── const: 3 [type=int] 648 │ └── const: 4 [type=int] 649 └── le [type=bool] 650 ├── tuple [type=tuple{int, int}] 651 │ ├── variable: "@1":1 [type=int] 652 │ └── variable: "@2":2 [type=int] 653 └── tuple [type=tuple{int, int}] 654 ├── const: 1 [type=int] 655 └── const: 2 [type=int] 656 657 # The left side of BETWEEN is typed differently in the two comparisons. 658 build-scalar 659 '' BETWEEN ''::BYTES AND '' 660 ---- 661 and [type=bool] 662 ├── ge [type=bool] 663 │ ├── const: '\x' [type=bytes] 664 │ └── const: '\x' [type=bytes] 665 └── le [type=bool] 666 ├── const: '' [type=string] 667 └── const: '' [type=string] 668 669 build-scalar 670 '' BETWEEN SYMMETRIC ''::BYTES AND '' 671 ---- 672 or [type=bool] 673 ├── and [type=bool] 674 │ ├── ge [type=bool] 675 │ │ ├── const: '\x' [type=bytes] 676 │ │ └── const: '\x' [type=bytes] 677 │ └── le [type=bool] 678 │ ├── const: '' [type=string] 679 │ └── const: '' [type=string] 680 └── and [type=bool] 681 ├── ge [type=bool] 682 │ ├── const: '' [type=string] 683 │ └── const: '' [type=string] 684 └── le [type=bool] 685 ├── const: '\x' [type=bytes] 686 └── const: '\x' [type=bytes] 687 688 build-scalar 689 NULL 690 ---- 691 null [type=unknown] 692 693 build-scalar 694 NULL::int 695 ---- 696 cast: INT8 [type=int] 697 └── null [type=unknown] 698 699 build-scalar vars=(int[]) 700 @1 = ARRAY[1, 2, 3] 701 ---- 702 eq [type=bool] 703 ├── variable: "@1":1 [type=int[]] 704 └── array: [type=int[]] 705 ├── const: 1 [type=int] 706 ├── const: 2 [type=int] 707 └── const: 3 [type=int] 708 709 build-scalar vars=(int[]) 710 @1 = ARRAY[1, 1.0, '1'] 711 ---- 712 eq [type=bool] 713 ├── variable: "@1":1 [type=int[]] 714 └── array: [type=int[]] 715 ├── const: 1 [type=int] 716 ├── const: 1 [type=int] 717 └── const: 1 [type=int] 718 719 build-scalar vars=(float[]) 720 @1 = ARRAY[1, 1.1, '1.123'] 721 ---- 722 eq [type=bool] 723 ├── variable: "@1":1 [type=float[]] 724 └── array: [type=float[]] 725 ├── const: 1.0 [type=float] 726 ├── const: 1.1 [type=float] 727 └── const: 1.123 [type=float] 728 729 build-scalar vars=(int[]) 730 @1 = ARRAY[] 731 ---- 732 eq [type=bool] 733 ├── variable: "@1":1 [type=int[]] 734 └── array: [type=int[]] 735 736 build-scalar vars=(string[]) 737 @1 = ARRAY['foo', 'bar', 'baz'] 738 ---- 739 eq [type=bool] 740 ├── variable: "@1":1 [type=string[]] 741 └── array: [type=string[]] 742 ├── const: 'foo' [type=string] 743 ├── const: 'bar' [type=string] 744 └── const: 'baz' [type=string] 745 746 build-scalar vars=(json) 747 @1->>'a' = 'b' 748 ---- 749 eq [type=bool] 750 ├── fetch-text [type=string] 751 │ ├── variable: "@1":1 [type=jsonb] 752 │ └── const: 'a' [type=string] 753 └── const: 'b' [type=string] 754 755 build-scalar vars=(json) 756 @1->'a' = '"b"' 757 ---- 758 eq [type=bool] 759 ├── fetch-val [type=jsonb] 760 │ ├── variable: "@1":1 [type=jsonb] 761 │ └── const: 'a' [type=string] 762 └── const: '"b"' [type=jsonb] 763 764 build-scalar vars=(json) 765 @1#>ARRAY['a'] = '"b"' 766 ---- 767 eq [type=bool] 768 ├── fetch-val-path [type=jsonb] 769 │ ├── variable: "@1":1 [type=jsonb] 770 │ └── array: [type=string[]] 771 │ └── const: 'a' [type=string] 772 └── const: '"b"' [type=jsonb] 773 774 build-scalar vars=(json) 775 @1#>>ARRAY['a'] = 'b' 776 ---- 777 eq [type=bool] 778 ├── fetch-text-path [type=string] 779 │ ├── variable: "@1":1 [type=jsonb] 780 │ └── array: [type=string[]] 781 │ └── const: 'a' [type=string] 782 └── const: 'b' [type=string] 783 784 build-scalar vars=(json, json) 785 @1 || @2 786 ---- 787 concat [type=jsonb] 788 ├── variable: "@1":1 [type=jsonb] 789 └── variable: "@2":2 [type=jsonb] 790 791 build-scalar 792 'hello' COLLATE en 793 ---- 794 collate [type=collatedstring{en}] 795 └── const: 'hello' [type=string] 796 797 build-scalar 798 random() 799 ---- 800 function: random [type=float] 801 802 build-scalar 803 ARRAY[1, 2] || NULL 804 ---- 805 concat [type=int[]] 806 ├── array: [type=int[]] 807 │ ├── const: 1 [type=int] 808 │ └── const: 2 [type=int] 809 └── cast: INT8[] [type=int[]] 810 └── null [type=unknown] 811 812 build-scalar 813 NULL || ARRAY[1, 2] 814 ---- 815 concat [type=int[]] 816 ├── cast: INT8[] [type=int[]] 817 │ └── null [type=unknown] 818 └── array: [type=int[]] 819 ├── const: 1 [type=int] 820 └── const: 2 [type=int] 821 822 build-scalar 823 ARRAY[1, 2] || NULL::smallint 824 ---- 825 concat [type=int[]] 826 ├── array: [type=int[]] 827 │ ├── const: 1 [type=int] 828 │ └── const: 2 [type=int] 829 └── cast: INT2 [type=int2] 830 └── null [type=unknown] 831 832 build-scalar 833 NULL::oid || ARRAY[1, 2] 834 ---- 835 concat [type=oid[]] 836 ├── cast: OID [type=oid] 837 │ └── null [type=unknown] 838 └── array: [type=oid[]] 839 ├── const: 1 [type=oid] 840 └── const: 2 [type=oid] 841 842 build-scalar 843 ARRAY['"foo"'::jsonb] 844 ---- 845 error: unimplemented: arrays of jsonb not allowed 846 847 build-scalar 848 ARRAY['"foo"'::json] 849 ---- 850 error: unimplemented: arrays of jsonb not allowed 851 852 opt 853 SELECT -((-9223372036854775808):::int) 854 ---- 855 values 856 ├── columns: "?column?":1 857 └── (-(-9223372036854775808),) 858 859 # TODO(justin): modify build-scalar to handle subqueries 860 # so this can use it. 861 build 862 SELECT ARRAY(SELECT 1) 863 ---- 864 project 865 ├── columns: array:2 866 ├── values 867 │ └── () 868 └── projections 869 └── array-flatten [as=array:2] 870 └── project 871 ├── columns: "?column?":1!null 872 ├── values 873 │ └── () 874 └── projections 875 └── 1 [as="?column?":1] 876 877 exec-ddl 878 CREATE TABLE x (a INT PRIMARY KEY) 879 ---- 880 881 exec-ddl 882 CREATE TABLE y (b INT PRIMARY KEY) 883 ---- 884 885 build 886 SELECT b, ARRAY(SELECT a FROM x WHERE x.a = y.b) FROM y 887 ---- 888 project 889 ├── columns: b:1!null array:3 890 ├── scan y 891 │ └── columns: b:1!null 892 └── projections 893 └── array-flatten [as=array:3] 894 └── select 895 ├── columns: a:2!null 896 ├── scan x 897 │ └── columns: a:2!null 898 └── filters 899 └── a:2 = b:1 900 901 build 902 SELECT b, ARRAY(SELECT a FROM x ORDER BY a) FROM y 903 ---- 904 project 905 ├── columns: b:1!null array:3 906 ├── scan y 907 │ └── columns: b:1!null 908 └── projections 909 └── array-flatten [as=array:3] 910 └── scan x 911 ├── columns: a:2!null 912 └── ordering: +2 913 914 build 915 SELECT ARRAY(VALUES ('foo'), ('bar'), ('baz')) 916 ---- 917 project 918 ├── columns: array:2 919 ├── values 920 │ └── () 921 └── projections 922 └── array-flatten [as=array:2] 923 └── values 924 ├── columns: column1:1!null 925 ├── ('foo',) 926 ├── ('bar',) 927 └── ('baz',) 928 929 build 930 SELECT ARRAY(VALUES (ARRAY[1])) 931 ---- 932 project 933 ├── columns: array:2 934 ├── values 935 │ └── () 936 └── projections 937 └── array-flatten [as=array:2] 938 └── values 939 ├── columns: column1:1 940 └── (ARRAY[1],) 941 942 build 943 SELECT ARRAY(SELECT (1, 2)) 944 ---- 945 project 946 ├── columns: array:2 947 ├── values 948 │ └── () 949 └── projections 950 └── array-flatten [as=array:2] 951 └── project 952 ├── columns: "?column?":1!null 953 ├── values 954 │ └── () 955 └── projections 956 └── (1, 2) [as="?column?":1] 957 958 build 959 SELECT ARRAY(VALUES ((1, 2))) 960 ---- 961 project 962 ├── columns: array:2 963 ├── values 964 │ └── () 965 └── projections 966 └── array-flatten [as=array:2] 967 └── values 968 ├── columns: column1:1 969 └── ((1, 2),) 970 971 build 972 SELECT ARRAY(VALUES ('{}'::JSONB)) 973 ---- 974 error (0A000): unimplemented: arrays of jsonb not allowed 975 976 build 977 SELECT ARRAY(SELECT 1, 2) 978 ---- 979 error (42601): subquery must return only one column, found 2 980 981 build 982 SELECT ARRAY(SELECT generate_series(1,100) ORDER BY 1 DESC) 983 ---- 984 project 985 ├── columns: array:2 986 ├── values 987 │ └── () 988 └── projections 989 └── array-flatten [as=array:2] 990 └── sort 991 ├── columns: generate_series:1 992 ├── ordering: -1 993 └── project-set 994 ├── columns: generate_series:1 995 ├── values 996 │ └── () 997 └── zip 998 └── generate_series(1, 100) 999 1000 build-scalar 1001 1 = ANY ARRAY[1, 2, 3] 1002 ---- 1003 any-scalar: eq [type=bool] 1004 ├── const: 1 [type=int] 1005 └── array: [type=int[]] 1006 ├── const: 1 [type=int] 1007 ├── const: 2 [type=int] 1008 └── const: 3 [type=int] 1009 1010 build-scalar 1011 1 > ANY ARRAY[1, 2, 3] 1012 ---- 1013 any-scalar: gt [type=bool] 1014 ├── const: 1 [type=int] 1015 └── array: [type=int[]] 1016 ├── const: 1 [type=int] 1017 ├── const: 2 [type=int] 1018 └── const: 3 [type=int] 1019 1020 build-scalar 1021 1 = ALL ARRAY[1, 2, 3] 1022 ---- 1023 not [type=bool] 1024 └── any-scalar: ne [type=bool] 1025 ├── const: 1 [type=int] 1026 └── array: [type=int[]] 1027 ├── const: 1 [type=int] 1028 ├── const: 2 [type=int] 1029 └── const: 3 [type=int] 1030 1031 build-scalar 1032 1 > ALL ARRAY[1, 2, 3] 1033 ---- 1034 not [type=bool] 1035 └── any-scalar: le [type=bool] 1036 ├── const: 1 [type=int] 1037 └── array: [type=int[]] 1038 ├── const: 1 [type=int] 1039 ├── const: 2 [type=int] 1040 └── const: 3 [type=int] 1041 1042 build-scalar 1043 1 = ANY (1, 2, 3) 1044 ---- 1045 any-scalar: eq [type=bool] 1046 ├── const: 1 [type=int] 1047 └── tuple [type=tuple{int, int, int}] 1048 ├── const: 1 [type=int] 1049 ├── const: 2 [type=int] 1050 └── const: 3 [type=int] 1051 1052 build-scalar 1053 'foo' = ANY ('foo', 'bar', 'baz') 1054 ---- 1055 any-scalar: eq [type=bool] 1056 ├── const: 'foo' [type=string] 1057 └── tuple [type=tuple{string, string, string}] 1058 ├── const: 'foo' [type=string] 1059 ├── const: 'bar' [type=string] 1060 └── const: 'baz' [type=string] 1061 1062 1063 build-scalar 1064 1 = ANY () 1065 ---- 1066 any-scalar: eq [type=bool] 1067 ├── const: 1 [type=int] 1068 └── tuple [type=tuple] 1069 1070 build 1071 SELECT 1 > ALL ARRAY['foo'] 1072 ---- 1073 error (22023): unsupported comparison operator: 1 > ALL ARRAY['foo']: could not parse "foo" as type int: strconv.ParseInt: parsing "foo": invalid syntax 1074 1075 build-scalar vars=(int[], string[], float[], int) 1076 (@1[1], @2[1+1], @3[@4]) 1077 ---- 1078 tuple [type=tuple{int, string, float}] 1079 ├── indirection [type=int] 1080 │ ├── variable: "@1":1 [type=int[]] 1081 │ └── const: 1 [type=int] 1082 ├── indirection [type=string] 1083 │ ├── variable: "@2":2 [type=string[]] 1084 │ └── const: 2 [type=int] 1085 └── indirection [type=float] 1086 ├── variable: "@3":3 [type=float[]] 1087 └── variable: "@4":4 [type=int] 1088 1089 build-scalar vars=(int,string,int[]) 1090 ( 1091 (@1 IS OF (INT), @1 IS OF (INT, STRING), @1 IS OF (STRING)), 1092 (@1 IS NOT OF (INT), @1 IS NOT OF (INT, STRING), @1 IS NOT OF (STRING)), 1093 (@2 IS NOT OF (INT), @2 IS NOT OF (INT, STRING), @2 IS NOT OF (STRING)), 1094 (@3 IS NOT OF (INT[]), @3 IS OF (INT[]), @3 IS OF (STRING[])) 1095 ) 1096 ---- 1097 tuple [type=tuple{tuple{bool, bool, bool}, tuple{bool, bool, bool}, tuple{bool, bool, bool}, tuple{bool, bool, bool}}] 1098 ├── tuple [type=tuple{bool, bool, bool}] 1099 │ ├── true [type=bool] 1100 │ ├── true [type=bool] 1101 │ └── false [type=bool] 1102 ├── tuple [type=tuple{bool, bool, bool}] 1103 │ ├── false [type=bool] 1104 │ ├── false [type=bool] 1105 │ └── true [type=bool] 1106 ├── tuple [type=tuple{bool, bool, bool}] 1107 │ ├── true [type=bool] 1108 │ ├── false [type=bool] 1109 │ └── false [type=bool] 1110 └── tuple [type=tuple{bool, bool, bool}] 1111 ├── false [type=bool] 1112 ├── true [type=bool] 1113 └── false [type=bool] 1114 1115 build-scalar vars=(string) 1116 @1 COLLATE en 1117 ---- 1118 collate [type=collatedstring{en}] 1119 └── variable: "@1":1 [type=string] 1120 1121 exec-ddl 1122 CREATE TABLE u (x INT) 1123 ---- 1124 1125 exec-ddl 1126 CREATE TABLE v (y INT[]) 1127 ---- 1128 1129 build 1130 SELECT ARRAY(SELECT x FROM u ORDER BY x) 1131 ---- 1132 project 1133 ├── columns: array:3 1134 ├── values 1135 │ └── () 1136 └── projections 1137 └── array-flatten [as=array:3] 1138 └── sort 1139 ├── columns: x:1 1140 ├── ordering: +1 1141 └── project 1142 ├── columns: x:1 1143 └── scan u 1144 └── columns: x:1 rowid:2!null 1145 1146 build 1147 SELECT * FROM v WHERE y = ARRAY(SELECT x FROM u ORDER BY x) 1148 ---- 1149 project 1150 ├── columns: y:1!null 1151 └── select 1152 ├── columns: y:1!null v.rowid:2!null 1153 ├── scan v 1154 │ └── columns: y:1 v.rowid:2!null 1155 └── filters 1156 └── eq 1157 ├── y:1 1158 └── array-flatten 1159 └── sort 1160 ├── columns: x:3 1161 ├── ordering: +3 1162 └── project 1163 ├── columns: x:3 1164 └── scan u 1165 └── columns: x:3 u.rowid:4!null 1166 1167 build 1168 SELECT ARRAY(SELECT (y, 2) FROM u ORDER BY x) FROM v 1169 ---- 1170 error (0A000): unimplemented: can't execute a correlated ARRAY(...) over tuple{int[], int} 1171 1172 build 1173 SELECT ARRAY(SELECT y FROM u ORDER BY x) FROM v 1174 ---- 1175 error (0A000): unimplemented: can't execute a correlated ARRAY(...) over int[] 1176 1177 build-scalar 1178 ISERROR(1/0) 1179 ---- 1180 if-err [type=bool] 1181 └── div [type=decimal] 1182 ├── const: 1 [type=int] 1183 └── const: 0 [type=int] 1184 1185 build-scalar 1186 ISERROR(1/0, '22012') 1187 ---- 1188 if-err [type=bool] 1189 ├── div [type=decimal] 1190 │ ├── const: 1 [type=int] 1191 │ └── const: 0 [type=int] 1192 └── err-code 1193 └── const: '22012' [type=string] 1194 1195 build-scalar vars=(decimal) 1196 IFERROR(1/0, @1) 1197 ---- 1198 if-err [type=decimal] 1199 ├── div [type=decimal] 1200 │ ├── const: 1 [type=int] 1201 │ └── const: 0 [type=int] 1202 └── else 1203 └── variable: "@1":1 [type=decimal] 1204 1205 build-scalar vars=(decimal, string) 1206 IFERROR(1/0, @1, @2) 1207 ---- 1208 if-err [type=decimal] 1209 ├── div [type=decimal] 1210 │ ├── const: 1 [type=int] 1211 │ └── const: 0 [type=int] 1212 ├── else 1213 │ └── variable: "@1":1 [type=decimal] 1214 └── err-code 1215 └── variable: "@2":2 [type=string] 1216 1217 build-scalar vars=(decimal) 1218 IFERROR(1/0, @1, '10000') 1219 ---- 1220 if-err [type=decimal] 1221 ├── div [type=decimal] 1222 │ ├── const: 1 [type=int] 1223 │ └── const: 0 [type=int] 1224 ├── else 1225 │ └── variable: "@1":1 [type=decimal] 1226 └── err-code 1227 └── const: '10000' [type=string]