Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Not a Bug
-
10.5, 10.6
-
None
Description
In MariaDB, the following statement does not throw an error, but it is executed successfully
- intended query
UPDATE table_name SET column1 = value1 and column2 = value2;
- should return an error
UPDATE table_name SET column1 = value1 and column2 = value2;
Here is the example:
CREATE TABLE tbl(a INT, b INT);
|
|
|
INSERT INTO tbl VALUES(1,1),(2,2); |
|
|
SELECT * FROM tbl;
|
----------------------
|
a b
|
------ --------
|
1 1 |
2 2 |
|
UPDATE tbl SET a = 3 AND b = 3; |
|
|
|
|
SELECT * FROM tbl;
|
-------------------------
|
a b
|
------ --------
|
0 1 |
0 2 |
|
Above, a was updated with 0, which is incompatible with other databases such as SQL server or Oracle.
There is no syntax error in the expression, that's why it is accepted. It just works differently from what one intuitively expects.
It is a boolean expression. As usual, implicit casts are applied, 3 casts to TRUE, b = 3 depends on the value of b, which in your case is FALSE for both rows, thus the result is FALSE, and it is cast back to integer 0.
Maybe I'm wrong about details on what is cast when and in which direction, but that's the general idea.
Something not working in SQL server or Oracle is not necessarily a sufficient reason to consider it incorrect. As I understand, neither Oracle nor SQL server have boolean types, so the comparison with them is difficult.
Whether it's allowed by SQL standard or not would be a more applicable factor. But even if it isn't, MySQL / MariaDB have lots of extensions to the standard.
MySQL accepts the same UPDATE with the same result.
PostgreSQL doesn't, but it doesn't mind the syntax, it just doesn't cast integers to booleans and back implicitly.