Details
-
Bug
-
Status: Closed (View Workflow)
-
Blocker
-
Resolution: Fixed
-
10.10(EOL), 10.11
-
None
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
-
MDEV-27256
Delete spider_use_handler and related code
-
- Closed
-
Activity
| Field | Original Value | New Value |
|---|---|---|
| Fix Version/s | 10.10 [ 27530 ] | |
| Fix Version/s | 10.11 [ 27614 ] | |
| Assignee | Nayuta Yanagisawa [ JIRAUSER47117 ] |
| 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 @@ -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 ! |
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: {code:sql} SELECT a.pkey FROM tbl_a a, tbl_b b WHERE a.pkey+0 = b.pkey+0 {code} How to repeat: Apply this patch: {code:diff} --- 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) {code} run {noformat} mtr spider.partition_mrr {noformat} 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 ! |
| Status | Open [ 1 ] | Confirmed [ 10101 ] |
| Status | Confirmed [ 10101 ] | In Progress [ 3 ] |
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
|
| Link |
This issue is caused by |
I will fix the problem by reverting MDEV-27256. https://github.com/MariaDB/server/commit/d2e45acb3e9fa74e7f9b2dbb16935f808d5574e3
| Fix Version/s | 10.10.2 [ 28410 ] | |
| Fix Version/s | 10.11.1 [ 28454 ] | |
| Fix Version/s | 10.10 [ 27530 ] | |
| Fix Version/s | 10.11 [ 27614 ] | |
| Resolution | Fixed [ 1 ] | |
| Status | In Progress [ 3 ] | Closed [ 6 ] |
The test case start to fail from d66f6f0cb464d40077dd0a91d55ef3e2179b3e6b.