Alessandro Solimando created HIVE-25909:
-------------------------------------------
Summary: Add test for 'hive.default.nulls.last' property for
windows with ordering
Key: HIVE-25909
URL: https://issues.apache.org/jira/browse/HIVE-25909
Project: Hive
Issue Type: Test
Components: CBO
Affects Versions: 4.0.0
Reporter: Alessandro Solimando
Assignee: Alessandro Solimando
Add a test around "hive.default.nulls.last" configuration property and its
interaction with order by clauses within windows.
The property is known to respect such properties:
||hive.default.nulls.last||ASC||DESC||
|true|NULL LAST|NULL FIRST|
|false|NULL FIRST|NULL LAST|
The test can be based along the line of the following examples:
{noformat}
-- hive.default.nulls.last is true by default, it sets NULLS_FIRST for DESC
set hive.default.nulls.last;
OUT:
hive.default.nulls.last=true
SELECT a, b, c, row_number() OVER (PARTITION BY a, b ORDER BY b DESC, c DESC)
FROM test1;
OUT:
John Doe 1990-05-10 00:00:00 2022-01-10 00:00:00 1
John Doe 1990-05-10 00:00:00 2021-12-10 00:00:00 2
John Doe 1990-05-10 00:00:00 2021-11-10 00:00:00 3
John Doe 1990-05-10 00:00:00 2021-10-10 00:00:00 4
John Doe 1990-05-10 00:00:00 2021-09-10 00:00:00 5
John Doe 1987-05-10 00:00:00 NULL 1
John Doe 1987-05-10 00:00:00 2022-01-10 00:00:00 2
John Doe 1987-05-10 00:00:00 2021-12-10 00:00:00 3
John Doe 1987-05-10 00:00:00 2021-11-10 00:00:00 4
John Doe 1987-05-10 00:00:00 2021-10-10 00:00:00 5
-- we set hive.default.nulls.last=false, it sets NULLS_LAST for DESC
set hive.default.nulls.last=false;
SELECT a, b, c, row_number() OVER (PARTITION BY a, b ORDER BY b DESC, c DESC)
FROM test1;
OUT:
John Doe 1990-05-10 00:00:00 2022-01-10 00:00:00 1
John Doe 1990-05-10 00:00:00 2021-12-10 00:00:00 2
John Doe 1990-05-10 00:00:00 2021-11-10 00:00:00 3
John Doe 1990-05-10 00:00:00 2021-10-10 00:00:00 4
John Doe 1990-05-10 00:00:00 2021-09-10 00:00:00 5
John Doe 1987-05-10 00:00:00 2022-01-10 00:00:00 1
John Doe 1987-05-10 00:00:00 2021-12-10 00:00:00 2
John Doe 1987-05-10 00:00:00 2021-11-10 00:00:00 3
John Doe 1987-05-10 00:00:00 2021-10-10 00:00:00 4
John Doe 1987-05-10 00:00:00 NULL 5
-- we set hive.default.nulls.last=false but we have explicit NULLS_LAST, we
expect NULLS_LAST
set hive.default.nulls.last=false;
SELECT a, b, c, row_number() OVER (PARTITION BY a, b ORDER BY b DESC, c DESC
NULLS LAST)
FROM test1;
OUT:
John Doe 1990-05-10 00:00:00 2022-01-10 00:00:00 1
John Doe 1990-05-10 00:00:00 2021-12-10 00:00:00 2
John Doe 1990-05-10 00:00:00 2021-11-10 00:00:00 3
John Doe 1990-05-10 00:00:00 2021-10-10 00:00:00 4
John Doe 1990-05-10 00:00:00 2021-09-10 00:00:00 5
John Doe 1987-05-10 00:00:00 2022-01-10 00:00:00 1
John Doe 1987-05-10 00:00:00 2021-12-10 00:00:00 2
John Doe 1987-05-10 00:00:00 2021-11-10 00:00:00 3
John Doe 1987-05-10 00:00:00 2021-10-10 00:00:00 4
John Doe 1987-05-10 00:00:00 NULL 5
-- we have explicit NULLS_FIRST, we expect NULLS_FIRST
SELECT a, b, c, row_number() OVER (PARTITION BY a, b ORDER BY b DESC, c DESC
NULLS FIRST)
FROM test1;
--OUT:
John Doe 1990-05-10 00:00:00 2022-01-10 00:00:00 1
John Doe 1990-05-10 00:00:00 2021-12-10 00:00:00 2
John Doe 1990-05-10 00:00:00 2021-11-10 00:00:00 3
John Doe 1990-05-10 00:00:00 2021-10-10 00:00:00 4
John Doe 1990-05-10 00:00:00 2021-09-10 00:00:00 5
John Doe 1987-05-10 00:00:00 NULL 1
John Doe 1987-05-10 00:00:00 2022-01-10 00:00:00 2
John Doe 1987-05-10 00:00:00 2021-12-10 00:00:00 3
John Doe 1987-05-10 00:00:00 2021-11-10 00:00:00 4
John Doe 1987-05-10 00:00:00 2021-10-10 00:00:00 5{noformat}
--
This message was sent by Atlassian Jira
(v8.20.1#820001)
