[MDEV-10220] Update Command not working properly. - Jira

Details

Type: Bug
Status: Closed (View Workflow)
Priority: Major
Resolution: Won't Fix
Affects Version/s: 10.1.13
Fix Version/s: N/A
Component/s: Data Manipulation - Update
Labels:
- update
Environment:

Hide
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

Show
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?

Attachments

Activity

Ascending order - Click to sort in descending order

Sergei Golubchik added a comment - 2016-06-13 07:48

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.

Sergei Golubchik added a comment - 2016-06-13 07:48 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.

sachin setiya (Inactive) added a comment - 2016-06-13 09:58

Can we correct this deficiency.

sachin setiya (Inactive) added a comment - 2016-06-13 09:58 Can we correct this deficiency.

Sergei Golubchik added a comment - 2016-06-13 11:22

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!

Sergei Golubchik added a comment - 2016-06-13 11:22 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!

People

Assignee:: Sergei Golubchik

Reporter:: sachin setiya (Inactive)

Votes:: 0 Vote for this issue

Watchers:: 3 Start watching this issue

Dates

Created:: 2016-06-13 03:47

Updated:: 2016-06-13 11:22

Resolved:: 2016-06-13 07:48

Git Integration

Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.

MariaDB Server