Subtle data integrity bug with web2py

[John Heenan]

This is a report of a subtle data integrity bug that is difficult to
describe in a short manner. However the bug is trivial to fix.

I noticed the bug when I imported data directly into sqlite3 that had
date fields with empty dates. I used the sqlite3 utility .import
command as I needed to preserve the ROWID primary keys. The dates are
termination dates and should only be filled in when a termination
occurs.

If the corresponding web2py db.Field type is 'date' and the imported
value of the date field is empty then the following error message
occurs from the web2py database administrator.
Invalid Query invalid literal for int() with base 10: ''

If web2py is used to enter data then no error arises.

However if the web2py entered record is exported from sqlite and then
reimported the error occurs.

This should be enough to ring bells that there appears to be a data
integrity bug.

The first reaction of experts is likely to be that the error is
related to dynamic typing of sqlite and the requirement that text
entered date follows a strict ISO8601 format for sqlite3 internal date
functions to work correctly. However this is irrelevant as web2py
stores and manipulates date data without using sqlite internal date
functions and web2py does not necessarily use a complete ISO8601
string.

The next paragraph might appear bizarre and wrong to a database
expert, however to anyone familiar with the dynamic typing rules of
sqlite there is nothing wrong.

The key to the problem is examining the type of dynamic data stored by
sqlite3. The typename in the create statement for the date fileld is
'DATE'. Since 'DATE' is not matched by sqlite3's dynmaic typing column
affinity rules, the affinity of the column defaults to 'NUMERIC'. The
actual type of the sqlite imported date with an empty date is 'text'
but the type of the date field when entered through web2py is 'null'
instead!

This means the problem lies with web2py and is easily fixed. All
web2py needs to do when parsing dates is to treat a null and an empty
string the same way!

John Heenan

[mdipierro]

Thanks for the explanation. It helps clarify a long standing problem.
I have a solution in trunk based on your comment. Please check it and
let us know if this is what you had in mind.

Massimo

[John Heenan]

Yes, the bug is now fixed.

I checked out the current mercurial trunk for web2py Version 1.79.2
(2010-06-08 16:40:21)

All I had to do to confirm the bug was fixed was change a web2py
db.Field type from a 'char' type to a 'date' type and confirm database
administration worked for the relevant table of an uploaded
application.

Thanks for the quick change.

Seems surreal that such a subtle data integrity bug could be fixed in
this manner!

John Heenan