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

Assertion Error with a query

rkishore999 Sep 24, 2009 3:15 PM

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

shawkins Sep 24, 2009 4:19 PM (in response to rkishore999)

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
Actions

2. Re: Assertion Error with a query

rkishore999 Sep 24, 2009 6:05 PM (in response to rkishore999)

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

shawkins Sep 24, 2009 6:27 PM (in response to rkishore999)
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,
Actions

4. Re: Assertion Error with a query

rkishore999 Sep 25, 2009 1:17 AM (in response to rkishore999)

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

shawkins Sep 25, 2009 4:35 PM (in response to rkishore999)

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,
Actions
6. Re: Assertion Error with a query

rkishore999 Sep 25, 2009 5:13 PM (in response to rkishore999)

Steve,

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

Regards

Ram
Actions
7. Re: Assertion Error with a query

shawkins Sep 29, 2009 10:48 AM (in response to rkishore999)

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,
Actions

Go to original post