MySQL Bugs: #102850: MySQL does not use functional index on the function LOG10

Bug #102850	MySQL does not use functional index on the function LOG10
Submitted:	7 Mar 2021 18:55	Modified:	12 Mar 2021 14:03
Reporter:	Sveta Smirnova (OCA)	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S5 (Performance)
Version:	8.0.23	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
MySQL does not use the functional index on the function LOG10

How to repeat:
mysql> CREATE TABLE expdata ( id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY, 
    -> value FLOAT,
    -> INDEX log10_fi ((log10(value)))
    -> );
Query OK, 0 rows affected (0.02 sec)

mysql> INSERT INTO expdata (value) VALUES (.01),(.1),(1),(10),(100);
Query OK, 5 rows affected (0.01 sec)
Records: 5  Duplicates: 0  Warnings: 0

mysql> SELECT id, value, log10(value) FROM expdata;
+----+-------+--------------------+
| id | value | log10(value)       |
+----+-------+--------------------+
|  1 |  0.01 | -2.000000009707238 |
|  2 |   0.1 | -0.999999993528508 |
|  3 |     1 |                  0 |
|  4 |    10 |                  1 |
|  5 |   100 |                  2 |
+----+-------+--------------------+
5 rows in set (0.00 sec)

mysql> EXPLAIN SELECT * FROM expdata WHERE log10(value) < 0;
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table   | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | expdata | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    5 |   100.00 | Using where |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

Note (Code 1003): /* select#1 */ select `cookbook`.`expdata`.`id` AS `id`,`cookbook`.`expdata`.`value` AS `value` from `cookbook`.`expdata` where (log10(`cookbook`.`expdata`.`value`) < 0)

-- OK, maybe it complains about range? What about equality?
mysql> EXPLAIN SELECT * FROM expdata WHERE log10(value) = 0;
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table   | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | expdata | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    5 |   100.00 | Using where |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

Note (Code 1003): /* select#1 */ select `cookbook`.`expdata`.`id` AS `id`,`cookbook`.`expdata`.`value` AS `value` from `cookbook`.`expdata` where (log10(`cookbook`.`expdata`.`value`) = 0)

-- Maybe the reason is that our column defined as FLOAT that makes any function not deterministic?
-- Let's try with DECIMAL for which LOG10 will always generate deterministic values
mysql> DROP TABLE expdata;
Query OK, 0 rows affected (0.02 sec)

mysql> CREATE TABLE expdata ( id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY, 
    -> value DECIMAL (10, 2), 
    -> INDEX log10_fi ((log10(value)))
    -> );
Query OK, 0 rows affected (0.03 sec)

mysql> 
mysql> INSERT INTO expdata (value) VALUES (.01),(.1),(1),(10),(100);
Query OK, 5 rows affected (0.01 sec)
Records: 5  Duplicates: 0  Warnings: 0

mysql> SELECT id, value, log10(value) FROM expdata;
+----+--------+--------------+
| id | value  | log10(value) |
+----+--------+--------------+
|  1 |   0.01 |           -2 |
|  2 |   0.10 |           -1 |
|  3 |   1.00 |            0 |
|  4 |  10.00 |            1 |
|  5 | 100.00 |            2 |
+----+--------+--------------+
5 rows in set (0.00 sec)

mysql> 
mysql> EXPLAIN SELECT * FROM expdata WHERE log10(value) < 0;
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table   | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | expdata | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    5 |   100.00 | Using where |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

Note (Code 1003): /* select#1 */ select `cookbook`.`expdata`.`id` AS `id`,`cookbook`.`expdata`.`value` AS `value` from `cookbook`.`expdata` where (log10(`cookbook`.`expdata`.`value`) < 0)
mysql> EXPLAIN SELECT * FROM expdata WHERE log10(value) = 0;
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table   | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | expdata | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    5 |   100.00 | Using where |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

Note (Code 1003): /* select#1 */ select `cookbook`.`expdata`.`id` AS `id`,`cookbook`.`expdata`.`value` AS `value` from `cookbook`.`expdata` where (log10(`cookbook`.`expdata`.`value`) = 0)

-- Still no luck!
-- What about PostgreSQL?
postgres=# create table expdata(id int, value float);
CREATE TABLE
postgres=# CREATE INDEX test_log10_fi on expdata (log10(value));
CREATE INDEX
postgres=# insert into expdata values(1,.01),(2,.1),(3,1),(4,10),(5,100);
INSERT 0 5
postgres=# explain SELECT * FROM expdata WHERE log10(value) < 0;
                                  QUERY PLAN                                  
------------------------------------------------------------------------------
 Bitmap Heap Scan on expdata  (cost=9.42..29.62 rows=680 width=12)
   Recheck Cond: (log10(value) < '0'::double precision)
   ->  Bitmap Index Scan on test_log10_fi  (cost=0.00..9.25 rows=680 width=0)
         Index Cond: (log10(value) < '0'::double precision)
(4 rows)

postgres=# explain SELECT * FROM expdata WHERE log10(value) = 0;
                                 QUERY PLAN                                  
-----------------------------------------------------------------------------
 Bitmap Heap Scan on expdata  (cost=4.23..14.81 rows=10 width=12)
   Recheck Cond: (log10(value) = '0'::double precision)
   ->  Bitmap Index Scan on test_log10_fi  (cost=0.00..4.23 rows=10 width=0)
         Index Cond: (log10(value) = '0'::double precision)
(4 rows)

It perfectly works!

Suggested fix:
Use functional indexes on LOG10. Probably test all built-in deterministic functions?

Test case for MTR

Attachment: log10fi.test (application/octet-stream, text), 835 bytes.

Hello Sveta,

Thank you for the report and feedback.
Verified as described.

regards,
Umesh

The reason for not using the index is that function LOG10 returns result in DOUBLE and MySQL has to convert it to the value on the right side of the comparison operator.

Workaround is to cast explicitly:

mysql> explain select value, log10(value) from expdata where log10(value) < cast(0 as double)\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: expdata
   partitions: NULL
         type: range
possible_keys: log10_fi
          key: log10_fi
      key_len: 9
          ref: NULL
         rows: 2
     filtered: 100.00
        Extra: Using where
1 row in set, 1 warning (0.00 sec)

I still believe this is a bug, because Optimizer could perform such a cast itself.

The simplest workaround is to use a proper floating point constant value:

explain select value, log10(value) from expdata where log10(value) < 0.0E0;

I agree we should do the conversion in the optimizer.