-
1. Re: Empty result set when query on VDB
shawkins Jun 3, 2015 8:11 AM (in response to fritzwijaya)Can you provide the query plan for the query with and without the condition - preferably obtained after execution as that will show output rows by each node?
-
2. Re: Empty result set when query on VDB
fritzwijaya Jun 5, 2015 7:38 AM (in response to shawkins)Hi Steven,
Thanks for replying.
How to generate the query execution plan?
I found the workaround but I'm not sure if it is the solution. Somehow after create new view schema based on the physical one, the result intermittent return and somehow it return empty. Still found it strange behavior.
For more detail about my working dev environment is: I have 2 table from 2 rdbms, says table A and B. Table A consist of hundreds of thousand records. Table B is tenth of thousand records. I created 2 physical schema based on this table. Then create the unified view schema Table C by Table A left joint to Table B. The workaround I found is, I added extra view schema that materialized set true before joining them to Table C
Empty result:
Not empty:
Log from teiid command:
-- Command log that empty result
04:31:50,729 DEBUG [org.teiid.COMMAND_LOG] (New I/O worker #1) START USER COMMAND: startTime=2015-06-05 04:31:50.728 requestID=epImwe1+/mYW.16 txID=null sessionID=epImwe1+/mYW applicationName=JDBC principal=gaby@teiid-security vdbName=SOLRview vdbVersion=1 sql=SELECT
C.CATENTRY_ID, C.SKU, M.PRODUCT_CODE, M.PRODUCT_NAME, M.PRODUCT_BRAND, M.VIDEO_URL, C.PRODUCT_MARK_FOR_DELETE, CAST(M.SHORT_DESCRIPTION AS STRING) AS SHORT_DESCRIPTION, CAST(M.LONG_DESCRIPTION AS STRING) AS LONG_DESCRIPTION, M.FULLIMAGE_PATH, C.THUMBNAIL, C.KEYWORD, C.CREATED_DATE, M.UNIQUE_SELLING_POINT AS UNIQUE_SELLING_POINT, C.BUYABLE, C.PUBLISHED, C.SEQUENCE, C.CATDESC1, C.FIELD5, C.MERCHANT_ID, C.PRODUCT_TYPE, C.MERCHANT_STORE_NAME, C.MERCHANT_LOGO, C.MERCHANT_MARK_FOR_DELETE, C.MERCHANT_TYPE, C.MERCHANT_STORY, C.MERCHANT_METADESCRIPTION, C.CAT1, C.KEYWORD1, C.CATDESC2, C.CAT2, C.KEYWORD2, C.CATDESC3, C.CAT3, C.KEYWORD3, C.CATDESC4, C.CAT4, C.KEYWORD4
FROM
SOLRview.view_PRODUCTBEAN_WCS AS C INNER JOIN SOLRview.view_PRODUCTBEAN_MD AS M ON C.PRODUCT_CODE = M.PRODUCT_CODE
04:31:50,825 DEBUG [org.teiid.COMMAND_LOG] (Worker10_QueryProcessorQueue1288) START DATA SRC COMMAND: startTime=2015-06-05 04:31:50.825 requestID=epImwe1+/mYW.16 sourceCommandID=3 executionID=12 txID=null modelName=WCS_PRODUCT translatorName=db2 sessionID=epImwe1+/mYW principal=gaby@teiid-security sql=SELECT g_1.PRODUCTCODE AS c_0, g_0.CATENTRY_ID AS c_1, g_0.PARTNUMBER AS c_2, g_0.MARKFORDELETE AS c_3, g_3.THUMBNAIL AS c_4, g_3.KEYWORD AS c_5, g_1.CREATEDDATE AS c_6, g_0.BUYABLE AS c_7, g_3.PUBLISHED AS c_8, g_4.SEQUENCE AS c_9, g_5.CATDESC1 AS c_10, g_0.FIELD5 AS c_11, g_0.FIELD4 AS c_12, g_1.PRODUCTTYPE AS c_13, g_2.MERCHANTSTORENAME AS c_14, g_2.MERCHANTLOGO AS c_15, g_2.MARKFORDELETE AS c_16, g_2.TYPEOFMERCHANT AS c_17, g_2.MERCHANTSTORY AS c_18, g_2.METADESCRIPTION AS c_19, g_5.CAT1 AS c_20, g_5.KEYWORD1 AS c_21, g_6.CATDESC1 AS c_22, g_6.CATDESC2 AS c_23, g_6.CAT1 AS c_24, g_6.CAT2 AS c_25, g_6.KEYWORD1 AS c_26, g_6.KEYWORD2 AS c_27, g_7.CATDESC1 AS c_28, g_7.CATDESC2 AS c_29, g_7.CATDESC3 AS c_30, g_7.CAT1 AS c_31, g_7.CAT2 AS c_32, g_7.CAT3 AS c_33, g_7.KEYWORD1 AS c_34, g_7.KEYWORD2 AS c_35, g_7.KEYWORD3 AS c_36, g_8.CATDESC1 AS c_37, g_8.CATDESC2 AS c_38, g_8.CATDESC3 AS c_39, g_8.CATDESC4 AS c_40, g_8.CAT1 AS c_41, g_8.CAT2 AS c_42, g_8.CAT3 AS c_43, g_8.CAT4 AS c_44, g_8.KEYWORD1 AS c_45, g_8.KEYWORD2 AS c_46, g_8.KEYWORD3 AS c_47, g_8.KEYWORD4 AS c_48 FROM (((((((WCS_PRODUCT.CATENTRY AS g_0 INNER JOIN WCS_PRODUCT.XCATENTRYEXT AS g_1 ON g_0.CATENTRY_ID = g_1.CATENTRY_ID) LEFT OUTER JOIN WCS_MERCHANT.XMERCHANT_PROFILE AS g_2 ON g_0.FIELD4 = g_2.MERCHANTID) INNER JOIN WCS_PRODUCT.CATENTDESC AS g_3 ON g_0.CATENTRY_ID = g_3.CATENTRY_ID) LEFT OUTER JOIN WCS_PRODUCT_CATEGORY_REL.CATGPENREL AS g_4 ON g_4.CATENTRY_ID = g_0.CATENTRY_ID AND g_4.CATALOG_ID = 12051) LEFT OUTER JOIN WCS_CATEGORY.VWCATGROUP_HIERARCHY_ALL_CATALOG AS g_5 ON g_5.CAT1 = g_4.CATGROUP_ID AND g_4.CATALOG_ID = g_5.CATALOG_ID) LEFT OUTER JOIN WCS_CATEGORY.VWCATGROUP_HIERARCHY_ALL_CATALOG AS g_6 ON g_6.CAT2 = g_4.CATGROUP_ID AND g_4.CATALOG_ID = g_6.CATALOG_ID) LEFT OUTER JOIN WCS_CATEGORY.VWCATGROUP_HIERARCHY_ALL_CATALOG AS g_7 ON g_7.CAT3 = g_4.CATGROUP_ID AND g_4.CATALOG_ID = g_7.CATALOG_ID) LEFT OUTER JOIN WCS_CATEGORY.VWCATGROUP_HIERARCHY_ALL_CATALOG AS g_8 ON g_8.CAT4 = g_4.CATGROUP_ID AND g_4.CATALOG_ID = g_8.CATALOG_ID WHERE (g_0.CATENTTYPE_ID = 'ProductBean') AND (g_3.LANGUAGE_ID = -1) ORDER BY c_0
04:31:50,828 DEBUG [org.teiid.COMMAND_LOG] (Worker11_QueryProcessorQueue1289) START DATA SRC COMMAND: startTime=2015-06-05 04:31:50.828 requestID=epImwe1+/mYW.16 sourceCommandID=4 executionID=13 txID=null modelName=MD_PRODUCT translatorName=postgresql sessionID=epImwe1+/mYW principal=gaby@teiid-security sql=SELECT g_0.product_code AS c_0, g_0.name AS c_1, g_0.brand AS c_2, g_0.url AS c_3, g_0.description AS c_4, g_0.long_description AS c_5, g_1.location_path AS c_6, g_0.unique_selling_point AS c_7 FROM MD_PRODUCT.pcc_product AS g_0 LEFT OUTER JOIN MD_PRODUCT.pcc_product_images AS g_1 ON g_1.product_id = g_0.id ORDER BY c_0
04:31:50,869 DEBUG [org.teiid.COMMAND_LOG] (Worker9_QueryProcessorQueue1290) END SRC COMMAND: endTime=2015-06-05 04:31:50.869 requestID=epImwe1+/mYW.16 sourceCommandID=4 executionID=13 txID=null modelName=MD_PRODUCT translatorName=postgresql sessionID=epImwe1+/mYW principal=gaby@teiid-security finalRowCount=246
04:32:01,215 DEBUG [org.teiid.COMMAND_LOG] (Worker9_QueryProcessorQueue1897) END SRC COMMAND: endTime=2015-06-05 04:32:01.215 requestID=epImwe1+/mYW.16 sourceCommandID=3 executionID=12 txID=null modelName=WCS_PRODUCT translatorName=db2 sessionID=epImwe1+/mYW principal=gaby@teiid-security finalRowCount=77619
04:32:01,225 DEBUG [org.teiid.COMMAND_LOG] (Worker9_QueryProcessorQueue1898) END USER COMMAND: endTime=2015-06-05 04:32:01.225 requestID=epImwe1+/mYW.16 txID=null sessionID=epImwe1+/mYW principal=gaby@teiid-security vdbName=SOLRview vdbVersion=1 finalRowCount=0
-- Command log that returning records
04:32:50,309 DEBUG [org.teiid.COMMAND_LOG] (New I/O worker #1) START USER COMMAND: startTime=2015-06-05 04:32:50.309 requestID=epImwe1+/mYW.17 txID=null sessionID=epImwe1+/mYW applicationName=JDBC principal=gaby@teiid-security vdbName=SOLRview vdbVersion=1 sql=SELECT
C.CATENTRY_ID, C.SKU, M.PRODUCT_CODE, M.PRODUCT_NAME, M.PRODUCT_BRAND, M.VIDEO_URL, C.PRODUCT_MARK_FOR_DELETE, CAST(M.SHORT_DESCRIPTION AS STRING) AS SHORT_DESCRIPTION, CAST(M.LONG_DESCRIPTION AS STRING) AS LONG_DESCRIPTION, M.FULLIMAGE_PATH, C.THUMBNAIL, C.KEYWORD, C.CREATED_DATE, M.UNIQUE_SELLING_POINT AS UNIQUE_SELLING_POINT, C.BUYABLE, C.PUBLISHED, C.SEQUENCE, C.CATDESC1, C.FIELD5, C.MERCHANT_ID, C.PRODUCT_TYPE, C.MERCHANT_STORE_NAME, C.MERCHANT_LOGO, C.MERCHANT_MARK_FOR_DELETE, C.MERCHANT_TYPE, C.MERCHANT_STORY, C.MERCHANT_METADESCRIPTION, C.CAT1, C.KEYWORD1, C.CATDESC2, C.CAT2, C.KEYWORD2, C.CATDESC3, C.CAT3, C.KEYWORD3, C.CATDESC4, C.CAT4, C.KEYWORD4
FROM
SOLRview.view_PRODUCTBEAN_WCS AS C INNER JOIN SOLRview.view_PRODUCTBEAN_MD AS M ON C.PRODUCT_CODE = M.PRODUCT_CODE
WHERE
C.PRODUCT_CODE='BLI-12345'
04:32:50,373 DEBUG [org.teiid.COMMAND_LOG] (Worker11_QueryProcessorQueue1900) START DATA SRC COMMAND: startTime=2015-06-05 04:32:50.373 requestID=epImwe1+/mYW.17 sourceCommandID=3 executionID=14 txID=null modelName=WCS_PRODUCT translatorName=db2 sessionID=epImwe1+/mYW principal=gaby@teiid-security sql=SELECT g_0.CATENTRY_ID AS c_0, g_0.PARTNUMBER AS c_1, g_0.MARKFORDELETE AS c_2, g_3.THUMBNAIL AS c_3, g_3.KEYWORD AS c_4, g_1.CREATEDDATE AS c_5, g_0.BUYABLE AS c_6, g_3.PUBLISHED AS c_7, g_4.SEQUENCE AS c_8, g_5.CATDESC1 AS c_9, g_0.FIELD5 AS c_10, g_0.FIELD4 AS c_11, g_1.PRODUCTTYPE AS c_12, g_2.MERCHANTSTORENAME AS c_13, g_2.MERCHANTLOGO AS c_14, g_2.MARKFORDELETE AS c_15, g_2.TYPEOFMERCHANT AS c_16, g_2.MERCHANTSTORY AS c_17, g_2.METADESCRIPTION AS c_18, g_5.CAT1 AS c_19, g_5.KEYWORD1 AS c_20, g_6.CATDESC1 AS c_21, g_6.CATDESC2 AS c_22, g_6.CAT1 AS c_23, g_6.CAT2 AS c_24, g_6.KEYWORD1 AS c_25, g_6.KEYWORD2 AS c_26, g_7.CATDESC1 AS c_27, g_7.CATDESC2 AS c_28, g_7.CATDESC3 AS c_29, g_7.CAT1 AS c_30, g_7.CAT2 AS c_31, g_7.CAT3 AS c_32, g_7.KEYWORD1 AS c_33, g_7.KEYWORD2 AS c_34, g_7.KEYWORD3 AS c_35, g_8.CATDESC1 AS c_36, g_8.CATDESC2 AS c_37, g_8.CATDESC3 AS c_38, g_8.CATDESC4 AS c_39, g_8.CAT1 AS c_40, g_8.CAT2 AS c_41, g_8.CAT3 AS c_42, g_8.CAT4 AS c_43, g_8.KEYWORD1 AS c_44, g_8.KEYWORD2 AS c_45, g_8.KEYWORD3 AS c_46, g_8.KEYWORD4 AS c_47 FROM (((((((WCS_PRODUCT.CATENTRY AS g_0 INNER JOIN WCS_PRODUCT.XCATENTRYEXT AS g_1 ON g_0.CATENTRY_ID = g_1.CATENTRY_ID) LEFT OUTER JOIN WCS_MERCHANT.XMERCHANT_PROFILE AS g_2 ON g_0.FIELD4 = g_2.MERCHANTID) INNER JOIN WCS_PRODUCT.CATENTDESC AS g_3 ON g_0.CATENTRY_ID = g_3.CATENTRY_ID) LEFT OUTER JOIN WCS_PRODUCT_CATEGORY_REL.CATGPENREL AS g_4 ON g_4.CATENTRY_ID = g_0.CATENTRY_ID AND g_4.CATALOG_ID = 12051) LEFT OUTER JOIN WCS_CATEGORY.VWCATGROUP_HIERARCHY_ALL_CATALOG AS g_5 ON g_5.CAT1 = g_4.CATGROUP_ID AND g_4.CATALOG_ID = g_5.CATALOG_ID) LEFT OUTER JOIN WCS_CATEGORY.VWCATGROUP_HIERARCHY_ALL_CATALOG AS g_6 ON g_6.CAT2 = g_4.CATGROUP_ID AND g_4.CATALOG_ID = g_6.CATALOG_ID) LEFT OUTER JOIN WCS_CATEGORY.VWCATGROUP_HIERARCHY_ALL_CATALOG AS g_7 ON g_7.CAT3 = g_4.CATGROUP_ID AND g_4.CATALOG_ID = g_7.CATALOG_ID) LEFT OUTER JOIN WCS_CATEGORY.VWCATGROUP_HIERARCHY_ALL_CATALOG AS g_8 ON g_8.CAT4 = g_4.CATGROUP_ID AND g_4.CATALOG_ID = g_8.CATALOG_ID WHERE (g_0.CATENTTYPE_ID = 'ProductBean') AND (g_1.PRODUCTCODE = 'BLI-12345') AND (g_3.LANGUAGE_ID = -1) LIMIT 1000
04:32:50,376 DEBUG [org.teiid.COMMAND_LOG] (Worker10_QueryProcessorQueue1901) START DATA SRC COMMAND: startTime=2015-06-05 04:32:50.376 requestID=epImwe1+/mYW.17 sourceCommandID=4 executionID=15 txID=null modelName=MD_PRODUCT translatorName=postgresql sessionID=epImwe1+/mYW principal=gaby@teiid-security sql=SELECT g_0.product_code AS c_0, g_0.name AS c_1, g_0.brand AS c_2, g_0.url AS c_3, g_0.description AS c_4, g_0.long_description AS c_5, g_1.location_path AS c_6, g_0.unique_selling_point AS c_7 FROM MD_PRODUCT.pcc_product AS g_0 LEFT OUTER JOIN MD_PRODUCT.pcc_product_images AS g_1 ON g_1.product_id = g_0.id WHERE g_0.product_code = 'BLI-12345' LIMIT 1000
04:32:50,383 DEBUG [org.teiid.COMMAND_LOG] (Worker9_QueryProcessorQueue1899) END SRC COMMAND: endTime=2015-06-05 04:32:50.383 requestID=epImwe1+/mYW.17 sourceCommandID=4 executionID=15 txID=null modelName=MD_PRODUCT translatorName=postgresql sessionID=epImwe1+/mYW principal=gaby@teiid-security finalRowCount=1
04:32:51,045 DEBUG [org.teiid.COMMAND_LOG] (Worker9_QueryProcessorQueue1904) END SRC COMMAND: endTime=2015-06-05 04:32:51.045 requestID=epImwe1+/mYW.17 sourceCommandID=3 executionID=14 txID=null modelName=WCS_PRODUCT translatorName=db2 sessionID=epImwe1+/mYW principal=gaby@teiid-security finalRowCount=1
04:32:51,053 DEBUG [org.teiid.COMMAND_LOG] (Worker9_QueryProcessorQueue1904) END USER COMMAND: endTime=2015-06-05 04:32:51.053 requestID=epImwe1+/mYW.17 txID=null sessionID=epImwe1+/mYW principal=gaby@teiid-security vdbName=SOLRview vdbVersion=1 finalRowCount=1
-
3. Re: Empty result set when query on VDB
shawkins Jun 5, 2015 8:45 AM (in response to fritzwijaya)Are you saying that the query intermittently returns no rows?
The only difference in the source queries appears to be the additional condition and the use of an ordering on the full query. The sorting likely means that the full plan must be performing a sort merge join, while the other plan is not. So the issue is likely further up in the query plan - for example if the ordering returned does not match the UTF ordering expected by Teiid, then the sort merge could exit early. Can you provide the query plans, and confirm the orderings from the source queries in the full plan?
-
4. Re: Empty result set when query on VDB
fritzwijaya Jun 7, 2015 10:26 PM (in response to shawkins)Yes, that happen when I created new view schema on the physical schema with materialized option set to true. Some times the result return as expected, blanks, or error with message duplicate key.
How to disable the ordering? AFAIK, I dont remember set the ordering.
Can you please tell me how to generate the full query plan?
Thanks
-
5. Re: Empty result set when query on VDB
shawkins Jun 8, 2015 9:37 AM (in response to fritzwijaya)> Yes, that happen when I created new view schema on the physical schema with materialized option set to true. Some times the result return as expected, blanks, or error with message duplicate key.
Presumably you are talking about internal materialization correct? Can you isolate what is happening to just a query against that materialized view?
> How to disable the ordering? AFAIK, I dont remember set the ordering.
That is happening because of join planning. You'd first want to determine if it's playing a role before altering the plan.
> Can you please tell me how to generate the full query plan?
See Query Plans - Teiid 8.11 (draft) - Project Documentation Editor and SET Statement - Teiid 8.11 (draft) - Project Documentation Editor or you should be able to get them from Teiid Designer.