[MDEV-10220] Update Command not working properly. Created: 2016-06-13  Updated: 2016-06-13  Resolved: 2016-06-13

Status: Closed
Project: MariaDB Server
Component/s: Data Manipulation - Update
Affects Version/s: 10.1.13
Fix Version/s: N/A

Type: Bug Priority: Major
Reporter: sachin setiya (Inactive) Assignee: Sergei Golubchik
Resolution: Won't Fix Votes: 0
Labels: update
Environment:

Linux anom 4.6.2-1-ARCH #1 SMP PREEMPT Wed Jun 8 08:40:59 CEST 2016 x86_64 GNU/Linux

LANG=en_US.UTF-8
COLORTERM=gnome-terminal
TERMINATOR_UUID=urn:uuid:12e3e77d-851b-45cc-9d99-9aef0efed9f8
GDM_LANG=en_US.UTF-8
TERM=xterm
LC_CTYPE=en_US.UTF-8


 Description   

Suppose a table contains data like 

    MariaDB [c]> select * from t2;
 
    +-----+
 
    | abc |
 
    +-----+
 
    |   1 |
 
    |   3 |
 
    |   5 |
 
    +-----+

Suppose my update command is 

    MariaDB [c]> update t2 set abc = abc+2;

It give following error

    ERROR 1062 (23000): Duplicate entry '3' for key 'PRIMARY'

I tried the same with oracle database but it worked fine.It seems to me that mariadb is comparing new row with old data ,which is not updated but is going to be update soon.Is it some kind of bug?

 Comments   
Comment by Sergei Golubchik [ 2016-06-13 ]

It's a documented deficiency. MariaDB updates one row at a time and does not do deferred constraint checking. So, in your example it updates the first row, tries to update abc from 1 to 3, and that immediately fails the uniqueness constraint. The standard correct behavior would've been to update all rows and only then ensure than uniqueness is not violated, but neither MariaDB nor MySQL ever supported that.

Comment by sachin setiya (Inactive) [ 2016-06-13 ]

Can we correct this deficiency.

Comment by Sergei Golubchik [ 2016-06-13 ]

Yes, but let's finish your current project first. But feel free to suggest how to fix this issue—you're very welcome to suggest a solution!

Generated at Thu Feb 08 07:40:33 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.