-
Notifications
You must be signed in to change notification settings - Fork 3.8k
/
aggregate_builtins.go
4211 lines (3692 loc) · 128 KB
/
aggregate_builtins.go
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
449
450
451
452
453
454
455
456
457
458
459
460
461
462
463
464
465
466
467
468
469
470
471
472
473
474
475
476
477
478
479
480
481
482
483
484
485
486
487
488
489
490
491
492
493
494
495
496
497
498
499
500
501
502
503
504
505
506
507
508
509
510
511
512
513
514
515
516
517
518
519
520
521
522
523
524
525
526
527
528
529
530
531
532
533
534
535
536
537
538
539
540
541
542
543
544
545
546
547
548
549
550
551
552
553
554
555
556
557
558
559
560
561
562
563
564
565
566
567
568
569
570
571
572
573
574
575
576
577
578
579
580
581
582
583
584
585
586
587
588
589
590
591
592
593
594
595
596
597
598
599
600
601
602
603
604
605
606
607
608
609
610
611
612
613
614
615
616
617
618
619
620
621
622
623
624
625
626
627
628
629
630
631
632
633
634
635
636
637
638
639
640
641
642
643
644
645
646
647
648
649
650
651
652
653
654
655
656
657
658
659
660
661
662
663
664
665
666
667
668
669
670
671
672
673
674
675
676
677
678
679
680
681
682
683
684
685
686
687
688
689
690
691
692
693
694
695
696
697
698
699
700
701
702
703
704
705
706
707
708
709
710
711
712
713
714
715
716
717
718
719
720
721
722
723
724
725
726
727
728
729
730
731
732
733
734
735
736
737
738
739
740
741
742
743
744
745
746
747
748
749
750
751
752
753
754
755
756
757
758
759
760
761
762
763
764
765
766
767
768
769
770
771
772
773
774
775
776
777
778
779
780
781
782
783
784
785
786
787
788
789
790
791
792
793
794
795
796
797
798
799
800
801
802
803
804
805
806
807
808
809
810
811
812
813
814
815
816
817
818
819
820
821
822
823
824
825
826
827
828
829
830
831
832
833
834
835
836
837
838
839
840
841
842
843
844
845
846
847
848
849
850
851
852
853
854
855
856
857
858
859
860
861
862
863
864
865
866
867
868
869
870
871
872
873
874
875
876
877
878
879
880
881
882
883
884
885
886
887
888
889
890
891
892
893
894
895
896
897
898
899
900
901
902
903
904
905
906
907
908
909
910
911
912
913
914
915
916
917
918
919
920
921
922
923
924
925
926
927
928
929
930
931
932
933
934
935
936
937
938
939
940
941
942
943
944
945
946
947
948
949
950
951
952
953
954
955
956
957
958
959
960
961
962
963
964
965
966
967
968
969
970
971
972
973
974
975
976
977
978
979
980
981
982
983
984
985
986
987
988
989
990
991
992
993
994
995
996
997
998
999
1000
// Copyright 2015 The Cockroach Authors.
//
// Use of this software is governed by the Business Source License
// included in the file licenses/BSL.txt.
//
// As of the Change Date specified in that file, in accordance with
// the Business Source License, use of this software will be governed
// by the Apache License, Version 2.0, included in the file
// licenses/APL.txt.
package builtins
import (
"bytes"
"context"
"fmt"
"math"
"time"
"unsafe"
"github.com/cockroachdb/apd/v2"
"github.com/cockroachdb/cockroach/pkg/geo"
"github.com/cockroachdb/cockroach/pkg/geo/geopb"
"github.com/cockroachdb/cockroach/pkg/geo/geos"
"github.com/cockroachdb/cockroach/pkg/sql/pgwire/pgcode"
"github.com/cockroachdb/cockroach/pkg/sql/pgwire/pgerror"
"github.com/cockroachdb/cockroach/pkg/sql/sem/tree"
"github.com/cockroachdb/cockroach/pkg/sql/types"
"github.com/cockroachdb/cockroach/pkg/util/arith"
"github.com/cockroachdb/cockroach/pkg/util/bitarray"
"github.com/cockroachdb/cockroach/pkg/util/duration"
"github.com/cockroachdb/cockroach/pkg/util/json"
"github.com/cockroachdb/cockroach/pkg/util/mon"
"github.com/cockroachdb/errors"
"github.com/twpayne/go-geom"
)
func initAggregateBuiltins() {
// Add all aggregates to the Builtins map after a few sanity checks.
for k, v := range aggregates {
if _, exists := builtins[k]; exists {
panic("duplicate builtin: " + k)
}
if v.props.Class != tree.AggregateClass {
panic(errors.AssertionFailedf("%s: aggregate functions should be marked with the tree.AggregateClass "+
"function class, found %v", k, v))
}
for _, a := range v.overloads {
if a.AggregateFunc == nil {
panic(errors.AssertionFailedf("%s: aggregate functions should have tree.AggregateFunc constructors, "+
"found %v", k, a))
}
if a.WindowFunc == nil {
panic(errors.AssertionFailedf("%s: aggregate functions should have tree.WindowFunc constructors, "+
"found %v", k, a))
}
}
builtins[k] = v
}
}
func aggProps() tree.FunctionProperties {
return tree.FunctionProperties{Class: tree.AggregateClass}
}
func aggPropsNullableArgs() tree.FunctionProperties {
f := aggProps()
f.NullableArgs = true
return f
}
// allMaxMinAggregateTypes contains extra types that aren't in
// types.Scalar that the max/min aggregate functions are defined on.
var allMaxMinAggregateTypes = append(
[]*types.T{types.AnyCollatedString, types.AnyEnum},
types.Scalar...,
)
// aggregates are a special class of builtin functions that are wrapped
// at execution in a bucketing layer to combine (aggregate) the result
// of the function being run over many rows.
//
// See `aggregateFuncHolder` in the sql package.
//
// In particular they must not be simplified during normalization
// (and thus must be marked as impure), even when they are given a
// constant argument (e.g. SUM(1)). This is because aggregate
// functions must return NULL when they are no rows in the source
// table, so their evaluation must always be delayed until query
// execution.
//
// Some aggregate functions must handle nullable arguments, since normalizing
// an aggregate function call to NULL in the presence of a NULL argument may
// not be correct. There are two cases where an aggregate function must handle
// nullable arguments:
// 1) the aggregate function does not skip NULLs (e.g., ARRAY_AGG); and
// 2) the aggregate function does not return NULL when it aggregates no rows
// (e.g., COUNT).
//
// For use in other packages, see AllAggregateBuiltinNames and
// GetBuiltinProperties().
// These functions are also identified with Class == tree.AggregateClass.
// The properties are reachable via tree.FunctionDefinition.
var aggregates = map[string]builtinDefinition{
"array_agg": setProps(aggPropsNullableArgs(),
arrayBuiltin(func(t *types.T) tree.Overload {
return makeAggOverloadWithReturnType(
[]*types.T{t},
func(args []tree.TypedExpr) *types.T {
if len(args) == 0 {
return types.MakeArray(t)
}
// Whenever possible, use the expression's type, so we can properly
// handle aliased types that don't explicitly have overloads.
return types.MakeArray(args[0].ResolvedType())
},
newArrayAggregate,
"Aggregates the selected values into an array.",
tree.VolatilityImmutable,
)
})),
"avg": makeBuiltin(aggProps(),
makeAggOverload([]*types.T{types.Int}, types.Decimal, newIntAvgAggregate,
"Calculates the average of the selected values.", tree.VolatilityImmutable),
makeAggOverload([]*types.T{types.Float}, types.Float, newFloatAvgAggregate,
"Calculates the average of the selected values.", tree.VolatilityImmutable),
makeAggOverload([]*types.T{types.Decimal}, types.Decimal, newDecimalAvgAggregate,
"Calculates the average of the selected values.", tree.VolatilityImmutable),
makeAggOverload([]*types.T{types.Interval}, types.Interval, newIntervalAvgAggregate,
"Calculates the average of the selected values.", tree.VolatilityImmutable),
),
"bit_and": makeBuiltin(aggProps(),
makeAggOverload([]*types.T{types.Int}, types.Int, newIntBitAndAggregate,
"Calculates the bitwise AND of all non-null input values, or null if none.", tree.VolatilityImmutable),
makeAggOverload([]*types.T{types.VarBit}, types.VarBit, newBitBitAndAggregate,
"Calculates the bitwise AND of all non-null input values, or null if none.", tree.VolatilityImmutable),
),
"bit_or": makeBuiltin(aggProps(),
makeAggOverload([]*types.T{types.Int}, types.Int, newIntBitOrAggregate,
"Calculates the bitwise OR of all non-null input values, or null if none.", tree.VolatilityImmutable),
makeAggOverload([]*types.T{types.VarBit}, types.VarBit, newBitBitOrAggregate,
"Calculates the bitwise OR of all non-null input values, or null if none.", tree.VolatilityImmutable),
),
"bool_and": makeBuiltin(aggProps(),
makeAggOverload([]*types.T{types.Bool}, types.Bool, newBoolAndAggregate,
"Calculates the boolean value of `AND`ing all selected values.", tree.VolatilityImmutable),
),
"bool_or": makeBuiltin(aggProps(),
makeAggOverload([]*types.T{types.Bool}, types.Bool, newBoolOrAggregate,
"Calculates the boolean value of `OR`ing all selected values.", tree.VolatilityImmutable),
),
"concat_agg": makeBuiltin(aggProps(),
makeAggOverload([]*types.T{types.String}, types.String, newStringConcatAggregate,
"Concatenates all selected values.", tree.VolatilityImmutable),
makeAggOverload([]*types.T{types.Bytes}, types.Bytes, newBytesConcatAggregate,
"Concatenates all selected values.", tree.VolatilityImmutable),
// TODO(eisen): support collated strings when the type system properly
// supports parametric types.
),
"corr": makeRegressionAggregateBuiltin(
newCorrAggregate,
"Calculates the correlation coefficient of the selected values.",
),
"covar_pop": makeRegressionAggregateBuiltin(
newCovarPopAggregate,
"Calculates the population covariance of the selected values.",
),
"covar_samp": makeRegressionAggregateBuiltin(
newCovarSampAggregate,
"Calculates the sample covariance of the selected values.",
),
"regr_avgx": makeRegressionAggregateBuiltin(
newRegressionAvgXAggregate, "Calculates the average of the independent variable (sum(X)/N).",
),
"regr_avgy": makeRegressionAggregateBuiltin(
newRegressionAvgYAggregate, "Calculates the average of the dependent variable (sum(Y)/N).",
),
"regr_intercept": makeRegressionAggregateBuiltin(
newRegressionInterceptAggregate, "Calculates y-intercept of the least-squares-fit linear equation determined by the (X, Y) pairs.",
),
"regr_r2": makeRegressionAggregateBuiltin(
newRegressionR2Aggregate, "Calculates square of the correlation coefficient.",
),
"regr_slope": makeRegressionAggregateBuiltin(
newRegressionSlopeAggregate, "Calculates slope of the least-squares-fit linear equation determined by the (X, Y) pairs.",
),
"regr_sx": makeRegressionAggregateBuiltin(
newRegressionSXAggregate, "Calculates sum of the independent variable.",
),
"regr_sy": makeRegressionAggregateBuiltin(
newRegressionSYAggregate, "Calculates sum of the independent variable.",
),
"regr_sxx": makeRegressionAggregateBuiltin(
newRegressionSXXAggregate, "Calculates sum of squares of the independent variable.",
),
"regr_sxy": makeRegressionAggregateBuiltin(
newRegressionSXYAggregate, "Calculates sum of products of independent times dependent variable.",
),
"regr_syy": makeRegressionAggregateBuiltin(
newRegressionSYYAggregate, "Calculates sum of squares of the dependent variable.",
),
"regr_count": makeBuiltin(aggProps(),
makeAggOverload([]*types.T{types.Float, types.Float}, types.Int, newRegressionCountAggregate,
"Calculates number of input rows in which both expressions are nonnull.", tree.VolatilityImmutable),
makeAggOverload([]*types.T{types.Int, types.Int}, types.Int, newRegressionCountAggregate,
"Calculates number of input rows in which both expressions are nonnull.", tree.VolatilityImmutable),
makeAggOverload([]*types.T{types.Decimal, types.Decimal}, types.Int, newRegressionCountAggregate,
"Calculates number of input rows in which both expressions are nonnull.", tree.VolatilityImmutable),
makeAggOverload([]*types.T{types.Float, types.Int}, types.Int, newRegressionCountAggregate,
"Calculates number of input rows in which both expressions are nonnull.", tree.VolatilityImmutable),
makeAggOverload([]*types.T{types.Float, types.Decimal}, types.Int, newRegressionCountAggregate,
"Calculates number of input rows in which both expressions are nonnull.", tree.VolatilityImmutable),
makeAggOverload([]*types.T{types.Int, types.Float}, types.Int, newRegressionCountAggregate,
"Calculates number of input rows in which both expressions are nonnull.", tree.VolatilityImmutable),
makeAggOverload([]*types.T{types.Int, types.Decimal}, types.Int, newRegressionCountAggregate,
"Calculates number of input rows in which both expressions are nonnull.", tree.VolatilityImmutable),
makeAggOverload([]*types.T{types.Decimal, types.Float}, types.Int, newRegressionCountAggregate,
"Calculates number of input rows in which both expressions are nonnull.", tree.VolatilityImmutable),
makeAggOverload([]*types.T{types.Decimal, types.Int}, types.Int, newRegressionCountAggregate,
"Calculates number of input rows in which both expressions are nonnull.", tree.VolatilityImmutable),
),
"count": makeBuiltin(aggPropsNullableArgs(),
makeAggOverload([]*types.T{types.Any}, types.Int, newCountAggregate,
"Calculates the number of selected elements.", tree.VolatilityImmutable),
),
"count_rows": makeBuiltin(aggProps(),
tree.Overload{
Types: tree.ArgTypes{},
ReturnType: tree.FixedReturnType(types.Int),
AggregateFunc: newCountRowsAggregate,
WindowFunc: func(params []*types.T, evalCtx *tree.EvalContext) tree.WindowFunc {
return newFramableAggregateWindow(
newCountRowsAggregate(params, evalCtx, nil /* arguments */),
func(evalCtx *tree.EvalContext, arguments tree.Datums) tree.AggregateFunc {
return newCountRowsAggregate(params, evalCtx, arguments)
},
)
},
Info: "Calculates the number of rows.",
Volatility: tree.VolatilityImmutable,
},
),
"every": makeBuiltin(aggProps(),
makeAggOverload([]*types.T{types.Bool}, types.Bool, newBoolAndAggregate,
"Calculates the boolean value of `AND`ing all selected values.", tree.VolatilityImmutable),
),
"max": collectOverloads(aggProps(), allMaxMinAggregateTypes,
func(t *types.T) tree.Overload {
info := "Identifies the maximum selected value."
vol := tree.VolatilityImmutable
return makeAggOverloadWithReturnType(
[]*types.T{t}, tree.IdentityReturnType(0), newMaxAggregate, info, vol,
)
}),
"min": collectOverloads(aggProps(), allMaxMinAggregateTypes,
func(t *types.T) tree.Overload {
info := "Identifies the minimum selected value."
vol := tree.VolatilityImmutable
return makeAggOverloadWithReturnType(
[]*types.T{t}, tree.IdentityReturnType(0), newMinAggregate, info, vol,
)
}),
"string_agg": makeBuiltin(aggPropsNullableArgs(),
makeAggOverload([]*types.T{types.String, types.String}, types.String, newStringConcatAggregate,
"Concatenates all selected values using the provided delimiter.", tree.VolatilityImmutable),
makeAggOverload([]*types.T{types.Bytes, types.Bytes}, types.Bytes, newBytesConcatAggregate,
"Concatenates all selected values using the provided delimiter.", tree.VolatilityImmutable),
),
"sum_int": makeBuiltin(aggProps(),
makeAggOverload([]*types.T{types.Int}, types.Int, newSmallIntSumAggregate,
"Calculates the sum of the selected values.", tree.VolatilityImmutable),
),
"sum": makeBuiltin(aggProps(),
makeAggOverload([]*types.T{types.Int}, types.Decimal, newIntSumAggregate,
"Calculates the sum of the selected values.", tree.VolatilityImmutable),
makeAggOverload([]*types.T{types.Float}, types.Float, newFloatSumAggregate,
"Calculates the sum of the selected values.", tree.VolatilityImmutable),
makeAggOverload([]*types.T{types.Decimal}, types.Decimal, newDecimalSumAggregate,
"Calculates the sum of the selected values.", tree.VolatilityImmutable),
makeAggOverload([]*types.T{types.Interval}, types.Interval, newIntervalSumAggregate,
"Calculates the sum of the selected values.", tree.VolatilityImmutable),
),
"sqrdiff": makeBuiltin(aggProps(),
makeAggOverload([]*types.T{types.Int}, types.Decimal, newIntSqrDiffAggregate,
"Calculates the sum of squared differences from the mean of the selected values.", tree.VolatilityImmutable),
makeAggOverload([]*types.T{types.Decimal}, types.Decimal, newDecimalSqrDiffAggregate,
"Calculates the sum of squared differences from the mean of the selected values.", tree.VolatilityImmutable),
makeAggOverload([]*types.T{types.Float}, types.Float, newFloatSqrDiffAggregate,
"Calculates the sum of squared differences from the mean of the selected values.", tree.VolatilityImmutable),
),
// final_(variance|stddev) computes the global (variance|standard deviation)
// from an arbitrary collection of local sums of squared difference from the mean.
// Adapted from https://www.johndcook.com/blog/skewness_kurtosis and
// https://github.com/cockroachdb/cockroach/pull/17728.
// TODO(knz): The 3-argument final_variance and final_stddev are
// only defined for internal use by distributed aggregations. They
// are marked as "private" so as to not trigger panics from issue
// #10495.
// The input signature is: SQDIFF, SUM, COUNT
"final_variance": makePrivate(makeBuiltin(aggProps(),
makeAggOverload(
[]*types.T{types.Decimal, types.Decimal, types.Int},
types.Decimal,
newDecimalFinalVarianceAggregate,
"Calculates the variance from the selected locally-computed squared difference values.",
tree.VolatilityImmutable,
),
makeAggOverload(
[]*types.T{types.Float, types.Float, types.Int},
types.Float,
newFloatFinalVarianceAggregate,
"Calculates the variance from the selected locally-computed squared difference values.",
tree.VolatilityImmutable,
),
)),
"final_stddev": makePrivate(makeBuiltin(aggProps(),
makeAggOverload(
[]*types.T{types.Decimal, types.Decimal, types.Int},
types.Decimal,
newDecimalFinalStdDevAggregate,
"Calculates the standard deviation from the selected locally-computed squared difference values.",
tree.VolatilityImmutable,
),
makeAggOverload(
[]*types.T{types.Float, types.Float, types.Int},
types.Float,
newFloatFinalStdDevAggregate,
"Calculates the standard deviation from the selected locally-computed squared difference values.",
tree.VolatilityImmutable,
),
)),
"final_stddev_pop": makePrivate(makeBuiltin(aggProps(),
makeAggOverload(
[]*types.T{types.Decimal, types.Decimal, types.Int},
types.Decimal,
newDecimalFinalStddevPopAggregate,
"Calculates the population standard deviation from the selected locally-computed squared difference values.",
tree.VolatilityImmutable,
),
makeAggOverload(
[]*types.T{types.Float, types.Float, types.Int},
types.Float,
newFloatFinalStddevPopAggregate,
"Calculates the population standard deviation from the selected locally-computed squared difference values.",
tree.VolatilityImmutable,
),
)),
"final_var_pop": makePrivate(makeBuiltin(aggProps(),
makeAggOverload(
[]*types.T{types.Decimal, types.Decimal, types.Int},
types.Decimal,
newDecimalFinalVarPopAggregate,
"Calculates the population variance from the selected locally-computed squared difference values.",
tree.VolatilityImmutable,
),
makeAggOverload(
[]*types.T{types.Float, types.Float, types.Int},
types.Float,
newFloatFinalVarPopAggregate,
"Calculates the population variance from the selected locally-computed squared difference values.",
tree.VolatilityImmutable,
),
)),
"final_sqrdiff": makePrivate(makeBuiltin(aggProps(),
makeAggOverload(
[]*types.T{types.Decimal, types.Decimal, types.Int},
types.Decimal,
newDecimalFinalSqrdiffAggregate,
"Calculates the sum of squared differences from the selected locally-computed squared difference values.",
tree.VolatilityImmutable,
),
makeAggOverload(
[]*types.T{types.Float, types.Float, types.Int},
types.Float,
newFloatFinalSqrdiffAggregate,
"Calculates the sum of squared differences from the selected locally-computed squared difference values.",
tree.VolatilityImmutable,
),
)),
"final_regr_sxx": makePrivate(makeBuiltin(aggProps(),
makeAggOverload(
[]*types.T{types.Decimal, types.Decimal, types.Int},
types.Decimal,
newDecimalFinalRegressionSxxAggregate,
"Calculates sum of squares of the independent variable from the selected locally-computed squared difference values.",
tree.VolatilityImmutable,
),
makeAggOverload(
[]*types.T{types.Float, types.Float, types.Int},
types.Float,
newFloatFinalRegressionSxxAggregate,
"Calculates sum of squares of the independent variable from the selected locally-computed squared difference values.",
tree.VolatilityImmutable,
),
)),
"final_regr_syy": makePrivate(makeBuiltin(aggProps(),
makeAggOverload(
[]*types.T{types.Decimal, types.Decimal, types.Int},
types.Decimal,
newDecimalFinalRegressionSyyAggregate,
"Calculates sum of squares of the dependent variable from the selected locally-computed squared difference values.",
tree.VolatilityImmutable,
),
makeAggOverload(
[]*types.T{types.Float, types.Float, types.Int},
types.Float,
newFloatFinalRegressionSyyAggregate,
"Calculates sum of squares of the dependent variable from the selected locally-computed squared difference values.",
tree.VolatilityImmutable,
),
)),
// variance is a historical alias for var_samp.
"variance": makeVarianceBuiltin(),
"var_samp": makeVarianceBuiltin(),
"var_pop": makeBuiltin(aggProps(),
makeAggOverload([]*types.T{types.Int}, types.Decimal, newIntVarPopAggregate,
"Calculates the population variance of the selected values.", tree.VolatilityImmutable),
makeAggOverload([]*types.T{types.Decimal}, types.Decimal, newDecimalVarPopAggregate,
"Calculates the population variance of the selected values.", tree.VolatilityImmutable),
makeAggOverload([]*types.T{types.Float}, types.Float, newFloatVarPopAggregate,
"Calculates the population variance of the selected values.", tree.VolatilityImmutable),
),
// stddev is a historical alias for stddev_samp.
"stddev": makeStdDevBuiltin(),
"stddev_samp": makeStdDevBuiltin(),
"stddev_pop": makeBuiltin(aggProps(),
makeAggOverload([]*types.T{types.Int}, types.Decimal, newIntStdDevPopAggregate,
"Calculates the population standard deviation of the selected values.", tree.VolatilityImmutable),
makeAggOverload([]*types.T{types.Decimal}, types.Decimal, newDecimalStdDevPopAggregate,
"Calculates the population standard deviation of the selected values.", tree.VolatilityImmutable),
makeAggOverload([]*types.T{types.Float}, types.Float, newFloatStdDevPopAggregate,
"Calculates the population standard deviation of the selected values.", tree.VolatilityImmutable),
),
"xor_agg": makeBuiltin(aggProps(),
makeAggOverload([]*types.T{types.Bytes}, types.Bytes, newBytesXorAggregate,
"Calculates the bitwise XOR of the selected values.", tree.VolatilityImmutable),
makeAggOverload([]*types.T{types.Int}, types.Int, newIntXorAggregate,
"Calculates the bitwise XOR of the selected values.", tree.VolatilityImmutable),
),
"json_agg": makeBuiltin(aggPropsNullableArgs(),
makeAggOverload([]*types.T{types.Any}, types.Jsonb, newJSONAggregate,
"Aggregates values as a JSON or JSONB array.", tree.VolatilityStable),
),
"jsonb_agg": makeBuiltin(aggPropsNullableArgs(),
makeAggOverload([]*types.T{types.Any}, types.Jsonb, newJSONAggregate,
"Aggregates values as a JSON or JSONB array.", tree.VolatilityStable),
),
"json_object_agg": makeBuiltin(aggPropsNullableArgs(),
makeAggOverload([]*types.T{types.String, types.Any}, types.Jsonb, newJSONObjectAggregate,
"Aggregates values as a JSON or JSONB object.", tree.VolatilityStable),
),
"jsonb_object_agg": makeBuiltin(aggPropsNullableArgs(),
makeAggOverload([]*types.T{types.String, types.Any}, types.Jsonb, newJSONObjectAggregate,
"Aggregates values as a JSON or JSONB object.", tree.VolatilityStable),
),
"st_makeline": makeBuiltin(
tree.FunctionProperties{
Class: tree.AggregateClass,
NullableArgs: true,
AvailableOnPublicSchema: true,
},
makeAggOverload(
[]*types.T{types.Geometry},
types.Geometry,
func(
params []*types.T, evalCtx *tree.EvalContext, arguments tree.Datums,
) tree.AggregateFunc {
return &stMakeLineAgg{
acc: evalCtx.Mon.MakeBoundAccount(),
}
},
infoBuilder{
info: "Forms a LineString from Point, MultiPoint or LineStrings. Other shapes will be ignored.",
}.String(),
tree.VolatilityImmutable,
),
),
"st_extent": makeBuiltin(
tree.FunctionProperties{
Class: tree.AggregateClass,
NullableArgs: true,
AvailableOnPublicSchema: true,
},
makeAggOverload(
[]*types.T{types.Geometry},
types.Box2D,
func(
params []*types.T, evalCtx *tree.EvalContext, arguments tree.Datums,
) tree.AggregateFunc {
return &stExtentAgg{}
},
infoBuilder{
info: "Forms a Box2D that encapsulates all provided geometries.",
}.String(),
tree.VolatilityImmutable,
),
),
"st_union": makeSTUnionBuiltin(),
"st_memunion": makeSTUnionBuiltin(),
"st_collect": makeSTCollectBuiltin(),
"st_memcollect": makeSTCollectBuiltin(),
AnyNotNull: makePrivate(makeBuiltin(aggProps(),
makeAggOverloadWithReturnType(
[]*types.T{types.Any},
tree.IdentityReturnType(0),
newAnyNotNullAggregate,
"Returns an arbitrary not-NULL value, or NULL if none exists.",
tree.VolatilityImmutable,
))),
// Ordered-set aggregations.
"percentile_disc": makeBuiltin(aggProps(),
makeAggOverloadWithReturnType(
[]*types.T{types.Float},
func(args []tree.TypedExpr) *types.T { return tree.UnknownReturnType },
builtinMustNotRun,
"Discrete percentile: returns the first input value whose position in the ordering equals or "+
"exceeds the specified fraction.",
tree.VolatilityImmutable),
makeAggOverloadWithReturnType(
[]*types.T{types.FloatArray},
func(args []tree.TypedExpr) *types.T { return tree.UnknownReturnType },
builtinMustNotRun,
"Discrete percentile: returns input values whose position in the ordering equals or "+
"exceeds the specified fractions.",
tree.VolatilityImmutable),
),
"percentile_disc_impl": makePrivate(collectOverloads(aggProps(), types.Scalar,
func(t *types.T) tree.Overload {
return makeAggOverload([]*types.T{types.Float, t}, t, newPercentileDiscAggregate,
"Implementation of percentile_disc.",
tree.VolatilityImmutable)
},
func(t *types.T) tree.Overload {
return makeAggOverload([]*types.T{types.FloatArray, t}, types.MakeArray(t), newPercentileDiscAggregate,
"Implementation of percentile_disc.",
tree.VolatilityImmutable)
},
)),
"percentile_cont": makeBuiltin(aggProps(),
makeAggOverload(
[]*types.T{types.Float},
types.Float,
builtinMustNotRun,
"Continuous percentile: returns a float corresponding to the specified fraction in the ordering, "+
"interpolating between adjacent input floats if needed.",
tree.VolatilityImmutable),
makeAggOverload(
[]*types.T{types.Float},
types.Interval,
builtinMustNotRun,
"Continuous percentile: returns an interval corresponding to the specified fraction in the ordering, "+
"interpolating between adjacent input intervals if needed.",
tree.VolatilityImmutable),
makeAggOverload(
[]*types.T{types.FloatArray},
types.MakeArray(types.Float),
builtinMustNotRun,
"Continuous percentile: returns floats corresponding to the specified fractions in the ordering, "+
"interpolating between adjacent input floats if needed.",
tree.VolatilityImmutable),
makeAggOverload(
[]*types.T{types.FloatArray},
types.MakeArray(types.Interval),
builtinMustNotRun,
"Continuous percentile: returns intervals corresponding to the specified fractions in the ordering, "+
"interpolating between adjacent input intervals if needed.",
tree.VolatilityImmutable),
),
"percentile_cont_impl": makePrivate(makeBuiltin(aggProps(),
makeAggOverload(
[]*types.T{types.Float, types.Float},
types.Float,
newPercentileContAggregate,
"Implementation of percentile_cont.",
tree.VolatilityImmutable),
makeAggOverload(
[]*types.T{types.Float, types.Interval},
types.Interval,
newPercentileContAggregate,
"Implementation of percentile_cont.",
tree.VolatilityImmutable),
makeAggOverload(
[]*types.T{types.FloatArray, types.Float},
types.MakeArray(types.Float),
newPercentileContAggregate,
"Implementation of percentile_cont.",
tree.VolatilityImmutable),
makeAggOverload(
[]*types.T{types.FloatArray, types.Interval},
types.MakeArray(types.Interval),
newPercentileContAggregate,
"Implementation of percentile_cont.",
tree.VolatilityImmutable),
)),
}
// AnyNotNull is the name of the aggregate returned by NewAnyNotNullAggregate.
const AnyNotNull = "any_not_null"
func makePrivate(b builtinDefinition) builtinDefinition {
b.props.Private = true
return b
}
func makeAggOverload(
in []*types.T,
ret *types.T,
f func([]*types.T, *tree.EvalContext, tree.Datums) tree.AggregateFunc,
info string,
volatility tree.Volatility,
) tree.Overload {
return makeAggOverloadWithReturnType(
in,
tree.FixedReturnType(ret),
f,
info,
volatility,
)
}
func makeAggOverloadWithReturnType(
in []*types.T,
retType tree.ReturnTyper,
f func([]*types.T, *tree.EvalContext, tree.Datums) tree.AggregateFunc,
info string,
volatility tree.Volatility,
) tree.Overload {
argTypes := make(tree.ArgTypes, len(in))
for i, typ := range in {
argTypes[i].Name = fmt.Sprintf("arg%d", i+1)
argTypes[i].Typ = typ
}
return tree.Overload{
// See the comment about aggregate functions in the definitions
// of the Builtins array above.
Types: argTypes,
ReturnType: retType,
AggregateFunc: f,
WindowFunc: func(params []*types.T, evalCtx *tree.EvalContext) tree.WindowFunc {
aggWindowFunc := f(params, evalCtx, nil /* arguments */)
switch w := aggWindowFunc.(type) {
case *minAggregate:
min := &slidingWindowFunc{}
min.sw = makeSlidingWindow(evalCtx, func(evalCtx *tree.EvalContext, a, b tree.Datum) int {
return -a.Compare(evalCtx, b)
})
return min
case *maxAggregate:
max := &slidingWindowFunc{}
max.sw = makeSlidingWindow(evalCtx, func(evalCtx *tree.EvalContext, a, b tree.Datum) int {
return a.Compare(evalCtx, b)
})
return max
case *intSumAggregate:
return newSlidingWindowSumFunc(aggWindowFunc)
case *decimalSumAggregate:
return newSlidingWindowSumFunc(aggWindowFunc)
case *floatSumAggregate:
return newSlidingWindowSumFunc(aggWindowFunc)
case *intervalSumAggregate:
return newSlidingWindowSumFunc(aggWindowFunc)
case *avgAggregate:
// w.agg is a sum aggregate.
return &avgWindowFunc{sum: newSlidingWindowSumFunc(w.agg)}
}
return newFramableAggregateWindow(
aggWindowFunc,
func(evalCtx *tree.EvalContext, arguments tree.Datums) tree.AggregateFunc {
return f(params, evalCtx, arguments)
},
)
},
Info: info,
Volatility: volatility,
}
}
func makeStdDevBuiltin() builtinDefinition {
return makeBuiltin(aggProps(),
makeAggOverload([]*types.T{types.Int}, types.Decimal, newIntStdDevAggregate,
"Calculates the standard deviation of the selected values.", tree.VolatilityImmutable),
makeAggOverload([]*types.T{types.Decimal}, types.Decimal, newDecimalStdDevAggregate,
"Calculates the standard deviation of the selected values.", tree.VolatilityImmutable),
makeAggOverload([]*types.T{types.Float}, types.Float, newFloatStdDevAggregate,
"Calculates the standard deviation of the selected values.", tree.VolatilityImmutable),
)
}
func makeSTCollectBuiltin() builtinDefinition {
return makeBuiltin(
tree.FunctionProperties{
Class: tree.AggregateClass,
NullableArgs: true,
AvailableOnPublicSchema: true,
},
makeAggOverload(
[]*types.T{types.Geometry},
types.Geometry,
newSTCollectAgg,
infoBuilder{
info: "Collects geometries into a GeometryCollection or multi-type as appropriate.",
}.String(),
tree.VolatilityImmutable,
),
)
}
func makeSTUnionBuiltin() builtinDefinition {
return makeBuiltin(
tree.FunctionProperties{
Class: tree.AggregateClass,
NullableArgs: true,
AvailableOnPublicSchema: true,
},
makeAggOverload(
[]*types.T{types.Geometry},
types.Geometry,
func(
params []*types.T, evalCtx *tree.EvalContext, arguments tree.Datums,
) tree.AggregateFunc {
return &stUnionAgg{
acc: evalCtx.Mon.MakeBoundAccount(),
}
},
infoBuilder{
info: "Applies a spatial union to the geometries provided.",
}.String(),
tree.VolatilityImmutable,
),
)
}
func makeRegressionAggregateBuiltin(
aggregateFunc func([]*types.T, *tree.EvalContext, tree.Datums) tree.AggregateFunc, info string,
) builtinDefinition {
return makeBuiltin(aggProps(),
makeAggOverload([]*types.T{types.Float, types.Float}, types.Float, aggregateFunc,
info, tree.VolatilityImmutable),
makeAggOverload([]*types.T{types.Int, types.Int}, types.Float, aggregateFunc,
info, tree.VolatilityImmutable),
makeAggOverload([]*types.T{types.Decimal, types.Decimal}, types.Float, aggregateFunc,
info, tree.VolatilityImmutable),
makeAggOverload([]*types.T{types.Float, types.Int}, types.Float, aggregateFunc,
info, tree.VolatilityImmutable),
makeAggOverload([]*types.T{types.Float, types.Decimal}, types.Float, aggregateFunc,
info, tree.VolatilityImmutable),
makeAggOverload([]*types.T{types.Int, types.Float}, types.Float, aggregateFunc,
info, tree.VolatilityImmutable),
makeAggOverload([]*types.T{types.Int, types.Decimal}, types.Float, aggregateFunc,
info, tree.VolatilityImmutable),
makeAggOverload([]*types.T{types.Decimal, types.Float}, types.Float, aggregateFunc,
info, tree.VolatilityImmutable),
makeAggOverload([]*types.T{types.Decimal, types.Int}, types.Float, aggregateFunc,
info, tree.VolatilityImmutable),
)
}
type stMakeLineAgg struct {
flatCoords []float64
layout geom.Layout
acc mon.BoundAccount
}
// Add implements the AggregateFunc interface.
func (agg *stMakeLineAgg) Add(
ctx context.Context, firstArg tree.Datum, otherArgs ...tree.Datum,
) error {
if firstArg == tree.DNull {
return nil
}
geomArg := tree.MustBeDGeometry(firstArg)
g, err := geomArg.AsGeomT()
if err != nil {
return err
}
if len(agg.flatCoords) == 0 {
agg.layout = g.Layout()
} else if agg.layout != g.Layout() {
return errors.Newf(
"mixed dimensionality not allowed (adding dimension %s to dimension %s)",
g.Layout(),
agg.layout,
)
}
switch g.(type) {
case *geom.Point, *geom.LineString, *geom.MultiPoint:
if err := agg.acc.Grow(ctx, int64(len(g.FlatCoords())*8)); err != nil {
return err
}
agg.flatCoords = append(agg.flatCoords, g.FlatCoords()...)
}
return nil
}
// Result implements the AggregateFunc interface.
func (agg *stMakeLineAgg) Result() (tree.Datum, error) {
if len(agg.flatCoords) == 0 {
return tree.DNull, nil
}
g, err := geo.MakeGeometryFromGeomT(geom.NewLineStringFlat(agg.layout, agg.flatCoords))
if err != nil {
return nil, err
}
return tree.NewDGeometry(g), nil
}
// Reset implements the AggregateFunc interface.
func (agg *stMakeLineAgg) Reset(ctx context.Context) {
agg.flatCoords = agg.flatCoords[:0]
agg.acc.Empty(ctx)
}
// Close implements the AggregateFunc interface.
func (agg *stMakeLineAgg) Close(ctx context.Context) {
agg.acc.Close(ctx)
}
// Size implements the AggregateFunc interface.
func (agg *stMakeLineAgg) Size() int64 {
return sizeOfSTMakeLineAggregate
}
type stUnionAgg struct {
srid geopb.SRID
// TODO(#geo): store the current union object in C memory, to avoid the EWKB round trips.
ewkb geopb.EWKB
acc mon.BoundAccount
set bool
}
// Add implements the AggregateFunc interface.
func (agg *stUnionAgg) Add(
ctx context.Context, firstArg tree.Datum, otherArgs ...tree.Datum,
) error {
if firstArg == tree.DNull {
return nil
}
geomArg := tree.MustBeDGeometry(firstArg)
if !agg.set {
agg.ewkb = geomArg.EWKB()
agg.set = true
agg.srid = geomArg.SRID()
return nil
}
if agg.srid != geomArg.SRID() {
c, err := geo.ParseGeometryFromEWKB(agg.ewkb)
if err != nil {
return err
}
return geo.NewMismatchingSRIDsError(geomArg.Geometry.SpatialObject(), c.SpatialObject())
}
if err := agg.acc.Grow(ctx, int64(len(geomArg.EWKB()))); err != nil {
return err
}
var err error
// TODO(#geo):We are allocating a slice for the result each time we
// call geos.Union in cStringToSafeGoBytes.
// We could change geos.Union to accept the existing slice.
agg.ewkb, err = geos.Union(agg.ewkb, geomArg.EWKB())
if err != nil {
return err
}
return agg.acc.ResizeTo(ctx, int64(len(agg.ewkb)))
}
// Result implements the AggregateFunc interface.
func (agg *stUnionAgg) Result() (tree.Datum, error) {
if !agg.set {
return tree.DNull, nil
}
g, err := geo.ParseGeometryFromEWKB(agg.ewkb)
if err != nil {
return nil, err
}
return tree.NewDGeometry(g), nil
}
// Reset implements the AggregateFunc interface.
func (agg *stUnionAgg) Reset(ctx context.Context) {
agg.ewkb = nil
agg.set = false
agg.acc.Empty(ctx)
}
// Close implements the AggregateFunc interface.
func (agg *stUnionAgg) Close(ctx context.Context) {
agg.acc.Close(ctx)
}
// Size implements the AggregateFunc interface.
func (agg *stUnionAgg) Size() int64 {
return sizeOfSTUnionAggregate
}
type stCollectAgg struct {
acc mon.BoundAccount
coll geom.T
}
func newSTCollectAgg(_ []*types.T, evalCtx *tree.EvalContext, _ tree.Datums) tree.AggregateFunc {
return &stCollectAgg{
acc: evalCtx.Mon.MakeBoundAccount(),
}
}
// Add implements the AggregateFunc interface.
func (agg *stCollectAgg) Add(
ctx context.Context, firstArg tree.Datum, otherArgs ...tree.Datum,
) error {
if firstArg == tree.DNull {
return nil
}
if err := agg.acc.Grow(ctx, int64(firstArg.Size())); err != nil {
return err
}
geomArg := tree.MustBeDGeometry(firstArg)
t, err := geomArg.AsGeomT()
if err != nil {
return err
}
if agg.coll != nil && agg.coll.SRID() != t.SRID() {
c, err := geo.MakeGeometryFromGeomT(agg.coll)
if err != nil {
return err
}
return geo.NewMismatchingSRIDsError(geomArg.Geometry.SpatialObject(), c.SpatialObject())
}
// Fast path for geometry collections
if gc, ok := agg.coll.(*geom.GeometryCollection); ok {
return gc.Push(t)
}
// Try to append to a multitype, if possible.
switch t := t.(type) {
case *geom.Point:
if agg.coll == nil {
agg.coll = geom.NewMultiPoint(t.Layout()).SetSRID(t.SRID())
}
if multi, ok := agg.coll.(*geom.MultiPoint); ok {
return multi.Push(t)
}
case *geom.LineString:
if agg.coll == nil {
agg.coll = geom.NewMultiLineString(t.Layout()).SetSRID(t.SRID())
}
if multi, ok := agg.coll.(*geom.MultiLineString); ok {