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

Spider doesn't return all rows when doing a join of two tables with no usable keys

    XMLWordPrintable

    Details

      Description

      When spider is trying to join two tables when there is no usable index or the optimizer chooses
      an index scan, the result does not contain all rows!

      The test query that fails is very simple:

      SELECT a.pkey FROM tbl_a a, tbl_b b WHERE a.pkey+0 = b.pkey+0
      

      How to repeat:

      Apply this patch:

      --- b/storage/spider/mysql-test/spider/t/partition_mrr.test
      +++ b/storage/spider/mysql-test/spider/t/partition_mrr.test
      @@ -168,7 +168,10 @@ if ($USE_CHILD_GROUP2)
         }
       }
       --connection master_1
      +explain SELECT a.pkey FROM tbl_a a, tbl_b b WHERE a.pkey = b.pkey;
       SELECT a.pkey FROM tbl_a a, tbl_b b WHERE a.pkey = b.pkey;
      +explain SELECT a.pkey FROM tbl_a a, tbl_b b WHERE a.pkey+0 = b.pkey+0;
      +SELECT a.pkey FROM tbl_a a, tbl_b b WHERE a.pkey+0 = b.pkey+0;
       if ($USE_CHILD_GROUP2)
       {
         if (!$OUTPUT_CHILD_GROUP2)
      

      run

      mtr spider.partition_mrr
      

      The first query, which uses index, returns rows 0..29 (In random order, but that is ok)
      Second query returns rows 0...12

      MariaDB 10.9 does not have this bug !

        Attachments

          Issue Links

            Activity

              People

              Assignee:
              nayuta-yanagisawa Nayuta Yanagisawa
              Reporter:
              monty Michael Widenius
              Votes:
              0 Vote for this issue
              Watchers:
              7 Start watching this issue

                Dates

                Created:
                Updated:
                Resolved:

                  Git Integration

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