Float values are not summarized properly

[johan@…]

Float values are not summarized properly and. Either float values should not be allowed to be entered or they should be first class and supported everywhere. The various reports and currently unable to show the floats and even less summarize them Using trac 0.10.4/sqlite backend and estimation plugin version 0.5.3.

[Russ Tyndall]

please upgrade to the most recent version of the plugin and let me know if you still experience the problem.

version='0.6.7' is most recent

​here is a download link

[superb@…]

In case this helps anyone, I experience a similar issue with Trac 0.10.4 and T&E 0.6.6 and the Developer Work Summary report: the Work_added column values are truncated to integers where > 1 and blank where < 1.

No other reports of interest at the mo or any idea when an upgrade to latest/0.6.7 will occur. FWIW the T&E demo site shows fractional values for that same report.

Substituting the latest report code (after manually expanding #STATUSES#) still results in truncated Work_added values; from a quick look back through the revision history there don't appear to be significant changes to the report code anyway.

Bodge aka Work-around

This is an ultra-quick hack which works for me at first glance; haven't tried to grok the code, db, plugin, ... YMMV! Change:

SELECT author as __group__,__style__, ticket, summary,
  newvalue as Work_added, time as time, _ord

to:

SELECT author as __group__,__style__, ticket, summary,
  newvalue + 0.0 as Work_added, time as time, _ord

No time to explore any further at the mo so plenty of scope left for upgrades, better bodges/fixes etc.

[Russ Tyndall]

Hmmm... I have had a couple reports of this, but I have never been able to reproduce the problem. Based on workaround above it seems like sqlites type guesser is failing. My guess is that there is some data weirdness that causes this.

I have created a new reports file which will hopefully make sqlite happy for everyone. I have tried to be as explicit as possible in each case about what type those columns should be. I dont have a setup capable of running trac10 at the moment so I cannot install/test this. Please let me know if it works for you and I will update the repo.

If you have this installed from source simply drop in the new report file, reinstall, and call the trac-admin upgrade. Alternatively you can just edit each of the reports manually with the content from each of the reports.

[Russ Tyndall]

A possible solution to floats not correctly summing

[superb@…]

Considering I can see float values working on the T&E demo site (0.6.7 presumably) I expect you're right with data wierdness. Somebody posted a remark about implicit casts being more forgiving of non-digit characters than explicit casts ​here although I haven't tested for veracity. I don't know whether our db contains 'bad' values like '1h' or '0.5 hours' or even 'about an hour', and am unaware of what field validation has been in place over the usage of the plugin/versions to grok probable cause.

Thanks for the response/code but doesn't work for me; appears to give identical truncated/omitted output to that of the initial 0.6.6 report. I expect this code works for those it worked for anyway, although only your unit tests will tell for sure ;-)

Happy with the bodge so no sweat; for diagnosing data wierdness I anticipate (from a quick look) selecting and type-identifying ticket_xyz.newvalue where ticket_xyz.field = 'hours'. I suspect that I'd next get round to looking at it when an upgrade wipes out my bodge but I'd like to help so placed on the not-quite-back burner. Also I know a bad db ain't your problem!

Cheers!

[Russ Tyndall]

so does that mean that this is happening in mysql/trac11 too :(

That is no good. This has been working for so long, that its pretty disheartening to have it broken in nonreproducable ways on multiple backends.

Thanks for the speedy reply, that is certainly the way to keep me on task trying to solve this.

I am nervous about relying on the implicit cast because this will break things in postgresql.

If you could perhaps you could select all rows where the cast yeilds a different value than the +0.0 variant. This might help us at least identify the cause of this problem. Perhaps:

SELECT * FROM ticket_change WHERE field='hours' and and CAST(newvalue as DECIMAL)!=newvalue+0.0;

--or if thats not enough

-- This may not work in sqlite depending on whether you have regexp defined
SELECT * FROM ticket_change WHERE field='hours' and  newvalue REGEXP '[a-zA-Z]'

Thanks for the help,

Russ

[Russ Tyndall]

Has anyone been able to trac down the data condition that causes this?

Russ

[Russ Tyndall]

Possibly related to: #2896

[Russ Tyndall]

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

Thanks, Russ

[Russ Tyndall]

The guy having problems on the other ticket has found a solution in getting more recent python sqlite bindings. Please see his comment and check to see if this helps you.

​http://trac-hacks.org/ticket/2896#comment:18

One indication that there was something different, was that I ran the other person's database on the same version of python, trac, and the plugin and got good results on my trac instance but bad results on his. When he ran the report sql against his database he got good results, except when displayed through trac.

HTH, Russ

[Filipe Correia]

I am finding this bug on my installation of the plugin too. I get the wrong values both on the page itself and when exporting as CSV.

It looks like all the values are being truncated. Ie, for values of '0.20' or '0.40', a '0' is displayed.

[Russ Tyndall]

Could you please include what versions of trac and what versions of which database backends are affected?

Do the correct results appear when running the reports directly against the database? Above it is noted that old sqlite bindings might have been to blame, but there was also some talk about this affecting other db backends (with which i have little experience).

Does changing the report per: ​http://trac-hacks.org/ticket/3490#BodgeakaWork-around , help the problem?

Thanks in advance for your replies. As I have been unable to ever reproduce this on any of my setups (live or dev), there is very little chance of me tracking down the bug without detailed info from those experiencing the problem.

Russ

[Filipe Correia]

Replying to bobbysmith007:

Could you please include what versions of trac and what versions of which database backends are affected?

I'm experiencing this with sqlite:

• Trac: 0.11.2
• SQLite: 3.3.6
• pysqlite: 1.1.7

Does changing the report per: ​http://trac-hacks.org/ticket/3490#BodgeakaWork-around , help the problem?

yep, it does.

Thanks in advance for your replies.

No problem. Please tell me if you need any further infos.

[Russ Tyndall]

Did you determine that number by using this snippet:

​http://trac.edgewall.org/wiki/PySqlite#DetermineactualSQLiteandPySqliteversion

If so, is it possible to update those bindings. I am currently using 2.2.2 which is old, but much newer than the version you are using. Hopefully if you are able to update those bindings, then this will just cease to be a problem for you. If not, I guess my suggestion would be to run with the "Bodgey" workaround from above.

If upgrading to pysqlite2 fixes the problem, please write back so that I have a determination about what was causing this (at least on the sqlite backend).

Thanks again,

Russ

[Russ Tyndall]

ps. the release date for pysqlite 1.1.7 seems to be 02-Feb-2006, so thats pretty outdated at this point.

[Filipe Correia]

I found out the version by clicking on the "Powered by Trac 0.11.2" link, on the bottom of my project's trac pages.

I'll see what i can do about update pysqlite. Thanks!

[Filipe Correia]

I haven't updated yet, but found another thing that may help.

On the "Ticket Hours Grouped By Milestone" report, there are two columns in particular, "Estimated_work" and "Total_work", that I'm finding interesting. The first one has the bug we're discussing, but the values on the second column are fine. I'm looking at the report's source, but can't really see a difference on how these two values are obtained that could explain this.

Any thoughts?

[Russ Tyndall]

I have a theory:

Part of the problem seems to be the db-api's type inferencing. It seems that if the first row contains an integer, rather than paying attention to the type of that column, it just assumes that column to be integral all the way down. Where as if the first row contains a floating point number, all values are determined to be floating.

Does this match what you are seeing where one column works and one does not?

[Russ Tyndall]

Also, much thanks for taking the time to help me figure this out, your assistance is very much appreciated.

Russ

[Filipe Correia]

Replying to bobbysmith007:

I have a theory:

Part of the problem seems to be the db-api's type inferencing. It seems that if the first row contains an integer, rather than paying attention to the type of that column, it just assumes that column to be integral all the way down. Where as if the first row contains a floating point number, all values are determined to be floating.

Does this match what you are seeing where one column works and one does not?

yes, it does. There's something else that i just noticed though. There's this one first row displayed in the report, with a displayed "Estimated_work" of '0' and a displayed "Total_work" of '0.0'. This suggests that some other factor is affecting the way these values are shown (considering it's not a storage problem, and that for sqlite a '0' and a '0.0' are the same thing...).

Clicking the link for this particular ticket i can see that the "Estimated Number of Hours" for this ticket is also displayed as '0.0' and that the "Total Hours" is displayed as '0' (that is, they are displayed the other way around :S ).

Replying to bobbysmith007:

Also, much thanks for taking the time to help me figure this out, your assistance is very much appreciated.

You're quite welcome.

[Russ Tyndall]

So then I would definitely say that upgrading the api between the db and python sounds like the actual solution to this.

Cheers, Russ

[Filipe Correia]

I confirm that upgrading to pysqlite 2.3.3 fixed the issue! thanks.