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

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

          is caused by

          Technical task - A technical task. MDEV-27256 Delete spider_use_handler and related code

          • Major - Major loss of function.
          • Closed

          Activity

            People

              nayuta-yanagisawa Nayuta Yanagisawa (Inactive)
              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.