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'