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

LP:1010116 - Incorrect query results in subqueries

Details

    • Bug
    • Status: Closed (View Workflow)
    • Minor
    • Resolution: Fixed
    • 5.5.25, 5.3.7
    • 5.5.27, 5.3.9
    • None

    Description

      https://bugs.launchpad.net/maria/+bug/1010116

      Test case 1 (with default optimizer_switch):

      SELECT
      		 
      `Derived1`.`id`,
      		 
      `Derived2`.`Val1`
      		 
      FROM (select 30631 as `id`) AS `Derived1` LEFT OUTER JOIN (SELECT
      		 
      2 as `id`,
      		 
      1 AS `Val1`
      		 
      FROM (select 30631 as `id`) AS `Derived3`) AS `Derived2` ON `Derived1`.`id` = `Derived2`.`id`;

      Expected result:

      id	Val1
      		 
      30631	NULL

      Actual result:

      id	Val1
      		 
      30631	1

      Reproducible on maria/5.3 revno 3562, maria/5.5 revno 3508
      Not reproducible on mysql/5.1 revno 3770, mysql/5.5 revno 3900, mysql/5.6 revno 3917, maria/5.2 revno 3168

      Test case 2

      create table t1 ( id int );
      		 
      insert into t1 values (30631);
      		 
      create table t2 ( id int );
      		 
      insert into t2 values (30631);
      		 
      create algorithm=MERGE view v2 as select 2 as id, 1 as val1 from t2;
      		 
      select t1.*, v2.* from t1 left join v2 on t1.id = v2.id;

      Expected result:

      id	id	val1
      		 
      30631	NULL	NULL

      Actual result:

      id	id	val1
      		 
      30631	2	1

      Reproducible on maria/5.2, maria/5.3, maria/5.5, mysql/5.1, mysql/5.5, mysql/5.6

      Attachments

        Issue Links

          relates to

          Bug - A problem which impairs or prevents the functions of the product. MDEV-3914 Wrong result (NULLs instead of real values) with INNER and RIGHT JOIN in a FROM subquery, derived_merge=on

          • Major - Major loss of function.
          • Closed

          Activity

            Ascending order - Click to sort in descending order
            elenst Elena Stepanova created issue -
            elenst Elena Stepanova made changes -
            Field Original Value New Value
            Description https://bugs.launchpad.net/maria/+bug/1010116

            		 https://bugs.launchpad.net/maria/+bug/1010116

            Reproducible on maria/5.3
            elenst Elena Stepanova made changes -
            Description https://bugs.launchpad.net/maria/+bug/1010116

            Reproducible on maria/5.3             		https://bugs.launchpad.net/maria/+bug/1010116

            Test case 2 (with default optimizer_switch):

            {noformat}
            SELECT
            `Derived1`.`id`,
            `Derived2`.`Val1`
            FROM (select 30631 as `id`) AS `Derived1` LEFT OUTER JOIN (SELECT
            2 as `id`,
            1 AS `Val1`
            FROM (select 30631 as `id`) AS `Derived3`) AS `Derived2` ON `Derived1`.`id` = `Derived2`.`id`;
            {noformat}

            Expected result:
            {noformat}
            id Val1
            30631 NULL
            {noformat}

            Actual result:
            {noformat}
            id Val1
            30631 1
            {noformat}

            Reproducible on maria/5.3 revno 3562, maria/5.5 revno 3508
            Not reproducible on mysql/5.1 revno 3770, mysql/5.5 revno 3900, mysql/5.6 revno 3917, maria/5.2 revno 3168

            Test case 2

            {noformat}
            create table t1 ( id int );
            insert into t1 values (30631);
            create table t2 ( id int );
            insert into t2 values (30631);
            create algorithm=MERGE view v2 as select 2 as id, 1 as val1 from t2;
            select t1.*, v2.* from t1 left join v2 on t1.id = v2.id;
            {noformat}

            Expected result:
            {noformat}
            id id val1
            30631 NULL NULL
            {noformat}

            Actual result:
            {noformat}
            id id val1
            30631 2 1
            {noformat}

            Reproducible on maria/5.2, maria/5.3, maria/5.5, mysql/5.1, mysql/5.5, mysql/5.6
            elenst Elena Stepanova made changes -
            Description https://bugs.launchpad.net/maria/+bug/1010116

            Test case 2 (with default optimizer_switch):

            {noformat}
            SELECT
            `Derived1`.`id`,
            `Derived2`.`Val1`
            FROM (select 30631 as `id`) AS `Derived1` LEFT OUTER JOIN (SELECT
            2 as `id`,
            1 AS `Val1`
            FROM (select 30631 as `id`) AS `Derived3`) AS `Derived2` ON `Derived1`.`id` = `Derived2`.`id`;
            {noformat}

            Expected result:
            {noformat}
            id Val1
            30631 NULL
            {noformat}

            Actual result:
            {noformat}
            id Val1
            30631 1
            {noformat}

            Reproducible on maria/5.3 revno 3562, maria/5.5 revno 3508
            Not reproducible on mysql/5.1 revno 3770, mysql/5.5 revno 3900, mysql/5.6 revno 3917, maria/5.2 revno 3168

            Test case 2

            {noformat}
            create table t1 ( id int );
            insert into t1 values (30631);
            create table t2 ( id int );
            insert into t2 values (30631);
            create algorithm=MERGE view v2 as select 2 as id, 1 as val1 from t2;
            select t1.*, v2.* from t1 left join v2 on t1.id = v2.id;
            {noformat}

            Expected result:
            {noformat}
            id id val1
            30631 NULL NULL
            {noformat}

            Actual result:
            {noformat}
            id id val1
            30631 2 1
            {noformat}

            Reproducible on maria/5.2, maria/5.3, maria/5.5, mysql/5.1, mysql/5.5, mysql/5.6

            		 https://bugs.launchpad.net/maria/+bug/1010116

            Test case 1 (with default optimizer_switch):

            {noformat}
            SELECT
            `Derived1`.`id`,
            `Derived2`.`Val1`
            FROM (select 30631 as `id`) AS `Derived1` LEFT OUTER JOIN (SELECT
            2 as `id`,
            1 AS `Val1`
            FROM (select 30631 as `id`) AS `Derived3`) AS `Derived2` ON `Derived1`.`id` = `Derived2`.`id`;
            {noformat}

            Expected result:
            {noformat}
            id Val1
            30631 NULL
            {noformat}

            Actual result:
            {noformat}
            id Val1
            30631 1
            {noformat}

            Reproducible on maria/5.3 revno 3562, maria/5.5 revno 3508
            Not reproducible on mysql/5.1 revno 3770, mysql/5.5 revno 3900, mysql/5.6 revno 3917, maria/5.2 revno 3168

            Test case 2

            {noformat}
            create table t1 ( id int );
            insert into t1 values (30631);
            create table t2 ( id int );
            insert into t2 values (30631);
            create algorithm=MERGE view v2 as select 2 as id, 1 as val1 from t2;
            select t1.*, v2.* from t1 left join v2 on t1.id = v2.id;
            {noformat}

            Expected result:
            {noformat}
            id id val1
            30631 NULL NULL
            {noformat}

            Actual result:
            {noformat}
            id id val1
            30631 2 1
            {noformat}

            Reproducible on maria/5.2, maria/5.3, maria/5.5, mysql/5.1, mysql/5.5, mysql/5.6
            sanja Oleksandr Byelkin made changes -
            Status Open [ 1 ] In Progress [ 3 ]
            sanja Oleksandr Byelkin made changes -
            Status In Progress [ 3 ] Open [ 1 ]
            sanja Oleksandr Byelkin made changes -
            Status Open [ 1 ] In Progress [ 3 ]
            sanja Oleksandr Byelkin made changes -
            Status In Progress [ 3 ] Open [ 1 ]
            sanja Oleksandr Byelkin made changes -
            Status Open [ 1 ] In Progress [ 3 ]
            sanja Oleksandr Byelkin made changes -
            Status In Progress [ 3 ] Open [ 1 ]
            serg Sergei Golubchik made changes -
            Priority Major [ 3 ] Minor [ 4 ]
            serg Sergei Golubchik made changes -
            Labels upstream
            sanja Oleksandr Byelkin made changes -
            Status Open [ 1 ] In Progress [ 3 ]
            sanja Oleksandr Byelkin made changes -
            Status In Progress [ 3 ] Open [ 1 ]
            sanja Oleksandr Byelkin made changes -
            Status Open [ 1 ] In Progress [ 3 ]
            serg Sergei Golubchik made changes -
            Resolution Fixed [ 1 ]
            Status In Progress [ 3 ] Closed [ 6 ]
            elenst Elena Stepanova made changes -
            serg Sergei Golubchik made changes -
            Workflow defaullt [ 13932 ] MariaDB v2 [ 45260 ]
            ratzpo Rasmus Johansson (Inactive) made changes -
            Workflow MariaDB v2 [ 45260 ] MariaDB v3 [ 65924 ]
            serg Sergei Golubchik made changes -
            Workflow MariaDB v3 [ 65924 ] MariaDB v4 [ 144950 ]

            People

              sanja Oleksandr Byelkin
              elenst Elena Stepanova
              Votes:
              0 Vote for this issue
              Watchers:
              3 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.