def parse(tokens): expression, next_token = ExpressionParser.parse( tokens, can_alias=False, until_one_of=("group", "having", "order", "limit", "offset", ";"), ) return WhereClause(expression), next_token
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 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_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_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_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 parse(tokens): expression = ExpressionParser.parse(tokens) return WhereClause(expression)
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_parenthesis_expression_where_clause(): actual = WhereClauseParser.parse(to_tokens("(col + 1) = col2")) expected = WhereClause( Condition(Parenthesis(Addition(Column("col"), Integer(1))), "=", Column("col2"))) assert actual == expected
def test_where_different_predicate(): actual = WhereClauseParser.parse(to_tokens("col <> 3")) expected = WhereClause(Condition(Column("col"), "<>", Integer(3)), ) assert actual == expected
def test_where_clause(): actual = WhereClauseParser.parse(to_tokens("col = 3")) expected = WhereClause(Condition(Column("col"), "=", Integer(3))) assert actual == expected