On 2016/08/17 9:05 AM, flo wrote:
Hi everyone,
I found a reproducible bug on the SQL UPDATE statement parsing. Here is the
details.
I 've try to update some data on a SQLite database with a outlandish syntax
with "AND" between the columns to be update. The SQL didn't fail but the
data update was incomplete.
The SQLite version :
$ sqlite3 -version
3.13.0 2016-05-18 10:57:30 fc49f556e48970561d7ab6a2f24fdd7d9eb81ff2
The data base schema :
$ sqlite3 test.db ".schema"
CREATE TABLE test(id interger, name varchar, age integer)
INTEGER is spelt wrong here (for id) - won't be a problem in this case,
but might cause other non-expected things.
The initial stat of the table :
$ sqlite3 test.db "SELECT * FROM test;"
1|toto|10
2|tita|10
2|tita|10
1|toto|10
2|tita|10
The oulandish SQL UPDATE :
$ sqlite3 test.db "UPDATE test SET id=0 AND name='new_name' AND age=30
WHERE id=1;"
The stat of the table after the outlandish update :
$ sqlite3 test.db "SELECT * FROM test;"
0|toto|10
2|tita|10
2|tita|10
0|toto|10
2|tita|10
==> The "id" column was updated but not the two other columns "name" and
"age".
There is nothing outlandish about this, it's a normal statement and
reads like normal SQL to the parser - I think all that happened is maybe
your expected meaning is not aligned with what the Parser sees. Firstly,
AND is a boolean operator in SQL, not a concatenation or grouping
mechanism, for that we need comma - To try and explain, let me first
show the correct way to update:
UPDATE test SET id = 0, name = 'new_name', age = 30 WHERE id = 1;
That must work correctly.
So why did it not fail then?
Well, it is perfectly valid to give boolean operations as an expression.
If I said " id = 3 AND 6 then the resulting value would be 2 (If you
are unsure why that is you need to read up on Boolean logic, check
google for it)
Also for evaluating booleans I could go if this_is_true AND this_is_true
AND _this_is_true then I will get a return value that is true (1) if
all three are true, and false (0) otherwise.
What happened in your case is it checked whether id is 0 AND name is
'new_name' AND age is 30... which of course it wasn't, so it returned 0
(false) and so updated your id with a 0 value where id was 1.
Perfectly behaving as expected.
I hope that makes it clear!
Ryan
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
