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

Wrong result with outer join, merged derived table and view

Details

    Description

      Table Elimination works wrong for the view. Ex:
      (EDIT : Table Elimination doesn't have anything to do with this bug)

      Test:

      CREATE TABLE t1 (
      		 
        PostID int(10) unsigned NOT NULL
      		 
      ) DEFAULT CHARSET=utf8;
      		 
       
      		 
      INSERT INTO t1 (PostID) VALUES (1), (2);
      		 
       
      		 
      CREATE TABLE t2 (
      		 
        VoteID int(10) unsigned NOT NULL PRIMARY KEY AUTO_INCREMENT,
      		 
        EntityID int(10) unsigned NOT NULL,
      		 
        UserID int(10) unsigned NOT NULL,
      		 
        UNIQUE KEY EntityID (EntityID,UserID)
      		 
      ) DEFAULT CHARSET=utf8;
      		 
        
      INSERT INTO t2 (EntityID, UserID) VALUES (1,  30), (2, 30);
      		 
       
      		 
      CREATE VIEW v1 as SELECT t1.*, T.Voted as Voted
      		 
      FROM 
      t1 LEFT JOIN (
      		 
        SELECT 1 AS Voted, EntityID 
        FROM t2 
        WHERE t2.UserID = '20' ) AS T 
        ON T.EntityID = t1.PostID
      		 
      WHERE t1.PostID='1'
      		 
      LIMIT 1;
      		 
      SELECT * FROM  v1;
      		 
       
      		 
      DROP VIEW v1;
      		 
      DROP TABLE t1,t2;

      Actual result:

      PostID	Voted
      		 
      1	1

      Expected result:

      PostID	Voted
      		 
      1	NULL

      Attachments

        Issue Links

          causes

          Bug - A problem which impairs or prevents the functions of the product. MDEV-31277 Wrong result on 2-nd execution of PS to select from view using derived

          • Critical - Crashes, loss of data, severe memory leak.
          • Closed
          is part of

          Technical task - A technical task. MDEV-27691 make working view-protocol

          • Major - Major loss of function.
          • Closed

          Activity

            Ascending order - Click to sort in descending order
            lstartseva Lena Startseva created issue -
            lstartseva Lena Startseva made changes -
            Field Original Value New Value
            lstartseva Lena Startseva made changes -
            Labels view-protocol
            lstartseva Lena Startseva made changes -
            Assignee Lena Startseva [ JIRAUSER50478 ] Oleksandr Byelkin [ sanja ]
            sanja Oleksandr Byelkin made changes -
            Priority Major [ 3 ] Critical [ 2 ]
            sanja Oleksandr Byelkin made changes -
            Assignee Oleksandr Byelkin [ sanja ] Sergei Petrunia [ psergey ]
            psergei Sergei Petrunia made changes -
            Assignee Sergei Petrunia [ psergey ] Rex Johnston [ JIRAUSER52533 ]
            Johnston Rex Johnston made changes -
            Fix Version/s 10.11 [ 27614 ]
            Fix Version/s 10.10 [ 27530 ]
            Johnston Rex Johnston made changes -
            Status Open [ 1 ] In Progress [ 3 ]
            psergei Sergei Petrunia made changes -
            Summary Table Elimination works wrong for the view Wrong result with outer join, merged derived table and view
            psergei Sergei Petrunia made changes -
            Description Table Elimination works wrong for the view. Ex:

            *Test:*
            {code:sql}
            CREATE TABLE t1 (
              PostID int(10) unsigned NOT NULL
            ) DEFAULT CHARSET=utf8;

            INSERT INTO t1 (PostID) VALUES (1), (2);

            CREATE TABLE t2 (
              VoteID int(10) unsigned NOT NULL PRIMARY KEY AUTO_INCREMENT,
              EntityID int(10) unsigned NOT NULL,
              UserID int(10) unsigned NOT NULL,
              UNIQUE KEY EntityID (EntityID,UserID)
            ) DEFAULT CHARSET=utf8;
              
            INSERT INTO t2 (EntityID, UserID) VALUES (1, 30), (2, 30);

            CREATE VIEW v1 as SELECT t1.*, T.Voted as Voted
            FROM
            t1 LEFT JOIN (
              SELECT 1 AS Voted, EntityID
              FROM t2
              WHERE t2.UserID = '20' ) AS T
              ON T.EntityID = t1.PostID
            WHERE t1.PostID='1'
            LIMIT 1;
            SELECT * FROM v1;

            DROP VIEW v1;
            DROP TABLE t1,t2;
            {code}

            *Actual result:*
            {code:sql}
            PostID Voted
            1 1
            {code}

            *Expected result:*
            {code:sql}
            PostID Voted
            1 NULL
            {code}
            		Table Elimination works wrong for the view. Ex:
            (*EDIT* : Table Elimination doesn't have anything to do with this bug)

            *Test:*
            {code:sql}
            CREATE TABLE t1 (
              PostID int(10) unsigned NOT NULL
            ) DEFAULT CHARSET=utf8;

            INSERT INTO t1 (PostID) VALUES (1), (2);

            CREATE TABLE t2 (
              VoteID int(10) unsigned NOT NULL PRIMARY KEY AUTO_INCREMENT,
              EntityID int(10) unsigned NOT NULL,
              UserID int(10) unsigned NOT NULL,
              UNIQUE KEY EntityID (EntityID,UserID)
            ) DEFAULT CHARSET=utf8;
              
            INSERT INTO t2 (EntityID, UserID) VALUES (1, 30), (2, 30);

            CREATE VIEW v1 as SELECT t1.*, T.Voted as Voted
            FROM
            t1 LEFT JOIN (
              SELECT 1 AS Voted, EntityID
              FROM t2
              WHERE t2.UserID = '20' ) AS T
              ON T.EntityID = t1.PostID
            WHERE t1.PostID='1'
            LIMIT 1;
            SELECT * FROM v1;

            DROP VIEW v1;
            DROP TABLE t1,t2;
            {code}

            *Actual result:*
            {code:sql}
            PostID Voted
            1 1
            {code}

            *Expected result:*
            {code:sql}
            PostID Voted
            1 NULL
            {code}
            psergei Sergei Petrunia made changes -
            Assignee Rex Johnston [ JIRAUSER52533 ] Sergei Petrunia [ psergey ]
            psergei Sergei Petrunia made changes -
            Status In Progress [ 3 ] In Review [ 10002 ]
            psergei Sergei Petrunia made changes -
            Status In Review [ 10002 ] Stalled [ 10000 ]
            psergei Sergei Petrunia made changes -
            Assignee Sergei Petrunia [ psergey ] Oleksandr Byelkin [ sanja ]
            Status Stalled [ 10000 ] In Review [ 10002 ]
            sanja Oleksandr Byelkin made changes -
            Assignee Oleksandr Byelkin [ sanja ] Igor Babaev [ igor ]
            Status In Review [ 10002 ] Stalled [ 10000 ]
            igor Igor Babaev (Inactive) made changes -
            Assignee Igor Babaev [ igor ] Sergei Petrunia [ psergey ]
            psergei Sergei Petrunia made changes -
            Component/s Optimizer [ 10200 ]
            Fix Version/s 10.3.38 [ 28507 ]
            Fix Version/s 10.4.28 [ 28509 ]
            Fix Version/s 10.5.19 [ 28511 ]
            Fix Version/s 10.6.12 [ 28513 ]
            Fix Version/s 10.7.8 [ 28515 ]
            Fix Version/s 10.8.7 [ 28517 ]
            Fix Version/s 10.9.5 [ 28519 ]
            Fix Version/s 10.10.3 [ 28521 ]
            Fix Version/s 10.11.2 [ 28523 ]
            Fix Version/s 10.11 [ 27614 ]
            Resolution Fixed [ 1 ]
            Status Stalled [ 10000 ] Closed [ 6 ]
            lstartseva Lena Startseva made changes -
            lstartseva Lena Startseva made changes -
            lstartseva Lena Startseva made changes -

            People

              psergei Sergei Petrunia
              lstartseva Lena Startseva
              Votes:
              0 Vote for this issue
              Watchers:
              6 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.