Igor Babaev (Inactive) added a comment - 2021-06-01 00:48 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 Babaev (Inactive) added a comment - 2021-06-01 01:48 - 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

Sergei Petrunia added a comment - 2021-06-02 12:18 Ok to push

Igor Babaev (Inactive) added a comment - 2021-06-02 21:49 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.