[teiid-issues] [JBoss JIRA] Closed: (TEIID-1466) MySQL syntax error in cast from date to timestamp

[Steven Hawkins (JIRA)]

     [ https://issues.jboss.org/browse/TEIID-1466?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]

Steven Hawkins closed TEIID-1466.
---------------------------------



> MySQL syntax error in cast from date to timestamp
> -------------------------------------------------
>
>                 Key: TEIID-1466
>                 URL: https://issues.jboss.org/browse/TEIID-1466
>             Project: Teiid
>          Issue Type: Bug
>          Components: Query Engine
>    Affects Versions: 7.3
>         Environment: Teiid 7.3 deployed on Jboss AS 5.1.0 GA running on Ubuntu Server 10.04 LTS, MySQL 5.1.51 running on the same machine as JBoss
>            Reporter: Claudio Venturini
>            Assignee: Steven Hawkins
>             Fix For: 7.4
>
>         Attachments: mysql.patch.txt
>
>
> If you have a table in MySQL with a field declared of type DATE, queries which need to cast that field to a timestamp fail.
> E.g. consider a MySQL table T with a field A of type DATE, and create the corresponding source model. If you submit the following two queries, the first works and the second fails.
> {noformat}
> SELECT A FROM T;
> {noformat}
> {noformat}
> SELECT CAST(A AS TIMESTAMP) FROM T;
> {noformat}
> This happens because in the second case, Teiid pushes to MySQL a query like the following:
> {noformat}
> SELECT CAST(g_0.A AS TIMESTAMP) FROM T AS g_0;
> {noformat}
> That syntax is not valid because MySQL doesn't support casting to timestamp. I think that if it would be possible to cast to datetime instead of timestamp, the query will work. Unfortunately, Teiid doesn't support the datetime data type... so there's no solution.
> I've found two workarounds, which prevent Teiid from pushing the cast to the source:
> # The first is to change the data type at the source, that is, by declaring the A field as DATETIME instead of DATE. But this is not alway possible.
> # The other is to declare the field of type dateTime or timestamp in the source model, while leaving the "Native Type" property to DATE

--
This message is automatically generated by JIRA.
For more information on JIRA, see: http://www.atlassian.com/software/jira