Merge mysql.com:/home/hf/work/mrg/mysql-5.0-opt

into  mysql.com:/home/hf/work/mrg/mysql-5.1-opt

8 files changed, 166 insertions, 9 deletions

diff --git a/mysql-test/r/explain.result b/mysql-test/r/explain.result
index e0d9f5131b4..13d63cdcdc3 100644
--- a/mysql-test/r/explain.result
+++ b/mysql-test/r/explain.result
@@ -57,3 +57,33 @@ select 3 into @v1;
 explain select 3 into @v1;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
+create table t1(f1 int, f2 int);
+insert into t1 values (1,1);
+create view v1 as select * from t1 where f1=1;
+explain extended select * from v1 where f2=1;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	system	NULL	NULL	NULL	NULL	1	
+Warnings:
+Note	1003	select `test`.`t1`.`f1` AS `f1`,`test`.`t1`.`f2` AS `f2` from `test`.`t1` where 1
+explain extended select * from t1 where 0;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE
+Warnings:
+Note	1003	select `test`.`t1`.`f1` AS `f1`,`test`.`t1`.`f2` AS `f2` from `test`.`t1` where 0
+explain extended select * from t1 where 1;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	system	NULL	NULL	NULL	NULL	1	
+Warnings:
+Note	1003	select `test`.`t1`.`f1` AS `f1`,`test`.`t1`.`f2` AS `f2` from `test`.`t1` where 1
+explain extended select * from t1 having 0;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible HAVING
+Warnings:
+Note	1003	select `test`.`t1`.`f1` AS `f1`,`test`.`t1`.`f2` AS `f2` from `test`.`t1` having 0
+explain extended select * from t1 having 1;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	system	NULL	NULL	NULL	NULL	1	
+Warnings:
+Note	1003	select `test`.`t1`.`f1` AS `f1`,`test`.`t1`.`f2` AS `f2` from `test`.`t1` having 1
+drop view v1;
+drop table t1;
diff --git a/mysql-test/r/func_in.result b/mysql-test/r/func_in.result
index 9ec621b7f35..dab44fa3dab 100644
--- a/mysql-test/r/func_in.result
+++ b/mysql-test/r/func_in.result
@@ -399,6 +399,78 @@ WHERE t3.a=t1.a AND t3.a=t2.a;
 3
 3
 DROP TABLE t1,t2,t3,t4;
+CREATE TABLE t1(a BIGINT UNSIGNED);
+INSERT INTO t1 VALUES (0xFFFFFFFFFFFFFFFF);
+SELECT * FROM t1 WHERE a=-1 OR a=-2 ;
+a
+SELECT * FROM t1 WHERE a IN (-1, -2);
+a
+CREATE TABLE t2 (a BIGINT UNSIGNED);
+insert into t2 values(13491727406643098568),
+(0x7fffffefffffffff),
+(0x7ffffffeffffffff),
+(0x7fffffffefffffff),
+(0x7ffffffffeffffff),
+(0x7fffffffffefffff),
+(0x7ffffffffffeffff),
+(0x7fffffffffffefff),
+(0x7ffffffffffffeff),
+(0x7fffffffffffffef),
+(0x7ffffffffffffffe),
+(0x7fffffffffffffff),
+(0x8000000000000000),
+(0x8000000000000001),
+(0x8000000000000002),
+(0x8000000000000300),
+(0x8000000000000400),
+(0x8000000000000401),
+(0x8000000000004001),
+(0x8000000000040001),
+(0x8000000000400001),
+(0x8000000004000001),
+(0x8000000040000001),
+(0x8000000400000001),
+(0x8000004000000001),
+(0x8000040000000001);
+SELECT HEX(a) FROM t2 WHERE a IN (0xBB3C3E98175D33C8, 42);
+HEX(a)
+BB3C3E98175D33C8
+SELECT HEX(a) FROM t2 WHERE a IN
+(0xBB3C3E98175D33C8,
+0x7fffffffffffffff,
+0x8000000000000000,
+0x8000000000000400,
+0x8000000000000401,
+42);
+HEX(a)
+BB3C3E98175D33C8
+7FFFFFFFFFFFFFFF
+8000000000000000
+8000000000000400
+8000000000000401
+SELECT HEX(a) FROM t2 WHERE a IN (0x7fffffffffffffff,0x8000000000000001);
+HEX(a)
+7FFFFFFFFFFFFFFF
+8000000000000001
+SELECT HEX(a) FROM t2 WHERE a IN (0x7ffffffffffffffe,0x7fffffffffffffff);
+HEX(a)
+7FFFFFFFFFFFFFFE
+7FFFFFFFFFFFFFFF
+SELECT HEX(a) FROM t2 WHERE a IN (0x7ffffffffffffffe,0x7fffffffffffffff,'abc');
+HEX(a)
+7FFFFFFFFFFFFFFE
+7FFFFFFFFFFFFFFF
+CREATE TABLE t3 (a BIGINT UNSIGNED);
+INSERT INTO t3 VALUES (9223372036854775551);
+SELECT HEX(a) FROM t3 WHERE a IN (9223372036854775807, 42);
+HEX(a)
+CREATE TABLE t4 (a DATE);
+INSERT INTO t4 VALUES ('1972-02-06'), ('1972-07-29');
+SELECT * FROM t4 WHERE a IN ('1972-02-06','19772-07-29');
+a
+Warnings:
+Warning	1292	Incorrect date value: '19772-07-29' for column 'a' at row 1
+DROP TABLE t1,t2,t3,t4;
 End of 5.0 tests
 create table t1(f1 char(1));
 insert into t1 values ('a'),('b'),('1');
diff --git a/mysql-test/r/func_str.result b/mysql-test/r/func_str.result
index e6c323276ea..b0efab73f26 100644
--- a/mysql-test/r/func_str.result
+++ b/mysql-test/r/func_str.result
@@ -2273,4 +2273,10 @@ abcxx
 select lpad('abc', cast(5 as unsigned integer), 'x');
 lpad('abc', cast(5 as unsigned integer), 'x')
 xxabc
+SELECT UNHEX('G');
+UNHEX('G')
+NULL
+SELECT UNHEX('G') IS NULL;
+UNHEX('G') IS NULL
+1
 End of 5.0 tests
diff --git a/mysql-test/r/func_test.result b/mysql-test/r/func_test.result
index 9a5cc666ca8..512369e4f61 100644
--- a/mysql-test/r/func_test.result
+++ b/mysql-test/r/func_test.result
@@ -79,7 +79,7 @@ explain extended select * from t1 where 1 xor 1;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE
 Warnings:
-Note	1003	select `test`.`t1`.`a` AS `a` from `test`.`t1`
+Note	1003	select `test`.`t1`.`a` AS `a` from `test`.`t1` where 0
 select - a from t1;
 - a
 -1
diff --git a/mysql-test/r/order_by.result b/mysql-test/r/order_by.result
index a056e1244e6..8bef6c6c72e 100644
--- a/mysql-test/r/order_by.result
+++ b/mysql-test/r/order_by.result
@@ -926,6 +926,14 @@ NULL
 2
 3
 DROP TABLE t1,t2,t3,t4;
+create table t1 (a int, b int, c int);
+insert into t1 values (1,2,3), (9,8,3), (19,4,3), (1,4,9);
+select a,(sum(b)/sum(c)) as ratio from t1 group by a order by sum(b)/sum(c) asc;
+a	ratio
+1	0.5000
+19	1.3333
+9	2.6667
+drop table t1;
 CREATE TABLE t1 (a INT, b INT, PRIMARY KEY (a), UNIQUE KEY b (b));
 INSERT INTO t1 VALUES (1,1),(2,2);
 CREATE TABLE t2 (a INT, b INT, KEY a (a,b));
diff --git a/mysql-test/r/subselect.result b/mysql-test/r/subselect.result
index 43abef692e9..cde957004a6 100644
--- a/mysql-test/r/subselect.result
+++ b/mysql-test/r/subselect.result
@@ -421,7 +421,7 @@ id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 3	UNION	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
 NULL	UNION RESULT	<union2,3>	ALL	NULL	NULL	NULL	NULL	NULL	NULL	
 Warnings:
-Note	1003	select 1 AS `1` from `test`.`t1`
+Note	1003	select 1 AS `1` from `test`.`t1` where 1
 drop table t1;
 CREATE TABLE `t1` (
 `numeropost` mediumint(8) unsigned NOT NULL auto_increment,
@@ -1180,7 +1180,7 @@ id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
 2	DEPENDENT SUBQUERY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE
 Warnings:
-Note	1003	select <in_optimizer>(0,<exists>(select 1 AS `Not_used` from `test`.`t1` `a`)) AS `0 IN (SELECT 1 FROM t1 a)`
+Note	1003	select <in_optimizer>(0,<exists>(select 1 AS `Not_used` from `test`.`t1` `a` where 0)) AS `0 IN (SELECT 1 FROM t1 a)`
 INSERT INTO t1 (pseudo) VALUES ('test1');
 SELECT 0 IN (SELECT 1 FROM t1 a);
 0 IN (SELECT 1 FROM t1 a)
@@ -1190,7 +1190,7 @@ id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
 2	DEPENDENT SUBQUERY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE
 Warnings:
-Note	1003	select <in_optimizer>(0,<exists>(select 1 AS `Not_used` from `test`.`t1` `a`)) AS `0 IN (SELECT 1 FROM t1 a)`
+Note	1003	select <in_optimizer>(0,<exists>(select 1 AS `Not_used` from `test`.`t1` `a` where 0)) AS `0 IN (SELECT 1 FROM t1 a)`
 drop table t1;
 CREATE TABLE `t1` (
 `i` int(11) NOT NULL default '0',
@@ -1532,7 +1532,7 @@ id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE
 2	SUBQUERY	t2	system	NULL	NULL	NULL	NULL	0	0.00	const row not found
 Warnings:
-Note	1003	select `test`.`t3`.`a` AS `a` from `test`.`t3`
+Note	1003	select `test`.`t3`.`a` AS `a` from `test`.`t3` where 0
 select * from t3 where NULL >= any (select b from t2 group by 1);
 a
 explain extended select * from t3 where NULL >= any (select b from t2 group by 1);
@@ -1540,7 +1540,7 @@ id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE
 2	SUBQUERY	t2	system	NULL	NULL	NULL	NULL	0	0.00	const row not found
 Warnings:
-Note	1003	select `test`.`t3`.`a` AS `a` from `test`.`t3`
+Note	1003	select `test`.`t3`.`a` AS `a` from `test`.`t3` where 0
 select * from t3 where NULL >= some (select b from t2);
 a
 explain extended select * from t3 where NULL >= some (select b from t2);
@@ -1548,7 +1548,7 @@ id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE
 2	SUBQUERY	t2	system	NULL	NULL	NULL	NULL	0	0.00	const row not found
 Warnings:
-Note	1003	select `test`.`t3`.`a` AS `a` from `test`.`t3`
+Note	1003	select `test`.`t3`.`a` AS `a` from `test`.`t3` where 0
 select * from t3 where NULL >= some (select b from t2 group by 1);
 a
 explain extended select * from t3 where NULL >= some (select b from t2 group by 1);
@@ -1556,7 +1556,7 @@ id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE
 2	SUBQUERY	t2	system	NULL	NULL	NULL	NULL	0	0.00	const row not found
 Warnings:
-Note	1003	select `test`.`t3`.`a` AS `a` from `test`.`t3`
+Note	1003	select `test`.`t3`.`a` AS `a` from `test`.`t3` where 0
 insert into t2 values (2,2), (2,1), (3,3), (3,1);
 select * from t3 where a > all (select max(b) from t2 group by a);
 a
@@ -1618,7 +1618,7 @@ id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 3	UNION	t1	system	NULL	NULL	NULL	NULL	1	100.00	
 NULL	UNION RESULT	<union2,3>	ALL	NULL	NULL	NULL	NULL	NULL	NULL	
 Warnings:
-Note	1003	select `test`.`t1`.`s1` AS `s1` from `test`.`t1`
+Note	1003	select `test`.`t1`.`s1` AS `s1` from `test`.`t1` where 1
 drop table t1;
 CREATE TABLE t1 (number char(11) NOT NULL default '') ENGINE=MyISAM CHARSET=latin1;
 INSERT INTO t1 VALUES ('69294728265'),('18621828126'),('89356874041'),('95895001874');
diff --git a/mysql-test/r/update.result b/mysql-test/r/update.result
index a40d3451a62..94b62625c3f 100644
--- a/mysql-test/r/update.result
+++ b/mysql-test/r/update.result
@@ -377,6 +377,10 @@ create table t1(f1 int, `*f2` int);
 insert into t1 values (1,1);
 update t1 set `*f2`=1;
 drop table t1;
+create table t1(f1 int);
+update t1 set f2=1 order by f2;
+ERROR 42S22: Unknown column 'f2' in 'order clause'
+drop table t1;
 CREATE TABLE t1 (
 request_id int unsigned NOT NULL auto_increment,
 user_id varchar(12) default NULL,
diff --git a/mysql-test/r/view.result b/mysql-test/r/view.result
index 8f83e1acb09..8eaa38ad53e 100644
--- a/mysql-test/r/view.result
+++ b/mysql-test/r/view.result
@@ -3126,6 +3126,43 @@ code	COUNT(DISTINCT country)
 100	2
 DROP VIEW v1;
 DROP TABLE t1;
+CREATE TABLE t1 (
+lid int NOT NULL PRIMARY KEY,
+name char(10) NOT NULL
+);
+INSERT INTO t1 (lid, name) VALUES
+(1, 'YES'), (2, 'NO');
+CREATE TABLE t2 (
+id int NOT NULL PRIMARY KEY, 
+gid int NOT NULL,
+lid int NOT NULL,
+dt date
+);
+INSERT INTO t2 (id, gid, lid, dt) VALUES
+(1, 1, 1, '2007-01-01'),(2, 1, 2, '2007-01-02'),
+(3, 2, 2, '2007-02-01'),(4, 2, 1, '2007-02-02');
+SELECT DISTINCT t2.gid AS lgid,
+(SELECT t1.name FROM t1, t2
+WHERE t1.lid  = t2.lid AND t2.gid = lgid
+ORDER BY t2.dt DESC LIMIT 1
+) as clid
+FROM t2;
+lgid	clid
+1	NO
+2	YES
+CREATE VIEW v1 AS
+SELECT DISTINCT t2.gid AS lgid,
+(SELECT t1.name FROM t1, t2
+WHERE t1.lid  = t2.lid AND t2.gid = lgid
+ORDER BY t2.dt DESC LIMIT 1
+) as clid
+FROM t2;
+SELECT * FROM v1;
+lgid	clid
+1	NO
+2	YES
+DROP VIEW v1;
+DROP table t1,t2;
 End of 5.0 tests.
 DROP DATABASE IF EXISTS `d-1`;
 CREATE DATABASE `d-1`;