MySQL Bugs: #102743: cond_push not work for update and delete.

Bug #102743	cond_push not work for update and delete.
Submitted:	26 Feb 2021 7:35	Modified:	2 Mar 2021 13:08
Reporter:	casa zhang (OCA)	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: DML	Severity:	S4 (Feature request)
Version:	8.0	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	Optimizer

Description:
In `Sql_cmd_update::update_single_table` and `Sql_cmd_delete::delete_from_single_table` functions, the code below doesn't work.

  if (conds &&
      thd->optimizer_switch_flag(OPTIMIZER_SWITCH_ENGINE_CONDITION_PUSHDOWN)) {
    table->file->cond_push(conds, false);
  }

cond_push function is empty, no conds passed to the handler, so handler pass the whole record to server every time when read_record.

can this be verified? thanks.

How to repeat:
every update will retrieve the whole record for updating, 

CREATE TABLE `t2` (
  `a` int(11) DEFAULT NULL,
  `b` int(11) DEFAULT NULL,
  `c` char(10) DEFAULT NULL,
  `d` varchar(256) DEFAULT NULL,
  `e` text,
  KEY `ii1` (`a`,`b`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

sql1: update t2 set b = 3 where a = 1;
sql2: select b from t2 where a = 1;

sql2 can retrieve column a and column b, but actually sql2 retrieve column a, b, c, d, e in read_record, all columns of the record. It's strange.

Hi Mr. zhang,

Thank you for your bug report.

However, certain parts of your text are not clear to us.

When we retrieve a record that needs updating or deleting, then it is always the entire record that is retrieved. Hence, we do not truly see what is the problem here ????

Waiting on your feedback.

thanks a lot for reply, my wonder is as follows:

if we create a table as `t2`;

CREATE TABLE `t2` (
  `a` int(11) DEFAULT NULL,
  `b` int(11) DEFAULT NULL,
  `c` char(10) DEFAULT NULL,
  `d` varchar(256) DEFAULT NULL,
  `e` text,
  KEY `ii1` (`a`,`b`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

sql1: update t2 set b = 3 where a = 1;

sql1 will not refer to column `e`, but we still retrieve the whole record which has column `a`, `b`, `c`, `d`, `e`, mysql transfers the whole record from innodb engine to server. I think it's not necessary to retrieve column `c`, `d`, `e`. If the column `e` has a large bytes text, it will spend much time to read the record from disk. So can this situation be optimized to retrieve only column `a` and `b`? like index condition pushdown(ICP) to engine.

In my online case, the column `e` of table `t2` has a large text, which is about  100M bytes, when execute sql1, it spend about 60s to 80s the first execution, but if we execute `select b from t2 where a = 1`, it will spend less than 0.1s because of index condition pushdown(ICP) to engine, and why UPDATE cann't use index condition pushdown(ICP)?

Hi Mr. zhang,

We do value your opinion, but it is not possible to retrieve only certain columns from a record, since record is an indivisible unit.

It is completely different in the read-only statements, when you have a query like this:

select a * from table 1 where a=some_constant.

Then , if you have an index on a, you do not have to touch a record at all. But, this is not possible with other DML statements where record has to be locked and values changed !!!!!

Not a bug.

Sinisa, I think this is a good feature request. The OP wants to eliminate the unnecessary copying of data from the SE to the Server.

Hi Mr. zhang,

It was concluded that this is not a bug, but a good feature request.

Verified as a feature request.