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

Wrong result on 2-nd execution of PS to select from view using derived

Details

    Description

      Problem is only on merged derived table. Left join with normal table gives correct result.

      Testcase:

      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);
      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;
       
      prepare stmt1 from "select * from v1";
       
      execute stmt1;
      execute stmt1;
       
      deallocate prepare stmt1;
       
      drop view v1;
      drop table t1, t2;
      

      Actual result:

      execute stmt1;
      Election	Voted	ElectionID
      1	NULL	NULL
      execute stmt1;
      Election	Voted	ElectionID
      1		NULL
      

      Expected result:

      execute stmt1;
      Election	Voted	ElectionID
      1	NULL	NULL
      execute stmt1;
      Election	Voted	ElectionID
      1	NULL	NULL
      

      Attachments

        Issue Links

          Activity

            Johnston Rex Johnston added a comment -

            10.4 appears to fail in a slightly different way

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

            vs

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

            Johnston Rex Johnston added a comment - 10.4 appears to fail in a slightly different way +----------+-------+------------+ | Election | Voted | ElectionID | +----------+-------+------------+ | 1 | Y | NULL | +----------+-------+------------+ +----------+-------+------------+ | Election | Voted | ElectionID | +----------+-------+------------+ | 1 | Y | NULL | +----------+-------+------------+ vs +----------+-------+------------+ | Election | Voted | ElectionID | +----------+-------+------------+ | 1 | NULL | NULL | +----------+-------+------------+ +----------+-------+------------+ | Election | Voted | ElectionID | +----------+-------+------------+ | 1 | | NULL | +----------+-------+------------+
            Johnston Rex Johnston added a comment -

            intercepting call in Item_direct_view_ref::save_in_field()

            first execution, field=="(Field *)

            {v1.Voted=NULL}

            "
            second execution field=="(Field *)

            {v1.Voted=}

            "

            traced to creation of temporary table, and the fields used.
            First execution has field->flags==0
            Second execution based on string literal 'Y' which cannot be null, so field->flags==NOT_NULL_FLAG

            Also first execution, setup_fields/.../Field_iterator_view::create_item() has table_list->field_translation not null,
            second execution it is null.

            Johnston Rex Johnston added a comment - intercepting call in Item_direct_view_ref::save_in_field() first execution, field=="(Field *) {v1.Voted=NULL} " second execution field=="(Field *) {v1.Voted=} " traced to creation of temporary table, and the fields used. First execution has field->flags==0 Second execution based on string literal 'Y' which cannot be null, so field->flags==NOT_NULL_FLAG Also first execution, setup_fields/.../Field_iterator_view::create_item() has table_list->field_translation not null, second execution it is null.
            igor Igor Babaev added a comment -

            sanja_byelkin please review this easy patch. I need to incorporate it into my tree for MDEV-30073. You can look at the patch in bb-10.5-igor.

            igor Igor Babaev added a comment - sanja_byelkin please review this easy patch. I need to incorporate it into my tree for MDEV-30073 . You can look at the patch in bb-10.5-igor.

            OK to push (can be pushed in 10.4)

            sanja Oleksandr Byelkin added a comment - OK to push (can be pushed in 10.4)
            igor Igor Babaev added a comment -

            A fix for this bug was pushed into 10.5. It has to be merged upstream as it is. It also can be cherry-picked into 10.4 if it's really needed there.

            igor Igor Babaev added a comment - A fix for this bug was pushed into 10.5. It has to be merged upstream as it is. It also can be cherry-picked into 10.4 if it's really needed there.

            People

              igor Igor Babaev
              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.