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: optimizer, join
7
# description
8
#
9
--disable_warnings
10
drop table if exists t1;
11
--enable_warnings
12
set global ob_sql_work_area_percentage = 100;
13
sleep 10;
14
#
15
##
16
## Test of join of many tables.
17
#
18
create table t1 (id int primary key,extra int);
19
insert into t1(id) values(1),(2);
20
select t1000.id from
21
t1 as t1000
22
left join t1 as t2000 on t1000.id=t2000.id
23
left join t1 as t2001 on t2000.id=t2001.id
24
left join t1 as t2002 on t2001.id=t2002.id
25
left join t1 as t2003 on t2002.id=t2003.id
26
left join t1 as t2004 on t2003.id=t2004.id
27
left join t1 as t2005 on t2004.id=t2005.id
28
left join t1 as t2006 on t2005.id=t2006.id
29
left join t1 as t2007 on t2006.id=t2007.id
30
left join t1 as t2008 on t2007.id=t2008.id
31
left join t1 as t2009 on t2008.id=t2009.id
32
left join t1 as t2010 on t2009.id=t2010.id
33
left join t1 as t2011 on t2010.id=t2011.id
34
left join t1 as t2012 on t2011.id=t2012.id
35
left join t1 as t2013 on t2012.id=t2013.id
36
left join t1 as t2014 on t2013.id=t2014.id
37
left join t1 as t2015 on t2014.id=t2015.id
38
left join t1 as t2016 on t2015.id=t2016.id
39
left join t1 as t2017 on t2016.id=t2017.id
40
left join t1 as t2018 on t2017.id=t2018.id
41
left join t1 as t2019 on t2018.id=t2019.id
42
left join t1 as t2020 on t2019.id=t2020.id
43
left join t1 as t2021 on t2020.id=t2021.id
44
left join t1 as t2022 on t2021.id=t2022.id
45
left join t1 as t2023 on t2022.id=t2023.id
46
left join t1 as t2024 on t2023.id=t2024.id
47
left join t1 as t2025 on t2024.id=t2025.id
48
left join t1 as t2026 on t2025.id=t2026.id
49
left join t1 as t2027 on t2026.id=t2027.id
50
left join t1 as t2028 on t2027.id=t2028.id
51
left join t1 as t2029 on t2028.id=t2029.id
52
left join t1 as t2030 on t2029.id=t2030.id
53
left join t1 as t2031 on t2030.id=t2031.id;
54

55
--replace_regex /Plan signature: [0-9]*/Plan signature/
56
--disable_result_log
57
explain select t1000.id from
58
t1 as t1000
59
left join t1 as t2000 on t1000.id=t2000.id
60
left join t1 as t2001 on t2000.id=t2001.id
61
left join t1 as t2002 on t2001.id=t2002.id
62
left join t1 as t2003 on t2002.id=t2003.id
63
left join t1 as t2004 on t2003.id=t2004.id
64
left join t1 as t2005 on t2004.id=t2005.id
65
left join t1 as t2006 on t2005.id=t2006.id
66
left join t1 as t2007 on t2006.id=t2007.id
67
left join t1 as t2008 on t2007.id=t2008.id
68
left join t1 as t2009 on t2008.id=t2009.id
69
left join t1 as t2010 on t2009.id=t2010.id
70
left join t1 as t2011 on t2010.id=t2011.id
71
left join t1 as t2012 on t2011.id=t2012.id
72
left join t1 as t2013 on t2012.id=t2013.id
73
left join t1 as t2014 on t2013.id=t2014.id
74
left join t1 as t2015 on t2014.id=t2015.id
75
left join t1 as t2016 on t2015.id=t2016.id
76
left join t1 as t2017 on t2016.id=t2017.id
77
left join t1 as t2018 on t2017.id=t2018.id
78
left join t1 as t2019 on t2018.id=t2019.id
79
left join t1 as t2020 on t2019.id=t2020.id
80
left join t1 as t2021 on t2020.id=t2021.id
81
left join t1 as t2022 on t2021.id=t2022.id
82
left join t1 as t2023 on t2022.id=t2023.id
83
left join t1 as t2024 on t2023.id=t2024.id
84
left join t1 as t2025 on t2024.id=t2025.id
85
left join t1 as t2026 on t2025.id=t2026.id
86
left join t1 as t2027 on t2026.id=t2027.id
87
left join t1 as t2028 on t2027.id=t2028.id
88
left join t1 as t2029 on t2028.id=t2029.id
89
left join t1 as t2030 on t2029.id=t2030.id
90
left join t1 as t2031 on t2030.id=t2031.id;
91
--enable_result_log
92
#--replace_result '31 tables' 'XX tables' '61 tables' 'XX tables'
93
#--error 1116
94
#select t1.a from t1 as t1 left join t1 as t2 using (a) left join t1 as t3 using (a) left join t1 as t4 using (a) left join t1 as t5 using (a) left join t1 as t6 using (a) left join t1 as t7 using (a) left join t1 as t8 using (a) left join t1 as t9 using (a) left join t1 as t10 using (a) left join t1 as t11 using (a) left join t1 as t12 using (a) left join t1 as t13 using (a) left join t1 as t14 using (a) left join t1 as t15 using (a) left join t1 as t16 using (a) left join t1 as t17 using (a) left join t1 as t18 using (a) left join t1 as t19 using (a) left join t1 as t20 using (a) left join t1 as t21 using (a) left join t1 as t22 using (a) left join t1 as t23 using (a) left join t1 as t24 using (a) left join t1 as t25 using (a) left join t1 as t26 using (a) left join t1 as t27 using (a) left join t1 as t28 using (a) left join t1 as t29 using (a) left join t1 as t30 using (a) left join t1 as t31 using (a) left join t1 as t32 using (a) left join t1 as t33 using (a) left join t1 as t34 using (a) left join t1 as t35 using (a) left join t1 as t36 using (a) left join t1 as t37 using (a) left join t1 as t38 using (a) left join t1 as t39 using (a) left join t1 as t40 using (a) left join t1 as t41 using (a) left join t1 as t42 using (a) left join t1 as t43 using (a) left join t1 as t44 using (a) left join t1 as t45 using (a) left join t1 as t46 using (a) left join t1 as t47 using (a) left join t1 as t48 using (a) left join t1 as t49 using (a) left join t1 as t50 using (a) left join t1 as t51 using (a) left join t1 as t52 using (a) left join t1 as t53 using (a) left join t1 as t54 using (a) left join t1 as t55 using (a) left join t1 as t56 using (a) left join t1 as t57 using (a) left join t1 as t58 using (a) left join t1 as t59 using (a) left join t1 as t60 using (a) left join t1 as t61 using (a) left join t1 as t62 using (a) left join t1 as t63 using (a) left join t1 as t64 using (a) left join t1 as t65 using (a);
95
#select a from t1 as t1 left join t1 as t2 using (a) left join t1 as t3 using (a) left join t1 as t4 using (a) left join t1 as t5 using (a) left join t1 as t6 using (a) left join t1 as t7 using (a) left join t1 as t8 using (a) left join t1 as t9 using (a) left join t1 as t10 using (a) left join t1 as t11 using (a) left join t1 as t12 using (a) left join t1 as t13 using (a) left join t1 as t14 using (a) left join t1 as t15 using (a) left join t1 as t16 using (a) left join t1 as t17 using (a) left join t1 as t18 using (a) left join t1 as t19 using (a) left join t1 as t20 using (a) left join t1 as t21 using (a) left join t1 as t22 using (a) left join t1 as t23 using (a) left join t1 as t24 using (a) left join t1 as t25 using (a) left join t1 as t26 using (a) left join t1 as t27 using (a) left join t1 as t28 using (a) left join t1 as t29 using (a) left join t1 as t30 using (a) left join t1 as t31 using (a);
96
#--replace_result '31 tables' 'XX tables' '61 tables' 'XX tables'
97
#--error 1116
98
#select a from t1 as t1 left join t1 as t2 using (a) left join t1 as t3 using (a) left join t1 as t4 using (a) left join t1 as t5 using (a) left join t1 as t6 using (a) left join t1 as t7 using (a) left join t1 as t8 using (a) left join t1 as t9 using (a) left join t1 as t10 using (a) left join t1 as t11 using (a) left join t1 as t12 using (a) left join t1 as t13 using (a) left join t1 as t14 using (a) left join t1 as t15 using (a) left join t1 as t16 using (a) left join t1 as t17 using (a) left join t1 as t18 using (a) left join t1 as t19 using (a) left join t1 as t20 using (a) left join t1 as t21 using (a) left join t1 as t22 using (a) left join t1 as t23 using (a) left join t1 as t24 using (a) left join t1 as t25 using (a) left join t1 as t26 using (a) left join t1 as t27 using (a) left join t1 as t28 using (a) left join t1 as t29 using (a) left join t1 as t30 using (a) left join t1 as t31 using (a) left join t1 as t32 using (a) left join t1 as t33 using (a) left join t1 as t34 using (a) left join t1 as t35 using (a) left join t1 as t36 using (a) left join t1 as t37 using (a) left join t1 as t38 using (a) left join t1 as t39 using (a) left join t1 as t40 using (a) left join t1 as t41 using (a) left join t1 as t42 using (a) left join t1 as t43 using (a) left join t1 as t44 using (a) left join t1 as t45 using (a) left join t1 as t46 using (a) left join t1 as t47 using (a) left join t1 as t48 using (a) left join t1 as t49 using (a) left join t1 as t50 using (a) left join t1 as t51 using (a) left join t1 as t52 using (a) left join t1 as t53 using (a) left join t1 as t54 using (a) left join t1 as t55 using (a) left join t1 as t56 using (a) left join t1 as t57 using (a) left join t1 as t58 using (a) left join t1 as t59 using (a) left join t1 as t60 using (a) left join t1 as t61 using (a) left join t1 as t62 using (a) left join t1 as t63 using (a) left join t1 as t64 using (a) left join t1 as t65 using (a);
99
drop table t1;
100
set global ob_sql_work_area_percentage = 5;
101
#
102