Yaoxuan Wu created FLINK-41584:
----------------------------------
Summary: Top-N filter on ROW_NUMBER / RANK with a DECIMAL bound
(rn <= 1.0) uses the literal's unscaled value and keeps too many rows; a DOUBLE
bound throws ClassCastException in RankUtil
Key: FLINK-41584
URL: https://issues.apache.org/jira/browse/FLINK-41584
Project: Flink
Issue Type: Bug
Components: Table SQL / Planner
Affects Versions: 2.2.1, 2.3.0, 2.0.1
Reporter: Yaoxuan Wu
When a Top-N filter on ROW_NUMBER / RANK compares the rank with a DECIMAL
literal, the rank range is computed from the literal's unscaled value, so the
filter keeps far too many rows. With a DOUBLE literal the planner throws a
ClassCastException.
{code:sql}
CREATE TEMPORARY VIEW t AS SELECT * FROM (VALUES (1, 10), (1, 20), (1, 30), (1,
40), (2, 50)) AS v(k, x);
-- streaming mode
SELECT * FROM (SELECT k, x, ROW_NUMBER() OVER (PARTITION BY k ORDER BY x) AS r
FROM t) WHERE r <= 1;
-- (1, 10, 1), (2, 50, 1) correct
SELECT * FROM (SELECT k, x, ROW_NUMBER() OVER (PARTITION BY k ORDER BY x) AS r
FROM t) WHERE r <= 1.0;
-- expected: (1, 10, 1), (2, 50, 1)
-- actual: all 5 rows plan: Rank(rankRange=[rankStart=1,
rankEnd=10], ...)
SELECT * FROM (SELECT k, x, ROW_NUMBER() OVER (PARTITION BY k ORDER BY x) AS r
FROM t) WHERE r < 2.5;
-- expected: (1, 10, 1), (1, 20, 2), (2, 50, 1)
-- actual: all 5 rows plan: rankEnd=24
SELECT * FROM (SELECT k, x, ROW_NUMBER() OVER (PARTITION BY k ORDER BY x) AS r
FROM t) WHERE r <= CAST(1 AS DOUBLE);
-- java.lang.ClassCastException: class java.math.BigDecimal cannot be cast to
class java.lang.Long
-- at
org.apache.flink.table.planner.plan.utils.RankUtil$.$anonfun$reduceComparisonPredicate$1(RankUtil.scala:309)
-- at
org.apache.flink.table.planner.plan.utils.RankUtil$.reduceComparisonPredicate(RankUtil.scala:308)
-- at
org.apache.flink.table.planner.plan.utils.RankUtil$.computeWindowBoundFromPredicate(RankUtil.scala:244)
-- at
org.apache.flink.table.planner.plan.utils.RankUtil$.extractRankRange(RankUtil.scala:111)
{code}
* Same in batch mode with RANK() instead of ROW_NUMBER().
* Same on 2.0.1, 2.2.1, 2.3.0 and master (2.4-SNAPSHOT; there the DOUBLE case
says {{java.lang.Double cannot be cast to class java.lang.Long}}).
* An INT literal ({{r <= 1}}) is correct.
Cause (RankUtil.scala):
* {{reduceComparisonPredicate}} reduces the non-rank side of the comparison and
reads the result with {{literal.getValue2.asInstanceOf[Long]}}.
* For a DECIMAL literal, Calcite's {{RexLiteral.getValue2}} returns
{{getValueAs(Long.class)}}, which is the unscaled value: 1.0 becomes 10, 1.5
becomes 15, 2.5 becomes 25. {{computeWindowBoundFromPredicate}} builds the rank
range from that number.
* For a DOUBLE / FLOAT literal, {{getValue2}} returns a BigDecimal (Calcite
1.36) or a Double (Calcite 1.41), and the unboxing to Long throws.
* The comparison of the BIGINT rank with a DECIMAL / DOUBLE literal is not
wrapped in a CAST, so {{identifyLimitPredicate}} accepts it as a limit
predicate.
A possible fix: read the value as a BigDecimal
({{getValueAs(classOf[java.math.BigDecimal])}}) and round it according to the
bound (floor for an upper bound, ceiling for a lower bound, no Top-N for {{=}}
with a fractional value), or return {{None}} (keep the filter, no Top-N) unless
the literal has an exact integer type.
--
This message was sent by Atlassian Jira
(v10.3.26#10030026)