[Commits] 5e61e17: MDEV-14515 ifnull result depends on number of rows in joined table

IgorBabaev igor at mariadb.com
Tue Apr 17 02:59:19 EEST 2018


revision-id: 5e61e1716e763315009318081fba5994b8910242 (mariadb-5.5.59-58-g5e61e17)
parent(s): 88ac368fea2182447284d6bacff4d93ef1acb865
author: Igor Babaev
committer: Igor Babaev
timestamp: 2018-04-16 16:59:19 -0700
message:

MDEV-14515 ifnull result depends on number of rows in joined table

Any expensive WHERE condition for a table-less query with
implicit aggregation was lost. As a result the used aggregate
functions were calculated over a non-empty set of rows even
in the case when the condition was false.

---
 mysql-test/r/subselect4.result | 24 ++++++++++++++++++++++--
 mysql-test/t/subselect4.test   | 23 +++++++++++++++++++++++
 sql/opt_subselect.cc           |  1 +
 3 files changed, 46 insertions(+), 2 deletions(-)

diff --git a/mysql-test/r/subselect4.result b/mysql-test/r/subselect4.result
index c20c048..6accc23 100644
--- a/mysql-test/r/subselect4.result
+++ b/mysql-test/r/subselect4.result
@@ -1056,7 +1056,7 @@ EXPLAIN
 SELECT * FROM t1 WHERE (2, 0) NOT IN (SELECT min(f3)+f3, min(f4)+f3+max(f4) FROM t2 WHERE f3 > 10);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE
-2	SUBQUERY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No matching min/max row
+2	SUBQUERY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE noticed after reading const tables
 SELECT * FROM t1 WHERE (2, 0) NOT IN (SELECT min(f3)+f3, min(f4)+f3+max(f4) FROM t2 WHERE f3 > 10);
 f1	f2
 SET @@optimizer_switch = 'materialization=off,in_to_exists=on,semijoin=off';
@@ -1147,7 +1147,7 @@ EXPLAIN
 SELECT * FROM t1 WHERE (2, 0) NOT IN (SELECT min(f3)+f3, min(f4)+f3+max(f4) FROM t2 WHERE f3 > 10);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE
-2	SUBQUERY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No matching min/max row
+2	SUBQUERY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE noticed after reading const tables
 SELECT * FROM t1 WHERE (2, 0) NOT IN (SELECT min(f3)+f3, min(f4)+f3+max(f4) FROM t2 WHERE f3 > 10);
 f1	f2
 set @@optimizer_switch=@save_optimizer_switch;
@@ -2511,3 +2511,23 @@ SELECT 2 IN (SELECT 2 from DUAL WHERE 1 != 1);
 0
 SET optimizer_switch= @@global.optimizer_switch;
 set @@tmp_table_size= @@global.tmp_table_size;
+#
+# mfrv-14515: Wrong results for tableless query with subquery in WHERE
+#             and implicit aggregation
+#
+create table t1 (i1 int, i2 int);
+insert into t1 values (1314, 1084),(1330, 1084),(1401, 1084),(580, 1084);
+create table t2 (cd int);
+insert into t2 values
+(1330), (1330), (1330), (1330), (1330), (1330), (1330), (1330),
+(1330), (1330), (1330), (1330), (1330), (1330), (1330), (1330);
+select max(10) from dual
+where exists (select 1 from t2 join t1 on t1.i1 = t2.cd  and t1.i2 = 345);
+max(10)
+NULL
+insert into t2 select * from t2;
+select max(10) from dual
+where exists (select 1 from t2 join t1 on t1.i1 = t2.cd  and t1.i2 = 345);
+max(10)
+NULL
+DROP TABLE t1,t2;
diff --git a/mysql-test/t/subselect4.test b/mysql-test/t/subselect4.test
index 673dc9b..2b53b55 100644
--- a/mysql-test/t/subselect4.test
+++ b/mysql-test/t/subselect4.test
@@ -2045,3 +2045,26 @@ SELECT 2 IN (SELECT 2 from DUAL WHERE 1 != 1);
 
 SET optimizer_switch= @@global.optimizer_switch;
 set @@tmp_table_size= @@global.tmp_table_size;
+
+--echo #
+--echo # mfrv-14515: Wrong results for tableless query with subquery in WHERE
+--echo #             and implicit aggregation
+--echo #
+
+create table t1 (i1 int, i2 int);
+insert into t1 values (1314, 1084),(1330, 1084),(1401, 1084),(580, 1084);
+
+create table t2 (cd int);
+insert into t2 values
+  (1330), (1330), (1330), (1330), (1330), (1330), (1330), (1330),
+  (1330), (1330), (1330), (1330), (1330), (1330), (1330), (1330);
+
+select max(10) from dual
+  where exists (select 1 from t2 join t1 on t1.i1 = t2.cd  and t1.i2 = 345);
+
+insert into t2 select * from t2;
+
+select max(10) from dual
+  where exists (select 1 from t2 join t1 on t1.i1 = t2.cd  and t1.i2 = 345);
+
+DROP TABLE t1,t2;
diff --git a/sql/opt_subselect.cc b/sql/opt_subselect.cc
index c21541c..1bda84b 100644
--- a/sql/opt_subselect.cc
+++ b/sql/opt_subselect.cc
@@ -5903,5 +5903,6 @@ bool JOIN::choose_tableless_subquery_plan()
       tmp_having= having;
     }
   }
+  exec_const_cond= conds;
   return FALSE;
 }


More information about the commits mailing list