Details
-
Bug
-
Status: Confirmed (View Workflow)
-
Major
-
Resolution: Unresolved
-
11.1.2, 10.5, 10.6, 10.9(EOL), 10.10(EOL), 10.11, 11.0(EOL)
Description
The same query returns different results under different configuration 'mrr':
CREATE TABLE t1(c0 INT , c1 VARCHAR(100), c2 VARCHAR(100) , PRIMARY KEY(c0, c1)) engine=MyISAM; |
CREATE TABLE t2(c0 INT); |
CREATE TABLE t3(c0 BOOLEAN); |
INSERT INTO t1 VALUES (2000, 'b', ''); |
INSERT INTO t1 VALUES (1000, '', 'a'); |
INSERT INTO t2 VALUES (3000); |
|
|
mysql> SELECT * FROM t2 STRAIGHT_JOIN t1 ON (t1.c2 RLIKE t1.c1) WHERE t1.c0; |
+------+------+----+------+ |
| c0 | c0 | c1 | c2 |
|
+------+------+----+------+ |
| 3000 | 1000 | | a |
|
+------+------+----+------+ |
|
|
mysql> SET STATEMENT optimizer_switch = 'mrr=on' FOR SELECT * FROM t2 STRAIGHT_JOIN t1 ON (t1.c2 RLIKE t1.c1) WHERE t1.c0; |
Empty set (0.00 sec) |
|
|
|
|
mysql> select version(); |
+---------------------------------------+ |
| version() |
|
+---------------------------------------+ |
| 11.1.2-MariaDB-1:11.1.2+maria~ubu2204 |
|
+---------------------------------------+ |
1 row in set (0.00 sec) |
Attachments
Issue Links
- relates to
-
-
- Confirmed
-
Thank you for the report!
I repeated as described on 10.5-11.1, 10.4 returned the expected (empty ) result.
MariaDB [test]> SELECT * FROM t2 STRAIGHT_JOIN t1 ON (t1.c2 RLIKE t1.c1) WHERE t1.c0;+------+------+----+------+| c0 | c0 | c1 | c2 |+------+------+----+------+| 3000 | 1000 | | a |+------+------+----+------+1 row in set (0,001 sec)MariaDB [test]> SELECT * FROM t2 STRAIGHT_JOIN t1 ON (t1.c2 RLIKE t1.c1);Empty set (0,000 sec)MariaDB [test]> SET STATEMENT optimizer_switch = 'mrr=on' FOR SELECT * FROM t2 STRAIGHT_JOIN t1 ON (t1.c2 RLIKE t1.c1) WHERE t1.c0;Empty set (0,001 sec)MariaDB [test]> explain extended SELECT * FROM t2 STRAIGHT_JOIN t1 ON (t1.c2 RLIKE t1.c1) WHERE t1.c0;+------+-------------+-------+-------+---------------+---------+---------+------+------+----------+------------------------------------------------------------------------+| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |+------+-------------+-------+-------+---------------+---------+---------+------+------+----------+------------------------------------------------------------------------+| 1 | SIMPLE | t2 | ALL | NULL | NULL | NULL | NULL | 1 | 100.00 | || 1 | SIMPLE | t1 | range | PRIMARY | PRIMARY | 4 | NULL | 2 | 100.00 | Using index condition; Using where; Using join buffer (flat, BNL join) |+------+-------------+-------+-------+---------------+---------+---------+------+------+----------+------------------------------------------------------------------------+2 rows in set, 1 warning (0,001 sec)Note (Code 1003): select `test`.`t2`.`c0` AS `c0`,`test`.`t1`.`c0` AS `c0`,`test`.`t1`.`c1` AS `c1`,`test`.`t1`.`c2` AS `c2` from `test`.`t2` straight_join `test`.`t1` where `test`.`t1`.`c0` <> 0 and `test`.`t1`.`c2` regexp `test`.`t1`.`c1`