github.com/cockroachdb/cockroach@v20.2.0-alpha.1+incompatible/pkg/sql/opt/exec/execbuilder/testdata/distsql_tighten_spans (about) 1 # LogicTest: 5node 2 3 # This test verifies that we correctly tighten spans during index selection as 4 # well as after partitioning spans in distsql. 5 6 ################ 7 # Schema setup # 8 ################ 9 10 statement ok 11 CREATE TABLE p1 ( 12 a INT, 13 b INT, 14 PRIMARY KEY (a, b), 15 INDEX b (b) 16 ) 17 18 statement ok 19 CREATE TABLE c1 ( 20 a INT, 21 b INT, 22 PRIMARY KEY (a,b) 23 ) INTERLEAVE IN PARENT p1 (a, b) 24 25 statement ok 26 CREATE TABLE p2 ( 27 i INT PRIMARY KEY, 28 d INT 29 ) 30 31 statement ok 32 CREATE INDEX p2_id ON p2 (i, d) INTERLEAVE IN PARENT p2 (i) 33 34 statement ok 35 CREATE TABLE bytes_t (a BYTES PRIMARY KEY) 36 37 statement ok 38 CREATE TABLE decimal_t (a DECIMAL PRIMARY KEY) 39 40 ############################ 41 # Split ranges for distsql # 42 ############################ 43 44 # Perform some splits to exercise distsql partitioning as well. 45 46 # Create split points at X = 2. 47 # Also split at the beginning of each index (0 for ASC, 100 for DESC) to 48 # prevent interfering with previous indexes/tables. 49 50 # p1 table (interleaved index) 51 statement ok 52 ALTER TABLE p1 SPLIT AT VALUES(2) 53 54 # Create a split at /2/# 55 statement ok 56 ALTER TABLE c1 SPLIT AT VALUES(2,1) 57 58 # Split index 59 statement ok 60 ALTER INDEX b SPLIT AT VALUES(0) 61 62 statement ok 63 ALTER INDEX b SPLIT AT VALUES(2) 64 65 # p2 table (interleaved index) 66 statement ok 67 ALTER TABLE p2 SPLIT AT VALUES(0) 68 69 statement ok 70 ALTER TABLE p2 SPLIT AT VALUES(2) 71 72 # Create a split at /2/# 73 statement ok 74 ALTER INDEX p2_id SPLIT AT VALUES(2) 75 76 ##################### 77 # Distribute ranges # 78 ##################### 79 80 # Distribute our ranges across the first 3 (for primary index) and last 2 81 # (for seconary indexes) nodes. 82 83 statement ok 84 ALTER TABLE p1 EXPERIMENTAL_RELOCATE SELECT ARRAY[i], i FROM generate_series(1,3) AS g(i) 85 86 statement ok 87 ALTER INDEX b EXPERIMENTAL_RELOCATE SELECT ARRAY[i+3], i FROM generate_series(1,2) AS g(i) 88 89 # Interleaved index table 90 statement ok 91 ALTER TABLE p2 EXPERIMENTAL_RELOCATE SELECT ARRAY[i], i FROM generate_series(1,3) as g(i) 92 93 ############################# 94 # Verify range distribution # 95 ############################# 96 97 # p1 table (interleaved table) 98 99 query TTTI colnames 100 SELECT start_key, end_key, replicas, lease_holder from [SHOW RANGES FROM TABLE p1] 101 ---- 102 start_key end_key replicas lease_holder 103 NULL /2 {1} 1 104 /2 /2/1/#/54/1 {2} 2 105 /2/1/#/54/1 NULL {3} 3 106 107 # Indexes 108 109 query TTTI colnames 110 SELECT start_key, end_key, replicas, lease_holder from [SHOW RANGES FROM INDEX b] 111 ---- 112 start_key end_key replicas lease_holder 113 NULL /0 {3} 3 114 /0 /2 {4} 4 115 /2 NULL {5} 5 116 117 # p2 table (interleaved index) 118 119 query TTTI colnames 120 SELECT start_key, end_key, replicas, lease_holder from [SHOW RANGES FROM TABLE p2] 121 ---- 122 start_key end_key replicas lease_holder 123 NULL /0 {1} 1 124 /0 /2 {1} 1 125 /2 /2/#/55/2 {2} 2 126 /2/#/55/2 NULL {3} 3 127 128 ############### 129 # Query tests # 130 ############### 131 132 # p1 table 133 134 # Secondary index should not be tightened. 135 query TTT 136 EXPLAIN SELECT * FROM p1 WHERE b <= 3 137 ---- 138 · distributed true 139 · vectorized true 140 scan · · 141 · table p1@b 142 · spans -/4 143 144 # Partial predicate on primary key should not be tightened. 145 query TTT 146 EXPLAIN SELECT * FROM p1 WHERE a <= 3 147 ---- 148 · distributed true 149 · vectorized true 150 scan · · 151 · table p1@primary 152 · spans -/4 153 154 # Tighten end key if span contains full primary key. 155 query TTT 156 EXPLAIN SELECT * FROM p1 WHERE a <= 3 AND b <= 3 157 ---- 158 · distributed true 159 · vectorized true 160 scan · · 161 · table p1@primary 162 · spans -/3/3/# 163 · filter b <= 3 164 165 query TTT 166 EXPLAIN SELECT * FROM p1 WHERE a <= 3 AND b < 4 167 ---- 168 · distributed true 169 · vectorized true 170 scan · · 171 · table p1@primary 172 · spans -/3/3/# 173 · filter b < 4 174 175 # Mixed bounds. 176 query TTT 177 EXPLAIN SELECT * FROM p1 WHERE a >= 2 AND b <= 3 178 ---- 179 · distributed true 180 · vectorized true 181 scan · · 182 · table p1@primary 183 · spans /2- 184 · filter b <= 3 185 186 # Edge cases. 187 188 query TTT 189 EXPLAIN SELECT * FROM p1 WHERE a <= 0 AND b <= 0 190 ---- 191 · distributed true 192 · vectorized true 193 scan · · 194 · table p1@primary 195 · spans -/0/0/# 196 · filter b <= 0 197 198 query TTT 199 EXPLAIN SELECT * FROM p1 WHERE a <= -1 AND b <= -1 200 ---- 201 · distributed true 202 · vectorized true 203 scan · · 204 · table p1@primary 205 · spans -/-1/-1/# 206 · filter b <= -1 207 208 query TTT 209 EXPLAIN SELECT * FROM p1 WHERE a = 1 AND b <= -9223372036854775808 210 ---- 211 · distributed true 212 · vectorized true 213 scan · · 214 · table p1@primary 215 · spans /1-/1/-9223372036854775808/# 216 217 query TTT 218 EXPLAIN SELECT * FROM p1 WHERE a = 1 AND b <= 9223372036854775807 219 ---- 220 · distributed true 221 · vectorized true 222 scan · · 223 · table p1@primary 224 · spans /1-/1/9223372036854775807/# 225 226 # Table c1 (interleaved table) 227 228 # Partial primary key does not tighten. 229 230 query TTT 231 EXPLAIN SELECT * FROM c1 WHERE a <= 3 232 ---- 233 · distributed true 234 · vectorized true 235 scan · · 236 · table c1@primary 237 · spans -/4 238 239 # Tighten span on fully primary key. 240 query TTT 241 EXPLAIN SELECT * FROM c1 WHERE a <= 3 AND b <= 3 242 ---- 243 · distributed true 244 · vectorized true 245 scan · · 246 · table c1@primary 247 · spans -/3/3/#/54/1/# 248 · filter b <= 3 249 250 # Table p2 with interleaved index. 251 252 # From the primary index. 253 254 # Lower bound (i >= 2) 255 query TTT 256 EXPLAIN SELECT * FROM p2 WHERE i >= 2 257 ---- 258 · distributed true 259 · vectorized true 260 scan · · 261 · table p2@primary 262 · spans /2- 263 264 # Upper bound (i <= 5) 265 266 query TTT 267 EXPLAIN SELECT * FROM p2 WHERE i <= 5 268 ---- 269 · distributed true 270 · vectorized true 271 scan · · 272 · table p2@primary 273 · spans -/5/# 274 275 # From the interleaved index: no tightening at all. 276 277 # Lower bound (i >= 1 AND d >= 2) 278 279 # Note 53/2 refers to the 2nd index (after primary index) of table p2. 280 query TTT 281 EXPLAIN SELECT * FROM p2@p2_id WHERE i >= 1 AND d >= 2 282 ---- 283 · distributed true 284 · vectorized true 285 scan · · 286 · table p2@p2_id 287 · spans /1/#/55/2/2- 288 · filter d >= 2 289 290 # Upper bound (i <= 6 AND d <= 5) 291 292 query TTT 293 EXPLAIN SELECT * FROM p2@p2_id WHERE i <= 6 AND d <= 5 294 ---- 295 · distributed true 296 · vectorized true 297 scan · · 298 · table p2@p2_id 299 · spans -/6/#/55/2/6 300 · filter d <= 5 301 302 # IS NULL 303 304 query TTT 305 EXPLAIN SELECT * FROM p2@p2_id WHERE i >= 1 AND d IS NULL 306 ---- 307 · distributed true 308 · vectorized true 309 scan · · 310 · table p2@p2_id 311 · spans /1/#/55/2/NULL- 312 · filter d IS NULL 313 314 # IS NOT NULL 315 316 query TTT 317 EXPLAIN SELECT * FROM p2@p2_id WHERE i >= 1 AND d IS NOT NULL 318 ---- 319 · distributed true 320 · vectorized true 321 scan · · 322 · table p2@p2_id 323 · spans /1/#/55/2/!NULL- 324 · filter d IS NOT NULL 325 326 # String table 327 328 query TTT colnames 329 EXPLAIN SELECT * FROM bytes_t WHERE a = 'a' 330 ---- 331 tree field description 332 · distributed true 333 · vectorized true 334 scan · · 335 · table bytes_t@primary 336 · spans /"a"-/"a"/# 337 338 # No tightening. 339 340 query TTT colnames 341 EXPLAIN SELECT * FROM bytes_t WHERE a < 'aa' 342 ---- 343 tree field description 344 · distributed true 345 · vectorized true 346 scan · · 347 · table bytes_t@primary 348 · spans -/"aa" 349 350 query TTT colnames 351 EXPLAIN SELECT * FROM decimal_t WHERE a = 1.00 352 ---- 353 tree field description 354 · distributed true 355 · vectorized true 356 scan · · 357 · table decimal_t@primary 358 · spans /1-/1/# 359 360 # No tightening. 361 362 query TTT colnames 363 EXPLAIN SELECT * FROM decimal_t WHERE a < 2 364 ---- 365 tree field description 366 · distributed true 367 · vectorized true 368 scan · · 369 · table decimal_t@primary 370 · spans -/2