Skip to content

Commit feff124

Browse files
authored
feat: more aggressive date sorting (tobymao#2690)
1 parent 77f2188 commit feff124

2 files changed

Lines changed: 12 additions & 20 deletions

File tree

‎sqlglot/optimizer/simplify.py‎

Lines changed: 8 additions & 16 deletions
Original file line numberDiff line numberDiff line change
@@ -482,11 +482,7 @@ def simplify_equality(expression: exp.Expression) -> exp.Expression:
482482
if isinstance(expression, COMPARISONS):
483483
l, r = expression.left, expression.right
484484

485-
if l.__class__ in INVERSE_OPS:
486-
pass
487-
elif r.__class__ in INVERSE_OPS:
488-
l, r = r, l
489-
else:
485+
if not l.__class__ in INVERSE_OPS:
490486
return expression
491487

492488
if r.is_number:
@@ -828,7 +824,8 @@ def _datetrunc_neq(
828824

829825

830826
DATETRUNC_BINARY_COMPARISONS: t.Dict[t.Type[exp.Expression], DateTruncBinaryTransform] = {
831-
exp.LT: lambda l, dt, u, d: l < date_literal(date_floor(dt, u, d)),
827+
exp.LT: lambda l, dt, u, d: l
828+
< date_literal(dt if dt == date_floor(dt, u, d) else date_floor(dt, u, d) + interval(u)),
832829
exp.GT: lambda l, dt, u, d: l >= date_literal(date_floor(dt, u, d) + interval(u)),
833830
exp.LTE: lambda l, dt, u, d: l < date_literal(date_floor(dt, u, d) + interval(u)),
834831
exp.GTE: lambda l, dt, u, d: l >= date_literal(date_ceil(dt, u, d)),
@@ -858,12 +855,7 @@ def simplify_datetrunc(expression: exp.Expression, dialect: Dialect) -> exp.Expr
858855
if isinstance(expression, exp.Binary):
859856
l, r = expression.left, expression.right
860857

861-
if _is_datetrunc_predicate(l, r):
862-
pass
863-
elif _is_datetrunc_predicate(r, l):
864-
comparison = INVERSE_COMPARISONS.get(comparison, comparison)
865-
l, r = r, l
866-
else:
858+
if not _is_datetrunc_predicate(l, r):
867859
return expression
868860

869861
l = t.cast(exp.DateTrunc, l)
@@ -906,12 +898,12 @@ def sort_comparison(expression: exp.Expression) -> exp.Expression:
906898
l, r = expression.this, expression.expression
907899
l_column = isinstance(l, exp.Column)
908900
r_column = isinstance(r, exp.Column)
909-
l_literal = isinstance(l, (exp.Literal, exp.Boolean))
910-
r_literal = isinstance(r, (exp.Literal, exp.Boolean))
901+
l_const = _is_constant(l)
902+
r_const = _is_constant(r)
911903

912-
if (l_column and not r_column) or (r_literal and not l_literal):
904+
if (l_column and not r_column) or (r_const and not l_const):
913905
return expression
914-
if (r_column and not l_column) or (l_literal and not r_literal) or (gen(l) > gen(r)):
906+
if (r_column and not l_column) or (l_const and not r_const) or (gen(l) > gen(r)):
915907
return INVERSE_COMPARISONS.get(expression.__class__, expression.__class__)(
916908
this=r, expression=l
917909
)

‎tests/fixtures/optimizer/simplify.sql‎

Lines changed: 4 additions & 4 deletions
Original file line numberDiff line numberDiff line change
@@ -832,14 +832,14 @@ x < CAST('2022-01-01' AS DATE) AND x >= CAST('2021-01-01' AS DATE);
832832

833833
-- Always false, except for nulls
834834
DATE_TRUNC('quarter', x) = CAST('2021-01-02' AS DATE);
835-
CAST('2021-01-02' AS DATE) = DATE_TRUNC('quarter', x);
835+
DATE_TRUNC('quarter', x) = CAST('2021-01-02' AS DATE);
836836

837837
DATE_TRUNC('year', x) <> CAST('2021-01-01' AS DATE);
838838
x < CAST('2021-01-01' AS DATE) AND x >= CAST('2022-01-01' AS DATE);
839839

840840
-- Always true, except for nulls
841841
DATE_TRUNC('year', x) <> CAST('2021-01-02' AS DATE);
842-
CAST('2021-01-02' AS DATE) <> DATE_TRUNC('year', x);
842+
DATE_TRUNC('year', x) <> CAST('2021-01-02' AS DATE);
843843

844844
DATE_TRUNC('year', x) <= CAST('2021-01-01' AS DATE);
845845
x < CAST('2022-01-01' AS DATE);
@@ -854,7 +854,7 @@ DATE_TRUNC('year', x) < CAST('2021-01-01' AS DATE);
854854
x < CAST('2021-01-01' AS DATE);
855855

856856
DATE_TRUNC('year', x) < CAST('2021-01-02' AS DATE);
857-
x < CAST('2021-01-01' AS DATE);
857+
x < CAST('2022-01-01' AS DATE);
858858

859859
DATE_TRUNC('year', x) >= CAST('2021-01-01' AS DATE);
860860
x >= CAST('2021-01-01' AS DATE);
@@ -958,7 +958,7 @@ DATE_SUB(x, 1, DAY) <> CAST('2021-01-01' AS DATE);
958958
x <> CAST('2021-01-02' AS DATE);
959959

960960
DATE_ADD(DATE_ADD(DATE_TRUNC('week', DATE_SUB(x, 1, DAY)), 1, DAY), 1, YEAR) < CAST('2021-01-08' AS DATE);
961-
x <= CAST('2020-01-14' AS DATE);
961+
x < CAST('2020-01-14' AS DATE);
962962

963963
x - INTERVAL '1' day = CAST(y AS DATE);
964964
CAST(y AS DATE) = x - INTERVAL '1' day;

0 commit comments

Comments
 (0)