[HIVE-25905] ORDER BY colName DESC does not honour 'hive.default.nulls.last' property in absence of NULLS_LAST/FIRST

• Type: Bug
• Status: Resolved
• Priority: Major
• Resolution: Not A Bug
• Affects Version/s: 4.0.0
• Component/s: Parser
• Labels: pull-request-available
• Assignee: Alessandro Solimando
• Reporter: Alessandro Solimando
• Created: 27/Jan/22 10:09
• Updated: 31/Jan/22 16:55
• Resolved: 28/Jan/22 12:52

Description

Consider the following table and data:

create table test1
(
  a string,
  b timestamp,
  c timestamp
);

INSERT INTO TABLE test1 VALUES
('John Doe', '1990-05-10 00:00:00.0', '2022-01-10 00:00:00.0'),
('John Doe', '1990-05-10 00:00:00.0', '2021-12-10 00:00:00.0'),
('John Doe', '1990-05-10 00:00:00.0', '2021-11-10 00:00:00.0'),
('John Doe', '1990-05-10 00:00:00.0', '2021-10-10 00:00:00.0'),
('John Doe', '1990-05-10 00:00:00.0', '2021-09-10 00:00:00.0'),
('John Doe', '1987-05-10 00:00:00.0', '2022-01-10 00:00:00.0'),
('John Doe', '1987-05-10 00:00:00.0', '2021-12-10 00:00:00.0'),
('John Doe', '1987-05-10 00:00:00.0', '2021-11-10 00:00:00.0'),
('John Doe', '1987-05-10 00:00:00.0', '2021-10-10 00:00:00.0'),
('John Doe', '1987-05-10 00:00:00.0', null);

Consider also the following query:

SELECT a, b, c, row_number() OVER (PARTITION BY a, b ORDER BY b DESC, c DESC) FROM test1; 

The output is:

John Doe	10/05/1990 00:00	10/01/2022 00:00	1
John Doe	10/05/1990 00:00	10/12/2021 00:00	2
John Doe	10/05/1990 00:00	10/11/2021 00:00	3
John Doe	10/05/1990 00:00	10/10/2021 00:00	4
John Doe	10/05/1990 00:00	10/09/2021 00:00	5
John Doe	10/05/1987 00:00	NULL 	                1
John Doe	10/05/1987 00:00	10/01/2022 00:00	2
John Doe	10/05/1987 00:00	10/12/2021 00:00	3
John Doe	10/05/1987 00:00	10/11/2021 00:00	4
John Doe	10/05/1987 00:00	10/10/2021 00:00	5

While the expected output should be:

John Doe	10/05/1990 00:00	10/01/2022 00:00	1
John Doe	10/05/1990 00:00	10/12/2021 00:00	2
John Doe	10/05/1990 00:00	10/11/2021 00:00	3
John Doe	10/05/1990 00:00	10/10/2021 00:00	4
John Doe	10/05/1990 00:00	10/09/2021 00:00	5
John Doe	10/05/1987 00:00	10/01/2022 00:00	1
John Doe	10/05/1987 00:00	10/12/2021 00:00	2
John Doe	10/05/1987 00:00	10/11/2021 00:00	3
John Doe	10/05/1987 00:00	10/10/2021 00:00	4
John Doe	10/05/1987 00:00	NULL 	                5

Issue Links

• relates to: HIVE-25917 Use default value for 'hive.default.nulls.last' when no config is available instead of false
• relates to: HIVE-25909 Add test for 'hive.default.nulls.last' property for windows with ordering
• links to: GitHub Pull Request #2979