Merge bk-internal.mysql.com:/data0/bk/mysql-5.0

into  bk-internal.mysql.com:/data0/bk/mysql-5.0-opt


BitKeeper/etc/gone:
  auto-union
mysys/my_getopt.c:
  Auto merged
sql/field.h:
  Auto merged
sql/item.cc:
  Auto merged
sql/item_cmpfunc.cc:
  Auto merged
sql/item_cmpfunc.h:
  Auto merged
sql/mysql_priv.h:
  Auto merged
sql/sql_prepare.cc:
  Auto merged
sql/sql_select.cc:
  Auto merged
sql/table.cc:
  Auto merged
mysql-test/r/select.result:
  Manual merge
mysql-test/t/select.test:
  Manual merge

9 files changed, 314 insertions, 4 deletions

diff --git a/mysql-test/r/default.result b/mysql-test/r/default.result
index e2aa3b4a3cc..0b2b6769505 100644
--- a/mysql-test/r/default.result
+++ b/mysql-test/r/default.result
@@ -193,6 +193,16 @@ a	b	c	d	e	f	g	h	i	x
 two	large	00:00:05	0007-01-01	11	13	17	0019-01-01 00:00:00	23	1
 	small	00:00:00	0000-00-00	0			0000-00-00 00:00:00	0	2
 two	large	00:00:05	0007-01-01	11	13	17	0019-01-01 00:00:00	23	3
-	small	00:00:00	0000-00-00	0			0000-00-00 00:00:00	0	4
+		00:00:00	0000-00-00	0			0000-00-00 00:00:00	0	4
 drop table bug20691;
+create table t1 (id int not null);
+insert into t1 values(default);
+Warnings:
+Warning	1364	Field 'id' doesn't have a default value
+create view v1 (c) as select id from t1;
+insert into t1 values(default);
+Warnings:
+Warning	1364	Field 'id' doesn't have a default value
+drop view v1;
+drop table t1;
 End of 5.0 tests.
diff --git a/mysql-test/r/gis.result b/mysql-test/r/gis.result
index 870e160e563..df39a4d8ca2 100644
--- a/mysql-test/r/gis.result
+++ b/mysql-test/r/gis.result
@@ -717,3 +717,7 @@ desc t1;
 Field	Type	Null	Key	Default	Extra
 GeomFromText('point(1 1)')	geometry	NO			
 drop table t1;
+create table t1 (g geometry not null);
+insert into t1 values(default);
+ERROR 22003: Cannot get geometry object from data you send to the GEOMETRY field
+drop table t1;
diff --git a/mysql-test/r/information_schema.result b/mysql-test/r/information_schema.result
index b93a4c28849..436bb70d0e7 100644
--- a/mysql-test/r/information_schema.result
+++ b/mysql-test/r/information_schema.result
@@ -1262,8 +1262,7 @@ from information_schema.tables
 order by object_schema;
 explain select * from v1;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	<derived2>	system	NULL	NULL	NULL	NULL	0	const row not found
-2	DERIVED	tables	ALL	NULL	NULL	NULL	NULL	2	Using filesort
+1	SIMPLE	tables	ALL	NULL	NULL	NULL	NULL	2	Using filesort
 explain select * from (select table_name from information_schema.tables) as a;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	<derived2>	system	NULL	NULL	NULL	NULL	0	const row not found
@@ -1279,3 +1278,40 @@ table_name
 t1
 t2
 drop table t1,t2;
+select 1 as f1 from information_schema.tables  where "CHARACTER_SETS"=
+(select cast(table_name as char)  from information_schema.tables
+order by table_name limit 1) limit 1;
+f1
+1
+select t.table_name, group_concat(t.table_schema, '.', t.table_name),
+count(*) as num1
+from information_schema.tables t
+inner join information_schema.columns c1
+on t.table_schema = c1.table_schema AND t.table_name = c1.table_name
+where t.table_schema = 'information_schema' and
+c1.ordinal_position =
+(select isnull(c2.column_type) -
+isnull(group_concat(c2.table_schema, '.', c2.table_name)) +
+count(*) as num
+from information_schema.columns c2 where
+c2.table_schema='information_schema' and
+(c2.column_type = 'varchar(7)' or c2.column_type = 'varchar(20)')
+group by c2.column_type order by num limit 1)
+group by t.table_name order by num1, t.table_name;
+table_name	group_concat(t.table_schema, '.', t.table_name)	num1
+CHARACTER_SETS	information_schema.CHARACTER_SETS	1
+COLLATIONS	information_schema.COLLATIONS	1
+COLLATION_CHARACTER_SET_APPLICABILITY	information_schema.COLLATION_CHARACTER_SET_APPLICABILITY	1
+COLUMNS	information_schema.COLUMNS	1
+COLUMN_PRIVILEGES	information_schema.COLUMN_PRIVILEGES	1
+KEY_COLUMN_USAGE	information_schema.KEY_COLUMN_USAGE	1
+ROUTINES	information_schema.ROUTINES	1
+SCHEMATA	information_schema.SCHEMATA	1
+SCHEMA_PRIVILEGES	information_schema.SCHEMA_PRIVILEGES	1
+STATISTICS	information_schema.STATISTICS	1
+TABLES	information_schema.TABLES	1
+TABLE_CONSTRAINTS	information_schema.TABLE_CONSTRAINTS	1
+TABLE_PRIVILEGES	information_schema.TABLE_PRIVILEGES	1
+TRIGGERS	information_schema.TRIGGERS	1
+USER_PRIVILEGES	information_schema.USER_PRIVILEGES	1
+VIEWS	information_schema.VIEWS	1
diff --git a/mysql-test/r/innodb_mysql.result b/mysql-test/r/innodb_mysql.result
index f150af4b6c2..298fc58ffcb 100644
--- a/mysql-test/r/innodb_mysql.result
+++ b/mysql-test/r/innodb_mysql.result
@@ -383,6 +383,33 @@ EXPLAIN SELECT SQL_BIG_RESULT b, SUM(c) FROM t1 GROUP BY b;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	128	Using filesort
 DROP TABLE t1;
+CREATE TABLE t1 (
+id int NOT NULL,
+name varchar(20) NOT NULL,
+dept varchar(20) NOT NULL,
+age tinyint(3) unsigned NOT NULL,
+PRIMARY KEY (id),
+INDEX (name,dept)
+) ENGINE=InnoDB;
+INSERT INTO t1(id, dept, age, name) VALUES
+(3987, 'cs1', 10, 'rs1'), (3988, 'cs2', 20, 'rs1'), (3995, 'cs3', 10, 'rs2'),
+(3996, 'cs4', 20, 'rs2'), (4003, 'cs5', 10, 'rs3'), (4004, 'cs6', 20, 'rs3'),
+(4011, 'cs7', 10, 'rs4'), (4012, 'cs8', 20, 'rs4'), (4019, 'cs9', 10, 'rs5'),
+(4020, 'cs10', 20, 'rs5'),(4027, 'cs11', 10, 'rs6'),(4028, 'cs12', 20, 'rs6');
+EXPLAIN SELECT DISTINCT t1.name, t1.dept FROM t1 WHERE t1.name='rs5';
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	range	name	name	44	NULL	2	Using where; Using index for group-by
+SELECT DISTINCT t1.name, t1.dept FROM t1 WHERE t1.name='rs5';
+name	dept
+rs5	cs10
+rs5	cs9
+DELETE FROM t1;
+EXPLAIN SELECT DISTINCT t1.name, t1.dept FROM t1 WHERE t1.name='rs5';
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	range	name	name	44	NULL	2	Using where; Using index for group-by
+SELECT DISTINCT t1.name, t1.dept FROM t1 WHERE t1.name='rs5';
+name	dept
+DROP TABLE t1;
 show variables like 'innodb_rollback_on_timeout';
 Variable_name	Value
 innodb_rollback_on_timeout	OFF
diff --git a/mysql-test/r/insert_select.result b/mysql-test/r/insert_select.result
index 1453d25ac75..2e0acf303c2 100644
--- a/mysql-test/r/insert_select.result
+++ b/mysql-test/r/insert_select.result
@@ -705,3 +705,29 @@ use bug21774_1;
 INSERT INTO bug21774_2.t1 SELECT t1.* FROM t1;
 DROP DATABASE bug21774_1;
 DROP DATABASE bug21774_2;
+USE test;
+create table t1(f1 int primary key, f2 int);
+insert into t1 values (1,1);
+affected rows: 1
+insert into t1 values (1,1) on duplicate key update f2=1;
+affected rows: 0
+insert into t1 values (1,1) on duplicate key update f2=2;
+affected rows: 2
+select * from t1;
+f1	f2
+1	2
+drop table t1;
+create table t1(f1 int primary key auto_increment, f2 int unique);
+insert into t1(f2) values(1);
+select @@identity;
+@@identity
+1
+insert ignore t1(f2) values(1);
+select @@identity;
+@@identity
+0
+insert ignore t1(f2) select 1;
+select @@identity;
+@@identity
+0
+drop table t1;
diff --git a/mysql-test/r/join_outer.result b/mysql-test/r/join_outer.result
index 89bb26c4b3f..df66336bd81 100644
--- a/mysql-test/r/join_outer.result
+++ b/mysql-test/r/join_outer.result
@@ -1194,3 +1194,23 @@ a	b
 3	3
 4	NULL
 DROP TABLE t1,t2;
+CREATE TABLE t1 (
+f1 varchar(16) collate latin1_swedish_ci PRIMARY KEY,
+f2 varchar(16) collate latin1_swedish_ci
+);
+CREATE TABLE t2 (
+f1 varchar(16) collate latin1_swedish_ci PRIMARY KEY,
+f3 varchar(16) collate latin1_swedish_ci
+);
+INSERT INTO t1 VALUES ('bla','blah');
+INSERT INTO t2 VALUES ('bla','sheep');
+SELECT * FROM t1 JOIN t2 USING(f1) WHERE f1='Bla';
+f1	f2	f3
+bla	blah	sheep
+SELECT * FROM t1 LEFT JOIN t2 USING(f1) WHERE f1='bla';
+f1	f2	f3
+bla	blah	sheep
+SELECT * FROM t1 LEFT JOIN t2 USING(f1) WHERE f1='Bla';
+f1	f2	f3
+bla	blah	sheep
+DROP TABLE t1,t2;
diff --git a/mysql-test/r/select.result b/mysql-test/r/select.result
index 702ac39cb95..3c4df47ca7b 100644
--- a/mysql-test/r/select.result
+++ b/mysql-test/r/select.result
@@ -3728,6 +3728,34 @@ WHERE ID_better=1 AND ID1_with_null IS NULL AND
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t1	ref	idx1,idx2	idx2	4	const	1	Using where
 DROP TABLE t1;
+CREATE TABLE t1 (a INT, ts TIMESTAMP, KEY ts(ts));
+INSERT INTO t1 VALUES (30,"2006-01-03 23:00:00"), (31,"2006-01-03 23:00:00");
+ANALYZE TABLE t1;
+Table	Op	Msg_type	Msg_text
+test.t1	analyze	status	OK
+CREATE TABLE t2 (a INT, dt1 DATETIME, dt2 DATETIME, PRIMARY KEY (a));
+INSERT INTO t2 VALUES (30, "2006-01-01 00:00:00", "2999-12-31 00:00:00");
+INSERT INTO t2 SELECT a+1,dt1,dt2 FROM t2;
+ANALYZE TABLE t2;
+Table	Op	Msg_type	Msg_text
+test.t2	analyze	status	OK
+EXPLAIN
+SELECT * FROM t1 LEFT JOIN t2 ON (t1.a=t2.a) WHERE t1.a=30
+AND t1.ts BETWEEN t2.dt1 AND t2.dt2
+AND t1.ts BETWEEN "2006-01-01" AND "2006-12-31";
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t2	const	PRIMARY	PRIMARY	4	const	1	
+1	SIMPLE	t1	range	ts	ts	4	NULL	1	Using where
+Warnings:
+Warning	1292	Incorrect datetime value: '2999-12-31 00:00:00' for column 'ts' at row 1
+SELECT * FROM t1 LEFT JOIN t2 ON (t1.a=t2.a) WHERE t1.a=30
+AND t1.ts BETWEEN t2.dt1 AND t2.dt2
+AND t1.ts BETWEEN "2006-01-01" AND "2006-12-31";
+a	ts	a	dt1	dt2
+30	2006-01-03 23:00:00	30	2006-01-01 00:00:00	2999-12-31 00:00:00
+Warnings:
+Warning	1292	Incorrect datetime value: '2999-12-31 00:00:00' for column 'ts' at row 1
+DROP TABLE t1,t2;
 create table t1 (a bigint unsigned);
 insert into t1 values
 (if(1, 9223372036854775808, 1)),
@@ -3758,3 +3786,4 @@ coalesce(cast(1111111111111111111 as unsigned), 1) co;
 i	c	co
 1111111111111111111	1111111111111111111	1111111111111111111
 End of 5.0 tests
+
diff --git a/mysql-test/r/update.result b/mysql-test/r/update.result
index 4b7dbb3dbe9..9e2bc52657a 100644
--- a/mysql-test/r/update.result
+++ b/mysql-test/r/update.result
@@ -377,3 +377,60 @@ create table t1(f1 int, `*f2` int);
 insert into t1 values (1,1);
 update t1 set `*f2`=1;
 drop table t1;
+CREATE TABLE t1 (
+request_id int unsigned NOT NULL auto_increment,
+user_id varchar(12) default NULL,
+time_stamp datetime NOT NULL default '0000-00-00 00:00:00',
+ip_address varchar(15) default NULL,
+PRIMARY KEY (request_id),
+KEY user_id_2 (user_id,time_stamp)
+);
+INSERT INTO t1 (user_id) VALUES ('user1');
+INSERT INTO t1(user_id) SELECT user_id FROM t1;
+INSERT INTO t1(user_id) SELECT user_id FROM t1;
+INSERT INTO t1(user_id) SELECT user_id FROM t1;
+INSERT INTO t1(user_id) SELECT user_id FROM t1;
+INSERT INTO t1(user_id) SELECT user_id FROM t1;
+INSERT INTO t1(user_id) SELECT user_id FROM t1;
+INSERT INTO t1(user_id) SELECT user_id FROM t1;
+INSERT INTO t1(user_id) SELECT user_id FROM t1;
+flush status;
+SELECT user_id FROM t1 WHERE request_id=9999999999999;
+user_id
+show status like '%Handler_read%';
+Variable_name	Value
+Handler_read_first	0
+Handler_read_key	1
+Handler_read_next	0
+Handler_read_prev	0
+Handler_read_rnd	0
+Handler_read_rnd_next	0
+SELECT user_id FROM t1 WHERE request_id=999999999999999999999999999999;
+user_id
+show status like '%Handler_read%';
+Variable_name	Value
+Handler_read_first	0
+Handler_read_key	2
+Handler_read_next	0
+Handler_read_prev	0
+Handler_read_rnd	0
+Handler_read_rnd_next	7
+UPDATE t1 SET user_id=null WHERE request_id=9999999999999;
+show status like '%Handler_read%';
+Variable_name	Value
+Handler_read_first	0
+Handler_read_key	3
+Handler_read_next	0
+Handler_read_prev	0
+Handler_read_rnd	0
+Handler_read_rnd_next	14
+UPDATE t1 SET user_id=null WHERE request_id=999999999999999999999999999999;
+show status like '%Handler_read%';
+Variable_name	Value
+Handler_read_first	0
+Handler_read_key	3
+Handler_read_next	0
+Handler_read_prev	0
+Handler_read_rnd	0
+Handler_read_rnd_next	21
+DROP TABLE t1;
diff --git a/mysql-test/r/view.result b/mysql-test/r/view.result
index 7185a70689a..50b41e1352f 100644
--- a/mysql-test/r/view.result
+++ b/mysql-test/r/view.result
@@ -2543,7 +2543,7 @@ create table t1(f1 int, f2 int);
 create view v1 as select ta.f1 as a, tb.f1 as b from t1 ta, t1 tb where ta.f1=tb
 .f1 and ta.f2=tb.f2;
 insert into t1 values(1,1),(2,2);
-create view v2 as select * from v1 where a > 1 with check option;
+create view v2 as select * from v1 where a > 1 with local check option;
 select * from v2;
 a	b
 2	2
@@ -3034,4 +3034,105 @@ SHOW CREATE VIEW v1;
 View	Create View
 v1	CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select _latin1'The\ZEnd' AS `TheEnd`
 DROP VIEW v1;
+CREATE TABLE t1 (mydate DATETIME);
+INSERT INTO t1 VALUES 
+('2007-01-01'), ('2007-01-02'), ('2007-01-30'), ('2007-01-31');
+CREATE VIEW v1 AS SELECT mydate from t1;
+SELECT * FROM t1 WHERE mydate BETWEEN '2007-01-01' AND '2007-01-31';
+mydate
+2007-01-01 00:00:00
+2007-01-02 00:00:00
+2007-01-30 00:00:00
+2007-01-31 00:00:00
+SELECT * FROM v1 WHERE mydate BETWEEN '2007-01-01' AND '2007-01-31';
+mydate
+2007-01-01 00:00:00
+2007-01-02 00:00:00
+2007-01-30 00:00:00
+2007-01-31 00:00:00
+DROP VIEW v1;
+DROP TABLE t1;
+CREATE TABLE t1 (a int);
+CREATE TABLE t2 (b int);
+INSERT INTO t1 VALUES (1), (2);
+INSERT INTO t2 VALUES (1), (2);
+CREATE VIEW v1 AS
+SELECT t2.b FROM t1,t2 WHERE t1.a = t2.b WITH CHECK OPTION;
+SELECT * FROM v1;
+b
+1
+2
+UPDATE v1 SET b=3;
+ERROR HY000: CHECK OPTION failed 'test.v1'
+SELECT * FROM v1;
+b
+1
+2
+SELECT * FROM t1;
+a
+1
+2
+SELECT * FROM t2;
+b
+1
+2
+DROP VIEW v1;
+DROP TABLE t1,t2;
+create table t1(f1 int, f2 int);
+insert into t1 values(1,2),(1,3),(1,1),(2,3),(2,1),(2,2);
+select * from t1;
+f1	f2
+1	2
+1	3
+1	1
+2	3
+2	1
+2	2
+create view v1 as select * from t1 order by f2;
+select * from v1;
+f1	f2
+1	1
+2	1
+1	2
+2	2
+1	3
+2	3
+explain extended select * from v1;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	6	Using filesort
+Warnings:
+Note	1003	select `test`.`t1`.`f1` AS `f1`,`test`.`t1`.`f2` AS `f2` from `test`.`t1` order by `test`.`t1`.`f2`
+select * from v1 order by f1;
+f1	f2
+1	1
+1	2
+1	3
+2	1
+2	2
+2	3
+explain extended select * from v1 order by f1;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	6	Using filesort
+Warnings:
+Note	1003	select `test`.`t1`.`f1` AS `f1`,`test`.`t1`.`f2` AS `f2` from `test`.`t1` order by `test`.`t1`.`f1`,`test`.`t1`.`f2`
+drop view v1;
+drop table t1;
+CREATE TABLE t1 (
+id int(11) NOT NULL PRIMARY KEY,
+country varchar(32),
+code int(11) default NULL
+);
+INSERT INTO t1 VALUES
+(1,'ITALY',100),(2,'ITALY',200),(3,'FRANCE',100), (4,'ITALY',100);
+CREATE VIEW v1 AS SELECT * FROM t1;
+SELECT code, COUNT(DISTINCT country) FROM t1 GROUP BY code ORDER BY MAX(id);
+code	COUNT(DISTINCT country)
+200	1
+100	2
+SELECT code, COUNT(DISTINCT country) FROM v1 GROUP BY code ORDER BY MAX(id);
+code	COUNT(DISTINCT country)
+200	1
+100	2
+DROP VIEW v1;
+DROP TABLE t1;
 End of 5.0 tests.