oceanbase

1
#owner: dachuan.sdc
2
#owner group: SQL3
3
# tags: optimizer
4

5
--disable_query_log
6
connect (obsys, $OBMYSQL_MS0, admin, $OBMYSQL_PWD, test, $OBMYSQL_PORT);
7
connection obsys;
8

9
set global ob_sql_work_area_percentage = 100;
10
sleep 10;
11
--enable_query_log
12
--source mysql_test/test_suite/px/include/exam_data.inc
13

14
## 测试不同并行度下的全表扫描
15
--sorted_result
16
select * from stu;
17
--sorted_result
18
select /*+ USE_PX parallel(2) */ * from stu;
19
--sorted_result
20
select /*+ USE_PX parallel(6) */ * from stu;
21
--sorted_result
22
select /*+ USE_PX parallel(18) */ * from stu;
23
--sorted_result
24
select /*+ USE_PX parallel(28) */ name, cls from stu;
25

26
--sorted_result
27
select * from teacher;
28
--sorted_result
29
select /*+ USE_PX parallel(2) */ * from teacher;
30
--sorted_result
31
select /*+ USE_PX parallel(6) */ * from teacher;
32
--sorted_result
33
select /*+ USE_PX parallel(8) */ * from teacher;
34
--sorted_result
35
select /*+ USE_PX parallel(18) */ name from teacher;
36

37
--sorted_result
38
select * from score;
39
--sorted_result
40
select /*+ USE_PX parallel(2) */ * from score;
41
--sorted_result
42
select /*+ USE_PX parallel(6) */ * from score;
43
--sorted_result
44
select /*+ USE_PX parallel(8) */ * from score;
45
--sorted_result
46
select /*+ USE_PX parallel(18) */ score, sid from score;
47

48

49

50
# 测试全表扫，范围扫，分区裁剪扫
51
--sorted_result
52
select /*+ USE_PX parallel(3) */ * from score where score > 60 and score < 90;
53
--sorted_result
54
select /*+ USE_PX parallel(3) */ * from score where score > 60 and score < 90 and subject = 'MA';
55
--sorted_result
56
select /*+ USE_PX parallel(3) */ * from score where score > 60 and score < 90 and subject IN ('MA', 'EN', 'HIS');
57
--sorted_result
58
select /*+ USE_PX parallel(3) */ * from score where sid >=20 and sid <= 40 and score > 60 and score < 90 and subject IN ('MA', 'EN', 'HIS');
59
--sorted_result
60
select /*+ USE_PX parallel(3) */ * from score where sid IN (41, 51) and score > 60 and score < 90 and subject IN ('MA', 'EN', 'HIS');
61

62

63

64
# 测试 merge sort receive
65
explain select /*+ USE_PX parallel(2) */ * from stu order by name desc;
66
select /*+ USE_PX parallel(2) */ * from stu order by name desc;
67
select /*+ USE_PX parallel(2) */ * from stu order by name;
68

69
# 测试 limit push down
70
explain select /*+ USE_PX parallel(2) */ * from stu order by name limit 3;
71
select /*+ USE_PX parallel(2) */ * from stu order by name limit 3;
72

73

74
## 
75
## SEArray自动扩容失败
76
create table t11 (c1 int, c2 int, c3 int, c4 int) partition by hash(c1);
77

78
select /*+use_px parallel(2) read_consistency(weak) */ t0.c1 from (select c1,c2 from t11 where t11.c1 BETWEEN 1000000000000001 and 1000000000000500) t0 join (select c1,c2 from t11 where t11.c1 BETWEEN 1000000000000001 and 1000000000000500) t1 on t0.c1=t1.c1 join (select c1,c2 from t11 where t11.c1 BETWEEN 1000000000000001 and 1000000000000500) t2 on t0.c1=t2.c1 join (select c1,c2 from t11 where t11.c1 BETWEEN 1000000000000001 and 1000000000000500) t3 on t0.c1=t3.c1 join (select c1,c2 from t11 where t11.c1 BETWEEN 1000000000000001 and 1000000000000500) t4 on t0.c1=t4.c1 join (select c1,c2 from t11 where t11.c1 BETWEEN 1000000000000001 and 1000000000000500) t5 on t0.c1=t5.c1 join (select c1,c2 from t11 where t11.c1 BETWEEN 1000000000000001 and 1000000000000500) t6 on t0.c1=t6.c1 join (select c1,c2 from t11 where t11.c1 BETWEEN 1000000000000001 and 1000000000000500) t7 on t0.c1=t7.c1 join (select c1,c2 from t11 where t11.c1 BETWEEN 1000000000000001 and 1000000000000500) t8 on t0.c1=t8.c1 join (select c1,c2 from t11 where t11.c1 BETWEEN 1000000000000001 and 1000000000000500) t9 on t0.c1=t9.c1 join (select c1,c2 from t11 where t11.c1 BETWEEN 1000000000000001 and 1000000000000500) t10 on t0.c1=t10.c1 join (select c1,c2 from t11 where t11.c1 BETWEEN 1000000000000001 and 1000000000000500) t11 on t0.c1=t11.c1 join (select c1,c2 from t11 where t11.c1 BETWEEN 1000000000000001 and 1000000000000500) t12 on t0.c1=t12.c1 join (select c1,c2 from t11 where t11.c1 BETWEEN 1000000000000001 and 1000000000000500) t13 on t0.c1=t13.c1 join (select c1,c2 from t11 where t11.c1 BETWEEN 1000000000000001 and 1000000000000500) t14 on t0.c1=t14.c1 join (select c1,c2 from t11 where t11.c1 BETWEEN 1000000000000001 and 1000000000000500) t15 on t0.c1=t15.c1 join (select c1,c2 from t11 where t11.c1 BETWEEN 1000000000000001 and 1000000000000500) t16 on t0.c1=t16.c1 join (select c1,c2 from t11 where t11.c1 BETWEEN 1000000000000001 and 1000000000000500) t17 on t0.c1=t17.c1 join (select c1,c2 from t11 where t11.c1 BETWEEN 1000000000000001 and 1000000000000500) t18 on t0.c1=t18.c1 join (select c1,c2 from t11 where t11.c1 BETWEEN 1000000000000001 and 1000000000000500) t19 on t0.c1=t19.c1;
79

80
--echo
81
# bug:
82
--disable_warnings
83
drop table if exists t1;
84
--enable_warnings
85
create table t1(c1 int primary key, c2 varchar(10)) partition by hash(c1) partitions 2;
86
insert into t1 values(1, 'aaa'),(2,'bbb');
87
explain basic select * from t1 where c2 not in('', 'xxx');
88
select * from t1 where c2 not in('', 'xxx');
89

90
--disable_query_log
91
set global ob_sql_work_area_percentage = 5;
92

93
disconnect obsys;
94