[jira] [Updated] (HIVE-18334) Cannot JOIN ON result of COALESCE

2021-03-18 Thread Hendrik Schultze (Jira) 


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

Hendrik Schultze updated HIVE-18334:

Priority: Critical  (was: Minor)

> Cannot JOIN ON result of COALESCE 
> --
>
> Key: HIVE-18334
> URL: https://issues.apache.org/jira/browse/HIVE-18334
> Project: Hive
>  Issue Type: Bug
>  Components: Query Processor
>Affects Versions: 2.3.2, 2.3.3
> Environment: Amazon AWS
> Release label:emr-5.11.0
> Hadoop distribution:Amazon 2.7.3
> Applications:Hive 2.3.2, Pig 0.17.0, Hue 4.0.1
> classification=hive-site,properties=[hive.strict.checks.cartesian.product=false,hive.mapred.mode=nonstrict]
>Reporter: Paul Jackson
>Priority: Critical
>
> A join is returning no results when the ON clause is equating the results of 
> two COALESCE functions. To reproduce:
> {code:SQL}
> CREATE TABLE t5 (
>   dno INTEGER,
>   dname VARCHAR(30),
>   eno INTEGER,
>   ename VARCHAR(30));
> CREATE TABLE t6 (
>   dno INTEGER,
>   dname VARCHAR(30),
>   eno INTEGER,
>   ename VARCHAR(30));
> INSERT INTO t5 VALUES
>   (10, 'FOO', NULL, NULL),
>   (20, 'BAR', NULL, NULL),
>   (NULL, NULL, 7300, 'LARRY'),
>   (NULL, NULL, 7400, 'MOE'),
>   (NULL, NULL, 7500, 'CURLY');
> INSERT INTO t6 VALUES
>   (10, 'LENNON', NULL, NULL),
>   (20, 'MCCARTNEY', NULL, NULL),
>   (NULL, NULL, 7300, 'READY'),
>   (NULL, NULL, 7400, 'WILLING'),
>   (NULL, NULL, 7500, 'ABLE');
> -- Fails with 0 results
> SELECT *
> FROM t5
> INNER JOIN t6
> ON COALESCE(`t5`.`eno`, `t5`.`dno`) = COALESCE(`t6`.`eno`, `t6`.`dno`)
> -- Full cross with where clause works (in nonstrict mode), returning 5 results
> SELECT *
> FROM t5
> JOIN t6
> WHERE `t5`.`eno` = `t6`.`eno` OR `t5`.`dno` = `t6`.`dno`
> -- Strange that coalescing the same field returns 2 results...
> SELECT *
> FROM t5
> INNER JOIN t6
> ON COALESCE(`t5`.`dno`, `t5`.`dno`) = COALESCE(`t6`.`dno`, `t6`.`dno`)
> -- ...and coalescing the other field returns 3 results
> SELECT *
> FROM t5
> INNER JOIN t6
> ON COALESCE(`t5`.`eno`, `t5`.`eno`) = COALESCE(`t6`.`eno`, `t6`.`eno`)
> {code}



--
This message was sent by Atlassian Jira
(v8.3.4#803005)

[jira] [Updated] (HIVE-18334) Cannot JOIN ON result of COALESCE

2021-03-18 Thread Hendrik Schultze (Jira) 


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

Hendrik Schultze updated HIVE-18334:

Affects Version/s: 2.3.3

> Cannot JOIN ON result of COALESCE 
> --
>
> Key: HIVE-18334
> URL: https://issues.apache.org/jira/browse/HIVE-18334
> Project: Hive
>  Issue Type: Bug
>  Components: Query Processor
>Affects Versions: 2.3.2, 2.3.3
> Environment: Amazon AWS
> Release label:emr-5.11.0
> Hadoop distribution:Amazon 2.7.3
> Applications:Hive 2.3.2, Pig 0.17.0, Hue 4.0.1
> classification=hive-site,properties=[hive.strict.checks.cartesian.product=false,hive.mapred.mode=nonstrict]
>Reporter: Paul Jackson
>Priority: Minor
>
> A join is returning no results when the ON clause is equating the results of 
> two COALESCE functions. To reproduce:
> {code:SQL}
> CREATE TABLE t5 (
>   dno INTEGER,
>   dname VARCHAR(30),
>   eno INTEGER,
>   ename VARCHAR(30));
> CREATE TABLE t6 (
>   dno INTEGER,
>   dname VARCHAR(30),
>   eno INTEGER,
>   ename VARCHAR(30));
> INSERT INTO t5 VALUES
>   (10, 'FOO', NULL, NULL),
>   (20, 'BAR', NULL, NULL),
>   (NULL, NULL, 7300, 'LARRY'),
>   (NULL, NULL, 7400, 'MOE'),
>   (NULL, NULL, 7500, 'CURLY');
> INSERT INTO t6 VALUES
>   (10, 'LENNON', NULL, NULL),
>   (20, 'MCCARTNEY', NULL, NULL),
>   (NULL, NULL, 7300, 'READY'),
>   (NULL, NULL, 7400, 'WILLING'),
>   (NULL, NULL, 7500, 'ABLE');
> -- Fails with 0 results
> SELECT *
> FROM t5
> INNER JOIN t6
> ON COALESCE(`t5`.`eno`, `t5`.`dno`) = COALESCE(`t6`.`eno`, `t6`.`dno`)
> -- Full cross with where clause works (in nonstrict mode), returning 5 results
> SELECT *
> FROM t5
> JOIN t6
> WHERE `t5`.`eno` = `t6`.`eno` OR `t5`.`dno` = `t6`.`dno`
> -- Strange that coalescing the same field returns 2 results...
> SELECT *
> FROM t5
> INNER JOIN t6
> ON COALESCE(`t5`.`dno`, `t5`.`dno`) = COALESCE(`t6`.`dno`, `t6`.`dno`)
> -- ...and coalescing the other field returns 3 results
> SELECT *
> FROM t5
> INNER JOIN t6
> ON COALESCE(`t5`.`eno`, `t5`.`eno`) = COALESCE(`t6`.`eno`, `t6`.`eno`)
> {code}



--
This message was sent by Atlassian Jira
(v8.3.4#803005)

[jira] [Updated] (HIVE-18334) Cannot JOIN ON result of COALESCE

2018-01-04 Thread Paul Jackson (JIRA) 

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

Paul Jackson updated HIVE-18334:

Component/s: Query Processor

> Cannot JOIN ON result of COALESCE 
> --
>
> Key: HIVE-18334
> URL: https://issues.apache.org/jira/browse/HIVE-18334
> Project: Hive
>  Issue Type: Bug
>  Components: Query Processor
>Affects Versions: 2.3.2
> Environment: Amazon AWS
> Release label:emr-5.11.0
> Hadoop distribution:Amazon 2.7.3
> Applications:Hive 2.3.2, Pig 0.17.0, Hue 4.0.1
> classification=hive-site,properties=[hive.strict.checks.cartesian.product=false,hive.mapred.mode=nonstrict]
>Reporter: Paul Jackson
>Priority: Minor
>
> A join is returning no results when the ON clause is equating the results of 
> two COALESCE functions. To reproduce:
> {code:SQL}
> CREATE TABLE t5 (
>   dno INTEGER,
>   dname VARCHAR(30),
>   eno INTEGER,
>   ename VARCHAR(30));
> CREATE TABLE t6 (
>   dno INTEGER,
>   dname VARCHAR(30),
>   eno INTEGER,
>   ename VARCHAR(30));
> INSERT INTO t5 VALUES
>   (10, 'FOO', NULL, NULL),
>   (20, 'BAR', NULL, NULL),
>   (NULL, NULL, 7300, 'LARRY'),
>   (NULL, NULL, 7400, 'MOE'),
>   (NULL, NULL, 7500, 'CURLY');
> INSERT INTO t6 VALUES
>   (10, 'LENNON', NULL, NULL),
>   (20, 'MCCARTNEY', NULL, NULL),
>   (NULL, NULL, 7300, 'READY'),
>   (NULL, NULL, 7400, 'WILLING'),
>   (NULL, NULL, 7500, 'ABLE');
> -- Fails with 0 results
> SELECT *
> FROM t5
> INNER JOIN t6
> ON COALESCE(`t5`.`eno`, `t5`.`dno`) = COALESCE(`t6`.`eno`, `t6`.`dno`)
> -- Full cross with where clause works (in nonstrict mode), returning 5 results
> SELECT *
> FROM t5
> JOIN t6
> WHERE `t5`.`eno` = `t6`.`eno` OR `t5`.`dno` = `t6`.`dno`
> -- Strange that coalescing the same field returns 2 results...
> SELECT *
> FROM t5
> INNER JOIN t6
> ON COALESCE(`t5`.`dno`, `t5`.`dno`) = COALESCE(`t6`.`dno`, `t6`.`dno`)
> -- ...and coalescing the other field returns 3 results
> SELECT *
> FROM t5
> INNER JOIN t6
> ON COALESCE(`t5`.`eno`, `t5`.`eno`) = COALESCE(`t6`.`eno`, `t6`.`eno`)
> {code}



--
This message was sent by Atlassian JIRA
(v6.4.14#64029)

[jira] [Updated] (HIVE-18334) Cannot JOIN ON result of COALESCE

2017-12-22 Thread Paul Jackson (JIRA) 

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

Paul Jackson updated HIVE-18334:

Description: 
A join is returning no results when the ON clause is equating the results of 
two COALESCE functions. To reproduce:

{code:SQL}
CREATE TABLE t5 (
  dno INTEGER,
  dname VARCHAR(30),
  eno INTEGER,
  ename VARCHAR(30));
CREATE TABLE t6 (
  dno INTEGER,
  dname VARCHAR(30),
  eno INTEGER,
  ename VARCHAR(30));
INSERT INTO t5 VALUES
  (10, 'FOO', NULL, NULL),
  (20, 'BAR', NULL, NULL),
  (NULL, NULL, 7300, 'LARRY'),
  (NULL, NULL, 7400, 'MOE'),
  (NULL, NULL, 7500, 'CURLY');
INSERT INTO t6 VALUES
  (10, 'LENNON', NULL, NULL),
  (20, 'MCCARTNEY', NULL, NULL),
  (NULL, NULL, 7300, 'READY'),
  (NULL, NULL, 7400, 'WILLING'),
  (NULL, NULL, 7500, 'ABLE');

-- Fails with 0 results
SELECT *
FROM t5
INNER JOIN t6
ON COALESCE(`t5`.`eno`, `t5`.`dno`) = COALESCE(`t6`.`eno`, `t6`.`dno`)

-- Full cross with where clause works (in nonstrict mode), returning 5 results
SELECT *
FROM t5
JOIN t6
WHERE `t5`.`eno` = `t6`.`eno` OR `t5`.`dno` = `t6`.`dno`

-- Strange that coalescing the same field returns 2 results...
SELECT *
FROM t5
INNER JOIN t6
ON COALESCE(`t5`.`dno`, `t5`.`dno`) = COALESCE(`t6`.`dno`, `t6`.`dno`)

-- ...and coalescing the other field returns 3 results
SELECT *
FROM t5
INNER JOIN t6
ON COALESCE(`t5`.`eno`, `t5`.`eno`) = COALESCE(`t6`.`eno`, `t6`.`eno`)
{code}

  was:
A join is returning no results when the ON clause is equating the results of 
two COALESCE functions. To reproduce:

{code:SQL}
CREATE TABLE t5 (
  dno INTEGER,
  dname VARCHAR(30),
  eno INTEGER,
  ename VARCHAR(30));
CREATE TABLE t6 (
  dno INTEGER,
  dname VARCHAR(30),
  eno INTEGER,
  ename VARCHAR(30));
INSERT INTO t5 VALUES
  (10, 'FOO', NULL, NULL),
  (20, 'BAR', NULL, NULL),
  (NULL, NULL, 7300, 'LARRY'),
  (NULL, NULL, 7400, 'MOE'),
  (NULL, NULL, 7500, 'CURLY');
INSERT INTO t6 VALUES
  (10, 'LENNON', NULL, NULL),
  (20, 'MCCARTNEY', NULL, NULL),
  (NULL, NULL, 7300, 'READY'),
  (NULL, NULL, 7400, 'WILLING'),
  (NULL, NULL, 7500, 'ABLE');

-- Fails with 0 results
SELECT *
FROM t5
INNER JOIN t6
ON COALESCE(`t5`.`eno`, `t5`.`dno`) = COALESCE(`t6`.`eno`, `t6`.`dno`)

-- Full cross with where clause works, returning 5 results
SELECT *
FROM t5
JOIN t6
WHERE `t5`.`eno` = `t6`.`eno` OR `t5`.`dno` = `t6`.`dno`

-- Strange that coalescing the same field returns 2 results...
SELECT *
FROM t5
INNER JOIN t6
ON COALESCE(`t5`.`dno`, `t5`.`dno`) = COALESCE(`t6`.`dno`, `t6`.`dno`)

-- ...and coalescing the other field returns 3 results
SELECT *
FROM t5
INNER JOIN t6
ON COALESCE(`t5`.`eno`, `t5`.`eno`) = COALESCE(`t6`.`eno`, `t6`.`eno`)
{code}


> Cannot JOIN ON result of COALESCE 
> --
>
> Key: HIVE-18334
> URL: https://issues.apache.org/jira/browse/HIVE-18334
> Project: Hive
>  Issue Type: Bug
>Affects Versions: 2.3.2
> Environment: Amazon AWS
> Release label:emr-5.11.0
> Hadoop distribution:Amazon 2.7.3
> Applications:Hive 2.3.2, Pig 0.17.0, Hue 4.0.1
> classification=hive-site,properties=[hive.strict.checks.cartesian.product=false,hive.mapred.mode=nonstrict]
>Reporter: Paul Jackson
>Priority: Minor
>
> A join is returning no results when the ON clause is equating the results of 
> two COALESCE functions. To reproduce:
> {code:SQL}
> CREATE TABLE t5 (
>   dno INTEGER,
>   dname VARCHAR(30),
>   eno INTEGER,
>   ename VARCHAR(30));
> CREATE TABLE t6 (
>   dno INTEGER,
>   dname VARCHAR(30),
>   eno INTEGER,
>   ename VARCHAR(30));
> INSERT INTO t5 VALUES
>   (10, 'FOO', NULL, NULL),
>   (20, 'BAR', NULL, NULL),
>   (NULL, NULL, 7300, 'LARRY'),
>   (NULL, NULL, 7400, 'MOE'),
>   (NULL, NULL, 7500, 'CURLY');
> INSERT INTO t6 VALUES
>   (10, 'LENNON', NULL, NULL),
>   (20, 'MCCARTNEY', NULL, NULL),
>   (NULL, NULL, 7300, 'READY'),
>   (NULL, NULL, 7400, 'WILLING'),
>   (NULL, NULL, 7500, 'ABLE');
> -- Fails with 0 results
> SELECT *
> FROM t5
> INNER JOIN t6
> ON COALESCE(`t5`.`eno`, `t5`.`dno`) = COALESCE(`t6`.`eno`, `t6`.`dno`)
> -- Full cross with where clause works (in nonstrict mode), returning 5 results
> SELECT *
> FROM t5
> JOIN t6
> WHERE `t5`.`eno` = `t6`.`eno` OR `t5`.`dno` = `t6`.`dno`
> -- Strange that coalescing the same field returns 2 results...
> SELECT *
> FROM t5
> INNER JOIN t6
> ON COALESCE(`t5`.`dno`, `t5`.`dno`) = COALESCE(`t6`.`dno`, `t6`.`dno`)
> -- ...and coalescing the other field returns 3 results
> SELECT *
> FROM t5
> INNER JOIN t6
> ON COALESCE(`t5`.`eno`, `t5`.`eno`) = COALESCE(`t6`.`eno`, `t6`.`eno`)
> {code}



--
This message was sent by Atlassian JIRA
(v6.4.14#64029)