def test_multiple_indices_access():
actual, _ = ExpressionParser.parse(to_tokens("array[index(0), 'foo'] alias"))
expected = Alias(
Index(
Column("array"),
[FunctionCall("index", Integer(0)), String("foo", quotes="'")],
),
with_as=False,
alias="alias",
)
assert actual == expected
def parse(tokens, start_quote, prefix=None):
string_content = next(tokens, None)
if string_content == start_quote:
end_quote = string_content
string_content = ""
else:
end_quote = next(tokens)
string_expression = String(string_content, quotes=start_quote, prefix=prefix)
if start_quote != end_quote:
raise ValueError("Did not find ending quote {}".format(start_quote))
return string_expression
def test_chained_columns_with_arithmetic_operator():
actual, _ = ExpressionParser.parse(
to_tokens("IF((a.field + b.field) = 200, 'true', 'false') fa"))
expected = Alias(
FunctionCall(
"IF", *[
Condition(
Parenthesis(
ArithmaticOperator(
"+",
ChainedColumns(Column("a"), Column("field")),
ChainedColumns(Column("b"), Column("field")),
)),
"=",
Integer(200),
),
String("true", quotes="'"),
String("false", quotes="'"),
]),
with_as=False,
alias="fa",
)
assert actual == expected
def test_string_value_back_quotes():
actual = ExpressionParser.parse(to_tokens("`val`"))
expected = String("val", quotes="`")
assert actual == expected
def test_string_value_double_quotes():
actual = ExpressionParser.parse(to_tokens('"val"'))
expected = String("val", quotes='"')
assert actual == expected
def test_string_value():
actual = ExpressionParser.parse(to_tokens("'VAL'"))
expected = String("VAL", quotes="'")
assert actual == expected
def test_simple_function_multiple_params():
actual = ExpressionParser.parse(to_tokens("test(col, 'Test')"))
expected = FunctionCall("test", Column("col"), String("Test", quotes="'"))
assert actual == expected
def test_nested_joins():
sql = """
SELECT COALESCE(sq_1.col, sq_2.col) f0_
FROM (SELECT ANY_VALUE(col) col,
LAST_VALUE(ANY_VALUE(col2)) OVER (PARTITION BY ANY_VALUE(col) ORDER BY SUM(clicks) ASC, SUM(metric) ASC RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) last,
hash
FROM (SELECT *
FROM `events`
WHERE _TABLE_SUFFIX BETWEEN '20200410' AND '20200510')
JOIN
(SELECT * EXCEPT (hash)
FROM (SELECT *,
ROW_NUMBER() OVER (PARTITION BY hash) AS rn
FROM `test-table`
WHERE _TABLE_SUFFIX BETWEEN '20200401' AND '20200501')
WHERE rn = 1)
USING (hash)
GROUP BY hash) sq_1
FULL OUTER JOIN
(SELECT ANY_VALUE(col) col,
hash
FROM (SELECT *
FROM `events`
WHERE _TABLE_SUFFIX BETWEEN '20200310' AND '20200410')
JOIN
(SELECT * EXCEPT (hash),
FROM (SELECT *,
ROW_NUMBER() OVER (PARTITION BY hash) AS rn
FROM `test-table`
WHERE _TABLE_SUFFIX BETWEEN '20200301' AND '20200401')
WHERE rn = 1)
USING (hash)
GROUP BY hash) sq_2
ON sq_1.hash = sq_2.hash
WHERE sq_1.last = 1
GROUP BY f0_
""" # noqa
actual = SQLStatementParser.parse(to_tokens(sql))
expected = SelectStatement(
expressions=[
Alias(
FunctionCall("coalesce", Column("sq_1.col"),
Column("sq_2.col")),
"f0_",
with_as=False,
)
],
from_statement=Join(
"FULL OUTER JOIN",
left_from=Alias(
Parenthesis(
SelectStatement(
expressions=[
Alias(
FunctionCall("any_value", Column("col")),
"col",
with_as=False,
),
Alias(
AnalyticsClause(
FunctionCall(
"last_value",
FunctionCall("any_value",
Column("col2")),
),
partition_by=[
FunctionCall("any_value",
Column("col"))
],
order_by=OrderByClause(
OrderByItem(
FunctionCall(
"sum", Column("clicks")),
has_asc=True,
),
OrderByItem(
FunctionCall(
"sum", Column("metric")),
has_asc=True,
),
),
frame_clause=WindowFrameClause(
"range",
"between unbounded preceding and unbounded following", # noqa
),
),
"last",
with_as=False,
),
Column("hash"),
],
from_statement=Join(
join_type="JOIN",
left_from=Parenthesis(
SelectStatement(
expressions=[Column("*")],
from_statement=Table(
String("events", quotes="`")),
where_clause=WhereClause(
Condition(
Column("_table_suffix"),
"between",
BooleanCondition(
"and",
String("20200410", quotes="'"),
String("20200510", quotes="'"),
),
)),
semi_colon=False,
)),
right_from=Parenthesis(
SelectStatement(
expressions=[
ExceptClause(Column("*"),
[Column("hash")])
],
from_statement=Parenthesis(
SelectStatement(
expressions=[
Column("*"),
Alias(
AnalyticsClause(
FunctionCall(
"row_number"),
partition_by=[
Column("hash")
],
order_by=None,
frame_clause=None,
),
Column("rn"),
with_as=True,
),
],
from_statement=Table(
String("test-table",
quotes="`")),
where_clause=WhereClause(
Condition(
Column("_table_suffix"),
"between",
BooleanCondition(
"and",
String("20200401",
quotes="'"),
String("20200501",
quotes="'"),
),
)),
semi_colon=False,
)),
where_clause=WhereClause(
Condition(Column("rn"), "=",
Integer(1))),
semi_colon=False,
)),
on=None,
using=UsingClause(Parenthesis(Column("hash"))),
),
group_by_clause=GroupByClause(Column("hash")),
semi_colon=False,
)),
"sq_1",
with_as=False,
),
right_from=Alias(
Parenthesis(
SelectStatement(
expressions=[
Alias(
FunctionCall("any_value", Column("col")),
"col",
with_as=False,
),
Column("hash"),
],
from_statement=Join(
join_type="JOIN",
left_from=Parenthesis(
SelectStatement(
expressions=[Column("*")],
from_statement=Table(
String("events", quotes="`")),
where_clause=WhereClause(
Condition(
Column("_table_suffix"),
"between",
BooleanCondition(
"and",
String("20200310", quotes="'"),
String("20200410", quotes="'"),
),
)),
semi_colon=False,
)),
right_from=Parenthesis(
SelectStatement(
expressions=[
ExceptClause(Column("*"),
[Column("hash")])
],
from_statement=Parenthesis(
SelectStatement(
expressions=[
Column("*"),
Alias(
AnalyticsClause(
FunctionCall(
"row_number"),
partition_by=[
Column("hash")
],
order_by=None,
frame_clause=None,
),
Column("rn"),
with_as=True,
),
],
from_statement=Table(
String("test-table",
quotes="`")),
where_clause=WhereClause(
Condition(
Column("_table_suffix"),
"between",
BooleanCondition(
"and",
String("20200301",
quotes="'"),
String("20200401",
quotes="'"),
),
)),
semi_colon=False,
), ),
where_clause=WhereClause(
Condition(Column("rn"), "=",
Integer(1))),
semi_colon=False,
)),
on=None,
using=UsingClause(Parenthesis(Column("hash"))),
),
group_by_clause=GroupByClause(Column("hash")),
semi_colon=False,
)),
"sq_2",
with_as=False,
),
on=OnClause(
Condition(Column("sq_1.hash"), "=", Column("sq_2.hash"))),
using=None,
),
where_clause=WhereClause(
Condition(Column("sq_1.last"), "=", Integer(1))),
group_by_clause=GroupByClause(Column("f0_")),
semi_colon=False,
)
assert actual == expected
def test_parse_date_function():
actual = ExpressionParser.parse(to_tokens("col >= DATE('2020-01-01')"))
expected = Condition(
Column("col"), ">=",
FunctionCall("date", String("2020-01-01", quotes="'")))
assert actual == expected
def test_parse_date_function1():
actual = ExpressionParser.parse(to_tokens("DATE('2020-01-01')"))
expected = FunctionCall("date", String("2020-01-01", quotes="'"))
assert actual == expected
def test_parenthesis_multiple_elements():
actual = ExpressionParser.parse(to_tokens("(field,other_field,3,'test')"))
expected = Parenthesis(Column("field"), Column("other_field"), Integer(3),
String("test", quotes="'"))
assert actual == expected
def test_aliased_string_without_as():
actual = ExpressionParser.parse(to_tokens("'col' column_name"))
expected = Alias(String("col", quotes="'"),
alias="column_name",
with_as=False)
assert actual == expected
def test_function_with_single_comma_string_param():
actual, _ = ExpressionParser.parse(to_tokens("test(',')"))
expected = FunctionCall("test", String(",", quotes="'"))
assert actual == expected