2017-01-16
| ||
18:10 | Back out check-in [0b3174e0b1364c] and replace it with a better fix for \ticket [91e2e8ba6ff2e2] - a fix that does not cause the problem identified by ticket [7ffd1ca1d2ad4ec]. Add new test cases for both tickets. (check-in: 9b64af7b user: drh tags: trunk) | |
16:43 | Back out check-in [0b3174e0b1364c] and replace it with a better fix for ticket [91e2e8ba6ff2e2] - a fix that does not cause the problem identified by ticket [7ffd1ca1d2ad4ec]. (Closed-Leaf check-in: 06136652 user: drh tags: automatic-index-affinity) | |
16:01 | Add test cases for tickets [91e2e8ba6ff2e2] and [7ffd1ca1d2ad4ec]. (check-in: 9d0dfe0b user: drh tags: automatic-index-affinity) | |
2011-09-19
| ||
17:37 | • Closed ticket [91e2e8ba]: Automatic indices cause undesirable type conversions (artifact: 7e156286 user: drh) | |
2011-06-23
| ||
17:29 | Add a bit to the SQLITE_TESTCTRL_OPTIMIZATIONS setting that will disable affinity when writing to any index, regardless of whether or not the index is on a manifestation of a view. This allows better testing of the fix for ticket [91e2e8ba6ff2e2]. (check-in: b61a76a5 user: drh tags: trunk) | |
16:40 | Add a test for ticket [91e2e8ba6f]. No changes to code. (check-in: c271f7e8 user: dan tags: trunk) | |
16:18 | • Fixed ticket [91e2e8ba]: Automatic indices cause undesirable type conversions plus 2 other changes (artifact: 74799351 user: drh) | |
16:18 | Do not do affinity transformations on inserts into an index for the manifestation of a view or subquery. Fix for ticket [91e2e8ba6ff2e2]. (check-in: 0b3174e0 user: drh tags: trunk) | |
16:17 | • New ticket [91e2e8ba] Automatic indices cause undesirable type conversions. (artifact: 43bb4571 user: drh) | |
Ticket Hash: | 91e2e8ba6ff2e2b2abd93c60f3b87f093639ad7d | |||
Title: | Automatic indices cause undesirable type conversions | |||
Status: | Closed | Type: | Code_Defect | |
Severity: | Important | Priority: | Immediate | |
Subsystem: | Unknown | Resolution: | Fixed | |
Last Modified: | 2011-09-19 17:37:12 | |||
Version Found In: | 3.7.6.3 | |||
Description: | ||||
Consider the following SQL code:
CREATE TABLE customer (id INT PRIMARY KEY); CREATE TABLE apr (id INT PRIMARY KEY, apr REAL); CREATE VIEW v1 AS SELECT c.id, i.apr FROM customer c LEFT JOIN apr i ON i.id=c.id; CREATE VIEW v2 AS SELECT c.id, v1.apr FROM customer c LEFT JOIN v1 ON v1.id=c.id; INSERT INTO customer (id) VALUES (1); INSERT INTO apr (id, apr) VALUES (1, 12); INSERT INTO customer (id) VALUES (2); INSERT INTO apr (id, apr) VALUES (2, 12.01); --works as expected SELECT id, (apr / 100), typeof(apr) apr_type FROM v1; --wrong type value for "apr" SELECT id, (apr / 100), typeof(apr) apr_type FROM v2; The two queries at the end should give identical results, however the second query returns apr as an integer when it can be expressed as an integer, which is incorrecct. The problem was originally reported on the mailing list by Mark Brand. The problem was introduced by automatic indices with check-in [2364313142668b]. The issue is that REAL values are automatically convert into INT if they can be prior to storage in tables an indices. This saves storage space since INT is typically smaller than REAL. (A integer 0 takes up 1 bytes versus 9 bytes for 0.0.) The conversion back to REAL happens automatically for real tables. But if the table is on a manifestation of a view, then the conversion back to REAL is omitted, resulting in an incorrect type on the result. The problem can be worked around by disabling automatic indices, which will prevent any indices on manifestations of views. |