[
https://issues.apache.org/jira/browse/DRILL-2354?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=14511830#comment-14511830
]
Victoria Markman edited comment on DRILL-2354 at 4/24/15 9:42 PM:
------------------------------------------------------------------
I take it back, on closer examination our behavior is different from Postgres.
Postgres:
{code}
postgres=# select c_boolean || c_date || c_varchar from j2;
ERROR: operator does not exist: boolean || date
LINE 1: select c_boolean || c_date || c_varchar from j2;
^
HINT: No operator matches the given name and argument type(s). You might need
to add explicit type casts.
{code}
Drill:
{code}
0: jdbc:drill:schema=dfs> select c_boolean || c_date || c_varchar from j2;
+------------+
| EXPR$0 |
+------------+
| false1960-01-030000 000000 00000 |
| true1960-01-050003 572402 62861 |
| false1960-01-080005 641325 34148 |
| false1960-01-110007 378129 06970 |
| false1960-01-140007 872046 92962 |
| true1960-01-150008 397933 38800 |
| false1960-01-180009 335067 20452 |
| false1960-01-200009 670826 24885 |
| false1960-01-210011 071815 62690 |
| null |
+------------+
10 rows selected (0.15 seconds)
{code}
At the moment you have at least one string literal in Postgres, it works:
{code}
postgres=# select 'abc' || c_boolean || c_date || c_varchar from j2;
?column?
-------------------------------------
abcfalse1960-01-030000 000000 00000
abctrue1960-01-050003 572402 62861
abcfalse1960-01-080005 641325 34148
abcfalse1960-01-110007 378129 06970
abcfalse1960-01-140007 872046 92962
abctrue1960-01-150008 397933 38800
abcfalse1960-01-180009 335067 20452
abcfalse1960-01-200009 670826 24885
abcfalse1960-01-210011 071815 62690
(10 rows)
{code}
So my conclusion is: we should remove "string || non-string or non-string ||
string" line and say that "as long as we can implicitly convert input to a
string, it will work".
Except for complex types: see drill-2874
I don't think we should worry about not being compatible with postgres, it
seems we are less restrictive, but I need more time to think about it.
was (Author: vicky):
I take it back, on closer examination our behavior is different from Postgres.
Postgres:
{code}
postgres=# select c_boolean || c_date || c_varchar from j2;
ERROR: operator does not exist: boolean || date
LINE 1: select c_boolean || c_date || c_varchar from j2;
^
HINT: No operator matches the given name and argument type(s). You might need
to add explicit type casts.
{code}
Drill:
{code}
0: jdbc:drill:schema=dfs> select c_boolean || c_date || c_varchar from j2;
+------------+
| EXPR$0 |
+------------+
| false1960-01-030000 000000 00000 |
| true1960-01-050003 572402 62861 |
| false1960-01-080005 641325 34148 |
| false1960-01-110007 378129 06970 |
| false1960-01-140007 872046 92962 |
| true1960-01-150008 397933 38800 |
| false1960-01-180009 335067 20452 |
| false1960-01-200009 670826 24885 |
| false1960-01-210011 071815 62690 |
| null |
+------------+
10 rows selected (0.15 seconds)
{code}
However, at the moment you have at lease one string literal in Postgres, it
works:
{code}
postgres=# select 'abc' || c_boolean || c_date || c_varchar from j2;
?column?
-------------------------------------
abcfalse1960-01-030000 000000 00000
abctrue1960-01-050003 572402 62861
abcfalse1960-01-080005 641325 34148
abcfalse1960-01-110007 378129 06970
abcfalse1960-01-140007 872046 92962
abctrue1960-01-150008 397933 38800
abcfalse1960-01-180009 335067 20452
abcfalse1960-01-200009 670826 24885
abcfalse1960-01-210011 071815 62690
(10 rows)
{code}
So my conclusion is: we should remove "string || non-string or non-string ||
string" line and say that "as long as we can implicitly convert input to a
string, it will work".
Except for complex types: see drill-2874
I don't think we should worry about not being compatible with postgres, it
seems we are less restrictive, but I need more time to think about it.
> Documentation for Concat Function & Concat Operator
> ----------------------------------------------------
>
> Key: DRILL-2354
> URL: https://issues.apache.org/jira/browse/DRILL-2354
> Project: Apache Drill
> Issue Type: Improvement
> Components: Documentation
> Reporter: Sean Hsuan-Yi Chu
> Assignee: Kristine Hahn
>
> In Drill, we have two ways to concatenate Strings:
> 1. Concat Function: concat(c1, c2, c3, ...)
> 2. Concat Operator: c1 || c2 || c3 || ...
> They are doing same thing except for dealing with NULL:
> 1. For concat function, NULL is treated as empty string
> 2. For concat operator: if any input is NULL, output NULL (no matter what
> other columns are)
> These are subtle difference and would be better to be documented.
> --------------------------------------------------------------------
> Some discussion can be found from DRILL-2328
--
This message was sent by Atlassian JIRA
(v6.3.4#6332)
