9

In SQLite the following statement would be successful and the string would be inserted/updated into the SALARY column which is of type INTEGER:

update employee set salary='TOO MUCH' where emp_id=1;

Note that zero will not be inserted/updated but the actual "TOO MUCH" string, so this is not about authomatic type conversion.

The FAQ states:

This is a feature, not a bug. SQLite uses dynamic typing. It does not enforce data type constraints. Data of any type can (usually) be inserted into any column. You can put arbitrary length strings into integer columns, floating point numbers in boolean columns, or dates in character columns. The datatype you assign to a column in the CREATE TABLE command does not restrict what data can be put into that column. Every column is able to hold an arbitrary length string. (There is one exception: Columns of type INTEGER PRIMARY KEY may only hold a 64-bit signed integer. An error will result if you try to put anything other than an integer into an INTEGER PRIMARY KEY column.)

So this behaviour is clearly intentional, nevertheless I wonder why SQLite has this behaviour, since most other SQL databases I know of behave quite differently, they would rise an error, or convert the string 0, when trying to insert a non-numeric string into a numeric column.

  • Would the SQLite library be less useful without this behaviour?

  • Is this made so by design to keep the library small and fast?

  • Would the SQLite library be significantly slower or bigger in order to rise errors when triying to insert a string into a numeric column?

  • 9
    Someone once said that "a feature is a bug as described by the marketing department". – Dan Pichelman Apr 6 '15 at 18:31
  • 3
    I doubt it's good for performance and data-density, though it might be beneficial to code-size. All in all, I would call it an intentional (or at lest adopted) mis-feature. – Deduplicator Apr 6 '15 at 18:38
  • 3
    "It seems to me an imposed limitation in order to remain a small-size, low-resources library usually used for embeded system that require real time performance..." I can't imagine a type check being anything more than a drop in the time/space performance bucket for operations that do any amount of disk IO. Also they have to have additional checks in the code since they can't simply assume the data's going to have a fixed size, so arguably the dynamic typing costs you performance. – Doval Apr 6 '15 at 18:38
  • 1
    @DocBrown Not cause I say so but because it's sui generis. – Tulains Córdova Apr 6 '15 at 18:42
  • 2
    @user61852 I suggest you rewrite the question completely and focus on whether SQLite's dynamic typing would give it performance benefits, and omit any mention of the feature/bug distinction or the general usefulness/uselessness of dynamic typing in X or Y context. – Doval Apr 6 '15 at 18:46
7

No, dynamic typing requires both more storage space and more processing time, especially since they also add type affinity, meaning it has a preferred type that the programmer is free to ignore. It is truly an intentional feature with real trade off costs. Those costs are effectively negligible for the use cases SQLite targets, but they are still there.

The usefulness of such features is hard to see because you're not accustomed to having it available to you. The workaround for its lack feels more natural to you now. Because of your prior experience, you're thinking of it as an INTEGER field which shall never be anything else, but SQLite sees it more as a field of any type, but probably will mostly contain integers. Perhaps it is a ZIP code for a company that mostly does business in the U.S., but has a handful of Canadian customers. Allowing the user to specify an integer affinity will save a lot of space over making it a string in every row, but still gives you that option.

  • 3
    I wrote an update to my question about successfully storing the string "TOO MUCH" into a numeric colum. Automatic conversion would insert zero. Also this is the first relational database implementation that I know that would allow that. I've worked with MSSQLServer, Oracle, PostgreSQL, MySQL, MS Acces, DBase, Foxbase, COBOL and Sybase SQL Anywhere. – Tulains Córdova Apr 6 '15 at 19:02
  • 2
    I don't understand your comment. Nothing about my answer had anything to do with automatic conversion. – Karl Bielefeldt Apr 6 '15 at 19:08
  • 1
    SQLite considers them storage classes instead of datatypes. sqlite.org/datatype3.html – JeffO Apr 6 '15 at 22:45
  • 1
    Upvoted for being clearly written, but you are ignoring the problems this causes in proving data accuracy. You can't pull some integers out of your DB and assume that they will be integers. ("Dynamic typing" is wildly at odds with the actual relational model itself.) – Wildcard Jan 22 '17 at 14:29
  • 1
    The costs of completely ignoring typing are far higher than a little storage space and processor time. – DeadMG Jan 22 '17 at 15:32

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.