github.com/cockroachdb/cockroach@v20.2.0-alpha.1+incompatible/pkg/sql/logictest/testdata/logic_test/dist_vectorize (about) 1 # LogicTest: 5node 5node-disk 2 3 statement ok 4 CREATE TABLE kv (k INT PRIMARY KEY, v INT) 5 6 statement ok 7 INSERT INTO kv SELECT i, i FROM generate_series(1,5) AS g(i) 8 9 statement ok 10 CREATE TABLE kw (k INT PRIMARY KEY, w INT) 11 12 statement ok 13 INSERT INTO kw SELECT i, i FROM generate_series(1,5) AS g(i) 14 15 # Split into 5 parts, each row from each table goes to one node. 16 statement ok 17 ALTER TABLE kv SPLIT AT SELECT i FROM generate_series(1,5) AS g(i) 18 19 statement ok 20 ALTER TABLE kw SPLIT AT SELECT i FROM generate_series(1,5) AS g(i) 21 22 statement ok 23 ALTER TABLE kv EXPERIMENTAL_RELOCATE SELECT ARRAY[i], i FROM generate_series(1, 5) as g(i) 24 25 statement ok 26 ALTER TABLE kw EXPERIMENTAL_RELOCATE SELECT ARRAY[i], i FROM generate_series(1, 5) as g(i) 27 28 # Verify data placement. 29 query TTTI rowsort 30 SELECT start_key, end_key, replicas, lease_holder from [SHOW RANGES FROM TABLE kv] 31 ---- 32 NULL /1 {1} 1 33 /1 /2 {1} 1 34 /2 /3 {2} 2 35 /3 /4 {3} 3 36 /4 /5 {4} 4 37 /5 NULL {5} 5 38 39 # Verify data placement. 40 query TTTI rowsort 41 SELECT start_key, end_key, replicas, lease_holder from [SHOW RANGES FROM TABLE kw] 42 ---- 43 NULL /1 {5} 5 44 /1 /2 {1} 1 45 /2 /3 {2} 2 46 /3 /4 {3} 3 47 /4 /5 {4} 4 48 /5 NULL {5} 5 49 50 # Verify that EXPLAIN ANALYZE works in a distributed setting. 51 query T 52 SELECT url FROM [EXPLAIN ANALYZE SELECT count(*) FROM kv] 53 ---- 54 https://cockroachdb.github.io/distsqlplan/decode.html#eJzMlV1vm0wQhe_fX7GaK_vVunzacbiKlbqSJQenxlE_IhQRGLkomKW7i5XI8n-vgEgFq12oWsVcssxhzuEZmAOI7wk44M2X8-sNyXlCPqxXN-R-_vl2OVu4ZObOll--zsng_cLbeB-XQ_JaGrI8lYP_h1X9094HCimL0A12KMC5BwMomEDBAgo2UBiDTyHjLEQhGC9KDqVgET2Do1OI0yyXxbFPIWQcwTmAjGWC4MAmeExwjUGEXNOBQoQyiJOyTcbjXcBfrp72QMHLglQ4ZKQVjVe5dIjLUgQKj4EMv6EgLJdZcVw8ROZZUjsq_ApMMJTxPpYvDtHf6UWZkEGSEBnv0CG6AP9IoZK8ehUy2CI4xpF2zzPbbjluA8m4Nm7GuV7duZuH9eqTNxj-nW98xjCXMUvbvZu_9f7Tcp4yHiHHqOHXP6rTGSe0vLubh4W7GVwZb5fOaqQzuk-a0T5pmjnSrDPPWkuiGo1J72bN7E7D7EDDGmn2mWm0JKrRuOgdDas7DasDDXtU_t3OSaMlUY3GtHc07O407A40xqMzs2jJU2Nx2TsWLft8jSJjqcCT3fjrJ-vFzsRoi9WCFSznId5yFpZtqstVqSsPIhSyumtUF4u0ulUYrIsNpdhsiI1Tsanu3NLaUqpttdj-E9_lWyxf6L-d36alsdLSRJ1n0r88F0pLU3Weaf_yXKqnVW_5UNSf2dsk8o___QgAAP__BbZk1w== 55 56 query T 57 SELECT url FROM [EXPLAIN ANALYZE SELECT * FROM kv JOIN kw ON kv.k = kw.k] 58 ---- 59 https://cockroachdb.github.io/distsqlplan/decode.html#eJzUmF9vqkgYxu_3U0zeq3P2jIXhj60kTWx23cTGarf2YncbL6jMKpECC4Nt0_jdN8g2rVZhgIUZ76rOn9_z8gxP532D-B8PLJgORoNf7lESeei3u8kNehj8cTu6Go7R1fhq9OdfA_Tt1-H0fvr76Dv6b-jP2cDVGl1PhmO0ekaTMVqtz1boEq2ez1YzwOAHDh3bTzQG6wEIYNAAgw4YDMBgwgxDGAVzGsdBlA55204YOi9gqRhcP0xY-vUMwzyIKFhvwFzmUbDg3n706B21HRopKmBwKLNdb7tNGLlPdvTaX60BwzS0_dhCHSXd-NFm8yWNUZCwMGEWSieyJPQ-fZUyxtSjc-auXfZqIfVMTYfFzPY8xNwnaiE1htkGQzYlw37HenxFSzte7gL1Ccw2s-0aCwoW2eBqOrtHdD5_6FSITEq1o0o_1gkih0bU2V_nR7ox16gDRbuh0YJeB65PI4XsucOjf7NvffLj-2XkLpbZn4BhkpakT3Bfw30d9416NaQvdJ4wN_CP1fGjRjpHjRL_kP6D0sdBJwgVYu6K5pBi1pdi7Egh_MYmxQdY0TqKLo2xSVWl5xxHWCql2lGlLR5hckpHWON3g8bhe72j1FTSmO9LKL3g8L1USrWjSlv0vXZKvtf53aBz-N7oKKY0biBVlfY4fC-VUu2o0hZ9r5-S7w1-Nxgcvjc70niBVNVpFru-oxBk-w4iKGBLGkmjWTuquUX_G6fk_4IL7B2Nw8CP6d7V5fDKaloa6ixoVvA4SKI5vY2C-Xab7ONkO2_7v6hDY5b9qmUfhv77TzGzGe_VR-W2VRmkrnxIvTpI_IevDBKR8MkRUwQTKWFwTQakrnxIvTpIVQ2ej0QkfHJ7Bm-JSdtnUj8z6btnbn-ynjuZ7HpRbUeQIV8k5SMJiaR8pFqR1AySmEgqYKoVSVXfbKZ8kZSPJCSS8pFqRVIzSGIiqYCpViRVNXi3TCS1g3QuX6jkIwkJlXwkIaGSj1QvVJoxk5h7zoV8oZKPJCRU8pGEhEo-Ur1QqWrwAiYh95yefKFCJOyeFTDVipWG6iQkVwqYxNxWiqDEdNBkbKE12EOr7PIGm2gN1UlQF03GNhop1UdryVFf2nP_X740xCTk2lLAJCZf8pkE5UsBlJh8KdPwbetdXqbj29YbqkzLVw4mQflSqunbFtSXRp2AfJltfvo3AAD__-kC5ik= 60 61 # Verify execution. 62 statement ok 63 SET vectorize = experimental_always 64 65 query I rowsort 66 SELECT kv.k FROM kv JOIN kw ON kv.k = kw.k 67 ---- 68 1 69 2 70 3 71 4 72 5 73 74 statement ok 75 RESET vectorize 76 77 # Regression test for #38919. 78 statement ok 79 SET optimizer = on 80 81 query B 82 SELECT EXISTS(SELECT * FROM kv WHERE k > 2) 83 ---- 84 true 85 86 statement ok 87 RESET optimizer 88 89 query T 90 EXPLAIN (VEC, VERBOSE) SELECT count(*) FROM kv 91 ---- 92 │ 93 ├ Node 1 94 │ └ *colexec.Materializer 95 │ └ *colexec.orderedAggregator 96 │ └ *colexec.oneShotOp 97 │ └ *colexec.distinctChainOps 98 │ └ *colexec.ParallelUnorderedSynchronizer 99 │ ├ *colexec.countOp 100 │ │ └ *colexec.simpleProjectOp 101 │ │ └ *colexec.CancelChecker 102 │ │ └ *colexec.colBatchScan 103 │ ├ *colrpc.Inbox 104 │ ├ *colrpc.Inbox 105 │ ├ *colrpc.Inbox 106 │ └ *colrpc.Inbox 107 ├ Node 2 108 │ └ *colrpc.Outbox 109 │ └ *colexec.deselectorOp 110 │ └ *colexec.countOp 111 │ └ *colexec.simpleProjectOp 112 │ └ *colexec.CancelChecker 113 │ └ *colexec.colBatchScan 114 ├ Node 3 115 │ └ *colrpc.Outbox 116 │ └ *colexec.deselectorOp 117 │ └ *colexec.countOp 118 │ └ *colexec.simpleProjectOp 119 │ └ *colexec.CancelChecker 120 │ └ *colexec.colBatchScan 121 ├ Node 4 122 │ └ *colrpc.Outbox 123 │ └ *colexec.deselectorOp 124 │ └ *colexec.countOp 125 │ └ *colexec.simpleProjectOp 126 │ └ *colexec.CancelChecker 127 │ └ *colexec.colBatchScan 128 └ Node 5 129 └ *colrpc.Outbox 130 └ *colexec.deselectorOp 131 └ *colexec.countOp 132 └ *colexec.simpleProjectOp 133 └ *colexec.CancelChecker 134 └ *colexec.colBatchScan 135 136 query T 137 EXPLAIN (VEC, VERBOSE) SELECT count(*) FROM kv NATURAL INNER HASH JOIN kv kv2 138 ---- 139 │ 140 ├ Node 1 141 │ └ *colexec.Materializer 142 │ └ *colexec.orderedAggregator 143 │ └ *colexec.oneShotOp 144 │ └ *colexec.distinctChainOps 145 │ └ *colexec.ParallelUnorderedSynchronizer 146 │ ├ *colexec.countOp 147 │ │ └ *colexec.simpleProjectOp 148 │ │ └ *colexec.diskSpillerBase 149 │ │ ├ *colexec.hashJoiner 150 │ │ │ ├ *colexec.ParallelUnorderedSynchronizer 151 │ │ │ │ ├ *colexec.routerOutputOp 152 │ │ │ │ │ └ *colexec.HashRouter 153 │ │ │ │ │ └ *colexec.CancelChecker 154 │ │ │ │ │ └ *colexec.colBatchScan 155 │ │ │ │ ├ *colrpc.Inbox 156 │ │ │ │ ├ *colrpc.Inbox 157 │ │ │ │ ├ *colrpc.Inbox 158 │ │ │ │ └ *colrpc.Inbox 159 │ │ │ └ *colexec.ParallelUnorderedSynchronizer 160 │ │ │ ├ *colexec.routerOutputOp 161 │ │ │ │ └ *colexec.HashRouter 162 │ │ │ │ └ *colexec.CancelChecker 163 │ │ │ │ └ *colexec.colBatchScan 164 │ │ │ ├ *colrpc.Inbox 165 │ │ │ ├ *colrpc.Inbox 166 │ │ │ ├ *colrpc.Inbox 167 │ │ │ └ *colrpc.Inbox 168 │ │ ├ *colexec.ParallelUnorderedSynchronizer 169 │ │ ├ *colexec.ParallelUnorderedSynchronizer 170 │ │ └ *colexec.externalHashJoiner 171 │ │ ├ *colexec.bufferExportingOperator 172 │ │ └ *colexec.bufferExportingOperator 173 │ ├ *colrpc.Inbox 174 │ ├ *colrpc.Inbox 175 │ ├ *colrpc.Inbox 176 │ └ *colrpc.Inbox 177 ├ Node 2 178 │ └ *colrpc.Outbox 179 │ └ *colexec.deselectorOp 180 │ └ *colexec.countOp 181 │ └ *colexec.simpleProjectOp 182 │ └ *colexec.diskSpillerBase 183 │ ├ *colexec.hashJoiner 184 │ │ ├ *colexec.ParallelUnorderedSynchronizer 185 │ │ │ ├ *colrpc.Inbox 186 │ │ │ ├ *colexec.routerOutputOp 187 │ │ │ │ └ *colexec.HashRouter 188 │ │ │ │ └ *colexec.CancelChecker 189 │ │ │ │ └ *colexec.colBatchScan 190 │ │ │ ├ *colrpc.Inbox 191 │ │ │ ├ *colrpc.Inbox 192 │ │ │ └ *colrpc.Inbox 193 │ │ └ *colexec.ParallelUnorderedSynchronizer 194 │ │ ├ *colrpc.Inbox 195 │ │ ├ *colexec.routerOutputOp 196 │ │ │ └ *colexec.HashRouter 197 │ │ │ └ *colexec.CancelChecker 198 │ │ │ └ *colexec.colBatchScan 199 │ │ ├ *colrpc.Inbox 200 │ │ ├ *colrpc.Inbox 201 │ │ └ *colrpc.Inbox 202 │ ├ *colexec.ParallelUnorderedSynchronizer 203 │ ├ *colexec.ParallelUnorderedSynchronizer 204 │ └ *colexec.externalHashJoiner 205 │ ├ *colexec.bufferExportingOperator 206 │ └ *colexec.bufferExportingOperator 207 ├ Node 3 208 │ └ *colrpc.Outbox 209 │ └ *colexec.deselectorOp 210 │ └ *colexec.countOp 211 │ └ *colexec.simpleProjectOp 212 │ └ *colexec.diskSpillerBase 213 │ ├ *colexec.hashJoiner 214 │ │ ├ *colexec.ParallelUnorderedSynchronizer 215 │ │ │ ├ *colrpc.Inbox 216 │ │ │ ├ *colrpc.Inbox 217 │ │ │ ├ *colexec.routerOutputOp 218 │ │ │ │ └ *colexec.HashRouter 219 │ │ │ │ └ *colexec.CancelChecker 220 │ │ │ │ └ *colexec.colBatchScan 221 │ │ │ ├ *colrpc.Inbox 222 │ │ │ └ *colrpc.Inbox 223 │ │ └ *colexec.ParallelUnorderedSynchronizer 224 │ │ ├ *colrpc.Inbox 225 │ │ ├ *colrpc.Inbox 226 │ │ ├ *colexec.routerOutputOp 227 │ │ │ └ *colexec.HashRouter 228 │ │ │ └ *colexec.CancelChecker 229 │ │ │ └ *colexec.colBatchScan 230 │ │ ├ *colrpc.Inbox 231 │ │ └ *colrpc.Inbox 232 │ ├ *colexec.ParallelUnorderedSynchronizer 233 │ ├ *colexec.ParallelUnorderedSynchronizer 234 │ └ *colexec.externalHashJoiner 235 │ ├ *colexec.bufferExportingOperator 236 │ └ *colexec.bufferExportingOperator 237 ├ Node 4 238 │ └ *colrpc.Outbox 239 │ └ *colexec.deselectorOp 240 │ └ *colexec.countOp 241 │ └ *colexec.simpleProjectOp 242 │ └ *colexec.diskSpillerBase 243 │ ├ *colexec.hashJoiner 244 │ │ ├ *colexec.ParallelUnorderedSynchronizer 245 │ │ │ ├ *colrpc.Inbox 246 │ │ │ ├ *colrpc.Inbox 247 │ │ │ ├ *colrpc.Inbox 248 │ │ │ ├ *colexec.routerOutputOp 249 │ │ │ │ └ *colexec.HashRouter 250 │ │ │ │ └ *colexec.CancelChecker 251 │ │ │ │ └ *colexec.colBatchScan 252 │ │ │ └ *colrpc.Inbox 253 │ │ └ *colexec.ParallelUnorderedSynchronizer 254 │ │ ├ *colrpc.Inbox 255 │ │ ├ *colrpc.Inbox 256 │ │ ├ *colrpc.Inbox 257 │ │ ├ *colexec.routerOutputOp 258 │ │ │ └ *colexec.HashRouter 259 │ │ │ └ *colexec.CancelChecker 260 │ │ │ └ *colexec.colBatchScan 261 │ │ └ *colrpc.Inbox 262 │ ├ *colexec.ParallelUnorderedSynchronizer 263 │ ├ *colexec.ParallelUnorderedSynchronizer 264 │ └ *colexec.externalHashJoiner 265 │ ├ *colexec.bufferExportingOperator 266 │ └ *colexec.bufferExportingOperator 267 └ Node 5 268 └ *colrpc.Outbox 269 └ *colexec.deselectorOp 270 └ *colexec.countOp 271 └ *colexec.simpleProjectOp 272 └ *colexec.diskSpillerBase 273 ├ *colexec.hashJoiner 274 │ ├ *colexec.ParallelUnorderedSynchronizer 275 │ │ ├ *colrpc.Inbox 276 │ │ ├ *colrpc.Inbox 277 │ │ ├ *colrpc.Inbox 278 │ │ ├ *colrpc.Inbox 279 │ │ └ *colexec.routerOutputOp 280 │ │ └ *colexec.HashRouter 281 │ │ └ *colexec.CancelChecker 282 │ │ └ *colexec.colBatchScan 283 │ └ *colexec.ParallelUnorderedSynchronizer 284 │ ├ *colrpc.Inbox 285 │ ├ *colrpc.Inbox 286 │ ├ *colrpc.Inbox 287 │ ├ *colrpc.Inbox 288 │ └ *colexec.routerOutputOp 289 │ └ *colexec.HashRouter 290 │ └ *colexec.CancelChecker 291 │ └ *colexec.colBatchScan 292 ├ *colexec.ParallelUnorderedSynchronizer 293 ├ *colexec.ParallelUnorderedSynchronizer 294 └ *colexec.externalHashJoiner 295 ├ *colexec.bufferExportingOperator 296 └ *colexec.bufferExportingOperator 297 298 # Test that SelOnDest flag of coldata.SliceArgs is respected when setting 299 # nulls. 300 statement ok 301 CREATE TABLE t1(a INT PRIMARY KEY, b INT) 302 303 statement ok 304 INSERT INTO t1 VALUES (1, NULL), (2, NULL) 305 306 query I rowsort 307 SELECT CASE WHEN a>1 THEN b*2 ELSE b*10 END FROM t1 308 ---- 309 NULL 310 NULL