Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-32143

Unexpected Results by mrr

    XMLWordPrintable

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)
    • 10.5, 10.6, 10.11
    • Optimizer

    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

          Activity

            People

              psergei Sergei Petrunia
              bajinsheng Jinsheng Ba
              Votes:
              0 Vote for this issue
              Watchers:
              2 Start watching this issue

              Dates

                Created:
                Updated:

                Git Integration

                  Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.