意外异常:索引超出范围.必须为非阴性-5.7.12

Question

我有一个约有1.2亿行的表(offers_lowest(，所以我创建了一个包含过去7整天数据的视图来提高性能，但每当我尝试选择视图或offers_lowest本身(在MySQL工作台中(时，我都会得到以下异常：

索引超出范围。必须是非负数并且小于集合的大小。参数名称：索引

直到我创建了视图，这才发生，这似乎很奇怪，它影响了原始表和视图。

以下是offers_lowest:的创建表语句

CREATE TABLE `offers_lowest` (
`cap_id` int(10) unsigned NOT NULL,
`date` date DEFAULT NULL,
`lowest_price` decimal(10,2) DEFAULT NULL,
`deposit` tinyint(3) unsigned DEFAULT NULL,
`term` tinyint(3) unsigned DEFAULT NULL,
`mileage` int(11) DEFAULT NULL,
`finance_type` enum('P','B') DEFAULT NULL,
`offer_id` int(10) unsigned DEFAULT NULL,
`broker_id` int(10) unsigned DEFAULT NULL,
KEY `idx_lowest_price` (`lowest_price`),
KEY `idx_specials_extra` (`finance_type`,`date`),
KEY `idx_specials_id_price` (`offer_id`,`lowest_price`),
KEY `idx_price_history` (`cap_id`,`deposit`,`term`,`mileage`,`finance_type`,`date`,`offer_id`,`broker_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

下面是视图的create语句：

CREATE 
ALGORITHM = UNDEFINED 
DEFINER = `moneyshake`@`%` 
SQL SECURITY DEFINER
VIEW `offers_lowest_7d` AS
SELECT 
`offers_lowest`.`cap_id` AS `cap_id`,
`offers_lowest`.`date` AS `date`,
`offers_lowest`.`lowest_price` AS `lowest_price`,
`offers_lowest`.`deposit` AS `deposit`,
`offers_lowest`.`term` AS `term`,
`offers_lowest`.`mileage` AS `mileage`,
`offers_lowest`.`finance_type` AS `finance_type`,
`offers_lowest`.`offer_id` AS `offer_id`,
`offers_lowest`.`broker_id` AS `broker_id`
FROM
`offers_lowest`
WHERE
((`offers_lowest`.`date` > (NOW() - INTERVAL 8 DAY))
AND (`offers_lowest`.`date` < CAST(NOW() AS DATE))
AND (`offers_lowest`.`finance_type` = 'P'))

该视图将行数减少到约5米。

SELECT * FROM the_view:的说明

表类型可能的_keys键key_len参考行筛选额外报价范围idx_specials_extra，idx_offers_llowest_dateidx_special s_extra<10349736>使用索引条件

id	select_type	分区
1	SIMPLE	报价最低

感谢Rick James，将CAST函数更改为CURDATE((并修复了它。