[Commits] e1cca292936: postreview changes

Oleksandr Byelkin sanja at mariadb.com
Tue Apr 16 10:25:04 EEST 2019


revision-id: e1cca2929362e0197f4f0e12ef7e6c8e5979ba73 (mariadb-10.4.4-18-ge1cca292936)
parent(s): 17cba93aa83b620fb1b771ea287bcd5b173d2776
author: Oleksandr Byelkin
committer: Oleksandr Byelkin
timestamp: 2019-04-16 09:25:04 +0200
message:

postreview changes

---
 mysql-test/main/subselect_innodb.result | 37 ++++++++++++++++++++++-----------
 mysql-test/main/subselect_innodb.test   | 34 ++++++++++++++++++------------
 2 files changed, 46 insertions(+), 25 deletions(-)

diff --git a/mysql-test/main/subselect_innodb.result b/mysql-test/main/subselect_innodb.result
index 518158e3a04..8e09be9b705 100644
--- a/mysql-test/main/subselect_innodb.result
+++ b/mysql-test/main/subselect_innodb.result
@@ -621,18 +621,31 @@ DROP TABLE t1,t2,t3,t4;
 # failed in  Item_equal::fix_fields, server crashes after 2nd execution
 # of PS
 # 
-CREATE TABLE t1 (pk int primary key, i1 int, i2 int, v1 varchar(1), v2 varchar(1), KEY i1 (i1), KEY v2 (v2,i1)) ENGINE=InnoDB ;
-INSERT INTO t1 VALUES (12,1,1,'r','r');
-CREATE TABLE t2 (pk int, i1 int, i2 int, v1 varchar(1), v2 varchar(1)) ENGINE=InnoDB ;
-CREATE TABLE t3 (pk int, i1 int, i2 int, v1 varchar(1), v2 varchar(1)) ENGINE=InnoDB ;
-INSERT INTO t3 VALUES (19,1,9,NULL,NULL),(20,5,6,'r','r');
+create table t1 (a int, b int);
+create table t2 (x int, y int);
+insert into t1 values (1,1),(2,2);
+insert into t2 values (1,1),(2,2),(2,3);
+# here we can see conditions pushdown (see HAVING):
 prepare stmt from "
-SELECT 1 FROM t1
-WHERE t1.pk = t1.i1 
-	AND ((t1.pk,t1.i1) IN (SELECT t3.pk, COUNT(t3.pk) FROM t3
-          WHERE EXISTS (SELECT 1 FROM (t1 JOIN t3 ON (t3.i2 = t1.i1)) WHERE t3.v1 = t1.v1)));";
+explain extended
+SELECT * FROM t1
+WHERE a = b
+      AND (a,b) IN (SELECT t2.x, COUNT(t2.y) FROM t2 WHERE 1=2 GROUP BY t2.x);";
 execute stmt;
-1
+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
+2	MATERIALIZED	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE
+Warnings:
+Note	1003	/* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from  <materialize> (/* select#2 */ select `test`.`t2`.`x`,count(`test`.`t2`.`y`) from `test`.`t2` where 0 group by `test`.`t2`.`x` having `COUNT(t2.y)` = `test`.`t2`.`x`) join `test`.`t1` where 0
+# here re-execution of the pushdown does not crash:
+prepare stmt from "
+SELECT * FROM t1
+WHERE a = b
+      AND (a,b) IN (SELECT t2.x, COUNT(t2.y) FROM t2 WHERE 1=2 GROUP BY t2.x);";
 execute stmt;
-1
-drop table t1,t2,t3;
+a	b
+execute stmt;
+a	b
+execute stmt;
+a	b
+drop table t1,t2;
diff --git a/mysql-test/main/subselect_innodb.test b/mysql-test/main/subselect_innodb.test
index 214d692e793..b8d12d04a5e 100644
--- a/mysql-test/main/subselect_innodb.test
+++ b/mysql-test/main/subselect_innodb.test
@@ -617,21 +617,29 @@ DROP TABLE t1,t2,t3,t4;
 --echo # of PS
 --echo # 
 
-CREATE TABLE t1 (pk int primary key, i1 int, i2 int, v1 varchar(1), v2 varchar(1), KEY i1 (i1), KEY v2 (v2,i1)) ENGINE=InnoDB ;
-INSERT INTO t1 VALUES (12,1,1,'r','r');
- 
-CREATE TABLE t2 (pk int, i1 int, i2 int, v1 varchar(1), v2 varchar(1)) ENGINE=InnoDB ;
- 
-CREATE TABLE t3 (pk int, i1 int, i2 int, v1 varchar(1), v2 varchar(1)) ENGINE=InnoDB ;
-INSERT INTO t3 VALUES (19,1,9,NULL,NULL),(20,5,6,'r','r');
+create table t1 (a int, b int);
+create table t2 (x int, y int);
  
+insert into t1 values (1,1),(2,2);
+insert into t2 values (1,1),(2,2),(2,3);
+
+--echo # here we can see conditions pushdown (see HAVING):
 prepare stmt from "
-SELECT 1 FROM t1
-WHERE t1.pk = t1.i1 
-	AND ((t1.pk,t1.i1) IN (SELECT t3.pk, COUNT(t3.pk) FROM t3
-          WHERE EXISTS (SELECT 1 FROM (t1 JOIN t3 ON (t3.i2 = t1.i1)) WHERE t3.v1 = t1.v1)));";
- 
+explain extended
+SELECT * FROM t1
+WHERE a = b
+      AND (a,b) IN (SELECT t2.x, COUNT(t2.y) FROM t2 WHERE 1=2 GROUP BY t2.x);";
+
+execute stmt;
+
+--echo # here re-execution of the pushdown does not crash:
+prepare stmt from "
+SELECT * FROM t1
+WHERE a = b
+      AND (a,b) IN (SELECT t2.x, COUNT(t2.y) FROM t2 WHERE 1=2 GROUP BY t2.x);";
+
+execute stmt;
 execute stmt;
 execute stmt;
 
-drop table t1,t2,t3;
+drop table t1,t2;


More information about the commits mailing list