github.com/cockroachdb/cockroach@v20.2.0-alpha.1+incompatible/pkg/sql/logictest/testdata/logic_test/distsql_tighten_spans (about) 1 # LogicTest: 5node-default-configs 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 # Insert dummy values # 42 ####################### 43 44 statement ok 45 INSERT INTO p1 VALUES 46 (1,10), 47 (2,1), 48 (2,2), 49 (2,8), 50 (3,5), 51 (3,10), 52 (4,1), 53 (4,2), 54 (4,4) 55 56 statement ok 57 INSERT INTO c1 VALUES 58 (1,10), 59 (2,1), 60 (2,4), 61 (2,8), 62 (2,10), 63 (4,2) 64 65 statement ok 66 INSERT INTO p2 VALUES 67 (1, NULL), 68 (2, 2), 69 (3, 1), 70 (4, NULL), 71 (5, NULL), 72 (6, 10), 73 (7, 2), 74 (8, 3) 75 76 statement ok 77 INSERT INTO bytes_t VALUES 78 ('a'), 79 ('aa'), 80 ('b'), 81 ('c'), 82 ('ca') 83 84 statement ok 85 INSERT INTO decimal_t VALUES 86 (1), 87 (1.000001), 88 (1.5), 89 (2), 90 (2.001) 91 92 ############################ 93 # Split ranges for distsql # 94 ############################ 95 96 # Perform some splits to exercise distsql partitioning as well. 97 98 # Create split points at X = 2. 99 # Also split at the beginning of each index (0 for ASC, 100 for DESC) to 100 # prevent interfering with previous indexes/tables. 101 102 # p1 table (interleaved index) 103 statement ok 104 ALTER TABLE p1 SPLIT AT VALUES(2) 105 106 # Create a split at /2/# 107 statement ok 108 ALTER TABLE c1 SPLIT AT VALUES(2,1) 109 110 # Split index 111 statement ok 112 ALTER INDEX b SPLIT AT VALUES(0) 113 114 statement ok 115 ALTER INDEX b SPLIT AT VALUES(2) 116 117 # p2 table (interleaved index) 118 statement ok 119 ALTER TABLE p2 SPLIT AT VALUES(0) 120 121 statement ok 122 ALTER TABLE p2 SPLIT AT VALUES(2) 123 124 # Create a split at /2/# 125 statement ok 126 ALTER INDEX p2_id SPLIT AT VALUES(2) 127 128 ##################### 129 # Distribute ranges # 130 ##################### 131 132 # Distribute our ranges across the first 3 (for primary index) and last 2 133 # (for seconary indexes) nodes. 134 135 statement ok 136 ALTER TABLE p1 EXPERIMENTAL_RELOCATE SELECT ARRAY[i], i FROM generate_series(1,3) AS g(i) 137 138 statement ok 139 ALTER INDEX b EXPERIMENTAL_RELOCATE SELECT ARRAY[i+3], i FROM generate_series(1,2) AS g(i) 140 141 # Interleaved index table 142 statement ok 143 ALTER TABLE p2 EXPERIMENTAL_RELOCATE SELECT ARRAY[i], i FROM generate_series(1,3) as g(i) 144 145 ############################# 146 # Verify range distribution # 147 ############################# 148 149 # p1 table (interleaved table) 150 151 query TTTI colnames 152 SELECT start_key, end_key, replicas, lease_holder FROM [SHOW RANGES FROM TABLE p1] 153 ---- 154 start_key end_key replicas lease_holder 155 NULL /2 {1} 1 156 /2 /2/1/#/54/1 {2} 2 157 /2/1/#/54/1 NULL {3} 3 158 159 # Indexes 160 161 query TTTI colnames 162 SELECT start_key, end_key, replicas, lease_holder FROM [SHOW RANGES FROM INDEX b] 163 ---- 164 start_key end_key replicas lease_holder 165 NULL /0 {3} 3 166 /0 /2 {4} 4 167 /2 NULL {5} 5 168 169 # p2 table (interleaved index) 170 171 query TTTI colnames 172 SELECT start_key, end_key, replicas, lease_holder FROM [SHOW RANGES FROM TABLE p2] 173 ---- 174 start_key end_key replicas lease_holder 175 NULL /0 {1} 1 176 /0 /2 {1} 1 177 /2 /2/#/55/2 {2} 2 178 /2/#/55/2 NULL {3} 3 179 180 ############### 181 # Query tests # 182 ############### 183 184 # p1 table 185 186 # Secondary index should not be tightened. 187 query II rowsort 188 SELECT * FROM p1 WHERE b <= 3 189 ---- 190 2 1 191 2 2 192 4 1 193 4 2 194 195 # Partial predicate on primary key should not be tightened. 196 query II rowsort 197 SELECT * FROM p1 WHERE a <= 3 198 ---- 199 1 10 200 2 1 201 2 2 202 2 8 203 3 5 204 3 10 205 206 # Tighten end key if span contains full primary key. 207 query II rowsort 208 SELECT * FROM p1 WHERE a <= 3 AND b <= 3 209 ---- 210 2 1 211 2 2 212 213 # Mixed bounds. 214 query II rowsort 215 SELECT * FROM p1 WHERE a >= 2 AND b <= 3 216 ---- 217 2 1 218 2 2 219 4 1 220 4 2 221 222 query II rowsort 223 SELECT * FROM c1 WHERE a <= 3 224 ---- 225 1 10 226 2 1 227 2 4 228 2 8 229 2 10 230 231 # Tighten span on fully primary key. 232 query II rowsort 233 SELECT * FROM c1 WHERE a <= 3 AND b <= 3 234 ---- 235 2 1 236 237 # Table p2 with interleaved index. 238 239 # From the primary index. 240 241 # Lower bound (i >= 2) 242 query II rowsort 243 SELECT * FROM p2 WHERE i>= 2 244 ---- 245 2 2 246 3 1 247 4 NULL 248 5 NULL 249 6 10 250 7 2 251 8 3 252 253 # Upper bound (i <= 5) 254 255 query II rowsort 256 SELECT * FROM p2 WHERE i <= 5 257 ---- 258 1 NULL 259 2 2 260 3 1 261 4 NULL 262 5 NULL 263 264 # From the interleaved index: no tightening at all. 265 266 # Lower bound (i >= 1 AND d >= 2) 267 query II rowsort 268 SELECT * FROM p2@p2_id WHERE i>= 1 AND d >= 2 269 ---- 270 2 2 271 6 10 272 7 2 273 8 3 274 275 # Upper bound (i <= 6 AND d <= 5) 276 query II rowsort 277 SELECT * FROM p2@p2_id WHERE i <= 6 AND d <= 5 278 ---- 279 2 2 280 3 1 281 282 # IS NULL 283 query II rowsort 284 SELECT * FROM p2@p2_id WHERE i>= 1 AND d IS NULL 285 ---- 286 1 NULL 287 4 NULL 288 5 NULL 289 290 # IS NOT NULL 291 query II rowsort 292 SELECT * FROM p2@p2_id WHERE i>= 1 AND d IS NOT NULL 293 ---- 294 2 2 295 3 1 296 6 10 297 7 2 298 8 3 299 300 # String table 301 query T 302 SELECT * FROM bytes_t WHERE a = 'a' 303 ---- 304 a 305 306 # No tightening. 307 query T 308 SELECT * FROM bytes_t WHERE a < 'aa' 309 ---- 310 a 311 312 query R 313 SELECT * FROM decimal_t WHERE a = 1.00 314 ---- 315 1 316 317 # No tightening. 318 319 query R rowsort 320 SELECT * FROM decimal_t WHERE a < 2 321 ---- 322 1 323 1.000001 324 1.5