Bug #45126 Incomplete cardinality for MERGE tables with multiple-column indexes
Submitted: 27 May 2009 11:07 Modified: 28 May 2009 2:13
Reporter: Sergey Vojtovich Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Merge storage engine Severity:S3 (Non-critical)
Version:4.1 and later OS:Any
Assigned to: CPU Architecture:Any
Tags: regression

[27 May 2009 11:07] Sergey Vojtovich
Description:
Found while reviewing BUG#41212. This is a regression after fixing BUG#22937.

Note that the provided "how to repeat" results may vary depending on platform (64bit or 32bit) and whether fix for BUG#41212 is applied.

How to repeat:
CREATE TABLE t1(a INT, b INT, c INT, d INT, KEY(a,b,c,d));
INSERT INTO t1 VALUES(1,1,1,1);
ANALYZE TABLE t1;
CREATE TABLE m1(a INT, b INT, c INT, d INT, KEY(a,b,c,d)) ENGINE=MERGE UNION=(t1);
SELECT TABLE_NAME, INDEX_NAME, CARDINALITY FROM INFORMATION_SCHEMA.STATISTICS WHERE TABLE_NAME='t1';
SELECT TABLE_NAME, INDEX_NAME, CARDINALITY FROM INFORMATION_SCHEMA.STATISTICS WHERE TABLE_NAME='m1';

---

CREATE TABLE t1(a INT, b INT, c INT, d INT, KEY(a,b,c,d));
INSERT INTO t1 VALUES(1,1,1,1);
ANALYZE TABLE t1;
Table	Op	Msg_type	Msg_text
test.t1	analyze	status	OK
CREATE TABLE m1(a INT, b INT, c INT, d INT, KEY(a,b,c,d)) ENGINE=MERGE UNION=(t1);
SELECT TABLE_NAME, INDEX_NAME, CARDINALITY FROM INFORMATION_SCHEMA.STATISTICS WHERE TABLE_NAME='t1';
TABLE_NAME	INDEX_NAME	CARDINALITY
t1	a	1
t1	a	1
t1	a	1
t1	a	1
SELECT TABLE_NAME, INDEX_NAME, CARDINALITY FROM INFORMATION_SCHEMA.STATISTICS WHERE TABLE_NAME='m1';
TABLE_NAME	INDEX_NAME	CARDINALITY
m1	a	1
m1	a	NULL
m1	a	NULL
m1	a	NULL

Suggested fix:
When copying cardinality statistics array during ha_myisammrg::info(),
size of buffer is determined as min(file->keys, table->key_parts). This is generally wrong, as file->keys means number of keys (not key segments) in the first underlying table.

This is generally done to avoid copying memory beyond of allocated bounds when MERGE table has more keys than underlying tables. That is incorrectly defined MERGE table. Currently this is possible only during ha_myisammrg::open(), as it calls ::info() before checking correctness of the MERGE table.

The best fix for this is probably not to request cardinality values before running conformance check. In this case we also should remove obsolete initialization of rec_per_key array in ha_myisammrg::info(), as it won't make sense anymore.
[28 May 2009 2:13] MySQL Verification Team
Thank you for the bug report. Verified as described.