Manual merge of mysql-5.1-bugteam into mysql-trunk-merge.

Conflicts:

mysql-test/collections/default.experimental

12 files changed, 250 insertions, 5 deletions

diff --git a/mysql-test/r/alter_table.result b/mysql-test/r/alter_table.result
index 15171798279..db80e9d2eea 100644
--- a/mysql-test/r/alter_table.result
+++ b/mysql-test/r/alter_table.result
@@ -1338,6 +1338,13 @@ ALTER TABLE t1 CHANGE COLUMN f1 f1_no_real_change TIMESTAMP NULL DEFAULT NULL;
 affected rows: 0
 info: Records: 0  Duplicates: 0  Warnings: 0
 DROP TABLE t1;
+#
+# Bug #31145: ALTER TABLE DROP COLUMN, ADD COLUMN crashes (linux) 
+#   or freezes (win) the server
+#
+CREATE TABLE t1 (a TEXT, id INT, b INT);
+ALTER TABLE t1 DROP COLUMN a, ADD COLUMN c TEXT FIRST;
+DROP TABLE t1;
 End of 5.1 tests
 CREATE TABLE t1(c CHAR(10), 
 i INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY);
diff --git a/mysql-test/r/count_distinct.result b/mysql-test/r/count_distinct.result
index a21748359b9..804bc1f4788 100644
--- a/mysql-test/r/count_distinct.result
+++ b/mysql-test/r/count_distinct.result
@@ -40,6 +40,26 @@ select t2.isbn,city,t1.libname,count(distinct t1.libname) as a from t3 left join
 isbn	city	libname	a
 007	Berkeley	Berkeley Public1	2
 000	New York	New York Public Libra	2
+select t2.isbn,city,@bar:=t1.libname,count(distinct t1.libname) as a
+from t3 left join t1 on t3.libname=t1.libname left join t2
+on t3.isbn=t2.isbn group by city having count(distinct
+t1.libname) > 1;
+isbn	city	@bar:=t1.libname	a
+007	Berkeley	Berkeley Public1	2
+000	New York	New York Public Libra	2
+SELECT @bar;
+@bar
+Berkeley Public2
+select t2.isbn,city,concat(@bar:=t1.libname),count(distinct t1.libname) as a
+from t3 left join t1 on t3.libname=t1.libname left join t2
+on t3.isbn=t2.isbn group by city having count(distinct
+t1.libname) > 1;
+isbn	city	concat(@bar:=t1.libname)	a
+007	Berkeley	Berkeley Public1	2
+000	New York	New York Public Libra	2
+SELECT @bar;
+@bar
+Berkeley Public2
 drop table t1, t2, t3;
 create table t1 (f1 int);
 insert into t1 values (1);
diff --git a/mysql-test/r/fulltext_order_by.result b/mysql-test/r/fulltext_order_by.result
index bc466b5aba7..bd3e79ec5c2 100644
--- a/mysql-test/r/fulltext_order_by.result
+++ b/mysql-test/r/fulltext_order_by.result
@@ -126,7 +126,7 @@ group by
 a.text, b.id, b.betreff
 order by 
 match(b.betreff) against ('+abc' in boolean mode) desc;
-ERROR 42S22: Unknown column 'b.betreff' in 'order clause'
+ERROR 42000: Incorrect usage/placement of 'MATCH()'
 select a.text, b.id, b.betreff
 from 
 t2 a inner join t3 b on a.id = b.forum inner join
@@ -142,7 +142,7 @@ where
 match(c.beitrag) against ('+abc' in boolean mode)
 order by 
 match(b.betreff) against ('+abc' in boolean mode) desc;
-ERROR 42S22: Unknown column 'b.betreff' in 'order clause'
+ERROR 42000: Incorrect usage/placement of 'MATCH()'
 select a.text, b.id, b.betreff
 from 
 t2 a inner join t3 b on a.id = b.forum inner join
@@ -158,7 +158,7 @@ where
 match(c.beitrag) against ('+abc' in boolean mode)
 order by 
 match(betreff) against ('+abc' in boolean mode) desc;
-text	id	betreff
+ERROR 42000: Incorrect usage/placement of 'MATCH()'
 (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;
diff --git a/mysql-test/r/sp.result b/mysql-test/r/sp.result
index 91a4ceae55c..be74186b005 100644
--- a/mysql-test/r/sp.result
+++ b/mysql-test/r/sp.result
@@ -6907,6 +6907,22 @@ CALL p1();
 CALL p1();
 DROP PROCEDURE p1;
 DROP TABLE t1;
+CREATE TABLE t1 ( f1 integer, primary key (f1));
+CREATE TABLE t2 LIKE t1;
+CREATE TEMPORARY TABLE t3 LIKE t1;
+CREATE PROCEDURE p1 () BEGIN SELECT f1 FROM t3 AS A WHERE A.f1 IN ( SELECT f1 FROM t3 ) ;
+END|
+CALL p1;
+ERROR HY000: Can't reopen table: 'A'
+CREATE VIEW t3 AS SELECT f1 FROM t2 A WHERE A.f1 IN ( SELECT f1 FROM t2 );
+DROP TABLE t3;
+CALL p1;
+f1
+CALL p1;
+f1
+DROP PROCEDURE p1;
+DROP TABLE t1, t2;
+DROP VIEW t3;
 #
 # Bug #46629: Item_in_subselect::val_int(): Assertion `0' 
 # on subquery inside a SP
diff --git a/mysql-test/r/union.result b/mysql-test/r/union.result
index 44a3812725a..dce32d2ced4 100644
--- a/mysql-test/r/union.result
+++ b/mysql-test/r/union.result
@@ -1588,3 +1588,66 @@ Warnings:
 Note	1003	select '0' AS `a` from `test`.`t1` union select '0' AS `a` from `test`.`t1` order by `a`
 DROP TABLE t1;
 End of 5.0 tests
+# 
+# Bug #49734: Crash on EXPLAIN EXTENDED UNION ... ORDER BY 
+#   <any non-const-function>
+# 
+CREATE TABLE t1 (a VARCHAR(10), FULLTEXT KEY a (a));
+INSERT INTO t1 VALUES (1),(2);
+CREATE TABLE t2 (b INT);
+INSERT INTO t2 VALUES (1),(2);
+# Should not crash
+EXPLAIN EXTENDED
+SELECT * FROM t1 UNION SELECT * FROM t1 ORDER BY a + 12;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	2	100.00	
+2	UNION	t1	ALL	NULL	NULL	NULL	NULL	2	100.00	
+NULL	UNION RESULT	<union1,2>	ALL	NULL	NULL	NULL	NULL	NULL	NULL	Using filesort
+Warnings:
+Note	1003	select `test`.`t1`.`a` AS `a` from `test`.`t1` union select `test`.`t1`.`a` AS `a` from `test`.`t1` order by (`a` + 12)
+# Should not crash
+SELECT * FROM t1 UNION SELECT * FROM t1 ORDER BY a + 12;
+a
+1
+2
+# Should not crash
+EXPLAIN EXTENDED
+SELECT * FROM t1 UNION SELECT * FROM t1
+ORDER BY MATCH(a) AGAINST ('+abc' IN BOOLEAN MODE);
+ERROR 42000: Incorrect usage/placement of 'MATCH()'
+# Should not crash
+SELECT * FROM t1 UNION SELECT * FROM t1
+ORDER BY MATCH(a) AGAINST ('+abc' IN BOOLEAN MODE);
+ERROR 42000: Incorrect usage/placement of 'MATCH()'
+# Should not crash
+(SELECT * FROM t1) UNION (SELECT * FROM t1)
+ORDER BY MATCH(a) AGAINST ('+abc' IN BOOLEAN MODE);
+a
+1
+2
+# Should not crash
+EXPLAIN EXTENDED
+SELECT * FROM t1 UNION SELECT * FROM t1
+ORDER BY (SELECT a FROM t2 WHERE b = 12);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	2	100.00	
+2	UNION	t1	ALL	NULL	NULL	NULL	NULL	2	100.00	
+3	SUBQUERY	t2	ALL	NULL	NULL	NULL	NULL	2	100.00	Using where
+NULL	UNION RESULT	<union1,2>	ALL	NULL	NULL	NULL	NULL	NULL	NULL	Using filesort
+Warnings:
+Note	1276	Field or reference 'test.t1.a' of SELECT #3 was resolved in SELECT #2
+Note	1003	select `test`.`t1`.`a` AS `a` from `test`.`t1` union select `test`.`t1`.`a` AS `a` from `test`.`t1` order by (select `test`.`t1`.`a` AS `a` from `test`.`t2` where (`test`.`t2`.`b` = 12))
+# Should not crash
+SELECT * FROM t1 UNION SELECT * FROM t1
+ORDER BY (SELECT a FROM t2 WHERE b = 12);
+a
+1
+2
+# Should not crash
+SELECT * FROM t2 UNION SELECT * FROM t2
+ORDER BY (SELECT * FROM t1 WHERE MATCH(a) AGAINST ('+abc' IN BOOLEAN MODE));
+b
+1
+2
+DROP TABLE t1,t2;
+End of 5.1 tests
diff --git a/mysql-test/r/user_var.result b/mysql-test/r/user_var.result
index 28da1dae931..be10bd9954b 100644
--- a/mysql-test/r/user_var.result
+++ b/mysql-test/r/user_var.result
@@ -409,6 +409,21 @@ SELECT a, b FROM t1 WHERE a=2 AND b=3 GROUP BY a, b;
 a	b
 2	3
 DROP TABLE t1;
+CREATE TABLE t1 (f1 int(11) default NULL, f2 int(11) default NULL);
+CREATE TABLE t2 (f1 int(11) default NULL, f2 int(11) default NULL, foo int(11));
+CREATE TABLE t3 (f1 int(11) default NULL, f2 int(11) default NULL);
+INSERT INTO t1 VALUES(10, 10);
+INSERT INTO t1 VALUES(10, 10);
+INSERT INTO t2 VALUES(10, 10, 10);
+INSERT INTO t2 VALUES(10, 10, 10);
+INSERT INTO t3 VALUES(10, 10);
+INSERT INTO t3 VALUES(10, 10);
+SELECT MIN(t2.f1),
+@bar:= (SELECT MIN(t3.f2) FROM t3 WHERE t3.f2 > foo)
+FROM t1,t2 WHERE t1.f1 = t2.f1 ORDER BY t2.f1;
+MIN(t2.f1)	@bar:= (SELECT MIN(t3.f2) FROM t3 WHERE t3.f2 > foo)
+10	NULL
+DROP TABLE t1, t2, t3;
 End of 5.0 tests
 CREATE TABLE t1 (i INT);
 CREATE TRIGGER t_after_insert AFTER INSERT ON t1 FOR EACH ROW SET @bug42188 = 10;
diff --git a/mysql-test/t/alter_table.test b/mysql-test/t/alter_table.test
index bf599818ad6..2bfe6dbaa62 100644
--- a/mysql-test/t/alter_table.test
+++ b/mysql-test/t/alter_table.test
@@ -1061,6 +1061,18 @@ ALTER TABLE t1 CHANGE COLUMN f1 f1_no_real_change TIMESTAMP NULL DEFAULT NULL;
 --disable_info
 DROP TABLE t1;
 
+
+--echo #
+--echo # Bug #31145: ALTER TABLE DROP COLUMN, ADD COLUMN crashes (linux) 
+--echo #   or freezes (win) the server
+--echo #
+
+CREATE TABLE t1 (a TEXT, id INT, b INT);
+ALTER TABLE t1 DROP COLUMN a, ADD COLUMN c TEXT FIRST;
+
+DROP TABLE t1;
+
+
 --echo End of 5.1 tests
 
 #
diff --git a/mysql-test/t/count_distinct.test b/mysql-test/t/count_distinct.test
index e63bdabdb95..d0996689aeb 100644
--- a/mysql-test/t/count_distinct.test
+++ b/mysql-test/t/count_distinct.test
@@ -35,6 +35,25 @@ insert into t1 values ('NYC Lib','New York');
 select t2.isbn,city,t1.libname,count(t1.libname) as a from t3 left join t1 on t3.libname=t1.libname left join t2 on t3.isbn=t2.isbn group by city,t1.libname;
 select t2.isbn,city,t1.libname,count(distinct t1.libname) as a from t3 left join t1 on t3.libname=t1.libname left join t2 on t3.isbn=t2.isbn group by city having count(distinct t1.libname) > 1;
 select t2.isbn,city,t1.libname,count(distinct t1.libname) as a from t3 left join t1 on t3.libname=t1.libname left join t2 on t3.isbn=t2.isbn group by city having count(distinct concat(t1.libname,'a')) > 1;
+
+select t2.isbn,city,@bar:=t1.libname,count(distinct t1.libname) as a
+       from t3 left join t1 on t3.libname=t1.libname left join t2
+       on t3.isbn=t2.isbn group by city having count(distinct
+       t1.libname) > 1;
+#
+# Wrong result, see bug#49872
+# 
+SELECT @bar;
+
+select t2.isbn,city,concat(@bar:=t1.libname),count(distinct t1.libname) as a
+       from t3 left join t1 on t3.libname=t1.libname left join t2
+       on t3.isbn=t2.isbn group by city having count(distinct
+       t1.libname) > 1;
+#
+# Wrong result, see bug#49872
+# 
+SELECT @bar;
+
 drop table t1, t2, t3;
 
 #
diff --git a/mysql-test/t/fulltext_order_by.test b/mysql-test/t/fulltext_order_by.test
index 074aefbf943..814cd4a5954 100644
--- a/mysql-test/t/fulltext_order_by.test
+++ b/mysql-test/t/fulltext_order_by.test
@@ -80,7 +80,7 @@ CREATE TABLE t3 (
   FULLTEXT KEY betreff (betreff)
 ) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=996 ;
 
---error 1054
+--error ER_CANT_USE_OPTION_HERE
 select a.text, b.id, b.betreff
 from 
   t2 a inner join t3 b on a.id = b.forum inner join
@@ -100,7 +100,7 @@ group by
 order by 
   match(b.betreff) against ('+abc' in boolean mode) desc;
   
---error 1054
+--error ER_CANT_USE_OPTION_HERE
 select a.text, b.id, b.betreff
 from 
   t2 a inner join t3 b on a.id = b.forum inner join
@@ -117,6 +117,7 @@ where
 order by 
   match(b.betreff) against ('+abc' in boolean mode) desc;
 
+--error ER_CANT_USE_OPTION_HERE
 select a.text, b.id, b.betreff
 from 
   t2 a inner join t3 b on a.id = b.forum inner join
diff --git a/mysql-test/t/sp.test b/mysql-test/t/sp.test
index b35c10450a2..b7ce4b7986d 100644
--- a/mysql-test/t/sp.test
+++ b/mysql-test/t/sp.test
@@ -8241,6 +8241,25 @@ while ($tab_count)
 DROP PROCEDURE p1;
 DROP TABLE t1;
 
+#
+# Bug#47649 crash during CALL procedure
+#
+CREATE TABLE t1 ( f1 integer, primary key (f1));
+CREATE TABLE t2 LIKE t1;
+CREATE TEMPORARY TABLE t3 LIKE t1;
+delimiter |;
+CREATE PROCEDURE p1 () BEGIN SELECT f1 FROM t3 AS A WHERE A.f1 IN ( SELECT f1 FROM t3 ) ;
+END|
+delimiter ;|
+--error ER_CANT_REOPEN_TABLE
+CALL p1;
+CREATE VIEW t3 AS SELECT f1 FROM t2 A WHERE A.f1 IN ( SELECT f1 FROM t2 );
+DROP TABLE t3;
+CALL p1;
+CALL p1;
+DROP PROCEDURE p1;
+DROP TABLE t1, t2;
+DROP VIEW t3;
 
 --echo #
 --echo # Bug #46629: Item_in_subselect::val_int(): Assertion `0' 
diff --git a/mysql-test/t/union.test b/mysql-test/t/union.test
index ec169838d59..91fc9546bbe 100644
--- a/mysql-test/t/union.test
+++ b/mysql-test/t/union.test
@@ -1102,3 +1102,56 @@ DROP TABLE t1;
 
 
 --echo End of 5.0 tests
+
+
+--echo # 
+--echo # Bug #49734: Crash on EXPLAIN EXTENDED UNION ... ORDER BY 
+--echo #   <any non-const-function>
+--echo # 
+
+CREATE TABLE t1 (a VARCHAR(10), FULLTEXT KEY a (a));
+INSERT INTO t1 VALUES (1),(2);
+CREATE TABLE t2 (b INT);
+INSERT INTO t2 VALUES (1),(2);
+
+--echo # Should not crash
+EXPLAIN EXTENDED
+SELECT * FROM t1 UNION SELECT * FROM t1 ORDER BY a + 12;
+
+--echo # Should not crash
+SELECT * FROM t1 UNION SELECT * FROM t1 ORDER BY a + 12;
+
+
+--echo # Should not crash
+--error ER_CANT_USE_OPTION_HERE
+EXPLAIN EXTENDED
+SELECT * FROM t1 UNION SELECT * FROM t1
+  ORDER BY MATCH(a) AGAINST ('+abc' IN BOOLEAN MODE);
+
+--echo # Should not crash
+--error ER_CANT_USE_OPTION_HERE
+SELECT * FROM t1 UNION SELECT * FROM t1
+  ORDER BY MATCH(a) AGAINST ('+abc' IN BOOLEAN MODE);
+
+--echo # Should not crash
+(SELECT * FROM t1) UNION (SELECT * FROM t1)
+  ORDER BY MATCH(a) AGAINST ('+abc' IN BOOLEAN MODE);
+
+
+--echo # Should not crash
+EXPLAIN EXTENDED
+SELECT * FROM t1 UNION SELECT * FROM t1
+  ORDER BY (SELECT a FROM t2 WHERE b = 12);
+
+--echo # Should not crash
+SELECT * FROM t1 UNION SELECT * FROM t1
+  ORDER BY (SELECT a FROM t2 WHERE b = 12);
+
+--echo # Should not crash
+SELECT * FROM t2 UNION SELECT * FROM t2
+  ORDER BY (SELECT * FROM t1 WHERE MATCH(a) AGAINST ('+abc' IN BOOLEAN MODE));
+
+DROP TABLE t1,t2;
+
+
+--echo End of 5.1 tests
diff --git a/mysql-test/t/user_var.test b/mysql-test/t/user_var.test
index c0740458a88..b6b58e7bd46 100644
--- a/mysql-test/t/user_var.test
+++ b/mysql-test/t/user_var.test
@@ -295,6 +295,26 @@ SELECT @a, @b;
 SELECT a, b FROM t1 WHERE a=2 AND b=3 GROUP BY a, b;
 DROP TABLE t1;
 
+#
+# Bug#47371: reference by same column name
+#
+CREATE TABLE t1 (f1 int(11) default NULL, f2 int(11) default NULL);
+CREATE TABLE t2 (f1 int(11) default NULL, f2 int(11) default NULL, foo int(11));
+CREATE TABLE t3 (f1 int(11) default NULL, f2 int(11) default NULL);
+
+INSERT INTO t1 VALUES(10, 10);
+INSERT INTO t1 VALUES(10, 10);
+INSERT INTO t2 VALUES(10, 10, 10);
+INSERT INTO t2 VALUES(10, 10, 10);
+INSERT INTO t3 VALUES(10, 10);
+INSERT INTO t3 VALUES(10, 10);
+
+SELECT MIN(t2.f1),
+@bar:= (SELECT MIN(t3.f2) FROM t3 WHERE t3.f2 > foo)
+FROM t1,t2 WHERE t1.f1 = t2.f1 ORDER BY t2.f1;
+
+DROP TABLE t1, t2, t3;
+
 --echo End of 5.0 tests
 
 #