Optimizer hints removed from views definition

Description

CREATE TABLE `t` (
`a` int(11) NOT NULL,
`b` int(11) DEFAULT NULL,
`c` int(11) DEFAULT NULL,
PRIMARY KEY (`a`),
KEY `b` (`b`),
KEY `c` (`c`)
) ENGINE=InnoDB;

– fill the table with values 1-N.

create view v1 as SELECT /*+ NO_ICP(t) */ * FROM t;

mysql> explain SELECT /*+ NO_ICP(t) */ * FROM t WHERE b>=42 AND c<=42 LIMIT 1\G
id: 1
select_type: SIMPLE
table: t
partitions: NULL
type: range
possible_keys: b,c
key: c
key_len: 5
ref: NULL
rows: 42
filtered: 50.00
Extra: Using where
1 row in set, 1 warning (0.00 sec)

mysql> explain SELECT /*+ NO_ICP(t) */ * FROM v1 WHERE b>=42 AND c<=42 LIMIT 1\G
id: 1
select_type: SIMPLE
table: t
partitions: NULL
type: range
possible_keys: b,c
key: c
key_len: 5
ref: NULL
rows: 42
filtered: 50.00
Extra: Using index condition; Using where

mysql> explain SELECT * FROM v1 WHERE b>=42 AND c<=42 LIMIT 1\G
id: 1
select_type: SIMPLE
table: t
partitions: NULL
type: range
possible_keys: b,c
key: c
key_len: 5
ref: NULL
rows: 42
filtered: 50.00
Extra: Using index condition; Using where
1 row in set, 1 warning (0.00 sec)

mysql> explain SELECT /*+ NO_ICP(v1) */ * FROM v1 WHERE b>=42 AND c<=42 LIMIT 1\G
id: 1
select_type: SIMPLE
table: t
partitions: NULL
type: range
possible_keys: b,c
key: c
key_len: 5
ref: NULL
rows: 42
filtered: 50.00
Extra: Using index condition; Using where
1 row in set, 2 warnings (0.00 sec)

As you can see it's not possible to use NO_ICP hint (and other ones like QB_NAME) for views.

Environment

None

AFFECTED CS IDs

249981

Smart Checklist

Activity

Show:

Julia Vural March 4, 2025 at 9:04 PM

It appears that this issue is no longer being worked on, so we are closing it for housekeeping purposes. If you believe the issue still exists, please open a new ticket after confirming it's present in the latest release.

Won't Do

Details

Assignee

Reporter

Affects versions

Priority

Smart Checklist

Created March 21, 2019 at 6:12 AM
Updated March 4, 2025 at 9:04 PM
Resolved March 4, 2025 at 9:04 PM