This is not a bug. The relevant documentation is the third paragraph of [the datatype documentation section 3.3][1], which I quote here: > When the SELECT statement that implements a VIEW or FROM-clause subquery is a compound SELECT then the affinity of each supposed column of the VIEW or subquery will be the affinity of the corresponding result column for one of the individual SELECT statements that make up the compound. However, it is indeterminate which of the SELECT statements will be used to determine affinity. Different constituent SELECT statements might be used to determine affinity at different times during query evaluation. The choice might vary across different versions of SQLite. The choice might change between one query and the next in the same version of SQLite. The choice might be different at different times within the same query. Hence, you can never be sure what affinity will be used for columns of a compound SELECT that have different affinities in the constituent subqueries. Best practice is to avoid mixing affinities in a compound SELECT if you care about the datatype of the result. In your V2 view, the affinity of the left side of the UNION ALL is TEXT, whereas the affinity of the right side is NONE. So the affinity of the V2.V3 column is indeterminate. The query planner is free to chose whatever type affinity it wants for V2.V3. Different answers might result depending on what the query planner chooses. The query planner is transforming your "SELECT *" query into something like this: > ~~~ SELECT aa.v1 FROM V0 AS aa JOIN V0 AS bb ON (aa.v1=bb.v1) UNION ALL SELECT aa.v1 FROM V0 AS aa JOIN V0 AS bb ON ((aa.v1 IN (10))=bb.v1) UNION ALL SELECT aa.v1 FROM V0 AS aa JOIN V0 AS bb ON (aa.v1=(bb.v1 IN (10))) UNION ALL SELECT aa.v1 FROM V0 AS aa JOIN V0 AS bb ON ((aa.v1 IN (10))=(bb.v1 IN (10))); ~~~ The above query generates four rows, one for each subquery because the affinity rules always cast the result to TEXT before comparison, except on the last subquery where the result of both sides of the comparison is already an integer anyhow. But the count(*) query is transformed into the rough equivalent of: > ~~~ CREATE TEMP TABLE mx AS SELECT v3 FROM v2; SELECT count(*) FROM mx AS aa JOIN mx AS bb ON (aa.v3=bb.v3); ~~~ In this case, the affinity of the mx.v3 column is BLOB, So the 0='0' and '0'=0 comparisons fail. Only the 0=0 and '0'='0' cases work, and so the result is two rows. Yes, this is confusing. That is why we say: "Best practice is to avoid mixing affinities in a compound SELECT...." But it does conform to the documented behavior, and hence is not a bug. [1]: https://sqlite.org/datatype3.html#column_affinity_for_views_and_subqueries