Bug #112802 Inconsistent results when using INDEX

• Submitted: 23 Oct 2023 8:23
• Modified: 23 Oct 2023 8:37
• Reporter: Wang Ke
• Status: Verified
• Category: MySQL Server: Optimizer
• Severity: S3 (Non-critical)
• Version: 8.1.0, 8.0.34
• OS: Any
• CPU Architecture: Any

[23 Oct 2023 8:23] Wang Ke

Description:
Inconsistent results were obtained when added a new index on a column:

```
mysql> CREATE TABLE t0 ( c0 INT , c1 BOOLEAN, c2 YEAR );
Query OK, 0 rows affected (0.15 sec)

mysql> INSERT INTO t0 VALUES (NULL, NULL, NULL);
Query OK, 1 row affected (0.03 sec)

mysql> INSERT INTO t0 VALUES (1, 0, 1);
Query OK, 1 row affected (0.03 sec)

mysql> INSERT INTO t0 VALUES (1, 1, 0);
Query OK, 1 row affected (0.01 sec)

mysql> INSERT INTO t0 VALUES (-1, 0, 0);
Query OK, 1 row affected (0.03 sec)

mysql> SELECT ra1.c2 FROM t0 ra1 LEFT JOIN t0 ra2 ON ra2.c2 = LEFT( ra1.c1 , 1 );
+------+
| c2   |
+------+
| NULL |
| 2001 |
| 2001 |
| 0000 |
| 0000 |
| 0000 |
+------+
6 rows in set (0.00 sec)

mysql> EXPLAIN SELECT ra1.c2 FROM t0 ra1 LEFT JOIN t0 ra2 ON ra2.c2 = LEFT( ra1.c1 , 1 );
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+--------------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra                                      |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+--------------------------------------------+
|  1 | SIMPLE      | ra1   | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    4 |   100.00 | NULL                                       |
|  1 | SIMPLE      | ra2   | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    4 |   100.00 | Using where; Using join buffer (hash join) |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+--------------------------------------------+
2 rows in set, 1 warning (0.00 sec)

mysql> CREATE INDEX i0 TYPE RTREE ON t0 ( c2 ) USING BTREE ;
Query OK, 0 rows affected (0.17 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> SELECT ra1.c2 FROM t0 ra1 LEFT JOIN t0 ra2 ON ra2.c2 = LEFT( ra1.c1 , 1 );
+------+
| c2   |
+------+
| NULL |
| 2001 |
| 0000 |
| 0000 |
+------+
4 rows in set (0.01 sec)

mysql> EXPLAIN SELECT ra1.c2 FROM t0 ra1 LEFT JOIN t0 ra2 ON ra2.c2 = LEFT( ra1.c1 , 1 );
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+--------------------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra                    |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+--------------------------+
|  1 | SIMPLE      | ra1   | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    4 |   100.00 | NULL                     |
|  1 | SIMPLE      | ra2   | NULL       | ref  | i0            | i0   | 2       | func |    1 |   100.00 | Using where; Using index |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+--------------------------+
2 rows in set, 1 warning (0.00 sec)

mysql> 

```

How to repeat:
Run this test case:

```
CREATE TABLE t0 ( c0 INT , c1 BOOLEAN, c2 YEAR );
INSERT INTO t0 VALUES (NULL, NULL, NULL);
INSERT INTO t0 VALUES (1, 0, 1);
INSERT INTO t0 VALUES (1, 1, 0);
INSERT INTO t0 VALUES (-1, 0, 0);
SELECT ra1.c2 FROM t0 ra1 LEFT JOIN t0 ra2 ON ra2.c2 = LEFT( ra1.c1 , 1 );
EXPLAIN SELECT ra1.c2 FROM t0 ra1 LEFT JOIN t0 ra2 ON ra2.c2 = LEFT( ra1.c1 , 1 );
CREATE INDEX i0 TYPE RTREE ON t0 ( c2 ) USING BTREE ;
SELECT ra1.c2 FROM t0 ra1 LEFT JOIN t0 ra2 ON ra2.c2 = LEFT( ra1.c1 , 1 );
EXPLAIN SELECT ra1.c2 FROM t0 ra1 LEFT JOIN t0 ra2 ON ra2.c2 = LEFT( ra1.c1 , 1 );
```

[23 Oct 2023 8:37] MySQL Verification Team

Hello Ke Wang,

Thank you for the report and test case.

regards,
Umesh

[27 Oct 2023 9:46] Daniel Blanchard

Posted by developer:
 
I think the strange syntax used to create the index is a red herring.
If the index is created using a simple syntax such as
CREATE INDEX i0 ON t0 ( c2 );
we still see the inconsistency:
mysql> show create table t0;
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table
                                                           |
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| t0    | CREATE TABLE `t0` (
  `c0` int DEFAULT NULL,
  `c1` tinyint(1) DEFAULT NULL,
  `c2` year DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.04 sec)

mysql> CREATE INDEX i0 ON t0 ( c2 );
Query OK, 0 rows affected (0.07 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> SELECT ra1.c2 FROM t0 ra1 LEFT JOIN t0 ra2 ON ra2.c2 = LEFT( ra1.c1 , 1 );
+------+
| c2   |
+------+
| NULL |
| 2001 |
| 0000 |
| 0000 |
+------+
4 rows in set (0.00 sec)

mysql> drop index i0 on t0;
Query OK, 0 rows affected (0.04 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> SELECT ra1.c2 FROM t0 ra1 LEFT JOIN t0 ra2 ON ra2.c2 = LEFT( ra1.c1 , 1 );
+------+
| c2   |
+------+
| NULL |
| 2001 |
| 2001 |
| 0000 |
| 0000 |
| 0000 |
+------+
6 rows in set (0.00 sec)

mysql>