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
