github.com/cockroachdb/cockroach@v20.2.0-alpha.1+incompatible/pkg/sql/opt/norm/testdata/rules/project_set (about) 1 exec-ddl 2 CREATE TABLE xy (x INT PRIMARY KEY, y INT, j JSON) 3 ---- 4 5 # -------------------------------------------------- 6 # ConvertZipArraysToValues 7 # -------------------------------------------------- 8 9 # Basic unnest case with single unzip and only constants in array. 10 norm expect=ConvertZipArraysToValues 11 SELECT unnest(ARRAY[1,2,3]) 12 ---- 13 values 14 ├── columns: unnest:1!null 15 ├── cardinality: [3 - 3] 16 ├── (1,) 17 ├── (2,) 18 └── (3,) 19 20 # Case with json_array_elements. 21 norm expect=ConvertZipArraysToValues 22 SELECT json_array_elements('[{"a": "one", "b": "two"}, {"a": "three", "b": "four"}]'::JSON) 23 ---- 24 values 25 ├── columns: json_array_elements:1!null 26 ├── cardinality: [2 - 2] 27 ├── ('{"a": "one", "b": "two"}',) 28 └── ('{"a": "three", "b": "four"}',) 29 30 # Case with jsonb_array_elements. 31 norm expect=ConvertZipArraysToValues 32 SELECT jsonb_array_elements('[{"a": "one", "b": "two"}, {"a": "three", "b": "four"}]'::JSON) 33 ---- 34 values 35 ├── columns: jsonb_array_elements:1!null 36 ├── cardinality: [2 - 2] 37 ├── ('{"a": "one", "b": "two"}',) 38 └── ('{"a": "three", "b": "four"}',) 39 40 # Case with all three matched function types and different array sizes. 41 # Case with json_array_elements. 42 norm expect=ConvertZipArraysToValues 43 SELECT 44 unnest(ARRAY[1,2,3]), 45 json_array_elements('[{"a": "one", "b": "two"}, {"a": "three", "b": "four"}]'::JSON), 46 jsonb_array_elements('[{"x": "one", "y": "two"}]'::JSON) 47 ---- 48 values 49 ├── columns: unnest:1!null json_array_elements:2 jsonb_array_elements:3 50 ├── cardinality: [3 - 3] 51 ├── (1, '{"a": "one", "b": "two"}', '{"x": "one", "y": "two"}') 52 ├── (2, '{"a": "three", "b": "four"}', NULL) 53 └── (3, NULL, NULL) 54 55 # Case with subquery in ProjectSet input. 56 norm expect=ConvertZipArraysToValues 57 SELECT unnest(ARRAY[1,2,3]) FROM unnest(ARRAY[4,5,6]) 58 ---- 59 inner-join (cross) 60 ├── columns: unnest:2!null 61 ├── cardinality: [9 - 9] 62 ├── values 63 │ ├── cardinality: [3 - 3] 64 │ ├── () 65 │ ├── () 66 │ └── () 67 ├── values 68 │ ├── columns: unnest:2!null 69 │ ├── cardinality: [3 - 3] 70 │ ├── (1,) 71 │ ├── (2,) 72 │ └── (3,) 73 └── filters (true) 74 75 # Case with correlated array. 76 norm expect=ConvertZipArraysToValues 77 SELECT unnest(ARRAY[x,y]) FROM xy 78 ---- 79 project 80 ├── columns: unnest:4 81 └── inner-join-apply 82 ├── columns: x:1!null y:2 unnest:4 83 ├── fd: (1)-->(2) 84 ├── scan xy 85 │ ├── columns: x:1!null y:2 86 │ ├── key: (1) 87 │ └── fd: (1)-->(2) 88 ├── values 89 │ ├── columns: unnest:4 90 │ ├── outer: (1,2) 91 │ ├── cardinality: [2 - 2] 92 │ ├── (x:1,) 93 │ └── (y:2,) 94 └── filters (true) 95 96 # Case with correlated array in a correlated subquery. 97 norm expect=ConvertZipArraysToValues 98 SELECT * FROM xy 99 WHERE EXISTS 100 (SELECT t 101 FROM unnest(ARRAY[NULL,2,NULL,4,5,x]) 102 AS f(t) 103 WHERE t=y 104 ) 105 ---- 106 semi-join-apply 107 ├── columns: x:1!null y:2!null j:3 108 ├── key: (1) 109 ├── fd: (1)-->(2,3) 110 ├── scan xy 111 │ ├── columns: x:1!null y:2 j:3 112 │ ├── key: (1) 113 │ └── fd: (1)-->(2,3) 114 ├── values 115 │ ├── columns: unnest:4 116 │ ├── outer: (1) 117 │ ├── cardinality: [6 - 6] 118 │ ├── (NULL,) 119 │ ├── (2,) 120 │ ├── (NULL,) 121 │ ├── (4,) 122 │ ├── (5,) 123 │ └── (x:1,) 124 └── filters 125 └── unnest:4 = y:2 [outer=(2,4), constraints=(/2: (/NULL - ]; /4: (/NULL - ]), fd=(2)==(4), (4)==(2)] 126 127 # Case with multiple arrays of different types and different sizes, including an 128 # empty array. 129 norm expect=ConvertZipArraysToValues 130 SELECT 131 unnest(ARRAY['one','two','three']), 132 unnest(ARRAY[1,2,5,6,NULL,8]), 133 unnest(ARRAY[]::BOOL[]), 134 json_array_elements('[{"a": "one", "b": "two"}, {"a": "three", "b": "four"}]'::JSON), 135 jsonb_array_elements('[{"x": "one", "y": "two"}]'::JSON), 136 jsonb_array_elements('[]'::JSON) 137 ---- 138 values 139 ├── columns: unnest:1 unnest:2 unnest:3 json_array_elements:4 jsonb_array_elements:5 jsonb_array_elements:6 140 ├── cardinality: [6 - 6] 141 ├── ('one', 1, NULL, '{"a": "one", "b": "two"}', '{"x": "one", "y": "two"}', NULL) 142 ├── ('two', 2, NULL, '{"a": "three", "b": "four"}', NULL, NULL) 143 ├── ('three', 5, NULL, NULL, NULL, NULL) 144 ├── (NULL, 6, NULL, NULL, NULL, NULL) 145 ├── (NULL, NULL, NULL, NULL, NULL, NULL) 146 └── (NULL, 8, NULL, NULL, NULL, NULL) 147 148 # unnest case with multiple empty arrays. 149 norm expect=ConvertZipArraysToValues 150 SELECT unnest(ARRAY[]::STRING[]), unnest(ARRAY[]::REAL[]), unnest(ARRAY[]::INT[]) 151 ---- 152 values 153 ├── columns: unnest:1!null unnest:2!null unnest:3!null 154 ├── cardinality: [0 - 0] 155 ├── key: () 156 └── fd: ()-->(1-3) 157 158 # json_array_elements case with empty array. 159 norm expect=ConvertZipArraysToValues 160 SELECT json_array_elements('[]') 161 ---- 162 values 163 ├── columns: json_array_elements:1!null 164 ├── cardinality: [0 - 0] 165 ├── key: () 166 └── fd: ()-->(1) 167 168 # unnest case with array of arrays. 169 norm expect=ConvertZipArraysToValues 170 SELECT unnest(ARRAY[[1,2,3],[4,5]]) 171 ---- 172 values 173 ├── columns: unnest:1!null 174 ├── cardinality: [2 - 2] 175 ├── (ARRAY[1,2,3],) 176 └── (ARRAY[4,5],) 177 178 # json_array_elements case with array of arrays. 179 norm expect=ConvertZipArraysToValues 180 SELECT json_array_elements('[[{"a": "x"}],[{"a": "y"}]]') 181 ---- 182 values 183 ├── columns: json_array_elements:1!null 184 ├── cardinality: [2 - 2] 185 ├── ('[{"a": "x"}]',) 186 └── ('[{"a": "y"}]',) 187 188 # Case with multiple correlated arrays. 189 norm expect=ConvertZipArraysToValues 190 SELECT unnest(ARRAY[x,y]), unnest(ARRAY[1,x*100]) FROM xy 191 ---- 192 project 193 ├── columns: unnest:4 unnest:5 194 └── inner-join-apply 195 ├── columns: x:1!null y:2 unnest:4 unnest:5 196 ├── fd: (1)-->(2) 197 ├── scan xy 198 │ ├── columns: x:1!null y:2 199 │ ├── key: (1) 200 │ └── fd: (1)-->(2) 201 ├── values 202 │ ├── columns: unnest:4 unnest:5 203 │ ├── outer: (1,2) 204 │ ├── cardinality: [2 - 2] 205 │ ├── (x:1, 1) 206 │ └── (y:2, x:1 * 100) 207 └── filters (true) 208 209 # No-op case - ConvertZipArraysToValues fires the first time but not the 210 # second because the outer zip is over a variable of an array instead of the 211 # array itself. 212 norm expect=ConvertZipArraysToValues 213 SELECT unnest(x) FROM unnest(ARRAY[[1,2,3],[4,5],[6]]) AS x 214 ---- 215 project 216 ├── columns: unnest:2 217 ├── immutable, side-effects 218 └── project-set 219 ├── columns: unnest:1!null unnest:2 220 ├── immutable, side-effects 221 ├── values 222 │ ├── columns: unnest:1!null 223 │ ├── cardinality: [3 - 3] 224 │ ├── (ARRAY[1,2,3],) 225 │ ├── (ARRAY[4,5],) 226 │ └── (ARRAY[6],) 227 └── zip 228 └── unnest(unnest:1) [outer=(1), immutable, side-effects] 229 230 # No-op case - an unnest with multiple inputs is not matched. 231 norm expect-not=ConvertZipArraysToValues 232 SELECT unnest(ARRAY[1,2,3], ARRAY[4,5,6]) 233 ---- 234 project 235 ├── columns: unnest:3 236 ├── immutable, side-effects 237 ├── project-set 238 │ ├── columns: unnest:1 unnest:2 239 │ ├── immutable, side-effects 240 │ ├── values 241 │ │ ├── cardinality: [1 - 1] 242 │ │ ├── key: () 243 │ │ └── () 244 │ └── zip 245 │ └── unnest(ARRAY[1,2,3], ARRAY[4,5,6]) [immutable, side-effects] 246 └── projections 247 └── ((unnest:1, unnest:2) AS unnest, unnest) [as=unnest:3, outer=(1,2)] 248 249 # No-op case because one of the ZipItems is not valid. 250 norm expect-not=ConvertZipArraysToValues 251 SELECT unnest(ARRAY[1,2,3]), unnest(ARRAY[1,2,3], ARRAY[4,5,6]) 252 ---- 253 project 254 ├── columns: unnest:1 unnest:4 255 ├── immutable, side-effects 256 ├── project-set 257 │ ├── columns: unnest:1 unnest:2 unnest:3 258 │ ├── immutable, side-effects 259 │ ├── values 260 │ │ ├── cardinality: [1 - 1] 261 │ │ ├── key: () 262 │ │ └── () 263 │ └── zip 264 │ ├── unnest(ARRAY[1,2,3]) [immutable, side-effects] 265 │ └── unnest(ARRAY[1,2,3], ARRAY[4,5,6]) [immutable, side-effects] 266 └── projections 267 └── ((unnest:2, unnest:3) AS unnest, unnest) [as=unnest:4, outer=(2,3)] 268 269 # No-op case because array_agg can only be determined at runtime. 270 norm expect-not=ConvertZipArraysToValues 271 SELECT unnest((SELECT array_agg(y) FROM xy)) 272 ---- 273 project-set 274 ├── columns: unnest:5 275 ├── immutable, side-effects 276 ├── values 277 │ ├── cardinality: [1 - 1] 278 │ ├── key: () 279 │ └── () 280 └── zip 281 └── function: unnest [immutable, side-effects, subquery] 282 └── subquery 283 └── scalar-group-by 284 ├── columns: array_agg:4 285 ├── cardinality: [1 - 1] 286 ├── key: () 287 ├── fd: ()-->(4) 288 ├── scan xy 289 │ └── columns: y:2 290 └── aggregations 291 └── array-agg [as=array_agg:4, outer=(2)] 292 └── y:2 293 294 # No-op case because a JSON column can only be determined at run-time. 295 norm expect-not=ConvertZipArraysToValues 296 SELECT json_array_elements(j) FROM xy 297 ---- 298 project 299 ├── columns: json_array_elements:4 300 ├── immutable, side-effects 301 └── project-set 302 ├── columns: j:3 json_array_elements:4 303 ├── immutable, side-effects 304 ├── scan xy 305 │ └── columns: j:3 306 └── zip 307 └── json_array_elements(j:3) [outer=(3), immutable, side-effects]