[teiid-issues] [JBoss JIRA] (TEIID-3641) ANSI 89 joins not translating to 92 syntax correctly

[Steven Hawkins (JIRA)]

     [ https://issues.jboss.org/browse/TEIID-3641?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]

Steven Hawkins resolved TEIID-3641.
-----------------------------------
    Resolution: Done


Since it doesn't appear that impala supports parens for nesting, updated the logic to write the join tree as left linear.  The engine will typically push down as right linear as that fits our internal evaluation model.

This will need to be revisited to handle bushy joins as the conversion would fail at the translator level.  The engine or the translator would need to convert one side of the join into a subquery instead.

> ANSI 89 joins not translating to 92 syntax correctly
> ----------------------------------------------------
>
>                 Key: TEIID-3641
>                 URL: https://issues.jboss.org/browse/TEIID-3641
>             Project: Teiid
>          Issue Type: Bug
>          Components: JDBC Connector
>    Affects Versions: 8.11.2
>         Environment: Ubuntu Linux Trusty
>            Reporter: Don Krapohl
>            Assignee: Steven Hawkins
>             Fix For: 8.12
>
>
> SQL 89 syntax being translated to SQL 92 has the ON portion of the join in the wrong place when there are multiple tables.
> Example source query:
> select sum(Table3.sales) as c1,
>      Table1.customer_id as c2,
>      Table1.customer_name as c3,
>      Table2.store_id as c4
> from 
>     dim_customer Table1,
>     dim_store Table2,
>      fact_sales Table3
> where (     Table1.customer_id = Table3.customer_id 
>         and Table1.customer_id = 3184 
>         and Table2.store_id = Table3.store_id 
>         and Table2.store_id = 9020 
>         and Table3.customer_id = 3184 
>         and Table3.store_id = 9020 ) 
> group by Table1.customer_id, Table1.customer_name, Table2.store_id
> is translated to
> SELECT SUM(g_2.sales), g_0.customer_id, g_0.customer_name, g_1.store_id
>   FROM dim_customer g_0 
>   JOIN dim_store g_1 
>   JOIN fact_sales g_2
>     ON g_1.store_id = g_2.store_id
>     ON g_0.customer_id = g_2.customer_id 
>  WHERE g_0.customer_id = 3184
>    AND g_1.store_id = 9020
>    AND g_2.customer_id = 3184
>    AND g_2.store_id = 9020 
>  GROUP BY g_0.customer_id, g_0.customer_name, g_1.store_id
>  
>  
>  
>  
>  Notice the two JOIN... JOIN... followed by two ON... ON... statements.  Our database (Impala) doesn't recognize this pattern of join syntax.  I haven't tested to determine if it's just Impala that doesn't recognize this syntax (implying a translator bug) or core query parsing.  Expected query should be something close to:
>  
>  SELECT SUM(g_2.sales), g_0.customer_id, g_0.customer_name, g_1.store_id
>    FROM dim_customer g_0 
>    JOIN fact_sales g_2
>      ON g_0.customer_id = g_2.customer_id 
>    JOIN dim_store g_1 
>      ON g_1.store_id = g_2.store_id
>   WHERE g_0.customer_id = 3184
>     AND g_1.store_id = 9020
>     AND g_2.customer_id = 3184
>     AND g_2.store_id = 9020 
>  GROUP BY g_0.customer_id, g_0.customer_name, g_1.store_id



--
This message was sent by Atlassian JIRA
(v6.3.15#6346)