Bug 1296543 - Redshift - conversion to time does not work
Redshift - conversion to time does not work
Status: CLOSED CURRENTRELEASE
Product: JBoss Data Virtualization 6
Classification: JBoss
Component: Teiid (Show other bugs)
6.3.0
Unspecified Unspecified
unspecified Severity unspecified
: ER1
: 6.3.0
Assigned To: Van Halbert
Juraj Duráni
:
Depends On:
Blocks:
  Show dependency treegraph
 
Reported: 2016-01-07 08:33 EST by Juraj Duráni
Modified: 2016-08-24 07:46 EDT (History)
6 users (show)

See Also:
Fixed In Version:
Doc Type: Bug Fix
Doc Text:
Story Points: ---
Clone Of:
Environment:
Last Closed: 2016-08-24 07:46:35 EDT
Type: Bug
Regression: ---
Mount Type: ---
Documentation: ---
CRM:
Verified Versions:
Category: ---
oVirt Team: ---
RHEL 7.3 requirements from Atomic Host:
Cloudforms Team: ---


Attachments (Terms of Use)


External Trackers
Tracker ID Priority Status Summary Last Updated
JBoss Issue Tracker TEIID-3885 Major Resolved Redshift - conversion to time does not work 2016-05-09 05:44 EDT

  None (edit)
Description Juraj Duráni 2016-01-07 08:33:06 EST
Description of problem:
Conversion to TIME data type does not work for Redshift (using postgresql translator).
E.g.: SELECT CONVERT(timestampvalue, time) FROM Source.SmallA

14:08:36,300 DEBUG [org.teiid.CONNECTOR] (Worker12_QueryProcessorQueue158) Source-specific command: SELECT cast(date_trunc('second', g_0.timevalue) AS time) FROM smalla AS g_0

14:08:36,481 WARN  [org.teiid.CONNECTOR] (Worker12_QueryProcessorQueue158) Connector worker process failed for atomic-request=ajCOQrzOcEmF.3.0.36: org.teiid.translator.jdbc.JDBCExecutionException: 0 TEIID11008:TEIID11004 Error executing statement(s): [Prepared Values: [] SQL: SELECT cast(date_trunc('second', g_0.timevalue) AS time) FROM smalla AS g_0]
	at org.teiid.translator.jdbc.JDBCQueryExecution.execute(JDBCQueryExecution.java:131) [translator-jdbc-8.12.3.redhat-1.jar:8.12.3.redhat-1]
	at org.teiid.dqp.internal.datamgr.ConnectorWorkItem.execute(ConnectorWorkItem.java:349)
	at sun.reflect.GeneratedMethodAccessor189.invoke(Unknown Source) [:1.8.0_40]
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) [rt.jar:1.8.0_40]
	at java.lang.reflect.Method.invoke(Method.java:497) [rt.jar:1.8.0_40]
	at org.teiid.dqp.internal.datamgr.ConnectorManager$1.invoke(ConnectorManager.java:211)
	at com.sun.proxy.$Proxy124.execute(Unknown Source)
	at org.teiid.dqp.internal.process.DataTierTupleSource.getResults(DataTierTupleSource.java:306)
	at org.teiid.dqp.internal.process.DataTierTupleSource$1.call(DataTierTupleSource.java:112)
	at org.teiid.dqp.internal.process.DataTierTupleSource$1.call(DataTierTupleSource.java:108)
	at java.util.concurrent.FutureTask.run(FutureTask.java:266) [rt.jar:1.8.0_40]
	at org.teiid.dqp.internal.process.FutureWork.run(FutureWork.java:65)
	at org.teiid.dqp.internal.process.DQPWorkContext.runInContext(DQPWorkContext.java:276)
	at org.teiid.dqp.internal.process.ThreadReuseExecutor$RunnableWrapper.run(ThreadReuseExecutor.java:119)
	at org.teiid.dqp.internal.process.ThreadReuseExecutor$3.run(ThreadReuseExecutor.java:210)
	at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142) [rt.jar:1.8.0_40]
	at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617) [rt.jar:1.8.0_40]
	at java.lang.Thread.run(Thread.java:745) [rt.jar:1.8.0_40]
Caused by: org.postgresql.util.PSQLException: ERROR: Specified types or functions (one per INFO message) not supported on Redshift tables.
	at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2157)
	at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1886)
	at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:255)
	at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:555)
	at org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:417)
	at org.postgresql.jdbc2.AbstractJdbc2Statement.executeQuery(AbstractJdbc2Statement.java:302)
	at org.jboss.jca.adapters.jdbc.WrappedPreparedStatement.executeQuery(WrappedPreparedStatement.java:462)
	at org.teiid.translator.jdbc.JDBCQueryExecution.execute(JDBCQueryExecution.java:123) [translator-jdbc-8.12.3.redhat-1.jar:8.12.3.redhat-1]
	... 17 more



Steps to Reproduce:
VDB:
<vdb name="test" version="1">
    <model name="Source" type="PHYSICAL">
        <source name="rs" connection-jndi-name="java:/Redshift" translator-name="postgresql"/>
        <metadata type="DDL">
            <![CDATA[create foreign table SmallA (
                intkey integer OPTIONS(NAMEINSOURCE 'intkey', NATIVE_TYPE 'int4'),
                timevalue timestamp OPTIONS(NAMEINSOURCE 'timevalue', NATIVE_TYPE 'timestamp')) OPTIONS(NAMEINSOURCE 'smalla');
            ]]>
        </metadata>
    </model>
</vdb>

Actual results:


Expected results:


Additional info:
Comment 1 JBoss JIRA Server 2016-01-08 08:48:18 EST
Steven Hawkins <shawkins@redhat.com> updated the status of jira TEIID-3885 to Resolved
Comment 2 Van Halbert 2016-01-08 08:54:18 EST
Note:  a new redshift translator has been created to account for the different capabilities than the postgresql translator.
Comment 3 Juraj Duráni 2016-05-09 05:48:38 EDT
Verified with new redshift translator.

Note You need to log in before you can comment on or make changes to this bug.