Bug #97986 Mysql return different number of rows when select different fields
Submitted: 13 Dec 2019 14:16 Modified: 13 Jan 16:29
Reporter: JAIME Martin Email Updates:
Status: No Feedback Impact on me:
None 
Category:MySQL Server: Partitions Severity:S3 (Non-critical)
Version:8.0.17 OS:Microsoft Windows
Assigned to: CPU Architecture:Any

[13 Dec 2019 14:16] JAIME Martin
Description:
I have a big table (more than 500.000.000 of rows, whit more than 50 fields, partition by hash over the field idterminal in 64 partitions).

The table definition are:

CREATE TABLE `historico` (
  `Nombre` varchar(50) DEFAULT NULL,
[...]
  `Clave` varchar(64) DEFAULT NULL,
[...]
  `IdTerminal` int(11) NOT NULL DEFAULT '0',
[...]
  UNIQUE KEY `idx_Historico_Clave` (`IdTerminal`,`Clave`),
  KEY `idx_historico_FechaRecepcion` (`IdTerminal`,`FechaRecepcion`),
  KEY `idx_historico_idterminal_fechagps` (`IdTerminal`,`FechaGPS`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
/*!50100 PARTITION BY HASH (`IdTerminal`)
PARTITIONS 64 */ 

I am getting some strange results: Depending on the fields I put in a query, the number of rows I get is different:

mysql> select idterminal, clave from historico partition(p0) where  clave = 'G20191016081555P41344';
+------------+-----------------------+
| idterminal | clave                 |
+------------+-----------------------+
|      41344 | G20191016081555P41344 |
+------------+-----------------------+
1 row in set (0.00 sec)

mysql> select nombre, idterminal, clave from historico partition(p0) where  clave = 'G20191016081555P41344';
+----------+------------+-----------------------+
| nombre   | idterminal | clave                 |
+----------+------------+-----------------------+
| A Name   |      41344 | G20191016081555P41344 |
| A Name   |      41344 | G20191016081555P41344 |
| A Name   |      41344 | G20191016081555P41344 |
| A Name   |      41344 | G20191016081555P41344 |
+----------+------------+-----------------------+
4 rows in set (5.07 sec)

This table has an unique index over the fields "idTerminal" and "clave", so I understand that the second query must return only one row. When I try to make a rebuild of this partition doing 
alter table historico rebuild partition p0; 
I get:

ERROR 1022 (23000) at line 1: Can't write; duplicate key in table 'historico'

Other crazy results:

mysql> select count(*) as how_many, clave, idterminal from historico partition(p0) group by clave, idterminal order by how_many desc limit 10;
+----------+----------------------------+------------+
| how_many | clave                      | idterminal |
+----------+----------------------------+------------+
|        4 | G20191021142024P700653     |      15680 |
|        4 | G20191001182332P700653     |      15680 |
|        4 | G20191001183144P700653     |      15680 |
|        4 | G20191005063231P830677     |      53824 |
|        4 | G20191005080409P830677     |      53824 |
|        4 | G20191005103808P830677     |      53824 |
|        4 | G20191005123548A830677-124 |      53824 |
|        4 | G20191005144959P830677     |      53824 |
|        4 | G20191005161853P830677     |      53824 |
|        4 | G20191005172114P830677     |      53824 |
+----------+----------------------------+------------+
10 rows in set (1 min 25.36 sec)

mysql> select count(*) as how_many, clave, idterminal from historico partition(p0) where idterminal = 15680 and clave = 'G20191021142024P700653' group by clave, idterminal order by how_many desc limit 10;
+----------+------------------------+------------+
| how_many | clave                  | idterminal |
+----------+------------------------+------------+
|        1 | G20191021142024P700653 |      15680 |
+----------+------------------------+------------+
1 row in set (0.00 sec)

My first suspicion was that the unique index are corrupted, but if I check the partition:

alter table historico check partition p0;

I get:

datos.historico check   status  OK

So, it´s look like a bug 

How to repeat:
I don´t know how to repeat in the laboratory.
[13 Dec 2019 16:29] Miguel Solorzano
Thank you for the bug report. We need a repeatable test case a SQL script (create table, insert data, query, real result and expected result) otherwise we won't be able to verify the bug report.
[14 Jan 1:00] Bugs System
No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".