oceanbase

1
#owner: zhouhaiyu.zhy
2
#owner group: SQL3
3
# tags: optimizer
4
--result_format 4
5

6
# test aggregation
7

8
--disable_result_log
9

10
--source mysql_test/test_suite/px/include/exam_data.inc
11
--disable_result_log
12
connect (obsys,$OBMYSQL_MS0,admin,$OBMYSQL_PWD,test,$OBMYSQL_PORT);
13
connection obsys;
14
alter system set enable_sql_audit = true;
15
alter system flush sql audit global;
16

17
disconnect obsys;
18
connection default;
19

20
--sorted_result
21
select /*+ TEST_SQL_AUDIT USE_PX parallel(2) */ * from teacher;
22
--sorted_result
23
select /*+ TEST_SQL_AUDIT USE_PX parallel(3) */ * from teacher;
24
--sorted_result
25
select /*+ TEST_SQL_AUDIT USE_PX parallel(4) */ * from teacher;
26
--sorted_result
27
select /*+ TEST_SQL_AUDIT USE_PX parallel(2) */ subject, avg(score), max(score), min(score) from score group by subject;
28
--sorted_result
29
select /*+ TEST_SQL_AUDIT USE_PX parallel(2) */ cls, subject, avg(score), max(score), min(score) from score join stu using (sid) group by cls, subject;
30
--sorted_result
31
select /*+ TEST_SQL_AUDIT USE_PX parallel(2) */ count(*) from score join stu using (sid) inner join teacher where score.subject = teacher.subject;
32
--sorted_result
33
select /*+ TEST_SQL_AUDIT USE_PX parallel(3) */ subject, avg(score), max(score), min(score) from score group by subject;
34
--sorted_result
35
select /*+ TEST_SQL_AUDIT USE_PX parallel(3) */ cls, subject, avg(score), max(score), min(score) from score join stu using (sid) group by cls, subject;
36
--sorted_result
37
select /*+ TEST_SQL_AUDIT USE_PX parallel(3) */ count(*) from score join stu using (sid) inner join teacher where score.subject = teacher.subject;
38

39
--enable_result_log
40
connect (obsys,$OBMYSQL_MS0,admin,$OBMYSQL_PWD,test,$OBMYSQL_PORT);
41
connection obsys;
42

43
--sorted_result
44
eval select query_sql, EXPECTED_WORKER_COUNT, USED_WORKER_COUNT from oceanbase.GV\$OB_SQL_AUDIT where EXPECTED_WORKER_COUNT > 0 AND query_sql like '%TEST_SQL_AUDIT%' order by request_time desc limit 10;
45