[teiid-issues] [JBoss JIRA] Commented: (TEIID-934) MySql Queries - SQL syntax error on pushdown queries when using functions like LPAD, REPLACE, etc

[Steven Hawkins (JIRA)]

    [ https://jira.jboss.org/jira/browse/TEIID-934?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12508422#action_12508422 ] 

Steven Hawkins commented on TEIID-934:
--------------------------------------

It turns out that the other functions do not have a problem.  It's our use of boolean as the target type in cast.  I've updated the numeric type mapping for boolean to be signed instead.  However our testing schema is really odd having integer and boolean columns represented by decimal.

> MySql Queries - SQL syntax error on pushdown queries when using functions like LPAD, REPLACE, etc
> -------------------------------------------------------------------------------------------------
>
>                 Key: TEIID-934
>                 URL: https://jira.jboss.org/jira/browse/TEIID-934
>             Project: Teiid
>          Issue Type: Bug
>          Components: Query Engine
>    Affects Versions: 7.0
>            Reporter: Warren Gibson
>            Assignee: Steven Hawkins
>             Fix For: 7.0
>
>
> Several CTC queries are failing in MySql when they are pushed down to the source.   See query example and resulting SQL error below:
> SELECT INTKEY, LPAD(TIMESTAMPVALUE, 18) FROM BQT1.SmallA ORDER BY INTKEY
> Error Code:1064 Message:Error Code:1064 Message:Error occurred on connector MySQL_Push<5> - Error Code:1064 Message:You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ') AS c_1 FROM smalla AS g_0 ORDER BY c_0' at line 1 Executing statement: [SQL: SELECT cast(g_0.INTKEY AS signed) AS c_0, LPAD(date_format(g_0.TIMESTAMPVALUE, '%Y-%m-%d %H:%i:%S.%f'), 18) AS c_1 FROM smalla AS g_0 ORDER BY c_0]
> The following fiunctions are producing "error in your SQL Syntax" errors:
> LPAD
> RPAD
> ASCII
> UCASE
> LCASE
> LOWER
> UPPER
> LEFT
> RIGHT
> LENGTH
> LOCATE
> LTRIM
> RTRIM
> REPEAT
> REPLACE

-- 
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators: https://jira.jboss.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira