def test_where_clause_keeps_generator_intact_is_null_condition():
tokens = to_tokens("col is null group by col")
actual, next_token = WhereClauseParser.parse(tokens)
expected = WhereClause(Condition(Column("col"), "is", Null()))
assert actual == expected
assert next_token == "group"
assert list(tokens) == ["by", "col"]
def test_nested_parenthesis_boolean():
actual = WhereClauseParser.parse(
to_tokens("(col = 1 and col2=4) or (col = 2 and (col =6 or col=9))"))
expected = WhereClause(
BooleanCondition(
"or",
Parenthesis(
BooleanCondition(
"and",
Condition(Column("col"), "=", Integer(1)),
Condition(Column("col2"), "=", Integer(4)),
)),
Parenthesis(
BooleanCondition(
"and",
Condition(Column("col"), "=", Integer(2)),
Parenthesis(
BooleanCondition(
"or",
Condition(Column("col"), "=", Integer(6)),
Condition(Column("col"), "=", Integer(9)),
)),
)),
))
assert actual == expected
def test_where_clause_keeps_generator_intact():
tokens = to_tokens("col = 3 group by col")
actual, next_token = WhereClauseParser.parse(tokens)
expected = WhereClause(Condition(Column("col"), "=", Integer(3)))
assert actual == expected
assert next_token == "group"
assert list(tokens) == ["by", "col"]
def test_subquery():
actual = SQLStatementParser.parse(
to_tokens(
"SELECT col"
" from (select count(*) col"
" from table group by x) WHERE col > 10 ORDER BY col DESC;"
)
)
expected = SelectStatement(
expressions=[Column("col")],
from_statement=Parenthesis(
SelectStatement(
expressions=[
Alias(CountFunctionCall(Column("*")), "col", with_as=False)
],
from_statement=Table(Column("table")),
group_by_clause=GroupByClause(Column("x")),
semi_colon=False,
)
),
where_clause=WhereClause(Condition(Column("col"), ">", Integer(10))),
order_by_clause=OrderByClause(OrderByItem(Column("col"), has_desc=True)),
semi_colon=True,
)
assert actual == expected
def _validate(self):
self.validated = True
try:
select_statement = SQLStatementParser.parse(to_tokens(self.sql))
self.errors = select_statement.validate()
except ParsingError as ex:
self.errors.append(str(ex))
def test_group_by_without_from():
actual = SQLStatementParser.parse(to_tokens("SELECT 1 GROUP BY 2"))
expected = SelectStatement(
expressions=[Integer(1)],
group_by_clause=GroupByClause(Integer(2)),
semi_colon=False,
)
assert actual == expected
def test_index_access_right_hand():
actual = ExpressionParser.parse(to_tokens("field = array[0]"))
expected = Condition(
Column("field"),
"=",
Index(Column("array"), [Integer(0)]),
)
assert actual == expected
def test_select_distinct_on():
actual = SQLStatementParser.parse(to_tokens("SELECT DISTINCT ON (col) col"))
expected = SelectStatement(
select_distinct=True,
select_distinct_on=[Column("col")],
expressions=[Column("col")],
semi_colon=False,
)
assert actual == expected
def test_boolean_where_clause():
actual = WhereClauseParser.parse(to_tokens("col = 3 and field = 5"))
expected = WhereClause(
BooleanCondition(
"and",
Condition(Column("col"), "=", Integer(3)),
Condition(Column("field"), "=", Integer(5)),
))
assert actual == expected
def test_from_subquery():
actual = FromStatementParser.parse(
to_tokens("(select field from table_stmt)"))
expected = Parenthesis(
SelectStatement(
expressions=[Column("field")],
from_statement=Table("table_stmt"),
semi_colon=False,
))
assert actual == expected
def test_unnest_with_offset_as_alias():
sql = "unnest(table) with offset as o"
actual = FromStatementParser.parse(to_tokens(sql))
expected = Unnest(
FunctionCall("unnest", Column("table")),
with_offset=True,
with_offset_as=True,
offset_alias="o",
)
assert actual == expected
def test_unnest():
sql = "unnest(table)"
actual = FromStatementParser.parse(to_tokens(sql))
expected = Unnest(
FunctionCall("unnest", Column("table")),
with_offset=False,
with_offset_as=False,
offset_alias=None,
)
assert actual == expected
def test_order_by_clause():
actual = SQLStatementParser.parse(
to_tokens("SELECT col FROM t ORDER BY col, 2"))
expected = SelectStatement(
expressions=[Column("col")],
from_statement=Table("t"),
order_by_clause=OrderByClause(OrderByItem(Column("col")),
OrderByItem(Integer(2))),
semi_colon=False,
)
assert actual == expected
def test_limit_parentheses():
actual = SQLStatementParser.parse(to_tokens("SELECT 1 LIMIT (((3)))"))
expected = SelectStatement(
expressions=[Integer(1)],
limit_clause=LimitClause(
limit_all=False,
expression=Parenthesis(Parenthesis(Parenthesis(Integer(3)))),
),
semi_colon=False,
)
assert actual == expected
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 test_chained_field():
actual, _ = ExpressionParser.parse(
to_tokens("table.field[offset(0)].subfield[offset(0)]"))
expected = ChainedColumns(
Column("table"),
ChainedColumns(
Index(Column("field"), [FunctionCall("offset", Integer(0))]),
Index(Column("subfield"), [FunctionCall("offset", Integer(0))]),
),
)
assert actual == expected
def test_boolean_condition():
actual, _ = ExpressionParser.parse(to_tokens("f1 IS NOT NULL AND f2 > 0 fnew"))
expected = Alias(
BooleanCondition(
"AND",
Condition(Column("f1"), "is not", Null()),
Condition(Column("f2"), ">", Integer(0)),
),
with_as=False,
alias="fnew",
)
assert actual == expected
def test_between_where_clause():
actual = WhereClauseParser.parse(to_tokens("col between 3 and 5"))
expected = WhereClause(
Condition(
Column("col"),
"between",
BooleanCondition(
"and",
Integer(3),
Integer(5),
),
))
assert actual == expected
def test_consecutive_parenthesis():
actual = ExpressionParser.parse(to_tokens("((col+1) = 3 AND col2=4)"))
expected = Parenthesis(
BooleanCondition(
"and",
Condition(Parenthesis(Addition(Column("col"), Integer(1))), "=",
Integer(3)),
Condition(
Column("col2"),
"=",
Integer(4),
),
))
assert actual == expected
def test_nested_date_functions():
actual = ExpressionParser.parse(
to_tokens("DATE(TIMESTAMP_TRUNC(CAST(a.date AS TIMESTAMP), MONTH))"))
expected = FunctionCall(
"date",
FunctionCall(
"timestamp_trunc",
FunctionCall(
"cast",
Alias(Column("a.date"), alias=Type("timestamp"),
with_as=True)),
Type("month"),
),
)
assert actual == expected
def test_parenthesis_boolean_where_clause():
actual = WhereClauseParser.parse(
to_tokens("(col = 3 and field = 5) or (f2 or f3)"))
expected = WhereClause(
BooleanCondition(
"or",
Parenthesis(
BooleanCondition(
"and",
Condition(Column("col"), "=", Integer(3)),
Condition(Column("field"), "=", Integer(5)),
)),
Parenthesis(BooleanCondition("or", Column("f2"), Column("f3"))),
))
assert actual == expected
def test_multiple_args_boolean_condition():
actual = WhereClauseParser.parse(
to_tokens("(col = 1 and col2=4 and col3=4)"))
expected = WhereClause(
Parenthesis(
BooleanCondition(
"and",
Condition(Column("col"), "=", Integer(1)),
BooleanCondition(
"and",
Condition(Column("col2"), "=", Integer(4)),
Condition(Column("col3"), "=", Integer(4)),
),
), ))
assert actual == expected
def test_nested_date_functions():
actual, _ = ExpressionParser.parse(
to_tokens("DATE(TIMESTAMP_TRUNC(CAST(date AS TIMESTAMP), MONTH))"))
expected = FunctionCall(
"DATE",
FunctionCall(
"TIMESTAMP_TRUNC",
CastFunctionCall(
Column("date"),
Type("TIMESTAMP"),
),
Type("MONTH"),
),
)
assert actual == expected
def test_boolean_condition_as_expression():
sql = "field is not null and col > 0"
actual, _ = ExpressionParser.parse(to_tokens(sql))
expected = BooleanCondition(
"and",
Condition(
Column("field"),
"is not",
Null(),
),
Condition(
Column("col"),
">",
Integer(0),
),
)
assert actual == expected
def test_select_boolean_condition_expression():
sql = "select field is not null and col > 0 from t;"
actual = SQLStatementParser.parse(to_tokens(sql))
expected = SelectStatement(
expressions=[
BooleanCondition(
"and",
Condition(
Column("field"),
"is not",
Null(),
),
Condition(
Column("col"),
">",
Integer(0),
),
)
],
from_statement=Table(Column("t")),
)
assert actual == expected
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_escaped_string():
value = "'O\\'Connor'"
assert list(to_tokens(value)) == ["'", "O\\'Connor", "'"]
def test_chained_columns():
value = "x.y.z"
assert list(to_tokens(value)) == ["x", ".", "y", ".", "z"]
def test_index():
value = "x[3]"
assert list(to_tokens(value)) == ["x", "[", "3", "]"]
def test_lt():
value = "x<3"
assert list(to_tokens(value)) == ["x", "<", "3"]
