Bug #96318 distinct group by There are discrepancies in statistical data

• Submitted: 25 Jul 2019 2:58
• Modified: 26 Jul 2019 1:25
• Reporter: laoyang yang
• Status: Closed
• Category: MySQL Server: Prepared statements
• Severity: S3 (Non-critical)
• Version: 5.7.17
• OS: Windows
• CPU Architecture: Any

[25 Jul 2019 2:58] laoyang yang

Description:
When I count the data of a table, everything is the same, but the query results are changing. When I query, I close all the entries of the database. Attached SQL statement：
SELECT
	wc.`CODE` AS CODE,
	wc.`NAME` AS NAME,
	wc.id AS id,
		wc.mac as mac,
			wc.file_address as file_address,
			wc.status as status,
wc.create_date as create_date,
	count(1) AS count
FROM
	(
		SELECT
			sh. NAME AS NAME,
			dwf. CODE AS CODE,
			dwf.mac as mac,
			dwf.file_address as file_address,
			dwf.id AS id,
dwf.create_date as create_date,
			sh.status as status,
			count(DISTINCT dwf.id) AS count
		FROM
			keyword_warning_file dwf
		LEFT JOIN keyword_shops sh ON dwf. CODE = sh. CODE
		GROUP BY
			dwf.mac,
			dwf.file_address
	) wc
where wc.`status`=0
GROUP BY
	`NAME`
ORDER BY
	count DESC
LIMIT 0,
 10

How to repeat:
Three queries will result in different results

[25 Jul 2019 13:11] MySQL Verification Team

Hello Mr. Yang,

Thank you for your bug report.

There are, however, several questions that we have to ask you before proceeding further.

First of all, you have categorised this bug under "Prepared statements". We do not see how do you prepare the statements.

Next, you claim that three queries return different results. We see only one query in your report. What are the other two.

Last, but not least, you claim that you close all the entries of the database. What do you mean by that sentence and what is it that you actually do ???

[26 Jul 2019 1:21] laoyang yang

Hello! The three queries I'm talking about refer to the same SQL statement.

All conditions are the same, three consecutive queries.

Query results are different

[26 Jul 2019 1:25] laoyang yang

By closing entries, I mean that when I query, I do not add any actions to modify the database.

[26 Jul 2019 12:28] MySQL Verification Team

Hello Mr. Yang,

First of all, you did not answer all of our questions.

Second, I tried running a very similar query to yours and I have got the same result all three times.

Last, and most important, you are using an old release, so please try 5.7.27 and let me know if you can repeat the behaviour. This is because we do not have a habit of chasing the bugs that are already fixed.