github.com/cockroachdb/cockroach@v20.2.0-alpha.1+incompatible/pkg/sql/opt/exec/execbuilder/testdata/subquery (about) 1 # LogicTest: local 2 3 # ------------------------------------------------------------------------------ 4 # Uncorrelated subqueries. 5 # ------------------------------------------------------------------------------ 6 statement ok 7 CREATE TABLE abc (a INT PRIMARY KEY, b INT, c INT) 8 9 query TTT 10 EXPLAIN ALTER TABLE abc SPLIT AT VALUES ((SELECT 42)) 11 ---- 12 · distributed false 13 · vectorized false 14 root · · 15 ├── split · · 16 │ └── values · · 17 │ size 1 column, 1 row 18 └── subquery · · 19 │ id @S1 20 │ original sql (SELECT 42) 21 │ exec mode one row 22 └── values · · 23 · size 1 column, 1 row 24 25 statement ok 26 ALTER TABLE abc SPLIT AT VALUES ((SELECT 1)) 27 28 query TTT 29 EXPLAIN SELECT EXISTS (SELECT a FROM abc) 30 ---- 31 · distributed false 32 · vectorized false 33 root · · 34 ├── values · · 35 │ size 1 column, 1 row 36 └── subquery · · 37 │ id @S1 38 │ original sql EXISTS (SELECT a FROM abc) 39 │ exec mode exists 40 └── scan · · 41 · table abc@primary 42 · spans LIMITED SCAN 43 · limit 1 44 45 query TTTTT 46 EXPLAIN (VERBOSE) SELECT * FROM abc WHERE a = (SELECT max(a) FROM abc WHERE EXISTS(SELECT * FROM abc WHERE c=a+3)) 47 ---- 48 · distributed false · · 49 · vectorized true · · 50 root · · (a, b, c) · 51 ├── scan · · (a, b, c) · 52 │ table abc@primary · · 53 │ spans FULL SCAN · · 54 │ filter a = @S2 · · 55 ├── subquery · · · · 56 │ │ id @S1 · · 57 │ │ original sql EXISTS (SELECT * FROM abc WHERE c = (a + 3)) · · 58 │ │ exec mode exists · · 59 │ └── limit · · (a, b, c) · 60 │ │ count 1 · · 61 │ └── scan · · (a, b, c) · 62 │ table abc@primary · · 63 │ spans FULL SCAN · · 64 │ filter c = (a + 3) · · 65 └── subquery · · · · 66 │ id @S2 · · 67 │ original sql (SELECT max(a) FROM abc WHERE EXISTS (SELECT * FROM abc WHERE c = (a + 3))) · · 68 │ exec mode one row · · 69 └── group · · (any_not_null) · 70 │ aggregate 0 any_not_null(a) · · 71 │ scalar · · · 72 └── limit · · (a) -a 73 │ count 1 · · 74 └── revscan · · (a) -a 75 · table abc@primary · · 76 · spans FULL SCAN · · 77 · filter @S1 · · 78 79 # IN expression transformed into semi-join. 80 query TTTTT 81 EXPLAIN (VERBOSE) SELECT a FROM abc WHERE a IN (SELECT a FROM abc) 82 ---- 83 · distributed false · · 84 · vectorized true · · 85 merge-join · · (a) · 86 │ type semi · · 87 │ equality (a) = (a) · · 88 │ left cols are key · · · 89 │ right cols are key · · · 90 │ mergeJoinOrder +"(a=a)" · · 91 ├── scan · · (a) +a 92 │ table abc@primary · · 93 │ spans FULL SCAN · · 94 └── scan · · (a) +a 95 · table abc@primary · · 96 · spans FULL SCAN · · 97 98 query TTT 99 EXPLAIN SELECT * FROM (SELECT * FROM (VALUES (1, 8, 8), (3, 1, 1), (2, 4, 4)) AS moo (moo1, moo2, moo3) ORDER BY moo2) as foo (foo1) ORDER BY foo1 100 ---- 101 · distributed false 102 · vectorized false 103 sort · · 104 │ order +foo1 105 └── values · · 106 · size 3 columns, 3 rows 107 108 # the subquery's plan must be visible in EXPLAIN 109 query TTT 110 EXPLAIN VALUES (1), ((SELECT 2)) 111 ---- 112 · distributed false 113 · vectorized false 114 root · · 115 ├── values · · 116 │ size 1 column, 2 rows 117 └── subquery · · 118 │ id @S1 119 │ original sql (SELECT 2) 120 │ exec mode one row 121 └── values · · 122 · size 1 column, 1 row 123 124 # This test checks that the double sub-query plan expansion caused by a 125 # sub-expression being shared by two or more plan nodes does not 126 # error out. 127 statement ok 128 CREATE TABLE tab4(col0 INTEGER, col1 FLOAT, col3 INTEGER, col4 FLOAT) 129 130 statement ok 131 CREATE INDEX idx_tab4_0 ON tab4 (col4,col0) 132 133 query TTTTT 134 EXPLAIN (VERBOSE) 135 SELECT col0 136 FROM tab4 137 WHERE 138 (col0 <= 0 AND col4 <= 5.38) 139 OR (col4 IN (SELECT col1 FROM tab4 WHERE col1 > 8.27)) 140 AND (col3 <= 5 AND (col3 BETWEEN 7 AND 9)) 141 ---- 142 · distributed false · · 143 · vectorized true · · 144 render · · (col0) · 145 │ render 0 col0 · · 146 └── index-join · · (col0, col3, col4, rowid[hidden]) · 147 │ table tab4@primary · · 148 │ key columns rowid · · 149 └── scan · · (col0, col4, rowid[hidden]) · 150 · table tab4@idx_tab4_0 · · 151 · spans /!NULL-/5.38/1 · · 152 · filter col0 <= 0 · · 153 154 # ------------------------------------------------------------------------------ 155 # Correlated subqueries. 156 # ------------------------------------------------------------------------------ 157 statement ok 158 CREATE TABLE a (x INT PRIMARY KEY, y INT); 159 CREATE TABLE b (x INT PRIMARY KEY, z INT); 160 161 query TTTTT 162 EXPLAIN (VERBOSE) SELECT * FROM a WHERE EXISTS(SELECT * FROM b WHERE a.x=b.x) 163 ---- 164 · distributed false · · 165 · vectorized true · · 166 merge-join · · (x, y) · 167 │ type semi · · 168 │ equality (x) = (x) · · 169 │ left cols are key · · · 170 │ right cols are key · · · 171 │ mergeJoinOrder +"(x=x)" · · 172 ├── scan · · (x, y) +x 173 │ table a@primary · · 174 │ spans FULL SCAN · · 175 └── scan · · (x) +x 176 · table b@primary · · 177 · spans FULL SCAN · · 178 179 query TTTTT 180 EXPLAIN (VERBOSE) SELECT * FROM a WHERE EXISTS(SELECT * FROM b WHERE b.x-1 = a.x) 181 ---- 182 · distributed false · · 183 · vectorized true · · 184 hash-join · · (x, y) · 185 │ type semi · · 186 │ equality (x) = (column5) · · 187 │ left cols are key · · · 188 ├── scan · · (x, y) · 189 │ table a@primary · · 190 │ spans FULL SCAN · · 191 └── render · · (column5) · 192 │ render 0 x - 1 · · 193 └── scan · · (x) · 194 · table b@primary · · 195 · spans FULL SCAN · · 196 197 query TTTTT 198 EXPLAIN (VERBOSE) SELECT * FROM a WHERE NOT EXISTS(SELECT * FROM b WHERE b.x = a.x) 199 ---- 200 · distributed false · · 201 · vectorized true · · 202 merge-join · · (x, y) · 203 │ type anti · · 204 │ equality (x) = (x) · · 205 │ left cols are key · · · 206 │ right cols are key · · · 207 │ mergeJoinOrder +"(x=x)" · · 208 ├── scan · · (x, y) +x 209 │ table a@primary · · 210 │ spans FULL SCAN · · 211 └── scan · · (x) +x 212 · table b@primary · · 213 · spans FULL SCAN · · 214 215 query TTTTT 216 EXPLAIN (VERBOSE) SELECT * FROM b WHERE NOT EXISTS(SELECT * FROM a WHERE x-1 = b.x) 217 ---- 218 · distributed false · · 219 · vectorized true · · 220 hash-join · · (x, z) · 221 │ type anti · · 222 │ equality (x) = (column5) · · 223 │ left cols are key · · · 224 ├── scan · · (x, z) · 225 │ table b@primary · · 226 │ spans FULL SCAN · · 227 └── render · · (column5) · 228 │ render 0 x - 1 · · 229 └── scan · · (x) · 230 · table a@primary · · 231 · spans FULL SCAN · · 232 233 query TTTTT 234 EXPLAIN (VERBOSE) SELECT ARRAY(SELECT x FROM b) 235 ---- 236 · distributed false · · 237 · vectorized false · · 238 root · · ("array") · 239 ├── values · · ("array") · 240 │ size 1 column, 1 row · · 241 │ row 0, expr 0 ARRAY @S1 · · 242 └── subquery · · · · 243 │ id @S1 · · 244 │ original sql (SELECT x FROM b) · · 245 │ exec mode all rows · · 246 └── scan · · (x) · 247 · table b@primary · · 248 · spans FULL SCAN · · 249 250 # Case where the plan has an apply join. 251 query TTTTT 252 EXPLAIN(verbose) SELECT * FROM abc WHERE EXISTS(SELECT * FROM (VALUES (a), (b)) WHERE column1=a) 253 ---- 254 · distributed false · · 255 · vectorized false · · 256 apply-join · · (a, b, c) · 257 │ type semi · · 258 │ pred column1 = a · · 259 └── scan · · (a, b, c) · 260 · table abc@primary · · 261 · spans FULL SCAN · · 262 263 # Case where the EXISTS subquery still has outer columns in the subquery 264 # (regression test for #28816). 265 query error could not decorrelate subquery 266 SELECT 267 subq_0.c1 AS c1 268 FROM 269 (SELECT ref_0.attrs AS c1 FROM crdb_internal.kv_store_status AS ref_0) AS subq_0 270 WHERE 271 5 >= CASE WHEN subq_0.c1 IS NOT NULL 272 THEN 5 273 ELSE pg_catalog.extract( 274 CAST( 275 CASE WHEN ( 276 EXISTS( 277 SELECT ref_1.raw_config_yaml AS c0 278 FROM crdb_internal.zones AS ref_1 279 WHERE subq_0.c1 IS NOT NULL 280 ) 281 ) 282 THEN pg_catalog.version() 283 ELSE pg_catalog.version() 284 END 285 AS STRING 286 ), 287 CAST(pg_catalog.current_date() AS DATE) 288 ) 289 END 290 291 # Case where the ANY subquery still has outer columns. 292 query error could not decorrelate subquery 293 SELECT 294 subq_0.c1 AS c1 295 FROM 296 (SELECT ref_0.attrs AS c1 FROM crdb_internal.kv_store_status AS ref_0) AS subq_0 297 WHERE 298 5 >= CASE WHEN subq_0.c1 IS NOT NULL 299 THEN 5 300 ELSE pg_catalog.extract( 301 CAST( 302 CASE 303 WHEN ( 304 '12'::STRING 305 = ANY ( 306 SELECT ref_1.raw_config_yaml AS c0 307 FROM crdb_internal.zones AS ref_1 308 WHERE subq_0.c1 IS NOT NULL 309 ) 310 ) 311 THEN pg_catalog.version() 312 ELSE pg_catalog.version() 313 END 314 AS STRING 315 ), 316 CAST(pg_catalog.current_date() AS DATE) 317 ) 318 END