github.com/cockroachdb/cockroach@v20.2.0-alpha.1+incompatible/pkg/sql/opt/memo/testdata/logprops/groupby (about) 1 exec-ddl 2 CREATE TABLE xyzs (x INT PRIMARY KEY, y INT, z FLOAT NOT NULL, s STRING, UNIQUE (s DESC, z)) 3 ---- 4 5 exec-ddl 6 CREATE TABLE kuv (k INT PRIMARY KEY, u FLOAT, v STRING) 7 ---- 8 9 # Group-by with interesting aggregate expressions. 10 build 11 SELECT y, sum(z), x, FALSE, avg(z) FILTER (WHERE z>0), string_agg(DISTINCT s, ',') 12 FROM xyzs 13 WHERE s IS NOT NULL 14 GROUP BY x, y 15 ---- 16 project 17 ├── columns: y:2(int) sum:5(float!null) x:1(int!null) bool:10(bool!null) avg:7(float) string_agg:9(string!null) 18 ├── key: (1) 19 ├── fd: ()-->(10), (1)-->(2,5,7,9) 20 ├── prune: (1,2,5,7,9,10) 21 ├── interesting orderings: (+1) 22 ├── group-by 23 │ ├── columns: x:1(int!null) y:2(int) sum:5(float!null) avg:7(float) string_agg:9(string!null) 24 │ ├── grouping columns: x:1(int!null) y:2(int) 25 │ ├── key: (1) 26 │ ├── fd: (1)-->(2,5,7,9) 27 │ ├── prune: (5,7,9) 28 │ ├── interesting orderings: (+1) 29 │ ├── project 30 │ │ ├── columns: column6:6(bool!null) column8:8(string!null) x:1(int!null) y:2(int) z:3(float!null) s:4(string!null) 31 │ │ ├── key: (1) 32 │ │ ├── fd: ()-->(8), (1)-->(2-4,6), (3,4)-->(1,2) 33 │ │ ├── prune: (1-4,6,8) 34 │ │ ├── interesting orderings: (+1) (-4,+3,+1) 35 │ │ ├── select 36 │ │ │ ├── columns: x:1(int!null) y:2(int) z:3(float!null) s:4(string!null) 37 │ │ │ ├── key: (1) 38 │ │ │ ├── fd: (1)-->(2-4), (3,4)-->(1,2) 39 │ │ │ ├── prune: (1-3) 40 │ │ │ ├── interesting orderings: (+1) (-4,+3,+1) 41 │ │ │ ├── scan xyzs 42 │ │ │ │ ├── columns: x:1(int!null) y:2(int) z:3(float!null) s:4(string) 43 │ │ │ │ ├── key: (1) 44 │ │ │ │ ├── fd: (1)-->(2-4), (3,4)~~>(1,2) 45 │ │ │ │ ├── prune: (1-4) 46 │ │ │ │ └── interesting orderings: (+1) (-4,+3,+1) 47 │ │ │ └── filters 48 │ │ │ └── is-not [type=bool, outer=(4), constraints=(/4: (/NULL - ]; tight)] 49 │ │ │ ├── variable: s:4 [type=string] 50 │ │ │ └── null [type=unknown] 51 │ │ └── projections 52 │ │ ├── gt [as=column6:6, type=bool, outer=(3)] 53 │ │ │ ├── variable: z:3 [type=float] 54 │ │ │ └── const: 0.0 [type=float] 55 │ │ └── const: ',' [as=column8:8, type=string] 56 │ └── aggregations 57 │ ├── sum [as=sum:5, type=float, outer=(3)] 58 │ │ └── variable: z:3 [type=float] 59 │ ├── agg-filter [as=avg:7, type=float, outer=(3,6)] 60 │ │ ├── avg [type=float] 61 │ │ │ └── variable: z:3 [type=float] 62 │ │ └── variable: column6:6 [type=bool] 63 │ └── agg-distinct [as=string_agg:9, type=string, outer=(4,8)] 64 │ └── string-agg [type=string] 65 │ ├── variable: s:4 [type=string] 66 │ └── variable: column8:8 [type=string] 67 └── projections 68 └── false [as=bool:10, type=bool] 69 70 # Scalar groupby. 71 build 72 SELECT sum(x), max(y), count(x) FROM xyzs 73 ---- 74 scalar-group-by 75 ├── columns: sum:5(decimal) max:6(int) count:7(int!null) 76 ├── cardinality: [1 - 1] 77 ├── key: () 78 ├── fd: ()-->(5-7) 79 ├── prune: (5-7) 80 ├── project 81 │ ├── columns: x:1(int!null) y:2(int) 82 │ ├── key: (1) 83 │ ├── fd: (1)-->(2) 84 │ ├── prune: (1,2) 85 │ ├── interesting orderings: (+1) 86 │ └── scan xyzs 87 │ ├── columns: x:1(int!null) y:2(int) z:3(float!null) s:4(string) 88 │ ├── key: (1) 89 │ ├── fd: (1)-->(2-4), (3,4)~~>(1,2) 90 │ ├── prune: (1-4) 91 │ └── interesting orderings: (+1) (-4,+3,+1) 92 └── aggregations 93 ├── sum [as=sum:5, type=decimal, outer=(1)] 94 │ └── variable: x:1 [type=int] 95 ├── max [as=max:6, type=int, outer=(2)] 96 │ └── variable: y:2 [type=int] 97 └── count [as=count:7, type=int, outer=(1)] 98 └── variable: x:1 [type=int] 99 100 # Group by unique index columns. 101 build 102 SELECT s FROM xyzs GROUP BY z, s 103 ---- 104 project 105 ├── columns: s:4(string) 106 ├── prune: (4) 107 ├── interesting orderings: (-4) 108 └── group-by 109 ├── columns: z:3(float!null) s:4(string) 110 ├── grouping columns: z:3(float!null) s:4(string) 111 ├── key: (3,4) 112 ├── interesting orderings: (-4,+3) 113 └── project 114 ├── columns: z:3(float!null) s:4(string) 115 ├── lax-key: (3,4) 116 ├── prune: (3,4) 117 ├── interesting orderings: (-4,+3) 118 └── scan xyzs 119 ├── columns: x:1(int!null) y:2(int) z:3(float!null) s:4(string) 120 ├── key: (1) 121 ├── fd: (1)-->(2-4), (3,4)~~>(1,2) 122 ├── prune: (1-4) 123 └── interesting orderings: (+1) (-4,+3,+1) 124 125 # Group by columns that otherwise wouldn't be weak key. 126 build 127 SELECT y, sum(z) FROM xyzs GROUP BY z, y 128 ---- 129 project 130 ├── columns: y:2(int) sum:5(float!null) 131 ├── prune: (2,5) 132 └── group-by 133 ├── columns: y:2(int) z:3(float!null) sum:5(float!null) 134 ├── grouping columns: y:2(int) z:3(float!null) 135 ├── key: (2,3) 136 ├── fd: (2,3)-->(5) 137 ├── prune: (5) 138 ├── project 139 │ ├── columns: y:2(int) z:3(float!null) 140 │ ├── prune: (2,3) 141 │ └── scan xyzs 142 │ ├── columns: x:1(int!null) y:2(int) z:3(float!null) s:4(string) 143 │ ├── key: (1) 144 │ ├── fd: (1)-->(2-4), (3,4)~~>(1,2) 145 │ ├── prune: (1-4) 146 │ └── interesting orderings: (+1) (-4,+3,+1) 147 └── aggregations 148 └── sum [as=sum:5, type=float, outer=(3)] 149 └── variable: z:3 [type=float] 150 151 # Group by column that is subset of unique index. 152 build 153 SELECT z, max(s) FROM xyzs GROUP BY z 154 ---- 155 group-by 156 ├── columns: z:3(float!null) max:5(string) 157 ├── grouping columns: z:3(float!null) 158 ├── key: (3) 159 ├── fd: (3)-->(5) 160 ├── prune: (5) 161 ├── project 162 │ ├── columns: z:3(float!null) s:4(string) 163 │ ├── lax-key: (3,4) 164 │ ├── prune: (3,4) 165 │ ├── interesting orderings: (-4,+3) 166 │ └── scan xyzs 167 │ ├── columns: x:1(int!null) y:2(int) z:3(float!null) s:4(string) 168 │ ├── key: (1) 169 │ ├── fd: (1)-->(2-4), (3,4)~~>(1,2) 170 │ ├── prune: (1-4) 171 │ └── interesting orderings: (+1) (-4,+3,+1) 172 └── aggregations 173 └── max [as=max:5, type=string, outer=(4)] 174 └── variable: s:4 [type=string] 175 176 # Group by all columns. 177 build 178 SELECT s FROM xyzs GROUP BY xyzs.* 179 ---- 180 project 181 ├── columns: s:4(string) 182 ├── prune: (4) 183 ├── interesting orderings: (-4) 184 └── group-by 185 ├── columns: x:1(int!null) y:2(int) z:3(float!null) s:4(string) 186 ├── grouping columns: x:1(int!null) y:2(int) z:3(float!null) s:4(string) 187 ├── key: (1) 188 ├── fd: (1)-->(2-4), (3,4)~~>(1,2) 189 ├── interesting orderings: (+1) (-4,+3,+1) 190 └── scan xyzs 191 ├── columns: x:1(int!null) y:2(int) z:3(float!null) s:4(string) 192 ├── key: (1) 193 ├── fd: (1)-->(2-4), (3,4)~~>(1,2) 194 ├── prune: (1-4) 195 └── interesting orderings: (+1) (-4,+3,+1) 196 197 # Propagate outer columns. 198 build 199 SELECT (SELECT sum(x) FROM (SELECT y, u FROM kuv) GROUP BY u) FROM xyzs GROUP BY y 200 ---- 201 project 202 ├── columns: sum:12(decimal) 203 ├── prune: (12) 204 ├── group-by 205 │ ├── columns: xyzs.y:2(int) sum:10(decimal!null) 206 │ ├── grouping columns: xyzs.y:2(int) 207 │ ├── key: (2) 208 │ ├── fd: (2)-->(10) 209 │ ├── prune: (10) 210 │ ├── project 211 │ │ ├── columns: x:9(int!null) xyzs.y:2(int) 212 │ │ ├── key: (9) 213 │ │ ├── fd: (9)-->(2) 214 │ │ ├── prune: (2,9) 215 │ │ ├── scan xyzs 216 │ │ │ ├── columns: xyzs.x:1(int!null) xyzs.y:2(int) z:3(float!null) s:4(string) 217 │ │ │ ├── key: (1) 218 │ │ │ ├── fd: (1)-->(2-4), (3,4)~~>(1,2) 219 │ │ │ ├── prune: (1-4) 220 │ │ │ └── interesting orderings: (+1) (-4,+3,+1) 221 │ │ └── projections 222 │ │ └── variable: xyzs.x:1 [as=x:9, type=int, outer=(1)] 223 │ └── aggregations 224 │ └── sum [as=sum:10, type=decimal, outer=(9)] 225 │ └── variable: x:9 [type=int] 226 └── projections 227 └── subquery [as=sum:12, type=decimal, outer=(2,10), correlated-subquery] 228 └── max1-row 229 ├── columns: sum:11(decimal) 230 ├── error: "more than one row returned by a subquery used as an expression" 231 ├── outer: (2,10) 232 ├── cardinality: [0 - 1] 233 ├── key: () 234 ├── fd: ()-->(11) 235 └── project 236 ├── columns: sum:11(decimal) 237 ├── outer: (2,10) 238 ├── fd: ()-->(11) 239 ├── prune: (11) 240 ├── group-by 241 │ ├── columns: u:6(float) 242 │ ├── grouping columns: u:6(float) 243 │ ├── outer: (2) 244 │ ├── key: (6) 245 │ └── project 246 │ ├── columns: u:6(float) 247 │ ├── outer: (2) 248 │ ├── prune: (6) 249 │ └── project 250 │ ├── columns: y:8(int) u:6(float) 251 │ ├── outer: (2) 252 │ ├── fd: ()-->(8) 253 │ ├── prune: (6,8) 254 │ ├── scan kuv 255 │ │ ├── columns: k:5(int!null) u:6(float) v:7(string) 256 │ │ ├── key: (5) 257 │ │ ├── fd: (5)-->(6,7) 258 │ │ ├── prune: (5-7) 259 │ │ └── interesting orderings: (+5) 260 │ └── projections 261 │ └── variable: xyzs.y:2 [as=y:8, type=int, outer=(2)] 262 └── projections 263 └── variable: sum:10 [as=sum:11, type=decimal, outer=(10)] 264 265 # Calculate groupby cardinality. 266 build 267 SELECT * FROM (VALUES (1), (2), (1), (NULL)) GROUP BY column1 268 ---- 269 group-by 270 ├── columns: column1:1(int) 271 ├── grouping columns: column1:1(int) 272 ├── cardinality: [1 - 4] 273 ├── key: (1) 274 └── values 275 ├── columns: column1:1(int) 276 ├── cardinality: [4 - 4] 277 ├── prune: (1) 278 ├── tuple [type=tuple{int}] 279 │ └── const: 1 [type=int] 280 ├── tuple [type=tuple{int}] 281 │ └── const: 2 [type=int] 282 ├── tuple [type=tuple{int}] 283 │ └── const: 1 [type=int] 284 └── tuple [type=tuple{int}] 285 └── cast: INT8 [type=int] 286 └── null [type=unknown] 287 288 # GroupBy with empty grouping columns. 289 opt 290 SELECT x, count(y) FROM xyzs GROUP BY x HAVING x=1 291 ---- 292 group-by 293 ├── columns: x:1(int!null) count:5(int!null) 294 ├── cardinality: [0 - 1] 295 ├── key: () 296 ├── fd: ()-->(1,5) 297 ├── prune: (1,5) 298 ├── scan xyzs 299 │ ├── columns: x:1(int!null) y:2(int) 300 │ ├── constraint: /1: [/1 - /1] 301 │ ├── cardinality: [0 - 1] 302 │ ├── key: () 303 │ ├── fd: ()-->(1,2) 304 │ ├── prune: (2) 305 │ └── interesting orderings: (+1) 306 └── aggregations 307 ├── count [as=count:5, type=int, outer=(2)] 308 │ └── variable: y:2 [type=int] 309 └── const-agg [as=x:1, type=int, outer=(1)] 310 └── variable: x:1 [type=int] 311 312 313 # Even with non-NULL input, some aggregates can still be NULL. 314 build 315 SELECT variance(x), stddev(x), corr(x, y) 316 FROM xyzs 317 GROUP BY x, y 318 ---- 319 project 320 ├── columns: variance:5(decimal) stddev:6(decimal) corr:7(float) 321 ├── prune: (5-7) 322 └── group-by 323 ├── columns: x:1(int!null) y:2(int) variance:5(decimal) stddev:6(decimal) corr:7(float) 324 ├── grouping columns: x:1(int!null) y:2(int) 325 ├── key: (1) 326 ├── fd: (1)-->(2,5-7) 327 ├── prune: (5-7) 328 ├── interesting orderings: (+1) 329 ├── project 330 │ ├── columns: x:1(int!null) y:2(int) 331 │ ├── key: (1) 332 │ ├── fd: (1)-->(2) 333 │ ├── prune: (1,2) 334 │ ├── interesting orderings: (+1) 335 │ └── scan xyzs 336 │ ├── columns: x:1(int!null) y:2(int) z:3(float!null) s:4(string) 337 │ ├── key: (1) 338 │ ├── fd: (1)-->(2-4), (3,4)~~>(1,2) 339 │ ├── prune: (1-4) 340 │ └── interesting orderings: (+1) (-4,+3,+1) 341 └── aggregations 342 ├── variance [as=variance:5, type=decimal, outer=(1)] 343 │ └── variable: x:1 [type=int] 344 ├── std-dev [as=stddev:6, type=decimal, outer=(1)] 345 │ └── variable: x:1 [type=int] 346 └── corr [as=corr:7, type=float, outer=(1,2)] 347 ├── variable: x:1 [type=int] 348 └── variable: y:2 [type=int]