github.com/matrixorigin/matrixone@v1.2.0/pkg/sql/plan/explain/explain_test.go (about) 1 // Copyright 2021 - 2022 Matrix Origin 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 // WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. 12 // See the License for the specific language governing permissions and 13 // limitations under the License. 14 15 package explain 16 17 import ( 18 "context" 19 "strings" 20 "testing" 21 22 "github.com/matrixorigin/matrixone/pkg/common/moerr" 23 "github.com/matrixorigin/matrixone/pkg/sql/parsers/dialect" 24 "github.com/matrixorigin/matrixone/pkg/sql/parsers/dialect/mysql" 25 "github.com/matrixorigin/matrixone/pkg/sql/parsers/tree" 26 "github.com/matrixorigin/matrixone/pkg/sql/plan" 27 ) 28 29 func TestSingleSql(t *testing.T) { 30 // input := "explain verbose SELECT N_REGIONKEY + 2 as a, N_REGIONKEY/2, N_REGIONKEY* N_NATIONKEY, N_REGIONKEY % N_NATIONKEY, N_REGIONKEY - N_NATIONKEY FROM NATION WHERE -N_NATIONKEY < -20" 31 //input := "explain verbose SELECT N_REGIONKEY + 2 as a FROM NATION WHERE -N_NATIONKEY < -20" 32 // input := "explain verbose select c_custkey from (select c_custkey from CUSTOMER group by c_custkey ) a" 33 // input := "explain SELECT N_NAME, N_REGIONKEY FROM NATION WHERE N_REGIONKEY > 0 AND N_NAME LIKE '%AA'" 34 // input := "explain verbose SELECT N_NAME, N_REGIONKEY a FROM NATION WHERE N_NATIONKEY > 0 AND N_NATIONKEY < 10" 35 //input := "explain verbose SELECT N_NAME, N_REGIONKEY a FROM NATION WHERE N_NATIONKEY > 0 OR N_NATIONKEY < 10" 36 //input := "explain verbose select * from part where p_container in ('SM CASE', 'SM BOX', 'SM PACK', 'SM PKG')" 37 //input := "explain select abs(N_REGIONKEY) from NATION" 38 //input := "explain verbose SELECT l.L_ORDERKEY a FROM CUSTOMER c, ORDERS o, LINEITEM l WHERE c.C_CUSTKEY = o.O_CUSTKEY and l.L_ORDERKEY = o.O_ORDERKEY and o.O_ORDERKEY < 10" 39 //input := "explain verbose update emp set sal = sal + 500, comm = 1200 where deptno = 10" 40 input := "explain verbose select case when p_type like 'PROMO%' then l_extendedprice * (1 - l_discount) when p_type like 'PRX%' then l_extendedprice * (2 - l_discount) else 0 end from lineitem,part where l_shipdate < date '1996-04-01' + interval '1' month" 41 mock := plan.NewMockOptimizer(true) 42 err := runOneStmt(mock, t, input) 43 if err != nil { 44 t.Fatalf("%+v", err) 45 } 46 } 47 48 func TestBasicSqlExplain(t *testing.T) { 49 sqls := []string{ 50 "explain verbose SELECT N_NAME, N_REGIONKEY a FROM NATION WHERE N_REGIONKEY > 0 ORDER BY a DESC", 51 "explain verbose SELECT N_NAME,N_REGIONKEY, 23 as a FROM NATION", 52 "explain verbose SELECT N_NAME, N_REGIONKEY, 23 as a FROM NATION", 53 "explain SELECT N_NAME, N_REGIONKEY a FROM NATION WHERE N_NATIONKEY > 0 OR N_NATIONKEY < 10", 54 "explain verbose SELECT N_NAME, N_REGIONKEY a FROM NATION WHERE N_NATIONKEY > 0 OR N_NATIONKEY < 10", 55 "explain SELECT N_NAME, N_REGIONKEY a FROM NATION WHERE N_NATIONKEY > 0 AND N_NATIONKEY < 10", 56 "explain verbose SELECT N_NAME, N_REGIONKEY a FROM NATION WHERE N_NATIONKEY > 0 AND N_NATIONKEY < 10", 57 "explain verbose SELECT N_NAME, N_REGIONKEY a FROM NATION WHERE N_NATIONKEY > 0 AND N_NATIONKEY < 10 ORDER BY N_NAME, N_REGIONKEY DESC", 58 "explain verbose SELECT count(*) FROM NATION group by N_NAME", 59 "explain verbose SELECT N_NAME, MAX(N_REGIONKEY) FROM NATION GROUP BY N_NAME HAVING MAX(N_REGIONKEY) > 10", 60 "explain SELECT N_NAME, N_REGIONKEY FROM NATION WHERE N_REGIONKEY > 0 AND N_NAME LIKE '%AA' ORDER BY N_NAME DESC, N_REGIONKEY limit 10", 61 "explain SELECT N_NAME, N_REGIONKEY FROM NATION WHERE N_REGIONKEY > 0 AND N_NAME LIKE '%AA' ORDER BY N_NAME DESC, N_REGIONKEY LIMIT 10 offset 20", 62 "explain verbose select case when p_type like 'PROMO%' then l_extendedprice * (1 - l_discount) when p_type like 'PRX%' then l_extendedprice * (2 - l_discount) else 0 end from lineitem,part where l_shipdate < date '1996-04-01' + interval '1' month", 63 } 64 mockOptimizer := plan.NewMockOptimizer(false) 65 runTestShouldPass(mockOptimizer, t, sqls) 66 } 67 68 // Single table query 69 func TestSingleTableQuery(t *testing.T) { 70 sqls := []string{ 71 "explain verbose SELECT N_NAME, N_REGIONKEY FROM NATION WHERE N_REGIONKEY > 0 AND N_NAME LIKE '%AA' ORDER BY N_NAME DESC, N_REGIONKEY LIMIT 10, 20", 72 "explain SELECT N_NAME, N_REGIONKEY FROM NATION WHERE N_REGIONKEY > 0 AND N_NAME LIKE '%AA' ORDER BY N_NAME DESC, N_REGIONKEY LIMIT 10, 20", 73 "explain verbose SELECT N_NAME, N_REGIONKEY a FROM NATION WHERE N_REGIONKEY > 0 ORDER BY a DESC", //test alias 74 "explain SELECT N_NAME, N_REGIONKEY a FROM NATION WHERE N_REGIONKEY > 0 ORDER BY a DESC", //test alias 75 "explain verbose SELECT NATION.N_NAME FROM NATION", // test alias 76 "explain SELECT NATION.N_NAME FROM NATION", // test alias 77 "explain verbose SELECT * FROM NATION", //test star 78 "explain SELECT * FROM NATION", //test star 79 "explain verbose SELECT a.* FROM NATION a", //test star 80 "explain SELECT a.* FROM NATION a", //test star 81 "explain verbose SELECT count(*) FROM NATION", //test star 82 "explain SELECT count(*) FROM NATION", //test star 83 "explain verbose SELECT count(*) FROM NATION group by N_NAME", //test star 84 "explain SELECT count(*) FROM NATION group by N_NAME", //test star 85 "explain verbose SELECT N_NAME, MAX(N_REGIONKEY) FROM NATION GROUP BY N_NAME HAVING MAX(N_REGIONKEY) > 10", //test agg 86 "explain SELECT N_NAME, MAX(N_REGIONKEY) FROM NATION GROUP BY N_NAME HAVING MAX(N_REGIONKEY) > 10", //test agg 87 "explain verbose SELECT DISTINCT N_NAME FROM NATION limit 10", 88 "explain verbose SELECT DISTINCT N_NAME FROM NATION", //test distinct 89 "explain SELECT DISTINCT N_NAME FROM NATION", //test distinct 90 "explain verbose SELECT N_REGIONKEY + 2 as a, N_REGIONKEY/2, N_REGIONKEY* N_NATIONKEY, N_REGIONKEY % N_NATIONKEY, N_REGIONKEY - N_NATIONKEY FROM NATION WHERE -N_NATIONKEY < -20", //test more expr 91 "explain SELECT N_REGIONKEY + 2 as a, N_REGIONKEY/2, N_REGIONKEY* N_NATIONKEY, N_REGIONKEY % N_NATIONKEY, N_REGIONKEY - N_NATIONKEY FROM NATION WHERE -N_NATIONKEY < -20", //test more expr 92 "explain verbose SELECT N_REGIONKEY FROM NATION where N_REGIONKEY >= N_NATIONKEY or (N_NAME like '%ddd' and N_REGIONKEY >0.5)", //test more expr 93 "explain SELECT N_REGIONKEY FROM NATION where N_REGIONKEY >= N_NATIONKEY or (N_NAME like '%ddd' and N_REGIONKEY >0.5)", //test more expr 94 "explain verbose SELECT N_REGIONKEY FROM NATION where N_REGIONKEY between 2 and 2 OR N_NATIONKEY not between 3 and 10", //test more expr 95 "explain SELECT N_REGIONKEY FROM NATION where N_REGIONKEY between 2 and 2 OR N_NATIONKEY not between 3 and 10", //test more expr 96 // "explain verbose SELECT N_REGIONKEY FROM NATION where N_REGIONKEY is null and N_NAME is not null", 97 // "explain SELECT N_REGIONKEY FROM NATION where N_REGIONKEY is null and N_NAME is not null", 98 } 99 mockOptimizer := plan.NewMockOptimizer(false) 100 runTestShouldPass(mockOptimizer, t, sqls) 101 } 102 103 // Join query 104 func TestJoinQuery(t *testing.T) { 105 sqls := []string{ 106 "explain SELECT NATION.N_NAME, REGION.R_NAME FROM NATION join REGION on NATION.N_REGIONKEY = REGION.R_REGIONKEY WHERE NATION.N_REGIONKEY > 10 AND NATION.N_NAME > REGION.R_NAME", 107 "explain verbose SELECT NATION.N_NAME, REGION.R_NAME FROM NATION join REGION on NATION.N_REGIONKEY = REGION.R_REGIONKEY WHERE NATION.N_REGIONKEY > 10 AND NATION.N_NAME > REGION.R_NAME", 108 "explain SELECT NATION.N_NAME, REGION.R_NAME FROM NATION left join REGION on NATION.N_REGIONKEY = REGION.R_REGIONKEY WHERE NATION.N_REGIONKEY > 10 AND NATION.N_NAME > REGION.R_NAME", 109 "explain verbose SELECT NATION.N_NAME, REGION.R_NAME FROM NATION left join REGION on NATION.N_REGIONKEY = REGION.R_REGIONKEY WHERE NATION.N_REGIONKEY > 10 AND NATION.N_NAME > REGION.R_NAME", 110 // "explain SELECT l.l_orderkey FROM customer c, orders o, lineitem l WHERE c.c_custkey = o.o_custkey and l.l_orderkey = o.o_orderkey and o.o_orderdate < current_date", 111 "explain verbose SELECT N_NAME, N_REGIONKEY FROM NATION join REGION on NATION.N_REGIONKEY = REGION.R_REGIONKEY WHERE NATION.N_REGIONKEY > 0", 112 "explain SELECT N_NAME, N_REGIONKEY FROM NATION join REGION on NATION.N_REGIONKEY = REGION.R_REGIONKEY WHERE NATION.N_REGIONKEY > 0", 113 "explain verbose SELECT N_NAME, NATION2.R_REGIONKEY FROM NATION2 join REGION using(R_REGIONKEY) WHERE NATION2.R_REGIONKEY > 0", 114 "explain SELECT N_NAME, NATION2.R_REGIONKEY FROM NATION2 join REGION using(R_REGIONKEY) WHERE NATION2.R_REGIONKEY > 0", 115 "explain verbose SELECT N_NAME, NATION2.R_REGIONKEY FROM NATION2 NATURAL JOIN REGION WHERE NATION2.R_REGIONKEY > 0", 116 "explain SELECT N_NAME, NATION2.R_REGIONKEY FROM NATION2 NATURAL JOIN REGION WHERE NATION2.R_REGIONKEY > 0", 117 "explain verbose SELECT N_NAME FROM NATION NATURAL JOIN REGION", //have no same column name but it's ok 118 "explain SELECT N_NAME FROM NATION NATURAL JOIN REGION", 119 "explain verbose SELECT N_NAME,N_REGIONKEY FROM NATION a join REGION b on a.N_REGIONKEY = b.R_REGIONKEY WHERE a.N_REGIONKEY > 0", //test alias 120 "explain SELECT N_NAME,N_REGIONKEY FROM NATION a join REGION b on a.N_REGIONKEY = b.R_REGIONKEY WHERE a.N_REGIONKEY > 0", //test alias 121 "explain verbose SELECT l.L_ORDERKEY a FROM CUSTOMER c, ORDERS o, LINEITEM l WHERE c.C_CUSTKEY = o.O_CUSTKEY and l.L_ORDERKEY = o.O_ORDERKEY and o.O_ORDERKEY < 10", //join three tables 122 "explain SELECT l.L_ORDERKEY a FROM CUSTOMER c, ORDERS o, LINEITEM l WHERE c.C_CUSTKEY = o.O_CUSTKEY and l.L_ORDERKEY = o.O_ORDERKEY and o.O_ORDERKEY < 10", //join three tables 123 "explain verbose SELECT c.* FROM CUSTOMER c, ORDERS o, LINEITEM l WHERE c.C_CUSTKEY = o.O_CUSTKEY and l.L_ORDERKEY = o.O_ORDERKEY", //test star 124 "explain SELECT c.* FROM CUSTOMER c, ORDERS o, LINEITEM l WHERE c.C_CUSTKEY = o.O_CUSTKEY and l.L_ORDERKEY = o.O_ORDERKEY", 125 "explain verbose SELECT * FROM CUSTOMER c, ORDERS o, LINEITEM l WHERE c.C_CUSTKEY = o.O_CUSTKEY and l.L_ORDERKEY = o.O_ORDERKEY", //test star 126 "explain SELECT * FROM CUSTOMER c, ORDERS o, LINEITEM l WHERE c.C_CUSTKEY = o.O_CUSTKEY and l.L_ORDERKEY = o.O_ORDERKEY", //test star 127 "explain verbose SELECT a.* FROM NATION a join REGION b on a.N_REGIONKEY = b.R_REGIONKEY WHERE a.N_REGIONKEY > 0", //test star 128 "explain SELECT a.* FROM NATION a join REGION b on a.N_REGIONKEY = b.R_REGIONKEY WHERE a.N_REGIONKEY > 0", 129 "explain verbose SELECT * FROM NATION a join REGION b on a.N_REGIONKEY = b.R_REGIONKEY WHERE a.N_REGIONKEY > 0", 130 "explain SELECT * FROM NATION a join REGION b on a.N_REGIONKEY = b.R_REGIONKEY WHERE a.N_REGIONKEY > 0", 131 } 132 mockOptimizer := plan.NewMockOptimizer(false) 133 runTestShouldPass(mockOptimizer, t, sqls) 134 } 135 136 // Nested query <no pass> 137 func TestNestedQuery(t *testing.T) { 138 sqls := []string{ 139 "explain verbose SELECT * FROM NATION where N_REGIONKEY > (select max(R_REGIONKEY) from REGION)", 140 //"explain SELECT * FROM NATION where N_REGIONKEY > (select max(R_REGIONKEY) from REGION where R_REGIONKEY < N_REGIONKEY)", 141 `explain verbose select 142 sum(l_extendedprice) / 7.0 as avg_yearly 143 from 144 lineitem, 145 part 146 where 147 p_partkey = l_partkey 148 and p_brand = 'Brand#54' 149 and p_container = 'LG BAG' 150 and l_quantity < ( 151 select 152 0.2 * avg(l_quantity) 153 from 154 lineitem 155 where 156 l_partkey = p_partkey 157 );`, //tpch q17 158 } 159 mockOptimizer := plan.NewMockOptimizer(false) 160 runTestShouldPass(mockOptimizer, t, sqls) 161 } 162 163 // Test Derived Table Query 164 func TestDerivedTableQuery(t *testing.T) { 165 sqls := []string{ 166 "explain select c_custkey from (select c_custkey from CUSTOMER group by c_custkey ) a", 167 "explain verbose select c_custkey from (select c_custkey from CUSTOMER group by c_custkey ) a", 168 "explain select c_custkey from (select c_custkey, count(C_NATIONKEY) ff from CUSTOMER group by c_custkey ) a where ff > 0 order by c_custkey", 169 "explain verbose select c_custkey from (select c_custkey, count(C_NATIONKEY) ff from CUSTOMER group by c_custkey ) a where ff > 0 order by c_custkey", 170 "explain select c_custkey from (select c_custkey, count(C_NATIONKEY) ff from CUSTOMER group by c_custkey ) a join NATION b on a.c_custkey = b.N_REGIONKEY where b.N_NATIONKEY > 10", 171 "explain verbose select c_custkey from (select c_custkey, count(C_NATIONKEY) ff from CUSTOMER group by c_custkey ) a join NATION b on a.c_custkey = b.N_REGIONKEY where b.N_NATIONKEY > 10", 172 "explain select a.* from (select c_custkey, count(C_NATIONKEY) ff from CUSTOMER group by c_custkey ) a join NATION b on a.c_custkey = b.N_REGIONKEY where b.N_NATIONKEY > 10", 173 "explain verbose select a.* from (select c_custkey, count(C_NATIONKEY) ff from CUSTOMER group by c_custkey ) a join NATION b on a.c_custkey = b.N_REGIONKEY where b.N_NATIONKEY > 10", 174 "explain select * from (select c_custkey, count(C_NATIONKEY) ff from CUSTOMER group by c_custkey ) a join NATION b on a.c_custkey = b.N_REGIONKEY where b.N_NATIONKEY > 10", 175 "explain verbose select * from (select c_custkey, count(C_NATIONKEY) ff from CUSTOMER group by c_custkey ) a join NATION b on a.c_custkey = b.N_REGIONKEY where b.N_NATIONKEY > 10", 176 } 177 mockOptimizer := plan.NewMockOptimizer(false) 178 runTestShouldPass(mockOptimizer, t, sqls) 179 } 180 181 // Collection query 182 func TestCollectionQuery(t *testing.T) { 183 sqls := []string{ 184 "explain verbose select 2 intersect select 2 union all select 22222", 185 "explain verbose select 1 union select 2", 186 "explain verbose select 1 union (select 2 union select 3)", 187 "explain verbose (select 1 union select 2) union select 3 intersect select 4 order by 1", 188 "explain verbose select 1 union select null", 189 "explain verbose select n_name from nation intersect select n_name from nation2", 190 "explain verbose select n_name from nation minus select n_name from nation2", 191 "explain verbose select 1 union select 2 intersect select 2 union all select 1.1 minus select 22222", 192 "explain verbose select 1 as a union select 2 order by a limit 1", 193 "explain verbose select n_name from nation union select n_comment from nation order by n_name", 194 "explain verbose with qn (foo, bar) as (select 1 as col, 2 as coll union select 4, 5) select qn1.bar from qn qn1", 195 "explain verbose select n_name, n_comment from nation union all select n_name, n_comment from nation2", 196 "explain verbose select n_name from nation intersect all select n_name from nation2", 197 "explain verbose SELECT distinct(l.L_ORDERKEY) FROM LINEITEM AS l WHERE l.L_SHIPINSTRUCT='DELIVER IN PERSON' UNION SELECT distinct(l.L_ORDERKEY) FROM LINEITEM AS l WHERE l.L_SHIPMODE='AIR' OR l.L_SHIPMODE='AIR REG'", 198 "explain verbose SELECT distinct(l.L_ORDERKEY) FROM LINEITEM AS l WHERE l.L_SHIPMODE IN ('AIR','AIR REG') EXCEPT SELECT distinct(l.L_ORDERKEY) FROM LINEITEM AS l WHERE l.L_SHIPINSTRUCT='DELIVER IN PERSON'", 199 } 200 mockOptimizer := plan.NewMockOptimizer(false) 201 runTestShouldPass(mockOptimizer, t, sqls) 202 } 203 204 func TestDMLInsert(t *testing.T) { 205 sqls := []string{ 206 //"explain INSERT NATION VALUES (1, 'NAME1',21, 'COMMENT1'), (2, 'NAME2', 22, 'COMMENT2')", 207 //"explain verbose INSERT NATION VALUES (1, 'NAME1',21, 'COMMENT1'), (2, 'NAME2', 22, 'COMMENT2')", 208 //"explain INSERT NATION (N_NATIONKEY, N_REGIONKEY, N_NAME) VALUES (1, 21, 'NAME1'), (2, 22, 'NAME2')", 209 //"explain verbose INSERT NATION (N_NATIONKEY, N_REGIONKEY, N_NAME) VALUES (1, 21, 'NAME1'), (2, 22, 'NAME2')", 210 "explain INSERT INTO NATION SELECT * FROM NATION2", 211 "explain verbose INSERT INTO NATION SELECT * FROM NATION2", 212 } 213 mockOptimizer := plan.NewMockOptimizer(false) 214 runTestShouldPass(mockOptimizer, t, sqls) 215 } 216 217 func TestDMLUpdate(t *testing.T) { 218 sqls := []string{ 219 "explain UPDATE NATION SET N_NAME ='U1', N_REGIONKEY=2", 220 "explain verbose UPDATE NATION SET N_NAME ='U1', N_REGIONKEY=2", 221 "explain UPDATE NATION SET N_NAME ='U1', N_REGIONKEY=2 WHERE N_NATIONKEY > 10 LIMIT 20", 222 "explain verbose UPDATE NATION SET N_NAME ='U1', N_REGIONKEY=2 WHERE N_NATIONKEY > 10 LIMIT 20", 223 "explain UPDATE NATION SET N_NAME ='U1', N_REGIONKEY=N_REGIONKEY+2 WHERE N_NATIONKEY > 10 LIMIT 20", 224 "explain verbose UPDATE NATION SET N_NAME ='U1', N_REGIONKEY=N_REGIONKEY+2 WHERE N_NATIONKEY > 10 LIMIT 20", 225 } 226 mockOptimizer := plan.NewMockOptimizer(true) 227 runTestShouldPass(mockOptimizer, t, sqls) 228 } 229 230 func TestDMLDelete(t *testing.T) { 231 sqls := []string{ 232 "explain DELETE FROM NATION", 233 "explain verbose DELETE FROM NATION", 234 "explain DELETE FROM NATION WHERE N_NATIONKEY > 10", 235 "explain verbose DELETE FROM NATION WHERE N_NATIONKEY > 10", 236 "explain DELETE FROM NATION WHERE N_NATIONKEY > 10 LIMIT 20", 237 "explain verbose DELETE FROM NATION WHERE N_NATIONKEY > 10 LIMIT 20", 238 "explain verbose DELETE FROM a1, a2 USING NATION AS a1 INNER JOIN NATION2 AS a2 WHERE a1.N_NATIONKEY=a2.N_NATIONKEY", 239 "explain verbose UPDATE NATION,REGION set NATION.N_REGIONKEY = REGION.R_REGIONKEY WHERE REGION.R_NAME = 'AAA'", 240 "explain verbose UPDATE NATION SET N_NAME ='U1', N_REGIONKEY=N_REGIONKEY+2 WHERE N_NATIONKEY > 10 LIMIT 20", 241 "explain verbose UPDATE NATION,NATION2 SET NATION.N_NAME ='U1',NATION2.N_NATIONKEY=15 WHERE NATION.N_NATIONKEY = NATION2.N_NATIONKEY", 242 } 243 mockOptimizer := plan.NewMockOptimizer(true) 244 runTestShouldPass(mockOptimizer, t, sqls) 245 } 246 247 func TestSystemVariableAndUserVariable(t *testing.T) { 248 sqls := []string{ 249 "explain verbose select @@autocommit from NATION", 250 "explain verbose select @@global.autocommit from NATION", 251 "explain verbose select @@session.autocommit from NATION", 252 "explain verbose select @@autocommit,N_NAME, N_REGIONKEY from NATION", 253 "explain verbose select @@global.autocommit,N_NAME, N_REGIONKEY from NATION", 254 "explain verbose select @@session.autocommit,N_NAME, N_REGIONKEY from NATION", 255 "explain verbose select @val from NATION", 256 "explain verbose select @val,@a,@b from NATION", 257 "explain verbose select @val,N_NAME, N_REGIONKEY from NATION", 258 "explain verbose select @@session.autocommit,@val from NATION", 259 "explain verbose select @@session.autocommit,@val,N_NAME from NATION", 260 } 261 mockOptimizer := plan.NewMockOptimizer(false) 262 runTestShouldPass(mockOptimizer, t, sqls) 263 } 264 265 // test index table 266 func TestSingleTableDeleteSQL(t *testing.T) { 267 sqls := []string{ 268 "explain verbose DELETE FROM emp where sal > 2000", 269 "explain verbose delete from emp t1 where t1.sal > 2000", 270 "explain verbose delete from emp where empno > 3000", 271 "explain verbose delete from emp where ename = 'SMITH'", 272 "explain verbose delete from dept where deptno = 10", 273 "explain verbose delete from dept where dname = 'RESEARCH'", 274 "explain verbose delete from dept where deptno = 10 order by dname limit 1", 275 "explain verbose delete from emp where deptno = 20 order by sal limit 2", 276 "explain verbose delete from emp where empno > 7800 order by empno limit 2", 277 } 278 mockOptimizer := plan.NewMockOptimizer(true) 279 runTestShouldPass(mockOptimizer, t, sqls) 280 } 281 282 // Composite unique index 283 func TestCompositeUniqueIndexTableDeleteSQL(t *testing.T) { 284 sqls := []string{ 285 "explain verbose delete from employees where sal > 2000", 286 "explain verbose delete from employees t1 where t1.sal > 2000", 287 "explain verbose delete from employees where empno > 3000", 288 "explain verbose delete from employees where ename = 'SMITH'", 289 "explain verbose delete from employees where empno = 7698", 290 "explain verbose delete from employees where empno = 7698 and ename = 'BLAKE'", 291 "explain verbose delete from employees where deptno = 20 order by sal limit 2", 292 "explain verbose delete from employees where empno > 7800 order by empno limit 2", 293 "explain verbose delete employees, dept from employees, dept where employees.deptno = dept.deptno and sal > 2000", 294 "explain verbose DELETE FROM employees, dept USING employees INNER JOIN dept WHERE employees.deptno = dept.deptno", 295 } 296 mockOptimizer := plan.NewMockOptimizer(true) 297 runTestShouldPass(mockOptimizer, t, sqls) 298 } 299 300 func TestMultiTableDeleteSQL(t *testing.T) { 301 sqls := []string{ 302 "explain verbose delete emp,dept from emp ,dept where emp.deptno = dept.deptno and emp.deptno = 10", 303 "explain verbose delete emp,dept from emp ,dept where emp.deptno = dept.deptno and sal > 2000", 304 "explain verbose delete t1,t2 from emp as t1,dept as t2 where t1.deptno = t2.deptno and t1.deptno = 10", 305 "explain verbose delete t1,dept from emp as t1,dept where t1.deptno = dept.deptno and t1.deptno = 10", 306 "explain verbose delete emp,dept from emp ,dept where emp.deptno = dept.deptno and empno > 7800", 307 "explain verbose delete emp,dept from emp ,dept where emp.deptno = dept.deptno and empno = 7839", 308 "explain verbose DELETE FROM emp, dept USING emp INNER JOIN dept WHERE emp.deptno = dept.deptno", 309 } 310 mockOptimizer := plan.NewMockOptimizer(true) 311 runTestShouldPass(mockOptimizer, t, sqls) 312 } 313 314 func runTestShouldPass(opt plan.Optimizer, t *testing.T, sqls []string) { 315 for _, sql := range sqls { 316 err := runOneStmt(opt, t, sql) 317 if err != nil { 318 t.Fatalf("%+v", err) 319 } 320 } 321 } 322 323 func runOneStmt(opt plan.Optimizer, t *testing.T, sql string) error { 324 t.Logf("SQL: %v\n", sql) 325 stmts, err := mysql.Parse(opt.CurrentContext().GetContext(), sql, 1, 0) 326 if err != nil { 327 t.Fatalf("%+v", err) 328 } 329 330 ctx := context.TODO() 331 if stmt, ok := stmts[0].(*tree.ExplainStmt); ok { 332 es := NewExplainDefaultOptions() 333 for _, v := range stmt.Options { 334 if strings.EqualFold(v.Name, "VERBOSE") { 335 if strings.EqualFold(v.Value, "TRUE") || v.Value == "NULL" { 336 es.Verbose = true 337 } else if strings.EqualFold(v.Value, "FALSE") { 338 es.Verbose = false 339 } else { 340 return moerr.NewInvalidInput(ctx, "boolean value %v", v.Value) 341 } 342 } else if strings.EqualFold(v.Name, "ANALYZE") { 343 if strings.EqualFold(v.Value, "TRUE") || v.Value == "NULL" { 344 es.Analyze = true 345 } else if strings.EqualFold(v.Value, "FALSE") { 346 es.Analyze = false 347 } else { 348 return moerr.NewInvalidInput(ctx, "boolean value %v", v.Value) 349 } 350 } else if strings.EqualFold(v.Name, "FORMAT") { 351 if v.Name == "NULL" { 352 return moerr.NewInvalidInput(ctx, "parameter name %v", v.Name) 353 } else if strings.EqualFold(v.Value, "TEXT") { 354 es.Format = EXPLAIN_FORMAT_TEXT 355 } else if strings.EqualFold(v.Value, "JSON") { 356 es.Format = EXPLAIN_FORMAT_JSON 357 } else { 358 return moerr.NewInvalidInput(ctx, "explain format %v", v.Value) 359 } 360 } else { 361 return moerr.NewInvalidInput(ctx, "EXPLAIN option %v", v.Name) 362 } 363 } 364 365 // this sql always return one stmt 366 ctx := opt.CurrentContext() 367 logicPlan, err := plan.BuildPlan(ctx, stmt.Statement, false) 368 if err != nil { 369 t.Errorf("Build Query Plan error: '%v'", tree.String(stmt, dialect.MYSQL)) 370 return err 371 } 372 buffer := NewExplainDataBuffer() 373 explainQuery := NewExplainQueryImpl(logicPlan.GetQuery()) 374 err = explainQuery.ExplainPlan(ctx.GetContext(), buffer, es) 375 if err != nil { 376 t.Errorf("explain Query Plan error: '%v'", tree.String(stmt, dialect.MYSQL)) 377 return err 378 } 379 t.Logf("\n" + buffer.ToString()) 380 } 381 return nil 382 }