Compatibility changes caused by modifications to Top-N queries - Realtime Compute for Apache Flink

This topic describes the compatibility between a job and the state data used to start the job after you modify Top-N queries for the job.

Compatible modifications

Fully compatible: Modify the sequence of partition keys.

-- Original SQL statement:
select a, b, c from (
  select *,
   row_number() over (partition by a, b order by c) as rk
   from MyTable)
where rk < 3;

-- Fully compatible: Modify the sequence of partition keys. 
select a, b, c from (
  select *,
   row_number() over (partition by b, a order by c) as rk
   from MyTable)
where rk < 3;

Fully compatible: Include or exclude the field that specifies the ranking position from the query result.

-- Original SQL statement: 
select a, b, c from (
  select *,
   row_number() over (partition by a order by c) as rk
   from MyTable)
where rk < 3;

-- Fully compatible: Include the rk field in the query result. 
select a, b, c, rk from (
  select *,
   row_number() over (partition by a order by c) as rk
   from MyTable)
where rk < 3;

Incompatible modifications

Use the UpdateFastRank algorithm for ranking and change the upstream upsert key.

Add, delete, or modify the partition keys or modify the computation logic of the fields included in the partition keys.

-- Original SQL statements: 
select a, b, c from (
  select *,
   row_number() over (partition by a order by c) as rk
   from MyTable)
where rk < 3;

-- Incompatible: Add d as a partition key. 
select a, b, c from (
  select *,
   row_number() over (partition by a, d order by c) as rk
   from MyTable)
where rk < 3;

-- Incompatible: Delete a from the partition keys. 
select a, b, c from (
  select *,
   row_number() over (order by c) as rk
   from MyTable)
where rk < 3;

-- Incompatible: Change the partition key from a to a + 1. 
select a, b, c from (
  select *,
   row_number() over (partition by a order by c) as rk
   from (select a + 1 as a, b, c from MyTable))
where rk < 3;

Modify the fields or order specified in the order by clause.

-- Original SQL statements: 
select a, b, c from (
  select *,
   row_number() over (partition by a order by c) as rk
   from MyTable)
where rk < 3;

-- Incompatible: Change the field used for sorting from c to b. 
select a, b, c from (
  select *,
   row_number() over (partition by a order by b) as rk
   from MyTable)
where rk < 3;

-- Incompatible: Change the field used for sorting from c to substring(c, 1, 5). 
select a, b, c from (
  select *,
   row_number() over (partition by a order by c) as rk
   from (select a, b, substring(c, 1, 5) as c from MyTable))
where rk < 3;

-- Incompatible: Change the sorting order from ascending to descending. 
select a, b, c from (
  select *,
   row_number() over (partition by a order by c desc) as rk
   from MyTable)
where rk < 3;

Modify the value of N. In a Top-N query, N specifies the number of top-ranked results to return.

-- Original SQL statements: 
select a, b, c from (
  select *,
   row_number() over (partition by a order by c) as rk
   from MyTable)
where rk < 3;

-- Incompatible: Change the value of N from 3 to 5. 
select a, b, c from (
  select *,
   row_number() over (partition by a order by c) as rk
   from MyTable)
where rk < 5;

Add, delete, or modify the selected fields for the Top-N query.

-- Original SQL statements: 
select a, b, c from (
  select *,
   row_number() over (partition by a order by c) as rk
   from MyTable)
where rk < 3;

-- Incompatible: Add d to the selected fields. 
select a, b, c, d from (
  select *,
   row_number() over (partition by a order by c) as rk
   from MyTable)
where rk < 3;

-- Incompatible: Delete b from the selected fields. 
select a, c from (
  select *,
   row_number() over (partition by a order by c) as rk
   from MyTable)
where rk < 3;

-- Incompatible: Change a selected field from b to b + 1. 
select a, b, c from (
  select *,
   row_number() over (partition by a order by c) as rk
   from (select a, b + 1 as b, c from MyTable))
where rk < 3;