github.com/cockroachdb/cockroach@v20.2.0-alpha.1+incompatible/pkg/sql/opt/exec/execbuilder/testdata/distsql_interleaved_join (about) 1 # LogicTest: 5node 2 3 # The following tables form the interleaved hierarchy: 4 # name: primary key: # rows: 'a' = id mod X : 5 # parent1 (pid1) 40 8 6 # child1 (pid1, cid1) 150 66 7 # grandchild1 (pid1, cid1, gcid1) 410 201 8 # child2 (pid1, cid2, cid3) 15 7 9 # grandchild2 (pid1, cid2, cid3, gcid2) 51 13 10 # parent2 (pid2) 5 2 11 # Additional rows in child1, child2, and grandchild1 with no corresponding 12 # parent row are also inserted. 13 # 14 # All IDs belonging to a table (pid1 --> parent1, cid1 --> child1, cid2,cid3 15 # --> child2, etc.) start from 1 up to (# rows). 16 # Foreign keys are modded by their ancestor's (# rows). For example, for child1 17 # row with cid1=500, we take ((cid1-1) % 200 + 1) = 100 as pid1. 18 # One exception is cid3, which is taken as cid2 % 15. 19 # There's a column 'a' that's modded by a factor. 20 # 21 # This allows us to test the following edge cases (in order of tests): 22 # - one-to-many (parent1 - child1) 23 # - one-to-one and one-to-none (parent1 - child2) 24 # - parent-grandchild (parent1 - grandchild1) 25 # - multiple interleaved columns (child2 - grandchild2) 26 # - additional ancestor above (child2 - grandchild2) 27 # - no interleaved relationship (parent1 - parent2, parent2 - child1) 28 # - TODO(richardwu): sibling-sibling (child1 - child2) 29 30 ################# 31 # Create tables # 32 ################# 33 34 statement ok 35 CREATE TABLE parent1 (pid1 INT PRIMARY KEY, pa1 INT) 36 37 statement ok 38 CREATE TABLE parent2 (pid2 INT PRIMARY KEY, pa2 INT) 39 40 statement ok 41 CREATE TABLE child1 ( 42 pid1 INT, 43 cid1 INT, 44 ca1 INT, 45 PRIMARY KEY(pid1, cid1) 46 ) 47 INTERLEAVE IN PARENT parent1 (pid1) 48 49 statement ok 50 CREATE TABLE child2 ( 51 pid1 INT, 52 cid2 INT, 53 cid3 INT, 54 ca2 INT, 55 PRIMARY KEY(pid1, cid2, cid3) 56 ) 57 INTERLEAVE IN PARENT parent1 (pid1) 58 59 statement ok 60 CREATE TABLE grandchild1 ( 61 pid1 INT, 62 cid1 INT, 63 gcid1 INT, 64 gca1 INT, 65 PRIMARY KEY(pid1, cid1, gcid1) 66 ) 67 INTERLEAVE IN PARENT child1 (pid1, cid1) 68 69 # No foreign key since we are permitting the rows to overflow out of child2 70 # for pid1 > 15. 71 statement ok 72 CREATE TABLE grandchild2 ( 73 pid1 INT, 74 cid2 INT, 75 cid3 INT, 76 gcid2 INT, 77 gca2 INT, 78 PRIMARY KEY(pid1, cid2, cid3, gcid2) 79 ) 80 INTERLEAVE IN PARENT child2 (pid1, cid2, cid3) 81 82 #################### 83 # Split our ranges # 84 #################### 85 86 # Split at parent1 key into five parts. 87 statement ok 88 ALTER TABLE parent1 SPLIT AT SELECT i FROM generate_series(8, 32, 8) AS g(i) 89 90 # Split at child1 keys in between parent1 parts (total 10 parts). 91 statement ok 92 ALTER TABLE child1 SPLIT AT SELECT pid1, pid1 + 40 FROM 93 generate_series(4, 36, 8) AS g(pid1) 94 95 # Split at grandchild2 keys in between the 10 parts (total 20 parts). 96 statement ok 97 ALTER TABLE grandchild2 SPLIT AT SELECT pid1, pid1 + 40, pid1, pid1 FROM 98 generate_series(2, 38, 4) AS g(pid1) 99 100 # Relocate the twenty parts to the five nodes. 101 statement ok 102 ALTER TABLE grandchild2 EXPERIMENTAL_RELOCATE 103 SELECT ARRAY[((i-1)/2)::INT%5+1], i, i+20, i, i FROM generate_series(1, 39, 2) AS g(i) 104 105 # Verify data placement. 106 query TTTI colnames,rowsort 107 SELECT start_key, end_key, replicas, lease_holder from [SHOW RANGES FROM TABLE parent1] 108 ---- 109 start_key end_key replicas lease_holder 110 NULL /2/#/56/1/42/2/#/58/1/2 {1} 1 111 /2/#/56/1/42/2/#/58/1/2 /4/#/55/1/44 {2} 2 112 /4/#/55/1/44 /6/#/56/1/46/6/#/58/1/6 {3} 3 113 /6/#/56/1/46/6/#/58/1/6 /8 {4} 4 114 /8 /10/#/56/1/50/10/#/58/1/10 {5} 5 115 /10/#/56/1/50/10/#/58/1/10 /12/#/55/1/52 {1} 1 116 /12/#/55/1/52 /14/#/56/1/54/14/#/58/1/14 {2} 2 117 /14/#/56/1/54/14/#/58/1/14 /16 {3} 3 118 /16 /18/#/56/1/58/18/#/58/1/18 {4} 4 119 /18/#/56/1/58/18/#/58/1/18 /20/#/55/1/60 {5} 5 120 /20/#/55/1/60 /22/#/56/1/62/22/#/58/1/22 {1} 1 121 /22/#/56/1/62/22/#/58/1/22 /24 {2} 2 122 /24 /26/#/56/1/66/26/#/58/1/26 {3} 3 123 /26/#/56/1/66/26/#/58/1/26 /28/#/55/1/68 {4} 4 124 /28/#/55/1/68 /30/#/56/1/70/30/#/58/1/30 {5} 5 125 /30/#/56/1/70/30/#/58/1/30 /32 {1} 1 126 /32 /34/#/56/1/74/34/#/58/1/34 {2} 2 127 /34/#/56/1/74/34/#/58/1/34 /36/#/55/1/76 {3} 3 128 /36/#/55/1/76 /38/#/56/1/78/38/#/58/1/38 {4} 4 129 /38/#/56/1/78/38/#/58/1/38 NULL {5} 5 130 131 query TTTI colnames,rowsort 132 SELECT start_key, end_key, replicas, lease_holder from [SHOW RANGES FROM TABLE child1] 133 ---- 134 start_key end_key replicas lease_holder 135 NULL /2/#/56/1/42/2/#/58/1/2 {1} 1 136 /2/#/56/1/42/2/#/58/1/2 /4/#/55/1/44 {2} 2 137 /4/#/55/1/44 /6/#/56/1/46/6/#/58/1/6 {3} 3 138 /6/#/56/1/46/6/#/58/1/6 /8 {4} 4 139 /8 /10/#/56/1/50/10/#/58/1/10 {5} 5 140 /10/#/56/1/50/10/#/58/1/10 /12/#/55/1/52 {1} 1 141 /12/#/55/1/52 /14/#/56/1/54/14/#/58/1/14 {2} 2 142 /14/#/56/1/54/14/#/58/1/14 /16 {3} 3 143 /16 /18/#/56/1/58/18/#/58/1/18 {4} 4 144 /18/#/56/1/58/18/#/58/1/18 /20/#/55/1/60 {5} 5 145 /20/#/55/1/60 /22/#/56/1/62/22/#/58/1/22 {1} 1 146 /22/#/56/1/62/22/#/58/1/22 /24 {2} 2 147 /24 /26/#/56/1/66/26/#/58/1/26 {3} 3 148 /26/#/56/1/66/26/#/58/1/26 /28/#/55/1/68 {4} 4 149 /28/#/55/1/68 /30/#/56/1/70/30/#/58/1/30 {5} 5 150 /30/#/56/1/70/30/#/58/1/30 /32 {1} 1 151 /32 /34/#/56/1/74/34/#/58/1/34 {2} 2 152 /34/#/56/1/74/34/#/58/1/34 /36/#/55/1/76 {3} 3 153 /36/#/55/1/76 /38/#/56/1/78/38/#/58/1/38 {4} 4 154 /38/#/56/1/78/38/#/58/1/38 NULL {5} 5 155 156 query TTTI colnames,rowsort 157 SELECT start_key, end_key, replicas, lease_holder from [SHOW RANGES FROM TABLE grandchild1] 158 ---- 159 start_key end_key replicas lease_holder 160 NULL /2/#/56/1/42/2/#/58/1/2 {1} 1 161 /2/#/56/1/42/2/#/58/1/2 /4/#/55/1/44 {2} 2 162 /4/#/55/1/44 /6/#/56/1/46/6/#/58/1/6 {3} 3 163 /6/#/56/1/46/6/#/58/1/6 /8 {4} 4 164 /8 /10/#/56/1/50/10/#/58/1/10 {5} 5 165 /10/#/56/1/50/10/#/58/1/10 /12/#/55/1/52 {1} 1 166 /12/#/55/1/52 /14/#/56/1/54/14/#/58/1/14 {2} 2 167 /14/#/56/1/54/14/#/58/1/14 /16 {3} 3 168 /16 /18/#/56/1/58/18/#/58/1/18 {4} 4 169 /18/#/56/1/58/18/#/58/1/18 /20/#/55/1/60 {5} 5 170 /20/#/55/1/60 /22/#/56/1/62/22/#/58/1/22 {1} 1 171 /22/#/56/1/62/22/#/58/1/22 /24 {2} 2 172 /24 /26/#/56/1/66/26/#/58/1/26 {3} 3 173 /26/#/56/1/66/26/#/58/1/26 /28/#/55/1/68 {4} 4 174 /28/#/55/1/68 /30/#/56/1/70/30/#/58/1/30 {5} 5 175 /30/#/56/1/70/30/#/58/1/30 /32 {1} 1 176 /32 /34/#/56/1/74/34/#/58/1/34 {2} 2 177 /34/#/56/1/74/34/#/58/1/34 /36/#/55/1/76 {3} 3 178 /36/#/55/1/76 /38/#/56/1/78/38/#/58/1/38 {4} 4 179 /38/#/56/1/78/38/#/58/1/38 NULL {5} 5 180 181 statement ok 182 SET CLUSTER SETTING sql.distsql.interleaved_joins.enabled = true; 183 184 ##################### 185 # Interleaved joins # 186 ##################### 187 188 # Select over two ranges for parent/child with split at children key. 189 query T 190 SELECT url FROM [EXPLAIN (DISTSQL) SELECT * FROM parent1 JOIN child1 USING(pid1) WHERE pid1 >= 3 AND pid1 <= 5] 191 ---- 192 https://cockroachdb.github.io/distsqlplan/decode.html#eJzMUsFu00AQvfMVq3eKYVC9Dr1YiuSKGnAVnOIUgVR8sLzT1JLrNbtrBKry78jeSDgVVMCpN8-89-b5zc497NcWMbbpOn19JQbTijfF5r24Tj9frs-yXCzOs-3V9sM6EAfKc0_oK8Odk-Jik-Wivm1aJcXHbZa_FYu-UTIQn96lReoL8WUIwyWvxDIQZ_n5vFmvxGlQgtBpxXl1xxbxNSQIEQhLlITe6Jqt1WaE7idipr4jDglN1w_Ot13jWkaModNGsWEFgmJXNe2Il_uSUGvDiH9Rc_1S9yfRAyJBD-4wtiRYV-0YcbSnmbWcWf9mcNY5Ni1X37jgSrG50E3H5iQ8csKabxzGeM1dZX4kh4WCUDS72znitwuCnwNCyzdukcgXwcqM3OkThM3gYpFISiJKXlFyij-lkUdpov9KI59omvDxNAXbXneW_-rVw_FsWO3Y35jVg6n50uh6svHlZtJNDcXWeXTpi6zz0PiDc7F8VBweieVDcfRP4nL_7GcAAAD__-nTPX0= 193 194 # Swap parent1 and child1 tables. 195 query T 196 SELECT url FROM [EXPLAIN (DISTSQL) SELECT * FROM child1 JOIN parent1 USING(pid1) WHERE pid1 >= 3 AND pid1 <= 5] 197 ---- 198 https://cockroachdb.github.io/distsqlplan/decode.html#eJzMUtFulEAUffcrJuep6DVlIH0h2YTGotKsbGVrNKk8EOZ2S0IZnBmMptl_NzCbyDbaqE--ce855x7OnfsA-6VDgm22zl5di9F04nW5eSdusk9X6_O8ECcX-fZ6-34diAPluSc0d22npLjc5IUYasO9k-LDNi_eiJOhVTIQH99mZeYL8XkMw5hXIg7EeXGxbDYrcRZUIPRacVHfs0VyAwlCBEKMijAY3bC12kzQw0zM1TckIaHth9H5tmtdx0gw9tooNqxAUOzqtpvwal8RGm0YyU9qoV_q4TR6RCTo0R3GVgTr6h0jifa0sJYL618MznvHpuP6K5dcKzaXuu3ZnIZHTljzrcMUr72vzffULxSEst3dLYHDdkHwc0Do-NadpPJFsDITef4EYTO6RKSS0ojSmNIz_C6NPEoT_VMa-Z-mCZ9OU7IddG_5j149nM6G1Y79jVk9moavjG5mG19uZt3cUGydR2Nf5L2Hph9ciuWT4vBILB-Lo78SV_tnPwIAAP__2mo9ew== 199 200 # Select over two ranges for parent/child with split at grandchild key. 201 # Also, rows with pid1 <= 30 should have 4 rows whereas pid1 > 30 should 202 # have 3 rows. 203 query T 204 SELECT url FROM [EXPLAIN (DISTSQL) SELECT * FROM parent1 JOIN child1 ON parent1.pid1 = child1.pid1 WHERE parent1.pid1 >= 29 AND parent1.pid1 <= 31 ORDER BY parent1.pid1] 205 ---- 206 https://cockroachdb.github.io/distsqlplan/decode.html#eJzUUdFu0zAUfecrru5TywyLU_ZApEoeNIhMJRnpJEAjD1F811nK4mA7CFT131Hsoi2oQ8Abb7n3nONzz8kO7ZcWE9yk6_T1FQymhTdl8Q6u04-X6_Msh9kq21xt3q_ncKA8DYS-NtQ5DhdFlkNzq1rJoch_rp_3SnJYHoAwfXiblinMJozPQxQtaAnxyzmc56tjaLOEBZ9DUa7SEl59mjhUyLDTkvL6jiwm18iR4RlWDHujG7JWm3G986RMfsMkYqi6fnDjumLYaEOY7NAp1xImmHWOTEv1VyqplmQutOrInI7PSnK1ar3Lmm4cjh7qrjbfxeEiZFiq7e1DJMRHhuEdZNjSjZsJfjJfmpHrP5FhMbgEBGciZmLBxAsmzrDaM9SDuz_WunpLmPA9eyTQfQ5tJBmS07MFP8FqfyR1rp_p_jSesB9zjyfu_J_qjP6POo8EKsn2urP0R1VFY9cktxT-jdWDaejS6MbbhLHwOr-QZF1AeRiyzkP-wIdi_lvxYiKOfhXHf-Vc7Z_8CAAA__8HAVEc 207 208 # Parent-child where pid1 <= 15 have one joined row and pid1 > 15 have no 209 # joined rows (since child2 only has 15 rows up to pid1 = 15). 210 # Note this spans all 5 nodes, which makes sense since we want to read all 211 # parent rows even if child rows are non-existent (so we can support OUTER 212 # joins). 213 # TODO(richardwu): we can remove nodes reading from just one table for INNER 214 # joins or LEFT/RIGHT joins. 215 query T 216 SELECT url FROM [EXPLAIN (DISTSQL) SELECT * FROM parent1 JOIN child2 USING(pid1) WHERE pid1 >= 12 ORDER BY pid1] 217 ---- 218 https://cockroachdb.github.io/distsqlplan/decode.html#eJzMlFFv0zAUhd_5FdZ5atlFi9N0D5EmBViATCUZ6RCgkYcovusiZXFwHASq-t9RkklbEQPWvvTN9j3H536WfNdov1XwsQwX4etL0ZlKvEmT9-Iq_HyxeBnFYnIWLS-XHxZTcSd5Pgqa3HBtpThPolgUN2WlXPFxGcVvxaQplZyKT-_CNBT9WnztHGfGp0K6IknPwlS8-jIUMhBqrTjOb7mFfwUJggvCDAQPhDkyQmN0wW2rTS9ZD4ZI_YDvEMq66Wx_nBEKbRj-Gra0FcNHVFs2FeffOeVcsTnXZc3m2AFBsc3Lakhc8LVFn1He5uZncEcFQlqubh5WRkQQxntAqPjaTgJ5ND01vXZYgpB01heBpMClwKNgTsEJsg1Bd_a-2dbmK4YvN_QI0D2HNooNq-22A3mEbPMH6li_0M3xfEv9WLq7lS53ek55uM_p7gTkHi7QbCeg2eECeTsBeYcL9I-RlHLb6Lrl__qdTv-9Wa14HAet7kzBF0YXQ8y4TQbfcKC4tWNVjpuoHkpDgw_N8q_mky2z87vZ3Sd5to_Z28c8f5I52zz7FQAA__8hjUPy 219 220 # These rows are all on the same node 1 (gateway). 221 query T 222 SELECT url FROM [EXPLAIN (DISTSQL) SELECT * FROM parent1 JOIN child2 USING(pid1) WHERE pid1 IN (1, 11, 21, 31) ORDER BY pid1] 223 ---- 224 https://cockroachdb.github.io/distsqlplan/decode.html#eJyMUE2L2zAQvfdXDHOK24FG6cfBUHDbuK2Da6d2SluCD8aaJALHciV52SX4vy-2Art7WNjbzHtP7-nNBe3_FkMs4zT-uoPBtPCtyH_CPv67TT8nGSzWSbkrf6UBXCWvvaCvDXdOwCZPMmhOqpUr-F0m2XdY9EqKAP78iIsYphkmG0EgBMFKELwTAeTFOi7gy79ZUCFhpyVn9ZkthnsUWBH2RjdsrTYTdJkFibzFcEmoun5wE1wRNtowhhd0yrWMISadY9NyfcMF15LNRquOzdslEkp2tWrnhJQPDqcMda7NXXRtg4SFOp4eM74aEnofJGz54BaReBN8MpN2HpEwH1wIkaBoRdF7ij5Q9BGrkVAP7uGz1tVHxlCM9PJCBdted5afFHjOeTlWhCyP7I9m9WAa3hrdzDF-zed3MyDZOs8KvySdp8ZqfHUfAAD__0Q3qDk= 225 226 # Parent-grandchild. 227 # We add the pa1 > 0 condition so a lookup join is not considered to be a better plan. 228 query T 229 SELECT url FROM [EXPLAIN (DISTSQL) 230 SELECT * FROM parent1 JOIN grandchild2 USING(pid1) WHERE 231 pid1 >= 11 AND pid1 <= 13 232 OR pid1 >= 19 AND pid1 <= 21 233 OR pid1 >= 31 AND pid1 <= 33 234 OR pa1 > 0 235 ] 236 ---- 237 https://cockroachdb.github.io/distsqlplan/decode.html#eJzsldFv0zAQxt_5K6x7SsBocdwOFqlSJtZBppKOtAikkYcovnWRsjg4DgJV_d9RnXZLqxRBK5WXvtW-78vV3--km0P1PQcPJsPR8N2U1Con19H4I7kbfr0dXQYhsa6CyXTyaWSTleRlIygThYVm5GYchGSmkkKkD1kuXPJ5EoTviVVmgtnky4dhNCSWZZkz-VY7DscBYcwml-EVad2mA8K4bZNxRLbFF11il9mdat75ac7X6jJZa4ljx0ChkALD5BEr8O6AAQUXKHCg0AMKfYgplEqmWFVSLSVzYwjET_AcCllR1np5HVNIpULw5qAznSN4EBQaVY7JD4wwEahuZFagOnOAgkCdZLnpOMJ7Dcse2WOifvmrWIGaArnOco3KMwn67fw8zwvC6dvVW_12iOvSKp227WKnzX36YoeP727H-ZbPd5_iXVeAQpTNHtrPbA0MUGiSAQo53mvLZ6_sgVoazE-gMK61R3xGfZf6Per3qX9O_TcQLyjIWj8TqHQyQ_DYgu6g9AynLqQSqFBs0IgXHRxD-VqWZ_0tYXdrd6M122tA2GlAjjog7l6U3BOlo1Lie1HiJ0pHpdTbi1LvROm_rcQOShFWpSwq_KuN5yxXJooZNvu1krVK8VbJ1LRpjmPjMxcCK91UWXMIClMyf7BtZn80n2-YnW2ze0hnfoi5d4i5_0_mePHidwAAAP__aOtjjw== 238 239 query T 240 SELECT url FROM [EXPLAIN (DISTSQL) 241 SELECT * FROM grandchild2 JOIN parent1 USING(pid1) WHERE 242 pid1 >= 11 AND pid1 <= 13 243 OR pid1 >= 19 AND pid1 <= 21 244 OR pid1 >= 31 AND pid1 <= 33 245 OR pa1 > 0 246 ] 247 ---- 248 https://cockroachdb.github.io/distsqlplan/decode.html#eJzslVFv0zAUhd_5FdZ9SsBocdwCi1TJE-sgU0lHWgTSyEMU33WRsjg4DgJV_e-oSbulVYqglbqXvtW-5_g057PkOZQ_MvBgMhwN309JpTNyFY4_kdvht5vRhR8Q69KfTCefRzZZSV42gpmOc5ncp5l0yfXYD0gRa8wNI18mfvCBWEUqmU2-fhyGQ2JZVr0m3yvH4TggjNnkIrgkrd1kQBi3bTIOybb4vEvsMrtTzTuP5nytLuK1ljh2BBRyJTGIH7AE7xYYUHCBAgcKPaDQh4hCoVWCZan0UjKvDb78BZ5DIc2Lyiy3IwqJ0gjeHExqMgQP_NygzjD-iSHGEvW1SnPUZw5QkGjiNKsTR3hnYJmRPsT6t2jVChTCdHbfnq46Xk_IVZoZ1F5dsGjX63meH0zfraoQ7Y7Xo1V5bdv5Tpv7eGKHj--O43zLJ9zH9tcToNBUAxQyvDOWYK_sgV5-YP0TKIwr4xHBqHCp4FT0qOhT8RaiBQVVmScEpYlnCB5b0B2YnuhUudISNcoNHNGiA2SgXqvirL8l7I52N6LZXjeEnW7IcW-Iuxcm94TpuJj4Xpj4CdNxMfX2wtQ7YXq-Z7EDU4hlofIS_-nVc5bPJsoZNm9sqSqd4I1WSR3TLMe1r96QWJpmypqFn9ej-g-2zeyv5jcbZmfb7B6SzA8x9w4x9__LHC1e_AkAAP__Vhplvw== 249 250 query TTT 251 EXPLAIN SELECT * FROM grandchild2 JOIN parent1 USING(pid1) WHERE 252 pid1 >= 11 AND pid1 <= 13 253 OR pid1 >= 19 AND pid1 <= 21 254 OR pid1 >= 31 AND pid1 <= 33 255 OR pa1 > 0 256 ---- 257 · distributed true 258 · vectorized true 259 render · · 260 └── merge-join · · 261 │ type inner 262 │ equality (pid1) = (pid1) 263 │ right cols are key · 264 │ mergeJoinOrder +"(pid1=pid1)" 265 ├── scan · · 266 │ table grandchild2@primary 267 │ spans FULL SCAN 268 └── scan · · 269 · table parent1@primary 270 · spans FULL SCAN 271 · filter ((((pid1 >= 11) AND (pid1 <= 13)) OR ((pid1 >= 19) AND (pid1 <= 21))) OR ((pid1 >= 31) AND (pid1 <= 33))) OR (pa1 > 0) 272 273 # Join on multiple interleaved columns with an overarching ancestor (parent1). 274 # Note there are 5 nodes because the filter cid2 >= 12 AND cid2 <= 14 275 # creates a giant parent span which requires reading from all rows. 276 query T 277 SELECT url FROM [EXPLAIN (DISTSQL) 278 SELECT * FROM child2 JOIN grandchild2 ON 279 child2.pid1=grandchild2.pid1 280 AND child2.cid2=grandchild2.cid2 281 AND child2.cid3=grandchild2.cid3 282 WHERE 283 child2.pid1 >= 5 AND child2.pid1 <= 7 284 OR child2.cid2 >= 12 AND child2.cid2 <= 14 285 OR gcid2 >= 49 AND gcid2 <= 51 286 ] 287 ---- 288 https://cockroachdb.github.io/distsqlplan/decode.html#eJzslU9r20wQxu_vp1jmZL2ZNl5Jzh-BYUPjUAVXTp1AC6kOQjtRBIrWXa1KS_B3L5KsWHLc0jqQXnyzZp6fZ_d5FuYRiq8ZeHA9mU7e3bBSZ-xiPvvAbiefr6ZnfsAG5_71zfXHqcVWkv8bQXyfZtJmlzM_YImOcrkqzAI2GDS_3y5Sydm4265LFjsLzlkrilNpb4iqkvVM5TxXORb79H4yn7BBf-aXcjh0aMxG_T9Z9-IxO7YsNpuvD1ufowW5veWQLcldq2WTHuWerqikpx9xywoBIVeSguiBCvBugQOCDQgOILiAMIIQYaFVTEWhdCV5rAFffgdviJDmi9JU5RAhVprAewSTmozAAz83pDOKvtGcIkn6UqU56cMhIEgyUZrVE6d0Z6CakT5E-odobgYI8zS57zY6HrdddpFmhrRX-Sw69nqe5wc3J6tbi465bWflk-hau0F1jX3CWs7tmLvBuR2DnzhAaG4PCBndmYHgByjsAxTOgTXW1WV6JUCYlcZjgqOwUTgoXBQjFEcojlGcoDiFcImgSrO2vzBRQuDxJf4ionUyZa60JE2yF0W43BJioN6oxeFoQ7h9tN0bzXd6HXz_Ol7vddg7RWTvI3q9iJydInL2Eb1eRO5OEbn7iP7NGtwS0ZyKhcoL-qMtN6zWJMmEmp1aqFLHdKVVXI9pPmc1VxckFabp8ubDz-tWfcAuzH8LH_Xg4SZsv2Sy8xLYfQk8-is4XP73MwAA__8gC4Cq 289 290 query TTT 291 EXPLAIN 292 SELECT * FROM child2 JOIN grandchild2 ON 293 child2.pid1=grandchild2.pid1 294 AND child2.cid2=grandchild2.cid2 295 AND child2.cid3=grandchild2.cid3 296 WHERE 297 child2.pid1 >= 5 AND child2.pid1 <= 7 298 OR child2.cid2 >= 12 AND child2.cid2 <= 14 299 OR gcid2 >= 49 AND gcid2 <= 51 300 ---- 301 · distributed true 302 · vectorized true 303 merge-join · · 304 │ type inner 305 │ equality (pid1, cid2, cid3) = (pid1, cid2, cid3) 306 │ left cols are key · 307 │ mergeJoinOrder +"(pid1=pid1)",+"(cid2=cid2)",+"(cid3=cid3)" 308 ├── scan · · 309 │ table child2@primary 310 │ spans FULL SCAN 311 └── scan · · 312 · table grandchild2@primary 313 · spans FULL SCAN 314 · filter (((pid1 >= 5) AND (pid1 <= 7)) OR ((cid2 >= 12) AND (cid2 <= 14))) OR ((gcid2 >= 49) AND (gcid2 <= 51)) 315 316 # Aggregation over parent and child keys. 317 query T 318 SELECT url FROM [EXPLAIN (DISTSQL) 319 SELECT sum(parent1.pid1), sum(child1.cid1) FROM parent1 JOIN child1 USING(pid1) WHERE 320 pid1 >= 10 AND pid1 <= 39 321 ] 322 ---- 323 https://cockroachdb.github.io/distsqlplan/decode.html#eJzUlVFv2j4Uxd__n8K6T0R_q40ToBAJCbSyLRWFDlptUpeHKLlNI4WYOc60CfHdJ8d0Dah1JuCFt9j3nvzuOY7iNRQ_MvBgMZ6MP9yTUmTk43x2Sx7H3-4mI39KWtf-4n7xZWKRbUtRLlurUGAu2cUqjZlFq63oOc1idhGpHf2KbRO5mflTosvkYeFPP5FWpSNfP4_nY70g30vbdnFAmG2R0fS6vhsNiNu3AqCQ8xin4RIL8B6BAQUHKLhAoQ0UOhBQWAkeYVFwoVrWlcCPf4FnU0jzVSnVdkAh4gLBW4NMZYbggZ9LFBmGP3GOYYzihqc5ikuFiFGGaVYRJ_gkQTHSZSh-D7f-gFYFMiulR4ZqOU-T53qj9v5SeGmkQzW9JgGFDJ9ka8j-twZCdVWPQOFvswvBhgIv5auLQoYJgsc29N-djpJEYBJKLi67u-4WD7etIVPM6smx3gU67wJfOWXORYwC4x1IsDGPxOxDZ3J3ZmIHHbdzhsfd4LSW7dVpjts5KFr3DKNtcFqLtneaaN2Dom2fYbQNTmvR9k8TbfugaO0zjLbBaS3azun__28A51iseF7g3j3w9pttdT9gnKC-TApeigjvBI8qjF7OKl21EWMhdZXphZ_rkhqwLmZGsbMjZvtix0xuQLtGddssbh8zd8co7prJ3WPIV0Zxz0zuHUPum8_KbvhMzB_ZPjvY_PcnAAD__4AGlMA= 324 325 ############### 326 # Outer joins # 327 ############### 328 329 # The schema/values for each table are as follows: 330 # Table: pkey: pkey values (same): values: 331 # outer_p1 (pid1) {1, 2, 3, ... 20} 100 + pkey 332 # outer_c1 (pid1, cid1, cid2) {2, 4, 6, ... 28} 200 + pkey 333 # outer_gc1 (pid1, cid1, cid2, gcid1) {4, 8, 12, ... 36} 300 + pkey 334 335 # Split between 4 nodes based on pkey value (p): 336 # node 1: p - 1 mod 20 ∈ [1...5) 337 # node 2: p - 1 mod 20 ∈ [5...10) 338 # node 3: p - 1 mod 20 ∈ [10...15) 339 # node 4: p - 1 mod 20 ∈ [15...20) 340 341 statement ok 342 CREATE TABLE outer_p1 ( 343 pid1 INT PRIMARY KEY, 344 pa1 INT 345 ) 346 347 statement ok 348 CREATE TABLE outer_c1 ( 349 pid1 INT, 350 cid1 INT, 351 cid2 INT, 352 ca1 INT, 353 PRIMARY KEY (pid1, cid1, cid2) 354 ) INTERLEAVE IN PARENT outer_p1 (pid1) 355 356 statement ok 357 CREATE TABLE outer_gc1 ( 358 pid1 INT, 359 cid1 INT, 360 cid2 INT, 361 gcid1 INT, 362 gca1 INT, 363 PRIMARY KEY (pid1, cid1, cid2, gcid1) 364 ) INTERLEAVE IN PARENT outer_c1 (pid1, cid1, cid2) 365 366 statement ok 367 ALTER TABLE outer_p1 SPLIT AT 368 SELECT i FROM generate_series(0, 40, 5) AS g(i) 369 370 statement ok 371 ALTER TABLE outer_p1 EXPERIMENTAL_RELOCATE 372 SELECT ARRAY[(((i-3)/5)%4)::INT + 1], i FROM generate_series(3, 40, 5) AS g(i) 373 374 query TTTI colnames,rowsort 375 SELECT start_key, end_key, replicas, lease_holder from [SHOW RANGES FROM TABLE outer_p1] 376 ---- 377 start_key end_key replicas lease_holder 378 NULL /0 {5} 5 379 /0 /5 {1} 1 380 /5 /10 {2} 2 381 /10 /15 {3} 3 382 /15 /20 {4} 4 383 /20 /25 {1} 1 384 /25 /30 {2} 2 385 /30 /35 {3} 3 386 /35 /40 {4} 4 387 /40 NULL {5} 5 388 389 ### Begin OUTER queries 390 391 query T 392 SELECT url FROM [EXPLAIN (DISTSQL) SELECT * FROM outer_p1 FULL OUTER JOIN outer_c1 USING (pid1)] 393 ---- 394 https://cockroachdb.github.io/distsqlplan/decode.html#eJzclM9q20AQxu99imVOdjslXknOQVBQcJWioEqpZEMhiCK0E1egaNXdVWkwfveiVUrqNP1n33TbnZlvvvnNYXagvzTgQx7G4WrNetWwyyx9z27Cj9fxRZSw2dsoX-cf4jl7KHk5FsjekPrUcXa5iWN2lUbJQ6jibJNHyTs262rB5wUgtFJQUt6RBv8GOCA4gOACggcISygQOiUr0lqqoWRnBZH4Bv4CoW673gzhAqGSisDfgalNQ-BD1BpSDZVfKaNSkLqSdUvqbLAQZMq6sY4x3RoYPOq7Ut0HPyYHhKzefv41VQ2psRUgrO878kfKdLMOM8sKCA3dmlnAX83fqKGLfQ4tqRWkfLZKL-IwX4WzgGPgzpEFDrLAQxYskQXnUOwRZG8e2bQptwQ-3-Nv-B-x-1YqQYrEAWexf2ZDiXwtu7Plk8LnrZ0Da37U6p3JrN45it-dDL97FL83GX7vKP7FZPj_cnoz0p1sNf3TZVkMp4nElsY7pmWvKrpWsrI24ze1OhsQpM2Y5eMnam3KDvizmP9RfH4gXjwVO6c4u6eIvVPEy_8SF_sX3wMAAP__E6R9Gg== 395 396 query T 397 SELECT url FROM [EXPLAIN (DISTSQL) SELECT * FROM outer_gc1 FULL OUTER JOIN outer_c1 USING (pid1, cid1, cid2)] 398 ---- 399 https://cockroachdb.github.io/distsqlplan/decode.html#eJzklU9r20AQxe_9FMuc7HpKvJKctoLCBlcpCqqUSjYUgihCO3EFilZdrUqD8XcvktImStN_9tEXw76Zt8_zWxhtoflSgguJF3jLFWt1yc7j6D278j5eBmd-yCZv_WSVfAim7K7l-dCgWkP60ybn7HwdBOwi8sM7LedsnfjhOzapC8mR5T9-rWkKCJWSFGY31IB7BRwQLECwAcEBhAWkCLVWOTWN0l3Ltjf48hu4c4SiqlvTySlCrjSBuwVTmJLABb8ypEvKvlJMmSR9oYqK9EkXIclkRdknBnRtoMsobjJ9K36OAQhxsfn8a60vDXcBwuq2JncYOVqvvLgfHBBKujYTwWcorBkKezZ9o7vbRlIXQZUk7bJldBZ4ydKbCI7idIoPBAvFy5Fgo3g1RSYcZGKBTLyGdIegWnOPojHZhsDlO_wNrntKbaW0JE1yhCXdPQE0VC9UfbJ41Ph0tDWK5nu9lHWsL2Xthcs-Vlz2XricY8Xl7IVrfqy4_vKViampVdXQP23FebdWSW5o2MGNanVOl1rlfcxwjHpfL0hqzFDlw8Gv-lL_Bx-a-R_NpyPz_LHZOiTZPsTsHGJe_Jc53T37HgAA__8UBrYr 400 401 query T 402 SELECT url FROM [EXPLAIN (DISTSQL) SELECT * FROM outer_c1 LEFT OUTER JOIN outer_p1 USING (pid1) WHERE pid1 >= 0 AND pid1 < 40] 403 ---- 404 https://cockroachdb.github.io/distsqlplan/decode.html#eJzUVF1vlEAUffdXTM7TomPKAPpA0oTGUqVBqCyNJpUYArcrCWVwGIzNZv-7Yahpt9aPbp_2jXvPPfdwTnJnjeFbCx_LMA7f5GxULTvJ0vfsIvx0Fh9FCVscR8t8-SG22M3I83lAjprUl0qwODzJ2WkaJTetXrDzZZS8ZYu-qYXFPr4Ls3Au2OfRtl06ZLbFjpLju82KebZVgKOTNSXlFQ3wLyDA4YDDBYeHgqNXsqJhkGqC12Y4qn_Atzmarh_11C44KqkI_hq60S3BR9RpUi2V3ymjsiZ1KpuO1IENjpp02bRGLaZLjUmjuSrVdfDLITiyZvX1d6ifoHkVOPLrnvw5jfQ8DzOTCThautSLQLywDtW0xXyCIx21zwLBA4cHLg88HrxGseGQo771MehyRfDFhv_B663FsZOqJkX1lqdi80AaiXwp-wPv3uDD0s6WtNgpZrGXMTs7eXX20qu7k1d3L73-46nIaOhlN9B_XYc9nRfVK5pvcZCjquhMycrIzGVqeKZR06BnVMxF1BnI_OBdsvgr-dUW2b5Pdp6i7D6F7D2KXGye_QwAAP__qyUQHg== 405 406 query T 407 SELECT url FROM [EXPLAIN (DISTSQL) SELECT * FROM outer_p1 RIGHT OUTER JOIN outer_gc1 USING (pid1) WHERE pid1 >= 1 AND pid1 <= 20] 408 ---- 409 https://cockroachdb.github.io/distsqlplan/decode.html#eJzUlG9rnEAQxt_3Uyzz6mynxNULBeHA0JjEYDX1DC2kUsSdXAXj2t21NBz33YtraHJp-u_y6t7dzDPPPvcbGNegv7YQwDJKorcFG1TLTvLsHbuKPl4kR3HKZsfxsli-Txx2N_JyGpCDIfW55yyPT88Kdp7F6V1vVXN2uYzTUzbrG8Ed9uEsyqOpYJ8G1_VpwbjDjtLjh816wTzXKQGhk4LS6oY0BFfAAcEDBB8Q5lAi9ErWpLVUo7y2w7H4DoGL0HT9YMZ2iVBLRRCswTSmJQgg7gyplqpvlFMlSJ3LpiN14AKCIFM1rU1L6NrAmNHcVOo2_AkECHmz-vKr1o_S9BYgFLc9BSyJTgqWXRZRbtcCCC1dm1nIXzkLNb5ifwJCNpiAhRzDNxh6GPoYzjE8hHKDIAdzz6JNtSII-AZ_w3uPOXRSCVIktrjKzRMbSeVr2R_MHw0-He1tRfOdVs33dtXeTrze3vL6O_H6e8v7l09HTrqXnaZ_uhR3PDUSK5ruUstB1XShZG1jpjKzPtsQpM2k8qmIOyvZP_jQzP9oPtwyu4_N3nOS_eeY5_9lLjcvfgQAAP__z_AVOA== 410 411 ######################## 412 # Non-interleaved joins # 413 ######################## 414 415 # Join on siblings uses merge joiner. 416 # TODO(richardwu): Update this once sibling joins are implemented. 417 query T 418 SELECT url FROM [EXPLAIN (DISTSQL) SELECT * FROM child1 JOIN child2 USING(pid1)] 419 ---- 420 https://cockroachdb.github.io/distsqlplan/decode.html#eJy8lk1v2zgQhu_7K4jZS7KhQZH6iC1gAS22aeEgsdM4BQoEPigWawtwJJeSgQaB_3shK20Sf3CoEtKNkvho-HKewzxD8X0JIUwuri7-vyNrtSQfb8fX5P7i683Vf8MROfkwnNxNPl-dkpct_9QbZot0mXByOR6O6rUgXybD0SdyskoTfjoFClmeyFH8KAsI74EDBQEUXKDgAQUfphRWKp_JoshVteV5CwyTHxA6FNJstS6r11MKs1xJCJ-hTMulhBDu4oelvJVxIhVzgEIiyzhdbsusVPoYq6eoPh5QmKzirAhJjwn2N_MDxpkn6nWfcSZInCXEJXm5kKqA6YZCvi5fKr8WfHgii7hYvC8VcZhuphSKMp5LCPmG_lkCX5NAdJlAHE3w-p9cJVLJZPc_Z1Vho10HLuNaqrm8zNNMKsZ3-rmU38qTiJ-d_qvS-aJeAoXxugxJxGkkaOTSyKdRQKPznfSvyVyDZOvs0KkPHniU9_IV4_7OzsO1vXe1ubkX3MzsI1r0mFet_eq915blDdIEZpZ3m0YcTdOh8bx944V5n4ShdW_60WPB76YF9bpqWtCWdQ3SnBta12kacTRNh9aJ9q1zzfvkGlp3uDU91m_LtQYZ-oaudZBBHM3QoWFu-4Z55t3xDA3r9xh3fnXId14eqhZxpy3JGsQYGErWTQxxNEaHnnndTowHjnMri1WeFdJoHnSqQDKZy_qainytZvJG5bNtmfpxvOW2E0kii7L-KuqHYVZ_qg5oDgc28MAG5lbn5r6e5g2uTDSDAxt4YANzq3PvXNkeLXZp5y3t6u_b1cL8_Z05u7RnI7geRgTXw4jgehgTHKERwX0bwfUwIrgeRgTXw5jgCI0IHtgIfm6jqB5GFNXDiKJ6GFMUoRFF-zaK6mFEUT2MKKqHMUURGlF0YKMot5oTEBqRFKERSxEa0xTDsVnBbliwmxbsxgXLecFuYOBWEwPfGxka2aqnMVv1NGarnkZtRXDM1ibD0n7PmkxLTWnM1kbzUmMcs3VveNDaOt389TMAAP__xGu2hQ== 421 422 # Join on non-interleaved tables (with key) uses merge joiner. 423 query T 424 SELECT url FROM [EXPLAIN (DISTSQL) SELECT * FROM parent1 JOIN parent2 ON pid1=pid2] 425 ---- 426 https://cockroachdb.github.io/distsqlplan/decode.html#eJy8lVFr2z4Uxd__n0Lc_0u7KsiSHdc1FDy2DlLapGv6MCh-cOO7xJBanuzASsl3H46zpQmJ5CCcp0i2fr4651xy36H8NYcQxjd3N1-eyELNybfH0T15vvnxcPd5MCRnXwfjp_H3u3OyPvKpOVAkCvOKk9vRYLjeCDIakiJLObmuf0QMFHKZ4jB5xRLCZ-BAQQAFFyh4QKEPMYVCyQmWpVT1kfcVMEh_Q-hQyPJiUdWPYwoTqRDCd6iyao4QwlPyMsdHTFJUzAEKKVZJNl-VKVT2mqi3aH1FoDAukrwMSY8J9j_r-4wzTzTrgHEmSJKnxCWymqEqIV5SkItqXXpT8eWNzJJytl0r4hAvYwpllUwRQr6kByRsviNVigrT3e9c1IU3pxb5vnOrWrtW3KOa4q3MclTM3_7sHH9WZxG_OL9W2XTWLIHCaFGFJOI0EjRyaeTtaN7ocVvoOeKmQ9mTBeN85-T-2t5Wbd6-HXjLdjjQDT3m1et-_dzrqjX46VvjssvWEO3jEW3j-RBDj_n_svKbdZ2V31U84vTxBF3G47aPx20bz_5EeizoKhT39KFcdRmK1z4Ur20oQY9x528wfWe9qZPhTle5HKGjr9MhPkxqq-uJg9c7Udtw51RjeM9FHrEsZF5iqyHr1FIwnWLjTikXaoIPSk5WZZrtaMWtZlaKZdW8XW8GefOqvmB72LOBfRs4sIG5QTTfpZ2PtNDDQgvzbdrZpV2bsPSwISw9bAhLDxvCMmg2iPZswurb2K2HDXbrYYPdethgt0GzQbRvY_eljd162GC3HjbYrYcNdhs0G0QHNnZf2dithw1262GD3XrYYLdBs-mf_5hhKY6kPSvat6IDK5qbhB83MePlf38CAAD___svEUg= 427 428 # Join on non-interleaved column uses hash joiner. 429 query T 430 SELECT url FROM [EXPLAIN (DISTSQL) SELECT * FROM parent1 JOIN child1 ON pa1 = ca1] 431 ---- 432 https://cockroachdb.github.io/distsqlplan/decode.html#eJy8ll1r2zAUhu_3K8TZTTsUZMkfTQwFj61jKV3SNb0YFF-4sdYEUtuTHVgp-e_DcbY2H9WRJ5w7O9Fj6dV5Lt5nKH8tIITJxdXFp1uyVAvy5Wb8jdxd_Li--jgckZPPw8nt5PvVKdks-dAsKBIls4qTy_FwRKaz-SLlZDwiRcLJOZkmPAYKWZ7KUfIoSwjvgAMFARRcoOABBR9iCoXKp7Isc1UveV4Dw_Q3hA6FeVYsq_rnmMI0VxLCZ6jm1UJCCLfJ_ULeyCSVijlAIZVVMl-stynU_DFRT9HmfEBhUiRZGZIeE-w98wPGmSea5z7jTJAkS4lL8momVQnxikK-rDZbv-x4_0RmSTnb3isSEK9iCmWVPEgI-Yr-XwT_cITmVrtN4G4lEG8mePnOMstVKpVMt74U1yS25MA1fE3K2WU-z6RifGeSC_mzOonE6bmaP8yqk8g9BQrjZRWSiNNI0MilkUcjfyfxSxrXIs2Bo47yXl4w7u_mPri3t7U3N3eBG-r8hgs95tXPfv2715XaLeIEZmp3nUanOT-y5rxTzYX5bISpaq-G0GPBv0kFzXM9qaAr1VrEOTNUreM0OtXEkVUTnarmms_GNVXt8Dx6rN-VYC1C9A0F6ySDTiv3yFq5nWrlmU_EM9Wq32Pc-TsW39m81HPhTldmtcgxMDSrqxg6ubwjy-UdrQUeOMiNLIs8K6VRx3PqKDJ9kM3VlPlSTeW1yqfrbZrX8Zpbl4xUllXzr2hehlnzV31AcziwgQc2MLc6N_f1NG9xZaIdHNjAAxuYW51758r2aLFLO69pV3_frhbm23fm7NKejeB6GBFcDyOC62FMcIRGBPdtBNfDiOB6GBFcD2OCIzQieGAj-JmNonoYUVQPI4rqYUxRhEYU7dsoqocRRfUwoqgexhRFaETRgY2i3KonIDQiKUIjliI0pimGY13BrizYtQW7umDZF-wKA7dqDHyvMrSyVU9jtuppzFY9jdqK4JitbcrS_szatKW2NGZrq77UGsds3SsPWlvj1bs_AQAA___1lbKo 433 434 # Prefix join on interleaved columns uses merge joiner. 435 query T 436 SELECT url FROM [EXPLAIN (DISTSQL) SELECT * FROM child2 JOIN grandchild2 USING(pid1, cid2)] 437 ---- 438 https://cockroachdb.github.io/distsqlplan/decode.html#eJzElt9v2joUx9_vX2Gd-9LeGiV2fhQiXSlXd91E1UJXOmlSxUNKPIhEE-YEaVXF_z6FsNFC8IlnEd6S4A_295yPpfMK-fc5BDC6urn6_4Es5Zx8vB_ekserr3c3__UH5OxDf_Qw-nxzTjZL_qkWTGbJPObketgfkKmM0njz4cuoP_hEzhZJzCiZJDE_HwOFNIvFIHoWOQSPwIACBwoOUHCBggdjCguZTUSeZ7Jc8roG-vEPCGwKSbpYFuXnMYVJJgUEr1AkxVxAAA_R01zciygW0rKBQiyKKJmvt1nI5DmSL2F1MKAwWkRpHpCOxa2_Lc-3mOXy6rlrMYuTKI2JQ7JiJmQO4xWFbFlsdt5u-PRCZlE-e79VyGjIYbwaU8iLaCogYCv6ZyG8-hBvStxyEn4wyfavMhkLKeLdv7qgIb8o92-8sqY4t0JOxXWWpEJabKfFc_GtONvQ5__KZDrbvgKF4bIIyDoRDR0aujS8pGGXhr2dmmzDOg3CLtO6ELVnH2SdbGExb2dl_d7uu71Zc2VYM-8PyNKx3PLZK7-7R7wDGoF8jTvQeip-MNUJ7gNr9T7w5i3kDZ1806WO5f9upV89l630j-ikRqBLHSfbTsUPpjqBk7xVJ53mLXQaOlnfsI7VPaKJGjG6Oia2k4UfzHIC_5xW_XObN85t6F-3YzH7V988e_NSNo7ZR1RQI0lPR8HW4vCDcU5goXuySbXmZPciX2RpLhrNoXaZTcRTUVUuz5ZyIu5kNllvU70O19x6_IlFXlS_8uqln1Y_lQdsDvsmcM8EZkbnZp6aZhol43qwbwL3TGBmdO6dku3RfJe239KOut6OEmbva2bv0q6J4GoYEVwNI4KrYUxwhEYE90wEV8OI4GoYEVwNY4IjNCK4byL4pYmiahhRVA0jiqphTFGERhTtmiiqhhFF1TCiqBrGFEVoRNGeiaLMaE5AaERShEYsRWhMUwzHZgWzYcFsWjAbFwznBbOBgRlNDGxvZNCyVU1jtqppzFY1jdqK4JitOsPSfs90piVdGrNVa17SxjFb94YHpa3j1V8_AwAA__-sid2f 439 440 # Subset join on interleaved columns uses hash joiner. 441 query T 442 SELECT url FROM [EXPLAIN (DISTSQL) SELECT * FROM child2 JOIN grandchild2 USING(pid1, cid3)] 443 ---- 444 https://cockroachdb.github.io/distsqlplan/decode.html#eJzElk1v2zgQhu_7K4jZS7KgIZH6iC1gAS22aesgtdM4BQoEOigWawtwJJeSgQaB_3shyWniL45YwupNlPiIfGeewzxD8X0BAUwury__vyMruSDvb8efyP3l15vr_4YjcvZuOLmbfL4-J5st_zQbpvN0kXByNR6OyEzGWbJ58WUyHH0gZ8s0YZRM08Q5j4BClidiFD-KAoJ7YECBAwUHKLhAwYOIwlLmU1EUuay2PNfAMPkBgU0hzZarsnodUZjmUkDwDGVaLgQEcBc_LMStiBMhLRsoJKKM00V9zFKmj7F8CpuLAYXJMs6KgPQsbv1teb7FLJc3z32LWZzEWUIckpdzIQuI1hTyVbk5-fXAhycyj4v59lEho6ED0TqiUJTxTEDA1vT3QniHQ7wpccdJ-NEkr79aZblMhBTJ1s-iisS2HCjHx7iYX-VpJqTFdnq6EN_Ks_qO5__KdDZ_WQCF8aoMSL2iIaehS0Ofhn0aDnYK8JrMMUh24NqjvJcvLebt1uDg2e7W2ay9H6yd5EfM6Flu9exV790TCq8RyNcQvvNU_GiqLuRnncnP2_eLtxTwTUt6lv-rb37zXPXNP6GAGoEudATsOhU_mqoLAXlnAjrt--W0FPBwd3pW_4TaacTo62jXTRZ-NEsXsjmdyea275LbUrZ-z2L2S5M8e7OousTsE_qmkWSg41tncfjROF0o5_6R6fLApW5FscyzQrSaHe0qlkhmoilTka_kVNzIfFof0yzHNVePLIkoyuYrbxbDrPlUXbA97JvAAxOYGd2beWqaaZSM68G-CTwwgZnRvXdKtkfzXdp-SzvqejtKmG3XzN6lXRPB1TAiuBpGBFfDmOAIjQjumQiuhhHB1TAiuBrGBEdoRHDfRPALE0XVMKKoGkYUVcOYogiNKNo3UVQNI4qqYURRNYwpitCIogMTRZnRnIDQiKQIjViK0JimGI7NCmbDgtm0YDYuGM4LZgMDM5oY2N7IoGWrmsZsVdOYrWoatRXBMVt1hqX9nulMS7o0ZqvWvKSNY7buDQ9KW6P1Xz8DAAD__4Wj0Hs= 445 446 # Multi-table staggered join uses interleaved joiner on the bottom join 447 # and a lookup join on the higher join. 448 query T 449 SELECT url FROM[EXPLAIN (DISTSQL) 450 SELECT * FROM grandchild1 451 JOIN child1 USING (pid1, cid1) 452 JOIN parent1 USING (pid1) 453 ORDER BY pid1 454 ] 455 ---- 456 https://cockroachdb.github.io/distsqlplan/decode.html#eJzUVU1r20wQvr-_YplT_GZKvJJsJ4LCto1bFFw7lVNoCToI7cRRq2jV1aq0BP_3IitNpGBLwQYX3zyaefx8zMLcQ_4jARfm48n43RUrdMLe-7OP7Hr85XLyxpuyo3NvfjX_NOmxh5H_q4GFDlMZ3caJ5Oxi5k3Zw-_Pc2_6gR1lseTIoljyXtXOQk2pafR7bOafj3329isrywAQUiVpGt5RDu41cECwAMEGBAcQBhAgZFpFlOdKlyP3K4Anf4HbR4jTrDDl5wAhUprAvQcTm4TABS81pBMKf5JPoSR9oeKU9EkfECSZME5WjBO6MVByxHeh_i1qHgHBjxe39e5jo_ovQEjoxhwJfozCOu691uX8YwkIs8K4THAUFgobhYNigGKIYgTBEkEV5kl-bsIFgcuX-HKLpYzK3Mmgaeuv4ocdAMJEqe9Fxr6pOGUqLVWt1TdCcbZRnLVR3JMmpSVpkk05gh9DsFzjYKpeqeyEN5eyid5u0POt1s8Paf0dFmvrH-5__dZW-VuHlH-HxVr-o_3nb2-Vv31I-XdYrOV_uv_8na3ydw4p_w6LtfzP_u35WSPOpzxTaU4vuiz98jaRXFB1y3JV6IgutYpWNFU5W-FWHyTlpuryqvDSqlUKrIN5K9hqgPlzsNXO3EFtt6KddrCzi-5BK3jYzjzchXnUCj5tZz7dhfmsfVf9jmfS_siecwfL__4EAAD__7LjvSk= 457 458 # Multi-table join with parent1 and child1 at the bottom uses interleaved 459 # joiner but induces a hash joiner on the higher join. 460 query T 461 SELECT url FROM [EXPLAIN (DISTSQL) 462 SELECT * FROM parent1 463 JOIN child1 USING (pid1) 464 JOIN grandchild1 USING (pid1, cid1) 465 ] 466 ---- 467 https://cockroachdb.github.io/distsqlplan/decode.html#eJzUlU9r20AQxe_9FMuckmaKvZL8T1BQad2i4NqpnUIh6CCkiaNW2VVXq9Ji_N2LpDSRXXcVbHDxcTXz9H7zZmFXkH9PwYXFeDJ-e80KlbL389lHdjP-cjV540_Z2Tt_cb34NDlnDy0v64YsVCQ0Z5czf8qiuySNOfu88Kcf2FmWxPy8LixVKOK_q8iisicABCFjmob3lIN7AxwQLECwAcEBhB4ECJmSEeW5VGXLqhL48U9wuwiJyApdfg4QIqkI3BXoRKcELvhCk0op_EFzCmNSlzIRpDpdQIhJh0laOU7oVkPpkdyH6pdXowLCPFneNQuNQQCh_hcgpHSrzzx-gZ51cf5alaLHIyDMCu0yj6NnoWej56DXQ6-P3gCCNYIs9BN-rsMlgcvX-PwRS4x6uE5vc6w_2A9bAoSJlN-KjH2ViWBSlFSPfMOKb1QhGuGsf8I9MRVCqpgUxRtAwXoH_lS-klmHd7c6d3vbG958r93zU9p9y4iN3fePv3trr_ytU8q_ZcRG_oPj52_vlb99Svm3jNjIf3j8_J298ndOKf-WERv5j_7v27MDbk55JkVOz3pZuuXbRPGS6ocsl4WK6ErJqLKpj7NKV32IKdd1ldcHX9SlErAp5kaxtSHm22LL7NxibRvVjlnsHMLdM4r7Zuf-Ic4Do3hodh4e4jwy76rbck3Ml2zbO1i_-B0AAP__qaW5dA==