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"]