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

Wrong result with semijoin and Federated as outer table

Details

    Description

      INSTALL SONAME 'ha_federatedx'; 
       
      eval create server s foreign data wrapper mysql options (host "127.0.0.1", database "test", user "root", port $MASTER_MYPORT);
      set optimizer_switch="materialization=off";
       
      CREATE TABLE t1 (a INT);
      INSERT INTO t1 VALUES (3),(2),(3);
       
      CREATE TABLE t2 (pk INT PRIMARY KEY);
      INSERT INTO t2 VALUES (1),(2),(3),(4);
       
      CREATE TABLE t2_fed ENGINE=FEDERATED CONNECTION='s/t2';
       
      SELECT * FROM t2_fed WHERE pk IN ( SELECT a FROM t1 );
      SET optimizer_switch='semijoin=off';
      SELECT * FROM t2_fed WHERE pk IN ( SELECT a FROM t1 );
       
      # Cleanup
       
      DROP TABLE t2_fed, t1, t2;
      DROP SERVER s;
       
      UNINSTALL SONAME 'ha_federatedx';
      

      bb-11.0 78c07ed17

      SELECT * FROM t2_fed WHERE pk IN ( SELECT a FROM t1 );
      pk
      3
      2
      3
      SET optimizer_switch='semijoin=off';
      SELECT * FROM t2_fed WHERE pk IN ( SELECT a FROM t1 );
      pk
      2
      3
      

      2 rows which are returned without semijoin is the expected result.

      Plans with semijoin enabled:

      bb-11.0

      EXPLAIN EXTENDED SELECT * FROM t2_fed WHERE pk IN ( SELECT a FROM t1 );
      id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
      1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	3	100.00	Using where; Start temporary
      1	PRIMARY	t2_fed	eq_ref	PRIMARY	PRIMARY	4	test.t1.a	1	33.33	End temporary
      Warnings:
      Note	1003	select `test`.`t2_fed`.`pk` AS `pk` from `test`.`t2_fed` semi join (`test`.`t1`) where `test`.`t2_fed`.`pk` = `test`.`t1`.`a`
      

      baseline

      EXPLAIN EXTENDED SELECT * FROM t2_fed WHERE pk IN ( SELECT a FROM t1 );
      id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
      1	PRIMARY	<subquery2>	ALL	distinct_key	NULL	NULL	NULL	3	100.00	
      1	PRIMARY	t2_fed	eq_ref	PRIMARY	PRIMARY	4	test.t1.a	1	100.00	
      2	MATERIALIZED	t1	ALL	NULL	NULL	NULL	NULL	3	100.00	Using where
      Warnings:
      Note	1003	select `test`.`t2_fed`.`pk` AS `pk` from `test`.`t2_fed` semi join (`test`.`t1`) where `test`.`t2_fed`.`pk` = `test`.`t1`.`a`
      

      Attachments

        Issue Links

          Activity

            I have verified that this an old bug.

            Adding
            "set optimizer_switch="materialization=off" to the test cases
            causes 3 rows to be returned for the first query also in 10.5 and 10.11

            monty Michael Widenius added a comment - I have verified that this an old bug. Adding "set optimizer_switch="materialization=off" to the test cases causes 3 rows to be returned for the first query also in 10.5 and 10.11

            Updated versions etc. based on the above.

            elenst Elena Stepanova added a comment - Updated versions etc. based on the above.

            The problem was that federated engine does not support comparable rowids which was not taken into account by semijoin code.

            Fixed by checking that we don't use semijoin with tables that does not support comparable rowids.

            monty Michael Widenius added a comment - The problem was that federated engine does not support comparable rowids which was not taken into account by semijoin code. Fixed by checking that we don't use semijoin with tables that does not support comparable rowids.

            Finding the cause and finding the best way to fix this.
            Also a lot of discussions with Petrunia about the right way to fix this.

            monty Michael Widenius added a comment - Finding the cause and finding the best way to fix this. Also a lot of discussions with Petrunia about the right way to fix this.

            People

              monty Michael Widenius
              elenst Elena Stepanova
              Votes:
              0 Vote for this issue
              Watchers:
              3 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.