Merge mysql.com:/home/jimw/my/mysql-5.0-3094

into  mysql.com:/home/jimw/my/mysql-5.0-clean

30 files changed, 547 insertions, 41 deletions

diff --git a/mysql-test/r/flush.result b/mysql-test/r/flush.result
index 306376b13c3..16c308e3450 100644
--- a/mysql-test/r/flush.result
+++ b/mysql-test/r/flush.result
@@ -27,3 +27,24 @@ select * from t1;
 n
 345
 drop table t1;
+create table t1 (c1 int);
+lock table t1 write;
+flush tables with read lock;
+ERROR HY000: Can't execute the given command because you have active locked tables or an active transaction
+lock table t1 read;
+flush tables with read lock;
+lock table t1 write;
+ERROR HY000: Can't execute the query because you have a conflicting read lock
+lock table t1 read;
+lock table t1 write;
+ERROR HY000: Can't execute the query because you have a conflicting read lock
+unlock tables;
+create table t2 (c1 int);
+create table t3 (c1 int);
+lock table t1 read, t2 read, t3 write;
+flush tables with read lock;
+ERROR HY000: Can't execute the given command because you have active locked tables or an active transaction
+lock table t1 read, t2 read, t3 read;
+flush tables with read lock;
+unlock tables;
+drop table t1, t2, t3;
diff --git a/mysql-test/r/fulltext_order_by.result b/mysql-test/r/fulltext_order_by.result
index 3b52be4b1f2..2c421826bb4 100644
--- a/mysql-test/r/fulltext_order_by.result
+++ b/mysql-test/r/fulltext_order_by.result
@@ -1,4 +1,4 @@
-DROP TABLE IF EXISTS t1;
+DROP TABLE IF EXISTS t1,t2,t3;
 CREATE TABLE t1 (
 a INT AUTO_INCREMENT PRIMARY KEY,
 message CHAR(20),
@@ -158,5 +158,19 @@ where
 match(c.beitrag) against ('+abc' in boolean mode)
 order by 
 match(betreff) against ('+abc' in boolean mode) desc;
-ERROR HY000: The used table type doesn't support FULLTEXT indexes
+text	id	betreff
+(select b.id, b.betreff from t3 b) union 
+(select b.id, b.betreff from t3 b) 
+order by match(betreff) against ('+abc' in boolean mode) desc;
+id	betreff
+(select b.id, b.betreff from t3 b) union 
+(select b.id, b.betreff from t3 b) 
+order by match(betreff) against ('+abc') desc;
+ERROR HY000: Can't find FULLTEXT index matching the column list
+select distinct b.id, b.betreff from t3 b 
+order by match(betreff) against ('+abc' in boolean mode) desc;
+id	betreff
+select b.id, b.betreff from t3 b group by b.id+1 
+order by match(betreff) against ('+abc' in boolean mode) desc;
+id	betreff
 drop table t1,t2,t3;
diff --git a/mysql-test/r/grant.result b/mysql-test/r/grant.result
index 19982c7bb60..14393be1cc8 100644
--- a/mysql-test/r/grant.result
+++ b/mysql-test/r/grant.result
@@ -591,3 +591,6 @@ insert into tables_priv values ('','test_db','mysqltest_1','test_table','test_gr
 flush privileges;
 delete from tables_priv where host = '' and user = 'mysqltest_1';
 flush privileges;
+set @user123="non-existent";
+select * from mysql.db where user=@user123;
+Host	Db	User	Select_priv	Insert_priv	Update_priv	Delete_priv	Create_priv	Drop_priv	Grant_priv	References_priv	Index_priv	Alter_priv	Create_tmp_table_priv	Lock_tables_priv	Create_view_priv	Show_view_priv	Create_routine_priv	Alter_routine_priv	Execute_priv
diff --git a/mysql-test/r/group_by.result b/mysql-test/r/group_by.result
index 64446e63e6f..f4cf5217fa7 100644
--- a/mysql-test/r/group_by.result
+++ b/mysql-test/r/group_by.result
@@ -751,6 +751,13 @@ COUNT(DISTINCT(t1.id))	comment
 1	NULL
 1	a problem
 DROP TABLE t1, t2;
+create table t1 (f1 date);
+insert into t1 values('2005-06-06');
+insert into t1 values('2005-06-06');
+select date(left(f1+0,8)) from t1 group by 1;
+date(left(f1+0,8))
+2005-06-06
+drop table t1;
 CREATE TABLE t1 (n int);
 INSERT INTO t1 VALUES (1);
 SELECT n+1 AS n FROM t1 GROUP BY n;
diff --git a/mysql-test/r/join_nested.result b/mysql-test/r/join_nested.result
index 27edac1b30b..1fd7e6f4390 100644
--- a/mysql-test/r/join_nested.result
+++ b/mysql-test/r/join_nested.result
@@ -1343,3 +1343,58 @@ id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	0	
 1	SIMPLE	t3	ALL	NULL	NULL	NULL	NULL	0	
 DROP TABLE t1,t2,t3;
+CREATE TABLE t1 (goods int(12) NOT NULL, price varchar(128) NOT NULL);
+INSERT INTO t1 VALUES (23, 2340), (26, 9900);
+CREATE TABLE t2 (goods int(12), name varchar(50), shop char(2));
+INSERT INTO t2 VALUES (23, 'as300', 'fr'), (26, 'as600', 'fr');
+create table t3 (groupid int(12) NOT NULL, goodsid int(12) NOT NULL);
+INSERT INTO t3 VALUES (3,23), (6,26);
+CREATE TABLE t4 (groupid int(12));
+INSERT INTO t4 VALUES (1), (2), (3), (4), (5), (6);
+SELECT * FROM
+(SELECT DISTINCT gl.groupid, gp.price
+FROM t4 gl 
+LEFT JOIN
+(t3 g INNER JOIN t2 p ON g.goodsid = p.goods 
+INNER JOIN t1 gp ON p.goods = gp.goods)
+ON gl.groupid = g.groupid and p.shop = 'fr') t;
+groupid	price
+1	NULL
+2	NULL
+3	2340
+4	NULL
+5	NULL
+6	9900
+CREATE VIEW v1 AS
+SELECT g.groupid groupid, p.goods goods,  
+p.name name, p.shop shop, 
+gp.price price
+FROM t3 g INNER JOIN t2 p ON g.goodsid = p.goods
+INNER JOIN t1 gp on p.goods = gp.goods;
+CREATE VIEW v2 AS
+SELECT DISTINCT g.groupid, fr.price
+FROM t4 g
+LEFT JOIN
+v1 fr on g.groupid = fr.groupid and fr.shop = 'fr';
+SELECT * FROM v2;
+groupid	price
+1	NULL
+2	NULL
+3	2340
+4	NULL
+5	NULL
+6	9900
+SELECT * FROM 
+(SELECT DISTINCT g.groupid, fr.price
+FROM t4 g
+LEFT JOIN
+v1 fr on g.groupid = fr.groupid and fr.shop = 'fr') t;
+groupid	price
+1	NULL
+2	NULL
+3	2340
+4	NULL
+5	NULL
+6	9900
+DROP VIEW v1,v2;
+DROP TABLE t1,t2,t3,t4;
diff --git a/mysql-test/r/key_cache.result b/mysql-test/r/key_cache.result
index b3aa4c5061c..de9a2b2c003 100644
--- a/mysql-test/r/key_cache.result
+++ b/mysql-test/r/key_cache.result
@@ -289,3 +289,9 @@ check table t1;
 Table	Op	Msg_type	Msg_text
 test.t1	check	status	OK
 drop table t1;
+set @@global.key_buffer_size=0;
+Warnings:
+Warning	1438	Cannot drop default keycache
+select @@global.key_buffer_size;
+@@global.key_buffer_size
+2097152
diff --git a/mysql-test/r/subselect.result b/mysql-test/r/subselect.result
index bbca9c905df..9606ed97ee7 100644
--- a/mysql-test/r/subselect.result
+++ b/mysql-test/r/subselect.result
@@ -942,7 +942,7 @@ ROW(1, 1, 'a') IN (select a,b,c from t1)
 1
 select ROW(1, 2, 'a') IN (select a,b,c from t1);
 ROW(1, 2, 'a') IN (select a,b,c from t1)
-NULL
+0
 select ROW(1, 1, 'a') IN (select b,a,c from t1);
 ROW(1, 1, 'a') IN (select b,a,c from t1)
 1
@@ -960,7 +960,7 @@ ROW(1, 1, 'a') IN (select a,b,c from t1 where c='b' or c='a')
 1
 select ROW(1, 2, 'a') IN (select a,b,c from t1 where c='b' or c='a');
 ROW(1, 2, 'a') IN (select a,b,c from t1 where c='b' or c='a')
-NULL
+0
 select ROW(1, 1, 'a') IN (select b,a,c from t1 where c='b' or c='a');
 ROW(1, 1, 'a') IN (select b,a,c from t1 where c='b' or c='a')
 1
@@ -2709,7 +2709,51 @@ select (1,2,3) = (select * from t1);
 ERROR 21000: Operand should contain 3 column(s)
 select (select * from t1) = (1,2,3);
 ERROR 21000: Operand should contain 2 column(s)
+drop table t1
+#;
+CREATE TABLE `t1` (
+`itemid` bigint(20) unsigned NOT NULL auto_increment,
+`sessionid` bigint(20) unsigned default NULL,
+`time` int(10) unsigned NOT NULL default '0',
+`type` set('A','D','E','F','G','I','L','N','U') collate latin1_general_ci NOT
+NULL default '',
+`data` text collate latin1_general_ci NOT NULL,
+PRIMARY KEY  (`itemid`)
+) DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci;
+INSERT INTO `t1` VALUES (1, 1, 1, 'D', '');
+CREATE TABLE `t2` (
+`sessionid` bigint(20) unsigned NOT NULL auto_increment,
+`pid` int(10) unsigned NOT NULL default '0',
+`date` int(10) unsigned NOT NULL default '0',
+`ip` varchar(15) collate latin1_general_ci NOT NULL default '',
+PRIMARY KEY  (`sessionid`)
+) DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci;
+INSERT INTO `t2` VALUES (1, 1, 1, '10.10.10.1');
+SELECT s.ip, count( e.itemid ) FROM `t1` e JOIN t2 s ON s.sessionid = e.sessionid WHERE e.sessionid = ( SELECT sessionid FROM t2 ORDER BY sessionid DESC LIMIT 1 ) GROUP BY s.ip HAVING count( e.itemid ) >0 LIMIT 0 , 30;
+ip	count( e.itemid )
+10.10.10.1	1
+drop tables t1,t2;
+create table t1 (fld enum('0','1'));
+insert into t1 values ('1');
+select * from (select max(fld) from t1) as foo;
+max(fld)
+1
 drop table t1;
+CREATE TABLE t1 (one int, two int, flag char(1));
+CREATE TABLE t2 (one int, two int, flag char(1));
+INSERT INTO t1 VALUES(1,2,'Y'),(2,3,'Y'),(3,4,'Y'),(5,6,'N'),(7,8,'N');
+INSERT INTO t2 VALUES(1,2,'Y'),(2,3,'Y'),(3,4,'Y'),(5,6,'N'),(7,8,'N');
+SELECT * FROM t1
+WHERE ROW(one,two) IN (SELECT DISTINCT one,two FROM t2 WHERE flag = 'N');
+one	two	flag
+5	6	N
+7	8	N
+SELECT * FROM t1
+WHERE ROW(one,two) IN (SELECT DISTINCT one,two FROM t1 WHERE flag = 'N');
+one	two	flag
+5	6	N
+7	8	N
+DROP TABLE t1,t2;
 create table t1 (df decimal(5,1));
 insert into t1 values(1.1);
 insert into t1 values(2.2);
diff --git a/mysql-test/t/flush.test b/mysql-test/t/flush.test
index f2bfa45ba59..f5fd9fcadf2 100644
--- a/mysql-test/t/flush.test
+++ b/mysql-test/t/flush.test
@@ -70,4 +70,35 @@ insert into t1 values (345);
 select * from t1;
 drop table t1;
 
+#
+# Bug#9459 - deadlock with flush with lock, and lock table write
+#
+create table t1 (c1 int);
+lock table t1 write;
+# Cannot get the global read lock with write locked tables.
+--error 1192
+flush tables with read lock;
+lock table t1 read;
+# Can get the global read lock with read locked tables.
+flush tables with read lock;
+--error 1223
+lock table t1 write;
+lock table t1 read;
+--error 1223
+lock table t1 write;
+# Release all table locks and the global read lock.
+unlock tables;
+create table t2 (c1 int);
+create table t3 (c1 int);
+lock table t1 read, t2 read, t3 write;
+# Cannot get the global read lock with write locked tables.
+--error 1192
+flush tables with read lock;
+lock table t1 read, t2 read, t3 read;
+# Can get the global read lock with read locked tables.
+flush tables with read lock;
+# Release all table locks and the global read lock.
+unlock tables;
+drop table t1, t2, t3;
+
 # End of 4.1 tests
diff --git a/mysql-test/t/fulltext_order_by.test b/mysql-test/t/fulltext_order_by.test
index a0bd7954674..444f1b0510c 100644
--- a/mysql-test/t/fulltext_order_by.test
+++ b/mysql-test/t/fulltext_order_by.test
@@ -1,5 +1,5 @@
 --disable_warnings
-DROP TABLE IF EXISTS t1;
+DROP TABLE IF EXISTS t1,t2,t3;
 --enable_warnings
 
 CREATE TABLE t1 (
@@ -117,8 +117,6 @@ where
 order by 
   match(b.betreff) against ('+abc' in boolean mode) desc;
 
--- todo psergey: fix
---error 1214 
 select a.text, b.id, b.betreff
 from 
   t2 a inner join t3 b on a.id = b.forum inner join
@@ -135,6 +133,22 @@ where
 order by 
   match(betreff) against ('+abc' in boolean mode) desc;
 
+# BUG#11869 part2: used table type doesn't support FULLTEXT indexes error
+(select b.id, b.betreff from t3 b) union 
+(select b.id, b.betreff from t3 b) 
+order by match(betreff) against ('+abc' in boolean mode) desc;
+
+--error 1191
+(select b.id, b.betreff from t3 b) union 
+(select b.id, b.betreff from t3 b) 
+order by match(betreff) against ('+abc') desc;
+
+select distinct b.id, b.betreff from t3 b 
+order by match(betreff) against ('+abc' in boolean mode) desc;
+
+select b.id, b.betreff from t3 b group by b.id+1 
+order by match(betreff) against ('+abc' in boolean mode) desc;
+
 drop table t1,t2,t3;
 
 # End of 4.1 tests
diff --git a/mysql-test/t/grant.test b/mysql-test/t/grant.test
index 05094b29589..635c7a3f4e7 100644
--- a/mysql-test/t/grant.test
+++ b/mysql-test/t/grant.test
@@ -480,4 +480,11 @@ flush privileges;
 delete from tables_priv where host = '' and user = 'mysqltest_1';
 flush privileges;
 
+#
+# Bug #10892 user variables not auto cast for comparisons
+# Check that we don't get illegal mix of collations
+#
+set @user123="non-existent";
+select * from mysql.db where user=@user123;
+
 # End of 4.1 tests
diff --git a/mysql-test/t/group_by.test b/mysql-test/t/group_by.test
index bfbdd098426..8300b502518 100644
--- a/mysql-test/t/group_by.test
+++ b/mysql-test/t/group_by.test
@@ -575,12 +575,22 @@ CREATE TABLE t1 (id varchar(20) NOT NULL);
 INSERT INTO t1 VALUES ('trans1'), ('trans2');
 CREATE TABLE t2 (id varchar(20) NOT NULL, err_comment blob NOT NULL);
 INSERT INTO t2 VALUES ('trans1', 'a problem');
-
 SELECT COUNT(DISTINCT(t1.id)), LEFT(err_comment, 256) AS comment
   FROM t1 LEFT JOIN t2 ON t1.id=t2.id GROUP BY comment;
 
 DROP TABLE t1, t2;
 
+
+#
+# Bug #12266 GROUP BY expression on DATE column produces result with
+#            reduced length
+#
+create table t1 (f1 date);
+insert into t1 values('2005-06-06');
+insert into t1 values('2005-06-06'); 
+select date(left(f1+0,8)) from t1 group by 1;
+drop table t1;
+
 #
 # Test for bug #11414: crash on Windows for a simple GROUP BY query 
 #  
diff --git a/mysql-test/t/join_nested.test b/mysql-test/t/join_nested.test
index 992217d0391..10b2dac5c8b 100644
--- a/mysql-test/t/join_nested.test
+++ b/mysql-test/t/join_nested.test
@@ -770,3 +770,51 @@ SELECT * FROM t1 LEFT JOIN (t2 LEFT JOIN t3 ON c21=c31) ON c11=c21;
 EXPLAIN SELECT * FROM t1 LEFT JOIN (t2 LEFT JOIN t3 ON c21=c31) ON c11=c21;
 
 DROP TABLE t1,t2,t3;
+
+#
+# Bug #12154: creation of temp table for a query with nested outer join
+# 
+
+CREATE TABLE t1 (goods int(12) NOT NULL, price varchar(128) NOT NULL);
+INSERT INTO t1 VALUES (23, 2340), (26, 9900);
+
+CREATE TABLE t2 (goods int(12), name varchar(50), shop char(2));
+INSERT INTO t2 VALUES (23, 'as300', 'fr'), (26, 'as600', 'fr');
+
+create table t3 (groupid int(12) NOT NULL, goodsid int(12) NOT NULL);
+INSERT INTO t3 VALUES (3,23), (6,26);
+
+CREATE TABLE t4 (groupid int(12));
+INSERT INTO t4 VALUES (1), (2), (3), (4), (5), (6);
+
+SELECT * FROM
+(SELECT DISTINCT gl.groupid, gp.price
+  FROM t4 gl 
+       LEFT JOIN
+       (t3 g INNER JOIN t2 p ON g.goodsid = p.goods 
+             INNER JOIN t1 gp ON p.goods = gp.goods)
+       ON gl.groupid = g.groupid and p.shop = 'fr') t;
+
+CREATE VIEW v1 AS
+SELECT g.groupid groupid, p.goods goods,  
+       p.name name, p.shop shop, 
+       gp.price price
+  FROM t3 g INNER JOIN t2 p ON g.goodsid = p.goods
+            INNER JOIN t1 gp on p.goods = gp.goods;
+
+CREATE VIEW v2 AS
+SELECT DISTINCT g.groupid, fr.price
+  FROM t4 g
+       LEFT JOIN
+       v1 fr on g.groupid = fr.groupid and fr.shop = 'fr';
+
+SELECT * FROM v2;
+
+SELECT * FROM 
+(SELECT DISTINCT g.groupid, fr.price
+  FROM t4 g
+       LEFT JOIN
+       v1 fr on g.groupid = fr.groupid and fr.shop = 'fr') t;
+
+DROP VIEW v1,v2;
+DROP TABLE t1,t2,t3,t4;
diff --git a/mysql-test/t/key_cache.test b/mysql-test/t/key_cache.test
index 5d0f904a716..df584021af1 100644
--- a/mysql-test/t/key_cache.test
+++ b/mysql-test/t/key_cache.test
@@ -168,4 +168,11 @@ check table t1;
 
 drop table t1;
 
+#
+# Bug#10473 - Can't set 'key_buffer_size' system variable to ZERO
+# (One cannot drop the default key cache.)
+#
+set @@global.key_buffer_size=0;
+select @@global.key_buffer_size;
+
 # End of 4.1 tests
diff --git a/mysql-test/t/subselect.test b/mysql-test/t/subselect.test
index 41cdf9f1c64..8bc47190dca 100644
--- a/mysql-test/t/subselect.test
+++ b/mysql-test/t/subselect.test
@@ -1726,9 +1726,58 @@ select (select a from t1) = (1,2);
 select (1,2,3) = (select * from t1);
 -- error 1241
 select (select * from t1) = (1,2,3);
+drop table t1
+
+#
+# Item_int_with_ref check (BUG#10020)
+#
+CREATE TABLE `t1` (
+  `itemid` bigint(20) unsigned NOT NULL auto_increment,
+  `sessionid` bigint(20) unsigned default NULL,
+  `time` int(10) unsigned NOT NULL default '0',
+  `type` set('A','D','E','F','G','I','L','N','U') collate latin1_general_ci NOT
+NULL default '',
+  `data` text collate latin1_general_ci NOT NULL,
+  PRIMARY KEY  (`itemid`)
+) DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci;
+INSERT INTO `t1` VALUES (1, 1, 1, 'D', '');
+CREATE TABLE `t2` (
+  `sessionid` bigint(20) unsigned NOT NULL auto_increment,
+  `pid` int(10) unsigned NOT NULL default '0',
+  `date` int(10) unsigned NOT NULL default '0',
+  `ip` varchar(15) collate latin1_general_ci NOT NULL default '',
+  PRIMARY KEY  (`sessionid`)
+) DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci;
+INSERT INTO `t2` VALUES (1, 1, 1, '10.10.10.1');
+SELECT s.ip, count( e.itemid ) FROM `t1` e JOIN t2 s ON s.sessionid = e.sessionid WHERE e.sessionid = ( SELECT sessionid FROM t2 ORDER BY sessionid DESC LIMIT 1 ) GROUP BY s.ip HAVING count( e.itemid ) >0 LIMIT 0 , 30;
+drop tables t1,t2;
+
+# BUG#11821 : Select from subselect using aggregate function on an enum
+# segfaults:
+create table t1 (fld enum('0','1'));
+insert into t1 values ('1');
+select * from (select max(fld) from t1) as foo;
 drop table t1;
 
 #
+# Bug #11867: queries with ROW(,elems>) IN (SELECT DISTINCT <cols> FROM ...)
+#
+
+CREATE TABLE t1 (one int, two int, flag char(1));
+CREATE TABLE t2 (one int, two int, flag char(1));
+INSERT INTO t1 VALUES(1,2,'Y'),(2,3,'Y'),(3,4,'Y'),(5,6,'N'),(7,8,'N');
+INSERT INTO t2 VALUES(1,2,'Y'),(2,3,'Y'),(3,4,'Y'),(5,6,'N'),(7,8,'N');
+
+SELECT * FROM t1
+  WHERE ROW(one,two) IN (SELECT DISTINCT one,two FROM t2 WHERE flag = 'N');
+SELECT * FROM t1
+  WHERE ROW(one,two) IN (SELECT DISTINCT one,two FROM t1 WHERE flag = 'N');
+
+DROP TABLE t1,t2;
+
+# End of 4.1 tests
+
+#
 #decimal-related tests
 #
 create table t1 (df decimal(5,1));
@@ -1868,4 +1917,3 @@ select * from (select max(fld) from t1) as foo;
 drop table t1;
 
 
-# End of 4.1 tests
diff --git a/scripts/make_win_src_distribution.sh b/scripts/make_win_src_distribution.sh
index 8183370f220..0d2a8cdd5d7 100644
--- a/scripts/make_win_src_distribution.sh
+++ b/scripts/make_win_src_distribution.sh
@@ -343,8 +343,8 @@ mv $BASE/sql/sql_yacc.cpp-new $BASE/sql/sql_yacc.cpp
 # Search the tree for plain text files and adapt the line end marker
 #
 find $BASE \( -name "*.dsp" -o -name "*.dsw" -o -name "*.cnf" -o -name "*.ini" \
-           -o -name COPYING -o -name ChangeLog -o -name EXCEPTIONS-CLIENT 
-           -o -name "INSTALL*" -o -name LICENSE -o -name "README*" 
+           -o -name COPYING -o -name ChangeLog -o -name EXCEPTIONS-CLIENT \
+           -o -name "INSTALL*" -o -name LICENSE -o -name "README*" \
            -o -name "*.vcproj" -o -name "*.sln" \) -type f -print \
 | while read v
   do
diff --git a/sql/item.cc b/sql/item.cc
index 25a12bc2d67..ae4cbadc01e 100644
--- a/sql/item.cc
+++ b/sql/item.cc
@@ -1016,14 +1016,18 @@ bool DTCollation::aggregate(DTCollation &dt, uint flags)
        ; // Do nothing
     }
     else if ((flags & MY_COLL_ALLOW_SUPERSET_CONV) &&
-             derivation < dt.derivation &&
-             collation->state & MY_CS_UNICODE)
+             collation->state & MY_CS_UNICODE &&
+             (derivation < dt.derivation ||
+             (derivation == dt.derivation &&
+             !(dt.collation->state & MY_CS_UNICODE))))
     {
       // Do nothing
     }
     else if ((flags & MY_COLL_ALLOW_SUPERSET_CONV) &&
-             dt.derivation < derivation &&
-             dt.collation->state & MY_CS_UNICODE)
+             dt.collation->state & MY_CS_UNICODE &&
+             (dt.derivation < derivation ||
+              (dt.derivation == derivation &&
+             !(collation->state & MY_CS_UNICODE))))
     {
       set(dt);
     }
diff --git a/sql/item_subselect.cc b/sql/item_subselect.cc
index ad1c9977e5b..53e377339b3 100644
--- a/sql/item_subselect.cc
+++ b/sql/item_subselect.cc
@@ -1070,15 +1070,20 @@ Item_in_subselect::row_value_transformer(JOIN *join)
     List_iterator_fast<Item> li(select_lex->item_list);
     for (uint i= 0; i < n; i++)
     {
+      Item *func;
       DBUG_ASSERT(left_expr->fixed && select_lex->ref_pointer_array[i]->fixed);
       if (select_lex->ref_pointer_array[i]->
           check_cols(left_expr->el(i)->cols()))
         DBUG_RETURN(RES_ERROR);
-      Item *func= new Item_ref_null_helper(&select_lex->context,
-                                           this,
-                                           select_lex->ref_pointer_array+i,
-                                           (char *) "<no matter>",
-                                           (char *) "<list ref>");
+      if (join->having || select_lex->with_sum_func ||
+          select_lex->group_list.elements)
+        func= new Item_ref_null_helper(&select_lex->context,
+                                       this,
+				       select_lex->ref_pointer_array+i,
+				       (char *) "<no matter>",
+				       (char *) "<list ref>");
+      else
+        func= li++;
       func=
 	eq_creator.create(new Item_direct_ref(&select_lex->context,
                                               (*optimizer->get_cache())->
diff --git a/sql/item_sum.cc b/sql/item_sum.cc
index bbcea4705fa..2d80a19aa55 100644
--- a/sql/item_sum.cc
+++ b/sql/item_sum.cc
@@ -2291,7 +2291,8 @@ bool Item_sum_count_distinct::setup(THD *thd)
   DBUG_ASSERT(table == 0);
   if (!(table= create_tmp_table(thd, tmp_table_param, list, (ORDER*) 0, 1,
 				0,
-				select_lex->options | thd->options,
+				(select_lex->options | thd->options) & 
+                                ~TMP_TABLE_FORCE_MYISAM,
 				HA_POS_ERROR, (char*)"")))
     return TRUE;
   table->file->extra(HA_EXTRA_NO_ROWS);		// Don't update rows
@@ -3073,7 +3074,8 @@ bool Item_func_group_concat::setup(THD *thd)
   */
   if (!(table= create_tmp_table(thd, tmp_table_param, all_fields,
                                 (ORDER*) 0, 0, TRUE,
-                                select_lex->options | thd->options,
+                                (select_lex->options | thd->options) & 
+                                ~TMP_TABLE_FORCE_MYISAM,
                                 HA_POS_ERROR, (char*) "")))
     DBUG_RETURN(TRUE);
   table->file->extra(HA_EXTRA_NO_ROWS);
diff --git a/sql/item_timefunc.h b/sql/item_timefunc.h
index 107d12e6da2..e79bc25030b 100644
--- a/sql/item_timefunc.h
+++ b/sql/item_timefunc.h
@@ -714,6 +714,12 @@ public:
   {
     return (new Field_date(maybe_null, name, t_arg, &my_charset_bin));
   }  
+  void fix_length_and_dec()
+  {
+    collation.set(&my_charset_bin);
+    max_length= 10;
+    maybe_null= 1;
+  }
 };
 
 
diff --git a/sql/mysql_priv.h b/sql/mysql_priv.h
index c56496c394d..241566b25c9 100644
--- a/sql/mysql_priv.h
+++ b/sql/mysql_priv.h
@@ -256,6 +256,13 @@ extern CHARSET_INFO *national_charset_info, *table_alias_charset;
 #define OPTION_WARNINGS         (1L << 13)      // THD, user
 #define OPTION_AUTO_IS_NULL     (1L << 14)      // THD, user, binlog
 #define OPTION_FOUND_COMMENT    (1L << 15)      // SELECT, intern, parser
+/* 
+  Force the used temporary table to be a MyISAM table (because we will use
+  fulltext functions when reading from it. This uses the same constant as
+  OPTION_FOUND_COMMENT because we've run out of bits and these two values 
+  are not used together.
+*/
+#define TMP_TABLE_FORCE_MYISAM          (1L << 15)
 #define OPTION_SAFE_UPDATES     (1L << 16)      // THD, user
 #define OPTION_BUFFER_RESULT    (1L << 17)      // SELECT, user
 #define OPTION_BIN_LOG          (1L << 18)      // THD, user
@@ -1450,6 +1457,12 @@ inline void setup_table_map(TABLE *table, TABLE_LIST *table_list, uint tablenr)
   table->status= STATUS_NO_RECORD;
   table->keys_in_use_for_query= table->s->keys_in_use;
   table->maybe_null= table_list->outer_join;
+  TABLE_LIST *embedding= table_list->embedding;
+  while (!table->maybe_null && embedding)
+  {
+    table->maybe_null= embedding->outer_join;
+    embedding= embedding->embedding;
+  }
   table->tablenr= tablenr;
   table->map= (table_map) 1 << tablenr;
   table->force_index= table_list->force_index;
diff --git a/sql/set_var.cc b/sql/set_var.cc
index ff37c46349d..637b33f18d2 100644
--- a/sql/set_var.cc
+++ b/sql/set_var.cc
@@ -2293,7 +2293,12 @@ bool sys_var_key_buffer_size::update(THD *thd, set_var *var)
   if (!tmp)					// Zero size means delete
   {
     if (key_cache == dflt_key_cache)
+    {
+      push_warning_printf(thd, MYSQL_ERROR::WARN_LEVEL_WARN,
+                          ER_WARN_CANT_DROP_DEFAULT_KEYCACHE,
+                          ER(ER_WARN_CANT_DROP_DEFAULT_KEYCACHE));
       goto end;					// Ignore default key cache
+    }
 
     if (key_cache->key_cache_inited)		// If initied
     {
diff --git a/sql/share/errmsg.txt b/sql/share/errmsg.txt
index 5dd5cd9c775..c47b3bb7dd9 100644
--- a/sql/share/errmsg.txt
+++ b/sql/share/errmsg.txt
@@ -5388,3 +5388,6 @@ ER_STACK_OVERRUN_NEED_MORE
 	eng "Thread stack overrun:  %ld bytes used of a %ld byte stack, and %ld bytes needed.  Use 'mysqld -O thread_stack=#' to specify a bigger stack."
 ER_TOO_LONG_BODY 42000 S1009
 	eng "Routine body for '%-.100s' is too long"
+ER_WARN_CANT_DROP_DEFAULT_KEYCACHE
+	eng "Cannot drop default keycache"
+        ger "Der Default-Keycache kann nicht gelöscht werden"
diff --git a/sql/sql_derived.cc b/sql/sql_derived.cc
index fc9d15e94c4..afcf7dbd93f 100644
--- a/sql/sql_derived.cc
+++ b/sql/sql_derived.cc
@@ -142,7 +142,8 @@ int mysql_derived_prepare(THD *thd, LEX *lex, TABLE_LIST *orig_table_list)
 				  unit->types, (ORDER*) 0,
 				  FALSE, 1,
 				  (first_select->options | thd->options |
-				   TMP_TABLE_ALL_COLUMNS),
+				   TMP_TABLE_ALL_COLUMNS) &
+                                   ~TMP_TABLE_FORCE_MYISAM,
 				  HA_POS_ERROR,
 				  orig_table_list->alias)))
     {
diff --git a/sql/sql_parse.cc b/sql/sql_parse.cc
index 677b3a98174..2fd10b36fad 100644
--- a/sql/sql_parse.cc
+++ b/sql/sql_parse.cc
@@ -6450,6 +6450,23 @@ bool reload_acl_and_cache(THD *thd, ulong options, TABLE_LIST *tables,
     if ((options & REFRESH_READ_LOCK) && thd)
     {
       /*
+        We must not try to aspire a global read lock if we have a write
+        locked table. This would lead to a deadlock when trying to
+        reopen (and re-lock) the table after the flush.
+      */
+      if (thd->locked_tables)
+      {
+        THR_LOCK_DATA **lock_p= thd->locked_tables->locks;
+        THR_LOCK_DATA **end_p= lock_p + thd->locked_tables->lock_count;
+
+        for (; lock_p < end_p; lock_p++)
+          if ((*lock_p)->type == TL_WRITE)
+          {
+            my_error(ER_LOCK_OR_ACTIVE_TRANSACTION, MYF(0));
+            return 1;
+          }
+      }
+      /*
 	Writing to the binlog could cause deadlocks, as we don't log
 	UNLOCK TABLES
       */
diff --git a/sql/sql_prepare.cc b/sql/sql_prepare.cc
index 18707cc6c87..0861bd1b0b2 100644
--- a/sql/sql_prepare.cc
+++ b/sql/sql_prepare.cc
@@ -88,6 +88,7 @@ class Prepared_statement: public Statement
 {
 public:
   THD *thd;
+  Protocol *protocol;
   Item_param **param_array;
   uint param_count;
   uint last_errno;
@@ -2021,6 +2022,7 @@ void mysql_stmt_execute(THD *thd, char *packet, uint packet_length)
         DBUG_VOID_RETURN;
       /* If lex->result is set, mysql_execute_command will use it */
       stmt->lex->result= &cursor->result;
+      stmt->protocol= &cursor->protocol;
       thd->lock_id= &cursor->lock_id;
     }
   }
@@ -2055,7 +2057,7 @@ void mysql_stmt_execute(THD *thd, char *packet, uint packet_length)
   }
   mysql_log.write(thd, thd->command, "[%lu] %s", stmt->id, thd->query);
 
-  thd->protocol= &thd->protocol_prep;           // Switch to binary protocol
+  thd->protocol= stmt->protocol;                // Switch to binary protocol
   if (!(specialflag & SPECIAL_NO_PRIOR))
     my_pthread_setprio(pthread_self(),QUERY_PRIOR);
   mysql_execute_command(thd);
@@ -2247,7 +2249,7 @@ void mysql_stmt_fetch(THD *thd, char *packet, uint packet_length)
   if (!(specialflag & SPECIAL_NO_PRIOR))
     my_pthread_setprio(pthread_self(), QUERY_PRIOR);
 
-  thd->protocol= &thd->protocol_prep;           // Switch to binary protocol
+  thd->protocol= stmt->protocol;                // Switch to binary protocol
   cursor->fetch(num_rows);
   thd->protocol= &thd->protocol_simple;         // Use normal protocol
 
@@ -2419,6 +2421,7 @@ Prepared_statement::Prepared_statement(THD *thd_arg)
              thd_arg->variables.query_alloc_block_size,
              thd_arg->variables.query_prealloc_size),
   thd(thd_arg),
+  protocol(&thd_arg->protocol_prep),
   param_array(0),
   param_count(0),
   last_errno(0)
diff --git a/sql/sql_select.cc b/sql/sql_select.cc
index 33476979c7e..fb0407e1405 100644
--- a/sql/sql_select.cc
+++ b/sql/sql_select.cc
@@ -1016,7 +1016,7 @@ JOIN::optimize()
 			    group_list : (ORDER*) 0),
 			   group_list ? 0 : select_distinct,
 			   group_list && simple_group,
-			   select_options,
+			   select_options & ~TMP_TABLE_FORCE_MYISAM,
 			   (order == 0 || skip_sort_order) ? select_limit :
 			   HA_POS_ERROR,
 			   (char *) "")))
@@ -1396,7 +1396,8 @@ JOIN::exec()
 						(ORDER*) 0,
 						curr_join->select_distinct && 
 						!curr_join->group_list,
-						1, curr_join->select_options,
+						1, curr_join->select_options
+                                                & ~TMP_TABLE_FORCE_MYISAM,
 						HA_POS_ERROR,
 						(char *) "")))
 	  DBUG_VOID_RETURN;
@@ -1714,6 +1715,7 @@ JOIN::destroy()
 Cursor::Cursor(THD *thd)
   :Query_arena(&main_mem_root, INITIALIZED),
    join(0), unit(0),
+   protocol(thd),
    close_at_commit(FALSE)
 {
   /* We will overwrite it at open anyway. */
@@ -8381,7 +8383,7 @@ create_tmp_table(THD *thd,TMP_TABLE_PARAM *param,List<Item> &fields,
   /* If result table is small; use a heap */
   if (blob_count || using_unique_constraint ||
       (select_options & (OPTION_BIG_TABLES | SELECT_SMALL_RESULT)) ==
-      OPTION_BIG_TABLES)
+      OPTION_BIG_TABLES ||(select_options & TMP_TABLE_FORCE_MYISAM))
   {
     table->file=get_new_handler(table,table->s->db_type= DB_TYPE_MYISAM);
     if (group &&
@@ -9744,7 +9746,13 @@ join_read_const_table(JOIN_TAB *tab, POSITION *pos)
       table->file->extra(HA_EXTRA_KEYREAD);
       tab->index= tab->ref.key;
     }
-    if ((error=join_read_const(tab)))
+    error=join_read_const(tab);
+    if (table->key_read)
+    {
+      table->key_read=0;
+      table->file->extra(HA_EXTRA_NO_KEYREAD);
+    }
+    if (error)
     {
       tab->info="unique row not found";
       /* Mark for EXPLAIN that the row was not found */
@@ -9752,11 +9760,6 @@ join_read_const_table(JOIN_TAB *tab, POSITION *pos)
       if (!table->maybe_null || error > 0)
 	DBUG_RETURN(error);
     }
-    if (table->key_read)
-    {
-      table->key_read=0;
-      table->file->extra(HA_EXTRA_NO_KEYREAD);
-    }
   }
   if (*tab->on_expr_ref && !table->null_row)
   {
diff --git a/sql/sql_select.h b/sql/sql_select.h
index 1fa246370c6..c950444e1c6 100644
--- a/sql/sql_select.h
+++ b/sql/sql_select.h
@@ -396,6 +396,7 @@ class Cursor: public Sql_alloc, public Query_arena
   };
   Engine_info ht_info[MAX_HA];
 public:
+  Protocol_prep protocol;
   Item_change_list change_list;
   select_send result;
   THR_LOCK_OWNER lock_id;
diff --git a/sql/sql_show.cc b/sql/sql_show.cc
index e3c6fa14522..d2d453e55dd 100644
--- a/sql/sql_show.cc
+++ b/sql/sql_show.cc
@@ -3319,7 +3319,8 @@ TABLE *create_schema_table(THD *thd, TABLE_LIST *table_list)
   if (!(table= create_tmp_table(thd, tmp_table_param,
                                 field_list, (ORDER*) 0, 0, 0, 
                                 (select_lex->options | thd->options |
-                                 TMP_TABLE_ALL_COLUMNS),
+                                 TMP_TABLE_ALL_COLUMNS) &
+                                ~TMP_TABLE_FORCE_MYISAM,
                                 HA_POS_ERROR, table_list->alias)))
     DBUG_RETURN(0);
   table_list->schema_table_param= tmp_table_param;
diff --git a/sql/sql_union.cc b/sql/sql_union.cc
index c414f5e9e72..cdbe8a986b2 100644
--- a/sql/sql_union.cc
+++ b/sql/sql_union.cc
@@ -119,8 +119,6 @@ void
 st_select_lex_unit::init_prepare_fake_select_lex(THD *thd) 
 {
   thd->lex->current_select= fake_select_lex;
-  fake_select_lex->ftfunc_list_alloc.empty();
-  fake_select_lex->ftfunc_list= &fake_select_lex->ftfunc_list_alloc;
   fake_select_lex->table_list.link_in_list((byte *)&result_table_list,
 					   (byte **)
 					   &result_table_list.next_local);
@@ -301,15 +299,24 @@ bool st_select_lex_unit::prepare(THD *thd_arg, select_result *sel_result,
         goto err;
       }
     }
+    
+    ulong create_options= (first_select_in_union()->options | thd_arg->options |
+                          TMP_TABLE_ALL_COLUMNS) & ~TMP_TABLE_FORCE_MYISAM;
+    /*
+      Force the temporary table to be a MyISAM table if we're going to use
+      fullext functions (MATCH ... AGAINST .. IN BOOLEAN MODE) when reading
+      from it (this should be removed in 5.2 when fulltext search is moved 
+      out of MyISAM).
+    */
+    if (global_parameters->ftfunc_list->elements)
+      create_options= create_options | TMP_TABLE_FORCE_MYISAM;
 
     union_result->tmp_table_param.field_count= types.elements;
     if (!(table= create_tmp_table(thd_arg,
 				  &union_result->tmp_table_param, types,
 				  (ORDER*) 0, (bool) union_distinct, 1, 
-				  (first_select_in_union()->options |
-				   thd_arg->options |
-				   TMP_TABLE_ALL_COLUMNS),
-				  HA_POS_ERROR, (char *) tmp_table_alias)))
+                                  create_options, HA_POS_ERROR,
+                                  (char *) tmp_table_alias)))
       goto err;
     table->file->extra(HA_EXTRA_WRITE_CACHE);
     table->file->extra(HA_EXTRA_IGNORE_DUP_KEY);
diff --git a/tests/mysql_client_test.c b/tests/mysql_client_test.c
index 53708a7a741..5e6de152f40 100644
--- a/tests/mysql_client_test.c
+++ b/tests/mysql_client_test.c
@@ -13932,6 +13932,125 @@ static void test_bug12001()
   DIE_UNLESS(res==1);
 }
 
+
+/* Bug#11909: wrong metadata if fetching from two cursors */
+
+static void test_bug11909()
+{
+  MYSQL_STMT *stmt1, *stmt2;
+  MYSQL_BIND bind[7];
+  int rc;
+  char firstname[20], midinit[20], lastname[20], workdept[20];
+  ulong firstname_len, midinit_len, lastname_len, workdept_len;
+  uint32 empno;
+  double salary;
+  float bonus;
+  const char *stmt_text;
+
+  myheader("test_bug11909");
+
+  stmt_text= "drop table if exists t1";
+  rc= mysql_real_query(mysql, stmt_text, strlen(stmt_text));
+  myquery(rc);
+
+  stmt_text= "create table t1 ("
+    "  empno int(11) not null, firstname varchar(20) not null,"
+    "  midinit varchar(20) not null, lastname varchar(20) not null,"
+    "  workdept varchar(6) not null, salary double not null,"
+    "  bonus float not null, primary key (empno)"
+    ") default charset=latin1 collate=latin1_bin";
+  rc= mysql_real_query(mysql, stmt_text, strlen(stmt_text));
+  myquery(rc);
+
+  stmt_text= "insert into t1 values "
+    "(10, 'CHRISTINE', 'I', 'HAAS',     'A00', 52750, 1000), "
+    "(20, 'MICHAEL',   'L', 'THOMPSON', 'B01', 41250, 800),"
+    "(30, 'SALLY',     'A', 'KWAN',     'C01', 38250, 800),"
+    "(50, 'JOHN',      'B', 'GEYER',    'E01', 40175, 800), "
+    "(60, 'IRVING',    'F', 'STERN',    'D11', 32250, 500)";
+  rc= mysql_real_query(mysql, stmt_text, strlen(stmt_text));
+  myquery(rc);
+
+  /* ****** Begin of trace ****** */
+
+  stmt1= open_cursor("SELECT empno, firstname, midinit, lastname,"
+                     "workdept, salary, bonus FROM t1");
+
+  bzero(bind, sizeof(bind));
+  bind[0].buffer_type= MYSQL_TYPE_LONG;
+  bind[0].buffer= (void*) &empno;
+
+  bind[1].buffer_type= MYSQL_TYPE_VAR_STRING;
+  bind[1].buffer= (void*) firstname;
+  bind[1].buffer_length= sizeof(firstname);
+  bind[1].length= &firstname_len;
+
+  bind[2].buffer_type= MYSQL_TYPE_VAR_STRING;
+  bind[2].buffer= (void*) midinit;
+  bind[2].buffer_length= sizeof(midinit);
+  bind[2].length= &midinit_len;
+
+  bind[3].buffer_type= MYSQL_TYPE_VAR_STRING;
+  bind[3].buffer= (void*) lastname;
+  bind[3].buffer_length= sizeof(lastname);
+  bind[3].length= &lastname_len;
+
+  bind[4].buffer_type= MYSQL_TYPE_VAR_STRING;
+  bind[4].buffer= (void*) workdept;
+  bind[4].buffer_length= sizeof(workdept);
+  bind[4].length= &workdept_len;
+
+  bind[5].buffer_type= MYSQL_TYPE_DOUBLE;
+  bind[5].buffer= (void*) &salary;
+
+  bind[6].buffer_type= MYSQL_TYPE_FLOAT;
+  bind[6].buffer= (void*) &bonus;
+  rc= mysql_stmt_bind_result(stmt1, bind);
+  check_execute(stmt1, rc);
+
+  rc= mysql_stmt_execute(stmt1);
+  check_execute(stmt1, rc);
+
+  rc= mysql_stmt_fetch(stmt1);
+  DIE_UNLESS(rc == 0);
+  DIE_UNLESS(empno == 10);
+  DIE_UNLESS(strcmp(firstname, "CHRISTINE") == 0);
+  DIE_UNLESS(strcmp(midinit, "I") == 0);
+  DIE_UNLESS(strcmp(lastname, "HAAS") == 0);
+  DIE_UNLESS(strcmp(workdept, "A00") == 0);
+  DIE_UNLESS(salary == (double) 52750.0);
+  DIE_UNLESS(bonus == (float) 1000.0);
+
+  stmt2= open_cursor("SELECT empno, firstname FROM t1");
+  rc= mysql_stmt_bind_result(stmt2, bind);
+  check_execute(stmt2, rc);
+
+  rc= mysql_stmt_execute(stmt2);
+  check_execute(stmt2, rc);
+
+  rc= mysql_stmt_fetch(stmt2);
+  DIE_UNLESS(rc == 0);
+
+  DIE_UNLESS(empno == 10);
+  DIE_UNLESS(strcmp(firstname, "CHRISTINE") == 0);
+
+  rc= mysql_stmt_reset(stmt2);
+  check_execute(stmt2, rc);
+
+  /* ERROR: next statement should return 0 */
+
+  rc= mysql_stmt_fetch(stmt1);
+  DIE_UNLESS(rc == 0);
+
+  mysql_stmt_close(stmt1);
+  mysql_stmt_close(stmt2);
+  rc= mysql_rollback(mysql);
+  myquery(rc);
+
+  rc= mysql_query(mysql, "drop table t1");
+  myquery(rc);
+}
+
 /*
   Read and parse arguments and MySQL options from my.cnf
 */
@@ -14178,6 +14297,7 @@ static struct my_tests_st my_tests[]= {
   { "test_bug11037", test_bug11037 },
   { "test_bug10760", test_bug10760 },
   { "test_bug12001", test_bug12001 },
+  { "test_bug11909", test_bug11909 },
   { 0, 0 }
 };