Bug #120095 query result changed after UPDATE HISTOGRAM, caused by query plan changed to "skip scan"
Submitted: 18 Mar 8:42 Modified: 8 Aug 13:04
Reporter: Alice Alice Email Updates:
Status: No Feedback Impact on me:
None 
Category:MySQL Server: DML Severity:S3 (Non-critical)
Version:8.4.8, 8.0.41 OS:Any
Assigned to: CPU Architecture:Any

[18 Mar 8:42] Alice Alice 
Description:
query result changed after UPDATE HISTOGRAM, caused by query plan changed to "skip scan",like this:
mysql> explain analyze SELECT t2.c0 AS ref0, t2.c1 AS ref1, t2.c2 AS ref2, t2.c3 AS ref3, t2.vc5 AS ref4, t2.vc7 AS ref5 FROM t2 WHERE (! ((t2.c3) >= ('2013-08-06')));
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| EXPLAIN                                                                                                                                                                                                           |
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| -> Filter: (t2.c3 < '2013-08-06')  (cost=165 rows=544) (actual time=0.0311..2.06 rows=1024 loops=1)
    -> Covering index scan on t2 using i0  (cost=165 rows=1632) (actual time=0.0278..1.62 rows=1632 loops=1)
 |
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set, 9 warnings (0.01 sec)

mysql> ANALYZE TABLE t2 UPDATE HISTOGRAM ON c0, c1, c2, c3, vc5, vc7;
+----------------+-----------+----------+------------------------------------------------+
| Table          | Op        | Msg_type | Msg_text                                       |
+----------------+-----------+----------+------------------------------------------------+
| 0304test156.t2 | histogram | status   | Histogram statistics created for column 'c0'.  |
| 0304test156.t2 | histogram | status   | Histogram statistics created for column 'c1'.  |
| 0304test156.t2 | histogram | status   | Histogram statistics created for column 'c2'.  |
| 0304test156.t2 | histogram | status   | Histogram statistics created for column 'c3'.  |
| 0304test156.t2 | histogram | status   | Histogram statistics created for column 'vc5'. |
| 0304test156.t2 | histogram | status   | Histogram statistics created for column 'vc7'. |
+----------------+-----------+----------+------------------------------------------------+
6 rows in set (0.05 sec)

mysql> explain analyze SELECT t2.c0 AS ref0, t2.c1 AS ref1, t2.c2 AS ref2, t2.c3 AS ref3, t2.vc5 AS ref4, t2.vc7 AS ref5 FROM t2 WHERE (! ((t2.c3) >= ('2013-08-06')));
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| EXPLAIN                                                                                                                                                                                                                                          |
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| -> Filter: (t2.c3 < '2013-08-06')  (cost=108 rows=112) (actual time=0.143..0.715 rows=112 loops=1)
    -> Covering index skip scan on t2 using i0 over NULL < c3 <= '00:20:13'  (cost=108 rows=112) (actual time=0.138..0.672 rows=112 loops=1)
 |
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

How to repeat:
CREATE TABLE `t2` (
  `c0` datetime /*!50606 STORAGE DISK */ /*!50606 COLUMN_FORMAT FIXED */ DEFAULT NULL COMMENT 'asdf',
  `c1` smallint DEFAULT NULL COMMENT 'asdf',
  `c2` year /*!50606 STORAGE MEMORY */ /*!50606 COLUMN_FORMAT DYNAMIC */ DEFAULT NULL COMMENT 'asdf',
  `c3` time /*!50606 STORAGE DISK */ DEFAULT NULL,
  `vc5` decimal(10,0) DEFAULT NULL /*!80023 INVISIBLE */,
  `vc7` time DEFAULT NULL,
  UNIQUE KEY `i0` (`c0` DESC,`c1`,`c2`,`c3`,`vc5`,`vc7`),
  UNIQUE KEY `i3` (`c0`,`c1`,`c2`,`c3`,`vc5`,`vc7` DESC),
  UNIQUE KEY `i4` (`c0`,`c1`,`c2`,`c3` DESC,`vc5`,`vc7`),
  KEY `i43` (`c0`,`c1`,`c2`,`c3`,`vc5`,`vc7`),
  KEY `i1` (`c0`,`c1`,`c2` DESC,`c3` DESC,`vc5`,`vc7`) /*!80000 INVISIBLE */,
  KEY `i2` (`c0`,`c1` DESC,`c2`,`c3`,`vc5`,`vc7` DESC),
  KEY `i5` (`c0`,`c1` DESC,`c2`,`c3` DESC,`vc5` DESC,`vc7`),
  KEY `i6` (`c0`,`c1`,`c2`,`c3`,`vc5`,`vc7`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

INSERT INTO `t2` (`c0`, `c1`, `c2`, `c3`, `vc5`, `vc7`) VALUES ('2525-01-01 00:00:00',-32768,NULL,'05:47:37',NULL,NULL),('2036-06-04 14:42:39',NULL,2145,NULL,NULL,NULL),('2034-10-25 05:36:39',-32768,2051,'06:48:40',NULL,NULL),('2032-01-15 19:25:01',15429,NULL,'11:52:20',NULL,NULL),('2030-11-15 00:01:28',-32768,NULL,'11:09:50',NULL,NULL),('2030-04-16 09:27:12',17423,2023,NULL,NULL,NULL),('2029-08-19 07:17:07',NULL,NULL,NULL,NULL,NULL),('2029-06-15 07:17:03',32767,NULL,NULL,NULL,NULL),('2028-07-29 16:03:13',27,NULL,'14:51:00',NULL,NULL),('2027-11-24 15:28:04',32767,2024,'22:28:04',NULL,NULL),('2027-10-02 23:56:09',32767,NULL,NULL,NULL,NULL),('2027-02-04 04:46:23',-32768,NULL,NULL,NULL,NULL),('2026-12-22 02:11:14',NULL,NULL,NULL,NULL,NULL),('2024-02-29 00:00:00',12061,2155,NULL,NULL,NULL),('2021-05-31 03:57:57',-32768,NULL,'00:00:00',NULL,NULL),('2020-08-05 05:11:24',NULL,2058,'03:15:09',NULL,NULL),('2018-07-25 13:54:23',32767,NULL,NULL,NULL,NULL),('2016-11-15 10:04:20',32767,0000,NULL,NULL,NULL),('2013-01-10 13:20:00',NULL,NULL,'16:47:57',NULL,NULL),('2011-07-22 00:20:01',-91,0000,NULL,NULL,NULL),('2007-02-23 12:27:00',-32768,0000,'09:22:11',NULL,NULL),('2005-10-21 05:55:22',-66,NULL,'12:57:22',NULL,NULL),('2005-06-01 08:42:25',NULL,NULL,NULL,NULL,NULL),('2004-09-27 09:47:00',32767,0000,'21:11:13',NULL,NULL),('2003-08-09 09:55:24',-32768,NULL,NULL,NULL,NULL),('2000-01-01 00:00:00',NULL,1963,'18:35:43',NULL,NULL),('1995-09-20 21:24:09',-20243,NULL,NULL,NULL,NULL),('1994-04-03 06:35:00',NULL,1970,'03:54:13',NULL,NULL),('1994-03-12 07:12:02',106,2140,'12:49:13',NULL,NULL),('1991-01-11 19:58:33',7,2111,'20:43:31',NULL,NULL),('1989-12-12 06:47:05',NULL,2038,NULL,NULL,NULL),('1986-06-26 07:16:19',-32768,NULL,NULL,NULL,NULL),('1983-02-07 15:30:00',NULL,2068,NULL,NULL,NULL),('1980-11-30 11:54:58',NULL,NULL,NULL,NULL,NULL),('1980-11-25 11:18:01',-124,0000,'02:45:26',NULL,NULL),('1978-08-09 18:14:02',19046,2014,'09:18:36',NULL,NULL),('1977-12-06 18:51:08',NULL,1945,'12:37:05',NULL,NULL),('1976-09-29 02:16:33',3890,2058,'00:26:00',NULL,NULL),('1972-09-21 15:25:29',NULL,0000,NULL,NULL,NULL),('1971-01-05 02:52:30',-24617,0000,'18:05:41',NULL,NULL),('1969-12-31 23:59:59',NULL,NULL,'08:21:26',NULL,NULL),('0000-00-00 00:00:00',NULL,NULL,NULL,NULL,NULL),('0000-00-00 00:00:00',NULL,1983,NULL,NULL,NULL),('0000-00-00 00:00:00',NULL,1983,'-25:30:45',NULL,NULL),('0000-00-00 00:00:00',NULL,1983,'00:00:00',NULL,NULL),('0000-00-00 00:00:00',NULL,1983,'00:00:01',NULL,NULL),('0000-00-00 00:00:00',NULL,1983,'00:48:27',NULL,NULL),('0000-00-00 00:00:00',NULL,1983,'01:51:14',NULL,NULL),('0000-00-00 00:00:00',NULL,1983,'06:43:48',NULL,NULL),('0000-00-00 00:00:00',NULL,1983,'09:27:41',NULL,NULL),('0000-00-00 00:00:00',NULL,1983,'10:45:32',NULL,NULL),('0000-00-00 00:00:00',NULL,1983,'11:03:14',NULL,NULL),('0000-00-00 00:00:00',NULL,1983,'11:09:28',NULL,NULL),('0000-00-00 00:00:00',NULL,1983,'11:23:42',NULL,NULL),('0000-00-00 00:00:00',NULL,1983,'11:50:42',NULL,NULL),('0000-00-00 00:00:00',NULL,1983,'12:03:57',NULL,NULL),('0000-00-00 00:00:00',NULL,1983,'12:14:51',NULL,NULL),('0000-00-00 00:00:00',NULL,1983,'12:16:52',NULL,NULL),('0000-00-00 00:00:00',NULL,1983,'12:34:57',NULL,NULL),('0000-00-00 00:00:00',NULL,1983,'13:37:41',NULL,NULL),('0000-00-00 00:00:00',NULL,1983,'13:41:06',NULL,NULL),('0000-00-00 00:00:00',NULL,1983,'16:42:53',NULL,NULL),('0000-00-00 00:00:00',NULL,1983,'20:03:16',NULL,NULL),('0000-00-00 00:00:00',-32768,0000,NULL,NULL,NULL),('0000-00-00 00:00:00',-32768,1983,'15:14:21',NULL,NULL),('0000-00-00 00:00:00',-32768,1983,'24:00:00',NULL,NULL),('0000-00-00 00:00:00',-30800,1983,NULL,NULL,NULL),('0000-00-00 00:00:00',7,NULL,'-25:30:45',NULL,NULL),('0000-00-00 00:00:00',35,1983,NULL,NULL,NULL),('0000-00-00 00:00:00',49,1983,'13:04:53',NULL,NULL),('0000-00-00 00:00:00',74,1983,NULL,NULL,NULL),('0000-00-00 00:00:00',89,1983,NULL,NULL,NULL),('0000-00-00 00:00:00',18520,1983,NULL,NULL,NULL),('0000-00-00 00:00:00',32767,NULL,NULL,NULL,NULL),('0000-00-00 00:00:00',32767,NULL,'10:18:07',NULL,NULL),('0000-00-00 00:00:00',32767,NULL,'20:07:46',NULL,NULL),('0000-00-00 00:00:00',32767,1974,'05:57:39',NULL,NULL),('0000-00-00 00:00:00',32767,1983,'15:02:49',NULL,NULL),('0000-00-00 00:00:00',32767,1983,'18:21:26',NULL,NULL),('0000-00-00 00:00:00',32767,2101,NULL,NULL,NULL),(NULL,NULL,NULL,'11:50:41',NULL,NULL),(NULL,NULL,NULL,'15:24:20',NULL,NULL),(NULL,NULL,NULL,'16:10:48',NULL,NULL),(NULL,NULL,NULL,'16:30:43',NULL,NULL),(NULL,NULL,NULL,'18:36:25',NULL,NULL),(NULL,NULL,0000,'05:16:01',NULL,NULL),(NULL,NULL,2062,'20:29:39',NULL,NULL),(NULL,NULL,2122,'00:49:46',NULL,NULL),(NULL,NULL,2130,'06:13:22',NULL,NULL),(NULL,NULL,2152,'12:41:30',NULL,NULL),(NULL,-32768,NULL,'-838:59:59',NULL,NULL),(NULL,-32768,NULL,'-25:30:45',NULL,NULL),(NULL,-32768,NULL,'14:57:10',NULL,NULL),(NULL,-32768,2117,'01:12:44',NULL,NULL),(NULL,5,NULL,'12:00:00',NULL,NULL),(NULL,78,NULL,NULL,NULL,NULL),(NULL,19627,NULL,NULL,NULL,NULL),(NULL,22796,NULL,NULL,NULL,NULL),(NULL,32767,NULL,NULL,NULL,NULL),(NULL,32767,NULL,'19:57:33',NULL,NULL),(NULL,32767,1978,'21:13:50',NULL,NULL),(NULL,32767,2038,'22:20:50',NULL,NULL);

insert into t2(c0,c1,c2,c3) select c0,c1,c2,c3 from t2;
insert into t2(c0,c1,c2,c3) select c0,c1,c2,c3 from t2;
insert into t2(c0,c1,c2,c3) select c0,c1,c2,c3 from t2;
insert into t2(c0,c1,c2,c3) select c0,c1,c2,c3 from t2;

SELECT t2.c0 AS ref0, t2.c1 AS ref1, t2.c2 AS ref2, t2.c3 AS ref3, t2.vc5 AS ref4, t2.vc7 AS ref5 FROM t2 WHERE (! ((t2.c3) >= ('2013-08-06')));

ANALYZE TABLE t2 UPDATE HISTOGRAM ON c0, c1, c2, c3, vc5, vc7;

SELECT t2.c0 AS ref0, t2.c1 AS ref1, t2.c2 AS ref2, t2.c3 AS ref3, t2.vc5 AS ref4, t2.vc7 AS ref5 FROM t2 WHERE (! ((t2.c3) >= ('2013-08-06')));

Suggested fix:
the root cause is:
1. after UPDATE HISTOGRAM, optimizer choose "skip scan" to execute this query;
2. using index skip scan, the query is changed;
[31 Mar 1:45] Alice Alice 
the same scenerio is also fault at MySQL8.4.8;
[8 Jul 13:04] Chaithra Marsur Gopala Reddy 
Hi Alice Alice,

We are unable to reproduce the problem at our end, both on 8.4.8 and the latest versions of 8.0, 8.4 and 9.7. Could you please provide us with a reputable test case. 
We have verified that if skip scan is forced using a hint, it does produce the wrong results. But optimizer is not choosing skip scan after UPDATE HISTOGRAM. So it would help us if you give us a repeatable test case for the issue you are seeing.

Thanks,
Chaithra
[9 Aug 1:00] Bugs System 
No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".