[MDEV-29947] Spider doesn't return all rows when doing a join of two tables with no usable keys Created: 2022-11-04  Updated: 2022-11-26  Resolved: 2022-11-14

Status: Closed
Project: MariaDB Server
Component/s: Storage Engine - Spider
Affects Version/s: 10.10, 10.11
Fix Version/s: 10.10.2, 10.11.1

Type: Bug Priority: Blocker
Reporter: Michael Widenius Assignee: Nayuta Yanagisawa (Inactive)
Resolution: Fixed Votes: 0
Labels: None

Issue Links:
Problem/Incident
is caused by MDEV-27256 Delete spider_use_handler and related... Closed

 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 !

 Comments   
Comment by Nayuta Yanagisawa (Inactive) [ 2022-11-07 ]

The test case start to fail from d66f6f0cb464d40077dd0a91d55ef3e2179b3e6b.

Comment by Nayuta Yanagisawa (Inactive) [ 2022-11-10 ]

The above was a false positive first bad commit. The true first bad commit seems to be 57d233e2a67df35deb8c53c662d79f426615ec95.

57d233e2a67df35deb8c53c662d79f426615ec95 is the first bad commit
 
commit 57d233e2a67df35deb8c53c662d79f426615ec95
 
Author: Nayuta Yanagisawa <nayuta.yanagisawa@hey.com>
 
Date:   Thu Jun 9 17:20:05 2022 +0900
 
 
 
    MDEV-27256 Delete spider_use_handler and related code (2/3)
 
    
    Delete the deprecated variable, spider_use_handler and related code.
 
    
    Spider now does not supports accessing data nodes via handler
 
    statements. Thus, the notion of SQL kinds are no longer useful.
 
    We too discard it.
 
 
 
 storage/spider/ha_spider.cc                        | 584 +------------------
 
 storage/spider/ha_spider.h                         |  26 -
 
 .../spider/mysql-test/spider/bugfix/r/index.result |   9 -
 
 .../spider/mysql-test/spider/bugfix/t/index.test   |   3 -
 
 .../spider/r/variable_deprecation.result           |  15 -
 
 .../mysql-test/spider/t/variable_deprecation.test  |   8 -
 
 storage/spider/spd_conn.cc                         | 114 ----
 
 storage/spider/spd_conn.h                          |  12 -
 
 storage/spider/spd_db_conn.cc                      | 633 +--------------------
 
 storage/spider/spd_db_conn.h                       |  20 -
 
 storage/spider/spd_db_include.h                    |  31 -
 
 storage/spider/spd_db_mysql.cc                     | 404 +------------
 
 storage/spider/spd_db_mysql.h                      |  39 --
 
 storage/spider/spd_group_by_handler.cc             |  24 -
 
 storage/spider/spd_include.h                       |   4 +-
 
 storage/spider/spd_param.cc                        |  27 -
 
 storage/spider/spd_param.h                         |   4 -
 
 storage/spider/spd_table.cc                        |  61 +-
 
 storage/spider/spd_trx.cc                          |   1 -
 
 19 files changed, 38 insertions(+), 1981 deletions(-)
 
bisect found first bad commit

Comment by Nayuta Yanagisawa (Inactive) [ 2022-11-11 ]

I will fix the problem by reverting MDEV-27256. https://github.com/MariaDB/server/commit/d2e45acb3e9fa74e7f9b2dbb16935f808d5574e3

Generated at Thu Feb 08 10:12:29 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.