sqlglot.optimizer.canonicalize
1from __future__ import annotations 2 3import itertools 4 5from sqlglot import exp 6 7 8def canonicalize(expression: exp.Expression) -> exp.Expression: 9 """Converts a sql expression into a standard form. 10 11 This method relies on annotate_types because many of the 12 conversions rely on type inference. 13 14 Args: 15 expression: The expression to canonicalize. 16 """ 17 exp.replace_children(expression, canonicalize) 18 19 expression = add_text_to_concat(expression) 20 expression = replace_date_funcs(expression) 21 expression = coerce_type(expression) 22 expression = remove_redundant_casts(expression) 23 expression = ensure_bool_predicates(expression) 24 expression = remove_ascending_order(expression) 25 26 return expression 27 28 29def add_text_to_concat(node: exp.Expression) -> exp.Expression: 30 if isinstance(node, exp.Add) and node.type and node.type.this in exp.DataType.TEXT_TYPES: 31 node = exp.Concat(expressions=[node.left, node.right]) 32 return node 33 34 35def replace_date_funcs(node: exp.Expression) -> exp.Expression: 36 if isinstance(node, exp.Date) and not node.expressions and not node.args.get("zone"): 37 return exp.cast(node.this, to=exp.DataType.Type.DATE) 38 if isinstance(node, exp.Timestamp) and not node.expression: 39 return exp.cast(node.this, to=exp.DataType.Type.TIMESTAMP) 40 return node 41 42 43def coerce_type(node: exp.Expression) -> exp.Expression: 44 if isinstance(node, exp.Binary): 45 _coerce_date(node.left, node.right) 46 elif isinstance(node, exp.Between): 47 _coerce_date(node.this, node.args["low"]) 48 elif isinstance(node, exp.Extract) and not node.expression.type.is_type( 49 *exp.DataType.TEMPORAL_TYPES 50 ): 51 _replace_cast(node.expression, exp.DataType.Type.DATETIME) 52 53 return node 54 55 56def remove_redundant_casts(expression: exp.Expression) -> exp.Expression: 57 if ( 58 isinstance(expression, exp.Cast) 59 and expression.to.type 60 and expression.this.type 61 and expression.to.type.this == expression.this.type.this 62 ): 63 return expression.this 64 return expression 65 66 67def ensure_bool_predicates(expression: exp.Expression) -> exp.Expression: 68 if isinstance(expression, exp.Connector): 69 _replace_int_predicate(expression.left) 70 _replace_int_predicate(expression.right) 71 72 elif isinstance(expression, (exp.Where, exp.Having, exp.If)): 73 _replace_int_predicate(expression.this) 74 75 return expression 76 77 78def remove_ascending_order(expression: exp.Expression) -> exp.Expression: 79 if isinstance(expression, exp.Ordered) and expression.args.get("desc") is False: 80 # Convert ORDER BY a ASC to ORDER BY a 81 expression.set("desc", None) 82 83 return expression 84 85 86def _coerce_date(a: exp.Expression, b: exp.Expression) -> None: 87 for a, b in itertools.permutations([a, b]): 88 if ( 89 a.type 90 and a.type.this == exp.DataType.Type.DATE 91 and b.type 92 and b.type.this not in (exp.DataType.Type.DATE, exp.DataType.Type.INTERVAL) 93 ): 94 _replace_cast(b, exp.DataType.Type.DATE) 95 96 97def _replace_cast(node: exp.Expression, to: exp.DataType.Type) -> None: 98 node.replace(exp.cast(node.copy(), to=to)) 99 100 101def _replace_int_predicate(expression: exp.Expression) -> None: 102 if isinstance(expression, exp.Coalesce): 103 for _, child in expression.iter_expressions(): 104 _replace_int_predicate(child) 105 elif expression.type and expression.type.this in exp.DataType.INTEGER_TYPES: 106 expression.replace(exp.NEQ(this=expression.copy(), expression=exp.Literal.number(0)))
9def canonicalize(expression: exp.Expression) -> exp.Expression: 10 """Converts a sql expression into a standard form. 11 12 This method relies on annotate_types because many of the 13 conversions rely on type inference. 14 15 Args: 16 expression: The expression to canonicalize. 17 """ 18 exp.replace_children(expression, canonicalize) 19 20 expression = add_text_to_concat(expression) 21 expression = replace_date_funcs(expression) 22 expression = coerce_type(expression) 23 expression = remove_redundant_casts(expression) 24 expression = ensure_bool_predicates(expression) 25 expression = remove_ascending_order(expression) 26 27 return expression
Converts a sql expression into a standard form.
This method relies on annotate_types because many of the conversions rely on type inference.
Arguments:
- expression: The expression to canonicalize.
36def replace_date_funcs(node: exp.Expression) -> exp.Expression: 37 if isinstance(node, exp.Date) and not node.expressions and not node.args.get("zone"): 38 return exp.cast(node.this, to=exp.DataType.Type.DATE) 39 if isinstance(node, exp.Timestamp) and not node.expression: 40 return exp.cast(node.this, to=exp.DataType.Type.TIMESTAMP) 41 return node
44def coerce_type(node: exp.Expression) -> exp.Expression: 45 if isinstance(node, exp.Binary): 46 _coerce_date(node.left, node.right) 47 elif isinstance(node, exp.Between): 48 _coerce_date(node.this, node.args["low"]) 49 elif isinstance(node, exp.Extract) and not node.expression.type.is_type( 50 *exp.DataType.TEMPORAL_TYPES 51 ): 52 _replace_cast(node.expression, exp.DataType.Type.DATETIME) 53 54 return node
def
remove_redundant_casts( expression: sqlglot.expressions.Expression) -> sqlglot.expressions.Expression:
def
ensure_bool_predicates( expression: sqlglot.expressions.Expression) -> sqlglot.expressions.Expression:
68def ensure_bool_predicates(expression: exp.Expression) -> exp.Expression: 69 if isinstance(expression, exp.Connector): 70 _replace_int_predicate(expression.left) 71 _replace_int_predicate(expression.right) 72 73 elif isinstance(expression, (exp.Where, exp.Having, exp.If)): 74 _replace_int_predicate(expression.this) 75 76 return expression
def
remove_ascending_order( expression: sqlglot.expressions.Expression) -> sqlglot.expressions.Expression: