5 Replies Latest reply on Jun 8, 2015 9:37 AM by Steven Hawkins

    Empty result set when query on VDB

    fritz wijaya Newbie

      I have multiple schema on my vdb (say: MyVDB). This vdb composed on multiple schema from different database source. Different rdbms too. DB2 and PostgreSQL.

      From table on each of this database source, I created schema to represent the table from each of system and then join them to new view schema.

       

      Query is successful executed from and return expected model that I design on the view schema, but the problem is the result is empty.

      This is occured when I executed the query without where condition (select * from myviewschema.tablename). But, if I add where condition to the query (where id = xxx), I get the expected records.

      I've check on the each schema from each of the rdbms (DB2 and PostgreSQL), the record is there and exists. Select all on this table get me the records too.

       

      I found it strange. There is no error log on the console or the server log. Just warn about terminated session. My teiid version is 8.8 running on EAP 6.1

       

      Please help me. Thanks in advance.

        • 1. Re: Empty result set when query on VDB
          Steven Hawkins Master

          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
            fritz wijaya Newbie

            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:

            Empty.PNG

             

            Not empty:

            NotEmpty.PNG

             

            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
              Steven Hawkins Master

              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
                fritz wijaya Newbie

                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
                  Steven Hawkins Master

                  > 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.