github.com/matrixorigin/matrixone@v1.2.0/pkg/sql/plan/explain/marshal_query_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 "bytes" 19 "context" 20 "encoding/json" 21 "fmt" 22 "testing" 23 24 "github.com/google/uuid" 25 plan2 "github.com/matrixorigin/matrixone/pkg/pb/plan" 26 "github.com/matrixorigin/matrixone/pkg/sql/parsers/dialect/mysql" 27 "github.com/matrixorigin/matrixone/pkg/sql/plan" 28 ) 29 30 func TestGetLabelOrTitle(t *testing.T) { 31 m := MarshalNodeImpl{} 32 errCount := 0 33 for _, v := range plan2.Node_NodeType_value { 34 node := &plan2.Node{ 35 NodeType: plan2.Node_NodeType(v), 36 NodeId: 0, 37 InsertCtx: &plan2.InsertCtx{ 38 Ref: &plan2.ObjectRef{}, 39 }, 40 Children: []int32{0, 1}, 41 } 42 m.node = node 43 opt := &ExplainOptions{ 44 Verbose: false, 45 Analyze: false, 46 Format: EXPLAIN_FORMAT_TEXT, 47 NodeType: plan2.Node_NodeType(v), 48 } 49 _, e := m.GetNodeTitle(context.TODO(), opt) 50 if e != nil && e.Error() == "internal error: "+errUnsupportedNodeType { 51 errCount = errCount + 1 52 fmt.Printf("you should add title for node=%s\n", plan2.Node_NodeType_name[v]) 53 } 54 55 _, e = m.GetNodeLabels(context.TODO(), opt) 56 if e != nil && e.Error() == "internal error: "+errUnsupportedNodeType { 57 errCount = errCount + 1 58 fmt.Printf("you should add label for node=%s\n", plan2.Node_NodeType_name[v]) 59 } 60 } 61 if errCount > 0 { 62 t.Fatalf("you should add title/label for some nodes") 63 } 64 } 65 66 func TestSimpleQueryToJson(t *testing.T) { 67 //input := "select * from part where p_container in ('SM CASE', 'SM BOX', 'SM PACK', 'SM PKG')" 68 //input := "SELECT DISTINCT N_NAME FROM NATION" 69 //input := "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" 70 sqls := []string{ 71 "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 "SELECT N_NAME, N_REGIONKEY a FROM NATION WHERE N_REGIONKEY > 0 ORDER BY a DESC", //test alias 73 "SELECT N_NAME, count(distinct N_REGIONKEY) FROM NATION group by N_NAME", //test distinct agg function 74 "SELECT N_NAME, MAX(N_REGIONKEY) FROM NATION GROUP BY N_NAME HAVING MAX(N_REGIONKEY) > 10", //test agg 75 "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 76 "SELECT N_REGIONKEY FROM NATION where N_REGIONKEY >= N_NATIONKEY or (N_NAME like '%ddd' and N_REGIONKEY >0.5)", //test more expr 77 "SELECT N_NAME,N_REGIONKEY FROM NATION join REGION on NATION.N_REGIONKEY = REGION.R_REGIONKEY", 78 "SELECT N_NAME, N_REGIONKEY FROM NATION join REGION on NATION.N_REGIONKEY = REGION.R_REGIONKEY WHERE NATION.N_REGIONKEY > 0", 79 "SELECT N_NAME, NATION2.R_REGIONKEY FROM NATION2 join REGION using(R_REGIONKEY) WHERE NATION2.R_REGIONKEY > 0", 80 "select n_name from nation intersect all select n_name from nation2", 81 "select n_name from nation minus select n_name from nation2", 82 "select 1 union select 2 intersect select 2 union all select 1.1 minus select 22222", 83 "SELECT N_NAME, MAX(N_REGIONKEY) FROM NATION GROUP BY N_NAME HAVING MAX(N_REGIONKEY) > 10", 84 "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", 85 "SELECT N_NAME, N_REGIONKEY FROM NATION join REGION on NATION.N_REGIONKEY = REGION.R_REGIONKEY WHERE NATION.N_REGIONKEY > 0", 86 "select col1 from (select c_custkey, count(C_NATIONKEY) ff from CUSTOMER group by c_custkey ) a(col1, col2) where col2 > 0 order by col1", 87 "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", 88 } 89 90 mock := plan.NewMockOptimizer(false) 91 buildPlanMarshalTest(mock, t, sqls) 92 } 93 94 func TestSingleTableQueryToJson(t *testing.T) { 95 sqls := []string{ 96 "SELECT '1900-01-01 00:00:00' + INTERVAL 2147483648 SECOND", 97 "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", 98 "SELECT N_NAME, N_REGIONKEY a FROM NATION WHERE N_REGIONKEY > 0 ORDER BY a DESC", //test alias 99 "SELECT NATION.N_NAME FROM NATION", //test alias 100 "SELECT * FROM NATION", //test star 101 "SELECT a.* FROM NATION a", //test star 102 "SELECT count(*) FROM NATION", //test star 103 "SELECT count(*) FROM NATION group by N_NAME", //test star 104 "SELECT N_NAME, count(distinct N_REGIONKEY) FROM NATION group by N_NAME", //test distinct agg function 105 "SELECT N_NAME, MAX(N_REGIONKEY) FROM NATION GROUP BY N_NAME HAVING MAX(N_REGIONKEY) > 10", //test agg 106 "SELECT DISTINCT N_NAME FROM NATION", //test distinct 107 "select sum(n_nationkey) as s from nation order by s", 108 "select date_add(date '2001-01-01', interval 1 day) as a", 109 "select date_sub(date '2001-01-01', interval '1' day) as a", 110 "select date_add('2001-01-01', interval '1' day) as a", 111 "select n_name, count(*) from nation group by n_name order by 2 asc", 112 "select count(distinct 12)", 113 "select nullif(n_name, n_comment), ifnull(n_comment, n_name) from nation", 114 115 "select 18446744073709551500", 116 "select 0xffffffffffffffff", 117 "select 0xffff", 118 119 "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 120 "SELECT N_REGIONKEY FROM NATION where N_REGIONKEY >= N_NATIONKEY or (N_NAME like '%ddd' and N_REGIONKEY >0.5)", //test more expr 121 "SELECT N_REGIONKEY FROM NATION where N_REGIONKEY between 2 and 2 OR N_NATIONKEY not between 3 and 10", //test more expr 122 // "SELECT N_REGIONKEY FROM NATION where N_REGIONKEY is null and N_NAME is not null", 123 "SELECT N_REGIONKEY FROM NATION where N_REGIONKEY IN (1, 2)", //test more expr 124 "SELECT N_REGIONKEY FROM NATION where N_REGIONKEY NOT IN (1)", //test more expr 125 "select N_REGIONKEY from nation group by N_REGIONKEY having abs(nation.N_REGIONKEY - 1) >10", 126 127 //"SELECT -1", 128 //"select date_add('1997-12-31 23:59:59',INTERVAL 100000 SECOND)", 129 //"select date_sub('1997-12-31 23:59:59',INTERVAL 2 HOUR)", 130 //"select @str_var, @int_var, @bool_var, @float_var, @null_var", 131 //"select @str_var, @@global.int_var, @@session.bool_var", 132 //"select n_name from nation where n_name != @str_var and n_regionkey > @int_var", 133 //"select n_name from nation where n_name != @@global.str_var and n_regionkey > @@session.int_var", 134 //"select distinct(n_name), ((abs(n_regionkey))) from nation", 135 //"SET @var = abs(-1), @@session.string_var = 'aaa'", 136 //"SET NAMES 'utf8mb4' COLLATE 'utf8mb4_general_ci'", 137 //"SELECT DISTINCT N_NAME FROM NATION ORDER BY N_NAME", //test distinct with order by 138 // 139 //"prepare stmt1 from select * from nation", 140 //"prepare stmt1 from select * from nation where n_name = ?", 141 //"prepare stmt1 from 'select * from nation where n_name = ?'", 142 //"prepare stmt1 from 'update nation set n_name = ? where n_nationkey > ?'", 143 //"prepare stmt1 from 'delete from nation where n_nationkey > ?'", 144 //"prepare stmt1 from 'insert into nation select * from nation2 where n_name = ?'", 145 //"prepare stmt1 from 'select * from nation where n_name = ?'", 146 //"prepare stmt1 from 'drop table if exists t1'", 147 //"prepare stmt1 from 'create table t1 (a int)'", 148 //"prepare stmt1 from select N_REGIONKEY from nation group by N_REGIONKEY having abs(nation.N_REGIONKEY - ?) > ?", 149 //"execute stmt1", 150 //"execute stmt1 using @str_var, @@global.int_var", 151 //"deallocate prepare stmt1", 152 //"drop prepare stmt1", 153 //"select count(n_name) from nation", 154 //"select l_shipdate + interval '1' day from lineitem", 155 //"select interval '1' day + l_shipdate from lineitem", 156 //"select interval '1' day + cast('2022-02-02 00:00:00' as datetime)", 157 //"select cast('2022-02-02 00:00:00' as datetime) + interval '1' day", 158 //"delete from nation", 159 //"delete nation, nation2 from nation join nation2 on nation.n_name = nation2.n_name", 160 } 161 mock := plan.NewMockOptimizer(false) 162 buildPlanMarshalTest(mock, t, sqls) 163 } 164 165 func TestJoinQueryToJson(t *testing.T) { 166 sqls := []string{ 167 "SELECT N_NAME,N_REGIONKEY FROM NATION join REGION on NATION.N_REGIONKEY = REGION.R_REGIONKEY", 168 "SELECT N_NAME, N_REGIONKEY FROM NATION join REGION on NATION.N_REGIONKEY = REGION.R_REGIONKEY WHERE NATION.N_REGIONKEY > 0", 169 "SELECT N_NAME, NATION2.R_REGIONKEY FROM NATION2 join REGION using(R_REGIONKEY) WHERE NATION2.R_REGIONKEY > 0", 170 "SELECT N_NAME, NATION2.R_REGIONKEY FROM NATION2 NATURAL JOIN REGION WHERE NATION2.R_REGIONKEY > 0", 171 "SELECT N_NAME FROM NATION NATURAL JOIN REGION", //have no same column name but it's ok 172 "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 173 "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 174 "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 175 "SELECT * FROM CUSTOMER c, ORDERS o, LINEITEM l WHERE c.C_CUSTKEY = o.O_CUSTKEY and l.L_ORDERKEY = o.O_ORDERKEY", //test star 176 "SELECT a.* FROM NATION a join REGION b on a.N_REGIONKEY = b.R_REGIONKEY WHERE a.N_REGIONKEY > 0", //test star 177 "SELECT * FROM NATION a join REGION b on a.N_REGIONKEY = b.R_REGIONKEY WHERE a.N_REGIONKEY > 0", 178 "SELECT N_NAME, R_REGIONKEY FROM NATION2 join REGION using(R_REGIONKEY)", 179 "select nation.n_name from nation join nation2 on nation.n_name !='a' join region on nation.n_regionkey = region.r_regionkey", 180 "select n_name from nation intersect select n_name from nation2", 181 "select n_name from nation minus select n_name from nation2", 182 } 183 mock := plan.NewMockOptimizer(false) 184 buildPlanMarshalTest(mock, t, sqls) 185 } 186 187 func TestNestedQueryToJson(t *testing.T) { 188 sqls := []string{ 189 "SELECT * FROM NATION where N_REGIONKEY > (select max(R_REGIONKEY) from REGION)", // unrelated 190 "SELECT * FROM NATION where N_REGIONKEY in (select max(R_REGIONKEY) from REGION)", // unrelated 191 "SELECT * FROM NATION where N_REGIONKEY not in (select max(R_REGIONKEY) from REGION)", // unrelated 192 "SELECT * FROM NATION where exists (select max(R_REGIONKEY) from REGION)", // unrelated 193 "SELECT * FROM NATION where N_REGIONKEY > (select max(R_REGIONKEY) from REGION where R_REGIONKEY = N_REGIONKEY)", // related 194 //"SELECT * FROM NATION where N_REGIONKEY > (select max(R_REGIONKEY) from REGION where R_REGIONKEY < N_REGIONKEY)", // related 195 //"DELETE FROM NATION WHERE N_NATIONKEY > 10", 196 `select 197 sum(l_extendedprice) / 7.0 as avg_yearly 198 from 199 lineitem, 200 part 201 where 202 p_partkey = l_partkey 203 and p_brand = 'Brand#54' 204 and p_container = 'LG BAG' 205 and l_quantity < ( 206 select 207 0.2 * avg(l_quantity) 208 from 209 lineitem 210 where 211 l_partkey = p_partkey 212 );`, //tpch q17 213 } 214 mock := plan.NewMockOptimizer(false) 215 buildPlanMarshalTest(mock, t, sqls) 216 } 217 218 func TestCollectionQueryToJson(t *testing.T) { 219 sqls := []string{ 220 "select 1 union select 2", 221 "select 1 union (select 2 union select 3)", 222 "(select 1 union select 2) union select 3 intersect select 4 order by 1", 223 "select 1 union select null", 224 "select n_name from nation intersect select n_name from nation2", 225 "select n_name from nation minus select n_name from nation2", 226 "select 1 union select 2 intersect select 2 union all select 1.1 minus select 22222", 227 "select 1 as a union select 2 order by a limit 1", 228 "select n_name from nation union select n_comment from nation order by n_name", 229 "with qn (foo, bar) as (select 1 as col, 2 as coll union select 4, 5) select qn1.bar from qn qn1", 230 "select n_name, n_comment from nation union all select n_name, n_comment from nation2", 231 "select n_name from nation intersect all select n_name from nation2", 232 "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'", 233 "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'", 234 } 235 236 mock := plan.NewMockOptimizer(false) 237 buildPlanMarshalTest(mock, t, sqls) 238 } 239 240 func TestDerivedTableQueryToJson(t *testing.T) { 241 // should pass 242 sqls := []string{ 243 "select c_custkey from (select c_custkey from CUSTOMER ) a", 244 "select c_custkey from (select c_custkey from CUSTOMER group by c_custkey ) a", 245 "select col1 from (select c_custkey from CUSTOMER group by c_custkey ) a(col1)", 246 "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", 247 "select col1 from (select c_custkey, count(C_NATIONKEY) ff from CUSTOMER group by c_custkey ) a(col1, col2) where col2 > 0 order by col1", 248 "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", 249 "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", 250 "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", 251 } 252 mock := plan.NewMockOptimizer(false) 253 buildPlanMarshalTest(mock, t, sqls) 254 } 255 256 func TestDMLToJson(t *testing.T) { 257 sqls := []string{ 258 "INSERT INTO NATION SELECT * FROM NATION2", 259 "UPDATE NATION SET N_NAME ='U1', N_REGIONKEY=N_REGIONKEY+2 WHERE N_NATIONKEY > 10 LIMIT 20", 260 "UPDATE NATION,NATION2 SET NATION.N_NAME ='U1',NATION2.N_NATIONKEY=15 WHERE NATION.N_NATIONKEY = NATION2.N_NATIONKEY", 261 "DELETE FROM NATION WHERE N_NATIONKEY > 10", 262 "DELETE FROM a1, a2 USING NATION AS a1 INNER JOIN NATION2 AS a2 WHERE a1.N_NATIONKEY=a2.N_NATIONKEY", 263 } 264 mock := plan.NewMockOptimizer(true) 265 buildPlanMarshalTest(mock, t, sqls) 266 } 267 268 func buildPlanMarshalTest(opt plan.Optimizer, t *testing.T, sqls []string) { 269 ctx := context.TODO() 270 for _, sql := range sqls { 271 t.Logf("sql: %s \n", sql) 272 plan, err := runSingleSql(opt, t, sql) 273 if err != nil { 274 t.Fatalf("%+v", err) 275 } 276 queryPlan := plan.GetQuery() 277 for _, node := range queryPlan.Nodes { 278 info := plan2.AnalyzeInfo{ 279 InputRows: 12, 280 OutputRows: 12, 281 TimeConsumed: 5, 282 InputSize: 24, 283 OutputSize: 24, 284 MemorySize: 10, 285 } 286 node.AnalyzeInfo = &info 287 } 288 289 marshalPlan := BuildJsonPlan(ctx, uuid.New(), &MarshalPlanOptions, queryPlan) 290 //marshal, err := json.Marshal(marshalPlan) 291 292 buffer := &bytes.Buffer{} 293 encoder := json.NewEncoder(buffer) 294 encoder.SetEscapeHTML(false) 295 err = encoder.Encode(marshalPlan) 296 297 if err != nil { 298 panic(err) 299 } 300 t.Logf("SQL plan to json : %s\n", buffer.String()) 301 302 } 303 } 304 305 func runSingleSql(opt plan.Optimizer, t *testing.T, sql string) (*plan.Plan, error) { 306 stmts, err := mysql.Parse(opt.CurrentContext().GetContext(), sql, 1, 0) 307 if err != nil { 308 t.Fatalf("%+v", err) 309 } 310 // this sql always return one stmt 311 ctx := opt.CurrentContext() 312 return plan.BuildPlan(ctx, stmts[0], false) 313 }