def test_regexp_operator_is_parsed():
raw_sql = '''
a regexp 'expr'
a not regexp 'expr'
'''
ast = parse(raw_sql)
for child_ast in ast:
assert child_ast.type == 'operator'
assert child_ast.value == 'regexp'
assert len(child_ast.children) == 2
assert ast[0].children[0].type == 'unknown'
assert ast[0].children[0].value == 'a'
assert ast[0].children[1].type == 'literal'
assert ast[0].children[1].value == "'expr'"
assert ast[1].children[0].type == 'unknown'
assert ast[1].children[0].value == 'a'
assert ast[1].children[1].type == 'operator'
assert ast[1].children[1].value == "not"
not_op = ast[1].children[1]
assert len(not_op.children) == 1
assert not_op.children[0].type == 'literal'
assert not_op.children[0].value == "'expr'"
def test_function_call_is_parsed():
raw_sql = '''
SUM(*)
MAX((2 + 4) * 2, SUM(col))
MIN(sum(col), AVG(col2))
'''
ast = parse(raw_sql)
expected_fn_names = ['SUM', 'MAX', 'MIN']
counter = 0
for ast_child in ast:
assert ast_child.type == 'function'
assert ast_child.value == expected_fn_names[counter]
assert len(ast_child.children) >= 1
counter += 1
min_fn_ast = ast.pop()
assert min_fn_ast.children[0].type == 'function'
assert min_fn_ast.children[0].value == 'sum'
assert min_fn_ast.children[1].type == 'function'
assert min_fn_ast.children[1].value == 'AVG'
def test_update_multiple_tables_stmt_is_parsed():
raw_sql = '''
UPDATE table1, table2 SET col = 20, col1 = (col5 + table2.col4) WHERE id > 1 ORDER BY id LIMIT 10
'''
ast = parse(raw_sql)
assert len(ast) > 0
ast = ast[0]
assert ast.type == 'update'
assert len(ast.children) == 5
table_refs = ast.get_child('table_references')
assert table_refs is not None
assert len(table_refs.children) == 2
table_ref = table_refs.children[0]
assert len(table_ref.children) == 1
table = table_ref.children[0]
assert table.value == 'table1'
table_ref = table_refs.children[1]
assert len(table_ref.children) == 1
table = table_ref.children[0]
assert table.value == 'table2'
def test_delete_from_multiple_tables():
raw_sql = '''
DELETE FROM table1, table2 WHERE id > 1 ORDER BY id LIMIT 10
'''
ast = parse(raw_sql)
assert len(ast) > 0
ast = ast[0]
assert ast.type == 'delete'
assert len(ast.children) == 4
table_refs = ast.get_child('from')
assert table_refs is not None
assert len(table_refs.children) == 2
table_ref = table_refs.children[0]
assert len(table_ref.children) == 1
table = table_ref.children[0]
assert table.value == 'table1'
table_ref = table_refs.children[1]
assert len(table_ref.children) == 1
table = table_ref.children[0]
assert table.value == 'table2'
assert ast.get_child('where') is not None
assert ast.get_child('order') is not None
assert ast.get_child('limit') is not None
def test_delete_from_partition_is_parsed():
raw_sql = '''
DELETE FROM table1 PARTITION (p1, p2) WHERE id > 1 ORDER BY id LIMIT 10
'''
ast = parse(raw_sql)
assert len(ast) > 0
ast = ast[0]
assert ast.type == 'delete'
assert len(ast.children) == 4
table_refs = ast.get_child('from')
assert table_refs is not None
assert len(table_refs.children) == 1
table_ref = table_refs.children[0]
assert len(table_ref.children) == 2
table = table_ref.children[0]
assert table.value == 'table1'
partition = table_ref.children[1]
assert partition.type == 'partition'
assert len(partition.children) == 1
assert partition.children[0].type == 'paren_group'
assert len(partition.children[0].children) == 2
assert ast.get_child('where') is not None
assert ast.get_child('order') is not None
assert ast.get_child('limit') is not None
def test_insert_with_assignment_list_is_parsed():
raw_sql = '''
INSERT INTO table SET col1 = 2, col2 = 3
'''
ast = parse(raw_sql)
assert len(ast) > 0
ast = ast[0]
assert ast.type == 'insert'
assert len(ast.children) == 2
into = ast.get_child('into')
assert into is not None
assert len(into.children) == 1
assert into.children[0].children[0].value == 'table'
assignment_list = ast.get_child('assignment_list')
assert assignment_list is not None
assert len(assignment_list.children) == 2
assignment = assignment_list.children[0]
assert assignment.type == 'operator'
assert assignment.value == '='
assert len(assignment.children) == 2
assert assignment.children[0].value == 'col1'
assert assignment.children[1].value == '2'
assignment = assignment_list.children[1]
assert assignment.type == 'operator'
assert assignment.value == '='
assert len(assignment.children) == 2
assert assignment.children[0].value == 'col2'
assert assignment.children[1].value == '3'
def test_multiple_unions_are_parsed():
raw_sql = '''
(SELECT col1, col2 FROM tbl1)
UNION
(SELECT col1, col2 FROM tbl2)
UNION
SELECT 1
UNION
SELECT
a,
b,
c
FROM tbl1 JOIN tbl2 JOIN tbl3 USING (a,b, c)
'''
ast = parse(raw_sql)
assert len(ast) > 0
ast = ast[0]
assert ast.type == 'operator'
assert ast.value == 'UNION'
assert ast.children[0].type == 'select'
assert ast.children[1].type == 'operator'
assert ast.children[1].value == 'UNION'
nested_union = ast.children[1]
assert nested_union.children[0].type == 'select'
assert nested_union.children[1].type == 'operator'
assert nested_union.children[1].value == 'UNION'
nested_union = nested_union.children[1]
assert nested_union.children[0].type == 'select'
assert nested_union.children[1].type == 'select'
def test_set_variable_from_subquery():
raw_sql = '''
SET @s :=
(SELECT col FROM table JOIN table2 ON table.a = table.b),
LOCAL l = 20
'''
ast = parse(raw_sql)
assert len(ast) > 0
ast = ast[0]
assert ast.type == 'set'
assert len(ast.children) == 2
assert ast.children[0].type == 'variable'
assert len(ast.children[0].children) == 1
op = ast.children[0].children[0]
assert op.type == 'operator'
assert len(op.children) == 2
assert op.children[0].value == '@s'
assert op.children[1].type == 'select'
op = ast.children[1].children[0]
assert op.type == 'operator'
assert len(op.children) == 2
assert op.children[0].value == '@@LOCAL.l'
assert op.children[1].value == '20'
def test_set_variable_modifiers_is_parsed():
raw_sql = '''
SET GLOBAL g = 1, SESSION s = 2, LOCAL l = 3, @var = 1;
SET @@GLOBAL.g = 1, @@SESSION.s = 2, @@LOCAL.l = 3, @var = 1;
'''
ast = parse(raw_sql)
assert len(ast) == 2
def test(set_ast):
assert set_ast.type == 'set'
assert len(set_ast.children) == 4
op = set_ast.children[0].children[0]
assert op.type == 'operator'
assert len(op.children) == 2
assert op.children[0].value == '@@GLOBAL.g'
op = set_ast.children[1].children[0]
assert op.type == 'operator'
assert len(op.children) == 2
assert op.children[0].value == '@@SESSION.s'
op = set_ast.children[2].children[0]
assert op.type == 'operator'
assert len(op.children) == 2
assert op.children[0].value == '@@LOCAL.l'
op = set_ast.children[3].children[0]
assert op.type == 'operator'
assert len(op.children) == 2
assert op.children[0].value == '@var'
test(ast[0])
test(ast[1])
def test_match_expr_is_parsed():
raw_sql = '''
MATCH(col1, col2) AGAINST ("string")
match(col3. col4) AGAINST ((1 * 2))
MATCH(col1) AGAINST ('some other string' IN BOOLEAN MODE)
MATCH(col1) against ('some other string' WITH QUERY EXPANSION)
'''
ast = parse(raw_sql)
for child_ast in ast:
assert child_ast.type == 'match'
assert child_ast.value.lower() == 'match'
assert len(child_ast.children) == 2
assert child_ast.children[0].type == 'paren_group'
assert child_ast.children[1].type == 'operator'
assert child_ast.children[1].value.lower() == 'against'
ast = ast.pop()
against_op = ast.children.pop()
modifier_op = against_op.children.pop()
assert modifier_op.type == 'operator'
assert modifier_op.value == 'modifier'
assert len(modifier_op.children) == 3
for modifier in modifier_op.children:
assert modifier.type == 'keyword'
assert len(modifier.value) > 0
def test_simple_insert_is_parsed():
raw_sql = '''
INSERT DELAYED INTO table (col1, col2, col3) VALUES (100, 200, 300)
'''
ast = parse(raw_sql)
assert len(ast) > 0
ast = ast[0]
assert ast.type == 'insert'
assert len(ast.children) == 4
modifier = ast.get_child('modifier')
assert modifier is not None
assert len(modifier.children) == 1
into = ast.get_child('into')
assert into is not None
assert len(into.children) == 1
assert into.children[0].children[0].value == 'table'
columns = ast.get_child('columns')
assert columns is not None
assert len(columns.children) == 1
assert len(columns.children[0].children) == 3
values = ast.get_child('values')
assert values is not None
assert len(values.children) == 1
assert len(values.children[0].children) == 3
def test_like_operator_is_parsed():
raw_sql = '''
a like 'something'
a not like '%something%' escape '%'
'''
ast = parse(raw_sql)
for child_ast in ast:
assert child_ast.type == 'operator'
assert child_ast.value == 'like'
assert len(child_ast.children) > 1
assert ast[0].children[0].type == 'unknown'
assert ast[0].children[0].value == 'a'
assert ast[0].children[1].type == 'literal'
assert ast[0].children[1].value == "'something'"
assert ast[1].children[0].type == 'unknown'
assert ast[1].children[0].value == 'a'
assert ast[1].children[1].type == 'operator'
assert ast[1].children[1].value == "not"
not_op = ast[1].children[1]
assert len(not_op.children) == 1
assert not_op.children[0].type == 'literal'
assert not_op.children[0].value == "'%something%'"
def test_parens_hae_precendance():
raw_sql = '''
(-1 * (2 + 3)) MOD 3
'''
ast = parse(raw_sql)
ast = ast[0]
assert ast.type == 'operator'
assert ast.value == 'mod'
assert len(ast.children) == 2
last = ast.children[0]
rast = ast.children[1]
assert rast.type == 'literal'
assert rast.value == '3'
assert last.type == 'paren_group'
assert len(last.children) == 1
ast = last.children[0]
assert ast.type == 'operator'
assert ast.value == '*'
assert len(ast.children) == 2
last = ast.children[0]
rast = ast.children[1]
assert rast.type == 'paren_group'
assert rast.children[0].type == 'operator'
def test_simple_update_is_parsed():
raw_sql = '''
UPDATE table1 SET col = 20 WHERE id > 1 ORDER BY id LIMIT 10
'''
ast = parse(raw_sql)
assert len(ast) > 0
ast = ast[0]
assert ast.type == 'update'
assert len(ast.children) == 5
table_refs = ast.get_child('table_references')
assert table_refs is not None
assert len(table_refs.children) == 1
table_ref = table_refs.children[0]
assert len(table_ref.children) == 1
table = table_ref.children[0]
assert table.value == 'table1'
assignment_list = ast.get_child('assignment_list')
assert assignment_list is not None
assert assignment_list.value == 'SET'
assert len(assignment_list.children) == 1
assert assignment_list.children[0].type == 'operator'
assert assignment_list.children[0].value == '='
assert ast.get_child('where') is not None
assert ast.get_child('order') is not None
assert ast.get_child('limit') is not None
def test_subquery_as_column():
raw_sql = '''
SELECT
col1,
(SELECT col2 FROM tbl2 LIMIT 1)
FROM tbl
'''
ast = parse(raw_sql)
assert len(ast) > 0
ast = ast[0]
columns = ast.get_child('columns')
tables = ast.get_child('from')
assert len(columns.children) == 2
assert len(tables.children) == 1
col = columns.children[0]
col_ = col.children[0]
assert col_.value == 'col1'
col = columns.children[1]
col_ = col.children[0]
assert col_.type == 'select'
def test_multiple_join_single_join_spec_is_parsed():
raw_sql = '''
SELECT
a,
b,
c
FROM t1 LEFT JOIN t2
RIGHT JOIN t3
NATURAL LEFT OUTER JOIN t4
ON (t1.a = t2.c AND t2.c = t3.d and t3.e = t4.e)
'''
ast = parse(raw_sql)
assert len(ast) > 0
ast = ast[0]
from_node = ast.get_child('from')
assert from_node is not None
assert len(from_node.children) == 1
join_spec = from_node.get_child('join_spec')
assert join_spec is not None
assert len(join_spec.children) == 1
assert join_spec.children[0]
def test_simple_select_stmt_is_parsed():
raw_sql = '''
SELECT col1, col2 FROM tbl1
'''
ast = parse(raw_sql)
assert len(ast) > 0
ast = ast[0]
assert ast.type == 'select'
assert len(ast.children) == 2
columns = ast.get_child('columns')
table = ast.get_child('from')
assert len(columns.children) == 2
assert len(table.children) == 1
col = columns.children[0]
col = col.get_last_child()
assert col.value == 'col1'
col = columns.children[1]
col = col.get_last_child()
assert col.value == 'col2'
table = table.get_last_child()
assert table.value == 'tbl1'
def test_natural_join_is_parsed():
raw_sql = '''
SELECT
a,
b,
c
FROM t1 NATURAL JOIN t2 t on t1.a = t.id
'''
ast = parse(raw_sql)
assert len(ast) > 0
ast = ast[0]
from_node = ast.get_child('from')
assert from_node is not None
assert len(from_node.children) == 1
join_type = from_node.get_child('join_type')
assert join_type is not None
assert join_type.value == 'NATURAL'
assert len(join_type.children) == 1
assert join_type.children[0].type == 'join'
def test_right_outer_join_is_parsed():
raw_sql = '''
SELECT
a,
b,
c
FROM t1 RIGHT OUTER JOIN t2 t on t1.a = t.id
'''
ast = parse(raw_sql)
assert len(ast) > 0
ast = ast[0]
from_node = ast.get_child('from')
assert from_node is not None
assert len(from_node.children) == 1
join_dir = from_node.get_child('join_dir')
assert join_dir is not None
assert join_dir.value == 'RIGHT'
assert len(join_dir.children) == 1
join_type = join_dir.children[0]
assert join_type is not None
assert join_type.value == 'OUTER'
assert len(join_type.children) == 1
assert join_type.children[0].type == 'join'
def test_left_join_is_parsed():
raw_sql = '''
SELECT
a,
b,
c
FROM t1 LEFT JOIN t2 t on t1.a = t.id
'''
ast = parse(raw_sql)
assert len(ast) > 0
ast = ast[0]
from_node = ast.get_child('from')
assert from_node is not None
assert len(from_node.children) == 1
join_dir = from_node.get_child('join_dir')
assert join_dir is not None
assert join_dir.value == 'LEFT'
assert len(join_dir.children) == 1
assert join_dir.children[0].type == 'join'
def test_collate_keyword_is_parsed():
raw_sql = '''
expr COLLATE utf8
FN(col1 collate latin)
'''
ast = parse(raw_sql)
assert ast[0].type == 'operator'
assert ast[0].value == 'collate'
assert len(ast[0].children) == 2
assert ast[0].children[0].type == 'unknown'
assert ast[0].children[0].value == 'expr'
assert ast[0].children[1].type == 'collation'
assert ast[0].children[1].value == 'utf8'
assert ast[1].children[0].type == 'operator'
assert ast[1].children[0].value == 'collate'
collate_op = ast[1].children[0]
assert collate_op.children[0].type == 'unknown'
assert collate_op.children[0].value == 'col1'
assert collate_op.children[1].type == 'collation'
assert collate_op.children[1].value == 'latin'
def test_param_marker_is_parsed():
raw_sql = '''
?
FN(?, 'literal')
'''
ast = parse(raw_sql)
assert ast[0].type == 'param_marker'
assert ast[1].children[0].type == 'param_marker'
def test_variable_is_parsed():
raw_sql = '''
@variable, @@GLOBAL, @@SESSION
@`my-var` @'my-var' @"my-var"
'''
ast = parse(raw_sql)
for child_ast in ast:
assert child_ast.type == 'variable'
assert len(child_ast.value) > 0
def test_not_operator_precedance():
raw_sql = '''
NOT 1 + 2 MOD (3 / 4)
'''
ast = parse(raw_sql)
ast = ast.pop()
assert len(ast.children) == 1
assert ast.type == 'unary_operator'
assert ast.value == 'not'
def test_update_joined_tables_stmt_is_parsed():
raw_sql = '''
UPDATE table1 t1, table2 AS t2 JOIN table3 t3 ON (t1.id = t2.id AND t2.id =
t3.id) SET col = 20, col1 = (col5 + table2.col4) WHERE id > 1 ORDER BY id LIMIT 10
'''
ast = parse(raw_sql)
assert len(ast) > 0
ast = ast[0]
assert ast.type == 'update'
assert len(ast.children) == 5
table_refs = ast.get_child('table_references')
assert table_refs is not None
assert len(table_refs.children) == 2
table_ref = table_refs.children[0]
assert len(table_ref.children) == 2
table = table_ref.children[0]
assert table.value == 'table1'
table = table_ref.children[1]
assert table.type == 'alias'
table_ref = table_refs.children[1]
assert len(table_ref.children) == 3
table = table_ref.children[0]
assert table.value == 'table2'
table = table_ref.children[1]
assert table.type == 'alias'
table = table_ref.children[2]
assert table.type == 'join'
assignment_list = ast.get_child('assignment_list')
assert assignment_list is not None
assert assignment_list.value == 'SET'
assert len(assignment_list.children) == 2
assert assignment_list.children[0].type == 'operator'
assert assignment_list.children[0].value == '='
assert ast.get_child('where') is not None
assert ast.get_child('order') is not None
assert ast.get_child('limit') is not None
def test_delete_using_is_parsed():
raw_sql = '''
DELETE t1.*, t2.* FROM table1 USING t1 JOIN t2 WHERE id > 1 ORDER BY id LIMIT 10
'''
ast = parse(raw_sql)
assert len(ast) > 0
ast = ast[0]
assert ast.type == 'delete'
assert len(ast.children) == 6
table_refs = ast.get_child('table_references')
assert table_refs is not None
assert len(table_refs.children) == 2
table_ref = table_refs.children[0]
assert len(table_ref.children) == 1
table = table_ref.children[0]
assert table.value == 't1'
table_ref = table_refs.children[1]
assert len(table_ref.children) == 1
table = table_ref.children[0]
assert table.value == 't2'
from_ = ast.get_child('from')
assert from_ is not None
assert len(from_.children) == 1
table_ref = from_.children[0]
assert len(table_ref.children) == 1
table = table_ref.children[0]
assert table.value == 'table1'
using = ast.get_child('using')
assert len(using.children) == 1
table_ref = using.children[0]
assert len(table_ref.children) == 2
assert table_ref.children[0].value == 't1'
assert table_ref.children[1].type == 'join'
assert ast.get_child('where') is not None
assert ast.get_child('order') is not None
assert ast.get_child('limit') is not None
def test_do_statement_is_parsed():
raw_sql = '''
do 1 + 1, sleep(5)
'''
ast = parse(raw_sql)
assert len(ast) > 0
ast = ast[0]
assert ast.type == 'do'
assert len(ast.children) == 2
assert ast.children[0].type == 'operator'
assert ast.children[1].type == 'function'
def test_mul_div_operators_have_precedance():
raw_sql = '''
1 * 2 + 9 / 3
'''
ast = parse(raw_sql)
ast = ast[0]
assert ast.type == 'operator'
assert ast.value == '+'
assert len(ast.children) == 2
assert ast.children[0].type == 'operator'
assert ast.children[0].value == '*'
assert ast.children[1].type == 'operator'
assert ast.children[1].value == '/'
def test_exists_expr_is_parsed():
raw_sql = '''
exists (select * from table)
'''
ast = parse(raw_sql)
ast = ast[0]
assert ast.type == 'unary_operator'
assert ast.value == 'exists'
assert len(ast.children) == 1
assert ast.children[0].type == 'select'
assert ast.children[0].value == 'select'
def test_interval_is_parsed():
raw_sql = '''
INTERVAL '1:1' MINUTE_SECOND
INTERVAL 1 DAY
interval '1.99999' SECOND_MICROSECOND
'''
ast = parse(raw_sql)
for child_ast in ast:
assert child_ast.type == 'interval'
assert child_ast.value.lower() == 'interval'
assert len(child_ast.children) == 2
assert child_ast.children[0].type == 'literal'
assert child_ast.children[1].type == 'keyword'
