Re: [sqlite] SQL Syntax fault on UPDATE statement
Effectively, Sorry about my mistake. 2016-08-17 10:33 GMT+02:00 Richard Hipp : > On 8/17/16, flo wrote: > > > > $ sqlite3 test.db "UPDATE test SET id=0 AND name='new_name' AND age=30 > > WHERE id=1;" > > The above is parsed like this: > > UPDATE test SET id = (0 AND name='new_name' AND age=30) WHERE id=1; > > And since the expression in parentheses always evaluates to 0, the > above is equivalent to: > > UPDATE test SET id=0 WHERE id=1; > > Which is exactly what SQLite is doing. > > -- > 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
Re: [sqlite] SQL Syntax fault on UPDATE statement
On 2016/08/17 11:04 AM, Simon Davies wrote: On 17 August 2016 at 09:39, R Smith wrote: On 2016/08/17 9:05 AM, flo wrote: Hi everyone, . . . 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) Boolean AND: sqlite> select 3 and 6; 1 Bitwise and: sqlite> select 3 & 6; 2 Indeed - thanks Simon. I wasn't actually thinking in SQL terms there, just trying to explain a principle - which I should have checked to be clear on to the OP. To be sure - "bitwise" isn't something else, it is still Boolean, it's just a way of doing boolean logic per bit (what makes the world of computing possible) as opposed to regarding the input as a single entity that evaluates to TRUE or FALSE. The fact that 3 and 6 both evaluate to TRUE (Because >0) in SQLite means they are regarded as entities (as they should when you use the word "AND" in stead of "&") and so rightly evaluate to TRUE (1) as above. As an aside - I come from programming languages where that difference did not exist and "(3 and 6) --> 2", and "((i>0) or 2) -> 3" where i>0, which made setting flags and such much much faster and is much better to my mind with the small caveat that you couldn't hide error-return values in Boolean guise or directly refer a return value that might have multiple possible values (but you only want to know if it is or isn't so) which is a better construct for SQL. In those days bytes were expensive. Now you can dedicate Integers or even strings and a whole column to one flag state without feeling any guilt - viva progress! :) ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQL Syntax fault on UPDATE statement
On 17 August 2016 at 09:39, R Smith wrote: > > > On 2016/08/17 9:05 AM, flo wrote: >> >> Hi everyone, . . . > 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) Boolean AND: sqlite> select 3 and 6; 1 Bitwise and: sqlite> select 3 & 6; 2 > I hope that makes it clear! > Ryan Regards, Simon ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQL Syntax fault on UPDATE statement
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
Re: [sqlite] SQL Syntax fault on UPDATE statement
Your UPDATE statement does not mean what you think it means. UPDATE test SET id=0 AND name='new_name' AND age=30 WHERE id=1; Is parsed as: UPDATE test SET id = (0 AND name='new_name' AND age=30) WHERE id=1; The expression (0 AND ...) will always evaluate to 0. -Ursprüngliche Nachricht- Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im Auftrag von flo Gesendet: Mittwoch, 17. August 2016 09:05 An: sqlite-users@mailinglists.sqlite.org Betreff: [sqlite] SQL Syntax fault on UPDATE statement 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) 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". A common SQL Update syntaxe work perfectly : $ sqlite3 test.db "UPDATE test SET id=6, name='new_name', age=30 WHERE id=2;" $ sqlite3 test.db "SELECT * FROM test;" 0|toto|10 6|new_name|30 6|new_name|30 0|toto|10 6|new_name|30 Good Luck der.nairolf ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users ___ Gunter Hick Software Engineer Scientific Games International GmbH FN 157284 a, HG Wien Klitschgasse 2-4, A-1130 Vienna, Austria Tel: +43 1 80100 0 E-Mail: h...@scigames.at This communication (including any attachments) is intended for the use of the intended recipient(s) only and may contain information that is confidential, privileged or legally protected. Any unauthorized use or dissemination of this communication is strictly prohibited. If you have received this communication in error, please immediately notify the sender by return e-mail message and delete all copies of the original communication. Thank you for your cooperation. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQL Syntax fault on UPDATE statement
On 8/17/16, flo wrote: > > $ sqlite3 test.db "UPDATE test SET id=0 AND name='new_name' AND age=30 > WHERE id=1;" The above is parsed like this: UPDATE test SET id = (0 AND name='new_name' AND age=30) WHERE id=1; And since the expression in parentheses always evaluates to 0, the above is equivalent to: UPDATE test SET id=0 WHERE id=1; Which is exactly what SQLite is doing. -- 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
[sqlite] SQL Syntax fault on UPDATE statement
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) 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". A common SQL Update syntaxe work perfectly : $ sqlite3 test.db "UPDATE test SET id=6, name='new_name', age=30 WHERE id=2;" $ sqlite3 test.db "SELECT * FROM test;" 0|toto|10 6|new_name|30 6|new_name|30 0|toto|10 6|new_name|30 Good Luck der.nairolf ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users