github.com/matrixorigin/matrixone@v0.7.0/test/distributed/cases/function/func_aggr_variance.result (about)

     1  SELECT variance(null);
     2  variance(null)
     3  null
     4  create table t1(a tinyint, b SMALLINT, c BIGINT, d INT, e BIGINT, f FLOAT, g DOUBLE, h decimal(38,19), i DATE, k datetime, l TIMESTAMP, m char(255), n varchar(255));
     5  insert into t1 values(1, 1, 2, 43, 5, 35.5, 31.133, 14.314, "2012-03-10", "2012-03-12 10:03:12", "2022-03-12 13:03:12", "ab23c", "d5cf");
     6  insert into t1 values(71, 1, 2, 34, 5, 5.5, 341.13, 15.314, "2012-03-22", "2013-03-12 10:03:12", "2032-03-12 13:04:12", "abr23c", "3dcf");
     7  insert into t1 values(1, 1, 21, 4, 54, 53.5, 431.13, 14.394, "2011-03-12", "2015-03-12 10:03:12", "2002-03-12 13:03:12", "afbc", "dct5f");
     8  insert into t1 values(1, 71, 2, 34, 5, 5.5, 31.313, 124.314, "2012-01-12", "2019-03-12 10:03:12", "2013-03-12 13:03:12", "3abd1c", "dcvf");
     9  select variance(a) from t1;
    10  variance(a)
    11  918.7500000000001
    12  select variance(b) from t1;
    13  variance(b)
    14  918.75
    15  select variance(c) from t1;
    16  variance(c)
    17  67.6875
    18  select variance(d) from t1;
    19  variance(d)
    20  217.6875
    21  select variance(e) from t1;
    22  variance(e)
    23  450.18750000000006
    24  select variance(f) from t1;
    25  variance(f)
    26  420.75
    27  select variance(g) from t1;
    28  variance(g)
    29  32502.24871225
    30  select variance(h) from t1;
    31  variance(h)
    32  2254.078699999999
    33  select variance(i) from t1;
    34  invalid argument aggregate function variance, bad value [DATE]
    35  select variance(k) from t1;
    36  invalid argument aggregate function variance, bad value [DATETIME]
    37  select variance(l) from t1;
    38  invalid argument aggregate function variance, bad value [TIMESTAMP]
    39  select variance(m) from t1;
    40  invalid argument aggregate function variance, bad value [CHAR]
    41  select variance(n) from t1;
    42  invalid argument aggregate function variance, bad value [VARCHAR]
    43  drop table t1;
    44  select variance(99999999999999999.99999);
    45  variance(99999999999999999.99999)
    46  0.0
    47  select variance(999999999999999933193939.99999);
    48  variance(999999999999999933193939.99999)
    49  0.0
    50  select variance(9999999999999999999999999999999999.9999999999999);
    51  variance(9999999999999999999999999999999999.9999999999999)
    52  0.0
    53  select variance(-99999999999999999.99999);
    54  variance(-99999999999999999.99999)
    55  0.0
    56  select variance(-999999999999999933193939.99999);
    57  variance(-999999999999999933193939.99999)
    58  0.0
    59  select variance(-9999999999999999999999999999999999.9999999999999);
    60  variance(-9999999999999999999999999999999999.9999999999999)
    61  0.0
    62  create table t1(a bigint);
    63  select variance(a) from t1;
    64  variance(a)
    65  null
    66  insert into t1 values(null),(null),(null),(null);
    67  select variance(a) from t1;
    68  variance(a)
    69  null
    70  insert into t1 values(12417249128419),(124124125124151),(5124125151415),(124125152651515);
    71  select variance(a) from t1;
    72  variance(a)
    73  3.333282253265104E27
    74  drop table t1;
    75  create table t1 ( a int not null default 1, big bigint );
    76  insert into t1 (big) values (-1),(1234567890167),(92233720368547),(18446744073709515);
    77  select * from t1;
    78  a	big
    79  1	-1
    80  1	1234567890167
    81  1	92233720368547
    82  1	18446744073709515
    83  select distinct variance(big),max(big),variance(big)-1 from t1;
    84  variance(big)	max(big)	variance(big)-1
    85  6.3589001724387045E31	18446744073709515	6.3589001724387045E31
    86  select variance(big),max(big),variance(big)-1 from t1 group by a;
    87  variance(big)	max(big)	variance(big)-1
    88  6.3589001724387045E31	18446744073709515	6.3589001724387045E31
    89  insert into t1 (big) values (184467440737615);
    90  select * from t1;
    91  a	big
    92  1	-1
    93  1	1234567890167
    94  1	92233720368547
    95  1	18446744073709515
    96  1	184467440737615
    97  select variance(big),max(big),variance(big)-1 from t1;
    98  variance(big)	max(big)	variance(big)-1
    99  5.404043539963767E31	18446744073709515	5.404043539963767E31
   100  select variance(big),max(big),variance(big)-1 from t1 group by a;
   101  variance(big)	max(big)	variance(big)-1
   102  5.404043539963767E31	18446744073709515	5.404043539963767E31
   103  drop table t1;
   104  CREATE TABLE t1 (Fld1 int(11) default NULL,Fld2 int(11) default NULL);
   105  INSERT INTO t1 VALUES (1,10),(1,20),(2,NULL),(2,NULL),(3,50);
   106  select Fld1, variance(Fld2) as q from t1 group by Fld1 having q is not null;
   107  SQL syntax error: column "q" must appear in the GROUP BY clause or be used in an aggregate function
   108  select Fld1, variance(Fld2) from t1 group by Fld1 having variance(Fld2) is not null;
   109  Fld1	variance(Fld2)
   110  1	25.0
   111  3	0.0
   112  select Fld1, variance(Fld2) from t1 group by Fld1 having avg(Fld2) is not null;
   113  Fld1	variance(Fld2)
   114  1	25.0
   115  3	0.0
   116  select Fld1, variance(Fld2) from t1 group by Fld1 having variance(Fld2) is not null;
   117  Fld1	variance(Fld2)
   118  1	25.0
   119  3	0.0
   120  drop table t1;
   121  SELECT variance(1)<variance(2);
   122  variance(1)<variance(2)
   123  false
   124  CREATE TABLE t1(i INT);
   125  INSERT INTO t1 VALUES (NULL),(1);
   126  SELECT variance(i)+0 as splus0, i+0 as plain FROM t1 GROUP BY i ;
   127  splus0	plain
   128  null	null
   129  0.0	1
   130  DROP TABLE t1;