[MDEV-26315] Group aggregation with sql_big_result returns wrong result on Spider Created: 2021-08-06  Updated: 2023-05-23

Status: Confirmed
Project: MariaDB Server
Component/s: Storage Engine - Spider
Affects Version/s: 10.3, 10.4, 10.5, 10.6
Fix Version/s: 10.4, 10.5, 10.6

Type: Bug Priority: Major
Reporter: Yongxin Xu Assignee: Yuchen Pei
Resolution: Unresolved Votes: 1
Labels: bug, not-10.2
Environment:

ALL


 Description   

How to repeat

CREATE TABLE t3 (
 
  spID int(10) unsigned,
 
  userID int(10) unsigned,
 
  score smallint(5) unsigned,
 
  key (spid),
 
  key (score)
 
)ENGINE=SPIDER COMMENT='wrapper "mysql", table "t3"'
 
PARTITION BY LIST (spID%2)
 
(
 
    PARTITION pt0 VALUES IN (0) COMMENT = 'database "test1_0", table "t3", server "SPT0"',
 
    PARTITION pt1 VALUES IN (1) COMMENT = 'database "test1_1", table "t3", server "SPT1"'
 
);
 
 
 
INSERT INTO t3 VALUES
 
(1,1,1),(2,2,2),(2,1,1),(3,3,3),(4,3,3),(5,3,3),(6,3,3),(7,3,3);
 
 
 
select sql_big_result spid,sum(userid) from t3 group by spid
 
desc; --bug

The result of the final SELECT is 

select sql_big_result spid,sum(userid) from t3 group by spid
 
    -> desc;
 
+------+-------------+
 
| spid | sum(userid) |
 
+------+-------------+
 
|    7 |           3 |
 
|    6 |           3 |
 
|    5 |           3 |
 
|    4 |           3 |
 
|    3 |           3 |
 
|    2 |           2 | --> sum(userid) should be 3 here!
 
|    1 |           1 |
 
+------+-------------+
 
7 rows in set (0.20 sec)

Solution

We solve the problem by simply prohibiting `sql_big_result` from using on the Spider SE.
https://github.com/Tencent/TenDBCluster-TSpider/commit/01c278b6b1b27ce95a8ecc30b0bc9dd90d8abd70

 Comments   
Comment by Yongxin Xu [ 2021-08-06 ]

nayuta-yanagisawa
I'm checking if some of our bugs can be reproducible on MariaDB Spider. This is one I've just found.

We prohibit the `sql_big_result` from using because we want to keep Spider as simple and stable as possible. Perhaps you can find a thorough solution with more code and checks.

Comment by Nayuta Yanagisawa (Inactive) [ 2021-08-11 ]

Yongxin Xu Thank you for your report! I will check it soon.

Comment by Nayuta Yanagisawa (Inactive) [ 2021-08-13 ]

The bug is reproducible on 10.3, 10.4, 10.5, and 10.6 HEAD, not on 10.2 HEAD

Generated at Thu Feb 08 09:44:22 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.