sql-parser/tests_config.py

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": "小于",
            },
        },
    ),
]