Bug 1296599 - Redshift - there is not function SUBSTR (RS uses SUBSTRING)
Redshift - there is not function SUBSTR (RS uses SUBSTRING)
Status: CLOSED CURRENTRELEASE
Product: JBoss Data Virtualization 6
Classification: JBoss
Component: Teiid (Show other bugs)
6.3.0
Unspecified Unspecified
unspecified Severity unspecified
: ER4
: 6.3.0
Assigned To: Van Halbert
Juraj Duráni
:
Depends On:
Blocks:
  Show dependency treegraph
 
Reported: 2016-01-07 10:59 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:
driver - PostgreSQL 9.2 translator - postgresql
Last Closed: 2016-08-24 07:46:33 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-3888 Major Closed Redshift - there is not function SUBSTR (RS uses SUBSTRING) 2016-06-09 09:33 EDT
JBoss Issue Tracker TEIID-4199 Major Closed Redshift fails locate pushdown 2016-06-09 09:32 EDT

  None (edit)
Description Juraj Duráni 2016-01-07 10:59:35 EST
Description of problem:
Postgresql translator uses SUBSTR function to get substring of string (e.g. function LEFT(x,y)), but Redshift does not support function SUBSTR. It support SUBSTRING function instead.

Steps to Reproduce:
Query: SELECT INTKEY, LEFT(INTKEY, 2) FROM BQT1.SmallA ORDER BY INTKEY
Source-specific query: SELECT g_0.intkey AS c_0, SUBSTR(cast(g_0.intnum AS varchar(4000)), 1, 1) AS c_1 FROM smalla AS g_0 ORDER BY c_0
Comment 1 JBoss JIRA Server 2016-01-08 08:48:18 EST
Steven Hawkins <shawkins@redhat.com> updated the status of jira TEIID-3888 to Resolved
Comment 2 Van Halbert 2016-01-08 08:53:09 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:43:29 EDT
This still does not work for function LOCATION(x,y,z).

Query: SELECT INTKEY, STRINGKEY FROM Source.SmallA WHERE LOCATE('1', STRINGKEY, 2) IN (1, 2)

Source-specific command: SELECT g_0.intkey, g_0.stringkey FROM smalla AS g_0 WHERE (position('1' in substr(g_0.stringkey, 2)) + 1) IN (1, 2)

Root exception message: Caused by: org.postgresql.util.PSQLException: ERROR: function SUBSTR does not exist (Hint: use SUBSTRING instead)
Comment 4 JBoss JIRA Server 2016-05-09 05:43:53 EDT
Juraj Duráni <jdurani@redhat.com> updated the status of jira TEIID-3888 to Reopened
Comment 5 JBoss JIRA Server 2016-05-10 08:42:51 EDT
Steven Hawkins <shawkins@redhat.com> updated the status of jira TEIID-3888 to Resolved
Comment 6 Juraj Duráni 2016-05-10 08:46:05 EDT
There is a new issue for LOCATE function. Adding as a new external tracker.
Comment 7 JBoss JIRA Server 2016-05-10 14:10:25 EDT
Steven Hawkins <shawkins@redhat.com> updated the status of jira TEIID-4199 to Resolved
Comment 8 JBoss JIRA Server 2016-06-09 09:32:53 EDT
Steven Hawkins <shawkins@redhat.com> updated the status of jira TEIID-4199 to Closed
Comment 9 JBoss JIRA Server 2016-06-09 09:33:05 EDT
Steven Hawkins <shawkins@redhat.com> updated the status of jira TEIID-3888 to Closed

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