https://www.redmine.org/
https://www.redmine.org/favicon.ico?1679302129
2014-01-15T13:24:12Z
Redmine
Redmine - Defect #15870: Parent task completion is 104% after update of subtasks
https://www.redmine.org/issues/15870?journal_id=54305
2014-01-15T13:24:12Z
Etienne Massip
<ul><li><strong>Category</strong> set to <i>Issues</i></li><li><strong>Priority</strong> changed from <i>Urgent</i> to <i>Normal</i></li></ul><p>Could you please give more details according to <a class="wiki-page" href="https://www.redmine.org/projects/redmine/wiki/SubmittingBugs">SubmittingBugs</a>?</p>
Redmine - Defect #15870: Parent task completion is 104% after update of subtasks
https://www.redmine.org/issues/15870?journal_id=54320
2014-01-16T07:29:28Z
Toshi MARUYAMA
<ul><li><strong>Status</strong> changed from <i>New</i> to <i>Needs feedback</i></li></ul>
Redmine - Defect #15870: Parent task completion is 104% after update of subtasks
https://www.redmine.org/issues/15870?journal_id=54351
2014-01-17T07:38:10Z
joe chen
<ul></ul><p>Etienne Massip wrote:</p>
<blockquote>
<p>Could you please give more details according to <a class="wiki-page" href="https://www.redmine.org/projects/redmine/wiki/SubmittingBugs">SubmittingBugs</a>?</p>
</blockquote>
<p>It is a parent task.<br />The parent task has a huge number of subtasks.<br />The completion of tasks of the parent is automatically calculated as 104%.</p>
Redmine - Defect #15870: Parent task completion is 104% after update of subtasks
https://www.redmine.org/issues/15870?journal_id=54377
2014-01-19T08:37:27Z
Jean-Philippe Lang
jp_lang@yahoo.fr
<ul><li><strong>Subject</strong> changed from <i>Task completion is 104%</i> to <i>Parent task completion is 104% after update of subtasks</i></li><li><strong>Status</strong> changed from <i>Needs feedback</i> to <i>Resolved</i></li><li><strong>Assignee</strong> set to <i>Jean-Philippe Lang</i></li><li><strong>Target version</strong> set to <i>2.4.3</i></li><li><strong>Resolution</strong> set to <i>Fixed</i></li></ul><p>Fixed in <a class="changeset" title="Make sure that we don't set the done ratio to a value > 100% (#15870)." href="https://www.redmine.org/projects/redmine/repository/svn/revisions/12669">r12669</a>.</p>
Redmine - Defect #15870: Parent task completion is 104% after update of subtasks
https://www.redmine.org/issues/15870?journal_id=54520
2014-01-27T14:57:10Z
Daniel Felix
<ul><li><strong>Has duplicate</strong> <i><a class="issue tracker-1 status-5 priority-4 priority-default closed" href="/issues/15974">Defect #15974</a>: percentage of done is more than 100%. </i> added</li></ul>
Redmine - Defect #15870: Parent task completion is 104% after update of subtasks
https://www.redmine.org/issues/15870?journal_id=54522
2014-01-27T15:20:41Z
Bruno Medeiros
<ul></ul><p><a class="changeset" title="Make sure that we don't set the done ratio to a value > 100% (#15870)." href="https://www.redmine.org/projects/redmine/repository/svn/revisions/12669">r12669</a> doesn't seem a real fix to me, it only forces the value to 100 if it's bigger than 100, it doesn't fix the calculation problem that lead to the incorrect value in the first place. Am I missing something?</p>
Redmine - Defect #15870: Parent task completion is 104% after update of subtasks
https://www.redmine.org/issues/15870?journal_id=54567
2014-01-30T15:13:01Z
Uwe Koloska
<ul></ul><p>No, you are absolutely right. The wrong calculation is not caused by some rounding errors or something like this, it comes from subtasks without estimated time while others have.</p>
<p>Scenario:<br />Parent<br /> - Sub1: 40h<br /> - Sub2: 40h<br /> - Sub3: 20h<br /> - Sub4: --</p>
<p>when closing all subtickets, <span>done sums up to 125</span>.</p>
<p>But now for the tricky part: To solve this, you can set Sub4 to 0.01. Then <span>done is correctly calculated with 100</span>.</p>
<p>And now for the fun fact: if you now change Sub4 back to nothing in estimated time, the calculation is correct:<br /> - Sub1: 40h := 30%<br /> - Sub2: 40h := 30%<br /> - Sub3: 20h := 15%<br /> - Sub4: --- := 25%</p>
<p>Ahh -- but if you change Sub4 to 0h you get the wrong result 125% again. Maybe the estimated time is not really gone if you delete just the entry?</p>
<a name="possible-solutions"></a>
<h3 >possible solutions<a href="#possible-solutions" class="wiki-anchor">¶</a></h3>
<ul>
<li>if there is at least one subticket with an estimated time, then treat all unset times as 0h and don't include this tickets %done in the calculation for the parent ticket</li>
<li>calculate the unestimated tickets with their weight coming from 1/number_of_subtickets and give the rest to the estimated tickets</li>
</ul>
<p>The last one is my preferred solution.</p>
Redmine - Defect #15870: Parent task completion is 104% after update of subtasks
https://www.redmine.org/issues/15870?journal_id=54573
2014-01-30T22:32:39Z
Jean-Philippe Lang
jp_lang@yahoo.fr
<ul></ul><p>Uwe Koloska wrote:</p>
<blockquote>
<p>Scenario:<br />Parent<br />- Sub1: 40h<br />- Sub2: 40h<br />- Sub3: 20h<br />- Sub4: --</p>
<p>when closing all subtickets, <span>done sums up to 125</span>.</p>
</blockquote>
<p>I've added a test for this scenario and reverted <a class="changeset" title="Make sure that we don't set the done ratio to a value > 100% (#15870)." href="https://www.redmine.org/projects/redmine/repository/svn/revisions/12669">r12669</a>, but it passes (see <a class="changeset" title="Reverts r12669 and add a test for #15870." href="https://www.redmine.org/projects/redmine/repository/svn/revisions/12739">r12739</a>). Any ideas?</p>
Redmine - Defect #15870: Parent task completion is 104% after update of subtasks
https://www.redmine.org/issues/15870?journal_id=54588
2014-01-31T07:40:29Z
Daniel Felix
<ul></ul><p>As I already said some time ago, the database column estimated_hours has the datatype real, which is one of the worst datatypes.</p>
<p>This should be changed to a much better datatype like decimal. Well in some edgecases float would be good enough to. But we never use real in any software/database application in our whole company as it is just awful. Yes real is a bit faster, and a bit smaller in some cases than a high precision decimal value. But well, disk space is cheaper than 1990 and a CPU easily could handle this.</p>
<p>Maybe you would give it a try and change it to a decimal value like decimal(13,5) which gives you 8 predecimal and 5 decimal digits. This would be enough for the most cases.</p>
Redmine - Defect #15870: Parent task completion is 104% after update of subtasks
https://www.redmine.org/issues/15870?journal_id=54597
2014-01-31T07:55:23Z
Jan Niggemann (redmine.org team member)
jan.niggemann@redmine.org
<ul></ul><p>Daniel Felix wrote:</p>
<blockquote>
<p>Well in some edgecases float would be good enough</p>
</blockquote>
<p><code>FLOAT</code> is an approximate numeric data type whereas <code>DECIMAL</code> isn't, at least in MySQL and MSSQL.</p>
<blockquote>
<p>Maybe you would give it a try and change it to a decimal value like decimal(13,5) which gives you 8 predecimal and 5 decimal digits.</p>
</blockquote>
<p>Good idea</p>
Redmine - Defect #15870: Parent task completion is 104% after update of subtasks
https://www.redmine.org/issues/15870?journal_id=54600
2014-01-31T10:17:14Z
Uwe Koloska
<ul></ul><p>Jean-Philippe Lang wrote:</p>
<blockquote>
<p>I've added a test for this scenario and reverted <a class="changeset" title="Make sure that we don't set the done ratio to a value > 100% (#15870)." href="https://www.redmine.org/projects/redmine/repository/svn/revisions/12669">r12669</a>, but it passes (see <a class="changeset" title="Reverts r12669 and add a test for #15870." href="https://www.redmine.org/projects/redmine/repository/svn/revisions/12739">r12739</a>). Any ideas?</p>
</blockquote>
<p>Yes:<br /><pre><code class="diff syntaxhl"> Issue.generate!(:estimated_hours => 40, :parent_issue_id => parent.id)
Issue.generate!(:estimated_hours => 40, :parent_issue_id => parent.id)
Issue.generate!(:estimated_hours => 20, :parent_issue_id => parent.id)
<span class="gd">- Issue.generate!(:parent_issue_id => parent.id)
</span><span class="gi">+ Issue.generate!(:estimated_hours => 0, :parent_issue_id => parent.id)
</span></code></pre></p>
<p>At least this is the case, where I can reproduce the error.</p>
But there is something in the story that makes me think (<em>error</em> means <code>%done > 100%</code> and all changes done to issue4):
<ol>
<li>error in normal operation if issue4 has no estimated time (the field was not touched and looks empty)</li>
<li>no error if estimated time is set to <code>0.01</code></li>
<li><strong>nor error</strong> if estimated time is deleted (and stay visually empty when reediting)</li>
<li>error if estimated time is set to <code>0</code></li>
<li>no error if estimated time is deleted again</li>
</ol>
Redmine - Defect #15870: Parent task completion is 104% after update of subtasks
https://www.redmine.org/issues/15870?journal_id=54601
2014-01-31T12:37:58Z
Jean-Philippe Lang
jp_lang@yahoo.fr
<ul></ul><p>It's a bit different from the above and it fails. The following change should fix it:</p>
<pre><code class="diff syntaxhl"><span class="gh">Index: app/models/issue.rb
===================================================================
</span><span class="gd">--- app/models/issue.rb (révision 12743)
</span><span class="gi">+++ app/models/issue.rb (copie de travail)
</span><span class="p">@@ -1355,7 +1355,7 @@</span>
unless Issue.use_status_for_done_ratio? && p.status && p.status.default_done_ratio
leaves_count = p.leaves.count
if leaves_count > 0
<span class="gd">- average = p.leaves.average(:estimated_hours).to_f
</span><span class="gi">+ average = p.leaves.where("estimated_hours > 0").average(:estimated_hours).to_f
</span> if average == 0
average = 1
end
</code></pre>
<p>Could you give it a try before I commit?</p>
Redmine - Defect #15870: Parent task completion is 104% after update of subtasks
https://www.redmine.org/issues/15870?journal_id=54602
2014-01-31T12:55:32Z
Daniel Felix
<ul></ul><p>I retried the described problems. Here is the solution:</p>
<p>In this testcase the error occurs with the given scenario of 4 tickets (40,40,20,NULL).<br />Here is the MS SQL Server Statement to reproduce. Shouldn't be a problem to change this for different dbms.</p>
<p>Issue 57 is the last issue with Null or 0 Value.</p>
<pre><code class="sql syntaxhl"> <span class="c1">-- first scenario which provides the error.</span>
<span class="k">update</span> <span class="n">redmine_development</span><span class="p">.</span><span class="n">dbo</span><span class="p">.</span><span class="n">issues</span>
<span class="k">set</span> <span class="n">estimated_hours</span> <span class="o">=</span> <span class="mi">0</span>
<span class="k">WHERE</span> <span class="n">ID</span> <span class="o">=</span> <span class="mi">57</span>
<span class="c1">-- second scenario which provides the error.</span>
<span class="k">update</span> <span class="n">redmine_development</span><span class="p">.</span><span class="n">dbo</span><span class="p">.</span><span class="n">issues</span>
<span class="k">set</span> <span class="n">estimated_hours</span> <span class="o">=</span> <span class="k">NULL</span>
<span class="k">WHERE</span> <span class="n">ID</span> <span class="o">=</span> <span class="mi">57</span>
<span class="c1">-- Code to retry the Rails calculation</span>
<span class="k">declare</span> <span class="o">@</span><span class="k">count</span> <span class="nb">int</span>
<span class="k">select</span> <span class="o">@</span><span class="k">count</span> <span class="o">=</span> <span class="k">count</span><span class="p">(</span><span class="o">*</span><span class="p">)</span>
<span class="c1">-- select * </span>
<span class="k">FROM</span> <span class="p">[</span><span class="n">redmine_development</span><span class="p">].[</span><span class="n">dbo</span><span class="p">].[</span><span class="n">issues</span><span class="p">]</span>
<span class="k">WHERE</span> <span class="n">parent_id</span> <span class="o">=</span> <span class="mi">53</span>
<span class="k">declare</span> <span class="o">@</span><span class="k">avg</span> <span class="nb">decimal</span><span class="p">(</span><span class="mi">13</span><span class="p">,</span><span class="mi">5</span><span class="p">)</span>
<span class="k">select</span> <span class="o">@</span><span class="k">avg</span> <span class="o">=</span> <span class="k">avg</span><span class="p">(</span><span class="n">estimated_hours</span><span class="p">)</span>
<span class="c1">-- select * </span>
<span class="k">FROM</span> <span class="p">[</span><span class="n">redmine_development</span><span class="p">].[</span><span class="n">dbo</span><span class="p">].[</span><span class="n">issues</span><span class="p">]</span>
<span class="k">WHERE</span> <span class="n">parent_id</span> <span class="o">=</span> <span class="mi">53</span>
<span class="k">declare</span> <span class="o">@</span><span class="n">val</span> <span class="nb">decimal</span><span class="p">(</span><span class="mi">13</span><span class="p">,</span><span class="mi">5</span><span class="p">)</span>
<span class="k">SELECT</span> <span class="o">@</span><span class="n">val</span> <span class="o">=</span> <span class="k">sum</span><span class="p">(</span><span class="n">d</span><span class="p">.</span><span class="n">v</span><span class="p">)</span> <span class="k">FROM</span> <span class="p">(</span>
<span class="c1">-- For testing purposes as subquery</span>
<span class="k">SELECT</span> <span class="n">COALESCE</span><span class="p">(</span><span class="k">CASE</span> <span class="k">WHEN</span> <span class="n">estimated_hours</span> <span class="o">></span> <span class="mi">0</span> <span class="k">THEN</span> <span class="n">estimated_hours</span> <span class="k">ELSE</span> <span class="k">NULL</span> <span class="k">END</span><span class="p">,</span> <span class="o">@</span><span class="k">avg</span><span class="p">)</span>
<span class="o">*</span> <span class="p">(</span><span class="k">CASE</span> <span class="k">WHEN</span> <span class="n">is_closed</span> <span class="o">=</span> <span class="mi">1</span> <span class="k">THEN</span> <span class="mi">100</span> <span class="k">ELSE</span> <span class="n">COALESCE</span><span class="p">(</span><span class="n">done_ratio</span><span class="p">,</span> <span class="mi">0</span><span class="p">)</span> <span class="k">END</span><span class="p">)</span> <span class="k">as</span> <span class="n">v</span>
<span class="k">FROM</span> <span class="p">[</span><span class="n">redmine_development</span><span class="p">].[</span><span class="n">dbo</span><span class="p">].[</span><span class="n">issues</span><span class="p">]</span> <span class="k">as</span> <span class="n">i</span>
<span class="k">inner</span> <span class="k">join</span> <span class="p">[</span><span class="n">redmine_development</span><span class="p">].[</span><span class="n">dbo</span><span class="p">].[</span><span class="n">issue_statuses</span><span class="p">]</span> <span class="k">as</span> <span class="n">ist</span>
<span class="k">on</span> <span class="n">i</span><span class="p">.</span><span class="n">status_id</span> <span class="o">=</span> <span class="n">ist</span><span class="p">.</span><span class="n">id</span>
<span class="k">WHERE</span> <span class="n">parent_id</span> <span class="o">=</span> <span class="mi">53</span>
<span class="p">)</span> <span class="k">as</span> <span class="n">d</span>
<span class="k">set</span> <span class="o">@</span><span class="n">val</span> <span class="o">=</span> <span class="o">@</span><span class="n">val</span> <span class="o">/</span> <span class="p">(</span><span class="o">@</span><span class="k">avg</span> <span class="o">*</span> <span class="o">@</span><span class="k">count</span><span class="p">)</span>
<span class="k">select</span> <span class="o">@</span><span class="n">val</span>
</code></pre>
<p>The error is quite easy.<br />If estimated_hours is NULL, the AVG-aggregation will leave the data row out in its calculation, which means:<br />4 data rows including one data row with NULL value results in this calculation: (40 + 40 + 20) / 3, means that this calculation (<a class="source" href="https://www.redmine.org/projects/redmine/repository/svn/entry/trunk/app/models/issue.rb#L1358">source:trunk/app/models/issue.rb#L1358</a>) resulting in 33,333... %<br />The sum of all estimated hours results in 10000 in both cases, test case 1 and test case 2.</p>
<p>This means later that this calculation (<a class="source" href="https://www.redmine.org/projects/redmine/repository/svn/entry/trunk/app/models/issue.rb#L1365">source:trunk/app/models/issue.rb#L1365</a>) results in this result:<br /><pre>
progress = done / (average * leaves_count)
progress = 10000 / (33.333 * 4)
progress = 75.00001
</pre></p>
<p>This would be the correct value.</p>
<p>The same thing with a 0 in estimated_hours, causes avg to take this value into account which results in this calculation (40 + 40 + 20 + 0) / 4 which means an avg of 25.</p>
<p>Taking this into account to the previous calculation results in this:<br /><pre>
progress = done / (average * leaves_count)
progress = 10000 / (25 * 4)
progress = 125.00000
</pre></p>
<p>BAM! Mistery resolved. :-P</p>
<p><strong>This leading me to this solution...</strong><br />Replacing this line (<a class="source" href="https://www.redmine.org/projects/redmine/repository/svn/entry/trunk/app/models/issue.rb#L1358">source:trunk/app/models/issue.rb#L1358</a>) with this, should solve the problem:<br /><pre><code class="ruby syntaxhl"><span class="nb">p</span><span class="p">.</span><span class="nf">leaves</span><span class="p">.</span><span class="nf">where</span><span class="p">(</span><span class="s2">"estimated_hours <> NULL"</span><span class="p">).</span><span class="nf">average</span><span class="p">(</span><span class="ss">:estimated_hours</span><span class="p">).</span><span class="nf">to_f</span>
</code></pre><br />or better with a quoted_nil:<br /><pre><code class="ruby syntaxhl"><span class="nb">p</span><span class="p">.</span><span class="nf">leaves</span><span class="p">.</span><span class="nf">where</span><span class="p">(</span><span class="s2">"estimated_hours <> </span><span class="si">#{</span><span class="n">connection</span><span class="p">.</span><span class="nf">quoted_nil</span><span class="si">}</span><span class="s2">"</span><span class="p">.</span><span class="nf">average</span><span class="p">(</span><span class="ss">:estimated_hours</span><span class="p">).</span><span class="nf">to_f</span>
</code></pre></p>
<p>Can you please try this, this should solve the problem.</p>
<p>Best regards,<br />Daniel</p>
Redmine - Defect #15870: Parent task completion is 104% after update of subtasks
https://www.redmine.org/issues/15870?journal_id=54603
2014-01-31T12:56:08Z
Daniel Felix
<ul></ul><p>Damn... Jean-Philippe has posted earlier. :-D</p>
Redmine - Defect #15870: Parent task completion is 104% after update of subtasks
https://www.redmine.org/issues/15870?journal_id=54605
2014-01-31T14:27:04Z
Uwe Koloska
<ul></ul><p>Jean-Philippe Lang wrote:</p>
<blockquote>
<p>It's a bit different from the above and it fails. The following change should fix it:</p>
<p>[...]</p>
<p>Could you give it a try before I commit?</p>
</blockquote>
<p>I have changed my test installation and the fix gives the correct result for the aforementioned use case.</p>
<p>Thank you!</p>
Redmine - Defect #15870: Parent task completion is 104% after update of subtasks
https://www.redmine.org/issues/15870?journal_id=54608
2014-01-31T16:27:39Z
Holger Just
<ul></ul><p>Note that this issue (and the proposed solutions) are the same as <a class="issue tracker-1 status-5 priority-4 priority-default closed" title="Defect: Invalid calculation of %done of parent issues under certain ciscumstances (Closed)" href="https://www.redmine.org/issues/15957">#15957</a> I posted a week ago with a patch and a test.</p>
<p>Unfortunately, I don't have the right to add issue relations here. My approach there reused part of the SQL already used in that function. In the end, the solution is to exclude all 0 values from the <code>average</code> calculation and to treat them as <code>NULL</code>.</p>
Redmine - Defect #15870: Parent task completion is 104% after update of subtasks
https://www.redmine.org/issues/15870?journal_id=54609
2014-01-31T17:51:39Z
Jean-Philippe Lang
jp_lang@yahoo.fr
<ul></ul><p>Fix committed in <a class="changeset" title="Fixed calculation of done_ratio of parent task with child having estimated_time at 0 (#15870)." href="https://www.redmine.org/projects/redmine/repository/svn/revisions/12745">r12745</a>.</p>
<p>Daniel, thanks for the explanation. Children with estimated time at 0 were taken into account for the average calculation but treated as null values after that. Holger, sorry for not having reviewed your patch earlier. <a class="changeset" title="Fixed calculation of done_ratio of parent task with child having estimated_time at 0 (#15870)." href="https://www.redmine.org/projects/redmine/repository/svn/revisions/12745">r12745</a> should give the very same results with a slightly cleaner query.</p>
Redmine - Defect #15870: Parent task completion is 104% after update of subtasks
https://www.redmine.org/issues/15870?journal_id=54686
2014-02-06T18:00:48Z
Jean-Philippe Lang
jp_lang@yahoo.fr
<ul><li><strong>Status</strong> changed from <i>Resolved</i> to <i>Closed</i></li></ul>