Merge bk-internal.mysql.com:/home/bk/mysql-4.1

into mashka.mysql.fi:/home/my/mysql-4.1


BitKeeper/etc/logging_ok:
  auto-union
sql/item_func.cc:
  Auto merged

10 files changed, 135 insertions, 6 deletions

diff --git a/mysql-test/r/count_distinct3.result b/mysql-test/r/count_distinct3.result
new file mode 100644
index 00000000000..633bb2fa252
--- /dev/null
+++ b/mysql-test/r/count_distinct3.result
@@ -0,0 +1,7 @@
+DROP TABLE IF EXISTS t1, t2;
+CREATE TABLE t1 (id INTEGER, grp TINYINT, id_rev INTEGER);
+SELECT COUNT(*) FROM t1;
+COUNT(*)
+4181000
+SELECT COUNT(DISTINCT id) FROM t1 GROUP BY grp;
+DROP TABLE t1;
diff --git a/mysql-test/r/ctype_latin1_de.result b/mysql-test/r/ctype_latin1_de.result
index e0cb7008899..7503c16a85e 100644
--- a/mysql-test/r/ctype_latin1_de.result
+++ b/mysql-test/r/ctype_latin1_de.result
@@ -264,3 +264,22 @@ select * from t1 where word like CAST(0xDF as CHAR);
 word	word2
 ß	ß
 drop table t1;
+CREATE TABLE t1 (
+s1 CHAR(5) CHARACTER SET latin1 COLLATE latin1_german2_ci
+);
+INSERT INTO t1 VALUES ('Ü');
+INSERT INTO t1 VALUES ('ue');
+SELECT DISTINCT s1 FROM t1;
+s1
+Ü
+SELECT s1,COUNT(*) FROM t1 GROUP BY s1;
+s1	COUNT(*)
+Ü	2
+SELECT COUNT(DISTINCT s1) FROM t1;
+COUNT(DISTINCT s1)
+1
+SELECT FIELD('ue',s1), FIELD('Ü',s1), s1='ue', s1='Ü' FROM t1;
+FIELD('ue',s1)	FIELD('Ü',s1)	s1='ue'	s1='Ü'
+1	1	1	1
+1	1	1	1
+DROP TABLE t1;
diff --git a/mysql-test/r/derived.result b/mysql-test/r/derived.result
index 5f405d83fa5..61937ba9266 100644
--- a/mysql-test/r/derived.result
+++ b/mysql-test/r/derived.result
@@ -137,7 +137,7 @@ a	t
 explain select count(*) from t1 as tt1, (select * from t1) as tt2;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Select tables optimized away
-2	DERIVED	tt1	index	NULL	a	4	NULL	10000	Using index
+2	DERIVED	tt1	ALL	NULL	NULL	NULL	NULL	10000	
 drop table t1;
 SELECT * FROM (SELECT (SELECT * FROM (SELECT 1 as a) as a )) as b;
 (SELECT * FROM (SELECT 1 as a) as a )
diff --git a/mysql-test/r/func_str.result b/mysql-test/r/func_str.result
index 37694ad2f0a..929ab0535d8 100644
--- a/mysql-test/r/func_str.result
+++ b/mysql-test/r/func_str.result
@@ -553,3 +553,8 @@ SUBSTR('abcdefg',-1,-1)
 select SUBSTR('abcdefg',1,-1) FROM DUAL;
 SUBSTR('abcdefg',1,-1)
 
+create table t7 (s1 char);
+select * from t7
+where concat(s1 collate latin1_general_ci,s1 collate latin1_swedish_ci) = 'AA';
+ERROR HY000: Illegal mix of collations (latin1_general_ci,EXPLICIT) and (latin1_swedish_ci,EXPLICIT) for operation 'concat'
+drop table t7;
diff --git a/mysql-test/r/innodb.result b/mysql-test/r/innodb.result
index 01aad9dcd62..69268f1d5c5 100644
--- a/mysql-test/r/innodb.result
+++ b/mysql-test/r/innodb.result
@@ -1322,10 +1322,10 @@ SELECT t2.id, t1.label FROM t2 INNER JOIN
 (SELECT t1.id_object as id_object FROM t1 WHERE t1.label LIKE '%test%') AS lbl 
 ON (t2.id = lbl.id_object) INNER JOIN t1 ON (t2.id = t1.id_object);
 id	label
-3382	Fournisseur Test
-102	Fournisseur Test
-1794	Fournisseur Test
-1822	Fournisseur Test
-3524	Fournisseur Test
+3382	Test
+102	Le Pekin (Test)
+1794	Test de resto
+1822	Test 3
+3524	Societe Test
 3525	Fournisseur Test
 drop table t1,t2;
diff --git a/mysql-test/r/union.result b/mysql-test/r/union.result
index 5b7b26bc1bb..6d8bd263546 100644
--- a/mysql-test/r/union.result
+++ b/mysql-test/r/union.result
@@ -328,3 +328,16 @@ id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t1	const	PRIMARY	PRIMARY	4	const	1	
 2	UNION	t1	ref	b	b	5	const	1	Using where
 drop table t1,t2;
+create table t1 (   id int not null auto_increment, primary key (id)   ,user_name text );
+create table t2 (    id int not null auto_increment, primary key (id)   ,group_name text );
+create table t3 (    id int not null auto_increment, primary key (id)   ,user_id int   ,index user_idx (user_id)   ,foreign key (user_id) references users(id)   ,group_id int   ,index group_idx (group_id)   ,foreign key (group_id) references groups(id) );
+insert into t1 (user_name) values ('Tester');
+insert into t2 (group_name) values ('Group A');
+insert into t2 (group_name) values ('Group B');
+insert into t3 (user_id, group_id) values (1,1);
+select 1 'is_in_group', a.user_name, c.group_name, b.id from t1 a, t3 b, t2 c where a.id = b.user_id and b.group_id = c.id UNION  select 0 'is_in_group', a.user_name, c.group_name, null from t1 a, t2 c;
+is_in_group	user_name	group_name	id
+1	Tester	Group A	1
+0	Tester	Group A	NULL
+0	Tester	Group B	NULL
+drop table t1, t2, t3;
diff --git a/mysql-test/t/count_distinct3.test b/mysql-test/t/count_distinct3.test
new file mode 100644
index 00000000000..e6cc98d47df
--- /dev/null
+++ b/mysql-test/t/count_distinct3.test
@@ -0,0 +1,55 @@
+#
+# this is a test for error 1032 in count(distinct) + group by, introduced in 
+# mysql-4.1
+#
+
+--disable_warnings
+DROP TABLE IF EXISTS t1, t2;
+--enable_warnings
+
+CREATE TABLE t1 (id INTEGER, grp TINYINT, id_rev INTEGER);
+
+--disable_query_log
+SET @rnd_max= 2147483647;
+let $1 = 1000;
+while ($1)
+{
+  SET @rnd= RAND();
+  SET @id = CAST(@rnd * @rnd_max AS UNSIGNED);
+  SET @id_rev= @rnd_max - @id;
+  SET @grp= CAST(128.0 * @rnd AS UNSIGNED); 
+  INSERT INTO t1 (id, grp, id_rev) VALUES (@id, @grp, @id_rev); 
+  dec $1;
+}
+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;
+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;
+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;
+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;
+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;
+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;
+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;
+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;
+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;
+DROP TABLE t2;
+--enable_query_log
+
+SELECT COUNT(*) FROM t1;
+
+# As t1 contains random numbers, results are different from test to test. 
+# That's okay, because we test only that select doesn't yield an
+# error. Note, that --disable_result_log doesn't suppress error output.
+
+--disable_result_log
+SELECT COUNT(DISTINCT id) FROM t1 GROUP BY grp;
+--enable_result_log
+DROP TABLE t1;
diff --git a/mysql-test/t/ctype_latin1_de.test b/mysql-test/t/ctype_latin1_de.test
index 22a4e14158d..00ac128a478 100644
--- a/mysql-test/t/ctype_latin1_de.test
+++ b/mysql-test/t/ctype_latin1_de.test
@@ -73,3 +73,14 @@ select * from t1 where word like 'AE';
 select * from t1 where word like binary 0xDF;
 select * from t1 where word like CAST(0xDF as CHAR);
 drop table t1;
+
+CREATE TABLE t1 (
+s1 CHAR(5) CHARACTER SET latin1 COLLATE latin1_german2_ci
+);
+INSERT INTO t1 VALUES ('Ü');
+INSERT INTO t1 VALUES ('ue');
+SELECT DISTINCT s1 FROM t1;
+SELECT s1,COUNT(*) FROM t1 GROUP BY s1;
+SELECT COUNT(DISTINCT s1) FROM t1;
+SELECT FIELD('ue',s1), FIELD('Ü',s1), s1='ue', s1='Ü' FROM t1;
+DROP TABLE t1;
diff --git a/mysql-test/t/func_str.test b/mysql-test/t/func_str.test
index f158eaa3764..4c996121446 100644
--- a/mysql-test/t/func_str.test
+++ b/mysql-test/t/func_str.test
@@ -305,3 +305,13 @@ select SUBSTR('abcdefg',-1,5) FROM DUAL;
 select SUBSTR('abcdefg',0,0) FROM DUAL;
 select SUBSTR('abcdefg',-1,-1) FROM DUAL;
 select SUBSTR('abcdefg',1,-1) FROM DUAL;
+
+#
+# Test that fix_fields doesn't follow to upper level (to comparison)
+# when an error on a lower level (in concat) has accured:
+#
+create table t7 (s1 char);
+--error 1265
+select * from t7
+where concat(s1 collate latin1_general_ci,s1 collate latin1_swedish_ci) = 'AA';
+drop table t7;
diff --git a/mysql-test/t/union.test b/mysql-test/t/union.test
index 6d857a4b40f..aeea27ade0f 100644
--- a/mysql-test/t/union.test
+++ b/mysql-test/t/union.test
@@ -209,3 +209,12 @@ explain (select * from t1 where a=1) union (select * from t2 where a=1);
 explain (select * from t1 where a=1 and b=10) union (select t1.a,t2.a from t1,t2 where t1.a=t2.a);
 explain (select * from t1 where a=1) union (select * from t1 where b=1);
 drop table t1,t2;
+create table t1 (   id int not null auto_increment, primary key (id)   ,user_name text );
+create table t2 (    id int not null auto_increment, primary key (id)   ,group_name text );
+create table t3 (    id int not null auto_increment, primary key (id)   ,user_id int   ,index user_idx (user_id)   ,foreign key (user_id) references users(id)   ,group_id int   ,index group_idx (group_id)   ,foreign key (group_id) references groups(id) );
+insert into t1 (user_name) values ('Tester');
+insert into t2 (group_name) values ('Group A');
+insert into t2 (group_name) values ('Group B');
+insert into t3 (user_id, group_id) values (1,1);
+select 1 'is_in_group', a.user_name, c.group_name, b.id from t1 a, t3 b, t2 c where a.id = b.user_id and b.group_id = c.id UNION  select 0 'is_in_group', a.user_name, c.group_name, null from t1 a, t2 c;
+drop table t1, t2, t3;