Re: [sqlite] SQL Syntax fault on UPDATE statement

[flo]

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

[R Smith]

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

[Simon Davies]

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

[R Smith]

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

[Hick Gunter]

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

[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

[sqlite] SQL Syntax fault on UPDATE statement

[flo]

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