github.com/matrixorigin/matrixone@v0.7.0/pkg/sql/plan/build_test.go

// Copyright 2021 - 2022 Matrix Origin
//
// Licensed under the Apache License, Version 2.0 (the "License");
// you may not use this file except in compliance with the License.
// You may obtain a copy of the License at
//
//      http://www.apache.org/licenses/LICENSE-2.0
//
// Unless required by applicable law or agreed to in writing, software
// distributed under the License is distributed on an "AS IS" BASIS,
// WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
// See the License for the specific language governing permissions and
// limitations under the License.

package plan

import (
	"bytes"
	"context"
	"encoding/json"
	"github.com/stretchr/testify/assert"
	"os"
	"strings"
	"testing"

	"github.com/matrixorigin/matrixone/pkg/vm/process"

	"github.com/matrixorigin/matrixone/pkg/pb/plan"
	"github.com/matrixorigin/matrixone/pkg/sql/parsers/dialect/mysql"
)

// only use in developing
func TestSingleSQL(t *testing.T) {
	sql := "select * from nation"

	mock := NewMockOptimizer(false)
	logicPlan, err := runOneStmt(mock, t, sql)
	if err != nil {
		t.Fatalf("%+v", err)
	}
	outPutPlan(logicPlan, true, t)
}

//Test Query Node Tree
// func TestNodeTree(t *testing.T) {
// 	type queryCheck struct {
// 		steps    []int32                    //steps
// 		nodeType map[int]plan.Node_NodeType //node_type in each node
// 		children map[int][]int32            //children in each node
// 	}

// 	// map[sql string]checkData
// 	nodeTreeCheckList := map[string]queryCheck{
// 		"SELECT -1": {
// 			steps: []int32{0},
// 			nodeType: map[int]plan.Node_NodeType{
// 				0: plan.Node_VALUE_SCAN,
// 			},
// 			children: nil,
// 		},
// 		"SELECT -1 from dual": {
// 			steps: []int32{0},
// 			nodeType: map[int]plan.Node_NodeType{
// 				0: plan.Node_VALUE_SCAN,
// 			},
// 			children: nil,
// 		},
// 		// one node
// 		"SELECT N_NAME FROM NATION WHERE N_REGIONKEY = 3": {
// 			steps: []int32{0},
// 			nodeType: map[int]plan.Node_NodeType{
// 				0: plan.Node_TABLE_SCAN,
// 			},
// 			children: nil,
// 		},
// 		// two nodes- SCAN + SORT
// 		"SELECT N_NAME FROM NATION WHERE N_REGIONKEY = 3 Order By N_REGIONKEY": {
// 			steps: []int32{1},
// 			nodeType: map[int]plan.Node_NodeType{
// 				0: plan.Node_TABLE_SCAN,
// 				1: plan.Node_SORT,
// 			},
// 			children: map[int][]int32{
// 				1: {0},
// 			},
// 		},
// 		// two nodes- SCAN + AGG(group by)
// 		"SELECT N_NAME FROM NATION WHERE N_REGIONKEY = 3 Group By N_NAME": {
// 			steps: []int32{1},
// 			nodeType: map[int]plan.Node_NodeType{
// 				0: plan.Node_TABLE_SCAN,
// 				1: plan.Node_AGG,
// 			},
// 			children: map[int][]int32{
// 				1: {0},
// 			},
// 		},
// 		"select sum(n_nationkey) from nation": {
// 			steps: []int32{1},
// 			nodeType: map[int]plan.Node_NodeType{
// 				0: plan.Node_TABLE_SCAN,
// 				1: plan.Node_AGG,
// 			},
// 			children: map[int][]int32{
// 				1: {0},
// 			},
// 		},
// 		"select sum(n_nationkey) from nation order by sum(n_nationkey)": {
// 			steps: []int32{2},
// 			nodeType: map[int]plan.Node_NodeType{
// 				0: plan.Node_TABLE_SCAN,
// 				1: plan.Node_AGG,
// 				2: plan.Node_SORT,
// 			},
// 			children: map[int][]int32{
// 				1: {0},
// 				2: {1},
// 			},
// 		},
// 		// two nodes- SCAN + AGG(distinct)
// 		"SELECT distinct N_NAME FROM NATION": {
// 			steps: []int32{1},
// 			nodeType: map[int]plan.Node_NodeType{
// 				0: plan.Node_TABLE_SCAN,
// 				1: plan.Node_AGG,
// 			},
// 			children: map[int][]int32{
// 				1: {0},
// 			},
// 		},
// 		// three nodes- SCAN + AGG(group by) + SORT
// 		"SELECT N_NAME, count(*) as ttl FROM NATION Group By N_NAME Order By ttl": {
// 			steps: []int32{2},
// 			nodeType: map[int]plan.Node_NodeType{
// 				0: plan.Node_TABLE_SCAN,
// 				1: plan.Node_AGG,
// 				2: plan.Node_SORT,
// 			},
// 			children: map[int][]int32{
// 				1: {0},
// 				2: {1},
// 			},
// 		},
// 		// three nodes - SCAN, SCAN, JOIN
// 		"SELECT N_NAME, N_REGIONKEY FROM NATION join REGION on NATION.N_REGIONKEY = REGION.R_REGIONKEY": {
// 			steps: []int32{3},
// 			nodeType: map[int]plan.Node_NodeType{
// 				0: plan.Node_TABLE_SCAN,
// 				1: plan.Node_TABLE_SCAN,
// 				2: plan.Node_JOIN,
// 				3: plan.Node_PROJECT,
// 			},
// 			children: map[int][]int32{
// 				2: {0, 1},
// 			},
// 		},
// 		// three nodes - SCAN, SCAN, JOIN  //use where for join condition
// 		"SELECT N_NAME, N_REGIONKEY FROM NATION, REGION WHERE NATION.N_REGIONKEY = REGION.R_REGIONKEY": {
// 			steps: []int32{3},
// 			nodeType: map[int]plan.Node_NodeType{
// 				0: plan.Node_TABLE_SCAN,
// 				1: plan.Node_TABLE_SCAN,
// 				2: plan.Node_JOIN,
// 				3: plan.Node_PROJECT,
// 			},
// 			children: map[int][]int32{
// 				2: {0, 1},
// 				3: {2},
// 			},
// 		},
// 		// 5 nodes - SCAN, SCAN, JOIN, SCAN, JOIN  //join three table
// 		"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_ORDERKEY < 10": {
// 			steps: []int32{6},
// 			nodeType: map[int]plan.Node_NodeType{
// 				0: plan.Node_TABLE_SCAN,
// 				1: plan.Node_TABLE_SCAN,
// 				2: plan.Node_JOIN,
// 				3: plan.Node_PROJECT,
// 				4: plan.Node_TABLE_SCAN,
// 				5: plan.Node_JOIN,
// 				6: plan.Node_PROJECT,
// 			},
// 			children: map[int][]int32{
// 				2: {0, 1},
// 				3: {2},
// 				5: {3, 4},
// 				6: {5},
// 			},
// 		},
// 		// 6 nodes - SCAN, SCAN, JOIN, SCAN, JOIN, SORT  //join three table
// 		"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_ORDERKEY < 10 order by c.C_CUSTKEY": {
// 			steps: []int32{7},
// 			nodeType: map[int]plan.Node_NodeType{
// 				0: plan.Node_TABLE_SCAN,
// 				1: plan.Node_TABLE_SCAN,
// 				2: plan.Node_JOIN,
// 				3: plan.Node_PROJECT,
// 				4: plan.Node_TABLE_SCAN,
// 				5: plan.Node_JOIN,
// 				6: plan.Node_PROJECT,
// 				7: plan.Node_SORT,
// 			},
// 			children: map[int][]int32{
// 				2: {0, 1},
// 				3: {2},
// 				5: {3, 4},
// 				6: {5},
// 				7: {6},
// 			},
// 		},
// 		// 3 nodes  //Derived table
// 		"select c_custkey from (select c_custkey, count(C_NATIONKEY) ff from CUSTOMER group by c_custkey) a where ff > 0": {
// 			steps: []int32{2},
// 			nodeType: map[int]plan.Node_NodeType{
// 				0: plan.Node_TABLE_SCAN,
// 				1: plan.Node_AGG,
// 				2: plan.Node_PROJECT,
// 			},
// 			children: map[int][]int32{
// 				1: {0},
// 				2: {1},
// 			},
// 		},
// 		// 4 nodes  //Derived table
// 		"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": {
// 			steps: []int32{3},
// 			nodeType: map[int]plan.Node_NodeType{
// 				0: plan.Node_TABLE_SCAN,
// 				1: plan.Node_AGG,
// 				2: plan.Node_PROJECT,
// 				3: plan.Node_SORT,
// 			},
// 			children: map[int][]int32{
// 				1: {0},
// 				2: {1},
// 				3: {2},
// 			},
// 		},
// 		// Derived table join normal table
// 		"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 order By b.N_REGIONKEY": {
// 			steps: []int32{6},
// 			nodeType: map[int]plan.Node_NodeType{
// 				0: plan.Node_TABLE_SCAN,
// 				1: plan.Node_AGG,
// 				2: plan.Node_PROJECT,
// 				3: plan.Node_TABLE_SCAN,
// 				4: plan.Node_JOIN,
// 				5: plan.Node_PROJECT,
// 				6: plan.Node_SORT,
// 			},
// 			children: map[int][]int32{
// 				1: {0},
// 				2: {1},
// 				4: {2, 3},
// 				5: {4},
// 				6: {5},
// 			},
// 		},
// 		// insert from values
// 		"INSERT NATION (N_NATIONKEY, N_REGIONKEY, N_NAME) VALUES (1, 21, 'NAME1'), (2, 22, 'NAME2')": {
// 			steps: []int32{1},
// 			nodeType: map[int]plan.Node_NodeType{
// 				0: plan.Node_VALUE_SCAN,
// 				1: plan.Node_INSERT,
// 			},
// 			children: map[int][]int32{
// 				1: {0},
// 			},
// 		},
// 		// insert from select
// 		"INSERT NATION SELECT * FROM NATION2": {
// 			steps: []int32{1},
// 			nodeType: map[int]plan.Node_NodeType{
// 				0: plan.Node_TABLE_SCAN,
// 				1: plan.Node_INSERT,
// 			},
// 			children: map[int][]int32{
// 				1: {0},
// 			},
// 		},
// 		// update
// 		"UPDATE NATION SET N_NAME ='U1', N_REGIONKEY=N_REGIONKEY+2 WHERE N_NATIONKEY > 10 LIMIT 20": {
// 			steps: []int32{1},
// 			nodeType: map[int]plan.Node_NodeType{
// 				0: plan.Node_TABLE_SCAN,
// 				1: plan.Node_UPDATE,
// 			},
// 			children: map[int][]int32{
// 				1: {0},
// 			},
// 		},
// 		// delete
// 		"DELETE FROM NATION WHERE N_NATIONKEY > 10 LIMIT 20": {
// 			steps: []int32{1},
// 			nodeType: map[int]plan.Node_NodeType{
// 				0: plan.Node_TABLE_SCAN,
// 				1: plan.Node_DELETE,
// 			},
// 		},
// 		// uncorrelated subquery
// 		"SELECT * FROM NATION where N_REGIONKEY > (select max(R_REGIONKEY) from REGION)": {
// 			steps: []int32{0},
// 			nodeType: map[int]plan.Node_NodeType{
// 				0: plan.Node_TABLE_SCAN, //nodeid = 1  here is the subquery
// 				1: plan.Node_TABLE_SCAN, //nodeid = 0, here is SELECT * FROM NATION where N_REGIONKEY > [subquery]
// 			},
// 			children: map[int][]int32{},
// 		},
// 		// correlated subquery
// 		`SELECT * FROM NATION where N_REGIONKEY >
// 			(select avg(R_REGIONKEY) from REGION where R_REGIONKEY < N_REGIONKEY group by R_NAME)
// 		order by N_NATIONKEY`: {
// 			steps: []int32{3},
// 			nodeType: map[int]plan.Node_NodeType{
// 				0: plan.Node_TABLE_SCAN, //nodeid = 1  subquery node，so,wo pop it to top
// 				1: plan.Node_TABLE_SCAN, //nodeid = 0
// 				2: plan.Node_AGG,        //nodeid = 2  subquery node，so,wo pop it to top
// 				3: plan.Node_SORT,       //nodeid = 3
// 			},
// 			children: map[int][]int32{
// 				2: {1}, //nodeid = 2, have children(NodeId=1, position=0)
// 				3: {0}, //nodeid = 3, have children(NodeId=0, position=2)
// 			},
// 		},
// 		// cte
// 		`with tbl(col1, col2) as (select n_nationkey, n_name from nation) select * from tbl order by col2`: {
// 			steps: []int32{1, 3},
// 			nodeType: map[int]plan.Node_NodeType{
// 				0: plan.Node_TABLE_SCAN,
// 				1: plan.Node_MATERIAL,
// 				2: plan.Node_MATERIAL_SCAN,
// 				3: plan.Node_SORT,
// 			},
// 			children: map[int][]int32{
// 				1: {0},
// 				3: {2},
// 			},
// 		},
// 	}

// 	// run test and check node tree
// 	for sql, check := range nodeTreeCheckList {
// 		mock := NewMockOptimizer(false)
// 		logicPlan, err := runOneStmt(mock, t, sql)
// 		query := logicPlan.GetQuery()
// 		if err != nil {
// 			t.Fatalf("%+v, sql=%v", err, sql)
// 		}
// 		if len(query.Steps) != len(check.steps) {
// 			t.Fatalf("run sql[%+v] error, root should be [%+v] but now is [%+v]", sql, check.steps, query.Steps)
// 		}
// 		for idx, step := range query.Steps {
// 			if step != check.steps[idx] {
// 				t.Fatalf("run sql[%+v] error, root should be [%+v] but now is [%+v]", sql, check.steps, query.Steps)
// 			}
// 		}
// 		for idx, typ := range check.nodeType {
// 			if idx >= len(query.Nodes) {
// 				t.Fatalf("run sql[%+v] error, query.Nodes[%+v].NodeType not exist", sql, idx)
// 			}
// 			if query.Nodes[idx].NodeType != typ {
// 				t.Fatalf("run sql[%+v] error, query.Nodes[%+v].NodeType should be [%+v] but now is [%+v]", sql, idx, typ, query.Nodes[idx].NodeType)
// 			}
// 		}
// 		for idx, children := range check.children {
// 			if idx >= len(query.Nodes) {
// 				t.Fatalf("run sql[%+v] error, query.Nodes[%+v].NodeType not exist", sql, idx)
// 			}
// 			if !reflect.DeepEqual(query.Nodes[idx].Children, children) {
// 				t.Fatalf("run sql[%+v] error, query.Nodes[%+v].Children should be [%+v] but now is [%+v]", sql, idx, children, query.Nodes[idx].Children)
// 			}
// 		}
// 	}
// }

// test single table plan building
func TestSingleTableSQLBuilder(t *testing.T) {
	mock := NewMockOptimizer(false)

	// should pass
	sqls := []string{
		"SELECT '1900-01-01 00:00:00' + INTERVAL 2147483648 SECOND",
		"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",
		"SELECT N_NAME, N_REGIONKEY a FROM NATION WHERE N_REGIONKEY > 0 ORDER BY a DESC", //test alias
		"SELECT NATION.N_NAME FROM NATION",                                       //test alias
		"SELECT * FROM NATION",                                                   //test star
		"SELECT a.* FROM NATION a",                                               //test star
		"SELECT count(*) FROM NATION",                                            //test star
		"SELECT count(*) FROM NATION group by N_NAME",                            //test star
		"SELECT N_NAME, count(distinct N_REGIONKEY) FROM NATION group by N_NAME", //test distinct agg function
		"SELECT N_NAME, MAX(N_REGIONKEY) FROM NATION GROUP BY N_NAME HAVING MAX(N_REGIONKEY) > 10", //test agg
		"SELECT DISTINCT N_NAME FROM NATION", //test distinct
		"select sum(n_nationkey) as s from nation order by s",
		"select date_add(date '2001-01-01', interval 1 day) as a",
		"select date_sub(date '2001-01-01', interval '1' day) as a",
		"select date_add('2001-01-01', interval '1' day) as a",
		"select n_name, count(*) from nation group by n_name order by 2 asc",
		"select count(distinct 12)",
		"select nullif(n_name, n_comment), ifnull(n_comment, n_name) from nation",

		"select 18446744073709551500",
		"select 0xffffffffffffffff",
		"select 0xffff",

		"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
		"SELECT N_REGIONKEY FROM NATION where N_REGIONKEY >= N_NATIONKEY or (N_NAME like '%ddd' and N_REGIONKEY >0.5)",                                                    //test more expr
		"SELECT N_REGIONKEY FROM NATION where N_REGIONKEY between 2 and 2 OR N_NATIONKEY not between 3 and 10",                                                            //test more expr
		// "SELECT N_REGIONKEY FROM NATION where N_REGIONKEY is null and N_NAME is not null",
		"SELECT N_REGIONKEY FROM NATION where N_REGIONKEY IN (1, 2)",  //test more expr
		"SELECT N_REGIONKEY FROM NATION where N_REGIONKEY NOT IN (1)", //test more expr
		"select N_REGIONKEY from nation group by N_REGIONKEY having abs(nation.N_REGIONKEY - 1) >10",

		"SELECT -1",
		"select date_add('1997-12-31 23:59:59',INTERVAL 100000 SECOND)",
		"select date_sub('1997-12-31 23:59:59',INTERVAL 2 HOUR)",
		"select @str_var, @int_var, @bool_var, @float_var, @null_var",
		"select @str_var, @@global.int_var, @@session.bool_var",
		"select n_name from nation where n_name != @str_var and n_regionkey > @int_var",
		"select n_name from nation where n_name != @@global.str_var and n_regionkey > @@session.int_var",
		"select distinct(n_name), ((abs(n_regionkey))) from nation",
		"SET @var = abs(-1), @@session.string_var = 'aaa'",
		"SET NAMES 'utf8mb4' COLLATE 'utf8mb4_general_ci'",
		"SELECT DISTINCT N_NAME FROM NATION ORDER BY N_NAME", //test distinct with order by

		"prepare stmt1 from select * from nation",
		"prepare stmt1 from select * from nation where n_name = ?",
		"prepare stmt1 from 'select * from nation where n_name = ?'",
		"prepare stmt1 from 'insert into nation select * from nation2 where n_name = ?'",
		"prepare stmt1 from 'select * from nation where n_name = ?'",
		"prepare stmt1 from 'drop table if exists t1'",
		"prepare stmt1 from 'create table t1 (a int)'",
		"prepare stmt1 from select N_REGIONKEY from nation group by N_REGIONKEY having abs(nation.N_REGIONKEY - ?) > ?",
		"execute stmt1",
		"execute stmt1 using @str_var, @@global.int_var",
		"deallocate prepare stmt1",
		"drop prepare stmt1",
		"select count(n_name) from nation limit 10",
		"select l_shipdate + interval '1' day from lineitem",
		"select interval '1' day + l_shipdate  from lineitem",
		"select interval '1' day + cast('2022-02-02 00:00:00' as datetime)",
		"select cast('2022-02-02 00:00:00' as datetime) + interval '1' day",
		"select true is unknown",
		"select null is not unknown",
		"select 1 as c,  1/2, abs(-2)",

		"select date('2022-01-01'), adddate(time'00:00:00', interval 1 day), subdate(time'00:00:00', interval 1 week), '2007-01-01' + interval 1 month, '2007-01-01' -  interval 1 hour",
		"select 2222332222222223333333333333333333, 0x616263,-10, bit_and(2), bit_or(2), bit_xor(10.1), 'aaa' like '%a',str_to_date('04/31/2004', '%m/%d/%Y'),unix_timestamp(from_unixtime(2147483647))",
		"select max(n_nationkey) over  (partition by N_REGIONKEY) from nation",
		"select * from generate_series(1, 5) g",
		"select * from nation where n_name like ? or n_nationkey > 10 order by 2 limit '10'",

		"values row(1,1), row(2,2), row(3,3) order by column_0 limit 2",
		"select * from (values row(1,1), row(2,2), row(3,3)) a (c1, c2)",
	}
	runTestShouldPass(mock, t, sqls, false, false)

	// should error
	sqls = []string{
		"SELECT N_NAME, N_REGIONKEY FROM table_not_exist",                   //table not exist
		"SELECT N_NAME, column_not_exist FROM NATION",                       //column not exist
		"SELECT N_NAME, N_REGIONKEY a FROM NATION ORDER BY cccc",            //column alias not exist
		"SELECT N_NAME, b.N_REGIONKEY FROM NATION a ORDER BY b.N_REGIONKEY", //table alias not exist
		"SELECT N_NAME FROM NATION WHERE ffff(N_REGIONKEY) > 0",             //function name not exist
		"SELECT NATION.N_NAME FROM NATION a",                                // mysql should error, but i don't think it is necesssary
		"select n_nationkey, sum(n_nationkey) from nation",
		"SET @var = abs(a)", // can't use column
		"SET @var = avg(2)", // can't use agg function

		"SELECT DISTINCT N_NAME FROM NATION GROUP BY N_REGIONKEY", //test distinct with group by
		"SELECT DISTINCT N_NAME FROM NATION ORDER BY N_REGIONKEY", //test distinct with order by
		//"select 18446744073709551500",                             //over int64
		//"select 0xffffffffffffffff",                               //over int64
	}
	runTestShouldError(mock, t, sqls)
}

// test join table plan building
func TestJoinTableSqlBuilder(t *testing.T) {
	mock := NewMockOptimizer(false)

	// should pass
	sqls := []string{
		"SELECT N_NAME,N_REGIONKEY FROM NATION join REGION on NATION.N_REGIONKEY = REGION.R_REGIONKEY",
		"SELECT N_NAME, N_REGIONKEY FROM NATION join REGION on NATION.N_REGIONKEY = REGION.R_REGIONKEY WHERE NATION.N_REGIONKEY > 0",
		"SELECT N_NAME, NATION2.R_REGIONKEY FROM NATION2 join REGION using(R_REGIONKEY) WHERE NATION2.R_REGIONKEY > 0",
		"SELECT N_NAME, NATION2.R_REGIONKEY FROM NATION2 NATURAL JOIN REGION WHERE NATION2.R_REGIONKEY > 0",
		"SELECT N_NAME FROM NATION NATURAL JOIN REGION",                                                                                                     //have no same column name but it's ok
		"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
		"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
		"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
		"SELECT * FROM CUSTOMER c, ORDERS o, LINEITEM l WHERE c.C_CUSTKEY = o.O_CUSTKEY and l.L_ORDERKEY = o.O_ORDERKEY",                                    //test star
		"SELECT a.* FROM NATION a join REGION b on a.N_REGIONKEY = b.R_REGIONKEY WHERE a.N_REGIONKEY > 0",                                                   //test star
		"SELECT * FROM NATION a join REGION b on a.N_REGIONKEY = b.R_REGIONKEY WHERE a.N_REGIONKEY > 0",
		"SELECT N_NAME, R_REGIONKEY FROM NATION2 join REGION using(R_REGIONKEY)",
		"select nation.n_name from nation join nation2 on nation.n_name !='a' join region on nation.n_regionkey = region.r_regionkey",
		"select * from nation, nation2, region",
	}
	runTestShouldPass(mock, t, sqls, false, false)

	// should error
	sqls = []string{
		"SELECT N_NAME,N_REGIONKEY FROM NATION join REGION on NATION.N_REGIONKEY = REGION.NotExistColumn",                    //column not exist
		"SELECT N_NAME, R_REGIONKEY FROM NATION join REGION using(R_REGIONKEY)",                                              //column not exist
		"SELECT N_NAME,N_REGIONKEY FROM NATION a join REGION b on a.N_REGIONKEY = b.R_REGIONKEY WHERE aaaaa.N_REGIONKEY > 0", //table alias not exist
		"select *", //No table used
	}
	runTestShouldError(mock, t, sqls)
}

// test derived table plan building
func TestDerivedTableSqlBuilder(t *testing.T) {
	mock := NewMockOptimizer(false)
	// should pass
	sqls := []string{
		"select c_custkey from (select c_custkey from CUSTOMER ) a",
		"select c_custkey from (select c_custkey from CUSTOMER group by c_custkey ) a",
		"select col1 from (select c_custkey from CUSTOMER group by c_custkey ) a(col1)",
		"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",
		"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",
		"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",
		"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",
		"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",
	}
	runTestShouldPass(mock, t, sqls, false, false)

	// should error
	sqls = []string{
		"select C_NAME from (select c_custkey from CUSTOMER) a",                               //column not exist
		"select c_custkey2222 from (select c_custkey from CUSTOMER group by c_custkey ) a",    //column not exist
		"select col1 from (select c_custkey from CUSTOMER group by c_custkey ) a(col1, col2)", //column length not match
		"select c_custkey from (select c_custkey from CUSTOMER group by c_custkey) a(col1)",   //column not exist
	}
	runTestShouldError(mock, t, sqls)
}

// test derived table plan building
func TestUnionSqlBuilder(t *testing.T) {
	mock := NewMockOptimizer(false)
	// should pass
	sqls := []string{
		"(select 1) union (select 1)",
		"(((select n_nationkey from nation order by n_nationkey))) union (((select n_nationkey from nation order by n_nationkey)))",
		"select 1 union select 2",
		"select 1 union (select 2 union select 3)",
		"(select 1 union select 2) union select 3 intersect select 4 order by 1",
		"select 1 union select null",
		"select n_name from nation intersect select n_name from nation2",
		"select n_name from nation minus select n_name from nation2",
		"select 1 union select 2 intersect select 2 union all select 1.1 minus select 22222",
		"select 1 as a union select 2 order by a limit 1",
		"select n_name from nation union select n_comment from nation order by n_name",
		"with qn (foo, bar) as (select 1 as col, 2 as coll union select 4, 5) select qn1.bar from qn qn1",
		"select n_name, n_comment from nation union all select n_name, n_comment from nation2",
		"select n_name from nation intersect all select n_name from nation2",
	}
	runTestShouldPass(mock, t, sqls, false, false)

	// should error
	sqls = []string{
		"select 1 union select 2, 'a'",
		"select n_name as a from nation union select n_comment from nation order by n_name",
		"select n_name from nation minus all select n_name from nation2", // not support
	}
	runTestShouldError(mock, t, sqls)
}

// test CTE plan building
func TestCTESqlBuilder(t *testing.T) {
	mock := NewMockOptimizer(false)

	// should pass
	sqls := []string{
		"WITH qn AS (SELECT * FROM nation) SELECT * FROM qn;",
		"WITH qn(a, b) AS (SELECT * FROM nation) SELECT * FROM qn;",
		"with qn0 as (select 1), qn1 as (select * from qn0), qn2 as (select 1), qn3 as (select 1 from qn1, qn2) select 1 from qn3",

		`WITH qn AS (select "outer" as a)
		SELECT (WITH qn AS (SELECT "inner" as a) SELECT a from qn),
		qn.a
		FROM qn`,
	}
	runTestShouldPass(mock, t, sqls, false, false)

	// should error
	sqls = []string{
		`with qn1 as (with qn3 as (select * from qn2) select * from qn3),
		qn2 as (select 1)
		select * from qn1`,

		`WITH qn2 AS (SELECT a FROM qn WHERE a IS NULL or a>0),
		qn AS (SELECT b as a FROM qn2)
		SELECT qn.a  FROM qn`,
	}
	runTestShouldError(mock, t, sqls)
}

func TestInsert(t *testing.T) {
	mock := NewMockOptimizer(false)
	// should pass
	sqls := []string{
		"INSERT INTO NATION VALUES (1, 'NAME1',21, 'COMMENT1'), (2, 'NAME2', 22, 'COMMENT2')",
		"INSERT INTO NATION (N_NATIONKEY, N_REGIONKEY, N_NAME, N_COMMENT) VALUES (1, 21, 'NAME1','comment1'), (2, 22, 'NAME2', 'comment2')",
		"INSERT INTO NATION SELECT * FROM NATION2",
	}
	runTestShouldPass(mock, t, sqls, false, false)

	// should error
	sqls = []string{
		"INSERT NATION VALUES (1, 'NAME1',21, 'COMMENT1'), ('NAME2', 22, 'COMMENT2')",                                // doesn't match value count
		"INSERT NATION (N_NATIONKEY, N_REGIONKEY, N_NAME) VALUES (1, 'NAME1'), (2, 22, 'NAME2')",                     // doesn't match value count
		"INSERT NATION (N_NATIONKEY, N_REGIONKEY, N_NAME2222) VALUES (1, 21, 'NAME1'), (2, 22, 'NAME2')",             // column not exist
		"INSERT NATION333 (N_NATIONKEY, N_REGIONKEY, N_NAME2222) VALUES (1, 2, 'NAME1'), (2, 22, 'NAME2')",           // table not exist
		"INSERT NATION (N_NATIONKEY, N_REGIONKEY, N_NAME2222) VALUES (1, 'should int32', 'NAME1'), (2, 22, 'NAME2')", // column type not match
		"INSERT NATION (N_NATIONKEY, N_REGIONKEY, N_NAME2222) VALUES (1, 2.22, 'NAME1'), (2, 22, 'NAME2')",           // column type not match
		"INSERT NATION (N_NATIONKEY, N_REGIONKEY, N_NAME2222) VALUES (1, 2, 'NAME1'), (2, 22, 'NAME2')",              // function expr not support now
		"INSERT INTO region SELECT * FROM NATION2",                                                                   // column length not match
		"INSERT INTO region SELECT 1, 2, 3, 4, 5, 6 FROM NATION2",                                                    // column length not match
		"INSERT NATION333 (N_NATIONKEY, N_REGIONKEY, N_NAME2222) SELECT 1, 2, 3 FROM NATION2",                        // table not exist
	}
	runTestShouldError(mock, t, sqls)
}

func TestUpdate(t *testing.T) {
	mock := NewMockOptimizer(true)
	// should pass
	sqls := []string{
		"UPDATE NATION SET N_NAME ='U1', N_REGIONKEY=2",
		"UPDATE NATION SET N_NAME ='U1', N_REGIONKEY=2 WHERE N_NATIONKEY > 10 LIMIT 20",
		"UPDATE NATION SET N_NAME ='U1', N_REGIONKEY=N_REGIONKEY+2 WHERE N_NATIONKEY > 10 LIMIT 20",
		"update NATION a join NATION2 b on a.N_REGIONKEY = b.R_REGIONKEY set a.N_NAME = 'aa'",
		"prepare stmt1 from 'update nation set n_name = ? where n_nationkey > ?'",
		"drop index idx1 on test_idx",
	}
	runTestShouldPass(mock, t, sqls, false, false)

	// should error
	sqls = []string{
		"UPDATE NATION SET N_NAME2 ='U1', N_REGIONKEY=2",    // column not exist
		"UPDATE NATION2222 SET N_NAME ='U1', N_REGIONKEY=2", // table not exist
	}
	runTestShouldError(mock, t, sqls)
}

func TestDelete(t *testing.T) {
	mock := NewMockOptimizer(true)
	// should pass
	sqls := []string{
		"DELETE FROM NATION",
		"DELETE FROM NATION WHERE N_NATIONKEY > 10",
		"DELETE FROM NATION WHERE N_NATIONKEY > 10 LIMIT 20",
		"delete nation from nation left join nation2 on nation.n_nationkey = nation2.n_nationkey",
		"delete from nation",
		"delete nation, nation2 from nation join nation2 on nation.n_name = nation2.n_name",
		"prepare stmt1 from 'delete from nation where n_nationkey > ?'",
	}
	runTestShouldPass(mock, t, sqls, false, false)

	// should error
	sqls = []string{
		"DELETE FROM NATION2222",                     // table not exist
		"DELETE FROM NATION WHERE N_NATIONKEY2 > 10", // column not found
	}
	runTestShouldError(mock, t, sqls)
}

func TestSubQuery(t *testing.T) {
	mock := NewMockOptimizer(false)
	// should pass
	sqls := []string{
		"SELECT * FROM NATION where N_REGIONKEY > (select max(R_REGIONKEY) from REGION)",                                 // unrelated
		"SELECT * FROM NATION where N_REGIONKEY in (select max(R_REGIONKEY) from REGION)",                                // unrelated
		"SELECT * FROM NATION where N_REGIONKEY not in (select max(R_REGIONKEY) from REGION)",                            // unrelated
		"SELECT * FROM NATION where exists (select max(R_REGIONKEY) from REGION)",                                        // unrelated
		"SELECT * FROM NATION where N_REGIONKEY > (select max(R_REGIONKEY) from REGION where R_REGIONKEY = N_REGIONKEY)", // related
		//"DELETE FROM NATION WHERE N_NATIONKEY > 10",
		`select
		sum(l_extendedprice) / 7.0 as avg_yearly
	from
		lineitem,
		part
	where
		p_partkey = l_partkey
		and p_brand = 'Brand#54'
		and p_container = 'LG BAG'
		and l_quantity < (
			select
				0.2 * avg(l_quantity)
			from
				lineitem
			where
				l_partkey = p_partkey
		);`, //tpch q17
		"select * from nation where n_regionkey in (select r_regionkey from region) and n_nationkey not in (1,2) and n_nationkey = some (select n_nationkey from nation2)",
	}
	runTestShouldPass(mock, t, sqls, false, false)

	// should error
	sqls = []string{
		"SELECT * FROM NATION where N_REGIONKEY > (select max(R_REGIONKEY) from REGION222)",                                 // table not exist
		"SELECT * FROM NATION where N_REGIONKEY > (select max(R_REGIONKEY) from REGION where R_REGIONKEY < N_REGIONKEY222)", // column not exist
		"SELECT * FROM NATION where N_REGIONKEY > (select max(R_REGIONKEY) from REGION where R_REGIONKEY < N_REGIONKEY)",    // related
	}
	runTestShouldError(mock, t, sqls)
}

func TestMysqlCompatibilityMode(t *testing.T) {
	mock := NewMockOptimizer(false)

	sqls := []string{
		"SELECT n_nationkey FROM NATION group by n_name",
		"SELECT n_nationkey, min(n_name) FROM NATION",
		"SELECT n_nationkey + 100 FROM NATION group by n_name",
	}
	// withou mysql compatibility
	runTestShouldError(mock, t, sqls)
	// with mysql compatibility
	mock.ctxt.mysqlCompatible = true
	runTestShouldPass(mock, t, sqls, false, false)
}

func TestTcl(t *testing.T) {
	mock := NewMockOptimizer(false)
	// should pass
	sqls := []string{
		"start transaction",
		"start transaction read write",
		"begin",
		"commit and chain",
		"commit and chain no release",
		"rollback and chain",
	}
	runTestShouldPass(mock, t, sqls, false, false)

	// should error
	sqls = []string{}
	runTestShouldError(mock, t, sqls)
}

func TestDdl(t *testing.T) {
	mock := NewMockOptimizer(false)
	// should pass
	sqls := []string{
		"create database db_name",               //db not exists and pass
		"create database if not exists db_name", //db not exists but pass
		"create database if not exists tpch",    //db exists and pass
		"drop database if exists db_name",       //db not exists but pass
		"drop database tpch",                    //db exists, pass
		"create view v1 as select * from nation",

		"create table tbl_name (t bool(20) comment 'dd', b int unsigned, c char(20), d varchar(20), primary key(b), index idx_t(c)) comment 'test comment'",
		"create table if not exists tbl_name (b int default 20 primary key, c char(20) default 'ss', d varchar(20) default 'kkk')",
		"create table if not exists nation (t bool(20), b int, c char(20), d varchar(20))",
		"drop table if exists tbl_name",
		"drop table if exists nation",
		"drop table nation",
		"drop table tpch.nation",
		"drop table if exists tpch.tbl_not_exist",
		"drop table if exists db_not_exist.tbl",
		"drop view v1",
		"truncate nation",
		"truncate tpch.nation",
		"truncate table nation",
		"truncate table tpch.nation",
		"create unique index idx_name on nation(n_regionkey)",
	}
	runTestShouldPass(mock, t, sqls, false, false)

	// should error
	sqls = []string{
		// "create database tpch",  // check in pipeline now
		// "drop database db_name", // check in pipeline now
		// "create table nation (t bool(20), b int, c char(20), d varchar(20))",             // check in pipeline now
		"create table nation (b int primary key, c char(20) primary key, d varchar(20))", //Multiple primary key
		"drop table tbl_name",           //table not exists in tpch
		"drop table tpch.tbl_not_exist", //database not exists
		"drop table db_not_exist.tbl",   //table not exists
	}
	runTestShouldError(mock, t, sqls)
}

func TestShow(t *testing.T) {
	mock := NewMockOptimizer(false)
	// should pass
	sqls := []string{
		"show variables",
		//"show create database tpch",
		"show create table nation",
		"show create table tpch.nation",
		"show databases",
		"show databases like '%d'",
		"show databases where `Database` = '11'",
		"show databases where `Database` = '11' or `Database` = 'ddd'",
		"show tables",
		"show tables from tpch",
		"show tables like '%dd'",
		"show tables from tpch where `Tables_in_tpch` = 'aa' or `Tables_in_tpch` like '%dd'",
		"show columns from nation",
		"show columns from nation from tpch",
		"show columns from nation where `Field` like '%ff' or `Type` = 1 or `Null` = 0",
		"show create view v1",
		"show create table v1",
		"show table_number",
		"show table_number from tpch",
		"show column_number from nation",
		"show config",
		"show index from tpch.nation",
		"show locks",
		"show node list",
		"show grants for ROLE role1",
		"show function status",
		"show function status like '%ff'",
		// "show grants",
	}
	runTestShouldPass(mock, t, sqls, false, false)

	// should error
	sqls = []string{
		"show create database db_not_exist",                    //db no exist
		"show create table tpch.nation22",                      //table not exist
		"show create view vvv",                                 //view not exist
		"show databases where d ='a'",                          //Column not exist,  show databases only have one column named 'Database'
		"show databases where `Databaseddddd` = '11'",          //column not exist
		"show tables from tpch22222",                           //database not exist
		"show tables from tpch where Tables_in_tpch222 = 'aa'", //column not exist
		"show columns from nation_ddddd",                       //table not exist
		"show columns from nation_ddddd from tpch",             //table not exist
		"show columns from nation where `Field22` like '%ff'",  //column not exist
		"show index from tpch.dddd",
		"show table_number from tpch222",
		"show column_number from nation222",
	}
	runTestShouldError(mock, t, sqls)
}

func TestResultColumns(t *testing.T) {
	mock := NewMockOptimizer(false)
	getColumns := func(sql string) []*ColDef {
		logicPlan, err := runOneStmt(mock, t, sql)
		if err != nil {
			t.Fatalf("sql %s build plan error:%+v", sql, err)
		}
		return GetResultColumnsFromPlan(logicPlan)
	}

	returnNilSQL := []string{
		"begin",
		"commit",
		"rollback",
		"INSERT NATION VALUES (1, 'NAME1',21, 'COMMENT1'), (2, 'NAME2', 22, 'COMMENT2')",
		// "UPDATE NATION SET N_NAME ='U1', N_REGIONKEY=2",
		// "DELETE FROM NATION",
		"create database db_name",
		"drop database tpch",
		"create table tbl_name (b int unsigned, c char(20))",
		"drop table nation",
	}
	for _, sql := range returnNilSQL {
		columns := getColumns(sql)
		if columns != nil {
			t.Fatalf("sql:%+v, return columns should be nil", sql)
		}
	}

	returnColumnsSQL := map[string]string{
		"SELECT N_NAME, N_REGIONKEY a FROM NATION WHERE N_REGIONKEY > 0 ORDER BY a DESC":            "N_NAME,a",
		"select n_nationkey, sum(n_regionkey) from (select * from nation) sub group by n_nationkey": "n_nationkey,sum(n_regionkey)",
		"show variables":            "Variable_name,Value",
		"show create database tpch": "Database,Create Database",
		"show create table nation":  "Table,Create Table",
		"show databases":            "Database",
		"show tables":               "Tables_in_tpch",
		"show columns from nation":  "Field,Type,Null,Key,Default,Extra,Comment",
	}
	for sql, colsStr := range returnColumnsSQL {
		cols := strings.Split(colsStr, ",")
		columns := getColumns(sql)
		if len(columns) != len(cols) {
			t.Fatalf("sql:%+v, return columns should be [%s]", sql, colsStr)
		}
		for idx, col := range cols {
			// now ast always change col_name to lower string. will be fixed soon
			if !strings.EqualFold(columns[idx].Name, col) {
				t.Fatalf("sql:%+v, return columns should be [%s]", sql, colsStr)
			}
		}
	}
}

func TestBuildUnnest(t *testing.T) {
	mock := NewMockOptimizer(false)
	sqls := []string{
		`select * from unnest('{"a":1}') as f`,
		`select * from unnest('{"a":1}', '') as f`,
		`select * from unnest('{"a":1}', '$', true) as f`,
	}
	runTestShouldPass(mock, t, sqls, false, false)
	errSqls := []string{
		`select * from unnest(t.t1.a)`,
		`select * from unnest(t.a, "$.b")`,
		`select * from unnest(t.a, "$.b", true)`,
		`select * from unnest(t.a) as f`,
		`select * from unnest(t.a, "$.b") as f`,
		`select * from unnest(t.a, "$.b", true) as f`,
		`select * from unnest('{"a":1}')`,
		`select * from unnest('{"a":1}', "$")`,
		`select * from unnest('{"a":1}', "", true)`,
	}
	runTestShouldError(mock, t, errSqls)
}

func TestVisitRule(t *testing.T) {
	sql := "select * from nation where n_nationkey > ? or n_nationkey=@int_var or abs(-1) > 1"
	mock := NewMockOptimizer(false)
	ctx := context.TODO()
	plan, err := runOneStmt(mock, t, sql)
	if err != nil {
		t.Fatalf("should not error, sql=%s", sql)
	}
	getParamRule := NewGetParamRule()
	vp := NewVisitPlan(plan, []VisitPlanRule{getParamRule})
	err = vp.Visit(context.TODO())
	if err != nil {
		t.Fatalf("should not error, sql=%s", sql)
	}
	getParamRule.SetParamOrder()
	args := getParamRule.params

	resetParamOrderRule := NewResetParamOrderRule(args)
	vp = NewVisitPlan(plan, []VisitPlanRule{resetParamOrderRule})
	err = vp.Visit(ctx)
	if err != nil {
		t.Fatalf("should not error, sql=%s", sql)
	}

	params := []*Expr{
		makePlan2Int64ConstExprWithType(10),
	}
	resetParamRule := NewResetParamRefRule(ctx, params)
	resetVarRule := NewResetVarRefRule(&mock.ctxt, &process.Process{})
	constantFoldRule := NewConstantFoldRule(&mock.ctxt)
	vp = NewVisitPlan(plan, []VisitPlanRule{resetParamRule, resetVarRule, constantFoldRule})
	err = vp.Visit(ctx)
	if err != nil {
		t.Fatalf("should not error, sql=%s", sql)
	}
}

func getJSON(v any, t *testing.T) []byte {
	b, err := json.Marshal(v)
	if err != nil {
		t.Logf("%+v", v)
	}
	var out bytes.Buffer
	err = json.Indent(&out, b, "", "  ")
	if err != nil {
		t.Logf("%+v", v)
	}
	return out.Bytes()
}

func testDeepCopy(logicPlan *Plan) {
	switch logicPlan.Plan.(type) {
	case *plan.Plan_Query:
		_ = DeepCopyPlan(logicPlan)
	case *plan.Plan_Ddl:
		_ = DeepCopyPlan(logicPlan)
	case *plan.Plan_Dcl:
	}
}

func outPutPlan(logicPlan *Plan, toFile bool, t *testing.T) {
	var json []byte
	switch logicPlan.Plan.(type) {
	case *plan.Plan_Query:
		json = getJSON(logicPlan.GetQuery(), t)
	case *plan.Plan_Tcl:
		json = getJSON(logicPlan.GetTcl(), t)
	case *plan.Plan_Ddl:
		json = getJSON(logicPlan.GetDdl(), t)
	case *plan.Plan_Dcl:
		json = getJSON(logicPlan.GetDcl(), t)
	}
	if toFile {
		err := os.WriteFile("/tmp/mo_plan_test.json", json, 0777)
		if err != nil {
			t.Logf("%+v", err)
		}
	} else {
		t.Logf(string(json))
	}
}

func runOneStmt(opt Optimizer, t *testing.T, sql string) (*Plan, error) {
	stmts, err := mysql.Parse(opt.CurrentContext().GetContext(), sql)
	if err != nil {
		t.Fatalf("%+v", err)
	}
	// this sql always return one stmt
	ctx := opt.CurrentContext()
	return BuildPlan(ctx, stmts[0])
}

func runTestShouldPass(opt Optimizer, t *testing.T, sqls []string, printJSON bool, toFile bool) {
	for _, sql := range sqls {
		logicPlan, err := runOneStmt(opt, t, sql)
		if err != nil {
			t.Fatalf("%+v, sql=%v", err, sql)
		}
		testDeepCopy(logicPlan)
		if printJSON {
			outPutPlan(logicPlan, toFile, t)
		}
	}
}

func runTestShouldError(opt Optimizer, t *testing.T, sqls []string) {
	for _, sql := range sqls {
		_, err := runOneStmt(opt, t, sql)
		if err == nil {
			t.Fatalf("should error, but pass: %v", sql)
		}
	}
}

func Test_mergeContexts(t *testing.T) {
	b1 := NewBinding(0, 1, "a", nil, nil, false)
	bc1 := NewBindContext(nil, nil)
	bc1.bindings = append(bc1.bindings, b1)

	b2 := NewBinding(1, 2, "a", nil, nil, false)
	bc2 := NewBindContext(nil, nil)
	bc2.bindings = append(bc2.bindings, b2)

	bc := NewBindContext(nil, nil)

	//a merge a
	err := bc.mergeContexts(context.Background(), bc1, bc2)
	assert.Error(t, err)
	assert.EqualError(t, err, "invalid input: table 'a' specified more than once")

	//a merge b
	b3 := NewBinding(2, 3, "b", nil, nil, false)
	bc3 := NewBindContext(nil, nil)
	bc3.bindings = append(bc3.bindings, b3)

	err = bc.mergeContexts(context.Background(), bc1, bc3)
	assert.NoError(t, err)

	// a merge a, ctx is  nil
	var ctx context.Context
	err = bc.mergeContexts(ctx, bc1, bc2)
	assert.Error(t, err)
	assert.EqualError(t, err, "invalid input: table 'a' specified more than once")
}