SQLite Forum

COUNT statement returns inconsistent values when used with UNION ALL
I would expect that the join result is 4 rows and the count should be 4.

v0.v1 has the affinity text
v2.v3 has two rows, one a '0' with affinity text and the other with the integer 0 with no affinity.

joining v2 to itself means that a.v3 == b.v3.  However, v3 (which comes from v2) has affinity text so the comparison, with affinity, results in the '0' == 0 with affinity text, which is true; so there are four rows.

The count is, however, wrong.  It should be 4.

That is, unless v2.v3 has no affinity, in which case the result should two rows.