MySQL Bugs: #88863: COUNT(*) can sometimes return bogus value

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:		CPU Architecture:	Any
Tags:	count(*)

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.

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>

Hello Vasil,

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

Thanks,
Umesh

test results

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

I can confirm this is happening in 

Engine version: 5.7.23

The query I am running is

SELECT COUNT(*) FROM `publisher_offer_clicks` WHERE `publisher_offer_clicks`.`publisher_offer_id` = 18972 AND (created_at > '2019-01-01')