Bug #91840 Wrong cardinality in InnoDB

• Submitted: 31 Jul 2018 10:11
• Modified: 6 Aug 2018 12:39
• Reporter: Dmitry Kan
• Status: Not a Bug
• Category: MySQL Server: InnoDB storage engine
• Severity: S3 (Non-critical)
• Version: 5.6.34-log
• OS: Any
• CPU Architecture: Any
• Tags: cardinality, innodb

[31 Jul 2018 10:11] Dmitry Kan

Description:
For the following table:
show create table id_mapper;
+-----------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table     | Create Table                                                                                                                                                                                                                                                                                                                                                                                                                                                           |
+-----------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| id_mapper | CREATE TABLE `id_mapper` (
  `MASTER_ID` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `AS_ID` varchar(45) NOT NULL,
  `PROVIDER_ID` varchar(45) DEFAULT NULL,
  `PROVIDER` varchar(45) DEFAULT NULL,
  `IS_INACTIVE` bit(1) NOT NULL DEFAULT b'0',
  PRIMARY KEY (`MASTER_ID`),
  KEY `id_mapper_as_id` (`AS_ID`),
  KEY `id_mapper_provider_id` (`PROVIDER_ID`),
  KEY `id_mapper_provider` (`PROVIDER`)
) ENGINE=InnoDB AUTO_INCREMENT=2551274 DEFAULT CHARSET=latin1 |
+-----------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

we have cardinality of 1 in the provider_id field. However, when showing index on the field show index command we get:

+-----------+------------+-----------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table     | Non_unique | Key_name              | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-----------+------------+-----------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

| id_mapper |          1 | id_mapper_provider    |            1 | PROVIDER    | A         |           2 |     NULL | NULL   | YES  | BTREE      |         |               |

which suggests, that cardinality on the field is 2.

Looks similar to https://bugs.mysql.com/bug.php?id=78066.

How to repeat:
See description

[31 Jul 2018 12:02] MySQL Verification Team

Hi,

You think that your cardinality is 1. Are you sure that you do not have NULLs in that column ???

Run this statement:

SELECT DISTINCT PROVIDER FROM id_mapper;

If you see two rows, then cardinality is fine. If not, run ANALYZE TABLE on that table and try again.

Do note that 5.7 and 8.0 have options that require much less frequent running of the above DDL command.

[31 Jul 2018 14:12] Dmitry Kan

Hi Sinisa,

There is a single value in the field and no NULLs.

analyze table id_mapper

produces:

entity_master.id_mapper	analyze	status	OK.

[31 Jul 2018 14:51] MySQL Verification Team

Now, after you have run ANALYZE, run again:

SELECT DISTINCT PROVIDER FROM id_mapper;

and

SELECT count(PROVIDER) FROM id_mapper where  PROVIDER IS NULL;

Let us know the results.

[1 Aug 2018 9:12] Dmitry Kan

SELECT DISTINCT PROVIDER FROM id_mapper; 
-- CB

SELECT count(PROVIDER) FROM id_mapper where  PROVIDER IS NULL;
-- 0

[1 Aug 2018 12:20] MySQL Verification Team

Hi,

And SHOW INDEX still shows the cardinality of 2 (two) ???

It could have been fixed by ANALYZE statement.

Thanks in advance.

[2 Aug 2018 13:31] Dmitry Kan

Hi,

It is still showing cardinality 2:

Table	Non_unique	Key_name	Seq_in_index	Column_name	Collation	Cardinality	Sub_part	Packed	Null	Index_type	Comment	Index_comment
id_mapper	1	id_mapper_provider	1	PROVIDER	A	2	NULL	NULL	YES	BTREE

[2 Aug 2018 13:45] MySQL Verification Team

Hi,

We must try to repeat this behaviour.

Please upload the dump of that table by using the tab "Files'. This file will be viewable only by us from Oracle.

Thanks in advance.

[6 Aug 2018 11:27] Dmitry Kan

It is a bit tricky, as it is sensitive data. Let me figure out, if there is a minimum subset of the table that exhibits similar issue.

[6 Aug 2018 11:31] Dmitry Kan

In the meantime, I have imported the same table to a locally running mysql Server version: 5.7.19 Homebrew and it shows cardinality 1 after analyze.

[6 Aug 2018 12:39] MySQL Verification Team

Hi,

Thank you for your feedback.

It turns out that this bug is fixed in 5.7, but the fix is too complex to be ported back to 5.6.

Thanks again.