Bug #88863 COUNT(*) can sometimes return bogus value
Submitted: 11 Dec 2017 17:24 Modified: 12 Dec 2017 9:37
Reporter: Vasil Dimov Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: InnoDB storage engine Severity:S2 (Serious)
Version:5.6.38, 5.7.20 OS:Any
Assigned to:
Tags: count(*)

[11 Dec 2017 17:24] Vasil Dimov
Description:
COUNT(*) sometimes returns just "1" instead of the correct value which is much larger, e.g. > 20000.

How to repeat:
git@github.com:mysql/mysql-server.git
branch: 5.6 @ 38e2b4d1fe7 (latest at the time of writing this)

Create the test table and insert 100K rows in it:

$ mysql test < test_table_init.dump

After the above completes successfully, run:

$ bunzip2 < load_data_1_mln.sql.bz2 |mysql test

This will start inserting 1M rows, while it is running execute:

$ while : ; do echo 'select count(*) from test_table WHERE recno < 1000000000;' |mysql test ; sleep 5 ; done
count(*)
0
count(*)
1465
count(*)
4849
count(*)
8183
count(*)
11300
count(*)
14635
count(*)
1
count(*)
20758
count(*)
1
count(*)
26688
count(*)
29309
count(*)
1
count(*)
35849
count(*)
39339
count(*)
1
count(*)
45810
count(*)
1

Observe that occasionally 1 is returned instead of the correct number of rows.
[11 Dec 2017 17:39] Vasil Dimov
An archive that contains the files to reproduce is uploaded to sftp.oracle.com:

$ sftp -oPort=2021 -oUser=vd@freebsd.org -oKexAlgorithms=diffie-hellman-group14-sha1,diffie-hellman-group1-sha1,diffie-hellman-group-exchange-sha1,diffie-hellman-group-exchange-sha256 -oHostKeyAlgorithms=+ssh-dss sftp.oracle.com:/support/incoming
...
sftp> put mysql-bug-data-88863.tar
Uploading mysql-bug-data-88863.tar to //support/incoming/mysql-bug-data-88863.tar
mysql-bug-data-88863.tar                                          100% 7765KB 349.5KB/s   00:22    
sftp>
[12 Dec 2017 9:37] Umesh Shastry
Hello Vasil,

Thank you for the report and test case.
Observed that 5.6.39 and 5.7.20 are affected.

Thanks,
Umesh
[12 Dec 2017 9:38] Umesh Shastry
test results

Attachment: 88863.results (application/octet-stream, text), 5.34 KiB.