Uploaded image for project: 'MariaDB ColumnStore'
  1. MariaDB ColumnStore
  2. MCOL-4715

Mixed inner and outer joins with "null filter" for the table which is not involved into the outer join produces wrong results.

Details

    • New Feature
    • Status: Closed (View Workflow)
    • Blocker
    • Resolution: Fixed
    • None
    • 22.08.1
    • None
    • None

    Description

      create table t1 (a int)engine=columnstore;
      create table t2 (a int)engine=columnstore;
      create table t3 (a int)engine=columnstore;
      insert into t1 values (1), (2), (3);
      insert into t2 values (2), (3);
      insert into t3 values (3);
      

      The following query produces expected result:

      MariaDB [test]> select * from t1 left join t2 left join t3 on t2.a=t3.a on t1.a=t3.a;
      +------+------+------+
      | a    | a    | a    |
      +------+------+------+
      |    1 | NULL | NULL |
      |    2 | NULL | NULL |
      |    3 |    3 |    3 |
      +------+------+------+
      

      However, if we apply an 'is null' WHERE predicate to the above query, we get incorrect result:

      MariaDB [test]> select * from t1 left join t2 left join t3 on t2.a=t3.a on t1.a=t3.a where t2.a is null;
      +------+------+------+
      | a    | a    | a    |
      +------+------+------+
      |    1 | NULL | NULL |
      |    2 | NULL | NULL |
      |    3 | NULL | NULL |
      +------+------+------+
      

      The expected result is the below (here, i1, i2, i3 are equivalent InnoDB tables):

      MariaDB [test]> select * from i1 left join i2 left join i3 on i2.a=i3.a on i1.a=i3.a where i2.a is null;
      +------+------+------+
      | a    | a    | a    |
      +------+------+------+
      |    1 | NULL | NULL |
      |    2 | NULL | NULL |
      +------+------+------+
      

      Attachments

        Activity

          Here are 2 additional cases that produce incorrect results:

          create table t1 (a int)engine=columnstore;
          create table t2 (a int)engine=columnstore;
          create table t3 (a int)engine=columnstore;
          create table t4 (a int)engine=columnstore;
          insert into t1 values (1), (2), (3), (4);
          insert into t2 values (2), (3), (4);
          insert into t3 values (3), (4);
          insert into t4 values (4);
          

          Bad result:

          MariaDB [test]> select * from t1 left join t2 on t1.a=t2.a left join t3 left join t4 on t3.a=t4.a on t1.a=t4.a where t3.a is null;
          +------+------+------+------+
          | a    | a    | a    | a    |
          +------+------+------+------+
          |    1 | NULL | NULL | NULL |
          |    2 |    2 | NULL | NULL |
          |    3 |    3 | NULL | NULL |
          |    4 |    4 | NULL | NULL |
          +------+------+------+------+
          

          Expected result:

          MariaDB [inno]> select * from t1 left join t2 on t1.a=t2.a left join t3 left join t4 on t3.a=t4.a on t1.a=t4.a where t3.a is null;
          +------+------+------+------+
          | a    | a    | a    | a    |
          +------+------+------+------+
          |    2 |    2 | NULL | NULL |
          |    3 |    3 | NULL | NULL |
          |    1 | NULL | NULL | NULL |
          +------+------+------+------+
          

          Bad result:

          MariaDB [test]> select * from t1 left join t2 on t1.a=t2.a left join t3 left join t4 on t3.a=t4.a on t2.a=t4.a where t3.a is null;
          +------+------+------+------+
          | a    | a    | a    | a    |
          +------+------+------+------+
          |    1 | NULL | NULL | NULL |
          |    2 |    2 | NULL | NULL |
          |    3 |    3 | NULL | NULL |
          |    4 |    4 | NULL | NULL |
          +------+------+------+------+
          

          Expected result:

          MariaDB [inno]> select * from t1 left join t2 on t1.a=t2.a left join t3 left join t4 on t3.a=t4.a on t2.a=t4.a where t3.a is null;
          +------+------+------+------+
          | a    | a    | a    | a    |
          +------+------+------+------+
          |    2 |    2 | NULL | NULL |
          |    3 |    3 | NULL | NULL |
          |    1 | NULL | NULL | NULL |
          +------+------+------+------+
          

          tntnatbry Gagan Goel (Inactive) added a comment - Here are 2 additional cases that produce incorrect results: create table t1 (a int )engine=columnstore; create table t2 (a int )engine=columnstore; create table t3 (a int )engine=columnstore; create table t4 (a int )engine=columnstore; insert into t1 values (1), (2), (3), (4); insert into t2 values (2), (3), (4); insert into t3 values (3), (4); insert into t4 values (4); Bad result: MariaDB [test]> select * from t1 left join t2 on t1.a=t2.a left join t3 left join t4 on t3.a=t4.a on t1.a=t4.a where t3.a is null ; + ------+------+------+------+ | a | a | a | a | + ------+------+------+------+ | 1 | NULL | NULL | NULL | | 2 | 2 | NULL | NULL | | 3 | 3 | NULL | NULL | | 4 | 4 | NULL | NULL | + ------+------+------+------+ Expected result: MariaDB [inno]> select * from t1 left join t2 on t1.a=t2.a left join t3 left join t4 on t3.a=t4.a on t1.a=t4.a where t3.a is null ; + ------+------+------+------+ | a | a | a | a | + ------+------+------+------+ | 2 | 2 | NULL | NULL | | 3 | 3 | NULL | NULL | | 1 | NULL | NULL | NULL | + ------+------+------+------+ Bad result: MariaDB [test]> select * from t1 left join t2 on t1.a=t2.a left join t3 left join t4 on t3.a=t4.a on t2.a=t4.a where t3.a is null ; + ------+------+------+------+ | a | a | a | a | + ------+------+------+------+ | 1 | NULL | NULL | NULL | | 2 | 2 | NULL | NULL | | 3 | 3 | NULL | NULL | | 4 | 4 | NULL | NULL | + ------+------+------+------+ Expected result: MariaDB [inno]> select * from t1 left join t2 on t1.a=t2.a left join t3 left join t4 on t3.a=t4.a on t2.a=t4.a where t3.a is null ; + ------+------+------+------+ | a | a | a | a | + ------+------+------+------+ | 2 | 2 | NULL | NULL | | 3 | 3 | NULL | NULL | | 1 | NULL | NULL | NULL | + ------+------+------+------+
          alexey.vorovich alexey vorovich (Inactive) added a comment - - edited

          toddstoffel and tntnatbry
          it is true that results are strange and Denis is looking at this . However , the query has a very strange JOIN order

          If we correct it to a rational form we are good

          // Some comments here
           
           
          MariaDB [d1]> select * from t1 left join t2 on t1.a=t2.a left join t3 on t2.a=t3.a where t2.a is null;
          +------+------+------+
          | a    | a    | a    |
          +------+------+------+
          |    1 | NULL | NULL |
          +------+------+------+
          1 row in set (0.023 sec)
           
          MariaDB [d1]> select * from t1 left join t2 on t1.a=t2.a left join t3 on t2.a=t3.a 
              -> ;
          +------+------+------+
          | a    | a    | a    |
          +------+------+------+
          |    1 | NULL | NULL |
          |    2 |    2 | NULL |
          |    3 |    3 |    3 |
          +------+------+------+
          3 rows in set (0.028 sec)
          
          

          I wonder if this is urgent ?? ??

          alexey.vorovich alexey vorovich (Inactive) added a comment - - edited toddstoffel and tntnatbry it is true that results are strange and Denis is looking at this . However , the query has a very strange JOIN order If we correct it to a rational form we are good // Some comments here     MariaDB [d1]> select * from t1 left join t2 on t1.a=t2.a left join t3 on t2.a=t3.a where t2.a is null ; +------+------+------+ | a | a | a | +------+------+------+ | 1 | NULL | NULL | +------+------+------+ 1 row in set ( 0.023 sec)   MariaDB [d1]> select * from t1 left join t2 on t1.a=t2.a left join t3 on t2.a=t3.a -> ; +------+------+------+ | a | a | a | +------+------+------+ | 1 | NULL | NULL | | 2 | 2 | NULL | | 3 | 3 | 3 | +------+------+------+ 3 rows in set ( 0.028 sec) I wonder if this is urgent ?? ??

          alexey.vorovich In the original query:

          select * from t1 left join t2 left join t3 on t2.a=t3.a on t1.a=t3.a;
          

          Here, t2 left join t3 on t2.a=t3.a join is performed first. The results of this join are then used to perform the left join with t1 using the on clause t1.a=t3.a. So this is not a cartesian join.

          Vs in your query:

          select * from t1 left join t2 on t1.a=t2.a left join t3 on t2.a=t3.a;
          

          Here, t1 left join t2 on t1.a=t2.a is performed first. The results of this join are then used to perform the left join with t3 on t2.a=t3.a.

          These are just two different queries with different join orders, they should both individually give correct results.

          tntnatbry Gagan Goel (Inactive) added a comment - alexey.vorovich In the original query: select * from t1 left join t2 left join t3 on t2.a=t3.a on t1.a=t3.a; Here, t2 left join t3 on t2.a=t3.a join is performed first. The results of this join are then used to perform the left join with t1 using the on clause t1.a=t3.a . So this is not a cartesian join. Vs in your query: select * from t1 left join t2 on t1.a=t2.a left join t3 on t2.a=t3.a; Here, t1 left join t2 on t1.a=t2.a is performed first. The results of this join are then used to perform the left join with t3 on t2.a=t3.a . These are just two different queries with different join orders, they should both individually give correct results.

          Build verified: 22.08 ($5531)

          Verified mentioned test case

          dleeyh Daniel Lee (Inactive) added a comment - Build verified: 22.08 ($5531) Verified mentioned test case

          People

            denis0x0D Denis Khalikov (Inactive)
            tntnatbry Gagan Goel (Inactive)
            Roman Roman
            Daniel Lee Daniel Lee (Inactive)
            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.