Details
-
Bug
-
Status: Closed (View Workflow)
-
Critical
-
Resolution: Fixed
-
10.4.11, 10.5.11, 10.3(EOL)
-
Back-end database instance: Mariadb 10.4.11 with 32 GB RAM 6 x 1 core Intel(R) Xeon(R) CPU E7-4850 v3 @ 2.20GHz
Spider node instance: Mariadb 10.4.11 with 300 GB RAM 24 x 1 core Intel(R) Xeon(R) CPU E7-4850 v3 @ 2.20GHz Linux 3.10.0-1127.19.1.el7.x86_64 #1 Tue Aug 25 17:23:54 UTC 2020 x86_64 x86_64 x86_64 SMP GNU/Linux
All instances are running the following in a VM environment:
CentOS 7 Linux 3.10.0-1127.19.1.el7.x86_64 #1 Tue Aug 25 17:23:54 UTC 2020 x86_64 x86_64 x86_64 SMP GNU/Linux
Back-end database instance: Mariadb 10.4.11 with 32 GB RAM 6 x 1 core Intel(R) Xeon(R) CPU E7-4850 v3 @ 2.20GHz Spider node instance: Mariadb 10.4.11 with 300 GB RAM 24 x 1 core Intel(R) Xeon(R) CPU E7-4850 v3 @ 2.20GHz Linux 3.10.0-1127.19.1.el7.x86_64 #1 Tue Aug 25 17:23:54 UTC 2020 x86_64 x86_64 x86_64 SMP GNU/Linux All instances are running the following in a VM environment: CentOS 7 Linux 3.10.0-1127.19.1.el7.x86_64 #1 Tue Aug 25 17:23:54 UTC 2020 x86_64 x86_64 x86_64 SMP GNU/Linux
Description
The following is a simplified version of my original query where the bug was first discovered. When attempting to do the following join of two Spider tables, the following error message is returned for a valid query:
select nextID from tab1 LEFT join tab2 on id = currentID where id in ( 1 ); |
ERROR 1064 (42000): 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 'join `forensic`.`tab2` t0 on (t0.`currentID` = 1 where 1' at line 1
|
If I run the query on the back-end database instance, it works as expected with no error. If I run it on the Spider node instance, I get the 1064 error.
I have tested this with Mariadb 10.4.11 and 10.5.11 (both with Spider version 3.3.15). I have also tested this with both tables as Innodb tables. The same error results.
I have reduced the contents of my.cnf to the following for simplicity (but I've tested with and without these and they don't effect the result) and there are file path definitions that I define:
log_warnings=1
|
default_storage_engine=MyISAM
|
innodb=OFF
|
general_log=ON
|
expire_logs_days=2
|
create database mydb default character set latin1; |
use mydb |
|
create user spidy@`%`; |
set password for spidy@`%` = password('password123'); |
grant select on *.* to spidy@`%`; |
|
-- grant all on mydb.tab1 to spidy@`%`;
|
-- grant all on mydb.tab2 to spidy@`%`;
|
|
create table tab1 ( |
id int(4) not null auto_increment, |
status varchar(25) default null, |
primary key (id) |
) engine=MYISAM default charset=latin1; |
|
create table tab2 ( |
currentID int(4) not null default 0, |
nextID int(4) not null default 0, |
primary key (currentID, nextID) |
) engine=MYISAM default charset=latin1; |
|
insert into tab1 values |
(null, 'WORKING'), |
(null, 'READY FOR QC'), |
(null, 'READY TO CREATE'), |
(null, 'CREATED'), |
(null, 'APPROVAL ERROR'), |
(null, 'CREATION ERROR'), |
(null, 'AWAITING'), |
(null, 'NEEDS RTL'), |
(null, 'RTL SUBMITTED'), |
(null, 'ARCHIVED'); |
|
insert into tab2 values |
(1,2),
|
(1,7),
|
(1,8),
|
(1,10),
|
(2,6),
|
(2,10),
|
(3,4),
|
(3,6),
|
(3,10),
|
(5,1),
|
(5,10),
|
(6,1),
|
(6,10),
|
(7,2),
|
(7,10),
|
(8,5),
|
(8,9),
|
(8,10),
|
(9,2),
|
(9,5),
|
(9,7),
|
(9,10),
|
(10,1),
|
(10,2),
|
(10,3),
|
(10,5),
|
(10,6),
|
(10,7),
|
(10,8),
|
(10,9);
|
|
create or replace server dataNode1 foreign data wrapper mysql options (PORT 3306, HOST 'hostname', USER 'spidy', PASSWORD 'password123'); |
select * from mysql.servers; |
select db_name, table_name, server from mysql.spider_tables; |
|
create table mydb.tab1 engine=SPIDER comment='wrapper "mysql", srv "dataNode1", table "tab1"'; |
create table mydb.tab2 engine=SPIDER comment='wrapper "mysql", srv "dataNode1", table "tab2"'; |
|
-- Test cases:
|
|
-- produces ERROR
|
select nextID |
from tab1 |
LEFT join tab2 on id = currentID |
where id in ( 1 ); |
|
-- produces ERROR
|
select nextID |
from tab2 |
RIGHT join tab1 on currentID = id |
where id in ( 1 ); |
|
-- produces ERROR
|
select nextID |
from tab2 |
RIGHT join tab1 on currentID = id |
where id in ( 1,2 ); |
|
ERROR 1064 (42000): 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 'join `forensic`.`tab2` t0 on (t0.`currentID` = 1 where 1' at line 1 |
|
-- OK - no error
|
select nextID |
from tab1 |
LEFT join tab2 on id = currentID |
where id in ( 1,2 ); |
|
-- OK - no error
|
select nextID |
from tab2 |
LEFT join tab1 on id = currentID |
where id in ( 1 ); |
|
-- OK - no error
|
select nextID |
from tab1 |
RIGHT join tab2 on currentID = id |
where id in ( 1 ); |
Attachments
Issue Links
- blocks
-
MDEV-29163 Server crash with SIGSEGV or dynamic-stack-buffer-overflow in spider_db_mbase_util::append_table
-
- Closed
-
-
MDEV-30392 Syntax error upon query with subquery from Spider table
-
- Closed
-
-
MDEV-31645 Spider doesn't recognize semi JOIN
-
- Closed
-
- includes
-
MDEV-29163 Server crash with SIGSEGV or dynamic-stack-buffer-overflow in spider_db_mbase_util::append_table
-
- Closed
-
-
MDEV-30392 Syntax error upon query with subquery from Spider table
-
- Closed
-
-
MDEV-31645 Spider doesn't recognize semi JOIN
-
- Closed
-
- relates to
-
MDEV-32238 Add a switch to disable spider group by handler
-
- Closed
-
-
MDEV-32273 Broken queries are passed to the group by handler for execution
-
- Open
-
-
MDEV-33679 spider returns parsing failure on valid left join select by translating the on expression to ()
-
- Closed
-
-
MDEV-28691 Spider: fall back to usual handler if GROUP BY handler fails to generate query
-
- Open
-
Hi holyfoot, ptal thanks:
1. 88765e7d583 upstream/bb-11.0-mdev-26247 MDEV-26247 Re-implement spider gbh query rewrite of tables
2. fe3d8dcf492 MDEV-26247 clean up spider_group_by_handler::init_scan()
3. 5b486c4893b MDEV-26247 Clean up spider_fields
4. dd9eea879e3 MDEV-26247 Remove some unused spider methods
---- no review needed for the following two commits ----
5. dfb5616184e MDEV-29502 Fix some issues with spider direct aggregate
6. a88b8bdd705 MDEV-31673 MDEV-29502 Remove spider_db_handler::need_lock_before_set_sql_for_exec
Of these commits, 1 is the actual bugfix, and it also fixes tickets included in this ticket. 2, 3, and 4 are cleanups. 5 and 6 are from
MDEV-29502which is fixed but have not propagated to 11.0 yet, and they do not require review. They also help makeMDEV-26345fail in the correct way (wrong results), rather than crash.Tested on ES-23.08 and 10.4 too:
ES-23.08 (a slightly earlier version)
827da11c103 upstream/23.08-enterprise-mdev-26247 MDEV-26247 Re-implement spider gbh query rewrite of tables
4b5fb4bb7ef MDEV-26247 clean up spider_group_by_handler::init_scan()
5638e375b8b MDEV-26247 Clean up spider_fields
8da5898302e MDEV-26247 Remove some unused spider methods
53294d52f8e MDEV-28998 remove a known reference to a SPIDER_CONN when it is freed
10.4
1ea1d1f6bf1 upstream/bb-10.4-ycp MDEV-26247 Re-implement spider gbh query rewrite of tables
a0266d3f5b1 MDEV-26247 clean up spider_group_by_handler::init_scan()
b1ecfa0ce02 MDEV-26247 Clean up spider_fields
788354732db MDEV-26247 Remove some unused spider methods