github.com/cockroachdb/cockroach@v20.2.0-alpha.1+incompatible/pkg/sql/opt/optbuilder/testdata/having (about) 1 # tests adapted from logictest -- aggregate 2 3 exec-ddl 4 CREATE TABLE kv ( 5 k INT PRIMARY KEY, 6 v INT, 7 w INT, 8 s STRING 9 ) 10 ---- 11 12 # Presence of HAVING triggers aggregation, reducing results to one row (even without GROUP BY). 13 build 14 SELECT 3 r FROM kv HAVING TRUE 15 ---- 16 project 17 ├── columns: r:5!null 18 ├── select 19 │ ├── scalar-group-by 20 │ │ └── project 21 │ │ └── scan kv 22 │ │ └── columns: k:1!null v:2 w:3 s:4 23 │ └── filters 24 │ └── true 25 └── projections 26 └── 3 [as=r:5] 27 28 build 29 SELECT s, count(*) FROM kv GROUP BY s HAVING count(*) > 1 30 ---- 31 select 32 ├── columns: s:4 count:5!null 33 ├── group-by 34 │ ├── columns: s:4 count_rows:5!null 35 │ ├── grouping columns: s:4 36 │ ├── project 37 │ │ ├── columns: s:4 38 │ │ └── scan kv 39 │ │ └── columns: k:1!null v:2 w:3 s:4 40 │ └── aggregations 41 │ └── count-rows [as=count_rows:5] 42 └── filters 43 └── count_rows:5 > 1 44 45 build 46 SELECT max(k), min(v) FROM kv HAVING min(v) > 2 47 ---- 48 select 49 ├── columns: max:5 min:6!null 50 ├── scalar-group-by 51 │ ├── columns: max:5 min:6 52 │ ├── project 53 │ │ ├── columns: k:1!null v:2 54 │ │ └── scan kv 55 │ │ └── columns: k:1!null v:2 w:3 s:4 56 │ └── aggregations 57 │ ├── max [as=max:5] 58 │ │ └── k:1 59 │ └── min [as=min:6] 60 │ └── v:2 61 └── filters 62 └── min:6 > 2 63 64 build 65 SELECT max(k), min(v) FROM kv HAVING max(v) > 2 66 ---- 67 project 68 ├── columns: max:5 min:6 69 └── select 70 ├── columns: max:5 min:6 max:7!null 71 ├── scalar-group-by 72 │ ├── columns: max:5 min:6 max:7 73 │ ├── project 74 │ │ ├── columns: k:1!null v:2 75 │ │ └── scan kv 76 │ │ └── columns: k:1!null v:2 w:3 s:4 77 │ └── aggregations 78 │ ├── max [as=max:5] 79 │ │ └── k:1 80 │ ├── min [as=min:6] 81 │ │ └── v:2 82 │ └── max [as=max:7] 83 │ └── v:2 84 └── filters 85 └── max:7 > 2 86 87 build 88 SELECT max(k), min(v) FROM kv HAVING max(min(v)) > 2 89 ---- 90 error (42803): max(): min(): aggregate function calls cannot be nested 91 92 build 93 SELECT max(k), min(v) FROM kv HAVING k 94 ---- 95 error (42804): argument of HAVING must be type bool, not type int 96 97 # Expressions listed in the HAVING clause must conform to same validation as the SELECT clause (grouped or aggregated). 98 build 99 SELECT 3 FROM kv GROUP BY v HAVING k > 5 100 ---- 101 error (42803): column "k" must appear in the GROUP BY clause or be used in an aggregate function 102 103 # Special case for grouping on primary key. 104 build 105 SELECT 3 FROM kv GROUP BY k HAVING v > 2 106 ---- 107 project 108 ├── columns: "?column?":5!null 109 ├── select 110 │ ├── columns: k:1!null v:2!null 111 │ ├── group-by 112 │ │ ├── columns: k:1!null v:2 113 │ │ ├── grouping columns: k:1!null v:2 114 │ │ └── project 115 │ │ ├── columns: k:1!null v:2 116 │ │ └── scan kv 117 │ │ └── columns: k:1!null v:2 w:3 s:4 118 │ └── filters 119 │ └── v:2 > 2 120 └── projections 121 └── 3 [as="?column?":5] 122 123 build 124 SELECT k FROM kv HAVING k > 7 125 ---- 126 error (42803): column "k" must appear in the GROUP BY clause or be used in an aggregate function 127 128 build 129 SELECT count(*), k+w AS r FROM kv GROUP BY k+w HAVING (k+w) > 5 130 ---- 131 select 132 ├── columns: count:5!null r:6!null 133 ├── group-by 134 │ ├── columns: count_rows:5!null column6:6 135 │ ├── grouping columns: column6:6 136 │ ├── project 137 │ │ ├── columns: column6:6 138 │ │ ├── scan kv 139 │ │ │ └── columns: k:1!null v:2 w:3 s:4 140 │ │ └── projections 141 │ │ └── k:1 + w:3 [as=column6:6] 142 │ └── aggregations 143 │ └── count-rows [as=count_rows:5] 144 └── filters 145 └── column6:6 > 5 146 147 build 148 SELECT count(*), k+w FROM kv GROUP BY k+w HAVING (k+v) > 5 149 ---- 150 error (42803): column "k" must appear in the GROUP BY clause or be used in an aggregate function 151 152 # Check that everything still works with differently qualified names 153 build 154 SELECT max(kv.v) FROM kv GROUP BY v HAVING kv.v > 5 155 ---- 156 project 157 ├── columns: max:5 158 └── select 159 ├── columns: v:2!null max:5 160 ├── group-by 161 │ ├── columns: v:2 max:5 162 │ ├── grouping columns: v:2 163 │ ├── project 164 │ │ ├── columns: v:2 165 │ │ └── scan kv 166 │ │ └── columns: k:1!null v:2 w:3 s:4 167 │ └── aggregations 168 │ └── max [as=max:5] 169 │ └── v:2 170 └── filters 171 └── v:2 > 5 172 173 build 174 SELECT sum(kv.w) FROM kv GROUP BY lower(s) HAVING lower(kv.s) LIKE 'test%' 175 ---- 176 project 177 ├── columns: sum:5 178 └── select 179 ├── columns: sum:5 column6:6!null 180 ├── group-by 181 │ ├── columns: sum:5 column6:6 182 │ ├── grouping columns: column6:6 183 │ ├── project 184 │ │ ├── columns: column6:6 w:3 185 │ │ ├── scan kv 186 │ │ │ └── columns: k:1!null v:2 w:3 s:4 187 │ │ └── projections 188 │ │ └── lower(s:4) [as=column6:6] 189 │ └── aggregations 190 │ └── sum [as=sum:5] 191 │ └── w:3 192 └── filters 193 └── column6:6 LIKE 'test%' 194 195 build 196 SELECT sum(kv.w) FROM kv GROUP BY lower(s) HAVING sum(w) IN (4, 5, 6) 197 ---- 198 project 199 ├── columns: sum:5!null 200 └── select 201 ├── columns: sum:5!null column6:6 202 ├── group-by 203 │ ├── columns: sum:5 column6:6 204 │ ├── grouping columns: column6:6 205 │ ├── project 206 │ │ ├── columns: column6:6 w:3 207 │ │ ├── scan kv 208 │ │ │ └── columns: k:1!null v:2 w:3 s:4 209 │ │ └── projections 210 │ │ └── lower(s:4) [as=column6:6] 211 │ └── aggregations 212 │ └── sum [as=sum:5] 213 │ └── w:3 214 └── filters 215 └── sum:5 IN (4, 5, 6) 216 217 build fully-qualify-names 218 SELECT t.kv.v FROM t.kv GROUP BY v, kv.k * w HAVING k * kv.w > 5 219 ---- 220 project 221 ├── columns: v:2 222 └── select 223 ├── columns: t.public.kv.v:2 column5:5!null 224 ├── group-by 225 │ ├── columns: t.public.kv.v:2 column5:5 226 │ ├── grouping columns: t.public.kv.v:2 column5:5 227 │ └── project 228 │ ├── columns: column5:5 t.public.kv.v:2 229 │ ├── scan t.public.kv 230 │ │ └── columns: t.public.kv.k:1!null t.public.kv.v:2 t.public.kv.w:3 t.public.kv.s:4 231 │ └── projections 232 │ └── t.public.kv.k:1 * t.public.kv.w:3 [as=column5:5] 233 └── filters 234 └── column5:5 > 5 235 236 build fully-qualify-names 237 SELECT t.kv.v FROM t.kv GROUP BY v, kv.k * w HAVING w > 5 238 ---- 239 error (42803): column "w" must appear in the GROUP BY clause or be used in an aggregate function 240 241 build fully-qualify-names 242 SELECT upper(s), count(s), count(upper(s)) FROM t.kv GROUP BY upper(s) HAVING count(s) > 1 243 ---- 244 select 245 ├── columns: upper:6 count:5!null count:7!null 246 ├── group-by 247 │ ├── columns: count:5!null column6:6 count:7!null 248 │ ├── grouping columns: column6:6 249 │ ├── project 250 │ │ ├── columns: column6:6 t.public.kv.s:4 251 │ │ ├── scan t.public.kv 252 │ │ │ └── columns: t.public.kv.k:1!null t.public.kv.v:2 t.public.kv.w:3 t.public.kv.s:4 253 │ │ └── projections 254 │ │ └── upper(t.public.kv.s:4) [as=column6:6] 255 │ └── aggregations 256 │ ├── count [as=count:5] 257 │ │ └── t.public.kv.s:4 258 │ └── count [as=count:7] 259 │ └── column6:6 260 └── filters 261 └── count:5 > 1 262 263 # Check that ordering by an alias of an aggregate works when HAVING is present. 264 build 265 SELECT sum(k) AS mk FROM kv GROUP BY v HAVING sum(k)=10 ORDER BY mk 266 ---- 267 project 268 ├── columns: mk:5!null 269 ├── ordering: +5 270 └── select 271 ├── columns: v:2 sum:5!null 272 ├── group-by 273 │ ├── columns: v:2 sum:5!null 274 │ ├── grouping columns: v:2 275 │ ├── project 276 │ │ ├── columns: k:1!null v:2 277 │ │ └── scan kv 278 │ │ └── columns: k:1!null v:2 w:3 s:4 279 │ └── aggregations 280 │ └── sum [as=sum:5] 281 │ └── k:1 282 └── filters 283 └── sum:5 = 10 284 285 build 286 SELECT sum(k) AS mk FROM kv GROUP BY v HAVING max(k) > 10 ORDER BY mk 287 ---- 288 sort 289 ├── columns: mk:5!null 290 ├── ordering: +5 291 └── project 292 ├── columns: sum:5!null 293 └── select 294 ├── columns: v:2 sum:5!null max:6!null 295 ├── group-by 296 │ ├── columns: v:2 sum:5!null max:6!null 297 │ ├── grouping columns: v:2 298 │ ├── project 299 │ │ ├── columns: k:1!null v:2 300 │ │ └── scan kv 301 │ │ └── columns: k:1!null v:2 w:3 s:4 302 │ └── aggregations 303 │ ├── sum [as=sum:5] 304 │ │ └── k:1 305 │ └── max [as=max:6] 306 │ └── k:1 307 └── filters 308 └── max:6 > 10 309 310 build 311 SELECT sum(k) AS mk FROM kv GROUP BY v HAVING v > 10 ORDER BY mk 312 ---- 313 sort 314 ├── columns: mk:5!null 315 ├── ordering: +5 316 └── project 317 ├── columns: sum:5!null 318 └── select 319 ├── columns: v:2!null sum:5!null 320 ├── group-by 321 │ ├── columns: v:2 sum:5!null 322 │ ├── grouping columns: v:2 323 │ ├── project 324 │ │ ├── columns: k:1!null v:2 325 │ │ └── scan kv 326 │ │ └── columns: k:1!null v:2 w:3 s:4 327 │ └── aggregations 328 │ └── sum [as=sum:5] 329 │ └── k:1 330 └── filters 331 └── v:2 > 10 332 333 build 334 SELECT max(k) AS mk1, max(k) AS mk2 FROM kv GROUP BY v HAVING max(k) > 10 ORDER BY mk1 335 ---- 336 sort 337 ├── columns: mk1:5!null mk2:5!null 338 ├── ordering: +5 339 └── project 340 ├── columns: max:5!null 341 └── select 342 ├── columns: v:2 max:5!null 343 ├── group-by 344 │ ├── columns: v:2 max:5!null 345 │ ├── grouping columns: v:2 346 │ ├── project 347 │ │ ├── columns: k:1!null v:2 348 │ │ └── scan kv 349 │ │ └── columns: k:1!null v:2 w:3 s:4 350 │ └── aggregations 351 │ └── max [as=max:5] 352 │ └── k:1 353 └── filters 354 └── max:5 > 10 355 356 build 357 SELECT max(k) AS mk1, max(k) AS mk2 FROM kv GROUP BY v HAVING max(k) > 10 ORDER BY mk2 358 ---- 359 sort 360 ├── columns: mk1:5!null mk2:5!null 361 ├── ordering: +5 362 └── project 363 ├── columns: max:5!null 364 └── select 365 ├── columns: v:2 max:5!null 366 ├── group-by 367 │ ├── columns: v:2 max:5!null 368 │ ├── grouping columns: v:2 369 │ ├── project 370 │ │ ├── columns: k:1!null v:2 371 │ │ └── scan kv 372 │ │ └── columns: k:1!null v:2 w:3 s:4 373 │ └── aggregations 374 │ └── max [as=max:5] 375 │ └── k:1 376 └── filters 377 └── max:5 > 10