[MDEV-30445] Wrong result with JOINs involving Spider table Created: 2023-01-21  Updated: 2023-11-28

Status: Open
Project: MariaDB Server
Component/s: Optimizer, Storage Engine - Spider
Affects Version/s: 10.4, 10.5, 10.6, 10.7, 10.8, 10.9, 10.10, 10.11
Fix Version/s: 10.4, 10.5, 10.6, 10.11

Type: Bug Priority: Major
Reporter: Elena Stepanova Assignee: Yuchen Pei
Resolution: Unresolved Votes: 0
Labels: 11.0-sel


 Description   

# This may not work, e.g. for in-source builds, fix the path
--source plugin/spider/spider/include/init_spider.inc
#--source ../storage/spider/mysql-test/spider/include/init_spider.inc
 
SET spider_same_server_link= on;
--eval create server s foreign data wrapper mysql options (host "127.0.0.1", database "test", user "root", port $MASTER_MYPORT);
 
CREATE TABLE t (a INT);
CREATE TABLE t_spider (a INT, KEY(a)) ENGINE=SPIDER COMMENT = "wrapper 'mysql', srv 's', table 't'";
INSERT INTO t VALUES (1),(2);
 
SELECT t1.* FROM t_spider AS t1 LEFT JOIN t_spider AS t2 RIGHT JOIN t_spider AS t3 ON 1 ON t1.a = 1;
 
# Cleanup
DROP TABLE t_spider, t;
DROP SERVER s;
 
# This may not work, e.g. for in-source builds, fix the path
--source plugin/spider/spider/include/deinit_spider.inc
#--source ../storage/spider/mysql-test/spider/include/deinit_spider.inc

10.4 f3f09def

SELECT t1.* FROM t_spider AS t1 LEFT JOIN t_spider AS t2 RIGHT JOIN t_spider AS t3 ON 1 ON t1.a = 1;
a
1
1
2
1
1
2

One of 2 rows shouldn't be there, the expected result is

SELECT t1.* FROM t AS t1 LEFT JOIN t AS t2 RIGHT JOIN t AS t3 ON 1 ON t1.a = 1;
a
1
1
1
1
2


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