Is it just me, or is Sqlite3 goofy?

[Paul Boddie]

Fredrik Lundh wrote:
> Steve Holden wrote:
>
> > Sure. But if you go back to the start of the thread you'll remember the
> > OP was originally complaining that SQLite was being promoted in the
> > Python docs as SQL compliant. It clearly isn't if its response to the
> > insertion of a data value that conflicts with the declared column type
> > is to store a value whose type is something else.
>
> the standard actually says "If the value of any input parameter provided
> by the SQL-agent falls outside the set of allowed values of the data
> type of the parameter /.../ the effect is implementation-defined" so
> that's perfectly SQL92 compliant.

To be fair, that text originates in section 12.3, referring to input
parameters to procedures. Meanwhile, the following text (subclause
13.8, "<insert statement>") appears to be more pertinent:

"If the data type of the target identified by the i-th <column name> is
an exact numeric type, then the data type of the i-th item of the
<insert statement> shall be an exact numeric type."

I have used SQLite (releases 2 and 3) to my satisfaction, aware of the
"common knowledge" around the limitations (or features) of SQLite with
respect to data types. I'd agree with the complainant that the
behaviour of SQLite isn't what one would expect, although I started my
relational database experience using an Oracle database system and
admit that I may have been spoilt, thus saving Mr Holden - a
self-confessed Yorkshireman, I believe - the effort involved in
pointing out the relative luxury of my professional upbringing. ;-)

Paul