Hibernate 4.1.2
C3P0 0.9.1 or 0.9.2
PervasiveSQL
I'm raising this as an issue here as a caution to others who may be using c3p0 with maxStatements (or maxStatementsPerConnection) configured with a value > 1.
In an effort to performance tune my application, I have been experimenting with various settings of the c3p0.maxStatements parameter.
When configured with any value greater than 1, there is a danger that erroneous results will be returned when using parameterised queries.
I first found this issue when testing the following code:
Code:
SQLQuery query =
session.createSQLQuery(
"select 'ST' as type, null as date1, "
+ "null as date2, null as wo_no, null as so_no, null as line_no, "
+ "0 AS demand, sum(st.qty_on_hand) as balance, 0 as supply from Stock st "
+ "where st.part_no = :part_no " + "union all "
+ "select 'WO' as type, mats.due_date as date1, mats.due_date as date2, "
+ "mats.wo_no as wo_no , wo.so_no as so_no, null as line_no, "
+ "sum(mats.qty_required - mats.qty_issd) as demand, 0 as balance, 0 as supply "
+ "from Materials mats, Wo wo " + "where mats.wo_no = wo.wo_no and mats.completely_issd = 'N' "
+ "and mats.part_no = :part_no "
+ "group by type, date1, date2, wo_no, so_no, line_no, balance, supply union all "
+ "select 'SO' as type, soi.due_date as date1, soi.due_date as date2, "
+ "null as wo_no, soi.so_no as so_no, soi.so_line_no as line_no, "
+ "sum(soi.qty - soi.qty_del) as demand, 0 as balance, 0 as supply " + "from So_items soi, Part p "
+ "where soi.part_no = p.part_no and p.mbf_code = 'B' "
+ "and soi.qty > soi.qty_del and soi.fully_del <> 'Y' " + "and soi.part_no = :part_no "
+ "group by type, date1, date2, wo_no, so_no, line_no, balance, supply " + "union all "
+ "select 'PO' as type, pod.due_date as date1, pod.due_date as date2, "
+ "null as wo_no, pod.po_no as so_no, pod.po_line_no as line_no, 0 as demand, 0 as balance, "
+ "if (pod.del_complete = 'Y', "
+ "sum(pod.qty_received - pod.qty_stored), sum(pod.qty_ordered - pod.qty_stored)) as supply "
+ "from Po_detail pod "
+ "where ( pod.del_complete = 'N' or (pod.qty_stored + pod.total_qty_scrapped) < pod.qty_ordered ) "
+ "and pod.part_no = :part_no "
+ " and ((pod.del_complete = 'Y' and pod.qty_received - pod.qty_stored > 0) or "
+ " (pod.del_complete = 'N' and pod.qty_ordered - pod.qty_stored > 0)) "
+ "group by type, date1, date2, wo_no, so_no, line_no, del_complete, balance, demand order by 2 asc, 1 asc");
query.setString("part_no", part_no);
query.addScalar("type", StandardBasicTypes.STRING);
query.addScalar("date1", StandardBasicTypes.DATE);
query.addScalar("date2", StandardBasicTypes.DATE);
query.addScalar("wo_no", StandardBasicTypes.STRING);
query.addScalar("so_no", StandardBasicTypes.STRING);
query.addScalar("line_no", StandardBasicTypes.INTEGER);
query.addScalar("demand", StandardBasicTypes.DOUBLE);
query.addScalar("balance", StandardBasicTypes.DOUBLE);
query.addScalar("supply", StandardBasicTypes.DOUBLE);
List<Object[]> oList = query.list();
During execution (in the same session), the above is executed multiple times (usually ~ 2000 times) with the :part_no parameter being replaced at each successive execution.
If c3p0 statement caching is enabled, the query erroneously returns the SAME result for each execution.
However, if I modify the code as follows I can safely enable c3p0 statement caching:
Code:
String q = "select 'ST' as type, null as date1, "
+ "null as date2, null as wo_no, null as so_no, null as line_no, "
+ "0 AS demand, sum(st.qty_on_hand) as balance, 0 as supply from Stock st "
+ "where st.part_no = :part_no " + "union all "
+ "select 'WO' as type, mats.due_date as date1, mats.due_date as date2, "
+ "mats.wo_no as wo_no , wo.so_no as so_no, null as line_no, "
+ "sum(mats.qty_required - mats.qty_issd) as demand, 0 as balance, 0 as supply "
+ "from Materials mats, Wo wo " + "where mats.wo_no = wo.wo_no and mats.completely_issd = 'N' "
+ "and mats.part_no = :part_no "
+ "group by type, date1, date2, wo_no, so_no, line_no, balance, supply union all "
+ "select 'SO' as type, soi.due_date as date1, soi.due_date as date2, "
+ "null as wo_no, soi.so_no as so_no, soi.so_line_no as line_no, "
+ "sum(soi.qty - soi.qty_del) as demand, 0 as balance, 0 as supply from So_items soi, Part p "
+ "where soi.part_no = p.part_no and p.mbf_code = 'B' "
+ "and soi.qty > soi.qty_del and soi.fully_del <> 'Y' " + "and soi.part_no = :part_no "
+ "group by type, date1, date2, wo_no, so_no, line_no, balance, supply " + "union all "
+ "select 'PO' as type, pod.due_date as date1, pod.due_date as date2, "
+ "null as wo_no, pod.po_no as so_no, pod.po_line_no as line_no, " + "0 as demand, 0 as balance, "
+ "if (pod.del_complete = 'Y', "
+ "sum(pod.qty_received - pod.qty_stored), sum(pod.qty_ordered - pod.qty_stored)) as supply "
+ "from Po_detail pod "
+ "where ( pod.del_complete = 'N' or (pod.qty_stored + pod.total_qty_scrapped) < pod.qty_ordered ) "
+ "and pod.part_no = :part_no "
+ " and ((pod.del_complete = 'Y' and pod.qty_received - pod.qty_stored > 0) or "
+ " (pod.del_complete = 'N' and pod.qty_ordered - pod.qty_stored > 0)) "
+ "group by type, date1, date2, wo_no, so_no, line_no, del_complete, balance, demand order by 2 asc, 1 asc";
q = q.replace(":part_no", "'"+part_no+"'");
SQLQuery query = session.createSQLQuery(q);
query.addScalar("type", StandardBasicTypes.STRING);
query.addScalar("date1", StandardBasicTypes.DATE);
query.addScalar("date2", StandardBasicTypes.DATE);
query.addScalar("wo_no", StandardBasicTypes.STRING);
query.addScalar("so_no", StandardBasicTypes.STRING);
query.addScalar("line_no", StandardBasicTypes.INTEGER);
query.addScalar("demand", StandardBasicTypes.DOUBLE);
query.addScalar("balance", StandardBasicTypes.DOUBLE);
query.addScalar("supply", StandardBasicTypes.DOUBLE);
List<Object[]> oList = query.list()
Debugging the classes between org.hibernate.engine.jdbc.internal.proxy.ConnectionProxyHandler and com.mchange.v2.c3p0.stmt.GooGooStatementCache is particularly difficult as some of the methods utilise reflection but it appears that using the first scheme (query.setString("part_no", part_no);), c3p0 is acually caching the statement:
Code:
"select 'ST' as type, null as date1, null as date2, null as wo_no, null as so_no, null as line_no, 0 AS demand, sum(st.qty_on_hand) as balance, 0 as supply from Stock st where st.part_no = ? union all select 'WO' as type, mats.due_date as date1, mats.due_date as date2, mats.wo_no as wo_no , wo.so_no as so_no, null as line_no, sum(mats.qty_required - mats.qty_issd) as demand, 0 as balance, 0 as supply from Materials mats, Wo wo where mats.wo_no = wo.wo_no and mats.completely_issd = 'N' and mats.part_no = ? group by type, date1, date2, wo_no, so_no, line_no, balance, supply union all select 'SO' as type, soi.due_date as date1, soi.due_date as date2, null as wo_no, soi.so_no as so_no, soi.so_line_no as line_no, sum(soi.qty - soi.qty_del) as demand, 0 as balance, 0 as supply from So_items soi, Part p where soi.part_no = p.part_no and p.mbf_code = 'B' and soi.qty > soi.qty_del and soi.fully_del <> 'Y' and soi.part_no = ? group by type, date1, date2, wo_no, so_no, line_no, balance, supply union all select 'PO' as type, pod.due_date as date1, pod.due_date as date2, null as wo_no, pod.po_no as so_no, pod.po_line_no as line_no, 0 as demand, 0 as balance, if (pod.del_complete = 'Y', sum(pod.qty_received - pod.qty_stored), sum(pod.qty_ordered - pod.qty_stored)) as supply from Po_detail pod where ( pod.del_complete = 'N' or (pod.qty_stored + pod.total_qty_scrapped) < pod.qty_ordered ) and pod.part_no = ? and ((pod.del_complete = 'Y' and pod.qty_received - pod.qty_stored > 0) or (pod.del_complete = 'N' and pod.qty_ordered - pod.qty_stored > 0)) group by type, date1, date2, wo_no, so_no, line_no, del_complete, balance, demand order by 2 asc, 1 asc"
NO parameters replaced!!
Curiously, the first time the query is executed is returns the correct results (presumably because the statement is not found in the cache) all susbsequent executions return the SAME results as the first because the statement has been cached.
However, if I use the second form (q = q.replace(":part_no", "'"+part_no+"'");), the parameters are replaced like:
Code:
"select 'ST' as type, null as date1, null as date2, null as wo_no, null as so_no, null as line_no, 0 AS demand, sum(st.qty_on_hand) as balance, 0 as supply from Stock st where st.part_no = '028975 ' union all select 'WO' as type, mats.due_date as date1, mats.due_date as date2, mats.wo_no as wo_no , wo.so_no as so_no, null as line_no, sum(mats.qty_required - mats.qty_issd) as demand, 0 as balance, 0 as supply from Materials mats, Wo wo where mats.wo_no = wo.wo_no and mats.completely_issd = 'N' and mats.part_no = '028975 ' group by type, date1, date2, wo_no, so_no, line_no, balance, supply union all select 'SO' as type, soi.due_date as date1, soi.due_date as date2, null as wo_no, soi.so_no as so_no, soi.so_line_no as line_no, sum(soi.qty - soi.qty_del) as demand, 0 as balance, 0 as supply from So_items soi, Part p where soi.part_no = p.part_no and p.mbf_code = 'B' and soi.qty > soi.qty_del and soi.fully_del <> 'Y' and soi.part_no = '028975 ' group by type, date1, date2, wo_no, so_no, line_no, balance, supply union all select 'PO' as type, pod.due_date as date1, pod.due_date as date2, null as wo_no, pod.po_no as so_no, pod.po_line_no as line_no, 0 as demand, 0 as balance, if (pod.del_complete = 'Y', sum(pod.qty_received - pod.qty_stored), sum(pod.qty_ordered - pod.qty_stored)) as supply from Po_detail pod where ( pod.del_complete = 'N' or (pod.qty_stored + pod.total_qty_scrapped) < pod.qty_ordered ) and pod.part_no = '028975 ' and ((pod.del_complete = 'Y' and pod.qty_received - pod.qty_stored > 0) or (pod.del_complete = 'N' and pod.qty_ordered - pod.qty_stored > 0)) group by type, date1, date2, wo_no, so_no, line_no, del_complete, balance, demand order by 2 asc, 1 asc"
The query now succeeds returning different results with each invocation.
The only conclusion I have drawn so far is that it is NOT safe to use c3p0 statement caching!!
Time permitting , I shall endeavour to debug further over the coming days.
db
Login
FAQ
Search
