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