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 ] |
| Link |
This issue is caused by |
| 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 ] |