github.com/cockroachdb/cockroach@v20.2.0-alpha.1+incompatible/pkg/sql/opt/memo/testdata/typing (about) 1 exec-ddl 2 CREATE TABLE a (x INT PRIMARY KEY, y INT) 3 ---- 4 5 exec-ddl 6 CREATE TABLE b (x STRING PRIMARY KEY, z DECIMAL NOT NULL) 7 ---- 8 9 exec-ddl 10 CREATE TABLE unusual (x INT PRIMARY KEY, arr INT[]) 11 ---- 12 13 # Variable 14 build 15 SELECT a.x FROM a 16 ---- 17 project 18 ├── columns: x:1(int!null) 19 └── scan a 20 └── columns: x:1(int!null) y:2(int) 21 22 # Const 23 build 24 SELECT 1 AS a, TRUE AS b, FALSE AS c, NULL AS d 25 ---- 26 project 27 ├── columns: a:1(int!null) b:2(bool!null) c:3(bool!null) d:4(unknown) 28 ├── values 29 │ └── () [type=tuple] 30 └── projections 31 ├── 1 [as=a:1, type=int] 32 ├── true [as=b:2, type=bool] 33 ├── false [as=c:3, type=bool] 34 └── NULL [as=d:4, type=unknown] 35 36 # Placeholder 37 build 38 SELECT * FROM a WHERE x = $1 39 ---- 40 select 41 ├── columns: x:1(int!null) y:2(int) 42 ├── scan a 43 │ └── columns: x:1(int!null) y:2(int) 44 └── filters 45 └── x:1 = $1 [type=bool] 46 47 # Tuple, Projections 48 build 49 SELECT (a.x, 1.5) AS r, a.y FROM a 50 ---- 51 project 52 ├── columns: r:3(tuple{int, decimal}!null) y:2(int) 53 ├── scan a 54 │ └── columns: x:1(int!null) y:2(int) 55 └── projections 56 └── (x:1, 1.5) [as=r:3, type=tuple{int, decimal}] 57 58 # And, Or, Not 59 build 60 SELECT * FROM a WHERE a.x = 1 AND NOT (a.y = 2 OR a.y = 3.5) 61 ---- 62 select 63 ├── columns: x:1(int!null) y:2(int) 64 ├── scan a 65 │ └── columns: x:1(int!null) y:2(int) 66 └── filters 67 └── (x:1 = 1) AND (NOT ((y:2 = 2) OR (y:2 = 3.5))) [type=bool] 68 69 # Eq, Ne 70 build 71 SELECT * FROM a WHERE a.x = 1 AND a.x <> 2 72 ---- 73 select 74 ├── columns: x:1(int!null) y:2(int) 75 ├── scan a 76 │ └── columns: x:1(int!null) y:2(int) 77 └── filters 78 └── (x:1 = 1) AND (x:1 != 2) [type=bool] 79 80 # Le, Ge, Lt, Gt 81 build 82 SELECT * FROM a WHERE a.x >= 1 AND a.x <= 10 AND a.y > 1 AND a.y < 10 83 ---- 84 select 85 ├── columns: x:1(int!null) y:2(int!null) 86 ├── scan a 87 │ └── columns: x:1(int!null) y:2(int) 88 └── filters 89 └── (((x:1 >= 1) AND (x:1 <= 10)) AND (y:2 > 1)) AND (y:2 < 10) [type=bool] 90 91 # In, NotIn 92 build 93 SELECT * FROM a WHERE a.x IN (1, 2) AND a.y NOT IN (3, 4) 94 ---- 95 select 96 ├── columns: x:1(int!null) y:2(int) 97 ├── scan a 98 │ └── columns: x:1(int!null) y:2(int) 99 └── filters 100 └── (x:1 IN (1, 2)) AND (y:2 NOT IN (3, 4)) [type=bool] 101 102 # Like, NotLike 103 build 104 SELECT * FROM b WHERE b.x LIKE '%foo%' AND b.x NOT LIKE '%bar%' 105 ---- 106 select 107 ├── columns: x:1(string!null) z:2(decimal!null) 108 ├── scan b 109 │ └── columns: x:1(string!null) z:2(decimal!null) 110 └── filters 111 └── (x:1 LIKE '%foo%') AND (x:1 NOT LIKE '%bar%') [type=bool] 112 113 # ILike, INotLike 114 build 115 SELECT * FROM b WHERE b.x ILIKE '%foo%' AND b.x NOT ILIKE '%bar%' 116 ---- 117 select 118 ├── columns: x:1(string!null) z:2(decimal!null) 119 ├── scan b 120 │ └── columns: x:1(string!null) z:2(decimal!null) 121 └── filters 122 └── (x:1 ILIKE '%foo%') AND (x:1 NOT ILIKE '%bar%') [type=bool] 123 124 # RegMatch, NotRegMatch, RegIMatch, NotRegIMatch 125 build 126 SELECT * FROM b WHERE b.x ~ 'foo' AND b.x !~ 'bar' AND b.x ~* 'foo' AND b.x !~* 'bar' 127 ---- 128 select 129 ├── columns: x:1(string!null) z:2(decimal!null) 130 ├── scan b 131 │ └── columns: x:1(string!null) z:2(decimal!null) 132 └── filters 133 └── (((x:1 ~ 'foo') AND (x:1 !~ 'bar')) AND (x:1 ~* 'foo')) AND (x:1 !~* 'bar') [type=bool] 134 135 # Is, IsNot 136 build 137 SELECT * FROM a WHERE a.x IS DISTINCT FROM a.y AND a.x IS NULL 138 ---- 139 select 140 ├── columns: x:1(int!null) y:2(int) 141 ├── scan a 142 │ └── columns: x:1(int!null) y:2(int) 143 └── filters 144 └── (x:1 IS DISTINCT FROM y:2) AND (x:1 IS NULL) [type=bool] 145 146 # Bitand, Bitor, Bitxor 147 build 148 SELECT a.x & a.y AS r, a.x | a.y AS s, a.x # a.y AS t FROM a 149 ---- 150 project 151 ├── columns: r:3(int) s:4(int) t:5(int) 152 ├── scan a 153 │ └── columns: x:1(int!null) y:2(int) 154 └── projections 155 ├── x:1 & y:2 [as=r:3, type=int] 156 ├── x:1 | y:2 [as=s:4, type=int] 157 └── x:1 # y:2 [as=t:5, type=int] 158 159 # Plus, Minus, Mult, Div, FloorDiv 160 build 161 SELECT a.x + 1.5 AS r, 162 DATE '2000-01-01' - 15 AS s, 163 10.10 * a.x AS t, 164 1 / a.y AS u, 165 a.x // 1.5 AS v 166 FROM a 167 ---- 168 project 169 ├── columns: r:3(decimal!null) s:4(date!null) t:5(decimal!null) u:6(decimal) v:7(decimal!null) 170 ├── scan a 171 │ └── columns: x:1(int!null) y:2(int) 172 └── projections 173 ├── x:1 + 1.5 [as=r:3, type=decimal] 174 ├── '2000-01-01' - 15 [as=s:4, type=date] 175 ├── 10.10 * x:1 [as=t:5, type=decimal] 176 ├── 1 / y:2 [as=u:6, type=decimal] 177 └── x:1 // 1.5 [as=v:7, type=decimal] 178 179 # Mod, Pow, LShift, RShift 180 build 181 SELECT 100.1 % a.x AS r, 182 a.x ^ 2.5 AS s, 183 a.x << 3 AS t, 184 a.y >> 2 AS u 185 FROM a 186 ---- 187 project 188 ├── columns: r:3(decimal!null) s:4(decimal!null) t:5(int!null) u:6(int) 189 ├── scan a 190 │ └── columns: x:1(int!null) y:2(int) 191 └── projections 192 ├── 100.1 % x:1 [as=r:3, type=decimal] 193 ├── x:1 ^ 2.5 [as=s:4, type=decimal] 194 ├── x:1 << 3 [as=t:5, type=int] 195 └── y:2 >> 2 [as=u:6, type=int] 196 197 # FetchVal, FetchText, FetchValPath, FetchTextPath 198 build 199 SELECT '[1, 2]'->1 AS r, 200 '[1, 2]'->>1 AS s, 201 '{"a": 5}'#>ARRAY['a'] AS t, 202 '{"a": 5}'#>>ARRAY['a'] AS u 203 FROM a 204 ---- 205 project 206 ├── columns: r:3(jsonb) s:4(string) t:5(jsonb) u:6(string) 207 ├── scan a 208 │ └── columns: x:1(int!null) y:2(int) 209 └── projections 210 ├── '[1, 2]'->1 [as=r:3, type=jsonb] 211 ├── '[1, 2]'->>1 [as=s:4, type=string] 212 ├── '{"a": 5}'#>ARRAY['a'] [as=t:5, type=jsonb] 213 └── '{"a": 5}'#>>ARRAY['a'] [as=u:6, type=string] 214 215 # Concat 216 build 217 SELECT b.x || 'more' AS r FROM b 218 ---- 219 project 220 ├── columns: r:3(string!null) 221 ├── scan b 222 │ └── columns: x:1(string!null) z:2(decimal!null) 223 └── projections 224 └── x:1 || 'more' [as=r:3, type=string] 225 226 # UnaryMinus, UnaryComplement 227 build 228 SELECT -a.y AS r, ~a.x AS s FROM a 229 ---- 230 project 231 ├── columns: r:3(int) s:4(int) 232 ├── scan a 233 │ └── columns: x:1(int!null) y:2(int) 234 └── projections 235 ├── -y:2 [as=r:3, type=int] 236 └── ~x:1 [as=s:4, type=int] 237 238 # Array Concat 239 build 240 SELECT arr || arr AS r, arr || NULL AS s, NULL || arr AS t FROM unusual 241 ---- 242 project 243 ├── columns: r:3(int[]) s:4(int[]) t:5(int[]) 244 ├── scan unusual 245 │ └── columns: x:1(int!null) arr:2(int[]) 246 └── projections 247 ├── arr:2 || arr:2 [as=r:3, type=int[]] 248 ├── arr:2 || NULL::INT8[] [as=s:4, type=int[]] 249 └── NULL::INT8[] || arr:2 [as=t:5, type=int[]] 250 251 # Array Element Concat 252 build 253 SELECT x || arr AS r, arr || x AS s, x || NULL AS t, NULL || x AS u FROM unusual 254 ---- 255 project 256 ├── columns: r:3(int[]) s:4(int[]) t:5(int[]) u:6(int[]) 257 ├── scan unusual 258 │ └── columns: x:1(int!null) arr:2(int[]) 259 └── projections 260 ├── x:1 || arr:2 [as=r:3, type=int[]] 261 ├── arr:2 || x:1 [as=s:4, type=int[]] 262 ├── x:1 || NULL::INT8[] [as=t:5, type=int[]] 263 └── NULL::INT8[] || x:1 [as=u:6, type=int[]] 264 265 # Function with fixed return type. 266 build 267 SELECT length('text') 268 ---- 269 project 270 ├── columns: length:1(int) 271 ├── values 272 │ └── () [type=tuple] 273 └── projections 274 └── length('text') [as=length:1, type=int] 275 276 # Function with return type dependent on arg types. 277 build 278 SELECT div(1.0, 2.0) 279 ---- 280 project 281 ├── columns: div:1(decimal) 282 ├── values 283 │ └── () [type=tuple] 284 └── projections 285 └── div(1.0, 2.0) [as=div:1, type=decimal] 286 287 # Function with same arguments in multiple overloads. 288 build 289 SELECT now() 290 ---- 291 project 292 ├── columns: now:1(timestamptz) 293 ├── values 294 │ └── () [type=tuple] 295 └── projections 296 └── now() [as=now:1, type=timestamptz] 297 298 # Variadic function. 299 build 300 SELECT greatest(1, 2, 3, 4) 301 ---- 302 project 303 ├── columns: greatest:1(int) 304 ├── values 305 │ └── () [type=tuple] 306 └── projections 307 └── greatest(1, 2, 3, 4) [as=greatest:1, type=int] 308 309 # Aggregate functions. 310 build 311 SELECT 312 array_agg(z), avg(z), bool_and(z=0), bool_or(z=0), concat_agg(x), count(z), 313 count(*), max(x), max(z), sum_int(x::int), sum(z), sqrdiff(z), variance(x::int), 314 stddev(z), xor_agg(x::int), json_agg(x::json), jsonb_agg(x::jsonb) 315 FROM b 316 ---- 317 scalar-group-by 318 ├── columns: array_agg:3(decimal[]) avg:4(decimal) bool_and:6(bool) bool_or:7(bool) concat_agg:8(string) count:9(int!null) count:10(int!null) max:11(string) max:12(decimal) sum_int:14(int) sum:15(decimal) sqrdiff:16(decimal) variance:17(decimal) stddev:18(decimal) xor_agg:19(int) json_agg:21(jsonb) jsonb_agg:22(jsonb) 319 ├── project 320 │ ├── columns: column5:5(bool!null) column13:13(int!null) column20:20(jsonb!null) x:1(string!null) z:2(decimal!null) 321 │ ├── scan b 322 │ │ └── columns: x:1(string!null) z:2(decimal!null) 323 │ └── projections 324 │ ├── z:2 = 0 [as=column5:5, type=bool] 325 │ ├── x:1::INT8 [as=column13:13, type=int] 326 │ └── x:1::JSONB [as=column20:20, type=jsonb] 327 └── aggregations 328 ├── array-agg [as=array_agg:3, type=decimal[]] 329 │ └── z:2 [type=decimal] 330 ├── avg [as=avg:4, type=decimal] 331 │ └── z:2 [type=decimal] 332 ├── bool-and [as=bool_and:6, type=bool] 333 │ └── column5:5 [type=bool] 334 ├── bool-or [as=bool_or:7, type=bool] 335 │ └── column5:5 [type=bool] 336 ├── concat-agg [as=concat_agg:8, type=string] 337 │ └── x:1 [type=string] 338 ├── count [as=count:9, type=int] 339 │ └── z:2 [type=decimal] 340 ├── count-rows [as=count_rows:10, type=int] 341 ├── max [as=max:11, type=string] 342 │ └── x:1 [type=string] 343 ├── max [as=max:12, type=decimal] 344 │ └── z:2 [type=decimal] 345 ├── sum-int [as=sum_int:14, type=int] 346 │ └── column13:13 [type=int] 347 ├── sum [as=sum:15, type=decimal] 348 │ └── z:2 [type=decimal] 349 ├── sqr-diff [as=sqrdiff:16, type=decimal] 350 │ └── z:2 [type=decimal] 351 ├── variance [as=variance:17, type=decimal] 352 │ └── column13:13 [type=int] 353 ├── std-dev [as=stddev:18, type=decimal] 354 │ └── z:2 [type=decimal] 355 ├── xor-agg [as=xor_agg:19, type=int] 356 │ └── column13:13 [type=int] 357 ├── json-agg [as=json_agg:21, type=jsonb] 358 │ └── column20:20 [type=jsonb] 359 └── jsonb-agg [as=jsonb_agg:22, type=jsonb] 360 └── column20:20 [type=jsonb] 361 362 # ConstAgg internal aggregate function. 363 opt 364 SELECT * FROM (SELECT x, x::string, y FROM a) WHERE (SELECT max(x) FROM b WHERE y=z::int) > 'foo' 365 ---- 366 project 367 ├── columns: x:1(int!null) x:3(string!null) y:2(int!null) 368 ├── select 369 │ ├── columns: a.x:1(int!null) y:2(int!null) max:6(string!null) 370 │ ├── group-by 371 │ │ ├── columns: a.x:1(int!null) y:2(int!null) max:6(string!null) 372 │ │ ├── grouping columns: a.x:1(int!null) 373 │ │ ├── inner-join (hash) 374 │ │ │ ├── columns: a.x:1(int!null) y:2(int!null) b.x:4(string!null) column7:7(int!null) 375 │ │ │ ├── scan a 376 │ │ │ │ └── columns: a.x:1(int!null) y:2(int) 377 │ │ │ ├── project 378 │ │ │ │ ├── columns: column7:7(int!null) b.x:4(string!null) 379 │ │ │ │ ├── scan b 380 │ │ │ │ │ └── columns: b.x:4(string!null) z:5(decimal!null) 381 │ │ │ │ └── projections 382 │ │ │ │ └── z:5::INT8 [as=column7:7, type=int] 383 │ │ │ └── filters 384 │ │ │ └── y:2 = column7:7 [type=bool] 385 │ │ └── aggregations 386 │ │ ├── max [as=max:6, type=string] 387 │ │ │ └── b.x:4 [type=string] 388 │ │ └── const-agg [as=y:2, type=int] 389 │ │ └── y:2 [type=int] 390 │ └── filters 391 │ └── max:6 > 'foo' [type=bool] 392 └── projections 393 └── a.x:1::STRING [as=x:3, type=string] 394 395 # ConstNotNullAgg internal aggregate function. 396 opt 397 SELECT EXISTS(SELECT * FROM a WHERE expr<0) FROM (SELECT x+1 AS expr FROM a) 398 ---- 399 project 400 ├── columns: exists:6(bool!null) 401 ├── group-by 402 │ ├── columns: true_agg:8(bool) rownum:10(int!null) 403 │ ├── grouping columns: rownum:10(int!null) 404 │ ├── left-join (cross) 405 │ │ ├── columns: expr:3(int!null) true:7(bool) rownum:10(int!null) 406 │ │ ├── ordinality 407 │ │ │ ├── columns: expr:3(int!null) rownum:10(int!null) 408 │ │ │ └── project 409 │ │ │ ├── columns: expr:3(int!null) 410 │ │ │ ├── scan a 411 │ │ │ │ └── columns: x:1(int!null) 412 │ │ │ └── projections 413 │ │ │ └── x:1 + 1 [as=expr:3, type=int] 414 │ │ ├── project 415 │ │ │ ├── columns: true:7(bool!null) 416 │ │ │ ├── scan a 417 │ │ │ └── projections 418 │ │ │ └── true [as=true:7, type=bool] 419 │ │ └── filters 420 │ │ └── expr:3 < 0 [type=bool] 421 │ └── aggregations 422 │ └── const-not-null-agg [as=true_agg:8, type=bool] 423 │ └── true:7 [type=bool] 424 └── projections 425 └── true_agg:8 IS NOT NULL [as=exists:6, type=bool] 426 427 # Cast 428 build 429 SELECT x::VARCHAR(2) FROM b 430 ---- 431 project 432 ├── columns: x:3(varchar!null) 433 ├── scan b 434 │ └── columns: b.x:1(string!null) z:2(decimal!null) 435 └── projections 436 └── b.x:1::VARCHAR(2) [as=x:3, type=varchar] 437 438 # Cast same type with different precisions. 439 # See #42571. 440 build 441 SELECT z::decimal(10, 3), z::decimal(10, 1), z::decimal(10, 4) FROM b 442 ---- 443 project 444 ├── columns: z:3(decimal!null) z:4(decimal!null) z:5(decimal!null) 445 ├── scan b 446 │ └── columns: x:1(string!null) b.z:2(decimal!null) 447 └── projections 448 ├── b.z:2::DECIMAL(10,3) [as=z:3, type=decimal] 449 ├── b.z:2::DECIMAL(10,1) [as=z:4, type=decimal] 450 └── b.z:2::DECIMAL(10,4) [as=z:5, type=decimal]