[Commits] 62d269e: MDEV-25635 Assertion failure when pushing from HAVING into WHERE of view

IgorBabaev igor at mariadb.com
Tue Jun 1 08:37:15 EEST 2021


revision-id: 62d269ea914de5b9225e0584cca52444e09c1c56 (mariadb-10.2.31-986-g62d269e)
parent(s): d06205ba3713da6c5875f124d5e431d3704aad1d
author: Igor Babaev
committer: Igor Babaev
timestamp: 2021-05-31 22:37:15 -0700
message:

MDEV-25635 Assertion failure when pushing from HAVING into WHERE of view

This bug could manifest itself after pushing a where condition over a
mergeable derived table / view / CTE DT into a grouping view / derived table / CTE V
whose item list contained set functions with constant arguments such as
MIN(2), SUM(1) etc. In such cases the field references used in the
condition pushed into the view V that correspond set functions are wrapped
into Item_direct_view_ref wrappers. Due to a wrong implementation of the
virtual method const_item() for the class Item_direct_view_ref the wrapped
set functions with constant arguments could be erroneously taken for
constant items. This could lead to a wrong result set returned by the main
select query in 10.2. In 10.4 where a possibility of pushing condition from
HAVING into WHERE had been added this could cause a crash.

---
 mysql-test/r/derived_cond_pushdown.result | 39 +++++++++++++++++++++++++++++++
 mysql-test/t/derived_cond_pushdown.test   | 25 ++++++++++++++++++++
 sql/item.h                                |  5 +++-
 3 files changed, 68 insertions(+), 1 deletion(-)

diff --git a/mysql-test/r/derived_cond_pushdown.result b/mysql-test/r/derived_cond_pushdown.result
index 25237aa..28532ae 100644
--- a/mysql-test/r/derived_cond_pushdown.result
+++ b/mysql-test/r/derived_cond_pushdown.result
@@ -10634,4 +10634,43 @@ m
 7
 drop view v1;
 drop table t1;
+#
+# MDEV-25635: pushdown into grouping view using aggregate functions
+#             with constant arguments via a mergeable derived table
+#
+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 v1;
+a	f	g
+1	1	1
+3	3	3
+7	3	3
+select * from (select * from v1) as dt where a=f and a=g;
+a	f	g
+1	1	1
+3	3	3
+explain extended select * from (select * from v1) as dt where a=f and a=g;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	PRIMARY	<derived3>	ALL	NULL	NULL	NULL	NULL	7	100.00	Using where
+3	DERIVED	t1	ALL	NULL	NULL	NULL	NULL	7	100.00	Using temporary; Using filesort
+Warnings:
+Note	1003	select `v1`.`a` AS `a`,`v1`.`f` AS `f`,`v1`.`g` AS `g` from `test`.`v1` where `v1`.`a` = `v1`.`f` and `v1`.`a` = `v1`.`g`
+create view v2 as select a, min(1) as f, min(1) as g from t1 group by a;
+select * from v2;
+a	f	g
+1	1	1
+3	1	1
+7	1	1
+select * from (select * from v2) as dt where a=f and a=g;
+a	f	g
+1	1	1
+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	<derived3>	ALL	NULL	NULL	NULL	NULL	7	100.00	Using where
+3	DERIVED	t1	ALL	NULL	NULL	NULL	NULL	7	100.00	Using temporary; Using filesort
+Warnings:
+Note	1003	select `v2`.`a` AS `a`,`v2`.`f` AS `f`,`v2`.`g` AS `g` from `test`.`v2` where `v2`.`f` = `v2`.`a` and `v2`.`g` = `v2`.`a`
+drop view v1,v2;
+drop table t1;
 # End of 10.2 tests
diff --git a/mysql-test/t/derived_cond_pushdown.test b/mysql-test/t/derived_cond_pushdown.test
index 31b4904..58f38ac 100644
--- a/mysql-test/t/derived_cond_pushdown.test
+++ b/mysql-test/t/derived_cond_pushdown.test
@@ -2212,4 +2212,29 @@ select * from v1 where m > 0;
 drop view v1;
 drop table t1;
 
+--echo #
+--echo # MDEV-25635: pushdown into grouping view using aggregate functions
+--echo #             with constant arguments via a mergeable derived table
+--echo #
+
+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 v1;
+let $q1=
+select * from (select * from v1) as dt where a=f and a=g;
+eval $q1;
+eval explain extended $q1;
+
+create view v2 as select a, min(1) as f, min(1) as g from t1 group by a;
+select * from v2;
+let $q2=
+select * from (select * from v2) as dt where a=f and a=g;
+eval $q2;
+eval explain extended $q2;
+
+drop view v1,v2;
+drop table t1;
+
 --echo # End of 10.2 tests
diff --git a/sql/item.h b/sql/item.h
index c94709c..76be66d 100644
--- a/sql/item.h
+++ b/sql/item.h
@@ -4952,7 +4952,10 @@ class Item_direct_view_ref :public Item_direct_ref
   table_map used_tables() const;
   void update_used_tables();
   table_map not_null_tables() const;
-  bool const_item() const { return used_tables() == 0; }
+  bool const_item() const
+  {
+    return (*ref)->const_item() && (null_ref_table == NO_NULL_TABLE);
+  }
   TABLE *get_null_ref_table() const { return null_ref_table; }
   bool walk(Item_processor processor, bool walk_subquery, void *arg)
   { 


More information about the commits mailing list