sql-parser/test_configs/sql_optimizer.py

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": {"type": "identifier", "value": "age"},
                "op_type": "bin_cmp_op",
                "right": {"type": "int", "value": 18},
                "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": "小于",
            },
        },
    ),
    (
        "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": {"type": "bool", "value": False},
        },
    ),
    (
        """select person.name 
        from person join class 
        on age=22 and class.id=person.classId ;
        """,
        {
            "join_options": {
                "join_with": {"type": "identifier", "value": "class"},
                "on": {
                    "left": {"field": "id", "table": "class", "type": "table_field"},
                    "op_type": "bin_cmp_op",
                    "right": {
                        "field": "classId",
                        "table": "person",
                        "type": "table_field",
                    },
                    "type": "相等",
                },
                "type": "join_options",
            },
            "select_cols": [
                {
                    "target": {
                        "field": "name",
                        "table": "person",
                        "type": "table_field",
                    },
                    "type": "select_column",
                }
            ],
            "table_names": ["person"],
            "type": "select_stmt",
            "where": {
                "left": {"type": "identifier", "value": "age"},
                "op_type": "bin_cmp_op",
                "right": {"type": "int", "value": 22},
                "type": "相等",
            },
        },
    ),
    (
        """select person.name 
        from person join class 
        on age=22;
        """,
        {
            "join_options": {
                "join_with": {"type": "identifier", "value": "class"},
                "on": {},
                "type": "join_options",
            },
            "select_cols": [
                {
                    "target": {
                        "field": "name",
                        "table": "person",
                        "type": "table_field",
                    },
                    "type": "select_column",
                }
            ],
            "table_names": ["person"],
            "type": "select_stmt",
            "where": {
                "left": {"type": "identifier", "value": "age"},
                "op_type": "bin_cmp_op",
                "right": {"type": "int", "value": 22},
                "type": "相等",
            },
        },
    ),
]