Merge sanja.is.com.ua:/home/bell/mysql/bk/mysql-4.1

into sanja.is.com.ua:/home/bell/mysql/bk/work-ps3-4.1


mysql-test/r/union.result:
  Auto merged
sql/item.cc:
  Auto merged
sql/item_sum.cc:
  Auto merged
sql/item_sum.h:
  Auto merged
sql/log_event.cc:
  Auto merged
sql/slave.cc:
  Auto merged
sql/sql_base.cc:
  Auto merged
sql/sql_cache.cc:
  Auto merged
sql/sql_insert.cc:
  Auto merged
sql/sql_prepare.cc:
  Auto merged
sql/sql_select.cc:
  Auto merged
sql/sql_parse.cc:
  Auto merged

7 files changed, 148 insertions, 56 deletions

diff --git a/mysql-test/t/func_gconcat.test b/mysql-test/t/func_gconcat.test
index d004c81f14e..9d99a57afe5 100644
--- a/mysql-test/t/func_gconcat.test
+++ b/mysql-test/t/func_gconcat.test
@@ -29,7 +29,8 @@ select grp,group_concat(c order by c) from t1 group by grp;
 select grp,group_concat(c order by c desc) from t1 group by grp;
 select grp,group_concat(d order by a) from t1 group by grp;
 select grp,group_concat(d order by a desc) from t1 group by grp;
-select grp,group_concat(a order by a,d+c) from t1 group by grp;
+select grp,group_concat(a order by a,d+c-ascii(c)-a) from t1 group by grp;
+select grp,group_concat(a order by d+c-ascii(c),a) from t1 group by grp;
 select grp,group_concat(c order by 1) from t1 group by grp;
 select grp,group_concat(c order by "c") from t1 group by grp;
 select grp,group_concat(distinct c order by c) from t1 group by grp;
@@ -50,22 +51,12 @@ select grp,group_concat(c order by grp desc) from t1 group by grp order by grp;
 select grp, group_concat(a separator "")+0 from t1 group by grp;

 select grp, group_concat(a separator "")+0.0 from t1 group by grp;

 select grp, ROUND(group_concat(a separator "")) from t1 group by grp;

+drop table t1;
 
 # Test NULL values
 
-drop table t1;
 create table t1 (grp int, c char(10));
-insert into t1 values (1,NULL);
-insert into t1 values (2,"b");
-insert into t1 values (2,NULL);
-insert into t1 values (3,"E");
-insert into t1 values (3,NULL);
-insert into t1 values (3,"D");
-insert into t1 values (3,NULL);
-insert into t1 values (3,NULL);
-insert into t1 values (3,"D");
-insert into t1 values (4,"");
-insert into t1 values (5,NULL);
+insert into t1 values (1,NULL),(2,"b"),(2,NULL),(3,"E"),(3,NULL),(3,"D"),(3,NULL),(3,NULL),(3,"D"),(4,""),(5,NULL);
 select grp,group_concat(c order by c) from t1 group by grp;
 
 # Test warnings
@@ -118,8 +109,12 @@ drop table t2;
 
 # check having
 create table t1 (bar varchar(32));
-insert into t1 values('test'),('test2');
-select * from t1 having group_concat(bar)='';
+insert into t1 values('test1'),('test2');
+select group_concat(bar order by concat(bar,bar)) from t1;
+select group_concat(bar order by concat(bar,bar) desc) from t1;
+select bar from t1 having group_concat(bar)='';
+select bar from t1 having instr(group_concat(bar), "test") > 0;
+select bar from t1 having instr(group_concat(bar order by concat(bar,bar) desc), "test2,test1") > 0;
 drop table t1;
 
 # ORDER BY fix_fields()
@@ -129,4 +124,58 @@ insert into t1 values (0,"a"),(0,"b"),(1,"c");
 insert into t2 values (1),(2),(3);
 select  group_concat(a1 order by (t1.a IN (select a0 from t2))) from t1;
 select  group_concat(a1 order by (t1.a)) from t1;
-drop table t1, t2;
\ No newline at end of file
+drop table t1, t2;
+
+#
+# Problem with GROUP BY (Bug #2695)
+#
+
+CREATE TABLE t1 (id1 tinyint(4) NOT NULL, id2 tinyint(4) NOT NULL);
+INSERT INTO t1 VALUES (1, 1),(1, 2),(1, 3),(1, 4),(1, 5),(2, 1),(2, 2),(2, 3);
+CREATE TABLE t2 (id1 tinyint(4) NOT NULL); 
+INSERT INTO t2 VALUES (1),(2),(3),(4),(5);
+SELECT t1.id1, GROUP_CONCAT(t1.id2 ORDER BY t1.id2 ASC) AS concat_id FROM t1, t2 WHERE t1.id1 = t2.id1 AND t1.id1=1 GROUP BY t1.id1;
+SELECT t1.id1, GROUP_CONCAT(t1.id2 ORDER BY t1.id2 ASC) AS concat_id FROM t1, t2 WHERE t1.id1 = t2.id1 GROUP BY t1.id1;
+SELECT t1.id1, GROUP_CONCAT(t1.id2 ORDER BY t1.id2 DESC) AS concat_id FROM t1, t2 WHERE t1.id1 = t2.id1 GROUP BY t1.id1;
+SELECT t1.id1, GROUP_CONCAT(t1.id2 ORDER BY 6-t1.id2 ASC) AS concat_id FROM t1, t2 WHERE t1.id1 = t2.id1 GROUP BY t1.id1;
+
+# The following failed when it was run twice:
+SELECT t1.id1, GROUP_CONCAT(t1.id2,6-t1.id2 ORDER BY 6-t1.id2 ASC) AS concat_id FROM t1, t2 WHERE t1.id1 = t2.id1 GROUP BY t1.id1;
+SELECT t1.id1, GROUP_CONCAT(t1.id2,6-t1.id2 ORDER BY 6-t1.id2 ASC) AS concat_id FROM t1, t2 WHERE t1.id1 = t2.id1 GROUP BY t1.id1;
+
+SELECT t1.id1, GROUP_CONCAT(t1.id2,"/",6-t1.id2 ORDER BY 1+0,6-t1.id2,t1.id2 ASC) AS concat_id FROM t1, t2 WHERE t1.id1 = t2.id1 GROUP BY t1.id1;
+drop table t1,t2;
+
+#
+# Problem with distinct (Bug #3381)
+#
+
+create table t1 (s1 char(10), s2 int not null);
+insert into t1 values ('a',2),('b',2),('c',1),('a',3),('b',4),('c',4);
+select distinct s1 from t1 order by s2,s1;
+select group_concat(distinct s1) from t1;
+select group_concat(distinct s1 order by s2) from t1 where s2 < 4;
+# The following is wrong and needs to be fixed ASAP
+select group_concat(distinct s1 order by s2) from t1;
+drop table t1;
+
+#
+# Test with subqueries (Bug #3319)
+#
+
+create table t1 (a int, c int); 
+insert into t1 values (1, 2), (2, 3), (2, 4), (3, 5);
+create table t2 (a int, c int);
+insert into t2 values (1, 5), (2, 4), (3, 3), (3,3);
+select group_concat(c) from t1;
+select group_concat(c order by (select c from t2 where t2.a=t1.a limit 1)) as grp from t1;
+
+select group_concat(c order by (select mid(group_concat(c order by a),1,5) from t2 where t2.a=t1.a)) as grp from t1;
+select group_concat(c order by (select mid(group_concat(c order by a),1,5) from t2 where t2.a=t1.a) desc) as grp from t1;
+
+# The following returns random results as we are sorting on blob addresses
+# select group_concat(c order by (select group_concat(c order by a) from t2 where t2.a=t1.a)) as grp from t1;
+# select group_concat(c order by (select group_concat(c) from t2 where a=t1.a)) as grp from t1;
+
+select a,c,(select group_concat(c order by a) from t2 where a=t1.a) as grp from t1 order by grp;
+drop table t1,t2;
diff --git a/mysql-test/t/func_group.test b/mysql-test/t/func_group.test
index ce89cbe1b22..2bd4838f934 100644
--- a/mysql-test/t/func_group.test
+++ b/mysql-test/t/func_group.test
@@ -134,7 +134,6 @@ select max(t2.a1) from t1 left outer join t2 on t1.a2=t2.a1 and 1=0 where t2.a1=
 select max(t1.a2),max(t2.a1) from t1 left outer join t2 on t1.a1=10;
 drop table t1,t2;
 
-
 #
 # Tests to check MIN/MAX query optimization
 #
diff --git a/mysql-test/t/grant2.test b/mysql-test/t/grant2.test
index 148c4d3da21..1fc1ed78385 100644
--- a/mysql-test/t/grant2.test
+++ b/mysql-test/t/grant2.test
@@ -15,6 +15,7 @@ grant all privileges on `my\_%`.* to mysqltest_1@localhost with grant option;
 connect (user1,localhost,mysqltest_1,,);
 connection user1;
 select current_user();
+select current_user;
 grant all privileges on `my\_1`.* to mysqltest_2@localhost with grant option;
 --error 1044
 grant all privileges on `my_%`.* to mysqltest_3@localhost with grant option;
diff --git a/mysql-test/t/grant_cache.test b/mysql-test/t/grant_cache.test
index 5ebf621ff40..7806253124e 100644
--- a/mysql-test/t/grant_cache.test
+++ b/mysql-test/t/grant_cache.test
@@ -12,6 +12,8 @@ reset query cache;
 flush status;
 connect (root,localhost,root,,test,$MASTER_MYPORT,master.sock);
 connection root;
+show grants for current_user;
+show grants;
 --disable_warnings
 create database if not exists mysqltest;
 --enable_warnings
@@ -43,6 +45,7 @@ grant SELECT(a) on mysqltest.t1 to mysqltest_3@localhost;
 # The following queries should be fetched from cache
 connect (user1,localhost,mysqltest_1,,mysqltest,$MASTER_MYPORT,master.sock);
 connection user1;
+show grants for current_user();
 show status like "Qcache_queries_in_cache";
 show status like "Qcache_hits";
 show status like "Qcache_not_cached";
@@ -64,6 +67,10 @@ show status like "Qcache_queries_in_cache";
 show status like "Qcache_hits";
 show status like "Qcache_not_cached";
 
+connect (unkuser,localhost,,,,$MASTER_MYPORT,master.sock);
+connection unkuser;
+show grants for current_user();
+
 # The following queries should be fetched from cache
 connect (user2,localhost,mysqltest_2,,mysqltest,$MASTER_MYPORT,master.sock);
 connection user2;
@@ -104,6 +111,7 @@ show status like "Qcache_not_cached";
 connect (user4,localhost,mysqltest_1,,*NO-ONE*,$MASTER_MYPORT,master.sock);
 connection user4;
 select "user4";
+show grants;
 --error 1046
 select a from t1;
 # The following query is not cached before (different database)
diff --git a/mysql-test/t/show_check.test b/mysql-test/t/show_check.test
index 638626a7456..b0307af19bb 100644
--- a/mysql-test/t/show_check.test
+++ b/mysql-test/t/show_check.test
@@ -256,3 +256,51 @@ delete from t3 where a=5;
 show table status;
 
 drop table t1, t2, t3;
+
+#
+# Test for bug #3342 SHOW CREATE DATABASE seems to require DROP privilege
+#
+
+create database test_$1;
+show create database test_$1;
+create table test_$1.t1(a int);
+insert into test_$1.t1 values(1);
+grant select on `test_$1`.* to mysqltest_1@localhost;
+grant usage  on `test_$1`.* to mysqltest_2@localhost;
+grant drop   on `test_$1`.* to mysqltest_3@localhost;
+
+connect (con1,localhost,mysqltest_1,,test_$1);
+connection con1;
+select * from t1;
+show create database test_$1;
+--error 1044
+drop table t1;
+--error 1044
+drop database test_$1;
+
+connect (con2,localhost,mysqltest_2,,test);
+connection con2;
+--error 1044
+select * from test_$1.t1;
+--error 1044
+show create database test_$1;
+--error 1044
+drop table test_$1.t1;
+--error 1044
+drop database test_$1;
+
+connect (con3,localhost,mysqltest_3,,test);
+connection con3;
+--error 1044
+select * from test_$1.t1;
+--error 1044
+show create database test_$1;
+drop table test_$1.t1;
+drop database test_$1;
+
+connection default;
+delete from mysql.user 
+where user='mysqltest_1' || user='mysqltest_2' || user='mysqltest_3';
+delete from mysql.db   
+where user='mysqltest_1' || user='mysqltest_2' || user='mysqltest_3';
+flush privileges;
diff --git a/mysql-test/t/synchronization-master.opt b/mysql-test/t/synchronization-master.opt
deleted file mode 100644
index 710a0395d55..00000000000
--- a/mysql-test/t/synchronization-master.opt
+++ /dev/null
@@ -1 +0,0 @@
---exit-info=2048
\ No newline at end of file
diff --git a/mysql-test/t/synchronization.test b/mysql-test/t/synchronization.test
index 695e405544a..7bdeaa8a740 100644
--- a/mysql-test/t/synchronization.test
+++ b/mysql-test/t/synchronization.test
@@ -1,45 +1,33 @@
--- source include/have_crypt.inc
-
 #
 # Test for Bug #2385 CREATE TABLE LIKE lacks locking on source and destination table
 #
 
-connect (con_to_sleep,localhost,lock_controller,,);
-connect (con_to_harm_sleeper,localhost,root,,);
+connect (con1,localhost,root,,);
+connect (con2,localhost,root,,);
 
 # locking of source:
-connection con_to_sleep;
-CREATE TABLE t1 (a int);
-send CREATE TABLE t2 LIKE t1;
-
-connection con_to_harm_sleeper;
-sleep 1;
-ALTER TABLE t1 add key(a);
-
-connection con_to_sleep;
-sleep 4;
-SHOW CREATE TABLE t2;
-drop table t1, t2;
-
-# locking of destination:
-connection con_to_sleep;
-CREATE TABLE t1 (a int);
-send CREATE TABLE t2 LIKE t1;
-
-connection con_to_harm_sleeper;
-sleep 1;
-CREATE TABLE t2 (b int);
-disable_query_log;
-select "-----------" as "let's take a look at result of create .. like : ";
-enable_query_log;
-
-connection con_to_sleep;
-sleep 1;
---error 1
-reap;
-disable_query_log;
-select "" as "-----------";
-enable_query_log;
-SHOW CREATE TABLE t2;
 
-drop table t1, t2;
\ No newline at end of file
+CREATE TABLE t1 (x1 int);
+let $1= 10;
+while ($1)
+{
+  connection con1;
+    send ALTER TABLE t1 CHANGE x1 x2 int;
+  connection con2;
+    CREATE TABLE t2 LIKE t1;
+    replace_result x1 xx x2 xx;
+    SHOW CREATE TABLE t2;
+    DROP TABLE t2;
+  connection con1;
+    reap;
+    send ALTER TABLE t1 CHANGE x2 x1 int;
+  connection con2;
+    CREATE TABLE t2 LIKE t1;
+    replace_result x1 xx x2 xx;
+    SHOW CREATE TABLE t2;
+    DROP TABLE t2;
+  connection con1;
+    reap;
+  dec $1;
+}
+DROP TABLE t1;