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

Assertion failure when pushing from HAVING into WHERE of view

Details

    Description

      I used my fuzzing tool to test Mariadb , and found a bug that can result in an abortion.

      Mariadb installation:
      1) cd mariadb-10.5.9
      2) mkdir build; cd build
      3) cmake -DWITH_ASAN=ON -DWITH_ASAN_SCOPE=ON -DWITH_DEBUG=ON ../
      4) make -j8 && sudo make install

      How to Repeat:
      export ASAN_OPTIONS=detect_leaks=0
      /usr/local/mysql/bin/mysqld_safe &
      /usr/local/mysql/bin/mysql -uroot -p123456(your password)
      MariaDB> drop database if exists test_db;
      MariaDB> create database test_db;
      MariaDB> source fuzz.sql;

      I have simplified the content of fuzz.sql, and I hope fuzz.sql can help you reproduce the bug and fix it. In addition, I attach the abortion report (which has its stack trace).

      Attachments

        Issue Links

          Activity

            This is a legacy bug and it's present in 10.2 as well.
            Let's try to construct a test case for the bug in 10.2 that would catch the bug in 10.2 where there is no pushdown from HAVING to WHERE. However in 10.2 we have pushdown of conditions into HAVING of materialized tables / views that also builds items of the form Item_direct_view_ref(Item_ref(Item_sum_func(Item_basic_constant))) if the pushdown happens to be from WHERE of mergeable derived tables / views.
            Here is a test case that uses such pushdown:

            create table t1 (a int);
            insert into t1 values (3), (7), (1), (3), (7), (7), (3);
            create view v1 as select a, sum(1) as f, sum(1) as g from t1 group by a;
            select * from (select * from v1) as dt where a=f and a=g;
            

            The result set from the query is:

            MariaDB [test]> select * from (select * from v1) as dt where a=f and a=g;
            +------+------+------+
            | a    | f    | g    |
            +------+------+------+
            |    3 |    3 |    3 |
            +------+------+------+
            1 row in set 
            

            though select from v1 returns

            MariaDB [test]> select * from v1;
            +------+------+------+
            | a    | f    | g    |
            +------+------+------+
            |    1 |    1 |    1 |
            |    3 |    3 |    3 |
            |    7 |    3 |    3 |
            +------+------+------+
            3 rows in set 
            

            If we manually push the condition a=f and a=g into v1 we have a proper result set:

            MariaDB [test]> select * from (select a, sum(1) as f, sum(1) as g from t1 group by a having a=f and a=g) as dt;
            +------+------+------+
            | a    | f    | g    |
            +------+------+------+
            |    1 |    1 |    1 |
            |    3 |    3 |    3 |
            +------+------+------+
            2 rows in set
            

            igor Igor Babaev (Inactive) added a comment - This is a legacy bug and it's present in 10.2 as well. Let's try to construct a test case for the bug in 10.2 that would catch the bug in 10.2 where there is no pushdown from HAVING to WHERE. However in 10.2 we have pushdown of conditions into HAVING of materialized tables / views that also builds items of the form Item_direct_view_ref(Item_ref(Item_sum_func(Item_basic_constant))) if the pushdown happens to be from WHERE of mergeable derived tables / views. Here is a test case that uses such pushdown: create table t1 (a int ); insert into t1 values (3), (7), (1), (3), (7), (7), (3); create view v1 as select a, sum (1) as f, sum (1) as g from t1 group by a; select * from ( select * from v1) as dt where a=f and a=g; The result set from the query is: MariaDB [test]> select * from (select * from v1) as dt where a=f and a=g; +------+------+------+ | a | f | g | +------+------+------+ | 3 | 3 | 3 | +------+------+------+ 1 row in set though select from v1 returns MariaDB [test]> select * from v1; +------+------+------+ | a | f | g | +------+------+------+ | 1 | 1 | 1 | | 3 | 3 | 3 | | 7 | 3 | 3 | +------+------+------+ 3 rows in set If we manually push the condition a=f and a=g into v1 we have a proper result set: MariaDB [test]> select * from (select a, sum(1) as f, sum(1) as g from t1 group by a having a=f and a=g) as dt; +------+------+------+ | a | f | g | +------+------+------+ | 1 | 1 | 1 | | 3 | 3 | 3 | +------+------+------+ 2 rows in set
            igor Igor Babaev (Inactive) added a comment - - edited

            It's interesting that for a similar test case that uses MIN(1) instead of SUM(1) in the view

            create table t1 (a int);
            insert into t1 values (3), (7), (1), (3), (7), (7), (3);
            create view v2 as select a, min(1) as f, min(1) as g from t1 group by a;
            select * from (select * from v2) as dt where a=f and a=g;
            

            not only the query returns an unexpected empty result

            MariaDB [test]> select * from (select * from v2) as dt where a=f and a=g;        
            Empty set 
            

            but EXPLAIN also returns an unexpected output

            MariaDB [test]> explain extended select * from (select * from v2) as dt where a=f and a=g;
            +------+-------------+-------+------+---------------+------+---------+------+------+----------+-----------------------------------------------------+
            | id   | select_type | table | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra                                               |
            +------+-------------+-------+------+---------------+------+---------+------+------+----------+-----------------------------------------------------+
            |    1 | PRIMARY     | NULL  | NULL | NULL          | NULL | NULL    | NULL | NULL |     NULL | Impossible WHERE noticed after reading const tables |
            |    3 | DERIVED     | NULL  | NULL | NULL          | NULL | NULL    | NULL | NULL |     NULL | Impossible HAVING                                   |
            +------+-------------+-------+------+---------------+------+---------+------+------+----------+-----------------------------------------------------+
            2 rows in set, 1 warning
             
            MariaDB [test]> show warnings;
            +-------+------+---------------------------------------------------------------------+
            | Level | Code | Message                                                             |
            +-------+------+---------------------------------------------------------------------+
            | Note  | 1003 | select NULL AS `a`,NULL AS `f`,NULL AS `g` from `test`.`v2` where 0 |
            +-------+------+---------------------------------------------------------------------+
            1 row in set 
            

            igor Igor Babaev (Inactive) added a comment - - edited It's interesting that for a similar test case that uses MIN(1) instead of SUM(1) in the view create table t1 (a int ); insert into t1 values (3), (7), (1), (3), (7), (7), (3); create view v2 as select a, min (1) as f, min (1) as g from t1 group by a; select * from ( select * from v2) as dt where a=f and a=g; not only the query returns an unexpected empty result MariaDB [test]> select * from (select * from v2) as dt where a=f and a=g; Empty set but EXPLAIN also returns an unexpected output MariaDB [test]> explain extended select * from (select * from v2) as dt where a=f and a=g; +------+-------------+-------+------+---------------+------+---------+------+------+----------+-----------------------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +------+-------------+-------+------+---------------+------+---------+------+------+----------+-----------------------------------------------------+ | 1 | PRIMARY | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | Impossible WHERE noticed after reading const tables | | 3 | DERIVED | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | Impossible HAVING | +------+-------------+-------+------+---------------+------+---------+------+------+----------+-----------------------------------------------------+ 2 rows in set, 1 warning   MariaDB [test]> show warnings; +-------+------+---------------------------------------------------------------------+ | Level | Code | Message | +-------+------+---------------------------------------------------------------------+ | Note | 1003 | select NULL AS `a`,NULL AS `f`,NULL AS `g` from `test`.`v2` where 0 | +-------+------+---------------------------------------------------------------------+ 1 row in set

            Ok to push

            psergei Sergei Petrunia added a comment - Ok to push

            This is a legacy bug, so I removed the marker "regression".

            igor Igor Babaev (Inactive) added a comment - This is a legacy bug, so I removed the marker "regression".

            A patch for this bug was pushed into 10.2. It has to be merged upstream as it is.
            An additional test case for 10.4 will be added after the patch has been merged into 10.4.

            igor Igor Babaev (Inactive) added a comment - A patch for this bug was pushed into 10.2. It has to be merged upstream as it is. An additional test case for 10.4 will be added after the patch has been merged into 10.4.

            People

              igor Igor Babaev (Inactive)
              Zuming Jiang Zuming Jiang
              Votes:
              0 Vote for this issue
              Watchers:
              6 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.