[MDEV-31785] UPDATE table_name SET column1 = value1 and column2 = value2 does not throw error Created: 2023-07-27  Updated: 2023-08-15  Resolved: 2023-07-31

Status: Closed
Project: MariaDB Server
Component/s: Parser
Affects Version/s: 10.5, 10.6
Fix Version/s: N/A

Type: Bug Priority: Major
Reporter: Allen Lee (Inactive) Assignee: Unassigned
Resolution: Not a Bug Votes: 0
Labels: 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.

 Comments   
Comment by Elena Stepanova [ 2023-07-27 ]

There is no syntax error in the expression, that's why it is accepted. It just works differently from what one intuitively expects.

UPDATE tbl SET a = (3 AND b = 3)

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.

Generated at Thu Feb 08 10:26:27 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.