[jira] [Commented] (HIVE-14918) Function concat_ws get a wrong value
[
https://issues.apache.org/jira/browse/HIVE-14918?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=15561219#comment-15561219
]
Pengcheng Xiong commented on HIVE-14918:
[~wisgood] if you like, you are always welcome to create your own UDF to treat
it differently.
> Function concat_ws get a wrong value
> --
>
> Key: HIVE-14918
> URL: https://issues.apache.org/jira/browse/HIVE-14918
> Project: Hive
> Issue Type: Bug
> Components: UDF
>Affects Versions: 1.1.1, 2.0.0, 2.1.0, 2.0.1
>Reporter: Xiaowei Wang
>Assignee: Xiaowei Wang
>Priority: Critical
> Fix For: 2.1.0
>
> Attachments: HIVE-14918.0.patch
>
>
> FROM src INSERT OVERWRITE TABLE dest1 SELECT 'abc', 'xyz', '8675309' WHERE
> src.key = 86;
> SELECT concat_ws('.',NULL) FROM dest1 ;
> The result is a empty string "",but I think it should be return NULL .
--
This message was sent by Atlassian JIRA
(v6.3.4#6332)
[jira] [Commented] (HIVE-14918) Function concat_ws get a wrong value
[
https://issues.apache.org/jira/browse/HIVE-14918?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=15561156#comment-15561156
]
Xiaowei Wang commented on HIVE-14918:
-
Yes,It is not a bug in MySQL .I close .Thanks!
> Function concat_ws get a wrong value
> --
>
> Key: HIVE-14918
> URL: https://issues.apache.org/jira/browse/HIVE-14918
> Project: Hive
> Issue Type: Bug
> Components: UDF
>Affects Versions: 1.1.1, 2.0.0, 2.1.0, 2.0.1
>Reporter: Xiaowei Wang
>Assignee: Xiaowei Wang
>Priority: Critical
> Fix For: 2.1.0
>
> Attachments: HIVE-14918.0.patch
>
>
> FROM src INSERT OVERWRITE TABLE dest1 SELECT 'abc', 'xyz', '8675309' WHERE
> src.key = 86;
> SELECT concat_ws('.',NULL) FROM dest1 ;
> The result is a empty string "",but I think it should be return NULL .
--
This message was sent by Atlassian JIRA
(v6.3.4#6332)
[jira] [Commented] (HIVE-14918) Function concat_ws get a wrong value
[
https://issues.apache.org/jira/browse/HIVE-14918?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=15561148#comment-15561148
]
Pengcheng Xiong commented on HIVE-14918:
[~wisgood] i just took a look at the link. it was not marked as a bug in MySQL
{code}
[19 Nov 2004 14:21] Sergei Golubchik
Thank you for taking the time to write to us, but this is not
a bug. Please double-check the documentation available at
http://www.mysql.com/documentation/ and the instructions on
how to report a bug at http://bugs.mysql.com/how-to-report.php
Additional info:
According to the manual, CONCAT_WS skips any `NULL' values after the separator
argument.
Thus CONCAT_WS(' ', NULL, NULL) has zero strings to concat, and the result,
quite naturally, is empty string. It does not depend on the separator:
mysql> select concat('>', concat_ws('=', NULL, NULL), '<');
+--+
| concat('>', concat_ws('|', NULL, NULL), '<') |
+--+
| >< |
+--+
{code}
> Function concat_ws get a wrong value
> --
>
> Key: HIVE-14918
> URL: https://issues.apache.org/jira/browse/HIVE-14918
> Project: Hive
> Issue Type: Bug
> Components: UDF
>Affects Versions: 1.1.1, 2.0.0, 2.1.0, 2.0.1
>Reporter: Xiaowei Wang
>Assignee: Xiaowei Wang
>Priority: Critical
> Fix For: 2.1.0
>
> Attachments: HIVE-14918.0.patch
>
>
> FROM src INSERT OVERWRITE TABLE dest1 SELECT 'abc', 'xyz', '8675309' WHERE
> src.key = 86;
> SELECT concat_ws('.',NULL) FROM dest1 ;
> The result is a empty string "",but I think it should be return NULL .
--
This message was sent by Atlassian JIRA
(v6.3.4#6332)
[jira] [Commented] (HIVE-14918) Function concat_ws get a wrong value
[
https://issues.apache.org/jira/browse/HIVE-14918?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=15561056#comment-15561056
]
Xiaowei Wang commented on HIVE-14918:
-
It is true that concat_ws('.',NULL) of MySQL return empty.
https://bugs.mysql.com/bug.php?id=6719
But I and most colleagues of mine don't understand.
Regardless of MySQL aside, which do you think is more reasonable ?
Thanks for your explanation.
> Function concat_ws get a wrong value
> --
>
> Key: HIVE-14918
> URL: https://issues.apache.org/jira/browse/HIVE-14918
> Project: Hive
> Issue Type: Bug
> Components: UDF
>Affects Versions: 1.1.1, 2.0.0, 2.1.0, 2.0.1
>Reporter: Xiaowei Wang
>Assignee: Xiaowei Wang
>Priority: Critical
> Fix For: 2.1.0
>
> Attachments: HIVE-14918.0.patch
>
>
> FROM src INSERT OVERWRITE TABLE dest1 SELECT 'abc', 'xyz', '8675309' WHERE
> src.key = 86;
> SELECT concat_ws('.',NULL) FROM dest1 ;
> The result is a empty string "",but I think it should be return NULL .
--
This message was sent by Atlassian JIRA
(v6.3.4#6332)
[jira] [Commented] (HIVE-14918) Function concat_ws get a wrong value
[
https://issues.apache.org/jira/browse/HIVE-14918?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=15560808#comment-15560808
]
Pengcheng Xiong commented on HIVE-14918:
Initially when it was developed, it was following MySQL.
{code}
mysql> SELECT concat_ws('.',NULL) FROM (SELECT 'abc', 'xyz', '8675309' from t
WHERE t.test = 86)subq;
+-+
| concat_ws('.',NULL) |
+-+
| |
+-+
1 row in set (0.13 sec)
{code}
> Function concat_ws get a wrong value
> --
>
> Key: HIVE-14918
> URL: https://issues.apache.org/jira/browse/HIVE-14918
> Project: Hive
> Issue Type: Bug
> Components: UDF
>Affects Versions: 1.1.1, 2.0.0, 2.1.0, 2.0.1
>Reporter: Xiaowei Wang
>Assignee: Xiaowei Wang
>Priority: Critical
> Fix For: 2.1.0
>
> Attachments: HIVE-14918.0.patch
>
>
> FROM src INSERT OVERWRITE TABLE dest1 SELECT 'abc', 'xyz', '8675309' WHERE
> src.key = 86;
> SELECT concat_ws('.',NULL) FROM dest1 ;
> The result is a empty string "",but I think it should be return NULL .
--
This message was sent by Atlassian JIRA
(v6.3.4#6332)
[jira] [Commented] (HIVE-14918) Function concat_ws get a wrong value
[
https://issues.apache.org/jira/browse/HIVE-14918?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=15560809#comment-15560809
]
Pengcheng Xiong commented on HIVE-14918:
Initially when it was developed, it was following MySQL.
{code}
mysql> SELECT concat_ws('.',NULL) FROM (SELECT 'abc', 'xyz', '8675309' from t
WHERE t.test = 86)subq;
+-+
| concat_ws('.',NULL) |
+-+
| |
+-+
1 row in set (0.13 sec)
{code}
> Function concat_ws get a wrong value
> --
>
> Key: HIVE-14918
> URL: https://issues.apache.org/jira/browse/HIVE-14918
> Project: Hive
> Issue Type: Bug
> Components: UDF
>Affects Versions: 1.1.1, 2.0.0, 2.1.0, 2.0.1
>Reporter: Xiaowei Wang
>Assignee: Xiaowei Wang
>Priority: Critical
> Fix For: 2.1.0
>
> Attachments: HIVE-14918.0.patch
>
>
> FROM src INSERT OVERWRITE TABLE dest1 SELECT 'abc', 'xyz', '8675309' WHERE
> src.key = 86;
> SELECT concat_ws('.',NULL) FROM dest1 ;
> The result is a empty string "",but I think it should be return NULL .
--
This message was sent by Atlassian JIRA
(v6.3.4#6332)
[jira] [Commented] (HIVE-14918) Function concat_ws get a wrong value
[
https://issues.apache.org/jira/browse/HIVE-14918?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=15560739#comment-15560739
]
Xiaowei Wang commented on HIVE-14918:
-
I mean, concat_ws('.',NULL) should return NULL not a empty string "" .What do
you think？
> Function concat_ws get a wrong value
> --
>
> Key: HIVE-14918
> URL: https://issues.apache.org/jira/browse/HIVE-14918
> Project: Hive
> Issue Type: Bug
> Components: UDF
>Affects Versions: 1.1.1, 2.0.0, 2.1.0, 2.0.1
>Reporter: Xiaowei Wang
>Assignee: Xiaowei Wang
>Priority: Critical
> Fix For: 2.1.0
>
> Attachments: HIVE-14918.0.patch
>
>
> FROM src INSERT OVERWRITE TABLE dest1 SELECT 'abc', 'xyz', '8675309' WHERE
> src.key = 86;
> SELECT concat_ws('.',NULL) FROM dest1 ;
> The result is a empty string "",but I think it should be return NULL .
--
This message was sent by Atlassian JIRA
(v6.3.4#6332)
[jira] [Commented] (HIVE-14918) Function concat_ws get a wrong value
[
https://issues.apache.org/jira/browse/HIVE-14918?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=15560316#comment-15560316
]
Pengcheng Xiong commented on HIVE-14918:
[~wisgood], i tried on current master, it is working fine. If you have problem
on 2.1, maybe we should backport some patches.
{code}
hive> create table d (c1 string, c2 string, c3 string);
OK
hive> FROM src INSERT OVERWRITE TABLE d SELECT 'abc', 'xyz', '8675309' WHERE
src.key = 86;
Query ID = pxiong_20161009101753_605845db-aeb9-4dab-87d6-5ad51fab1f79
Total jobs = 1
hive> select * from d;
OK
abc xyz 8675309
Time taken: 0.127 seconds, Fetched: 1 row(s)
hive> SELECT concat_ws('.',NULL) FROM d;
OK
Time taken: 0.096 seconds, Fetched: 1 row(s)
{code}
And also, a rewritten query also works fine
{code}
hive> SELECT concat_ws('.',NULL) FROM (SELECT 'abc', 'xyz', '8675309' from src
WHERE src.key = 86)subq;
OK
Time taken: 0.272 seconds, Fetched: 1 row(s)
{code}
> Function concat_ws get a wrong value
> --
>
> Key: HIVE-14918
> URL: https://issues.apache.org/jira/browse/HIVE-14918
> Project: Hive
> Issue Type: Bug
> Components: UDF
>Affects Versions: 1.1.1, 2.0.0, 2.1.0, 2.0.1
>Reporter: Xiaowei Wang
>Assignee: Xiaowei Wang
>Priority: Critical
> Fix For: 2.1.0
>
> Attachments: HIVE-14918.0.patch
>
>
> FROM src INSERT OVERWRITE TABLE dest1 SELECT 'abc', 'xyz', '8675309' WHERE
> src.key = 86;
> SELECT concat_ws('.',NULL) FROM dest1 ;
> The result is a empty string "",but I think it should be return NULL .
--
This message was sent by Atlassian JIRA
(v6.3.4#6332)
[jira] [Commented] (HIVE-14918) Function concat_ws get a wrong value
[
https://issues.apache.org/jira/browse/HIVE-14918?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=15559558#comment-15559558
]
Hive QA commented on HIVE-14918:
Here are the results of testing the latest attachment:
https://issues.apache.org/jira/secure/attachment/12832319/HIVE-14918.0.patch
{color:green}SUCCESS:{color} +1 due to 1 test(s) being added or modified.
{color:red}ERROR:{color} -1 due to 3 failed/errored test(s), 10663 tests
executed
*Failed tests:*
{noformat}
org.apache.hadoop.hive.cli.TestMiniTezCliDriver.testCliDriver[explainanalyze_2]
org.apache.hive.jdbc.TestJdbcWithMiniHS2.testAddJarConstructorUnCaching
org.apache.hive.spark.client.TestSparkClient.testJobSubmission
{noformat}
Test results: https://builds.apache.org/job/PreCommit-HIVE-Build/1440/testReport
Console output: https://builds.apache.org/job/PreCommit-HIVE-Build/1440/console
Test logs:
http://ec2-204-236-174-241.us-west-1.compute.amazonaws.com/logs/PreCommit-HIVE-Build-1440/
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: 3 tests failed
{noformat}
This message is automatically generated.
ATTACHMENT ID: 12832319 - PreCommit-HIVE-Build
> Function concat_ws get a wrong value
> --
>
> Key: HIVE-14918
> URL: https://issues.apache.org/jira/browse/HIVE-14918
> Project: Hive
> Issue Type: Bug
> Components: UDF
>Affects Versions: 1.1.1, 2.0.0, 2.1.0, 2.0.1
>Reporter: Xiaowei Wang
>Assignee: Xiaowei Wang
>Priority: Critical
> Fix For: 2.1.0
>
> Attachments: HIVE-14918.0.patch
>
>
> FROM src INSERT OVERWRITE TABLE dest1 SELECT 'abc', 'xyz', '8675309' WHERE
> src.key = 86;
> SELECT concat_ws('.',NULL) FROM dest1 ;
> The result is a empty string "",but I think it should be return NULL .
--
This message was sent by Atlassian JIRA
(v6.3.4#6332)
[jira] [Commented] (HIVE-14918) Function concat_ws get a wrong value
[
https://issues.apache.org/jira/browse/HIVE-14918?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=15559471#comment-15559471
]
Xiaowei Wang commented on HIVE-14918:
-
Is this a problem ?
[~pxiong] [~speleato] [~ashutoshc] [~prasanth_j] [~thejas]
> Function concat_ws get a wrong value
> --
>
> Key: HIVE-14918
> URL: https://issues.apache.org/jira/browse/HIVE-14918
> Project: Hive
> Issue Type: Bug
> Components: UDF
>Affects Versions: 1.1.1, 2.0.0, 2.1.0, 2.0.1
>Reporter: Xiaowei Wang
>Assignee: Xiaowei Wang
>Priority: Critical
> Fix For: 2.1.0
>
> Attachments: HIVE-14918.0.patch
>
>
> FROM src INSERT OVERWRITE TABLE dest1 SELECT 'abc', 'xyz', '8675309' WHERE
> src.key = 86;
> SELECT concat_ws('.',NULL) FROM dest1 ;
> The result is a empty string "",but I think it should be return NULL .
--
This message was sent by Atlassian JIRA
(v6.3.4#6332)
