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

Wrong result with JOINs involving Spider table

Details

    Description

      # This may not work, e.g. for in-source builds, fix the path
      		 
      --source plugin/spider/spider/include/init_spider.inc
      		 
      #--source ../storage/spider/mysql-test/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);
      		 
      CREATE TABLE t_spider (a INT, KEY(a)) ENGINE=SPIDER COMMENT = "wrapper 'mysql', srv 's', table 't'";
      		 
      INSERT INTO t VALUES (1),(2);
      		 
       
      		 
      SELECT t1.* FROM t_spider AS t1 LEFT JOIN t_spider AS t2 RIGHT JOIN t_spider AS t3 ON 1 ON t1.a = 1;
      		 
       
      		 
      # 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
      		 
      #--source ../storage/spider/mysql-test/spider/include/deinit_spider.inc

      10.4 f3f09def

      		 
      SELECT t1.* FROM t_spider AS t1 LEFT JOIN t_spider AS t2 RIGHT JOIN t_spider AS t3 ON 1 ON t1.a = 1;
      		 
      a
      		 
      1
      		 
      1
      		 
      2
      		 
      1
      		 
      1
      		 
      2

      One of 2 rows shouldn't be there, the expected result is 

      SELECT t1.* FROM t AS t1 LEFT JOIN t AS t2 RIGHT JOIN t AS t3 ON 1 ON t1.a = 1;
      		 
      a
      		 
      1
      		 
      1
      		 
      1
      		 
      1
      		 
      2

      Attachments

        Activity

          Ascending order - Click to sort in descending order
          elenst Elena Stepanova created issue -
          julien.fritsch Julien Fritsch made changes -
          Field Original Value New Value
          Fix Version/s 10.7 [ 24805 ]
          julien.fritsch Julien Fritsch made changes -
          Fix Version/s 10.8 [ 26121 ]
          julien.fritsch Julien Fritsch made changes -
          Fix Version/s 10.9 [ 26905 ]
          julien.fritsch Julien Fritsch made changes -
          Fix Version/s 10.10 [ 27530 ]
          ycp Yuchen Pei added a comment -

          This issue seems no longer reproducible. The following test (placed under spider/bugfix suite) passes in 10.5 ea9869504d4bd47e111fe2aab99f85b60946f648 and 10.11 579450c2c10b867347612efaf67bdce3c88504cd

          --disable_query_log
          		 
          --disable_result_log
          		 
          --source ../../t/test_init.inc
          		 
          --enable_result_log
          		 
          --enable_query_log
          		 
          set spider_same_server_link= 1;
          		 
          evalp CREATE SERVER srv FOREIGN DATA WRAPPER mysql
          		 
          OPTIONS (SOCKET "$MASTER_1_MYSOCK", DATABASE 'test',user 'root');
          		 
          create table t (a int);
          		 
          create table t_spider (a int, key(a)) ENGINE=Spider
          		 
          COMMENT='WRAPPER "mysql", srv "srv",TABLE "t"';
          		 
          insert into t values (1),(2);
          		 
          SELECT t1.* FROM t_spider AS t1 LEFT JOIN t_spider AS t2 RIGHT JOIN t_spider AS t3 ON 1 ON t1.a = 1;
          		 
          drop table t_spider, t;
          		 
          drop server srv;
          		 
          --disable_query_log
          		 
          --disable_result_log
          		 
          --source ../../t/test_deinit.inc
          		 
          --enable_result_log
          		 
          --enable_query_log

          ycp Yuchen Pei added a comment - This issue seems no longer reproducible. The following test (placed under spider/bugfix suite) passes in 10.5 ea9869504d4bd47e111fe2aab99f85b60946f648 and 10.11 579450c2c10b867347612efaf67bdce3c88504cd --disable_query_log --disable_result_log --source ../../t/test_init.inc --enable_result_log --enable_query_log set spider_same_server_link= 1; evalp CREATE SERVER srv FOREIGN DATA WRAPPER mysql OPTIONS (SOCKET "$MASTER_1_MYSOCK" , DATABASE 'test' , user 'root' ); create table t (a int ); create table t_spider (a int , key (a)) ENGINE=Spider COMMENT= 'WRAPPER "mysql", srv "srv",TABLE "t"' ; insert into t values (1),(2); SELECT t1.* FROM t_spider AS t1 LEFT JOIN t_spider AS t2 RIGHT JOIN t_spider AS t3 ON 1 ON t1.a = 1; drop table t_spider, t; drop server srv; --disable_query_log --disable_result_log --source ../../t/test_deinit.inc --enable_result_log --enable_query_log
          ycp Yuchen Pei made changes -
          Fix Version/s N/A [ 14700 ]
          Fix Version/s 10.4 [ 22408 ]
          Fix Version/s 10.5 [ 23123 ]
          Fix Version/s 10.6 [ 24028 ]
          Fix Version/s 10.11 [ 27614 ]
          Resolution Cannot Reproduce [ 5 ]
          Status Open [ 1 ] Closed [ 6 ]

          People

            ycp Yuchen Pei
            elenst Elena Stepanova
            Votes:
            0 Vote for this issue
            Watchers:
            2 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.