alamb commented on code in PR #26850: URL: https://github.com/apache/datafusion/pull/26850#discussion_r4427728714
########## datafusion/sqllogictest/test_files/aggregate.slt: ########## @@ -9193,6 +9193,63 @@ select 0 from having_test having max(v1) = 4 query error select * from having_test group by v1 having max(v1) = 3 +# HAVING that references the alias of a volatile expression must filter on +# the value the query returns: every returned row must satisfy the predicate +# on the returned value of r; see +# https://github.com/apache/datafusion/issues/7976 +query B +select count(*) > 0 from ( + select value as v, random() as r + from generate_series(1, 1000) + group by v + having r > 0.5 +) where r <= 0.5; +---- +false + +# the predicate is planned as a filter above the projection, where the alias +# is a real column, instead of dereferencing the alias into a fresh random() +query TT +EXPLAIN select v1, random() as r from having_test group by v1 having r > 0.5 +---- +logical_plan +01)Filter: r > Float64(0.5) +02)--Projection: having_test.v1, random() AS r +03)----Aggregate: groupBy=[[having_test.v1]], aggr=[[]] +04)------TableScan: having_test projection=[v1] +physical_plan +01)FilterExec: r@1 > 0.5 +02)--ProjectionExec: expr=[v1@0 as v1, random() as r] +03)----AggregateExec: mode=FinalPartitioned, gby=[v1@0 as v1], aggr=[] +04)------RepartitionExec: partitioning=Hash([v1@0], 4), input_partitions=1 +05)--------AggregateExec: mode=Partial, gby=[v1@0 as v1], aggr=[] +06)----------DataSourceExec: partitions=1, partition_sizes=[1] + +# a non-volatile alias next to a volatile alias is also kept as a column +# reference and resolved against the projection +query I +select count(*) from (select v1, max(v2) as m, random() as r from having_test group by v1 having r >= 0 and m > 0) +---- +3 + +# an aggregate that is not in the SELECT list cannot be combined with a Review Comment: 🤯 ########## datafusion/sqllogictest/test_files/qualify.slt: ########## @@ -345,6 +345,53 @@ physical_plan 11)--------------------AggregateExec: mode=Partial, gby=[dept@1 as dept], aggr=[sum(users.salary)] 12)----------------------DataSourceExec: partitions=1, partition_sizes=[1] +# QUALIFY that references the alias of a volatile expression must filter on +# the value the query returns: every returned row must satisfy the predicate +# on the returned value of r; see +# https://github.com/apache/datafusion/issues/7976 +query B +select count(*) > 0 from ( + select row_number() over () as rn, random() as r + from generate_series(1, 1000) + qualify rn >= 1 and r > 0.5 +) where r <= 0.5; +---- +false + +# the predicate is planned as a filter above the projection, where the alias +# is a real column, instead of dereferencing the alias into a fresh random(); +# the deterministic rn >= 1 conjunct is still pushed below the projection +query TT +EXPLAIN SELECT id, ROW_NUMBER() OVER (ORDER BY id) AS rn, random() AS r +FROM users +QUALIFY rn >= 1 AND r > 0.5; +---- +logical_plan +01)Filter: r > Float64(0.5) +02)--Projection: users.id, row_number() ORDER BY [users.id ASC NULLS LAST] RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW AS rn, random() AS r +03)----Filter: row_number() ORDER BY [users.id ASC NULLS LAST] RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW >= UInt64(1) +04)------WindowAggr: windowExpr=[[row_number() ORDER BY [users.id ASC NULLS LAST] RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW]] +05)--------TableScan: users projection=[id] +physical_plan +01)FilterExec: r@2 > 0.5 +02)--ProjectionExec: expr=[id@0 as id, row_number() ORDER BY [users.id ASC NULLS LAST] RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW@1 as rn, random() as r] Review Comment: good to see random still here -- This is an automated message from the Apache Git Service. To respond to the message, please log on to GitHub and use the URL above to go to the specific comment. To unsubscribe, e-mail: [email protected] For queries about this service, please contact Infrastructure at: [email protected] --------------------------------------------------------------------- To unsubscribe, e-mail: [email protected] For additional commands, e-mail: [email protected]
