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
