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

Wrong join query result with outer_join_with_cache = off

    XMLWordPrintable

    Details

    • Type: Bug
    • Status: Closed (View Workflow)
    • Priority: Major
    • Resolution: Fixed
    • Affects Version/s: 10.0.10, 10.0.11, 10.0.13, 10.1.0
    • Fix Version/s: 10.0.16
    • Component/s: Optimizer
    • Environment:
      OS: doesn't matter (works on both Linux or Windows)
      Hardware: doesn't matter

      Description

      Reproducing

      1. Create two simple tables and fill them with rows:

        CREATE TABLE `table1` (
            `id` INT AUTO_INCREMENT,
            `link` INT,
            `field1` INT,
            PRIMARY KEY (`id`)
        )
        ENGINE=InnoDB;
         
        CREATE TABLE `table2` (
            `id` INT AUTO_INCREMENT,
            `link` INT,
            `field2` INT,
            PRIMARY KEY (`id`)
        )
        ENGINE=InnoDB;
         
        INSERT INTO `table1` (`link`, `field1`) VALUES (3, 1);
        INSERT INTO `table1` (`link`, `field1`) VALUES (1, 2);
        INSERT INTO `table1` (`link`, `field1`) VALUES (2, 3);
         
        INSERT INTO `table2` (`link`, `field2`) VALUES (2, 1);
        INSERT INTO `table2` (`link`, `field2`) VALUES (3, 1);

      2. (optional) Enable outer_join_with_cache optimization, if it's disabled by default:

        SET SESSION optimizer_switch='outer_join_with_cache=on';

      3. Select all rows, joining by `link`, ordering by `field1`, using group_concat function in select query:

        select
            field1, t1.link, field2, group_concat('any string or field')
        from table1 t1
            left join table2 t2 on t1.link = t2.link
        group by field1

        Notice everything works fine now:

        field1 link field2 group_concat
        1 3 1 any string of field
        2 1 NULL any string of field
        3 2 1 any string of field
      4. Disable outer_join_with_cache optimization:

        SET SESSION optimizer_switch='outer_join_with_cache=off';

      5. Repeat step 3.
        Notice that the result of the query has been changed: there is NULL value instead of 1 - it's wrong:
        field1 link field2 group_concat
        1 3 NULL any string of field
        2 1 NULL any string of field
        3 2 1 any string of field
      6. If your database is small you can control this behavior by switching outer_join_with_cache on and off, otherwise it seems like outer_join_with_cache is always turned off, so it always reproduces on big tables, whether outer_join_with_cache is on or off.

      Other points

      1. Using group_concat seems to be a necessary condition for reproducing this bug.
      2. If you will order the query in step 3 by `t1.link`:

        select
            field1, t1.link, field2, group_concat('any string or field')
        from table1 t1
            left join table2 t2 on t1.link = t2.link
        group by field1
        order by t1.link

        everything will work fine:

        field1 link field2 group_concat
        2 1 NULL any string of field
        3 2 1 any string of field
        1 3 1 any string of field

        Attachments

          Issue Links

            Activity

              People

              Assignee:
              psergei Sergei Petrunia
              Reporter:
              justvamp Igor Kosulin
              Votes:
              0 Vote for this issue
              Watchers:
              6 Start watching this issue

                Dates

                Created:
                Updated:
                Resolved: