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

Incorrect Right Join execution

    XMLWordPrintable

Details

    • Bug
    • Status: Closed (View Workflow)
    • Major
    • Resolution: Duplicate
    • 5.1.67, 5.2.14, 5.3.12, 5.5.36, 5.5.37, 10.0.10
    • 5.5.56, 10.0.34, 10.1.29
    • Optimizer
    • Does not matter
    • 5.5.44, 5.5.58, 10.1.29

    Description

      Hello all.
      I've got a strange behavior of the queries with outer right join.
      First let's create some tables, fill them with data and create a function.

      CREATE TABLE `test1` (
        `id` INT NOT NULL,
        PRIMARY KEY (`id`));
       
      insert into test1 values(1);
      insert into test1 values(2);
      insert into test1 values(3);
      insert into test1 values(9);

      CREATE TABLE `test2` (
        `id` INT NOT NULL,
        PRIMARY KEY (`id`));
       
      insert into test2 values(1);
      insert into test2 values(7);
      insert into test2 values(8);
      insert into test2 values(9);

      CREATE TABLE `test3` (
        `id` INT NOT NULL,
        PRIMARY KEY (`id`));
       
      insert into test3 values(1);
      insert into test3 values(2);

      CREATE TABLE `test4` (
        `id` INT NOT NULL,
        PRIMARY KEY (`id`));
       
       
      insert into test4 values(2);
      insert into test4 values(88);

      DELIMITER $$
       
      CREATE FUNCTION `foo` (var1 int )
      RETURNS INTEGER
      BEGIN
      	
      RETURN coalesce((select min(id) from test4 where id = var1), 1);
      END$$
       
      DELIMITER ;

      Now let's try the query:

      select test1.*, test2.*, test3.*
      from test1
      right join test2 ON test1.id = test2.id
      right join test3 ON test3.id = foo(test1.id);

      What do I get:

      '1', '1', '1'
      '9', '9', '1'
      NULL, NULL, '2'

      What do I expect:

      1,	1,	1
      Null,	7,	1
      Null,	8,	1
      9,	9,	1
      Null,	Null,	2

      Now look at this query:

      select test1.*, test2.*, test3.*, foo(coalesce(test1.id)),  foo(test1.id)
      from test1
      right join test2 ON test1.id = test2.id
      right join test3 ON test3.id = foo(coalesce(test1.id));

      What do I expect (without 2 last columns):

      1,	1,	1
      Null,	7,	1
      Null,	8,	1
      9,	9,	1
      Null,	Null,	2

      I think that "test1.id" and "coalesce(test1.id)" are similar, yes? This means that both of queries must return a similar resultset. But the second query returns exactly what I expect.

      I think the reason is in query optimizer. It converts outer join to inner join because of using "test1.id" in ON clause. In this case it is not a correct behavior. It does not assume that the function used in this expression may work with nulls well.
      But coalesce (and case expression also) hints to optimizer dont do this conversion.

      Attachments

        Activity

          People

            sanja Oleksandr Byelkin
            kalinin.k.a Konstantin
            Votes:
            0 Vote for this issue
            Watchers:
            5 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.