I expected STRICT tables to disregard data type affinity.

According to the documentation, 1. Datatypes In SQLite ... As of version 3.37.0 (2021-11-27), SQLite provides STRICT tables that do rigid type enforcement, for developers who prefer that kind of thing.

Consider my session

SQLite version 3.40.0 2022-11-16 12:10:08
Enter ".help" for usage hints.
sqlite> DROP TABLE IF EXISTS tblTranNonQuery;
sqlite> CREATE TABLE tblTranNonQuery (Fld1 INT, Fld2 TEXT,Fld3 REAL) STRICT;
sqlite> INSERT INTO tblTranNonQuery (Fld1,Fld2, Fld3) VALUES(1,'Row1',100),(2,'Row2',200);
sqlite> select * from tblTranNonQuery;
Fld1  Fld2  Fld3
----  ----  -----
1     Row1  100.0
2     Row2  200.0
sqlite> update tblTranNonQuery Set Fld3 = '12.22' where fld1 = 1;
sqlite> select * from tblTranNonQuery;
Fld1  Fld2  Fld3
----  ----  -----
1     Row1  12.22
2     Row2  200.0
sqlite>

My SQL statement:

update tblTranNonQuery Set Fld3 = '12.22' where fld1 = 1;

succeeds although Fld3 is defined as REAL and the update value '12.22' is TEXT.

This suggests that data type afinity applies to 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.

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.

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.