mindsdb_sql_parser/tests/test_base_sql/test_base_sql.py at 1bc1b70519c6f5bcec6b74df84ab04fe6a23820e · mindsdb/mindsdb_sql_parser · GitHub

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
from textwrap import dedent

import pytest

from mindsdb_sql_parser import parse_sql
from mindsdb_sql_parser.exceptions import ParsingException

from mindsdb_sql_parser.ast import *


class TestSql:
    def test_ending(self):
        sql = """INSERT INTO tbl_name VALUES (1, 3)
           ;
        """

        parse_sql(sql)

    def test_not_equal(self):
        sql = " select * from t1 where a<>1"

        ast = parse_sql(sql)

        expected_ast = Select(
            targets=[Star()],
            from_table=Identifier('t1'),
            where=BinaryOperation(
                op='<>',
                args=[
                    Identifier('a'),
                    Constant(1)
                ]
            )
        )

        assert str(ast).lower() == str(expected_ast).lower()
        assert ast.to_tree() == expected_ast.to_tree()

    def test_escaping(self):
        expected_ast = Select(
            targets=[
                Constant(value="a ' \" b"),
                Constant(value="a ' \" b"),
                Constant(value="a \\n b"),
                Constant(value="a \\\n b"),
                Constant(value="a \\\n b"),
                Constant(value="a\nb"),
            ]
        )

        sql = dedent('''
select
'a \\' \\" b',  -- double quote
"a \\' \\" b",  -- single quote
"a \\n b",
"a \\\n b", -- double quote
'a \\\n b', -- single quote
"a
b"
        ''')

        ast = parse_sql(sql)

        assert str(ast).lower() == str(expected_ast).lower()
        assert ast.to_tree() == expected_ast.to_tree()

    def test_quotes_escaping(self):
        sql = "select 'women''s soccer'"

        expected_ast = Select(
            targets=[
                Constant(value="women's soccer")
            ]
        )
        ast = parse_sql(sql)

        assert str(ast).lower() == str(expected_ast).lower()
        assert ast.to_tree() == expected_ast.to_tree()

    def test_quotes_identifier(self):
        sql = 'select t2."var (k)" from t2'

        expected_ast = Select(
            targets=[
                Identifier(parts=['t2', 'var (k)'])
            ],
            from_table=Identifier('t2')
        )
        ast = parse_sql(sql)

        assert str(ast).lower() == str(expected_ast).lower()
        assert ast.to_tree() == expected_ast.to_tree()

    def test_multy_statement(self):
        sql = """
        select 1;
        select 2
        """

        with pytest.raises(ParsingException) as excinfo:
            parse_sql(sql)

        assert "Only a single sql statement is expected" in str(excinfo.value)

    def test_trailing_semicolon(self):
        query = parse_sql("select 1;")
        assert query == Select(targets=[Constant(1)])

    def test_comment_after_semicolon(self):
        sql = """
        select 1; -- my query
        """

        query = parse_sql(sql)
        assert query == Select(targets=[Constant(1)])

    def test_comment_symbols_in_string(self):
        expected_query = Select(targets=[Constant('--x')])

        query = parse_sql("select '--x'")
        assert query == expected_query

        query = parse_sql('select "--x"')
        assert query == expected_query

        # multiline
        expected_query = Select(targets=[Constant('/*  x */')])

        query = parse_sql("select '/*  x */'")
        assert query == expected_query

        query = parse_sql('select "/*  x */"')
        assert query == expected_query