`Text` won't round-trip for integer values

[goertzenator]

When saving a Text containing an integer (ie, mytxt = "200") to a field in a table, reading the Text back will produce the following error:

ConversionFailed {errSQLType = "INTEGER", errHaskellType = "Text", errMessage = "need a text"}

To troubleshoot this I read the problem field back as SQLData and got SQLInteger 200... so Texts that look like integers get saved as integers, and FromField Text cannot handle this.

This is a nasty bug: consider an existing application that has a "First Name" field. If you enter "123" the database will be poisoned with an SQLInteger value and then the app will fail upon reading it back.

At a minimum, Text should always save as SQLText regardless of content. Optionally FromField Text could be made more flexible to accept SQLInteger and other types.

[goertzenator]

Here's a patch I'm using to cope with a database that has already been poisoned with Integer values in what is supposed to be a text column.

goertzenator@016f02b

[sigrlami]

@goertzenator can you make a PR? I'll test it over the weekend

[goertzenator]

I did some experimentation with storing strings like "123" and it seems to be the underlying C sqlite library itself that is converting those strings to number types.

My method involves dumping a sqlite database with the shell strings command. Data saved as strings is easily visible, data saved as integers is not. Using the sqlite3 program I wrote the values "hello123" and "123" to the same STRING column. strings showed "hello123", but "123" was absent.

[goertzenator]

Further investigation has revealed the root cause of all my headaches. One thing I never mentioned is how I created these tables that are causing me problems:

CREATE TABLE books (name STRING)

It turns out STRING is not a valid SQL data type (I should have used TEXT). Sqlite will happily accept this type without error, and any column that ends up with an unrecognized type will get NUMERIC affinity :( ... and that is why my strings were saving as integers.

Using the STRICT table creation option is supposed to catch this. Lesson learned.