From 7873b89fc50d420a5f538a5b60faf79131f86c4f Mon Sep 17 00:00:00 2001 From: unknown Date: Mon, 5 Apr 2004 13:56:05 +0300 Subject: Fixed many compiler warnings Fixed bugs in group_concat with ORDER BY and DISTINCT (Bugs #2695, #3381 and #3319) Fixed crash when doing rollback in slave and the io thread catched up with the sql thread Set locked_in_memory properly include/mysql_com.h: Fixed compiler warning libmysqld/emb_qcache.cc: Removed not used variable libmysqld/lib_sql.cc: Removed not used variable myisam/mi_locking.c: Added comment myisam/mi_rnext.c: Fixed bug in concurrent insert myisam/mi_rprev.c: Simple optimization mysql-test/r/func_gconcat.result: New tests mysql-test/t/func_gconcat.test: New tests mysql-test/t/func_group.test: Cleanup sql-common/client.c: Removed compiler warning sql/derror.cc: Better comments sql/field.cc: Removed not used function/variable sql/field.h: Removed not needed variable sql/ha_innodb.cc: Removed not used function sql/item.cc: Fixed compiler warning sql/item_cmpfunc.cc: Fixed compiler warning sql/item_func.cc: Fixed compiler warning sql/item_geofunc.cc: Fixed compiler warning sql/item_sum.cc: Fixed bugs in group_concat and added more comments (Bugs #2695, #3381 and #3319) - field->abs_offset was not needed - Wrong assumption of field order in temporary table - Some not used variables removed - Added ORDER BY fields after argument fields so that code in sql_select.cc can move all fields to point to temporary tables, if needed. - Optimized loops sql/item_sum.h: Bug fixing and cleanup of group_concat() sql/log.cc: Removed wrong comment sql/log_event.cc: Removed compiler warning sql/mysqld.cc: Set locked_in_memory properly sql/protocol.cc: Removed compiler warning sql/set_var.cc: Code cleanup sql/slave.cc: Fixed crash when doing rollback in slave and the io thread catched up with the sql thread sql/sql_cache.cc: Removed compiler warnings sql/sql_derived.cc: Removed not used variable sql/sql_insert.cc: Removed compiler warnings sql/sql_lex.cc: Removed not used lable sql/sql_lex.h: Removed compiler warnings sql/sql_parse.cc: Removed compiler warnings sql/sql_prepare.cc: Removed compiler warnings sql/sql_select.cc: Removed not used variables Added function comments sql/sql_show.cc: Removed compiler warnings sql/sql_yacc.yy: Fix for ORDER BY handling in GROUP_CONCAT() --- mysql-test/t/func_gconcat.test | 70 ++++++++++++++++++++++++++++++++++-------- mysql-test/t/func_group.test | 1 - 2 files changed, 57 insertions(+), 14 deletions(-) (limited to 'mysql-test/t') diff --git a/mysql-test/t/func_gconcat.test b/mysql-test/t/func_gconcat.test index d004c81f14e..87df427616a 100644 --- a/mysql-test/t/func_gconcat.test +++ b/mysql-test/t/func_gconcat.test @@ -50,22 +50,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 @@ -129,4 +119,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; +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 # -- cgit v1.2.1 From 8c06c8c01025668cdfa6ae66f25060b012ef2347 Mon Sep 17 00:00:00 2001 From: unknown Date: Mon, 5 Apr 2004 17:55:26 +0500 Subject: WL1368: SHOW GRANTS FOR CURRENT USER 'SHOW GRANTS' syntax is added 'SHOW GRANTS FOR CURRENT_USER' syntax is added 'SHOW GRANTS FOR CURRENT_USER()' syntax is added CURRENT_USER without parens in expressions(SELECT CURRENT_USER;) mysql-test/r/grant2.result: WL1368: SHOW GRANTS FOR CURRENT USER mysql-test/r/grant_cache.result: WL1368: SHOW GRANTS FOR CURRENT USER mysql-test/t/grant2.test: WL1368: SHOW GRANTS FOR CURRENT USER mysql-test/t/grant_cache.test: WL1368: SHOW GRANTS FOR CURRENT USER sql/lex.h: WL1368: SHOW GRANTS FOR CURRENT USER sql/sql_yacc.yy: WL1368: SHOW GRANTS FOR CURRENT USER --- mysql-test/t/grant2.test | 1 + mysql-test/t/grant_cache.test | 8 ++++++++ 2 files changed, 9 insertions(+) (limited to 'mysql-test/t') 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) -- cgit v1.2.1 From b5d93e4ef5409f76ce509edffd7d35b0ba987f64 Mon Sep 17 00:00:00 2001 From: unknown Date: Mon, 5 Apr 2004 18:27:19 +0500 Subject: corrected wrong checking of DBUG_OFF in mysql-test/t/synchronization.test mysql-test/r/have_debug.require: corrected mistake mysql-test/t/synchronization.test: corrected wrong test --- mysql-test/t/synchronization.test | 2 +- 1 file changed, 1 insertion(+), 1 deletion(-) (limited to 'mysql-test/t') diff --git a/mysql-test/t/synchronization.test b/mysql-test/t/synchronization.test index 695e405544a..bbea4c44dad 100644 --- a/mysql-test/t/synchronization.test +++ b/mysql-test/t/synchronization.test @@ -1,4 +1,4 @@ --- source include/have_crypt.inc +-- source include/have_debug.inc # # Test for Bug #2385 CREATE TABLE LIKE lacks locking on source and destination table -- cgit v1.2.1 From 315f5d27c4579220ce56f56d29ff299ad715ec11 Mon Sep 17 00:00:00 2001 From: unknown Date: Tue, 6 Apr 2004 19:17:13 +0500 Subject: fixed the tests mysql-test/t/synchronization.test for Bug #2385 CREATE TABLE LIKE lacks locking on source and destination table BitKeeper/deleted/.del-synchronization-master.opt~265be23ead00949: Delete: mysql-test/t/synchronization-master.opt BitKeeper/deleted/.del-have_debug.require~d981522532c711b2: Delete: mysql-test/r/have_debug.require BitKeeper/deleted/.del-have_debug.inc~f4dc5160b9912226: Delete: mysql-test/include/have_debug.inc mysql-test/r/synchronization.result: fixed the test for Bug #2385 CREATE TABLE LIKE lacks locking on source and destination table mysql-test/t/synchronization.test: fixed the test for Bug #2385 CREATE TABLE LIKE lacks locking on source and destination table sql/sql_table.cc: deleted test sleep from mysql_create_like_table --- mysql-test/t/synchronization-master.opt | 1 - mysql-test/t/synchronization.test | 64 ++++++++++++++------------------- 2 files changed, 26 insertions(+), 39 deletions(-) delete mode 100644 mysql-test/t/synchronization-master.opt (limited to 'mysql-test/t') 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 bbea4c44dad..7bdeaa8a740 100644 --- a/mysql-test/t/synchronization.test +++ b/mysql-test/t/synchronization.test @@ -1,45 +1,33 @@ --- source include/have_debug.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; -- cgit v1.2.1 From 6c3106456def1ba07d0aa2610be615222095df7f Mon Sep 17 00:00:00 2001 From: unknown Date: Tue, 6 Apr 2004 20:26:35 +0500 Subject: fixed bug #3342 "SHOW CREATE DATABASE seems to require DROP privilege" (now it require only SELECT privilege on database) mysql-test/r/show_check.result: added test for bug #3342 "SHOW CREATE DATABASE seems to require DROP privilege" mysql-test/t/show_check.test: added test for bug #3342 "SHOW CREATE DATABASE seems to require DROP privilege" --- mysql-test/t/show_check.test | 48 ++++++++++++++++++++++++++++++++++++++++++++ 1 file changed, 48 insertions(+) (limited to 'mysql-test/t') 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; -- cgit v1.2.1 From e9447881eb13378b06f0939091ab4e258a83ad51 Mon Sep 17 00:00:00 2001 From: unknown Date: Wed, 7 Apr 2004 04:33:58 +0300 Subject: Portability fixes Fixed problems with group_concat() and HAVING Updated crash-me values sql-bench/limits/mysql-4.0.cfg: Rename: sql-bench/limits/mysql.cfg -> sql-bench/limits/mysql-4.0.cfg include/my_global.h: Safety fix libmysqld/Makefile.am: Portability fix (For AIX 64 bit) mysql-test/r/func_gconcat.result: More tests mysql-test/t/func_gconcat.test: More tests sql/field.cc: Cleanups sql/init.cc: moved thread_stack_min to right place sql/item_sum.cc: Fixed problems with group_concat() and HAVING Removed some not needed variables sql/item_sum.h: Fixed problems with group_concat() and HAVING Removed some not needed variables sql/mysqld.cc: Moved thread_stack_min to right place to handle case where we didn't get as much stack space as we asked for sql/sql_parse.cc: More debugging sql/sql_select.cc: Cleanup sql/sql_yacc.yy: Fixed handling of Item_group_concat() in having. (Arguments should not be handled as refs) --- mysql-test/t/func_gconcat.test | 13 +++++++++---- 1 file changed, 9 insertions(+), 4 deletions(-) (limited to 'mysql-test/t') diff --git a/mysql-test/t/func_gconcat.test b/mysql-test/t/func_gconcat.test index 87df427616a..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; @@ -108,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() @@ -147,7 +152,7 @@ drop table t1,t2; 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; +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 -- cgit v1.2.1