sql-parser/test_configs/sql_checker.py

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 `大于`",
    ),
    (
        """select person.name 
        from person join class 
        on age=22 and class.id=person.classId ;
        """,
        True
    )
]