[
https://issues.apache.org/jira/browse/HIVE-27997?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Mergen updated HIVE-27997:
--------------------------
Description:
Hive returns incorrect result if there is NVL() in an ON clause with Map Join
enabled.
STEPS TO REPRODUCE:
{code:java}
Step 1: Create a table test_nvl
create table test_nvl(a string);
Step 2: Insert null and non-null data into table test_nvl
insert into test_nvl values ('x'), ('y'), (null);
select * from test_nvl;
+-------------+
| test_nvl.a |
+-------------+
| x |
| y |
| NULL |
+-------------+
Step 3 : Execute the following query
select x.a, y.a
from test_nvl x
left join test_nvl y
on nvl(x.a, '') = nvl(y.a, '');{code}
EXPECTED RESULT:
{code:java}
+-------+-------+
| x.a | y.a |
+-------+-------+
| x | x |
| y | y |
| NULL | NULL |
+-------+-------+ {code}
ACTUAL RESULT:
{code:java}
+-------+------+
| x.a | y.a |
+-------+------+
| x | x |
| y | x |
| NULL | x |
+-------+------+{code}
(Obviously 'y' != 'x' and NULL != 'x' so they should not be in the same line)
The query works fine with Map Join disabled:
{code:java}
-- Using Merge Join instead.
set hive.auto.convert.join=false;
select x.a, y.a
from test_nvl x
left join test_nvl y
on nvl(x.a, '') = nvl(y.a, '');
+-------+-------+
| x.a | y.a |
+-------+-------+
| NULL | NULL |
| x | x |
| y | y |
+-------+-------+ {code}
was:
Hive returns incorrect result if there is NVL() in an ON clause with Map Join
enabled.
STEPS TO REPRODUCE:
{code:java}
Step 1: Create a table test_nvl
create table test_nvl(a string);
Step 2: Insert null and non-null data into table test_nvl
insert into test_nvl values ('x'), ('y'), (null);
select * from test_nvl;
+-------------+
| test_nvl.a |
+-------------+
| x |
| y |
| NULL |
+-------------+
Step 3 : Execute the following query
select x.a, y.a
from test_nvl x
left join test_nvl y
on nvl(x.a, '') = nvl(y.a, '');{code}
EXPECTED RESULT:
{code:java}
+-------+-------+
| x.a | y.a |
+-------+-------+
| x | x |
| y | y |
| NULL | NULL |
+-------+-------+ {code}
ACTUAL RESULT:
{code:java}
+-------+------+
| x.a | y.a |
+-------+------+
| x | x |
| y | x |
| NULL | x |
+-------+------+{code}
(Obviously 'y' != 'x' and NULL != 'x' so they should not be in the same line)
The query works fine with Map Join disabled:
{code:java}
-- Using Merge Join instead.
set hive.auto.convert.join=false;
select x.a, y.a
from test_nvl x
left join test_nvl y
on nvl(x.a, '') = nvl(y.a, '');
+-------+-------+
| x.a | y.a |
+-------+-------+
| NULL | NULL |
| x | x |
| y | y |
+-------+-------+ {code}
> Incorrect result for Hive join query with NVL and Map Join
> ----------------------------------------------------------
>
> Key: HIVE-27997
> URL: https://issues.apache.org/jira/browse/HIVE-27997
> Project: Hive
> Issue Type: Bug
> Components: Operators
> Affects Versions: 3.1.3
> Reporter: Mergen
> Priority: Major
>
> Hive returns incorrect result if there is NVL() in an ON clause with Map Join
> enabled.
>
> STEPS TO REPRODUCE:
> {code:java}
> Step 1: Create a table test_nvl
> create table test_nvl(a string);
> Step 2: Insert null and non-null data into table test_nvl
> insert into test_nvl values ('x'), ('y'), (null);
> select * from test_nvl;
> +-------------+
> | test_nvl.a |
> +-------------+
> | x |
> | y |
> | NULL |
> +-------------+
> Step 3 : Execute the following query
> select x.a, y.a
> from test_nvl x
> left join test_nvl y
> on nvl(x.a, '') = nvl(y.a, '');{code}
>
> EXPECTED RESULT:
> {code:java}
> +-------+-------+
> | x.a | y.a |
> +-------+-------+
> | x | x |
> | y | y |
> | NULL | NULL |
> +-------+-------+ {code}
>
> ACTUAL RESULT:
> {code:java}
> +-------+------+
> | x.a | y.a |
> +-------+------+
> | x | x |
> | y | x |
> | NULL | x |
> +-------+------+{code}
> (Obviously 'y' != 'x' and NULL != 'x' so they should not be in the same line)
>
> The query works fine with Map Join disabled:
> {code:java}
> -- Using Merge Join instead.
> set hive.auto.convert.join=false;
> select x.a, y.a
> from test_nvl x
> left join test_nvl y
> on nvl(x.a, '') = nvl(y.a, '');
> +-------+-------+
> | x.a | y.a |
> +-------+-------+
> | NULL | NULL |
> | x | x |
> | y | y |
> +-------+-------+ {code}
>
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
