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)

Reply via email to