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

Wrong result for query with outer join and IS NOT TRUE predicate in where clause

    XMLWordPrintable

Details

    Description

      The problem manifests itself when running queries with IS NOT TRUE or IS NOT FALSE predicates in their where clauses.
      The following simple test case can demonstrate the problem in 5.5 and all upper versions

      create table t1 (a int);
      create table t2 (b int);
      insert into t1 values (3), (7), (1);
      insert into t2 values (7), (4), (3);
      select * from t1 left join t2 on a=b;
      select * from t1 left join t2 on a=b where (b > 3) is not true;
      select * from t1 left join t2 on a=b where (b > 3) is not false;
      

      MariaDB [test]> select * from t1 left join t2 on a=b;
      +------+------+
      | a    | b    |
      +------+------+
      |    7 |    7 |
      |    3 |    3 |
      |    1 | NULL |
      +------+------+
      3 rows in set (0.00 sec)
       
      MariaDB [test]> select * from t1 left join t2 on a=b where (b > 3) is not true;
      +------+------+
      | a    | b    |
      +------+------+
      |    3 |    3 |
      +------+------+
      1 row in set (0.00 sec)
      MariaDB [test]> select * from t1 left join t2 on a=b where (b > 3) is not false;
      +------+------+
      | a    | b    |
      +------+------+
      |    7 |    7 |
      +------+------+
      1 row in set (0.00 sec)
      

      MySQL 8.0 (and most probably all prior versions) returns the same result sets for the above queries.
      Postgres returns correct results:

      postgres=# select * from t1 left join t2 on a=b;
       a | b 
      ---+---
       1 |  
       3 | 3
       7 | 7
      (3 rows)
       
      postgres=# select * from t1 left join t2 on a=b where (b > 1) is not true;
       a | b 
      ---+---
       1 |  
      (1 row)
      

      Attachments

        Activity

          People

            igor Igor Babaev
            igor Igor Babaev
            Votes:
            1 Vote for this issue
            Watchers:
            2 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.