Merge

mysql-test/t/range.test:
  Auto merged
sql/item.cc:
  Auto merged
sql/item.h:
  Auto merged
sql/item_cmpfunc.cc:
  Auto merged
sql/item_cmpfunc.h:
  Auto merged
sql/item_func.cc:
  Auto merged
sql/opt_range.cc:
  Auto merged
sql/opt_sum.cc:
  Auto merged
sql/sql_list.h:
  Auto merged
sql/sql_select.h:
  Auto merged

24 files changed, 312 insertions, 61 deletions

diff --git a/mysql-test/t/bigint.test b/mysql-test/t/bigint.test
index 9ac44c8ba0a..5c977983087 100644
--- a/mysql-test/t/bigint.test
+++ b/mysql-test/t/bigint.test
@@ -22,7 +22,7 @@ select -(0-3),round(-(0-3)), round(9999999999999999999);
 #
 
 create table t1 (a bigint unsigned not null, primary key(a));
-insert into t1 values (18446744073709551615), (0xFFFFFFFFFFFFFFFE);
+insert into t1 values (18446744073709551615), (0xFFFFFFFFFFFFFFFE), (18446744073709551613), (18446744073709551612);
 select * from t1;
 select * from t1 where a=18446744073709551615;
 # select * from t1 where a='18446744073709551615';
@@ -54,3 +54,17 @@ insert into t1 values (null,1);
 select * from t1;
 select * from t1 limit 9999999999;
 drop table t1;
+
+#
+# Item_uint::save_to_field()
+# BUG#1845
+# This can't be fixed in MySQL 4.0 without loosing precisions for bigints
+#
+
+CREATE TABLE t1 ( quantity decimal(60,0));
+insert into t1 values (10000000000000000000);
+insert into t1 values (10000000000000000000.0);
+insert into t1 values ('10000000000000000000');
+select * from t1;
+drop table t1;
+
diff --git a/mysql-test/t/case.test b/mysql-test/t/case.test
index 9a1b6b6bbfe..9377c6c4aef 100644
--- a/mysql-test/t/case.test
+++ b/mysql-test/t/case.test
@@ -57,7 +57,8 @@ CREATE TABLE t1 SELECT
  CASE WHEN 1 THEN 'a' ELSE 1.0 END AS c5,
  CASE WHEN 1 THEN 1.0 ELSE 'a' END AS c6,
  CASE WHEN 1 THEN  1  ELSE 1.0 END AS c7,
- CASE WHEN 1 THEN 1.0 ELSE  1  END AS c8
+ CASE WHEN 1 THEN 1.0 ELSE  1  END AS c8,
+ CASE WHEN 1 THEN 1.0 END AS c9
 ;
 SHOW CREATE TABLE t1;
 DROP TABLE t1;
diff --git a/mysql-test/t/count_distinct3.test b/mysql-test/t/count_distinct3.test
index e6cc98d47df..9d2bb0d139a 100644
--- a/mysql-test/t/count_distinct3.test
+++ b/mysql-test/t/count_distinct3.test
@@ -21,6 +21,7 @@ while ($1)
   INSERT INTO t1 (id, grp, id_rev) VALUES (@id, @grp, @id_rev); 
   dec $1;
 }
+set @@read_buffer_size=2*1024*1024;
 CREATE TABLE t2 SELECT * FROM t1;
 INSERT INTO t1 (id, grp, id_rev) SELECT id, grp, id_rev FROM t2;
 INSERT INTO t2 (id, grp, id_rev) SELECT id, grp, id_rev FROM t1;
@@ -53,3 +54,5 @@ SELECT COUNT(*) FROM t1;
 SELECT COUNT(DISTINCT id) FROM t1 GROUP BY grp;
 --enable_result_log
 DROP TABLE t1;
+
+set @@read_buffer_size=default;
diff --git a/mysql-test/t/create.test b/mysql-test/t/create.test
index 0c1280751bc..dc4d2701bee 100644
--- a/mysql-test/t/create.test
+++ b/mysql-test/t/create.test
@@ -4,6 +4,7 @@
 
 --disable_warnings
 drop table if exists t1,t2,t3;
+drop database if exists test_$1;
 --enable_warnings
 
 create table t1 (b char(0));
@@ -57,6 +58,7 @@ select 1ea10.1a20,1e+ 1e+10 from 1ea10;
 drop table 1ea10;
 create table t1 (t1.index int);
 drop table t1;
+# Test that we get warning for this
 drop database if exists test_$1;
 create database test_$1;
 create table test_$1.$test1 (a$1 int, $b int, c$ int);
@@ -245,7 +247,6 @@ drop table t3;
 show create table t3;
 select * from t3;
 drop table t2, t3;
-drop database if exists test_$1;
 create database test_$1;
 create table test_$1.t3 like t1;
 create temporary table t3 like test_$1.t3;
@@ -326,9 +327,6 @@ drop table t1, t2;
 # Bug #1209
 #
 
---disable_warnings
-drop database if exists test_$1;
---enable_warnings
 create database test_$1;
 use test_$1;
 select database();
diff --git a/mysql-test/t/date_formats.test b/mysql-test/t/date_formats.test
index d9219d3ac2e..7b88c0ecf72 100644
--- a/mysql-test/t/date_formats.test
+++ b/mysql-test/t/date_formats.test
@@ -200,3 +200,12 @@ select get_format(DATE, 'TEST') as a;
 select str_to_date('15-01-2001 12:59:59', GET_FORMAT(DATE,'USA'));
 
 explain extended select makedate(1997,1), addtime("31.12.97 11.59.59.999999 PM", "1 1.1.1.000002"),subtime("31.12.97 11.59.59.999999 PM", "1 1.1.1.000002"),timediff("01.01.97 11:59:59.000001 PM","31.12.95 11:59:59.000002 PM"),cast(str_to_date("15-01-2001 12:59:59", "%d-%m-%Y %H:%i:%S") as TIME), maketime(23,11,12),microsecond("1997-12-31 23:59:59.000001");
+
+#
+# Test of date_format()
+#
+
+create table t1 (d date);
+insert into t1 values ('2004-07-14'),('2005-07-14');
+select date_format(d,"%d") from t1 order by 1;
+drop table t1;
diff --git a/mysql-test/t/derived.test b/mysql-test/t/derived.test
index ee50ffda476..caf673d95c1 100644
--- a/mysql-test/t/derived.test
+++ b/mysql-test/t/derived.test
@@ -132,3 +132,9 @@ insert into t1 VALUES(1,1,1), (2,2,1);
 select count(*) from t1 INNER JOIN (SELECT A.E1, A.E2, A.E3 FROM t1 AS A WHERE A.E3 = (SELECT MAX(B.E3) FROM t1 AS B WHERE A.E2 = B.E2)) AS THEMAX ON t1.E1 = THEMAX.E2 AND t1.E1 = t1.E2;
 explain select count(*) from t1 INNER JOIN (SELECT A.E1, A.E2, A.E3 FROM t1 AS A WHERE A.E3 = (SELECT MAX(B.E3) FROM t1 AS B WHERE A.E2 = B.E2)) AS THEMAX ON t1.E1 = THEMAX.E2 AND t1.E1 = t1.E2;
 drop table t1;
+
+create table t1 (a int);
+insert into t1 values (1),(2);
+select * from ( select * from t1 union select * from t1) a,(select * from t1 union select * from t1) b;
+explain select * from ( select * from t1 union select * from t1) a,(select * from t1 union select * from t1) b;
+drop table t1;
diff --git a/mysql-test/t/fulltext.test b/mysql-test/t/fulltext.test
index a0292740c8d..38c09682bac 100644
--- a/mysql-test/t/fulltext.test
+++ b/mysql-test/t/fulltext.test
@@ -81,6 +81,17 @@ select * from t1;
 drop table t1;
 
 #
+# why to scan strings for trunc*
+#
+create table t1 (a varchar(200) not null, fulltext (a));
+insert t1 values ("aaa10 bbb20"), ("aaa20 bbb15"), ("aaa30 bbb10");
+select * from t1 where match a against ("+aaa* +bbb*" in boolean mode);
+select * from t1 where match a against ("+aaa* +bbb1*" in boolean mode);
+select * from t1 where match a against ("+aaa* +ccc*" in boolean mode);
+select * from t1 where match a against ("+aaa10 +(bbb*)" in boolean mode);
+drop table t1;
+
+#
 # Check bug reported by Matthias Urlichs
 #
 
diff --git a/mysql-test/t/fulltext_left_join.test b/mysql-test/t/fulltext_left_join.test
index 855649923c4..e3083ddc4e1 100644
--- a/mysql-test/t/fulltext_left_join.test
+++ b/mysql-test/t/fulltext_left_join.test
@@ -41,5 +41,7 @@ create table t2 (name varchar(255) not null default '', entity_id int(11) not nu
 insert into t2 (name, entity_id) values ('aberdeen town hall', 1), ('glasgow royal concert hall', 2), ('queen\'s hall, edinburgh', 3);
 select * from t1 left join t2 on venue_id = entity_id where match(name) against('aberdeen' in boolean mode) and dt = '2003-05-23 19:30:00';
 select * from t1 left join t2 on venue_id = entity_id where match(name) against('aberdeen') and dt = '2003-05-23 19:30:00';
+select * from t1 left join t2 on (venue_id = entity_id and match(name) against('aberdeen' in boolean mode)) where dt = '2003-05-23 19:30:00';
+select * from t1 left join t2 on (venue_id = entity_id and match(name) against('aberdeen')) where dt = '2003-05-23 19:30:00';
 drop table t1,t2;
 
diff --git a/mysql-test/t/mix_innodb_myisam_binlog.test b/mysql-test/t/mix_innodb_myisam_binlog.test
index 314587feda6..26f7cbf22a0 100644
--- a/mysql-test/t/mix_innodb_myisam_binlog.test
+++ b/mysql-test/t/mix_innodb_myisam_binlog.test
@@ -3,7 +3,7 @@
 # It would be nice to make this a replication test, but in 4.0 the
 # slave is always with --skip-innodb in the testsuite. I (Guilhem) however
 # did some tests manually on a slave; tables are replicated fine and
-# Exec_master_log_pos advances as expected.
+# Exec_Master_Log_Pos advances as expected.
 
 -- source include/have_innodb.inc
 
diff --git a/mysql-test/t/null.test b/mysql-test/t/null.test
index 0be755ba7ad..5fa8f8a7651 100644
--- a/mysql-test/t/null.test
+++ b/mysql-test/t/null.test
@@ -86,3 +86,14 @@ INSERT INTO t1 (d) values (null),(null);
 select * from t1;
 drop table t1;
 
+#
+# Test to check elimination of IS NULL predicate for a non-nullable attribute
+# (bug #1990)  
+#
+create table t1 (a int not null, b int not null, index idx(a));
+insert into t1 values
+  (1,1), (2,2), (3,3), (4,4), (5,5), (6,6),
+  (7,7), (8,8), (9,9), (10,10), (11,11), (12,12);
+explain select * from t1 where a between 2 and 3;
+explain select * from t1 where a between 2 and 3 or b is null;
+drop table t1;
diff --git a/mysql-test/t/order_by.test b/mysql-test/t/order_by.test
index 702feedc3ce..ca4143fa1c9 100644
--- a/mysql-test/t/order_by.test
+++ b/mysql-test/t/order_by.test
@@ -365,3 +365,11 @@ SELECT * FROM t1 WHERE FieldKey > '2' ORDER BY LongVal;
 EXPLAIN SELECT * FROM t1 WHERE FieldKey > '2' ORDER BY FieldKey, LongVal;
 SELECT * FROM t1 WHERE FieldKey > '2' ORDER BY FieldKey, LongVal;
 DROP TABLE t1;
+#
+# Bug #1945 - Crashing bug with bad User Variables in UPDATE ... ORDER BY ...
+#
+CREATE TABLE t1 (a INT, b INT);
+SET @id=0;
+UPDATE t1 SET a=0 ORDER BY (a=@id), b;
+DROP TABLE t1;
+
diff --git a/mysql-test/t/raid.test b/mysql-test/t/raid.test
index 395a04615cb..0d6e851a153 100644
--- a/mysql-test/t/raid.test
+++ b/mysql-test/t/raid.test
@@ -9,6 +9,7 @@ enable_query_log;
 
 --disable_warnings
 DROP TABLE IF EXISTS t1,t2;
+DROP DATABASE IF EXISTS test_$1;
 --enable_warnings
 
 #
@@ -111,8 +112,8 @@ ALTER TABLE t1 DROP COLUMN x;
 ALTER TABLE t1 RENAME t2;
 select count(*) from t2;
 DROP TABLE t2;
+
 /* variable rows */
-DROP TABLE IF EXISTS t2;
 CREATE TABLE t1 (
 id int unsigned not null auto_increment primary key,
 c varchar(255) not null
diff --git a/mysql-test/t/range.test b/mysql-test/t/range.test
index a1d227459fd..bdbbce5cdfb 100644
--- a/mysql-test/t/range.test
+++ b/mysql-test/t/range.test
@@ -226,7 +226,18 @@ INSERT INTO t1 VALUES
 # we expect that optimizer will choose index on A
 EXPLAIN SELECT * FROM t1 WHERE a IN(1,2) AND b=5;
 SELECT * FROM t1 WHERE a IN(1,2) AND b=5;
+DROP TABLE t1;
+
+#
+# Test error with 
+#
 
+CREATE TABLE t1 (a int, b int, c int, INDEX (c,a,b));
+INSERT INTO t1 VALUES (1,0,0),(1,0,0),(1,0,0);
+INSERT INTO t1 VALUES (0,1,0),(0,1,0),(0,1,0);
+# -- First reports 3; second reports 6
+SELECT COUNT(*) FROM t1 WHERE (c=0 and a=1) or (c=0 and b=1);
+SELECT COUNT(*) FROM t1 WHERE (c=0 and b=1) or (c=0 and a=1);
 DROP TABLE t1;
 
 # test for a bug with in() and unique key
diff --git a/mysql-test/t/rpl_change_master.test b/mysql-test/t/rpl_change_master.test
new file mode 100644
index 00000000000..e6452b5b619
--- /dev/null
+++ b/mysql-test/t/rpl_change_master.test
@@ -0,0 +1,30 @@
+source include/master-slave.inc;
+
+connection slave;
+select get_lock("a",5);
+connection master;
+create table t1(n int);
+insert into t1 values(1+get_lock("a",15)*0);
+insert into t1 values(2);
+save_master_pos;
+connection slave;
+--real_sleep 3; # can't sync_with_master as we should be blocked
+stop slave;
+select * from t1;
+--replace_result $MASTER_MYPORT MASTER_MYPORT
+--replace_column 1 # 33 #
+show slave status;
+change master to master_user='root';
+--replace_result $MASTER_MYPORT MASTER_MYPORT
+--replace_column 1 # 33 #
+show slave status;
+# Will restart from after the values(2), which is bug
+select release_lock("a");
+start slave;
+sync_with_master;
+select * from t1;
+connection master;
+drop table t1;
+save_master_pos;
+connection slave;
+sync_with_master;
diff --git a/mysql-test/t/rpl_loaddata_rule_m.test b/mysql-test/t/rpl_loaddata_rule_m.test
index ec3a9259e32..678dae13889 100644
--- a/mysql-test/t/rpl_loaddata_rule_m.test
+++ b/mysql-test/t/rpl_loaddata_rule_m.test
@@ -9,7 +9,7 @@ drop database if exists mysqltest;
 --enable_warnings
 
 connection slave;
-reset master;
+stop slave; # don't need slave for this test
 
 # Test logging on master
 
diff --git a/mysql-test/t/rpl_max_relay_size.test b/mysql-test/t/rpl_max_relay_size.test
index 99f0a9fdde6..a28aed52ec8 100644
--- a/mysql-test/t/rpl_max_relay_size.test
+++ b/mysql-test/t/rpl_max_relay_size.test
@@ -76,7 +76,7 @@ sync_with_master;
 --replace_result $MASTER_MYPORT MASTER_PORT 3306 MASTER_PORT 3334 MASTER_PORT
 --replace_column 1 # 33 #
 show slave status;
-# one more rotation, to be sure Relay_log_space is correctly updated
+# one more rotation, to be sure Relay_Log_Space is correctly updated
 flush logs;
 connection master;
 drop table t1;
diff --git a/mysql-test/t/rpl_rotate_logs.test b/mysql-test/t/rpl_rotate_logs.test
index 78213c6a750..7560d56af1c 100644
--- a/mysql-test/t/rpl_rotate_logs.test
+++ b/mysql-test/t/rpl_rotate_logs.test
@@ -101,7 +101,8 @@ show master logs;
 # we just tests if synonyms are accepted
 purge binary logs to 'master-bin.000002';
 show binary logs;
---sleep 1;
+# sleeping 10 seconds or more would make the slave believe connection is down
+--real_sleep 1;
 purge master logs before now();
 show binary logs;
 insert into t2 values (65);
diff --git a/mysql-test/t/rpl_until.test b/mysql-test/t/rpl_until.test
index 8b20a493826..9bc4ea4e7b1 100644
--- a/mysql-test/t/rpl_until.test
+++ b/mysql-test/t/rpl_until.test
@@ -38,7 +38,7 @@ start slave until master_log_file='master-no-such-bin.000001', master_log_pos=29
 select * from t1;
 sleep 2;
 --replace_result $MASTER_MYPORT MASTER_MYPORT
---replace_column 1 # 33 #
+--replace_column 1 # 9 # 23 # 33 #
 show slave status;
 
 # try replicate all until second insert to t2;
@@ -46,7 +46,7 @@ start slave until relay_log_file='slave-relay-bin.000002', relay_log_pos=537;
 sleep 2;
 select * from t2;
 --replace_result $MASTER_MYPORT MASTER_MYPORT
---replace_column 1 # 33 #
+--replace_column 1 # 9 # 23 # 33 #
 show slave status;
 
 # clean up
@@ -62,7 +62,7 @@ start slave until master_log_file='master-bin.000001', master_log_pos=561;
 sleep 2;
 # here the sql slave thread should be stopped
 --replace_result $MASTER_MYPORT MASTER_MYPORT
---replace_column 1 # 33 #
+--replace_column 1 # 9 # 23 # 33 #
 show slave status;
 
 #testing various error conditions
diff --git a/mysql-test/t/sp-error.test b/mysql-test/t/sp-error.test
index 132a6b086c6..50a8b8c8768 100644
--- a/mysql-test/t/sp-error.test
+++ b/mysql-test/t/sp-error.test
@@ -32,18 +32,18 @@ create function func1() returns int
   return 42|
 
 # Can't create recursively
---error 1284
+--error 1285
 create procedure foo()
   create procedure bar() set @x=3|
---error 1284
+--error 1285
 create procedure foo()
   create function bar() returns double return 2.3|
 
 # Already exists
---error 1285
+--error 1286
 create procedure proc1()
   set @x = 42|
---error 1285
+--error 1286
 create function func1() returns int
   return 42|
 
@@ -51,39 +51,39 @@ drop procedure proc1|
 drop function func1|
 
 # Does not exist
---error 1286
+--error 1287
 alter procedure foo|
---error 1286
+--error 1287
 alter function foo|
---error 1286
+--error 1287
 drop procedure foo|
---error 1286
+--error 1287
 drop function foo|
---error 1286
+--error 1287
 call foo()|
 drop procedure if exists foo|
---error 1286
+--error 1287
 show create procedure foo|
 
 # LEAVE/ITERATE with no match
---error 1289
+--error 1290
 create procedure foo()
 foo: loop
   leave bar;
 end loop|
---error 1289
+--error 1290
 create procedure foo()
 foo: loop
   iterate bar;
 end loop|
---error 1289
+--error 1290
 create procedure foo()
 foo: begin
   iterate foo;
 end|
 
 # Redefining label
---error 1290 
+--error 1291 
 create procedure foo()
 foo: loop
   foo: loop
@@ -92,7 +92,7 @@ foo: loop
 end loop foo|
 
 # End label mismatch
---error 1291
+--error 1292
 create procedure foo()
 foo: loop
   set @x=2;
@@ -113,17 +113,17 @@ begin
   select name from mysql.proc;
   select type from mysql.proc;
 end|
---error 1293
+--error 1294
 call foo()|
 drop procedure foo|
 
 # RETURN in FUNCTION only
---error 1294
+--error 1295
 create procedure foo()
   return 42|
 
 # Doesn't allow queries in FUNCTIONs (for now :-( )
---error 1295
+--error 1296
 create function foo() returns int
 begin
   declare x int;
@@ -137,19 +137,19 @@ create procedure p(x int)
 create function f(x int) returns int
   return x+42|
 
---error 1299
+--error 1300
 call p()|
---error 1299
+--error 1300
 call p(1, 2)|
---error 1299
+--error 1300
 select f()|
---error 1299
+--error 1300
 select f(1, 2)|
 
 drop procedure p|
 drop function f|
 
---error 1300
+--error 1301
 create procedure p(val int, out res int)
 begin
   declare x int default 0;
@@ -163,7 +163,7 @@ begin
   end if;
 end|
 
---error 1300
+--error 1301
 create procedure p(val int, out res int)
 begin
   declare x int default 0;
@@ -178,7 +178,7 @@ begin
   end if;
 end|
 
---error 1301
+--error 1302
 create function f(val int) returns int
 begin
   declare x int;
@@ -196,12 +196,12 @@ begin
   end if;
 end|
 
---error 1302
+--error 1303
 select f(10)|
 
 drop function f|
 
---error 1303
+--error 1304
 create procedure p()
 begin
   declare c cursor for insert into test.t1 values ("foo", 42);
@@ -210,7 +210,7 @@ begin
   close c;
 end|
 
---error 1304
+--error 1305
 create procedure p()
 begin
   declare x int;
@@ -220,7 +220,7 @@ begin
   close c;
 end|
 
---error 1305
+--error 1306
 create procedure p()
 begin
   declare c cursor for select * from test.t;
@@ -242,7 +242,7 @@ begin
   open c;
   close c;
 end|
---error 1306
+--error 1307
 call p()|
 drop procedure p|
 
@@ -254,11 +254,11 @@ begin
   close c;
   close c;
 end|
---error 1307
+--error 1308
 call p()|
 drop procedure p|
 
---error 1286
+--error 1287
 alter procedure bar3 sql security invoker|
 --error 1059
 alter procedure bar3 name
@@ -272,7 +272,7 @@ drop table if exists t1|
 create table t1 (val int, x float)|
 insert into t1 values (42, 3.1), (19, 1.2)|
 
---error 1308
+--error 1309
 create procedure p()
 begin
   declare c cursor for select * from t1;
@@ -292,7 +292,7 @@ begin
   fetch c into x;
   close c;
 end|
---error 1309
+--error 1310
 call p()|
 drop procedure p|
 
@@ -307,34 +307,34 @@ begin
   fetch c into x, y, z;
   close c;
 end|
---error 1309
+--error 1310
 call p()|
 drop procedure p|
 
---error 1311
+--error 1312
 create procedure p(in x int, x char(10))
 begin
 end|
---error 1311
+--error 1312
 create function p(x int, x char(10))
 begin
 end|
 
---error 1312
+--error 1313
 create procedure p()
 begin
   declare x float;
   declare x int;
 end|
 
---error 1313
+--error 1314
 create procedure p()
 begin
   declare c condition for 1064;
   declare c condition for 1065;
 end|
 
---error 1314
+--error 1315
 create procedure p()
 begin
   declare c cursor for select * from t1;
@@ -358,7 +358,7 @@ drop procedure bug1965|
 #
 # BUG#1966
 #
---error 1308
+--error 1309
 select 1 into a|
 
 
diff --git a/mysql-test/t/sp.test b/mysql-test/t/sp.test
index d8530b5130b..25657bd79e2 100644
--- a/mysql-test/t/sp.test
+++ b/mysql-test/t/sp.test
@@ -734,7 +734,7 @@ create procedure hndlr4()
 begin
   declare x int default 0;
   declare val int;	                           # No default
-  declare continue handler for 1310 set x=1;
+  declare continue handler for sqlstate '02000' set x=1;
 
   select data into val from test.t3 where id='z' limit 1;  # No hits
 
@@ -753,7 +753,7 @@ drop procedure hndlr4|
 create procedure cur1()
 begin
   declare done int default 0;
-  declare continue handler for 1310 set done = 1;
+  declare continue handler for sqlstate '02000' set done = 1;
   declare c cursor for select * from test.t2;
   declare a char(16);
   declare b int;
@@ -782,7 +782,7 @@ create table t3 ( s char(16), i int )|
 create procedure cur2()
 begin
   declare done int default 0;
-  declare continue handler for 1310 set done = 1;
+  declare continue handler for sqlstate '02000' set done = 1;
   declare c1 cursor for select id,data from test.t1;
   declare c2 cursor for select i from test.t2;
 
diff --git a/mysql-test/t/subselect.test b/mysql-test/t/subselect.test
index 3126c5d55af..f0df84a6a93 100644
--- a/mysql-test/t/subselect.test
+++ b/mysql-test/t/subselect.test
@@ -67,7 +67,7 @@ insert into t4 values (4,8),(3,8),(5,9);
 select (select a from t1 where t1.a = a1) as a2, (select b from t2 where t2.b=a2) as a1;
 select (select a from t1 where t1.a=t2.a), a from t2;
 select (select a from t1 where t1.a=t2.b), a from t2;
-select (select a from t1), a from t2;
+select (select a from t1), a, (select 1 union select 2 limit 1) from t2;
 select (select a from t3), a from t2;
 select * from t2 where t2.a=(select a from t1);
 insert into t3 values (6),(7),(3);
diff --git a/mysql-test/t/symlink.test b/mysql-test/t/symlink.test
index 4be0cd0c6a2..f618c342936 100644
--- a/mysql-test/t/symlink.test
+++ b/mysql-test/t/symlink.test
@@ -65,7 +65,7 @@ drop table t1;
 #
 
 disable_query_log;
---error 1279,1279
+--error 1103,1103
 create table t1 (a int not null auto_increment, b char(16) not null, primary key (a)) type=myisam data directory="tmp";
 
 # Check that we cannot link over a table from another database.
@@ -75,7 +75,7 @@ create database mysqltest;
 --error 1,1
 create table mysqltest.t9 (a int not null auto_increment, b char(16) not null, primary key (a)) type=myisam index directory="/this-dir-does-not-exist";
 
---error 1279,1279
+--error 1103,1103
 create table mysqltest.t9 (a int not null auto_increment, b char(16) not null, primary key (a)) type=myisam index directory="not-hard-path";
 
 --error 1,1
diff --git a/mysql-test/t/union.test b/mysql-test/t/union.test
index 3cfdc14b0b8..e302378e117 100644
--- a/mysql-test/t/union.test
+++ b/mysql-test/t/union.test
@@ -302,3 +302,137 @@ insert into t1 values (NULL, 'a', 1), (NULL, 'b', 2), (NULL, 'c', 3), (NULL, 'd'
 insert into t2 values (1, 100), (1, 101), (1, 102), (2, 100), (2, 103), (2, 104), (3, 101), (3, 102), (3, 105);
 SELECT mp.pla_id, MIN(m1.matintnum) AS matintnum FROM t2 mp INNER JOIN t1 m1 ON mp.mat_id=m1.mat_id GROUP BY mp.pla_id union SELECT 0, 0;
 drop table t1, t2;
+
+#
+# types conversions
+#
+create table t1 SELECT "a" as a UNION select "aa" as a;
+select * from t1;
+show create table t1;
+drop table t1;
+create table t1 SELECT 12 as a UNION select "aa" as a;
+select * from t1;
+show create table t1;
+drop table t1;
+create table t1 SELECT 12 as a UNION select 12.2 as a;
+select * from t1;
+show create table t1;
+drop table t1;
+
+create table t2 (it1 tinyint, it2 tinyint not null, i int not null, ib bigint, f float, d double, y year, da date, dt datetime, sc char(10), sv varchar(10), b blob, tx text);
+insert into t2 values (NULL, 1, 3, 4, 1.5, 2.5, 1972, '1972-10-22', '1972-10-22 11:50', 'testc', 'testv', 'tetetetetest', 'teeeeeeeeeeeest');
+
+create table t1 SELECT it2 from t2 UNION select it1 from t2;
+select * from t1;
+show create table t1;
+drop table t1;
+create table t1 SELECT it2 from t2 UNION select i from t2;
+select * from t1;
+show create table t1;
+drop table t1;
+create table t1 SELECT i from t2 UNION select f from t2;
+select * from t1;
+show create table t1;
+drop table t1;
+create table t1 SELECT f from t2 UNION select d from t2;
+select * from t1;
+show create table t1;
+drop table t1;
+create table t1 SELECT ib from t2 UNION select f from t2;
+select * from t1;
+show create table t1;
+drop table t1;
+create table t1 SELECT ib from t2 UNION select d from t2;
+select * from t1;
+show create table t1;
+drop table t1;
+create table t1 SELECT f from t2 UNION select y from t2;
+select * from t1;
+show create table t1;
+drop table t1;
+create table t1 SELECT f from t2 UNION select da from t2;
+select * from t1;
+show create table t1;
+drop table t1;
+create table t1 SELECT y from t2 UNION select da from t2;
+select * from t1;
+show create table t1;
+drop table t1;
+create table t1 SELECT y from t2 UNION select dt from t2;
+select * from t1;
+show create table t1;
+drop table t1;
+create table t1 SELECT da from t2 UNION select dt from t2;
+select * from t1;
+show create table t1;
+drop table t1;
+create table t1 SELECT dt from t2 UNION select sc from t2;
+select * from t1;
+show create table t1;
+drop table t1;
+create table t1 SELECT dt from t2 UNION select sv from t2;
+select * from t1;
+show create table t1;
+drop table t1;
+create table t1 SELECT sc from t2 UNION select sv from t2;
+select * from t1;
+show create table t1;
+drop table t1;
+create table t1 SELECT dt from t2 UNION select b from t2;
+select * from t1;
+show create table t1;
+drop table t1;
+create table t1 SELECT sv from t2 UNION select b from t2;
+select * from t1;
+show create table t1;
+drop table t1;
+create table t1 SELECT i from t2 UNION select d from t2 UNION select b from t2;
+select * from t1;
+show create table t1;
+drop table t1;
+create table t1 SELECT sv from t2 UNION select tx from t2;
+select * from t1;
+show create table t1;
+drop table t1;
+create table t1 SELECT b from t2 UNION select tx from t2;
+select * from t1;
+show create table t1;
+drop table t1,t2;
+create table t1 (d decimal(10,1));
+create table t2 (d decimal(10,9));
+insert into t1 values ("100000000.0");
+insert into t2 values ("1.23456780");
+create table t3 select * from t2 union select * from t1;
+select * from t3;
+show create table t3;
+drop table t1,t2,t3;
+create table t1 select 1 union select -1;
+select * from t1;
+show create table t1;
+drop table t1;
+-- error 1266
+create table t1 select _latin1"test" union select _latin2"testt" ;
+create table t1 select _latin2"test" union select _latin2"testt" ;
+show create table t1;
+drop table t1;
+
+#
+# conversion memory->disk table
+#
+#
+# conversion memory->disk table
+#
+create table t1 (s char(200));
+insert into t1 values (repeat("1",200));
+create table t2 select * from t1;
+insert into t2 select * from t1;
+insert into t1 select * from t2;
+insert into t2 select * from t1;
+insert into t1 select * from t2;
+insert into t2 select * from t1;
+set local tmp_table_size=1024;
+select count(*) from (select * from t1 union all select * from t2 order by 1) b;
+select count(*) from t1;
+select count(*) from t2;
+drop table t1,t2;
+set local tmp_table_size=default;
diff --git a/mysql-test/t/user_var.test b/mysql-test/t/user_var.test
index 8fe48641fed..39742d20c7c 100644
--- a/mysql-test/t/user_var.test
+++ b/mysql-test/t/user_var.test
@@ -60,3 +60,14 @@ drop table t1;
 
 # just for fun :)
 select @a:=10, @b:=2, @a>@b, @a:="10", @b:="2", @a>@b, @a:=10, @b:=2, @a>@b, @a:="10", @b:="2", @a>@b;
+
+#
+# bug#1739
+# Item_func_set_user_var sets update_query_id, Item_func_get_user_var checks it
+#
+create table t1 (i int not null);
+insert t1 values (1),(2),(2),(3),(3),(3);
+select @a:=0; select @a, @a:=@a+count(*), count(*), @a from t1 group by i;
+select @a:=0; select @a+0, @a:=@a+0+count(*), count(*), @a+0 from t1 group by i;
+drop table t1;
+