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

Wrong result on 2nd execution of PS for query with derived table

    XMLWordPrintable

Details

    Description

      Testcase:

      --source have_federatedx.inc
      --source include/federated.inc
       
      connection default;
      set global federated_pushdown=1;
       
      connection slave;
      DROP TABLE IF EXISTS federated.t1;
      CREATE TABLE federated.t1 (
        id int(20) NOT NULL,
        name varchar(16) NOT NULL default ''
      )
      DEFAULT CHARSET=latin1;
      INSERT INTO federated.t1 VALUES
        (3,'xxx'), (7,'yyy'), (4,'xxx'), (1,'zzz'), (5,'yyy');
       
      connection master;
      DROP TABLE IF EXISTS federated.t1;
      --replace_result $SLAVE_MYPORT SLAVE_PORT
      eval
      CREATE TABLE federated.t1 (
        id int(20) NOT NULL,
        name varchar(16) NOT NULL default ''
      )
      ENGINE="FEDERATED" DEFAULT CHARSET=latin1
      CONNECTION='mysql://root@127.0.0.1:$SLAVE_MYPORT/federated/t1';
       
      CREATE TABLE federated.t3 (
        name varchar(16) NOT NULL default ''
      )
      DEFAULT CHARSET=latin1;
      INSERT INTO federated.t3 VALUES
        ('yyy'), ('www'), ('yyy'), ('xxx'), ('www'), ('yyy'), ('www');
       
      SELECT *
      FROM federated.t3, (SELECT * FROM federated.t1 WHERE id > 3) t
      WHERE federated.t3.name=t.name;
       
      prepare stmt from 'SELECT *
      FROM federated.t3, (SELECT * FROM federated.t1 WHERE id > 3) t
      WHERE federated.t3.name=t.name';
      execute stmt;
      execute stmt;
      deallocate prepare stmt;
       
      set global federated_pushdown=0;
      source include/federated_cleanup.inc;
      

      Actual result:

      execute stmt;
      name	id	name
      yyy	5	yyy
      yyy	7	yyy
      yyy	5	yyy
      yyy	7	yyy
      xxx	4	xxx
      yyy	5	yyy
      yyy	7	yyy
      execute stmt;
      name	id	name
      yyy	0	
      yyy	0	
      yyy	0	
      yyy	0	
      xxx	0	
      yyy	0	
      yyy	0	
      

      Expected result:

      execute stmt;
      name	id	name
      yyy	5	yyy
      yyy	7	yyy
      yyy	5	yyy
      yyy	7	yyy
      xxx	4	xxx
      yyy	5	yyy
      yyy	7	yyy
      execute stmt;
      name	id	name
      yyy	5	yyy
      yyy	7	yyy
      yyy	5	yyy
      yyy	7	yyy
      xxx	4	xxx
      yyy	5	yyy
      yyy	7	yyy
      

      Attachments

        Issue Links

          Activity

            People

              igor Igor Babaev
              lstartseva Lena Startseva
              Votes:
              0 Vote for this issue
              Watchers:
              7 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.