Ticket: https://www.sqlite.org/src/info/ef36060112a50591
On 8/26/16, Алексей Черных <[email protected]> wrote: > DELETE statement has no effect on existing data, if AND clause used and > values > are in ' escape chars. > > OS: Ubuntu 16 (updated) > > #sqlite3 -version > #3.11.0 2016-02-15 17:29:24 3d862f207e3adc00f78066799ac5a8c282430a5f > > Bug is reproduced in our database with a hundred of tables, but it is easy > to > reproduce on single table with foreign keys replaced with integer field, so > the > way to reproduce (BUG itself is on 5th step): > > 1. sqlite3 /root/testdb > > 2. CREATE TABLE aps_assign( id INT NOT NULL, aps INT NOT NULL, webdomain INT > NOT NULL, path VARCHAR(255), CONSTRAINT aps_assign_primary_key PRIMARY KEY > (id) ON CONFLICT FAIL); > > 3. INSERT INTO aps_assign (id, aps, webdomain, path) VALUES ('1', '1', > '1000', > '/path1'); > INSERT INTO aps_assign (id, aps, webdomain, path) VALUES ('2', '2', '2000', > '/path2'); > INSERT INTO aps_assign (id, aps, webdomain, path) VALUES ('3', '3', '3000', > '/path3'); > INSERT INTO aps_assign (id, aps, webdomain, path) VALUES ('4', '4', '4000', > '/path4'); > > 4. SELECT * FROM aps_assign WHERE id='4' AND webdomain='4000'; > id|aps|webdomain|path > 4|4|4000|/path4 > > 5. BUG HERE: Statement > DELETE FROM aps_assign WHERE id='4' AND webdomain='4000'; > does not delete anything > > 6. After DELETE execute: > SELECT * FROM aps_assign WHERE id='4' AND webdomain='4000'; > and see: > id|aps|webdomain|path > 4|4|4000|/path4 > record is on its place > > 7. Try to delete by fields without ' chars: > DELETE FROM aps_assign WHERE id=4 AND webdomain=4000; > SELECT * FROM aps_assign WHERE id='4' AND webdomain='4000'; > see record deleted here > > 8. Try to delete by one of fields: > DELETE FROM aps_assign WHERE id='3'; > SELECT * FROM aps_assign WHERE id='3'; > see record deleted here > > 9. Try to delete by other: > DELETE FROM aps_assign WHERE webdomain='2000'; > SELECT * FROM aps_assign WHERE webdomain='2000'; > see record deleted here > > So, we see: > If DELETE statement has '-marks and AND clause, the bug is reproduced. > > Additionally: > > SELECT * FROM aps_assign WHERE id=1 AND webdomain='1000'; > id|aps|webdomain|path > 1|1|1000|/path1 > > sqlite> DELETE FROM aps_assign WHERE id=1 AND webdomain='1000'; << this does > not delete row > sqlite> SELECT * FROM aps_assign WHERE id=1 AND webdomain='1000'; > id|aps|webdomain|path > 1|1|1000|/path1 > > sqlite> SELECT * FROM aps_assign WHERE id='1' AND webdomain=1000; > id|aps|webdomain|path > 1|1|1000|/path1 > > sqlite> DELETE FROM aps_assign WHERE id='1' AND webdomain=1000; << this > deletes row > sqlite> SELECT * FROM aps_assign WHERE id='1' AND webdomain=1000; << record > deleted > _______________________________________________ > sqlite-users mailing list > [email protected] > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > -- D. Richard Hipp [email protected] _______________________________________________ sqlite-users mailing list [email protected] http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
