2650 Sergey Petrunia 2008-06-07
Make subselect[_no_XXX].test go faster - use bulk table fill
modified:
mysql-test/r/subselect.result
mysql-test/r/subselect_no_mat.result
mysql-test/r/subselect_no_opts.result
mysql-test/r/subselect_no_semijoin.result
mysql-test/t/subselect.test
=== modified file 'mysql-test/r/subselect.result'
--- a/mysql-test/r/subselect.result 2008-05-22 18:40:15 +0000
+++ b/mysql-test/r/subselect.result 2008-06-07 00:26:08 +0000
@@ -4287,15 +4287,20 @@ Note 1276 Field or reference 'test.t1.a'
Note 1276 Field or reference 'test.t1.a' of SELECT #3 was resolved in SELECT #1
Note 1003 select 2 AS `2` from `test`.`t1` where exists((select 1 AS `1` from `test`.`t2` where (`test`.`t1`.`a` = `test`.`t2`.`a`)) union (select 1 AS `1` from `test`.`t2` where (`test`.`t1`.`a` = `test`.`t2`.`a`)))
DROP TABLE t1,t2;
+create table t0(a int);
+insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
create table t1(f11 int, f12 int);
create table t2(f21 int unsigned not null, f22 int, f23 varchar(10));
insert into t1 values(1,1),(2,2), (3, 3);
+insert into t2
+select -1 , (@a:=(A.a + 10 * (B.a + 10 * (C.a+10*D.a))))/5000 + 1, @a
+from t0 A, t0 B, t0 C, t0 D;
set session sort_buffer_size= 33*1024;
select count(*) from t1 where f12 =
(select f22 from t2 where f22 = f12 order by f21 desc, f22, f23 limit 1);
count(*)
3
-drop table t1,t2;
+drop table t0,t1,t2;
CREATE TABLE t4 (
f7 varchar(32) collate utf8_bin NOT NULL default '',
f10 varchar(32) collate utf8_bin default NULL,
=== modified file 'mysql-test/r/subselect_no_mat.result'
--- a/mysql-test/r/subselect_no_mat.result 2008-05-22 18:40:15 +0000
+++ b/mysql-test/r/subselect_no_mat.result 2008-06-07 00:26:08 +0000
@@ -4291,15 +4291,20 @@ Note 1276 Field or reference 'test.t1.a'
Note 1276 Field or reference 'test.t1.a' of SELECT #3 was resolved in SELECT #1
Note 1003 select 2 AS `2` from `test`.`t1` where exists((select 1 AS `1` from `test`.`t2` where (`test`.`t1`.`a` = `test`.`t2`.`a`)) union (select 1 AS `1` from `test`.`t2` where (`test`.`t1`.`a` = `test`.`t2`.`a`)))
DROP TABLE t1,t2;
+create table t0(a int);
+insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
create table t1(f11 int, f12 int);
create table t2(f21 int unsigned not null, f22 int, f23 varchar(10));
insert into t1 values(1,1),(2,2), (3, 3);
+insert into t2
+select -1 , (@a:=(A.a + 10 * (B.a + 10 * (C.a+10*D.a))))/5000 + 1, @a
+from t0 A, t0 B, t0 C, t0 D;
set session sort_buffer_size= 33*1024;
select count(*) from t1 where f12 =
(select f22 from t2 where f22 = f12 order by f21 desc, f22, f23 limit 1);
count(*)
3
-drop table t1,t2;
+drop table t0,t1,t2;
CREATE TABLE t4 (
f7 varchar(32) collate utf8_bin NOT NULL default '',
f10 varchar(32) collate utf8_bin default NULL,
=== modified file 'mysql-test/r/subselect_no_opts.result'
--- a/mysql-test/r/subselect_no_opts.result 2008-05-22 18:40:15 +0000
+++ b/mysql-test/r/subselect_no_opts.result 2008-06-07 00:26:08 +0000
@@ -4291,15 +4291,20 @@ Note 1276 Field or reference 'test.t1.a'
Note 1276 Field or reference 'test.t1.a' of SELECT #3 was resolved in SELECT #1
Note 1003 select 2 AS `2` from `test`.`t1` where exists((select 1 AS `1` from `test`.`t2` where (`test`.`t1`.`a` = `test`.`t2`.`a`)) union (select 1 AS `1` from `test`.`t2` where (`test`.`t1`.`a` = `test`.`t2`.`a`)))
DROP TABLE t1,t2;
+create table t0(a int);
+insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
create table t1(f11 int, f12 int);
create table t2(f21 int unsigned not null, f22 int, f23 varchar(10));
insert into t1 values(1,1),(2,2), (3, 3);
+insert into t2
+select -1 , (@a:=(A.a + 10 * (B.a + 10 * (C.a+10*D.a))))/5000 + 1, @a
+from t0 A, t0 B, t0 C, t0 D;
set session sort_buffer_size= 33*1024;
select count(*) from t1 where f12 =
(select f22 from t2 where f22 = f12 order by f21 desc, f22, f23 limit 1);
count(*)
3
-drop table t1,t2;
+drop table t0,t1,t2;
CREATE TABLE t4 (
f7 varchar(32) collate utf8_bin NOT NULL default '',
f10 varchar(32) collate utf8_bin default NULL,
=== modified file 'mysql-test/r/subselect_no_semijoin.result'
--- a/mysql-test/r/subselect_no_semijoin.result 2008-05-22 18:40:15 +0000
+++ b/mysql-test/r/subselect_no_semijoin.result 2008-06-07 00:26:08 +0000
@@ -4291,15 +4291,20 @@ Note 1276 Field or reference 'test.t1.a'
Note 1276 Field or reference 'test.t1.a' of SELECT #3 was resolved in SELECT #1
Note 1003 select 2 AS `2` from `test`.`t1` where exists((select 1 AS `1` from `test`.`t2` where (`test`.`t1`.`a` = `test`.`t2`.`a`)) union (select 1 AS `1` from `test`.`t2` where (`test`.`t1`.`a` = `test`.`t2`.`a`)))
DROP TABLE t1,t2;
+create table t0(a int);
+insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
create table t1(f11 int, f12 int);
create table t2(f21 int unsigned not null, f22 int, f23 varchar(10));
insert into t1 values(1,1),(2,2), (3, 3);
+insert into t2
+select -1 , (@a:=(A.a + 10 * (B.a + 10 * (C.a+10*D.a))))/5000 + 1, @a
+from t0 A, t0 B, t0 C, t0 D;
set session sort_buffer_size= 33*1024;
select count(*) from t1 where f12 =
(select f22 from t2 where f22 = f12 order by f21 desc, f22, f23 limit 1);
count(*)
3
-drop table t1,t2;
+drop table t0,t1,t2;
CREATE TABLE t4 (
f7 varchar(32) collate utf8_bin NOT NULL default '',
f10 varchar(32) collate utf8_bin default NULL,
=== modified file 'mysql-test/t/subselect.test'
--- a/mysql-test/t/subselect.test 2008-05-22 18:40:15 +0000
+++ b/mysql-test/t/subselect.test 2008-06-07 00:26:08 +0000
@@ -3111,24 +3111,21 @@ DROP TABLE t1,t2;
# Bug#33675: Usage of an uninitialized memory by filesort in a subquery
# caused server crash.
#
+create table t0(a int);
+insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
create table t1(f11 int, f12 int);
create table t2(f21 int unsigned not null, f22 int, f23 varchar(10));
insert into t1 values(1,1),(2,2), (3, 3);
-let $i=10000;
---disable_query_log
--disable_warnings
-while ($i)
-{
- eval insert into t2 values (-1 , $i/5000 + 1, '$i');
- dec $i;
-}
+insert into t2
+select -1 , (@a:=(A.a + 10 * (B.a + 10 * (C.a+10*D.a))))/5000 + 1, @a
+from t0 A, t0 B, t0 C, t0 D;
--enable_warnings
---enable_query_log
set session sort_buffer_size= 33*1024;
select count(*) from t1 where f12 =
(select f22 from t2 where f22 = f12 order by f21 desc, f22, f23 limit 1);
-drop table t1,t2;
+drop table t0,t1,t2;
#
# BUG#33794 "MySQL crashes executing specific query on specific dump"
| Thread |
|---|
| • bzr push into mysql-6.0 branch (sergefp:2650) | Sergey Petrunia | 7 Jun |
