Optimizer hints removed from views definition
General
Escalation
General
Escalation
Description
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
Details
Assignee
Unassigned
UnassignedReporter
Nickolay Ihalainen
Nickolay Ihalainen(Deactivated)Upstream Bug URL
Affects versions
Priority
Smart Checklist
Open Smart Checklist
Smart Checklist

Open 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
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.