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;