These two queries don't work:
-- STMT_SELECT_WORKING_NODE_CHILDREN_1
SELECT local_relpath FROM nodes
WHERE wc_id = ?1 AND parent_relpath = ?2
AND op_depth = (SELECT MAX(op_depth) FROM nodes
WHERE wc_id = ?1 AND parent_relpath = ?2 AND op_depth > 0);
-- STMT_COUNT_WORKING_NODE_CHILDREN_1
SELECT COUNT(*) FROM nodes
WHERE wc_id = ?1 AND parent_relpath = ?2
AND op_depth = (SELECT MAX(op_depth) FROM nodes
WHERE wc_id = ?1 AND parent_relpath = ?2 AND op_depth > 0);
The problem is that they determine the higest op_depth for the
children and return only the children with that op_depth. This
happens to work if all the children have the same highest op_depth but
that's just an accident.
I can fix the first one as follows:
-- STMT_SELECT_WORKING_NODE_CHILDREN_1
SELECT local_relpath FROM nodes
WHERE wc_id = ?1 AND parent_relpath = ?2 AND op_depth > 0
GROUP BY local_relpath;
but I don't know how to fix the second one. How do I count the number
of rows returned by that GROUP BY query?
This leads on to the problem of selecting just the highest op_depth
for each child. Is it possible to get one query to return just the
highest op_depth for each child?
--
Philip
