Merge bk-internal:/home/bk/mysql-5.1

into  blade09.mysql.com:/tmp/mmj/mysql-5.1-runtime

9 files changed, 320 insertions, 0 deletions

diff --git a/mysql-test/r/func_str.result b/mysql-test/r/func_str.result
index 283327430ba..9253f28d038 100644
--- a/mysql-test/r/func_str.result
+++ b/mysql-test/r/func_str.result
@@ -2325,4 +2325,73 @@ abc
 SELECT INSERT('abc', 6, 3, '1234');
 INSERT('abc', 6, 3, '1234')
 abc
+CREATE TABLE t1 (a INT);
+CREATE VIEW v1 AS SELECT CRC32(a) AS C FROM t1;
+INSERT INTO t1 VALUES (1),(2),(3),(4),(5),(6),(7),(8),(9),(10);
+SELECT CRC32(a), COUNT(*) FROM t1 GROUP BY 1;
+CRC32(a)	COUNT(*)
+450215437	1
+498629140	1
+1790921346	1
+1842515611	1
+2212294583	1
+2226203566	1
+2366072709	1
+2707236321	1
+4088798008	1
+4194326291	1
+SELECT CRC32(a), COUNT(*) FROM t1 GROUP BY 1 ORDER BY 1;
+CRC32(a)	COUNT(*)
+450215437	1
+498629140	1
+1790921346	1
+1842515611	1
+2212294583	1
+2226203566	1
+2366072709	1
+2707236321	1
+4088798008	1
+4194326291	1
+SELECT * FROM (SELECT CRC32(a) FROM t1) t2;
+CRC32(a)
+2212294583
+450215437
+1842515611
+4088798008
+2226203566
+498629140
+1790921346
+4194326291
+2366072709
+2707236321
+CREATE TABLE t2 SELECT CRC32(a) FROM t1;
+desc t2;
+Field	Type	Null	Key	Default	Extra
+CRC32(a)	int(10) unsigned	YES		NULL	
+SELECT * FROM v1;
+C
+2212294583
+450215437
+1842515611
+4088798008
+2226203566
+498629140
+1790921346
+4194326291
+2366072709
+2707236321
+SELECT * FROM (SELECT * FROM v1) x;
+C
+2212294583
+450215437
+1842515611
+4088798008
+2226203566
+498629140
+1790921346
+4194326291
+2366072709
+2707236321
+DROP TABLE t1, t2;
+DROP VIEW v1;
 End of 5.0 tests
diff --git a/mysql-test/r/row.result b/mysql-test/r/row.result
index e6dea211fdc..9f57ef12e12 100644
--- a/mysql-test/r/row.result
+++ b/mysql-test/r/row.result
@@ -193,6 +193,103 @@ SELECT ROW(2,1) IN (ROW(21,2),ROW(ROW(1,1,3),0));
 ERROR 21000: Operand should contain 1 column(s)
 SELECT ROW(2,1) IN (ROW(ROW(1,1,3),0),ROW(21,2));
 ERROR 21000: Operand should contain 1 column(s)
+CREATE TABLE t1(a int, b int, c int);
+INSERT INTO t1 VALUES (1, 2, 3),
+(NULL, 2, 3  ), (1, NULL, 3  ), (1, 2,   NULL),
+(NULL, 2, 3+1), (1, NULL, 3+1), (1, 2+1, NULL),
+(NULL, 2, 3-1), (1, NULL, 3-1), (1, 2-1, NULL);
+SELECT (1,2,3) = (1,   NULL, 3);
+(1,2,3) = (1,   NULL, 3)
+NULL
+SELECT (1,2,3) = (1+1, NULL, 3);
+(1,2,3) = (1+1, NULL, 3)
+0
+SELECT (1,2,3) = (1,   NULL, 3+1);
+(1,2,3) = (1,   NULL, 3+1)
+0
+SELECT * FROM t1 WHERE (a,b,c) = (1,2,3);
+a	b	c
+1	2	3
+SELECT (1,2,3) <> (1,   NULL, 3);
+(1,2,3) <> (1,   NULL, 3)
+NULL
+SELECT (1,2,3) <> (1+1, NULL, 3);
+(1,2,3) <> (1+1, NULL, 3)
+1
+SELECT (1,2,3) <> (1,   NULL, 3+1);
+(1,2,3) <> (1,   NULL, 3+1)
+1
+SELECT * FROM t1 WHERE (a,b,c) <> (1,2,3);
+a	b	c
+NULL	2	4
+1	NULL	4
+1	3	NULL
+NULL	2	2
+1	NULL	2
+1	1	NULL
+SELECT (1,2,3) < (NULL, 2,    3);
+(1,2,3) < (NULL, 2,    3)
+NULL
+SELECT (1,2,3) < (1,    NULL, 3);
+(1,2,3) < (1,    NULL, 3)
+NULL
+SELECT (1,2,3) < (1-1,  NULL, 3);
+(1,2,3) < (1-1,  NULL, 3)
+0
+SELECT (1,2,3) < (1+1,  NULL, 3);
+(1,2,3) < (1+1,  NULL, 3)
+1
+SELECT * FROM t1 WHERE (a,b,c) < (1,2,3);
+a	b	c
+1	1	NULL
+SELECT (1,2,3) <= (NULL, 2,    3);
+(1,2,3) <= (NULL, 2,    3)
+NULL
+SELECT (1,2,3) <= (1,    NULL, 3);
+(1,2,3) <= (1,    NULL, 3)
+NULL
+SELECT (1,2,3) <= (1-1,  NULL, 3);
+(1,2,3) <= (1-1,  NULL, 3)
+0
+SELECT (1,2,3) <= (1+1,  NULL, 3);
+(1,2,3) <= (1+1,  NULL, 3)
+1
+SELECT * FROM t1 WHERE (a,b,c) <= (1,2,3);
+a	b	c
+1	2	3
+1	1	NULL
+SELECT (1,2,3) > (NULL, 2,    3);
+(1,2,3) > (NULL, 2,    3)
+NULL
+SELECT (1,2,3) > (1,    NULL, 3);
+(1,2,3) > (1,    NULL, 3)
+NULL
+SELECT (1,2,3) > (1-1,  NULL, 3);
+(1,2,3) > (1-1,  NULL, 3)
+1
+SELECT (1,2,3) > (1+1,  NULL, 3);
+(1,2,3) > (1+1,  NULL, 3)
+0
+SELECT * FROM t1 WHERE (a,b,c) > (1,2,3);
+a	b	c
+1	3	NULL
+SELECT (1,2,3) >= (NULL, 2,    3);
+(1,2,3) >= (NULL, 2,    3)
+NULL
+SELECT (1,2,3) >= (1,    NULL, 3);
+(1,2,3) >= (1,    NULL, 3)
+NULL
+SELECT (1,2,3) >= (1-1,  NULL, 3);
+(1,2,3) >= (1-1,  NULL, 3)
+1
+SELECT (1,2,3) >= (1+1,  NULL, 3);
+(1,2,3) >= (1+1,  NULL, 3)
+0
+SELECT * FROM t1 WHERE (a,b,c) >= (1,2,3);
+a	b	c
+1	2	3
+1	3	NULL
+DROP TABLE t1;
 SELECT ROW(1,1,1) = ROW(1,1,1) as `1`, ROW(1,1,1) = ROW(1,2,1) as `0`, ROW(1,NULL,1) = ROW(2,2,1) as `0`, ROW(1,NULL,1) = ROW(1,2,2) as `0`, ROW(1,NULL,1) = ROW(1,2,1) as `null` ;
 1	0	0	0	null
 1	0	0	0	NULL
diff --git a/mysql-test/r/subselect3.result b/mysql-test/r/subselect3.result
index 18feb7cb6b9..657d95b7ee3 100644
--- a/mysql-test/r/subselect3.result
+++ b/mysql-test/r/subselect3.result
@@ -692,3 +692,22 @@ a	MAX(b)	test
 2	3	h
 3	4	i
 DROP TABLE t1, t2;
+CREATE TABLE t1 (a int);
+CREATE TABLE t2 (b int, PRIMARY KEY(b));
+INSERT INTO t1 VALUES (1), (NULL), (4);
+INSERT INTO t2 VALUES (3), (1),(2), (5), (4), (7), (6);
+EXPLAIN EXTENDED 
+SELECT a FROM t1, t2 WHERE a=b AND (b NOT IN (SELECT a FROM t1));
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	3	100.00	Using where
+1	PRIMARY	t2	eq_ref	PRIMARY	PRIMARY	4	test.t1.a	1	100.00	Using index
+2	DEPENDENT SUBQUERY	t1	ALL	NULL	NULL	NULL	NULL	3	100.00	Using where
+Warnings:
+Note	1003	select `test`.`t1`.`a` AS `a` from `test`.`t1` join `test`.`t2` where ((`test`.`t2`.`b` = `test`.`t1`.`a`) and (not(<in_optimizer>(`test`.`t1`.`a`,<exists>(select 1 AS `Not_used` from `test`.`t1` where ((<cache>(`test`.`t2`.`b`) = `test`.`t1`.`a`) or isnull(`test`.`t1`.`a`)) having <is_not_null_test>(`test`.`t1`.`a`))))))
+SELECT a FROM t1, t2 WHERE a=b AND (b NOT IN (SELECT a FROM t1));
+a
+SELECT a FROM t1, t2 WHERE a=b AND (b NOT IN (SELECT a FROM t1 WHERE a > 4));
+a
+1
+4
+DROP TABLE t1,t2;
diff --git a/mysql-test/r/view.result b/mysql-test/r/view.result
index a542d3270a2..8ebf4d40067 100644
--- a/mysql-test/r/view.result
+++ b/mysql-test/r/view.result
@@ -3311,6 +3311,41 @@ lgid	clid
 2	YES
 DROP VIEW v1;
 DROP table t1,t2;
+CREATE TABLE t1 (a INT);
+INSERT INTO t1 VALUES (1),(2),(3);
+CREATE VIEW v1 AS SELECT a FROM t1 ORDER BY a;
+SELECT * FROM t1 UNION SELECT * FROM v1;
+a
+1
+2
+3
+EXPLAIN SELECT * FROM t1 UNION SELECT * FROM v1;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	3	
+2	UNION	t1	ALL	NULL	NULL	NULL	NULL	3	
+NULL	UNION RESULT	<union1,2>	ALL	NULL	NULL	NULL	NULL	NULL	
+SELECT * FROM v1 UNION SELECT * FROM t1;
+a
+1
+2
+3
+EXPLAIN SELECT * FROM v1 UNION SELECT * FROM t1;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	3	
+2	UNION	t1	ALL	NULL	NULL	NULL	NULL	3	
+NULL	UNION RESULT	<union1,2>	ALL	NULL	NULL	NULL	NULL	NULL	
+SELECT * FROM t1 UNION SELECT * FROM v1 ORDER BY a;
+a
+1
+2
+3
+EXPLAIN SELECT * FROM t1 UNION SELECT * FROM v1 ORDER BY a;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	3	
+2	UNION	t1	ALL	NULL	NULL	NULL	NULL	3	
+NULL	UNION RESULT	<union1,2>	ALL	NULL	NULL	NULL	NULL	NULL	Using filesort
+DROP VIEW v1;
+DROP TABLE t1;
 End of 5.0 tests.
 DROP DATABASE IF EXISTS `d-1`;
 CREATE DATABASE `d-1`;
diff --git a/mysql-test/t/func_str.test b/mysql-test/t/func_str.test
index ca78ced134b..ddb3d2baf7f 100644
--- a/mysql-test/t/func_str.test
+++ b/mysql-test/t/func_str.test
@@ -1165,4 +1165,22 @@ SELECT INSERT('abc', 4, 3, '1234');
 SELECT INSERT('abc', 5, 3, '1234');
 SELECT INSERT('abc', 6, 3, '1234');
 
+#
+# Bug #27530: Grouping on crc32, or create table select crc32
+#
+CREATE TABLE t1 (a INT);
+CREATE VIEW v1 AS SELECT CRC32(a) AS C FROM t1;
+
+INSERT INTO t1 VALUES (1),(2),(3),(4),(5),(6),(7),(8),(9),(10);
+SELECT CRC32(a), COUNT(*) FROM t1 GROUP BY 1;
+SELECT CRC32(a), COUNT(*) FROM t1 GROUP BY 1 ORDER BY 1;
+SELECT * FROM (SELECT CRC32(a) FROM t1) t2;
+CREATE TABLE t2 SELECT CRC32(a) FROM t1;
+desc t2;
+SELECT * FROM v1;
+SELECT * FROM (SELECT * FROM v1) x;
+
+DROP TABLE t1, t2;
+DROP VIEW v1;
+
 --echo End of 5.0 tests
diff --git a/mysql-test/t/row.test b/mysql-test/t/row.test
index bf25359b7be..d2750fecbac 100644
--- a/mysql-test/t/row.test
+++ b/mysql-test/t/row.test
@@ -110,6 +110,51 @@ SELECT ROW(2,1) IN (ROW(21,2),ROW(ROW(1,1,3),0));
 --error 1241
 SELECT ROW(2,1) IN (ROW(ROW(1,1,3),0),ROW(21,2));
 
+#
+# Bug#27704: erroneous comparison of rows with NULL components
+#
+CREATE TABLE t1(a int, b int, c int);
+INSERT INTO t1 VALUES (1, 2, 3),
+  (NULL, 2, 3  ), (1, NULL, 3  ), (1, 2,   NULL),
+  (NULL, 2, 3+1), (1, NULL, 3+1), (1, 2+1, NULL),
+  (NULL, 2, 3-1), (1, NULL, 3-1), (1, 2-1, NULL);
+
+SELECT (1,2,3) = (1,   NULL, 3);
+SELECT (1,2,3) = (1+1, NULL, 3);
+SELECT (1,2,3) = (1,   NULL, 3+1);
+SELECT * FROM t1 WHERE (a,b,c) = (1,2,3);
+
+SELECT (1,2,3) <> (1,   NULL, 3);
+SELECT (1,2,3) <> (1+1, NULL, 3);
+SELECT (1,2,3) <> (1,   NULL, 3+1);
+SELECT * FROM t1 WHERE (a,b,c) <> (1,2,3);
+
+SELECT (1,2,3) < (NULL, 2,    3);
+SELECT (1,2,3) < (1,    NULL, 3);
+SELECT (1,2,3) < (1-1,  NULL, 3);
+SELECT (1,2,3) < (1+1,  NULL, 3);
+SELECT * FROM t1 WHERE (a,b,c) < (1,2,3);
+
+SELECT (1,2,3) <= (NULL, 2,    3);
+SELECT (1,2,3) <= (1,    NULL, 3);
+SELECT (1,2,3) <= (1-1,  NULL, 3);
+SELECT (1,2,3) <= (1+1,  NULL, 3);
+SELECT * FROM t1 WHERE (a,b,c) <= (1,2,3);
+
+SELECT (1,2,3) > (NULL, 2,    3);
+SELECT (1,2,3) > (1,    NULL, 3);
+SELECT (1,2,3) > (1-1,  NULL, 3);
+SELECT (1,2,3) > (1+1,  NULL, 3);
+SELECT * FROM t1 WHERE (a,b,c) > (1,2,3);
+
+SELECT (1,2,3) >= (NULL, 2,    3);
+SELECT (1,2,3) >= (1,    NULL, 3);
+SELECT (1,2,3) >= (1-1,  NULL, 3);
+SELECT (1,2,3) >= (1+1,  NULL, 3);
+SELECT * FROM t1 WHERE (a,b,c) >= (1,2,3);
+
+DROP TABLE t1;
+
 # End of 4.1 tests
 
 #
diff --git a/mysql-test/t/sp_trans_log.test b/mysql-test/t/sp_trans_log.test
index cc3b2531bed..9b85981e2e1 100644
--- a/mysql-test/t/sp_trans_log.test
+++ b/mysql-test/t/sp_trans_log.test
@@ -1,3 +1,6 @@
+# binlog disabled in embedded server
+-- source include/not_embedded.inc
+
 # part of sp_trans test that appeared to be sensitive to binlog format
 --source include/have_innodb.inc
 --source include/have_binlog_format_mixed_or_row.inc
diff --git a/mysql-test/t/subselect3.test b/mysql-test/t/subselect3.test
index 11468cd6759..dfe09968fa2 100644
--- a/mysql-test/t/subselect3.test
+++ b/mysql-test/t/subselect3.test
@@ -528,3 +528,21 @@ SELECT a, MAX(b),
 
 
 DROP TABLE t1, t2;
+
+
+#
+# Bug #27870: crash of an equijoin query with WHERE condition containing 
+#             a subquery predicate of the form <join attr> NOT IN (SELECT ...)
+#
+
+CREATE TABLE t1 (a int);
+CREATE TABLE t2 (b int, PRIMARY KEY(b));
+INSERT INTO t1 VALUES (1), (NULL), (4);
+INSERT INTO t2 VALUES (3), (1),(2), (5), (4), (7), (6);
+
+EXPLAIN EXTENDED 
+SELECT a FROM t1, t2 WHERE a=b AND (b NOT IN (SELECT a FROM t1));
+SELECT a FROM t1, t2 WHERE a=b AND (b NOT IN (SELECT a FROM t1));
+SELECT a FROM t1, t2 WHERE a=b AND (b NOT IN (SELECT a FROM t1 WHERE a > 4));
+
+DROP TABLE t1,t2;
diff --git a/mysql-test/t/view.test b/mysql-test/t/view.test
index 620f292bc12..a599f948c1e 100644
--- a/mysql-test/t/view.test
+++ b/mysql-test/t/view.test
@@ -3197,6 +3197,22 @@ SELECT * FROM v1;
 DROP VIEW v1;
 DROP table t1,t2;
 
+#
+# Bug#27786: Inconsistent Operation Performing UNION On View With ORDER BY 
+#
+CREATE TABLE t1 (a INT); INSERT INTO t1 VALUES (1),(2),(3);
+CREATE VIEW v1 AS SELECT a FROM t1 ORDER BY a;
+
+SELECT * FROM t1 UNION SELECT * FROM v1;
+EXPLAIN SELECT * FROM t1 UNION SELECT * FROM v1;
+SELECT * FROM v1 UNION SELECT * FROM t1;
+EXPLAIN SELECT * FROM v1 UNION SELECT * FROM t1;
+SELECT * FROM t1 UNION SELECT * FROM v1 ORDER BY a;
+EXPLAIN SELECT * FROM t1 UNION SELECT * FROM v1 ORDER BY a;
+
+DROP VIEW v1;
+DROP TABLE t1;
+
 --echo End of 5.0 tests.
 
 #