gistfile1.txt

Trying an example from https://www.fromdual.com/mariadb-indexing-of-null-values, part #2.
With MyRocks storage engine, the best query plan is picked automatically. This proves the problem is in innodb, not the optimizer.

MariaDB [test3]> set rocksdb_max_row_locks=1024*1024*500;
Query OK, 0 rows affected (0.00 sec)

MariaDB [test3]> set storage_engine=rocksdb;                       
Query OK, 0 rows affected (0.00 sec)

MariaDB [test3]> create table ten(a int);
Query OK, 0 rows affected (0.01 sec)

MariaDB [test3]> insert into ten values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
Query OK, 10 rows affected (0.00 sec)
Records: 10  Duplicates: 0  Warnings: 0

MariaDB [test3]> 
MariaDB [test3]> create table one_k(a int);
Query OK, 0 rows affected (0.02 sec)

MariaDB [test3]> insert into one_k select A.a + B.a* 10 + C.a * 100 from ten A, ten B, ten C;
Query OK, 1000 rows affected (0.01 sec)
Records: 1000  Duplicates: 0  Warnings: 0

MariaDB [test3]> 
MariaDB [test3]> CREATE TABLE null_test (
    ->   id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY
    -> , data VARCHAR(32) DEFAULT NULL
    -> , ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP() 
    -> );
Query OK, 0 rows affected (0.01 sec)

MariaDB [test3]> 
MariaDB [test3]> insert into null_test select NULL,  'Some data to show if null works', NULL from one_k A, one_k B;
Query OK, 1000000 rows affected (15.20 sec)
Records: 1000000  Duplicates: 0  Warnings: 0

MariaDB [test3]> 
MariaDB [test3]> 
MariaDB [test3]> UPDATE null_test SET data = NULL WHERE ID % 1000 = 0;
Query OK, 1000 rows affected (5.77 sec)
Rows matched: 1000  Changed: 1000  Warnings: 0

MariaDB [test3]> ALTER TABLE null_test ADD INDEX (data);
Query OK, 0 rows affected (4.51 sec)
Records: 0  Duplicates: 0  Warnings: 0

MariaDB [test3]> ANALYZE TABLE null_test;
+-----------------+---------+----------+----------+
| Table           | Op      | Msg_type | Msg_text |
+-----------------+---------+----------+----------+
| test3.null_test | analyze | status   | OK       |
+-----------------+---------+----------+----------+
1 row in set (0.00 sec)

MariaDB [test3]> 
MariaDB [test3]> EXPLAIN EXTENDED
    -> SELECT max(ts) FROM null_test WHERE data IS NULL;
+------+-------------+-----------+------+---------------+------+---------+-------+------+----------+-----------------------+
| id   | select_type | table     | type | possible_keys | key  | key_len | ref   | rows | filtered | Extra                 |
+------+-------------+-----------+------+---------------+------+---------+-------+------+----------+-----------------------+
|    1 | SIMPLE      | null_test | ref  | data          | data | 35      | const |  468 |   100.00 | Using index condition |
+------+-------------+-----------+------+---------------+------+---------+-------+------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)

MariaDB [test3]> 
MariaDB [test3]> SELECT max(ts) FROM null_test force index(data) WHERE data IS NULL;
+---------------------+
| max(ts)             |
+---------------------+
| 2018-11-30 16:33:37 |
+---------------------+
1 row in set (0.01 sec)

MariaDB [test3]> SELECT max(ts) FROM null_test force index(data) WHERE data IS NULL;
+---------------------+
| max(ts)             |
+---------------------+
| 2018-11-30 16:33:37 |
+---------------------+
1 row in set (0.01 sec)

MariaDB [test3]> SELECT max(ts) FROM null_test force index(data) WHERE data IS NULL;
+---------------------+
| max(ts)             |
+---------------------+
| 2018-11-30 16:33:37 |
+---------------------+
1 row in set (0.01 sec)

MariaDB [test3]> 
MariaDB [test3]> SELECT max(ts) FROM null_test ignore index(data) WHERE data IS NULL;
+---------------------+
| max(ts)             |
+---------------------+
| 2018-11-30 16:33:37 |
+---------------------+
1 row in set (0.56 sec)

MariaDB [test3]> SELECT max(ts) FROM null_test ignore index(data) WHERE data IS NULL;
+---------------------+
| max(ts)             |
+---------------------+
| 2018-11-30 16:33:37 |
+---------------------+
1 row in set (0.49 sec)

MariaDB [test3]> SELECT max(ts) FROM null_test ignore index(data) WHERE data IS NULL;
+---------------------+
| max(ts)             |
+---------------------+
| 2018-11-30 16:33:37 |
+---------------------+
1 row in set (0.49 sec)

MariaDB [test3]> 
MariaDB [test3]> EXPLAIN EXTENDED
    -> SELECT max(ts) FROM null_test WHERE data = 'Some data to show if null works';
+------+-------------+-----------+------+---------------+------+---------+------+---------+----------+-------------+
| id   | select_type | table     | type | possible_keys | key  | key_len | ref  | rows    | filtered | Extra       |
+------+-------------+-----------+------+---------------+------+---------+------+---------+----------+-------------+
|    1 | SIMPLE      | null_test | ALL  | data          | NULL | NULL    | NULL | 1001000 |    99.00 | Using where |
+------+-------------+-----------+------+---------------+------+---------+------+---------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

MariaDB [test3]> 
MariaDB [test3]> SELECT max(ts) FROM null_test force index(data) WHERE data = 'Some data to show if null works';
+---------------------+
| max(ts)             |
+---------------------+
| 2018-11-30 16:33:37 |
+---------------------+
1 row in set (5.09 sec)

MariaDB [test3]> SELECT max(ts) FROM null_test force index(data) WHERE data = 'Some data to show if null works';
+---------------------+
| max(ts)             |
+---------------------+
| 2018-11-30 16:33:37 |
+---------------------+
1 row in set (8.65 sec)

MariaDB [test3]> SELECT max(ts) FROM null_test force index(data) WHERE data = 'Some data to show if null works';
+---------------------+
| max(ts)             |
+---------------------+
| 2018-11-30 16:33:37 |
+---------------------+
1 row in set (4.98 sec)

MariaDB [test3]> 
MariaDB [test3]> SELECT max(ts) FROM null_test ignore index(data) WHERE data = 'Some data to show if null works';
+---------------------+
| max(ts)             |
+---------------------+
| 2018-11-30 16:33:37 |
+---------------------+
1 row in set (1.42 sec)

MariaDB [test3]> SELECT max(ts) FROM null_test ignore index(data) WHERE data = 'Some data to show if null works';
+---------------------+
| max(ts)             |
+---------------------+
| 2018-11-30 16:33:37 |
+---------------------+
1 row in set (1.42 sec)

MariaDB [test3]> SELECT max(ts) FROM null_test ignore index(data) WHERE data = 'Some data to show if null works';
+---------------------+
| max(ts)             |
+---------------------+
| 2018-11-30 16:33:37 |
+---------------------+
1 row in set (1.42 sec)

MariaDB [test3]>