GDC-1485: If a workflow includes a Join In-DB tool it throws an error

[ntobon]

Issue

When Alteryx creates a query determined by an In-DB workflow, if the workflow includes a Join In-DB tool, Alteryx aliases the tables to be joined as "Left" and "Right".  These are reserved words for some databases. The workflow throws an error indicating that query could not be parsed correctly.  The issue didn't happen in previous versions. 

Error is generated for any database that considers “Left” and "Right" reserved Keywords and the text of error could be different. Examples:

- Error for Teradata database:

Data Stream Out (28) Error SQLPrepare: [Teradata][ODBC Teradata Driver][Teradata Database](-3707)Syntax error, expected something like '(' between the 'Left' keyword and '.'.

- Error for GreenPlum database: 

Error: Data Stream Out (7): Error opening "SELECT Left.meme_ck, Left.grgr_ck, Left.sbsb_ck, Left.meme_sfx, Left.meme_rel, Left.meme_id_name, Left.meme_last_name, ... Right.meme_ck AS R_meme_ck, Right.cspd_cat, Right.mepe_eff_dt, Right.mepe_term_dt, Right.mepe_create_dtm, ... FROM (select * from gpgen_cr_ai.cmc_meme_member_full limit 20000) AS Left INNER JOIN (select * from gpgen_cr.cmc_mepe_prcs_elig limit 100000) AS Right ON Left.meme_ck = Right.meme_ck": No Columns Returned.

 

Environment Details

• Alteryx Designer
  • Version(s) 2021.1, 2021.2, 2021.3
• Any database that considers “Left” and "Right" reserved Keywords

Cause

Defect GDC-1485
Status: Open.

 

 

Resolution

Defect GDC-1485 is target for resolution in future release.

Workaround

Edit Data Connections and change Table/FieldName SQL Style from "None" to  “Quoted”

image.png

Additional Resources

Wikipedia (https://en.wikipedia.org/wiki/SQL_reserved_words) lists the following databases: SQL-2016, DB2, Mimer, MySQL, PostgreSQL, SQL Server, Teradata