| List: | Commits | « Previous MessageNext Message » | |
| From: | Sergey Petrunia | Date: | July 19 2008 4:22pm |
| Subject: | bzr commit into mysql-6.0-opt branch (sergefp:2674) | ||
| View as plain text | |||
#At file:///home/psergey/dev/mysql-6.0-opt-r2/
2674 Sergey Petrunia 2008-07-19 [merge]
mysql-6.0 <-> mysql-6.0-opt merge
modified:
.bzr-mysql/default.conf
configure.in
mysql-test/r/constraints.result
mysql-test/r/sp-error.result
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/r/subselect_sj.result
mysql-test/r/subselect_sj2.result
mysql-test/t/constraints.test
mysql-test/t/sp-error.test
mysql-test/t/subselect.test
mysql-test/t/subselect_sj.test
mysql-test/t/subselect_sj2.test
sql/handler.cc
sql/handler.h
sql/mysql_priv.h
sql/mysqld.cc
sql/sp_pcontext.cc
sql/sql_select.cc
sql/sql_select.h
sql/sql_yacc.yy
sql/structs.h
=== modified file '.bzr-mysql/default.conf'
--- a/.bzr-mysql/default.conf 2008-07-15 20:02:29 +0000
+++ b/.bzr-mysql/default.conf 2008-07-19 14:21:51 +0000
@@ -1,5 +1,5 @@
-[MYSQL]
-tree_location = bzr+ssh://bk-internal.mysql.com/bzrroot/server/mysql-6.0-backup/
-post_commit_to = commits@lists.mysql.com
-post_push_to = commits@stripped
-tree_name = mysql-6.0-backup
+[MYSQL]
+tree_location = bzr+ssh://bk-internal.mysql.com/bzrroot/server/mysql-6.0-opt/
+post_commit_to = commits@stripped
+post_push_to = commits@stripped
+tree_name = "mysql-6.0-opt"
=== modified file 'configure.in'
--- a/configure.in 2008-07-17 10:35:38 +0000
+++ b/configure.in 2008-07-19 14:21:51 +0000
@@ -835,7 +835,7 @@ AC_TYPE_SIZE_T
AC_HEADER_DIRENT
AC_HEADER_STDC
AC_HEADER_SYS_WAIT
-AC_CHECK_HEADERS(fcntl.h fenv.h float.h floatingpoint.h fpu_control.h \
+AC_CHECK_HEADERS(fcntl.h fenv.h float.h floatingpoint.h fpu_control.h ieeefp.h \
limits.h memory.h pwd.h select.h \
stdlib.h stddef.h sys/fpu.h sys/stat.h \
strings.h string.h synch.h sys/mman.h sys/socket.h netinet/in.h arpa/inet.h \
=== modified file 'mysql-test/r/constraints.result'
--- a/mysql-test/r/constraints.result 2006-02-22 09:09:59 +0000
+++ b/mysql-test/r/constraints.result 2008-06-12 15:21:49 +0000
@@ -3,7 +3,7 @@ create table t1 (a int check (a>0));
insert into t1 values (1);
insert into t1 values (0);
drop table t1;
-create table t1 (a int ,b int, check a>b);
+create table t1 (a int, b int, check (a>b));
insert into t1 values (1,0);
insert into t1 values (0,1);
drop table t1;
@@ -27,3 +27,10 @@ t1 CREATE TABLE `t1` (
UNIQUE KEY `key_2` (`a`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1
drop table t1;
+drop table if exists t_illegal;
+create table t_illegal (a int, b int, check a>b);
+ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'a>b)' at line 1
+create table t_illegal (a int, b int, constraint abc check a>b);
+ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'a>b)' at line 1
+create table t_illegal (a int, b int, constraint abc);
+ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ')' at line 1
=== modified file 'mysql-test/r/sp-error.result'
--- a/mysql-test/r/sp-error.result 2008-07-03 19:42:27 +0000
+++ b/mysql-test/r/sp-error.result 2008-07-19 14:21:51 +0000
@@ -1624,6 +1624,29 @@ begin
declare continue handler for sqlstate '00000' set @x=0;
end$$
ERROR 42000: Bad SQLSTATE: '00000'
+drop procedure if exists proc_36510;
+create procedure proc_36510()
+begin
+declare should_be_illegal condition for sqlstate '00123';
+declare continue handler for should_be_illegal set @x=0;
+end$$
+ERROR 42000: Bad SQLSTATE: '00123'
+create procedure proc_36510()
+begin
+declare continue handler for sqlstate '00123' set @x=0;
+end$$
+ERROR 42000: Bad SQLSTATE: '00123'
+create procedure proc_36510()
+begin
+declare should_be_illegal condition for 0;
+declare continue handler for should_be_illegal set @x=0;
+end$$
+ERROR HY000: Incorrect CONDITION value: '0'
+create procedure proc_36510()
+begin
+declare continue handler for 0 set @x=0;
+end$$
+ERROR HY000: Incorrect CONDITION value: '0'
drop procedure if exists p1;
set @old_recursion_depth = @@max_sp_recursion_depth;
set @@max_sp_recursion_depth = 255;
=== modified file 'mysql-test/r/subselect.result'
--- a/mysql-test/r/subselect.result 2008-07-10 23:29:27 +0000
+++ b/mysql-test/r/subselect.result 2008-07-19 14:21:51 +0000
@@ -1323,6 +1323,10 @@ create table t1 (a int, b int, index a (
create table t2 (a int, index a (a));
create table t3 (a int, b int, index a (a));
insert into t1 values (1,10), (2,20), (3,30), (4,40);
+create table t0(a int);
+insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
+insert into t1
+select rand()*100000+200,rand()*100000 from t0 A, t0 B, t0 C, t0 D;
insert into t2 values (2), (3), (4), (5);
insert into t3 values (10,3), (20,4), (30,5);
select * from t2 where t2.a in (select a from t1);
@@ -1373,7 +1377,7 @@ id select_type table type possible_keys
1 PRIMARY t1 ref a a 5 test.t2.a 101 100.00 Using where; Using index; FirstMatch(t2)
Warnings:
Note 1003 select `test`.`t2`.`a` AS `a` from `test`.`t2` semi join (`test`.`t1`) where ((`test`.`t1`.`a` = `test`.`t2`.`a`) and (`test`.`t1`.`b` <> 30))
-drop table t1, t2, t3;
+drop table t0, t1, t2, t3;
create table t1 (a int, b int);
create table t2 (a int, b int);
create table t3 (a int, b int);
@@ -4287,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_mat.result'
--- a/mysql-test/r/subselect_no_mat.result 2008-07-10 23:29:27 +0000
+++ b/mysql-test/r/subselect_no_mat.result 2008-07-19 14:21:51 +0000
@@ -1327,6 +1327,10 @@ create table t1 (a int, b int, index a (
create table t2 (a int, index a (a));
create table t3 (a int, b int, index a (a));
insert into t1 values (1,10), (2,20), (3,30), (4,40);
+create table t0(a int);
+insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
+insert into t1
+select rand()*100000+200,rand()*100000 from t0 A, t0 B, t0 C, t0 D;
insert into t2 values (2), (3), (4), (5);
insert into t3 values (10,3), (20,4), (30,5);
select * from t2 where t2.a in (select a from t1);
@@ -1377,7 +1381,7 @@ id select_type table type possible_keys
1 PRIMARY t1 ref a a 5 test.t2.a 101 100.00 Using where; Using index; FirstMatch(t2)
Warnings:
Note 1003 select `test`.`t2`.`a` AS `a` from `test`.`t2` semi join (`test`.`t1`) where ((`test`.`t1`.`a` = `test`.`t2`.`a`) and (`test`.`t1`.`b` <> 30))
-drop table t1, t2, t3;
+drop table t0, t1, t2, t3;
create table t1 (a int, b int);
create table t2 (a int, b int);
create table t3 (a int, b int);
@@ -4291,15 +4295,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-07-10 23:29:27 +0000
+++ b/mysql-test/r/subselect_no_opts.result 2008-07-19 14:21:51 +0000
@@ -1327,6 +1327,10 @@ create table t1 (a int, b int, index a (
create table t2 (a int, index a (a));
create table t3 (a int, b int, index a (a));
insert into t1 values (1,10), (2,20), (3,30), (4,40);
+create table t0(a int);
+insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
+insert into t1
+select rand()*100000+200,rand()*100000 from t0 A, t0 B, t0 C, t0 D;
insert into t2 values (2), (3), (4), (5);
insert into t3 values (10,3), (20,4), (30,5);
select * from t2 where t2.a in (select a from t1);
@@ -1377,7 +1381,7 @@ id select_type table type possible_keys
2 DEPENDENT SUBQUERY t1 index_subquery a a 5 func 1001 100.00 Using index; Using where
Warnings:
Note 1003 select `test`.`t2`.`a` AS `a` from `test`.`t2` where <in_optimizer>(`test`.`t2`.`a`,<exists>(<index_lookup>(<cache>(`test`.`t2`.`a`) in t1 on a where ((`test`.`t1`.`b` <> 30) and (<cache>(`test`.`t2`.`a`) = `test`.`t1`.`a`)))))
-drop table t1, t2, t3;
+drop table t0, t1, t2, t3;
create table t1 (a int, b int);
create table t2 (a int, b int);
create table t3 (a int, b int);
@@ -4291,15 +4295,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-07-10 23:29:27 +0000
+++ b/mysql-test/r/subselect_no_semijoin.result 2008-07-19 14:21:51 +0000
@@ -1327,6 +1327,10 @@ create table t1 (a int, b int, index a (
create table t2 (a int, index a (a));
create table t3 (a int, b int, index a (a));
insert into t1 values (1,10), (2,20), (3,30), (4,40);
+create table t0(a int);
+insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
+insert into t1
+select rand()*100000+200,rand()*100000 from t0 A, t0 B, t0 C, t0 D;
insert into t2 values (2), (3), (4), (5);
insert into t3 values (10,3), (20,4), (30,5);
select * from t2 where t2.a in (select a from t1);
@@ -1377,7 +1381,7 @@ id select_type table type possible_keys
2 SUBQUERY t1 index NULL a 10 NULL 10005 100.00 Using where; Using index
Warnings:
Note 1003 select `test`.`t2`.`a` AS `a` from `test`.`t2` where <in_optimizer>(`test`.`t2`.`a`,`test`.`t2`.`a` in ( <materialize> (select `test`.`t1`.`a` AS `a` from `test`.`t1` where (`test`.`t1`.`b` <> 30) ), <primary_index_lookup>(`test`.`t2`.`a` in <temporary table> on distinct_key)))
-drop table t1, t2, t3;
+drop table t0, t1, t2, t3;
create table t1 (a int, b int);
create table t2 (a int, b int);
create table t3 (a int, b int);
@@ -4291,15 +4295,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_sj.result'
--- a/mysql-test/r/subselect_sj.result 2008-05-01 03:53:36 +0000
+++ b/mysql-test/r/subselect_sj.result 2008-06-11 23:16:53 +0000
@@ -197,5 +197,44 @@ id select_type table type possible_keys
1 PRIMARY t1 ALL NULL NULL NULL NULL 103 100.00 Using where; Using join buffer
Warnings:
Note 1003 select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t10` join `test`.`t1` where ((`test`.`t1`.`a` = `test`.`t10`.`pk`) and (`test`.`t10`.`pk` < 3))
+
+BUG#37120 optimizer_switch allowable values not according to specification
+
+select @@optimizer_switch;
+@@optimizer_switch
+
+set optimizer_switch='no_materialization';
+select @@optimizer_switch;
+@@optimizer_switch
+no_materialization
+set optimizer_switch='no_semijoin';
+select @@optimizer_switch;
+@@optimizer_switch
+no_semijoin
+set optimizer_switch='no_loosescan';
+select @@optimizer_switch;
+@@optimizer_switch
+no_loosescan
+set optimizer_switch='no_semijoin,no_materialization';
+select @@optimizer_switch;
+@@optimizer_switch
+no_materialization,no_semijoin
+set optimizer_switch='no_materialization,no_semijoin';
+select @@optimizer_switch;
+@@optimizer_switch
+no_materialization,no_semijoin
+set optimizer_switch='no_semijoin,no_materialization,no_loosescan';
+select @@optimizer_switch;
+@@optimizer_switch
+no_materialization,no_semijoin,no_loosescan
+set optimizer_switch='no_semijoin,no_loosescan';
+select @@optimizer_switch;
+@@optimizer_switch
+no_semijoin,no_loosescan
+set optimizer_switch='no_materialization,no_loosescan';
+select @@optimizer_switch;
+@@optimizer_switch
+no_materialization,no_loosescan
+set optimizer_switch='';
drop table t0, t1;
drop table t10, t11, t12;
=== modified file 'mysql-test/r/subselect_sj2.result'
--- a/mysql-test/r/subselect_sj2.result 2008-05-03 08:27:47 +0000
+++ b/mysql-test/r/subselect_sj2.result 2008-05-31 07:14:57 +0000
@@ -654,3 +654,30 @@ call p1();
a
drop procedure p1;
drop table t1;
+create table t0 (a int);
+insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
+create table t1 (a int) as select A.a + 10 *(B.a + 10*C.a) as a from t0 A, t0 B, t0 C;
+create table t2 (id int, a int, primary key(id), key(a)) as select a as id, a as a from t1;
+show create table t2;
+Table Create Table
+t2 CREATE TABLE `t2` (
+ `id` int(11) NOT NULL DEFAULT '0',
+ `a` int(11) DEFAULT NULL,
+ PRIMARY KEY (`id`),
+ KEY `a` (`a`)
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+set @a=0;
+create table t3 as select * from t2 limit 0;
+insert into t3 select @a:=@a+1, t2.a from t2, t0;
+insert into t3 select @a:=@a+1, t2.a from t2, t0;
+insert into t3 select @a:=@a+1, t2.a from t2, t0;
+alter table t3 add primary key(id), add key(a);
+The following must use loose index scan over t3, key a:
+explain select count(a) from t2 where a in ( SELECT a FROM t3);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY t3 index a a 5 NULL 30000 Using index; LooseScan
+1 PRIMARY t2 ref a a 5 test.t3.a 1 Using index
+select count(a) from t2 where a in ( SELECT a FROM t3);
+count(a)
+1000
+drop table t0,t1,t2,t3;
=== modified file 'mysql-test/t/constraints.test'
--- a/mysql-test/t/constraints.test 2005-07-28 00:22:47 +0000
+++ b/mysql-test/t/constraints.test 2008-06-12 15:21:49 +0000
@@ -10,7 +10,7 @@ create table t1 (a int check (a>0));
insert into t1 values (1);
insert into t1 values (0);
drop table t1;
-create table t1 (a int ,b int, check a>b);
+create table t1 (a int, b int, check (a>b));
insert into t1 values (1,0);
insert into t1 values (0,1);
drop table t1;
@@ -29,3 +29,21 @@ show create table t1;
drop table t1;
# End of 4.1 tests
+
+#
+# Bug#35578 (Parser allows useless/illegal CREATE TABLE syntax)
+#
+
+--disable_warnings
+drop table if exists t_illegal;
+--enable_warnings
+
+--error ER_PARSE_ERROR
+create table t_illegal (a int, b int, check a>b);
+
+--error ER_PARSE_ERROR
+create table t_illegal (a int, b int, constraint abc check a>b);
+
+--error ER_PARSE_ERROR
+create table t_illegal (a int, b int, constraint abc);
+
=== modified file 'mysql-test/t/sp-error.test'
--- a/mysql-test/t/sp-error.test 2008-07-03 19:42:27 +0000
+++ b/mysql-test/t/sp-error.test 2008-07-19 14:21:51 +0000
@@ -2392,6 +2392,42 @@ end$$
delimiter ;$$
+#
+# Bug#36510 (Stored Procedures: mysql_error_code 0 should be illegal)
+#
+
+--disable_warnings
+drop procedure if exists proc_36510;
+--enable_warnings
+
+delimiter $$;
+
+--error ER_SP_BAD_SQLSTATE
+create procedure proc_36510()
+begin
+ declare should_be_illegal condition for sqlstate '00123';
+ declare continue handler for should_be_illegal set @x=0;
+end$$
+
+--error ER_SP_BAD_SQLSTATE
+create procedure proc_36510()
+begin
+ declare continue handler for sqlstate '00123' set @x=0;
+end$$
+
+--error ER_WRONG_VALUE
+create procedure proc_36510()
+begin
+ declare should_be_illegal condition for 0;
+ declare continue handler for should_be_illegal set @x=0;
+end$$
+
+--error ER_WRONG_VALUE
+create procedure proc_36510()
+begin
+ declare continue handler for 0 set @x=0;
+end$$
+delimiter ;$$
#
# Bug#15192: "fatal errors" are caught by handlers in stored procedures
=== modified file 'mysql-test/t/subselect.test'
--- a/mysql-test/t/subselect.test 2008-07-10 23:29:27 +0000
+++ b/mysql-test/t/subselect.test 2008-07-19 14:21:51 +0000
@@ -810,15 +810,12 @@ create table t1 (a int, b int, index a (
create table t2 (a int, index a (a));
create table t3 (a int, b int, index a (a));
insert into t1 values (1,10), (2,20), (3,30), (4,40);
-disable_query_log;
# making table large enough
-let $1 = 10000;
-while ($1)
- {
- eval insert into t1 values (rand()*100000+200,rand()*100000);
- dec $1;
- }
-enable_query_log;
+create table t0(a int);
+insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
+insert into t1
+select rand()*100000+200,rand()*100000 from t0 A, t0 B, t0 C, t0 D;
+
insert into t2 values (2), (3), (4), (5);
insert into t3 values (10,3), (20,4), (30,5);
select * from t2 where t2.a in (select a from t1);
@@ -831,7 +828,7 @@ insert into t1 values (3,31);
select * from t2 where t2.a in (select a from t1 where t1.b <> 30);
select * from t2 where t2.a in (select a from t1 where t1.b <> 30 and t1.b <> 31);
explain extended select * from t2 where t2.a in (select a from t1 where t1.b <> 30);
-drop table t1, t2, t3;
+drop table t0, t1, t2, t3;
#
# alloc_group_fields() working
@@ -3120,24 +3117,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"
=== modified file 'mysql-test/t/subselect_sj.test'
--- a/mysql-test/t/subselect_sj.test 2008-05-01 03:53:36 +0000
+++ b/mysql-test/t/subselect_sj.test 2008-06-11 23:16:53 +0000
@@ -92,5 +92,36 @@ execute s1;
insert into t1 select (A.a + 10 * B.a),1 from t0 A, t0 B;
explain extended select * from t1 where a in (select pk from t10 where pk<3);
+--echo
+--echo BUG#37120 optimizer_switch allowable values not according to specification
+--echo
+
+select @@optimizer_switch;
+
+set optimizer_switch='no_materialization';
+select @@optimizer_switch;
+
+set optimizer_switch='no_semijoin';
+select @@optimizer_switch;
+
+set optimizer_switch='no_loosescan';
+select @@optimizer_switch;
+
+set optimizer_switch='no_semijoin,no_materialization';
+select @@optimizer_switch;
+
+set optimizer_switch='no_materialization,no_semijoin';
+select @@optimizer_switch;
+
+set optimizer_switch='no_semijoin,no_materialization,no_loosescan';
+select @@optimizer_switch;
+
+set optimizer_switch='no_semijoin,no_loosescan';
+select @@optimizer_switch;
+
+set optimizer_switch='no_materialization,no_loosescan';
+select @@optimizer_switch;
+set optimizer_switch='';
+
drop table t0, t1;
drop table t10, t11, t12;
=== modified file 'mysql-test/t/subselect_sj2.test'
--- a/mysql-test/t/subselect_sj2.test 2008-05-03 08:27:47 +0000
+++ b/mysql-test/t/subselect_sj2.test 2008-07-10 16:02:38 +0000
@@ -271,11 +271,11 @@ insert into t2 (ID, Name, CountryCode, P
(1,'Kabul','AFG',1780000), (2,'Qandahar','AFG',237500), (3,'Herat','AFG',186800),
(4,'Mazar-e-Sharif','AFG',127800), (33,'Willemstad','ANT',2345), (34,'Tirana','ALB',270000),
(55,'Andorra la Vella','AND',21189), (61,'South Hill','AIA',961), (62,'The Valley','AIA',595),
-(63,'Saint John�s','ATG',24000), (64,'Dubai','ARE',669181), (65,'Abu Dhabi','ARE',398695),
+(63,'Saint JohnAbu Dhabi','ARE',398695),
(66,'Sharja','ARE',320095), (67,'al-Ayn','ARE',225970), (68,'Ajman','ARE',114395),
(126,'Yerevan','ARM',1248700), (127,'Gjumri','ARM',211700), (128,'Vanadzor','ARM',172700),
-(129,'Oranjestad','ABW',29034), (144,'Baku','AZE',1787800), (145,'G�nc�','AZE',299300),
-(146,'Sumqayit','AZE',283000), (147,'Ming�Oranjestad','ABW',29034), (144,'Baku','AZE',1787800), (145,'G���nc���','AZE',299300),
+(146,'Sumqayit','AZE',283000), (147,'Ming������evir','AZE',93900), (148,'Nassau','BHS',172000),
(149,'al-Manama','BHR',148000), (150,'Dhaka','BGD',3612850), (151,'Chittagong','BGD',1392860),
(152,'Khulna','BGD',663340), (153,'Rajshahi','BGD',294056), (154,'Narayanganj','BGD',202134),
(155,'Rangpur','BGD',191398), (156,'Mymensingh','BGD',188713), (157,'Barisal','BGD',170232),
@@ -285,7 +285,7 @@ insert into t2 (ID, Name, CountryCode, P
(167,'Jamalpur','BGD',103556), (168,'Pabna','BGD',103277), (169,'Naogaon','BGD',101266),
(170,'Sirajganj','BGD',99669), (171,'Narsinghdi','BGD',98342), (172,'Saidpur','BGD',96777),
(173,'Gazipur','BGD',96717), (174,'Bridgetown','BRB',6070), (175,'Antwerpen','BEL',446525),
-(176,'Gent','BEL',224180), (177,'Charleroi','BEL',200827), (178,'Li�ge','BEL',185639),
+(176,'Gent','BEL',224180), (177,'Charleroi','BEL',200827), (178,'Li���ge','BEL',185639),
(179,'Bruxelles [Brussel]','BEL',133859), (180,'Brugge','BEL',116246), (181,'Schaerbeek','BEL',105692),
(182,'Namur','BEL',105419), (183,'Mons','BEL',90935), (184,'Belize City','BLZ',55810),
(185,'Belmopan','BLZ',7105), (190,'Saint George','BMU',1800), (191,'Hamilton','BMU',1200),
@@ -293,10 +293,10 @@ insert into t2 (ID, Name, CountryCode, P
(203,'Zenica','BIH',96027), (538,'Bandar Seri Begawan','BRN',21484), (539,'Sofija','BGR',1122302),
(540,'Plovdiv','BGR',342584), (541,'Varna','BGR',299801), (542,'Burgas','BGR',195255),
(543,'Ruse','BGR',166467), (544,'Stara Zagora','BGR',147939), (545,'Pleven','BGR',121952),
-(546,'Sliven','BGR',105530), (547,'Dobric','BGR',100399), (548,'84,'San Jos�','CRI',339131), (1523,'Wien','AUT',1608144),
+(546,'Sliven','BGR',105530), (547,'Dobric','BGR',100399), (548,'���umen','BGR',94686),
+(553,'George Town','CYM',19600), (584,'San Jos���','CRI',339131), (1523,'Wien','AUT',1608144),
(1524,'Graz','AUT',240967), (1525,'Linz','AUT',188022), (1526,'Salzburg','AUT',144247),
-(1527,'Innsbruck','AUT',111752), (1528,'Klagenfurt','AUT',91141), (1810,'Montr�al','CAN',1016376),
+(1527,'Innsbruck','AUT',111752), (1528,'Klagenfurt','AUT',91141), (1810,'Montr���al','CAN',1016376),
(1811,'Calgary','CAN',768082), (1812,'Toronto','CAN',688275), (1813,'North York','CAN',622632),
(1814,'Winnipeg','CAN',618477), (1815,'Edmonton','CAN',616306), (1816,'Mississauga','CAN',608072),
(1817,'Scarborough','CAN',594501), (1818,'Vancouver','CAN',514008), (1819,'Etobicoke','CAN',348845),
@@ -304,25 +304,25 @@ insert into t2 (ID, Name, CountryCode, P
(1823,'Laval','CAN',330393), (1824,'Surrey','CAN',304477), (1825,'Brampton','CAN',296711),
(1826,'Windsor','CAN',207588), (1827,'Saskatoon','CAN',193647), (1828,'Kitchener','CAN',189959),
(1829,'Markham','CAN',189098), (1830,'Regina','CAN',180400), (1831,'Burnaby','CAN',179209),
-(1832,'Qu�bec','CAN',167264), (1833,'York','CAN',154980), (1834,'Richmond','CAN',148867),
+(1832,'Qu��ichmond','CAN',148867),
(1835,'Vaughan','CAN',147889), (1836,'Burlington','CAN',145150), (1837,'Oshawa','CAN',140173),
(1838,'Oakville','CAN',139192), (1839,'Saint Catharines','CAN',136216), (1840,'Longueuil','CAN',127977),
(1841,'Richmond Hill','CAN',116428), (1842,'Thunder Bay','CAN',115913), (1843,'Nepean','CAN',115100),
(1844,'Cape Breton','CAN',114733), (1845,'East York','CAN',114034), (1846,'Halifax','CAN',113910),
(1847,'Cambridge','CAN',109186), (1848,'Gloucester','CAN',107314), (1849,'Abbotsford','CAN',105403),
-(1850,'Guelph','CAN',103593), (1851,'Saint John�s','CAN',101936), (1852,'Coquitlam','CAN',101820),
+(1850,'Guelph','CAN',103593), (1851,'Saint John���s','CAN',101936), (1852,'Coquitlam','CAN',101820),
(1853,'Saanich','CAN',101388), (1854,'Gatineau','CAN',100702), (1855,'Delta','CAN',95411),
(1856,'Sudbury','CAN',92686), (1857,'Kelowna','CAN',89442), (1858,'Barrie','CAN',89269),
(1890,'Shanghai','CHN',9696300), (1891,'Peking','CHN',7472000), (1892,'Chongqing','CHN',6351600),
(1893,'Tianjin','CHN',5286800), (1894,'Wuhan','CHN',4344600), (1895,'Harbin','CHN',4289800),
(1896,'Shenyang','CHN',4265200), (1897,'Kanton [Guangzhou]','CHN',4256300), (1898,'Chengdu','CHN',3361500),
-(1899,'Nanking [Nanjing]','CHN',2870300), (1900,'Changchun','CHN',2812000), (1901,'Xi�an','CHN',2761400),
+(1899,'Nanking [Nanjing]','CHN',2870300), (1900,'Changchun','CHN',2812000), (1901,'Xi�1903,'Qingdao','CHN',2596000), (1904,'Jinan','CHN',2278100),
(1905,'Hangzhou','CHN',2190500), (1906,'Zhengzhou','CHN',2107200), (1907,'Shijiazhuang','CHN',2041500),
(1908,'Taiyuan','CHN',1968400), (1909,'Kunming','CHN',1829500), (1910,'Changsha','CHN',1809800),
(1911,'Nanchang','CHN',1691600), (1912,'Fuzhou','CHN',1593800), (1913,'Lanzhou','CHN',1565800),
(1914,'Guiyang','CHN',1465200), (1915,'Ningbo','CHN',1371200), (1916,'Hefei','CHN',1369100),
-(1917,'Urumt�i [�r�mqi]','CHN',1310100), (1918,'Anshan','CHN',1200000), (1919,'Fushun','CHN',1200000),
+(1917,'Urumt���i [���r���mqi]','CHN',1310100), (1918,'Anshan','CHN',1200000), (1919,'Fushun','CHN',1200000),
(1920,'Nanning','CHN',1161800), (1921,'Zibo','CHN',1140000), (1922,'Qiqihar','CHN',1070000),
(1923,'Jilin','CHN',1040000), (1924,'Tangshan','CHN',1040000), (1925,'Baotou','CHN',980000),
(1926,'Shenzhen','CHN',950500), (1927,'Hohhot','CHN',916700), (1928,'Handan','CHN',840000),
@@ -346,12 +346,12 @@ insert into t2 (ID, Name, CountryCode, P
(1980,'Zhenjiang','CHN',368316), (1981,'Huaibei','CHN',366549), (1982,'Qinhuangdao','CHN',364972),
(1983,'Guilin','CHN',364130), (1984,'Liupanshui','CHN',363954), (1985,'Panjin','CHN',362773),
(1986,'Yangquan','CHN',362268), (1987,'Jinxi','CHN',357052), (1988,'Liaoyuan','CHN',354141),
-(1989,'Lianyungang','CHN',354139), (1990,'Xianyang','CHN',352125), (1991,'Tai�an','CHN',350696),
+(1989,'Lianyungang','CHN',354139), (1990,'Xianyang','CHN',352125), (1991,'Tai���an','CHN',350696),
(1992,'Chifeng','CHN',350077), (1993,'Shaoguan','CHN',350043), (1994,'Nantong','CHN',343341),
(1995,'Leshan','CHN',341128), (1996,'Baoji','CHN',337765), (1997,'Linyi','CHN',324720),
(1998,'Tonghua','CHN',324600), (1999,'Siping','CHN',317223), (2000,'Changzhi','CHN',317144),
(2001,'Tengzhou','CHN',315083), (2002,'Chaozhou','CHN',313469), (2003,'Yangzhou','CHN',312892),
-(2004,'Dongwan','CHN',308669), (2005,'Ma�anshan','CHN',305421), (2006,'Foshan','CHN',303160),
+(2004,'Dongwan','CHN',308669), (2005,'Ma��2007,'Yueyang','CHN',302800), (2008,'Xingtai','CHN',302789), (2009,'Changde','CHN',301276),
(2010,'Shihezi','CHN',299676), (2011,'Yancheng','CHN',296831), (2012,'Jiujiang','CHN',291187),
(2013,'Dongying','CHN',281728), (2014,'Shashi','CHN',281352), (2015,'Xintai','CHN',281248),
@@ -371,7 +371,7 @@ insert into t2 (ID, Name, CountryCode, P
(2055,'Jiangyin','CHN',213659), (2056,'Hebi','CHN',212976), (2057,'Jiaxing','CHN',211526),
(2058,'Wuzhou','CHN',210452), (2059,'Meihekou','CHN',209038), (2060,'Xuchang','CHN',208815),
(2061,'Liaocheng','CHN',207844), (2062,'Haicheng','CHN',205560), (2063,'Qianjiang','CHN',205504),
-(2064,'Baiyin','CHN',204970), (2065,'Bei�an','CHN',204899), (2066,'Yixing','CHN',200824),
+(2064,'Baiyin','CHN',204970), (2065,'Bei���an','CHN',204899), (2066,'Yixing','CHN',200824),
(2067,'Laizhou','CHN',198664), (2068,'Qaramay','CHN',197602), (2069,'Acheng','CHN',197595),
(2070,'Dezhou','CHN',195485), (2071,'Nanping','CHN',195064), (2072,'Zhaoqing','CHN',194784),
(2073,'Beipiao','CHN',194301), (2074,'Fengcheng','CHN',193784), (2075,'Fuyu','CHN',192981),
@@ -392,21 +392,21 @@ insert into t2 (ID, Name, CountryCode, P
(2118,'Xiaoshan','CHN',162930), (2119,'Zaoyang','CHN',162198), (2120,'Xinghua','CHN',161910),
(2121,'Hami','CHN',161315), (2122,'Huizhou','CHN',161023), (2123,'Jinmen','CHN',160794),
(2124,'Sanming','CHN',160691), (2125,'Ulanhot','CHN',159538), (2126,'Korla','CHN',159344),
-(2127,'Wanxian','CHN',156823), (2128,'Rui27,'Wanxian','CHN',156823), (2128,'Rui���an','CHN',156468), (2129,'Zhoushan','CHN',156317),
(2130,'Liangcheng','CHN',156307), (2131,'Jiaozhou','CHN',153364), (2132,'Taizhou','CHN',152442),
(2133,'Suzhou','CHN',151862), (2134,'Yichun','CHN',151585), (2135,'Taonan','CHN',150168),
-(2136,'Pingdu','CHN',150123), (2137,'Ji�an','CHN',148583), (2138,'Longkou','CHN',148362),
+(2136,'Pingdu','CHN',150123), (2137,'Ji���an','CHN',148583), (2138,'Longkou','CHN',148362),
(2139,'Langfang','CHN',148105), (2140,'Zhoukou','CHN',146288), (2141,'Suining','CHN',146086),
-(2142,'Yulin','CHN',144467), (2143,'Jinhua','CHN',144280), (2144,'Liu�an','CHN',144248),
+(2142,'Yulin','CHN',144467), (2143,'Jinhua','CHN',144280), (2144,'Liu�, (2146,'Suizhou','CHN',142302), (2147,'Ankang','CHN',142170),
-(2148,'Weinan','CHN',140169), (2149,'Longjing','CHN',139417), (2150,'Da�an','CHN',138963),
+(2148,'Weinan','CHN',140169), (2149,'Longjing','CHN',139417), (2150,'Da���an','CHN',138963),
(2151,'Lengshuijiang','CHN',137994), (2152,'Laiyang','CHN',137080), (2153,'Xianning','CHN',136811),
(2154,'Dali','CHN',136554), (2155,'Anda','CHN',136446), (2156,'Jincheng','CHN',136396),
(2157,'Longyan','CHN',134481), (2158,'Xichang','CHN',134419), (2159,'Wendeng','CHN',133910),
(2160,'Hailun','CHN',133565), (2161,'Binzhou','CHN',133555), (2162,'Linhe','CHN',133183),
(2163,'Wuwei','CHN',133101), (2164,'Duyun','CHN',132971), (2165,'Mishan','CHN',132744),
(2166,'Shangrao','CHN',132455), (2167,'Changji','CHN',132260), (2168,'Meixian','CHN',132156),
-(2169,'Yushu','CHN',131861), (2170,'Tiefa','CHN',131807), (2171,'Huai�an','CHN',131149),
+(2169,'Yushu','CHN',131861), (2170,'Tiefa','CHN',131807), (2171,'Huai���an','CHN',131149),
(2172,'Leiyang','CHN',130115), (2173,'Zalantun','CHN',130031), (2174,'Weihai','CHN',128888),
(2175,'Loudi','CHN',128418), (2176,'Qingzhou','CHN',128258), (2177,'Qidong','CHN',126872),
(2178,'Huaihua','CHN',126785), (2179,'Luohe','CHN',126438), (2180,'Chuzhou','CHN',125341),
@@ -417,39 +417,39 @@ insert into t2 (ID, Name, CountryCode, P
(2193,'Lianyuan','CHN',118858), (2194,'Kuytun','CHN',118553), (2195,'Puqi','CHN',117264),
(2196,'Hongjiang','CHN',116188), (2197,'Qinzhou','CHN',114586), (2198,'Renqiu','CHN',114256),
(2199,'Yuyao','CHN',114065), (2200,'Guigang','CHN',114025), (2201,'Kaili','CHN',113958),
-(2202,'Yan�an','CHN',113277), (2203,'Beihai','CHN',112673), (2204,'Xuangzhou','CHN',112673),
-(2205,'Quzhou','CHN',112373), (2206,'Yong�an','CHN',111762), (2207,'Zixing','CHN',110048),
+(2202,'Yan���an','CHN',113277), (2203,'Beihai','CHN',112673), (2204,'Xuangzhou','CHN',112673),
+(2205,'Quzhou','CHN',112373), (2206,'Yong���an','CHN',111762), (2207,'Zixing','CHN',110048),
(2208,'Liyang','CHN',109520), (2209,'Yizheng','CHN',109268), (2210,'Yumen','CHN',109234),
(2211,'Liling','CHN',108504), (2212,'Yuncheng','CHN',108359), (2213,'Shanwei','CHN',107847),
(2214,'Cixi','CHN',107329), (2215,'Yuanjiang','CHN',107004), (2216,'Bozhou','CHN',106346),
-(2217,'Jinchang','CHN',105287), (2218,'Fu�an','CHN',105265), (2219,'Suqian','CHN',105021),
+(2217,'Jinchang','CHN',105287), (2218,'Fu���an','CHN',105265), (2219,'Suqian','CHN',105021),
(2220,'Shishou','CHN',104571), (2221,'Hengshui','CHN',104269), (2222,'Danjiangkou','CHN',103211),
(2223,'Fujin','CHN',103104), (2224,'Sanya','CHN',102820), (2225,'Guangshui','CHN',102770),
(2226,'Huangshan','CHN',102628), (2227,'Xingcheng','CHN',102384), (2228,'Zhucheng','CHN',102134),
(2229,'Kunshan','CHN',102052), (2230,'Haining','CHN',100478), (2231,'Pingliang','CHN',99265),
(2232,'Fuqing','CHN',99193), (2233,'Xinzhou','CHN',98667), (2234,'Jieyang','CHN',98531),
-(2235,'Zhangjiagang','CHN',97994), (2236,'Tong Xian','CHN',97168), (2237,'Ya�an','CHN',95900),
+(2235,'Zhangjiagang','CHN',97994), (2236,'Tong Xian','CHN',97168), (2237,'Ya���an','CHN',95900),
(2238,'Jinzhou','CHN',95761), (2239,'Emeishan','CHN',94000), (2240,'Enshi','CHN',93056),
(2241,'Bose','CHN',93009), (2242,'Yuzhou','CHN',92889), (2243,'Kaiyuan','CHN',91999),
(2244,'Tumen','CHN',91471), (2245,'Putian','CHN',91030), (2246,'Linhai','CHN',90870),
(2247,'Xilin Hot','CHN',90646), (2248,'Shaowu','CHN',90286), (2249,'Junan','CHN',90222),
(2250,'Huaying','CHN',89400), (2251,'Pingyi','CHN',89373), (2252,'Huangyan','CHN',89288),
-(2413,'La Habana','CUB',2256000), (2414,'Santiago de Cuba','CUB',433180), (2415,'Camag�ey','CUB',298726),
-(2416,'Holgu�n','CUB',249492), (2417,'Santa Clara','CUB',207350), (2418,'Guant�namo','CUB',205078),
-(2419,'Pinar del R�o','CUB',142100), (2420,'Bayamo','CUB',141000), (2421,'Cienfuegos','CUB',132770),
+(2413,'La Habana','CUB',2256000), (2414,'Santiago de Cuba','CUB',433180), (2415,'Camag���ey','CUB',298726),
+(2416,'Holgu���n','CUB',249492), (2417,'Santa Clara','CUB',207350), (2418,'Guant���namo','CUB',205078),
+(2419,'Pinar del R���o','CUB',142100), (2420,'Bayamo','CUB',141000), (2421,'Cienfuegos','CUB',132770),
(2422,'Victoria de las Tunas','CUB',132350), (2423,'Matanzas','CUB',123273), (2424,'Manzanillo','CUB',109350),
-(2425,'Sancti-Sp�ritus','CUB',100751), (2426,'Ciego de �vila','CUB',98505), (2430,'Nicosia','CYP',195000),
-(2431,'Limassol','CYP',154400), (3245,'Z�rich','CHE',336800), (3246,'Geneve','CHE',173500),
+(2425,'Sancti-Sp���ritus','CUB',100751), (2426,'Ciego de ���vila','CUB',98505), (2430,'Nicosia','CYP',195000),
+(2431,'Limassol','CYP',154400), (3245,'Z���rich','CHE',336800), (3246,'Geneve','CHE',173500),
(3247,'Basel','CHE',166700), (3248,'Bern','CHE',122700), (3249,'Lausanne','CHE',114500),
(3339,'Praha','CZE',1181126), (3340,'Brno','CZE',381862), (3341,'Ostrava','CZE',320041),
(3342,'Plzen','CZE',166759), (3343,'Olomouc','CZE',102702), (3344,'Liberec','CZE',99155),
-(3345,'Cesk� Budejovice','CZE',98186), (3346,'Hradec Kr�lov�','CZE',98080), (3347,'�st� nad Labem','CZE',95491),
+(3345,'Cesk��� Budejovice','CZE',98186), (3346,'Hradec Kr���lov���','CZE',98080), (3347,'���st��� nad Labem','CZE',95491),
(3348,'Pardubice','CZE',91309), (3520,'Minsk','BLR',1674000), (3521,'Gomel','BLR',475000),
(3522,'Mogiljov','BLR',356000), (3523,'Vitebsk','BLR',340000), (3524,'Grodno','BLR',302000),
-(3525,'Brest','BLR',286000), (3526,'Bobruisk','BLR',221000), (3527,'Baranovit�i','BLR',167000),
-(3528,'Borisov','BLR',151000), (3529,'Pinsk','BLR',130000), (3530,'Or�a','BLR',124000),
+(3525,'Brest','BLR',286000), (3526,'Bobruisk','BLR',221000), (3527,'Baranovit���i','BLR',167000),
+(3528,'Borisov','BLR',151000), (3529,'Pinsk','BLR',130000), (3530,'Or���a','BLR',124000),
(3531,'Mozyr','BLR',110000), (3532,'Novopolotsk','BLR',106000), (3533,'Lida','BLR',101000),
-(3534,'Soligorsk','BLR',101000), (3535,'Molodet�no','BLR',97000);
+(3534,'Soligorsk','BLR',101000), (3535,'Molodet��ent) values
('AFG','Afghanistan','Asia'), ('ANT','Netherlands Antilles','North America'),
@@ -837,3 +837,25 @@ delimiter ;|
call p1();
drop procedure p1;
drop table t1;
+
+#
+# BUG#35468 "Slowdown and wrong result for uncorrelated subquery w/o where"
+#
+
+create table t0 (a int);
+insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
+create table t1 (a int) as select A.a + 10 *(B.a + 10*C.a) as a from t0 A, t0 B, t0 C;
+create table t2 (id int, a int, primary key(id), key(a)) as select a as id, a as a from t1;
+show create table t2;
+set @a=0;
+create table t3 as select * from t2 limit 0;
+insert into t3 select @a:=@a+1, t2.a from t2, t0;
+insert into t3 select @a:=@a+1, t2.a from t2, t0;
+insert into t3 select @a:=@a+1, t2.a from t2, t0;
+
+alter table t3 add primary key(id), add key(a);
+--echo The following must use loose index scan over t3, key a:
+explain select count(a) from t2 where a in ( SELECT a FROM t3);
+select count(a) from t2 where a in ( SELECT a FROM t3);
+
+drop table t0,t1,t2,t3;
=== modified file 'sql/handler.cc'
--- a/sql/handler.cc 2008-07-09 07:12:43 +0000
+++ b/sql/handler.cc 2008-07-19 14:21:51 +0000
@@ -4584,7 +4584,7 @@ ha_rows DsMrr_impl::dsmrr_info_const(uin
@retval FALSE No
*/
-bool DsMrr_impl::key_uses_partial_cols(uint keyno)
+bool key_uses_partial_cols(TABLE *table, uint keyno)
{
KEY_PART_INFO *kp= table->key_info[keyno].key_part;
KEY_PART_INFO *kp_end= kp + table->key_info[keyno].key_parts;
@@ -4596,7 +4596,6 @@ bool DsMrr_impl::key_uses_partial_cols(u
return FALSE;
}
-
/**
DS-MRR Internals: Choose between Default MRR implementation and DS-MRR
@@ -4630,7 +4629,7 @@ bool DsMrr_impl::choose_mrr_impl(uint ke
(*flags & HA_MRR_INDEX_ONLY) || (*flags & HA_MRR_SORTED) ||
(keyno == table->s->primary_key &&
h->primary_key_is_clustered()) ||
- key_uses_partial_cols(keyno))
+ key_uses_partial_cols(table, keyno))
{
/* Use the default implementation */
*flags |= HA_MRR_USE_DEFAULT_IMPL;
=== modified file 'sql/handler.h'
--- a/sql/handler.h 2008-06-30 20:21:41 +0000
+++ b/sql/handler.h 2008-07-10 16:02:38 +0000
@@ -2331,6 +2331,7 @@ private:
};
+bool key_uses_partial_cols(TABLE *table, uint keyno);
/**
A Disk-Sweep MRR interface implementation
@@ -2391,7 +2392,6 @@ public:
void *seq_init_param, uint n_ranges, uint *bufsz,
uint *flags, COST_VECT *cost);
private:
- bool key_uses_partial_cols(uint keyno);
bool choose_mrr_impl(uint keyno, ha_rows rows, uint *flags, uint *bufsz,
COST_VECT *cost);
bool get_disk_sweep_mrr_cost(uint keynr, ha_rows rows, uint flags,
=== modified file 'sql/mysql_priv.h'
--- a/sql/mysql_priv.h 2008-07-09 07:12:43 +0000
+++ b/sql/mysql_priv.h 2008-07-19 14:21:51 +0000
@@ -568,6 +568,7 @@ enum open_table_mode
/* @@optimizer_switch flags */
#define OPTIMIZER_SWITCH_NO_MATERIALIZATION 1
#define OPTIMIZER_SWITCH_NO_SEMIJOIN 2
+#define OPTIMIZER_SWITCH_NO_LOOSE_SCAN 4
/*
=== modified file 'sql/mysqld.cc'
--- a/sql/mysqld.cc 2008-07-09 07:12:43 +0000
+++ b/sql/mysqld.cc 2008-07-19 14:21:51 +0000
@@ -330,15 +330,16 @@ TYPELIB sql_mode_typelib= { array_elemen
static const char *optimizer_switch_names[]=
{
- "no_materialization", "no_semijoin",
+ "no_materialization", "no_semijoin", "no_loosescan",
NullS
};
/* Corresponding defines are named OPTIMIZER_SWITCH_XXX */
static const unsigned int optimizer_switch_names_len[]=
{
- /*no_materialization*/ 19,
- /*no_semijoin*/ 11
+ /*no_materialization*/ 18,
+ /*no_semijoin*/ 11,
+ /*no_loosescan*/ 12,
};
TYPELIB optimizer_switch_typelib= { array_elements(optimizer_switch_names)-1,"",
=== modified file 'sql/sp_pcontext.cc'
--- a/sql/sp_pcontext.cc 2008-04-09 00:56:49 +0000
+++ b/sql/sp_pcontext.cc 2008-06-12 20:20:47 +0000
@@ -51,7 +51,8 @@ sp_cond_check(LEX_STRING *sqlstate)
(c < 'A' || 'Z' < c))
return FALSE;
}
- if (strcmp(sqlstate->str, "00000") == 0)
+ /* SQLSTATE class '00' : completion condition */
+ if (strncmp(sqlstate->str, "00", 2) == 0)
return FALSE;
return TRUE;
}
=== modified file 'sql/sql_select.cc'
--- a/sql/sql_select.cc 2008-07-10 23:29:27 +0000
+++ b/sql/sql_select.cc 2008-07-19 14:21:51 +0000
@@ -169,13 +169,11 @@ static int join_read_always_key(JOIN_TAB
static int join_read_last_key(JOIN_TAB *tab);
static int join_no_more_records(READ_RECORD *info);
static int join_read_next(READ_RECORD *info);
-static int join_read_next_different(READ_RECORD *info);
static int join_init_quick_read_record(JOIN_TAB *tab);
static int test_if_quick_select(JOIN_TAB *tab);
static int join_init_read_record(JOIN_TAB *tab);
static int join_read_first(JOIN_TAB *tab);
static int join_read_next_same(READ_RECORD *info);
-static int join_read_next_same_diff(READ_RECORD *info);
static int join_read_last(JOIN_TAB *tab);
static int join_read_prev_same(READ_RECORD *info);
static int join_read_prev(READ_RECORD *info);
@@ -1055,6 +1053,12 @@ static
int setup_semijoin_dups_elimination(JOIN *join, ulonglong options, uint no_jbuf_after)
{
table_map cur_map= join->const_table_map | PSEUDO_TABLE_BITS;
+ enum sj_strategy_enum {
+ SJ_STRATEGY_INVALID,
+ SJ_STRATEGY_INSIDEOUT,
+ SJ_STRATEGY_DUPS_WEEDOUT,
+ SJ_STRATEGY_DUPS_WEEDOUT_JBUF
+ };
struct {
/*
0 - invalid (EOF marker),
@@ -1062,7 +1066,7 @@ int setup_semijoin_dups_elimination(JOIN
2 - Temptable (maybe confluent),
3 - Temptable with join buffering
*/
- uint strategy;
+ enum sj_strategy_enum strategy;
uint start_idx; /* Left range bound */
uint end_idx; /* Right range bound */
/*
@@ -1086,7 +1090,7 @@ int setup_semijoin_dups_elimination(JOIN
/*
First pass: locate the duplicate-generating ranges and pick the strategies.
*/
- for (i=join->const_tables ; i < join->tables ; i++)
+ for (i= join->const_tables ; i < join->tables ; i++)
{
JOIN_TAB *tab=join->join_tab+i;
TABLE *table=tab->table;
@@ -1104,7 +1108,7 @@ int setup_semijoin_dups_elimination(JOIN
be that it overlaps with anther semi-join's range. we don't
support InsideOut for joined ranges)
*/
- if (join->best_positions[i].use_insideout_scan)
+ if (join->best_positions[i].insideout_key != MAX_KEY)
emb_insideout_nest= tab->emb_sj_nest;
}
@@ -1174,7 +1178,7 @@ int setup_semijoin_dups_elimination(JOIN
bitmap_covers(cur_map, emb_insideout_nest->sj_inner_tables))
{
/* Save that this range is handled with InsideOut: */
- dups_ranges[cur_range].strategy= 1;
+ dups_ranges[cur_range].strategy= SJ_STRATEGY_INSIDEOUT;
end_of_range= TRUE;
}
else if (bitmap_covers(cur_map, emb_outer_tables | emb_sj_map))
@@ -1183,7 +1187,9 @@ int setup_semijoin_dups_elimination(JOIN
This is a complete range to be handled with either DuplicateWeedout
or FirstMatch
*/
- dups_ranges[cur_range].strategy= dealing_with_jbuf? 3 : 2;
+ dups_ranges[cur_range].strategy=
+ dealing_with_jbuf? SJ_STRATEGY_DUPS_WEEDOUT_JBUF:
+ SJ_STRATEGY_DUPS_WEEDOUT;
/*
This will hold tables from within the range that need to be put
into the join buffer before we can use the FirstMatch on its tail.
@@ -1199,7 +1205,7 @@ int setup_semijoin_dups_elimination(JOIN
emb_sj_map= emb_outer_tables= 0;
emb_insideout_nest= NULL;
dealing_with_jbuf= FALSE;
- dups_ranges[++cur_range].strategy= 0;
+ dups_ranges[++cur_range].strategy= SJ_STRATEGY_INVALID;
}
else
{
@@ -1213,18 +1219,27 @@ int setup_semijoin_dups_elimination(JOIN
THD *thd= join->thd;
SJ_TMP_TABLE **next_sjtbl_ptr= &join->sj_tmp_tables;
/*
- Second pass: setup the chosen strategies
+ The second pass: setup the chosen strategies
*/
for (int j= 0; j < cur_range; j++)
{
JOIN_TAB *tab=join->join_tab + dups_ranges[j].start_idx;
JOIN_TAB *jump_to;
- if (dups_ranges[j].strategy == 1) // InsideOut strategy
+ if (dups_ranges[j].strategy == SJ_STRATEGY_INSIDEOUT)
{
+ /* We jump from the last table to the first one */
tab->insideout_match_tab= join->join_tab + dups_ranges[j].end_idx - 1;
+
+ /* Calculate key length */
+ uint nparts= join->best_positions[dups_ranges[j].start_idx].insideout_parts;
+ uint keyno= join->best_positions[dups_ranges[j].start_idx].insideout_key;
+ uint keylen= 0;
+ for (uint kp=0; kp < nparts; kp++)
+ keylen += tab->table->key_info[keyno].key_part[kp].store_length;
+ tab->insideout_key_len= keylen;
jump_to= tab++;
}
- else // DuplicateWeedout strategy
+ else // DuplicateWeedout or FirstMatch
{
SJ_TMP_TABLE::TAB sjtabs[MAX_TABLES];
table_map cur_map= join->const_table_map | PSEUDO_TABLE_BITS;
@@ -5417,9 +5432,16 @@ best_access_path(JOIN *join,
table_map best_ref_depends_map= 0;
double tmp;
ha_rows rec;
- uint best_is_sj_inside_out= 0;
+ uint best_is_sj_inside_out= MAX_KEY;
+ uint best_sj_keyparts;
+ bool try_sj_inside_out= FALSE;
+ uint sj_insideout_quick_select= FALSE;
+ uint sj_insideout_quick_max_sj_keypart;
+ uint sj_inside_out_scan= MAX_KEY;
DBUG_ENTER("best_access_path");
-
+
+ LINT_INIT(best_sj_keyparts); // Protected by sj_inside_out_scan
+ LINT_INIT(sj_insideout_quick_max_sj_keypart); // Protected by sj_insideout_quick_*
if (s->keyuse)
{ /* Use key if possible */
TABLE *table= s->table;
@@ -5427,14 +5449,13 @@ best_access_path(JOIN *join,
double best_records= DBL_MAX;
uint max_key_part=0;
ulonglong bound_sj_equalities= 0;
- bool try_sj_inside_out= FALSE;
/*
Discover the bound equalites. We need to do this, if
1. The next table is an SJ-inner table, and
2. It is the first table from that semijoin, and
3. We're not within a semi-join range (i.e. all semi-joins either have
all or none of their tables in join_table_map), except
- s->emb_sj_nest (which we've just entered).
+ s->emb_sj_nest (which we've just entered, see #2).
4. All non-IN-equality correlation references from this sj-nest are
bound
5. But some of the IN-equalities aren't (so this can't be handled by
@@ -5447,12 +5468,15 @@ best_access_path(JOIN *join,
join->cur_emb_sj_nests == s->emb_sj_nest->sj_inner_tables && // (3)
!(remaining_tables &
s->emb_sj_nest->nested_join->sj_corr_tables) && // (4)
- remaining_tables & s->emb_sj_nest->nested_join->sj_depends_on) // (5)
+ remaining_tables & s->emb_sj_nest->nested_join->sj_depends_on &&// (5)
+ !test(thd->variables.optimizer_switch & OPTIMIZER_SWITCH_NO_LOOSE_SCAN))
{
/* This table is an InsideOut scan candidate */
bound_sj_equalities= get_bound_sj_equalities(s->emb_sj_nest,
remaining_tables);
try_sj_inside_out= TRUE;
+ DBUG_PRINT("info", ("Will try InsideOut scan, bound_map=%llx",
+ (longlong)bound_sj_equalities));
}
/* Test how we can use keys */
@@ -5473,6 +5497,9 @@ best_access_path(JOIN *join,
start_key= keyuse;
ulonglong handled_sj_equalities=0;
key_part_map sj_insideout_map= 0;
+ uint max_sj_keypart= 0;
+ DBUG_PRINT("info", ("Considering ref access on key %s",
+ keyuse->table->key_info[keyuse->key].name));
do /* For each keypart */
{
@@ -5520,6 +5547,7 @@ best_access_path(JOIN *join,
{
handled_sj_equalities |= 1ULL << keyuse->sj_pred_no;
sj_insideout_map |= ((key_part_map)1) << keyuse->keypart;
+ set_if_bigger(max_sj_keypart, keyuse->keypart);
}
}
@@ -5538,7 +5566,7 @@ best_access_path(JOIN *join,
if (rec < MATCHING_ROWS_IN_OTHER_TABLE)
rec= MATCHING_ROWS_IN_OTHER_TABLE; // Fix for small tables
- bool sj_inside_out_scan= FALSE;
+ sj_inside_out_scan= MAX_KEY;
/*
ft-keys require special treatment
*/
@@ -5555,49 +5583,66 @@ best_access_path(JOIN *join,
{
found_constraint= test(found_part);
/*
- Check if InsideOut scan is applicable:
- 1. All IN-equalities are either "bound" or "handled"
- 2. Index keyparts are
- ...
+ Check if we can use InsideOut semi-join strategy. We can if
+ 1. This is the right table at right location
+ 2. All IN-equalities are either
+ - "bound", ie. the outer_expr part refers to the preceding tables
+ - "handled", ie. covered by the index we're considering
+ 3. Index order allows to enumerate subquery's duplicate groups in
+ order. This happens when the index definition matches this
+ pattern:
+
+ (handled_col|bound_col)* (other_col|bound_col)
+
*/
- if (try_sj_inside_out &&
- table->covering_keys.is_set(key) &&
- (handled_sj_equalities | bound_sj_equalities) == // (1)
- PREV_BITS(ulonglong, s->emb_sj_nest->sj_in_exprs)) // (1)
+ if (try_sj_inside_out && // (1)
+ (handled_sj_equalities | bound_sj_equalities) == // (2)
+ PREV_BITS(ulonglong, s->emb_sj_nest->sj_in_exprs) && // (2)
+ (PREV_BITS(key_part_map, max_sj_keypart+1) & // (3)
+ (found_part | sj_insideout_map)) == // (3)
+ (found_part | sj_insideout_map) && // (3)
+ !key_uses_partial_cols(s->table, key))
{
- uint n_fixed_parts= max_part_bit(found_part);
- if (n_fixed_parts != keyinfo->key_parts &&
- (PREV_BITS(uint, n_fixed_parts) | sj_insideout_map) ==
- PREV_BITS(uint, keyinfo->key_parts))
+ /* Ok, can use the strategy */
+ sj_inside_out_scan= key;
+ if (s->quick && s->quick->index == key &&
+ s->quick->get_type() == QUICK_SELECT_I::QS_TYPE_RANGE)
{
- /*
- Not all parts are fixed. Produce bitmap of remaining bits and
- check if all of them are covered.
- */
- sj_inside_out_scan= TRUE;
- DBUG_PRINT("info", ("Using sj InsideOut scan"));
- if (!n_fixed_parts)
- {
- /*
- It's a confluent ref scan.
+ sj_insideout_quick_select= TRUE;
+ sj_insideout_quick_max_sj_keypart= max_sj_keypart;
+ }
+ DBUG_PRINT("info", ("Can use InsideOut scan"));
- That is, all found KEYUSE elements refer to IN-equalities,
- and there is really no ref access because there is no
- t.keypart0 = {bound expression}
+ /*
+ Check if this is a confluent where there are no usable bound
+ IN-equalities, e.g. we have
- Calculate the cost of complete loose index scan.
- */
- records= (double)s->table->file->stats.records;
+ outer_expr IN (SELECT innertbl.key FROM ...)
+
+ and outer_expr cannot be evaluated yet, so it's actually full
+ index scan and not a ref access
+ */
+ if (!(found_part & 1 ) && /* no usable ref access for 1st key part */
+ table->covering_keys.is_set(key))
+ {
+ DBUG_PRINT("info", ("Can use full index scan for InsideOut"));
+ /* Calculate the cost of complete loose index scan. */
+ records= rows2double(s->table->file->stats.records);
- /* The cost is entire index scan cost (divided by 2) */
- best_time= s->table->file->index_only_read_time(key, records);
+ /* The cost is entire index scan cost (divided by 2) */
+ best_time= s->table->file->index_only_read_time(key, records);
- /* Now figure how many different keys we will get */
- ulong rpc;
- if ((rpc= keyinfo->rec_per_key[keyinfo->key_parts-1]))
- records= records / rpc;
- start_key= NULL;
- }
+ /*
+ Now find out how many different keys we will get (for now we
+ ignore the fact that we have "keypart_i=const" restriction for
+ some key components, that may make us think think that loose
+ scan will produce more distinct records than it actually will)
+ */
+ ulong rpc;
+ if ((rpc= keyinfo->rec_per_key[max_sj_keypart]))
+ records= records / rpc;
+ start_key= NULL;
+ /* Fall through */
}
}
@@ -5852,7 +5897,7 @@ best_access_path(JOIN *join,
tmp= best_time; // Do nothing
}
- if (sj_inside_out_scan && !start_key)
+ if ((sj_inside_out_scan != MAX_KEY) && !start_key)
{
tmp= tmp/2;
if (records)
@@ -5869,8 +5914,9 @@ best_access_path(JOIN *join,
best_max_key_part= max_key_part;
best_ref_depends_map= found_ref;
best_is_sj_inside_out= sj_inside_out_scan;
+ best_sj_keyparts= max_sj_keypart;
}
- }
+ } /* for each key */
records= best_records;
}
@@ -5909,6 +5955,7 @@ best_access_path(JOIN *join,
! s->table->covering_keys.is_clear_all() && best_key && !s->quick) &&// (3)
!(s->table->force_index && best_key && !s->quick)) // (4)
{ // Check full join
+ sj_inside_out_scan= MAX_KEY;
ha_rows rnd_records= s->found_records;
/*
If there is a filtering condition on the table (i.e. ref analyzer found
@@ -5948,6 +5995,11 @@ best_access_path(JOIN *join,
tmp= record_count *
(s->quick->read_time +
(s->found_records - rnd_records)/(double) TIME_FOR_COMPARE);
+
+ if (sj_insideout_quick_select && idx == join->const_tables)
+ {
+ sj_inside_out_scan= s->quick->index;
+ }
}
else
{
@@ -5999,7 +6051,8 @@ best_access_path(JOIN *join,
best_key= 0;
/* range/index_merge/ALL/index access method are "independent", so: */
best_ref_depends_map= 0;
- best_is_sj_inside_out= FALSE;
+ best_is_sj_inside_out= sj_inside_out_scan;
+ best_sj_keyparts= sj_insideout_quick_max_sj_keypart;
}
}
@@ -6009,7 +6062,8 @@ best_access_path(JOIN *join,
join->positions[idx].key= best_key;
join->positions[idx].table= s;
join->positions[idx].ref_depend_map= best_ref_depends_map;
- join->positions[idx].use_insideout_scan= best_is_sj_inside_out;
+ join->positions[idx].insideout_key= best_is_sj_inside_out;
+ join->positions[idx].insideout_parts= best_sj_keyparts + 1;
if (!best_key &&
idx == join->const_tables &&
@@ -6952,7 +7006,7 @@ get_best_combination(JOIN *join)
DBUG_PRINT("info",("type: %d", j->type));
if (j->type == JT_CONST)
continue; // Handled in make_join_stat..
-
+ j->insideout_match_tab= NULL;
j->ref.key = -1;
j->ref.key_parts=0;
@@ -6961,6 +7015,7 @@ get_best_combination(JOIN *join)
if (j->keys.is_clear_all() || !(keyuse= join->best_positions[tablenr].key))
{
j->type=JT_ALL;
+ j->index= join->best_positions[tablenr].insideout_key;
if (tablenr != join->const_tables)
join->full_join=1;
}
@@ -7255,6 +7310,7 @@ make_simple_join(JOIN *join,TABLE *tmp_t
join_tab->ref.key_parts= 0;
join_tab->flush_weedout_table= join_tab->check_weed_out_table= NULL;
join_tab->do_firstmatch= NULL;
+ join_tab->insideout_match_tab= NULL;
bzero((char*) &join_tab->read_record,sizeof(join_tab->read_record));
tmp_table->status=0;
tmp_table->null_row=0;
@@ -7523,7 +7579,15 @@ make_join_select(JOIN *join,SQL_SELECT *
thd->lex->current_select->master_unit() ==
&thd->lex->unit) // not upper level SELECT
join->const_table_map|=RAND_TABLE_BIT;
- { // Check const tables
+
+ /*
+ Extract expressions that depend on constant tables
+ 1. Const part of the join's WHERE clause can be checked immediately
+ and if it is not satisfied then the join has empty result
+ 2. Constant parts of outer joins' ON expressions must be attached
+ there inside the triggers.
+ */
+ {
COND *const_cond=
make_cond_for_table(cond,
join->const_table_map,
@@ -7770,9 +7834,9 @@ make_join_select(JOIN *join,SQL_SELECT *
tab->keys=sel->quick_keys;
tab->keys.merge(sel->needed_reg);
tab->use_quick= (!sel->needed_reg.is_clear_all() &&
- (select->quick_keys.is_clear_all() ||
- (select->quick &&
- (select->quick->records >= 100L)))) ?
+ (sel->quick_keys.is_clear_all() ||
+ (sel->quick &&
+ (sel->quick->records >= 100L)))) ?
2 : 1;
sel->read_tables= used_tables & ~current_map;
}
@@ -8288,9 +8352,8 @@ make_join_readinfo(JOIN *join, ulonglong
sorted= 0; // only first must be sorted
if (tab->insideout_match_tab)
{
- if (!(tab->insideout_buf= (uchar*)join->thd->alloc(tab->table->key_info
- [tab->index].
- key_length)))
+ if (!(tab->insideout_buf=
+ (uchar*)join->thd->alloc(tab->insideout_key_len)))
return TRUE;
}
switch (tab->type) {
@@ -8351,8 +8414,7 @@ make_join_readinfo(JOIN *join, ulonglong
if (tab->type == JT_REF)
{
tab->read_first_record= join_read_always_key;
- tab->read_record.read_record= tab->insideout_match_tab?
- join_read_next_same_diff : join_read_next_same;
+ tab->read_record.read_record= join_read_next_same;
}
else
{
@@ -13641,9 +13703,25 @@ sub_select(JOIN *join,JOIN_TAB *join_tab
Note: psergey has added the 2nd part of the following condition; the
change should probably be made in 5.1, too.
*/
+ bool skip_over= FALSE;
while (rc == NESTED_LOOP_OK && join->return_tab >= join_tab)
{
+ if (join_tab->insideout_match_tab && join_tab->insideout_match_tab->found_match)
+ {
+ KEY *key= join_tab->table->key_info + join_tab->index;
+ key_copy(join_tab->insideout_buf, info->record, key,
+ join_tab->insideout_key_len);
+ skip_over= TRUE;
+ }
error= info->read_record(info);
+
+ if (skip_over && !error &&
+ !key_cmp(join_tab->table->key_info[join_tab->index].key_part,
+ join_tab->insideout_buf, join_tab->insideout_key_len))
+ {
+ continue;
+ }
+
rc= evaluate_join_record(join, join_tab, error);
}
@@ -14371,37 +14449,6 @@ join_no_more_records(READ_RECORD *info _
return -1;
}
-static int
-join_read_next_same_diff(READ_RECORD *info)
-{
- TABLE *table= info->table;
- JOIN_TAB *tab=table->reginfo.join_tab;
- if (tab->insideout_match_tab->found_match)
- {
- KEY *key= tab->table->key_info + tab->index;
- do
- {
- int error;
- /* Save index tuple from record to the buffer */
- key_copy(tab->insideout_buf, info->record, key, 0);
-
- if ((error=table->file->index_next_same(table->record[0],
- tab->ref.key_buff,
- tab->ref.key_length)))
- {
- if (error != HA_ERR_END_OF_FILE)
- return report_error(table, error);
- table->status= STATUS_GARBAGE;
- return -1;
- }
- } while (!key_cmp(tab->table->key_info[tab->index].key_part,
- tab->insideout_buf, key->key_length));
- tab->insideout_match_tab->found_match= 0;
- return 0;
- }
- else
- return join_read_next_same(info);
-}
static int
join_read_next_same(READ_RECORD *info)
@@ -14498,17 +14545,7 @@ join_read_first(JOIN_TAB *tab)
tab->read_record.file=table->file;
tab->read_record.index=tab->index;
tab->read_record.record=table->record[0];
- if (tab->insideout_match_tab)
- {
- tab->read_record.do_insideout_scan= tab;
- tab->read_record.read_record=join_read_next_different;
- tab->insideout_match_tab->found_match= 0;
- }
- else
- {
- tab->read_record.read_record=join_read_next;
- tab->read_record.do_insideout_scan= 0;
- }
+ tab->read_record.read_record=join_read_next;
if (!table->file->inited)
table->file->ha_index_init(tab->index, tab->sorted);
@@ -14523,32 +14560,6 @@ join_read_first(JOIN_TAB *tab)
static int
-join_read_next_different(READ_RECORD *info)
-{
- JOIN_TAB *tab= info->do_insideout_scan;
- if (tab->insideout_match_tab->found_match)
- {
- KEY *key= tab->table->key_info + tab->index;
- do
- {
- int error;
- /* Save index tuple from record to the buffer */
- key_copy(tab->insideout_buf, info->record, key, 0);
-
- if ((error=info->file->index_next(info->record)))
- return report_error(info->table, error);
-
- } while (!key_cmp(tab->table->key_info[tab->index].key_part,
- tab->insideout_buf, key->key_length));
- tab->insideout_match_tab->found_match= 0;
- return 0;
- }
- else
- return join_read_next(info);
-}
-
-
-static int
join_read_next(READ_RECORD *info)
{
int error;
=== modified file 'sql/sql_select.h'
--- a/sql/sql_select.h 2008-06-05 16:11:22 +0000
+++ b/sql/sql_select.h 2008-07-10 16:02:38 +0000
@@ -272,13 +272,18 @@ typedef struct st_join_table {
struct st_join_table *do_firstmatch;
/*
- ptr - this join tab should do an InsideOut scan. Points
- to the tab for which we'll need to check tab->found_match.
-
+ ptr - We're doing and InsideOut scan, and this is the first (aka
+ "driving" join tab). ptr to the last tab, for which we'll need
+ to check tab->found_match to see if the current value group had
+ a match.
NULL - Not an insideout scan.
*/
struct st_join_table *insideout_match_tab;
- uchar *insideout_buf; // Buffer to save index tuple to be able to skip dups
+ /* Buffer to save index tuple to be able to skip duplicates */
+ uchar *insideout_buf;
+
+ /* How many key components to store in the above */
+ uint insideout_key_len;
/* Used by InsideOut scan. Just set to true when have found a row. */
bool found_match;
@@ -348,8 +353,16 @@ typedef struct st_position
/* If ref-based access is used: bitmap of tables this table depends on */
table_map ref_depend_map;
-
- bool use_insideout_scan;
+
+ /*
+ keyno - This is an insideout scan on this key. If keyuse is NULL then
+ this is a full index scan, otherwise this is a ref + insideout
+ scan (and keyno matches the KEUSE's)
+ MAX_KEY - This is not an InsideOut scan
+ */
+ uint insideout_key;
+ /* Number of key parts to be used by insideout */
+ uint insideout_parts;
} POSITION;
=== modified file 'sql/sql_yacc.yy'
--- a/sql/sql_yacc.yy 2008-07-14 14:56:49 +0000
+++ b/sql/sql_yacc.yy 2008-07-19 14:21:51 +0000
@@ -589,10 +589,10 @@ bool my_yyoverflow(short **a, YYSTYPE **
%pure_parser /* We have threads */
/*
- Currently there are 172 shift/reduce conflicts.
+ Currently there are 169 shift/reduce conflicts.
We should not introduce new conflicts any more.
*/
-%expect 172
+%expect 169
/*
Comments for TOKENS.
@@ -2610,6 +2610,11 @@ sp_hcond_element:
sp_cond:
ulong_num
{ /* mysql errno */
+ if ($1 == 0)
+ {
+ my_error(ER_WRONG_VALUE, MYF(0), "CONDITION", "0");
+ MYSQL_YYABORT;
+ }
$$= (sp_cond_type_t *)YYTHD->alloc(sizeof(sp_cond_type_t));
$$->type= sp_cond_type_t::number;
$$->mysqlerr= $1;
@@ -4773,10 +4778,6 @@ key_def:
/* Only used for ALTER TABLE. Ignored otherwise. */
lex->alter_info.flags|= ALTER_FOREIGN_KEY;
}
- | constraint opt_check_constraint
- {
- Lex->col_list.empty(); /* Alloced by sql_alloc */
- }
| opt_constraint check_constraint
{
Lex->col_list.empty(); /* Alloced by sql_alloc */
@@ -4789,7 +4790,7 @@ opt_check_constraint:
;
check_constraint:
- CHECK_SYM expr
+ CHECK_SYM '(' expr ')'
;
opt_constraint:
=== modified file 'sql/structs.h'
--- a/sql/structs.h 2008-06-23 11:55:24 +0000
+++ b/sql/structs.h 2008-07-10 16:02:38 +0000
@@ -137,7 +137,6 @@ typedef struct st_read_record { /* Par
uchar *cache,*cache_pos,*cache_end,*read_positions;
IO_CACHE *io_cache;
bool print_error, ignore_not_found_rows;
- struct st_join_table *do_insideout_scan;
} READ_RECORD;
| Thread | ||
|---|---|---|
| • bzr commit into mysql-6.0-opt branch (sergefp:2674) | Sergey Petrunia | 19 Jul |
© 1995-2008 MySQL AB, 2008-2009 Sun Microsystems, Inc.
Page generated in 0.885 sec. using MySQL 5.1.14-beta-log