Merge epotemkin@bk-internal.mysql.com:/home/bk/mysql-5.0

into  moonbone.local:/work/tmp_merge-5.0-opt-mysql


mysql-test/r/rpl_insert_id.result:
  Auto merged
mysql-test/t/rpl_insert_id.test:
  Auto merged
sql/item_strfunc.cc:
  Auto merged
sql/item_strfunc.h:
  Auto merged
sql/sql_class.cc:
  Auto merged
sql/sql_class.h:
  Auto merged
sql/sql_insert.cc:
  Auto merged
sql/sql_select.cc:
  Auto merged

10 files changed, 155 insertions, 2 deletions

diff --git a/mysql-test/r/func_str.result b/mysql-test/r/func_str.result
index 1485809ba70..720529a9f11 100644
--- a/mysql-test/r/func_str.result
+++ b/mysql-test/r/func_str.result
@@ -1038,6 +1038,21 @@ select * from t1 where f1='test' and (f2= sha("TEST") or f2= sha("test"));
 f1	f2
 test	a94a8fe5ccb19ba61c4c0873d391e987982fbbd3
 drop table t1;
+CREATE TABLE t1 (a varchar(10));
+INSERT INTO t1 VALUES ('abc'), ('xyz');
+SELECT a, CONCAT(a,' ',a) AS c FROM t1
+HAVING LEFT(c,LENGTH(c)-INSTR(REVERSE(c)," ")) = a;
+a	c
+abc	abc abc
+xyz	xyz xyz
+SELECT a, CONCAT(a,' ',a) AS c FROM t1
+HAVING LEFT(CONCAT(a,' ',a),
+LENGTH(CONCAT(a,' ',a))-
+INSTR(REVERSE(CONCAT(a,' ',a))," ")) = a;
+a	c
+abc	abc abc
+xyz	xyz xyz
+DROP TABLE t1;
 End of 4.1 tests
 create table t1 (d decimal default null);
 insert into t1 values (null);
diff --git a/mysql-test/r/odbc.result b/mysql-test/r/odbc.result
index 2d9d39393b1..5629d3dab33 100644
--- a/mysql-test/r/odbc.result
+++ b/mysql-test/r/odbc.result
@@ -14,3 +14,14 @@ explain select * from t1 where b is null;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE
 drop table t1;
+CREATE TABLE t1 (a INT AUTO_INCREMENT PRIMARY KEY);
+INSERT INTO t1 VALUES (NULL);
+SELECT sql_no_cache a, last_insert_id() FROM t1 WHERE a IS NULL;
+a	last_insert_id()
+1	1
+SELECT sql_no_cache a, last_insert_id() FROM t1 WHERE a IS NULL;
+a	last_insert_id()
+SELECT sql_no_cache a, last_insert_id() FROM t1;
+a	last_insert_id()
+1	1
+DROP TABLE t1;
diff --git a/mysql-test/r/rpl_insert_id.result b/mysql-test/r/rpl_insert_id.result
index cd66e8727c1..3aa82bf1d63 100644
--- a/mysql-test/r/rpl_insert_id.result
+++ b/mysql-test/r/rpl_insert_id.result
@@ -74,6 +74,19 @@ SET FOREIGN_KEY_CHECKS=0;
 INSERT INTO t1 VALUES (1),(1);
 ERROR 23000: Duplicate entry '1' for key 1
 drop table t1;
+create table t1(a int auto_increment, key(a));
+create table t2(a int);
+insert into t1 (a) values (null);
+insert into t2 (a) select a from t1 where a is null;
+insert into t2 (a) select a from t1 where a is null;
+select * from t2;
+a
+1
+select * from t2;
+a
+1
+drop table t1;
+drop table t2;
 drop function if exists bug15728;
 drop function if exists bug15728_insert;
 drop table if exists t1, t2;
diff --git a/mysql-test/r/select.result b/mysql-test/r/select.result
index e6c590489a0..7f01d453906 100644
--- a/mysql-test/r/select.result
+++ b/mysql-test/r/select.result
@@ -3395,3 +3395,6 @@ a	t1.b + 0	t1.c + 0	a	t2.b + 0	c	d
 1	0	1	1	0	1	NULL
 2	0	1	NULL	NULL	NULL	NULL
 drop table t1,t2;
+SELECT 0.9888889889 * 1.011111411911;
+0.9888889889 * 1.011111411911
+0.9998769417899202067879
diff --git a/mysql-test/r/view.result b/mysql-test/r/view.result
index 72cffb9531c..295f9442f13 100644
--- a/mysql-test/r/view.result
+++ b/mysql-test/r/view.result
@@ -2735,4 +2735,31 @@ m	e
 4	a
 1	b
 DROP VIEW v1;
-DROP TABLE IF EXISTS t1,t2;
+DROP TABLE t1,t2;
+CREATE TABLE t1 (a INT NOT NULL, b INT NULL DEFAULT NULL);
+CREATE VIEW v1 AS SELECT a, b FROM t1;
+INSERT INTO v1 (b) VALUES (2);
+Warnings:
+Warning	1423	Field of view 'test.v1' underlying table doesn't have a default value
+SET SQL_MODE = STRICT_ALL_TABLES;
+INSERT INTO v1 (b) VALUES (4);
+ERROR HY000: Field of view 'test.v1' underlying table doesn't have a default value
+SET SQL_MODE = '';
+SELECT * FROM t1;
+a	b
+0	2
+DROP VIEW v1;
+DROP TABLE t1;
+CREATE TABLE t1 (firstname text, surname text);
+INSERT INTO t1 VALUES
+("Bart","Simpson"),("Milhouse","van Houten"),("Montgomery","Burns");
+CREATE VIEW v1 AS SELECT CONCAT(firstname," ",surname) AS name FROM t1;
+SELECT CONCAT(LEFT(name,LENGTH(name)-INSTR(REVERSE(name)," ")),
+LEFT(name,LENGTH(name)-INSTR(REVERSE(name)," "))) AS f1
+FROM v1;
+f1
+BartBart
+Milhouse vanMilhouse van
+MontgomeryMontgomery
+DROP VIEW v1;
+DROP TABLE t1;
diff --git a/mysql-test/t/func_str.test b/mysql-test/t/func_str.test
index b13fe039261..054da668584 100644
--- a/mysql-test/t/func_str.test
+++ b/mysql-test/t/func_str.test
@@ -685,6 +685,23 @@ select * from t1 where f1='test' and (f2= sha("test") or f2= sha("TEST"));
 select * from t1 where f1='test' and (f2= sha("TEST") or f2= sha("test"));
 drop table t1;
 
+#
+# Bug#18243: REVERSE changes its argument
+#
+
+CREATE TABLE t1 (a varchar(10));
+INSERT INTO t1 VALUES ('abc'), ('xyz');
+
+SELECT a, CONCAT(a,' ',a) AS c FROM t1
+  HAVING LEFT(c,LENGTH(c)-INSTR(REVERSE(c)," ")) = a;
+
+SELECT a, CONCAT(a,' ',a) AS c FROM t1
+  HAVING LEFT(CONCAT(a,' ',a),
+              LENGTH(CONCAT(a,' ',a))-
+                     INSTR(REVERSE(CONCAT(a,' ',a))," ")) = a;
+
+DROP TABLE t1;
+ 
 --echo End of 4.1 tests
 
 #
diff --git a/mysql-test/t/odbc.test b/mysql-test/t/odbc.test
index d4b6fc35e74..6a754bb32a7 100644
--- a/mysql-test/t/odbc.test
+++ b/mysql-test/t/odbc.test
@@ -21,4 +21,14 @@ select * from t1 where a is null;
 explain select * from t1 where b is null;
 drop table t1;
 
+#
+# Bug #14553: NULL in WHERE resets LAST_INSERT_ID
+#
+CREATE TABLE t1 (a INT AUTO_INCREMENT PRIMARY KEY);
+INSERT INTO t1 VALUES (NULL);
+SELECT sql_no_cache a, last_insert_id() FROM t1 WHERE a IS NULL;
+SELECT sql_no_cache a, last_insert_id() FROM t1 WHERE a IS NULL;
+SELECT sql_no_cache a, last_insert_id() FROM t1;
+DROP TABLE t1;
+
 # End of 4.1 tests
diff --git a/mysql-test/t/rpl_insert_id.test b/mysql-test/t/rpl_insert_id.test
index 90a123cf5dc..fa66306aaa6 100644
--- a/mysql-test/t/rpl_insert_id.test
+++ b/mysql-test/t/rpl_insert_id.test
@@ -77,6 +77,24 @@ sync_slave_with_master;
 connection master;
 drop table t1;
 sync_slave_with_master;
+ 
+#
+# Bug#14553: NULL in WHERE resets LAST_INSERT_ID
+#
+connection master;
+create table t1(a int auto_increment, key(a));
+create table t2(a int);
+insert into t1 (a) values (null);
+insert into t2 (a) select a from t1 where a is null;
+insert into t2 (a) select a from t1 where a is null;
+select * from t2;
+sync_slave_with_master;
+connection slave;
+select * from t2;
+connection master;
+drop table t1;
+drop table t2;
+sync_slave_with_master;
 # End of 4.1 tests
 
 
diff --git a/mysql-test/t/select.test b/mysql-test/t/select.test
index b75d0dd8bb6..b44f682c02e 100644
--- a/mysql-test/t/select.test
+++ b/mysql-test/t/select.test
@@ -2901,3 +2901,8 @@ from t1 left outer join t2 on t1.a = t2.c and t2.b <> 1
 where t1.b <> 1 order by t1.a;
 
 drop table t1,t2;
+
+#
+# Bug #20569: Garbage in DECIMAL results from some mathematical functions
+#
+SELECT 0.9888889889 * 1.011111411911;
diff --git a/mysql-test/t/view.test b/mysql-test/t/view.test
index a1c1e9b2ad1..623195dd527 100644
--- a/mysql-test/t/view.test
+++ b/mysql-test/t/view.test
@@ -2595,4 +2595,38 @@ CREATE TABLE t2 SELECT * FROM v1;
 SELECT * FROM t2;
 
 DROP VIEW v1;
-DROP TABLE IF EXISTS t1,t2;
+DROP TABLE t1,t2;
+
+#
+# Bug#16110: insert permitted into view col w/o default value
+#
+CREATE TABLE t1 (a INT NOT NULL, b INT NULL DEFAULT NULL);
+CREATE VIEW v1 AS SELECT a, b FROM t1;
+
+INSERT INTO v1 (b) VALUES (2);
+
+SET SQL_MODE = STRICT_ALL_TABLES;
+--error 1423
+INSERT INTO v1 (b) VALUES (4);
+SET SQL_MODE = '';
+
+SELECT * FROM t1;
+
+DROP VIEW v1;
+DROP TABLE t1;
+
+#
+# Bug #18243: expression over a view column that with the REVERSE function
+#
+
+CREATE TABLE t1 (firstname text, surname text);
+INSERT INTO t1 VALUES
+  ("Bart","Simpson"),("Milhouse","van Houten"),("Montgomery","Burns");
+
+CREATE VIEW v1 AS SELECT CONCAT(firstname," ",surname) AS name FROM t1;
+SELECT CONCAT(LEFT(name,LENGTH(name)-INSTR(REVERSE(name)," ")),
+              LEFT(name,LENGTH(name)-INSTR(REVERSE(name)," "))) AS f1
+ FROM v1;
+
+DROP VIEW v1;
+DROP TABLE t1;