[MDEV-20502] Queries against spider tables return wrong values for columns following constant declarations. Created: 2019-09-04  Updated: 2023-02-02  Resolved: 2020-05-05

Status: Closed
Project: MariaDB Server
Component/s: Storage Engine - Spider
Affects Version/s: 10.4.7, 10.3.25
Fix Version/s: 10.4.13, 10.5.3, 10.3.28

Type: Bug Priority: Critical
Reporter: Juan Assignee: Kentoku Shiba (Inactive)
Resolution: Fixed Votes: 0
Labels: None

Issue Links:
Problem/Incident
causes MDEV-26345 SELECT MIN on Spider table returns mo... Stalled
Relates
relates to MDEV-29502 ASAN: heap-buffer-overflow & stack-bu... Closed

 Description   

On source server:

CREATE TABLE simple (
 
id int(10) unsigned NOT NULL AUTO_INCREMENT,  
val int(10) unsigned DEFAULT NULL, 
PRIMARY KEY (id),   ENGINE=InnoDB;
 
 
 
insert into simple(val) values(1);

On spider server:

create server s31689 foreign data wrapper mysql options (
 
host '10.0.0.241' ,
 
database 'd31689' ,
 
user 'spider' ,
 
password 'spider' ,
 
port 3306 );
 
 
 
CREATE TABLE simple (
 
id int(10) unsigned NOT NULL AUTO_INCREMENT,  
val int(10) unsigned DEFAULT NULL, 
PRIMARY KEY (id),   ENGINE=SPIDER 
DEFAULT CHARSET=utf8
 
COMMENT='srv "s31689", table "simple"'
 
 
 
Query OK, 0 rows affected (0.026 sec)
 
 
 
MariaDB [d31689]> select * from simple;
 
+----+------+
 
| id | val  |
 
+----+------+
 
|  1 |    1 |
 
+----+------+
 
1 row in set (0.015 sec)
 
 
 
MariaDB [d31689]> select id, 0 as const, val from simple;
 
+----+-------+------+
 
| id | const | val  |
 
+----+-------+------+
 
|  1 |     0 |    0 |
 
+----+-------+------+
 
1 row in set (0.002 sec)



 Comments   
Comment by Kentoku Shiba (Inactive) [ 2020-04-14 ]

When executing a query like "select id, 0 as const, val from ...", there are 3 columns(items) in Query->select at handlerton->create_group_by(). After that, MariaDB makes a temporary table with 2 columns.
How can I know which columns are skipped for the temporary table?

Comment by Igor Babaev [ 2020-04-15 ]

Kentoku,
With what group by queries do you have problems? Give me an example please.

Comment by Kentoku Shiba (Inactive) [ 2020-04-24 ]

igor,
When executing a query like "select max(id), 0 as const, group_id from ...", there are 3 columns(items) in Query->select at handlerton->create_group_by(). After that, MariaDB makes a temporary table with 2 columns.
I heard from Sergei that const items in select clause are skipped for columns of temporary table of group by handler.
So I fixed that Spider skips const items in select clause at executing group by handler. 4d47d73
Is it ok for you?

Comment by Igor Babaev [ 2020-05-04 ]

Kentoku,

The patch is ok. I would suggest only to add the following to your test case:

 SELECT id, 0 AS const, val FROM tbl_a;
 
 id     const   val
 
 1      0       1
 
+SELECT 1+2, id, 0 AS const, val, val+10, (SELECT tbl_a.val+1 FROM tbl_a) AS sq
 
+FROM tbl_a;
 
+1+2    id      const   val     val+10  sq
 
+3      1       0       1       11      2
 
+INSERT INTO tbl_a (val) VALUES (2), (1);
 
+SELECT val+10, 0 AS const, val, (SELECT tbl_a.val+1 FROM tbl_a LIMIT 1) AS sq
 
+FROM tbl_a GROUP BY val;
 
+val+10 const   val     sq
 
+11     0       1       2
 
+12     0       2       2
 
+SELECT MAX(id) AS m, 0 AS const, val, (SELECT tbl_a.val+1 FROM tbl_a LIMIT 1) AS sq
 
+FROM tbl_a GROUP BY val;
 
+m      const   val     sq
 
+3      0       1       2
 
+2      0       2       2
 
 connection child2_1;
 
 SELECT argument FROM mysql.general_log WHERE argument LIKE '%select %';

Comment by Igor Babaev [ 2020-05-04 ]

The patch is ok, but see my feedback in the last comment.

Comment by Kentoku Shiba (Inactive) [ 2020-05-05 ]

Add test cases, rebuild, test, commit, push

Comment by Valerii Kravchuk [ 2020-12-10 ]

Can we get the fix backported to 10.3 too?

Comment by Sergei Golubchik [ 2021-01-12 ]

done

Generated at Thu Feb 08 08:59:58 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.