Bug 1296599

Summary:	Redshift - there is not function SUBSTR (RS uses SUBSTRING)
Product:	[JBoss] JBoss Data Virtualization 6	Reporter:	Juraj Duráni <jdurani>
Component:	Teiid	Assignee:	Van Halbert <vhalbert>
Status:	CLOSED CURRENTRELEASE	QA Contact:	Juraj Duráni <jdurani>
Severity:	unspecified	Docs Contact:
Priority:	unspecified
Version:	6.3.0	CC:	aszczucz, blafond, jolee, mbaluch, thauser, vhalbert
Target Milestone:	ER4
Target Release:	6.3.0
Hardware:	Unspecified
OS:	Unspecified
Whiteboard:
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 11:46:33 UTC	Type:	Bug
Regression:	---	Mount Type:	---
Documentation:	---	CRM:
Verified Versions:		Category:	---
oVirt Team:	---	RHEL 7.3 requirements from Atomic Host:
Cloudforms Team:	---	Target Upstream Version:

Description Juraj Duráni 2016-01-07 15:59:35 UTC

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 13:48:18 UTC

Steven Hawkins <shawkins> updated the status of jira TEIID-3888 to Resolved

Comment 2 Van Halbert 2016-01-08 13:53:09 UTC

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 09:43:29 UTC

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 09:43:53 UTC

Juraj Duráni <jdurani> updated the status of jira TEIID-3888 to Reopened

Comment 5 JBoss JIRA Server 2016-05-10 12:42:51 UTC

Steven Hawkins <shawkins> updated the status of jira TEIID-3888 to Resolved

Comment 6 Juraj Duráni 2016-05-10 12:46:05 UTC

There is a new issue for LOCATE function. Adding as a new external tracker.

Comment 7 JBoss JIRA Server 2016-05-10 18:10:25 UTC

Steven Hawkins <shawkins> updated the status of jira TEIID-4199 to Resolved

Comment 8 JBoss JIRA Server 2016-06-09 13:32:53 UTC

Steven Hawkins <shawkins> updated the status of jira TEIID-4199 to Closed

Comment 9 JBoss JIRA Server 2016-06-09 13:33:05 UTC

Steven Hawkins <shawkins> updated the status of jira TEIID-3888 to Closed