What makes you believe that '12.22' is text? (or string)? It looks like a number to me.

Is it the quotes perhaps? Quoting a numeric value is very much allowed and works in any SQL engine[1]. Try it. Insert/Export scripts, Data Frameworks, etc. will have a hard time without this capability.

You'd have a reason to bemoan the situation if the value was '12.abc' perhaps. That should definitely not work, but 12.22 and '12.22' both are perfectly legal numbers.

[1] At least those I use, such as MySQL/MariaDB, PostGres, MSSQL

What makes you believe that '12.22' is text? (or string)? It looks like a number to me.

It does not look like a number to me and SQLite agrees:

sqlite> select '12.22' = 12.22 as CompareText_REAL ;
CompareText_REAL
----------------
0

Anything within single quotes is a STRING or TEXT value in any SQL.

Quoting a numeric value is very much allowed and works in any SQL engine

You cite MSSQL among them but

select 10 * '12.22';

fails in MSSQL with message

Conversion failed when converting the varchar value '12.22' to data type int.

Anything within single quotes is a STRING or TEXT value in any SQL.

And a number is allowed to be quoted (or if you will, represented in string form) in the DDL of any SQL.

Here is your script with minimal modifications to allow running in MySQL:

DROP TABLE IF EXISTS tblTranNonQuery;
CREATE TABLE tblTranNonQuery (Fld1 INT, Fld2 TEXT, Fld3 FLOAT);
INSERT INTO tblTranNonQuery (Fld1,Fld2, Fld3) VALUES
(1,'Row1',100),
(2,'Row2',200);
UPDATE tblTranNonQuery SET Fld3 = '12.22' WHERE fld1 = 1;
SELECT * FROM tblTranNonQuery;
-- Fld1 | Fld2 | Fld3
-- -----|------|------
--    1 | Row1 | 12.22
--    2 | Row2 |   200

And here it is for MSSQL:

CREATE TABLE tblTranNonQuery (Fld1 INT, Fld2 TEXT, Fld3 FLOAT);
INSERT INTO tblTranNonQuery (Fld1,Fld2, Fld3) VALUES
(1,'Row1',100),
(2,'Row2',200);
UPDATE tblTranNonQuery SET Fld3 = '12.22' WHERE fld1 = 1;
SELECT * FROM tblTranNonQuery;
DROP TABLE tblTranNonQuery;
-- Fld1	Fld2	Fld3
-- 1	Row1	12,22
-- 2	Row2	200

Try it yourself - it works just fine.

This is turning into noise given that my post simply requested:

The documentation ought to make it clear that STRICT tables do rigid type enforcement after allowing for implicit casting resulting from data type affinity considerations.

SQLite coerces data into the correct type (where possible) at the point of interpreting the DDL:

1. Like every other SQL out there (as I've shown), and
2. As is already noted in the documentation

(See Point No. 3 under "2. STRICT Tables")

The documentation ought to make it clear that STRICT tables do rigid type enforcement after allowing for implicit casting resulting from data type affinity considerations.

Section 2, paragraph 3 of the relevant and readily discoverable doc on STRICT tables states:

SQLite attempts to coerce the data into the appropriate type using the usual affinity rules, as PostgreSQL, MySQL, SQL Server, and Oracle all do. If the value cannot be losslessly converted in the specified datatype, then an SQLITE_CONSTRAINT_DATATYPE error is raised.

That makes quite clear the very condition ("after allowing for ...") that you seem to believe needs clarification. In fact, it is even more clear when read in its context. I see no room for improvement in the language of Section 2.

This is turning into noise given that my post simply requested:

The other posts did not resemble noise at all, in my view. I suggest that you develop a different expectation as to what answers you should get here, and especially a different attitude regarding what answers are useful. People answering are trying to help, and often that means addressing issues that go beyond the narrow confines of the exact question you posed. I think you would do better to understand why the answers you get are thought relevant than to suggest that other people's posts are mere "noise". That is a gross and somewhat insulting characterization. It is inappropriate here.

... It is inappropriate here.

My original post referred to 1. Datatypes In SQLite found at https://sqlite.org/search?s=d&q=STRICT, noted that SQLite provides STRICT tables that do rigid type enforcement (as stated) is not literally true and that affinity rules are at play; I suggested clarification to make this self-evident.

In my view, detailed responses suggesting that verbatim numbers and the same numbers inside single quotes are identical in several SQL dialects notably MSSQL is noise.

I'll summarize here, to support the points I'll make below.

a. SQLite's STRICT table feature allows lossless conversions to be done in order to update a typed column or insert into it.

b. This behavior is clearly documented¹, (as I mentioned earlier.)

c. The said documentation mentions that this is done in the way that several other DBMSs do it.

d. Other posters here have pointed out this same behavioral similarity.

e. You claim that this language, "SQLite provides STRICT tables that do rigid type enforcement, for developers who prefer that kind of thing.", is misleading because the DML execution does not raise an error when you provide data which can be losslessly coerced into the declared datatype in a STRICT table.

f. You dismiss talk of cross-DBMS compatibility and clear documentation of the behavior as "noise", I surmise because it does not address your most important point; that being the absence or laxity of the claimed "rigid type enforcement".

I can see that there is a noise issue here. But I do not see that others are the main contributors. I see that you have over-interpreted the phrase, "rigid type enforcement", to mean something beyond what the words require and contrary to the clear documentation. A perfectly reasonable interpretation of "rigid type enforcement", which is consistent with the docs and consistent with other DBMSs, is that what is rigidly enforced is the type of the data that is actually stored in the DB. Just because you expect errors that are nowhere mentioned in the docs, would be contrary to the detailed documentation of STRICT tables, and would be at odds with how other DBMSs accomplish rigid type enforcement, does not mean that you have been misled. It only means that you have too rigidly adopted an overly specific and incorrect interpretation of a very brief mention of the STRICT table feature, a mention which links directly to the fully specific and detailed description of the feature. That mention is appropriately brief, and exploding it into a detailed description, when such a description is one click away, would be a disfavor to readers who visit the Datatypes page because they wish to learn about datatypes in SQLite. The disfavor would amount to an SNR reduction.

---

1. ^{^} From STRICT Tables: Content inserted into the column with a datatype other than ANY must be either a NULL (assuming there is no NOT NULL constraint on the column) or the type specified. SQLite attempts to coerce the data into the appropriate type using the usual affinity rules, as PostgreSQL, MySQL, SQL Server, and Oracle all do. If the value cannot be losslessly converted in the specified datatype, then an SQLITE_CONSTRAINT_DATATYPE error is raised.

A perfectly reasonable interpretation of "rigid type enforcement", which is consistent with the docs and consistent with other DBMSs, is that what is rigidly enforced is the type of the data that is actually stored in the DB.

I do NOT see the consistency you allude to.

sqlite> select * from tblTranNonQuery;
Fld1  Fld2  Fld3
----  ----  -----
1     Row1  100.0
2     Row2  200.0
sqlite> update tblTranNonQuery set fld2 = 'Revised2' where 100.0='100.0'; /* fld3 has 100.0 as a value*/
sqlite> select * from tblTranNonQuery;
Fld1  Fld2  Fld3
----  ----  -----
1     Row1  100.0
2     Row2  200.0
sqlite> /* No change */
sqlite> select '10.22' = 10.22;
'10.22' = 10.22
---------------
0
sqlite> /* Result is false*/
sqlite>

`

I don't see what that has to do with the price of beans.

This would at least be tangential to the thread's topic: sqlite> create table t(i int, s text, r real) strict; sqlite> insert into t values(1, 'one', '1.0'); sqlite> insert into t values(2, 'two', '2.0'); sqlite> update t set s='truly '||s where r=1.0; sqlite> update t set s='really '||s where r=2.0; sqlite> .header on sqlite> select * from t; i|s|r 1|truly one|1.0 2|really two|2.0 sqlite> update t set s='very '||s where r='1.0'; sqlite> select * from t; i|s|r 1|very truly one|1.0 2|really two|2.0 sqlite> select * from t where '1.0'=r; i|s|r 1|very truly one|1.0

It does not look like a number to me and SQLite agrees:

sqlite> select '12.22' = 12.22 as CompareText_REAL ;

Your example is an expression and has nothing to do with the affinity of table columns - it omits any conversion act (casting) during the insert or an update.

To show the type from the strict table column one can use the sql function typeof(expr) .

Your example is an expression and has nothing to do with the affinity of table columns

Refer to the session shown below:

1. FLD3 is of type real
2. '10.22' is of type text
3. tblTranNonQuery is a STRICT table
4. The SQL

update tblTranNonQuery set fld3='10.22';

is assigning a value of type text to a column of type real in a table that is STRICT (rigid type enforcement), because the text value is a real within quotes .. is that not affinity?

Besides, the issue is nothing to do with affinity, it is to do with the specific documentation I referred to not mentioning anything about affinity to make matters more self-evident.

1. With

select '12.22' = 12.22 as CompareText_REAL ;

SQLite does NOT resolve '12.22' (text) as 12.22 (real) but with

update tblTranNonQuery set fld3='10.22';

SQLite is able to resolve '12.22' as a real value.

The session:

sqlite> select distinct(typeof(fld3)) from tblTranNonQuery;
(typeof(fld3))
--------------
real
sqlite> select typeof('10.22');
typeof('10.22')
---------------
text
sqlite> update tblTranNonQuery set fld3='10.22';
sqlite> select * from tblTranNonQuery;
Fld1  Fld2  Fld3
----  ----  -----
1     Row1  10.22
2     Row2  10.22
sqlite>

The paragraph 2.3 (complete description) is clear -at least to me- in how the conversion by insert/update into a strict table column on non-ANY-type is done:

Content inserted into the column with a datatype other than ANY must be either a NULL (assuming there is no NOT NULL constraint on the column) or the type specified.

SQLite attempts to coerce the data into the appropriate type using the usual affinity rules, as PostgreSQL, MySQL, SQL Server, and Oracle all do.

If the value cannot be losslessly converted in the specified datatype, then an SQLITE_CONSTRAINT_DATATYPE error is raised.

The right word for what happens during insert/update is coerce rather than cast; what looks like a number is taken as a number for a strict table column that should be of strict type number.

Aask was complaining (though this is perhaps not clear since the link they provided is incorrect) that the data types doc is misleading and should be clarified where it references strict tables.

Personally I'm not sure I agree. It's a note in passing, not an authoritative description (which is linked to in that paragraph). Maybe it could be reworded to satisfy Aask's concerns is to what strictness means in this context, though.