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 ]
            Johnston Rex Johnston added a comment -

            Simplest egregious activation so far...

            CREATE TABLE t1 (
            		 
              Election int(10) unsigned NOT NULL
            		 
            );
            		 
             
            INSERT INTO t1 (Election) VALUES (1);
            		 
             
            CREATE TABLE t2 (
            		 
              VoteID int(10),
            		 
              ElectionID int(10),
            		 
              UserID int(10)
            		 
            );
            		 
             
            		 
            INSERT INTO t2 (ElectionID, UserID) VALUES (2,  30), (3, 30);
            		 
            -- INSERT INTO t2 (ElectionID, UserID) VALUES (1,  30);
            		 
             
            		 
            set @foo = "select * FROM t1
            		 
              LEFT JOIN ( SELECT 'Y' AS Voted, ElectionID FROM t2 ) AS T 
                ON T.ElectionID = t1.Election
            		 
            LIMIT 9";
            		 
             
            		 
            -- limit needed for bug
            		 
             
            		 
            prepare mq from @foo;
            		 
            execute mq;
            		 
             
            		 
            prepare mq from concat( "CREATE VIEW v1 as ", @foo );
            		 
            execute mq;
            		 
             
            		 
            SELECT * FROM  v1;
            		 
             
            DROP VIEW v1;
            		 
            DROP TABLE t1,t2;
            		 
             
            		 
             
            		 
            Election	Voted	ElectionID
            		 
            1	NULL	NULL
            		 
            Election	Voted	ElectionID
            		 
            1	Y	NULL

            Johnston Rex Johnston added a comment - Simplest egregious activation so far... CREATE TABLE t1 ( Election int(10) unsigned NOT NULL ); INSERT INTO t1 (Election) VALUES (1); CREATE TABLE t2 ( VoteID int(10), ElectionID int(10), UserID int(10) );   INSERT INTO t2 (ElectionID, UserID) VALUES (2, 30), (3, 30); -- INSERT INTO t2 (ElectionID, UserID) VALUES (1, 30);   set @foo = "select * FROM t1 LEFT JOIN ( SELECT 'Y' AS Voted, ElectionID FROM t2 ) AS T ON T.ElectionID = t1.Election LIMIT 9";   -- limit needed for bug   prepare mq from @foo; execute mq;   prepare mq from concat( "CREATE VIEW v1 as ", @foo ); execute mq;   SELECT * FROM v1; DROP VIEW v1; DROP TABLE t1,t2;     Election Voted ElectionID 1 NULL NULL Election Voted ElectionID 1 Y NULL
            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 added a comment -

            My testcase, based on the above:

            ## My testcase, based on yours:
            		 
             
            		 
            CREATE TABLE t1 (
            		 
              Election int(10) unsigned NOT NULL
            		 
            );
            		 
             
            INSERT INTO t1 (Election) VALUES (1);
            		 
             
            CREATE TABLE t2 (
            		 
              VoteID int(10),
            		 
              ElectionID int(10),
            		 
              UserID int(10)
            		 
            );
            		 
            INSERT INTO t2 (ElectionID, UserID) VALUES (2,  30), (3, 30);
            		 
             
            		 
            # Disable join buffering. It's easier to debug basic joins.
            		 
            set join_cache_level=0;
            		 
             
            		 
            select * FROM t1
            		 
              LEFT JOIN ( SELECT 'Y' AS Voted, ElectionID FROM t2 ) AS T 
                ON T.ElectionID = t1.Election
            		 
            LIMIT 9;
            		 
             
            		 
            create view v1 as
            		 
            select * FROM t1
            		 
              LEFT JOIN ( SELECT 'Y' AS Voted, ElectionID FROM t2 ) AS T 
                ON T.ElectionID = t1.Election
            		 
            LIMIT 9;
            		 
             
            		 
            select * from v1;

            This gives:

            +----------+-------+------------+
            		 
            | Election | Voted | ElectionID |
            		 
            +----------+-------+------------+
            		 
            |        1 | NULL  |       NULL |
            		 
            +----------+-------+------------+
            		 
             
            		 
            +----------+-------+------------+
            		 
            | Election | Voted | ElectionID |
            		 
            +----------+-------+------------+
            		 
            |        1 | Y     |       NULL |
            		 
            +----------+-------+------------+

            psergei Sergei Petrunia added a comment - My testcase, based on the above: ## My testcase, based on yours:   CREATE TABLE t1 ( Election int(10) unsigned NOT NULL ); INSERT INTO t1 (Election) VALUES (1); CREATE TABLE t2 ( VoteID int(10), ElectionID int(10), UserID int(10) ); INSERT INTO t2 (ElectionID, UserID) VALUES (2, 30), (3, 30);   # Disable join buffering. It's easier to debug basic joins. set join_cache_level=0;   select * FROM t1 LEFT JOIN ( SELECT 'Y' AS Voted, ElectionID FROM t2 ) AS T ON T.ElectionID = t1.Election LIMIT 9;   create view v1 as select * FROM t1 LEFT JOIN ( SELECT 'Y' AS Voted, ElectionID FROM t2 ) AS T ON T.ElectionID = t1.Election LIMIT 9;   select * from v1; This gives: +----------+-------+------------+ | Election | Voted | ElectionID | +----------+-------+------------+ | 1 | NULL | NULL | +----------+-------+------------+   +----------+-------+------------+ | Election | Voted | ElectionID | +----------+-------+------------+ | 1 | Y | NULL | +----------+-------+------------+
            psergei Sergei Petrunia added a comment -

            Note that ElectionID is fine. The issue is with Voted column which comes from:

            SELECT 'Y' AS Voted

            Also note that
            If one does 

            set optimizer_switch='table_elimination=off'

            the bug is still there (and the EXPLAIN has no change)

            If one does

            set optimizer_switch='derived_merge=off'

            then the bug goes away.

            psergei Sergei Petrunia added a comment - Note that ElectionID is fine. The issue is with Voted column which comes from: SELECT 'Y' AS Voted Also note that If one does set optimizer_switch='table_elimination=off' the bug is still there (and the EXPLAIN has no change) If one does set optimizer_switch='derived_merge=off' then the bug goes away.
            Johnston Rex Johnston added a comment - 

            with null compliment bug
            		 
            T@50   : | | | | | | | | | | | >JOIN_CACHE::join_null_complements
            		 
            T@50   : | | | | | | | | | | | | >JOIN_CACHE::generate_full_extensions
            		 
            T@50   : | | | | | | | | | | | | | >JOIN_CACHE:check_match
            		 
            T@50   : | | | | | | | | | | | | | <JOIN_CACHE:check_match
            		 
            T@50   : | | | | | | | | | | | | | >end_send
            		 
            T@50   : | | | | | | | | | | | | | | >select_send::send_data
            		 
            T@50   : | | | | | | | | | | | | | | | >Protocol::send_result_set_row
            		 
            T@50   : | | | | | | | | | | | | | | | <Protocol::send_result_set_row
            		 
            T@50   : | | | | | | | | | | | | | | | >Protocol::write
            		 
            T@50   : | | | | | | | | | | | | | | | | packet_header: Memory: 0x7f  Bytes: (4)
            		 
            04 00 00 06 
            T@50   : | | | | | | | | | | | | | | | <Protocol::write
            		 
            T@50   : | | | | | | | | | | | | | | <select_send::send_data
            		 
            T@50   : | | | | | | | | | | | | | <end_send
            		 
            T@50   : | | | | | | | | | | | | <JOIN_CACHE::generate_full_extensions
            		 
            T@50   : | | | | | | | | | | | <JOIN_CACHE::join_null_complements
            		 
             
            		 
            without null compliment bug
            		 
            T@50   : | | | | | | | | | | | | | | | | | | >JOIN_CACHE::join_null_complements
            		 
            T@50   : | | | | | | | | | | | | | | | | | | | >JOIN_CACHE::generate_full_extensions
            		 
            T@50   : | | | | | | | | | | | | | | | | | | | | >JOIN_CACHE:check_match
            		 
            T@50   : | | | | | | | | | | | | | | | | | | | | <JOIN_CACHE:check_match
            		 
            T@50   : | | | | | | | | | | | | | | | | | | | | >end_send
            		 
            T@50   : | | | | | | | | | | | | | | | | | | | | | >fill_record
            		 
            T@50   : | | | | | | | | | | | | | | | | | | | | | | >save_field_in_field
            		 
            T@50   : | | | | | | | | | | | | | | | | | | | | | | <save_field_in_field
            		 
            T@50   : | | | | | | | | | | | | | | | | | | | | | | >save_field_in_field
            		 
            T@50   : | | | | | | | | | | | | | | | | | | | | | | <save_field_in_field
            		 
            T@50   : | | | | | | | | | | | | | | | | | | | | | <fill_record
            		 
            T@50   : | | | | | | | | | | | | | | | | | | | | | >heap_write
            		 
            T@50   : | | | | | | | | | | | | | | | | | | | | | | >next_free_record_pos
            		 
            T@50   : | | | | | | | | | | | | | | | | | | | | | | | >my_malloc
            		 
            T@50   : | | | | | | | | | | | | | | | | | | | | | | | | my: size: 126992 flags: 65552
            		 
            T@50   : | | | | | | | | | | | | | | | | | | | | | | | | info: thd memory_used: 47640  size: 127016
            		 
            T@50   : | | | | | | | | | | | | | | | | | | | | | | | | exit: ptr: 0x7f
            		 
            T@50   : | | | | | | | | | | | | | | | | | | | | | | | <my_malloc
            		 
            T@50   : | | | | | | | | | | | | | | | | | | | | | | | exit: Used new position: 0x7f
            		 
            T@50   : | | | | | | | | | | | | | | | | | | | | | | <next_free_record_pos
            		 
            T@50   : | | | | | | | | | | | | | | | | | | | | | <heap_write
            		 
            T@50   : | | | | | | | | | | | | | | | | | | | | <end_send
            		 
            T@50   : | | | | | | | | | | | | | | | | | | | <JOIN_CACHE::generate_full_extensions
            		 
            T@50   : | | | | | | | | | | | | | | | | | | <JOIN_CACHE::join_null_complements

            Johnston Rex Johnston added a comment - with null compliment bug T@50 : | | | | | | | | | | | >JOIN_CACHE::join_null_complements T@50 : | | | | | | | | | | | | >JOIN_CACHE::generate_full_extensions T@50 : | | | | | | | | | | | | | >JOIN_CACHE:check_match T@50 : | | | | | | | | | | | | | <JOIN_CACHE:check_match T@50 : | | | | | | | | | | | | | >end_send T@50 : | | | | | | | | | | | | | | >select_send::send_data T@50 : | | | | | | | | | | | | | | | >Protocol::send_result_set_row T@50 : | | | | | | | | | | | | | | | <Protocol::send_result_set_row T@50 : | | | | | | | | | | | | | | | >Protocol::write T@50 : | | | | | | | | | | | | | | | | packet_header: Memory: 0x7f Bytes: (4) 04 00 00 06 T@50 : | | | | | | | | | | | | | | | <Protocol::write T@50 : | | | | | | | | | | | | | | <select_send::send_data T@50 : | | | | | | | | | | | | | <end_send T@50 : | | | | | | | | | | | | <JOIN_CACHE::generate_full_extensions T@50 : | | | | | | | | | | | <JOIN_CACHE::join_null_complements   without null compliment bug T@50 : | | | | | | | | | | | | | | | | | | >JOIN_CACHE::join_null_complements T@50 : | | | | | | | | | | | | | | | | | | | >JOIN_CACHE::generate_full_extensions T@50 : | | | | | | | | | | | | | | | | | | | | >JOIN_CACHE:check_match T@50 : | | | | | | | | | | | | | | | | | | | | <JOIN_CACHE:check_match T@50 : | | | | | | | | | | | | | | | | | | | | >end_send T@50 : | | | | | | | | | | | | | | | | | | | | | >fill_record T@50 : | | | | | | | | | | | | | | | | | | | | | | >save_field_in_field T@50 : | | | | | | | | | | | | | | | | | | | | | | <save_field_in_field T@50 : | | | | | | | | | | | | | | | | | | | | | | >save_field_in_field T@50 : | | | | | | | | | | | | | | | | | | | | | | <save_field_in_field T@50 : | | | | | | | | | | | | | | | | | | | | | <fill_record T@50 : | | | | | | | | | | | | | | | | | | | | | >heap_write T@50 : | | | | | | | | | | | | | | | | | | | | | | >next_free_record_pos T@50 : | | | | | | | | | | | | | | | | | | | | | | | >my_malloc T@50 : | | | | | | | | | | | | | | | | | | | | | | | | my: size: 126992 flags: 65552 T@50 : | | | | | | | | | | | | | | | | | | | | | | | | info: thd memory_used: 47640 size: 127016 T@50 : | | | | | | | | | | | | | | | | | | | | | | | | exit: ptr: 0x7f T@50 : | | | | | | | | | | | | | | | | | | | | | | | <my_malloc T@50 : | | | | | | | | | | | | | | | | | | | | | | | exit: Used new position: 0x7f T@50 : | | | | | | | | | | | | | | | | | | | | | | <next_free_record_pos T@50 : | | | | | | | | | | | | | | | | | | | | | <heap_write T@50 : | | | | | | | | | | | | | | | | | | | | <end_send T@50 : | | | | | | | | | | | | | | | | | | | <JOIN_CACHE::generate_full_extensions T@50 : | | | | | | | | | | | | | | | | | | <JOIN_CACHE::join_null_complements
            Johnston Rex Johnston added a comment -

            incorrect result being written here

            (gdb) where
            		 
            #0  fill_record (thd=0x7fff94000db8, table=0x7fff942f2a80, ptr=0x7fff942f33e8, values=..., ignore_errors=true, use_value=false)
            		 
                at /home/rex/src/mariadb/server/sql/sql_base.cc:9187
            		 
            #1  0x0000555556080069 in select_unit::send_data (this=0x7fff942abd38, values=...) at /home/rex/src/mariadb/server/sql/sql_union.cc:119
            		 
            #2  0x0000555555fffcd7 in select_result_sink::send_data_with_check (this=0x7fff942abd38, items=..., u=0x7fff94018298, sent=0)
            		 
                at /home/rex/src/mariadb/server/sql/sql_class.h:5721
            		 
            #3  0x0000555555fe5d24 in end_send (join=0x7fff942abe20, join_tab=0x7fff942af810, end_of_records=false) at /home/rex/src/mariadb/server/sql/sql_select.cc:23078
            		 
            #4  0x000055555616fbb1 in JOIN_CACHE::generate_full_extensions (this=0x7fff942b0320, rec_ptr=0x7fff942f8979 "\001\001")
            		 
                at /home/rex/src/mariadb/server/sql/sql_join_cache.cc:2478
            		 
            #5  0x000055555616fed7 in JOIN_CACHE::join_null_complements (this=0x7fff942b0320, skip_last=false) at /home/rex/src/mariadb/server/sql/sql_join_cache.cc:2626
            		 
            #6  0x000055555616f30a in JOIN_CACHE::join_records (this=0x7fff942b0320, skip_last=false) at /home/rex/src/mariadb/server/sql/sql_join_cache.cc:2194
            		 
            #7  0x0000555555fe25b2 in sub_select_cache (join=0x7fff942abe20, join_tab=0x7fff942af458, end_of_records=true)
            		 
                at /home/rex/src/mariadb/server/sql/sql_select.cc:21614
            		 
            #8  0x0000555555fe27bc in sub_select (join=0x7fff942abe20, join_tab=0x7fff942af0a0, end_of_records=true) at /home/rex/src/mariadb/server/sql/sql_select.cc:21785
            		 
            #9  0x0000555555fe1f3f in do_select (join=0x7fff942abe20, procedure=0x0) at /home/rex/src/mariadb/server/sql/sql_select.cc:21387
            		 
            #10 0x0000555555fb3a62 in JOIN::exec_inner (this=0x7fff942abe20) at /home/rex/src/mariadb/server/sql/sql_select.cc:4822
            		 
            #11 0x0000555555fb2ad7 in JOIN::exec (this=0x7fff942abe20) at /home/rex/src/mariadb/server/sql/sql_select.cc:4600
            		 
            #12 0x0000555555fb4408 in mysql_select (thd=0x7fff94000db8, tables=0x7fff942f43c0, fields=..., conds=0x0, og_num=0, order=0x0, group=0x0, having=0x0, 
                proc_param=0x0, select_options=2201187781376, result=0x7fff942abd38, unit=0x7fff94018298, select_lex=0x7fff94019f28)
            		 
                at /home/rex/src/mariadb/server/sql/sql_select.cc:5080
            		 
            #13 0x0000555555ee494a in mysql_derived_fill (thd=0x7fff94000db8, lex=0x7fff94005120, derived=0x7fff94016eb0)
            		 
                at /home/rex/src/mariadb/server/sql/sql_derived.cc:1289
            		 
            #14 0x0000555555ee1882 in mysql_handle_single_derived (lex=0x7fff94005120, derived=0x7fff94016eb0, phases=96)
            		 
                at /home/rex/src/mariadb/server/sql/sql_derived.cc:200
            		 
            #15 0x0000555555fcf06a in st_join_table::preread_init (this=0x7fff942b1a78) at /home/rex/src/mariadb/server/sql/sql_select.cc:14588
            		 
            #16 0x0000555555fe2846 in sub_select (join=0x7fff942aaff8, join_tab=0x7fff942b1a78, end_of_records=false) at /home/rex/src/mariadb/server/sql/sql_select.cc:21802
            		 
            #17 0x0000555555fe1ed5 in do_select (join=0x7fff942aaff8, procedure=0x0) at /home/rex/src/mariadb/server/sql/sql_select.cc:21385
            		 
            #18 0x0000555555fb3a62 in JOIN::exec_inner (this=0x7fff942aaff8) at /home/rex/src/mariadb/server/sql/sql_select.cc:4822
            		 
            #19 0x0000555555fb2ad7 in JOIN::exec (this=0x7fff942aaff8) at /home/rex/src/mariadb/server/sql/sql_select.cc:4600
            		 
            #20 0x0000555555fb4408 in mysql_select (thd=0x7fff94000db8, tables=0x7fff94016eb0, fields=..., conds=0x0, og_num=0, order=0x0, group=0x0, having=0x0, 
                proc_param=0x0, select_options=2164525824, result=0x7fff942f7f60, unit=0x7fff940051f8, select_lex=0x7fff940168d0)
            		 
                at /home/rex/src/mariadb/server/sql/sql_select.cc:5080
            		 
            #21 0x0000555555fa3493 in handle_select (thd=0x7fff94000db8, lex=0x7fff94005120, result=0x7fff942f7f60, setup_tables_done_option=0)
            		 
                at /home/rex/src/mariadb/server/sql/sql_select.cc:581
            		 
            #22 0x0000555555f4b4e9 in execute_sqlcom_select (thd=0x7fff94000db8, all_tables=0x7fff94016eb0) at /home/rex/src/mariadb/server/sql/sql_parse.cc:6265
            		 
            #23 0x0000555555f426fd in mysql_execute_command (thd=0x7fff94000db8, is_called_from_prepared_stmt=false) at /home/rex/src/mariadb/server/sql/sql_parse.cc:3949
            		 
            #24 0x0000555555f50117 in mysql_parse (thd=0x7fff94000db8, rawbuf=0x7fff94016850 "select * from v1", length=16, parser_state=0x7fffe0107390)
            		 
                at /home/rex/src/mariadb/server/sql/sql_parse.cc:8000
            		 
            #25 0x0000555555f3c742 in dispatch_command (command=COM_QUERY, thd=0x7fff94000db8, packet=0x7fff9400b929 "", packet_length=16, blocking=true)
            		 
                at /home/rex/src/mariadb/server/sql/sql_parse.cc:1894
            		 
            #26 0x0000555555f3b11b in do_command (thd=0x7fff94000db8, blocking=true) at /home/rex/src/mariadb/server/sql/sql_parse.cc:1407
            		 
            #27 0x0000555556116142 in do_handle_one_connection (connect=0x555558a444f8, put_in_cache=true) at /home/rex/src/mariadb/server/sql/sql_connect.cc:1415
            		 
            #28 0x0000555556115ead in handle_one_connection (arg=0x555558a444f8) at /home/rex/src/mariadb/server/sql/sql_connect.cc:1317
            		 
            #29 0x0000555556616b42 in pfs_spawn_thread (arg=0x55555895d528) at /home/rex/src/mariadb/server/storage/perfschema/pfs.cc:2201
            		 
            #30 0x00007ffff7940ea7 in start_thread (arg=<optimized out>) at pthread_create.c:477
            		 
            #31 0x00007ffff752daef in clone () at ../sysdeps/unix/sysv/linux/x86_64/clone.S:95

            Johnston Rex Johnston added a comment - incorrect result being written here (gdb) where #0 fill_record (thd=0x7fff94000db8, table=0x7fff942f2a80, ptr=0x7fff942f33e8, values=..., ignore_errors=true, use_value=false) at /home/rex/src/mariadb/server/sql/sql_base.cc:9187 #1 0x0000555556080069 in select_unit::send_data (this=0x7fff942abd38, values=...) at /home/rex/src/mariadb/server/sql/sql_union.cc:119 #2 0x0000555555fffcd7 in select_result_sink::send_data_with_check (this=0x7fff942abd38, items=..., u=0x7fff94018298, sent=0) at /home/rex/src/mariadb/server/sql/sql_class.h:5721 #3 0x0000555555fe5d24 in end_send (join=0x7fff942abe20, join_tab=0x7fff942af810, end_of_records=false) at /home/rex/src/mariadb/server/sql/sql_select.cc:23078 #4 0x000055555616fbb1 in JOIN_CACHE::generate_full_extensions (this=0x7fff942b0320, rec_ptr=0x7fff942f8979 "\001\001") at /home/rex/src/mariadb/server/sql/sql_join_cache.cc:2478 #5 0x000055555616fed7 in JOIN_CACHE::join_null_complements (this=0x7fff942b0320, skip_last=false) at /home/rex/src/mariadb/server/sql/sql_join_cache.cc:2626 #6 0x000055555616f30a in JOIN_CACHE::join_records (this=0x7fff942b0320, skip_last=false) at /home/rex/src/mariadb/server/sql/sql_join_cache.cc:2194 #7 0x0000555555fe25b2 in sub_select_cache (join=0x7fff942abe20, join_tab=0x7fff942af458, end_of_records=true) at /home/rex/src/mariadb/server/sql/sql_select.cc:21614 #8 0x0000555555fe27bc in sub_select (join=0x7fff942abe20, join_tab=0x7fff942af0a0, end_of_records=true) at /home/rex/src/mariadb/server/sql/sql_select.cc:21785 #9 0x0000555555fe1f3f in do_select (join=0x7fff942abe20, procedure=0x0) at /home/rex/src/mariadb/server/sql/sql_select.cc:21387 #10 0x0000555555fb3a62 in JOIN::exec_inner (this=0x7fff942abe20) at /home/rex/src/mariadb/server/sql/sql_select.cc:4822 #11 0x0000555555fb2ad7 in JOIN::exec (this=0x7fff942abe20) at /home/rex/src/mariadb/server/sql/sql_select.cc:4600 #12 0x0000555555fb4408 in mysql_select (thd=0x7fff94000db8, tables=0x7fff942f43c0, fields=..., conds=0x0, og_num=0, order=0x0, group=0x0, having=0x0, proc_param=0x0, select_options=2201187781376, result=0x7fff942abd38, unit=0x7fff94018298, select_lex=0x7fff94019f28) at /home/rex/src/mariadb/server/sql/sql_select.cc:5080 #13 0x0000555555ee494a in mysql_derived_fill (thd=0x7fff94000db8, lex=0x7fff94005120, derived=0x7fff94016eb0) at /home/rex/src/mariadb/server/sql/sql_derived.cc:1289 #14 0x0000555555ee1882 in mysql_handle_single_derived (lex=0x7fff94005120, derived=0x7fff94016eb0, phases=96) at /home/rex/src/mariadb/server/sql/sql_derived.cc:200 #15 0x0000555555fcf06a in st_join_table::preread_init (this=0x7fff942b1a78) at /home/rex/src/mariadb/server/sql/sql_select.cc:14588 #16 0x0000555555fe2846 in sub_select (join=0x7fff942aaff8, join_tab=0x7fff942b1a78, end_of_records=false) at /home/rex/src/mariadb/server/sql/sql_select.cc:21802 #17 0x0000555555fe1ed5 in do_select (join=0x7fff942aaff8, procedure=0x0) at /home/rex/src/mariadb/server/sql/sql_select.cc:21385 #18 0x0000555555fb3a62 in JOIN::exec_inner (this=0x7fff942aaff8) at /home/rex/src/mariadb/server/sql/sql_select.cc:4822 #19 0x0000555555fb2ad7 in JOIN::exec (this=0x7fff942aaff8) at /home/rex/src/mariadb/server/sql/sql_select.cc:4600 #20 0x0000555555fb4408 in mysql_select (thd=0x7fff94000db8, tables=0x7fff94016eb0, fields=..., conds=0x0, og_num=0, order=0x0, group=0x0, having=0x0, proc_param=0x0, select_options=2164525824, result=0x7fff942f7f60, unit=0x7fff940051f8, select_lex=0x7fff940168d0) at /home/rex/src/mariadb/server/sql/sql_select.cc:5080 #21 0x0000555555fa3493 in handle_select (thd=0x7fff94000db8, lex=0x7fff94005120, result=0x7fff942f7f60, setup_tables_done_option=0) at /home/rex/src/mariadb/server/sql/sql_select.cc:581 #22 0x0000555555f4b4e9 in execute_sqlcom_select (thd=0x7fff94000db8, all_tables=0x7fff94016eb0) at /home/rex/src/mariadb/server/sql/sql_parse.cc:6265 #23 0x0000555555f426fd in mysql_execute_command (thd=0x7fff94000db8, is_called_from_prepared_stmt=false) at /home/rex/src/mariadb/server/sql/sql_parse.cc:3949 #24 0x0000555555f50117 in mysql_parse (thd=0x7fff94000db8, rawbuf=0x7fff94016850 "select * from v1", length=16, parser_state=0x7fffe0107390) at /home/rex/src/mariadb/server/sql/sql_parse.cc:8000 #25 0x0000555555f3c742 in dispatch_command (command=COM_QUERY, thd=0x7fff94000db8, packet=0x7fff9400b929 "", packet_length=16, blocking=true) at /home/rex/src/mariadb/server/sql/sql_parse.cc:1894 #26 0x0000555555f3b11b in do_command (thd=0x7fff94000db8, blocking=true) at /home/rex/src/mariadb/server/sql/sql_parse.cc:1407 #27 0x0000555556116142 in do_handle_one_connection (connect=0x555558a444f8, put_in_cache=true) at /home/rex/src/mariadb/server/sql/sql_connect.cc:1415 #28 0x0000555556115ead in handle_one_connection (arg=0x555558a444f8) at /home/rex/src/mariadb/server/sql/sql_connect.cc:1317 #29 0x0000555556616b42 in pfs_spawn_thread (arg=0x55555895d528) at /home/rex/src/mariadb/server/storage/perfschema/pfs.cc:2201 #30 0x00007ffff7940ea7 in start_thread (arg=<optimized out>) at pthread_create.c:477 #31 0x00007ffff752daef in clone () at ../sysdeps/unix/sysv/linux/x86_64/clone.S:95
            igor Igor Babaev (Inactive) added a comment - - edited

            Johnston,
            Without your patch we have:

            MariaDB [test]> SELECT * FROM ( SELECT * FROM (SELECT (SELECT COUNT(*) FROM t1) AS Elections, Election FROM t1) S  LEFT JOIN ( SELECT ( SELECT COUNT(*) FROM t2) AS Voted, ElectionID FROM t2 ) T ON T.ElectionID = S.Election LIMIT 9) DT;
            		 
            +-----------+----------+-------+------------+
            		 
            | Elections | Election | Voted | ElectionID |
            		 
            +-----------+----------+-------+------------+
            		 
            |         1 |        1 |     2 |       NULL |
            		 
            +-----------+----------+-------+------------+

            and

            MariaDB [test]> SELECT * FROM ( SELECT * FROM (SELECT 1 AS Elections, Election FROM t1) S  LEFT JOIN ( SELECT 'Y' AS Voted, ElectionID FROM t2 ) T ON T.ElectionID = S.Election LIMIT 9) DT;
            		 
            +-----------+----------+-------+------------+
            		 
            | Elections | Election | Voted | ElectionID |
            		 
            +-----------+----------+-------+------------+
            		 
            |         1 |        1 | Y     |       NULL |
            		 
            +-----------+----------+-------+------------+

            With your patch we have:

            MariaDB [test]> SELECT * FROM ( SELECT * FROM (SELECT (SELECT COUNT(*) FROM t1) AS Elections, Election FROM t1) S  LEFT JOIN ( SELECT ( SELECT COUNT(*) FROM t2) AS Voted, ElectionID FROM t2 ) T ON T.ElectionID = S.Election LIMIT 9) DT;
            		 
            +-----------+----------+-------+------------+
            		 
            | Elections | Election | Voted | ElectionID |
            		 
            +-----------+----------+-------+------------+
            		 
            |         1 |        1 |     2 |       NULL |
            		 
            +-----------+----------+-------+------------+

            and

            MariaDB [test]> SELECT * FROM ( SELECT * FROM (SELECT 1 AS Elections, Election FROM t1) S  LEFT JOIN ( SELECT 'Y' AS Voted, ElectionID FROM t2 ) T ON T.ElectionID = S.Election LIMIT 9) DT;                                                   
            +-----------+----------+-------+------------+
            		 
            | Elections | Election | Voted | ElectionID |
            		 
            +-----------+----------+-------+------------+
            		 
            |         0 |        1 | NULL  |       NULL |
            		 
            +-----------+----------+-------+------------+

            It looks like the problem is pretty hard.

            igor Igor Babaev (Inactive) added a comment - - edited Johnston , Without your patch we have: MariaDB [test]> SELECT * FROM ( SELECT * FROM (SELECT (SELECT COUNT(*) FROM t1) AS Elections, Election FROM t1) S LEFT JOIN ( SELECT ( SELECT COUNT(*) FROM t2) AS Voted, ElectionID FROM t2 ) T ON T.ElectionID = S.Election LIMIT 9) DT; +-----------+----------+-------+------------+ | Elections | Election | Voted | ElectionID | +-----------+----------+-------+------------+ | 1 | 1 | 2 | NULL | +-----------+----------+-------+------------+ and MariaDB [test]> SELECT * FROM ( SELECT * FROM (SELECT 1 AS Elections, Election FROM t1) S LEFT JOIN ( SELECT 'Y' AS Voted, ElectionID FROM t2 ) T ON T.ElectionID = S.Election LIMIT 9) DT; +-----------+----------+-------+------------+ | Elections | Election | Voted | ElectionID | +-----------+----------+-------+------------+ | 1 | 1 | Y | NULL | +-----------+----------+-------+------------+ With your patch we have: MariaDB [test]> SELECT * FROM ( SELECT * FROM (SELECT (SELECT COUNT(*) FROM t1) AS Elections, Election FROM t1) S LEFT JOIN ( SELECT ( SELECT COUNT(*) FROM t2) AS Voted, ElectionID FROM t2 ) T ON T.ElectionID = S.Election LIMIT 9) DT; +-----------+----------+-------+------------+ | Elections | Election | Voted | ElectionID | +-----------+----------+-------+------------+ | 1 | 1 | 2 | NULL | +-----------+----------+-------+------------+ and MariaDB [test]> SELECT * FROM ( SELECT * FROM (SELECT 1 AS Elections, Election FROM t1) S LEFT JOIN ( SELECT 'Y' AS Voted, ElectionID FROM t2 ) T ON T.ElectionID = S.Election LIMIT 9) DT; +-----------+----------+-------+------------+ | Elections | Election | Voted | ElectionID | +-----------+----------+-------+------------+ | 0 | 1 | NULL | NULL | +-----------+----------+-------+------------+ It looks like the problem is pretty hard.
            igor Igor Babaev (Inactive) added a comment -

            For the queries below we have correct answers without any patch:

            MariaDB [test]> SELECT * FROM ( SELECT * FROM (SELECT 1 AS Elections, Election FROM t1) S  LEFT JOIN ( SELECT R.a AS Voted, ElectionID FROM (SELECT 'Y' AS a FROM DUAL) R, t2 ) T ON T.ElectionID = S.Election LIMIT 9) DT;
            		 
            +-----------+----------+-------+------------+
            		 
            | Elections | Election | Voted | ElectionID |
            		 
            +-----------+----------+-------+------------+
            		 
            |         1 |        1 | NULL  |       NULL |
            		 
            +-----------+----------+-------+------------+
            		 
             
            		 
            MariaDB [test]> SELECT * FROM ( SELECT * FROM (SELECT 1 AS Elections, Election FROM t1) S  LEFT JOIN ( SELECT R.c AS Voted, ElectionID FROM (SELECT COUNT(*) AS c FROM t2) R, t2 ) T ON T.ElectionID = S.Election LIMIT 9) DT;
            		 
            +-----------+----------+-------+------------+
            		 
            | Elections | Election | Voted | ElectionID |
            		 
            +-----------+----------+-------+------------+
            		 
            |         1 |        1 |  NULL |       NULL |
            		 
            +-----------+----------+-------+------------+

            igor Igor Babaev (Inactive) added a comment - For the queries below we have correct answers without any patch: MariaDB [test]> SELECT * FROM ( SELECT * FROM (SELECT 1 AS Elections, Election FROM t1) S LEFT JOIN ( SELECT R.a AS Voted, ElectionID FROM (SELECT 'Y' AS a FROM DUAL) R, t2 ) T ON T.ElectionID = S.Election LIMIT 9) DT; +-----------+----------+-------+------------+ | Elections | Election | Voted | ElectionID | +-----------+----------+-------+------------+ | 1 | 1 | NULL | NULL | +-----------+----------+-------+------------+   MariaDB [test]> SELECT * FROM ( SELECT * FROM (SELECT 1 AS Elections, Election FROM t1) S LEFT JOIN ( SELECT R.c AS Voted, ElectionID FROM (SELECT COUNT(*) AS c FROM t2) R, t2 ) T ON T.ElectionID = S.Election LIMIT 9) DT; +-----------+----------+-------+------------+ | Elections | Election | Voted | ElectionID | +-----------+----------+-------+------------+ | 1 | 1 | NULL | NULL | +-----------+----------+-------+------------+
            Johnston Rex Johnston added a comment - - edited

            with patch at 3d92430

             
            		 
            MariaDB [test]> SELECT * FROM ( SELECT * FROM (SELECT 1 AS Elections, Election FROM t1) S  LEFT JOIN ( SELECT R.a AS Voted, ElectionID FROM (SELECT 'Y' AS a FROM DUAL) R, t2 ) T ON T.ElectionID = S.Election LIMIT 9) DT;
            		 
            +-----------+----------+-------+------------+
            		 
            | Elections | Election | Voted | ElectionID |
            		 
            +-----------+----------+-------+------------+
            		 
            |         1 |        1 | NULL  |       NULL |
            		 
            +-----------+----------+-------+------------+
            		 
            1 row in set (0.011 sec)
            		 
             
            		 
            MariaDB [test]> SELECT * FROM ( SELECT * FROM (SELECT 1 AS Elections, Election FROM t1) S  LEFT JOIN ( SELECT R.c AS Voted, ElectionID FROM (SELECT COUNT(*) AS c FROM t2) R, t2 ) T ON T.ElectionID = S.Election LIMIT 9) DT;
            		 
            +-----------+----------+-------+------------+
            		 
            | Elections | Election | Voted | ElectionID |
            		 
            +-----------+----------+-------+------------+
            		 
            |         1 |        1 |  NULL |       NULL |
            		 
            +-----------+----------+-------+------------+
            		 
            1 row in set (0.007 sec)
            		 
             
            		 
            SELECT * FROM ( SELECT * FROM (SELECT 1 AS Elections, Election FROM t1) S  LEFT JOIN ( SELECT 'Y' AS Voted, ElectionID FROM t2 ) T ON T.ElectionID = S.Election LIMIT 9) DT;
            		 
            +-----------+----------+-------+------------+
            		 
            | Elections | Election | Voted | ElectionID |
            		 
            +-----------+----------+-------+------------+
            		 
            |         1 |        1 | NULL  |       NULL |
            		 
            +-----------+----------+-------+------------+
            		 
            1 row in set (0.008 sec)

            I'm pretty sure that "Voted" in the last query should be NULL not 'Y'.

            Johnston Rex Johnston added a comment - - edited with patch at 3d92430   MariaDB [test]> SELECT * FROM ( SELECT * FROM ( SELECT 1 AS Elections, Election FROM t1) S LEFT JOIN ( SELECT R.a AS Voted, ElectionID FROM ( SELECT 'Y' AS a FROM DUAL) R, t2 ) T ON T.ElectionID = S.Election LIMIT 9) DT; + -----------+----------+-------+------------+ | Elections | Election | Voted | ElectionID | + -----------+----------+-------+------------+ | 1 | 1 | NULL | NULL | + -----------+----------+-------+------------+ 1 row in set (0.011 sec)   MariaDB [test]> SELECT * FROM ( SELECT * FROM ( SELECT 1 AS Elections, Election FROM t1) S LEFT JOIN ( SELECT R.c AS Voted, ElectionID FROM ( SELECT COUNT (*) AS c FROM t2) R, t2 ) T ON T.ElectionID = S.Election LIMIT 9) DT; + -----------+----------+-------+------------+ | Elections | Election | Voted | ElectionID | + -----------+----------+-------+------------+ | 1 | 1 | NULL | NULL | + -----------+----------+-------+------------+ 1 row in set (0.007 sec)   SELECT * FROM ( SELECT * FROM ( SELECT 1 AS Elections, Election FROM t1) S LEFT JOIN ( SELECT 'Y' AS Voted, ElectionID FROM t2 ) T ON T.ElectionID = S.Election LIMIT 9) DT; + -----------+----------+-------+------------+ | Elections | Election | Voted | ElectionID | + -----------+----------+-------+------------+ | 1 | 1 | NULL | NULL | + -----------+----------+-------+------------+ 1 row in set (0.008 sec) I'm pretty sure that "Voted" in the last query should be NULL not 'Y'.
            psergei Sergei Petrunia added a comment -

            (Commenting based on the patch, not testcases):

            Johnston, One obvious thing that the patch misses is that it has the code to set item to NULL but doesn't have the code to clear it...

            psergei Sergei Petrunia added a comment - (Commenting based on the patch, not testcases): Johnston , One obvious thing that the patch misses is that it has the code to set item to NULL but doesn't have the code to clear it...
            psergei Sergei Petrunia added a comment - - edited

            Looking at the issue:

            Outer join computation is done as follows: for

              t1 left join t2 on ...

            table t2 may have a NULL-complemented row. This is achieved by calling mark_as_null_row() for t2, which makes all of columns in t2 to get NULL values.

            In our example, however, there is a column Voted which is not a column of t2:

             t1
            		 
              LEFT JOIN ( SELECT 'Y' AS Voted, ElectionID FROM t2 ) AS T

            The query that is not using the view produces the correct result (Voted=NULL)...

            Looking how does it manage to do that... select_send::send_data() calls this:

             
            		 
            bool Item_direct_view_ref::send(Protocol *protocol, st_value *buffer)
            		 
            {
            		 
              if (check_null_ref())
            		 
                return protocol->store_null();
            		 
            ...

            check_null_ref() is how 'Y as Voted' changes its value depending on whether t2 has NULL-complemented row or not.
            There is an accompanying setup function set_null_ref_table()...

            Now, debugging the failing query... I see calls are made to Item_direct_view_ref::set_null_ref_table...

            However Item_ref::save_in_field() doesn't call check_null_ref(): 

            int Item_ref::save_in_field(Field *to, bool no_conversions)
            		 
            {
            		 
              int res;
            		 
              if (result_field) // Not taken
            		 
              {
            		 
              ....
            		 
              }
            		 
              res= (*ref)->save_in_field(to, no_conversions);
            		 
            }

            psergei Sergei Petrunia added a comment - - edited Looking at the issue: Outer join computation is done as follows: for t1 left join t2 on ... table t2 may have a NULL-complemented row. This is achieved by calling mark_as_null_row() for t2, which makes all of columns in t2 to get NULL values. In our example, however, there is a column Voted which is not a column of t2: t1 LEFT JOIN ( SELECT 'Y' AS Voted, ElectionID FROM t2 ) AS T The query that is not using the view produces the correct result (Voted=NULL)... Looking how does it manage to do that... select_send::send_data() calls this:   bool Item_direct_view_ref::send(Protocol *protocol, st_value *buffer) { if (check_null_ref()) return protocol->store_null(); ... check_null_ref() is how 'Y as Voted' changes its value depending on whether t2 has NULL-complemented row or not. There is an accompanying setup function set_null_ref_table()... Now, debugging the failing query... I see calls are made to Item_direct_view_ref::set_null_ref_table... However Item_ref::save_in_field() doesn't call check_null_ref(): int Item_ref::save_in_field(Field *to, bool no_conversions) { int res; if (result_field) // Not taken { .... } res= (* ref )->save_in_field(to, no_conversions); }
            psergei Sergei Petrunia added a comment -

            So, the fix is probably to make Item_ref::save_in_field() do the check...

            Note that there are Item_direct_view_ref::save_org_in_field and Item_direct_view_ref::save_in_result_field which do call check_null_ref()...

            psergei Sergei Petrunia added a comment - So, the fix is probably to make Item_ref::save_in_field() do the check... Note that there are Item_direct_view_ref::save_org_in_field and Item_direct_view_ref::save_in_result_field which do call check_null_ref()...
            Johnston Rex Johnston added a comment -

            In order to do this, we'll need to pull this

              TABLE *null_ref_table;
            		 
             
            		 
              void set_null_ref_table()
            		 
              {
            		 
                if (!view->is_inner_table_of_outer_join() ||
            		 
                    !(null_ref_table= view->get_real_join_table()))
            		 
                  null_ref_table= NO_NULL_TABLE;
            		 
              }
            		 
             
            		 
              bool check_null_ref()
            		 
              {
            		 
                DBUG_ASSERT(null_ref_table);
            		 
                if (null_ref_table != NO_NULL_TABLE && null_ref_table->null_row)
            		 
                {
            		 
                  null_value= 1;
            		 
                  return TRUE;
            		 
                }
            		 
                return FALSE;
            		 
              }

            from Item_direct_view_ref into it's parent's parent class Item_ref. Or equivalent.

            Johnston Rex Johnston added a comment - In order to do this, we'll need to pull this TABLE *null_ref_table;   void set_null_ref_table() { if (!view->is_inner_table_of_outer_join() || !(null_ref_table= view->get_real_join_table())) null_ref_table= NO_NULL_TABLE; }   bool check_null_ref() { DBUG_ASSERT(null_ref_table); if (null_ref_table != NO_NULL_TABLE && null_ref_table->null_row) { null_value= 1; return TRUE; } return FALSE; } from Item_direct_view_ref into it's parent's parent class Item_ref. Or equivalent.
            igor Igor Babaev (Inactive) added a comment - - edited

            Another observation: without any patch for this query we have:

            MariaDB [test]> SELECT * FROM ( SELECT * FROM (SELECT 1 AS Elections, Election FROM t1) S  LEFT JOIN ( SELECT 'Y' AS Voted ) T ON S.Election = 20 LIMIT 9) DT;
            		 
            +-----------+----------+-------+
            		 
            | Elections | Election | Voted |
            		 
            +-----------+----------+-------+
            		 
            |         1 |        1 | NULL  |
            		 
            +-----------+----------+-------+
            		 
            1 row in set (0.001 sec)
            		 
             
            		 
            MariaDB [test]> EXPLAIN EXTENDED SELECT * FROM ( SELECT * FROM (SELECT 1 AS Electors, Election FROM t1) S  LEFT JOIN ( SELECT 'Y' AS Voted ) T ON S.Election = 20 LIMIT 9) DT;
            		 
            +------+-------------+------------+------+---------------+------+---------+------+------+----------+----------------+
            		 
            | id   | select_type | table      | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra          |
            		 
            +------+-------------+------------+------+---------------+------+---------+------+------+----------+----------------+
            		 
            |    1 | PRIMARY     | <derived2> | ALL  | NULL          | NULL | NULL    | NULL |    2 |   100.00 |                |
            		 
            |    2 | DERIVED     | t1         | ALL  | NULL          | NULL | NULL    | NULL |    1 |   100.00 |                |
            		 
            |    2 | DERIVED     | <derived4> | ALL  | NULL          | NULL | NULL    | NULL |    1 |   100.00 | Using where    |
            		 
            |    4 | DERIVED     | NULL       | NULL | NULL          | NULL | NULL    | NULL | NULL |     NULL | No tables used |
            		 
            +------+-------------+------------+------+---------------+------+---------+------+------+----------+----------------+

            So here T is materialized. It could not be otherwise because in any case we need the right operand for left join,

            igor Igor Babaev (Inactive) added a comment - - edited Another observation: without any patch for this query we have: MariaDB [test]> SELECT * FROM ( SELECT * FROM (SELECT 1 AS Elections, Election FROM t1) S LEFT JOIN ( SELECT 'Y' AS Voted ) T ON S.Election = 20 LIMIT 9) DT; +-----------+----------+-------+ | Elections | Election | Voted | +-----------+----------+-------+ | 1 | 1 | NULL | +-----------+----------+-------+ 1 row in set (0.001 sec)   MariaDB [test]> EXPLAIN EXTENDED SELECT * FROM ( SELECT * FROM (SELECT 1 AS Electors, Election FROM t1) S LEFT JOIN ( SELECT 'Y' AS Voted ) T ON S.Election = 20 LIMIT 9) DT; +------+-------------+------------+------+---------------+------+---------+------+------+----------+----------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +------+-------------+------------+------+---------------+------+---------+------+------+----------+----------------+ | 1 | PRIMARY | <derived2> | ALL | NULL | NULL | NULL | NULL | 2 | 100.00 | | | 2 | DERIVED | t1 | ALL | NULL | NULL | NULL | NULL | 1 | 100.00 | | | 2 | DERIVED | <derived4> | ALL | NULL | NULL | NULL | NULL | 1 | 100.00 | Using where | | 4 | DERIVED | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | No tables used | +------+-------------+------------+------+---------------+------+---------+------+------+----------+----------------+ So here T is materialized. It could not be otherwise because in any case we need the right operand for left join,
            psergei Sergei Petrunia made changes -
            Assignee Rex Johnston [ JIRAUSER52533 ] Sergei Petrunia [ psergey ]
            psergei Sergei Petrunia added a comment -

            Setting correct status.

            psergei Sergei Petrunia added a comment - Setting correct status.
            psergei Sergei Petrunia made changes -
            Status In Progress [ 3 ] In Review [ 10002 ]
            psergei Sergei Petrunia added a comment -

            Review input: https://lists.launchpad.net/maria-developers/msg13275.html

            psergei Sergei Petrunia added a comment - Review input: https://lists.launchpad.net/maria-developers/msg13275.html
            psergei Sergei Petrunia added a comment -

            Alternative patch: https://github.com/MariaDB/server/commit/af37017ccb52a325a5894e2a051970bb19bddcc1

            psergei Sergei Petrunia added a comment - Alternative patch: https://github.com/MariaDB/server/commit/af37017ccb52a325a5894e2a051970bb19bddcc1
            psergei Sergei Petrunia added a comment -

            (Tried all posted examples on the "alternative patch". They all produce correct result)

            psergei Sergei Petrunia added a comment - (Tried all posted examples on the "alternative patch". They all produce correct result)
            psergei Sergei Petrunia made changes -
            Status In Review [ 10002 ] Stalled [ 10000 ]
            psergei Sergei Petrunia added a comment -

            sanja could you please review?

            psergei Sergei Petrunia added a comment - sanja could you please review?
            psergei Sergei Petrunia made changes -
            Assignee Sergei Petrunia [ psergey ] Oleksandr Byelkin [ sanja ]
            Status Stalled [ 10000 ] In Review [ 10002 ]
            psergei Sergei Petrunia added a comment -

            Slightly updated commit to fix compile errors in BB: e2cc436a6a7b1c2438e8db1c02838e7fd68e8024

            psergei Sergei Petrunia added a comment - Slightly updated commit to fix compile errors in BB: e2cc436a6a7b1c2438e8db1c02838e7fd68e8024
            igor Igor Babaev (Inactive) added a comment - - edited

            Here's a good test case to check that it works correctly:

            create table t2 (b int);
            		 
            insert into t2 values (3),(7),(1);
            		 
            create table t3 (c int);
            		 
            insert into t3 values (3),(1);
            		 
            create table t1 (a int);
            		 
            insert into t1 values (1),(2),(7),(1);
            		 
             
            		 
            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
            		 
              limit 9
            		 
            ) dt;
            		 
             
            		 
            create view v3(x,c) as select * from (select 'X' as x, t3.c from t3) dt3;
            		 
            create view v2(y,b) as select * from (select 'Y' as y, t2.b from t2) dt2;
            		 
            create view v(y,b,x,c) as select * from v2 left join v3 on v2.b=v3.c;
            		 
            create view v1 as  select 'Z' as z, t1.a, v.*  from t1 left join v on t1.a=v.b limit 9;
            		 
            select * from v1;

            Expected result set (from PostgreSQL 9.6)

            z	a	y	b	x	c
            		 
            Z	7	Y	7	(null)	(null)
            		 
            Z	1	Y	1	X	1
            		 
            Z	1	Y	1	X	1
            		 
            Z	2	(null)	(null)	(null)	(null)

            Please add it to the patch and run with join_cache_level=0 and join_cache_level=default.

            igor Igor Babaev (Inactive) added a comment - - edited Here's a good test case to check that it works correctly: create table t2 (b int ); insert into t2 values (3),(7),(1); create table t3 (c int ); insert into t3 values (3),(1); create table t1 (a int ); insert into t1 values (1),(2),(7),(1);   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 limit 9 ) dt;   create view v3(x,c) as select * from ( select 'X' as x, t3.c from t3) dt3; create view v2(y,b) as select * from ( select 'Y' as y, t2.b from t2) dt2; create view v(y,b,x,c) as select * from v2 left join v3 on v2.b=v3.c; create view v1 as select 'Z' as z, t1.a, v.* from t1 left join v on t1.a=v.b limit 9; select * from v1; Expected result set (from PostgreSQL 9.6) z a y b x c Z 7 Y 7 (null) (null) Z 1 Y 1 X 1 Z 1 Y 1 X 1 Z 2 (null) (null) (null) (null) Please add it to the patch and run with join_cache_level=0 and join_cache_level=default.
            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
            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.