[MDEV-31645] Spider doesn't recognize semi JOIN - Jira

Details

Type: Bug
Status: Closed (View Workflow)
Priority: Major
Resolution: Fixed
Affects Version/s: 10.5.11, 10.5.19, 10.4(EOL), 10.5, 10.6, 10.9(EOL), 10.10(EOL), 10.11, 11.0(EOL)
Fix Version/s: 10.4.33, 10.5.24, 10.6.17, 10.11.7, 11.0.5, 11.1.4, 11.2.3
Component/s: Storage Engine - Spider
Labels:
- spider-gbh
Environment:
Windows x64

Description

Have to disable semijoin optimizer switch due to Spider not recognizing the optimized query

Attachments

- Sort By Name
- Sort By Date
- Ascending
- Descending
- Thumbnails
- List
- Download All

SpiderSemiJoin-1.sql
1 kB
2023-07-07 16:15

Issue Links

is blocked by

MDEV-26247 Spider: Valid LEFT JOIN results in ERROR 1064

Closed

is part of

MDEV-26247 Spider: Valid LEFT JOIN results in ERROR 1064

Closed

relates to

MDEV-32238 Add a switch to disable spider group by handler

Closed

Activity

Ascending order - Click to sort in descending order

Alice Sherepa added a comment - 2023-07-19 15:11

Thank you for the report! I repeated on 10.4-11.0:

set password for 'root'@localhost='';

INSTALL SONAME "ha_spider";

CREATE DATABASE spidertesta;

CREATE TABLE spidertesta.testtable ( testnum bigint(20) NOT NULL, readvalue bigint(20) DEFAULT 0, PRIMARY KEY (testnum)) engine=myisam;

CREATE SERVER localspider FOREIGN DATA WRAPPER mysql OPTIONS( HOST '127.0.0.1', DATABASE 'spidertesta',USER 'root', PASSWORD '',PORT 16000 );

CREATE DATABASE spidertestb;

SET @@spider_same_server_link=1;

CREATE TABLE spidertestb.testtable ( testnum bigint(20) NOT NULL, readvalue bigint(20) DEFAULT 0, PRIMARY KEY (testnum)) ENGINE=SPIDER COMMENT='srv "localspider"';

SET SESSION optimizer_switch='semijoin=off';

SELECT * FROM spidertestb.testtable

WHERE TestNum BETWEEN 0 AND 10 AND ReadValue IN(SELECT ReadValue FROM spidertestb.testtable WHERE TestNum BETWEEN 11 AND 20);

SET SESSION optimizer_switch='semijoin=on';

SELECT * FROM spidertestb.testtable

WHERE TestNum BETWEEN 0 AND 10 AND ReadValue IN(SELECT ReadValue FROM spidertestb.testtable WHERE TestNum BETWEEN 11 AND 20);

second select fails:

query 'SELECT * FROM spidertestb.testtable

WHERE TestNum BETWEEN 0 AND 10 AND ReadValue IN(SELECT ReadValue FROM spidertestb.testtable WHERE TestNum BETWEEN 11 AND 20)' failed: ER_PARSE_ERROR (1064): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '`spidertesta`.`testtable` t1 where ((t1.`readvalue` = t0.`readvalue`) and (t0...' at line 1

Alice Sherepa added a comment - 2023-07-19 15:11 Thank you for the report! I repeated on 10.4-11.0: set password for 'root' @localhost= '' ; INSTALL SONAME "ha_spider" ; CREATE DATABASE spidertesta; CREATE TABLE spidertesta.testtable ( testnum bigint (20) NOT NULL , readvalue bigint (20) DEFAULT 0, PRIMARY KEY (testnum)) engine=myisam; CREATE SERVER localspider FOREIGN DATA WRAPPER mysql OPTIONS( HOST '127.0.0.1' , DATABASE 'spidertesta' , USER 'root' , PASSWORD '' ,PORT 16000 ); CREATE DATABASE spidertestb; SET @@spider_same_server_link=1; CREATE TABLE spidertestb.testtable ( testnum bigint (20) NOT NULL , readvalue bigint (20) DEFAULT 0, PRIMARY KEY (testnum)) ENGINE=SPIDER COMMENT= 'srv "localspider"' ; SET SESSION optimizer_switch= 'semijoin=off' ; SELECT * FROM spidertestb.testtable WHERE TestNum BETWEEN 0 AND 10 AND ReadValue IN ( SELECT ReadValue FROM spidertestb.testtable WHERE TestNum BETWEEN 11 AND 20); SET SESSION optimizer_switch= 'semijoin=on' ; SELECT * FROM spidertestb.testtable WHERE TestNum BETWEEN 0 AND 10 AND ReadValue IN ( SELECT ReadValue FROM spidertestb.testtable WHERE TestNum BETWEEN 11 AND 20); second select fails: query 'SELECT * FROM spidertestb.testtable WHERE TestNum BETWEEN 0 AND 10 AND ReadValue IN(SELECT ReadValue FROM spidertestb.testtable WHERE TestNum BETWEEN 11 AND 20)' failed: ER_PARSE_ERROR (1064): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '`spidertesta`.`testtable` t1 where ((t1.`readvalue` = t0.`readvalue`) and (t0...' at line 1

Yuchen Pei added a comment - 2023-09-22 13:05

mtr case

--disable_query_log

--disable_result_log

--source ../../t/test_init.inc

--enable_result_log

--enable_query_log

evalp CREATE SERVER srv FOREIGN DATA WRAPPER MYSQL OPTIONS (SOCKET "$MASTER_1_MYSOCK", DATABASE 'test',user 'root');

CREATE TABLE t1 ( a bigint(20) NOT NULL, b bigint(20) DEFAULT 0, PRIMARY KEY (a));

CREATE TABLE t2 ( a bigint(20) NOT NULL, b bigint(20) DEFAULT 0, PRIMARY KEY (a)) ENGINE=SPIDER COMMENT='srv "srv", WRAPPER "mysql", TABLE "t1"';

SET SESSION optimizer_switch='semijoin=off';

SELECT * FROM t2

WHERE A BETWEEN 0 AND 10 AND B IN(SELECT B FROM t2 WHERE A BETWEEN 11 AND 20);

SET SESSION optimizer_switch='semijoin=on';

SELECT * FROM t2

WHERE A BETWEEN 0 AND 10 AND B IN(SELECT B FROM t2 WHERE A BETWEEN 11 AND 20);

drop table t1, t2;

drop server srv;

--disable_query_log

--disable_result_log

--source ../../t/test_deinit.inc

--enable_result_log

--enable_query_log

Yuchen Pei added a comment - 2023-09-22 13:05 mtr case --disable_query_log --disable_result_log --source ../../t/test_init.inc --enable_result_log --enable_query_log evalp CREATE SERVER srv FOREIGN DATA WRAPPER MYSQL OPTIONS (SOCKET "$MASTER_1_MYSOCK" , DATABASE 'test' , user 'root' ); CREATE TABLE t1 ( a bigint (20) NOT NULL , b bigint (20) DEFAULT 0, PRIMARY KEY (a)); CREATE TABLE t2 ( a bigint (20) NOT NULL , b bigint (20) DEFAULT 0, PRIMARY KEY (a)) ENGINE=SPIDER COMMENT= 'srv "srv", WRAPPER "mysql", TABLE "t1"' ; SET SESSION optimizer_switch= 'semijoin=off' ; SELECT * FROM t2 WHERE A BETWEEN 0 AND 10 AND B IN ( SELECT B FROM t2 WHERE A BETWEEN 11 AND 20); SET SESSION optimizer_switch= 'semijoin=on' ; SELECT * FROM t2 WHERE A BETWEEN 0 AND 10 AND B IN ( SELECT B FROM t2 WHERE A BETWEEN 11 AND 20); drop table t1, t2; drop server srv; --disable_query_log --disable_result_log --source ../../t/test_deinit.inc --enable_result_log --enable_query_log

Yuchen Pei added a comment - 2023-11-17 00:43

fixed by ~~MDEV-26247~~

Yuchen Pei added a comment - 2023-11-17 00:43 fixed by MDEV-26247

People

Assignee:: Yuchen Pei

Reporter:: Rob G

Votes:: 0 Vote for this issue

Watchers:: 3 Start watching this issue

Dates

Created:: 2023-07-07 16:58

Updated:: 2023-11-22 02:53

Resolved:: 2023-11-17 00:43

Git Integration

Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.

MariaDB Server

Details

Description

Attachments

Attachments

Issue Links

Activity

People

Dates

Git Integration