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

Wrong result: HAVING evaluates NULL = NULL as true, SELECT from view returns extra rows

    XMLWordPrintable

    Details

      Description

      Priority is set to minor due to the artificiality of the query

      create or replace table t (a int);
      insert into t values (1),(2);
      create or replace view v as select if(a = 1,'Y','N') AS b from t;
      select v2.b AS f from v v1 left join v v2 ON (false) having (f = f);
       
      # With ORDER BY, result set is empty as expected
      select v2.b AS f from v v1 left join v v2 ON (false) having (f = f) order by f;
       
      # Cleanup
      drop view v;
      drop table t;
      

      10.4 dc6bc85cd2

      MariaDB [test]> select v2.b AS f from v v1 left join v v2 ON (false) having (f = f);
      +------+
      | f    |
      +------+
      | NULL |
      | NULL |
      +------+
      2 rows in set (0.006 sec)
       
      MariaDB [test]> select v2.b AS f from v v1 left join v v2 ON (false) having (f = f) order by f;
      Empty set (0.002 sec)
      

      MariaDB [test]> explain extended select v2.b AS f from v v1 left join v v2 ON (false) having (f = f);
      +------+-------------+-------+------+---------------+------+---------+------+------+----------+-------------------------------------------------+
      | id   | select_type | table | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra                                           |
      +------+-------------+-------+------+---------------+------+---------+------+------+----------+-------------------------------------------------+
      |    1 | SIMPLE      | t     | ALL  | NULL          | NULL | NULL    | NULL | 2    |   100.00 |                                                 |
      |    1 | SIMPLE      | t     | ALL  | NULL          | NULL | NULL    | NULL | 2    |   100.00 | Using where; Using join buffer (flat, BNL join) |
      +------+-------------+-------+------+---------------+------+---------+------+------+----------+-------------------------------------------------+
      2 rows in set, 1 warning (0.001 sec)
       
      MariaDB [test]> show warnings;
      +-------+------+--------------------------------------------------------------------------------------------------------------------+
      | Level | Code | Message                                                                                                            |
      +-------+------+--------------------------------------------------------------------------------------------------------------------+
      | Note  | 1003 | select if(`test`.`t`.`a` = 1,'Y','N') AS `f` from `test`.`t` left join (`test`.`t`) on(0) where 1 having `b` = `b` |
      +-------+------+--------------------------------------------------------------------------------------------------------------------+
      1 row in set (0.000 sec)
      

      Reproducible on 10.2-10.7, with at least InnoDB and MyISAM.

        Attachments

          Activity

            People

            Assignee:
            psergei Sergei Petrunia
            Reporter:
            elenst Elena Stepanova
            Votes:
            0 Vote for this issue
            Watchers:
            2 Start watching this issue

              Dates

              Created:
              Updated:

                Git Integration