[MDEV-30392] Syntax error upon query with subquery from Spider table - Jira

Details

Type: Bug
Status: Closed (View Workflow)
Priority: Major
Resolution: Fixed
Affects Version/s: 10.3(EOL), 10.4(EOL), 10.5, 10.6, 10.7(EOL), 10.8(EOL), 10.9(EOL), 10.10(EOL), 10.11
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

Description

# This may not work, e.g. for in-source builds, fix the path

--source plugin/spider/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);

INSERT INTO t VALUES (1),(2);

CREATE TABLE t_spider (a INT) ENGINE=SPIDER COMMENT = "wrapper 'mysql', srv 's', table 't'";

SELECT a FROM t_spider WHERE a IN ( SELECT a FROM t_spider );

# 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

10.4 f97f6955

mysqltest: At line 12: query 'SELECT a FROM t_spider WHERE a IN ( SELECT a FROM t_spider )' failed: 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 '`test`.`t` t1 where 1' at line 1

The complaint is of course about the query that Spider generates to access remote, which is

select t0.`a` `a` from `test`.`t` t0`test`.`t` t1 where 1

Attachments

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

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

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 INT);

INSERT INTO t1 VALUES (1),(2);

CREATE TABLE t2 (a INT) ENGINE=SPIDER COMMENT = "wrapper 'mysql', srv 'srv', table 't1'";

SELECT a FROM t2 WHERE a IN ( SELECT a FROM t2 );

# Cleanup

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:04 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 INT ); INSERT INTO t1 VALUES (1),(2); CREATE TABLE t2 (a INT ) ENGINE=SPIDER COMMENT = "wrapper 'mysql', srv 'srv', table 't1'" ; SELECT a FROM t2 WHERE a IN ( SELECT a FROM t2 ); # Cleanup 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:42

fixed by ~~MDEV-26247~~

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

People

Assignee:: Yuchen Pei

Reporter:: Elena Stepanova

Votes:: 0 Vote for this issue

Watchers:: 2 Start watching this issue

Dates

Created:: 2023-01-12 13:34

Updated:: 2023-11-22 02:53

Resolved:: 2023-11-17 00:42

Git Integration

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

MariaDB Server