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}, }, "right": { "type": "相等", "left": {"type": "identifier", "value": "col2"}, "right": {"type": "int", "value": 4}, }, }, } ], ), ( "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}, }, }, }, }, "right": { "type": "相等", "left": {"type": "identifier", "value": "col2"}, "right": {"type": "int", "value": 4}, }, }, "right": { "type": "相等", "left": {"type": "identifier", "value": "col3"}, "right": {"type": "identifier", "value": "col2"}, }, }, } ], ), ( "delete from tb1 where c1 = 1 and c2= 3 or c3=3;", [ { "type": "delete_stmt", "table_name": "tb1", "where": { "type": "或", "left": { "left": { "type": "相等", "left": {"type": "identifier", "value": "c1"}, "right": {"type": "int", "value": 1}, }, "type": "且", "right": { "type": "相等", "left": {"type": "identifier", "value": "c2"}, "right": {"type": "int", "value": 3}, }, }, "right": { "type": "相等", "left": {"type": "identifier", "value": "c3"}, "right": {"type": "int", "value": 3}, }, }, } ], ), ( "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}, }, "right": { "type": "或", "left": { "type": "相等", "left": {"type": "identifier", "value": "c2"}, "right": {"type": "int", "value": 3}, }, "right": { "type": "相等", "left": {"type": "identifier", "value": "c3"}, "right": {"type": "int", "value": 3}, }, }, }, "right": { "type": "相等", "left": {"type": "identifier", "value": "c4"}, "right": {"type": "string", "value": "'asd'"}, }, }, } ], ), ( "select * from t2;", [ { "type": "select_stmt", "select_cols": [{"type": "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}, }, } ], ), ( """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}, ], }, "right": { "type": "包含于", "left": {"type": "identifier", "value": "c"}, "right": [ {"type": "int", "value": 3}, {"type": "int", "value": 4}, {"type": "int", "value": 5}, ], }, }, } ], ), ( """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'"}, }, }, }, } ], ), ( """ 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"}, }, }, "where": { "type": "相等", "left": {"type": "table_field", "table": "SC", "field": "Cno"}, "right": {"type": "string", "value": "'81003'"}, }, } ], ), ( """ 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", }, }, "right": { "type": "相等", "left": { "type": "table_field", "table": "SC", "field": "Cno", }, "right": {"type": "string", "value": "'81003'"}, }, }, }, "where": {}, } ], ), ('drop table t1;', [{"type": "drop_stmt", "table_name": "t1"}]), ] 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`'), ]