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

LP:705369 - Different number of rows updated with and without table_elimination

    XMLWordPrintable

Details

    • Bug
    • Status: Open (View Workflow)
    • Minor
    • Resolution: Unresolved
    • 5.5.28a, 5.3.9, 5.2.13, 5.1.66
    • 5.5(EOL)
    • None

    Description

      The following query:

      UPDATE  part RIGHT JOIN partsupp ON ( p_partkey = ps_partkey ) RIGHT JOIN supplier
      ON ( ps_suppkey = s_suppkey ) RIGHT JOIN nation ON ( s_nationkey = n_nationkey ) JOIN
      region ON ( n_regionkey = r_regionkey ) SET s_name = DEFAULT , r_comment = DEFAULT
      WHERE r_comment IS NOT NULL OR ps_suppkey BETWEEN 10 AND 14;

      updates one row in supplier with table_elimination=on and no rows with table_elimination=off .

      Test case:

      PREPARE p1 FROM "
      UPDATE  part RIGHT JOIN partsupp ON ( p_partkey = ps_partkey ) RIGHT JOIN supplier
      ON ( ps_suppkey = s_suppkey ) RIGHT JOIN nation ON ( s_nationkey = n_nationkey ) JOIN
      region ON ( n_regionkey = r_regionkey ) SET s_name = DEFAULT , r_comment = DEFAULT
      WHERE r_comment IS NOT NULL OR ps_suppkey BETWEEN 10 AND 14;";
      SET AUTOCOMMIT=OFF;
      SET SESSION optimizer_switch = 'table_elimination=on';
      START TRANSACTION;
      EXECUTE p1;
      SELECT s_name FROM supplier;
      ROLLBACK;
      SET SESSION optimizer_switch = 'table_elimination=off';
      START TRANSACTION;
      EXECUTE p1;
      SELECT s_name FROM supplier;
      ROLLBACK;

      dataset (load with storage-engine=InnoDB):

      http://bazaar.launchpad.net/~randgen/randgen/rqg2/download/philips%40eve-20101203092302-30zu7xpf7uw0ub3a/dbt3s0.001.dump-20101203092242-l5ozx659nxhsvi0e-3/dbt3-s0.001.dump

      Attachments

        Activity

          People

            psergei Sergei Petrunia
            philipstoev Philip Stoev (Inactive)
            Votes:
            0 Vote for this issue
            Watchers:
            2 Start watching this issue

            Dates

              Created:
              Updated:

              Git Integration

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