oceanbase

1
--disable_query_log
2
set @@session.explicit_defaults_for_timestamp=off;
3
--enable_query_log
4
#owner: bin.lb
5
#owner group: sql1
6
#tags: group_by
7
#description:
8

9
##
10
## simple test of all group functions
11
##
12
#
13
--disable_warnings
14
drop table if exists t1,t2;
15
--enable_warnings
16

17
#
18
##
19
## Test that new VARCHAR correctly works with COUNT(DISTINCT)
20
##
21
#
22
CREATE TABLE t1 (pk int primary key, a VARCHAR(400));
23
INSERT INTO t1  VALUES (1,'A'), (2,'a'), (3,'a '), (4,'a   '),
24
                          (5,'B'), (6,'b'), (7,'b '), (8,'b   ');
25
SELECT COUNT(DISTINCT a) FROM t1;
26
DROP TABLE t1;
27
#
28
##
29
## Test for buf #9210: GROUP BY with expression if a decimal type
30
##
31
#
32
CREATE TABLE t1 (pk int primary key, a int, b int, c int);
33
INSERT INTO t1 (pk, a, b, c) VALUES
34
  (1,1,1,1), (2,1,1,2), (3,1,1,3),
35
  (4,1,2,1), (5,1,2,2), (6,1,2,3),
36
  (7,1,3,1), (8,1,3,2), (9,1,3,3),
37
  (10,2,1,1), (11,2,1,2), (12,2,1,3),
38
  (13,2,2,1), (14,2,2,2), (15,2,2,3),
39
  (16,2,3,1), (17,2,3,2), (18,2,3,3),
40
  (19,3,1,1), (20,3,1,2), (21,3,1,3),
41
  (22,3,2,1), (23,3,2,2), (24,3,2,3),
42
  (25,3,3,1), (26,3,3,2), (27,3,3,3);
43
#
44
SELECT b/c as v, a FROM t1 ORDER BY v, a;
45
SELECT b/c as v, SUM(a) FROM t1 GROUP BY v ORDER BY v;
46
SELECT SUM(a) as suma FROM t1 GROUP BY b/c ORDER BY suma;
47

48
DROP TABLE t1;
49