Join subquery field - Performance issue

[mtawk]

I have the following query on H2 database that has a join linking a table through subquery field:

select "Customer"."CustomerID"   as "Customer_CustomerID","Customer"."LastName" as "Customer_LastName","Customer"."BirthDate" as "Customer_BirthDate"

,(select "StreamCards_sub_f"."Merchant_MerchantID"  as "StreamCards_Merchant_Merchan" from  "h2dbimplify_data_cebankModel_fR"."implify_data_cebank"."StreamCards" "StreamCards_sub_f" where  ("Customer"."CustomerID" = "StreamCards_sub_f"."Customer_CustomerID")  LIMIT 0 , 1) as "SubQuery StreamCards"

,"Merchant"."Merchant" as "Merchant_Merchant","Merchant"."Category_CategoryID"   as "Merchant_Category_CategoryID","Category"."Category" as "Category_Category"

from  "h2dbimplify_data_cebankModel_fR"."implify_data_cebank"."Customer" "Customer"

LEFT JOIN "h2dbimplify_data_cebankModel_fR"."implify_data_cebank"."Merchant" "Merchant" ON   ( ("Merchant"."MerchantID"   =  (select "StreamCards_sub_f"."Merchant_MerchantID"  as "StreamCards_Merchant_Merchan" from  "h2dbimplify_data_cebankModel_fR"."implify_data_cebank"."StreamCards" "StreamCards_sub_f" where  ("Customer"."CustomerID" = "StreamCards_sub_f"."Customer_CustomerID")  LIMIT 0 , 1)) ) \n

LEFT JOIN "h2dbimplify_data_cebankModel_fR"."implify_data_cebank"."Category" "Category" ON "Merchant"."Category_CategoryID" = "Category"."CategoryID"

where  ( (DAYOFMONTH("Customer"."BirthDate") =  DAYOFMONTH( CURDATE() )) AND (MONTH("Customer"."BirthDate") =  MONTH( CURDATE() )) ) 

LIMIT 0 , 10

the below jdbc query executed by teiid is taking too much time (if the parenthesis grouping the from table with the first join is removed, the query executes fast and returns the right results):

SELECT g_0."CustomerID" AS c_0, g_0."LastName" AS c_1, g_0."BirthDate" AS c_2, (SELECT g_4."Merchant_MerchantID" AS c_0 FROM "implify_data_cebank"."StreamCards" AS g_4 WHERE g_4."Customer_CustomerID" = g_0."CustomerID" LIMIT 1) AS c_3, g_1."Merchant" AS c_4, g_1."Category_CategoryID" AS c_5, g_3."Category" AS c_6 FROM ("implify_data_cebank"."Customer" AS g_0 LEFT OUTER JOIN "implify_data_cebank"."Merchant" AS g_1 ON g_1."MerchantID" = (SELECT g_2."Merchant_MerchantID" AS c_0 FROM "implify_data_cebank"."StreamCards" AS g_2 WHERE g_2."Customer_CustomerID" = g_0."CustomerID" LIMIT 1)) LEFT OUTER JOIN "implify_data_cebank"."Category" AS g_3 ON g_1."Category_CategoryID" = g_3."CategoryID" WHERE day_of_month(g_0."BirthDate") = ? AND month(g_0."BirthDate") = ? LIMIT 10

Here is the execution plan:

<?xml version='1.0' encoding='UTF-8'?><node name="AccessNode"><property name="Relational Node ID"><value>2</value></property><property name="Output Columns"><value>Customer_CustomerID (integer)</value><value>Customer_LastName (string)</value><value>Customer_BirthDate (timestamp)</value><value>SubQuery StreamCards (integer)</value><value>Merchant_Merchant (string)</value><value>Merchant_Category_CategoryID (integer)</value><value>Category_Category (string)</value></property><property name="Statistics"><value>Node Output Rows: 3</value><value>Node Next Batch Process Time: 0</value><value>Node Cumulative Next Batch Process Time: 0</value><value>Node Cumulative Process Time: 258613</value><value>Node Next Batch Calls: 2</value><value>Node Blocks: 1</value></property><property name="Cost Estimates"><value>Estimated Node Cardinality: 10.0</value></property><property name="Query"><value>SELECT g_0.CustomerID AS c_0, g_0.LastName AS c_1, g_0.BirthDate AS c_2, (SELECT g_4.Merchant_MerchantID AS c_0 FROM h2dbimplify_data_cebankModel_fR.implify_data_cebank.StreamCards AS g_4 WHERE g_4.Customer_CustomerID = g_0.CustomerID LIMIT 1) AS c_3, g_1.Merchant AS c_4, g_1.Category_CategoryID AS c_5, g_3.Category AS c_6 FROM (h2dbimplify_data_cebankModel_fR.implify_data_cebank.Customer AS g_0 LEFT OUTER JOIN h2dbimplify_data_cebankModel_fR.implify_data_cebank.Merchant AS g_1 ON g_1.MerchantID = (SELECT g_2.Merchant_MerchantID AS c_0 FROM h2dbimplify_data_cebankModel_fR.implify_data_cebank.StreamCards AS g_2 WHERE g_2.Customer_CustomerID = g_0.CustomerID LIMIT 1)) LEFT OUTER JOIN h2dbimplify_data_cebankModel_fR.implify_data_cebank.Category AS g_3 ON g_1.Category_CategoryID = g_3.CategoryID WHERE (DAYOFMONTH(g_0.BirthDate) = DAYOFMONTH(CURDATE())) AND (MONTH(g_0.BirthDate) = MONTH(CURDATE())) LIMIT 10</value></property><property name="Model Name"><value>h2dbimplify_data_cebankModel_fR</value></property><property name="Data Bytes Sent"><value>0</value></property><property name="Planning Time"><value>91</value></property></node>

[shawkins]

Is it known behavior for H2 that nesting parens change the optimizer behavior and/or is there an optimizer setting for this?

In general this is related to [TEIID-3549] Join conditions with OR generate a wrong JDBC query - JBoss Issue Tracker which is adding nesting parens on both the left and right sides.  It looks like the actual issue that H2 is only when the join is bushy and then only parens are required on the right side, so that fix could be narrowed.

[mtawk]

The actual execution takes around 60 seconds, while if the parenthesis are removed the execution takes only 3 seconds

[shawkins]

I believe you that it is taking longer, but I can't find any reference to why that is influencing the H2 optimizer.

Can you log another issue to narrow the fix from TEIID-3549?

[shawkins]

Logged as [TEIID-4257] Nesting join parens cause H2 performance issue - JBoss Issue Tracker