[jira] [Commented] (CALCITE-1375) Why "select CONCAT('a','b') from EMPS" won't work?

["Julian Hyde (JIRA)" <ji...@apache.org>]

    [ https://issues.apache.org/jira/browse/CALCITE-1375?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=15491972#comment-15491972 ] 

Julian Hyde commented on CALCITE-1375:
--------------------------------------

Because CONCAT is not in the SQL standard. It is only in the JDBC standard, hence you can get it via JDBC escape syntax. Use {{||}}, which is standard.

(Why does MySQL use {{CONCAT}}? Because they are idiots and used {{||}} for OR thinking, I suppose, that it would make it easier for C programmers.)

> Why "select CONCAT('a','b') from EMPS" won't work?
> --------------------------------------------------
>
>                 Key: CALCITE-1375
>                 URL: https://issues.apache.org/jira/browse/CALCITE-1375
>             Project: Calcite
>          Issue Type: Bug
>            Reporter: hongbin ma
>            Assignee: Julian Hyde
>
> Hi experts,
> I have some confusing observations:
> || query || result ||
> | select QUARTER(DATE'2010-01-02') from EMPS; | OK |
> | select {fn QUARTER(DATE'2010-01-02')} from EMPS; | OK|
> |select {fn CONCAT('a','b')} from EMPS; | OK|
> |select CONCAT('a','b') from EMPS; |FAIL|
> The error message is : 
> Error: Error while executing SQL "select CONCAT('a','b') from EMPS": From line 1, column 8 to line 1, column 22: No match found for function signature CONCAT(<CHARACTER>, <CHARACTER>) (state=,code=0)
> why is CONCAT without function escape failing? is there any reasons behind this? 
> There're many JDBC escape functions in https://calcite.apache.org/docs/reference.html, is there a way to tell which can run without the "{fn" prefix? 
> I'm using calcite 1.8.0's sqlline under example/csv 
> thanks!



--
This message was sent by Atlassian JIRA
(v6.3.4#6332)