[teiid-issues] [JBoss JIRA] Updated: (TEIID-1466) MySQL syntax error in cast from date to timestamp
Steven Hawkins (JIRA)
jira-events at lists.jboss.org
Thu Feb 10 10:16:46 EST 2011
[ https://issues.jboss.org/browse/TEIID-1466?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Steven Hawkins updated TEIID-1466:
----------------------------------
Fix Version/s: 7.4
> 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
More information about the teiid-issues
mailing list