[FLINK-33010] NPE when using GREATEST() in Flink SQL

• Type: Bug
• Status: Resolved
• Priority: Minor
• Resolution: Fixed
• Affects Version/s: 1.16.1, 1.16.2
• Fix Version/s: 1.18.0, 1.16.3, 1.17.2, 1.19.0
• Component/s: Table SQL / API, Table SQL / Planner
• Labels: pull-request-available
• Assignee: Sergey Nuyanzin
• Reporter: Hector Rios
• Created: 31/Aug/23 09:24
• Updated: 12/Sep/23 06:45
• Resolved: 10/Sep/23 21:17

Description

Hi,

I see NPEs in flink 1.14 and flink 1.16 when running queries with GREATEST() and timestamps. Below is an example to help in reproducing the issue.

CREATE TEMPORARY VIEW Positions AS
SELECT
SecurityId,
ccy1,
CAST(publishTimestamp AS TIMESTAMP(3)) as publishTimestamp
FROM (VALUES
(1, 'USD', '2022-01-01'),
(2, 'GBP', '2022-02-02'),
(3, 'GBX', '2022-03-03'),
(4, 'GBX', '2022-04-4'))
AS ccy(SecurityId, ccy1, publishTimestamp);

CREATE TEMPORARY VIEW Benchmarks AS
SELECT
SecurityId,
ccy1,
CAST(publishTimestamp AS TIMESTAMP(3)) as publishTimestamp
FROM (VALUES
(3, 'USD', '2023-01-01'),
(4, 'GBP', '2023-02-02'),
(5, 'GBX', '2023-03-03'),
(6, 'GBX', '2023-04-4'))
AS ccy(SecurityId, ccy1, publishTimestamp);

SELECT *,
GREATEST(
IFNULL(Positions.publishTimestamp,CAST('1970-1-1' AS TIMESTAMP(3))),
IFNULL(Benchmarks.publishTimestamp,CAST('1970-1-1' AS TIMESTAMP(3)))
)
FROM Positions
FULL JOIN Benchmarks ON Positions.SecurityId = Benchmarks.SecurityId 

 

Using "IF" is a workaround at the moment instead of using "GREATEST"

  

Issue Links

• links to: GitHub Pull Request #23345
• links to: PR#23345