github.com/dolthub/go-mysql-server@v0.18.0/sql/planbuilder/parse_test.go (about) 1 // Copyright 2023 Dolthub, Inc. 2 // 3 // Licensed under the Apache License, Version 2.0 (the "License"); 4 // you may not use this file except in compliance with the License. 5 // You may obtain a copy of the License at 6 // 7 // http://www.apache.org/licenses/LICENSE-2.0 8 // 9 // Unless required by applicable law or agreed to in writing, software 10 // distributed under the License is distributed on an "AS IS" BASIS, 11 // WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. 12 // See the License for the specific language governing permissions and 13 // limitations under the License. 14 15 package planbuilder 16 17 import ( 18 "bufio" 19 "context" 20 "fmt" 21 "os" 22 "path/filepath" 23 "strings" 24 "testing" 25 26 "github.com/dolthub/vitess/go/sqltypes" 27 "github.com/dolthub/vitess/go/vt/sqlparser" 28 "github.com/stretchr/testify/require" 29 30 "github.com/dolthub/go-mysql-server/memory" 31 "github.com/dolthub/go-mysql-server/sql" 32 "github.com/dolthub/go-mysql-server/sql/expression/function" 33 "github.com/dolthub/go-mysql-server/sql/types" 34 ) 35 36 type planTest struct { 37 Query string 38 ExpectedPlan string 39 Skip bool 40 } 41 42 type planErrTest struct { 43 Query string 44 Err string 45 Skip bool 46 } 47 48 func TestPlanBuilder(t *testing.T) { 49 var verbose, rewrite bool 50 //verbose = true 51 //rewrite = true 52 53 var tests = []planTest{ 54 { 55 Query: "select 0 as col1, 1 as col2, 2 as col2 group by col2 having col2 = 1", 56 ExpectedPlan: ` 57 Project 58 ├─ columns: [0 (tinyint) as col1, 1 (tinyint) as col2, 2 (tinyint) as col2] 59 └─ Having 60 ├─ Eq 61 │ ├─ col2:2!null 62 │ └─ 1 (tinyint) 63 └─ Project 64 ├─ columns: [0 (tinyint) as col1, 1 (tinyint) as col2, 2 (tinyint) as col2] 65 └─ GroupBy 66 ├─ select: 67 ├─ group: 1 (tinyint) as col2 68 └─ Table 69 ├─ name: 70 ├─ columns: [] 71 ├─ colSet: () 72 └─ tableId: 0 73 `, 74 }, 75 { 76 Query: "with cte(x) as (select 1 as x) select 1 as x from cte having avg(x) > 0", 77 ExpectedPlan: ` 78 Project 79 ├─ columns: [1 (tinyint) as x] 80 └─ Having 81 ├─ GreaterThan 82 │ ├─ avg(cte.x):4 83 │ └─ 0 (tinyint) 84 └─ Project 85 ├─ columns: [avg(cte.x):4, cte.x:2!null, 1 (tinyint) as x] 86 └─ GroupBy 87 ├─ select: AVG(cte.x:2!null), cte.x:2!null 88 ├─ group: 89 └─ SubqueryAlias 90 ├─ name: cte 91 ├─ outerVisibility: false 92 ├─ isLateral: false 93 ├─ cacheable: true 94 ├─ colSet: (2) 95 ├─ tableId: 1 96 └─ Project 97 ├─ columns: [1 (tinyint) as x] 98 └─ Table 99 ├─ name: 100 ├─ columns: [] 101 ├─ colSet: () 102 └─ tableId: 0 103 `, 104 }, 105 { 106 Query: "select 1 as x from xy having AVG(x) > 0", 107 ExpectedPlan: ` 108 Project 109 ├─ columns: [1 (tinyint) as x] 110 └─ Having 111 ├─ GreaterThan 112 │ ├─ avg(xy.x):5 113 │ └─ 0 (tinyint) 114 └─ Project 115 ├─ columns: [avg(xy.x):5, xy.x:1!null, 1 (tinyint) as x] 116 └─ GroupBy 117 ├─ select: AVG(xy.x:1!null), xy.x:1!null 118 ├─ group: 119 └─ Table 120 ├─ name: xy 121 ├─ columns: [x y z] 122 ├─ colSet: (1-3) 123 └─ tableId: 1 124 `, 125 }, 126 { 127 Query: "select x as x from xy having avg(x) > 0", 128 ExpectedPlan: ` 129 Project 130 ├─ columns: [xy.x:1!null as x] 131 └─ Having 132 ├─ GreaterThan 133 │ ├─ avg(xy.x):5 134 │ └─ 0 (tinyint) 135 └─ Project 136 ├─ columns: [avg(xy.x):5, xy.x:1!null, xy.x:1!null as x] 137 └─ GroupBy 138 ├─ select: AVG(xy.x:1!null), xy.x:1!null 139 ├─ group: 140 └─ Table 141 ├─ name: xy 142 ├─ columns: [x y z] 143 ├─ colSet: (1-3) 144 └─ tableId: 1 145 `, 146 }, 147 { 148 Query: "select x, x from xy order by x", 149 ExpectedPlan: ` 150 Project 151 ├─ columns: [xy.x:1!null, xy.x:1!null] 152 └─ Sort(xy.x:1!null ASC nullsFirst) 153 └─ Table 154 ├─ name: xy 155 ├─ columns: [x y z] 156 ├─ colSet: (1-3) 157 └─ tableId: 1 158 `, 159 }, 160 { 161 Query: "select t1.x as x, t1.x as x from xy t1, xy t2 order by x;", 162 ExpectedPlan: ` 163 Project 164 ├─ columns: [t1.x:1!null as x, t1.x:1!null as x] 165 └─ Sort(t1.x:1!null as x ASC nullsFirst) 166 └─ Project 167 ├─ columns: [t1.x:1!null, t1.y:2!null, t1.z:3!null, t2.x:4!null, t2.y:5!null, t2.z:6!null, t1.x:1!null as x, t1.x:1!null as x] 168 └─ CrossJoin 169 ├─ TableAlias(t1) 170 │ └─ Table 171 │ ├─ name: xy 172 │ ├─ columns: [x y z] 173 │ ├─ colSet: (1-3) 174 │ └─ tableId: 1 175 └─ TableAlias(t2) 176 └─ Table 177 ├─ name: xy 178 ├─ columns: [x y z] 179 ├─ colSet: (4-6) 180 └─ tableId: 2 181 `, 182 }, 183 { 184 Query: ` 185 analyze table xy 186 update histogram on (x, y) using data '{"row_count": 40, "distinct_count": 40, "null_count": 1, "columns": ["x", "y"], "histogram": [{"row_count": 20, "upper_bound": [50.0]}, {"row_count": 20, "upper_bound": [80.0]}]}'`, 187 ExpectedPlan: ` 188 update histogram xy.(x,y) using {"statistic":{"avg_size":0,"buckets":[],"columns":["x","y"],"created_at":"0001-01-01T00:00:00Z","distinct_count":40,"null_count":40,"qualifier":"mydb.xy.primary","row_count":40,"types:":["bigint","bigint"]}}`, 189 }, 190 { 191 Query: "SELECT b.y as s1, a.y as s2, first_value(a.z) over (partition by a.y) from xy a join xy b on a.y = b.y", 192 ExpectedPlan: ` 193 Project 194 ├─ columns: [b.y:5!null as s1, a.y:2!null as s2, first_value(a.z) over ( partition by a.y rows between unbounded preceding and unbounded following):9!null as first_value(a.z) over (partition by a.y)] 195 └─ Window 196 ├─ first_value(a.z) over ( partition by a.y ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) 197 ├─ b.y:5!null 198 ├─ a.y:2!null 199 └─ InnerJoin 200 ├─ Eq 201 │ ├─ a.y:2!null 202 │ └─ b.y:5!null 203 ├─ TableAlias(a) 204 │ └─ Table 205 │ ├─ name: xy 206 │ ├─ columns: [x y z] 207 │ ├─ colSet: (1-3) 208 │ └─ tableId: 1 209 └─ TableAlias(b) 210 └─ Table 211 ├─ name: xy 212 ├─ columns: [x y z] 213 ├─ colSet: (4-6) 214 └─ tableId: 2 215 `, 216 }, 217 { 218 Query: "select a.x, b.y as s1, a.y as s2 from xy a join xy b on a.y = b.y group by b.y", 219 ExpectedPlan: ` 220 Project 221 ├─ columns: [a.x:1!null, b.y:5!null as s1, a.y:2!null as s2] 222 └─ GroupBy 223 ├─ select: a.x:1!null, b.y:5!null, a.y:2!null 224 ├─ group: b.y:5!null 225 └─ InnerJoin 226 ├─ Eq 227 │ ├─ a.y:2!null 228 │ └─ b.y:5!null 229 ├─ TableAlias(a) 230 │ └─ Table 231 │ ├─ name: xy 232 │ ├─ columns: [x y z] 233 │ ├─ colSet: (1-3) 234 │ └─ tableId: 1 235 └─ TableAlias(b) 236 └─ Table 237 ├─ name: xy 238 ├─ columns: [x y z] 239 ├─ colSet: (4-6) 240 └─ tableId: 2 241 `, 242 }, 243 { 244 Query: "with cte(y,x) as (select x,y from xy) select * from cte", 245 ExpectedPlan: ` 246 SubqueryAlias 247 ├─ name: cte 248 ├─ outerVisibility: false 249 ├─ isLateral: false 250 ├─ cacheable: true 251 ├─ colSet: (4,5) 252 ├─ tableId: 2 253 └─ Project 254 ├─ columns: [xy.x:1!null, xy.y:2!null] 255 └─ Table 256 ├─ name: xy 257 ├─ columns: [x y z] 258 ├─ colSet: (1-3) 259 └─ tableId: 1 260 `, 261 }, 262 { 263 Query: "select * from xy where x = 2", 264 ExpectedPlan: ` 265 Project 266 ├─ columns: [xy.x:1!null, xy.y:2!null, xy.z:3!null] 267 └─ Filter 268 ├─ Eq 269 │ ├─ xy.x:1!null 270 │ └─ 2 (tinyint) 271 └─ Table 272 ├─ name: xy 273 ├─ columns: [x y z] 274 ├─ colSet: (1-3) 275 └─ tableId: 1 276 `, 277 }, 278 { 279 Query: "select xy.* from xy where x = 2", 280 ExpectedPlan: ` 281 Project 282 ├─ columns: [xy.x:1!null, xy.y:2!null, xy.z:3!null] 283 └─ Filter 284 ├─ Eq 285 │ ├─ xy.x:1!null 286 │ └─ 2 (tinyint) 287 └─ Table 288 ├─ name: xy 289 ├─ columns: [x y z] 290 ├─ colSet: (1-3) 291 └─ tableId: 1 292 `, 293 }, 294 { 295 Query: "select x, y from xy where x = 2", 296 ExpectedPlan: ` 297 Project 298 ├─ columns: [xy.x:1!null, xy.y:2!null] 299 └─ Filter 300 ├─ Eq 301 │ ├─ xy.x:1!null 302 │ └─ 2 (tinyint) 303 └─ Table 304 ├─ name: xy 305 ├─ columns: [x y z] 306 ├─ colSet: (1-3) 307 └─ tableId: 1 308 `, 309 }, 310 { 311 Query: "select x, xy.y from xy where x = 2", 312 ExpectedPlan: ` 313 Project 314 ├─ columns: [xy.x:1!null, xy.y:2!null] 315 └─ Filter 316 ├─ Eq 317 │ ├─ xy.x:1!null 318 │ └─ 2 (tinyint) 319 └─ Table 320 ├─ name: xy 321 ├─ columns: [x y z] 322 ├─ colSet: (1-3) 323 └─ tableId: 1 324 `, 325 }, 326 { 327 Query: "select x, xy.y from xy where xy.x = 2", 328 ExpectedPlan: ` 329 Project 330 ├─ columns: [xy.x:1!null, xy.y:2!null] 331 └─ Filter 332 ├─ Eq 333 │ ├─ xy.x:1!null 334 │ └─ 2 (tinyint) 335 └─ Table 336 ├─ name: xy 337 ├─ columns: [x y z] 338 ├─ colSet: (1-3) 339 └─ tableId: 1 340 `, 341 }, 342 { 343 Query: "select x, s.y from xy s where s.x = 2", 344 ExpectedPlan: ` 345 Project 346 ├─ columns: [s.x:1!null, s.y:2!null] 347 └─ Filter 348 ├─ Eq 349 │ ├─ s.x:1!null 350 │ └─ 2 (tinyint) 351 └─ TableAlias(s) 352 └─ Table 353 ├─ name: xy 354 ├─ columns: [x y z] 355 ├─ colSet: (1-3) 356 └─ tableId: 1 357 `, 358 }, 359 { 360 Query: "select x, s.y from xy s join uv on x = u where s.x = 2", 361 ExpectedPlan: ` 362 Project 363 ├─ columns: [s.x:1!null, s.y:2!null] 364 └─ Filter 365 ├─ Eq 366 │ ├─ s.x:1!null 367 │ └─ 2 (tinyint) 368 └─ InnerJoin 369 ├─ Eq 370 │ ├─ s.x:1!null 371 │ └─ uv.u:4!null 372 ├─ TableAlias(s) 373 │ └─ Table 374 │ ├─ name: xy 375 │ ├─ columns: [x y z] 376 │ ├─ colSet: (1-3) 377 │ └─ tableId: 1 378 └─ Table 379 ├─ name: uv 380 ├─ columns: [u v w] 381 ├─ colSet: (4-6) 382 └─ tableId: 2 383 `, 384 }, 385 { 386 Query: "select y as x from xy", 387 ExpectedPlan: ` 388 Project 389 ├─ columns: [xy.y:2!null as x] 390 └─ Table 391 ├─ name: xy 392 ├─ columns: [x y z] 393 ├─ colSet: (1-3) 394 └─ tableId: 1 395 `, 396 }, 397 { 398 Query: "select * from xy join (select * from uv) s on x = u", 399 ExpectedPlan: ` 400 Project 401 ├─ columns: [xy.x:1!null, xy.y:2!null, xy.z:3!null, s.u:7!null, s.v:8!null, s.w:9!null] 402 └─ InnerJoin 403 ├─ Eq 404 │ ├─ xy.x:1!null 405 │ └─ s.u:7!null 406 ├─ Table 407 │ ├─ name: xy 408 │ ├─ columns: [x y z] 409 │ ├─ colSet: (1-3) 410 │ └─ tableId: 1 411 └─ SubqueryAlias 412 ├─ name: s 413 ├─ outerVisibility: false 414 ├─ isLateral: false 415 ├─ cacheable: true 416 ├─ colSet: (7-9) 417 ├─ tableId: 3 418 └─ Project 419 ├─ columns: [uv.u:4!null, uv.v:5!null, uv.w:6!null] 420 └─ Table 421 ├─ name: uv 422 ├─ columns: [u v w] 423 ├─ colSet: (4-6) 424 └─ tableId: 2 425 `, 426 }, 427 { 428 Query: "select * from xy where x in (select u from uv where x = u)", 429 ExpectedPlan: ` 430 Project 431 ├─ columns: [xy.x:1!null, xy.y:2!null, xy.z:3!null] 432 └─ Filter 433 ├─ InSubquery 434 │ ├─ left: xy.x:1!null 435 │ └─ right: Subquery 436 │ ├─ cacheable: false 437 │ ├─ alias-string: select u from uv where x = u 438 │ └─ Project 439 │ ├─ columns: [uv.u:4!null] 440 │ └─ Filter 441 │ ├─ Eq 442 │ │ ├─ xy.x:1!null 443 │ │ └─ uv.u:4!null 444 │ └─ Table 445 │ ├─ name: uv 446 │ ├─ columns: [u v w] 447 │ ├─ colSet: (4-6) 448 │ └─ tableId: 2 449 └─ Table 450 ├─ name: xy 451 ├─ columns: [x y z] 452 ├─ colSet: (1-3) 453 └─ tableId: 1 454 `, 455 }, 456 { 457 Query: "with cte as (select 1) select * from cte", 458 ExpectedPlan: ` 459 SubqueryAlias 460 ├─ name: cte 461 ├─ outerVisibility: false 462 ├─ isLateral: false 463 ├─ cacheable: true 464 ├─ colSet: (2) 465 ├─ tableId: 1 466 └─ Project 467 ├─ columns: [1 (tinyint)] 468 └─ Table 469 ├─ name: 470 ├─ columns: [] 471 ├─ colSet: () 472 └─ tableId: 0 473 `, 474 }, 475 { 476 Query: "with recursive cte(s) as (select x from xy union select s from cte join xy on y = s) select * from cte", 477 ExpectedPlan: ` 478 SubqueryAlias 479 ├─ name: cte 480 ├─ outerVisibility: false 481 ├─ isLateral: false 482 ├─ cacheable: true 483 ├─ colSet: (4) 484 ├─ tableId: 2 485 └─ RecursiveCTE 486 └─ Union distinct 487 ├─ Project 488 │ ├─ columns: [xy.x:1!null] 489 │ └─ Table 490 │ ├─ name: xy 491 │ ├─ columns: [x y z] 492 │ ├─ colSet: (1-3) 493 │ └─ tableId: 1 494 └─ Project 495 ├─ columns: [cte.s:4!null] 496 └─ InnerJoin 497 ├─ Eq 498 │ ├─ xy.y:6!null 499 │ └─ cte.s:4!null 500 ├─ RecursiveTable(cte) 501 └─ Table 502 ├─ name: xy 503 ├─ columns: [x y z] 504 ├─ colSet: (5-7) 505 └─ tableId: 4 506 `, 507 }, 508 { 509 Query: "select x, sum(y) from xy group by x order by x - count(y)", 510 ExpectedPlan: ` 511 Project 512 ├─ columns: [xy.x:1!null, sum(xy.y):4!null as sum(y)] 513 └─ Sort((xy.x:1!null - count(xy.y):5!null) ASC nullsFirst) 514 └─ GroupBy 515 ├─ select: COUNT(xy.y:2!null), SUM(xy.y:2!null), xy.x:1!null 516 ├─ group: xy.x:1!null 517 └─ Table 518 ├─ name: xy 519 ├─ columns: [x y z] 520 ├─ colSet: (1-3) 521 └─ tableId: 1 522 `, 523 }, 524 { 525 Query: "select sum(x) from xy group by x order by y", 526 ExpectedPlan: ` 527 Project 528 ├─ columns: [sum(xy.x):4!null as sum(x)] 529 └─ Sort(xy.y:2!null ASC nullsFirst) 530 └─ GroupBy 531 ├─ select: SUM(xy.x:1!null), xy.y:2!null 532 ├─ group: xy.x:1!null 533 └─ Table 534 ├─ name: xy 535 ├─ columns: [x y z] 536 ├─ colSet: (1-3) 537 └─ tableId: 1 538 `, 539 }, 540 { 541 Query: "SELECT y, count(x) FROM xy GROUP BY y ORDER BY count(x) DESC", 542 ExpectedPlan: ` 543 Project 544 ├─ columns: [xy.y:2!null, count(xy.x):4!null as count(x)] 545 └─ Sort(count(xy.x):4!null DESC nullsFirst) 546 └─ GroupBy 547 ├─ select: COUNT(xy.x:1!null), xy.y:2!null 548 ├─ group: xy.y:2!null 549 └─ Table 550 ├─ name: xy 551 ├─ columns: [x y z] 552 ├─ colSet: (1-3) 553 └─ tableId: 1 554 `, 555 }, 556 { 557 Query: "select count(x) from xy", 558 ExpectedPlan: ` 559 Project 560 ├─ columns: [count(xy.x):4!null as count(x)] 561 └─ GroupBy 562 ├─ select: COUNT(xy.x:1!null) 563 ├─ group: 564 └─ Table 565 ├─ name: xy 566 ├─ columns: [x y z] 567 ├─ colSet: (1-3) 568 └─ tableId: 1 569 `, 570 }, 571 { 572 Query: "SELECT y, count(x) FROM xy GROUP BY y ORDER BY y DESC", 573 ExpectedPlan: ` 574 Project 575 ├─ columns: [xy.y:2!null, count(xy.x):4!null as count(x)] 576 └─ Sort(xy.y:2!null DESC nullsFirst) 577 └─ GroupBy 578 ├─ select: COUNT(xy.x:1!null), xy.y:2!null 579 ├─ group: xy.y:2!null 580 └─ Table 581 ├─ name: xy 582 ├─ columns: [x y z] 583 ├─ colSet: (1-3) 584 └─ tableId: 1 585 `, 586 }, 587 { 588 Query: "SELECT y, count(x) FROM xy GROUP BY y ORDER BY y", 589 ExpectedPlan: ` 590 Project 591 ├─ columns: [xy.y:2!null, count(xy.x):4!null as count(x)] 592 └─ Sort(xy.y:2!null ASC nullsFirst) 593 └─ GroupBy 594 ├─ select: COUNT(xy.x:1!null), xy.y:2!null 595 ├─ group: xy.y:2!null 596 └─ Table 597 ├─ name: xy 598 ├─ columns: [x y z] 599 ├─ colSet: (1-3) 600 └─ tableId: 1 601 `, 602 }, 603 { 604 Query: "SELECT count(xy.x) AS count_1, xy.y + xy.z AS lx FROM xy GROUP BY xy.x + xy.z", 605 ExpectedPlan: ` 606 Project 607 ├─ columns: [count(xy.x):4!null as count_1, (xy.y:2!null + xy.z:3!null) as lx] 608 └─ GroupBy 609 ├─ select: COUNT(xy.x:1!null), xy.y:2!null, xy.z:3!null 610 ├─ group: (xy.x:1!null + xy.z:3!null) 611 └─ Table 612 ├─ name: xy 613 ├─ columns: [x y z] 614 ├─ colSet: (1-3) 615 └─ tableId: 1 616 `, 617 }, 618 { 619 Query: "SELECT count(xy.x) AS count_1, xy.x + xy.z AS lx FROM xy GROUP BY xy.x + xy.z", 620 ExpectedPlan: ` 621 Project 622 ├─ columns: [count(xy.x):4!null as count_1, (xy.x:1!null + xy.z:3!null) as lx] 623 └─ GroupBy 624 ├─ select: COUNT(xy.x:1!null), xy.x:1!null, xy.z:3!null 625 ├─ group: (xy.x:1!null + xy.z:3!null) 626 └─ Table 627 ├─ name: xy 628 ├─ columns: [x y z] 629 ├─ colSet: (1-3) 630 └─ tableId: 1 631 `, 632 }, 633 { 634 Query: "select x from xy order by z", 635 ExpectedPlan: ` 636 Project 637 ├─ columns: [xy.x:1!null] 638 └─ Sort(xy.z:3!null ASC nullsFirst) 639 └─ Table 640 ├─ name: xy 641 ├─ columns: [x y z] 642 ├─ colSet: (1-3) 643 └─ tableId: 1 644 `, 645 }, 646 { 647 Query: "select count(*) from (select count(*) from xy) dt", 648 ExpectedPlan: ` 649 Project 650 ├─ columns: [count(1):6!null as count(*)] 651 └─ GroupBy 652 ├─ select: COUNT(1 (bigint)) 653 ├─ group: 654 └─ SubqueryAlias 655 ├─ name: dt 656 ├─ outerVisibility: false 657 ├─ isLateral: false 658 ├─ cacheable: true 659 ├─ colSet: (5) 660 ├─ tableId: 2 661 └─ Project 662 ├─ columns: [count(1):4!null as count(*)] 663 └─ GroupBy 664 ├─ select: COUNT(1 (bigint)) 665 ├─ group: 666 └─ Table 667 ├─ name: xy 668 ├─ columns: [x y z] 669 ├─ colSet: (1-3) 670 └─ tableId: 1 671 `, 672 }, 673 { 674 Query: "select s from (select count(*) as s from xy) dt;", 675 ExpectedPlan: ` 676 SubqueryAlias 677 ├─ name: dt 678 ├─ outerVisibility: false 679 ├─ isLateral: false 680 ├─ cacheable: true 681 ├─ colSet: (6) 682 ├─ tableId: 2 683 └─ Project 684 ├─ columns: [count(1):4!null as s] 685 └─ GroupBy 686 ├─ select: COUNT(1 (bigint)) 687 ├─ group: 688 └─ Table 689 ├─ name: xy 690 ├─ columns: [x y z] 691 ├─ colSet: (1-3) 692 └─ tableId: 1 693 `, 694 }, 695 { 696 Query: "SELECT count(*), x+y AS r FROM xy GROUP BY x, y", 697 ExpectedPlan: ` 698 Project 699 ├─ columns: [count(1):4!null as count(*), (xy.x:1!null + xy.y:2!null) as r] 700 └─ GroupBy 701 ├─ select: COUNT(1 (bigint)), xy.x:1!null, xy.y:2!null 702 ├─ group: xy.x:1!null, xy.y:2!null 703 └─ Table 704 ├─ name: xy 705 ├─ columns: [x y z] 706 ├─ colSet: (1-3) 707 └─ tableId: 1 708 `, 709 }, 710 { 711 Query: "SELECT count(*), x+y AS r FROM xy GROUP BY x+y", 712 ExpectedPlan: ` 713 Project 714 ├─ columns: [count(1):4!null as count(*), (xy.x:1!null + xy.y:2!null) as r] 715 └─ GroupBy 716 ├─ select: COUNT(1 (bigint)), xy.x:1!null, xy.y:2!null 717 ├─ group: (xy.x:1!null + xy.y:2!null) 718 └─ Table 719 ├─ name: xy 720 ├─ columns: [x y z] 721 ├─ colSet: (1-3) 722 └─ tableId: 1 723 `, 724 }, 725 { 726 Query: "SELECT count(*) FROM xy GROUP BY 1+2", 727 ExpectedPlan: ` 728 Project 729 ├─ columns: [count(1):4!null as count(*)] 730 └─ GroupBy 731 ├─ select: COUNT(1 (bigint)) 732 ├─ group: (1 (tinyint) + 2 (tinyint)) 733 └─ Table 734 ├─ name: xy 735 ├─ columns: [x y z] 736 ├─ colSet: (1-3) 737 └─ tableId: 1 738 `, 739 }, 740 { 741 Query: "SELECT count(*), upper(x) FROM xy GROUP BY upper(x)", 742 ExpectedPlan: ` 743 Project 744 ├─ columns: [count(1):4!null as count(*), upper(xy.x) as upper(x)] 745 └─ GroupBy 746 ├─ select: COUNT(1 (bigint)), xy.x:1!null 747 ├─ group: upper(xy.x) 748 └─ Table 749 ├─ name: xy 750 ├─ columns: [x y z] 751 ├─ colSet: (1-3) 752 └─ tableId: 1 753 `, 754 }, 755 { 756 Query: "SELECT y, count(*), z FROM xy GROUP BY 1, 3", 757 ExpectedPlan: ` 758 Project 759 ├─ columns: [xy.y:2!null, count(1):4!null as count(*), xy.z:3!null] 760 └─ GroupBy 761 ├─ select: COUNT(1 (bigint)), xy.y:2!null, xy.z:3!null 762 ├─ group: xy.y:2!null, xy.z:3!null 763 └─ Table 764 ├─ name: xy 765 ├─ columns: [x y z] 766 ├─ colSet: (1-3) 767 └─ tableId: 1 768 `, 769 }, 770 { 771 Query: "SELECT x, sum(x) FROM xy group by 1 having avg(x) > 1 order by 1", 772 ExpectedPlan: ` 773 Project 774 ├─ columns: [xy.x:1!null, sum(xy.x):4!null as sum(x)] 775 └─ Sort(xy.x:1!null ASC nullsFirst) 776 └─ Having 777 ├─ GreaterThan 778 │ ├─ avg(xy.x):5 779 │ └─ 1 (tinyint) 780 └─ GroupBy 781 ├─ select: AVG(xy.x:1!null), SUM(xy.x:1!null), xy.x:1!null 782 ├─ group: xy.x:1!null 783 └─ Table 784 ├─ name: xy 785 ├─ columns: [x y z] 786 ├─ colSet: (1-3) 787 └─ tableId: 1 788 `, 789 }, 790 { 791 Query: "SELECT y, SUM(x) FROM xy GROUP BY y ORDER BY SUM(x) + 1 ASC", 792 ExpectedPlan: ` 793 Project 794 ├─ columns: [xy.y:2!null, sum(xy.x):4!null as SUM(x)] 795 └─ Sort((sum(xy.x):4!null + 1 (tinyint)) ASC nullsFirst) 796 └─ GroupBy 797 ├─ select: SUM(xy.x:1!null), xy.y:2!null 798 ├─ group: xy.y:2!null 799 └─ Table 800 ├─ name: xy 801 ├─ columns: [x y z] 802 ├─ colSet: (1-3) 803 └─ tableId: 1 804 `, 805 }, 806 { 807 Query: "SELECT y, SUM(x) FROM xy GROUP BY y ORDER BY COUNT(*) ASC", 808 ExpectedPlan: ` 809 Project 810 ├─ columns: [xy.y:2!null, sum(xy.x):4!null as SUM(x)] 811 └─ Sort(count(1):5!null ASC nullsFirst) 812 └─ GroupBy 813 ├─ select: COUNT(1 (bigint)), SUM(xy.x:1!null), xy.y:2!null 814 ├─ group: xy.y:2!null 815 └─ Table 816 ├─ name: xy 817 ├─ columns: [x y z] 818 ├─ colSet: (1-3) 819 └─ tableId: 1 820 `, 821 }, 822 { 823 Query: "SELECT y, SUM(x) FROM xy GROUP BY y ORDER BY SUM(x) % 2, SUM(x), AVG(x) ASC", 824 ExpectedPlan: ` 825 Project 826 ├─ columns: [xy.y:2!null, sum(xy.x):4!null as SUM(x)] 827 └─ Sort((sum(xy.x):4!null % 2 (tinyint)) ASC nullsFirst, sum(xy.x):4!null ASC nullsFirst, avg(xy.x):7 ASC nullsFirst) 828 └─ GroupBy 829 ├─ select: AVG(xy.x:1!null), SUM(xy.x:1!null), xy.y:2!null 830 ├─ group: xy.y:2!null 831 └─ Table 832 ├─ name: xy 833 ├─ columns: [x y z] 834 ├─ colSet: (1-3) 835 └─ tableId: 1 836 `, 837 }, 838 { 839 Query: "SELECT y, SUM(x) FROM xy GROUP BY y ORDER BY AVG(x) ASC", 840 ExpectedPlan: ` 841 Project 842 ├─ columns: [xy.y:2!null, sum(xy.x):4!null as SUM(x)] 843 └─ Sort(avg(xy.x):5 ASC nullsFirst) 844 └─ GroupBy 845 ├─ select: AVG(xy.x:1!null), SUM(xy.x:1!null), xy.y:2!null 846 ├─ group: xy.y:2!null 847 └─ Table 848 ├─ name: xy 849 ├─ columns: [x y z] 850 ├─ colSet: (1-3) 851 └─ tableId: 1 852 `, 853 }, 854 { 855 Query: "SELECT x, sum(x) FROM xy group by 1 having avg(x) > 1 order by 2", 856 ExpectedPlan: ` 857 Project 858 ├─ columns: [xy.x:1!null, sum(xy.x):4!null as sum(x)] 859 └─ Sort(sum(xy.x):4!null as sum(x) ASC nullsFirst) 860 └─ Having 861 ├─ GreaterThan 862 │ ├─ avg(xy.x):5 863 │ └─ 1 (tinyint) 864 └─ GroupBy 865 ├─ select: AVG(xy.x:1!null), SUM(xy.x:1!null), xy.x:1!null 866 ├─ group: xy.x:1!null 867 └─ Table 868 ├─ name: xy 869 ├─ columns: [x y z] 870 ├─ colSet: (1-3) 871 └─ tableId: 1 872 `, 873 }, 874 { 875 Query: "SELECT x, sum(y * z) FROM xy group by x having sum(y * z) > 1", 876 ExpectedPlan: ` 877 Project 878 ├─ columns: [xy.x:1!null, sum((xy.y * xy.z)):4!null as sum(y * z)] 879 └─ Having 880 ├─ GreaterThan 881 │ ├─ sum((xy.y * xy.z)):4!null 882 │ └─ 1 (tinyint) 883 └─ GroupBy 884 ├─ select: SUM((xy.y:2!null * xy.z:3!null)), xy.x:1!null, xy.y:2!null, xy.z:3!null 885 ├─ group: xy.x:1!null 886 └─ Table 887 ├─ name: xy 888 ├─ columns: [x y z] 889 ├─ colSet: (1-3) 890 └─ tableId: 1 891 `, 892 }, 893 { 894 Query: "select (select u from uv where x = u) from xy group by (select u from uv where x = u), x;", 895 ExpectedPlan: ` 896 Project 897 ├─ columns: [Subquery 898 │ ├─ cacheable: false 899 │ ├─ alias-string: select u from uv where x = u 900 │ └─ Project 901 │ ├─ columns: [uv.u:4!null] 902 │ └─ Filter 903 │ ├─ Eq 904 │ │ ├─ xy.x:1!null 905 │ │ └─ uv.u:4!null 906 │ └─ Table 907 │ ├─ name: uv 908 │ ├─ columns: [u v w] 909 │ ├─ colSet: (4-6) 910 │ └─ tableId: 2 911 │ as (select u from uv where x = u)] 912 └─ GroupBy 913 ├─ select: 914 ├─ group: Subquery 915 │ ├─ cacheable: false 916 │ ├─ alias-string: select u from uv where x = u 917 │ └─ Project 918 │ ├─ columns: [uv.u:7!null] 919 │ └─ Filter 920 │ ├─ Eq 921 │ │ ├─ xy.x:1!null 922 │ │ └─ uv.u:7!null 923 │ └─ Table 924 │ ├─ name: uv 925 │ ├─ columns: [u v w] 926 │ ├─ colSet: (7-9) 927 │ └─ tableId: 3 928 │ , xy.x:1!null 929 └─ Table 930 ├─ name: xy 931 ├─ columns: [x y z] 932 ├─ colSet: (1-3) 933 └─ tableId: 1 934 `, 935 }, 936 { 937 Query: "SELECT * FROM xy WHERE xy.y > (SELECT dt.u FROM (SELECT uv.u AS u FROM uv WHERE uv.v = xy.x) dt);", 938 ExpectedPlan: ` 939 Project 940 ├─ columns: [xy.x:1!null, xy.y:2!null, xy.z:3!null] 941 └─ Filter 942 ├─ GreaterThan 943 │ ├─ xy.y:2!null 944 │ └─ Subquery 945 │ ├─ cacheable: false 946 │ ├─ alias-string: select dt.u from (select uv.u as u from uv where uv.v = xy.x) as dt 947 │ └─ SubqueryAlias 948 │ ├─ name: dt 949 │ ├─ outerVisibility: false 950 │ ├─ isLateral: false 951 │ ├─ cacheable: false 952 │ ├─ colSet: (8) 953 │ ├─ tableId: 3 954 │ └─ Project 955 │ ├─ columns: [uv.u:4!null as u] 956 │ └─ Filter 957 │ ├─ Eq 958 │ │ ├─ uv.v:5!null 959 │ │ └─ xy.x:1!null 960 │ └─ Table 961 │ ├─ name: uv 962 │ ├─ columns: [u v w] 963 │ ├─ colSet: (4-6) 964 │ └─ tableId: 2 965 └─ Table 966 ├─ name: xy 967 ├─ columns: [x y z] 968 ├─ colSet: (1-3) 969 └─ tableId: 1 970 `, 971 }, 972 { 973 Query: "SELECT * FROM xy HAVING xy.z > (SELECT dt.u FROM (SELECT uv.u AS u FROM uv WHERE uv.v = xy.y) dt);", 974 ExpectedPlan: ` 975 Project 976 ├─ columns: [xy.x:1!null, xy.y:2!null, xy.z:3!null] 977 └─ Having 978 ├─ GreaterThan 979 │ ├─ xy.z:3!null 980 │ └─ Subquery 981 │ ├─ cacheable: false 982 │ ├─ alias-string: select dt.u from (select uv.u as u from uv where uv.v = xy.y) as dt 983 │ └─ SubqueryAlias 984 │ ├─ name: dt 985 │ ├─ outerVisibility: false 986 │ ├─ isLateral: false 987 │ ├─ cacheable: false 988 │ ├─ colSet: (8) 989 │ ├─ tableId: 3 990 │ └─ Project 991 │ ├─ columns: [uv.u:4!null as u] 992 │ └─ Filter 993 │ ├─ Eq 994 │ │ ├─ uv.v:5!null 995 │ │ └─ xy.y:2!null 996 │ └─ Table 997 │ ├─ name: uv 998 │ ├─ columns: [u v w] 999 │ ├─ colSet: (4-6) 1000 │ └─ tableId: 2 1001 └─ Table 1002 ├─ name: xy 1003 ├─ columns: [x y z] 1004 ├─ colSet: (1-3) 1005 └─ tableId: 1 1006 `, 1007 }, 1008 { 1009 Query: "SELECT (SELECT dt.z FROM (SELECT uv.u AS z FROM uv WHERE uv.v = xy.y) dt) FROM xy;", 1010 ExpectedPlan: ` 1011 Project 1012 ├─ columns: [Subquery 1013 │ ├─ cacheable: false 1014 │ ├─ alias-string: select dt.z from (select uv.u as z from uv where uv.v = xy.y) as dt 1015 │ └─ SubqueryAlias 1016 │ ├─ name: dt 1017 │ ├─ outerVisibility: false 1018 │ ├─ isLateral: false 1019 │ ├─ cacheable: false 1020 │ ├─ colSet: (8) 1021 │ ├─ tableId: 3 1022 │ └─ Project 1023 │ ├─ columns: [uv.u:4!null as z] 1024 │ └─ Filter 1025 │ ├─ Eq 1026 │ │ ├─ uv.v:5!null 1027 │ │ └─ xy.y:2!null 1028 │ └─ Table 1029 │ ├─ name: uv 1030 │ ├─ columns: [u v w] 1031 │ ├─ colSet: (4-6) 1032 │ └─ tableId: 2 1033 │ as (SELECT dt.z FROM (SELECT uv.u AS z FROM uv WHERE uv.v = xy.y) dt)] 1034 └─ Table 1035 ├─ name: xy 1036 ├─ columns: [x y z] 1037 ├─ colSet: (1-3) 1038 └─ tableId: 1 1039 `, 1040 }, 1041 { 1042 Query: "SELECT (SELECT max(dt.z) FROM (SELECT uv.u AS z FROM uv WHERE uv.v = xy.y) dt) FROM xy;", 1043 ExpectedPlan: ` 1044 Project 1045 ├─ columns: [Subquery 1046 │ ├─ cacheable: false 1047 │ ├─ alias-string: select max(dt.z) from (select uv.u as z from uv where uv.v = xy.y) as dt 1048 │ └─ Project 1049 │ ├─ columns: [max(dt.z):9!null] 1050 │ └─ GroupBy 1051 │ ├─ select: MAX(dt.z:8!null) 1052 │ ├─ group: 1053 │ └─ SubqueryAlias 1054 │ ├─ name: dt 1055 │ ├─ outerVisibility: false 1056 │ ├─ isLateral: false 1057 │ ├─ cacheable: false 1058 │ ├─ colSet: (8) 1059 │ ├─ tableId: 3 1060 │ └─ Project 1061 │ ├─ columns: [uv.u:4!null as z] 1062 │ └─ Filter 1063 │ ├─ Eq 1064 │ │ ├─ uv.v:5!null 1065 │ │ └─ xy.y:2!null 1066 │ └─ Table 1067 │ ├─ name: uv 1068 │ ├─ columns: [u v w] 1069 │ ├─ colSet: (4-6) 1070 │ └─ tableId: 2 1071 │ as (SELECT max(dt.z) FROM (SELECT uv.u AS z FROM uv WHERE uv.v = xy.y) dt)] 1072 └─ Table 1073 ├─ name: xy 1074 ├─ columns: [x y z] 1075 ├─ colSet: (1-3) 1076 └─ tableId: 1 1077 `, 1078 }, 1079 { 1080 Query: "SELECT xy.*, (SELECT max(dt.u) FROM (SELECT uv.u AS u FROM uv WHERE uv.v = xy.y) dt) FROM xy;", 1081 ExpectedPlan: ` 1082 Project 1083 ├─ columns: [xy.x:1!null, xy.y:2!null, xy.z:3!null, Subquery 1084 │ ├─ cacheable: false 1085 │ ├─ alias-string: select max(dt.u) from (select uv.u as u from uv where uv.v = xy.y) as dt 1086 │ └─ Project 1087 │ ├─ columns: [max(dt.u):9!null] 1088 │ └─ GroupBy 1089 │ ├─ select: MAX(dt.u:8!null) 1090 │ ├─ group: 1091 │ └─ SubqueryAlias 1092 │ ├─ name: dt 1093 │ ├─ outerVisibility: false 1094 │ ├─ isLateral: false 1095 │ ├─ cacheable: false 1096 │ ├─ colSet: (8) 1097 │ ├─ tableId: 3 1098 │ └─ Project 1099 │ ├─ columns: [uv.u:4!null as u] 1100 │ └─ Filter 1101 │ ├─ Eq 1102 │ │ ├─ uv.v:5!null 1103 │ │ └─ xy.y:2!null 1104 │ └─ Table 1105 │ ├─ name: uv 1106 │ ├─ columns: [u v w] 1107 │ ├─ colSet: (4-6) 1108 │ └─ tableId: 2 1109 │ as (SELECT max(dt.u) FROM (SELECT uv.u AS u FROM uv WHERE uv.v = xy.y) dt)] 1110 └─ Table 1111 ├─ name: xy 1112 ├─ columns: [x y z] 1113 ├─ colSet: (1-3) 1114 └─ tableId: 1 1115 `, 1116 }, 1117 { 1118 Query: "select x, x as y from xy order by y", 1119 ExpectedPlan: ` 1120 Project 1121 ├─ columns: [xy.x:1!null, xy.x:1!null as y] 1122 └─ Sort(xy.x:1!null as y ASC nullsFirst) 1123 └─ Project 1124 ├─ columns: [xy.x:1!null, xy.y:2!null, xy.z:3!null, xy.x:1!null as y] 1125 └─ Table 1126 ├─ name: xy 1127 ├─ columns: [x y z] 1128 ├─ colSet: (1-3) 1129 └─ tableId: 1 1130 `, 1131 }, 1132 { 1133 Query: "select x, y as x from xy order by y", 1134 ExpectedPlan: ` 1135 Project 1136 ├─ columns: [xy.x:1!null, xy.y:2!null as x] 1137 └─ Sort(xy.y:2!null ASC nullsFirst) 1138 └─ Project 1139 ├─ columns: [xy.x:1!null, xy.y:2!null, xy.z:3!null, xy.y:2!null as x] 1140 └─ Table 1141 ├─ name: xy 1142 ├─ columns: [x y z] 1143 ├─ colSet: (1-3) 1144 └─ tableId: 1 1145 `, 1146 }, 1147 { 1148 Query: "select sum(x) as `count(x)` from xy order by `count(x)`;", 1149 ExpectedPlan: ` 1150 Project 1151 ├─ columns: [sum(xy.x):4!null as count(x)] 1152 └─ Sort(sum(xy.x):4!null as count(x) ASC nullsFirst) 1153 └─ Project 1154 ├─ columns: [sum(xy.x):4!null, sum(xy.x):4!null as count(x)] 1155 └─ GroupBy 1156 ├─ select: SUM(xy.x:1!null) 1157 ├─ group: 1158 └─ Table 1159 ├─ name: xy 1160 ├─ columns: [x y z] 1161 ├─ colSet: (1-3) 1162 └─ tableId: 1 1163 `, 1164 }, 1165 { 1166 Query: "select (1+x) s from xy group by 1 having s = 1", 1167 ExpectedPlan: ` 1168 Project 1169 ├─ columns: [(1 (tinyint) + xy.x:1!null) as s] 1170 └─ Having 1171 ├─ Eq 1172 │ ├─ s:4!null 1173 │ └─ 1 (tinyint) 1174 └─ Project 1175 ├─ columns: [xy.x:1!null, (1 (tinyint) + xy.x:1!null) as s] 1176 └─ GroupBy 1177 ├─ select: xy.x:1!null 1178 ├─ group: (1 (tinyint) + xy.x:1!null) as s 1179 └─ Table 1180 ├─ name: xy 1181 ├─ columns: [x y z] 1182 ├─ colSet: (1-3) 1183 └─ tableId: 1 1184 `, 1185 }, 1186 { 1187 Query: "select (1+x) s from xy join uv on (1+x) = (1+u) group by 1 having s = 1", 1188 ExpectedPlan: ` 1189 Project 1190 ├─ columns: [(1 (tinyint) + xy.x:1!null) as s] 1191 └─ Having 1192 ├─ Eq 1193 │ ├─ s:7!null 1194 │ └─ 1 (tinyint) 1195 └─ Project 1196 ├─ columns: [xy.x:1!null, (1 (tinyint) + xy.x:1!null) as s] 1197 └─ GroupBy 1198 ├─ select: xy.x:1!null 1199 ├─ group: (1 (tinyint) + xy.x:1!null) as s 1200 └─ InnerJoin 1201 ├─ Eq 1202 │ ├─ (1 (tinyint) + xy.x:1!null) 1203 │ └─ (1 (tinyint) + uv.u:4!null) 1204 ├─ Table 1205 │ ├─ name: xy 1206 │ ├─ columns: [x y z] 1207 │ ├─ colSet: (1-3) 1208 │ └─ tableId: 1 1209 └─ Table 1210 ├─ name: uv 1211 ├─ columns: [u v w] 1212 ├─ colSet: (4-6) 1213 └─ tableId: 2 1214 `, 1215 }, 1216 { 1217 Query: ` 1218 select 1219 x, 1220 x*y, 1221 ROW_NUMBER() OVER(PARTITION BY x) AS row_num1, 1222 sum(x) OVER(PARTITION BY y ORDER BY x) AS sum 1223 from xy`, 1224 ExpectedPlan: ` 1225 Project 1226 ├─ columns: [xy.x:1!null, (xy.x:1!null * xy.y:2!null) as x*y, row_number() over ( partition by xy.x rows between unbounded preceding and unbounded following):4!null as row_num1, sum 1227 │ ├─ over ( partition by xy.y order by xy.x asc) 1228 │ └─ xy.x 1229 │ :6!null as sum] 1230 └─ Window 1231 ├─ row_number() over ( partition by xy.x ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) 1232 ├─ SUM 1233 │ ├─ over ( partition by xy.y order by xy.x ASC) 1234 │ └─ xy.x:1!null 1235 ├─ xy.x:1!null 1236 ├─ xy.y:2!null 1237 └─ Table 1238 ├─ name: xy 1239 ├─ columns: [x y z] 1240 ├─ colSet: (1-3) 1241 └─ tableId: 1 1242 `, 1243 }, 1244 { 1245 Query: ` 1246 select 1247 x+1 as x, 1248 sum(x) OVER(PARTITION BY y ORDER BY x) AS sum 1249 from xy 1250 having x > 1;`, 1251 ExpectedPlan: ` 1252 Project 1253 ├─ columns: [(xy.x:1!null + 1 (tinyint)) as x, sum 1254 │ ├─ over ( partition by xy.y order by xy.x asc) 1255 │ └─ xy.x 1256 │ :5!null as sum] 1257 └─ Having 1258 ├─ GreaterThan 1259 │ ├─ x:4!null 1260 │ └─ 1 (tinyint) 1261 └─ Project 1262 ├─ columns: [sum 1263 │ ├─ over ( partition by xy.y order by xy.x asc) 1264 │ └─ xy.x 1265 │ :5!null, xy.x:1!null, (xy.x:1!null + 1 (tinyint)) as x, sum 1266 │ ├─ over ( partition by xy.y order by xy.x asc) 1267 │ └─ xy.x 1268 │ :5!null as sum] 1269 └─ Window 1270 ├─ SUM 1271 │ ├─ over ( partition by xy.y order by xy.x ASC) 1272 │ └─ xy.x:1!null 1273 ├─ xy.x:1!null 1274 └─ Table 1275 ├─ name: xy 1276 ├─ columns: [x y z] 1277 ├─ colSet: (1-3) 1278 └─ tableId: 1 1279 `, 1280 }, 1281 { 1282 Query: ` 1283 SELECT 1284 x, 1285 ROW_NUMBER() OVER w AS 'row_number', 1286 RANK() OVER w AS 'rank', 1287 DENSE_RANK() OVER w AS 'dense_rank' 1288 FROM xy 1289 WINDOW w AS (PARTITION BY y ORDER BY x);`, 1290 ExpectedPlan: ` 1291 Project 1292 ├─ columns: [xy.x:1!null, row_number() over ( partition by xy.y order by xy.x asc rows between unbounded preceding and unbounded following):4!null as row_number, rank() over ( partition by xy.y order by xy.x asc rows between unbounded preceding and unbounded following):6!null as rank, dense_rank() over ( partition by xy.y order by xy.x asc rows between unbounded preceding and unbounded following):8!null as dense_rank] 1293 └─ Window 1294 ├─ row_number() over ( partition by xy.y order by xy.x ASC ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) 1295 ├─ rank() over ( partition by xy.y order by xy.x ASC ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) 1296 ├─ dense_rank() over ( partition by xy.y order by xy.x ASC ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) 1297 ├─ xy.x:1!null 1298 └─ Table 1299 ├─ name: xy 1300 ├─ columns: [x y z] 1301 ├─ colSet: (1-3) 1302 └─ tableId: 1 1303 `, 1304 }, 1305 { 1306 Query: "select x, row_number() over (w3) from xy window w1 as (w2), w2 as (), w3 as (w1)", 1307 ExpectedPlan: ` 1308 Project 1309 ├─ columns: [xy.x:1!null, row_number() over ( rows between unbounded preceding and unbounded following):4!null as row_number() over (w3)] 1310 └─ Window 1311 ├─ row_number() over ( ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) 1312 ├─ xy.x:1!null 1313 └─ Table 1314 ├─ name: xy 1315 ├─ columns: [x y z] 1316 ├─ colSet: (1-3) 1317 └─ tableId: 1 1318 `, 1319 }, 1320 { 1321 Query: "SELECT x, first_value(z) over (partition by y) FROM xy order by x*y,x", 1322 ExpectedPlan: ` 1323 Project 1324 ├─ columns: [xy.x:1!null, first_value(xy.z) over ( partition by xy.y rows between unbounded preceding and unbounded following):4!null as first_value(z) over (partition by y)] 1325 └─ Sort((xy.x:1!null * xy.y:2!null) ASC nullsFirst, xy.x:1!null ASC nullsFirst) 1326 └─ Window 1327 ├─ first_value(xy.z) over ( partition by xy.y ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) 1328 ├─ xy.x:1!null 1329 ├─ xy.y:2!null 1330 └─ Table 1331 ├─ name: xy 1332 ├─ columns: [x y z] 1333 ├─ colSet: (1-3) 1334 └─ tableId: 1 1335 `, 1336 }, 1337 { 1338 Query: "SELECT x, avg(x) FROM xy group by x order by sum(x)", 1339 ExpectedPlan: ` 1340 Project 1341 ├─ columns: [xy.x:1!null, avg(xy.x):4 as avg(x)] 1342 └─ Sort(sum(xy.x):5!null ASC nullsFirst) 1343 └─ GroupBy 1344 ├─ select: AVG(xy.x:1!null), SUM(xy.x:1!null), xy.x:1!null 1345 ├─ group: xy.x:1!null 1346 └─ Table 1347 ├─ name: xy 1348 ├─ columns: [x y z] 1349 ├─ colSet: (1-3) 1350 └─ tableId: 1 1351 `, 1352 }, 1353 { 1354 Query: "SELECT x, avg(x) FROM xy group by x order by avg(x)", 1355 ExpectedPlan: ` 1356 Project 1357 ├─ columns: [xy.x:1!null, avg(xy.x):4 as avg(x)] 1358 └─ Sort(avg(xy.x):4 ASC nullsFirst) 1359 └─ GroupBy 1360 ├─ select: AVG(xy.x:1!null), xy.x:1!null 1361 ├─ group: xy.x:1!null 1362 └─ Table 1363 ├─ name: xy 1364 ├─ columns: [x y z] 1365 ├─ colSet: (1-3) 1366 └─ tableId: 1 1367 `, 1368 }, 1369 { 1370 Query: "SELECT x, avg(x) FROM xy group by x order by avg(y)", 1371 ExpectedPlan: ` 1372 Project 1373 ├─ columns: [xy.x:1!null, avg(xy.x):4 as avg(x)] 1374 └─ Sort(avg(xy.y):5 ASC nullsFirst) 1375 └─ GroupBy 1376 ├─ select: AVG(xy.x:1!null), AVG(xy.y:2!null), xy.x:1!null 1377 ├─ group: xy.x:1!null 1378 └─ Table 1379 ├─ name: xy 1380 ├─ columns: [x y z] 1381 ├─ colSet: (1-3) 1382 └─ tableId: 1 1383 `, 1384 }, 1385 { 1386 Query: "SELECT x, avg(x) FROM xy group by x order by avg(y)+y", 1387 ExpectedPlan: ` 1388 Project 1389 ├─ columns: [xy.x:1!null, avg(xy.x):4 as avg(x)] 1390 └─ Sort((avg(xy.y):5 + xy.y:2!null) ASC nullsFirst) 1391 └─ GroupBy 1392 ├─ select: AVG(xy.x:1!null), AVG(xy.y:2!null), xy.x:1!null, xy.y:2!null 1393 ├─ group: xy.x:1!null 1394 └─ Table 1395 ├─ name: xy 1396 ├─ columns: [x y z] 1397 ├─ colSet: (1-3) 1398 └─ tableId: 1 1399 `, 1400 }, 1401 { 1402 Query: "SELECT x, lead(x) over (partition by y order by x) FROM xy order by x;", 1403 ExpectedPlan: ` 1404 Project 1405 ├─ columns: [xy.x:1!null, lead(xy.x, 1) over ( partition by xy.y order by xy.x asc):4 as lead(x) over (partition by y order by x)] 1406 └─ Sort(xy.x:1!null ASC nullsFirst) 1407 └─ Window 1408 ├─ lead(xy.x, 1) over ( partition by xy.y order by xy.x ASC) 1409 ├─ xy.x:1!null 1410 └─ Table 1411 ├─ name: xy 1412 ├─ columns: [x y z] 1413 ├─ colSet: (1-3) 1414 └─ tableId: 1 1415 `, 1416 }, 1417 { 1418 Query: "SELECT CAST(10.56789 as CHAR(3));", 1419 ExpectedPlan: ` 1420 Project 1421 ├─ columns: [convert 1422 │ ├─ type: char 1423 │ ├─ typeLength: 3 1424 │ └─ 10.56789 (decimal(7,5)) 1425 │ as CAST(10.56789 as CHAR(3))] 1426 └─ Table 1427 ├─ name: 1428 ├─ columns: [] 1429 ├─ colSet: () 1430 └─ tableId: 0 1431 `, 1432 }, 1433 { 1434 Query: "select x+y as X from xy where x < 1 having x > 1", 1435 ExpectedPlan: ` 1436 Project 1437 ├─ columns: [(xy.x:1!null + xy.y:2!null) as X] 1438 └─ Having 1439 ├─ GreaterThan 1440 │ ├─ x:4!null 1441 │ └─ 1 (tinyint) 1442 └─ Project 1443 ├─ columns: [xy.x:1!null, xy.y:2!null, xy.z:3!null, (xy.x:1!null + xy.y:2!null) as X] 1444 └─ Filter 1445 ├─ LessThan 1446 │ ├─ xy.x:1!null 1447 │ └─ 1 (tinyint) 1448 └─ Table 1449 ├─ name: xy 1450 ├─ columns: [x y z] 1451 ├─ colSet: (1-3) 1452 └─ tableId: 1 1453 `, 1454 }, 1455 { 1456 Query: "select x, count(*) over (order by y) from xy order by x", 1457 ExpectedPlan: ` 1458 Project 1459 ├─ columns: [xy.x:1!null, count 1460 │ ├─ over ( order by xy.y asc) 1461 │ └─ 1 1462 │ :4!null as count(*) over (order by y)] 1463 └─ Sort(xy.x:1!null ASC nullsFirst) 1464 └─ Window 1465 ├─ COUNT 1466 │ ├─ over ( order by xy.y ASC) 1467 │ └─ 1 (bigint) 1468 ├─ xy.x:1!null 1469 └─ Table 1470 ├─ name: xy 1471 ├─ columns: [x y z] 1472 ├─ colSet: (1-3) 1473 └─ tableId: 1 1474 `, 1475 }, 1476 { 1477 Query: "select x+y as s from xy having exists (select * from xy where y = s)", 1478 ExpectedPlan: ` 1479 Project 1480 ├─ columns: [(xy.x:1!null + xy.y:2!null) as s] 1481 └─ Having 1482 ├─ EXISTS Subquery 1483 │ ├─ cacheable: false 1484 │ ├─ alias-string: select * from xy where y = s 1485 │ └─ Project 1486 │ ├─ columns: [xy.x:5!null, xy.y:6!null, xy.z:7!null] 1487 │ └─ Filter 1488 │ ├─ Eq 1489 │ │ ├─ xy.y:6!null 1490 │ │ └─ s:4!null 1491 │ └─ Table 1492 │ ├─ name: xy 1493 │ ├─ columns: [x y z] 1494 │ ├─ colSet: (5-7) 1495 │ └─ tableId: 2 1496 └─ Project 1497 ├─ columns: [xy.x:1!null, xy.y:2!null, xy.z:3!null, (xy.x:1!null + xy.y:2!null) as s] 1498 └─ Table 1499 ├─ name: xy 1500 ├─ columns: [x y z] 1501 ├─ colSet: (1-3) 1502 └─ tableId: 1 1503 `, 1504 }, 1505 { 1506 Query: "select x, count(x) as cnt from xy group by x having x > 1", 1507 ExpectedPlan: ` 1508 Project 1509 ├─ columns: [xy.x:1!null, count(xy.x):4!null as cnt] 1510 └─ Having 1511 ├─ GreaterThan 1512 │ ├─ xy.x:0!null 1513 │ └─ 1 (tinyint) 1514 └─ Project 1515 ├─ columns: [count(xy.x):4!null, xy.x:1!null, count(xy.x):4!null as cnt] 1516 └─ GroupBy 1517 ├─ select: COUNT(xy.x:1!null), xy.x:1!null 1518 ├─ group: xy.x:1!null 1519 └─ Table 1520 ├─ name: xy 1521 ├─ columns: [x y z] 1522 ├─ colSet: (1-3) 1523 └─ tableId: 1 1524 `, 1525 }, 1526 { 1527 Query: ` 1528 SELECT x 1529 FROM xy 1530 WHERE EXISTS (SELECT count(u) AS count_1 1531 FROM uv 1532 WHERE y = u GROUP BY u 1533 HAVING count(u) > 1)`, 1534 ExpectedPlan: ` 1535 Project 1536 ├─ columns: [xy.x:1!null] 1537 └─ Filter 1538 ├─ EXISTS Subquery 1539 │ ├─ cacheable: false 1540 │ ├─ alias-string: select count(u) count_1 from uv where y = u group by u having count(u) > 1 1541 │ └─ Project 1542 │ ├─ columns: [count(uv.u):7!null as count_1] 1543 │ └─ Having 1544 │ ├─ GreaterThan 1545 │ │ ├─ count(uv.u):7!null 1546 │ │ └─ 1 (tinyint) 1547 │ └─ Project 1548 │ ├─ columns: [count(uv.u):7!null, uv.u:4!null, count(uv.u):7!null as count_1] 1549 │ └─ GroupBy 1550 │ ├─ select: COUNT(uv.u:4!null), uv.u:4!null 1551 │ ├─ group: uv.u:4!null 1552 │ └─ Filter 1553 │ ├─ Eq 1554 │ │ ├─ xy.y:2!null 1555 │ │ └─ uv.u:4!null 1556 │ └─ Table 1557 │ ├─ name: uv 1558 │ ├─ columns: [u v w] 1559 │ ├─ colSet: (4-6) 1560 │ └─ tableId: 2 1561 └─ Table 1562 ├─ name: xy 1563 ├─ columns: [x y z] 1564 ├─ colSet: (1-3) 1565 └─ tableId: 1 1566 `, 1567 }, 1568 { 1569 Query: ` 1570 WITH RECURSIVE 1571 rt (foo) AS ( 1572 SELECT 1 as foo 1573 UNION ALL 1574 SELECT foo + 1 as foo FROM rt WHERE foo < 5 1575 ), 1576 ladder (depth, foo) AS ( 1577 SELECT 1 as depth, NULL as foo from rt 1578 UNION ALL 1579 SELECT ladder.depth + 1 as depth, rt.foo 1580 FROM ladder JOIN rt WHERE ladder.foo = rt.foo 1581 ) 1582 SELECT * FROM ladder;`, 1583 ExpectedPlan: ` 1584 SubqueryAlias 1585 ├─ name: ladder 1586 ├─ outerVisibility: false 1587 ├─ isLateral: false 1588 ├─ cacheable: true 1589 ├─ colSet: (6,7) 1590 ├─ tableId: 4 1591 └─ RecursiveCTE 1592 └─ Union all 1593 ├─ Project 1594 │ ├─ columns: [1 (tinyint) as depth, NULL (null) as foo] 1595 │ └─ SubqueryAlias 1596 │ ├─ name: rt 1597 │ ├─ outerVisibility: false 1598 │ ├─ isLateral: false 1599 │ ├─ cacheable: true 1600 │ ├─ colSet: (2) 1601 │ ├─ tableId: 1 1602 │ └─ RecursiveCTE 1603 │ └─ Union all 1604 │ ├─ Project 1605 │ │ ├─ columns: [1 (tinyint) as foo] 1606 │ │ └─ Table 1607 │ │ ├─ name: 1608 │ │ ├─ columns: [] 1609 │ │ ├─ colSet: () 1610 │ │ └─ tableId: 0 1611 │ └─ Project 1612 │ ├─ columns: [(rt.foo:2!null + 1 (tinyint)) as foo] 1613 │ └─ Filter 1614 │ ├─ LessThan 1615 │ │ ├─ rt.foo:2!null 1616 │ │ └─ 5 (tinyint) 1617 │ └─ RecursiveTable(rt) 1618 └─ Project 1619 ├─ columns: [(ladder.depth:6!null + 1 (tinyint)) as depth, rt.foo:2!null] 1620 └─ Filter 1621 ├─ Eq 1622 │ ├─ ladder.foo:7 1623 │ └─ rt.foo:2!null 1624 └─ CrossJoin 1625 ├─ RecursiveTable(ladder) 1626 └─ SubqueryAlias 1627 ├─ name: rt 1628 ├─ outerVisibility: false 1629 ├─ isLateral: false 1630 ├─ cacheable: true 1631 ├─ colSet: (2) 1632 ├─ tableId: 1 1633 └─ RecursiveCTE 1634 └─ Union all 1635 ├─ Project 1636 │ ├─ columns: [1 (tinyint) as foo] 1637 │ └─ Table 1638 │ ├─ name: 1639 │ ├─ columns: [] 1640 │ ├─ colSet: () 1641 │ └─ tableId: 0 1642 └─ Project 1643 ├─ columns: [(rt.foo:2!null + 1 (tinyint)) as foo] 1644 └─ Filter 1645 ├─ LessThan 1646 │ ├─ rt.foo:2!null 1647 │ └─ 5 (tinyint) 1648 └─ RecursiveTable(rt) 1649 `, 1650 }, 1651 { 1652 Query: "select x as cOl, y as COL FROM xy", 1653 ExpectedPlan: ` 1654 Project 1655 ├─ columns: [xy.x:1!null as cOl, xy.y:2!null as COL] 1656 └─ Table 1657 ├─ name: xy 1658 ├─ columns: [x y z] 1659 ├─ colSet: (1-3) 1660 └─ tableId: 1 1661 `, 1662 }, 1663 { 1664 Query: "SELECT x as alias1, (SELECT alias1+1 group by alias1 having alias1 > 0) FROM xy where x > 1;", 1665 ExpectedPlan: ` 1666 Project 1667 ├─ columns: [xy.x:1!null as alias1, Subquery 1668 │ ├─ cacheable: false 1669 │ ├─ alias-string: select alias1 + 1 group by alias1 having alias1 > 0 1670 │ └─ Project 1671 │ ├─ columns: [(alias1:4!null + 1 (tinyint)) as alias1+1] 1672 │ └─ Having 1673 │ ├─ GreaterThan 1674 │ │ ├─ alias1:4!null 1675 │ │ └─ 0 (tinyint) 1676 │ └─ GroupBy 1677 │ ├─ select: alias1:4!null 1678 │ ├─ group: xy.x:1!null as alias1 1679 │ └─ Table 1680 │ ├─ name: 1681 │ ├─ columns: [] 1682 │ ├─ colSet: () 1683 │ └─ tableId: 0 1684 │ as (SELECT alias1+1 group by alias1 having alias1 > 0)] 1685 └─ Project 1686 ├─ columns: [xy.x:1!null, xy.y:2!null, xy.z:3!null, xy.x:1!null as alias1] 1687 └─ Filter 1688 ├─ GreaterThan 1689 │ ├─ xy.x:1!null 1690 │ └─ 1 (tinyint) 1691 └─ Table 1692 ├─ name: xy 1693 ├─ columns: [x y z] 1694 ├─ colSet: (1-3) 1695 └─ tableId: 1 1696 `, 1697 }, 1698 { 1699 Query: "select count(*) from xy group by x having count(*) < x", 1700 ExpectedPlan: ` 1701 Project 1702 ├─ columns: [count(1):4!null as count(*)] 1703 └─ Having 1704 ├─ LessThan 1705 │ ├─ count(1):4!null 1706 │ └─ xy.x:1!null 1707 └─ GroupBy 1708 ├─ select: COUNT(1 (bigint)), xy.x:1!null 1709 ├─ group: xy.x:1!null 1710 └─ Table 1711 ├─ name: xy 1712 ├─ columns: [x y z] 1713 ├─ colSet: (1-3) 1714 └─ tableId: 1 1715 `, 1716 }, 1717 { 1718 Query: "select - SUM(DISTINCT - - 71) as col2 from xy cor0", 1719 ExpectedPlan: ` 1720 Project 1721 ├─ columns: [-sum(distinct 71) as col2] 1722 └─ GroupBy 1723 ├─ select: SUM(DISTINCT 71) 1724 ├─ group: 1725 └─ TableAlias(cor0) 1726 └─ Table 1727 ├─ name: xy 1728 ├─ columns: [x y z] 1729 ├─ colSet: (1-3) 1730 └─ tableId: 1 1731 `, 1732 }, 1733 { 1734 Query: "select x as y, y from xy s order by x desc", 1735 ExpectedPlan: ` 1736 Project 1737 ├─ columns: [s.x:1!null as y, s.y:2!null] 1738 └─ Sort(s.x:1!null DESC nullsFirst) 1739 └─ Project 1740 ├─ columns: [s.x:1!null, s.y:2!null, s.z:3!null, s.x:1!null as y] 1741 └─ TableAlias(s) 1742 └─ Table 1743 ├─ name: xy 1744 ├─ columns: [x y z] 1745 ├─ colSet: (1-3) 1746 └─ tableId: 1 1747 `, 1748 }, 1749 { 1750 Query: "select x+1 as x, (select x) from xy;", 1751 ExpectedPlan: ` 1752 Project 1753 ├─ columns: [(xy.x:1!null + 1 (tinyint)) as x, Subquery 1754 │ ├─ cacheable: false 1755 │ ├─ alias-string: select x 1756 │ └─ Project 1757 │ ├─ columns: [xy.x:1!null] 1758 │ └─ Table 1759 │ ├─ name: 1760 │ ├─ columns: [] 1761 │ ├─ colSet: () 1762 │ └─ tableId: 0 1763 │ as (select x)] 1764 └─ Project 1765 ├─ columns: [xy.x:1!null, xy.y:2!null, xy.z:3!null, (xy.x:1!null + 1 (tinyint)) as x] 1766 └─ Table 1767 ├─ name: xy 1768 ├─ columns: [x y z] 1769 ├─ colSet: (1-3) 1770 └─ tableId: 1 1771 `, 1772 }, 1773 { 1774 Query: ` 1775 SELECT fi, COUNT(*) FROM ( 1776 SELECT tbl.x AS fi 1777 FROM xy tbl 1778 ) t 1779 GROUP BY fi 1780 ORDER BY COUNT(*) ASC, fi`, 1781 ExpectedPlan: ` 1782 Project 1783 ├─ columns: [t.fi:5!null, count(1):6!null as COUNT(*)] 1784 └─ Sort(count(1):6!null ASC nullsFirst, t.fi:5!null ASC nullsFirst) 1785 └─ GroupBy 1786 ├─ select: COUNT(1 (bigint)), t.fi:5!null 1787 ├─ group: t.fi:5!null 1788 └─ SubqueryAlias 1789 ├─ name: t 1790 ├─ outerVisibility: false 1791 ├─ isLateral: false 1792 ├─ cacheable: true 1793 ├─ colSet: (5) 1794 ├─ tableId: 2 1795 └─ Project 1796 ├─ columns: [tbl.x:1!null as fi] 1797 └─ TableAlias(tbl) 1798 └─ Table 1799 ├─ name: xy 1800 ├─ columns: [x y z] 1801 ├─ colSet: (1-3) 1802 └─ tableId: 1 1803 `, 1804 }, 1805 { 1806 Query: "select y as k from xy union select x from xy order by k", 1807 ExpectedPlan: ` 1808 Union distinct 1809 ├─ sortFields: k:4!null 1810 ├─ Project 1811 │ ├─ columns: [xy.y:2!null as k] 1812 │ └─ Table 1813 │ ├─ name: xy 1814 │ ├─ columns: [x y z] 1815 │ ├─ colSet: (1-3) 1816 │ └─ tableId: 1 1817 └─ Project 1818 ├─ columns: [xy.x:5!null] 1819 └─ Table 1820 ├─ name: xy 1821 ├─ columns: [x y z] 1822 ├─ colSet: (5-7) 1823 └─ tableId: 2 1824 `, 1825 }, 1826 { 1827 Query: "SELECT sum(y) over w FROM xy WINDOW w as (partition by z order by x rows unbounded preceding) order by x", 1828 ExpectedPlan: ` 1829 Project 1830 ├─ columns: [sum 1831 │ ├─ over ( partition by xy.z order by xy.x asc rows between unbounded preceding and unbounded following) 1832 │ └─ xy.y 1833 │ :4!null as sum(y) over w] 1834 └─ Sort(xy.x:1!null ASC nullsFirst) 1835 └─ Window 1836 ├─ SUM 1837 │ ├─ over ( partition by xy.z order by xy.x ASC ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) 1838 │ └─ xy.y:2!null 1839 ├─ xy.x:1!null 1840 └─ Table 1841 ├─ name: xy 1842 ├─ columns: [x y z] 1843 ├─ colSet: (1-3) 1844 └─ tableId: 1 1845 `, 1846 }, 1847 { 1848 Query: "select 1 as a, (select a) as a", 1849 ExpectedPlan: ` 1850 Project 1851 ├─ columns: [1 (tinyint) as a, Subquery 1852 │ ├─ cacheable: false 1853 │ ├─ alias-string: select a 1854 │ └─ Project 1855 │ ├─ columns: [a:1!null] 1856 │ └─ Table 1857 │ ├─ name: 1858 │ ├─ columns: [] 1859 │ ├─ colSet: () 1860 │ └─ tableId: 0 1861 │ as a] 1862 └─ Project 1863 ├─ columns: [dual.:0!null, 1 (tinyint) as a, Subquery 1864 │ ├─ cacheable: false 1865 │ ├─ alias-string: select a 1866 │ └─ Project 1867 │ ├─ columns: [a:1!null] 1868 │ └─ Table 1869 │ ├─ name: 1870 │ ├─ columns: [] 1871 │ ├─ colSet: () 1872 │ └─ tableId: 0 1873 │ as a] 1874 └─ Table 1875 ├─ name: 1876 ├─ columns: [] 1877 ├─ colSet: () 1878 └─ tableId: 0 1879 `, 1880 }, 1881 { 1882 Query: "SELECT max(x), (select max(dt.a) from (SELECT x as a) as dt(a)) as a1 from xy group by a1;", 1883 ExpectedPlan: ` 1884 Project 1885 ├─ columns: [max(xy.x):4!null as max(x), Subquery 1886 │ ├─ cacheable: false 1887 │ ├─ alias-string: select max(dt.a) from (select x as a) as dt (a) 1888 │ └─ Project 1889 │ ├─ columns: [max(dt.a):7!null] 1890 │ └─ GroupBy 1891 │ ├─ select: MAX(dt.a:6!null) 1892 │ ├─ group: 1893 │ └─ SubqueryAlias 1894 │ ├─ name: dt 1895 │ ├─ outerVisibility: false 1896 │ ├─ isLateral: false 1897 │ ├─ cacheable: false 1898 │ ├─ colSet: (6) 1899 │ ├─ tableId: 2 1900 │ └─ Project 1901 │ ├─ columns: [xy.x:1!null as a] 1902 │ └─ Table 1903 │ ├─ name: 1904 │ ├─ columns: [] 1905 │ ├─ colSet: () 1906 │ └─ tableId: 0 1907 │ as a1] 1908 └─ Project 1909 ├─ columns: [max(xy.x):4!null, Subquery 1910 │ ├─ cacheable: false 1911 │ ├─ alias-string: select max(dt.a) from (select x as a) as dt (a) 1912 │ └─ Project 1913 │ ├─ columns: [max(dt.a):7!null] 1914 │ └─ GroupBy 1915 │ ├─ select: MAX(dt.a:6!null) 1916 │ ├─ group: 1917 │ └─ SubqueryAlias 1918 │ ├─ name: dt 1919 │ ├─ outerVisibility: false 1920 │ ├─ isLateral: false 1921 │ ├─ cacheable: false 1922 │ ├─ colSet: (6) 1923 │ ├─ tableId: 2 1924 │ └─ Project 1925 │ ├─ columns: [xy.x:1!null as a] 1926 │ └─ Table 1927 │ ├─ name: 1928 │ ├─ columns: [] 1929 │ ├─ colSet: () 1930 │ └─ tableId: 0 1931 │ as a1] 1932 └─ GroupBy 1933 ├─ select: MAX(xy.x:1!null) 1934 ├─ group: Subquery 1935 │ ├─ cacheable: false 1936 │ ├─ alias-string: select max(dt.a) from (select x as a) as dt (a) 1937 │ └─ Project 1938 │ ├─ columns: [max(dt.a):7!null] 1939 │ └─ GroupBy 1940 │ ├─ select: MAX(dt.a:6!null) 1941 │ ├─ group: 1942 │ └─ SubqueryAlias 1943 │ ├─ name: dt 1944 │ ├─ outerVisibility: false 1945 │ ├─ isLateral: false 1946 │ ├─ cacheable: false 1947 │ ├─ colSet: (6) 1948 │ ├─ tableId: 2 1949 │ └─ Project 1950 │ ├─ columns: [xy.x:1!null as a] 1951 │ └─ Table 1952 │ ├─ name: 1953 │ ├─ columns: [] 1954 │ ├─ colSet: () 1955 │ └─ tableId: 0 1956 │ as a1 1957 └─ Table 1958 ├─ name: xy 1959 ├─ columns: [x y z] 1960 ├─ colSet: (1-3) 1961 └─ tableId: 1 1962 `, 1963 }, 1964 { 1965 Query: "select x as s, y as s from xy", 1966 ExpectedPlan: ` 1967 Project 1968 ├─ columns: [xy.x:1!null as s, xy.y:2!null as s] 1969 └─ Table 1970 ├─ name: xy 1971 ├─ columns: [x y z] 1972 ├─ colSet: (1-3) 1973 └─ tableId: 1 1974 `, 1975 }, 1976 { 1977 Query: "SELECT * FROM xy AS OF convert('2018-01-01', DATETIME) AS s ORDER BY x", 1978 ExpectedPlan: ` 1979 Project 1980 ├─ columns: [s.x:1!null, s.y:2!null, s.z:3!null] 1981 └─ Sort(s.x:1!null ASC nullsFirst) 1982 └─ TableAlias(s) 1983 └─ Table 1984 ├─ name: xy 1985 ├─ columns: [x y z] 1986 ├─ colSet: (1-3) 1987 └─ tableId: 1 1988 `, 1989 }, 1990 { 1991 Query: "create table myTable (a int primary key, b int, c int as (a + b + 1), d int default (b + 1), check (b+d > 0));", 1992 ExpectedPlan: ` 1993 Create table myTable 1994 ├─ Columns 1995 │ ├─ Name: a, Source: myTable, Type: int, PrimaryKey: true, Nullable: false, Comment: , Default: Generated: , AutoIncrement: false, Extra: 1996 │ ├─ Name: b, Source: myTable, Type: int, PrimaryKey: false, Nullable: true, Comment: , Default: Generated: , AutoIncrement: false, Extra: 1997 │ ├─ Name: c, Source: myTable, Type: int, PrimaryKey: false, Nullable: true, Comment: , Default: Generated: parenthesized(((mytable.a:0!null + mytable.b:1) + 1 (tinyint))), AutoIncrement: false, Extra: 1998 │ └─ Name: d, Source: myTable, Type: int, PrimaryKey: false, Nullable: true, Comment: , Default: parenthesized((mytable.b:1 + 1 (tinyint)))Generated: , AutoIncrement: false, Extra: 1999 └─ CheckConstraints 2000 └─ CHECK GreaterThan 2001 ├─ (mytable.b:1 + mytable.d:3) 2002 └─ 0 (tinyint) 2003 ENFORCED 2004 `, 2005 }, 2006 { 2007 Query: "SELECT x as y FROM xy GROUP BY x HAVING AVG(-y) IS NOT NULL", 2008 ExpectedPlan: ` 2009 Project 2010 ├─ columns: [xy.x:1!null as y] 2011 └─ Having 2012 ├─ NOT 2013 │ └─ avg(-xy.y):5 IS NULL 2014 └─ Project 2015 ├─ columns: [avg(-xy.y):5, xy.x:1!null, xy.y:2!null, xy.x:1!null as y] 2016 └─ GroupBy 2017 ├─ select: AVG(-xy.y), xy.x:1!null, xy.y:2!null 2018 ├─ group: xy.x:1!null 2019 └─ Table 2020 ├─ name: xy 2021 ├─ columns: [x y z] 2022 ├─ colSet: (1-3) 2023 └─ tableId: 1 2024 `, 2025 }, 2026 { 2027 Query: "select x as xx from xy group by xx having xx = 123;", 2028 ExpectedPlan: ` 2029 Project 2030 ├─ columns: [xy.x:1!null as xx] 2031 └─ Having 2032 ├─ Eq 2033 │ ├─ xx:4!null 2034 │ └─ 123 (tinyint) 2035 └─ Project 2036 ├─ columns: [xy.x:1!null, xy.x:1!null as xx] 2037 └─ GroupBy 2038 ├─ select: xy.x:1!null 2039 ├─ group: xy.x:1!null as xx 2040 └─ Table 2041 ├─ name: xy 2042 ├─ columns: [x y z] 2043 ├─ colSet: (1-3) 2044 └─ tableId: 1 2045 `, 2046 }, 2047 { 2048 Query: "select x as xx from xy having xx = 123;", 2049 ExpectedPlan: ` 2050 Project 2051 ├─ columns: [xy.x:1!null as xx] 2052 └─ Having 2053 ├─ Eq 2054 │ ├─ xx:4!null 2055 │ └─ 123 (tinyint) 2056 └─ Project 2057 ├─ columns: [xy.x:1!null, xy.y:2!null, xy.z:3!null, xy.x:1!null as xx] 2058 └─ Table 2059 ├─ name: xy 2060 ├─ columns: [x y z] 2061 ├─ colSet: (1-3) 2062 └─ tableId: 1 2063 `, 2064 }, 2065 { 2066 Query: "select x as xx from xy group by xx having x = 123;", 2067 ExpectedPlan: ` 2068 Project 2069 ├─ columns: [xy.x:1!null as xx] 2070 └─ Having 2071 ├─ Eq 2072 │ ├─ xy.x:1!null 2073 │ └─ 123 (tinyint) 2074 └─ Project 2075 ├─ columns: [xy.x:1!null, xy.x:1!null as xx] 2076 └─ GroupBy 2077 ├─ select: xy.x:1!null 2078 ├─ group: xy.x:1!null as xx 2079 └─ Table 2080 ├─ name: xy 2081 ├─ columns: [x y z] 2082 ├─ colSet: (1-3) 2083 └─ tableId: 1 2084 `, 2085 }, 2086 { 2087 Query: "select x as xx from xy having x = 123;", 2088 ExpectedPlan: ` 2089 Project 2090 ├─ columns: [xy.x:1!null as xx] 2091 └─ Having 2092 ├─ Eq 2093 │ ├─ xy.x:1!null 2094 │ └─ 123 (tinyint) 2095 └─ Project 2096 ├─ columns: [xy.x:1!null, xy.y:2!null, xy.z:3!null, xy.x:1!null as xx] 2097 └─ Table 2098 ├─ name: xy 2099 ├─ columns: [x y z] 2100 ├─ colSet: (1-3) 2101 └─ tableId: 1 2102 `, 2103 }, 2104 { 2105 Query: "select x + 1 as xx from xy group by xx having xx = 123;", 2106 ExpectedPlan: ` 2107 Project 2108 ├─ columns: [(xy.x:1!null + 1 (tinyint)) as xx] 2109 └─ Having 2110 ├─ Eq 2111 │ ├─ xx:4!null 2112 │ └─ 123 (tinyint) 2113 └─ Project 2114 ├─ columns: [xy.x:1!null, (xy.x:1!null + 1 (tinyint)) as xx] 2115 └─ GroupBy 2116 ├─ select: xy.x:1!null 2117 ├─ group: (xy.x:1!null + 1 (tinyint)) as xx 2118 └─ Table 2119 ├─ name: xy 2120 ├─ columns: [x y z] 2121 ├─ colSet: (1-3) 2122 └─ tableId: 1 2123 `, 2124 }, 2125 { 2126 Query: "select x + 1 as xx from xy having xx = 123;", 2127 ExpectedPlan: ` 2128 Project 2129 ├─ columns: [(xy.x:1!null + 1 (tinyint)) as xx] 2130 └─ Having 2131 ├─ Eq 2132 │ ├─ xx:4!null 2133 │ └─ 123 (tinyint) 2134 └─ Project 2135 ├─ columns: [xy.x:1!null, xy.y:2!null, xy.z:3!null, (xy.x:1!null + 1 (tinyint)) as xx] 2136 └─ Table 2137 ├─ name: xy 2138 ├─ columns: [x y z] 2139 ├─ colSet: (1-3) 2140 └─ tableId: 1 2141 `, 2142 }, 2143 { 2144 Query: "select x as xx from xy group by x having x = xx;", 2145 ExpectedPlan: ` 2146 Project 2147 ├─ columns: [xy.x:1!null as xx] 2148 └─ Having 2149 ├─ Eq 2150 │ ├─ xy.x:1!null 2151 │ └─ xx:4!null 2152 └─ Project 2153 ├─ columns: [xy.x:1!null, xy.x:1!null as xx] 2154 └─ GroupBy 2155 ├─ select: xy.x:1!null 2156 ├─ group: xy.x:1!null 2157 └─ Table 2158 ├─ name: xy 2159 ├─ columns: [x y z] 2160 ├─ colSet: (1-3) 2161 └─ tableId: 1 2162 `, 2163 }, 2164 { 2165 Query: "select x as xx from xy group by xx having x = xx;", 2166 ExpectedPlan: ` 2167 Project 2168 ├─ columns: [xy.x:1!null as xx] 2169 └─ Having 2170 ├─ Eq 2171 │ ├─ xy.x:1!null 2172 │ └─ xx:4!null 2173 └─ Project 2174 ├─ columns: [xy.x:1!null, xy.x:1!null as xx] 2175 └─ GroupBy 2176 ├─ select: xy.x:1!null 2177 ├─ group: xy.x:1!null as xx 2178 └─ Table 2179 ├─ name: xy 2180 ├─ columns: [x y z] 2181 ├─ colSet: (1-3) 2182 └─ tableId: 1 2183 `, 2184 }, 2185 { 2186 Query: "select x as xx from xy group by x, xx having x = xx;", 2187 ExpectedPlan: ` 2188 Project 2189 ├─ columns: [xy.x:1!null as xx] 2190 └─ Having 2191 ├─ Eq 2192 │ ├─ xy.x:1!null 2193 │ └─ xx:4!null 2194 └─ Project 2195 ├─ columns: [xy.x:1!null, xy.x:1!null as xx] 2196 └─ GroupBy 2197 ├─ select: xy.x:1!null 2198 ├─ group: xy.x:1!null, xy.x:1!null as xx 2199 └─ Table 2200 ├─ name: xy 2201 ├─ columns: [x y z] 2202 ├─ colSet: (1-3) 2203 └─ tableId: 1 2204 `, 2205 }, 2206 { 2207 Query: "select x as xx from xy having x = xx;", 2208 ExpectedPlan: ` 2209 Project 2210 ├─ columns: [xy.x:1!null as xx] 2211 └─ Having 2212 ├─ Eq 2213 │ ├─ xy.x:1!null 2214 │ └─ xx:4!null 2215 └─ Project 2216 ├─ columns: [xy.x:1!null, xy.y:2!null, xy.z:3!null, xy.x:1!null as xx] 2217 └─ Table 2218 ├─ name: xy 2219 ├─ columns: [x y z] 2220 ├─ colSet: (1-3) 2221 └─ tableId: 1 2222 `, 2223 }, 2224 { 2225 Query: "select -x as y from xy group by x, y having -x > y;", 2226 ExpectedPlan: ` 2227 Project 2228 ├─ columns: [-xy.x as y] 2229 └─ Having 2230 ├─ GreaterThan 2231 │ ├─ -xy.x 2232 │ └─ xy.y:2!null 2233 └─ Project 2234 ├─ columns: [xy.x:1!null, xy.y:2!null, -xy.x as y] 2235 └─ GroupBy 2236 ├─ select: xy.x:1!null, xy.y:2!null 2237 ├─ group: xy.x:1!null, xy.y:2!null 2238 └─ Table 2239 ├─ name: xy 2240 ├─ columns: [x y z] 2241 ├─ colSet: (1-3) 2242 └─ tableId: 1 2243 `, 2244 }, 2245 { 2246 Query: "select x as xx from xy join uv on (x = u) group by xx having xx = 123;", 2247 ExpectedPlan: ` 2248 Project 2249 ├─ columns: [xy.x:1!null as xx] 2250 └─ Having 2251 ├─ Eq 2252 │ ├─ xx:7!null 2253 │ └─ 123 (tinyint) 2254 └─ Project 2255 ├─ columns: [xy.x:1!null, xy.x:1!null as xx] 2256 └─ GroupBy 2257 ├─ select: xy.x:1!null 2258 ├─ group: xy.x:1!null as xx 2259 └─ InnerJoin 2260 ├─ Eq 2261 │ ├─ xy.x:1!null 2262 │ └─ uv.u:4!null 2263 ├─ Table 2264 │ ├─ name: xy 2265 │ ├─ columns: [x y z] 2266 │ ├─ colSet: (1-3) 2267 │ └─ tableId: 1 2268 └─ Table 2269 ├─ name: uv 2270 ├─ columns: [u v w] 2271 ├─ colSet: (4-6) 2272 └─ tableId: 2 2273 `, 2274 }, 2275 { 2276 Query: "select x as xx from xy join uv on (x = u) having xx = 123;", 2277 ExpectedPlan: ` 2278 Project 2279 ├─ columns: [xy.x:1!null as xx] 2280 └─ Having 2281 ├─ Eq 2282 │ ├─ xx:7!null 2283 │ └─ 123 (tinyint) 2284 └─ Project 2285 ├─ columns: [xy.x:1!null, xy.y:2!null, xy.z:3!null, uv.u:4!null, uv.v:5!null, uv.w:6!null, xy.x:1!null as xx] 2286 └─ InnerJoin 2287 ├─ Eq 2288 │ ├─ xy.x:1!null 2289 │ └─ uv.u:4!null 2290 ├─ Table 2291 │ ├─ name: xy 2292 │ ├─ columns: [x y z] 2293 │ ├─ colSet: (1-3) 2294 │ └─ tableId: 1 2295 └─ Table 2296 ├─ name: uv 2297 ├─ columns: [u v w] 2298 ├─ colSet: (4-6) 2299 └─ tableId: 2 2300 `, 2301 }, 2302 { 2303 Query: "select x as xx from xy join uv on (x = u) group by xx having x = 123;", 2304 ExpectedPlan: ` 2305 Project 2306 ├─ columns: [xy.x:1!null as xx] 2307 └─ Having 2308 ├─ Eq 2309 │ ├─ xy.x:1!null 2310 │ └─ 123 (tinyint) 2311 └─ Project 2312 ├─ columns: [xy.x:1!null, xy.x:1!null as xx] 2313 └─ GroupBy 2314 ├─ select: xy.x:1!null 2315 ├─ group: xy.x:1!null as xx 2316 └─ InnerJoin 2317 ├─ Eq 2318 │ ├─ xy.x:1!null 2319 │ └─ uv.u:4!null 2320 ├─ Table 2321 │ ├─ name: xy 2322 │ ├─ columns: [x y z] 2323 │ ├─ colSet: (1-3) 2324 │ └─ tableId: 1 2325 └─ Table 2326 ├─ name: uv 2327 ├─ columns: [u v w] 2328 ├─ colSet: (4-6) 2329 └─ tableId: 2 2330 `, 2331 }, 2332 { 2333 Query: "select x as xx from xy join uv on (x = u) having x = 123;", 2334 ExpectedPlan: ` 2335 Project 2336 ├─ columns: [xy.x:1!null as xx] 2337 └─ Having 2338 ├─ Eq 2339 │ ├─ xy.x:1!null 2340 │ └─ 123 (tinyint) 2341 └─ Project 2342 ├─ columns: [xy.x:1!null, xy.y:2!null, xy.z:3!null, uv.u:4!null, uv.v:5!null, uv.w:6!null, xy.x:1!null as xx] 2343 └─ InnerJoin 2344 ├─ Eq 2345 │ ├─ xy.x:1!null 2346 │ └─ uv.u:4!null 2347 ├─ Table 2348 │ ├─ name: xy 2349 │ ├─ columns: [x y z] 2350 │ ├─ colSet: (1-3) 2351 │ └─ tableId: 1 2352 └─ Table 2353 ├─ name: uv 2354 ├─ columns: [u v w] 2355 ├─ colSet: (4-6) 2356 └─ tableId: 2 2357 `, 2358 }, 2359 { 2360 Query: "select x + 1 as xx from xy join uv on (x = u) group by xx having xx = 123;", 2361 ExpectedPlan: ` 2362 Project 2363 ├─ columns: [(xy.x:1!null + 1 (tinyint)) as xx] 2364 └─ Having 2365 ├─ Eq 2366 │ ├─ xx:7!null 2367 │ └─ 123 (tinyint) 2368 └─ Project 2369 ├─ columns: [xy.x:1!null, (xy.x:1!null + 1 (tinyint)) as xx] 2370 └─ GroupBy 2371 ├─ select: xy.x:1!null 2372 ├─ group: (xy.x:1!null + 1 (tinyint)) as xx 2373 └─ InnerJoin 2374 ├─ Eq 2375 │ ├─ xy.x:1!null 2376 │ └─ uv.u:4!null 2377 ├─ Table 2378 │ ├─ name: xy 2379 │ ├─ columns: [x y z] 2380 │ ├─ colSet: (1-3) 2381 │ └─ tableId: 1 2382 └─ Table 2383 ├─ name: uv 2384 ├─ columns: [u v w] 2385 ├─ colSet: (4-6) 2386 └─ tableId: 2 2387 `, 2388 }, 2389 { 2390 Query: "select x + 1 as xx from xy join uv on (x = u) having xx = 123;", 2391 ExpectedPlan: ` 2392 Project 2393 ├─ columns: [(xy.x:1!null + 1 (tinyint)) as xx] 2394 └─ Having 2395 ├─ Eq 2396 │ ├─ xx:7!null 2397 │ └─ 123 (tinyint) 2398 └─ Project 2399 ├─ columns: [xy.x:1!null, xy.y:2!null, xy.z:3!null, uv.u:4!null, uv.v:5!null, uv.w:6!null, (xy.x:1!null + 1 (tinyint)) as xx] 2400 └─ InnerJoin 2401 ├─ Eq 2402 │ ├─ xy.x:1!null 2403 │ └─ uv.u:4!null 2404 ├─ Table 2405 │ ├─ name: xy 2406 │ ├─ columns: [x y z] 2407 │ ├─ colSet: (1-3) 2408 │ └─ tableId: 1 2409 └─ Table 2410 ├─ name: uv 2411 ├─ columns: [u v w] 2412 ├─ colSet: (4-6) 2413 └─ tableId: 2 2414 `, 2415 }, 2416 { 2417 Query: "select x +1 as xx from xy join uv on (x = u) group by x having avg(x) = 123;", 2418 ExpectedPlan: ` 2419 Project 2420 ├─ columns: [(xy.x:1!null + 1 (tinyint)) as xx] 2421 └─ Having 2422 ├─ Eq 2423 │ ├─ avg(xy.x):8 2424 │ └─ 123 (tinyint) 2425 └─ Project 2426 ├─ columns: [avg(xy.x):8, xy.x:1!null, (xy.x:1!null + 1 (tinyint)) as xx] 2427 └─ GroupBy 2428 ├─ select: AVG(xy.x:1!null), xy.x:1!null 2429 ├─ group: xy.x:1!null 2430 └─ InnerJoin 2431 ├─ Eq 2432 │ ├─ xy.x:1!null 2433 │ └─ uv.u:4!null 2434 ├─ Table 2435 │ ├─ name: xy 2436 │ ├─ columns: [x y z] 2437 │ ├─ colSet: (1-3) 2438 │ └─ tableId: 1 2439 └─ Table 2440 ├─ name: uv 2441 ├─ columns: [u v w] 2442 ├─ colSet: (4-6) 2443 └─ tableId: 2 2444 `, 2445 }, 2446 { 2447 Skip: true, 2448 Query: "select x + 1 as xx from xy join uv on (x = u) group by xx having avg(xx) = 123;", 2449 }, 2450 } 2451 2452 var w *bufio.Writer 2453 var outputPath string 2454 if rewrite { 2455 tmp, err := os.MkdirTemp("", "*") 2456 if err != nil { 2457 panic(err) 2458 } 2459 2460 outputPath = filepath.Join(tmp, "queryPlans.txt") 2461 f, err := os.Create(outputPath) 2462 require.NoError(t, err) 2463 2464 w = bufio.NewWriter(f) 2465 _, _ = fmt.Fprintf(w, "var %s = []planTest{\n", "tests") 2466 2467 defer func() { 2468 w.WriteString("}\n") 2469 w.Flush() 2470 t.Logf("Query plans in %s", outputPath) 2471 }() 2472 } 2473 2474 db := memory.NewDatabase("mydb") 2475 cat := newTestCatalog(db) 2476 pro := memory.NewDBProvider(db) 2477 sess := memory.NewSession(sql.NewBaseSession(), pro) 2478 2479 ctx := sql.NewContext(context.Background(), sql.WithSession(sess)) 2480 ctx.SetCurrentDatabase("mydb") 2481 b := New(ctx, cat) 2482 2483 for _, tt := range tests { 2484 t.Run(tt.Query, func(t *testing.T) { 2485 if tt.Skip { 2486 if rewrite { 2487 w.WriteString("\t{\n") 2488 w.WriteString(fmt.Sprintf("\t\tSkip: true,\n")) 2489 if strings.Contains(tt.Query, "\n") { 2490 w.WriteString(fmt.Sprintf("\t\tQuery: `\n\t%s`,\n", strings.TrimSpace(tt.Query))) 2491 } else { 2492 w.WriteString(fmt.Sprintf("\t\tQuery: \"%s\",\n", strings.TrimSpace(tt.Query))) 2493 } 2494 w.WriteString("\t},\n") 2495 } 2496 t.Skip() 2497 } 2498 stmt, err := sqlparser.Parse(tt.Query) 2499 require.NoError(t, err) 2500 2501 outScope := b.build(nil, stmt, tt.Query) 2502 defer b.Reset() 2503 plan := sql.DebugString(outScope.node) 2504 2505 if rewrite { 2506 w.WriteString("\t{\n") 2507 if strings.Contains(tt.Query, "\n") { 2508 w.WriteString(fmt.Sprintf("\t\tQuery: `\n\t%s`,\n", strings.TrimSpace(tt.Query))) 2509 } else { 2510 w.WriteString(fmt.Sprintf("\t\tQuery: \"%s\",\n", strings.TrimSpace(tt.Query))) 2511 } 2512 w.WriteString(fmt.Sprintf("\t\tExpectedPlan: `\n%s`,\n", plan)) 2513 w.WriteString("\t},\n") 2514 } 2515 if verbose { 2516 print(plan) 2517 } 2518 2519 require.Equal(t, tt.ExpectedPlan, "\n"+sql.DebugString(outScope.node)) 2520 require.True(t, outScope.node.Resolved()) 2521 }) 2522 } 2523 } 2524 2525 func newTestCatalog(db *memory.Database) *sql.MapCatalog { 2526 cat := &sql.MapCatalog{ 2527 Databases: make(map[string]sql.Database), 2528 Tables: make(map[string]sql.Table), 2529 } 2530 2531 cat.Tables["xy"] = memory.NewTable(db, "xy", sql.NewPrimaryKeySchema(sql.Schema{ 2532 {Name: "x", Type: types.Int64}, 2533 {Name: "y", Type: types.Int64}, 2534 {Name: "z", Type: types.Int64}, 2535 }, 0), nil) 2536 cat.Tables["uv"] = memory.NewTable(db, "uv", sql.NewPrimaryKeySchema(sql.Schema{ 2537 {Name: "u", Type: types.Int64}, 2538 {Name: "v", Type: types.Int64}, 2539 {Name: "w", Type: types.Int64}, 2540 }, 0), nil) 2541 2542 db.AddTable("xy", cat.Tables["xy"].(memory.MemTable)) 2543 db.AddTable("uv", cat.Tables["uv"].(memory.MemTable)) 2544 cat.Databases["mydb"] = db 2545 cat.Funcs = function.NewRegistry() 2546 return cat 2547 } 2548 2549 func TestParseColumnTypeString(t *testing.T) { 2550 tests := []struct { 2551 columnType string 2552 expectedSqlType sql.Type 2553 }{ 2554 { 2555 "tinyint", 2556 types.Int8, 2557 }, 2558 { 2559 "tinyint(0)", 2560 types.Int8, 2561 }, 2562 { 2563 // MySQL 8.1.0 only honors display width for TINYINT and only when the display width is 1 2564 "tinyint(1)", 2565 types.MustCreateNumberTypeWithDisplayWidth(sqltypes.Int8, 1), 2566 }, 2567 { 2568 "tinyint(2)", 2569 types.Int8, 2570 }, 2571 { 2572 "SMALLINT", 2573 types.Int16, 2574 }, 2575 { 2576 "SMALLINT(1)", 2577 types.Int16, 2578 }, 2579 { 2580 "MeDiUmInT", 2581 types.Int24, 2582 }, 2583 { 2584 "MEDIUMINT(1)", 2585 types.Int24, 2586 }, 2587 { 2588 "INT", 2589 types.Int32, 2590 }, 2591 { 2592 "INT(0)", 2593 types.Int32, 2594 }, 2595 { 2596 "BIGINT", 2597 types.Int64, 2598 }, 2599 { 2600 "BIGINT(1)", 2601 types.Int64, 2602 }, 2603 { 2604 "TINYINT UNSIGNED", 2605 types.Uint8, 2606 }, 2607 { 2608 "TINYINT(1) UNSIGNED", 2609 types.Uint8, 2610 }, 2611 { 2612 "SMALLINT UNSIGNED", 2613 types.Uint16, 2614 }, 2615 { 2616 "SMALLINT(1) UNSIGNED", 2617 types.Uint16, 2618 }, 2619 { 2620 "MEDIUMINT UNSIGNED", 2621 types.Uint24, 2622 }, 2623 { 2624 "MEDIUMINT(1) UNSIGNED", 2625 types.Uint24, 2626 }, 2627 { 2628 "INT UNSIGNED", 2629 types.Uint32, 2630 }, 2631 { 2632 "INT(1) UNSIGNED", 2633 types.Uint32, 2634 }, 2635 { 2636 "BIGINT UNSIGNED", 2637 types.Uint64, 2638 }, 2639 { 2640 "BIGINT(1) UNSIGNED", 2641 types.Uint64, 2642 }, 2643 { 2644 // Boolean is a synonym for TINYINT(1) 2645 "BOOLEAN", 2646 types.MustCreateNumberTypeWithDisplayWidth(sqltypes.Int8, 1), 2647 }, 2648 { 2649 "FLOAT", 2650 types.Float32, 2651 }, 2652 { 2653 "DOUBLE", 2654 types.Float64, 2655 }, 2656 { 2657 "REAL", 2658 types.Float64, 2659 }, 2660 { 2661 "DECIMAL", 2662 types.MustCreateColumnDecimalType(10, 0), 2663 }, 2664 { 2665 "DECIMAL(22)", 2666 types.MustCreateColumnDecimalType(22, 0), 2667 }, 2668 { 2669 "DECIMAL(55, 13)", 2670 types.MustCreateColumnDecimalType(55, 13), 2671 }, 2672 { 2673 "DEC(34, 2)", 2674 types.MustCreateColumnDecimalType(34, 2), 2675 }, 2676 { 2677 "FIXED(4, 4)", 2678 types.MustCreateColumnDecimalType(4, 4), 2679 }, 2680 { 2681 "BIT(31)", 2682 types.MustCreateBitType(31), 2683 }, 2684 { 2685 "TINYBLOB", 2686 types.TinyBlob, 2687 }, 2688 { 2689 "BLOB", 2690 types.Blob, 2691 }, 2692 { 2693 "MEDIUMBLOB", 2694 types.MediumBlob, 2695 }, 2696 { 2697 "LONGBLOB", 2698 types.LongBlob, 2699 }, 2700 { 2701 "TINYTEXT", 2702 types.TinyText, 2703 }, 2704 { 2705 "TEXT", 2706 types.Text, 2707 }, 2708 { 2709 "MEDIUMTEXT", 2710 types.MediumText, 2711 }, 2712 { 2713 "LONGTEXT", 2714 types.LongText, 2715 }, 2716 { 2717 "CHAR(5)", 2718 types.MustCreateStringWithDefaults(sqltypes.Char, 5), 2719 }, 2720 { 2721 "VARCHAR(255)", 2722 types.MustCreateStringWithDefaults(sqltypes.VarChar, 255), 2723 }, 2724 { 2725 "VARCHAR(300) COLLATE latin1_german2_ci", 2726 types.MustCreateString(sqltypes.VarChar, 300, sql.Collation_latin1_german2_ci), 2727 }, 2728 { 2729 "BINARY(6)", 2730 types.MustCreateBinary(sqltypes.Binary, 6), 2731 }, 2732 { 2733 "VARBINARY(256)", 2734 types.MustCreateBinary(sqltypes.VarBinary, 256), 2735 }, 2736 { 2737 "YEAR", 2738 types.Year, 2739 }, 2740 { 2741 "DATE", 2742 types.Date, 2743 }, 2744 { 2745 "TIME", 2746 types.Time, 2747 }, 2748 { 2749 "TIMESTAMP", 2750 types.Timestamp, 2751 }, 2752 { 2753 "TIMESTAMP(3)", 2754 types.MustCreateDatetimeType(sqltypes.Timestamp, 3), 2755 }, 2756 { 2757 "TIMESTAMP(6)", 2758 types.TimestampMaxPrecision, 2759 }, 2760 { 2761 "DATETIME(3)", 2762 types.MustCreateDatetimeType(sqltypes.Datetime, 3), 2763 }, 2764 { 2765 "DATETIME", 2766 types.Datetime, 2767 }, 2768 { 2769 "DATETIME(6)", 2770 types.DatetimeMaxPrecision, 2771 }, 2772 } 2773 2774 for _, test := range tests { 2775 ctx := sql.NewEmptyContext() 2776 ctx.SetCurrentDatabase("mydb") 2777 t.Run("parse "+test.columnType, func(t *testing.T) { 2778 res, err := ParseColumnTypeString(test.columnType) 2779 require.NoError(t, err) 2780 if collatedType, ok := res.(sql.TypeWithCollation); ok { 2781 if collatedType.Collation() == sql.Collation_Unspecified { 2782 res, err = collatedType.WithNewCollation(sql.Collation_Default) 2783 require.NoError(t, err) 2784 } 2785 } 2786 require.Equal(t, test.expectedSqlType, res) 2787 }) 2788 t.Run("round trip "+test.columnType, func(t *testing.T) { 2789 str := test.expectedSqlType.String() 2790 typ, err := ParseColumnTypeString(str) 2791 require.NoError(t, err) 2792 if collatedType, ok := typ.(sql.TypeWithCollation); ok { 2793 if collatedType.Collation() == sql.Collation_Unspecified { 2794 typ, err = collatedType.WithNewCollation(sql.Collation_Default) 2795 require.NoError(t, err) 2796 } 2797 } 2798 require.Equal(t, test.expectedSqlType, typ) 2799 require.Equal(t, typ.String(), str) 2800 }) 2801 } 2802 } 2803 2804 func TestPlanBuilderErr(t *testing.T) { 2805 var tests = []planErrTest{ 2806 { 2807 Query: "select x, y as x from xy order by x;", 2808 Err: "ambiguous column or alias name \"x\"", 2809 }, 2810 { 2811 Query: "select x from xy having z > 0", 2812 Err: "column \"z\" could not be found in any table in scope", 2813 }, 2814 { 2815 Query: "select x from xy having z > 0 order by y", 2816 Err: "column \"z\" could not be found in any table in scope", 2817 }, 2818 { 2819 Query: "SELECT x, sum(x) FROM xy group by 1 having x+y order by 1", 2820 Err: "column \"y\" could not be found in any table in scope", 2821 }, 2822 { 2823 Query: "select x + 1 as xx from xy join uv on (x = u) group by xx having x = 123;", 2824 Err: "column \"x\" could not be found in any table in scope", 2825 }, 2826 { 2827 Query: "select x + 1 as xx from xy join uv on (x = u) having x = 123;", 2828 Err: "column \"x\" could not be found in any table in scope", 2829 }, 2830 } 2831 2832 db := memory.NewDatabase("mydb") 2833 cat := newTestCatalog(db) 2834 pro := memory.NewDBProvider(db) 2835 sess := memory.NewSession(sql.NewBaseSession(), pro) 2836 2837 ctx := sql.NewContext(context.Background(), sql.WithSession(sess)) 2838 ctx.SetCurrentDatabase("mydb") 2839 b := New(ctx, cat) 2840 2841 for _, tt := range tests { 2842 t.Run(tt.Query, func(t *testing.T) { 2843 if tt.Skip { 2844 t.Skip() 2845 } 2846 stmt, err := sqlparser.Parse(tt.Query) 2847 require.NoError(t, err) 2848 2849 _, err = b.BindOnly(stmt, tt.Query) 2850 defer b.Reset() 2851 2852 require.Error(t, err) 2853 require.Equal(t, tt.Err, err.Error()) 2854 }) 2855 } 2856 }