github.com/cockroachdb/cockroach@v20.2.0-alpha.1+incompatible/pkg/sql/opt/optbuilder/testdata/lateral (about) 1 exec-ddl 2 CREATE TABLE x (a INT PRIMARY KEY) 3 ---- 4 5 exec-ddl 6 CREATE TABLE y (b INT PRIMARY KEY) 7 ---- 8 9 exec-ddl 10 CREATE TABLE z (c INT PRIMARY KEY) 11 ---- 12 13 build 14 SELECT * FROM x, y, z 15 ---- 16 inner-join (cross) 17 ├── columns: a:1!null b:2!null c:3!null 18 ├── scan x 19 │ └── columns: a:1!null 20 ├── inner-join (cross) 21 │ ├── columns: b:2!null c:3!null 22 │ ├── scan y 23 │ │ └── columns: b:2!null 24 │ ├── scan z 25 │ │ └── columns: c:3!null 26 │ └── filters (true) 27 └── filters (true) 28 29 build 30 SELECT * FROM x, LATERAL (SELECT * FROM y WHERE b = a) 31 ---- 32 inner-join-apply 33 ├── columns: a:1!null b:2!null 34 ├── scan x 35 │ └── columns: a:1!null 36 ├── select 37 │ ├── columns: b:2!null 38 │ ├── scan y 39 │ │ └── columns: b:2!null 40 │ └── filters 41 │ └── b:2 = a:1 42 └── filters (true) 43 44 build 45 SELECT * FROM x, (SELECT * FROM y WHERE b = a) 46 ---- 47 error (42703): column "a" does not exist 48 49 # Ensure that the presence of LATERAL properly affects name resolution. 50 51 build 52 SELECT 53 (SELECT b FROM (SELECT c AS a FROM z), LATERAL (SELECT * FROM y WHERE b = a)) 54 FROM x 55 ---- 56 project 57 ├── columns: b:4 58 ├── scan x 59 │ └── columns: a:1!null 60 └── projections 61 └── subquery [as=b:4] 62 └── max1-row 63 ├── columns: y.b:3!null 64 └── project 65 ├── columns: y.b:3!null 66 └── inner-join-apply 67 ├── columns: c:2!null y.b:3!null 68 ├── scan z 69 │ └── columns: c:2!null 70 ├── select 71 │ ├── columns: y.b:3!null 72 │ ├── scan y 73 │ │ └── columns: y.b:3!null 74 │ └── filters 75 │ └── y.b:3 = c:2 76 └── filters (true) 77 78 build 79 SELECT 80 (SELECT b FROM (SELECT c AS a FROM z), (SELECT * FROM y WHERE b = a)) 81 FROM x 82 ---- 83 project 84 ├── columns: b:4 85 ├── scan x 86 │ └── columns: a:1!null 87 └── projections 88 └── subquery [as=b:4] 89 └── max1-row 90 ├── columns: y.b:3!null 91 └── project 92 ├── columns: y.b:3!null 93 └── inner-join (cross) 94 ├── columns: c:2!null y.b:3!null 95 ├── scan z 96 │ └── columns: c:2!null 97 ├── select 98 │ ├── columns: y.b:3!null 99 │ ├── scan y 100 │ │ └── columns: y.b:3!null 101 │ └── filters 102 │ └── y.b:3 = a:1 103 └── filters (true) 104 105 build 106 SELECT * FROM x AS o WHERE EXISTS(SELECT * FROM x, LATERAL (SELECT * FROM y WHERE b = x.a AND o.a = x.a)) 107 ---- 108 select 109 ├── columns: a:1!null 110 ├── scan o 111 │ └── columns: o.a:1!null 112 └── filters 113 └── exists 114 └── inner-join-apply 115 ├── columns: x.a:2!null b:3!null 116 ├── scan x 117 │ └── columns: x.a:2!null 118 ├── select 119 │ ├── columns: b:3!null 120 │ ├── scan y 121 │ │ └── columns: b:3!null 122 │ └── filters 123 │ └── (b:3 = x.a:2) AND (o.a:1 = x.a:2) 124 └── filters (true) 125 126 build 127 SELECT * FROM x, LATERAL (SELECT * FROM y WHERE b = a), z 128 ---- 129 inner-join-apply 130 ├── columns: a:1!null b:2!null c:3!null 131 ├── inner-join-apply 132 │ ├── columns: a:1!null b:2!null 133 │ ├── scan x 134 │ │ └── columns: a:1!null 135 │ ├── select 136 │ │ ├── columns: b:2!null 137 │ │ ├── scan y 138 │ │ │ └── columns: b:2!null 139 │ │ └── filters 140 │ │ └── b:2 = a:1 141 │ └── filters (true) 142 ├── scan z 143 │ └── columns: c:3!null 144 └── filters (true) 145 146 build 147 SELECT * FROM x, LATERAL (SELECT * FROM y WHERE b = a), x 148 ---- 149 error (42712): source name "x" specified more than once (missing AS clause) 150 151 exec-ddl 152 CREATE TABLE j ( 153 id INT PRIMARY KEY, 154 j JSONB 155 ) 156 ---- 157 158 build 159 SELECT * FROM j, jsonb_array_elements(j.j->'foo') 160 ---- 161 inner-join-apply 162 ├── columns: id:1!null j:2 value:3 163 ├── scan j 164 │ └── columns: id:1!null j:2 165 ├── project-set 166 │ ├── columns: value:3 167 │ ├── values 168 │ │ └── () 169 │ └── zip 170 │ └── jsonb_array_elements(j:2->'foo') 171 └── filters (true) 172 173 # Explicit LATERAL makes no difference for SRFs. 174 175 build 176 SELECT * FROM j, LATERAL jsonb_array_elements(j.j->'foo') 177 ---- 178 inner-join-apply 179 ├── columns: id:1!null j:2 value:3 180 ├── scan j 181 │ └── columns: id:1!null j:2 182 ├── project-set 183 │ ├── columns: value:3 184 │ ├── values 185 │ │ └── () 186 │ └── zip 187 │ └── jsonb_array_elements(j:2->'foo') 188 └── filters (true) 189 190 # Referencing a lateral SRF from a lateral SRF. 191 192 build 193 SELECT * FROM j, jsonb_array_elements(j.j->'foo') AS e, jsonb_array_elements(e.e->'bar') 194 ---- 195 inner-join-apply 196 ├── columns: id:1!null j:2 e:3 value:4 197 ├── inner-join-apply 198 │ ├── columns: id:1!null j:2 value:3 199 │ ├── scan j 200 │ │ └── columns: id:1!null j:2 201 │ ├── project-set 202 │ │ ├── columns: value:3 203 │ │ ├── values 204 │ │ │ └── () 205 │ │ └── zip 206 │ │ └── jsonb_array_elements(j:2->'foo') 207 │ └── filters (true) 208 ├── project-set 209 │ ├── columns: value:4 210 │ ├── values 211 │ │ └── () 212 │ └── zip 213 │ └── jsonb_array_elements(value:3->'bar') 214 └── filters (true) 215 216 build 217 SELECT 218 * 219 FROM 220 j, 221 jsonb_array_elements( 222 ( 223 SELECT 224 j2.j->'members' AS members 225 FROM 226 j AS j2 227 WHERE 228 j2.j->>'name' = j.j->>'name' 229 ) 230 ) 231 ---- 232 inner-join-apply 233 ├── columns: id:1!null j:2 value:6 234 ├── scan j 235 │ └── columns: j.id:1!null j.j:2 236 ├── project-set 237 │ ├── columns: value:6 238 │ ├── values 239 │ │ └── () 240 │ └── zip 241 │ └── function: jsonb_array_elements 242 │ └── subquery 243 │ └── max1-row 244 │ ├── columns: members:5 245 │ └── project 246 │ ├── columns: members:5 247 │ ├── select 248 │ │ ├── columns: j2.id:3!null j2.j:4 249 │ │ ├── scan j2 250 │ │ │ └── columns: j2.id:3!null j2.j:4 251 │ │ └── filters 252 │ │ └── (j2.j:4->>'name') = (j.j:2->>'name') 253 │ └── projections 254 │ └── j2.j:4->'members' [as=members:5] 255 └── filters (true) 256 257 build 258 SELECT * FROM x JOIN LATERAL (SELECT * FROM y WHERE b = x.a) ON true 259 ---- 260 inner-join-apply 261 ├── columns: a:1!null b:2!null 262 ├── scan x 263 │ └── columns: a:1!null 264 ├── select 265 │ ├── columns: b:2!null 266 │ ├── scan y 267 │ │ └── columns: b:2!null 268 │ └── filters 269 │ └── b:2 = a:1 270 └── filters 271 └── true 272 273 build 274 SELECT * FROM x LEFT JOIN LATERAL (SELECT * FROM y WHERE b = x.a) ON true 275 ---- 276 left-join-apply 277 ├── columns: a:1!null b:2 278 ├── scan x 279 │ └── columns: a:1!null 280 ├── select 281 │ ├── columns: b:2!null 282 │ ├── scan y 283 │ │ └── columns: b:2!null 284 │ └── filters 285 │ └── b:2 = a:1 286 └── filters 287 └── true 288 289 build 290 SELECT * FROM x RIGHT JOIN LATERAL (SELECT * FROM y WHERE b = x.a) ON true 291 ---- 292 error (42601): The combining JOIN type must be INNER or LEFT for a LATERAL reference 293 294 build 295 SELECT * FROM x FULL OUTER JOIN LATERAL (SELECT * FROM y WHERE b = x.a) ON true 296 ---- 297 error (42601): The combining JOIN type must be INNER or LEFT for a LATERAL reference