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.