|
2016-11-23
| ||
| 23:49 | • Fixed ticket [199df416]: Different answer with and without index on IN operator with type mismatch plus 3 other changes (artifact: d6505cbd user: drh) | |
|
2016-09-07
| ||
| 12:20 | Correct affinity computations for a SELECT on the RHS of an IN operator. Fix for ticket [199df4168c]. (check-in: 2d1983ff user: drh tags: branch-3.14) | |
|
2016-09-03
| ||
| 15:31 | Consider the affinity of "b" when using an "a IN (SELECT b ...)" expression with an index on "a". Fix for [199df416]. (check-in: f5e49855 user: dan tags: rowvalue) | |
| 15:24 | • Ticket [199df416] Different answer with and without index on IN operator with type mismatch status still Open with 6 other changes (artifact: 6a413497 user: drh) | |
| 14:19 | • New ticket [199df416]. (artifact: 21640ea6 user: drh) | |
| Ticket Hash: | 199df4168c7bb752a5a752cb46d5d364278f0d53 | |||
| Title: | Different answer with and without index on IN operator with type mismatch | |||
| Status: | Fixed | Type: | Code_Defect | |
| Severity: | Important | Priority: | Immediate | |
| Subsystem: | Unknown | Resolution: | Fixed | |
| Last Modified: | 2016-11-23 23:49:14 | |||
| Version Found In: | 3.14.1 | |||
| User Comments: | ||||
|
drh added on 2016-09-03 14:19:59:
In the following SQL, the SELECT statement occurs twice, once before and once after the index is created. The two SELECTs give different answers. This should never be. An index will hopefully make the answer appear more quickly, but it should never change the answer. CREATE TABLE b1(a TEXT); INSERT INTO b1 VALUES(1); CREATE TABLE b2(x BLOB); INSERT INTO b2 VALUES(1); SELECT count(*) FROM b1 WHERE a IN (SELECT x FROM b2); CREATE UNIQUE INDEX b1a ON b1(a); SELECT count(*) FROM b1 WHERE a IN (SELECT x FROM b2); This problem has been in the code since before version 3.3.7 (2006-08-12), and and hence gone unnoticed for over ten years. For that reason, it is given a low severity. This problem was discovered during internal code reviews of the row-value feature planned for the 3.15 release. There have been no reports of this problem in the wild. drh added on 2016-09-03 15:24:12: This is related to [93fb9f89d6a69dc3d8], though that prior ticket dealt with joins instead of IN operators. Consider: CREATE TABLE b1(a TEXT); INSERT INTO b1 VALUES(1); CREATE TABLE b2(x BLOB); INSERT INTO b2 VALUES(1); SELECT count(*) FROM b1 WHERE a IN (SELECT x FROM b2); SELECT count(*) FROM b1, b2 WHERE a=x; CREATE UNIQUE INDEX b1a ON b1(a); SELECT count(*) FROM b1 WHERE a IN (SELECT x FROM b2); SELECT count(*) FROM b1, b2 WHERE a=x; The code above used to return "0 0 1 1" prior to check-in [e72186f2d6] which was the fix for ticket [93fb9f89d6a69dc3d8]. After that check-in (circa 2009-08-13) the answer returned was "0 0 1 0". The correct answer should be "0 0 0 0". | ||||