MySQL Bugs: #59347: Incorrect row returned for a query on views when ICP is enabled

Bug #59347	Incorrect row returned for a query on views when ICP is enabled
Submitted:	7 Jan 2011 10:41	Modified:	31 Jan 2011 10:06
Reporter:	SaiKumar V	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S2 (Serious)
Version:	5.6.1	OS:	Linux (RedHat x64)
Assigned to:	Assigned Account	CPU Architecture:	Any
Tags:	index_condition_pushdown, optimizer_switch

Description:
The Query joins two views when ICP is on (Default),the bug cannot be reproduced if the query is on the base table. 

mysql> SELECT  table1 .`pk`,table1 .`col_int_key` FROM F table1, C table2 where table1 .`pk`  = table2 .`col_int_key` and table1 .`pk`  >=  2;
+-----+-------------+
| pk  | col_int_key |
+-----+-------------+
|   2 |           6 |
|   2 |           6 |
|   3 |           5 |
|   3 |           5 |
|   3 |           5 |
|   5 |        NULL |
|   5 |        NULL |
|   6 |           8 |
|   8 |        NULL |
|   8 |        NULL |
|   9 |           3 |
|   9 |           3 |
|   9 |           3 |
|   9 |           3 |
|  53 |           5 |
| 166 |           4 |
+-----+-------------+
16 rows in set (0.00 sec)

mysql> SELECT  table1 .`pk`,table1 .`col_int_key` FROM view_F table1, view_C table2 where table1 .`pk`  = table2 .`col_int_key` and table1 .`pk`  >=  2;
+-----+-------------+
| pk  | col_int_key |
+-----+-------------+
|   1 |           4 |
|   2 |           6 |
|   2 |           6 |
|   3 |           5 |
|   3 |           5 |
|   3 |           5 |
|   5 |        NULL |
|   5 |        NULL |
|   6 |           8 |
|   8 |        NULL |
|   8 |        NULL |
|   9 |           3 |
|   9 |           3 |
|   9 |           3 |
|   9 |           3 |
|  53 |           5 |
| 166 |           4 |
+-----+-------------+
17 rows in set (0.01 sec)

mysql> set optimizer_switch='index_condition_pushdown=off';
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT  table1 .`pk`,table1 .`col_int_key` FROM view_F table1, view_C table2 where table1 .`pk`  = table2 .`col_int_key` and table1 .`pk`  >=  2;
+-----+-------------+
| pk  | col_int_key |
+-----+-------------+
|   2 |           6 |
|   2 |           6 |
|   3 |           5 |
|   3 |           5 |
|   3 |           5 |
|   5 |        NULL |
|   5 |        NULL |
|   6 |           8 |
|   8 |        NULL |
|   8 |        NULL |
|   9 |           3 |
|   9 |           3 |
|   9 |           3 |
|   9 |           3 |
|  53 |           5 |
| 166 |           4 |
+-----+-------------+
16 rows in set (0.01 sec)

How to repeat:
Found on release binary,  mysqld Ver 14.14 Distrib 5.6.1-m5

The is a RQG Transform failure.

CREATE TABLE `C` (
  `pk` int(11) NOT NULL AUTO_INCREMENT,
  `col_int_nokey` int(11) DEFAULT NULL,
  `col_int_key` int(11) DEFAULT NULL,
  `col_date_key` date DEFAULT NULL,
  `col_date_nokey` date DEFAULT NULL,
  `col_time_key` time DEFAULT NULL,
  `col_time_nokey` time DEFAULT NULL,
  `col_datetime_key` datetime DEFAULT NULL,
  `col_datetime_nokey` datetime DEFAULT NULL,
  `col_varchar_key` varchar(1) DEFAULT NULL,
  `col_varchar_nokey` varchar(1) DEFAULT NULL,
  PRIMARY KEY (`pk`),
  KEY `col_int_key` (`col_int_key`),
  KEY `col_date_key` (`col_date_key`),
  KEY `col_time_key` (`col_time_key`),
  KEY `col_datetime_key` (`col_datetime_key`),
  KEY `col_varchar_key` (`col_varchar_key`,`col_int_key`)
) ENGINE=InnoDB AUTO_INCREMENT=21 DEFAULT CHARSET=latin1;

INSERT INTO `C` VALUES (1,NULL,2,NULL,NULL,'11:28:45','11:28:45','2004-10-11 18:13:16','2004-10-11 18:13:16','w','w'),(2,7,9,'2001-09-19','2001-09-19','20:25:14','20:25:14',NULL,NULL,'m','m'),(3,9,3,'2004-09-12','2004-09-12','13:47:24','13:47:24','1900-01-01 00:00:00','1900-01-01 00:00:00','m','m'),(4,7,9,NULL,NULL,'19:24:11','19:24:11','2009-07-25 00:00:00','2009-07-25 00:00:00','k','k'),(5,4,NULL,'2002-07-19','2002-07-19','15:59:13','15:59:13',NULL,NULL,'r','r'),(6,2,9,'2002-12-16','2002-12-16','00:00:00','00:00:00','2008-07-27 00:00:00','2008-07-27 00:00:00','t','t'),(7,6,3,'2006-02-08','2006-02-08','15:15:04','15:15:04','2002-11-13 16:37:31','2002-11-13 16:37:31','j','j'),(8,8,8,'2006-08-28','2006-08-28','11:32:06','11:32:06','1900-01-01 00:00:00','1900-01-01 00:00:00','u','u'),(9,NULL,8,'2001-04-14','2001-04-14','18:32:33','18:32:33','2003-12-10 00:00:00','2003-12-10 00:00:00','h','h'),(10,5,53,'2000-01-05','2000-01-05','15:19:25','15:19:25','2001-12-21 22:38:22','2001-12-21 22:38:22','o','o'),(11,NULL,0,'2003-12-06','2003-12-06','19:03:19','19:03:19','2008-12-13 23:16:44','2008-12-13 23:16:44',NULL,NULL),(12,6,5,'1900-01-01','1900-01-01','00:39:46','00:39:46','2005-08-15 12:39:41','2005-08-15 12:39:41','k','k'),(13,188,166,'2002-11-27','2002-11-27',NULL,NULL,NULL,NULL,'e','e'),(14,2,3,NULL,NULL,'00:00:00','00:00:00','2006-09-11 12:06:14','2006-09-11 12:06:14','n','n'),(15,1,0,'2003-05-27','2003-05-27','13:12:11','13:12:11','2007-12-15 12:39:34','2007-12-15 12:39:34','t','t'),(16,1,1,'2005-05-03','2005-05-03','04:56:48','04:56:48','2005-08-09 00:00:00','2005-08-09 00:00:00','c','c'),(17,0,9,'2001-04-18','2001-04-18','19:56:05','19:56:05','2001-09-02 22:50:02','2001-09-02 22:50:02','m','m'),(18,9,5,'2005-12-27','2005-12-27','19:35:19','19:35:19','2005-12-16 22:58:11','2005-12-16 22:58:11','y','y'),(19,NULL,6,'2004-08-20','2004-08-20','05:03:03','05:03:03','2007-04-19 00:19:53','2007-04-19 00:19:53','f','f'),(20,4,2,'1900-01-01','1900-01-01','18:38:59','18:38:59','1900-01-01 00:00:00','1900-01-01 00:00:00','d','d');

CREATE TABLE `F` (
  `pk` int(11) NOT NULL AUTO_INCREMENT,
  `col_int_nokey` int(11) DEFAULT NULL,
  `col_int_key` int(11) DEFAULT NULL,
  `col_date_key` date DEFAULT NULL,
  `col_date_nokey` date DEFAULT NULL,
  `col_time_key` time DEFAULT NULL,
  `col_time_nokey` time DEFAULT NULL,
  `col_datetime_key` datetime DEFAULT NULL,
  `col_datetime_nokey` datetime DEFAULT NULL,
  `col_varchar_key` varchar(1) DEFAULT NULL,
  `col_varchar_nokey` varchar(1) DEFAULT NULL,
  PRIMARY KEY (`pk`),
  KEY `col_int_key` (`col_int_key`),
  KEY `col_date_key` (`col_date_key`),
  KEY `col_time_key` (`col_time_key`),
  KEY `col_datetime_key` (`col_datetime_key`),
  KEY `col_varchar_key` (`col_varchar_key`,`col_int_key`)
) ENGINE=InnoDB AUTO_INCREMENT=1001 DEFAULT CHARSET=latin1;

-- Size of insert for table F is large , pls find it attached.
(trying to reduce the data-set also resulted in not yielding the defect)

Inserts for Table F

Attachment: InsertF.sql (text/x-sql), 5.75 KiB.

Noticed that in the report how to repeat section:- ive missed the create stmts for the views.

The create statements for the view are :-

create view view_C as select * from C;

create view view_F as select * from F;

Simplified test case:-

CREATE TABLE `C1` (
  `col_int_key` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

INSERT INTO `C1` VALUES (1),(2),(3),(5),(6),(8),(9),(53),(166);

CREATE TABLE `F1` (
  `pk` int(11) NOT NULL AUTO_INCREMENT,
  `col_int_key` int(11) DEFAULT NULL,
  PRIMARY KEY (`pk`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

INSERT INTO `F1` VALUES (5,NULL),(8,NULL),(9,3),(1,4),(166,4),(3,5),(53,5),(2,6),(6,8);

create view view_F1 as select * from F1;

SELECT table1 .`pk`,table1 .`col_int_key` FROM view_F1 table1, C1 table2 where table1 .`pk`  = table2 .`col_int_key` and table1 .`pk` >=  2;

This is likely caused by the same underlying issue as Bug#59259. The query triggers use of the "adaptive hash index" in InnoDB and this causes the evaluation of the pushed index condition to be skipped (verified by instrumenting the relevant code in row_search_for_mysql()).

I am leaving this bug in the current state and will verify that it is solved by the fix for Bug#59259.

As noted in the initial bug report it it only possible to reproduce this bug when the join includes a view. If the view is replaced by the corresponding base table the bug does not occur. The reason for this is that the optimizer produces a different query plan when joining with a view than when joining with the corresponding base table. This is not optimal. The optimizer should be able to make the same query plan on both cases. I have filed Bug#59696 for this issue.

I have verified that this bug is solved by the fix for Bug#59259. Closing it as a duplicate of Bug#59259.