U0100068: FIRST_VALUE in GaussDB does not support IGNORE NULLS

Description

Analytic function FIRST_VALUE in GaussDB does not support IGNORE NULLS.

Database Type and Version

• Source database type and version: Oracle versions supported by UGO
• Target database type and version: GaussDB of versions earlier than V2.0-8.100

Syntax Example

----------- Scenario 1: ORDER BY DESC is used.
SELECT a,b,FIRST_VALUE(b) IGNORE NULLS OVER(PARTITION BY a ORDER BY b desc) FROM first_value_t1;
----------- Scenario 2: ORDER BY is used.
SELECT a,b,FIRST_VALUE(b) IGNORE NULLS OVER(PARTITION BY a ORDER BY b ) FROM first_value_t1;
----------- Scenario 3: ORDER BY is not used.
SELECT a,b,FIRST_VALUE(b) IGNORE NULLS OVER(PARTITION BY a) FROM first_value_t1;
;

Suggestion

1. Scenarios 1 and 2: Remove FIRST_VALUE(b) IGNORE NULLS OVER(PARTITION BY a order by b (desc)). Add ROW_NUMBER() OVER(PARTITION BY a order by b (desc)) AS rn to other query columns to form a CTE.

2. Association between the primary table and the CTE: Add case when t.b is not null then FIRST_VALUE(r.b) OVER(PARTITION BY r.a ORDER BY r.b) else t.b end AS first_value_not_null to the SELECT query column.

(1) If the ascending order is specified, else t.b is changed to else r.b.

3. Add t.a = r.a AND r.rn = 1 to WHERE.

4. Scenario 3: Use FIRST_VALUE(b) OVER(PARTITION BY a ORDER BY CASE WHEN b IS NOT NULL THEN 0 ELSE 1 END) instead of FIRST_VALUE(b) IGNORE NULLS OVER(PARTITION BY a).

Script compatible with GaussDB:

----------- Scenario 1: ORDER BY DESC is used.
---Script compatible with GaussDB:
WITH cte AS (SELECT a, b,ROW_NUMBER() OVER (PARTITION BY a ORDER BY b desc ) AS rn FROM first_value_t1 WHERE  b IS NOT NULL)
SELECT t.a,t.b,case when t.b is not null 
then FIRST_VALUE(r.b) OVER(PARTITION BY r.a ORDER BY r.b)
else t.b end AS first_value_not_null
FROM first_value_t1 t , cte r where t.a = r.a AND r.rn = 1;
----------- Scenario 2: ORDER BY is used.
---Script compatible with GaussDB:
WITH cte AS (SELECT a, b,ROW_NUMBER() OVER (PARTITION BY a ORDER BY b  ) AS rn FROM first_value_t1 WHERE  b IS NOT NULL)
SELECT t.a,t.b,case when t.b is not null 
then FIRST_VALUE(r.b) OVER(PARTITION BY r.a ORDER BY r.b )
else r.b end AS first_value_not_null
FROM first_value_t1 t , cte r where t.a = r.a AND r.rn = 1;
----------- Scenario 3: ORDER BY is not used.
---Script compatible with GaussDB:
SELECT a,b,FIRST_VALUE(b)  OVER(PARTITION BY a  ORDER BY CASE WHEN b IS NOT NULL THEN 0 ELSE 1 END)  FROM first_value_t1;