sql_parser_tests = [ ("create table asd;", [{"type": "create_stmt", "table_name": "asd", "cols": []}]), ( "create table tb (col1 INT, col2 string, col3 FLOAT);", [ { "type": "create_stmt", "table_name": "tb", "cols": [ { "type": "create_column", "column_name": "col1", "data_type": "INT", "primary_key": False, }, { "type": "create_column", "column_name": "col2", "data_type": "string", "primary_key": False, }, { "type": "create_column", "column_name": "col3", "data_type": "FLOAT", "primary_key": False, }, ], } ], ), ( """ create table tb1 ( col1 int primary key, col2 FLOAT ); """, [ { "type": "create_stmt", "table_name": "tb1", "cols": [ { "type": "create_column", "column_name": "col1", "data_type": "int", "primary_key": True, }, { "type": "create_column", "column_name": "col2", "data_type": "FLOAT", "primary_key": False, }, ], } ], ), ( """ create table tb2 ( x float, y int, z int ); """, [ { "type": "create_stmt", "table_name": "tb2", "cols": [ { "type": "create_column", "column_name": "x", "data_type": "float", "primary_key": False, }, { "type": "create_column", "column_name": "y", "data_type": "int", "primary_key": False, }, { "type": "create_column", "column_name": "z", "data_type": "int", "primary_key": False, }, ], } ], ), ( """insert into tb1 values (1, 'foo');""", [ { "type": "insert_stmt", "table_name": "tb1", "values": [ {"type": "int", "value": 1}, {"type": "string", "value": "'foo'"}, ], } ], ), ( """insert into tb1 values (2, 'foo', 'zxc', 1234.234);""", [ { "type": "insert_stmt", "table_name": "tb1", "values": [ {"type": "int", "value": 2}, {"type": "string", "value": "'foo'"}, {"type": "string", "value": "'zxc'"}, {"type": "float", "value": 1234.234}, ], } ], ), ( "update tb1 set col1=3, col4=4 where col1=2 and col2=4;", [ { "type": "update_stmt", "table_name": "tb1", "set": [ { "type": "assign_const", "left": {"type": "identifier", "value": "col1"}, "right": {"type": "int", "value": 3}, }, { "type": "assign_const", "left": {"type": "identifier", "value": "col4"}, "right": {"type": "int", "value": 4}, }, ], "where": { "type": "且", "left": { "type": "相等", "left": {"type": "identifier", "value": "col1"}, "right": {"type": "int", "value": 2}, "op_type": "bin_cmp_op", }, "right": { "type": "相等", "left": {"type": "identifier", "value": "col2"}, "right": {"type": "int", "value": 4}, "op_type": "bin_cmp_op", }, "op_type": "bin_logical_op", }, } ], ), ( "update tb1 set col1=3, col4=4 where not not not col1=2 and col2=4 or col3=col2;", [ { "type": "update_stmt", "table_name": "tb1", "set": [ { "type": "assign_const", "left": {"type": "identifier", "value": "col1"}, "right": {"type": "int", "value": 3}, }, { "type": "assign_const", "left": {"type": "identifier", "value": "col4"}, "right": {"type": "int", "value": 4}, }, ], "where": { "type": "或", "left": { "type": "且", "left": { "type": "非", "left": { "type": "非", "left": { "type": "非", "left": { "type": "相等", "left": {"type": "identifier", "value": "col1"}, "right": {"type": "int", "value": 2}, "op_type": "bin_cmp_op", }, }, }, }, "right": { "type": "相等", "left": {"type": "identifier", "value": "col2"}, "right": {"type": "int", "value": 4}, "op_type": "bin_cmp_op", }, "op_type": "bin_logical_op", }, "right": { "type": "相等", "left": {"type": "identifier", "value": "col3"}, "right": {"type": "identifier", "value": "col2"}, "op_type": "bin_cmp_op", }, "op_type": "bin_logical_op", }, } ], ), ( "delete from tb1 where c1 = 1 and c2= 3 or c3=3;", [ { "type": "delete_stmt", "table_name": "tb1", "where": { "type": "或", "left": { "type": "且", "left": { "type": "相等", "left": {"type": "identifier", "value": "c1"}, "right": {"type": "int", "value": 1}, "op_type": "bin_cmp_op", }, "right": { "type": "相等", "left": {"type": "identifier", "value": "c2"}, "right": {"type": "int", "value": 3}, "op_type": "bin_cmp_op", }, "op_type": "bin_logical_op", }, "right": { "type": "相等", "left": {"type": "identifier", "value": "c3"}, "right": {"type": "int", "value": 3}, "op_type": "bin_cmp_op", }, "op_type": "bin_logical_op", }, } ], ), ( "delete from tb1 where c1 = 1 and (c2= 3 or c3=3) or (c4='asd');", [ { "type": "delete_stmt", "table_name": "tb1", "where": { "type": "或", "left": { "type": "且", "left": { "type": "相等", "left": {"type": "identifier", "value": "c1"}, "right": {"type": "int", "value": 1}, "op_type": "bin_cmp_op", }, "right": { "type": "或", "left": { "type": "相等", "left": {"type": "identifier", "value": "c2"}, "right": {"type": "int", "value": 3}, "op_type": "bin_cmp_op", }, "right": { "type": "相等", "left": {"type": "identifier", "value": "c3"}, "right": {"type": "int", "value": 3}, "op_type": "bin_cmp_op", }, "op_type": "bin_logical_op", }, "op_type": "bin_logical_op", }, "right": { "type": "相等", "left": {"type": "identifier", "value": "c4"}, "right": {"type": "string", "value": "'asd'"}, "op_type": "bin_cmp_op", }, "op_type": "bin_logical_op", }, } ], ), ( "select * from t2;", [ { "type": "select_stmt", "select_cols": [ { "type": "select_all_column", "target": { "type": "select_all_column", "value": "select_all_column", }, } ], "table_names": ["t2"], "where": {}, } ], ), ( "select c2 as t from t2 where col1>2;", [ { "type": "select_stmt", "select_cols": [ { "type": "select_column", "target": {"type": "identifier", "value": "c2"}, "alias": "t", } ], "table_names": ["t2"], "where": { "type": "大于", "left": {"type": "identifier", "value": "col1"}, "right": {"type": "int", "value": 2}, "op_type": "bin_cmp_op", }, } ], ), ( """SELECT Sname FROM Student WHERE Sno IN (1,2) and c in (3, 4, 5);""", [ { "type": "select_stmt", "select_cols": [ { "type": "select_column", "target": {"type": "identifier", "value": "Sname"}, } ], "table_names": ["Student"], "where": { "type": "且", "left": { "type": "包含于", "left": {"type": "identifier", "value": "Sno"}, "right": [ {"type": "int", "value": 1}, {"type": "int", "value": 2}, ], "op_type": "bin_contains_op", }, "right": { "type": "包含于", "left": {"type": "identifier", "value": "c"}, "right": [ {"type": "int", "value": 3}, {"type": "int", "value": 4}, {"type": "int", "value": 5}, ], "op_type": "bin_contains_op", }, "op_type": "bin_logical_op", }, } ], ), ( """SELECT Student.Sname FROM Student WHERE Sno IN ( SELECT Sno FROM SC WHERE SC.Cno='81003' ); """, [ { "type": "select_stmt", "select_cols": [ { "type": "select_column", "target": { "type": "table_field", "table": "Student", "field": "Sname", }, } ], "table_names": ["Student"], "where": { "type": "包含于", "left": {"type": "identifier", "value": "Sno"}, "right": { "type": "select_stmt", "select_cols": [ { "type": "select_column", "target": {"type": "identifier", "value": "Sno"}, } ], "table_names": ["SC"], "where": { "type": "相等", "left": { "type": "table_field", "table": "SC", "field": "Cno", }, "right": {"type": "string", "value": "'81003'"}, "op_type": "bin_cmp_op", }, }, "op_type": "bin_contains_op", }, } ], ), ( """ select Student.Sname from Student join SC on Student.Sno=SC.Sno where SC.Cno='81003'; """, [ { "type": "select_stmt", "select_cols": [ { "type": "select_column", "target": { "type": "table_field", "table": "Student", "field": "Sname", }, } ], "table_names": ["Student"], "join_options": { "type": "join_options", "join_with": {"type": "identifier", "value": "SC"}, "on": { "type": "相等", "left": { "type": "table_field", "table": "Student", "field": "Sno", }, "right": {"type": "table_field", "table": "SC", "field": "Sno"}, "op_type": "bin_cmp_op", }, }, "where": { "type": "相等", "left": {"type": "table_field", "table": "SC", "field": "Cno"}, "right": {"type": "string", "value": "'81003'"}, "op_type": "bin_cmp_op", }, } ], ), ( """ select Student.Sname from Student join SC on Student.Sno=SC.Sno and SC.Cno='81003'; """, [ { "type": "select_stmt", "select_cols": [ { "type": "select_column", "target": { "type": "table_field", "table": "Student", "field": "Sname", }, } ], "table_names": ["Student"], "join_options": { "type": "join_options", "join_with": {"type": "identifier", "value": "SC"}, "on": { "type": "且", "left": { "type": "相等", "left": { "type": "table_field", "table": "Student", "field": "Sno", }, "right": { "type": "table_field", "table": "SC", "field": "Sno", }, "op_type": "bin_cmp_op", }, "right": { "type": "相等", "left": { "type": "table_field", "table": "SC", "field": "Cno", }, "right": {"type": "string", "value": "'81003'"}, "op_type": "bin_cmp_op", }, "op_type": "bin_logical_op", }, }, "where": {}, } ], ), ("drop table t1;", [{"type": "drop_stmt", "table_name": "t1"}]), ( "select * from tb1, tb2;", [ { "type": "select_stmt", "select_cols": [ { "type": "select_all_column", "target": { "type": "select_all_column", "value": "select_all_column", }, } ], "table_names": ["tb1", "tb2"], "where": {}, } ], ), ] sql_checker_tests = [ ("drop table person;", True), ("create table person(name string, age int, classId int);", True), ("select age from person;", True), ("select * from person;", True), ("select gender from person;", "column `gender` not exists in `person`"), ("select 123 from person;", True), ("drop table class;", True), ("create table class (id int, grade int, faculty string);", True), ("select * from class where grade = 2 and faculty = 'Computer Science';", True), ( "select * from class where grade = 2 and count=33;", "column `count` not exists in `class`", ), ("select age, class.grade from class, person;", True), ( "select age, person.grade from class, person;", "column `person.grade` not exists in `class, person`", ), ( """SELECT person.name, grade, faculty FROM person WHERE name = '张三' and classId IN ( SELECT * FROM class WHERE class.id = classId ); """, "column `grade` not exists in `person`", ), ( """select person.name from person join class on class.id=person.classId and person.grade=2 where age=22; """, "column `person.grade` not exists in `person, class`", ), ( """select person.name from person join class on class.id=person.classId and class.grade=2 where age=22; """, True, ), ( """select person.name from person join class on class.id=person.classId and class.grade = 'zxc' where age=22; """, "column `class.grade` type is `int`, but `string:'zxc'` type is `string`, cannot `相等`", ), ( """select person.name from person where age>name; """, "column `age` type is `int`, but `name` type is `string`, cannot `大于`", ), ] sql_optimizer_tests = [ ( "select * from person where (age < 18) or (age > 60 and age < 35);", { "select_cols": [ { "target": { "type": "select_all_column", "value": "select_all_column", }, "type": "select_all_column", } ], "table_names": ["person"], "type": "select_stmt", "where": { "left": { "left": {"type": "identifier", "value": "age"}, "op_type": "bin_cmp_op", "right": {"type": "int", "value": 18}, "type": "小于", }, "op_type": "bin_logical_op", "right": {"type": "bool", "value": False}, "type": "或", }, }, ), ( "select * from person where (age < 18) and age < 5 and age < 35;", { "select_cols": [ { "target": { "type": "select_all_column", "value": "select_all_column", }, "type": "select_all_column", } ], "table_names": ["person"], "type": "select_stmt", "where": { "left": {"type": "identifier", "value": "age"}, "op_type": "bin_cmp_op", "right": {"type": "int", "value": 5}, "type": "小于", }, }, ), ]