[teiid-issues] [JBoss JIRA] Created: (TEIID-1466) MySQL syntax error in cast from date to timestamp
Claudio Venturini (JIRA)
jira-events at lists.jboss.org
Thu Feb 10 09:39:46 EST 2011
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
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