MySQL Bugs: #103630: Performance issue, select executed when not required

Bug #103630	Performance issue, select executed when not required
Submitted:	7 May 2021 22:00	Modified:	18 Jun 2021 13:07
Reporter:	Justin Levene	Email Updates:
Status:	No Feedback	Impact on me:	None
Category:	MySQL Server: Stored Routines	Severity:	S5 (Performance)
Version:	8.0.20	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	IF, performance, speed

Description:
Inside a function I have declared a variable

declare _ids text default if(some_var=0, null, (select group_concat(`ID`) from `table`));

When "some_var" equals 0, it seems that the select is still called as the above is slower than:

declare _ids text default (select group_concat(`ID`) from `table`);

How to repeat:
This is a performance bug, see above

Suggested fix:
MySQL should not execute the select statement when the "if" statement deems it not necessary as the result is never used.

Hi Mr. Levene,

Thank you for your bug report.

However, we require more info on your problem.

First of all, declaring variables in stored routines, in the way that you have done it, does not guarantee the correct flow of the routine code.

Please, try separating declaration from the code. In the case that SELECT still gets executed, then we need additional info.

First, full routine code, next, proof that logic in the code does not work and third, a proof that SELECT gets executed in the code too.

Then, check whether the same problem occurs in 8.0.24.

Thanks in advance.

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".