Wrong cardinality for InnoDB table

Upstream?

Confirmed with PS 5.6

nilnandan@desktop:~/Downloads$ mysql -uroot -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 40
Server version: 5.6.25-73.1-log Percona Server (GPL), Release 73.1, Revision 07b797f

Copyright (c) 2009-2015 Percona LLC and/or its affiliates
Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved.

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> use test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql>
mysql> create table t1(f1 int, f2 int, key(f1), key f1_2 (f1, f2)) engine=innodb;
ERROR 1050 (42S01): Table 't1' already exists
mysql>
mysql> drop table t1
    ->
    -> ;
Query OK, 0 rows affected (0.00 sec)

mysql>
mysql>
mysql>
mysql>
mysql> create table t1(f1 int, f2 int, key(f1), key f1_2 (f1, f2)) engine=innodb;
Query OK, 0 rows affected (0.01 sec)

mysql> insert into t1(f1) values (1),(2),(3),(4),(5),(6);
Query OK, 6 rows affected (0.01 sec)
Records: 6 Duplicates: 0 Warnings: 0

mysql> insert into t1 (f1) select f1 from t1;
Query OK, 6 rows affected (0.00 sec)
Records: 6 Duplicates: 0 Warnings: 0

mysql> insert into t1 (f1) select f1 from t1;
Query OK, 12 rows affected (0.00 sec)
Records: 12 Duplicates: 0 Warnings: 0

mysql> insert into t1 (f1) select f1 from t1;
Query OK, 24 rows affected (0.01 sec)
Records: 24 Duplicates: 0 Warnings: 0

mysql> insert into t1 (f1) select f1 from t1;
Query OK, 48 rows affected (0.00 sec)
Records: 48 Duplicates: 0 Warnings: 0

mysql> insert into t1 (f1) select f1 from t1;
Query OK, 96 rows affected (0.00 sec)
Records: 96 Duplicates: 0 Warnings: 0

mysql> insert into t1 (f1) select f1 from t1;
Query OK, 192 rows affected (0.00 sec)
Records: 192 Duplicates: 0 Warnings: 0

mysql> insert into t1 (f1) select f1 from t1;
Query OK, 384 rows affected (0.01 sec)
Records: 384 Duplicates: 0 Warnings: 0

mysql> update t1 set f2=rand();
Query OK, 768 rows affected, 1 warning (0.01 sec)
Rows matched: 768 Changed: 768 Warnings: 1

mysql>
mysql> show warnings;
+-------+------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Level | Code | Message |
+-------+------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Note | 1592 | Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statement is unsafe because it uses a system function that may return a different value on the slave. |...

Reported upstream.

Another observation:
If the table is created with 'STATS_PERSISTENT=0' option.
The results of 'SHOW INDEXES FROM t1' are even stranger

***************************************************************************************************
SHOW INDEXES FROM t1;
Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment
t1 1 f1 1 f1 A 12 NULL NULL YES BTREE
t1 1 f1_2 1 f1 A 10 NULL NULL YES BTREE
t1 1 f1_2 2 f2 A 35 NULL NULL YES BTREE
***************************************************************************************************

To sum up
MyISAM / expected : ( 6, 6, 12)
InnoDB STATS_PERSISTENT=1 : (12, 12, 24)
InnoDB STATS_PERSISTENT=0 : (12, 10, 35)

Here is a simplified test case (1 field, 1 index and 1 INSERT SELECT)

******************************************************
--source include/have_innodb.inc

CREATE TABLE t1(f1 INT, key(f1)) STATS_PERSISTENT=1 ENGINE=InnoDB;
INSERT INTO t1(f1) VALUES (1), (2), (3), (4), (5), (6);

ANALYZE TABLE t1;
SELECT COUNT(DISTINCT f1), COUNT(*) FROM t1;
SELECT INDEX_SCHEMA, INDEX_NAME, CARDINALITY FROM INFORMATION_SCHEMA.STATISTICS WHERE TABLE_NAME = 't1';

INSERT INTO t1(f1) SELECT f1 FROM t1;

ANALYZE TABLE t1;
SELECT COUNT(DISTINCT f1), COUNT(*) FROM t1;
SELECT INDEX_SCHEMA, INDEX_NAME, CARDINALITY FROM INFORMATION_SCHEMA.STATISTICS WHERE TABLE_NAME = 't1';

DROP TABLE t1;
******************************************************

After the 'INSERT INTO t1(f1) SELECT f1 FROM t1' statement index cardinality changes from expected 6 to 12.

It looks like in InnoDB 5.6 this behavior is by design. Here is an excerpt from 'ha_innobase::info_low()' ('ha_innodb.cc')

********************************************
rec_per_key = innodb_rec_per_key(
  index, j, stats.records);

/* Since MySQL seems to favor table scans
too much over index searches, we pretend
index selectivity is 2 times better than
our estimate: */

rec_per_key = rec_per_key / 2;

if (rec_per_key == 0) {
 rec_per_key = 1;
}
********************************************

In 5.7 this fragment is partially reworked, so this is why we get expected results there.

Bug #1733520 was marked as duplicate of this one.

Percona now uses JIRA for bug reports so this bug report is migrated to: https://jira.percona.com/browse/PS-1642