#At file:///home/spetrunia/dev/mysql-6.0-bka-preview-merge/
2646 Sergey Petrunia 2008-09-04 [merge]
mysql-6.0 -> mysql-6.0-bka-preview merge
removed:
mysql-test/suite/rpl/t/rpl_row_err_daisychain-master.opt
mysql-test/suite/rpl/t/rpl_row_err_daisychain-slave.opt
mysql-test/suite/rpl_ndb/t/rpl_truncate_7ndb_2-master.opt
added:
mysql-test/r/subselect_nulls.result
mysql-test/t/subselect_nulls.test
modified:
.bzr-mysql/default.conf
configure.in
mysql-test/extra/binlog_tests/binlog_insert_delayed.test
mysql-test/include/show_binlog_events.inc
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/suite/binlog/r/binlog_killed_simulate.result
mysql-test/suite/binlog/r/binlog_row_binlog.result
mysql-test/suite/binlog/r/binlog_row_mix_innodb_myisam.result
mysql-test/suite/binlog/r/binlog_statement_insert_delayed.result
mysql-test/suite/binlog/r/binlog_stm_binlog.result
mysql-test/suite/binlog/r/binlog_stm_mix_innodb_myisam.result
mysql-test/suite/rpl/r/rpl_loaddata_map.result
mysql-test/suite/rpl/r/rpl_stm_log.result
mysql-test/suite/rpl/r/rpl_variables.result
mysql-test/suite/rpl/t/disabled.def
mysql-test/suite/rpl/t/rpl_incident.test
mysql-test/suite/rpl/t/rpl_loaddata_map.test
mysql-test/suite/rpl/t/rpl_trunc_temp.test
mysql-test/suite/rpl/t/rpl_variables.test
mysql-test/suite/rpl_ndb/t/rpl_truncate_7ndb_2.test
mysql-test/suite/rpl_ndb_big/r/rpl_row_basic_7ndb.result
mysql-test/t/constraints.test
mysql-test/t/disabled.def
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/hostname.cc
sql/mysql_priv.h
sql/mysqld.cc
sql/net_serv.cc
sql/sp_pcontext.cc
sql/sql_binlog.cc
sql/sql_cursor.cc
sql/sql_select.cc
sql/sql_select.h
sql/sql_yacc.yy
sql/structs.h
tests/mysql_client_test.c
=== modified file '.bzr-mysql/default.conf'
--- a/.bzr-mysql/default.conf 2008-08-18 05:43:50 +0000
+++ b/.bzr-mysql/default.conf 2008-09-04 18:18:11 +0000
@@ -1,4 +1,5 @@
[MYSQL]
+tree_location = bzr+ssh://bk-internal.mysql.com/bzrroot/server/mysql-6.0-opt/
post_commit_to = "commits@lists.mysql.com"
post_push_to = "commits@lists.mysql.com"
tree_name = "mysql-6.0-bka-preview"
=== modified file 'configure.in'
--- a/configure.in 2008-08-18 05:43:50 +0000
+++ b/configure.in 2008-09-04 18:18:11 +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/extra/binlog_tests/binlog_insert_delayed.test'
--- a/mysql-test/extra/binlog_tests/binlog_insert_delayed.test 2007-03-29 19:38:03 +0000
+++ b/mysql-test/extra/binlog_tests/binlog_insert_delayed.test 2008-07-23 16:56:39 +0000
@@ -1,17 +1,43 @@
-# Test of binlogging of INSERT_ID with INSERT DELAYED
+# ==== Purpose ====
+#
+# Verify that INSERT DELAYED in mixed or row mode writes events to the
+# binlog, and that AUTO_INCREMENT works correctly.
+#
+# ==== Method ====
+#
+# Insert both single and multiple rows into an autoincrement column,
+# both with specified value and with NULL.
+#
+# With INSERT DELAYED, the rows do not show up in the table
+# immediately, so we must do source include/wait_until_rows_count.inc
+# between any two INSERT DELAYED statements. Moreover, if mixed or
+# row-based logging is used, there is also a delay between when rows
+# show up in the table and when they show up in the binlog. To ensure
+# that the rows show up in the binlog, we call FLUSH TABLES, which
+# waits until the delayed_insert thread has finished.
+#
+# We cannot read the binlog after executing INSERT DELAYED statements
+# that insert multiple rows, because that is nondeterministic. More
+# precisely, rows may be written in batches to the binlog, where each
+# batch has one Table_map_log_event and one or more
+# Write_rows_log_event. The number of rows included in each batch is
+# nondeterministic.
+#
+# ==== Related bugs ====
+#
+# BUG#20627: INSERT DELAYED does not honour auto_increment_* variables
+# Bug in this test: BUG#38068: binlog_stm_binlog fails sporadically in pushbuild
+
+
create table t1 (a int not null auto_increment, primary key (a)) engine=myisam;
-# First, avoid BUG#20627:
-set @@session.auto_increment_increment=1, @@session.auto_increment_offset=1;
-# Verify that only one INSERT_ID event is binlogged.
-# Note, that because of WL#3368 mixed mode binlog records RBR events for the delayed
-let $table=t1;
-let $rows_inserted=11; # total number of inserted rows in this test
-insert delayed into t1 values (207);
-let $count=1;
-# use this macro instead of sleeps.
+let $table=t1;
+let $count=0;
+insert delayed into t1 values (207);
+inc $count;
--source include/wait_until_rows_count.inc
+
insert delayed into t1 values (null);
inc $count;
--source include/wait_until_rows_count.inc
@@ -20,9 +46,10 @@ insert delayed into t1 values (300);
inc $count;
--source include/wait_until_rows_count.inc
-# moving binlog check affront of multi-rows queries which work is indeterministic (extra table_maps)
-# todo: better check is to substitute SHOW BINLOG with reading from binlog, probably bug#19459 is in
-# the way
+# It is not enough to wait until all rows have been inserted into the
+# table. FLUSH TABLES ensures that they are in the binlog too. See
+# comment above.
+FLUSH TABLES;
source include/show_binlog_events.inc;
insert delayed into t1 values (null),(null),(null),(null);
@@ -33,8 +60,5 @@ insert delayed into t1 values (null),(nu
inc $count; inc $count; inc $count; inc $count;
--source include/wait_until_rows_count.inc
-#check this assertion about $count calculation
---echo $count == $rows_inserted
-
select * from t1;
drop table t1;
=== modified file 'mysql-test/include/show_binlog_events.inc'
--- a/mysql-test/include/show_binlog_events.inc 2008-04-01 15:13:57 +0000
+++ b/mysql-test/include/show_binlog_events.inc 2008-08-18 18:12:46 +0000
@@ -4,7 +4,7 @@ if (!$binlog_start)
{
let $binlog_start=107;
}
---replace_result $binlog_start <binlog_start>
+--replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR $binlog_start <binlog_start>
--replace_column 2 # 4 # 5 #
---replace_regex /\/\* xid=.* \*\//\/* XID *\// /table_id: [0-9]+/table_id: #/ /file_id=[0-9]+/file_id=#/
+--replace_regex /\/\* xid=.* \*\//\/* XID *\// /table_id: [0-9]+/table_id: #/ /file_id=[0-9]+/file_id=#/ /block_len=[0-9]+/block_len=#/
--eval show binlog events from $binlog_start
=== 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-08-16 05:15:49 +0000
+++ b/mysql-test/r/subselect.result 2008-09-04 18:18:11 +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-08-16 05:15:49 +0000
+++ b/mysql-test/r/subselect_no_mat.result 2008-09-04 18:18:11 +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-08-16 05:15:49 +0000
+++ b/mysql-test/r/subselect_no_opts.result 2008-09-04 18:18:11 +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-08-16 05:15:49 +0000
+++ b/mysql-test/r/subselect_no_semijoin.result 2008-09-04 18:18:11 +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,
=== added file 'mysql-test/r/subselect_nulls.result'
--- a/mysql-test/r/subselect_nulls.result 1970-01-01 00:00:00 +0000
+++ b/mysql-test/r/subselect_nulls.result 2008-08-18 12:45:37 +0000
@@ -0,0 +1,114 @@
+drop table if exists x1;
+drop table if exists x2;
+create table x1(k int primary key, d1 int, d2 int);
+create table x2(k int primary key, d1 int, d2 int);
+insert into x1 values
+(10, 10, 10),
+(20, 20, 20),
+(21, 20, null),
+(30, null, 30),
+(40, 40, 40);
+insert into x2 values
+(10, 10, 10),
+(20, 20, 20),
+(21, 20, null),
+(30, null, 30);
+select *
+from x1
+where (d1, d2) in (select d1, d2
+from x2);
+k d1 d2
+10 10 10
+20 20 20
+select *
+from x1
+where (d1, d2) in (select d1, d2
+from x2) is true;
+k d1 d2
+10 10 10
+20 20 20
+select *
+from x1
+where (d1, d2) in (select d1, d2
+from x2) is false;
+k d1 d2
+40 40 40
+select *
+from x1
+where (d1, d2) in (select d1, d2
+from x2) is unknown;
+k d1 d2
+21 20 NULL
+30 NULL 30
+select *
+from x1
+where d1 in (select d1
+from x2
+where x1.d2=x2.d2);
+k d1 d2
+10 10 10
+20 20 20
+select *
+from x1
+where d1 in (select d1
+from x2
+where x1.d2=x2.d2) is true;
+k d1 d2
+10 10 10
+20 20 20
+select *
+from x1
+where d1 in (select d1
+from x2
+where x1.d2=x2.d2) is false;
+k d1 d2
+21 20 NULL
+40 40 40
+select *
+from x1
+where d1 in (select d1
+from x2
+where x1.d2=x2.d2) is unknown;
+k d1 d2
+30 NULL 30
+select *
+from x1
+where 1 in (select 1
+from x2
+where x1.d1=x2.d1 and x1.d2=x2.d2);
+k d1 d2
+10 10 10
+20 20 20
+select *
+from x1
+where 1 in (select 1
+from x2
+where x1.d1=x2.d1 and x1.d2=x2.d2) is true;
+k d1 d2
+10 10 10
+20 20 20
+select *
+from x1
+where 1 in (select 1
+from x2
+where x1.d1=x2.d1 and x1.d2=x2.d2) is false;
+k d1 d2
+21 20 NULL
+30 NULL 30
+40 40 40
+select *
+from x1
+where 1 in (select 1
+from x2
+where x1.d1=x2.d1 and x1.d2=x2.d2) is unknown;
+k d1 d2
+select *
+from x1
+where exists (select *
+from x2
+where x1.d1=x2.d1 and x1.d2=x2.d2);
+k d1 d2
+10 10 10
+20 20 20
+drop table x1;
+drop table x2;
=== modified file 'mysql-test/r/subselect_sj.result'
--- a/mysql-test/r/subselect_sj.result 2008-07-22 04:54:18 +0000
+++ b/mysql-test/r/subselect_sj.result 2008-09-04 18:18:11 +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-07-22 04:54:18 +0000
+++ b/mysql-test/r/subselect_sj2.result 2008-09-04 18:18:11 +0000
@@ -650,3 +650,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/suite/binlog/r/binlog_killed_simulate.result'
--- a/mysql-test/suite/binlog/r/binlog_killed_simulate.result 2008-01-29 13:43:41 +0000
+++ b/mysql-test/suite/binlog/r/binlog_killed_simulate.result 2008-07-29 15:36:13 +0000
@@ -18,7 +18,7 @@ load data infile '../std_data_ln/rpl_loa
ERROR 70100: Query execution was interrupted
show binlog events from <binlog_start>;
Log_name Pos Event_type Server_id End_log_pos Info
-master-bin.000001 # Begin_load_query # # ;file_id=#;block_len=12
+master-bin.000001 # Begin_load_query # # ;file_id=#;block_len=#
master-bin.000001 # Execute_load_query # # use `test`; load data infile '../std_data_ln/rpl_loaddata.dat' into table t2 /* will be "killed" in the middle */ ;file_id=#
select
(@a:=load_file("MYSQLTEST_VARDIR/tmp/binlog_killed_bug27571.binlog"))
=== modified file 'mysql-test/suite/binlog/r/binlog_row_binlog.result'
--- a/mysql-test/suite/binlog/r/binlog_row_binlog.result 2008-04-02 13:14:23 +0000
+++ b/mysql-test/suite/binlog/r/binlog_row_binlog.result 2008-08-18 18:12:46 +0000
@@ -1141,10 +1141,10 @@ master-bin.000001 # Delete_rows # # tabl
master-bin.000001 # Query # # use `mysql`; COMMIT
drop table t1,t2,t3,tt1;
create table t1 (a int not null auto_increment, primary key (a)) engine=myisam;
-set @@session.auto_increment_increment=1, @@session.auto_increment_offset=1;
insert delayed into t1 values (207);
insert delayed into t1 values (null);
insert delayed into t1 values (300);
+FLUSH TABLES;
show binlog events from <binlog_start>;
Log_name Pos Event_type Server_id End_log_pos Info
master-bin.000001 # Query # # use `test`; create table t1 (id tinyint auto_increment primary key)
@@ -1188,9 +1188,9 @@ master-bin.000001 # Query # # use `test`
master-bin.000001 # Table_map # # table_id: # (test.t1)
master-bin.000001 # Write_rows # # table_id: # flags: STMT_END_F
master-bin.000001 # Query # # use `test`; COMMIT
+master-bin.000001 # Query # # use `test`; FLUSH TABLES
insert delayed into t1 values (null),(null),(null),(null);
insert delayed into t1 values (null),(null),(400),(null);
-11 == 11
select * from t1;
a
207
=== modified file 'mysql-test/suite/binlog/r/binlog_row_mix_innodb_myisam.result'
--- a/mysql-test/suite/binlog/r/binlog_row_mix_innodb_myisam.result 2008-05-09 10:27:23 +0000
+++ b/mysql-test/suite/binlog/r/binlog_row_mix_innodb_myisam.result 2008-08-18 18:12:46 +0000
@@ -927,7 +927,7 @@ Log_name Pos Event_type Server_id End_lo
master-bin.000001 # Query # # use `test`; BEGIN
master-bin.000001 # Intvar # # INSERT_ID=10
master-bin.000001 # User var # # @`b`=_latin1 0x3135 COLLATE latin1_swedish_ci
-master-bin.000001 # Begin_load_query # # ;file_id=#;block_len=12
+master-bin.000001 # Begin_load_query # # ;file_id=#;block_len=#
master-bin.000001 # Intvar # # INSERT_ID=10
master-bin.000001 # User var # # @`b`=_latin1 0x3135 COLLATE latin1_swedish_ci
master-bin.000001 # Execute_load_query # # use `test`; load data infile '../std_data_ln/rpl_loaddata.dat' into table t4 (a, @b) set b= @b + bug27417(2) ;file_id=#
=== modified file 'mysql-test/suite/binlog/r/binlog_statement_insert_delayed.result'
--- a/mysql-test/suite/binlog/r/binlog_statement_insert_delayed.result 2007-06-27 12:28:02 +0000
+++ b/mysql-test/suite/binlog/r/binlog_statement_insert_delayed.result 2008-07-29 15:36:13 +0000
@@ -1,8 +1,8 @@
create table t1 (a int not null auto_increment, primary key (a)) engine=myisam;
-set @@session.auto_increment_increment=1, @@session.auto_increment_offset=1;
insert delayed into t1 values (207);
insert delayed into t1 values (null);
insert delayed into t1 values (300);
+FLUSH TABLES;
show binlog events from <binlog_start>;
Log_name Pos Event_type Server_id End_log_pos Info
master-bin.000001 # Query # # use `test`; create table t1 (a int not null auto_increment, primary key (a)) engine=myisam
@@ -10,9 +10,9 @@ master-bin.000001 # Query # # use `test`
master-bin.000001 # Intvar # # INSERT_ID=208
master-bin.000001 # Query # # use `test`; insert delayed into t1 values (null)
master-bin.000001 # Query # # use `test`; insert delayed into t1 values (300)
+master-bin.000001 # Query # # use `test`; FLUSH TABLES
insert delayed into t1 values (null),(null),(null),(null);
insert delayed into t1 values (null),(null),(400),(null);
-11 == 11
select * from t1;
a
207
=== modified file 'mysql-test/suite/binlog/r/binlog_stm_binlog.result'
--- a/mysql-test/suite/binlog/r/binlog_stm_binlog.result 2008-04-01 15:13:57 +0000
+++ b/mysql-test/suite/binlog/r/binlog_stm_binlog.result 2008-08-18 18:12:46 +0000
@@ -629,10 +629,10 @@ master-bin.000001 # Query # # use `mysql
master-bin.000001 # Query # # use `mysql`; DELETE FROM user WHERE host='localhost' AND user='@#@'
drop table t1,t2,t3,tt1;
create table t1 (a int not null auto_increment, primary key (a)) engine=myisam;
-set @@session.auto_increment_increment=1, @@session.auto_increment_offset=1;
insert delayed into t1 values (207);
insert delayed into t1 values (null);
insert delayed into t1 values (300);
+FLUSH TABLES;
show binlog events from <binlog_start>;
Log_name Pos Event_type Server_id End_log_pos Info
master-bin.000001 # Query # # use `test`; create table t1 (id tinyint auto_increment primary key)
@@ -660,9 +660,9 @@ master-bin.000001 # Query # # use `test`
master-bin.000001 # Table_map # # table_id: # (test.t1)
master-bin.000001 # Write_rows # # table_id: # flags: STMT_END_F
master-bin.000001 # Query # # use `test`; COMMIT
+master-bin.000001 # Query # # use `test`; FLUSH TABLES
insert delayed into t1 values (null),(null),(null),(null);
insert delayed into t1 values (null),(null),(400),(null);
-11 == 11
select * from t1;
a
207
=== modified file 'mysql-test/suite/binlog/r/binlog_stm_mix_innodb_myisam.result'
--- a/mysql-test/suite/binlog/r/binlog_stm_mix_innodb_myisam.result 2008-05-09 10:27:23 +0000
+++ b/mysql-test/suite/binlog/r/binlog_stm_mix_innodb_myisam.result 2008-08-18 18:12:46 +0000
@@ -624,7 +624,7 @@ show binlog events from <binlog_start>;
Log_name Pos Event_type Server_id End_log_pos Info
master-bin.000001 # Query # # use `test`; BEGIN
master-bin.000001 # Intvar # # INSERT_ID=10
-master-bin.000001 # Begin_load_query # # ;file_id=#;block_len=12
+master-bin.000001 # Begin_load_query # # ;file_id=#;block_len=#
master-bin.000001 # Intvar # # INSERT_ID=10
master-bin.000001 # Execute_load_query # # use `test`; load data infile '../std_data_ln/rpl_loaddata.dat' into table t4 (a, @b) set b= @b + bug27417(2) ;file_id=#
master-bin.000001 # Query # # use `test`; ROLLBACK
@@ -859,7 +859,7 @@ Log_name Pos Event_type Server_id End_lo
master-bin.000001 # Query # # use `test`; BEGIN
master-bin.000001 # Intvar # # INSERT_ID=10
master-bin.000001 # User var # # @`b`=_latin1 0x3135 COLLATE latin1_swedish_ci
-master-bin.000001 # Begin_load_query # # ;file_id=#;block_len=12
+master-bin.000001 # Begin_load_query # # ;file_id=#;block_len=#
master-bin.000001 # Intvar # # INSERT_ID=10
master-bin.000001 # User var # # @`b`=_latin1 0x3135 COLLATE latin1_swedish_ci
master-bin.000001 # Execute_load_query # # use `test`; load data infile '../std_data_ln/rpl_loaddata.dat' into table t4 (a, @b) set b= @b + bug27417(2) ;file_id=#
=== modified file 'mysql-test/suite/rpl/r/rpl_loaddata_map.result'
--- a/mysql-test/suite/rpl/r/rpl_loaddata_map.result 2008-02-07 06:33:07 +0000
+++ b/mysql-test/suite/rpl/r/rpl_loaddata_map.result 2008-08-18 18:12:46 +0000
@@ -4,6 +4,8 @@ reset master;
reset slave;
drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t9;
start slave;
+==== Create a big file ====
+==== Load our big file into a table ====
create table t2 (id int not null primary key auto_increment);
select @@session.read_buffer_size - @@session.max_allowed_packet > 0 ;
@@session.read_buffer_size - @@session.max_allowed_packet > 0
@@ -12,15 +14,18 @@ load data infile 'MYSQLTEST_VARDIR/tmp/b
select count(*) from t2 /* 5 000 */;
count(*)
5000
-show binlog events in 'master-bin.000002' from <binlog_start>;
+show binlog events from <binlog_start>;
Log_name Pos Event_type Server_id End_log_pos Info
-master-bin.000002 # Query # # use `test`; create table t2 (id int not null primary key auto_increment)
-master-bin.000002 # Begin_load_query # # ;file_id=#;block_len=#
-master-bin.000002 # Append_block # # ;file_id=#;block_len=#
-master-bin.000002 # Append_block # # ;file_id=#;block_len=#
-master-bin.000002 # Execute_load_query # # use `test`; load data infile 'MYSQLTEST_VARDIR/tmp/bug30435_5k.txt' into table t2 ;file_id=#
+master-bin.000001 # Query # # use `test`; create table t2 (id int not null primary key auto_increment)
+master-bin.000001 # Begin_load_query # # ;file_id=#;block_len=#
+master-bin.000001 # Append_block # # ;file_id=#;block_len=#
+master-bin.000001 # Append_block # # ;file_id=#;block_len=#
+master-bin.000001 # Execute_load_query # # use `test`; load data infile 'MYSQLTEST_VARDIR/tmp/bug30435_5k.txt' into table t2 ;file_id=#
+==== Verify results on slave ====
+[on slave]
select count(*) from t2 /* 5 000 */;
count(*)
5000
-drop table t1, t2;
-end of the tests
+==== Clean up ====
+[on master]
+drop table t2;
=== modified file 'mysql-test/suite/rpl/r/rpl_stm_log.result'
--- a/mysql-test/suite/rpl/r/rpl_stm_log.result 2008-02-12 10:27:52 +0000
+++ b/mysql-test/suite/rpl/r/rpl_stm_log.result 2008-08-18 18:12:46 +0000
@@ -196,7 +196,7 @@ master-bin.000001 # Intvar # # INSERT_ID
master-bin.000001 # Query # # use `test`; insert into t1 values (NULL)
master-bin.000001 # Query # # use `test`; drop table t1
master-bin.000001 # Query # # use `test`; create table t1 (word char(20) not null)ENGINE=MyISAM
-master-bin.000001 # Begin_load_query # # ;file_id=#;block_len=581
+master-bin.000001 # Begin_load_query # # ;file_id=#;block_len=#
master-bin.000001 # Execute_load_query # # use `test`; load data infile '../std_data_ln/words.dat' into table t1 ignore 1 lines ;file_id=#
master-bin.000001 # Rotate # # master-bin.000002;pos=4
show binlog events in 'master-bin.000002';
=== modified file 'mysql-test/suite/rpl/r/rpl_variables.result'
--- a/mysql-test/suite/rpl/r/rpl_variables.result 2008-03-07 12:59:36 +0000
+++ b/mysql-test/suite/rpl/r/rpl_variables.result 2008-07-17 16:26:59 +0000
@@ -45,7 +45,7 @@ RESET SLAVE;
RESET MASTER;
START SLAVE;
[on slave]
-SET @@global.init_slave = 'ant';
+SET @@global.init_slave = 'SELECT 1';
[on master]
CREATE TABLE tstmt (id INT AUTO_INCREMENT PRIMARY KEY,
truth BOOLEAN,
@@ -66,9 +66,9 @@ SET @@global.sync_binlog = 2000000;
INSERT INTO tstmt(num) VALUES (@@global.sync_binlog);
SET @@global.sync_binlog = 3000000;
INSERT INTO tstmt(num) VALUES (@@global.sync_binlog);
-SET @@global.init_slave = 'bison';
+SET @@global.init_slave = 'SELECT 2';
INSERT INTO tstmt(text) VALUES (@@global.init_slave);
-SET @@global.init_slave = 'cat';
+SET @@global.init_slave = 'SELECT 3';
INSERT INTO tstmt(text) VALUES (@@global.init_slave);
SET @@global.slave_exec_mode = 'IDEMPOTENT';
INSERT INTO tstmt(text) VALUES (@@global.slave_exec_mode);
@@ -140,9 +140,9 @@ INSERT INTO tproc(num) VALUES (@@global.
SET @@global.sync_binlog = 3000000;
INSERT INTO tproc(num) VALUES (@@global.sync_binlog);
# string
-SET @@global.init_slave = 'bison';
+SET @@global.init_slave = 'SELECT 2';
INSERT INTO tproc(text) VALUES (@@global.init_slave);
-SET @@global.init_slave = 'cat';
+SET @@global.init_slave = 'SELECT 3';
INSERT INTO tproc(text) VALUES (@@global.init_slave);
# enumeration
SET @@global.slave_exec_mode = 'IDEMPOTENT';
@@ -226,9 +226,9 @@ INSERT INTO tfunc(num) VALUES (@@global.
SET @@global.sync_binlog = 3000000;
INSERT INTO tfunc(num) VALUES (@@global.sync_binlog);
# string
-SET @@global.init_slave = 'bison';
+SET @@global.init_slave = 'SELECT 2';
INSERT INTO tfunc(text) VALUES (@@global.init_slave);
-SET @@global.init_slave = 'cat';
+SET @@global.init_slave = 'SELECT 3';
INSERT INTO tfunc(text) VALUES (@@global.init_slave);
# enumeration
SET @@global.slave_exec_mode = 'IDEMPOTENT';
@@ -316,9 +316,9 @@ INSERT INTO ttrig(num) VALUES (@@global.
SET @@global.sync_binlog = 3000000;
INSERT INTO ttrig(num) VALUES (@@global.sync_binlog);
# string
-SET @@global.init_slave = 'bison';
+SET @@global.init_slave = 'SELECT 2';
INSERT INTO ttrig(text) VALUES (@@global.init_slave);
-SET @@global.init_slave = 'cat';
+SET @@global.init_slave = 'SELECT 3';
INSERT INTO ttrig(text) VALUES (@@global.init_slave);
# enumeration
SET @@global.slave_exec_mode = 'IDEMPOTENT';
@@ -395,9 +395,9 @@ PREPARE p5 FROM 'SET @@global.sync_binlo
PREPARE p6 FROM 'INSERT INTO tprep(num) VALUES (@@global.sync_binlog)';
PREPARE p7 FROM 'SET @@global.sync_binlog = 3000000';
PREPARE p8 FROM 'INSERT INTO tprep(num) VALUES (@@global.sync_binlog)';
-PREPARE p9 FROM 'SET @@global.init_slave = \'bison\'';
+PREPARE p9 FROM 'SET @@global.init_slave = \'SELECT 2\'';
PREPARE p10 FROM 'INSERT INTO tprep(text) VALUES (@@global.init_slave)';
-PREPARE p11 FROM 'SET @@global.init_slave = \'cat\'';
+PREPARE p11 FROM 'SET @@global.init_slave = \'SELECT 3\'';
PREPARE p12 FROM 'INSERT INTO tprep(text) VALUES (@@global.init_slave)';
PREPARE p13 FROM 'SET @@global.slave_exec_mode = \'IDEMPOTENT\'';
PREPARE p14 FROM 'INSERT INTO tprep(text) VALUES (@@global.slave_exec_mode)';
@@ -522,8 +522,8 @@ id truth num text
2 0 NULL NULL
3 NULL 2000000 NULL
4 NULL 3000000 NULL
-5 NULL NULL bison
-6 NULL NULL cat
+5 NULL NULL SELECT 2
+6 NULL NULL SELECT 3
7 NULL NULL IDEMPOTENT
8 NULL NULL STRICT
9 1 NULL NULL
=== modified file 'mysql-test/suite/rpl/t/disabled.def'
--- a/mysql-test/suite/rpl/t/disabled.def 2008-08-16 05:15:49 +0000
+++ b/mysql-test/suite/rpl/t/disabled.def 2008-09-04 18:18:11 +0000
@@ -30,8 +30,6 @@ rpl_row_basic_7ndb : BUG#33360 2
rpl_redirect : Failure is sporadic and and the test is superfluous (mats)
rpl_innodb_bug28430 : Failure on Solaris Bug #36793
rpl_server_id1 : Bug #36818 rpl_server_id1 fails expecting slave has stopped (azundris)
-rpl_row_basic_2myisam : Bug#37879 2008-07-14 alik Disabled to make 6.0 greaner (the test fails too often)
-rpl_row_basic_3innodb : Bug#37879 2008-07-14 alik Disabled to make 6.0 greaner (the test fails too often)
rpl_heartbeat : Bug#37714 2008-07-14 alik Disabled to make 6.0 greaner (the test fails too often)
rpl_idempotency : Bug#37767 2008-07-14 alik Disabled to make 6.0 greaner (the test fails too often)
=== modified file 'mysql-test/suite/rpl/t/rpl_incident.test'
--- a/mysql-test/suite/rpl/t/rpl_incident.test 2007-06-27 12:28:02 +0000
+++ b/mysql-test/suite/rpl/t/rpl_incident.test 2008-07-18 13:07:14 +0000
@@ -19,6 +19,37 @@ connection slave;
# The 4 should not be inserted into the table, since the incident log
# event should have stop the slave.
--echo **** On Slave ****
+#### BEGIN DEBUG INFO ADDED BY SVEN 2008-07-18 -- SEE BUG#38077 ####
+let $tables= query_get_value(SHOW TABLES, Tables_in_test, 1);
+if (`SELECT '$tables' != 't1'`)
+{
+ --echo **** TEST CASE BUG! PRINTING DEBUG INFO! ****
+ --echo **** Dear developer, if you see this in the output of a test
+ --echo **** case run, please add all the information below as a
+ --echo **** comment to BUG#38077. If it's a pushbuild failure, please
+ --echo **** include a link to the push page.
+ --echo **** Thank you! /Sven
+ SHOW BINLOG EVENTS;
+ --echo **** master binlog ****
+ --error 0,1
+ --exec $MYSQL_BINLOG --hexdump $MYSQLTEST_VARDIR/log/master-bin.000001
+ --echo **** slave binlog ****
+ --error 0,1
+ --exec $MYSQL_BINLOG --hexdump $MYSQLTEST_VARDIR/log/slave-bin.000001
+ --echo **** slave status ****
+ query_vertical SHOW SLAVE STATUS;
+ --echo **** slave's master status ****
+ SHOW MASTER STATUS;
+ --echo **** slave binlog events ****
+ --echo [on master]
+ connection master;
+ --echo **** master status ****
+ SHOW MASTER STATUS;
+ --echo **** master binlog events ****
+ SHOW BINLOG EVENTS;
+ exit;
+}
+#### END DEBUG INFO ####
SELECT * FROM t1;
--replace_result $MASTER_MYPORT MASTER_PORT
=== modified file 'mysql-test/suite/rpl/t/rpl_loaddata_map.test'
--- a/mysql-test/suite/rpl/t/rpl_loaddata_map.test 2008-02-05 14:30:44 +0000
+++ b/mysql-test/suite/rpl/t/rpl_loaddata_map.test 2008-08-18 18:12:46 +0000
@@ -1,7 +1,16 @@
+# ==== Purpose ====
#
# check replication of load data with the server parameters subjected to
# read_buffer_size > max_allowed_packet
#
+# ==== Implementation ====
+#
+# Insert many rows into t1, write t1 to file.
+# Load the file into t2.
+# See that t2 came out as expected on slave.
+#
+# ==== Related Bugs ====
+#
# BUG#30435 loading large LOAD DATA INFILE breaks slave with
# read_buffer_size set on master
# BUG#33413 show binlog events fails if binlog has event size of close
@@ -9,10 +18,17 @@
source include/have_binlog_format_mixed_or_statement.inc;
source include/master-slave.inc;
-source include/have_innodb.inc;
-source include/have_binlog_format_mixed_or_statement.inc;
+
+
+--echo ==== Create a big file ====
+
+# We turn off binlogging to avoid too much noise in the binlog. t1 is
+# just an auxiliary construction anyways, it is not needed on the
+# slave.
--disable_query_log
+SET @@sql_log_bin= 0;
+
let $rows= 5000;
create table t1 (id int not null primary key auto_increment);
@@ -22,10 +38,13 @@ while($rows)
dec $rows;
}
eval select * into outfile '$MYSQLTEST_VARDIR/tmp/bug30435_5k.txt' from t1;
-flush logs;
+
+DROP TABLE t1;
+SET @@sql_log_bin= 1;
--enable_query_log
-connection master;
+
+--echo ==== Load our big file into a table ====
create table t2 (id int not null primary key auto_increment);
select @@session.read_buffer_size - @@session.max_allowed_packet > 0 ;
@@ -34,21 +53,21 @@ select @@session.read_buffer_size - @@se
eval load data infile '$MYSQLTEST_VARDIR/tmp/bug30435_5k.txt' into table t2;
select count(*) from t2 /* 5 000 */;
-# the binglog will show fragmented Append_block events
---let $binlog_start=107
---replace_column 2 # 4 # 5 #
---replace_regex /\/\* xid=.* \*\//\/* XID *\// /file_id=[0-9]+/file_id=#/ /block_len=[0-9]+/block_len=#/
---replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR $binlog_start <binlog_start>
---eval show binlog events in 'master-bin.000002' from $binlog_start
+# the binlog will show fragmented Append_block events
+source include/show_binlog_events.inc;
+
+--echo ==== Verify results on slave ====
+--echo [on slave]
sync_slave_with_master;
-#connection slave;
select count(*) from t2 /* 5 000 */;
+
+--echo ==== Clean up ====
+
+--echo [on master]
connection master;
-drop table t1, t2;
+drop table t2;
sync_slave_with_master;
remove_file $MYSQLTEST_VARDIR/tmp/bug30435_5k.txt;
-
---echo end of the tests
=== removed file 'mysql-test/suite/rpl/t/rpl_row_err_daisychain-master.opt'
--- a/mysql-test/suite/rpl/t/rpl_row_err_daisychain-master.opt 2007-06-27 12:28:02 +0000
+++ b/mysql-test/suite/rpl/t/rpl_row_err_daisychain-master.opt 1970-01-01 00:00:00 +0000
@@ -1 +0,0 @@
---binlog-format=row
=== removed file 'mysql-test/suite/rpl/t/rpl_row_err_daisychain-slave.opt'
--- a/mysql-test/suite/rpl/t/rpl_row_err_daisychain-slave.opt 2007-06-27 12:28:02 +0000
+++ b/mysql-test/suite/rpl/t/rpl_row_err_daisychain-slave.opt 1970-01-01 00:00:00 +0000
@@ -1 +0,0 @@
---binlog-format=statement --log-slave-updates
=== modified file 'mysql-test/suite/rpl/t/rpl_trunc_temp.test'
--- a/mysql-test/suite/rpl/t/rpl_trunc_temp.test 2008-02-28 11:36:14 +0000
+++ b/mysql-test/suite/rpl/t/rpl_trunc_temp.test 2008-07-23 10:13:55 +0000
@@ -1,13 +1,30 @@
-# Requires statement logging
+# ==== Purpose ====
+#
+# Verify that Slave_open_temp_tables is increased when a temporary
+# table is opened on the slave, and decreased when a temporary table
+# is closed on the slave, and that it is preserved during 'DELETE FROM
+# table' and 'TRUNCATE table'.
+#
+# ==== Method ====
+#
+# Create a temporary table on master, insert rows, and try:
+# - delete rows from the table
+# - truncate the table
+# - drop the table
+#
+# ==== Related bugs ====
+#
+# BUG#17137 Running "truncate table" on temporary table leaves the table open on a slave
+#
+# Bug in this test: BUG#37493: rpl_trunc_temp.test nondeterministic
+
+
+# Requires statement-based logging since temporary tables are not
+# logged in row-based logging
-- source include/have_binlog_format_mixed_or_statement.inc
source include/master-slave.inc;
-#
-# Bug#17137 Running "truncate table" on temporary table
-# leaves the table open on a slave
-#
-
create temporary table t1 (n int);
insert into t1 values(1);
sync_slave_with_master;
@@ -28,13 +45,10 @@ show status like 'Slave_open_temp_tables
# Disconnect the master, temp table on slave should dissapear
disconnect master;
-connection master1;
-# Wait until drop of temp tables appers in binlog
+connection slave;
+
+# Wait until drop of temp tables appers in slave's binlog
let $wait_binlog_event= DROP;
source include/wait_for_binlog_event.inc;
-connection slave;
show status like 'Slave_open_temp_tables';
-
-
-# End of 4.1 tests
=== modified file 'mysql-test/suite/rpl/t/rpl_variables.test'
--- a/mysql-test/suite/rpl/t/rpl_variables.test 2008-03-07 12:59:36 +0000
+++ b/mysql-test/suite/rpl/t/rpl_variables.test 2008-07-17 16:26:59 +0000
@@ -117,7 +117,7 @@ source include/reset_master_and_slave.in
# above, but can't because it affects how the slave works.
--echo [on slave]
connection slave;
-SET @@global.init_slave = 'ant';
+SET @@global.init_slave = 'SELECT 1';
--echo [on master]
@@ -154,9 +154,9 @@ SET @@global.sync_binlog = 3000000;
INSERT INTO tstmt(num) VALUES (@@global.sync_binlog);
# string
-SET @@global.init_slave = 'bison';
+SET @@global.init_slave = 'SELECT 2';
INSERT INTO tstmt(text) VALUES (@@global.init_slave);
-SET @@global.init_slave = 'cat';
+SET @@global.init_slave = 'SELECT 3';
INSERT INTO tstmt(text) VALUES (@@global.init_slave);
# enumeration
@@ -258,9 +258,9 @@ BEGIN
INSERT INTO tproc(num) VALUES (@@global.sync_binlog);
# string
- SET @@global.init_slave = 'bison';
+ SET @@global.init_slave = 'SELECT 2';
INSERT INTO tproc(text) VALUES (@@global.init_slave);
- SET @@global.init_slave = 'cat';
+ SET @@global.init_slave = 'SELECT 3';
INSERT INTO tproc(text) VALUES (@@global.init_slave);
# enumeration
@@ -367,9 +367,9 @@ BEGIN
INSERT INTO tfunc(num) VALUES (@@global.sync_binlog);
# string
- SET @@global.init_slave = 'bison';
+ SET @@global.init_slave = 'SELECT 2';
INSERT INTO tfunc(text) VALUES (@@global.init_slave);
- SET @@global.init_slave = 'cat';
+ SET @@global.init_slave = 'SELECT 3';
INSERT INTO tfunc(text) VALUES (@@global.init_slave);
# enumeration
@@ -478,9 +478,9 @@ BEGIN
INSERT INTO ttrig(num) VALUES (@@global.sync_binlog);
# string
- SET @@global.init_slave = 'bison';
+ SET @@global.init_slave = 'SELECT 2';
INSERT INTO ttrig(text) VALUES (@@global.init_slave);
- SET @@global.init_slave = 'cat';
+ SET @@global.init_slave = 'SELECT 3';
INSERT INTO ttrig(text) VALUES (@@global.init_slave);
# enumeration
@@ -581,9 +581,9 @@ PREPARE p7 FROM 'SET @@global.sync_binlo
PREPARE p8 FROM 'INSERT INTO tprep(num) VALUES (@@global.sync_binlog)';
# string
-PREPARE p9 FROM 'SET @@global.init_slave = \'bison\'';
+PREPARE p9 FROM 'SET @@global.init_slave = \'SELECT 2\'';
PREPARE p10 FROM 'INSERT INTO tprep(text) VALUES (@@global.init_slave)';
-PREPARE p11 FROM 'SET @@global.init_slave = \'cat\'';
+PREPARE p11 FROM 'SET @@global.init_slave = \'SELECT 3\'';
PREPARE p12 FROM 'INSERT INTO tprep(text) VALUES (@@global.init_slave)';
# enumeration
=== removed file 'mysql-test/suite/rpl_ndb/t/rpl_truncate_7ndb_2-master.opt'
--- a/mysql-test/suite/rpl_ndb/t/rpl_truncate_7ndb_2-master.opt 2008-06-26 05:18:28 +0000
+++ b/mysql-test/suite/rpl_ndb/t/rpl_truncate_7ndb_2-master.opt 1970-01-01 00:00:00 +0000
@@ -1 +0,0 @@
---binlog-format=mixed
=== modified file 'mysql-test/suite/rpl_ndb/t/rpl_truncate_7ndb_2.test'
--- a/mysql-test/suite/rpl_ndb/t/rpl_truncate_7ndb_2.test 2008-06-26 05:18:28 +0000
+++ b/mysql-test/suite/rpl_ndb/t/rpl_truncate_7ndb_2.test 2008-08-18 18:12:46 +0000
@@ -1,6 +1,6 @@
# Same test as rpl_truncate_7ndb.test, but with mixed mode
# This is marked with 'big_test' just because the rpl_truncate_7ndb test is
# so slow...
-
+--source include/have_binlog_format_mixed.inc
--source include/big_test.inc
--source suite/rpl_ndb/t/rpl_truncate_7ndb.test
=== modified file 'mysql-test/suite/rpl_ndb_big/r/rpl_row_basic_7ndb.result'
--- a/mysql-test/suite/rpl_ndb_big/r/rpl_row_basic_7ndb.result 2008-08-16 05:15:49 +0000
+++ b/mysql-test/suite/rpl_ndb_big/r/rpl_row_basic_7ndb.result 2008-09-04 18:18:11 +0000
@@ -488,7 +488,7 @@ Comparing tables master:test.t2 and slav
INSERT INTO t3 VALUES (1, "", 1);
INSERT INTO t3 VALUES (2, repeat(_utf8'a', 128), 2);
Last_SQL_Error
-Table definition on master and slave does not match: Column 1 size mismatch - master has size 512, test.t3 on slave has size 65. Master's column size should be <= the slave's column size.
+Table definition on master and slave does not match: Column 1 size mismatch - master has size 384, test.t3 on slave has size 49. Master's column size should be <= the slave's column size.
RESET MASTER;
STOP SLAVE;
RESET SLAVE;
@@ -501,7 +501,7 @@ Comparing tables master:test.t4 and slav
INSERT INTO t5 VALUES (1, "", 1);
INSERT INTO t5 VALUES (2, repeat(_utf8'a', 255), 2);
Last_SQL_Error
-Table definition on master and slave does not match: Column 1 size mismatch - master has size 1020, test.t5 on slave has size 65. Master's column size should be <= the slave's column size.
+Table definition on master and slave does not match: Column 1 size mismatch - master has size 765, test.t5 on slave has size 49. Master's column size should be <= the slave's column size.
RESET MASTER;
STOP SLAVE;
RESET SLAVE;
@@ -510,7 +510,7 @@ START SLAVE;
INSERT INTO t6 VALUES (1, "", 1);
INSERT INTO t6 VALUES (2, repeat(_utf8'a', 255), 2);
Last_SQL_Error
-Table definition on master and slave does not match: Column 1 size mismatch - master has size 1020, test.t6 on slave has size 513. Master's column size should be <= the slave's column size.
+Table definition on master and slave does not match: Column 1 size mismatch - master has size 765, test.t6 on slave has size 385. Master's column size should be <= the slave's column size.
RESET MASTER;
STOP SLAVE;
RESET SLAVE;
=== 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/disabled.def'
--- a/mysql-test/t/disabled.def 2008-08-18 05:43:50 +0000
+++ b/mysql-test/t/disabled.def 2008-09-04 18:18:11 +0000
@@ -22,7 +22,6 @@ csv_alter_table : Bug#33696 2008-01
query_cache_wlock_invalidate_func: Bug#35390 causes not deterministic results.
thread_cache_size_func: BUG#35988, BUG#36733 Due to not deterministic results
cast : Bug#35594 2008-03-27 main.cast fails on Windows2003-64
-subselect_notembedded : Bug#35803, Bug#33506 2008-Apr-03 subselect_notembedded crashes the server
status : Bug#37481 status.test fails randomly
maria-preload : Bug#35107 crashes
key_buffer_size_basic_64 : Bug #36522: Some tests of system variables have diffs on 64bit platorms
=== 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-08-16 05:15:49 +0000
+++ b/mysql-test/t/subselect.test 2008-09-04 18:18:11 +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"
=== added file 'mysql-test/t/subselect_nulls.test'
--- a/mysql-test/t/subselect_nulls.test 1970-01-01 00:00:00 +0000
+++ b/mysql-test/t/subselect_nulls.test 2008-08-18 12:45:37 +0000
@@ -0,0 +1,94 @@
+-- Initialize tables for the test
+
+--disable_warnings
+drop table if exists x1;
+drop table if exists x2;
+--enable_warnings
+
+create table x1(k int primary key, d1 int, d2 int);
+create table x2(k int primary key, d1 int, d2 int);
+
+insert into x1 values
+ (10, 10, 10),
+ (20, 20, 20),
+ (21, 20, null),
+ (30, null, 30),
+ (40, 40, 40);
+insert into x2 values
+ (10, 10, 10),
+ (20, 20, 20),
+ (21, 20, null),
+ (30, null, 30);
+
+-- Test various IN and EXISTS queries with NULL values and UNKNOWN
+-- Q1 T=(10, 20) U=(21,30) F=(40)
+select *
+from x1
+where (d1, d2) in (select d1, d2
+ from x2);
+select *
+from x1
+where (d1, d2) in (select d1, d2
+ from x2) is true;
+select *
+from x1
+where (d1, d2) in (select d1, d2
+ from x2) is false;
+select *
+from x1
+where (d1, d2) in (select d1, d2
+ from x2) is unknown;
+
+-- Q2 T=(10, 20) U=(30) F=(21, 40)
+select *
+from x1
+where d1 in (select d1
+ from x2
+ where x1.d2=x2.d2);
+select *
+from x1
+where d1 in (select d1
+ from x2
+ where x1.d2=x2.d2) is true;
+select *
+from x1
+where d1 in (select d1
+ from x2
+ where x1.d2=x2.d2) is false;
+select *
+from x1
+where d1 in (select d1
+ from x2
+ where x1.d2=x2.d2) is unknown;
+
+-- Q3 T=(10, 20) U=() F=(21, 30, 40)
+select *
+from x1
+where 1 in (select 1
+ from x2
+ where x1.d1=x2.d1 and x1.d2=x2.d2);
+select *
+from x1
+where 1 in (select 1
+ from x2
+ where x1.d1=x2.d1 and x1.d2=x2.d2) is true;
+select *
+from x1
+where 1 in (select 1
+ from x2
+ where x1.d1=x2.d1 and x1.d2=x2.d2) is false;
+select *
+from x1
+where 1 in (select 1
+ from x2
+ where x1.d1=x2.d1 and x1.d2=x2.d2) is unknown;
+
+-- Q4 T=(10, 20) F=(21, 30, 40)
+select *
+from x1
+where exists (select *
+ from x2
+ where x1.d1=x2.d1 and x1.d2=x2.d2);
+
+drop table x1;
+drop table x2;
=== 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 John���s','ATG',24000), (64,'Dubai','ARE',669181), (65,'Abu 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��evir','AZE',93900), (148,'Nassau','BHS',172000),
+(129,'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�l]','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,'�umen','BGR',94686),
-(553,'George Town','CYM',19600), (584,'San Jos�','CRI',339131), (1523,'Wien','AUT',1608144),
+(546,'Sliven','BGR',105530), (547,'Dobric','BGR',100399), (548,''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���bec','CAN',167264), (1833,'York','CAN',154980), (1834,'Richmond','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���an','CHN',2761400),
(1902,'Dalian','CHN',2697000), (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',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���anshan','CHN',305421), (2006,'Foshan','CHN',303160),
(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,'Rui�an','CHN',156468), (2129,'Zhoushan','CHN',156317),
+(2127,'Wanxian','CHN',156823), (2128,'Rui�56317),
(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,'Ji148362),
(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���an','CHN',144248),
(2145,'Shuangcheng','CHN',142659), (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��, (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��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����� 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���no','BLR',97000);
insert into t1 (Code, Name, Continent) 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-08-18 05:43:50 +0000
+++ b/sql/handler.cc 2008-09-04 18:18:11 +0000
@@ -4622,7 +4622,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;
@@ -4634,7 +4634,6 @@ bool DsMrr_impl::key_uses_partial_cols(u
return FALSE;
}
-
/**
DS-MRR Internals: Choose between Default MRR implementation and DS-MRR
@@ -4668,7 +4667,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-08-18 05:43:50 +0000
+++ b/sql/handler.h 2008-09-04 18:18:11 +0000
@@ -2348,6 +2348,7 @@ private:
};
+bool key_uses_partial_cols(TABLE *table, uint keyno);
/**
A Disk-Sweep MRR interface implementation
@@ -2408,7 +2409,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/hostname.cc'
--- a/sql/hostname.cc 2008-07-08 16:01:41 +0000
+++ b/sql/hostname.cc 2008-08-23 23:34:28 +0000
@@ -49,7 +49,6 @@ public:
};
static hash_filo *hostname_cache;
-static pthread_mutex_t LOCK_hostname;
void hostname_cache_refresh()
{
@@ -66,7 +65,6 @@ bool hostname_cache_init()
&my_charset_bin)))
return 1;
hostname_cache->clear();
- (void) pthread_mutex_init(&LOCK_hostname,MY_MUTEX_INIT_SLOW);
return 0;
}
@@ -75,7 +73,6 @@ void hostname_cache_free()
{
if (hostname_cache)
{
- (void) pthread_mutex_destroy(&LOCK_hostname);
delete hostname_cache;
hostname_cache= 0;
}
=== modified file 'sql/mysql_priv.h'
--- a/sql/mysql_priv.h 2008-08-18 05:43:50 +0000
+++ b/sql/mysql_priv.h 2008-09-04 18:18:11 +0000
@@ -570,6 +570,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
/*
@@ -2066,14 +2067,14 @@ extern FILE *bootstrap_file;
extern int bootstrap_error;
extern FILE *stderror_file;
extern pthread_key(MEM_ROOT**,THR_MALLOC);
-extern pthread_mutex_t LOCK_mysql_create_db,LOCK_Acl,LOCK_open, LOCK_lock_db,
+extern pthread_mutex_t LOCK_mysql_create_db, LOCK_open, LOCK_lock_db,
LOCK_thread_count,LOCK_mapped_file,LOCK_user_locks, LOCK_status,
LOCK_error_log, LOCK_delayed_insert, LOCK_uuid_short,
LOCK_delayed_status, LOCK_delayed_create, LOCK_crypt, LOCK_timezone,
LOCK_slave_list, LOCK_active_mi, LOCK_manager, LOCK_global_read_lock,
LOCK_global_system_variables, LOCK_user_conn,
LOCK_prepared_stmt_count,
- LOCK_bytes_sent, LOCK_bytes_received, LOCK_connection_count;
+ LOCK_connection_count;
#ifdef HAVE_OPENSSL
extern pthread_mutex_t LOCK_des_key_file;
#endif
=== modified file 'sql/mysqld.cc'
--- a/sql/mysqld.cc 2008-08-18 05:43:50 +0000
+++ b/sql/mysqld.cc 2008-09-04 18:18:11 +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,"",
@@ -676,11 +677,11 @@ SHOW_COMP_OPTION have_community_features
pthread_key(MEM_ROOT**,THR_MALLOC);
pthread_key(THD*, THR_THD);
-pthread_mutex_t LOCK_mysql_create_db, LOCK_Acl, LOCK_open, LOCK_thread_count,
+pthread_mutex_t LOCK_mysql_create_db, LOCK_open, LOCK_thread_count,
LOCK_mapped_file, LOCK_status, LOCK_global_read_lock,
LOCK_error_log,
LOCK_delayed_insert, LOCK_delayed_status, LOCK_delayed_create,
- LOCK_crypt, LOCK_bytes_sent, LOCK_bytes_received,
+ LOCK_crypt,
LOCK_global_system_variables,
LOCK_user_conn, LOCK_slave_list, LOCK_active_mi,
LOCK_connection_count;
@@ -1445,7 +1446,6 @@ static void clean_up_mutexes()
{
(void) pthread_mutex_destroy(&LOCK_mysql_create_db);
(void) pthread_mutex_destroy(&LOCK_lock_db);
- (void) pthread_mutex_destroy(&LOCK_Acl);
(void) rwlock_destroy(&LOCK_grant);
(void) pthread_mutex_destroy(&LOCK_open);
(void) pthread_mutex_destroy(&LOCK_thread_count);
@@ -1457,8 +1457,6 @@ static void clean_up_mutexes()
(void) pthread_mutex_destroy(&LOCK_delayed_create);
(void) pthread_mutex_destroy(&LOCK_manager);
(void) pthread_mutex_destroy(&LOCK_crypt);
- (void) pthread_mutex_destroy(&LOCK_bytes_sent);
- (void) pthread_mutex_destroy(&LOCK_bytes_received);
(void) pthread_mutex_destroy(&LOCK_user_conn);
(void) pthread_mutex_destroy(&LOCK_connection_count);
Events::destroy_mutexes();
@@ -3652,7 +3650,6 @@ static int init_thread_environment()
{
(void) pthread_mutex_init(&LOCK_mysql_create_db,MY_MUTEX_INIT_SLOW);
(void) pthread_mutex_init(&LOCK_lock_db,MY_MUTEX_INIT_SLOW);
- (void) pthread_mutex_init(&LOCK_Acl,MY_MUTEX_INIT_SLOW);
(void) pthread_mutex_init(&LOCK_open, NULL);
(void) pthread_mutex_init(&LOCK_thread_count,MY_MUTEX_INIT_FAST);
(void) pthread_mutex_init(&LOCK_mapped_file,MY_MUTEX_INIT_SLOW);
@@ -3663,8 +3660,6 @@ static int init_thread_environment()
(void) pthread_mutex_init(&LOCK_delayed_create,MY_MUTEX_INIT_SLOW);
(void) pthread_mutex_init(&LOCK_manager,MY_MUTEX_INIT_FAST);
(void) pthread_mutex_init(&LOCK_crypt,MY_MUTEX_INIT_FAST);
- (void) pthread_mutex_init(&LOCK_bytes_sent,MY_MUTEX_INIT_FAST);
- (void) pthread_mutex_init(&LOCK_bytes_received,MY_MUTEX_INIT_FAST);
(void) pthread_mutex_init(&LOCK_user_conn, MY_MUTEX_INIT_FAST);
(void) pthread_mutex_init(&LOCK_active_mi, MY_MUTEX_INIT_FAST);
(void) pthread_mutex_init(&LOCK_global_system_variables, MY_MUTEX_INIT_FAST);
=== modified file 'sql/net_serv.cc'
--- a/sql/net_serv.cc 2008-05-29 15:44:11 +0000
+++ b/sql/net_serv.cc 2008-08-23 23:32:16 +0000
@@ -90,7 +90,6 @@ void sql_print_error(const char *format,
*/
extern uint test_flags;
extern ulong bytes_sent, bytes_received, net_big_packet_count;
-extern pthread_mutex_t LOCK_bytes_sent , LOCK_bytes_received;
#ifndef MYSQL_INSTANCE_MANAGER
#ifdef HAVE_QUERY_CACHE
#define USE_QUERY_CACHE
=== 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_binlog.cc'
--- a/sql/sql_binlog.cc 2008-05-23 13:54:03 +0000
+++ b/sql/sql_binlog.cc 2008-08-18 18:12:46 +0000
@@ -208,6 +208,7 @@ void mysql_client_binlog_statement(THD*
#if !defined(MYSQL_CLIENT) && defined(HAVE_REPLICATION)
if (apply_event_and_update_pos(ev, thd, thd->rli_fake, FALSE))
{
+ delete ev;
/*
TODO: Maybe a better error message since the BINLOG statement
now contains several events.
=== modified file 'sql/sql_cursor.cc'
--- a/sql/sql_cursor.cc 2008-08-11 13:45:45 +0000
+++ b/sql/sql_cursor.cc 2008-08-18 18:12:46 +0000
@@ -155,6 +155,7 @@ int mysql_open_cursor(THD *thd, uint fla
if (! (sensitive_cursor= new (thd->mem_root) Sensitive_cursor(thd, result)))
{
delete result_materialize;
+ result_materialize= NULL;
return 1;
}
@@ -212,6 +213,7 @@ int mysql_open_cursor(THD *thd, uint fla
if ((rc= materialized_cursor->open(0)))
{
delete materialized_cursor;
+ materialized_cursor= NULL;
goto err_open;
}
=== modified file 'sql/sql_select.cc'
--- a/sql/sql_select.cc 2008-08-18 05:43:50 +0000
+++ b/sql/sql_select.cc 2008-09-04 18:18:11 +0000
@@ -167,14 +167,12 @@ 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 bool test_if_use_dynamic_range_scan(JOIN_TAB *join_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);
@@ -1054,6 +1052,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),
@@ -1061,7 +1065,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 */
/*
@@ -1085,7 +1089,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;
@@ -1103,7 +1107,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;
}
@@ -1173,7 +1177,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))
@@ -1182,7 +1186,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.
@@ -1198,7 +1204,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
{
@@ -1212,19 +1218,28 @@ 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;
bool first_match_only= FALSE;
- 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;
@@ -5424,9 +5439,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;
@@ -5434,14 +5456,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
@@ -5454,12 +5475,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 */
@@ -5480,6 +5504,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 */
{
@@ -5527,6 +5554,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);
}
}
@@ -5545,7 +5573,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
*/
@@ -5562,49 +5590,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)
- {
- 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))
+ 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))
+ {
+ /* 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 */
}
}
@@ -5859,7 +5904,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)
@@ -5876,8 +5921,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;
}
@@ -5916,6 +5962,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
@@ -5955,6 +6002,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
{
@@ -6006,7 +6058,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;
}
}
@@ -6016,7 +6069,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 &&
@@ -6586,13 +6640,6 @@ best_extension_by_limited_search(JOIN
double current_record_count, current_read_time;
advance_sj_state(remaining_tables, s);
- /*
- psergey-insideout-todo:
- when best_access_path() detects it could do an InsideOut scan or
- some other scan, have it return an insideout scan and a flag that
- requests to "fork" this loop iteration. (Q: how does that behave
- when the depth is insufficient??)
- */
/* Find the best access method from 's' to the current partial plan */
best_access_path(join, s, thd, remaining_tables, idx,
record_count, read_time);
@@ -6960,7 +7007,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;
@@ -6969,6 +7016,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;
}
@@ -7265,6 +7313,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;
@@ -7533,7 +7582,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,
@@ -7780,9 +7837,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;
}
@@ -8429,9 +8486,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) {
@@ -8497,8 +8553,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
{
@@ -12478,9 +12533,11 @@ err:
SYNOPSIS
create_duplicate_weedout_tmp_table()
- thd
- uniq_tuple_length_arg
- SJ_TMP_TABLE
+ thd Thread handle
+ uniq_tuple_length_arg Length of the table's column
+ sjtbl Update sjtbl->[start_]recinfo values which
+ will be needed if we'll need to convert the
+ created temptable from HEAP to MyISAM/Maria.
DESCRIPTION
Create a temporary table to weed out duplicate rowid combinations. The
@@ -13852,9 +13909,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);
}
@@ -13875,7 +13948,14 @@ sub_select(JOIN *join,JOIN_TAB *join_tab
SYNPOSIS
do_sj_dups_weedout()
-
+ thd Thread handle
+ sjtbl Duplicate weedout table
+
+ DESCRIPTION
+ Try storing current record combination of outer tables (i.e. their
+ rowids) in the temporary table. This records the fact that we've seen
+ this record combination and also tells us if we've seen it before.
+
RETURN
-1 Error
1 The row combination is a duplicate (discard it)
@@ -13939,7 +14019,6 @@ int do_sj_dups_weedout(THD *thd, SJ_TMP_
sjtbl->start_recinfo,
&sjtbl->recinfo, error, 1))
return -1;
- //return (error == HA_ERR_FOUND_DUPP_KEY || error== HA_ERR_FOUND_DUPP_UNIQUE) ? 1: -1;
return 1;
}
return 0;
@@ -14503,37 +14582,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)
@@ -14634,17 +14682,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);
@@ -14659,32 +14697,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-08-16 06:08:18 +0000
+++ b/sql/sql_select.h 2008-09-04 18:18:11 +0000
@@ -237,15 +237,20 @@ 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;
+
+ /* Length of key tuple (depends on #keyparts used) to store in the above */
+ uint insideout_key_len;
- /* Used by InsideOut scan. Just set to true when have found a row. */
+ /* Used by LooseScan. TRUE<=> there has been a matching record combination */
bool found_match;
enum {
@@ -1184,8 +1189,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-08-18 05:43:50 +0000
+++ b/sql/sql_yacc.yy 2008-09-04 18:18:11 +0000
@@ -590,10 +590,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.
@@ -2627,6 +2627,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));
if ($$ == NULL)
MYSQL_YYABORT;
@@ -4800,10 +4805,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 */
@@ -4816,7 +4817,7 @@ opt_check_constraint:
;
check_constraint:
- CHECK_SYM expr
+ CHECK_SYM '(' expr ')'
;
opt_constraint:
=== modified file 'sql/structs.h'
--- a/sql/structs.h 2008-08-16 05:15:49 +0000
+++ b/sql/structs.h 2008-09-04 18:18:11 +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;
=== modified file 'tests/mysql_client_test.c'
--- a/tests/mysql_client_test.c 2008-08-18 05:43:50 +0000
+++ b/tests/mysql_client_test.c 2008-09-04 18:18:11 +0000
@@ -17623,7 +17623,6 @@ static void test_wl4166_2()
}
-
/**
Test how warnings generated during assignment of parameters
are (currently not) preserve in case of reprepare.
@@ -17881,6 +17880,36 @@ static void test_wl4284_1()
DBUG_VOID_RETURN;
}
+/**
+ Bug#38486 Crash when using cursor protocol
+*/
+
+static void test_bug38486(void)
+{
+ MYSQL_STMT *stmt;
+ const char *stmt_text;
+ unsigned long type= CURSOR_TYPE_READ_ONLY;
+
+ DBUG_ENTER("test_bug38486");
+ myheader("test_bug38486");
+
+ stmt= mysql_stmt_init(mysql);
+ mysql_stmt_attr_set(stmt, STMT_ATTR_CURSOR_TYPE, (void*)&type);
+ stmt_text= "CREATE TABLE t1 (a INT)";
+ mysql_stmt_prepare(stmt, stmt_text, strlen(stmt_text));
+ mysql_stmt_execute(stmt);
+ mysql_stmt_close(stmt);
+
+ stmt= mysql_stmt_init(mysql);
+ mysql_stmt_attr_set(stmt, STMT_ATTR_CURSOR_TYPE, (void*)&type);
+ stmt_text= "INSERT INTO t1 VALUES (1)";
+ mysql_stmt_prepare(stmt, stmt_text, strlen(stmt_text));
+ mysql_stmt_execute(stmt);
+ mysql_stmt_close(stmt);
+
+ DBUG_VOID_RETURN;
+}
+
/*
Read and parse arguments and MySQL options from my.cnf
*/
@@ -18193,6 +18222,7 @@ static struct my_tests_st my_tests[]= {
{ "test_wl4166_4", test_wl4166_4 },
{ "test_bug36004", test_bug36004 },
{ "test_wl4284_1", test_wl4284_1 },
+ { "test_bug38486", test_bug38486 },
{ 0, 0 }
};
| Thread |
|---|
| • bzr commit into mysql-6.0-bka-preview branch (sergefp:2646) | Sergey Petrunia | 4 Sep |
