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

Wrong result (extra rows) on 2nd execution of PS with exists_to_in, MERGE view

Details

    • Bug
    • Status: Stalled (View Workflow)
    • Critical
    • Resolution: Unresolved
    • 10.0.13, 10.1(EOL), 10.2(EOL), 10.3(EOL), 10.4(EOL), 10.5, 10.6, 10.7(EOL), 10.8(EOL), 10.9(EOL), 10.10(EOL)
    • 10.5, 10.6
    • Optimizer, Prepared Statements
    • None
    • 10.0.20, 10.0.24

    Description

      CREATE TABLE t1 (a VARCHAR(3)) ENGINE=MyISAM;
      		 
      INSERT INTO t1 VALUES ('foo'),('bar');
      		 
       
      		 
      CREATE TABLE t2 (i INT, b VARCHAR(3)) ENGINE=MyISAM;
      		 
      CREATE ALGORITHM=MERGE VIEW v2 AS SELECT * FROM t2;
      		 
      INSERT INTO t2 VALUES (4,'foo'),(5,'baz');
      		 
       
      		 
      PREPARE stmt FROM "SELECT * FROM t1 WHERE NOT EXISTS ( SELECT i FROM v2 WHERE b = a )";
      		 
      EXECUTE stmt;
      		 
      EXECUTE stmt;

      Actual result:

      EXECUTE stmt;
      		 
      a
      		 
      bar
      		 
      EXECUTE stmt;
      		 
      a
      		 
      foo
      		 
      bar

      Expected result:

      EXECUTE stmt;
      		 
      a
      		 
      bar
      		 
      EXECUTE stmt;
      		 
      a
      		 
      bar
      		 

      revision-id: knielsen@knielsen-hq.org-20140902120701-txesdmkczel84qgf
      		 
      revno: 4369
      		 
      branch-nick: 10.0

      Attachments

        Issue Links

          is blocked by

          Bug - A problem which impairs or prevents the functions of the product. MDEV-30073 Wrong result on 2nd execution of PS for query with NOT EXISTS

          • Critical - Crashes, loss of data, severe memory leak.
          • In Progress

          Activity

            Ascending order - Click to sort in descending order
            View 8 older comments
            sanja Oleksandr Byelkin added a comment - - edited

            Second time used other engine, so probably passing the same JOIN_TAB address to it is just a coincidence or (using wrong JOIN (small probability, because it should be freed)).

            sanja Oleksandr Byelkin added a comment - - edited Second time used other engine, so probably passing the same JOIN_TAB address to it is just a coincidence or (using wrong JOIN (small probability, because it should be freed)).
            sanja Oleksandr Byelkin added a comment - - edited

            The problem is that Item_in_subselect::setup_mat_engine() uses select_engine->join->fields_list which on the second execution chaged due to transformation rollback!!!

            sanja Oleksandr Byelkin added a comment - - edited The problem is that Item_in_subselect::setup_mat_engine() uses select_engine->join->fields_list which on the second execution chaged due to transformation rollback!!!
            sanja Oleksandr Byelkin added a comment -

            revision-id: 016f44b15455d2ca0018902cdc78d1cb29a14423 (mariadb-10.0.23-30-g016f44b)
            parent(s): 089b65fd5e8ecd1971cf4c6c372e20575ff4ca9e
            committer: Oleksandr Byelkin
            timestamp: 2016-02-14 16:52:56 +0100
            message:

            MDEV-6704: Wrong result (extra rows) on 2nd execution of PS with exists_to_in, MERGE view

            The problem was that after first execution permanent exists-to-in transformation became broken back by rolling back temporary changes of view resolving.

            Added method to rewrite temporary changes.

            —

            sanja Oleksandr Byelkin added a comment - revision-id: 016f44b15455d2ca0018902cdc78d1cb29a14423 (mariadb-10.0.23-30-g016f44b) parent(s): 089b65fd5e8ecd1971cf4c6c372e20575ff4ca9e committer: Oleksandr Byelkin timestamp: 2016-02-14 16:52:56 +0100 message: MDEV-6704 : Wrong result (extra rows) on 2nd execution of PS with exists_to_in, MERGE view The problem was that after first execution permanent exists-to-in transformation became broken back by rolling back temporary changes of view resolving. Added method to rewrite temporary changes. —
            psergei Sergei Petrunia added a comment -

            Attempted to do a review pass, discussed with sanja. Current approach works but in my opinion change_item_tree() is evil and should not be extended.
            I'd like to come up with some other solution. I need to think about this.

            psergei Sergei Petrunia added a comment - Attempted to do a review pass, discussed with sanja . Current approach works but in my opinion change_item_tree() is evil and should not be extended. I'd like to come up with some other solution. I need to think about this.
            igor Igor Babaev (Inactive) added a comment - - edited

            Just reassigned the bug to myself.

            igor Igor Babaev (Inactive) added a comment - - edited Just reassigned the bug to myself.

            People

              Johnston Rex Johnston
              elenst Elena Stepanova
              Votes:
              0 Vote for this issue
              Watchers:
              7 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.