7 Replies Latest reply on Sep 29, 2009 10:48 AM by Steven Hawkins

    Assertion Error with a query

    Ram Yelamanchilli Newbie

      I'm trying the following query which is a bit big. I get Assertion error. I'm posting the query here.

      SELECT snetmap.contact_name, snetmap.contact_id
      FROM (
       SELECT e.customer_id, d.network_id
       FROM CUST_MASTER a,
       NET_MASTER b,
       (SELECT customer_id, network_id from CUST_NET_MAT group by customer_id, network_id) c,
       NET_DETAILS d,
       CUST_DETAILS e
       WHERE a.customer_id_pk = c.customer_id
       AND b.network_id_pk = c.network_id
       AND c.network_id = d.network_id
       AND a.customer_id_pk = e.customer_id
       AND d.ACTIVE = 'Y'
       GROUP BY e.customer_id, d.network_id
       ) snet,
       (
       SELECT a.network_id, a.contact_id, b.contact_name
       FROM CUST_NET_MAT A, CSD_DATAUPDATERS B
       WHERE customer_id in (SELECT e.customer_id
       FROM CUST_MASTER a,
       NET_MASTER b,
       (SELECT customer_id, network_id from CUST_NET_MAT group by customer_id, network_id) c,
       NET_DETAILS d,
       CUST_DETAILS e
       WHERE a.customer_id_pk = c.customer_id
       AND b.network_id_pk = c.network_id
       AND c.network_id = d.network_id
       AND a.customer_id_pk = e.customer_id
       AND d.ACTIVE = 'Y')
       AND network_id in (SELECT d1.network_id
       FROM CUST_MASTER a1,
       NET_MASTER b1,
       (SELECT customer_id, network_id from CUST_NET_MAT group by customer_id, network_id) c1,
       NET_DETAILS d1,
       CUST_DETAILS e1
       WHERE a1.customer_id_pk = c1.customer_id
       AND b1.network_id_pk = c1.network_id
       AND c1.network_id = d1.network_id
       AND a1.customer_id_pk = e1.customer_id
       AND d1.ACTIVE = 'Y')
       AND a.dataupdaters_id = b.DATAUPDATERS_ID_PK
       GROUP BY a.network_id, a.dataupdaters_id, b.DATAUPDATERS_NAME
       ) snetmap,
       CUST_CUST_MAP c
      WHERE snet.network_id = snetmap.network_id
       AND snet.customer_id = c.ced_customer_id
       AND c.strd_customer_id = 60202
      GROUP BY snetmap.contact_name, snetmap.contact_id
      LIMIT 3
      


      I have debugged the server code a bit. It is failing in the LanguageBuildFactory.translate(Criteria criteria) method. criteria is coming as an instance of "DependentSetCriteria", but there is no code in the method to translate the dependentSetCriteria. I hope I'm not confusing.

      Regards

      Ram

        • 1. Re: Assertion Error with a query
          Steven Hawkins Master

          Hi Ram,

          We should be creating a DependentAccessNode or DependentProcedureAccessNode to handle the DependentSetCriteria before the command is sent to the connector. This will need a JIRA issue. Can you post the either the plan xml or the option debug log output with the issue as well?

          Thanks

          • 2. Re: Assertion Error with a query
            Ram Yelamanchilli Newbie

            Steve,

            I used the following code.

            Statement statement = connection.createStatement();
             com.metamatrix.jdbc.api.Statement mmstatement = (com.metamatrix.jdbc.api.Statement)statement;
             mmstatement.setExecutionProperty(
             ExecutionProperties.SQL_OPTION_SHOWPLAN, "true");
            
            
             ResultSet results = null;
             try {
             results = mmstatement.executeQuery(sql);
             } catch (Exception e) {
             System.out.println(e.getMessage());
             System.out.println("**************************************");
            
             PlanNode queryPlan = mmstatement.getPlanDescription();
            
             System.out.println(XMLOutputVisitor.convertToXML(queryPlan));
             System.out.println("**************************************");
            
             }
            


            I'm getting null. How to get the query plan? Sorry for my ignorance.

            Regards

            Ram



            • 3. Re: Assertion Error with a query
              Steven Hawkins Master

              You should use

              mmstatement.setExecutionProperty(ExecutionProperties.PROP_SQL_OPTIONS,
               ExecutionProperties.SQL_OPTION_SHOWPLAN);
              


              or use the Designer SQLExplorer view (plans are shown from the results panel), or add an OPTION SHOWPLAN to the command. I'll update the setExecutionProperty docs to clarify the above.

              Thanks,

              • 4. Re: Assertion Error with a query
                Ram Yelamanchilli Newbie

                Steve,

                Thanks for your help. Here is the query plan.

                <?xml version="1.0" encoding="UTF-8"?>
                <node name="Relational Plan">
                 <properties>
                 <property name="outputCols">
                 <collection>
                 <value>contact_name (string)</value>
                 <value>contact_id (string)</value>
                 </collection>
                 </property>
                 </properties>
                 <node name="Project">
                 <properties>
                 <property name="outputCols">
                 <collection>
                 <value>contact_name (string)</value>
                 <value>contact_id (string)</value>
                 </collection>
                 </property>
                 <property name="nodeCostEstimates">
                 <collection>
                 <value>Estimated Node Cardinality: 3.0</value>
                 </collection>
                 </property>
                 <property name="selectCols">
                 <collection>
                 <value>snetmap.dataupdaters_name AS contact_name</value>
                 <value>snetmap.CCP AS contact_id</value>
                 </collection>
                 </property>
                 <property name="nodeStatistics">
                 <collection>
                 <value>Node Output Rows: 0</value>
                 <value>Node Process Time: 0</value>
                 <value>Node Cumulative Process Time: 0</value>
                 <value>Node Cumulative Next Batch Process Time: 0</value>
                 <value>Node Next Batch Calls: 0</value>
                 <value>Node Blocks: 0</value>
                 </collection>
                 </property>
                 </properties>
                 <node name="Limit">
                 <properties>
                 <property name="outputCols">
                 <collection>
                 <value>DATAUPDATERS_NAME (string)</value>
                 <value>CCP (string)</value>
                 </collection>
                 </property>
                 <property name="nodeCostEstimates">
                 <collection>
                 <value>Estimated Node Cardinality: 3.0</value>
                 </collection>
                 </property>
                 <property name="rowOffset" value="null"/>
                 <property name="rowLimit" value="3"/>
                 <property name="nodeStatistics">
                 <collection>
                 <value>Node Output Rows: 0</value>
                 <value>Node Process Time: 0</value>
                 <value>Node Cumulative Process Time: 0</value>
                 <value>Node Cumulative Next Batch Process Time: 0</value>
                 <value>Node Next Batch Calls: 0</value>
                 <value>Node Blocks: 0</value>
                 </collection>
                 </property>
                 </properties>
                 <node name="Grouping">
                 <properties>
                 <property name="outputCols">
                 <collection>
                 <value>DATAUPDATERS_NAME (string)</value>
                 <value>CCP (string)</value>
                 </collection>
                 </property>
                 <property name="groupCols">
                 <collection>
                 <value>snetmap.dataupdaters_name</value>
                 <value>snetmap.CCP</value>
                 </collection>
                 </property>
                 <property name="removeDups" value="false"/>
                 <property name="nodeCostEstimates">
                 <collection>
                 <value>Estimated Node Cardinality: 1671.2372</value>
                 </collection>
                 </property>
                 <property name="nodeStatistics">
                 <collection>
                 <value>Node Output Rows: 0</value>
                 <value>Node Process Time: 0</value>
                 <value>Node Cumulative Process Time: 0</value>
                 <value>Node Cumulative Next Batch Process Time: 0</value>
                 <value>Node Next Batch Calls: 0</value>
                 <value>Node Blocks: 0</value>
                 </collection>
                 </property>
                 </properties>
                 <node name="Dependent Join">
                 <properties>
                 <property name="outputCols">
                 <collection>
                 <value>DATAUPDATERS_NAME (string)</value>
                 <value>CCP (string)</value>
                 </collection>
                 </property>
                 <property name="joinStrategy" value="MERGE JOIN (ALREADY_SORTED/SORT)"/>
                 <property name="nodeCostEstimates">
                 <collection>
                 <value>Estimated Node Cardinality: 1671.2372</value>
                 <value>Estimated Dependent Join Cost: 147.84045</value>
                 <value>Estimated Join Cost: 152.37813</value>
                 </collection>
                 </property>
                 <property name="joinType" value="INNER JOIN"/>
                 <property name="joinCriteria">
                 <collection>
                 <value>snetmap.network_id=snet.network_id</value>
                 </collection>
                 </property>
                 <property name="nodeStatistics">
                 <collection>
                 <value>Node Output Rows: 0</value>
                 <value>Node Process Time: 0</value>
                 <value>Node Cumulative Process Time: 0</value>
                 <value>Node Cumulative Next Batch Process Time: 0</value>
                 <value>Node Next Batch Calls: 0</value>
                 <value>Node Blocks: 0</value>
                 </collection>
                 </property>
                 </properties>
                 <node name="Access">
                 <properties>
                 <property name="outputCols">
                 <collection>
                 <value>NETWORK_ID (biginteger)</value>
                 <value>DATAUPDATERS_NAME (string)</value>
                 <value>CCP (string)</value>
                 </collection>
                 </property>
                 <property name="sql" value="SELECT DISTINCT v_0.c_0, v_0.c_1, v_0.c_2 FROM (SELECT g_0.NETWORK_ID AS c_0, g_1.DATAUPDATERS_NAME AS c_1, g_0.DATAUPDATERS_ID AS c_2 FROM ATGT.CCP_CED.CSD_CUST_NETW_MATRIX AS g_0, ATGT.CCP_CED.CSD_DATAUPDATERS AS g_1 WHERE (g_0.DATAUPDATERS_ID = g_1.DATAUPDATERS_ID_PK) AND (g_0.CUSTOMER_ID IN (SELECT g_6.customer_id FROM ATGT.CCP_CED.CSD_NETWORK_MASTER AS g_2, ATGT.CCP_CED.CSD_NETWORK_DETAILS AS g_3, ATGT.CCP_CED.CSD_CUST_NETW_MATRIX AS g_4, ATGT.CCP_CED.CSD_CUSTOMER_MASTER AS g_5, ATGT.CCP_CED.CSD_CUSTOMER_DETAILS AS g_6 WHERE (g_5.customer_id_pk = g_6.customer_id) AND (g_5.customer_id_pk = g_4.customer_id) AND (g_4.network_id = g_3.network_id) AND (g_2.network_id_pk = g_4.network_id) AND (g_3.ACTIVE = 'Y'))) AND (g_0.NETWORK_ID IN (SELECT g_8.NETWORK_ID FROM ATGT.CCP_CED.CSD_NETWORK_MASTER AS g_7, ATGT.CCP_CED.CSD_NETWORK_DETAILS AS g_8, ATGT.CCP_CED.CSD_CUST_NETW_MATRIX AS g_9, ATGT.CCP_CED.CSD_CUSTOMER_MASTER AS g_10, ATGT.CCP_CED.CSD_CUSTOMER_DETAILS AS g_11 WHERE (g_10.CUSTOMER_ID_PK = g_11.CUSTOMER_ID) AND (g_10.CUSTOMER_ID_PK = g_9.CUSTOMER_ID) AND (g_9.NETWORK_ID = g_8.NETWORK_ID) AND (g_7.NETWORK_ID_PK = g_9.NETWORK_ID) AND (g_8.ACTIVE = 'Y'))) GROUP BY g_0.NETWORK_ID, g_0.DATAUPDATERS_ID, g_1.DATAUPDATERS_NAME) AS v_0 GROUP BY v_0.c_0, v_0.c_1, v_0.c_2 ORDER BY c_0"/>
                 <property name="modelName" value="ATGT"/>
                 <property name="nodeCostEstimates">
                 <collection>
                 <value>Estimated Node Cardinality: 277.7126</value>
                 <value>Estimated Independent Node Produced Set Size: 138.8563</value>
                 <value>Estimated Dependent Access Cardinality: 277.7126</value>
                 </collection>
                 </property>
                 <property name="nodeStatistics">
                 <collection>
                 <value>Node Output Rows: 0</value>
                 <value>Node Process Time: 0</value>
                 <value>Node Cumulative Process Time: 0</value>
                 <value>Node Cumulative Next Batch Process Time: 0</value>
                 <value>Node Next Batch Calls: 0</value>
                 <value>Node Blocks: 0</value>
                 </collection>
                 </property>
                 </properties>
                 </node>
                 <node name="Dependent Join">
                 <properties>
                 <property name="outputCols">
                 <collection>
                 <value>NETWORK_ID (biginteger)</value>
                 </collection>
                 </property>
                 <property name="joinStrategy" value="MERGE JOIN (ALREADY_SORTED/ALREADY_SORTED)"/>
                 <property name="nodeCostEstimates">
                 <collection>
                 <value>Estimated Node Cardinality: 637.8938</value>
                 <value>Estimated Independent Node Produced Set Size: 318.9469</value>
                 <value>Estimated Dependent Access Cardinality: 555.4252</value>
                 <value>Estimated Dependent Join Cost: 203.56662</value>
                 <value>Estimated Join Cost: 271.2988</value>
                 </collection>
                 </property>
                 <property name="joinType" value="INNER JOIN"/>
                 <property name="joinCriteria">
                 <collection>
                 <value>c__3.CED_CUSTOMER_ID=snet.customer_id</value>
                 </collection>
                 </property>
                 <property name="nodeStatistics">
                 <collection>
                 <value>Node Output Rows: 0</value>
                 <value>Node Process Time: 0</value>
                 <value>Node Cumulative Process Time: 0</value>
                 <value>Node Cumulative Next Batch Process Time: 0</value>
                 <value>Node Next Batch Calls: 0</value>
                 <value>Node Blocks: 0</value>
                 </collection>
                 </property>
                 </properties>
                 <node name="Access">
                 <properties>
                 <property name="outputCols">
                 <collection>
                 <value>CED_CUSTOMER_ID (biginteger)</value>
                 </collection>
                 </property>
                 <property name="sql" value="SELECT DISTINCT g_0.CED_CUSTOMER_ID AS c_0 FROM PSEDEV.NDI_ADMIN.STRD_PKG_CUST_CED_CUST_MAP AS g_0 WHERE g_0.STRD_CUSTOMER_ID = 60202 ORDER BY c_0"/>
                 <property name="modelName" value="PSEDEV"/>
                 <property name="nodeCostEstimates">
                 <collection>
                 <value>Estimated Node Cardinality: 21.666666</value>
                 <value>Estimated Independent Node Produced Set Size: 10.833333</value>
                 <value>Estimated Dependent Access Cardinality: 21.666666</value>
                 </collection>
                 </property>
                 <property name="nodeStatistics">
                 <collection>
                 <value>Node Output Rows: 0</value>
                 <value>Node Process Time: 0</value>
                 <value>Node Cumulative Process Time: 0</value>
                 <value>Node Cumulative Next Batch Process Time: 0</value>
                 <value>Node Next Batch Calls: 0</value>
                 <value>Node Blocks: 0</value>
                 </collection>
                 </property>
                 </properties>
                 </node>
                 <node name="Dependent Access">
                 <properties>
                 <property name="outputCols">
                 <collection>
                 <value>CUSTOMER_ID (biginteger)</value>
                 <value>NETWORK_ID (biginteger)</value>
                 </collection>
                 </property>
                 <property name="sql" value="SELECT DISTINCT v_0.c_0, v_0.c_1 FROM (SELECT g_4.CUSTOMER_ID AS c_0, g_1.NETWORK_ID AS c_1 FROM ATGT.CCP_CED.CSD_NETWORK_MASTER AS g_0, ATGT.CCP_CED.CSD_NETWORK_DETAILS AS g_1, ATGT.CCP_CED.CSD_CUST_NETW_MATRIX AS g_2, ATGT.CCP_CED.CSD_CUSTOMER_MASTER AS g_3, ATGT.CCP_CED.CSD_CUSTOMER_DETAILS AS g_4 WHERE (g_3.CUSTOMER_ID_PK = g_4.CUSTOMER_ID) AND (g_3.CUSTOMER_ID_PK = g_2.CUSTOMER_ID) AND (g_2.NETWORK_ID = g_1.NETWORK_ID) AND (g_0.NETWORK_ID_PK = g_2.NETWORK_ID) AND (g_1.ACTIVE = 'Y') AND (g_1.NETWORK_ID IN (&lt;dependent values&gt;)) AND (g_4.CUSTOMER_ID IN (&lt;dependent values&gt;)) GROUP BY g_4.CUSTOMER_ID, g_1.NETWORK_ID) AS v_0 ORDER BY c_0"/>
                 <property name="modelName" value="ATGT"/>
                 <property name="nodeCostEstimates">
                 <collection>
                 <value>Estimated Node Cardinality: 1207.0914</value>
                 <value>Estimated Independent Node Produced Set Size: 603.5457</value>
                 <value>Estimated Dependent Access Cardinality: 43.333332</value>
                 </collection>
                 </property>
                 <property name="nodeStatistics">
                 <collection>
                 <value>Node Output Rows: 0</value>
                 <value>Node Process Time: 0</value>
                 <value>Node Cumulative Process Time: 0</value>
                 <value>Node Cumulative Next Batch Process Time: 0</value>
                 <value>Node Next Batch Calls: 0</value>
                 <value>Node Blocks: 0</value>
                 </collection>
                 </property>
                 </properties>
                 </node>
                 </node>
                 </node>
                 </node>
                 </node>
                 </node>
                </node>
                


                If you can guide me I wanted to work on this issue.

                Thanks & regards

                Ram

                • 5. Re: Assertion Error with a query
                  Steven Hawkins Master

                  Ram,

                  What is your dependent source ConnectorCapabilities class returning for getMaxInCriteriaSize? It looks like if it's returning a non-positive value, then we're skipping the expansion of the dependent set criteria. This is a bug, since either the QueryRewriter or the LanguageBridgeFactory should perform the expansion in that case. The workaround would be to set the connector binding property override getMaxInCriteriaSize to a positive value.

                  You're welcome to do the fix, I can assist in creating test cases, reviewing, etc.

                  Thanks,

                  • 6. Re: Assertion Error with a query
                    Ram Yelamanchilli Newbie

                    Steve,

                    I overrided the "getMaxInCriteriaSize" property to 100. The problem still exists.

                    Regards

                    Ram

                    • 7. Re: Assertion Error with a query
                      Steven Hawkins Master

                      Hi Ram,

                      I've committed the fix for when the in criteria size is unlimited, see https://jira.jboss.org/jira/browse/TEIID-858. It would also fix the situation you're having, but there may be something else causing your failure.

                      Thanks,