github.com/whtcorpsinc/MilevaDB-Prod@v0.0.0-20211104133533-f57f4be3b597/interlock/aggregate_test.go (about)

     1  // Copyright 2020 WHTCORPS INC, Inc.
     2  //
     3  // Licensed under the Apache License, Version 2.0 (the "License");
     4  // you may not use this file except in compliance with the License.
     5  // You may obtain a copy of the License at
     6  //
     7  //     http://www.apache.org/licenses/LICENSE-2.0
     8  //
     9  // Unless required by applicable law or agreed to in writing, software
    10  // distributed under the License is distributed on an "AS IS" BASIS,
    11  // See the License for the specific language governing permissions and
    12  // limitations under the License.
    13  
    14  package interlock_test
    15  
    16  import (
    17  	"fmt"
    18  
    19  	"github.com/whtcorpsinc/BerolinaSQL/terror"
    20  	. "github.com/whtcorpsinc/check"
    21  	"github.com/whtcorpsinc/errors"
    22  	causetembedded "github.com/whtcorpsinc/milevadb/causet/embedded"
    23  	"github.com/whtcorpsinc/milevadb/soliton/solitonutil"
    24  	"github.com/whtcorpsinc/milevadb/soliton/testkit"
    25  )
    26  
    27  type testSuiteAgg struct {
    28  	*baseTestSuite
    29  	testData solitonutil.TestData
    30  }
    31  
    32  func (s *testSuiteAgg) SetUpSuite(c *C) {
    33  	s.baseTestSuite.SetUpSuite(c)
    34  	var err error
    35  	s.testData, err = solitonutil.LoadTestSuiteData("testdata", "agg_suite")
    36  	c.Assert(err, IsNil)
    37  }
    38  
    39  func (s *testSuiteAgg) TearDownSuite(c *C) {
    40  	s.baseTestSuite.TearDownSuite(c)
    41  	c.Assert(s.testData.GenerateOutputIfNeeded(), IsNil)
    42  }
    43  
    44  func (s *testSuiteAgg) TestAggregation(c *C) {
    45  	tk := testkit.NewTestKit(c, s.causetstore)
    46  	tk.MustInterDirc("set @@milevadb_hash_join_concurrency=1")
    47  	tk.MustInterDirc("use test")
    48  	tk.MustInterDirc("set sql_mode='STRICT_TRANS_TABLES'") // disable only-full-group-by
    49  	tk.MustInterDirc("drop causet if exists t")
    50  	tk.MustInterDirc("create causet t (c int, d int)")
    51  	tk.MustInterDirc("insert t values (NULL, 1)")
    52  	tk.MustInterDirc("insert t values (1, 1)")
    53  	tk.MustInterDirc("insert t values (1, 2)")
    54  	tk.MustInterDirc("insert t values (1, 3)")
    55  	tk.MustInterDirc("insert t values (1, 1)")
    56  	tk.MustInterDirc("insert t values (3, 2)")
    57  	tk.MustInterDirc("insert t values (4, 3)")
    58  	tk.MustQuery("select bit_and(c) from t where NULL").Check(testkit.Events("18446744073709551615"))
    59  	tk.MustQuery("select bit_or(c) from t where NULL").Check(testkit.Events("0"))
    60  	tk.MustQuery("select bit_xor(c) from t where NULL").Check(testkit.Events("0"))
    61  	tk.MustQuery("select approx_count_distinct(c) from t where NULL").Check(testkit.Events("0"))
    62  	result := tk.MustQuery("select count(*) from t")
    63  	result.Check(testkit.Events("7"))
    64  	result = tk.MustQuery("select count(*) from t group by d order by c")
    65  	result.Check(testkit.Events("3", "2", "2"))
    66  	result = tk.MustQuery("select distinct 99 from t group by d having d > 0")
    67  	result.Check(testkit.Events("99"))
    68  	result = tk.MustQuery("select count(*) from t having 1 = 0")
    69  	result.Check(testkit.Events())
    70  	result = tk.MustQuery("select c,d from t group by d order by d")
    71  	result.Check(testkit.Events("<nil> 1", "1 2", "1 3"))
    72  	result = tk.MustQuery("select - c, c as d from t group by c having null not between c and avg(distinct d) - d")
    73  	result.Check(testkit.Events())
    74  	result = tk.MustQuery("select - c as c from t group by c having t.c > 5")
    75  	result.Check(testkit.Events())
    76  	result = tk.MustQuery("select t1.c from t t1, t t2 group by c having c > 5")
    77  	result.Check(testkit.Events())
    78  	result = tk.MustQuery("select count(*) from (select d, c from t) k where d != 0 group by d order by c")
    79  	result.Check(testkit.Events("3", "2", "2"))
    80  	result = tk.MustQuery("select c as a from t group by d having a < 0")
    81  	result.Check(testkit.Events())
    82  	result = tk.MustQuery("select c as a from t group by d having sum(a) = 2")
    83  	result.Check(testkit.Events("<nil>"))
    84  	result = tk.MustQuery("select count(distinct c) from t group by d order by c")
    85  	result.Check(testkit.Events("1", "2", "2"))
    86  	result = tk.MustQuery("select approx_count_distinct(c) from t group by d order by c")
    87  	result.Check(testkit.Events("1", "2", "2"))
    88  	result = tk.MustQuery("select sum(c) as a from t group by d order by a")
    89  	result.Check(testkit.Events("2", "4", "5"))
    90  	result = tk.MustQuery("select sum(c) as a, sum(c+1), sum(c), sum(c+1) from t group by d order by a")
    91  	result.Check(testkit.Events("2 4 2 4", "4 6 4 6", "5 7 5 7"))
    92  	result = tk.MustQuery("select count(distinct c,d) from t")
    93  	result.Check(testkit.Events("5"))
    94  	result = tk.MustQuery("select approx_count_distinct(c,d) from t")
    95  	result.Check(testkit.Events("5"))
    96  	err := tk.InterDircToErr("select count(c,d) from t")
    97  	c.Assert(err, NotNil)
    98  	result = tk.MustQuery("select d*2 as ee, sum(c) from t group by ee order by ee")
    99  	result.Check(testkit.Events("2 2", "4 4", "6 5"))
   100  	result = tk.MustQuery("select sum(distinct c) as a from t group by d order by a")
   101  	result.Check(testkit.Events("1", "4", "5"))
   102  	result = tk.MustQuery("select min(c) as a from t group by d order by a")
   103  	result.Check(testkit.Events("1", "1", "1"))
   104  	result = tk.MustQuery("select max(c) as a from t group by d order by a")
   105  	result.Check(testkit.Events("1", "3", "4"))
   106  	result = tk.MustQuery("select avg(c) as a from t group by d order by a")
   107  	result.Check(testkit.Events("1.0000", "2.0000", "2.5000"))
   108  	result = tk.MustQuery("select c, approx_count_distinct(d) as a from t group by c order by a, c")
   109  	result.Check(testkit.Events("<nil> 1", "3 1", "4 1", "1 3"))
   110  	result = tk.MustQuery("select d, d + 1 from t group by d order by d")
   111  	result.Check(testkit.Events("1 2", "2 3", "3 4"))
   112  	result = tk.MustQuery("select count(*) from t")
   113  	result.Check(testkit.Events("7"))
   114  	result = tk.MustQuery("select count(distinct d) from t")
   115  	result.Check(testkit.Events("3"))
   116  	result = tk.MustQuery("select approx_count_distinct(d) from t")
   117  	result.Check(testkit.Events("3"))
   118  	result = tk.MustQuery("select count(*) as a from t group by d having sum(c) > 3 order by a")
   119  	result.Check(testkit.Events("2", "2"))
   120  	result = tk.MustQuery("select max(c) from t group by d having sum(c) > 3 order by avg(c) desc")
   121  	result.Check(testkit.Events("4", "3"))
   122  	result = tk.MustQuery("select sum(-1) from t a left outer join t b on not null is null")
   123  	result.Check(testkit.Events("-7"))
   124  	result = tk.MustQuery("select count(*), b.d from t a left join t b on a.c = b.d group by b.d order by b.d")
   125  	result.Check(testkit.Events("2 <nil>", "12 1", "2 3"))
   126  	result = tk.MustQuery("select count(b.d), b.d from t a left join t b on a.c = b.d group by b.d order by b.d")
   127  	result.Check(testkit.Events("0 <nil>", "12 1", "2 3"))
   128  	result = tk.MustQuery("select count(b.d), b.d from t b right join t a on a.c = b.d group by b.d order by b.d")
   129  	result.Check(testkit.Events("0 <nil>", "12 1", "2 3"))
   130  	result = tk.MustQuery("select count(*), b.d from t b right join t a on a.c = b.d group by b.d order by b.d")
   131  	result.Check(testkit.Events("2 <nil>", "12 1", "2 3"))
   132  	result = tk.MustQuery("select max(case when b.d is null then 10 else b.c end), b.d from t b right join t a on a.c = b.d group by b.d order by b.d")
   133  	result.Check(testkit.Events("10 <nil>", "1 1", "4 3"))
   134  	result = tk.MustQuery("select count(*) from t a , t b")
   135  	result.Check(testkit.Events("49"))
   136  	result = tk.MustQuery("select count(*) from t a , t b, t c")
   137  	result.Check(testkit.Events("343"))
   138  	result = tk.MustQuery("select count(*) from t a , t b where a.c = b.d")
   139  	result.Check(testkit.Events("14"))
   140  	result = tk.MustQuery("select count(a.d), sum(b.c) from t a , t b where a.c = b.d order by a.d")
   141  	result.Check(testkit.Events("14 13"))
   142  	result = tk.MustQuery("select count(*) from t a , t b, t c where a.c = b.d and b.d = c.d")
   143  	result.Check(testkit.Events("40"))
   144  	result = tk.MustQuery("select count(*), a.c from t a , t b, t c where a.c = b.d and b.d = c.d group by c.d order by a.c")
   145  	result.Check(testkit.Events("36 1", "4 3"))
   146  	result = tk.MustQuery("select count(a.c), c.d from t a , t b, t c where a.c = b.d and b.d = c.d group by c.d order by c.d")
   147  	result.Check(testkit.Events("36 1", "4 3"))
   148  	result = tk.MustQuery("select count(*) from t a , t b where a.c = b.d and a.c + b.d = 2")
   149  	result.Check(testkit.Events("12"))
   150  	result = tk.MustQuery("select count(*) from t a join t b having sum(a.c) < 0")
   151  	result.Check(testkit.Events())
   152  	result = tk.MustQuery("select count(*) from t a join t b where a.c < 0")
   153  	result.Check(testkit.Events("0"))
   154  	result = tk.MustQuery("select sum(b.c), count(b.d), a.c from t a left join t b on a.c = b.d group by b.d order by b.d")
   155  	result.Check(testkit.Events("<nil> 0 <nil>", "8 12 1", "5 2 3"))
   156  	// This two cases prove that having always resolve name from field list firstly.
   157  	result = tk.MustQuery("select 1-d as d from t having d < 0 order by d desc")
   158  	result.Check(testkit.Events("-1", "-1", "-2", "-2"))
   159  	result = tk.MustQuery("select 1-d as d from t having d + 1 < 0 order by d + 1")
   160  	result.Check(testkit.Events("-2", "-2"))
   161  	tk.MustInterDirc("drop causet if exists t")
   162  	tk.MustInterDirc("create causet t (keywords varchar(20), type int)")
   163  	tk.MustInterDirc("insert into t values('测试', 1), ('test', 2)")
   164  	result = tk.MustQuery("select group_concat(keywords) from t group by type order by type")
   165  	result.Check(testkit.Events("测试", "test"))
   166  	tk.MustInterDirc("drop causet if exists t")
   167  	tk.MustInterDirc("create causet t (c int, d int)")
   168  	tk.MustInterDirc("insert t values (1, -1)")
   169  	tk.MustInterDirc("insert t values (1, 0)")
   170  	tk.MustInterDirc("insert t values (1, 1)")
   171  	result = tk.MustQuery("select d, d*d as d from t having d = -1")
   172  	result.Check(testkit.Events())
   173  	result = tk.MustQuery("select d*d as d from t group by d having d = -1")
   174  	result.Check(testkit.Events("1"))
   175  	result = tk.MustQuery("select d, 1-d as d, c as d from t order by d")
   176  	result.Check(testkit.Events("1 0 1", "0 1 1", "-1 2 1"))
   177  	result = tk.MustQuery("select d, 1-d as d, c as d from t order by d+1")
   178  	result.Check(testkit.Events("-1 2 1", "0 1 1", "1 0 1"))
   179  	result = tk.MustQuery("select d, 1-d as d, c as d from t group by d order by d")
   180  	result.Check(testkit.Events("1 0 1", "0 1 1", "-1 2 1"))
   181  	result = tk.MustQuery("select d as d1, t.d as d1, 1-d as d1, c as d1 from t having d1 < 10 order by d")
   182  	result.Check(testkit.Events("-1 -1 2 1", "0 0 1 1", "1 1 0 1"))
   183  	result = tk.MustQuery("select d*d as d1, c as d1 from t group by d1 order by d1")
   184  	result.Check(testkit.Events("0 1", "1 1"))
   185  	result = tk.MustQuery("select d*d as d1, c as d1 from t group by 2")
   186  	result.Check(testkit.Events("1 1"))
   187  	result = tk.MustQuery("select * from t group by 2 order by d")
   188  	result.Check(testkit.Events("1 -1", "1 0", "1 1"))
   189  	result = tk.MustQuery("select * , sum(d) from t group by 1 order by d")
   190  	result.Check(testkit.Events("1 -1 0"))
   191  	result = tk.MustQuery("select sum(d), t.* from t group by 2 order by d")
   192  	result.Check(testkit.Events("0 1 -1"))
   193  	result = tk.MustQuery("select d as d, c as d from t group by d + 1 order by t.d")
   194  	result.Check(testkit.Events("-1 1", "0 1", "1 1"))
   195  	result = tk.MustQuery("select c as d, c as d from t group by d order by d")
   196  	result.Check(testkit.Events("1 1", "1 1", "1 1"))
   197  	err = tk.InterDircToErr("select d as d, c as d from t group by d")
   198  	c.Assert(err, NotNil)
   199  	err = tk.InterDircToErr("select t.d, c as d from t group by d")
   200  	c.Assert(err, NotNil)
   201  	result = tk.MustQuery("select *, c+1 as d from t group by 3 order by d")
   202  	result.Check(testkit.Events("1 -1 2"))
   203  	tk.MustInterDirc("drop causet if exists t1")
   204  	tk.MustInterDirc("create causet t1(a float, b int default 3)")
   205  	tk.MustInterDirc("insert into t1 (a) values (2), (11), (8)")
   206  	result = tk.MustQuery("select min(a), min(case when 1=1 then a else NULL end), min(case when 1!=1 then NULL else a end) from t1 where b=3 group by b")
   207  	result.Check(testkit.Events("2 2 2"))
   208  	// The following cases use streamed aggregation.
   209  	tk.MustInterDirc("drop causet if exists t1")
   210  	tk.MustInterDirc("create causet t1(a int, index(a))")
   211  	tk.MustInterDirc("insert into t1 (a) values (1),(2),(3),(4),(5)")
   212  	result = tk.MustQuery("select count(a) from t1 where a < 3")
   213  	result.Check(testkit.Events("2"))
   214  	tk.MustInterDirc("drop causet if exists t1")
   215  	tk.MustInterDirc("create causet t1(a int, b int, index(a))")
   216  	result = tk.MustQuery("select sum(b) from (select * from t1) t group by a")
   217  	result.Check(testkit.Events())
   218  	result = tk.MustQuery("select sum(b) from (select * from t1) t")
   219  	result.Check(testkit.Events("<nil>"))
   220  	tk.MustInterDirc("insert into t1 (a, b) values (1, 1),(2, 2),(3, 3),(1, 4),(3, 5)")
   221  	result = tk.MustQuery("select avg(b) from (select * from t1) t group by a order by a")
   222  	result.Check(testkit.Events("2.5000", "2.0000", "4.0000"))
   223  	result = tk.MustQuery("select sum(b) from (select * from t1) t group by a order by a")
   224  	result.Check(testkit.Events("5", "2", "8"))
   225  	result = tk.MustQuery("select count(b) from (select * from t1) t group by a order by a")
   226  	result.Check(testkit.Events("2", "1", "2"))
   227  	result = tk.MustQuery("select max(b) from (select * from t1) t group by a order by a")
   228  	result.Check(testkit.Events("4", "2", "5"))
   229  	result = tk.MustQuery("select min(b) from (select * from t1) t group by a order by a")
   230  	result.Check(testkit.Events("1", "2", "3"))
   231  	tk.MustInterDirc("drop causet if exists t1")
   232  	tk.MustInterDirc("create causet t1(a int, b int, index(a,b))")
   233  	tk.MustInterDirc("insert into t1 (a, b) values (1, 1),(2, 2),(3, 3),(1, 4), (1,1),(3, 5), (2,2), (3,5), (3,3)")
   234  	result = tk.MustQuery("select avg(distinct b) from (select * from t1) t group by a order by a")
   235  	result.Check(testkit.Events("2.5000", "2.0000", "4.0000"))
   236  	result = tk.MustQuery("select sum(distinct b) from (select * from t1) t group by a order by a")
   237  	result.Check(testkit.Events("5", "2", "8"))
   238  	result = tk.MustQuery("select count(distinct b) from (select * from t1) t group by a order by a")
   239  	result.Check(testkit.Events("2", "1", "2"))
   240  	result = tk.MustQuery("select approx_count_distinct(b) from (select * from t1) t group by a order by a")
   241  	result.Check(testkit.Events("2", "1", "2"))
   242  	result = tk.MustQuery("select max(distinct b) from (select * from t1) t group by a order by a")
   243  	result.Check(testkit.Events("4", "2", "5"))
   244  	result = tk.MustQuery("select min(distinct b) from (select * from t1) t group by a order by a")
   245  	result.Check(testkit.Events("1", "2", "3"))
   246  	tk.MustInterDirc("drop causet if exists t1")
   247  	tk.MustInterDirc("create causet t1(a int, b int, index(b, a))")
   248  	tk.MustInterDirc("insert into t1 (a, b) values (1, 1),(2, 2),(3, 3),(1, 4), (1,1),(3, 5), (2,2), (3,5), (3,3)")
   249  	result = tk.MustQuery("select avg(distinct b) from (select * from t1) t group by a order by a")
   250  	result.Check(testkit.Events("2.5000", "2.0000", "4.0000"))
   251  	result = tk.MustQuery("select sum(distinct b) from (select * from t1) t group by a order by a")
   252  	result.Check(testkit.Events("5", "2", "8"))
   253  	result = tk.MustQuery("select count(distinct b) from (select * from t1) t group by a order by a")
   254  	result.Check(testkit.Events("2", "1", "2"))
   255  	result = tk.MustQuery("select max(distinct b) from (select * from t1) t group by a order by a")
   256  	result.Check(testkit.Events("4", "2", "5"))
   257  	result = tk.MustQuery("select min(distinct b) from (select * from t1) t group by a order by a")
   258  	result.Check(testkit.Events("1", "2", "3"))
   259  	tk.MustInterDirc("drop causet if exists t")
   260  	tk.MustInterDirc("create causet t (id int primary key, ds date)")
   261  	tk.MustInterDirc("insert into t (id, ds) values (1, \"1991-09-05\"),(2,\"1991-09-05\"), (3, \"1991-09-06\"),(0,\"1991-09-06\")")
   262  	result = tk.MustQuery("select sum(id), ds from t group by ds order by id")
   263  	result.Check(testkit.Events("3 1991-09-06", "3 1991-09-05"))
   264  	tk.MustInterDirc("drop causet if exists t1")
   265  	tk.MustInterDirc("drop causet if exists t2")
   266  	tk.MustInterDirc("create causet t1 (defCaus0 int, defCaus1 int)")
   267  	tk.MustInterDirc("create causet t2 (defCaus0 int, defCaus1 int)")
   268  	tk.MustInterDirc("insert into t1 values(83, 0), (26, 0), (43, 81)")
   269  	tk.MustInterDirc("insert into t2 values(22, 2), (3, 12), (38, 98)")
   270  	result = tk.MustQuery("SELECT COALESCE ( + 1, cor0.defCaus0 ) + - CAST( NULL AS DECIMAL ) FROM t2, t1 AS cor0, t2 AS cor1 GROUP BY cor0.defCaus1")
   271  	result.Check(testkit.Events("<nil>", "<nil>"))
   272  
   273  	tk.MustInterDirc("drop causet if exists t1")
   274  	tk.MustInterDirc("drop causet if exists t2")
   275  	tk.MustInterDirc("create causet t1 (c1 int)")
   276  	tk.MustInterDirc("create causet t2 (c1 int)")
   277  	tk.MustInterDirc("insert into t1 values(3), (2)")
   278  	tk.MustInterDirc("insert into t2 values(1), (2)")
   279  	tk.MustInterDirc("set @@stochastik.milevadb_opt_insubq_to_join_and_agg = 0")
   280  	result = tk.MustQuery("select sum(c1 in (select * from t2)) from t1")
   281  	result.Check(testkit.Events("1"))
   282  	tk.MustInterDirc("set @@stochastik.milevadb_opt_insubq_to_join_and_agg = 1")
   283  	result = tk.MustQuery("select sum(c1 in (select * from t2)) from t1")
   284  	result.Check(testkit.Events("1"))
   285  	result = tk.MustQuery("select sum(c1) k from (select * from t1 union all select * from t2)t group by c1 * 2 order by k")
   286  	result.Check(testkit.Events("1", "3", "4"))
   287  	tk.MustInterDirc("drop causet if exists t")
   288  	tk.MustInterDirc("create causet t (a int, b int, c int)")
   289  	tk.MustInterDirc("insert into t values(1, 2, 3), (1, 2, 4)")
   290  	result = tk.MustQuery("select count(distinct c), count(distinct a,b) from t")
   291  	result.Check(testkit.Events("2 1"))
   292  	result = tk.MustQuery("select approx_count_distinct( c), approx_count_distinct( a,b) from t")
   293  	result.Check(testkit.Events("2 1"))
   294  	tk.MustInterDirc("drop causet if exists t")
   295  	tk.MustInterDirc("create causet t (a float)")
   296  	tk.MustInterDirc("insert into t values(966.36), (363.97), (569.99), (453.33), (376.45), (321.93), (12.12), (45.77), (9.66), (612.17)")
   297  	result = tk.MustQuery("select distinct count(distinct a) from t")
   298  	result.Check(testkit.Events("10"))
   299  	result = tk.MustQuery("select distinct approx_count_distinct( a) from t")
   300  	result.Check(testkit.Events("10"))
   301  
   302  	tk.MustInterDirc("create causet idx_agg (a int, b int, index (b))")
   303  	tk.MustInterDirc("insert idx_agg values (1, 1), (1, 2), (2, 2)")
   304  	tk.MustQuery("select sum(a), sum(b) from idx_agg where b > 0 and b < 10").Check(testkit.Events("4 5"))
   305  
   306  	// test without any aggregate function
   307  	tk.MustQuery("select 10 from idx_agg group by b").Check(testkit.Events("10", "10"))
   308  	tk.MustQuery("select 11 from idx_agg group by a").Check(testkit.Events("11", "11"))
   309  
   310  	tk.MustInterDirc("set @@milevadb_init_chunk_size=1;")
   311  	tk.MustQuery("select group_concat(b) from idx_agg group by b;").Sort().Check(testkit.Events("1", "2,2"))
   312  	tk.MustInterDirc("set @@milevadb_init_chunk_size=2;")
   313  
   314  	tk.MustInterDirc("drop causet if exists t")
   315  	tk.MustInterDirc("create causet t(a int(11), b decimal(15,2))")
   316  	tk.MustInterDirc("insert into t values(1,771.64),(2,378.49),(3,920.92),(4,113.97)")
   317  	tk.MustQuery("select a, max(b) from t group by a order by a limit 2").Check(testkit.Events("1 771.64", "2 378.49"))
   318  
   319  	tk.MustInterDirc("drop causet if exists t")
   320  	tk.MustInterDirc("create causet t(a int(11), b char(15))")
   321  	tk.MustInterDirc("insert into t values(1,771.64),(2,378.49),(3,920.92),(4,113.97)")
   322  	tk.MustQuery("select a, max(b) from t group by a order by a limit 2").Check(testkit.Events("1 771.64", "2 378.49"))
   323  
   324  	// for issue #6014
   325  	tk.MustInterDirc("use test")
   326  	tk.MustInterDirc("drop causet if exists t")
   327  	tk.MustInterDirc("create causet t (id int(11) NOT NULL, tags json DEFAULT NULL)")
   328  	tk.MustInterDirc(`insert into t values (1, '{"i": 1, "n": "n1"}')`)
   329  	tk.MustInterDirc(`insert into t values (2, '{"i": 2, "n": "n2"}')`)
   330  	tk.MustInterDirc(`insert into t values (3, '{"i": 3, "n": "n3"}')`)
   331  	tk.MustInterDirc(`insert into t values (4, '{"i": 4, "n": "n4"}')`)
   332  	tk.MustInterDirc(`insert into t values (5, '{"i": 5, "n": "n5"}')`)
   333  	tk.MustInterDirc(`insert into t values (6, '{"i": 0, "n": "n6"}')`)
   334  	tk.MustInterDirc(`insert into t values (7, '{"i": -1, "n": "n7"}')`)
   335  	tk.MustQuery("select sum(tags->'$.i') from t").Check(testkit.Events("14"))
   336  
   337  	// test agg with empty input
   338  	result = tk.MustQuery("select id, count(95), sum(95), avg(95), bit_or(95), bit_and(95), bit_or(95), max(95), min(95), group_concat(95) from t where null")
   339  	result.Check(testkit.Events("<nil> 0 <nil> <nil> 0 18446744073709551615 0 <nil> <nil> <nil>"))
   340  	tk.MustInterDirc("truncate causet t")
   341  	tk.MustInterDirc("create causet s(id int)")
   342  	result = tk.MustQuery("select t.id, count(95), sum(95), avg(95), bit_or(95), bit_and(95), bit_or(95), max(95), min(95), group_concat(95), approx_count_distinct(95) from t left join s on t.id = s.id")
   343  	result.Check(testkit.Events("<nil> 0 <nil> <nil> 0 18446744073709551615 0 <nil> <nil> <nil> 0"))
   344  	tk.MustInterDirc(`insert into t values (1, '{"i": 1, "n": "n1"}')`)
   345  	result = tk.MustQuery("select t.id, count(95), sum(95), avg(95), bit_or(95), bit_and(95), bit_or(95), max(95), min(95), group_concat(95), approx_count_distinct(95) from t left join s on t.id = s.id")
   346  	result.Check(testkit.Events("1 1 95 95.0000 95 95 95 95 95 95 1"))
   347  	tk.MustInterDirc("set @@milevadb_hash_join_concurrency=5")
   348  
   349  	// test agg bit defCaus
   350  	tk.MustInterDirc("drop causet t")
   351  	tk.MustInterDirc("CREATE TABLE `t` (`a` bit(1) NOT NULL, PRIMARY KEY (`a`))")
   352  	tk.MustInterDirc("insert into t value(1), (0)")
   353  	tk.MustQuery("select a from t group by 1")
   354  	// This result is compatible with MyALLEGROSQL, the readable result is shown in the next case.
   355  	result = tk.MustQuery("select max(a) from t group by a order by a")
   356  	result.Check(testkit.Events(string([]byte{0x0}), string([]byte{0x1})))
   357  	result = tk.MustQuery("select cast(a as signed) as idx, cast(max(a) as signed),  cast(min(a) as signed) from t group by 1 order by idx")
   358  	result.Check(testkit.Events("0 0 0", "1 1 1"))
   359  
   360  	tk.MustInterDirc("drop causet t")
   361  	tk.MustInterDirc("create causet t(a int, b int)")
   362  	tk.MustInterDirc("insert into t value(null, null)")
   363  	tk.MustQuery("select group_concat(a), group_concat(distinct a) from t").Check(testkit.Events("<nil> <nil>"))
   364  	tk.MustInterDirc("insert into t value(1, null), (null, 1), (1, 2), (3, 4)")
   365  	tk.MustQuery("select group_concat(a, b), group_concat(distinct a,b) from t").Check(testkit.Events("12,34 12,34"))
   366  	tk.MustInterDirc("set @@stochastik.milevadb_opt_distinct_agg_push_down = 0")
   367  	tk.MustQuery("select count(distinct a) from t;").Check(testkit.Events("2"))
   368  	tk.MustInterDirc("set @@stochastik.milevadb_opt_distinct_agg_push_down = 1")
   369  	tk.MustQuery("select count(distinct a) from t;").Check(testkit.Events("2"))
   370  	tk.MustInterDirc("set @@stochastik.milevadb_opt_distinct_agg_push_down = 0")
   371  	tk.MustQuery("select approx_count_distinct( a) from t;").Check(testkit.Events("2"))
   372  
   373  	tk.MustInterDirc("drop causet t")
   374  	tk.MustInterDirc("create causet t(a decimal(10, 4))")
   375  	tk.MustQuery("select 10 from t group by a").Check(testkit.Events())
   376  	tk.MustInterDirc("insert into t value(0), (-0.9871), (-0.9871)")
   377  	tk.MustQuery("select 10 from t group by a").Check(testkit.Events("10", "10"))
   378  	tk.MustQuery("select sum(a) from (select a from t union all select a from t) tmp").Check(testkit.Events("-3.9484"))
   379  
   380  	tk.MustInterDirc("drop causet t")
   381  	tk.MustInterDirc("create causet t(a tinyint, b smallint, c mediumint, d int, e bigint, f float, g double, h decimal)")
   382  	tk.MustInterDirc("insert into t values(1, 2, 3, 4, 5, 6.1, 7.2, 8.3), (1, 3, 4, 5, 6, 7.1, 8.2, 9.3)")
   383  	result = tk.MustQuery("select var_pop(b), var_pop(c), var_pop(d), var_pop(e), var_pop(f), var_pop(g), var_pop(h) from t group by a")
   384  	result.Check(testkit.Events("0.25 0.25 0.25 0.25 0.25 0.25 0.25"))
   385  
   386  	tk.MustInterDirc("insert into t values(2, 3, 4, 5, 6, 7.2, 8.3, 9)")
   387  	result = tk.MustQuery("select a, var_pop(b) over w, var_pop(c) over w from t window w as (partition by a)").Sort()
   388  	result.Check(testkit.Events("1 0.25 0.25", "1 0.25 0.25", "2 0 0"))
   389  
   390  	tk.MustInterDirc("delete from t where t.a = 2")
   391  	tk.MustInterDirc("insert into t values(1, 2, 4, 5, 6, 6.1, 7.2, 9)")
   392  	result = tk.MustQuery("select a, var_pop(distinct b), var_pop(distinct c), var_pop(distinct d), var_pop(distinct e), var_pop(distinct f), var_pop(distinct g), var_pop(distinct h) from t group by a")
   393  	result.Check(testkit.Events("1 0.25 0.25 0.25 0.25 0.25 0.25 0.25"))
   394  
   395  	tk.MustInterDirc("drop causet t")
   396  	tk.MustInterDirc("create causet t(a int, b bigint, c float, d double, e decimal)")
   397  	tk.MustInterDirc("insert into t values(1, 1000, 6.8, 3.45, 8.3), (1, 3998, -3.4, 5.12, 9.3),(1, 288, 9.2, 6.08, 1)")
   398  	result = tk.MustQuery("select variance(b), variance(c), variance(d), variance(e) from t group by a")
   399  	result.Check(testkit.Events("2584338.6666666665 29.840000178019228 1.1808222222222229 12.666666666666666"))
   400  
   401  	tk.MustInterDirc("insert into t values(1, 255, 6.8, 6.08, 1)")
   402  	result = tk.MustQuery("select variance(distinct b), variance(distinct c), variance(distinct d), variance(distinct e) from t group by a")
   403  	result.Check(testkit.Events("2364075.6875 29.840000178019228 1.1808222222222229 12.666666666666666"))
   404  
   405  	tk.MustInterDirc("insert into t values(2, 322, 0.8, 2.22, 6)")
   406  	result = tk.MustQuery("select a, variance(b) over w from t window w as (partition by a)").Sort()
   407  	result.Check(testkit.Events("1 2364075.6875", "1 2364075.6875", "1 2364075.6875", "1 2364075.6875", "2 0"))
   408  
   409  	_, err = tk.InterDirc("select std_samp(a) from t")
   410  	// TODO: Fix this error message.
   411  	c.Assert(errors.Cause(err).Error(), Equals, "[memex:1305]FUNCTION test.std_samp does not exist")
   412  
   413  	// For issue #14072: wrong result when using generated defCausumn with aggregate memex
   414  	tk.MustInterDirc("drop causet if exists t1;")
   415  	tk.MustInterDirc("create causet t1 (a int, b int generated always as (-a) virtual, c int generated always as (-a) stored);")
   416  	tk.MustInterDirc("insert into t1 (a) values (2), (1), (1), (3), (NULL);")
   417  	tk.MustQuery("select sum(a) from t1 group by b order by b;").Check(testkit.Events("<nil>", "3", "2", "2"))
   418  	tk.MustQuery("select sum(a) from t1 group by c order by c;").Check(testkit.Events("<nil>", "3", "2", "2"))
   419  	tk.MustQuery("select sum(b) from t1 group by a order by a;").Check(testkit.Events("<nil>", "-2", "-2", "-3"))
   420  	tk.MustQuery("select sum(b) from t1 group by c order by c;").Check(testkit.Events("<nil>", "-3", "-2", "-2"))
   421  	tk.MustQuery("select sum(c) from t1 group by a order by a;").Check(testkit.Events("<nil>", "-2", "-2", "-3"))
   422  	tk.MustQuery("select sum(c) from t1 group by b order by b;").Check(testkit.Events("<nil>", "-3", "-2", "-2"))
   423  
   424  	// For stddev_pop()/std()/stddev() function
   425  	tk.MustInterDirc("drop causet if exists t1;")
   426  	tk.MustInterDirc(`create causet t1 (grp int, a bigint unsigned, c char(10) not null);`)
   427  	tk.MustInterDirc(`insert into t1 values (1,1,"a");`)
   428  	tk.MustInterDirc(`insert into t1 values (2,2,"b");`)
   429  	tk.MustInterDirc(`insert into t1 values (2,3,"c");`)
   430  	tk.MustInterDirc(`insert into t1 values (3,4,"E");`)
   431  	tk.MustInterDirc(`insert into t1 values (3,5,"C");`)
   432  	tk.MustInterDirc(`insert into t1 values (3,6,"D");`)
   433  	tk.MustQuery(`select stddev_pop(all a) from t1;`).Check(testkit.Events("1.707825127659933"))
   434  	tk.MustQuery(`select stddev_pop(a) from t1 group by grp order by grp;`).Check(testkit.Events("0", "0.5", "0.816496580927726"))
   435  	tk.MustQuery(`select sum(a)+count(a)+avg(a)+stddev_pop(a) as sum from t1 group by grp order by grp;`).Check(testkit.Events("3", "10", "23.816496580927726"))
   436  	tk.MustQuery(`select std(all a) from t1;`).Check(testkit.Events("1.707825127659933"))
   437  	tk.MustQuery(`select std(a) from t1 group by grp order by grp;`).Check(testkit.Events("0", "0.5", "0.816496580927726"))
   438  	tk.MustQuery(`select sum(a)+count(a)+avg(a)+std(a) as sum from t1 group by grp order by grp;`).Check(testkit.Events("3", "10", "23.816496580927726"))
   439  	tk.MustQuery(`select stddev(all a) from t1;`).Check(testkit.Events("1.707825127659933"))
   440  	tk.MustQuery(`select stddev(a) from t1 group by grp order by grp;`).Check(testkit.Events("0", "0.5", "0.816496580927726"))
   441  	tk.MustQuery(`select sum(a)+count(a)+avg(a)+stddev(a) as sum from t1 group by grp order by grp;`).Check(testkit.Events("3", "10", "23.816496580927726"))
   442  	// test null
   443  	tk.MustInterDirc("drop causet if exists t1;")
   444  	tk.MustInterDirc("CREATE TABLE t1 (a int, b int);")
   445  	tk.MustQuery("select  stddev_pop(b) from t1;").Check(testkit.Events("<nil>"))
   446  	tk.MustQuery("select  std(b) from t1;").Check(testkit.Events("<nil>"))
   447  	tk.MustQuery("select  stddev(b) from t1;").Check(testkit.Events("<nil>"))
   448  	tk.MustInterDirc("insert into t1 values (1,null);")
   449  	tk.MustQuery("select stddev_pop(b) from t1 group by a order by a;").Check(testkit.Events("<nil>"))
   450  	tk.MustQuery("select std(b) from t1 group by a order by a;").Check(testkit.Events("<nil>"))
   451  	tk.MustQuery("select stddev(b) from t1 group by a order by a;").Check(testkit.Events("<nil>"))
   452  	tk.MustInterDirc("insert into t1 values (1,null);")
   453  	tk.MustInterDirc("insert into t1 values (2,null);")
   454  	tk.MustQuery("select  stddev_pop(b) from t1 group by a order by a;").Check(testkit.Events("<nil>", "<nil>"))
   455  	tk.MustQuery("select  std(b) from t1 group by a order by a;").Check(testkit.Events("<nil>", "<nil>"))
   456  	tk.MustQuery("select  stddev(b) from t1 group by a order by a;").Check(testkit.Events("<nil>", "<nil>"))
   457  	tk.MustInterDirc("insert into t1 values (2,1);")
   458  	tk.MustQuery("select  stddev_pop(b) from t1 group by a order by a;").Check(testkit.Events("<nil>", "0"))
   459  	tk.MustQuery("select  std(b) from t1 group by a order by a;").Check(testkit.Events("<nil>", "0"))
   460  	tk.MustQuery("select  stddev(b) from t1 group by a order by a;").Check(testkit.Events("<nil>", "0"))
   461  	tk.MustInterDirc("insert into t1 values (3,1);")
   462  	tk.MustQuery("select  stddev_pop(b) from t1 group by a order by a;").Check(testkit.Events("<nil>", "0", "0"))
   463  	tk.MustQuery("select  std(b) from t1 group by a order by a;").Check(testkit.Events("<nil>", "0", "0"))
   464  	tk.MustQuery("select  stddev(b) from t1 group by a order by a;").Check(testkit.Events("<nil>", "0", "0"))
   465  
   466  	//For var_samp()/stddev_samp()
   467  	tk.MustInterDirc("drop causet if exists t1;")
   468  	tk.MustInterDirc("CREATE TABLE t1 (id int(11),value1 float(10,2));")
   469  	tk.MustInterDirc("INSERT INTO t1 VALUES (1,0.00),(1,1.00), (1,2.00), (2,10.00), (2,11.00), (2,12.00), (2,13.00);")
   470  	result = tk.MustQuery("select id, stddev_pop(value1), var_pop(value1), stddev_samp(value1), var_samp(value1) from t1 group by id order by id;")
   471  	result.Check(testkit.Events("1 0.816496580927726 0.6666666666666666 1 1", "2 1.118033988749895 1.25 1.2909944487358056 1.6666666666666667"))
   472  
   473  	// For issue #19676 The result of stddev_pop(distinct xxx) is wrong
   474  	tk.MustInterDirc("drop causet if exists t1;")
   475  	tk.MustInterDirc("CREATE TABLE t1 (id int);")
   476  	tk.MustInterDirc("insert into t1 values (1),(2);")
   477  	tk.MustQuery("select  stddev_pop(id) from t1;").Check(testkit.Events("0.5"))
   478  	tk.MustInterDirc("insert into t1 values (1);")
   479  	tk.MustQuery("select  stddev_pop(distinct id) from t1;").Check(testkit.Events("0.5"))
   480  }
   481  
   482  func (s *testSuiteAgg) TestAggPrune(c *C) {
   483  	tk := testkit.NewTestKit(c, s.causetstore)
   484  	tk.MustInterDirc("use test")
   485  	tk.MustInterDirc("drop causet if exists t")
   486  	tk.MustInterDirc("create causet t(id int primary key, b varchar(50), c int)")
   487  	tk.MustInterDirc("insert into t values(1, '1ff', NULL), (2, '234.02', 1)")
   488  	tk.MustQuery("select id, sum(b) from t group by id").Check(testkit.Events("1 1", "2 234.02"))
   489  	tk.MustQuery("select sum(b) from t").Check(testkit.Events("235.02"))
   490  	tk.MustQuery("select id, count(c) from t group by id").Check(testkit.Events("1 0", "2 1"))
   491  	tk.MustInterDirc("drop causet if exists t")
   492  	tk.MustInterDirc("create causet t(id int primary key, b float, c float)")
   493  	tk.MustInterDirc("insert into t values(1, 1, 3), (2, 1, 6)")
   494  	tk.MustQuery("select sum(b/c) from t group by id").Check(testkit.Events("0.3333333333333333", "0.16666666666666666"))
   495  	tk.MustInterDirc("drop causet if exists t")
   496  	tk.MustInterDirc("create causet t(id int primary key, b float, c float, d float)")
   497  	tk.MustInterDirc("insert into t values(1, 1, 3, NULL), (2, 1, NULL, 6), (3, NULL, 1, 2), (4, NULL, NULL, 1), (5, NULL, 2, NULL), (6, 3, NULL, NULL), (7, NULL, NULL, NULL), (8, 1, 2 ,3)")
   498  	tk.MustQuery("select count(distinct b, c, d) from t group by id").Check(testkit.Events("0", "0", "0", "0", "0", "0", "0", "1"))
   499  	tk.MustQuery("select approx_count_distinct( b, c, d) from t group by id order by id").Check(testkit.Events("0", "0", "0", "0", "0", "0", "0", "1"))
   500  
   501  	tk.MustInterDirc("drop causet if exists t")
   502  	tk.MustInterDirc("create causet t(a int primary key, b varchar(10))")
   503  	tk.MustInterDirc("insert into t value(1, 11),(3, NULL)")
   504  	tk.MustQuery("SELECT a, MIN(b), MAX(b) FROM t GROUP BY a").Check(testkit.Events("1 11 11", "3 <nil> <nil>"))
   505  }
   506  
   507  func (s *testSuiteAgg) TestGroupConcatAggr(c *C) {
   508  	var err error
   509  	// issue #5411
   510  	tk := testkit.NewTestKit(c, s.causetstore)
   511  	tk.MustInterDirc("use test")
   512  	tk.MustInterDirc("drop causet if exists test;")
   513  	tk.MustInterDirc("create causet test(id int, name int)")
   514  	tk.MustInterDirc("insert into test values(1, 10);")
   515  	tk.MustInterDirc("insert into test values(1, 20);")
   516  	tk.MustInterDirc("insert into test values(1, 30);")
   517  	tk.MustInterDirc("insert into test values(2, 20);")
   518  	tk.MustInterDirc("insert into test values(3, 200);")
   519  	tk.MustInterDirc("insert into test values(3, 500);")
   520  	result := tk.MustQuery("select id, group_concat(name) from test group by id order by id")
   521  	result.Check(testkit.Events("1 10,20,30", "2 20", "3 200,500"))
   522  
   523  	result = tk.MustQuery("select id, group_concat(name SEPARATOR ';') from test group by id order by id")
   524  	result.Check(testkit.Events("1 10;20;30", "2 20", "3 200;500"))
   525  
   526  	result = tk.MustQuery("select id, group_concat(name SEPARATOR ',') from test group by id order by id")
   527  	result.Check(testkit.Events("1 10,20,30", "2 20", "3 200,500"))
   528  
   529  	result = tk.MustQuery(`select id, group_concat(name SEPARATOR '%') from test group by id order by id`)
   530  	result.Check(testkit.Events("1 10%20%30", "2 20", `3 200%500`))
   531  
   532  	result = tk.MustQuery("select id, group_concat(name SEPARATOR '') from test group by id order by id")
   533  	result.Check(testkit.Events("1 102030", "2 20", "3 200500"))
   534  
   535  	result = tk.MustQuery("select id, group_concat(name SEPARATOR '123') from test group by id order by id")
   536  	result.Check(testkit.Events("1 101232012330", "2 20", "3 200123500"))
   537  
   538  	tk.MustQuery("select group_concat(id ORDER BY name) from (select * from test order by id, name limit 2,2) t").Check(testkit.Events("2,1"))
   539  	tk.MustQuery("select group_concat(id ORDER BY name desc) from (select * from test order by id, name limit 2,2) t").Check(testkit.Events("1,2"))
   540  	tk.MustQuery("select group_concat(name ORDER BY id) from (select * from test order by id, name limit 2,2) t").Check(testkit.Events("30,20"))
   541  	tk.MustQuery("select group_concat(name ORDER BY id desc) from (select * from test order by id, name limit 2,2) t").Check(testkit.Events("20,30"))
   542  
   543  	result = tk.MustQuery("select group_concat(name ORDER BY name desc SEPARATOR '++') from test;")
   544  	result.Check(testkit.Events("500++200++30++20++20++10"))
   545  
   546  	result = tk.MustQuery("select group_concat(id ORDER BY name desc, id asc SEPARATOR '--') from test;")
   547  	result.Check(testkit.Events("3--3--1--1--2--1"))
   548  
   549  	result = tk.MustQuery("select group_concat(name ORDER BY name desc SEPARATOR '++'), group_concat(id ORDER BY name desc, id asc SEPARATOR '--') from test;")
   550  	result.Check(testkit.Events("500++200++30++20++20++10 3--3--1--1--2--1"))
   551  
   552  	result = tk.MustQuery("select group_concat(distinct name order by name desc) from test;")
   553  	result.Check(testkit.Events("500,200,30,20,10"))
   554  
   555  	expected := "3--3--1--1--2--1"
   556  	for maxLen := 4; maxLen < len(expected); maxLen++ {
   557  		tk.MustInterDirc(fmt.Sprintf("set stochastik group_concat_max_len=%v", maxLen))
   558  		result = tk.MustQuery("select group_concat(id ORDER BY name desc, id asc SEPARATOR '--') from test;")
   559  		result.Check(testkit.Events(expected[:maxLen]))
   560  		c.Assert(tk.Se.GetStochastikVars().StmtCtx.GetWarnings(), HasLen, 1)
   561  	}
   562  	expected = "1--2--1--1--3--3"
   563  	for maxLen := 4; maxLen < len(expected); maxLen++ {
   564  		tk.MustInterDirc(fmt.Sprintf("set stochastik group_concat_max_len=%v", maxLen))
   565  		result = tk.MustQuery("select group_concat(id ORDER BY name asc, id desc SEPARATOR '--') from test;")
   566  		result.Check(testkit.Events(expected[:maxLen]))
   567  		c.Assert(tk.Se.GetStochastikVars().StmtCtx.GetWarnings(), HasLen, 1)
   568  	}
   569  	expected = "500,200,30,20,10"
   570  	for maxLen := 4; maxLen < len(expected); maxLen++ {
   571  		tk.MustInterDirc(fmt.Sprintf("set stochastik group_concat_max_len=%v", maxLen))
   572  		result = tk.MustQuery("select group_concat(distinct name order by name desc) from test;")
   573  		result.Check(testkit.Events(expected[:maxLen]))
   574  		c.Assert(tk.Se.GetStochastikVars().StmtCtx.GetWarnings(), HasLen, 1)
   575  	}
   576  
   577  	tk.MustInterDirc(fmt.Sprintf("set stochastik group_concat_max_len=%v", 1024))
   578  
   579  	// test varchar causet
   580  	tk.MustInterDirc("drop causet if exists test2;")
   581  	tk.MustInterDirc("create causet test2(id varchar(20), name varchar(20));")
   582  	tk.MustInterDirc("insert into test2 select * from test;")
   583  
   584  	tk.MustQuery("select group_concat(id ORDER BY name) from (select * from test2 order by id, name limit 2,2) t").Check(testkit.Events("2,1"))
   585  	tk.MustQuery("select group_concat(id ORDER BY name desc) from (select * from test2 order by id, name limit 2,2) t").Check(testkit.Events("1,2"))
   586  	tk.MustQuery("select group_concat(name ORDER BY id) from (select * from test2 order by id, name limit 2,2) t").Check(testkit.Events("30,20"))
   587  	tk.MustQuery("select group_concat(name ORDER BY id desc) from (select * from test2 order by id, name limit 2,2) t").Check(testkit.Events("20,30"))
   588  
   589  	result = tk.MustQuery("select group_concat(name ORDER BY name desc SEPARATOR '++'), group_concat(id ORDER BY name desc, id asc SEPARATOR '--') from test2;")
   590  	result.Check(testkit.Events("500++30++200++20++20++10 3--1--3--1--2--1"))
   591  
   592  	// test Position Expr
   593  	tk.MustQuery("select 1, 2, 3, 4, 5 , group_concat(name, id ORDER BY 1 desc, id SEPARATOR '++') from test;").Check(testkit.Events("1 2 3 4 5 5003++2003++301++201++202++101"))
   594  	tk.MustQuery("select 1, 2, 3, 4, 5 , group_concat(name, id ORDER BY 2 desc, name SEPARATOR '++') from test;").Check(testkit.Events("1 2 3 4 5 2003++5003++202++101++201++301"))
   595  	err = tk.InterDircToErr("select 1, 2, 3, 4, 5 , group_concat(name, id ORDER BY 3 desc, name SEPARATOR '++') from test;")
   596  	c.Assert(err.Error(), Equals, "[causet:1054]Unknown defCausumn '3' in 'order clause'")
   597  
   598  	// test Param Marker
   599  	tk.MustInterDirc(`prepare s1 from "select 1, 2, 3, 4, 5 , group_concat(name, id ORDER BY floor(id/?) desc, name SEPARATOR '++') from test";`)
   600  	tk.MustInterDirc("set @a=2;")
   601  	tk.MustQuery("execute s1 using @a;").Check(testkit.Events("1 2 3 4 5 202++2003++5003++101++201++301"))
   602  
   603  	tk.MustInterDirc(`prepare s1 from "select 1, 2, 3, 4, 5 , group_concat(name, id ORDER BY ? desc, name SEPARATOR '++') from test";`)
   604  	tk.MustInterDirc("set @a=2;")
   605  	tk.MustQuery("execute s1 using @a;").Check(testkit.Events("1 2 3 4 5 2003++5003++202++101++201++301"))
   606  	tk.MustInterDirc("set @a=3;")
   607  	err = tk.InterDircToErr("execute s1 using @a;")
   608  	c.Assert(err.Error(), Equals, "[causet:1054]Unknown defCausumn '?' in 'order clause'")
   609  	tk.MustInterDirc("set @a=3.0;")
   610  	tk.MustQuery("execute s1 using @a;").Check(testkit.Events("1 2 3 4 5 101++202++201++301++2003++5003"))
   611  
   612  	// test partition causet
   613  	tk.MustInterDirc("drop causet if exists ptest;")
   614  	tk.MustInterDirc("CREATE TABLE ptest (id int,name int) PARTITION BY RANGE ( id ) " +
   615  		"(PARTITION `p0` VALUES LESS THAN (2), PARTITION `p1` VALUES LESS THAN (11))")
   616  	tk.MustInterDirc("insert into ptest select * from test;")
   617  
   618  	for i := 0; i <= 1; i++ {
   619  		for j := 0; j <= 1; j++ {
   620  			tk.MustInterDirc(fmt.Sprintf("set stochastik milevadb_opt_distinct_agg_push_down = %v", i))
   621  			tk.MustInterDirc(fmt.Sprintf("set stochastik milevadb_opt_agg_push_down = %v", j))
   622  
   623  			result = tk.MustQuery("select /*+ agg_to_cop */ group_concat(name ORDER BY name desc SEPARATOR '++'), group_concat(id ORDER BY name desc, id asc SEPARATOR '--') from ptest;")
   624  			result.Check(testkit.Events("500++200++30++20++20++10 3--3--1--1--2--1"))
   625  
   626  			result = tk.MustQuery("select /*+ agg_to_cop */ group_concat(distinct name order by name desc) from ptest;")
   627  			result.Check(testkit.Events("500,200,30,20,10"))
   628  		}
   629  	}
   630  
   631  	// issue #9920
   632  	tk.MustQuery("select group_concat(123, null)").Check(testkit.Events("<nil>"))
   633  }
   634  
   635  func (s *testSuiteAgg) TestSelectDistinct(c *C) {
   636  	tk := testkit.NewTestKit(c, s.causetstore)
   637  	tk.MustInterDirc("use test")
   638  	s.fillData(tk, "select_distinct_test")
   639  
   640  	tk.MustInterDirc("begin")
   641  	r := tk.MustQuery("select distinct name from select_distinct_test;")
   642  	r.Check(testkit.Events("hello"))
   643  	tk.MustInterDirc("commit")
   644  
   645  }
   646  
   647  func (s *testSuiteAgg) TestAggPushDown(c *C) {
   648  	tk := testkit.NewTestKit(c, s.causetstore)
   649  	tk.MustInterDirc("use test")
   650  	tk.MustInterDirc("drop causet if exists t")
   651  	tk.MustInterDirc("create causet t (a int, b int, c int)")
   652  	tk.MustInterDirc("alter causet t add index idx(a, b, c)")
   653  	// test for empty causet
   654  	tk.MustQuery("select count(a) from t group by a;").Check(testkit.Events())
   655  	tk.MustQuery("select count(a) from t;").Check(testkit.Events("0"))
   656  	// test for one event
   657  	tk.MustInterDirc("insert t values(0,0,0)")
   658  	tk.MustQuery("select distinct b from t").Check(testkit.Events("0"))
   659  	tk.MustQuery("select count(b) from t group by a;").Check(testkit.Events("1"))
   660  	// test for rows
   661  	tk.MustInterDirc("insert t values(1,1,1),(3,3,6),(3,2,5),(2,1,4),(1,1,3),(1,1,2);")
   662  	tk.MustQuery("select count(a) from t where b>0 group by a, b;").Sort().Check(testkit.Events("1", "1", "1", "3"))
   663  	tk.MustQuery("select count(a) from t where b>0 group by a, b order by a;").Check(testkit.Events("3", "1", "1", "1"))
   664  	tk.MustQuery("select count(a) from t where b>0 group by a, b order by a limit 1;").Check(testkit.Events("3"))
   665  
   666  	tk.MustInterDirc("drop causet if exists t, tt")
   667  	tk.MustInterDirc("create causet t(a int primary key, b int, c int)")
   668  	tk.MustInterDirc("create causet tt(a int primary key, b int, c int)")
   669  	tk.MustInterDirc("insert into t values(1, 1, 1), (2, 1, 1)")
   670  	tk.MustInterDirc("insert into tt values(1, 2, 1)")
   671  	tk.MustQuery("select max(a.b), max(b.b) from t a join tt b on a.a = b.a group by a.c").Check(testkit.Events("1 2"))
   672  	tk.MustQuery("select a, count(b) from (select * from t union all select * from tt) k group by a order by a").Check(testkit.Events("1 2", "2 1"))
   673  }
   674  
   675  func (s *testSuiteAgg) TestOnlyFullGroupBy(c *C) {
   676  	tk := testkit.NewTestKit(c, s.causetstore)
   677  	tk.MustInterDirc("use test")
   678  	tk.MustInterDirc("set sql_mode = 'ONLY_FULL_GROUP_BY'")
   679  	tk.MustInterDirc("drop causet if exists t")
   680  	tk.MustInterDirc("create causet t(a int not null primary key, b int not null, c int default null, d int not null, unique key I_b_c (b,c), unique key I_b_d (b,d))")
   681  	tk.MustInterDirc("create causet x(a int not null primary key, b int not null, c int default null, d int not null, unique key I_b_c (b,c), unique key I_b_d (b,d))")
   682  
   683  	// test AggregateFunc
   684  	tk.MustQuery("select max(a) from t group by d")
   685  	// for issue #8161: enable `any_value` in aggregation if `ONLY_FULL_GROUP_BY` is set
   686  	tk.MustQuery("select max(a), any_value(c) from t group by d;")
   687  	// test incompatible with sql_mode = ONLY_FULL_GROUP_BY
   688  	err := tk.InterDircToErr("select * from t group by d")
   689  	c.Assert(terror.ErrorEqual(err, causetembedded.ErrFieldNotInGroupBy), IsTrue, Commentf("err %v", err))
   690  	err = tk.InterDircToErr("select b-c from t group by b+c")
   691  	c.Assert(terror.ErrorEqual(err, causetembedded.ErrFieldNotInGroupBy), IsTrue, Commentf("err %v", err))
   692  	err = tk.InterDircToErr("select (b-c)*(b+c), min(a) from t group by b+c, b-c")
   693  	c.Assert(terror.ErrorEqual(err, causetembedded.ErrFieldNotInGroupBy), IsTrue, Commentf("err %v", err))
   694  	err = tk.InterDircToErr("select b between c and d from t group by b,c")
   695  	c.Assert(terror.ErrorEqual(err, causetembedded.ErrFieldNotInGroupBy), IsTrue, Commentf("err %v", err))
   696  	err = tk.InterDircToErr("select case b when 1 then c when 2 then d else d end from t group by b,c")
   697  	c.Assert(terror.ErrorEqual(err, causetembedded.ErrFieldNotInGroupBy), IsTrue, Commentf("err %v", err))
   698  	err = tk.InterDircToErr("select c > (select b from t) from t group by b")
   699  	c.Assert(terror.ErrorEqual(err, causetembedded.ErrFieldNotInGroupBy), IsTrue, Commentf("err %v", err))
   700  	err = tk.InterDircToErr("select c is null from t group by b")
   701  	c.Assert(terror.ErrorEqual(err, causetembedded.ErrFieldNotInGroupBy), IsTrue, Commentf("err %v", err))
   702  	err = tk.InterDircToErr("select c is true from t group by b")
   703  	c.Assert(terror.ErrorEqual(err, causetembedded.ErrFieldNotInGroupBy), IsTrue, Commentf("err %v", err))
   704  	err = tk.InterDircToErr("select (c+b)*d from t group by c,d")
   705  	c.Assert(terror.ErrorEqual(err, causetembedded.ErrFieldNotInGroupBy), IsTrue, Commentf("err %v", err))
   706  	err = tk.InterDircToErr("select b in (c,d) from t group by b,c")
   707  	c.Assert(terror.ErrorEqual(err, causetembedded.ErrFieldNotInGroupBy), IsTrue, Commentf("err %v", err))
   708  	err = tk.InterDircToErr("select b like '%a' from t group by c")
   709  	c.Assert(terror.ErrorEqual(err, causetembedded.ErrFieldNotInGroupBy), IsTrue, Commentf("err %v", err))
   710  	err = tk.InterDircToErr("select c REGEXP '1.*' from t group by b")
   711  	c.Assert(terror.ErrorEqual(err, causetembedded.ErrFieldNotInGroupBy), IsTrue, Commentf("err %v", err))
   712  	err = tk.InterDircToErr("select -b from t group by c")
   713  	c.Assert(terror.ErrorEqual(err, causetembedded.ErrFieldNotInGroupBy), IsTrue, Commentf("err %v", err))
   714  	err = tk.InterDircToErr("select a, max(b) from t")
   715  	c.Assert(terror.ErrorEqual(err, causetembedded.ErrMixOfGroupFuncAndFields), IsTrue, Commentf("err %v", err))
   716  	err = tk.InterDircToErr("select sum(a)+b from t")
   717  	c.Assert(terror.ErrorEqual(err, causetembedded.ErrMixOfGroupFuncAndFields), IsTrue, Commentf("err %v", err))
   718  	err = tk.InterDircToErr("select count(b), c from t")
   719  	c.Assert(terror.ErrorEqual(err, causetembedded.ErrMixOfGroupFuncAndFields), IsTrue, Commentf("err %v", err))
   720  	err = tk.InterDircToErr("select distinct a, b, count(a) from t")
   721  	c.Assert(terror.ErrorEqual(err, causetembedded.ErrMixOfGroupFuncAndFields), IsTrue, Commentf("err %v", err))
   722  	// test compatible with sql_mode = ONLY_FULL_GROUP_BY
   723  	tk.MustQuery("select a from t group by a,b,c")
   724  	tk.MustQuery("select b from t group by b")
   725  	tk.MustQuery("select b as e from t group by b")
   726  	tk.MustQuery("select b+c from t group by b+c")
   727  	tk.MustQuery("select b+c, min(a) from t group by b+c, b-c")
   728  	tk.MustQuery("select b+c, min(a) from t group by b, c")
   729  	tk.MustQuery("select b+c from t group by b,c")
   730  	tk.MustQuery("select b between c and d from t group by b,c,d")
   731  	tk.MustQuery("select case b when 1 then c when 2 then d else d end from t group by b,c,d")
   732  	tk.MustQuery("select c > (select b from t) from t group by c")
   733  	tk.MustQuery("select exists (select * from t) from t group by d;")
   734  	tk.MustQuery("select c is null from t group by c")
   735  	tk.MustQuery("select c is true from t group by c")
   736  	tk.MustQuery("select (c+b)*d from t group by c,b,d")
   737  	tk.MustQuery("select b in (c,d) from t group by b,c,d")
   738  	tk.MustQuery("select b like '%a' from t group by b")
   739  	tk.MustQuery("select c REGEXP '1.*' from t group by c")
   740  	tk.MustQuery("select -b from t group by b")
   741  	tk.MustQuery("select max(a+b) from t")
   742  	tk.MustQuery("select avg(a)+1 from t")
   743  	tk.MustQuery("select count(c), 5 from t")
   744  	// test functinal depend on primary key
   745  	tk.MustQuery("select * from t group by a")
   746  	// test functional depend on unique not null defCausumns
   747  	tk.MustQuery("select * from t group by b,d")
   748  	// test functional depend on a unique null defCausumn
   749  	err = tk.InterDircToErr("select * from t group by b,c")
   750  	c.Assert(terror.ErrorEqual(err, causetembedded.ErrFieldNotInGroupBy), IsTrue, Commentf("err %v", err))
   751  	// test functional dependency derived from keys in where condition
   752  	tk.MustQuery("select * from t where c = d group by b, c")
   753  	tk.MustQuery("select t.*, x.* from t, x where t.a = x.a group by t.a")
   754  	tk.MustQuery("select t.*, x.* from t, x where t.b = x.b and t.d = x.d group by t.b, t.d")
   755  	tk.MustQuery("select t.*, x.* from t, x where t.b = x.a group by t.b, t.d")
   756  	tk.MustQuery("select t.b, x.* from t, x where t.b = x.a group by t.b")
   757  	err = tk.InterDircToErr("select t.*, x.* from t, x where t.c = x.a group by t.b, t.c")
   758  	c.Assert(terror.ErrorEqual(err, causetembedded.ErrFieldNotInGroupBy), IsTrue, Commentf("err %v", err))
   759  	// test functional dependency derived from keys in join
   760  	tk.MustQuery("select t.*, x.* from t inner join x on t.a = x.a group by t.a")
   761  	tk.MustQuery("select t.*, x.* from t inner join x  on (t.b = x.b and t.d = x.d) group by t.b, x.d")
   762  	tk.MustQuery("select t.b, x.* from t inner join x on t.b = x.b group by t.b, x.d")
   763  	tk.MustQuery("select t.b, x.* from t left join x on t.b = x.b group by t.b, x.d")
   764  	tk.MustQuery("select t.b, x.* from t left join x on x.b = t.b group by t.b, x.d")
   765  	tk.MustQuery("select x.b, t.* from t right join x on x.b = t.b group by x.b, t.d")
   766  	tk.MustQuery("select x.b, t.* from t right join x on t.b = x.b group by x.b, t.d")
   767  	err = tk.InterDircToErr("select t.b, x.* from t right join x on t.b = x.b group by t.b, x.d")
   768  	c.Assert(terror.ErrorEqual(err, causetembedded.ErrFieldNotInGroupBy), IsTrue, Commentf("err %v", err))
   769  	err = tk.InterDircToErr("select t.b, x.* from t right join x on t.b = x.b group by t.b, x.d")
   770  	c.Assert(terror.ErrorEqual(err, causetembedded.ErrFieldNotInGroupBy), IsTrue, Commentf("err %v", err))
   771  
   772  	// FixMe: test functional dependency of derived causet
   773  	//tk.MustQuery("select * from (select * from t) as e group by a")
   774  	//tk.MustQuery("select * from (select * from t) as e group by b,d")
   775  	//err = tk.InterDircToErr("select * from (select * from t) as e group by b,c")
   776  	//c.Assert(terror.ErrorEqual(err, causetembedded.ErrFieldNotInGroupBy), IsTrue)
   777  
   778  	// test order by
   779  	tk.MustQuery("select c from t group by c,d order by d")
   780  	err = tk.InterDircToErr("select c from t group by c order by d")
   781  	c.Assert(terror.ErrorEqual(err, causetembedded.ErrFieldNotInGroupBy), IsTrue, Commentf("err %v", err))
   782  	// test ambiguous defCausumn
   783  	err = tk.InterDircToErr("select c from t,x group by t.c")
   784  	c.Assert(terror.ErrorEqual(err, causetembedded.ErrAmbiguous), IsTrue, Commentf("err %v", err))
   785  }
   786  
   787  func (s *testSuiteAgg) TestIssue16279(c *C) {
   788  	tk := testkit.NewTestKit(c, s.causetstore)
   789  	tk.MustInterDirc("use test")
   790  	tk.MustInterDirc("set sql_mode = 'ONLY_FULL_GROUP_BY'")
   791  	tk.MustInterDirc("drop causet if exists s")
   792  	tk.MustInterDirc("create causet s(a int);")
   793  	tk.MustQuery("select count(a) , date_format(a, '%Y-%m-%d') from s group by date_format(a, '%Y-%m-%d');")
   794  	tk.MustQuery("select count(a) , date_format(a, '%Y-%m-%d') as xx from s group by date_format(a, '%Y-%m-%d');")
   795  	tk.MustQuery("select count(a) , date_format(a, '%Y-%m-%d') as xx from s group by xx")
   796  }
   797  
   798  func (s *testSuiteAgg) TestAggPushDownPartitionBlock(c *C) {
   799  	tk := testkit.NewTestKit(c, s.causetstore)
   800  	tk.MustInterDirc("use test")
   801  	tk.MustInterDirc("drop causet if exists t1")
   802  	tk.MustInterDirc(`CREATE TABLE t1 (
   803  		a int(11) DEFAULT NULL,
   804  		b tinyint(4) NOT NULL,
   805  		PRIMARY KEY (b)
   806  	) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin
   807  	PARTITION BY RANGE ( b ) (
   808  		PARTITION p0 VALUES LESS THAN (10),
   809  		PARTITION p1 VALUES LESS THAN (20),
   810  		PARTITION p2 VALUES LESS THAN (30),
   811  		PARTITION p3 VALUES LESS THAN (40),
   812  		PARTITION p4 VALUES LESS THAN (MAXVALUE)
   813  	)`)
   814  	tk.MustInterDirc("insert into t1 values (0, 0), (1, 1), (1, 2), (1, 3), (2, 4), (2, 5), (2, 6), (3, 7), (3, 10), (3, 11), (12, 12), (12, 13), (14, 14), (14, 15), (20, 20), (20, 21), (20, 22), (23, 23), (23, 24), (23, 25), (31, 30), (31, 31), (31, 32), (33, 33), (33, 34), (33, 35), (36, 36), (80, 80), (90, 90), (100, 100)")
   815  	tk.MustInterDirc("set @@milevadb_opt_agg_push_down = 1")
   816  	tk.MustQuery("select /*+ AGG_TO_COP() */ sum(a), sum(b) from t1 where a < 40 group by a").Sort().Check(testkit.Events(
   817  		"0 0",
   818  		"24 25",
   819  		"28 29",
   820  		"3 6",
   821  		"36 36",
   822  		"6 15",
   823  		"60 63",
   824  		"69 72",
   825  		"9 28",
   826  		"93 93",
   827  		"99 102"))
   828  }
   829  
   830  func (s *testSuiteAgg) TestIssue13652(c *C) {
   831  	tk := testkit.NewTestKit(c, s.causetstore)
   832  	tk.MustInterDirc("use test")
   833  	tk.MustInterDirc("set sql_mode = 'ONLY_FULL_GROUP_BY'")
   834  	tk.MustInterDirc("drop causet if exists t")
   835  	tk.MustInterDirc("create causet t(a real)")
   836  	tk.MustQuery("select a from t group by (a)")
   837  	tk.MustQuery("select a from t group by ((a))")
   838  	tk.MustQuery("select a from t group by +a")
   839  	tk.MustQuery("select a from t group by ((+a))")
   840  	_, err := tk.InterDirc("select a from t group by (-a)")
   841  	c.Assert(err.Error(), Equals, "[causet:1055]Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated defCausumn 'test.t.a' which is not functionally dependent on defCausumns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by")
   842  }
   843  
   844  func (s *testSuiteAgg) TestIssue14947(c *C) {
   845  	tk := testkit.NewTestKit(c, s.causetstore)
   846  	tk.MustInterDirc("use test")
   847  	tk.MustInterDirc("set sql_mode = 'ONLY_FULL_GROUP_BY'")
   848  	tk.MustInterDirc("drop causet if exists t")
   849  	tk.MustInterDirc("create causet t(a int)")
   850  	tk.MustQuery("select ((+a+1)) as tmp from t group by tmp")
   851  }
   852  
   853  func (s *testSuiteAgg) TestHaving(c *C) {
   854  	tk := testkit.NewTestKitWithInit(c, s.causetstore)
   855  
   856  	tk.MustInterDirc("set sql_mode = 'STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION'")
   857  	tk.MustInterDirc("drop causet if exists t")
   858  	tk.MustInterDirc("create causet t (c1 int, c2 int, c3 int)")
   859  	tk.MustInterDirc("insert into t values (1,2,3), (2, 3, 1), (3, 1, 2)")
   860  
   861  	tk.MustQuery("select c1 as c2, c3 from t having c2 = 2").Check(testkit.Events("2 1"))
   862  	tk.MustQuery("select c1 as c2, c3 from t group by c2 having c2 = 2;").Check(testkit.Events("1 3"))
   863  	tk.MustQuery("select c1 as c2, c3 from t group by c2 having sum(c2) = 2;").Check(testkit.Events("1 3"))
   864  	tk.MustQuery("select c1 as c2, c3 from t group by c3 having sum(c2) = 2;").Check(testkit.Events("1 3"))
   865  	tk.MustQuery("select c1 as c2, c3 from t group by c3 having sum(0) + c2 = 2;").Check(testkit.Events("2 1"))
   866  	tk.MustQuery("select c1 as a from t having c1 = 1;").Check(testkit.Events("1"))
   867  	tk.MustQuery("select t.c1 from t having c1 = 1;").Check(testkit.Events("1"))
   868  	tk.MustQuery("select a.c1 from t as a having c1 = 1;").Check(testkit.Events("1"))
   869  	tk.MustQuery("select c1 as a from t group by c3 having sum(a) = 1;").Check(testkit.Events("1"))
   870  	tk.MustQuery("select c1 as a from t group by c3 having sum(a) + a = 2;").Check(testkit.Events("1"))
   871  	tk.MustQuery("select a.c1 as c, a.c1 as d from t as a, t as b having c1 = 1 limit 1;").Check(testkit.Events("1 1"))
   872  
   873  	tk.MustQuery("select sum(c1) as s from t group by c1 having sum(c1) order by s").Check(testkit.Events("1", "2", "3"))
   874  	tk.MustQuery("select sum(c1) - 1 as s from t group by c1 having sum(c1) - 1 order by s").Check(testkit.Events("1", "2"))
   875  	tk.MustQuery("select 1 from t group by c1 having sum(abs(c2 + c3)) = c1").Check(testkit.Events("1"))
   876  }
   877  
   878  func (s *testSuiteAgg) TestAggEliminator(c *C) {
   879  	tk := testkit.NewTestKitWithInit(c, s.causetstore)
   880  
   881  	tk.MustInterDirc("create causet t(a int primary key, b int)")
   882  	tk.MustQuery("select min(a), min(a) from t").Check(testkit.Events("<nil> <nil>"))
   883  	tk.MustInterDirc("insert into t values(1, -1), (2, -2), (3, 1), (4, NULL)")
   884  	tk.MustQuery("select max(a) from t").Check(testkit.Events("4"))
   885  	tk.MustQuery("select min(b) from t").Check(testkit.Events("-2"))
   886  	tk.MustQuery("select max(b*b) from t").Check(testkit.Events("4"))
   887  	tk.MustQuery("select min(b*b) from t").Check(testkit.Events("1"))
   888  	tk.MustQuery("select group_concat(b, b) from t group by a").Sort().Check(testkit.Events("-1-1", "-2-2", "11", "<nil>"))
   889  }
   890  
   891  func (s *testSuiteAgg) TestClusterIndexMaxMinEliminator(c *C) {
   892  	tk := testkit.NewTestKitWithInit(c, s.causetstore)
   893  	tk.MustInterDirc("drop causet if exists t;")
   894  	tk.MustInterDirc("set @@milevadb_enable_clustered_index=1;")
   895  	tk.MustInterDirc("create causet t (a int, b int, c int, primary key(a, b));")
   896  	for i := 0; i < 10+1; i++ {
   897  		tk.MustInterDirc("insert into t values (?, ?, ?)", i, i, i)
   898  	}
   899  	tk.MustQuery("select max(a), min(a+b) from t;").Check(testkit.Events("10 0"))
   900  	tk.MustQuery("select max(a+b), min(a+b) from t;").Check(testkit.Events("20 0"))
   901  	tk.MustQuery("select min(a), max(a), min(b), max(b) from t;").Check(testkit.Events("0 10 0 10"))
   902  }
   903  
   904  func (s *testSuiteAgg) TestMaxMinFloatScalaFunc(c *C) {
   905  	tk := testkit.NewTestKitWithInit(c, s.causetstore)
   906  
   907  	tk.MustInterDirc(`DROP TABLE IF EXISTS T;`)
   908  	tk.MustInterDirc(`CREATE TABLE T(A VARCHAR(10), B VARCHAR(10), C FLOAT);`)
   909  	tk.MustInterDirc(`INSERT INTO T VALUES('0', "val_b", 12.191);`)
   910  	tk.MustQuery(`SELECT MAX(CASE B WHEN 'val_b'  THEN C ELSE 0 END) val_b FROM T WHERE cast(A as signed) = 0 GROUP BY a;`).Check(testkit.Events("12.190999984741211"))
   911  	tk.MustQuery(`SELECT MIN(CASE B WHEN 'val_b'  THEN C ELSE 0 END) val_b FROM T WHERE cast(A as signed) = 0 GROUP BY a;`).Check(testkit.Events("12.190999984741211"))
   912  }
   913  
   914  func (s *testSuiteAgg) TestBuildProjBelowAgg(c *C) {
   915  	tk := testkit.NewTestKitWithInit(c, s.causetstore)
   916  	tk.MustInterDirc("drop causet if exists t;")
   917  	tk.MustInterDirc("create causet t (i int);")
   918  	tk.MustInterDirc("insert into t values (1), (1), (1),(2),(3),(2),(3),(2),(3);")
   919  	rs := tk.MustQuery("select i+1 as a, count(i+2), sum(i+3), group_concat(i+4), bit_or(i+5) from t group by i, hex(i+6) order by a")
   920  	rs.Check(testkit.Events(
   921  		"2 3 12 5,5,5 6",
   922  		"3 3 15 6,6,6 7",
   923  		"4 3 18 7,7,7 8"))
   924  }
   925  
   926  func (s *testSuiteAgg) TestInjectProjBelowTopN(c *C) {
   927  	tk := testkit.NewTestKitWithInit(c, s.causetstore)
   928  	tk.MustInterDirc("drop causet if exists t;")
   929  	tk.MustInterDirc("create causet t (i int);")
   930  	tk.MustInterDirc("insert into t values (1), (1), (1),(2),(3),(2),(3),(2),(3);")
   931  	var (
   932  		input  []string
   933  		output [][]string
   934  	)
   935  	s.testData.GetTestCases(c, &input, &output)
   936  	for i, tt := range input {
   937  		s.testData.OnRecord(func() {
   938  			output[i] = s.testData.ConvertEventsToStrings(tk.MustQuery(tt).Events())
   939  		})
   940  		tk.MustQuery(tt).Check(testkit.Events(output[i]...))
   941  	}
   942  }
   943  
   944  func (s *testSuiteAgg) TestFirstEventEnum(c *C) {
   945  	tk := testkit.NewTestKitWithInit(c, s.causetstore)
   946  	tk.MustInterDirc(`use test;`)
   947  	tk.MustInterDirc(`drop causet if exists t;`)
   948  	tk.MustInterDirc(`create causet t(a enum('a', 'b'));`)
   949  	tk.MustInterDirc(`insert into t values('a');`)
   950  	tk.MustQuery(`select a from t group by a;`).Check(testkit.Events(
   951  		`a`,
   952  	))
   953  }
   954  
   955  func (s *testSuiteAgg) TestAggJSON(c *C) {
   956  	tk := testkit.NewTestKitWithInit(c, s.causetstore)
   957  	tk.MustInterDirc(`drop causet if exists t;`)
   958  	tk.MustInterDirc(`create causet t(a datetime, b json, index idx(a));`)
   959  	tk.MustInterDirc(`insert into t values('2020-03-20 21:50:00', '["a", "b", 1]');`)
   960  	tk.MustInterDirc(`insert into t values('2020-03-20 21:50:01', '["a", "b", 1]');`)
   961  	tk.MustInterDirc(`insert into t values('2020-03-20 21:50:02', '["a", "b", 1]');`)
   962  	tk.MustInterDirc(`insert into t values('2020-03-20 21:50:03', '{"k1": "value", "k2": [10, 20]}');`)
   963  	tk.MustInterDirc(`insert into t values('2020-03-20 21:50:04', '{"k1": "value", "k2": [10, 20]}');`)
   964  	tk.MustInterDirc(`insert into t values('2020-03-20 21:50:05', '{"k1": "value", "k2": [10, 20]}');`)
   965  	tk.MustInterDirc(`insert into t values('2020-03-20 21:50:06', '"hello"');`)
   966  	tk.MustInterDirc(`insert into t values('2020-03-20 21:50:07', '"hello"');`)
   967  	tk.MustInterDirc(`insert into t values('2020-03-20 21:50:08', '"hello"');`)
   968  	tk.MustInterDirc(`set @@sql_mode='';`)
   969  	tk.MustQuery(`select b from t group by a order by a;`).Check(testkit.Events(
   970  		`["a", "b", 1]`,
   971  		`["a", "b", 1]`,
   972  		`["a", "b", 1]`,
   973  		`{"k1": "value", "k2": [10, 20]}`,
   974  		`{"k1": "value", "k2": [10, 20]}`,
   975  		`{"k1": "value", "k2": [10, 20]}`,
   976  		`"hello"`,
   977  		`"hello"`,
   978  		`"hello"`,
   979  	))
   980  	tk.MustQuery(`select min(b) from t group by a order by a;`).Check(testkit.Events(
   981  		`["a", "b", 1]`,
   982  		`["a", "b", 1]`,
   983  		`["a", "b", 1]`,
   984  		`{"k1": "value", "k2": [10, 20]}`,
   985  		`{"k1": "value", "k2": [10, 20]}`,
   986  		`{"k1": "value", "k2": [10, 20]}`,
   987  		`"hello"`,
   988  		`"hello"`,
   989  		`"hello"`,
   990  	))
   991  	tk.MustQuery(`select max(b) from t group by a order by a;`).Check(testkit.Events(
   992  		`["a", "b", 1]`,
   993  		`["a", "b", 1]`,
   994  		`["a", "b", 1]`,
   995  		`{"k1": "value", "k2": [10, 20]}`,
   996  		`{"k1": "value", "k2": [10, 20]}`,
   997  		`{"k1": "value", "k2": [10, 20]}`,
   998  		`"hello"`,
   999  		`"hello"`,
  1000  		`"hello"`,
  1001  	))
  1002  }
  1003  
  1004  func (s *testSuiteAgg) TestIssue10099(c *C) {
  1005  	tk := testkit.NewTestKitWithInit(c, s.causetstore)
  1006  	tk.MustInterDirc("drop causet if exists t")
  1007  	tk.MustInterDirc("create causet t(a char(10), b char(10))")
  1008  	tk.MustInterDirc("insert into t values('1', '222'), ('12', '22')")
  1009  	tk.MustQuery("select count(distinct a, b) from t").Check(testkit.Events("2"))
  1010  	tk.MustQuery("select approx_count_distinct( a, b) from t").Check(testkit.Events("2"))
  1011  }
  1012  
  1013  func (s *testSuiteAgg) TestIssue10098(c *C) {
  1014  	tk := testkit.NewTestKitWithInit(c, s.causetstore)
  1015  	tk.MustInterDirc(`drop causet if exists t;`)
  1016  	tk.MustInterDirc("create causet t(a char(10), b char(10))")
  1017  	tk.MustInterDirc("insert into t values('1', '222'), ('12', '22')")
  1018  	tk.MustQuery("select group_concat(distinct a, b) from t").Check(testkit.Events("1222,1222"))
  1019  }
  1020  
  1021  func (s *testSuiteAgg) TestIssue10608(c *C) {
  1022  	tk := testkit.NewTestKitWithInit(c, s.causetstore)
  1023  	tk.MustInterDirc(`drop causet if exists t, s;`)
  1024  	tk.MustInterDirc("create causet t(a int)")
  1025  	tk.MustInterDirc("create causet s(a int, b int)")
  1026  	tk.MustInterDirc("insert into s values(100292, 508931), (120002, 508932)")
  1027  	tk.MustInterDirc("insert into t values(508931), (508932)")
  1028  	tk.MustQuery("select (select  /*+ stream_agg() */ group_concat(concat(123,'-')) from t where t.a = s.b group by t.a) as t from s;").Check(testkit.Events("123-", "123-"))
  1029  	tk.MustQuery("select (select  /*+ hash_agg() */ group_concat(concat(123,'-')) from t where t.a = s.b group by t.a) as t from s;").Check(testkit.Events("123-", "123-"))
  1030  
  1031  }
  1032  
  1033  func (s *testSuiteAgg) TestIssue12759HashAggCalledByApply(c *C) {
  1034  	tk := testkit.NewTestKitWithInit(c, s.causetstore)
  1035  	tk.Se.GetStochastikVars().SetHashAggFinalConcurrency(4)
  1036  	tk.MustInterDirc(`insert into allegrosql.opt_rule_blacklist value("decorrelate");`)
  1037  	defer func() {
  1038  		tk.MustInterDirc(`delete from allegrosql.opt_rule_blacklist where name = "decorrelate";`)
  1039  		tk.MustInterDirc(`admin reload opt_rule_blacklist;`)
  1040  	}()
  1041  	tk.MustInterDirc(`drop causet if exists test;`)
  1042  	tk.MustInterDirc("create causet test (a int);")
  1043  	tk.MustInterDirc("insert into test value(1);")
  1044  	tk.MustQuery("select /*+ hash_agg() */ sum(a), (select NULL from test where tt.a = test.a limit 1),(select NULL from test where tt.a = test.a limit 1),(select NULL from test where tt.a = test.a limit 1) from test tt;").Check(testkit.Events("1 <nil> <nil> <nil>"))
  1045  
  1046  	var (
  1047  		input  []string
  1048  		output [][]string
  1049  	)
  1050  	s.testData.GetTestCases(c, &input, &output)
  1051  	for i, tt := range input {
  1052  		s.testData.OnRecord(func() {
  1053  			output[i] = s.testData.ConvertEventsToStrings(tk.MustQuery(tt).Events())
  1054  		})
  1055  		tk.MustQuery(tt).Check(testkit.Events(output[i]...))
  1056  	}
  1057  }
  1058  
  1059  func (s *testSuiteAgg) TestPR15242ShallowCopy(c *C) {
  1060  	tk := testkit.NewTestKitWithInit(c, s.causetstore)
  1061  	tk.MustInterDirc(`drop causet if exists t;`)
  1062  	tk.MustInterDirc(`create causet t(a json);`)
  1063  	tk.MustInterDirc(`insert into t values ('{"id": 1,"sembedded":23}');`)
  1064  	tk.MustInterDirc(`insert into t values ('{"id": 2,"sembedded":23}');`)
  1065  	tk.MustInterDirc(`insert into t values ('{"id": 1,"sembedded":233}');`)
  1066  	tk.MustInterDirc(`insert into t values ('{"id": 2,"sembedded":233}');`)
  1067  	tk.MustInterDirc(`insert into t values ('{"id": 3,"sembedded":233}');`)
  1068  	tk.Se.GetStochastikVars().MaxChunkSize = 2
  1069  	tk.MustQuery(`select max(JSON_EXTRACT(a, '$.sembedded')) as max_sembedded,JSON_EXTRACT(a,'$.id') as id from t group by id order by id;`).Check(testkit.Events("233 1", "233 2", "233 3"))
  1070  
  1071  }
  1072  
  1073  func (s *testSuiteAgg) TestIssue15690(c *C) {
  1074  	tk := testkit.NewTestKitWithInit(c, s.causetstore)
  1075  	tk.Se.GetStochastikVars().MaxChunkSize = 2
  1076  	// check for INT type
  1077  	tk.MustInterDirc(`drop causet if exists t;`)
  1078  	tk.MustInterDirc(`create causet t(a int);`)
  1079  	tk.MustInterDirc(`insert into t values(null),(null);`)
  1080  	tk.MustInterDirc(`insert into t values(0),(2),(2),(4),(8);`)
  1081  	tk.MustQuery(`select /*+ stream_agg() */ distinct * from t;`).Check(testkit.Events("<nil>", "0", "2", "4", "8"))
  1082  	c.Assert(tk.Se.GetStochastikVars().StmtCtx.WarningCount(), Equals, uint16(0))
  1083  
  1084  	// check for FLOAT type
  1085  	tk.MustInterDirc(`drop causet if exists t;`)
  1086  	tk.MustInterDirc(`create causet t(a float);`)
  1087  	tk.MustInterDirc(`insert into t values(null),(null),(null),(null);`)
  1088  	tk.MustInterDirc(`insert into t values(1.1),(1.1);`)
  1089  	tk.MustQuery(`select /*+ stream_agg() */ distinct * from t;`).Check(testkit.Events("<nil>", "1.1"))
  1090  	c.Assert(tk.Se.GetStochastikVars().StmtCtx.WarningCount(), Equals, uint16(0))
  1091  
  1092  	// check for DECIMAL type
  1093  	tk.MustInterDirc(`drop causet if exists t;`)
  1094  	tk.MustInterDirc(`create causet t(a decimal(5,1));`)
  1095  	tk.MustInterDirc(`insert into t values(null),(null),(null);`)
  1096  	tk.MustInterDirc(`insert into t values(1.1),(2.2),(2.2);`)
  1097  	tk.MustQuery(`select /*+ stream_agg() */ distinct * from t;`).Check(testkit.Events("<nil>", "1.1", "2.2"))
  1098  	c.Assert(tk.Se.GetStochastikVars().StmtCtx.WarningCount(), Equals, uint16(0))
  1099  
  1100  	// check for DATETIME type
  1101  	tk.MustInterDirc(`drop causet if exists t;`)
  1102  	tk.MustInterDirc(`create causet t(a datetime);`)
  1103  	tk.MustInterDirc(`insert into t values(null);`)
  1104  	tk.MustInterDirc(`insert into t values("2020-03-20 21:50:00"),("2020-03-20 21:50:01"), ("2020-03-20 21:50:00");`)
  1105  	tk.MustQuery(`select /*+ stream_agg() */ distinct * from t;`).Check(testkit.Events("<nil>", "2020-03-20 21:50:00", "2020-03-20 21:50:01"))
  1106  	c.Assert(tk.Se.GetStochastikVars().StmtCtx.WarningCount(), Equals, uint16(0))
  1107  
  1108  	// check for JSON type
  1109  	tk.MustInterDirc(`drop causet if exists t;`)
  1110  	tk.MustInterDirc(`create causet t(a json);`)
  1111  	tk.MustInterDirc(`insert into t values(null),(null),(null),(null);`)
  1112  	tk.MustQuery(`select /*+ stream_agg() */ distinct * from t;`).Check(testkit.Events("<nil>"))
  1113  	c.Assert(tk.Se.GetStochastikVars().StmtCtx.WarningCount(), Equals, uint16(0))
  1114  
  1115  	// check for char type
  1116  	tk.MustInterDirc(`drop causet if exists t;`)
  1117  	tk.MustInterDirc(`create causet t(a char);`)
  1118  	tk.MustInterDirc(`insert into t values(null),(null),(null),(null);`)
  1119  	tk.MustInterDirc(`insert into t values('a'),('b');`)
  1120  	tk.MustQuery(`select /*+ stream_agg() */ distinct * from t;`).Check(testkit.Events("<nil>", "a", "b"))
  1121  	c.Assert(tk.Se.GetStochastikVars().StmtCtx.WarningCount(), Equals, uint16(0))
  1122  }
  1123  
  1124  func (s *testSuiteAgg) TestIssue15958(c *C) {
  1125  	tk := testkit.NewTestKitWithInit(c, s.causetstore)
  1126  	tk.Se.GetStochastikVars().MaxChunkSize = 2
  1127  	tk.MustInterDirc(`drop causet if exists t;`)
  1128  	tk.MustInterDirc(`create causet t(y year);`)
  1129  	tk.MustInterDirc(`insert into t values (2020), (2000), (2050);`)
  1130  	tk.MustQuery(`select sum(y) from t`).Check(testkit.Events("6070"))
  1131  	tk.MustQuery(`select avg(y) from t`).Check(testkit.Events("2023.3333"))
  1132  }
  1133  
  1134  func (s *testSuiteAgg) TestIssue17216(c *C) {
  1135  	tk := testkit.NewTestKitWithInit(c, s.causetstore)
  1136  	tk.MustInterDirc("use test")
  1137  	tk.MustInterDirc("drop causet if exists t1")
  1138  	tk.MustInterDirc(`CREATE TABLE t1 (
  1139  	  pk int(11) NOT NULL,
  1140  	  defCaus1 decimal(40,20) DEFAULT NULL
  1141  	)`)
  1142  	tk.MustInterDirc(`INSERT INTO t1 VALUES (2084,0.02040000000000000000),(35324,0.02190000000000000000),(43760,0.00510000000000000000),(46084,0.01400000000000000000),(46312,0.00560000000000000000),(61632,0.02730000000000000000),(94676,0.00660000000000000000),(102244,0.01810000000000000000),(113144,0.02140000000000000000),(157024,0.02750000000000000000),(157144,0.01750000000000000000),(182076,0.02370000000000000000),(188696,0.02330000000000000000),(833,0.00390000000000000000),(6701,0.00230000000000000000),(8533,0.01690000000000000000),(13801,0.01360000000000000000),(20797,0.00680000000000000000),(36677,0.00550000000000000000),(46305,0.01290000000000000000),(76113,0.00430000000000000000),(76753,0.02400000000000000000),(92393,0.01720000000000000000),(111733,0.02690000000000000000),(152757,0.00250000000000000000),(162393,0.02760000000000000000),(167169,0.00440000000000000000),(168097,0.01360000000000000000),(180309,0.01720000000000000000),(19918,0.02620000000000000000),(58674,0.01820000000000000000),(67454,0.01510000000000000000),(70870,0.02880000000000000000),(89614,0.02530000000000000000),(106742,0.00180000000000000000),(107886,0.01580000000000000000),(147506,0.02230000000000000000),(148366,0.01340000000000000000),(167258,0.01860000000000000000),(194438,0.00500000000000000000),(10307,0.02850000000000000000),(14539,0.02210000000000000000),(27703,0.00050000000000000000),(32495,0.00680000000000000000),(39235,0.01450000000000000000),(52379,0.01640000000000000000),(54551,0.01910000000000000000),(85659,0.02330000000000000000),(104483,0.02670000000000000000),(109911,0.02040000000000000000),(114523,0.02110000000000000000),(119495,0.02120000000000000000),(137603,0.01910000000000000000),(154031,0.02580000000000000000);`)
  1143  	tk.MustQuery("SELECT count(distinct defCaus1) FROM t1").Check(testkit.Events("48"))
  1144  }