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

[21 Apr 19:46] Manuel Ung
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.
[22 Apr 8:55] MySQL Verification Team
Hello Manuel Ung,

Thank you for the report and feedback.

[27 Apr 7:16] huahua xu
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.
[24 Jun 22:11] Manuel Ung
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.