Repository navigation
Feature: Implement Extremum Rewrite for ANY Inequalities - #24090
Conversation
This PR introduces a substantial optimization for ALL/ANY inequalities (<, <=, >, >=) by rewriting them into generic Extremum aggregations (MIN, MAX, COUNT(*), COUNT(child)) using a LogicalAggregate and a scalar CASE expression. This fundamentally transforms O(N*M) nested loop resolutions into O(N+M) hash join operations, resulting in massive speedups for large scale datasets. This works gracefully for both uncorrelated global subqueries and fully correlated subqueries. Robust sqllogictests have been added covering Three-Valued Logic edge cases (Empty sets, LHS NULLs, and RHS NULLs).
6ff1dbb to
26aa016
Compare
|
Thanks for the PR! I think there's a correctness issue with collations. Could we preserve the comparison collation when computing the extremum? The rewrite currently binds For example, on this PR: SELECT 'b' COLLATE NOCASE >
ANY (SELECT x FROM (VALUES ('Z'), ('a')) t(x));This should return Repro: # name: test/temp.test
# description: ANY extremum rewrite must preserve collations
# group: [optimizer]
query I
SELECT 'b' COLLATE NOCASE > ANY(SELECT x FROM (VALUES ('Z'), ('a')) t(x));
----
trueCan you also add a TODO/FIXME that we should get this to work for multi-column in the future? This works for single column only right now (which is fine!). |
|
Thanks for the changes! Ready to merge from my side once the merge conflict is resolved |
What's Changed
This PR introduces optimization for
ALLandANYinequalities (<,<=,>,>=) by rewriting them into generic Extremum aggregations using aLogicalAggregateand a scalarCASEexpression.This transforms
O(N*M)nested loop resolutions intoO(N+M)hash join operations, improving performance for these subqueries.Details
<, <=, >, >= ANYconditions in both Uncorrelated (PlanUncorrelatedSubquery) and Correlated (PlanCorrelatedSubquery) code paths.LogicalAggregatecalculatingMIN,MAX,COUNT(*), andCOUNT(child)onto the subquery.CASEscalar expression that respects SQL's Three-Valued Logic matrix.NULLvalues on the LHS,NULLvalues inside the subquery, empty global queries, empty correlated groups, and string locale collations.Testing
statistics_join.testto expect the correctly pushed downANY_JOINgenerated by the rewrite.any_extremum_rewrite.testthat covers these three-valued-logic permutation for both correlated and uncorrelated structures.Closes #23988