[jira] [Comment Edited] (DRILL-2354) Documentation for Concat Function & Concat Operator

["Victoria Markman (JIRA)" <ji...@apache.org>]

    [ 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)