Ticket: https://www.sqlite.org/src/info/ef36060112a50591
On 8/26/16, Алексей Черных 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
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
--
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
