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
Steven Hawkins <shawkins> updated the status of jira TEIID-3888 to Resolved
Note: a new redshift translator has been created to account for the different capabilities than the postgresql translator.
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)
Juraj Duráni <jdurani> updated the status of jira TEIID-3888 to Reopened
There is a new issue for LOCATE function. Adding as a new external tracker.
Steven Hawkins <shawkins> updated the status of jira TEIID-4199 to Resolved
Steven Hawkins <shawkins> updated the status of jira TEIID-4199 to Closed
Steven Hawkins <shawkins> updated the status of jira TEIID-3888 to Closed