MySQL Bugs: #107088: Divide by zero in cost_group_min

Bug #107088	Divide by zero in cost_group_min_max
Submitted:	21 Apr 2022 19:46	Modified:	24 Jun 2022 22:11
Reporter:	Manuel Ung	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S3 (Non-critical)
Version:	8.0.28	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
cost_group_min_max will sometimes divide by zero if there is an empty table on this line:

      quick_prefix_selectivity =
          (double)quick_prefix_records / (double)table_records;

I don't think this adversely affects query planning, though it will cause ubsan errors, increasing the noise.

How to repeat:
Hopefully, this should be obvious by inspection, but it should be easy to reproduce with something like:

create table t (i int, j int, k int, key(i, j, k));
explain select i, j, k from t where i > 2 group by i;

and checking under gdb (or ubsan) that divide by zero is occuring.

Suggested fix:
In get_best_group_min_max, we should skip considering group by plans for empty tables.

Hello Manuel Ung,

Thank you for the report and feedback.

regards,
Umesh

Hi Manuel Ung,

Your concern is unlikely to happen.

The storage engines estimate the number of rows in the table and often add one to a zero value.

Thanks, it seems like this is probably not a bug then. It seems like only engines with HA_STATS_RECORDS_IS_EXACT return 0 for stats.records, and we skip a lot of optimization when the table is empty already, thus avoiding divide by zero.

For all other engines, they should always return something >= 1, so there is no divide by zero issue.