Inconsistent rounding in developer work summary

[caleb@…]

I am using the "Developer Work Summary" for my billing and have been having problems some months getting consistent numbers out of the report.

The only trend I have spotted is that when the very first line item in the report is a whole number (say 2 hours) all the other items are rounded to whole numbers. If they are less than a whole hour (say 10 minutes) they show up as blank. On the other hand if the first line item is some fraction of an hour (.17 or 10 minutes for example) then the entire rest of the report shows the correct times.

[Russ Tyndall]

um looking into this I cannot see what is causing it. Can you post the sql of the report giving you trouble. It might be that you have an old version that is giving you trouble. Alternatively I am going to need a wee bit more info to start tackling this.

• what database backend and version are you using
• does this happen on all of the "work summary" reports or just "developer work summary"

I cannot recreate this error on my end, so the above information might help me sort this out

[anonymous]

SQL of Developer Work Summary report...

SELECT author as __group__,__style__, ticket, summary,
  newvalue as Work_added, time as time, _ord
FROM(
  SELECT '' as __style__, author, t.id  as ticket,
    t.summary as summary,
    CASE WHEN newvalue = '' OR newvalue IS NULL THEN 0
         ELSE CAST( newvalue AS DECIMAL ) END as newvalue,
    ticket_change.time as time, 0 as _ord
  FROM ticket_change
  JOIN ticket t on t.id = ticket_change.ticket
  LEFT JOIN ticket_custom as billable on billable.ticket = t.id 
    and billable.name = 'billable'
  WHERE field = 'hours' and
    t.status IN ($NEW, $ASSIGNED, $REOPENED, $CLOSED) 
      AND billable.value in ($BILLABLE, $UNBILLABLE)
      AND ticket_change.time >= $STARTDATE
      AND ticket_change.time < $ENDDATE
      
  UNION 
  
  SELECT 'background-color:#DFE;' as __style__, author, NULL as ticket,
    Null as summary,
    SUM( CASE WHEN newvalue = '' OR newvalue IS NULL THEN 0
         ELSE CAST( newvalue AS DECIMAL ) END) as newvalue,
    NULL as time, 1 as _ord
  FROM ticket_change
  JOIN ticket t on t.id = ticket_change.ticket
  LEFT JOIN ticket_custom as billable on billable.ticket = t.id 
    and billable.name = 'billable'
  WHERE field = 'hours' and
    t.status IN ($NEW, $ASSIGNED, $REOPENED, $CLOSED) 
      AND billable.value in ($BILLABLE, $UNBILLABLE)
      AND ticket_change.time >= $STARTDATE
      AND ticket_change.time < $ENDDATE
  GROUP By author
)  as tbl
ORDER BY author,  _ord ASC, time

• Database backend is sqlite version 3.5.5.
• The developer work summary is the only report I see this problem on.

[anonymous]

Report starting with days that have non-whole hours.

[anonymous]

Report starting with days that have whole hours.

[anonymous]

The two images I just attached are a report run on the same data set, except the one that worked properly started a couple days earlier so that it started off on a day that had non-whole-hour times.

[Russ Tyndall]

Can you post a snapshot of the change history of ticket #249 (or one of the others that shows with no work_added). There shouldnt be those blank rows and I need to figure out what is causing them as I cannot recreate them. It must be some change on the ticket is interfering with the report.

As you can tell from

CASE WHEN newvalue = '' OR newvalue IS NULL THEN 0
         ELSE CAST( newvalue AS DECIMAL ) END

that field should always be a number. Whatever is causing that report to display rows without that number is very likely also causing the sum total to be wrong.

[caleb@…]

Email me off of here and I'll give you a temporary password so you can check this problem out at the source. I don't see anything noteworthy about the change history on any of those tickets, and note that they DO work some of the time, if you vary the start date.

[Russ Tyndall]

Replying to caleb@chiefworks.com:

Email me off of here and I'll give you a temporary password so you can check this problem out at the source. I don't see anything noteworthy about the change history on any of those tickets, and note that they DO work some of the time, if you vary the start date.

Sorry it took so long to respond. I just got off on other projects. re-reading this ticket it also occurs to me that perhaps the THEN case below should return a floating 0.0 instead of an integer 0.

CASE WHEN newvalue = '' OR newvalue IS NULL THEN 0.0
         ELSE CAST( newvalue AS DECIMAL ) END

Easier than giving me a login would be to post your trac database here or email it to me at my email address if there is sensitive data in it.

All I really need to do is examine the contents of the ticket change table if you want to truncate the wiki and what not.

Sorry again for the delay.

Russ

[anonymous]

Replying to bobbysmith007:

perhaps the THEN case below should return a floating 0.0 instead of an integer 0.

CASE WHEN newvalue = '' OR newvalue IS NULL THEN 0.0
         ELSE CAST( newvalue AS DECIMAL ) END

I tried making that change to my reports and saw no difference. I also tried 0.01 in case it was trying to round before it casted, also same results.

I emailed you the db from the trac project in question.

[Russ Tyndall]

Alright... I am stumped. I dropped that DB into a trac instance on my end, ran the report and... no problems.

What release of trac are you using (preferably svn revision)?

Can you try executing the report that is failing in the database rather than through trac so we can narrow down whether it is sqlite or trac that is screwing this up.

Sorry I cannot be more helpful.

Russ

[caleb@…]

Replying to bobbysmith007:

What release of trac are you using (preferably svn revision)?

I am running a distro packaged version of track 0.10.4, I do not know the svn release on it.

Can you try executing the report that is failing in the database

Meh. That's different, the report shows up the correct numbers. Here are the first two lines of a failing report directly from the db I sent you:

caleb||337|Cleanup Area Businesses page|2|1207350004|0
caleb||339|Fishing Video too low.....|0.25|1207351068|0

And here are the same two how they show up in trac:

#337  	Cleanup Area Businesses page  	2  	04/04/08
#339 	Fishing Video too low..... 		04/04/08

Any suggestions on where I should go with it from here?

[caleb@…]

For reference, I looked in the source for my distribution and the package is being built from source code downloaded directly from this url: ​http://ftp.edgewall.com/pub/trac/trac-0.10.4.tar.gz

Also for reference, the code I ran manually against the database I sent you was:

SELECT author as __group__,__style__, ticket, summary,
  newvalue as Work_added, time as time, _ord
FROM(
  SELECT '' as __style__, author, t.id  as ticket,
    t.summary as summary,
    CASE WHEN newvalue = '' OR newvalue IS NULL THEN 0
         ELSE CAST( newvalue AS DECIMAL ) END as newvalue,
    ticket_change.time as time, 0 as _ord
  FROM ticket_change
  JOIN ticket t on t.id = ticket_change.ticket
  LEFT JOIN ticket_custom as billable on billable.ticket = t.id 
    and billable.name = 'billable'
  WHERE field = 'hours' and
    t.status IN ('new', 'assigned', 'assigned', 'closed') 
      AND billable.value in (1, 1)
      AND ticket_change.time >= 1207029600
      AND ticket_change.time < 1209621601
      
  UNION 
  
  SELECT 'background-color:#DFE;' as __style__, author, NULL as ticket,
    Null as summary,
    SUM( CASE WHEN newvalue = '' OR newvalue IS NULL THEN 0
         ELSE CAST( newvalue AS DECIMAL ) END) as newvalue,
    NULL as time, 1 as _ord
  FROM ticket_change
  JOIN ticket t on t.id = ticket_change.ticket
  LEFT JOIN ticket_custom as billable on billable.ticket = t.id 
    and billable.name = 'billable'
  WHERE field = 'hours' and
    t.status IN ('new', 'assigned', 'reopened', 'closed') 
      AND billable.value in (1, 1)
      AND ticket_change.time >= 1207029600
      AND ticket_change.time < 1209621601
  GROUP By author
)  as tbl
ORDER BY author,  _ord ASC, time

This exact same code, run from track, produces the messed up code, see my test report at: ​http://trac.alerque.com/projects/ocra/report/19

[Russ Tyndall]

I installed a new trac instance at the head of trac10 stable and dropped your DB in. So I have verified that there is a fix for this bug in trac 10 stable.

It is pretty easy to install trac from svn (especially if you already have a lesser version globally installed).

Below is an example script that I believe works [stuff in square brackets should be replaced sensibly]:

mkdir trac10
cd trac10
svn co http://svn.edgewall.com/repos/trac/branches/0.10-stable
sudo python setup.py install
sudo trac-admin [trac instance director(y|ies)] upgrade 
[Reboot the trac webserver]

I hope this helps,

Russ

[caleb@…]

At some point (I think when I upgraded to trac 0.10.5) this problem went away has has been fine for some time. Unfortunately it is back.

When I upgraded to trac 0.11.1 (and duly upgraded this plugin from svn to match) it has cropped up again. As far as I can tell the symptoms are the same. If I manually run on the console against sqllite the same query that gets run in the developer work summary report I get exact numbers, but the report in trac is only showing rounded numbers.

Do you have anything I can try to trace down to make this cooperate?

[Russ Tyndall]

Possibly related to: #3490

[Russ Tyndall]

Do you see the same rounding bugs when you export as csv?

[anonymous]

Replying to bobbysmith007:

Do you see the same rounding bugs when you export as csv?

Yes, I do. The CSV export is improperly rounded from floats to whole numbers.

[Russ Tyndall]

This is so frustrating, just like last time you were experiencing the error, I dropped your database into a new trac install and the report shows fine. That same report on your site (my user was apparently still active) shows that we are running the same version of trac but not getting the same results from the database.

You say that executing the report in the database will give you correct results and comments on the other ticket imply that this is happening on other database backends. This makes it seem as if this is certainly something happening in a specific version of trac or python. (What version of Python are you running?)

I'll keep at it, but not sure when the problem will click

[caleb@…]

Hey thanks for looking into this, and yes I made sure your user was active again before reopening this ticket. I realize it's a troublesome bug because nobody is how who's code is even at fault.

The server this is running on (not the same one it was last time) is running python 2.5.2, sqlite-3.5.6, python-sqlite-1.1.7 ... wait that's weird ... be right back.

Ok my distribution package of trac has an odd (wrong) dependency of for python-sqlite. I just forced to use the newer version (python-sqlite-2.3.5) instead and the rounding behavior stopped immediately.

I will make this as resolved since it doesn't seem to be specific to this plugin and my install is working, but if anybody wants to work on tracing this farther I'd be glad to help. If it's just something that has been fixed in the python bindings for sqlite then we can probably not worry about it ... perhaps the other related ticket is along these lines too?