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

Spider: Valid LEFT JOIN results in ERROR 1064

Details

    • Bug
    • Status: Closed (View Workflow)
    • Critical
    • Resolution: Fixed
    • 10.4.11, 10.5.11, 10.3(EOL)

    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

          Activity

            ycp Yuchen Pei added a comment - - edited

            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-29502 which is fixed but have not propagated to 11.0 yet, and they do not require review. They also help make MDEV-26345 fail 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

            ycp Yuchen Pei added a comment - - edited 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-29502 which is fixed but have not propagated to 11.0 yet, and they do not require review. They also help make MDEV-26345 fail 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

            Agree with the solution.
            Still there are two issues about the patch in the comment.

            holyfoot Alexey Botchkov added a comment - Agree with the solution. Still there are two issues about the patch in the comment .
            ycp Yuchen Pei added a comment -

            Hi holyfoot, thanks for the comment, which I have addressed at [1]. ptal the updated commits, thanks:

            11fde0f8bd7 upstream/bb-11.0-mdev-26247 MDEV-26247 Re-implement spider gbh query rewrite of tables
            fe3d8dcf492 MDEV-26247 clean up spider_group_by_handler::init_scan()
            5b486c4893b MDEV-26247 Clean up spider_fields
            dd9eea879e3 MDEV-26247 Remove some unused spider methods
            

            [1] https://github.com/MariaDB/server/commit/88765e7d583e3aeae59be0c5f1d3f2894fea72f9#commitcomment-130266611

            ycp Yuchen Pei added a comment - Hi holyfoot , thanks for the comment, which I have addressed at [1] . ptal the updated commits, thanks: 11fde0f8bd7 upstream/bb-11.0-mdev-26247 MDEV-26247 Re-implement spider gbh query rewrite of tables fe3d8dcf492 MDEV-26247 clean up spider_group_by_handler::init_scan() 5b486c4893b MDEV-26247 Clean up spider_fields dd9eea879e3 MDEV-26247 Remove some unused spider methods [1] https://github.com/MariaDB/server/commit/88765e7d583e3aeae59be0c5f1d3f2894fea72f9#commitcomment-130266611

            ok to push.

            holyfoot Alexey Botchkov added a comment - ok to push.
            ycp Yuchen Pei added a comment -

            pushing the following to 10.4

            178396573a1 upstream/bb-10.4-mdev-26247 MDEV-26247 Re-implement spider gbh query rewrite of tables
            0bacef76177 MDEV-26247 clean up spider_group_by_handler::init_scan()
            2d1e09a77f0 MDEV-26247 Clean up spider_fields
            8c1dcb2579e MDEV-26247 Remove some unused spider methods

            conflict solutions:

            • 10.4->10.5: 1328e6298651fa92af92605153f5119b9c33cb97
            • 10.5->10.6: 90d6006cbfffdb9226a822b8842f474f8cfe5168
            • 10.6->10.11: 21671e090b9e1596f8e355541788b7b0f2dcfb54
            • 10.11->11.0: 9c7bf73f66e991c9697c1c278581f2dd8b46b267
            ycp Yuchen Pei added a comment - pushing the following to 10.4 178396573a1 upstream/bb-10.4-mdev-26247 MDEV-26247 Re-implement spider gbh query rewrite of tables 0bacef76177 MDEV-26247 clean up spider_group_by_handler::init_scan() 2d1e09a77f0 MDEV-26247 Clean up spider_fields 8c1dcb2579e MDEV-26247 Remove some unused spider methods conflict solutions: 10.4->10.5: 1328e6298651fa92af92605153f5119b9c33cb97 10.5->10.6: 90d6006cbfffdb9226a822b8842f474f8cfe5168 10.6->10.11: 21671e090b9e1596f8e355541788b7b0f2dcfb54 10.11->11.0: 9c7bf73f66e991c9697c1c278581f2dd8b46b267

            People

              ycp Yuchen Pei
              bgladhill Bob Gladhill
              Votes:
              1 Vote for this issue
              Watchers:
              11 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved:

                Git Integration

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