You are viewing a plain text version of this content. The canonical link for it is here.
Posted to issues@hive.apache.org by "Stamatis Zampetakis (Jira)" <ji...@apache.org> on 2022/02/01 10:40:00 UTC

[jira] [Resolved] (HIVE-25909) Add test for 'hive.default.nulls.last' property for windows with ordering

     [ https://issues.apache.org/jira/browse/HIVE-25909?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]

Stamatis Zampetakis resolved HIVE-25909.
----------------------------------------
    Fix Version/s: 4.0.0
       Resolution: Fixed

Fixed in https://github.com/apache/hive/commit/d2c6769059cd8e91c0accda2758e1606edaa4b61. Thanks for adding more tests around this feature [~asolimando]!

> 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
>            Priority: Minor
>              Labels: pull-request-available
>             Fix For: 4.0.0
>
>          Time Spent: 1h
>  Remaining Estimate: 0h
>
> 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)