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

    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

            • Assignee:
              igor Igor Babaev
              Reporter:
              igor Igor Babaev
            • Votes:
              1 Vote for this issue
              Watchers:
              2 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved: