[MDEV-37178] Optimizer support for functional indexes does not work for delete - Jira

XML

Word

Printable

In case:

--source include/have_sequence.inc

create table t (c int, key (c));

insert into t select seq from seq_1_to_10000;

alter table t

  add column vc int as (c + 1),

  add index(vc);

explain select * from t order by c + 1 limit 2;

explain delete from t order by c + 1 limit 2;

drop table t;

new changes from ~~MDEV-36132~~ work for SELECT:

explain select * from t order by c + 1 limit 2;

id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra

1	SIMPLE	t	index	NULL	vc	5	NULL	2

but do not work for DELETE:

explain delete from t order by c + 1 limit 2;

id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra

1	SIMPLE	t	ALL	NULL	NULL	NULL	NULL	10000	Using filesort

is blocked by

MDEV-36132 Optimizer support for functional indexes: handle GROUP/ORDER BY

is caused by

MDEV-36132 Optimizer support for functional indexes: handle GROUP/ORDER BY

split to

MDEV-37252 UPDATE chooses full table scan when ORDER BY keys are also columns to update

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