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
            View 20 older comments
            Johnston Rex Johnston added a comment -

            I've tested the above query on 10.11 + SergeiP's patch & pg-11.7 and it produces the same result set.

            PG-11.7

             z | a | y | b | x | c 
            ---+---+---+---+---+---
            		 
             Z | 7 | Y | 7 |   |  
             Z | 1 | Y | 1 | X | 1
            		 
             Z | 1 | Y | 1 | X | 1
            		 
             Z | 2 |   |   |   |  
            (4 rows)

            vs

            10.11 + patch

            z	a	y	b	x	c
            		 
            Z	1	Y	1	X	1
            		 
            Z	1	Y	1	X	1
            		 
            Z	7	Y	7	NULL	NULL
            		 
            Z	2	NULL	NULL	NULL	NULL

            Johnston Rex Johnston added a comment - I've tested the above query on 10.11 + SergeiP's patch & pg-11.7 and it produces the same result set. PG-11.7 z | a | y | b | x | c ---+---+---+---+---+--- Z | 7 | Y | 7 | | Z | 1 | Y | 1 | X | 1 Z | 1 | Y | 1 | X | 1 Z | 2 | | | | (4 rows) vs 10.11 + patch z a y b x c Z 1 Y 1 X 1 Z 1 Y 1 X 1 Z 7 Y 7 NULL NULL Z 2 NULL NULL NULL NULL
            igor Igor Babaev (Inactive) added a comment -

            Another good query to test:

            select * from
            		 
            (
            		 
              select * from
            		 
                (select 'Z' as z, t1.a from t1) dt1
            		 
                left join 
                  (select 'Y' as y, t2.b from t2) dt2
            		 
                  left join
            		 
                  (select 'X' as x, t3.c from t3) dt3
            		 
                  on dt2.b=dt3.c
            		 
                on dt1.a=dt2.b and x IS NULL
            		 
              limit 9
            		 
            ) dt;

            igor Igor Babaev (Inactive) added a comment - Another good query to test: select * from ( select * from ( select 'Z' as z, t1.a from t1) dt1 left join ( select 'Y' as y, t2.b from t2) dt2 left join ( select 'X' as x, t3.c from t3) dt3 on dt2.b=dt3.c on dt1.a=dt2.b and x IS NULL limit 9 ) dt;
            igor Igor Babaev (Inactive) added a comment -

            Another observation: the setting null_value= 1; in the added method Item_dIrect_view_ref:: save_in_field is useless as Item_direct_view_ref::check_null_ref() already has this setting.

            igor Igor Babaev (Inactive) added a comment - Another observation: the setting null_value= 1; in the added method Item_dIrect_view_ref:: save_in_field is useless as Item_direct_view_ref::check_null_ref() already has this setting.
            psergei Sergei Petrunia added a comment -

            Thanks, all input from sanja and [igor is now taken into account in patch variant #3:
            https://github.com/MariaDB/server/commit/f3647b8b2e694423f3575b9bd227777476c2fb8d

            psergei Sergei Petrunia added a comment - Thanks, all input from sanja and [ igor is now taken into account in patch variant #3: https://github.com/MariaDB/server/commit/f3647b8b2e694423f3575b9bd227777476c2fb8d
            sanja Oleksandr Byelkin added a comment -

            OK to push

            sanja Oleksandr Byelkin added a comment - OK to push

            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.