[teiid-issues] [JBoss JIRA] (TEIID-3549) Join conditions with OR generate a wrong JDBC query

[Steven Hawkins (JIRA)]

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

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


Updated the translator to use parens for joins.  

> Join conditions with OR generate a wrong JDBC query
> ---------------------------------------------------
>
>                 Key: TEIID-3549
>                 URL: https://issues.jboss.org/browse/TEIID-3549
>             Project: Teiid
>          Issue Type: Bug
>          Components: JDBC Connector
>            Reporter: Mark Tawk
>            Assignee: Steven Hawkins
>             Fix For: 8.12
>
>
> I'm using Teiid 8.10 with h2 translator
> I have the following query containing a join with conditions separated by OR logical operator:
> select  "BilanBanque"."SIGNEDDATA" as "CalculatedField1"
> from  "implify_data"."Evolution_PCy_050615" "Evolution_PCy_050615" 
> LEFT JOIN "implify_data"."Evolution_PCy_050615" "MidCat" ON "Evolution_PCy_050615"."ID" = "MidCat"."PID" AND ( ( ("MidCat"."Level" IN  (1) ) ) ) 
>  LEFT JOIN "implify_data"."Evolution_PCy_050615" "Data" ON   ( ( ("Data"."Level" IN  (2) ) ) AND ( ("Data"."PID" =  "Evolution_PCy_050615"."ID") OR ("Data"."PID" =  "MidCat"."ID") ) ) 
>  LEFT JOIN "implify_data"."Sheet1_haU_010615" "BilanBanque" ON "Data"."ID" = "BilanBanque"."Account"
>  where   ("BilanBanque"."BankName" IN  ('Bank1') ) 
> The corresponding JDBC query executed by Teiid contains 2 times ON after the join:
> SELECT g_3."SIGNEDDATA" FROM "implify_data"."Evolution_PCy_050615" AS g_0 LEFT OUTER JOIN "implify_data"."Evolution_PCy_050615" AS g_1 ON g_0."ID" = g_1."PID" AND g_1."Level" = 1 INNER JOIN "implify_data"."Evolution_PCy_050615" AS g_2 INNER JOIN "implify_data"."Sheet1_haU_010615" AS g_3 ON g_2."ID" = g_3."Account" ON g_2."PID" = g_0."ID" OR g_2."PID" = g_1."ID" WHERE g_2."Level" = 2 AND g_3."BankName" = 'Bank1'
> if I remove the conditions with OR from the join, the query executes with success.
> You find below the execution plan:
> <?xml version='1.0' encoding='UTF-8'?><node name="AccessNode"><property name="Relational Node ID"><value>0</value></property><property name="Output Columns"><value>CalculatedField1 (bigdecimal)</value></property><property name="Statistics"><value>Node Output Rows: 0</value><value>Node Next Batch Process Time: 0</value><value>Node Cumulative Next Batch Process Time: 16</value><value>Node Cumulative Process Time: 0</value><value>Node Next Batch Calls: 1</value><value>Node Blocks: 1</value></property><property name="Cost Estimates"><value>Estimated Node Cardinality: -1.0</value></property><property name="Query"><value>SELECT g_3.SIGNEDDATA FROM (implify_dataModel.implify_data.Evolution_PCy_050615 AS g_0 LEFT OUTER JOIN implify_dataModel.implify_data.Evolution_PCy_050615 AS g_1 ON g_0.ID = g_1.PID AND g_1.Level = 1) INNER JOIN (implify_dataModel.implify_data.Evolution_PCy_050615 AS g_2 INNER JOIN implify_dataModel.implify_data.Sheet1_haU_010615 AS g_3 ON g_2.ID = g_3.Account) ON ((g_2.PID = g_0.ID) OR (g_2.PID = g_1.ID)) WHERE (g_2.Level = 2) AND (g_3.BankName = 'Bank1')</value></property><property name="Model Name"><value>implify_dataModel</value></property><property name="Data Bytes Sent"><value>0</value></property></node>



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