Bug #70588 Index merge used on partitionned table can return wrong result set

Submitted: 10 Oct 2013 12:09 Modified: 5 Nov 2013 17:05

Reporter: Joffrey MICHAIE Email Updates:

Status: Closed Impact on me:

None

Category: Server: Partition Severity: S2 (Serious)

Version: 5.6.14,5.5 OS: Any

Assigned to: Target Version:

Tags: index merge, intersect, partition, wrong resultset

Triage: Needs Triage: D2 (Serious)

ViewAdd CommentFilesDeveloperEdit SubmissionView Progress LogContributions

[10 Oct 2013 12:09] Joffrey MICHAIE

Description:

Simple query where

indexed_col = 'x' and indexed_date_column = 'yyyy-mm-aa'

returns wrong resultset, when run on partitionned table, and using index_merge (indexed_col,indexed_date_column)

Found on 5.5, repeated on 5.6.14, didn't test on 5.1

How to repeat:

Start MySQL with 100% default settings.

CREATE TABLE `poll` (

`id_key` int(10) unsigned NOT NULL AUTO_INCREMENT,

`id` int(6) NOT NULL DEFAULT '0',

`id_poll` int(6) NOT NULL DEFAULT '0',

`date_long` datetime NOT NULL,

`date_short` date NOT NULL,

PRIMARY KEY (`id_key`,`id_poll`),

KEY `id` (`id`),

KEY `date_creation` (`date_short`)

) ENGINE=InnoDB AUTO_INCREMENT=14101389 DEFAULT CHARSET=latin1

/*!50100 PARTITION BY HASH (id_poll)

PARTITIONS 20 */;

INSERT INTO `poll` VALUES (NULL,1718848,580660,'2013-10-09 18:21:00','2013-10-09');

INSERT INTO `poll` VALUES (NULL,39369,869049,'2013-10-09 18:21:02','2013-10-09');

INSERT INTO `poll` VALUES (NULL,70,1916580,'2013-10-09 18:21:09','2013-10-09');

INSERT INTO `poll` VALUES (NULL,70,1333222,'2013-10-09 18:20:31','2013-10-09');

INSERT INTO `poll` VALUES (NULL,1557405,869555,'2013-10-09 18:20:32','2013-10-09');

INSERT INTO `poll` VALUES (NULL,70,1343938,'2013-10-09 18:20:32','2013-10-09');

INSERT INTO `poll` VALUES (NULL,70,1157259,'2013-10-09 18:20:40','2013-10-09');

INSERT INTO `poll` VALUES (NULL,70,1800441,'2013-10-09 18:20:50','2013-10-09');

INSERT INTO `poll` VALUES (NULL,70,60,'2013-10-09 18:20:53','2013-10-09');

INSERT INTO `poll` VALUES (NULL,1718848,580660,'2013-10-09 18:21:00','2013-10-09');

INSERT INTO `poll` VALUES (NULL,39369,869049,'2013-10-09 18:21:02','2013-10-09');

INSERT INTO `poll` VALUES (NULL,70,1916580,'2013-10-09 18:21:09','2013-10-09');

INSERT INTO `poll` VALUES (NULL,70,1916580,'2013-10-0Query OK, 1 row affected (0,01 sec)

explain SELECT date_short FROM poll WHERE id =70 AND date_short = '2013-10-10'; +----+-------------+-------+-------------+------------------+------------------+---------+------+------+-------------------------------------------------------------+

+----+-------------+-------+-------------+------------------+------------------+---------+------+------+-------------------------------------------------------------+

+----+-------------+-------+-------------+------------------+------------------+---------+------+------+-------------------------------------------------------------+

1 row in set (0,00 sec)

(data sample is small, you can run ANALYZE TABLE if intersect is not shown)

mysql> SELECT date_short FROM poll WHERE id =70 AND date_short = '2013-10-10';

Empty set (0,01 sec)

mysql> SELECT date_short FROM poll WHERE id =70 AND date_short like '2013-10-10';

+------------+

| date_short |

+------------+

| 2013-10-10 |

+------------+

1 row in set (0,00 sec)

Other examples:

mysql> SELECT date_short FROM poll IGNORE INDEX (date_creation) WHERE id =70 AND date_short = '2013-10-10';

+------------+

| date_short |

+------------+

| 2013-10-10 |

+------------+

1 row in set (0,00 sec)

mysql> SELECT date_short FROM poll IGNORE INDEX (id) WHERE id =70 AND date_short = '2013-10-10';

+------------+

| date_short |

+------------+

| 2013-10-10 |

+------------+

1 row in set (0,00 sec)

mysql> alter table poll remove partitioning;

Query OK, 13 rows affected (0,08 sec)

Records: 13 Duplicates: 0 Warnings: 0

mysql> SELECT date_short FROM poll WHERE id =70 AND date_short = '2013-10-10';

+------------+

| date_short |

+------------+

| 2013-10-10 |

+------------+

1 row in set (0,00 sec)

Suggested fix:

Return correct resultset, or do not use intersection merge on hash partitionned tables

[10 Oct 2013 12:30] Miguel Solorzano

Thank you for the bug report.

C:\dbs>c:\dbs\5.6\bin\mysql -uroot --port=3560 --prompt="mysql 5.6 > "

Welcome to the MySQL monitor. Commands end with ; or \g.

Your MySQL connection id is 3

Server version: 5.6.15 Source distribution

Oracle is a registered trademark of Oracle Corporation and/or its

affiliates. Other names may be trademarks of their respective

owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql 5.6 > use xd

Database changed

mysql 5.6 > CREATE TABLE `poll` (

-> `id_key` int(10) unsigned NOT NULL AUTO_INCREMENT,

-> `id` int(6) NOT NULL DEFAULT '0',

-> `id_poll` int(6) NOT NULL DEFAULT '0',

-> `date_long` datetime NOT NULL,

-> `date_short` date NOT NULL,

-> PRIMARY KEY (`id_key`,`id_poll`),

-> KEY `id` (`id`),

-> KEY `date_creation` (`date_short`)

-> ) ENGINE=InnoDB AUTO_INCREMENT=14101389 DEFAULT CHARSET=latin1

-> /*!50100 PARTITION BY HASH (id_poll)

-> PARTITIONS 20 */;

Query OK, 0 rows affected (4.76 sec)

mysql 5.6 >

mysql 5.6 > INSERT INTO `poll` VALUES (NULL,1718848,580660,'2013-10-09 18:21:00','2013-10-09');

Query OK, 1 row affected (0.20 sec)

<CUT>

mysql 5.6 > SELECT date_short FROM poll WHERE id =70 AND date_short = '2013-10-10';

Empty set (0.00 sec)

mysql 5.6 > alter table poll remove partitioning;

Query OK, 13 rows affected (3.76 sec)

Records: 13 Duplicates: 0 Warnings: 0

mysql 5.6 > SELECT date_short FROM poll WHERE id =70 AND date_short = '2013-10-10';

+------------+

| date_short |

+------------+

| 2013-10-10 |

+------------+

1 row in set (0.02 sec)

mysql 5.6 >

[10 Oct 2013 12:55] Joffrey MICHAIE

From:

http://dev.mysql.com/doc/relnotes/mysql/5.6/en/news-5-6-14.html

For partitioned tables, queries could return different results depending on whether Index Merge was used. (Bug #16862316)

[5 Nov 2013 17:05] Jonathan Stephens

Fixed in 5.5+. Documented in the 5.5.36, 5.6.16, and 5.7.4 changelogs, as follows:

Queries using index_merge optimization (see

http://dev.mysql.com/doc/refman/5.5/en/index-merge-optimization.html)

could return invalid results when run against tables that were

partitioned by HASH.

Closed.

[2 Feb 17:20] Laurynas Biveinis

5.5$ bzr log -r 4538

------------------------------------------------------------

revno: 4538

committer: Aditya A <aditya.a@oracle.com>

branch nick: mysql-5.5

timestamp: Tue 2013-11-05 19:25:26 +0530

message:

Bug#17588348: INDEX MERGE USED ON PARTITIONED TABLE

CAN RETURN WRONG RESULT SET

PROBLEM

-------

In ha_partition::cmp_ref() we were only calling the

underlying cmp_ref() of storage engine if the records

are in the same partiton,else we sort by partition and

returns the result.But the index merge intersect

algorithm expects first to sort by row-id first and

then by partition id.

FIX

---

Compare the refernces first using storage engine cmp_ref

and then if references are equal(only happens if

non clustered index is used) then sort it by partition id.

[Approved by Mattiasj #rb3755]

posted on 2014-12-08 17:33 小马歌阅读(205) 评论(0) 编辑收藏所属分类: db tools

新用户注册刷新评论列表


只有注册用户登录后才能发表评论。




网站导航: 博客园 IT新闻 Chat2DB C++博客博问管理
相关文章: MySQL加锁分析【转】 MySQL 小心使用 replace into【转】 MySQL 开发实践 8 问，你能 hold 住几个？【转】推荐几本学习MySQL的好书【转】学习Cassandra资料的一些整理【转】一篇文章，掌握所有开源数据库的现状为什么 Uber 工程师将 Postgres 数据库换成 MySQL？怎样打造一个分布式数据库 Mac上安装MySQL Spring加Mybatis实现MySQL数据库主从读写分离

My Links

Blog Stats

留言簿(26)

随笔分类

文章分类

文章档案

博客连接

搜索

最新评论

阅读排行榜

评论排行榜

Bug #70588 Index merge used on partitionned table can return wrong result set