Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-26247

Spider: Valid LEFT JOIN results in ERROR 1064

    XMLWordPrintable

    Details

    • Type: Bug
    • Status: In Progress (View Workflow)
    • Priority: Major
    • Resolution: Unresolved
    • Affects Version/s: 10.3, 10.4.11, 10.5.11
    • Fix Version/s: 10.3, 10.4, 10.5, 10.6
    • Labels:
    • Environment:

      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

          Activity

            People

            Assignee:
            nayuta-yanagisawa Nayuta Yanagisawa
            Reporter:
            bgladhill Bob Gladhill
            Votes:
            1 Vote for this issue
            Watchers:
            5 Start watching this issue

              Dates

              Created:
              Updated:

                Git Integration