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

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

        Details

        • Type: Bug
        • Status: Open
        • Priority: Minor
        • Resolution: Unresolved
        • Affects Version/s: 5.5.28a, 5.3.9, 5.2.13, 5.1.66
        • Fix Version/s: 5.5
        • Component/s: None
        • Labels:

          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

            Gliffy Diagrams

              Attachments

                Activity

                Ascending order - Click to sort in descending order
                Hide
                Permalink
                philipstoev Philip Stoev added a comment -

                Re: Different number of rows updated with and without table_elimination
                Both mysql 5.1 and mysql 5.5 do not update the row in supplier. The UPDATE query reports "Rows matched: 5 Changed: 5"

                Show
                philipstoev Philip Stoev added a comment - Re: Different number of rows updated with and without table_elimination Both mysql 5.1 and mysql 5.5 do not update the row in supplier. The UPDATE query reports "Rows matched: 5 Changed: 5"
                Hide
                Permalink
                ratzpo Rasmus Johansson added a comment -

                Launchpad bug id: 705369

                Show
                ratzpo Rasmus Johansson added a comment - Launchpad bug id: 705369

                  People

                  • Assignee:
                    psergey Sergei Petrunia
                    Reporter:
                    philipstoev Philip Stoev
                  • Votes:
                    0 Vote for this issue
                    Watchers:
                    2 Start watching this issue

                    Dates

                    • Created:
                      Updated: