Bug #108686 Bug usibg limit 1 sub select

• Submitted: 5 Oct 2022 11:08
• Modified: 5 Oct 2022 13:36
• Reporter: Antonio Remualdo
• Status: Can't repeat
• Category: MySQL Server: Optimizer
• Severity: S3 (Non-critical)
• Version: 8.0.28
• OS: Any
• CPU Architecture: x86

[5 Oct 2022 11:08] Antonio Remualdo

Description:
Hello, we update our MySQL server 5 to 8 and we found somes error in this process.

I have a select, this use a subselect, when i put a limit 1 the result is null but if I remove that limit or put limit different the 1 the value is returned.

That error didnt happen in a old mysql version, only in this new version.

With error some procedures needs be rewrite because the final result its different between the Mysql 5 and Mysql 8

How to repeat:
Run the script, in the final you have 2 columns val_test_bad(with error) and val_test_ok, only difference between these columns is de limit 1.

[5 Oct 2022 11:52] MySQL Verification Team

Hi Mr. Remualdo,

Thank you for your bug report.

However, we need additional info from you.

Your query is very complex and it contains several nested queries. Our question is simple:

Do you get the same error if you use these nested queries as the independent SQL statements ????

We are waiting on your feedback.

[5 Oct 2022 12:21] MySQL Verification Team

Hi Mr. Remualdo,

We have tested your queries with both 8.0.30 and 8.0.31 and we can not repeat the bug that you are reporting.

We have first extracted your 4 (four) nested queries in the separate simple queries and then we ran your large SELECT and we got very consistent results:

SUM(COALESCE(mco.val_preco_medio,0))
26.23131338
SUM(COALESCE(mco.val_preco_medio,0))
26.23131338
SUM(COALESCE(mco.val_preco_medio,0))
26.23131338
SUM(COALESCE(mco.val_preco_medio,0))
26.23131338

cod_centro	dsc_sku	cod_material	dat_last_date_base	dat_data	val_preco_estoque_inicial_cervejaria	val_test_bad	val_test_ok	val_test_ok	val_test_ok3
0	Avangard	50463038	2022-08-01	2022-07-01	0	NULL	NULL	NULL	NULL
BR01	Avangard	50463038	2022-08-01	2022-07-01	11.99975871	NULL	11.99975871	11.99975871	11.99975871
BR02	Avangard	50463038	2022-08-01	2022-07-01	0	NULL	NULL	NULL	NULL
BR03	Avangard	50463038	2022-08-01	2022-07-01	0	NULL	NULL	NULL	NULL
BR04	Avangard	50463038	2022-08-01	2022-07-01	5.719623523	NULL	5.719623523	5.719623523	5.719623523
BR05	Avangard	50463038	2022-08-01	2022-07-01	0	NULL	NULL	NULL	NULL

Can't repeat.

[5 Oct 2022 13:04] MySQL Verification Team

Hi Mr. Remualdo,

Sorry , but we only test in our environment with the latest MySQL build available.

Can't repeat.

[5 Oct 2022 13:04] Antonio Remualdo

I attach a image, you can see 4 subselects, only different between them its the limit.

If limit is 1, the return is null, but if limit is different the 1 or remove limit, the value return.

[5 Oct 2022 13:06] Antonio Remualdo

I can create a database with 8.0.30 to test, give a minutes.

[5 Oct 2022 13:27] Antonio Remualdo

Focus analysis in the second and fifth row.

[5 Oct 2022 13:28] MySQL Verification Team

Hi Mr. Remualdo,

Sorry , we shall repeat it once again. We only test in our environment with the latest MySQL build available.

Can't repeat.

[5 Oct 2022 13:29] Antonio Remualdo

But 8.0.30 is the last no?

[5 Oct 2022 13:31] MySQL Verification Team

Hi,

8.0.30 is a release.

The environment we use is not AWS.

[5 Oct 2022 13:36] Antonio Remualdo

Ok, I'll create a database in my local machine, than you can analysis this environment?

[5 Oct 2022 14:02] MySQL Verification Team

Hi,

Sorry, but we only analyse in our environment.

We do not test bugs on other machines.

This report is closed now ......