1-- =================================================================== 2-- test recursive planning functionality for non-colocated subqueries 3-- We prefered to use EXPLAIN almost all the queries here, 4-- otherwise the execution time of so many repartition queries would 5-- be too high for the regression tests. Also, note that we're mostly 6-- interested in recurive planning side of the things, thus supressing 7-- the actual explain output. 8-- =================================================================== 9 10SET client_min_messages TO DEBUG1; 11 12CREATE SCHEMA non_colocated_subquery; 13 14SET search_path TO non_colocated_subquery, public; 15 16-- we don't use the data anyway 17CREATE TABLE users_table_local AS SELECT * FROM users_table LIMIT 0; 18CREATE TABLE events_table_local AS SELECT * FROM events_table LIMIT 0; 19 20 21SET citus.enable_repartition_joins TO ON; 22\set VERBOSITY terse 23 24-- Function that parses explain output as JSON 25-- copied from multi_explain.sql and had to give 26-- a different name via postfix to prevent concurrent 27-- create/drop etc. 28CREATE OR REPLACE FUNCTION explain_json_2(query text) 29RETURNS jsonb 30AS $BODY$ 31DECLARE 32 result jsonb; 33BEGIN 34 EXECUTE format('EXPLAIN (FORMAT JSON) %s', query) INTO result; 35 RETURN result; 36END; 37$BODY$ LANGUAGE plpgsql; 38 39 40-- leaf queries contain colocated joins 41-- but not the subquery 42SELECT true AS valid FROM explain_json_2($$ 43 SELECT 44 foo.value_2 45 FROM 46 (SELECT users_table.value_2 FROM users_table, events_table WHERE users_table.user_id = events_table.user_id AND event_type IN (1,2,3,4)) as foo, 47 (SELECT users_table.value_2 FROM users_table, events_table WHERE users_table.user_id = events_table.user_id AND event_type IN (5,6,7,8)) as bar 48 WHERE 49 foo.value_2 = bar.value_2; 50$$); 51 52 53-- simple non colocated join with subqueries in WHERE clause 54SELECT true AS valid FROM explain_json_2($$ 55 56 SELECT 57 count(*) 58 FROM 59 events_table 60 WHERE 61 event_type 62 IN 63 (SELECT event_type FROM events_table WHERE user_id < 100); 64 65$$); 66 67-- simple non colocated join with subqueries in WHERE clause with NOT IN 68SELECT true AS valid FROM explain_json_2($$ 69 70 SELECT 71 count(*) 72 FROM 73 events_table 74 WHERE 75 user_id 76 NOT IN 77 (SELECT user_id FROM events_table WHERE event_type = 2); 78$$); 79 80 81-- Subqueries in WHERE and FROM are mixed 82-- In this query, only subquery in WHERE is not a colocated join 83SELECT true AS valid FROM explain_json_2($$ 84 85 SELECT 86 foo.user_id 87 FROM 88 (SELECT users_table.user_id, event_type FROM users_table, events_table WHERE users_table.user_id = events_table.user_id AND event_type IN (1,2,3,4)) as foo, 89 (SELECT users_table.user_id FROM users_table, events_table WHERE users_table.user_id = events_table.user_id AND event_type IN (5,6,7,8)) as bar 90 WHERE 91 foo.user_id = bar.user_id AND 92 foo.event_type IN (SELECT event_type FROM events_table WHERE user_id < 3); 93 94$$); 95 96 97-- Subqueries in WHERE and FROM are mixed 98-- In this query, one of the joins in the FROM clause is not colocated 99SELECT true AS valid FROM explain_json_2($$ 100 101 SELECT 102 foo.user_id 103 FROM 104 (SELECT users_table.user_id, event_type FROM users_table, events_table WHERE users_table.user_id = events_table.user_id AND event_type IN (1,2,3,4)) as foo, 105 (SELECT (users_table.user_id / 2) as user_id FROM users_table, events_table WHERE users_table.user_id = events_table.user_id AND event_type IN (5,6,7,8)) as bar 106 WHERE 107 foo.user_id = bar.user_id AND 108 foo.user_id IN (SELECT user_id FROM events_table WHERE user_id < 10); 109$$); 110 111-- Subqueries in WHERE and FROM are mixed 112-- In this query, both the joins in the FROM clause is not colocated 113SELECT true AS valid FROM explain_json_2($$ 114 115 SELECT 116 foo.user_id 117 FROM 118 (SELECT users_table.user_id, event_type FROM users_table, events_table WHERE users_table.user_id = events_table.user_id AND event_type IN (1,2,3,4)) as foo, 119 (SELECT (users_table.user_id / 2) as user_id FROM users_table, events_table WHERE users_table.user_id = events_table.user_id AND event_type IN (5,6,7,8)) as bar 120 WHERE 121 foo.user_id = bar.user_id AND 122 foo.user_id NOT IN (SELECT user_id FROM events_table WHERE user_id < 10); 123$$); 124 125 126-- Subqueries in WHERE and FROM are mixed 127-- In this query, one of the joins in the FROM clause is not colocated and subquery in WHERE clause is not colocated 128-- similar to the above, but, this time bar is the anchor subquery 129SELECT true AS valid FROM explain_json_2($$ 130 SELECT 131 foo.user_id 132 FROM 133 (SELECT users_table.user_id, event_type FROM users_table, events_table WHERE users_table.user_id = events_table.value_2 AND event_type IN (1,2,3,4)) as foo, 134 (SELECT users_table.user_id FROM users_table, events_table WHERE users_table.user_id = events_table.user_id AND event_type IN (5,6,7,8)) as bar 135 WHERE 136 foo.user_id = bar.user_id AND 137 foo.event_type IN (SELECT event_type FROM events_table WHERE user_id < 4); 138$$); 139 140 141 142-- The inner subqueries and the subquery in WHERE are non-located joins 143SELECT true AS valid FROM explain_json_2($$ 144 SELECT foo_top.*, events_table.user_id FROM 145 ( 146 147 SELECT 148 foo.user_id, random() 149 FROM 150 (SELECT users_table.user_id, event_type FROM users_table, events_table WHERE users_table.user_id = events_table.value_2 AND event_type IN (1,2,3,4)) as foo, 151 (SELECT users_table.user_id FROM users_table, events_table WHERE users_table.user_id = events_table.event_type AND event_type IN (5,6,7,8)) as bar 152 WHERE 153 foo.user_id = bar.user_id AND 154 foo.event_type IN (SELECT event_type FROM events_table WHERE user_id = 5) 155 156 ) as foo_top, events_table WHERE events_table.user_id = foo_top.user_id; 157$$); 158 159-- Slightly more complex query where there are 5 joins, 1 of them is non-colocated 160SELECT true AS valid FROM explain_json_2($$ 161 162 SELECT * FROM 163 ( 164 SELECT 165 foo1.user_id, random() 166 FROM 167 (SELECT users_table.user_id, users_table.value_1 FROM users_table, events_table WHERE users_table.user_id = events_table.user_id AND event_type IN (1,2,3,4)) as foo1, 168 (SELECT users_table.user_id, users_table.value_1 FROM users_table, events_table WHERE users_table.user_id = events_table.user_id AND event_type IN (5,6,7,8)) as foo2, 169 (SELECT users_table.user_id, users_table.value_1 FROM users_table, events_table WHERE users_table.user_id = events_table.user_id AND event_type IN (9,10,11,12)) as foo3, 170 (SELECT users_table.user_id, users_table.value_1 FROM users_table, events_table WHERE users_table.user_id = events_table.user_id AND event_type IN (13,14,15,16)) as foo4, 171 (SELECT users_table.user_id, users_table.value_1 FROM users_table, events_table WHERE users_table.user_id = events_table.user_id AND event_type IN (17,18,19,20)) as foo5 172 173 WHERE 174 175 foo1.user_id = foo4.user_id AND 176 foo1.user_id = foo2.user_id AND 177 foo1.user_id = foo3.user_id AND 178 foo1.user_id = foo4.user_id AND 179 foo1.user_id = foo5.value_1 180 ) as foo_top; 181 182$$); 183 184 185 186-- Very similar to the above query 187-- One of the queries is not joined on partition key, but this time subquery itself 188SELECT true AS valid FROM explain_json_2($$ 189 190 SELECT * FROM 191 ( 192 SELECT 193 foo1.user_id, random() 194 FROM 195 (SELECT users_table.user_id, users_table.value_1 FROM users_table, events_table WHERE users_table.user_id = events_table.user_id AND event_type IN (1,2,3,4)) as foo1, 196 (SELECT users_table.user_id, users_table.value_1 FROM users_table, events_table WHERE users_table.user_id = events_table.user_id AND event_type IN (5,6,7,8)) as foo2, 197 (SELECT users_table.user_id, users_table.value_1 FROM users_table, events_table WHERE users_table.user_id = events_table.user_id AND event_type IN (9,10,11,12)) as foo3, 198 (SELECT users_table.user_id, users_table.value_1 FROM users_table, events_table WHERE users_table.user_id = events_table.user_id AND event_type IN (13,14,15,16)) as foo4, 199 (SELECT users_table.user_id, users_table.value_1 FROM users_table, events_table WHERE users_table.user_id = events_table.value_2 AND event_type IN (17,18,19,20)) as foo5 200 201 WHERE 202 203 foo1.user_id = foo4.user_id AND 204 foo1.user_id = foo2.user_id AND 205 foo1.user_id = foo3.user_id AND 206 foo1.user_id = foo4.user_id AND 207 foo1.user_id = foo5.user_id 208 ) as foo_top; 209$$); 210 211 212-- There are two non colocated joins, one is in the one of the leaf queries, 213-- the other is on the top-level subquery 214SELECT true AS valid FROM explain_json_2($$ 215 216 SELECT * FROM 217 ( 218 SELECT 219 foo1.user_id, random() 220 FROM 221 (SELECT users_table.user_id, users_table.value_1 FROM users_table, events_table WHERE users_table.user_id = events_table.user_id AND event_type IN (1,2,3,4)) as foo1, 222 (SELECT users_table.user_id, users_table.value_1 FROM users_table, events_table WHERE users_table.user_id = events_table.value_2 AND event_type IN (5,6,7,8)) as foo2, 223 (SELECT users_table.user_id, users_table.value_1 FROM users_table, events_table WHERE users_table.user_id = events_table.user_id AND event_type IN (9,10,11,12)) as foo3, 224 (SELECT users_table.user_id, users_table.value_1 FROM users_table, events_table WHERE users_table.user_id = events_table.user_id AND event_type IN (13,14,15,16)) as foo4, 225 (SELECT users_table.user_id, users_table.value_1 FROM users_table, events_table WHERE users_table.user_id = events_table.user_id AND event_type IN (17,18,19,20)) as foo5 226 WHERE 227 foo1.user_id = foo4.user_id AND 228 foo1.user_id = foo2.user_id AND 229 foo1.user_id = foo3.user_id AND 230 foo1.user_id = foo4.user_id AND 231 foo1.user_id = foo5.value_1 232 ) as foo_top; 233$$); 234 235 236-- a similar query to the above, but, this sime the second 237-- non colocated join is on the already recursively planned subquery 238-- the results should be the same 239SELECT true AS valid FROM explain_json_2($$ 240 241 SELECT * FROM 242 ( 243 SELECT 244 foo1.user_id, random() 245 FROM 246 (SELECT users_table.user_id, users_table.value_1 FROM users_table, events_table WHERE users_table.user_id = events_table.user_id AND event_type IN (1,2,3,4)) as foo1, 247 (SELECT users_table.user_id, users_table.value_1 FROM users_table, events_table WHERE users_table.user_id = events_table.value_2 AND event_type IN (5,6,7,8)) as foo2, 248 (SELECT users_table.user_id, users_table.value_1 FROM users_table, events_table WHERE users_table.user_id = events_table.user_id AND event_type IN (9,10,11,12)) as foo3, 249 (SELECT users_table.user_id, users_table.value_1 FROM users_table, events_table WHERE users_table.user_id = events_table.user_id AND event_type IN (13,14,15,16)) as foo4, 250 (SELECT users_table.user_id, users_table.value_1 FROM users_table, events_table WHERE users_table.user_id = events_table.user_id AND event_type IN (17,18,19,20)) as foo5 251 WHERE 252 foo1.user_id = foo4.user_id AND 253 foo1.user_id = foo2.user_id AND 254 foo1.user_id = foo3.user_id AND 255 foo1.user_id = foo4.user_id AND 256 foo2.user_id = foo5.value_1 257 ) as foo_top; 258$$); 259 260-- Deeper subqueries are non-colocated 261SELECT true AS valid FROM explain_json_2($$ 262 263 SELECT 264 count(*) 265 FROM 266 ( 267 SELECT 268 foo.user_id 269 FROM 270 (SELECT users_table.user_id FROM users_table, events_table WHERE users_table.user_id = events_table.value_2 AND event_type IN (1,2,3,4)) as foo, 271 (SELECT users_table.user_id FROM users_table, events_table WHERE users_table.user_id = events_table.user_id AND event_type IN (5,6,7,8)) as bar 272 WHERE 273 foo.user_id = bar.user_id) as foo_top JOIN 274 275 ( 276 SELECT 277 foo.user_id 278 FROM 279 (SELECT users_table.user_id FROM users_table, events_table WHERE users_table.user_id = events_table.value_2 AND event_type IN (1,2,3,4)) as foo, 280 (SELECT users_table.user_id FROM users_table, events_table WHERE users_table.user_id = events_table.user_id AND event_type IN (5,6,7,8)) as bar 281 WHERE 282 foo.user_id = bar.user_id) as bar_top 283 ON (foo_top.user_id = bar_top.user_id); 284$$); 285 286 287 288-- Top level Subquery is not colocated 289SELECT true AS valid FROM explain_json_2($$ 290 291 SELECT 292 count(*) 293 FROM 294 ( 295 SELECT 296 foo.user_id, foo.value_2 297 FROM 298 (SELECT DISTINCT users_table.user_id, users_table.value_2 FROM users_table, events_table WHERE users_table.user_id = events_table.user_id AND event_type IN (1,2,3,4)) as foo, 299 (SELECT DISTINCT users_table.user_id FROM users_table, events_table WHERE users_table.user_id = events_table.user_id AND event_type IN (5,6,7,8)) as bar 300 WHERE 301 foo.user_id = bar.user_id) as foo_top JOIN 302 303 ( 304 SELECT 305 foo.user_id 306 FROM 307 (SELECT DISTINCT users_table.user_id FROM users_table, events_table WHERE users_table.user_id = events_table.user_id AND event_type IN (9,10,11,12)) as foo, 308 (SELECT DISTINCT users_table.user_id FROM users_table, events_table WHERE users_table.user_id = events_table.user_id AND event_type IN (13,14,15,16)) as bar 309 WHERE 310 foo.user_id = bar.user_id) as bar_top 311 ON (foo_top.value_2 = bar_top.user_id); 312 313$$); 314 315-- Top level Subquery is not colocated as the above 316SELECT true AS valid FROM explain_json_2($$ 317 318 SELECT 319 count(*) 320 FROM 321 ( 322 SELECT 323 foo.user_id, foo.value_2 324 FROM 325 (SELECT DISTINCT users_table.user_id, users_table.value_2 FROM users_table, events_table WHERE users_table.user_id = events_table.user_id AND event_type IN (1,2,3,4)) as foo, 326 (SELECT DISTINCT users_table.user_id FROM users_table, events_table WHERE users_table.user_id = events_table.user_id AND event_type IN (5,6,7,8)) as bar 327 WHERE 328 foo.user_id = bar.user_id) as foo_top JOIN 329 ( 330 SELECT 331 foo.user_id 332 FROM 333 (SELECT DISTINCT users_table.user_id FROM users_table, events_table WHERE users_table.user_id = events_table.user_id AND event_type IN (9,10,11,12)) as foo, 334 (SELECT DISTINCT users_table.user_id FROM users_table, events_table WHERE users_table.user_id = events_table.value_2 AND event_type IN (13,14,15,16)) as bar 335 WHERE 336 foo.user_id = bar.user_id) as bar_top 337 ON (foo_top.value_2 = bar_top.user_id); 338$$); 339 340 341 342-- non colocated joins are deep inside the query 343SELECT true AS valid FROM explain_json_2($$ 344 345 SELECT 346 count(*) 347 FROM 348 ( 349 SELECT * FROM 350 (SELECT DISTINCT users_table.user_id FROM users_table, 351 (SELECT events_table.user_id as my_users FROM events_table, users_table WHERE events_table.event_type = users_table.user_id) as foo 352 WHERE foo.my_users = users_table.user_id) as mid_level_query 353 ) as bar; 354$$); 355 356-- similar to the above, with relation rtes 357-- we're able to recursively plan foo 358-- note that if we haven't added random() to the subquery, we'd be able run the query 359-- via regular repartitioning since PostgreSQL would pull the query up 360SELECT true AS valid FROM explain_json_2($$ 361 362 SELECT count(*) FROM ( SELECT * FROM 363 (SELECT DISTINCT users_table.user_id FROM users_table, 364 (SELECT events_table.event_type as my_users, random() FROM events_table, users_table WHERE events_table.user_id = users_table.user_id) as foo 365 WHERE foo.my_users = users_table.user_id) as mid_level_query ) as bar; 366 367$$); 368 369 370-- same as the above query, but, one level deeper subquery 371 SELECT true AS valid FROM explain_json_2($$ 372 373 SELECT 374 count(*) 375 FROM 376 ( 377 SELECT * FROM 378 (SELECT DISTINCT users_table.user_id FROM users_table, 379 (SELECT events_table.user_id as my_users FROM events_table, 380 (SELECT events_table.user_id, random() FROM users_table, events_table WHERE users_table.user_id = events_table.user_id) as selected_users 381 WHERE events_table.event_type = selected_users.user_id) as foo 382 383 WHERE foo.my_users = users_table.user_id) as mid_level_query 384 ) as bar; 385 $$); 386 387-- deeper query, subquery in WHERE clause 388-- this time successfull plan the query since the join on the relation and 389-- the subquery on the distribution key 390SELECT true AS valid FROM explain_json_2($$ 391 392 SELECT 393 count(*) 394 FROM 395 ( 396 SELECT * FROM 397 (SELECT DISTINCT users_table.user_id FROM users_table, 398 399 400 (SELECT events_table.user_id as my_users FROM events_table, 401 (SELECT events_table.user_id FROM users_table, events_table WHERE users_table.user_id = events_table.user_id AND 402 403 users_table.user_id IN (SELECT value_2 FROM events_table) 404 405 ) as selected_users 406 WHERE events_table.user_id = selected_users.user_id) as foo 407 408 WHERE foo.my_users = users_table.user_id) as mid_level_query 409 410 ) as bar; 411 412$$); 413 414-- should recursively plan the subquery in WHERE clause 415SELECT true AS valid FROM explain_json_2($$SELECT 416 count(*) 417FROM 418 users_table 419WHERE 420 value_1 421 IN 422 (SELECT 423 users_table.user_id 424 FROM 425 users_table, events_table 426 WHERE 427 users_table.user_id = events_table.value_2 AND event_type IN (5,6));$$); 428 429-- leaf subquery repartitioning should work fine when used with CTEs 430SELECT true AS valid FROM explain_json_2($$ 431 WITH q1 AS (SELECT user_id FROM users_table) 432SELECT count(*) FROM q1, (SELECT 433 users_table.user_id, random() 434 FROM 435 users_table, events_table 436 WHERE 437 users_table.user_id = events_table.value_2 AND event_type IN (1,2,3,4)) as bar WHERE bar.user_id = q1.user_id ;$$); 438 439-- subquery joins should work fine when used with CTEs 440SELECT true AS valid FROM explain_json_2($$ 441 WITH q1 AS MATERIALIZED (SELECT user_id FROM users_table) 442 SELECT count(*) FROM q1, (SELECT 443 users_table.user_id, random() 444 FROM 445 users_table, events_table 446 WHERE 447 users_table.user_id = events_table.user_id AND event_type IN (1,2,3,4)) as bar WHERE bar.user_id = q1.user_id ;$$); 448 449 450-- should work fine within UNIONs 451SELECT true AS valid FROM explain_json_2($$ 452 (SELECT users_table.user_id FROM users_table, events_table WHERE users_table.user_id = events_table.value_2 AND event_type IN (1,2,3,4)) UNION 453 (SELECT users_table.user_id FROM users_table, events_table WHERE users_table.user_id = events_table.user_id AND event_type IN (5,6,7,8));$$); 454 455-- should work fine within leaf queries of deeper subqueries 456SELECT true AS valid FROM explain_json_2($$ 457SELECT event, array_length(events_table, 1) 458FROM ( 459 SELECT event, array_agg(t.user_id) AS events_table 460 FROM ( 461 SELECT 462 DISTINCT ON(e.event_type::text) e.event_type::text as event, e.time, e.user_id 463 FROM 464 users_table AS u, 465 events_table AS e, 466 (SELECT users_table.user_id FROM users_table, events_table WHERE users_table.user_id = events_table.value_2 AND event_type IN (5,6,7,8)) as bar 467 WHERE u.user_id = e.user_id AND 468 u.user_id IN 469 ( 470 SELECT 471 user_id 472 FROM 473 users_table 474 WHERE value_2 >= 5 475 AND EXISTS (SELECT users_table.user_id FROM users_table, events_table WHERE users_table.user_id = events_table.value_2 AND event_type IN (1,2,3,4)) 476 LIMIT 5 477 ) 478 ) t, users_table WHERE users_table.value_1 = t.event::int 479 GROUP BY event 480) q 481ORDER BY 2 DESC, 1; 482$$); 483 484 485 486-- this is also supported since we can recursively plan relations as well 487-- the relations are joined under a join tree with an alias 488SELECT true AS valid FROM explain_json_2($$ 489 490 SELECT 491 count(*) 492 FROM 493 (users_table u1 JOIN users_table u2 using(value_1)) a JOIN (SELECT value_1, random() FROM users_table) as u3 USING (value_1); 494$$); 495 496-- a very similar query to the above 497-- however, this time we users a subquery instead of join alias, and it works 498SELECT true AS valid FROM explain_json_2($$ 499 500 SELECT 501 count(*) 502 FROM 503 (SELECT * FROM users_table u1 JOIN users_table u2 using(value_1)) a JOIN (SELECT value_1, random() FROM users_table) as u3 USING (value_1); 504$$); 505 506-- a similar query to the above, this time subquery is on the left 507-- and the relation is on the right of the join tree 508SELECT true AS valid FROM explain_json_2($$ 509 510 SELECT 511 count(*) 512 FROM 513 (SELECT value_2, random() FROM users_table) as u1 514 JOIN 515 events_table 516 using (value_2); 517$$); 518 519 520 521-- recursive planning should kick in for outer joins as well 522SELECT true AS valid FROM explain_json_2($$ 523 524 SELECT 525 count(*) 526 FROM 527 (SELECT value_2, random() FROM users_table) as u1 528 LEFT JOIN 529 (SELECT value_2, random() FROM users_table) as u2 530 USING(value_2); 531$$); 532 533 534-- recursive planning should kick in for outer joins as well 535-- but this time recursive planning might convert the query 536-- into a not supported join 537SELECT true AS valid FROM explain_json_2($$ 538 539 SELECT 540 count(*) 541 FROM 542 (SELECT value_2, random() FROM users_table) as u1 543 RIGHT JOIN 544 (SELECT value_2, random() FROM users_table) as u2 545 USING(value_2); 546$$); 547 548 549-- set operations may produce not very efficient plans 550-- although we could have picked a as our anchor subquery, 551-- we pick foo in this case and recursively plan a 552SELECT true AS valid FROM explain_json_2($$ 553 554 SELECT * FROM 555 ( 556 ( 557 SELECT user_id FROM users_table 558 UNION 559 SELECT user_id FROM users_table 560 ) a 561 JOIN 562 (SELECT value_1 FROM users_table) as foo ON (a.user_id = foo.value_1) 563 ); 564$$); 565 566-- we could do the same with regular tables as well 567SELECT true AS valid FROM explain_json_2($$ 568 569 SELECT * FROM 570 ( 571 ( 572 SELECT user_id FROM users_table 573 UNION 574 SELECT user_id FROM users_table 575 ) a 576 JOIN 577 users_table as foo ON (a.user_id = foo.value_1) 578 ); 579$$); 580 581-- this time the plan is optimial, we are 582-- able to keep the UNION query given that foo 583-- is the anchor 584SELECT true AS valid FROM explain_json_2($$ 585 586 SELECT * FROM 587 ( 588 (SELECT user_id FROM users_table) as foo 589 JOIN 590 ( 591 SELECT user_id FROM users_table WHERE user_id IN (1,2,3,4) 592 UNION 593 SELECT user_id FROM users_table WHERE user_id IN (5,6,7,8) 594 ) a 595 596 ON (a.user_id = foo.user_id) 597 JOIN 598 599 (SELECT value_1 FROM users_table) as bar 600 601 ON(foo.user_id = bar.value_1) 602 ); 603$$); 604 605-- it should be safe to recursively plan non colocated subqueries 606-- inside a CTE 607SELECT true AS valid FROM explain_json_2($$ 608 609 WITH non_colocated_subquery AS 610 ( 611 SELECT 612 foo.value_2 613 FROM 614 (SELECT users_table.value_2 FROM users_table, events_table WHERE users_table.user_id = events_table.user_id AND event_type IN (1,2,3,4)) as foo, 615 (SELECT users_table.value_2 FROM users_table, events_table WHERE users_table.user_id = events_table.user_id AND event_type IN (5,6,7,8)) as bar 616 WHERE 617 foo.value_2 = bar.value_2 618 ), 619 non_colocated_subquery_2 AS 620 ( 621 SELECT 622 count(*) as cnt 623 FROM 624 events_table 625 WHERE 626 event_type 627 IN 628 (SELECT event_type FROM events_table WHERE user_id < 4) 629 ) 630 SELECT 631 * 632 FROM 633 non_colocated_subquery, non_colocated_subquery_2 634 WHERE 635 non_colocated_subquery.value_2 != non_colocated_subquery_2.cnt 636$$); 637 638-- non colocated subquery joins should work fine along with local tables 639SELECT true AS valid FROM explain_json_2($$ 640 SELECT 641 count(*) 642 FROM 643 (SELECT users_table.value_2 FROM users_table, events_table WHERE users_table.user_id = events_table.user_id AND event_type IN (1,2,3,4)) as foo, 644 (SELECT users_table_local.value_2 FROM users_table_local, events_table_local WHERE users_table_local.user_id = events_table_local.user_id AND event_type IN (5,6,7,8)) as bar, 645 (SELECT users_table.value_2 FROM users_table, events_table WHERE users_table.user_id = events_table.user_id AND event_type IN (9,10,11,12)) as baz 646 WHERE 647 foo.value_2 = bar.value_2 648 AND 649 foo.value_2 = baz.value_2 650$$); 651 652-- a combination of subqueries in FROM and WHERE clauses 653SELECT true AS valid FROM explain_json_2($$ 654 655 SELECT 656 count(*) 657 FROM 658 (SELECT user_id FROM users_table) as foo 659 JOIN 660 ( 661 SELECT user_id FROM users_table WHERE user_id IN (1,2,3,4) 662 UNION 663 SELECT user_id FROM users_table WHERE user_id IN (5,6,7,8) 664 ) a 665 666 ON (a.user_id = foo.user_id) 667 JOIN 668 669 (SELECT value_1, value_2 FROM users_table) as bar 670 671 ON(foo.user_id = bar.value_1) 672 WHERE 673 value_2 IN (SELECT value_1 FROM users_table WHERE value_2 < 1) 674 AND 675 value_1 IN (SELECT value_2 FROM users_table WHERE value_1 < 2) 676 AND 677 foo.user_id IN (SELECT users_table.user_id FROM users_table, events_table WHERE users_table.user_id = events_table.user_id AND event_type IN (1,2)) 678$$); 679 680-- make sure that we don't pick the refeence table as 681-- the anchor 682SELECT true AS valid FROM explain_json_2($$ 683 684 SELECT count(*) 685 FROM 686 users_reference_table AS users_table_ref, 687 (SELECT user_id FROM users_Table) AS foo, 688 (SELECT user_id, value_2 FROM events_Table) AS bar 689 WHERE 690 users_table_ref.user_id = foo.user_id 691 AND foo.user_id = bar.value_2; 692$$); 693 694-- make sure to skip calling recursive planning over and over again 695-- for already recursively planned subqueries 696SET client_min_messages TO DEBUG2; 697SELECT * 698FROM 699 (SELECT * 700 FROM users_table 701 OFFSET 0) AS users_table 702JOIN LATERAL 703 (SELECT * 704 FROM 705 (SELECT * 706 FROM events_table 707 WHERE user_id = users_table.user_id) AS bar 708 LEFT JOIN users_table u2 ON u2.user_id = bar.value_2) AS foo ON TRUE; 709 710-- similar to the above, make sure that we skip recursive planning when 711-- the subquery doesn't have any tables 712SELECT true AS valid FROM explain_json_2($$ 713SELECT * 714FROM 715 (SELECT 1 AS user_id) AS users_table 716JOIN LATERAL 717 (SELECT * 718 FROM 719 (SELECT * 720 FROM events_table 721 WHERE user_id = users_table.user_id) AS bar 722 LEFT JOIN users_table u2 ON u2.user_id = bar.value_2) AS foo ON TRUE 723$$); 724 725-- similar to the above, make sure that we skip recursive planning when 726-- the subquery contains only intermediate results 727SELECT * 728FROM 729 ( 730 SELECT * FROM( 731 SELECT * 732 FROM users_table 733 EXCEPT 734 SELECT * 735 FROM users_table 736 WHERE value_1 > 2 737 ) AS users_table_union 738 ) AS users_table_limited 739JOIN LATERAL 740 (SELECT * 741 FROM 742 (SELECT * 743 FROM 744 (SELECT * 745 FROM events_table WHERE value_3 > 4 746 INTERSECT 747 SELECT * 748 FROM events_table 749 WHERE value_2 > 2 750 ) AS events_table 751 WHERE user_id = users_table_limited.user_id) AS bar 752 LEFT JOIN users_table u2 ON u2.user_id = bar.value_2) AS foo ON TRUE; 753 754-- similar to the above, but this time there are multiple 755-- non-colocated subquery joins one of them contains lateral 756-- join 757SELECT count(*) FROM events_table WHERE user_id NOT IN 758( 759 SELECT users_table_limited.user_id 760 FROM 761 (SELECT * 762 FROM users_table 763 EXCEPT 764 SELECT * 765 FROM users_table 766 WHERE value_1 > 2 767 ) AS users_table_limited 768 JOIN LATERAL 769 (SELECT * 770 FROM 771 (SELECT * 772 FROM 773 (SELECT * 774 FROM events_table WHERE value_3 > 4 775 INTERSECT 776 SELECT * 777 FROM events_table 778 WHERE value_2 > 2 779 ) AS events_table 780 WHERE user_id = users_table_limited.user_id) AS bar 781 LEFT JOIN users_table u2 ON u2.user_id = bar.value_2) AS foo ON TRUE 782 ); 783 784 785-- make sure that non-colocated subquery joins work fine in 786-- modifications 787CREATE TABLE table1 (id int, tenant_id int); 788CREATE VIEW table1_view AS SELECT * from table1 where id < 100; 789CREATE TABLE table2 (id int, tenant_id int) partition by range(tenant_id); 790CREATE TABLE table2_p1 PARTITION OF table2 FOR VALUES FROM (1) TO (10); 791 792-- modifications on the partitons are only allowed with rep=1 793SET citus.shard_replication_factor TO 1; 794 795SELECT create_distributed_table('table2','tenant_id'); 796SELECT create_distributed_table('table1','tenant_id'); 797 798-- all of the above queries are non-colocated subquery joins 799-- because the views are replaced with subqueries 800UPDATE table2 SET id=20 FROM table1_view WHERE table1_view.id=table2.id; 801UPDATE table2_p1 SET id=20 FROM table1_view WHERE table1_view.id=table2_p1.id; 802 803RESET client_min_messages; 804DROP FUNCTION explain_json_2(text); 805 806SET search_path TO 'public'; 807DROP SCHEMA non_colocated_subquery CASCADE; 808