[jira] [Commented] (HIVE-14027) NULL values produced by left outer join do not behave as NULL
[
https://issues.apache.org/jira/browse/HIVE-14027?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=15371152#comment-15371152
]
Ashutosh Chauhan commented on HIVE-14027:
-
+1 lets file follow-up jira to retrieve key from correct side of join to
eliminate need of converters.
> NULL values produced by left outer join do not behave as NULL
> -
>
> Key: HIVE-14027
> URL: https://issues.apache.org/jira/browse/HIVE-14027
> Project: Hive
> Issue Type: Bug
> Components: Query Processor
>Affects Versions: 1.2.1, 2.2.0
>Reporter: Vaibhav Gumashta
>Assignee: Jesus Camacho Rodriguez
> Attachments: HIVE-14027.01.patch, HIVE-14027.02.patch,
> HIVE-14027.03.patch, HIVE-14027.patch
>
>
> Consider the following setup:
> {code}
> create table tbl (n bigint, t string);
> insert into tbl values (1, 'one');
> insert into tbl values(2, 'two');
> select a.n, a.t, isnull(b.n), isnull(b.t) from (select * from tbl where n =
> 1) a left outer join (select * from tbl where 1 = 2) b on a.n = b.n;
> 1onefalsetrue
> {code}
> The query should return true for isnull(b.n).
> I've tested by inserting a row with null value for the bigint column into
> tbl, and isnull returns true in that case.
--
This message was sent by Atlassian JIRA
(v6.3.4#6332)
[jira] [Commented] (HIVE-14027) NULL values produced by left outer join do not behave as NULL
[
https://issues.apache.org/jira/browse/HIVE-14027?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=15371107#comment-15371107
]
Jesus Camacho Rodriguez commented on HIVE-14027:
Consider the original query:
{noformat}
select a.n, a.t, isnull(b.n), isnull(b.t) from (select * from tbl where n = 1)
a left outer join (select * from tbl where 1 = 2) b on a.n = b.n;
{noformat}
The field {{n}} from table {{a}} can become a constant {{1}} when we fold the
OI. For every record that the left outer join produces, {{n}} will always be
{{1}}. The real problem is that the _MapJoin_ reuses the OI of the key from the
outer side for the key of the inner side. This is fine for inner joins, but it
is incorrect for outer joins.
_Solution 1_ would be to rewrite the _MapJoin_ operator logic. But that change
is non-trivial (at least for me that I am not familiar with the operator
logic). After I studied it initially, I did not proceed with it, as I would
have to change the initialization logic, process logic, UnwrapRowContainer, etc.
Thus, I went on with the _solution 2_ that is implemented in this patch.
Consider an outer join. If OI of outer/inner sides are different, we take the
data from the outer side (as it is done currently), but we apply the
_converter_ logic on the data.
In fact, depending on the cost of deserializing vs cost of conversion, it might
be that the current solution is more efficient than _solution 1_.
> NULL values produced by left outer join do not behave as NULL
> -
>
> Key: HIVE-14027
> URL: https://issues.apache.org/jira/browse/HIVE-14027
> Project: Hive
> Issue Type: Bug
> Components: Query Processor
>Affects Versions: 1.2.1, 2.2.0
>Reporter: Vaibhav Gumashta
>Assignee: Jesus Camacho Rodriguez
> Attachments: HIVE-14027.01.patch, HIVE-14027.02.patch,
> HIVE-14027.03.patch, HIVE-14027.patch
>
>
> Consider the following setup:
> {code}
> create table tbl (n bigint, t string);
> insert into tbl values (1, 'one');
> insert into tbl values(2, 'two');
> select a.n, a.t, isnull(b.n), isnull(b.t) from (select * from tbl where n =
> 1) a left outer join (select * from tbl where 1 = 2) b on a.n = b.n;
> 1onefalsetrue
> {code}
> The query should return true for isnull(b.n).
> I've tested by inserting a row with null value for the bigint column into
> tbl, and isnull returns true in that case.
--
This message was sent by Atlassian JIRA
(v6.3.4#6332)
[jira] [Commented] (HIVE-14027) NULL values produced by left outer join do not behave as NULL
[
https://issues.apache.org/jira/browse/HIVE-14027?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=15371085#comment-15371085
]
Ashutosh Chauhan commented on HIVE-14027:
-
Why does it become constant OI? Sounds like thats the problem. It shouldn't be
a constant OI. Or may be I am missing something.
> NULL values produced by left outer join do not behave as NULL
> -
>
> Key: HIVE-14027
> URL: https://issues.apache.org/jira/browse/HIVE-14027
> Project: Hive
> Issue Type: Bug
> Components: Query Processor
>Affects Versions: 1.2.1, 2.2.0
>Reporter: Vaibhav Gumashta
>Assignee: Jesus Camacho Rodriguez
> Attachments: HIVE-14027.01.patch, HIVE-14027.02.patch,
> HIVE-14027.03.patch, HIVE-14027.patch
>
>
> Consider the following setup:
> {code}
> create table tbl (n bigint, t string);
> insert into tbl values (1, 'one');
> insert into tbl values(2, 'two');
> select a.n, a.t, isnull(b.n), isnull(b.t) from (select * from tbl where n =
> 1) a left outer join (select * from tbl where 1 = 2) b on a.n = b.n;
> 1onefalsetrue
> {code}
> The query should return true for isnull(b.n).
> I've tested by inserting a row with null value for the bigint column into
> tbl, and isnull returns true in that case.
--
This message was sent by Atlassian JIRA
(v6.3.4#6332)
[jira] [Commented] (HIVE-14027) NULL values produced by left outer join do not behave as NULL
[
https://issues.apache.org/jira/browse/HIVE-14027?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=15370984#comment-15370984
]
Jesus Camacho Rodriguez commented on HIVE-14027:
That is the source of the original problem and what the code was originally
doing: the problem is that OI of the outer side becomes constant OI, and thus,
the one of the right would return the constant too.
> NULL values produced by left outer join do not behave as NULL
> -
>
> Key: HIVE-14027
> URL: https://issues.apache.org/jira/browse/HIVE-14027
> Project: Hive
> Issue Type: Bug
> Components: Query Processor
>Affects Versions: 1.2.1, 2.2.0
>Reporter: Vaibhav Gumashta
>Assignee: Jesus Camacho Rodriguez
> Attachments: HIVE-14027.01.patch, HIVE-14027.02.patch,
> HIVE-14027.03.patch, HIVE-14027.patch
>
>
> Consider the following setup:
> {code}
> create table tbl (n bigint, t string);
> insert into tbl values (1, 'one');
> insert into tbl values(2, 'two');
> select a.n, a.t, isnull(b.n), isnull(b.t) from (select * from tbl where n =
> 1) a left outer join (select * from tbl where 1 = 2) b on a.n = b.n;
> 1onefalsetrue
> {code}
> The query should return true for isnull(b.n).
> I've tested by inserting a row with null value for the bigint column into
> tbl, and isnull returns true in that case.
--
This message was sent by Atlassian JIRA
(v6.3.4#6332)
[jira] [Commented] (HIVE-14027) NULL values produced by left outer join do not behave as NULL
[
https://issues.apache.org/jira/browse/HIVE-14027?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=15370982#comment-15370982
]
Ashutosh Chauhan commented on HIVE-14027:
-
Instead of adding converter wondering if its possible to get OI itself from
outer side?
> NULL values produced by left outer join do not behave as NULL
> -
>
> Key: HIVE-14027
> URL: https://issues.apache.org/jira/browse/HIVE-14027
> Project: Hive
> Issue Type: Bug
> Components: Query Processor
>Affects Versions: 1.2.1, 2.2.0
>Reporter: Vaibhav Gumashta
>Assignee: Jesus Camacho Rodriguez
> Attachments: HIVE-14027.01.patch, HIVE-14027.02.patch,
> HIVE-14027.03.patch, HIVE-14027.patch
>
>
> Consider the following setup:
> {code}
> create table tbl (n bigint, t string);
> insert into tbl values (1, 'one');
> insert into tbl values(2, 'two');
> select a.n, a.t, isnull(b.n), isnull(b.t) from (select * from tbl where n =
> 1) a left outer join (select * from tbl where 1 = 2) b on a.n = b.n;
> 1onefalsetrue
> {code}
> The query should return true for isnull(b.n).
> I've tested by inserting a row with null value for the bigint column into
> tbl, and isnull returns true in that case.
--
This message was sent by Atlassian JIRA
(v6.3.4#6332)
[jira] [Commented] (HIVE-14027) NULL values produced by left outer join do not behave as NULL
[
https://issues.apache.org/jira/browse/HIVE-14027?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=15370444#comment-15370444
]
Jesus Camacho Rodriguez commented on HIVE-14027:
[~ashutoshc], fails are unrelated. Could you review the additional changes?
Basically I extended the UnwrapRowContainer to deal with different types in the
key for outer/inner part of the join.
Btw, there are no q file changes because the only thing that is changing is the
internal representation in the Join i.e. the object inspectors. I added
additional tests to the new q file though.
> NULL values produced by left outer join do not behave as NULL
> -
>
> Key: HIVE-14027
> URL: https://issues.apache.org/jira/browse/HIVE-14027
> Project: Hive
> Issue Type: Bug
> Components: Query Processor
>Affects Versions: 1.2.1, 2.2.0
>Reporter: Vaibhav Gumashta
>Assignee: Jesus Camacho Rodriguez
> Attachments: HIVE-14027.01.patch, HIVE-14027.02.patch,
> HIVE-14027.03.patch, HIVE-14027.patch
>
>
> Consider the following setup:
> {code}
> create table tbl (n bigint, t string);
> insert into tbl values (1, 'one');
> insert into tbl values(2, 'two');
> select a.n, a.t, isnull(b.n), isnull(b.t) from (select * from tbl where n =
> 1) a left outer join (select * from tbl where 1 = 2) b on a.n = b.n;
> 1onefalsetrue
> {code}
> The query should return true for isnull(b.n).
> I've tested by inserting a row with null value for the bigint column into
> tbl, and isnull returns true in that case.
--
This message was sent by Atlassian JIRA
(v6.3.4#6332)
[jira] [Commented] (HIVE-14027) NULL values produced by left outer join do not behave as NULL
[
https://issues.apache.org/jira/browse/HIVE-14027?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=15369408#comment-15369408
]
Hive QA commented on HIVE-14027:
Here are the results of testing the latest attachment:
https://issues.apache.org/jira/secure/attachment/12816954/HIVE-14027.02.patch
{color:green}SUCCESS:{color} +1 due to 1 test(s) being added or modified.
{color:red}ERROR:{color} -1 due to 9 failed/errored test(s), 10297 tests
executed
*Failed tests:*
{noformat}
org.apache.hadoop.hive.cli.TestCliDriver.testCliDriver_acid_globallimit
org.apache.hadoop.hive.cli.TestCliDriver.testCliDriver_list_bucket_dml_12
org.apache.hadoop.hive.cli.TestCliDriver.testCliDriver_list_bucket_dml_13
org.apache.hadoop.hive.cli.TestCliDriver.testCliDriver_subquery_multiinsert
org.apache.hadoop.hive.cli.TestMiniLlapCliDriver.testCliDriver_vector_complex_all
org.apache.hadoop.hive.cli.TestMiniLlapCliDriver.testCliDriver_vector_complex_join
org.apache.hadoop.hive.cli.TestMiniTezCliDriver.testCliDriver_acid_globallimit
org.apache.hadoop.hive.cli.TestMinimrCliDriver.org.apache.hadoop.hive.cli.TestMinimrCliDriver
org.apache.hadoop.hive.llap.tezplugins.TestLlapTaskSchedulerService.testDelayedLocalityNodeCommErrorImmediateAllocation
{noformat}
Test results:
https://builds.apache.org/job/PreCommit-HIVE-MASTER-Build/448/testReport
Console output:
https://builds.apache.org/job/PreCommit-HIVE-MASTER-Build/448/console
Test logs:
http://ec2-204-236-174-241.us-west-1.compute.amazonaws.com/logs/PreCommit-HIVE-MASTER-Build-448/
Messages:
{noformat}
Executing org.apache.hive.ptest.execution.TestCheckPhase
Executing org.apache.hive.ptest.execution.PrepPhase
Executing org.apache.hive.ptest.execution.ExecutionPhase
Executing org.apache.hive.ptest.execution.ReportingPhase
Tests exited with: TestsFailedException: 9 tests failed
{noformat}
This message is automatically generated.
ATTACHMENT ID: 12816954 - PreCommit-HIVE-MASTER-Build
> NULL values produced by left outer join do not behave as NULL
> -
>
> Key: HIVE-14027
> URL: https://issues.apache.org/jira/browse/HIVE-14027
> Project: Hive
> Issue Type: Bug
> Components: Query Processor
>Affects Versions: 1.2.1, 2.2.0
>Reporter: Vaibhav Gumashta
>Assignee: Jesus Camacho Rodriguez
> Attachments: HIVE-14027.01.patch, HIVE-14027.02.patch,
> HIVE-14027.patch
>
>
> Consider the following setup:
> {code}
> create table tbl (n bigint, t string);
> insert into tbl values (1, 'one');
> insert into tbl values(2, 'two');
> select a.n, a.t, isnull(b.n), isnull(b.t) from (select * from tbl where n =
> 1) a left outer join (select * from tbl where 1 = 2) b on a.n = b.n;
> 1onefalsetrue
> {code}
> The query should return true for isnull(b.n).
> I've tested by inserting a row with null value for the bigint column into
> tbl, and isnull returns true in that case.
--
This message was sent by Atlassian JIRA
(v6.3.4#6332)
[jira] [Commented] (HIVE-14027) NULL values produced by left outer join do not behave as NULL
[
https://issues.apache.org/jira/browse/HIVE-14027?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=15358849#comment-15358849
]
Hive QA commented on HIVE-14027:
Here are the results of testing the latest attachment:
https://issues.apache.org/jira/secure/attachment/12815493/HIVE-14027.01.patch
{color:green}SUCCESS:{color} +1 due to 1 test(s) being added or modified.
{color:red}ERROR:{color} -1 due to 22 failed/errored test(s), 10288 tests
executed
*Failed tests:*
{noformat}
org.apache.hadoop.hive.cli.TestCliDriver.testCliDriver_list_bucket_dml_12
org.apache.hadoop.hive.cli.TestCliDriver.testCliDriver_list_bucket_dml_13
org.apache.hadoop.hive.cli.TestCliDriver.testCliDriver_stats_list_bucket
org.apache.hadoop.hive.cli.TestCliDriver.testCliDriver_subquery_multiinsert
org.apache.hadoop.hive.cli.TestMiniLlapCliDriver.testCliDriver_tez_union
org.apache.hadoop.hive.cli.TestMiniLlapCliDriver.testCliDriver_vector_complex_all
org.apache.hadoop.hive.cli.TestMiniLlapCliDriver.testCliDriver_vector_complex_join
org.apache.hadoop.hive.cli.TestMiniTezCliDriver.testCliDriver_auto_join29
org.apache.hadoop.hive.cli.TestMiniTezCliDriver.testCliDriver_auto_join30
org.apache.hadoop.hive.cli.TestMiniTezCliDriver.testCliDriver_auto_join_filters
org.apache.hadoop.hive.cli.TestMiniTezCliDriver.testCliDriver_auto_join_nulls
org.apache.hadoop.hive.cli.TestMiniTezCliDriver.testCliDriver_empty_join
org.apache.hadoop.hive.cli.TestMiniTezCliDriver.testCliDriver_tez_union
org.apache.hadoop.hive.cli.TestMiniTezCliDriver.testCliDriver_vector_groupby_mapjoin
org.apache.hadoop.hive.cli.TestMiniTezCliDriver.testCliDriver_vector_join30
org.apache.hadoop.hive.cli.TestMiniTezCliDriver.testCliDriver_vector_join_filters
org.apache.hadoop.hive.cli.TestMiniTezCliDriver.testCliDriver_vector_join_nulls
org.apache.hadoop.hive.cli.TestMiniTezCliDriver.testCliDriver_vector_nullsafe_join
org.apache.hadoop.hive.cli.TestMiniTezCliDriver.testCliDriver_vector_outer_join0
org.apache.hadoop.hive.cli.TestMiniTezCliDriver.testCliDriver_vector_outer_join1
org.apache.hadoop.hive.cli.TestMiniTezCliDriver.testCliDriver_vector_outer_join4
org.apache.hadoop.hive.cli.TestMiniTezCliDriver.testCliDriver_vector_outer_join6
{noformat}
Test results:
https://builds.apache.org/job/PreCommit-HIVE-MASTER-Build/334/testReport
Console output:
https://builds.apache.org/job/PreCommit-HIVE-MASTER-Build/334/console
Test logs:
http://ec2-50-18-27-0.us-west-1.compute.amazonaws.com/logs/PreCommit-HIVE-MASTER-Build-334/
Messages:
{noformat}
Executing org.apache.hive.ptest.execution.TestCheckPhase
Executing org.apache.hive.ptest.execution.PrepPhase
Executing org.apache.hive.ptest.execution.ExecutionPhase
Executing org.apache.hive.ptest.execution.ReportingPhase
Tests exited with: TestsFailedException: 22 tests failed
{noformat}
This message is automatically generated.
ATTACHMENT ID: 12815493 - PreCommit-HIVE-MASTER-Build
> NULL values produced by left outer join do not behave as NULL
> -
>
> Key: HIVE-14027
> URL: https://issues.apache.org/jira/browse/HIVE-14027
> Project: Hive
> Issue Type: Bug
> Components: Query Processor
>Affects Versions: 1.2.1, 2.2.0
>Reporter: Vaibhav Gumashta
>Assignee: Jesus Camacho Rodriguez
> Attachments: HIVE-14027.01.patch, HIVE-14027.patch
>
>
> Consider the following setup:
> {code}
> create table tbl (n bigint, t string);
> insert into tbl values (1, 'one');
> insert into tbl values(2, 'two');
> select a.n, a.t, isnull(b.n), isnull(b.t) from (select * from tbl where n =
> 1) a left outer join (select * from tbl where 1 = 2) b on a.n = b.n;
> 1onefalsetrue
> {code}
> The query should return true for isnull(b.n).
> I've tested by inserting a row with null value for the bigint column into
> tbl, and isnull returns true in that case.
--
This message was sent by Atlassian JIRA
(v6.3.4#6332)
[jira] [Commented] (HIVE-14027) NULL values produced by left outer join do not behave as NULL
[
https://issues.apache.org/jira/browse/HIVE-14027?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=15357442#comment-15357442
]
Hive QA commented on HIVE-14027:
Here are the results of testing the latest attachment:
https://issues.apache.org/jira/secure/attachment/12814188/HIVE-14027.patch
{color:green}SUCCESS:{color} +1 due to 1 test(s) being added or modified.
{color:red}ERROR:{color} -1 due to 21 failed/errored test(s), 10287 tests
executed
*Failed tests:*
{noformat}
org.apache.hadoop.hive.cli.TestCliDriver.testCliDriver_list_bucket_dml_12
org.apache.hadoop.hive.cli.TestCliDriver.testCliDriver_stats_list_bucket
org.apache.hadoop.hive.cli.TestCliDriver.testCliDriver_subquery_multiinsert
org.apache.hadoop.hive.cli.TestMiniLlapCliDriver.testCliDriver_tez_union
org.apache.hadoop.hive.cli.TestMiniLlapCliDriver.testCliDriver_vector_complex_all
org.apache.hadoop.hive.cli.TestMiniLlapCliDriver.testCliDriver_vector_complex_join
org.apache.hadoop.hive.cli.TestMiniTezCliDriver.testCliDriver_auto_join29
org.apache.hadoop.hive.cli.TestMiniTezCliDriver.testCliDriver_auto_join30
org.apache.hadoop.hive.cli.TestMiniTezCliDriver.testCliDriver_auto_join_filters
org.apache.hadoop.hive.cli.TestMiniTezCliDriver.testCliDriver_auto_join_nulls
org.apache.hadoop.hive.cli.TestMiniTezCliDriver.testCliDriver_empty_join
org.apache.hadoop.hive.cli.TestMiniTezCliDriver.testCliDriver_tez_union
org.apache.hadoop.hive.cli.TestMiniTezCliDriver.testCliDriver_vector_groupby_mapjoin
org.apache.hadoop.hive.cli.TestMiniTezCliDriver.testCliDriver_vector_join30
org.apache.hadoop.hive.cli.TestMiniTezCliDriver.testCliDriver_vector_join_filters
org.apache.hadoop.hive.cli.TestMiniTezCliDriver.testCliDriver_vector_join_nulls
org.apache.hadoop.hive.cli.TestMiniTezCliDriver.testCliDriver_vector_nullsafe_join
org.apache.hadoop.hive.cli.TestMiniTezCliDriver.testCliDriver_vector_outer_join0
org.apache.hadoop.hive.cli.TestMiniTezCliDriver.testCliDriver_vector_outer_join1
org.apache.hadoop.hive.cli.TestMiniTezCliDriver.testCliDriver_vector_outer_join4
org.apache.hadoop.hive.cli.TestMiniTezCliDriver.testCliDriver_vector_outer_join6
{noformat}
Test results:
https://builds.apache.org/job/PreCommit-HIVE-MASTER-Build/326/testReport
Console output:
https://builds.apache.org/job/PreCommit-HIVE-MASTER-Build/326/console
Test logs:
http://ec2-50-18-27-0.us-west-1.compute.amazonaws.com/logs/PreCommit-HIVE-MASTER-Build-326/
Messages:
{noformat}
Executing org.apache.hive.ptest.execution.TestCheckPhase
Executing org.apache.hive.ptest.execution.PrepPhase
Executing org.apache.hive.ptest.execution.ExecutionPhase
Executing org.apache.hive.ptest.execution.ReportingPhase
Tests exited with: TestsFailedException: 21 tests failed
{noformat}
This message is automatically generated.
ATTACHMENT ID: 12814188 - PreCommit-HIVE-MASTER-Build
> NULL values produced by left outer join do not behave as NULL
> -
>
> Key: HIVE-14027
> URL: https://issues.apache.org/jira/browse/HIVE-14027
> Project: Hive
> Issue Type: Bug
> Components: Query Processor
>Affects Versions: 1.2.1, 2.2.0
>Reporter: Vaibhav Gumashta
>Assignee: Jesus Camacho Rodriguez
> Attachments: HIVE-14027.01.patch, HIVE-14027.patch
>
>
> Consider the following setup:
> {code}
> create table tbl (n bigint, t string);
> insert into tbl values (1, 'one');
> insert into tbl values(2, 'two');
> select a.n, a.t, isnull(b.n), isnull(b.t) from (select * from tbl where n =
> 1) a left outer join (select * from tbl where 1 = 2) b on a.n = b.n;
> 1onefalsetrue
> {code}
> The query should return true for isnull(b.n).
> I've tested by inserting a row with null value for the bigint column into
> tbl, and isnull returns true in that case.
--
This message was sent by Atlassian JIRA
(v6.3.4#6332)
[jira] [Commented] (HIVE-14027) NULL values produced by left outer join do not behave as NULL
[
https://issues.apache.org/jira/browse/HIVE-14027?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=15355602#comment-15355602
]
Ashutosh Chauhan commented on HIVE-14027:
-
+1
expect few golden file updates
> NULL values produced by left outer join do not behave as NULL
> -
>
> Key: HIVE-14027
> URL: https://issues.apache.org/jira/browse/HIVE-14027
> Project: Hive
> Issue Type: Bug
> Components: Query Processor
>Affects Versions: 1.2.1, 2.2.0
>Reporter: Vaibhav Gumashta
>Assignee: Jesus Camacho Rodriguez
> Attachments: HIVE-14027.patch
>
>
> Consider the following setup:
> {code}
> create table tbl (n bigint, t string);
> insert into tbl values (1, 'one');
> insert into tbl values(2, 'two');
> select a.n, a.t, isnull(b.n), isnull(b.t) from (select * from tbl where n =
> 1) a left outer join (select * from tbl where 1 = 2) b on a.n = b.n;
> 1onefalsetrue
> {code}
> The query should return true for isnull(b.n).
> I've tested by inserting a row with null value for the bigint column into
> tbl, and isnull returns true in that case.
--
This message was sent by Atlassian JIRA
(v6.3.4#6332)
[jira] [Commented] (HIVE-14027) NULL values produced by left outer join do not behave as NULL
[
https://issues.apache.org/jira/browse/HIVE-14027?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=15350484#comment-15350484
]
balaswamy vaddeman commented on HIVE-14027:
---
Yes, I modified link
> NULL values produced by left outer join do not behave as NULL
> -
>
> Key: HIVE-14027
> URL: https://issues.apache.org/jira/browse/HIVE-14027
> Project: Hive
> Issue Type: Bug
> Components: Query Processor
>Affects Versions: 1.2.1
>Reporter: Vaibhav Gumashta
>Assignee: Vaibhav Gumashta
>
> Consider the following setup:
> {code}
> create table tbl (n bigint, t string);
> insert into tbl values (1, 'one');
> insert into tbl values(2, 'two');
> select a.n, a.t, isnull(b.n), isnull(b.t) from (select * from tbl where n =
> 1) a left outer join (select * from tbl where 1 = 2) b on a.n = b.n;
> 1onefalsetrue
> {code}
> The query should return true for isnull(b.n).
> I've tested by inserting a row with null value for the bigint column into
> tbl, and isnull returns true in that case.
--
This message was sent by Atlassian JIRA
(v6.3.4#6332)
[jira] [Commented] (HIVE-14027) NULL values produced by left outer join do not behave as NULL
[
https://issues.apache.org/jira/browse/HIVE-14027?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=15348967#comment-15348967
]
Jesus Camacho Rodriguez commented on HIVE-14027:
It seems to be a problem in the execution side, not in the planner.
If we set hive.auto.convert.join to false, we get correct results.
> NULL values produced by left outer join do not behave as NULL
> -
>
> Key: HIVE-14027
> URL: https://issues.apache.org/jira/browse/HIVE-14027
> Project: Hive
> Issue Type: Bug
> Components: Query Processor
>Affects Versions: 1.2.1
>Reporter: Vaibhav Gumashta
>Assignee: Vaibhav Gumashta
>
> Consider the following setup:
> {code}
> create table tbl (n bigint, t string);
> insert into tbl values (1, 'one');
> insert into tbl values(2, 'two');
> select a.n, a.t, isnull(b.n), isnull(b.t) from (select * from tbl where n =
> 1) a left outer join (select * from tbl where 1 = 2) b on a.n = b.n;
> 1onefalsetrue
> {code}
> The query should return true for isnull(b.n).
> I've tested by inserting a row with null value for the bigint column into
> tbl, and isnull returns true in that case.
--
This message was sent by Atlassian JIRA
(v6.3.4#6332)
[jira] [Commented] (HIVE-14027) NULL values produced by left outer join do not behave as NULL
[
https://issues.apache.org/jira/browse/HIVE-14027?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=15347734#comment-15347734
]
Thejas M Nair commented on HIVE-14027:
--
[~vaddebalu] did you mean to post link to a different jira ?
> NULL values produced by left outer join do not behave as NULL
> -
>
> Key: HIVE-14027
> URL: https://issues.apache.org/jira/browse/HIVE-14027
> Project: Hive
> Issue Type: Bug
> Components: Query Processor
>Affects Versions: 1.2.1
>Reporter: Vaibhav Gumashta
>Assignee: Vaibhav Gumashta
>
> Consider the following setup:
> {code}
> create table tbl (n bigint, t string);
> insert into tbl values (1, 'one');
> insert into tbl values(2, 'two');
> select a.n, a.t, isnull(b.n), isnull(b.t) from (select * from tbl where n =
> 1) a left outer join (select * from tbl where 1 = 2) b on a.n = b.n;
> 1onefalsetrue
> {code}
> The query should return true for isnull(b.n).
> I've tested by inserting a row with null value for the bigint column into
> tbl, and isnull returns true in that case.
--
This message was sent by Atlassian JIRA
(v6.3.4#6332)
[jira] [Commented] (HIVE-14027) NULL values produced by left outer join do not behave as NULL
[
https://issues.apache.org/jira/browse/HIVE-14027?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=15347646#comment-15347646
]
balaswamy vaddeman commented on HIVE-14027:
---
similar issue.https://issues.apache.org/jira/browse/HIVE-14027
> NULL values produced by left outer join do not behave as NULL
> -
>
> Key: HIVE-14027
> URL: https://issues.apache.org/jira/browse/HIVE-14027
> Project: Hive
> Issue Type: Bug
> Components: Query Processor
>Affects Versions: 1.2.1
>Reporter: Vaibhav Gumashta
>Assignee: Vaibhav Gumashta
>
> Consider the following setup:
> {code}
> create table tbl (n bigint, t string);
> insert into tbl values (1, 'one');
> insert into tbl values(2, 'two');
> select a.n, a.t, isnull(b.n), isnull(b.t) from (select * from tbl where n =
> 1) a left outer join (select * from tbl where 1 = 2) b on a.n = b.n;
> 1onefalsetrue
> {code}
> The query should return true for isnull(b.n).
> I've tested by inserting a row with null value for the bigint column into
> tbl, and isnull returns true in that case.
--
This message was sent by Atlassian JIRA
(v6.3.4#6332)
[jira] [Commented] (HIVE-14027) NULL values produced by left outer join do not behave as NULL
[
https://issues.apache.org/jira/browse/HIVE-14027?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=15346927#comment-15346927
]
Thejas M Nair commented on HIVE-14027:
--
This is happening only when UDF is in picture. If it is a select without UDF,
the results are correct.
Some more experiments I ran with hive 1.2.1 -
created a new table - tbl2
hive> select * from tbl2;
OK
2 two
ran -
select a.n, a.t, concat(b.n, ""), concat(b.t, "") from (select * from tbl where
n = 1) a left outer join (select * from tbl2 where 1 = 2) b on a.n = b.n;
actually ran - select a.n, a.t, concat(b.n, ""), concat(b.t, "") from (select *
from tbl where n = 1) a left outer join (select * from tbl2 where 1 = 2) b on
a.n = b.n;
ie replaced tbl with tbl2 for b in original query.
it gives -
1 one 1 NULL
looks like the join key is getting used as the input
ie . a.t is getting used as input
sorry, a.n is getting used as input to concat(b.n, "")
there might be an assumption somewhere that a.n and b.n are the same, since
this is a join
but doesn't hold true for outer join
> NULL values produced by left outer join do not behave as NULL
> -
>
> Key: HIVE-14027
> URL: https://issues.apache.org/jira/browse/HIVE-14027
> Project: Hive
> Issue Type: Bug
> Components: Query Processor
>Affects Versions: 1.2.1, 2.0.1
>Reporter: Vaibhav Gumashta
>Assignee: Vaibhav Gumashta
>
> Consider the following setup:
> {code}
> create table tbl (n bigint, t string);
> insert into tbl values (1, 'one');
> insert into tbl values(2, 'two');
> select a.n, a.t, isnull(b.n), isnull(b.t) from (select * from tbl where n =
> 1) a left outer join (select * from tbl where 1 = 2) b on a.n = b.n;
> 1onefalsetrue
> {code}
> The query should return true for isnull(b.n).
> I've tested by inserting a row with null value for the bigint column into
> tbl, and isnull returns true in that case.
--
This message was sent by Atlassian JIRA
(v6.3.4#6332)
[jira] [Commented] (HIVE-14027) NULL values produced by left outer join do not behave as NULL
[
https://issues.apache.org/jira/browse/HIVE-14027?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=15342133#comment-15342133
]
Vaibhav Gumashta commented on HIVE-14027:
-
Explain output:
{code}
hive> explain select a.n, a.t, isnull(b.n), isnull(b.t) from (select * from
biq998_bigint where n = 1) a left outer join (select * from biq998_bigint
where 1 = 2) b on a.n = b.n;
OK
STAGE DEPENDENCIES:
Stage-4 is a root stage
Stage-3 depends on stages: Stage-4
Stage-0 depends on stages: Stage-3
STAGE PLANS:
Stage: Stage-4
Map Reduce Local Work
Alias -> Map Local Tables:
$hdt$_1:biq998_bigint
Fetch Operator
limit: -1
Alias -> Map Local Operator Tree:
$hdt$_1:biq998_bigint
TableScan
alias: biq998_bigint
Statistics: Num rows: 2 Data size: 10 Basic stats: COMPLETE Column
stats: NONE
Filter Operator
predicate: false (type: boolean)
Statistics: Num rows: 1 Data size: 5 Basic stats: COMPLETE Column
stats: NONE
Select Operator
expressions: n (type: bigint), t (type: string)
outputColumnNames: _col0, _col1
Statistics: Num rows: 1 Data size: 5 Basic stats: COMPLETE
Column stats: NONE
HashTable Sink Operator
keys:
0 1 (type: bigint)
1 _col0 (type: bigint)
Stage: Stage-3
Map Reduce
Map Operator Tree:
TableScan
alias: biq998_bigint
Statistics: Num rows: 2 Data size: 10 Basic stats: COMPLETE Column
stats: NONE
Filter Operator
predicate: (n = 1) (type: boolean)
Statistics: Num rows: 1 Data size: 5 Basic stats: COMPLETE Column
stats: NONE
Select Operator
expressions: t (type: string)
outputColumnNames: _col1
Statistics: Num rows: 1 Data size: 5 Basic stats: COMPLETE
Column stats: NONE
Map Join Operator
condition map:
Left Outer Join0 to 1
keys:
0 1 (type: bigint)
1 _col0 (type: bigint)
outputColumnNames: _col1, _col2, _col3
Statistics: Num rows: 1 Data size: 5 Basic stats: COMPLETE
Column stats: NONE
Select Operator
expressions: 1 (type: bigint), _col1 (type: string), _col2
is null (type: boolean), _col3 is null (type: boolean)
outputColumnNames: _col0, _col1, _col2, _col3
Statistics: Num rows: 1 Data size: 5 Basic stats: COMPLETE
Column stats: NONE
File Output Operator
compressed: false
Statistics: Num rows: 1 Data size: 5 Basic stats:
COMPLETE Column stats: NONE
table:
input format:
org.apache.hadoop.mapred.SequenceFileInputFormat
output format:
org.apache.hadoop.hive.ql.io.HiveSequenceFileOutputFormat
serde:
org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe
Local Work:
Map Reduce Local Work
Stage: Stage-0
Fetch Operator
limit: -1
Processor Tree:
ListSink
Time taken: 0.637 seconds, Fetched: 70 row(s)
{code}
Looks like stage-4 after the filter operator should output 0 rows; stats shown
are different.
> NULL values produced by left outer join do not behave as NULL
> -
>
> Key: HIVE-14027
> URL: https://issues.apache.org/jira/browse/HIVE-14027
> Project: Hive
> Issue Type: Bug
> Components: Query Processor
>Affects Versions: 1.2.1, 2.0.1
>Reporter: Vaibhav Gumashta
>Assignee: Vaibhav Gumashta
>
> Consider the following setup:
> {code}
> create table tbl (n bigint, t string);
> insert into tbl values (1, 'one');
> insert into tbl values(2, 'two');
> select a.n, a.t, isnull(b.n), isnull(b.t) from (select * from tbl where n =
> 1) a left outer join (select * from tbl where 1 = 2) b on a.n = b.n;
> 1onefalsetrue
> {code}
> The query should return true for isnull(b.n).
> I've tested by inserting a row with null value for the bigint column into
> tbl, and isnull returns true in that case.
--
This message was sent by Atlassian JIRA
(v6.3.4#6332)
