[MDEV-28883] Re-design the upper level of handling UPDATE and DELETE statements - Jira

Details

Type: Technical task
Status: Closed (View Workflow)
Priority: Major
Resolution: Fixed
Affects Version/s: None
Fix Version/s: 11.1.0
Component/s: Data Manipulation - Delete, Data Manipulation - Update
Labels:

Description

This sub-task is aimed at a new design of top level processing of UPDATE and DELETE statements. This design is described in MDEV-27159.

Attachments

Issue Links

blocks

MCOL-4740 UPDATE returns wrong "Rows matched" on multi-tables

Closed

MDEV-17954 multi-table DELETE statement with the same source and target in subselect

Confirmed

causes

MDEV-28965 Assertion failure when preparing UPDATE with derived table in WHERE

Closed

MDEV-29174 Assertion failure with UPDATE of view using MERGE table

Closed

MDEV-29189 Crash of the second execution of SF using DELETE/UPDATE

Closed

MDEV-29428 Incorrect result for delete with "order by" clause

Closed

MDEV-32934 ASAN use-after-poison in MYSQL_DML_DONE()

Stalled

MDEV-33121 ER_INVALID_GROUP_FUNC_USE not thrown for DELETE with group function

Confirmed

MDEV-33790 Incorrect DEFAULT expression evaluated in UPDATE

Closed

MDEV-34819 THD::save_prep_leaf_tables is unused

Open

relates to

MDEV-20250 Implement early partition pruning before statement start

In Progress

MDEV-33121 ER_INVALID_GROUP_FUNC_USE not thrown for DELETE with group function

Confirmed

MDEV-33533 Crash at execution of DELETE when trying to use rowid filter

Closed

MDEV-35402 Dead code: save_prep_leaf_tables() functions and leaf_tables_prep

Confirmed

(5 causes, 4 relates to)

Activity

Ascending order - Click to sort in descending order

Sergei Golubchik added a comment - 2022-06-27 17:21

please, use bb-10.10-~~MDEV-28883~~ for testing

Sergei Golubchik added a comment - 2022-06-27 17:21 please, use bb-10.10- MDEV-28883 for testing

Roel Van de Paar added a comment - 2022-06-29 09:47

igor hi! The second testcase from MDEV-22783:

CREATE TABLE t0 (a INT) ENGINE=InnoDB PARTITION BY KEY(a);

INSERT INTO t0 VALUES (0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0);

DELETE FROM t0 WHERE a=(SELECT * FROM t0 WHERE a=0);

Produces a somewhat different stack on bb-10.10-MDEV-28883:

10.10.0 2db18fdb3d68d906fbd188ec570a64502ba55849 (Debug)
Core was generated by `/test/MDEV-28883_MD280622-mariadb-10.10.0-linux-x86_64-dbg/bin/mysqld --no-defa'.
Program terminated with signal SIGABRT, Aborted.
#0 __GI_raise (sig=sig@entry=6) at ../sysdeps/unix/sysv/linux/raise.c:50
[Current thread is 1 (Thread 0x1512576e4700 (LWP 515593))]
(gdb) bt
#0 __GI_raise (sig=sig@entry=6) at ../sysdeps/unix/sysv/linux/raise.c:50
#1 0x000015128fca5859 in __GI_abort () at abort.c:79
#2 0x000015128fca5729 in __assert_fail_base (fmt=0x15128fe3b588 "%s%s%s:%u: %s%sAssertion `%s' failed.\n%n", assertion=0x55930ca7fa08 "!is_set() \|\| (m_status == DA_OK_BULK && is_bulk_op())", file=0x55930ca7f8d8 "/test/bb-10.10-MDEV-28883_dbg/sql/sql_error.cc", line=335, function=<optimized out>) at assert.c:92
#3 0x000015128fcb6fd6 in __GI___assert_fail (assertion=assertion@entry=0x55930ca7fa08 "!is_set() \|\| (m_status == DA_OK_BULK && is_bulk_op())", file=file@entry=0x55930ca7f8d8 "/test/bb-10.10-MDEV-28883_dbg/sql/sql_error.cc", line=line@entry=335, function=function@entry=0x55930ca7fa40 "void Diagnostics_area::set_ok_status(ulonglong, ulonglong, const char*)") at assert.c:101
#4 0x000055930be5bfbd in Diagnostics_area::set_ok_status (this=0x15123c006b78, affected_rows=affected_rows@entry=0, last_insert_id=last_insert_id@entry=0, message=message@entry=0x0) at /test/bb-10.10-MDEV-28883_dbg/sql/sql_error.h:1031
#5 0x000055930be547c5 in my_ok (message=0x0, id=0, affected_rows_arg=0, thd=0x15123c000db8) at /test/bb-10.10-MDEV-28883_dbg/sql/sql_class.h:5608
#6 Sql_cmd_delete::delete_from_single_table (this=this@entry=0x15123c016220, thd=thd@entry=0x15123c000db8) at /test/bb-10.10-MDEV-28883_dbg/sql/sql_delete.cc:478
#7 0x000055930be569de in Sql_cmd_delete::execute_inner (this=0x15123c016220, thd=0x15123c000db8) at /test/bb-10.10-MDEV-28883_dbg/sql/sql_delete.cc:1726
#8 0x000055930beecf8f in Sql_cmd_dml::execute (this=0x15123c016220, thd=0x15123c000db8) at /test/bb-10.10-MDEV-28883_dbg/sql/sql_select.cc:30659
#9 0x000055930beb10c0 in mysql_execute_command (thd=thd@entry=0x15123c000db8, is_called_from_prepared_stmt=is_called_from_prepared_stmt@entry=false) at /test/bb-10.10-MDEV-28883_dbg/sql/sql_parse.cc:4386
#10 0x000055930be9dec5 in mysql_parse (thd=thd@entry=0x15123c000db8, rawbuf=<optimized out>, length=<optimized out>, parser_state=parser_state@entry=0x1512576e3460) at /test/bb-10.10-MDEV-28883_dbg/sql/sql_parse.cc:7797
#11 0x000055930beab41a in dispatch_command (command=command@entry=COM_QUERY, thd=thd@entry=0x15123c000db8, packet=packet@entry=0x15123c00b6e9 "DELETE FROM t0 WHERE a=(SELECT * FROM t0 WHERE a=0)", packet_length=packet_length@entry=51, blocking=blocking@entry=true) at /test/bb-10.10-MDEV-28883_dbg/sql/sql_class.h:1364
#12 0x000055930beadb27 in do_command (thd=0x15123c000db8, blocking=blocking@entry=true) at /test/bb-10.10-MDEV-28883_dbg/sql/sql_parse.cc:1405
#13 0x000055930c00c948 in do_handle_one_connection (connect=<optimized out>, connect@entry=0x55930f693c48, put_in_cache=put_in_cache@entry=true) at /test/bb-10.10-MDEV-28883_dbg/sql/sql_connect.cc:1418
#14 0x000055930c00ce51 in handle_one_connection (arg=0x55930f693c48) at /test/bb-10.10-MDEV-28883_dbg/sql/sql_connect.cc:1312
#15 0x00001512901b6609 in start_thread (arg=<optimized out>) at pthread_create.c:477
#16 0x000015128fda2133 in clone () at ../sysdeps/unix/sysv/linux/x86_64/clone.S:95

i.e. the stack differs for example in mysql_delete vs Sql_cmd_delete::delete_from_single_table and Sql_cmd_delete::execute_inner.
I assume this is expected? Please check.

Roel Van de Paar added a comment - 2022-06-29 09:47 igor hi! The second testcase from MDEV-22783 : CREATE TABLE t0 (a INT ) ENGINE=InnoDB PARTITION BY KEY (a); INSERT INTO t0 VALUES (0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0); DELETE FROM t0 WHERE a=( SELECT * FROM t0 WHERE a=0); Produces a somewhat different stack on bb-10.10- MDEV-28883 : 10.10.0 2db18fdb3d68d906fbd188ec570a64502ba55849 (Debug) Core was generated by `/test/MDEV-28883_MD280622-mariadb-10.10.0-linux-x86_64-dbg/bin/mysqld --no-defa'. Program terminated with signal SIGABRT, Aborted. #0 __GI_raise (sig=sig@entry=6) at ../sysdeps/unix/sysv/linux/raise.c:50 [Current thread is 1 (Thread 0x1512576e4700 (LWP 515593))] (gdb) bt #0 __GI_raise (sig=sig@entry=6) at ../sysdeps/unix/sysv/linux/raise.c:50 #1 0x000015128fca5859 in __GI_abort () at abort.c:79 #2 0x000015128fca5729 in __assert_fail_base (fmt=0x15128fe3b588 "%s%s%s:%u: %s%sAssertion `%s' failed.\n%n", assertion=0x55930ca7fa08 "!is_set() || (m_status == DA_OK_BULK && is_bulk_op())", file=0x55930ca7f8d8 "/test/bb-10.10-MDEV-28883_dbg/sql/sql_error.cc", line=335, function=<optimized out>) at assert.c:92 #3 0x000015128fcb6fd6 in __GI___assert_fail (assertion=assertion@entry=0x55930ca7fa08 "!is_set() || (m_status == DA_OK_BULK && is_bulk_op())", file=file@entry=0x55930ca7f8d8 "/test/bb-10.10-MDEV-28883_dbg/sql/sql_error.cc", line=line@entry=335, function=function@entry=0x55930ca7fa40 "void Diagnostics_area::set_ok_status(ulonglong, ulonglong, const char*)") at assert.c:101 #4 0x000055930be5bfbd in Diagnostics_area::set_ok_status (this=0x15123c006b78, affected_rows=affected_rows@entry=0, last_insert_id=last_insert_id@entry=0, message=message@entry=0x0) at /test/bb-10.10-MDEV-28883_dbg/sql/sql_error.h:1031 #5 0x000055930be547c5 in my_ok (message=0x0, id=0, affected_rows_arg=0, thd=0x15123c000db8) at /test/bb-10.10-MDEV-28883_dbg/sql/sql_class.h:5608 #6 Sql_cmd_delete::delete_from_single_table (this=this@entry=0x15123c016220, thd=thd@entry=0x15123c000db8) at /test/bb-10.10-MDEV-28883_dbg/sql/sql_delete.cc:478 #7 0x000055930be569de in Sql_cmd_delete::execute_inner (this=0x15123c016220, thd=0x15123c000db8) at /test/bb-10.10-MDEV-28883_dbg/sql/sql_delete.cc:1726 #8 0x000055930beecf8f in Sql_cmd_dml::execute (this=0x15123c016220, thd=0x15123c000db8) at /test/bb-10.10-MDEV-28883_dbg/sql/sql_select.cc:30659 #9 0x000055930beb10c0 in mysql_execute_command (thd=thd@entry=0x15123c000db8, is_called_from_prepared_stmt=is_called_from_prepared_stmt@entry=false) at /test/bb-10.10-MDEV-28883_dbg/sql/sql_parse.cc:4386 #10 0x000055930be9dec5 in mysql_parse (thd=thd@entry=0x15123c000db8, rawbuf=<optimized out>, length=<optimized out>, parser_state=parser_state@entry=0x1512576e3460) at /test/bb-10.10-MDEV-28883_dbg/sql/sql_parse.cc:7797 #11 0x000055930beab41a in dispatch_command (command=command@entry=COM_QUERY, thd=thd@entry=0x15123c000db8, packet=packet@entry=0x15123c00b6e9 "DELETE FROM t0 WHERE a=(SELECT * FROM t0 WHERE a=0)", packet_length=packet_length@entry=51, blocking=blocking@entry=true) at /test/bb-10.10-MDEV-28883_dbg/sql/sql_class.h:1364 #12 0x000055930beadb27 in do_command (thd=0x15123c000db8, blocking=blocking@entry=true) at /test/bb-10.10-MDEV-28883_dbg/sql/sql_parse.cc:1405 #13 0x000055930c00c948 in do_handle_one_connection (connect=<optimized out>, connect@entry=0x55930f693c48, put_in_cache=put_in_cache@entry=true) at /test/bb-10.10-MDEV-28883_dbg/sql/sql_connect.cc:1418 #14 0x000055930c00ce51 in handle_one_connection (arg=0x55930f693c48) at /test/bb-10.10-MDEV-28883_dbg/sql/sql_connect.cc:1312 #15 0x00001512901b6609 in start_thread (arg=<optimized out>) at pthread_create.c:477 #16 0x000015128fda2133 in clone () at ../sysdeps/unix/sysv/linux/x86_64/clone.S:95 i.e. the stack differs for example in mysql_delete vs Sql_cmd_delete::delete_from_single_table and Sql_cmd_delete::execute_inner . I assume this is expected? Please check.

Roel Van de Paar added a comment - 2022-07-01 23:26 - edited

Created ~~MDEV-29003~~ - ~~MDEV-28883~~: Assertion `*err == DB_SUCCESS' failed in btr_page_split_and_insert

Roel Van de Paar added a comment - 2022-07-01 23:26 - edited Created MDEV-29003 - MDEV-28883 : Assertion `*err == DB_SUCCESS' failed in btr_page_split_and_insert

Roel Van de Paar added a comment - 2022-07-07 02:23 - edited

Note: comment on MDEV-28871

Roel Van de Paar added a comment - 2022-07-07 02:23 - edited Note: comment on MDEV-28871

Roel Van de Paar added a comment - 2022-07-07 07:36 - edited

igor Hi again! Here is another testcase which crashes slightly differently between bb-10.10-~~MDEV-28883~~ and trunk.

SET sql_mode='';

CREATE TABLE t (a DATE) PARTITION BY HASH (EXTRACT(DAY FROM a));

INSERT INTO t VALUES (_ucs2 0x1ECC),(_ucs2 0x1ECD),(_ucs2 0x1ECE),(_ucs2 0x1ECF);

DELETE FROM t WHERE a=(SELECT 1 FROM t);

Trunk:

!is_set() || (m_status == DA_OK_BULK && is_bulk_op())|SIGABRT|Diagnostics_area::set_ok_status|my_ok|mysql_delete|mysql_execute_command

Which is a stack seen in MDEV-22783. On bb-10.10-~~MDEV-28883~~ it is instead:

!is_set() || (m_status == DA_OK_BULK && is_bulk_op())|SIGABRT|Diagnostics_area::set_ok_status|my_ok|Sql_cmd_delete::delete_from_single_table|Sql_cmd_delete::execute_inner

Please confirm that this is expected.

Roel Van de Paar added a comment - 2022-07-07 07:36 - edited igor Hi again! Here is another testcase which crashes slightly differently between bb-10.10- MDEV-28883 and trunk. SET sql_mode= '' ; CREATE TABLE t (a DATE ) PARTITION BY HASH (EXTRACT( DAY FROM a)); INSERT INTO t VALUES (_ucs2 0x1ECC),(_ucs2 0x1ECD),(_ucs2 0x1ECE),(_ucs2 0x1ECF); DELETE FROM t WHERE a=( SELECT 1 FROM t); Trunk: !is_set() || (m_status == DA_OK_BULK && is_bulk_op())|SIGABRT|Diagnostics_area::set_ok_status|my_ok|mysql_delete|mysql_execute_command Which is a stack seen in MDEV-22783 . On bb-10.10- MDEV-28883 it is instead: !is_set() || (m_status == DA_OK_BULK && is_bulk_op())|SIGABRT|Diagnostics_area::set_ok_status|my_ok|Sql_cmd_delete::delete_from_single_table|Sql_cmd_delete::execute_inner Please confirm that this is expected.

Roel Van de Paar added a comment - 2022-07-21 18:40

Testing update:

The original bb-10.10-~~MDEV-28883~~ branch was used for all original runs.
A filter for ~~MDEV-28965~~ was added to the original runs.
The runs had some interesting bugs, most of which proved to be unrelated to this ticket after analysis, except ~~MDEV-29003~~.
~~MDEV-28965~~ was recently fixed, and a push was pushed to bb-10.10-~~MDEV-28883~~.
- A new set of runs was initiated against this fix, with the filter for ~~MDEV-28965~~ removed.
- In the new run, an SQL file - which was interleaved with a multi-table delete query (ref MDEV-17954) to test MDEV-17954 functionality - was used
The ~~MDEV-28965~~ fix was confirmed in the updated bb-10.10-~~MDEV-28883~~ and the ~~MDEV-29003~~ crash was re-confirmed to still be present.
There are a few questions posted above for igor

Roel Van de Paar added a comment - 2022-07-21 18:40 Testing update: The original bb-10.10- MDEV-28883 branch was used for all original runs. A filter for MDEV-28965 was added to the original runs. The runs had some interesting bugs, most of which proved to be unrelated to this ticket after analysis, except MDEV-29003 . MDEV-28965 was recently fixed, and a push was pushed to bb-10.10- MDEV-28883 . A new set of runs was initiated against this fix, with the filter for MDEV-28965 removed. In the new run, an SQL file - which was interleaved with a multi-table delete query (ref MDEV-17954 ) to test MDEV-17954 functionality - was used The MDEV-28965 fix was confirmed in the updated bb-10.10- MDEV-28883 and the MDEV-29003 crash was re-confirmed to still be present. There are a few questions posted above for igor

Roel Van de Paar added a comment - 2022-07-21 18:46

Next steps;
1) Confirm if ~~MDEV-29003~~ is required to be fixed before feature release yes/no (igor)
2) Confirm second run (after ~~MDEV-28965~~ was fixed, MDEV-17954 testing) produces any new results (Roel) - ETA mid next week, depending on amount of analysis required
3) Confirm questions above (igor)

Roel Van de Paar added a comment - 2022-07-21 18:46 Next steps; 1) Confirm if MDEV-29003 is required to be fixed before feature release yes/no ( igor ) 2) Confirm second run (after MDEV-28965 was fixed, MDEV-17954 testing) produces any new results ( Roel ) - ETA mid next week, depending on amount of analysis required 3) Confirm questions above ( igor )

Roel Van de Paar added a comment - 2022-07-22 09:14

Early update on #2: The runs are not showing any new results after 10000 trials including MDEV-17954 testing, and the branch looks stable in general.
Save any action items above, the feature is ok to be released imho. I will keep an eye on the runs for a few more days, but testing is considered complete.

Roel Van de Paar added a comment - 2022-07-22 09:14 Early update on #2: The runs are not showing any new results after 10000 trials including MDEV-17954 testing, and the branch looks stable in general. Save any action items above, the feature is ok to be released imho. I will keep an eye on the runs for a few more days, but testing is considered complete.

Roel Van de Paar added a comment - 2022-07-26 17:57

The runs remained clean after 100-150k trials, except for 1-2 low frequency issues of interest. These are reducing now.

Roel Van de Paar added a comment - 2022-07-26 17:57 The runs remained clean after 100-150k trials, except for 1-2 low frequency issues of interest. These are reducing now.

Roel Van de Paar added a comment - 2022-07-26 18:23

Created ~~MDEV-29174~~: Assertion `tbl->pos_in_table_list == tables' failed in open_tables on UPDATE

Roel Van de Paar added a comment - 2022-07-26 18:23 Created MDEV-29174 : Assertion `tbl->pos_in_table_list == tables' failed in open_tables on UPDATE

Roel Van de Paar added a comment - 2022-07-28 09:27

Created ~~MDEV-29189~~ SIGSEGV in Sql_cmd_delete::delete_from_single_table

Roel Van de Paar added a comment - 2022-07-28 09:27 Created MDEV-29189 SIGSEGV in Sql_cmd_delete::delete_from_single_table

Roel Van de Paar added a comment - 2022-07-28 12:14

This rounds up my analysis of the second test run after ~~MDEV-28965~~ was fixed.
IMHO it would be best to do a new test run after the bugs added are fixed if time permits.

Roel Van de Paar added a comment - 2022-07-28 12:14 This rounds up my analysis of the second test run after MDEV-28965 was fixed. IMHO it would be best to do a new test run after the bugs added are fixed if time permits.

Roel Van de Paar added a comment - 2022-08-05 11:26

After the fixes for ~~MDEV-29174~~ and ~~MDEV-29189~~ I have started a 3rd testrun against the updated bb-10.10-~~MDEV-28883~~ branch.

Roel Van de Paar added a comment - 2022-08-05 11:26 After the fixes for MDEV-29174 and MDEV-29189 I have started a 3rd testrun against the updated bb-10.10- MDEV-28883 branch.

Roel Van de Paar added a comment - 2022-08-06 01:01

After ~11k trials in bb-10.10-~~MDEV-28883~~ (v3) branch, there are only 1-2 new issues showing: reducing the same.

Roel Van de Paar added a comment - 2022-08-06 01:01 After ~11k trials in bb-10.10- MDEV-28883 (v3) branch, there are only 1-2 new issues showing: reducing the same.

Roel Van de Paar added a comment - 2022-08-09 02:11 - edited

Over a few days, a few additional issues showed and some are highly sporadic. Not sure if there is any connection yet.
In general, I remain of the opinion that this branch looks very clean to start with, of good quality, and I have had no objections to release as of 2022-07-22.

Roel Van de Paar added a comment - 2022-08-09 02:11 - edited Over a few days, a few additional issues showed and some are highly sporadic. Not sure if there is any connection yet. In general, I remain of the opinion that this branch looks very clean to start with, of good quality, and I have had no objections to release as of 2022-07-22.

Roel Van de Paar added a comment - 2022-08-10 12:26 - edited

MDEV-29287, ~~MDEV-29291~~ and MDEV-29331 were discovered during testing of this feature, however they proved to exist in main trees also.

Roel Van de Paar added a comment - 2022-08-10 12:26 - edited MDEV-29287 , MDEV-29291 and MDEV-29331 were discovered during testing of this feature, however they proved to exist in main trees also.

Roel Van de Paar added a comment - 2022-08-19 11:59 - edited

~~Created ~~MDEV-29334~~ Assertion `*err == DB_SUCCESS' failed in btr_page_split_and_insert on INSERT~~
> This is a new variation of ~~MDEV-29003~~/~~MDEV-28950~~ it looks like.
This was the last bug in my list of highly sporadic bugs to analyze.

Roel Van de Paar added a comment - 2022-08-19 11:59 - edited Created MDEV-29334 Assertion `*err == DB_SUCCESS' failed in btr_page_split_and_insert on INSERT > This is a new variation of MDEV-29003 / MDEV-28950 it looks like. This was the last bug in my list of highly sporadic bugs to analyze.

Lena Startseva added a comment - 2022-08-23 09:16

Discussed with the Roal: the task can be pushed

Lena Startseva added a comment - 2022-08-23 09:16 Discussed with the Roal: the task can be pushed

Lena Startseva added a comment - 2022-09-21 15:08

The task was twice rebased after last comment and needs to be retested.
Tests are being developed for this improvements in MDEV-29390

Lena Startseva added a comment - 2022-09-21 15:08 The task was twice rebased after last comment and needs to be retested. Tests are being developed for this improvements in MDEV-29390

Igor Babaev (Inactive) added a comment - 2023-03-16 22:04

The commit was pushed into 11.1

Igor Babaev (Inactive) added a comment - 2023-03-16 22:04 The commit was pushed into 11.1

People

Assignee:: Igor Babaev (Inactive)

Reporter:: Igor Babaev (Inactive)

Votes:: 0 Vote for this issue

Watchers:: 7 Start watching this issue

Dates

Created:: 2022-06-18 04:24

Updated:: 2025-01-07 06:45

Resolved:: 2023-03-16 22:04

Git Integration

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

MariaDB Server