diff options
| -rw-r--r-- | mysql-test/r/grant.result | 34 | ||||
| -rw-r--r-- | mysql-test/r/information_schema.result | 24 | ||||
| -rw-r--r-- | mysql-test/r/multi_update.result | 10 | ||||
| -rw-r--r-- | mysql-test/r/packet.result | 4 | ||||
| -rw-r--r-- | mysql-test/r/query_cache_notembedded.result | 1 | ||||
| -rw-r--r-- | mysql-test/r/subselect.result | 252 | ||||
| -rw-r--r-- | mysql-test/r/synchronization.result | 44 | ||||
| -rw-r--r-- | mysql-test/t/backup.test | 16 | ||||
| -rw-r--r-- | mysql-test/t/check.test | 21 | ||||
| -rw-r--r-- | mysql-test/t/compress.test | 11 | ||||
| -rw-r--r-- | mysql-test/t/grant.test | 214 | ||||
| -rw-r--r-- | mysql-test/t/information_schema.test | 174 | ||||
| -rw-r--r-- | mysql-test/t/multi_update.test | 74 | ||||
| -rw-r--r-- | mysql-test/t/overflow.test | 10 | ||||
| -rw-r--r-- | mysql-test/t/packet.test | 20 | ||||
| -rw-r--r-- | mysql-test/t/query_cache_notembedded.test | 18 | ||||
| -rw-r--r-- | mysql-test/t/sp-threads.test | 29 | ||||
| -rw-r--r-- | mysql-test/t/subselect.test | 598 | ||||
| -rw-r--r-- | mysql-test/t/synchronization.test | 22 | ||||
| -rw-r--r-- | mysql-test/t/timezone_grant.test | 41 |
20 files changed, 912 insertions, 705 deletions
diff --git a/mysql-test/r/grant.result b/mysql-test/r/grant.result index 18f11ebcc71..e7e5e1e7165 100644 --- a/mysql-test/r/grant.result +++ b/mysql-test/r/grant.result @@ -164,7 +164,7 @@ Warnings: Warning 1364 Field 'ssl_cipher' doesn't have a default value Warning 1364 Field 'x509_issuer' doesn't have a default value Warning 1364 Field 'x509_subject' doesn't have a default value -insert into mysql.db (host, db, user, select_priv) values +insert into mysql.db (host, db, user, select_priv) values ('localhost', 'a%', 'test11', 'Y'), ('localhost', 'ab%', 'test11', 'Y'); alter table mysql.db order by db asc; flush privileges; @@ -264,7 +264,7 @@ drop user mysqltest_1@localhost; SET NAMES koi8r; CREATE DATABASE ÂÄ; USE ÂÄ; -CREATE TABLE ÔÁ (ËÏÌ int); +CREATE TABLE ÔÁ (ËÏÌ INT); GRANT SELECT ON ÂÄ.* TO ÀÚÅÒ@localhost; SHOW GRANTS FOR ÀÚÅÒ@localhost; Grants for ÀÚÅÒ@localhost @@ -383,21 +383,21 @@ grant update (a) on mysqltest_1.t1 to mysqltest_3@localhost; grant select (b) on mysqltest_1.t2 to mysqltest_3@localhost; grant select (c) on mysqltest_2.t1 to mysqltest_3@localhost; grant update (d) on mysqltest_2.t2 to mysqltest_3@localhost; -SELECT * FROM INFORMATION_SCHEMA.COLUMN_PRIVILEGES -WHERE GRANTEE = '''mysqltest_3''@''localhost''' -ORDER BY TABLE_NAME,COLUMN_NAME,PRIVILEGE_TYPE; +SELECT * FROM INFORMATION_SCHEMA.COLUMN_PRIVILEGES +WHERE GRANTEE = '''mysqltest_3''@''localhost''' + ORDER BY TABLE_NAME,COLUMN_NAME,PRIVILEGE_TYPE; GRANTEE TABLE_CATALOG TABLE_SCHEMA TABLE_NAME COLUMN_NAME PRIVILEGE_TYPE IS_GRANTABLE 'mysqltest_3'@'localhost' NULL mysqltest_1 t1 a UPDATE NO 'mysqltest_3'@'localhost' NULL mysqltest_2 t1 c SELECT NO 'mysqltest_3'@'localhost' NULL mysqltest_1 t2 b SELECT NO 'mysqltest_3'@'localhost' NULL mysqltest_2 t2 d UPDATE NO SELECT * FROM INFORMATION_SCHEMA.TABLE_PRIVILEGES -WHERE GRANTEE = '''mysqltest_3''@''localhost''' -ORDER BY TABLE_NAME,PRIVILEGE_TYPE; +WHERE GRANTEE = '''mysqltest_3''@''localhost''' + ORDER BY TABLE_NAME,PRIVILEGE_TYPE; GRANTEE TABLE_CATALOG TABLE_SCHEMA TABLE_NAME PRIVILEGE_TYPE IS_GRANTABLE SELECT * from INFORMATION_SCHEMA.SCHEMA_PRIVILEGES -WHERE GRANTEE = '''mysqltest_3''@''localhost''' -ORDER BY TABLE_SCHEMA,PRIVILEGE_TYPE; +WHERE GRANTEE = '''mysqltest_3''@''localhost''' + ORDER BY TABLE_SCHEMA,PRIVILEGE_TYPE; GRANTEE TABLE_CATALOG TABLE_SCHEMA PRIVILEGE_TYPE IS_GRANTABLE SELECT * from INFORMATION_SCHEMA.USER_PRIVILEGES WHERE GRANTEE = '''mysqltest_3''@''localhost''' @@ -884,11 +884,11 @@ flush privileges; drop table t2; drop table t1; CREATE DATABASE mysqltest3; -use mysqltest3; +USE mysqltest3; CREATE TABLE t_nn (c1 INT); CREATE VIEW v_nn AS SELECT * FROM t_nn; CREATE DATABASE mysqltest2; -use mysqltest2; +USE mysqltest2; CREATE TABLE t_nn (c1 INT); CREATE VIEW v_nn AS SELECT * FROM t_nn; CREATE VIEW v_yn AS SELECT * FROM t_nn; @@ -958,7 +958,7 @@ DROP TABLE mysqltest3.t_nn; DROP DATABASE mysqltest3; REVOKE ALL PRIVILEGES, GRANT OPTION FROM 'mysqltest_1'@'localhost'; DROP USER 'mysqltest_1'@'localhost'; -use test; +USE test; create user mysqltest1_thisisreallytoolong; ERROR HY000: String 'mysqltest1_thisisreallytoolong' is too long for user name (should be no longer than 16) CREATE DATABASE mysqltest1; @@ -1196,16 +1196,16 @@ DROP DATABASE mysqltest1; DROP DATABASE mysqltest2; DROP USER mysqltest_1@localhost; DROP USER mysqltest_2@localhost; -use test; +USE test; CREATE TABLE t1 (f1 int, f2 int); INSERT INTO t1 VALUES(1,1), (2,2); CREATE DATABASE db27878; GRANT UPDATE(f1) ON t1 TO 'mysqltest_1'@'localhost'; GRANT SELECT ON `test`.* TO 'mysqltest_1'@'localhost'; GRANT ALL ON db27878.* TO 'mysqltest_1'@'localhost'; -use db27878; +USE db27878; CREATE SQL SECURITY INVOKER VIEW db27878.v1 AS SELECT * FROM test.t1; -use db27878; +USE db27878; UPDATE v1 SET f2 = 4; ERROR HY000: View 'db27878.v1' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them SELECT * FROM test.t1; @@ -1217,7 +1217,7 @@ REVOKE SELECT ON `test`.* FROM 'mysqltest_1'@'localhost'; REVOKE ALL ON db27878.* FROM 'mysqltest_1'@'localhost'; DROP USER mysqltest_1@localhost; DROP DATABASE db27878; -use test; +USE test; DROP TABLE t1; # # Bug#33275 Server crash when creating temporary table mysql.user @@ -1355,6 +1355,6 @@ Warnings: Warning 1403 There is no such grant defined for user 'userbug33464' on host 'localhost' on routine 'fn2' DROP PROCEDURE sp3; DROP USER 'userbug33464'@'localhost'; -use test; +USE test; DROP DATABASE dbbug33464; SET @@global.log_bin_trust_function_creators= @old_log_bin_trust_function_creators; diff --git a/mysql-test/r/information_schema.result b/mysql-test/r/information_schema.result index f8deef15819..5dd0f159428 100644 --- a/mysql-test/r/information_schema.result +++ b/mysql-test/r/information_schema.result @@ -99,7 +99,7 @@ t2 t3 t5 v1 -select c,table_name from v1 +select c,table_name from v1 inner join information_schema.TABLES v2 on (v1.c=v2.table_name) where v1.c like "t%"; c table_name @@ -118,7 +118,7 @@ t4 t4 t2 t2 t3 t3 t5 t5 -select c,table_name from v1 +select c,table_name from v1 left join information_schema.TABLES v2 on (v1.c=v2.table_name) where v1.c like "t%"; c table_name @@ -197,7 +197,7 @@ a int(11) YES NULL create view mysqltest.v1 (c) as select a from mysqltest.t1; grant select (a) on mysqltest.t1 to mysqltest_2@localhost; grant select on mysqltest.v1 to mysqltest_3; -select table_name, column_name, privileges from information_schema.columns +select table_name, column_name, privileges from information_schema.columns where table_schema = 'mysqltest' and table_name = 't1'; table_name column_name privileges t1 a select @@ -275,7 +275,7 @@ begin select * from t1; select * from t2; end| -select parameter_style, sql_data_access, dtd_identifier +select parameter_style, sql_data_access, dtd_identifier from information_schema.routines where routine_schema='test'; parameter_style sql_data_access dtd_identifier SQL CONTAINS SQL NULL @@ -560,7 +560,7 @@ drop view v1; create table t1(a NUMERIC(5,3), b NUMERIC(5,1), c float(5,2), d NUMERIC(6,4), e float, f DECIMAL(6,3), g int(11), h DOUBLE(10,3), i DOUBLE); -select COLUMN_NAME,COLUMN_TYPE, CHARACTER_MAXIMUM_LENGTH, +select COLUMN_NAME,COLUMN_TYPE, CHARACTER_MAXIMUM_LENGTH, CHARACTER_OCTET_LENGTH, NUMERIC_PRECISION, NUMERIC_SCALE from information_schema.columns where table_name= 't1'; COLUMN_NAME COLUMN_TYPE CHARACTER_MAXIMUM_LENGTH CHARACTER_OCTET_LENGTH NUMERIC_PRECISION NUMERIC_SCALE @@ -621,7 +621,7 @@ TABLE_NAME= "vo"; CONSTRAINT_CATALOG CONSTRAINT_SCHEMA CONSTRAINT_NAME TABLE_CATALOG TABLE_SCHEMA TABLE_NAME COLUMN_NAME ORDINAL_POSITION POSITION_IN_UNIQUE_CONSTRAINT REFERENCED_TABLE_SCHEMA REFERENCED_TABLE_NAME REFERENCED_COLUMN_NAME drop view vo; select TABLE_NAME,TABLE_TYPE,ENGINE -from information_schema.tables +from information_schema.tables where table_schema='information_schema' limit 2; TABLE_NAME TABLE_TYPE ENGINE CHARACTER_SETS SYSTEM VIEW MEMORY @@ -726,7 +726,7 @@ where table_schema="information_schema" and table_name="COLUMNS" and column_type varchar(32) varchar(32) -select TABLE_ROWS from information_schema.tables where +select TABLE_ROWS from information_schema.tables where table_schema="information_schema" and table_name="COLUMNS"; TABLE_ROWS NULL @@ -760,7 +760,7 @@ count(*) drop view a2, a1; drop table t_crashme; select table_schema,table_name, column_name from -information_schema.columns +information_schema.columns where data_type = 'longtext'; table_schema table_name column_name information_schema COLUMNS COLUMN_DEFAULT @@ -804,7 +804,7 @@ event last_executed datetime event starts datetime event ends datetime SELECT COUNT(*) FROM INFORMATION_SCHEMA.TABLES A -WHERE NOT EXISTS +WHERE NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS B WHERE A.TABLE_SCHEMA = B.TABLE_SCHEMA AND A.TABLE_NAME = B.TABLE_NAME); @@ -833,7 +833,7 @@ x_float NULL NULL x_double_precision NULL NULL drop table t1; grant select on test.* to mysqltest_4@localhost; -SELECT TABLE_NAME, COLUMN_NAME, PRIVILEGES FROM INFORMATION_SCHEMA.COLUMNS +SELECT TABLE_NAME, COLUMN_NAME, PRIVILEGES FROM INFORMATION_SCHEMA.COLUMNS where COLUMN_NAME='TABLE_NAME'; TABLE_NAME COLUMN_NAME PRIVILEGES COLUMNS TABLE_NAME select @@ -1079,7 +1079,7 @@ BEGIN SELECT 'foo' FROM DUAL; END | ERROR 42000: Unknown database 'information_schema' -select ROUTINE_NAME from routines where ROUTINE_SCHEMA='information_schema'; +select ROUTINE_NAME from routines where ROUTINE_SCHEMA='information_schema'; ROUTINE_NAME grant all on information_schema.* to 'user1'@'localhost'; ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema' @@ -1634,7 +1634,7 @@ show events where Db= 'information_schema'; Db Name Definer Time zone Type Execute at Interval value Interval field Starts Ends Status Originator character_set_client collation_connection Database Collation use test; # -# Bug#34166: Server crash in SHOW OPEN TABLES and prelocking +# Bug#34166 Server crash in SHOW OPEN TABLES and prelocking # drop table if exists t1; drop function if exists f1; diff --git a/mysql-test/r/multi_update.result b/mysql-test/r/multi_update.result index b8f6aa9e4d4..449333a4ae6 100644 --- a/mysql-test/r/multi_update.result +++ b/mysql-test/r/multi_update.result @@ -375,7 +375,7 @@ update t2, t1 set t2.field=t1.field where t1.id1=t2.id2 and 0=1; delete t1, t2 from t2 inner join t1 on t1.id1=t2.id2 where 0=1; -delete t1, t2 from t2,t1 +delete t1, t2 from t2,t1 where t1.id1=t2.id2 and 0=1; drop table t1,t2; CREATE TABLE t1 ( a int ); @@ -443,12 +443,12 @@ delete t1 from t1,t2 where t1.col1 < (select max(col1) from t1) and t1.col1 = t2 ERROR HY000: You can't specify target table 't1' for update in FROM clause drop table t1,t2; create table t1 ( -aclid bigint not null primary key, -status tinyint(1) not null +aclid bigint not null primary key, +status tinyint(1) not null ) engine = innodb; create table t2 ( -refid bigint not null primary key, -aclid bigint, index idx_acl(aclid) +refid bigint not null primary key, +aclid bigint, index idx_acl(aclid) ) engine = innodb; insert into t2 values(1,null); delete t2, t1 from t2 left join t1 on (t2.aclid=t1.aclid) where t2.refid='1'; diff --git a/mysql-test/r/packet.result b/mysql-test/r/packet.result index fa2fb33d278..ecbb47d4ee0 100644 --- a/mysql-test/r/packet.result +++ b/mysql-test/r/packet.result @@ -1,3 +1,5 @@ +set @max_allowed_packet=@@global.max_allowed_packet; +set @net_buffer_length=@@global.net_buffer_length; set global max_allowed_packet=100; Warnings: Warning 1292 Truncated incorrect max_allowed_packet value: '100' @@ -23,3 +25,5 @@ len select length(repeat('a',2000)); length(repeat('a',2000)) 2000 +set global max_allowed_packet=@max_allowed_packet; +set global net_buffer_length=@net_buffer_length; diff --git a/mysql-test/r/query_cache_notembedded.result b/mysql-test/r/query_cache_notembedded.result index 64bb40f1d17..d9bf7a6d814 100644 --- a/mysql-test/r/query_cache_notembedded.result +++ b/mysql-test/r/query_cache_notembedded.result @@ -375,6 +375,7 @@ id show status like 'Qcache_queries_in_cache'; Variable_name Value Qcache_queries_in_cache 1 +USE test; DROP DATABASE bug30269; DROP USER 'bug30269'@'localhost'; set GLOBAL query_cache_type=default; diff --git a/mysql-test/r/subselect.result b/mysql-test/r/subselect.result index aff26454b2d..7f6ebb14c3e 100644 --- a/mysql-test/r/subselect.result +++ b/mysql-test/r/subselect.result @@ -192,11 +192,11 @@ select (select a from t3 where a<t2.a*4 order by 1 desc limit 1), a from t2; (select a from t3 where a<t2.a*4 order by 1 desc limit 1) a 3 1 7 2 -select (select t3.a from t3 where a<8 order by 1 desc limit 1), a from +select (select t3.a from t3 where a<8 order by 1 desc limit 1), a from (select * from t2 where a>1) as tt; (select t3.a from t3 where a<8 order by 1 desc limit 1) a 7 2 -explain extended select (select t3.a from t3 where a<8 order by 1 desc limit 1), a from +explain extended select (select t3.a from t3 where a<8 order by 1 desc limit 1), a from (select * from t2 where a>1) as tt; id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY <derived3> system NULL NULL NULL NULL 1 100.00 @@ -2303,20 +2303,20 @@ drop table t1,t2; CREATE TABLE t1 ( a int, b int ); CREATE TABLE t2 ( c int, d int ); INSERT INTO t1 VALUES (1,2), (2,3), (3,4); -SELECT a AS abc, b FROM t1 outr WHERE b = +SELECT a AS abc, b FROM t1 outr WHERE b = (SELECT MIN(b) FROM t1 WHERE a=outr.a); abc b 1 2 2 3 3 4 -INSERT INTO t2 SELECT a AS abc, b FROM t1 outr WHERE b = +INSERT INTO t2 SELECT a AS abc, b FROM t1 outr WHERE b = (SELECT MIN(b) FROM t1 WHERE a=outr.a); select * from t2; c d 1 2 2 3 3 4 -CREATE TABLE t3 SELECT a AS abc, b FROM t1 outr WHERE b = +CREATE TABLE t3 SELECT a AS abc, b FROM t1 outr WHERE b = (SELECT MIN(b) FROM t1 WHERE a=outr.a); select * from t3; abc b @@ -2517,8 +2517,8 @@ INSERT INTO t1 VALUES ('ASM','American Samoa','Oceania','Polynesia',199.00,0,680 INSERT INTO t1 VALUES ('ATF','French Southern territories','Antarctica','Antarctica',7780.00,0,0,NULL,0.00,NULL,'Terres australes françaises','Nonmetropolitan Territory of France','Jacques Chirac',NULL,'TF'); INSERT INTO t1 VALUES ('UMI','United States Minor Outlying Islands','Oceania','Micronesia/Caribbean',16.00,0,0,NULL,0.00,NULL,'United States Minor Outlying Islands','Dependent Territory of the US','George W. Bush',NULL,'UM'); /*!40000 ALTER TABLE t1 ENABLE KEYS */; -SELECT DISTINCT Continent AS c FROM t1 outr WHERE -Code <> SOME ( SELECT Code FROM t1 WHERE Continent = outr.Continent AND +SELECT DISTINCT Continent AS c FROM t1 outr WHERE +Code <> SOME ( SELECT Code FROM t1 WHERE Continent = outr.Continent AND Population < 200); c Oceania @@ -2628,32 +2628,32 @@ select count(distinct t2.userid) pass, groupstuff.*, count(t2.courseid) crse, -t1.categoryid, +t1.categoryid, t2.courseid, date_format(date, '%b%y') as colhead -from t2 -join t1 on t2.courseid=t1.courseid +from t2 +join t1 on t2.courseid=t1.courseid join ( -select -t5.userid, -parentid, -parentgroup, -childid, -groupname, -grouptypeid -from t5 -join +select +t5.userid, +parentid, +parentgroup, +childid, +groupname, +grouptypeid +from t5 +join ( -select t4.id as parentid, -t4.name as parentgroup, -t4.id as childid, -t4.name as groupname, -t4.grouptypeid -from t4 -) as gin on t5.groupid=gin.childid -) as groupstuff on t2.userid = groupstuff.userid -group by +select t4.id as parentid, +t4.name as parentgroup, +t4.id as childid, +t4.name as groupname, +t4.grouptypeid +from t4 +) as gin on t5.groupid=gin.childid +) as groupstuff on t2.userid = groupstuff.userid +group by groupstuff.groupname, colhead , t2.courseid; pass userid parentid parentgroup childid groupname grouptypeid crse categoryid courseid colhead 1 5141 12 group2 12 group2 5 1 5 12 Aug04 @@ -2929,9 +2929,9 @@ INSERT INTO t1 VALUES("0037", "1", "2005-12-06 12:18:56"); INSERT INTO t1 VALUES("0037", "2", "2006-01-06 12:25:53"); INSERT INTO t1 VALUES("0048", "1", "2006-01-06 12:37:50"); INSERT INTO t1 VALUES("0059", "1", "2006-01-06 12:37:50"); -select * from t1 r1 -where (r1.retailerID,(r1.changed)) in -(SELECT r2.retailerId,(max(changed)) from t1 r2 +select * from t1 r1 +where (r1.retailerID,(r1.changed)) in +(SELECT r2.retailerId,(max(changed)) from t1 r2 group by r2.retailerId); retailerID statusID changed 0026 2 2006-01-06 12:25:53 @@ -2943,41 +2943,41 @@ create table t1(a int, primary key (a)); insert into t1 values (10); create table t2 (a int primary key, b varchar(32), c int, unique key b(c, b)); insert into t2(a, c, b) values (1,10,'359'), (2,10,'35988'), (3,10,'35989'); -explain SELECT sql_no_cache t1.a, r.a, r.b FROM t1 LEFT JOIN t2 r -ON r.a = (SELECT t2.a FROM t2 WHERE t2.c = t1.a AND t2.b <= '359899' -ORDER BY t2.c DESC, t2.b DESC LIMIT 1) WHERE t1.a = 10; +explain SELECT sql_no_cache t1.a, r.a, r.b FROM t1 LEFT JOIN t2 r +ON r.a = (SELECT t2.a FROM t2 WHERE t2.c = t1.a AND t2.b <= '359899' + ORDER BY t2.c DESC, t2.b DESC LIMIT 1) WHERE t1.a = 10; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 system PRIMARY NULL NULL NULL 1 1 PRIMARY r const PRIMARY PRIMARY 4 const 1 2 DEPENDENT SUBQUERY t2 range b b 40 NULL 2 Using where -SELECT sql_no_cache t1.a, r.a, r.b FROM t1 LEFT JOIN t2 r -ON r.a = (SELECT t2.a FROM t2 WHERE t2.c = t1.a AND t2.b <= '359899' -ORDER BY t2.c DESC, t2.b DESC LIMIT 1) WHERE t1.a = 10; +SELECT sql_no_cache t1.a, r.a, r.b FROM t1 LEFT JOIN t2 r +ON r.a = (SELECT t2.a FROM t2 WHERE t2.c = t1.a AND t2.b <= '359899' + ORDER BY t2.c DESC, t2.b DESC LIMIT 1) WHERE t1.a = 10; a a b 10 3 35989 -explain SELECT sql_no_cache t1.a, r.a, r.b FROM t1 LEFT JOIN t2 r -ON r.a = (SELECT t2.a FROM t2 WHERE t2.c = t1.a AND t2.b <= '359899' -ORDER BY t2.c, t2.b LIMIT 1) WHERE t1.a = 10; +explain SELECT sql_no_cache t1.a, r.a, r.b FROM t1 LEFT JOIN t2 r +ON r.a = (SELECT t2.a FROM t2 WHERE t2.c = t1.a AND t2.b <= '359899' + ORDER BY t2.c, t2.b LIMIT 1) WHERE t1.a = 10; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 system PRIMARY NULL NULL NULL 1 1 PRIMARY r const PRIMARY PRIMARY 4 const 1 2 DEPENDENT SUBQUERY t2 range b b 40 NULL 2 Using where -SELECT sql_no_cache t1.a, r.a, r.b FROM t1 LEFT JOIN t2 r -ON r.a = (SELECT t2.a FROM t2 WHERE t2.c = t1.a AND t2.b <= '359899' -ORDER BY t2.c, t2.b LIMIT 1) WHERE t1.a = 10; +SELECT sql_no_cache t1.a, r.a, r.b FROM t1 LEFT JOIN t2 r +ON r.a = (SELECT t2.a FROM t2 WHERE t2.c = t1.a AND t2.b <= '359899' + ORDER BY t2.c, t2.b LIMIT 1) WHERE t1.a = 10; a a b 10 1 359 drop table t1,t2; -CREATE TABLE t1 ( -field1 int NOT NULL, -field2 int NOT NULL, -field3 int NOT NULL, -PRIMARY KEY (field1,field2,field3) +CREATE TABLE t1 ( +field1 int NOT NULL, +field2 int NOT NULL, +field3 int NOT NULL, +PRIMARY KEY (field1,field2,field3) ); -CREATE TABLE t2 ( -fieldA int NOT NULL, -fieldB int NOT NULL, -PRIMARY KEY (fieldA,fieldB) +CREATE TABLE t2 ( +fieldA int NOT NULL, +fieldB int NOT NULL, +PRIMARY KEY (fieldA,fieldB) ); INSERT INTO t1 VALUES (1,1,1), (1,1,2), (1,2,1), (1,2,2), (1,2,3), (1,3,1); @@ -2991,14 +2991,14 @@ field1 field2 COUNT(*) SELECT field1, field2 FROM t1 GROUP BY field1, field2 -HAVING COUNT(*) >= ALL (SELECT fieldB +HAVING COUNT(*) >= ALL (SELECT fieldB FROM t2 WHERE fieldA = field1); field1 field2 1 2 SELECT field1, field2 FROM t1 GROUP BY field1, field2 -HAVING COUNT(*) < ANY (SELECT fieldB +HAVING COUNT(*) < ANY (SELECT fieldB FROM t2 WHERE fieldA = field1); field1 field2 1 1 @@ -3021,8 +3021,8 @@ a a IN (SELECT a FROM t1) DROP TABLE t1,t2; CREATE TABLE t1 (a DATETIME); INSERT INTO t1 VALUES ('1998-09-23'), ('2003-03-25'); -CREATE TABLE t2 AS SELECT -(SELECT a FROM t1 WHERE a < '2000-01-01') AS sub_a +CREATE TABLE t2 AS SELECT +(SELECT a FROM t1 WHERE a < '2000-01-01') AS sub_a FROM t1 WHERE a > '2000-01-01'; SHOW CREATE TABLE t2; Table Create Table @@ -3188,7 +3188,7 @@ INSERT INTO t2 VALUES ( 6 ); CREATE TABLE t3 ( c3 integer ); INSERT INTO t3 VALUES ( 7 ); INSERT INTO t3 VALUES ( 8 ); -SELECT c1,c2 FROM t1 LEFT JOIN t2 ON c1 = c2 +SELECT c1,c2 FROM t1 LEFT JOIN t2 ON c1 = c2 WHERE EXISTS (SELECT c3 FROM t3 WHERE c2 IS NULL ); c1 c2 2 NULL @@ -3231,20 +3231,20 @@ E1 DROP TABLE t1,t2; CREATE TABLE t1(select_id BIGINT, values_id BIGINT); INSERT INTO t1 VALUES (1, 1); -CREATE TABLE t2 (select_id BIGINT, values_id BIGINT, +CREATE TABLE t2 (select_id BIGINT, values_id BIGINT, PRIMARY KEY(select_id,values_id)); INSERT INTO t2 VALUES (0, 1), (0, 2), (0, 3), (1, 5); -SELECT values_id FROM t1 +SELECT values_id FROM t1 WHERE values_id IN (SELECT values_id FROM t2 WHERE select_id IN (1, 0)); values_id 1 -SELECT values_id FROM t1 +SELECT values_id FROM t1 WHERE values_id IN (SELECT values_id FROM t2 WHERE select_id BETWEEN 0 AND 1); values_id 1 -SELECT values_id FROM t1 +SELECT values_id FROM t1 WHERE values_id IN (SELECT values_id FROM t2 WHERE select_id = 0 OR select_id = 1); values_id @@ -3259,7 +3259,7 @@ drop table t1; CREATE TABLE t1 (a int, b int); CREATE TABLE t2 (c int, d int); CREATE TABLE t3 (e int); -INSERT INTO t1 VALUES +INSERT INTO t1 VALUES (1,10), (2,10), (1,20), (2,20), (3,20), (2,30), (4,40); INSERT INTO t2 VALUES (2,10), (2,20), (4,10), (5,10), (3,20), (2,40); @@ -3322,7 +3322,7 @@ a 2 SELECT a FROM t1 GROUP BY a HAVING a IN (SELECT c FROM t2 -WHERE MIN(b) < d AND +WHERE MIN(b) < d AND EXISTS(SELECT e FROM t3 WHERE MAX(b)=e AND e <= d)); a 2 @@ -3373,7 +3373,7 @@ a 4 SELECT t1.a FROM t1 GROUP BY t1.a HAVING t1.a > ALL(SELECT t2.c FROM t2 -WHERE EXISTS(SELECT t3.e FROM t3 +WHERE EXISTS(SELECT t3.e FROM t3 WHERE SUM(t1.a+t2.c) < t3.e/4)); ERROR HY000: Invalid use of group function SELECT t1.a from t1 GROUP BY t1.a HAVING AVG(SUM(t1.b)) > 20; @@ -3486,7 +3486,7 @@ mid bigint(20) unsigned NOT NULL, date date NOT NULL, PRIMARY KEY (id) ); -INSERT INTO t2 VALUES +INSERT INTO t2 VALUES (1, 1, '2006-03-30'), (2, 2, '2006-04-06'), (3, 3, '2006-04-13'), (4, 2, '2006-04-20'), (5, 1, '2006-05-01'); SELECT *, @@ -3524,7 +3524,7 @@ i2 int(11) NOT NULL default '0', t datetime NOT NULL default '0000-00-00 00:00:00', PRIMARY KEY (i1,i2,t) ); -INSERT INTO t1 VALUES +INSERT INTO t1 VALUES (24,1,'2005-03-03 16:31:31'),(24,1,'2005-05-27 12:40:07'), (24,1,'2005-05-27 12:40:08'),(24,1,'2005-05-27 12:40:10'), (24,1,'2005-05-27 12:40:25'),(24,1,'2005-05-27 12:40:30'), @@ -3540,7 +3540,7 @@ PRIMARY KEY (i1) INSERT INTO t2 VALUES (24,1,'2006-06-20 12:29:40'); EXPLAIN SELECT * FROM t1,t2 -WHERE t1.t = (SELECT t1.t FROM t1 +WHERE t1.t = (SELECT t1.t FROM t1 WHERE t1.t < t2.t AND t1.i2=1 AND t2.i1=t1.i1 ORDER BY t1.t DESC LIMIT 1); id select_type table type possible_keys key key_len ref rows Extra @@ -3548,7 +3548,7 @@ id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 index NULL PRIMARY 16 NULL 11 Using where; Using index 2 DEPENDENT SUBQUERY t1 range PRIMARY PRIMARY 16 NULL 5 Using where; Using index SELECT * FROM t1,t2 -WHERE t1.t = (SELECT t1.t FROM t1 +WHERE t1.t = (SELECT t1.t FROM t1 WHERE t1.t < t2.t AND t1.i2=1 AND t2.i1=t1.i1 ORDER BY t1.t DESC LIMIT 1); i1 i2 t i1 i2 t @@ -3557,22 +3557,22 @@ DROP TABLE t1, t2; CREATE TABLE t1 (i INT); (SELECT i FROM t1) UNION (SELECT i FROM t1); i -SELECT sql_no_cache * FROM t1 WHERE NOT EXISTS +SELECT sql_no_cache * FROM t1 WHERE NOT EXISTS ( -(SELECT i FROM t1) UNION +(SELECT i FROM t1) UNION (SELECT i FROM t1) ); -ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'UNION +ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'UNION (SELECT i FROM t1) )' at line 3 -SELECT * FROM t1 +SELECT * FROM t1 WHERE NOT EXISTS (((SELECT i FROM t1) UNION (SELECT i FROM t1))); ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'UNION (SELECT i FROM t1)))' at line 2 explain select ((select t11.i from t1 t11) union (select t12.i from t1 t12)) from t1; ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'union (select t12.i from t1 t12)) from t1' at line 1 -explain select * from t1 where not exists +explain select * from t1 where not exists ((select t11.i from t1 t11) union (select t12.i from t1 t12)); ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'union (select t12.i from t1 t12))' at line 2 DROP TABLE t1; @@ -3591,9 +3591,9 @@ insert into t1 (a) select FLOOR(rand() * 100) from t1; insert into t1 (a) select FLOOR(rand() * 100) from t1; insert into t1 (a) select FLOOR(rand() * 100) from t1; insert into t1 (a) select FLOOR(rand() * 100) from t1; -SELECT a, -(SELECT REPEAT(' ',250) FROM t1 i1 -WHERE i1.b=t1.a ORDER BY RAND() LIMIT 1) AS a +SELECT a, +(SELECT REPEAT(' ',250) FROM t1 i1 +WHERE i1.b=t1.a ORDER BY RAND() LIMIT 1) AS a FROM t1 ORDER BY a LIMIT 5; a a 0 NULL @@ -3622,7 +3622,7 @@ COUNT(DISTINCT t1.b) (SELECT COUNT(DISTINCT t1.b)) 2 2 1 1 1 1 -SELECT COUNT(DISTINCT t1.b), +SELECT COUNT(DISTINCT t1.b), (SELECT COUNT(DISTINCT t1.b) union select 1 from DUAL where 12 < 3) FROM t1 GROUP BY t1.a; COUNT(DISTINCT t1.b) (SELECT COUNT(DISTINCT t1.b) union select 1 from DUAL where 12 < 3) @@ -3633,7 +3633,7 @@ SELECT ( SELECT ( SELECT COUNT(DISTINCT t1.b) ) -) +) FROM t1 GROUP BY t1.a; ( SELECT ( @@ -3648,8 +3648,8 @@ SELECT ( SELECT ( SELECT COUNT(DISTINCT t1.b) ) -) -FROM t1 GROUP BY t1.a LIMIT 1) +) +FROM t1 GROUP BY t1.a LIMIT 1) FROM t1 t2 GROUP BY t2.a; ( @@ -3657,7 +3657,7 @@ SELECT ( SELECT ( SELECT COUNT(DISTINCT t1.b) ) -) +) FROM t1 GROUP BY t1.a LIMIT 1) 2 2 @@ -3669,13 +3669,13 @@ PRIMARY KEY (x), FOREIGN KEY (y) REFERENCES t1 (b)); SET SESSION sort_buffer_size = 32 * 1024; Warnings: Warning 1292 Truncated incorrect sort_buffer_size value: '32768' -SELECT SQL_NO_CACHE COUNT(*) +SELECT SQL_NO_CACHE COUNT(*) FROM (SELECT a, b, (SELECT x FROM t2 WHERE y=b ORDER BY z DESC LIMIT 1) c FROM t1) t; COUNT(*) 3000 SET SESSION sort_buffer_size = 8 * 1024 * 1024; -SELECT SQL_NO_CACHE COUNT(*) +SELECT SQL_NO_CACHE COUNT(*) FROM (SELECT a, b, (SELECT x FROM t2 WHERE y=b ORDER BY z DESC LIMIT 1) c FROM t1) t; COUNT(*) @@ -3736,7 +3736,7 @@ sq 2 4 DEALLOCATE PREPARE stmt1; -SELECT f2, AVG(f21), +SELECT f2, AVG(f21), (SELECT t.f3 FROM t2 AS t WHERE t2.f2=t.f2 AND t.f3=MAX(t2.f3)) AS test FROM t2 GROUP BY f2; f2 AVG(f21) test @@ -3744,12 +3744,12 @@ f2 AVG(f21) test 2 2.0000 2004-02-29 11:11:11 DROP TABLE t1,t2; CREATE TABLE t1 (a int, b INT, c CHAR(10) NOT NULL); -INSERT INTO t1 VALUES -(1,1,'a'), (1,2,'b'), (1,3,'c'), (1,4,'d'), (1,5,'e'), -(2,1,'f'), (2,2,'g'), (2,3,'h'), (3,4,'i'), (3,3,'j'), +INSERT INTO t1 VALUES +(1,1,'a'), (1,2,'b'), (1,3,'c'), (1,4,'d'), (1,5,'e'), +(2,1,'f'), (2,2,'g'), (2,3,'h'), (3,4,'i'), (3,3,'j'), (3,2,'k'), (3,1,'l'), (1,9,'m'); -SELECT a, MAX(b), -(SELECT t.c FROM t1 AS t WHERE t1.a=t.a AND t.b=MAX(t1.b)) AS test +SELECT a, MAX(b), +(SELECT t.c FROM t1 AS t WHERE t1.a=t.a AND t.b=MAX(t1.b)) AS test FROM t1 GROUP BY a; a MAX(b) test 1 9 m @@ -3900,7 +3900,7 @@ COUNT(*) a (SELECT MIN(m) FROM t2 WHERE m = count(*)) 2 2 2 3 3 3 1 4 1 -SELECT COUNT(*), a +SELECT COUNT(*), a FROM t1 GROUP BY a HAVING (SELECT MIN(m) FROM t2 WHERE m = count(*)) > 1; COUNT(*) a @@ -3931,7 +3931,7 @@ INSERT INTO t1 VALUES (1,1,0,'a'), (1,2,0,'b'), (1,3,0,'c'), (1,4,0,'d'), (1,5,0,'e'), (2,1,0,'f'), (2,2,0,'g'), (2,3,0,'h'), (3,4,0,'i'), (3,3,0,'j'), (3,2,0,'k'), (3,1,0,'l'), (1,9,0,'m'), (1,0,10,'n'), (2,0,5,'o'), (3,0,7,'p'); SELECT a, MAX(b), -(SELECT t.c FROM t1 AS t WHERE t1.a=t.a AND t.b=MAX(t1.b + 0)) as test +(SELECT t.c FROM t1 AS t WHERE t1.a=t.a AND t.b=MAX(t1.b + 0)) as test FROM t1 GROUP BY a; a MAX(b) test 1 9 m @@ -3953,7 +3953,7 @@ a AVG(b) test 3 2.5000 NULL SELECT tt.a, (SELECT (SELECT c FROM t1 as t WHERE t1.a=t.a AND t.d=MAX(t1.b + tt.a) -LIMIT 1) FROM t1 WHERE t1.a=tt.a GROUP BY a LIMIT 1) as test +LIMIT 1) FROM t1 WHERE t1.a=tt.a GROUP BY a LIMIT 1) as test FROM t1 as tt; a test 1 n @@ -3975,7 +3975,7 @@ a test SELECT tt.a, (SELECT (SELECT t.c FROM t1 AS t WHERE t1.a=t.a AND t.d=MAX(t1.b + tt.a) LIMIT 1) -FROM t1 WHERE t1.a=tt.a GROUP BY a LIMIT 1) as test +FROM t1 WHERE t1.a=tt.a GROUP BY a LIMIT 1) as test FROM t1 as tt GROUP BY tt.a; a test 1 n @@ -3984,7 +3984,7 @@ a test SELECT tt.a, MAX( (SELECT (SELECT t.c FROM t1 AS t WHERE t1.a=t.a AND t.d=MAX(t1.b + tt.a) LIMIT 1) -FROM t1 WHERE t1.a=tt.a GROUP BY a LIMIT 1)) as test +FROM t1 WHERE t1.a=tt.a GROUP BY a LIMIT 1)) as test FROM t1 as tt GROUP BY tt.a; a test 1 n @@ -4027,11 +4027,11 @@ COUNT(1) 1 SELECT SUM( (SELECT AVG( (SELECT t1.a FROM t2) ) FROM DUAL) ) FROM t1; ERROR HY000: Invalid use of group function -SELECT +SELECT SUM( (SELECT AVG( (SELECT COUNT(*) FROM t1 t HAVING t1.a < 12) ) FROM t2) ) FROM t1; ERROR HY000: Invalid use of group function -SELECT t1.a as XXA, +SELECT t1.a as XXA, SUM( (SELECT AVG( (SELECT COUNT(*) FROM t1 t HAVING XXA < 12) ) FROM t2) ) FROM t1; ERROR HY000: Invalid use of group function @@ -4048,25 +4048,25 @@ INSERT INTO t1 VALUES (3,'FL'), (2,'GA'), (4,'FL'), (1,'GA'), (5,'NY'), (7,'FL'), (6,'NY'); CREATE TABLE t2 (id int NOT NULL, INDEX idx(id)); INSERT INTO t2 VALUES (7), (5), (1), (3); -SELECT id, st FROM t1 +SELECT id, st FROM t1 WHERE st IN ('GA','FL') AND EXISTS(SELECT 1 FROM t2 WHERE t2.id=t1.id); id st 3 FL 1 GA 7 FL -SELECT id, st FROM t1 +SELECT id, st FROM t1 WHERE st IN ('GA','FL') AND EXISTS(SELECT 1 FROM t2 WHERE t2.id=t1.id) GROUP BY id; id st 1 GA 3 FL 7 FL -SELECT id, st FROM t1 +SELECT id, st FROM t1 WHERE st IN ('GA','FL') AND NOT EXISTS(SELECT 1 FROM t2 WHERE t2.id=t1.id); id st 2 GA 4 FL -SELECT id, st FROM t1 +SELECT id, st FROM t1 WHERE st IN ('GA','FL') AND NOT EXISTS(SELECT 1 FROM t2 WHERE t2.id=t1.id) GROUP BY id; id st @@ -4237,7 +4237,7 @@ a b DROP TABLE t1,t2; CREATE TABLE t1(a INT, b INT); INSERT INTO t1 VALUES (1,1), (1,2), (2,3), (2,4); -EXPLAIN +EXPLAIN SELECT a AS out_a, MIN(b) FROM t1 WHERE b > (SELECT MIN(b) FROM t1 WHERE a = out_a) GROUP BY a; @@ -4246,7 +4246,7 @@ SELECT a AS out_a, MIN(b) FROM t1 WHERE b > (SELECT MIN(b) FROM t1 WHERE a = out_a) GROUP BY a; ERROR 42S22: Unknown column 'out_a' in 'where clause' -EXPLAIN +EXPLAIN SELECT a AS out_a, MIN(b) FROM t1 t1_outer WHERE b > (SELECT MIN(b) FROM t1 WHERE a = t1_outer.a) GROUP BY a; @@ -4277,16 +4277,16 @@ Warnings: Note 1276 Field or reference 'test.t1.a' of SELECT #2 was resolved in SELECT #1 Note 1003 select 2 AS `2` from `test`.`t1` where exists(select 1 AS `1` from `test`.`t2` where (`test`.`t1`.`a` = `test`.`t2`.`a`)) EXPLAIN EXTENDED -SELECT 2 FROM t1 WHERE EXISTS ((SELECT 1 FROM t2 WHERE t1.a=t2.a) UNION +SELECT 2 FROM t1 WHERE EXISTS ((SELECT 1 FROM t2 WHERE t1.a=t2.a) UNION (SELECT 1 FROM t2 WHERE t1.a = t2.a)); -ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'UNION +ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'UNION (SELECT 1 FROM t2 WHERE t1.a = t2.a))' at line 2 DROP TABLE t1,t2; create table t1(f11 int, f12 int); create table t2(f21 int unsigned not null, f22 int, f23 varchar(10)); insert into t1 values(1,1),(2,2), (3, 3); set session sort_buffer_size= 33*1024; -select count(*) from t1 where f12 = +select count(*) from t1 where f12 = (select f22 from t2 where f22 = f12 order by f21 desc, f22, f23 limit 1); count(*) 3 @@ -4327,12 +4327,12 @@ IF( FROM t2 VPC, t4 a2, t2 a3 WHERE VPC.f4 = a2.f10 AND a3.f2 = a4 -LIMIT 1) IS NULL, -0, +LIMIT 1) IS NULL, +0, t3.f5 ) ) AS a6 -FROM +FROM t2, t3, t1 JOIN t2 a1 ON t1.f9 = a1.f4 GROUP BY a4; a4 f3 a6 @@ -4341,7 +4341,7 @@ a4 f3 a6 DROP TABLE t1, t2, t3, t4; create table t1 (a float(5,4) zerofill); create table t2 (a float(5,4),b float(2,0)); -select t1.a from t1 where +select t1.a from t1 where t1.a= (select b from t2 limit 1) and not t1.a= (select a from t2 limit 1) ; a @@ -4362,7 +4362,7 @@ Warnings: Note 1003 select 1 AS `1` from `test`.`t1` where <in_optimizer>(1,<exists>(select 1 AS `1` from `test`.`t1` where (`test`.`t1`.`a` > 3) group by `test`.`t1`.`a` having (<cache>(1) = <ref_null_helper>(1)))) DROP TABLE t1; End of 5.0 tests. -CREATE TABLE t1 (a int, b int); +CREATE TABLE t1 (a INT, b INT); INSERT INTO t1 VALUES (2,22),(1,11),(2,22); SELECT a FROM t1 WHERE (SELECT COUNT(b) FROM DUAL) > 0 GROUP BY a; a @@ -4385,22 +4385,22 @@ WHERE (SELECT COUNT(t0.b) FROM t1 t WHERE t.b>20) GROUP BY a; ERROR HY000: Invalid use of group function SET @@sql_mode=default; DROP TABLE t1; -CREATE TABLE t1 (s1 char(1)); +CREATE TABLE t1 (s1 CHAR(1)); INSERT INTO t1 VALUES ('a'); SELECT * FROM t1 WHERE _utf8'a' = ANY (SELECT s1 FROM t1); s1 a DROP TABLE t1; -CREATE TABLE t1(c int, KEY(c)); -CREATE TABLE t2(a int, b int); +CREATE TABLE t1(c INT, KEY(c)); +CREATE TABLE t2(a INT, b INT); INSERT INTO t2 VALUES (1, 10), (2, NULL); INSERT INTO t1 VALUES (1), (3); SELECT * FROM t2 WHERE b NOT IN (SELECT max(t.c) FROM t1, t1 t WHERE t.c>10); a b DROP TABLE t1,t2; -CREATE TABLE t1(pk int PRIMARY KEY, a int, INDEX idx(a)); +CREATE TABLE t1(pk INT PRIMARY KEY, a INT, INDEX idx(a)); INSERT INTO t1 VALUES (1, 10), (3, 30), (2, 20); -CREATE TABLE t2(pk int PRIMARY KEY, a int, b int, INDEX idxa(a)); +CREATE TABLE t2(pk INT PRIMARY KEY, a INT, b INT, INDEX idxa(a)); INSERT INTO t2 VALUES (2, 20, 700), (1, 10, 200), (4, 10, 100); SELECT * FROM t1 WHERE EXISTS (SELECT DISTINCT a FROM t2 WHERE t1.a < t2.a ORDER BY b); @@ -4409,36 +4409,36 @@ pk a 3 30 2 20 DROP TABLE t1,t2; -CREATE TABLE t1 (a int, b int, PRIMARY KEY (a), KEY b (b)); +CREATE TABLE t1 (a INT, b INT, PRIMARY KEY (a), KEY b (b)); INSERT INTO t1 VALUES (1,NULL), (9,NULL); CREATE TABLE t2 ( -a int, -b int, -c int, -d int, +a INT, +b INT, +c INT, +d INT, PRIMARY KEY (a), UNIQUE KEY b (b,c,d), KEY b_2 (b), KEY c (c), KEY d (d) ); -INSERT INTO t2 VALUES +INSERT INTO t2 VALUES (43, 2, 11 ,30), (44, 2, 12 ,30), (45, 1, 1 ,10000), (46, 1, 2 ,10000), (556,1, 32 ,10000); CREATE TABLE t3 ( -a int, -b int, -c int, +a INT, +b INT, +c INT, PRIMARY KEY (a), UNIQUE KEY b (b,c), KEY c (c), KEY b_2 (b) ); INSERT INTO t3 VALUES (1,1,1), (2,32,1); -explain +explain SELECT t1.a, (SELECT 1 FROM t2 WHERE t2.b=t3.c AND t2.c=t1.a ORDER BY t2.d LIMIT 1) AS incorrect FROM t1, t3 WHERE t3.b=t1.a; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t3 index b,b_2 b 10 NULL 2 Using index @@ -4479,7 +4479,7 @@ DELETE FROM v3; DROP VIEW v1,v2,v3; DROP TABLE t1,t2; # -# BUG#37822: Correlated subquery with IN and IS UNKNOWN provides wrong result +# Bug#37822 Correlated subquery with IN and IS UNKNOWN provides wrong result # create table t1(id integer primary key, g integer, v integer, s char(1)); create table t2(id integer primary key, g integer, v integer, s char(1)); diff --git a/mysql-test/r/synchronization.result b/mysql-test/r/synchronization.result index 5d8585f1f88..3f6d16178dd 100644 --- a/mysql-test/r/synchronization.result +++ b/mysql-test/r/synchronization.result @@ -1,6 +1,6 @@ -drop table if exists t1,t2; -CREATE TABLE t1 (x1 int); -ALTER TABLE t1 CHANGE x1 x2 int; +DROP TABLE IF EXISTS t1,t2; +CREATE TABLE t1 (x1 INT); +ALTER TABLE t1 CHANGE x1 x2 INT; CREATE TABLE t2 LIKE t1; SHOW CREATE TABLE t2; Table Create Table @@ -8,7 +8,7 @@ t2 CREATE TABLE `t2` ( `xx` int(11) DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 DROP TABLE t2; -ALTER TABLE t1 CHANGE x2 x1 int; +ALTER TABLE t1 CHANGE x2 x1 INT; CREATE TABLE t2 LIKE t1; SHOW CREATE TABLE t2; Table Create Table @@ -16,7 +16,7 @@ t2 CREATE TABLE `t2` ( `xx` int(11) DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 DROP TABLE t2; -ALTER TABLE t1 CHANGE x1 x2 int; +ALTER TABLE t1 CHANGE x1 x2 INT; CREATE TABLE t2 LIKE t1; SHOW CREATE TABLE t2; Table Create Table @@ -24,7 +24,7 @@ t2 CREATE TABLE `t2` ( `xx` int(11) DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 DROP TABLE t2; -ALTER TABLE t1 CHANGE x2 x1 int; +ALTER TABLE t1 CHANGE x2 x1 INT; CREATE TABLE t2 LIKE t1; SHOW CREATE TABLE t2; Table Create Table @@ -32,7 +32,7 @@ t2 CREATE TABLE `t2` ( `xx` int(11) DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 DROP TABLE t2; -ALTER TABLE t1 CHANGE x1 x2 int; +ALTER TABLE t1 CHANGE x1 x2 INT; CREATE TABLE t2 LIKE t1; SHOW CREATE TABLE t2; Table Create Table @@ -40,7 +40,7 @@ t2 CREATE TABLE `t2` ( `xx` int(11) DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 DROP TABLE t2; -ALTER TABLE t1 CHANGE x2 x1 int; +ALTER TABLE t1 CHANGE x2 x1 INT; CREATE TABLE t2 LIKE t1; SHOW CREATE TABLE t2; Table Create Table @@ -48,7 +48,7 @@ t2 CREATE TABLE `t2` ( `xx` int(11) DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 DROP TABLE t2; -ALTER TABLE t1 CHANGE x1 x2 int; +ALTER TABLE t1 CHANGE x1 x2 INT; CREATE TABLE t2 LIKE t1; SHOW CREATE TABLE t2; Table Create Table @@ -56,7 +56,7 @@ t2 CREATE TABLE `t2` ( `xx` int(11) DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 DROP TABLE t2; -ALTER TABLE t1 CHANGE x2 x1 int; +ALTER TABLE t1 CHANGE x2 x1 INT; CREATE TABLE t2 LIKE t1; SHOW CREATE TABLE t2; Table Create Table @@ -64,7 +64,7 @@ t2 CREATE TABLE `t2` ( `xx` int(11) DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 DROP TABLE t2; -ALTER TABLE t1 CHANGE x1 x2 int; +ALTER TABLE t1 CHANGE x1 x2 INT; CREATE TABLE t2 LIKE t1; SHOW CREATE TABLE t2; Table Create Table @@ -72,7 +72,7 @@ t2 CREATE TABLE `t2` ( `xx` int(11) DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 DROP TABLE t2; -ALTER TABLE t1 CHANGE x2 x1 int; +ALTER TABLE t1 CHANGE x2 x1 INT; CREATE TABLE t2 LIKE t1; SHOW CREATE TABLE t2; Table Create Table @@ -80,7 +80,7 @@ t2 CREATE TABLE `t2` ( `xx` int(11) DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 DROP TABLE t2; -ALTER TABLE t1 CHANGE x1 x2 int; +ALTER TABLE t1 CHANGE x1 x2 INT; CREATE TABLE t2 LIKE t1; SHOW CREATE TABLE t2; Table Create Table @@ -88,7 +88,7 @@ t2 CREATE TABLE `t2` ( `xx` int(11) DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 DROP TABLE t2; -ALTER TABLE t1 CHANGE x2 x1 int; +ALTER TABLE t1 CHANGE x2 x1 INT; CREATE TABLE t2 LIKE t1; SHOW CREATE TABLE t2; Table Create Table @@ -96,7 +96,7 @@ t2 CREATE TABLE `t2` ( `xx` int(11) DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 DROP TABLE t2; -ALTER TABLE t1 CHANGE x1 x2 int; +ALTER TABLE t1 CHANGE x1 x2 INT; CREATE TABLE t2 LIKE t1; SHOW CREATE TABLE t2; Table Create Table @@ -104,7 +104,7 @@ t2 CREATE TABLE `t2` ( `xx` int(11) DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 DROP TABLE t2; -ALTER TABLE t1 CHANGE x2 x1 int; +ALTER TABLE t1 CHANGE x2 x1 INT; CREATE TABLE t2 LIKE t1; SHOW CREATE TABLE t2; Table Create Table @@ -112,7 +112,7 @@ t2 CREATE TABLE `t2` ( `xx` int(11) DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 DROP TABLE t2; -ALTER TABLE t1 CHANGE x1 x2 int; +ALTER TABLE t1 CHANGE x1 x2 INT; CREATE TABLE t2 LIKE t1; SHOW CREATE TABLE t2; Table Create Table @@ -120,7 +120,7 @@ t2 CREATE TABLE `t2` ( `xx` int(11) DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 DROP TABLE t2; -ALTER TABLE t1 CHANGE x2 x1 int; +ALTER TABLE t1 CHANGE x2 x1 INT; CREATE TABLE t2 LIKE t1; SHOW CREATE TABLE t2; Table Create Table @@ -128,7 +128,7 @@ t2 CREATE TABLE `t2` ( `xx` int(11) DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 DROP TABLE t2; -ALTER TABLE t1 CHANGE x1 x2 int; +ALTER TABLE t1 CHANGE x1 x2 INT; CREATE TABLE t2 LIKE t1; SHOW CREATE TABLE t2; Table Create Table @@ -136,7 +136,7 @@ t2 CREATE TABLE `t2` ( `xx` int(11) DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 DROP TABLE t2; -ALTER TABLE t1 CHANGE x2 x1 int; +ALTER TABLE t1 CHANGE x2 x1 INT; CREATE TABLE t2 LIKE t1; SHOW CREATE TABLE t2; Table Create Table @@ -144,7 +144,7 @@ t2 CREATE TABLE `t2` ( `xx` int(11) DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 DROP TABLE t2; -ALTER TABLE t1 CHANGE x1 x2 int; +ALTER TABLE t1 CHANGE x1 x2 INT; CREATE TABLE t2 LIKE t1; SHOW CREATE TABLE t2; Table Create Table @@ -152,7 +152,7 @@ t2 CREATE TABLE `t2` ( `xx` int(11) DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 DROP TABLE t2; -ALTER TABLE t1 CHANGE x2 x1 int; +ALTER TABLE t1 CHANGE x2 x1 INT; CREATE TABLE t2 LIKE t1; SHOW CREATE TABLE t2; Table Create Table diff --git a/mysql-test/t/backup.test b/mysql-test/t/backup.test index 61e80ec682e..c761f48cbcb 100644 --- a/mysql-test/t/backup.test +++ b/mysql-test/t/backup.test @@ -1,7 +1,10 @@ # The server need to be started in $MYSQLTEST_VARDIR since it # uses ../../std_data/ --- source include/uses_vardir.inc +--source include/uses_vardir.inc + +# Save the initial number of concurrent sessions +--source include/count_sessions.inc # # This test is a bit tricky as we can't use backup table to overwrite an old @@ -57,6 +60,9 @@ unlock tables; connection con1; reap; drop table t5; +connection default; +disconnect con1; +disconnect con2; remove_file $MYSQLTEST_VARDIR/tmp/t1.MYD; remove_file $MYSQLTEST_VARDIR/tmp/t2.MYD; remove_file $MYSQLTEST_VARDIR/tmp/t3.MYD; @@ -68,6 +74,7 @@ remove_file $MYSQLTEST_VARDIR/tmp/t3.frm; remove_file $MYSQLTEST_VARDIR/tmp/t4.frm; remove_file $MYSQLTEST_VARDIR/tmp/t5.frm; + # End of 4.1 tests # End of 5.0 tests @@ -87,4 +94,11 @@ DROP TABLE `t+1`; RESTORE TABLE `t+1` FROM '../../tmp'; SELECT * FROM `t+1`; DROP TABLE `t+1`; +# +remove_file $MYSQLTEST_VARDIR/tmp/t@002b1.frm; +remove_file $MYSQLTEST_VARDIR/tmp/t@002b1.MYD; + + +# Wait till all disconnects are completed +--source include/wait_until_count_sessions.inc diff --git a/mysql-test/t/check.test b/mysql-test/t/check.test index 363f9d419d6..ff23b352b5a 100644 --- a/mysql-test/t/check.test +++ b/mysql-test/t/check.test @@ -1,3 +1,6 @@ +# Save the initial number of concurrent sessions +--source include/count_sessions.inc + connect (con1,localhost,root,,); connect (con2,localhost,root,,); connection con1; @@ -21,16 +24,18 @@ connection con2; insert into t1 values (200000); connection con1; reap; +connection default; +disconnect con1; +disconnect con2; drop table t1; + # End of 4.1 tests # -# Bug #9897 Views: 'Check Table' crashes MySQL, with a view and a table -# in the statement +# Bug#9897 Views: 'Check Table' crashes MySQL, with a view and a table +# in the statement # - -connection default; Create table t1(f1 int); Create table t2(f1 int); Create view v1 as Select * from t1; @@ -38,11 +43,15 @@ Check Table v1,t2; drop view v1; drop table t1, t2; + # -# BUG#26325 - TEMPORARY TABLE "corrupt" after first read, according to CHECK -# TABLE +# Bug#26325 TEMPORARY TABLE "corrupt" after first read, according to CHECK TABLE # CREATE TEMPORARY TABLE t1(a INT); CHECK TABLE t1; REPAIR TABLE t1; DROP TABLE t1; + + +# Wait till we reached the initial number of concurrent sessions +--source include/wait_until_count_sessions.inc diff --git a/mysql-test/t/compress.test b/mysql-test/t/compress.test index cd40aef002c..0a20daef8de 100644 --- a/mysql-test/t/compress.test +++ b/mysql-test/t/compress.test @@ -6,6 +6,10 @@ -- source include/have_compress.inc +# Save the initial number of concurrent sessions +--source include/count_sessions.inc + + connect (comp_con,localhost,root,,,,,COMPRESS); # Check compression turned on @@ -17,3 +21,10 @@ select * from information_schema.session_status where variable_name= 'COMPRESSIO # Check compression turned on SHOW STATUS LIKE 'Compression'; + +connection default; +disconnect comp_con; + +# Wait till all disconnects are completed +--source include/wait_until_count_sessions.inc + diff --git a/mysql-test/t/grant.test b/mysql-test/t/grant.test index a9491dc2736..2e42bdbf06c 100644 --- a/mysql-test/t/grant.test +++ b/mysql-test/t/grant.test @@ -3,6 +3,9 @@ # Grant tests not performed with embedded server -- source include/not_embedded.inc +# Save the initial number of concurrent sessions +--source include/count_sessions.inc + SET @old_log_bin_trust_function_creators= @@global.log_bin_trust_function_creators; SET GLOBAL log_bin_trust_function_creators = 1; @@ -81,7 +84,7 @@ delete from mysql.db where user='mysqltest_1'; delete from mysql.tables_priv where user='mysqltest_1'; delete from mysql.columns_priv where user='mysqltest_1'; flush privileges; ---error 1141 +--error ER_NONEXISTING_GRANT show grants for mysqltest_1@localhost; # @@ -119,15 +122,15 @@ drop table t1; # # Test some error conditions # ---error 1221 +--error ER_WRONG_USAGE GRANT FILE on mysqltest.* to mysqltest_1@localhost; -select 1; # To test that the previous command didn't cause problems +select 1; # To test that the previous command didn't cause problems # -# Bug #4898: User privileges depending on ORDER BY Settings of table db +# Bug#4898 User privileges depending on ORDER BY Settings of table db # insert into mysql.user (host, user) values ('localhost', 'test11'); -insert into mysql.db (host, db, user, select_priv) values +insert into mysql.db (host, db, user, select_priv) values ('localhost', 'a%', 'test11', 'Y'), ('localhost', 'ab%', 'test11', 'Y'); alter table mysql.db order by db asc; flush privileges; @@ -139,7 +142,7 @@ delete from mysql.user where user='test11'; delete from mysql.db where user='test11'; # -# Bug#6123: GRANT USAGE inserts useless Db row +# Bug#6123 GRANT USAGE inserts useless Db row # create database mysqltest1; grant usage on mysqltest1.* to test6123 identified by 'magic123'; @@ -148,7 +151,7 @@ delete from mysql.user where user='test6123'; drop database mysqltest1; # -# Test for 'drop user', 'revoke privileges, grant' +# Test for 'drop user', 'revoke privileges, grant' # create table t1 (a int); @@ -163,7 +166,7 @@ grant select(a) on test.t1 to drop_user@localhost; show grants for drop_user@localhost; # -# Bug3086 +# Bug#3086 SHOW GRANTS doesn't follow ANSI_QUOTES # set sql_mode=ansi_quotes; show grants for drop_user@localhost; @@ -181,7 +184,7 @@ show grants for drop_user@localhost; revoke all privileges, grant option from drop_user@localhost; show grants for drop_user@localhost; drop user drop_user@localhost; ---error 1269 +--error ER_REVOKE_GRANTS revoke all privileges, grant option from drop_user@localhost; grant select(a) on test.t1 to drop_user1@localhost; @@ -191,10 +194,10 @@ grant select on *.* to drop_user4@localhost; # Drop user now implicitly revokes all privileges. drop user drop_user1@localhost, drop_user2@localhost, drop_user3@localhost, drop_user4@localhost; ---error 1269 +--error ER_REVOKE_GRANTS revoke all privileges, grant option from drop_user1@localhost, drop_user2@localhost, drop_user3@localhost, drop_user4@localhost; ---error 1396 +--error ER_CANNOT_USER drop user drop_user1@localhost, drop_user2@localhost, drop_user3@localhost, drop_user4@localhost; drop table t1; @@ -204,12 +207,12 @@ show grants for mysqltest_1@localhost; drop user mysqltest_1@localhost; # -# Bug #3403 Wrong encodin in SHOW GRANTS output +# Bug#3403 Wrong encoding in SHOW GRANTS output # SET NAMES koi8r; CREATE DATABASE ÂÄ; USE ÂÄ; -CREATE TABLE ÔÁ (ËÏÌ int); +CREATE TABLE ÔÁ (ËÏÌ INT); GRANT SELECT ON ÂÄ.* TO ÀÚÅÒ@localhost; SHOW GRANTS FOR ÀÚÅÒ@localhost; @@ -230,7 +233,7 @@ DROP DATABASE ÂÄ; SET NAMES latin1; # -# Bug #5831: REVOKE ALL PRIVILEGES, GRANT OPTION does not revoke everything +# Bug#5831 REVOKE ALL PRIVILEGES, GRANT OPTION does not revoke everything # USE test; CREATE TABLE t1 (a int ); @@ -299,7 +302,7 @@ DROP DATABASE testdb9; DROP DATABASE testdb10; # -# Bug #6932: a problem with 'revoke ALL PRIVILEGES' +# Bug#6932 a problem with 'revoke ALL PRIVILEGES' # create table t1(a int, b int, c int, d int); @@ -313,7 +316,7 @@ drop user grant_user@localhost; drop table t1; # -# Bug#7391: Cross-database multi-table UPDATE security problem +# Bug#7391 Cross-database multi-table UPDATE security problem # create database mysqltest_1; create database mysqltest_2; @@ -322,36 +325,36 @@ create table mysqltest_1.t2 select 1 b, 2 r; create table mysqltest_2.t1 select 1 c, 2 s; create table mysqltest_2.t2 select 1 d, 2 t; -#test the column privileges +# test the column privileges grant update (a) on mysqltest_1.t1 to mysqltest_3@localhost; grant select (b) on mysqltest_1.t2 to mysqltest_3@localhost; grant select (c) on mysqltest_2.t1 to mysqltest_3@localhost; grant update (d) on mysqltest_2.t2 to mysqltest_3@localhost; connect (conn1,localhost,mysqltest_3,,); connection conn1; -SELECT * FROM INFORMATION_SCHEMA.COLUMN_PRIVILEGES - WHERE GRANTEE = '''mysqltest_3''@''localhost''' +SELECT * FROM INFORMATION_SCHEMA.COLUMN_PRIVILEGES + WHERE GRANTEE = '''mysqltest_3''@''localhost''' ORDER BY TABLE_NAME,COLUMN_NAME,PRIVILEGE_TYPE; SELECT * FROM INFORMATION_SCHEMA.TABLE_PRIVILEGES - WHERE GRANTEE = '''mysqltest_3''@''localhost''' + WHERE GRANTEE = '''mysqltest_3''@''localhost''' ORDER BY TABLE_NAME,PRIVILEGE_TYPE; SELECT * from INFORMATION_SCHEMA.SCHEMA_PRIVILEGES - WHERE GRANTEE = '''mysqltest_3''@''localhost''' + WHERE GRANTEE = '''mysqltest_3''@''localhost''' ORDER BY TABLE_SCHEMA,PRIVILEGE_TYPE; SELECT * from INFORMATION_SCHEMA.USER_PRIVILEGES WHERE GRANTEE = '''mysqltest_3''@''localhost''' ORDER BY TABLE_CATALOG,PRIVILEGE_TYPE; ---error 1143 +--error ER_COLUMNACCESS_DENIED_ERROR update mysqltest_1.t1, mysqltest_1.t2 set q=10 where b=1; ---error 1143 +--error ER_COLUMNACCESS_DENIED_ERROR update mysqltest_1.t2, mysqltest_2.t2 set d=20 where d=1; ---error 1142 +--error ER_TABLEACCESS_DENIED_ERROR update mysqltest_1.t1, mysqltest_2.t2 set d=20 where d=1; ---error 1142 +--error ER_TABLEACCESS_DENIED_ERROR update mysqltest_2.t1, mysqltest_1.t2 set c=20 where b=1; ---error 1143 +--error ER_COLUMNACCESS_DENIED_ERROR update mysqltest_2.t1, mysqltest_2.t2 set d=10 where s=2; -#the following two should work +# the following two should work update mysqltest_1.t1, mysqltest_2.t2 set a=10,d=10; update mysqltest_1.t1, mysqltest_2.t1 set a=20 where c=20; connection master; @@ -362,7 +365,7 @@ revoke all on mysqltest_1.t2 from mysqltest_3@localhost; revoke all on mysqltest_2.t1 from mysqltest_3@localhost; revoke all on mysqltest_2.t2 from mysqltest_3@localhost; -#test the db/table level privileges +# test the db/table level privileges grant all on mysqltest_2.* to mysqltest_3@localhost; grant select on *.* to mysqltest_3@localhost; # Next grant is needed to trigger bug#7391. Do not optimize! @@ -374,17 +377,17 @@ connection conn2; use mysqltest_1; update mysqltest_2.t1, mysqltest_2.t2 set c=500,d=600; # the following failed before, should fail now. ---error 1142 +--error ER_TABLEACCESS_DENIED_ERROR update mysqltest_1.t1, mysqltest_1.t2 set a=100,b=200; use mysqltest_2; -#the following used to succeed, it must fail now. ---error 1142 +# the following used to succeed, it must fail now. +--error ER_TABLEACCESS_DENIED_ERROR update mysqltest_1.t1, mysqltest_1.t2 set a=100,b=200; ---error 1142 +--error ER_TABLEACCESS_DENIED_ERROR update mysqltest_2.t1, mysqltest_1.t2 set c=100,b=200; ---error 1142 +--error ER_TABLEACCESS_DENIED_ERROR update mysqltest_1.t1, mysqltest_2.t2 set a=100,d=200; -#lets see the result +# lets see the result connection master; select t1.*,t2.* from mysqltest_1.t1,mysqltest_1.t2; select t1.*,t2.* from mysqltest_2.t1,mysqltest_2.t2; @@ -396,6 +399,7 @@ delete from mysql.columns_priv where user="mysqltest_3"; flush privileges; drop database mysqltest_1; drop database mysqltest_2; +disconnect conn2; # # just SHOW PRIVILEGES test @@ -403,7 +407,7 @@ drop database mysqltest_2; SHOW PRIVILEGES; # -# Rights for renaming test (Bug #3270) +# Rights for renaming test (Bug#3270) # connect (root,localhost,root,,test,$MASTER_MYPORT,$MASTER_MYSOCK); connection root; @@ -414,16 +418,18 @@ create table mysqltest.t1 (a int,b int,c int); grant all on mysqltest.t1 to mysqltest_1@localhost; connect (user1,localhost,mysqltest_1,,mysqltest,$MASTER_MYPORT,$MASTER_MYSOCK); connection user1; --- error 1142 +-- error ER_TABLEACCESS_DENIED_ERROR alter table t1 rename t2; disconnect user1; connection root; revoke all privileges on mysqltest.t1 from mysqltest_1@localhost; delete from mysql.user where user=_binary'mysqltest_1'; drop database mysqltest; +connection default; +disconnect root; # -# check all new table priveleges +# check all new table privileges # CREATE USER dummy@localhost; CREATE DATABASE mysqltest; @@ -488,7 +494,7 @@ REVOKE ALL PRIVILEGES, GRANT OPTION FROM dummy@localhost; DROP USER dummy@localhost; DROP DATABASE mysqltest; # -# Bug #11330: Entry in tables_priv with host = '' causes crash +# Bug#11330 Entry in tables_priv with host = '' causes crash # connection default; use mysql; @@ -499,7 +505,7 @@ flush privileges; use test; # -# Bug #10892 user variables not auto cast for comparisons +# Bug#10892 user variables not auto cast for comparisons # Check that we don't get illegal mix of collations # set @user123="non-existent"; @@ -518,18 +524,18 @@ show grants for root@localhost; set names latin1; # -# Bug #15598 Server crashes in specific case during setting new password +# Bug#15598 Server crashes in specific case during setting new password # - Caused by a user with host '' # create user mysqltest_7@; set password for mysqltest_7@ = password('systpass'); show grants for mysqltest_7@; drop user mysqltest_7@; ---error 1141 +--error ER_NONEXISTING_GRANT show grants for mysqltest_7@; # -# Bug#14385: GRANT and mapping to correct user account problems +# Bug#14385 GRANT and mapping to correct user account problems # create database mysqltest; use mysqltest; @@ -545,7 +551,7 @@ flush privileges; drop database mysqltest; # -# Bug #27515: DROP previlege is not required for RENAME TABLE +# Bug#27515 DROP previlege is not required for RENAME TABLE # connection master; create database db27515; @@ -556,7 +562,7 @@ grant insert, create on db27515.t2 to user27515@localhost; connect (conn27515, localhost, user27515, , db27515); connection conn27515; ---error 1142 +--error ER_TABLEACCESS_DENIED_ERROR rename table t1 to t2; disconnect conn27515; @@ -568,7 +574,7 @@ drop database db27515; --echo End of 4.1 tests # -# Bug #16297 In memory grant tables not flushed when users's hostname is "" +# Bug#16297 In memory grant tables not flushed when users's hostname is "" # use test; create table t1 (a int); @@ -585,11 +591,11 @@ create user mysqltest_8; create user mysqltest_8@host8; # Try to create them again ---error 1396 +--error ER_CANNOT_USER create user mysqltest_8@''; ---error 1396 +--error ER_CANNOT_USER create user mysqltest_8; ---error 1396 +--error ER_CANNOT_USER create user mysqltest_8@host8; select user, QUOTE(host) from mysql.user where user="mysqltest_8"; @@ -684,44 +690,43 @@ flush privileges; show grants for mysqltest_8@''; show grants for mysqltest_8; drop user mysqltest_8@''; ---error 1141 +--error ER_NONEXISTING_GRANT show grants for mysqltest_8@''; show grants for mysqltest_8; select * from information_schema.user_privileges where grantee like "'mysqltest_8'%"; drop user mysqltest_8; --replace_result $MASTER_MYSOCK MASTER_SOCKET $MASTER_MYPORT MASTER_PORT ---error 1045 +--error ER_ACCESS_DENIED_ERROR connect (conn6,localhost,mysqltest_8,,); connection master; ---error 1141 +--error ER_NONEXISTING_GRANT show grants for mysqltest_8; drop user mysqltest_8@host8; ---error 1141 +--error ER_NONEXISTING_GRANT show grants for mysqltest_8@host8; # Restore the anonymous users. insert into mysql.user select * from t2; flush privileges; drop table t2; - drop table t1; # -# Bug#20214: Incorrect error when user calls SHOW CREATE VIEW on non -# privileged view +# Bug#20214 Incorrect error when user calls SHOW CREATE VIEW on non +# privileged view # connection master; CREATE DATABASE mysqltest3; -use mysqltest3; +USE mysqltest3; CREATE TABLE t_nn (c1 INT); CREATE VIEW v_nn AS SELECT * FROM t_nn; CREATE DATABASE mysqltest2; -use mysqltest2; +USE mysqltest2; CREATE TABLE t_nn (c1 INT); CREATE VIEW v_nn AS SELECT * FROM t_nn; @@ -743,24 +748,18 @@ SHOW CREATE VIEW mysqltest2.v_nn; --error ER_TABLEACCESS_DENIED_ERROR SHOW CREATE TABLE mysqltest2.v_nn; - - # fail because of missing SHOW VIEW --error ER_TABLEACCESS_DENIED_ERROR SHOW CREATE VIEW mysqltest2.v_yn; --error ER_TABLEACCESS_DENIED_ERROR SHOW CREATE TABLE mysqltest2.v_yn; - - # succeed (despite of missing SELECT, having SHOW VIEW bails us out) SHOW CREATE TABLE mysqltest2.v_ny; # succeed (despite of missing SELECT, having SHOW VIEW bails us out) SHOW CREATE VIEW mysqltest2.v_ny; - - # fail because of missing (specific or generic) SELECT --error ER_TABLEACCESS_DENIED_ERROR SHOW CREATE TABLE mysqltest3.t_nn; @@ -769,16 +768,12 @@ SHOW CREATE TABLE mysqltest3.t_nn; --error ER_TABLEACCESS_DENIED_ERROR SHOW CREATE VIEW mysqltest3.t_nn; - - # fail because of missing missing (specific or generic) SELECT (and SHOW VIEW) --error ER_TABLEACCESS_DENIED_ERROR SHOW CREATE VIEW mysqltest3.v_nn; --error ER_TABLEACCESS_DENIED_ERROR SHOW CREATE TABLE mysqltest3.v_nn; - - # succeed thanks to generic SELECT SHOW CREATE TABLE mysqltest2.t_nn; @@ -786,17 +781,13 @@ SHOW CREATE TABLE mysqltest2.t_nn; --error ER_WRONG_OBJECT SHOW CREATE VIEW mysqltest2.t_nn; - - # succeed, have SELECT and SHOW VIEW SHOW CREATE VIEW mysqltest2.v_yy; # succeed, have SELECT and SHOW VIEW SHOW CREATE TABLE mysqltest2.v_yy; - - -#clean-up +# clean-up connection master; # succeed, we're root @@ -809,38 +800,33 @@ SHOW CREATE TABLE mysqltest2.t_nn; --error ER_WRONG_OBJECT SHOW CREATE VIEW mysqltest2.t_nn; - - DROP VIEW mysqltest2.v_nn; DROP VIEW mysqltest2.v_yn; DROP VIEW mysqltest2.v_ny; DROP VIEW mysqltest2.v_yy; - DROP TABLE mysqltest2.t_nn; - DROP DATABASE mysqltest2; - - - DROP VIEW mysqltest3.v_nn; DROP TABLE mysqltest3.t_nn; - DROP DATABASE mysqltest3; - +disconnect mysqltest_1; REVOKE ALL PRIVILEGES, GRANT OPTION FROM 'mysqltest_1'@'localhost'; DROP USER 'mysqltest_1'@'localhost'; # restore the original database -use test; +USE test; +connection default; +disconnect master; + # -# Bug #10668: CREATE USER does not enforce username length limit +# Bug#10668 CREATE USER does not enforce username length limit # --error ER_WRONG_STRING_LENGTH create user mysqltest1_thisisreallytoolong; # -# Test for BUG#16899: Possible buffer overflow in handling of DEFINER-clause. +# Test for Bug#16899 Possible buffer overflow in handling of DEFINER-clause. # # These checks are intended to ensure that appropriate errors are risen when # illegal user name or hostname is specified in user-clause of GRANT/REVOKE @@ -848,7 +834,7 @@ create user mysqltest1_thisisreallytoolong; # # -# Bug #22369: Alter table rename combined with other alterations causes lost tables +# Bug#22369 Alter table rename combined with other alterations causes lost tables # CREATE DATABASE mysqltest1; CREATE TABLE mysqltest1.t1 ( @@ -968,7 +954,7 @@ REVOKE EXECUTE ON PROCEDURE t1 FROM some_user_name@1234567890abcdefghij123456789 # -# BUG#23556: TRUNCATE TABLE still maps to DELETE +# Bug#23556 TRUNCATE TABLE still maps to DELETE # CREATE USER bug23556@localhost; CREATE DATABASE bug23556; @@ -1000,8 +986,12 @@ DROP TABLE t1; USE test; DROP DATABASE bug23556; DROP USER bug23556@localhost; +connection default; +disconnect bug23556; + + # -# Bug #6774: Replication fails with Wrong usage of DB GRANT and GLOBAL PRIVILEGES +# Bug#6774 Replication fails with Wrong usage of DB GRANT and GLOBAL PRIVILEGES # # Check if GRANT ... ON * ... fails when no database is selected connect (con1, localhost, root,,*NO-ONE*); @@ -1013,7 +1003,7 @@ connection default; # -# BUG#9504: Stored procedures: execute privilege doesn't make 'use database' +# Bug#9504 Stored procedures: execute privilege doesn't make 'use database' # okay. # @@ -1038,8 +1028,8 @@ CREATE PROCEDURE mysqltest2.p_inv() SQL SECURITY INVOKER SELECT 1; CREATE FUNCTION mysqltest3.f_def() RETURNS INT SQL SECURITY DEFINER - RETURN 1; - + RETURN 1; + CREATE FUNCTION mysqltest4.f_inv() RETURNS INT SQL SECURITY INVOKER RETURN 1; @@ -1095,7 +1085,7 @@ DROP USER mysqltest_1@localhost; # -# BUG#27337: Privileges are not restored properly. +# Bug#27337 Privileges are not restored properly. # # Actually, the patch for this bugs fixes two problems. So, here are two test # cases. @@ -1157,7 +1147,7 @@ DROP DATABASE mysqltest2; DROP USER mysqltest_1@localhost; -# Test case 2: priveleges are not checked properly for prepared statements. +# Test case 2: privileges are not checked properly for prepared statements. # Prepare. @@ -1230,6 +1220,7 @@ EXECUTE stmt2; --echo --echo ---> connection: default +--disconnect bug27337_con1 --disconnect bug27337_con2 DROP DATABASE mysqltest1; @@ -1239,21 +1230,21 @@ DROP USER mysqltest_1@localhost; DROP USER mysqltest_2@localhost; # -# Bug#27878: Unchecked privileges on a view referring to a table from another -# database. +# Bug#27878 Unchecked privileges on a view referring to a table from another +# database. # -use test; +USE test; CREATE TABLE t1 (f1 int, f2 int); INSERT INTO t1 VALUES(1,1), (2,2); CREATE DATABASE db27878; GRANT UPDATE(f1) ON t1 TO 'mysqltest_1'@'localhost'; GRANT SELECT ON `test`.* TO 'mysqltest_1'@'localhost'; GRANT ALL ON db27878.* TO 'mysqltest_1'@'localhost'; -use db27878; +USE db27878; CREATE SQL SECURITY INVOKER VIEW db27878.v1 AS SELECT * FROM test.t1; connect (user1,localhost,mysqltest_1,,test); connection user1; -use db27878; +USE db27878; --error 1356 UPDATE v1 SET f2 = 4; SELECT * FROM test.t1; @@ -1264,7 +1255,7 @@ REVOKE SELECT ON `test`.* FROM 'mysqltest_1'@'localhost'; REVOKE ALL ON db27878.* FROM 'mysqltest_1'@'localhost'; DROP USER mysqltest_1@localhost; DROP DATABASE db27878; -use test; +USE test; DROP TABLE t1; --echo # @@ -1273,8 +1264,10 @@ DROP TABLE t1; CREATE TEMPORARY TABLE mysql.user (id INT); FLUSH PRIVILEGES; DROP TABLE mysql.user; + + # -# Bug #33201 Crash occurs when granting update privilege on one column of a view +# Bug#33201 Crash occurs when granting update privilege on one column of a view # drop table if exists test; drop function if exists test_function; @@ -1305,7 +1298,7 @@ SET PASSWORD FOR CURRENT_USER() = PASSWORD(""); --echo End of 5.0 tests # -# Bug#21432: Database/Table name limited to 64 bytes, not chars, problems with multi-byte +# Bug#21432 Database/Table name limited to 64 bytes, not chars, problems with multi-byte # set names utf8; grant select on test.* to юзер_юзер@localhost; @@ -1317,7 +1310,7 @@ grant select on test.* to очень_длинный_юзер@localhost; set names default; # -# Bug #20901 - CREATE privilege is enough to insert into a table +# Bug#20901 CREATE privilege is enough to insert into a table # create database mysqltest; @@ -1329,7 +1322,7 @@ create table t1 (i INT); connect (user1,localhost,mysqltest,,mysqltest); connection user1; # show we don't have INSERT ---error 1142 +--error ER_TABLEACCESS_DENIED_ERROR insert into t1 values (1); # show we have CREATE create table t2 (i INT); @@ -1348,11 +1341,11 @@ insert into t2 values (1); # CREATE IF NOT EXISTS...SELECT, t1 exists, no INSERT, must fail ---error 1142 +--error ER_TABLEACCESS_DENIED_ERROR create table if not exists t1 select * from t2; # CREATE IF NOT EXISTS...SELECT, no t3 yet, no INSERT, must fail ---error 1142 +--error ER_TABLEACCESS_DENIED_ERROR create table if not exists t3 select * from t2; # CREATE IF NOT EXISTS...SELECT, t4 exists, have INSERT, must succeed @@ -1366,7 +1359,7 @@ create table if not exists t5 select * from t2; create table t6 select * from t2; # CREATE...SELECT, no t7 yet, no INSERT, must fail ---error 1142 +--error ER_TABLEACCESS_DENIED_ERROR create table t7 select * from t2; # CREATE...SELECT, t4 exists, have INSERT, must still fail (exists) @@ -1374,7 +1367,7 @@ create table t7 select * from t2; create table t4 select * from t2; # CREATE...SELECT, t1 exists, no INSERT, must fail ---error 1142 +--error ER_TABLEACCESS_DENIED_ERROR create table t1 select * from t2; @@ -1394,7 +1387,7 @@ use test; # -# Bug #16470 crash on grant if old grant tables +# Bug#16470 crash on grant if old grant tables # --echo FLUSH PRIVILEGES without procs_priv table. RENAME TABLE mysql.procs_priv TO mysql.procs_gone; @@ -1415,7 +1408,7 @@ FLUSH PRIVILEGES; # -# Bug#33464: DROP FUNCTION caused a crash. +# Bug#33464 DROP FUNCTION caused a crash. # CREATE DATABASE dbbug33464; CREATE USER 'userbug33464'@'localhost'; @@ -1472,8 +1465,11 @@ DROP PROCEDURE sp3; --error 0, ER_CANNOT_USER DROP USER 'userbug33464'@'localhost'; -use test; +USE test; DROP DATABASE dbbug33464; SET @@global.log_bin_trust_function_creators= @old_log_bin_trust_function_creators; + +# Wait till we reached the initial number of concurrent sessions +--source include/wait_until_count_sessions.inc diff --git a/mysql-test/t/information_schema.test b/mysql-test/t/information_schema.test index 7105d7e04f0..ef6e3eaca12 100644 --- a/mysql-test/t/information_schema.test +++ b/mysql-test/t/information_schema.test @@ -5,6 +5,10 @@ # on the presence of the log tables (which are CSV-based). --source include/have_csv.inc +# Save the initial number of concurrent sessions +--source include/count_sessions.inc + + # Test for information_schema.schemata & # show databases @@ -44,11 +48,11 @@ create view v1 (c) as table_name<>'ndb_apply_status'; select * from v1; -select c,table_name from v1 +select c,table_name from v1 inner join information_schema.TABLES v2 on (v1.c=v2.table_name) where v1.c like "t%"; -select c,table_name from v1 +select c,table_name from v1 left join information_schema.TABLES v2 on (v1.c=v2.table_name) where v1.c like "t%"; @@ -77,16 +81,17 @@ grant select (a) on mysqltest.t1 to mysqltest_2@localhost; grant select on mysqltest.v1 to mysqltest_3; connect (user3,localhost,mysqltest_2,,); connection user3; -select table_name, column_name, privileges from information_schema.columns +select table_name, column_name, privileges from information_schema.columns where table_schema = 'mysqltest' and table_name = 't1'; show columns from mysqltest.t1; connect (user4,localhost,mysqltest_3,,mysqltest); connection user4; select table_name, column_name, privileges from information_schema.columns where table_schema = 'mysqltest' and table_name = 'v1'; ---error 1345 +--error ER_VIEW_NO_EXPLAIN explain select * from v1; connection default; +disconnect user4; drop view v1, mysqltest.v1; drop tables mysqltest.t4, mysqltest.t1, t2, t3, t5; @@ -136,7 +141,7 @@ delimiter ;| # # Bug#7222 information_schema: errors in "routines" # -select parameter_style, sql_data_access, dtd_identifier +select parameter_style, sql_data_access, dtd_identifier from information_schema.routines where routine_schema='test'; --replace_column 5 # 6 # @@ -163,7 +168,7 @@ drop view v1; connect (user1,localhost,mysqltest_1,,); connection user1; select ROUTINE_NAME, ROUTINE_DEFINITION from information_schema.ROUTINES; ---error 1305 +--error ER_SP_DOES_NOT_EXIST show create function sub1; connection user3; select ROUTINE_NAME, ROUTINE_DEFINITION from information_schema.ROUTINES; @@ -182,6 +187,7 @@ show create function sub2; show function status like "sub2"; connection default; disconnect user1; +disconnect user3; drop function sub2; show create procedure sel2; @@ -321,7 +327,7 @@ drop view v1; create table t1(a NUMERIC(5,3), b NUMERIC(5,1), c float(5,2), d NUMERIC(6,4), e float, f DECIMAL(6,3), g int(11), h DOUBLE(10,3), i DOUBLE); -select COLUMN_NAME,COLUMN_TYPE, CHARACTER_MAXIMUM_LENGTH, +select COLUMN_NAME,COLUMN_TYPE, CHARACTER_MAXIMUM_LENGTH, CHARACTER_OCTET_LENGTH, NUMERIC_PRECISION, NUMERIC_SCALE from information_schema.columns where table_name= 't1'; drop table t1; @@ -334,7 +340,7 @@ drop table t115; delimiter //; create procedure p108 () begin declare c cursor for select data_type from information_schema.columns; open c; open c; end;// ---error 1325 +--error ER_SP_CURSOR_ALREADY_OPEN call p108()// delimiter ;// drop procedure p108; @@ -344,24 +350,24 @@ where table_name= "user"; select * from v1; drop view v1; -create view vo as select 'a' union select 'a'; +create view vo as select 'a' union select 'a'; show index from vo; select * from information_schema.TABLE_CONSTRAINTS where TABLE_NAME= "vo"; select * from information_schema.KEY_COLUMN_USAGE where -TABLE_NAME= "vo"; +TABLE_NAME= "vo"; drop view vo; select TABLE_NAME,TABLE_TYPE,ENGINE -from information_schema.tables +from information_schema.tables where table_schema='information_schema' limit 2; show tables from information_schema like "T%"; ---error 1044 +--error ER_DBACCESS_DENIED_ERROR create database information_schema; use information_schema; show full tables like "T%"; ---error 1109 +--error ER_UNKNOWN_TABLE create table t1(a int); use test; show tables; @@ -369,15 +375,15 @@ use information_schema; show tables like "T%"; # -# Bug#7210: information_schema: can't access when table-name = reserved word +# Bug#7210 information_schema: can't access when table-name = reserved word # select table_name from tables where table_name='user'; select column_name, privileges from columns where table_name='user' and column_name like '%o%'; # -# Bug#7212: information_schema: "Can't find file" errors if storage engine gone -# Bug#7211: information_schema: crash if bad view +# Bug#7212 information_schema: "Can't find file" errors if storage engine gone +# Bug#7211 information_schema: crash if bad view # use test; create function sub1(i int) returns int @@ -404,9 +410,9 @@ drop view v3; drop table t4; # -# Bug#7213: information_schema: redundant non-standard TABLE_NAMES table +# Bug#7213 information_schema: redundant non-standard TABLE_NAMES table # ---error 1109 +--error ER_UNKNOWN_TABLE select * from information_schema.table_names; # @@ -419,7 +425,7 @@ where table_schema="information_schema" and table_name="COLUMNS" and # # Bug#2718 information_schema: errors in "tables" # -select TABLE_ROWS from information_schema.tables where +select TABLE_ROWS from information_schema.tables where table_schema="information_schema" and table_name="COLUMNS"; select table_type from information_schema.tables where table_schema="mysql" and table_name="user"; @@ -432,14 +438,14 @@ show status where variable_name like "%database%"; show variables where variable_name like "skip_show_databas"; # -# Bug #7981:SHOW GLOBAL STATUS crashes server +# Bug#7981 SHOW GLOBAL STATUS crashes server # # We don't actually care about the value, just that it doesn't crash. --replace_column 2 # show global status like "Threads_running"; # -# Bug #7915 crash,JOIN VIEW, subquery, +# Bug#7915 crash,JOIN VIEW, subquery, # SELECT .. FROM INFORMATION_SCHEMA.COLUMNS # create table t1(f1 int); @@ -450,7 +456,7 @@ drop view v1; drop table t1, t2; # -# Bug #7476: crash on SELECT * FROM INFORMATION_SCHEMA.TABLES +# Bug#7476 crash on SELECT * FROM INFORMATION_SCHEMA.TABLES # use test; CREATE TABLE t_crashme ( f1 BIGINT); @@ -478,26 +484,26 @@ drop view a2, a1; drop table t_crashme; # -# Bug #7215 information_schema: columns are longtext instead of varchar -# Bug #7217 information_schema: columns are varbinary() instead of timestamp +# Bug#7215 information_schema: columns are longtext instead of varchar +# Bug#7217 information_schema: columns are varbinary() instead of timestamp # select table_schema,table_name, column_name from -information_schema.columns +information_schema.columns where data_type = 'longtext'; select table_name, column_name, data_type from information_schema.columns where data_type = 'datetime'; # -# Bug #8164 subquery with INFORMATION_SCHEMA.COLUMNS, 100 % CPU +# Bug#8164 subquery with INFORMATION_SCHEMA.COLUMNS, 100 % CPU # SELECT COUNT(*) FROM INFORMATION_SCHEMA.TABLES A -WHERE NOT EXISTS +WHERE NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS B WHERE A.TABLE_SCHEMA = B.TABLE_SCHEMA AND A.TABLE_NAME = B.TABLE_NAME); # -# Bug #9344 INFORMATION_SCHEMA, wrong content, numeric columns +# Bug#9344 INFORMATION_SCHEMA, wrong content, numeric columns # create table t1 @@ -515,21 +521,22 @@ WHERE TABLE_NAME= 't1'; drop table t1; # -# Bug#10261 INFORMATION_SCHEMA.COLUMNS, incomplete result for non root user +# Bug#10261 INFORMATION_SCHEMA.COLUMNS, incomplete result for non root user # grant select on test.* to mysqltest_4@localhost; connect (user10261,localhost,mysqltest_4,,); connection user10261; -SELECT TABLE_NAME, COLUMN_NAME, PRIVILEGES FROM INFORMATION_SCHEMA.COLUMNS +SELECT TABLE_NAME, COLUMN_NAME, PRIVILEGES FROM INFORMATION_SCHEMA.COLUMNS where COLUMN_NAME='TABLE_NAME'; connection default; +disconnect user10261; delete from mysql.user where user='mysqltest_4'; delete from mysql.db where user='mysqltest_4'; flush privileges; # -# Bug #9404 information_schema: Weird error messages +# Bug#9404 information_schema: Weird error messages # with SELECT SUM() ... GROUP BY queries # SELECT table_schema, count(*) FROM information_schema.TABLES WHERE table_schema IN ('mysql', 'INFORMATION_SCHEMA', 'test', 'mysqltest') AND table_name<>'ndb_binlog_index' AND table_name<>'ndb_apply_status' GROUP BY TABLE_SCHEMA; @@ -570,7 +577,7 @@ drop table t1; # -# Bug #10964 Information Schema:Authorization check on privilege tables is improper +# Bug#10964 Information Schema:Authorization check on privilege tables is improper # create database mysqltest; @@ -614,12 +621,16 @@ select * from information_schema.user_privileges where grantee like '%user%' order by grantee; show grants; connection default; +disconnect con1; +disconnect con2; +disconnect con3; +disconnect con4; drop user user1@localhost, user2@localhost, user3@localhost, user4@localhost; use test; drop database mysqltest; # -# Bug #11055 information_schema: routines.sql_data_access has wrong value +# Bug#11055 information_schema: routines.sql_data_access has wrong value # --disable_warnings drop procedure if exists p1; @@ -634,13 +645,13 @@ drop procedure p1; drop procedure p2; # -# Bug #9434 SHOW CREATE DATABASE information_schema; +# Bug#9434 SHOW CREATE DATABASE information_schema; # show create database information_schema; # -# Bug #11057 information_schema: columns table has some questionable contents -# Bug #12301 information_schema: NUMERIC_SCALE must be 0 for integer columns +# Bug#11057 information_schema: columns table has some questionable contents +# Bug#12301 information_schema: NUMERIC_SCALE must be 0 for integer columns # create table t1(f1 LONGBLOB, f2 LONGTEXT); select column_name,data_type,CHARACTER_OCTET_LENGTH, @@ -656,7 +667,7 @@ where table_name='t1'; drop table t1; # -# Bug #12127 triggers do not show in info_schema before they are used if set to the database +# Bug#12127 triggers do not show in info_schema before they are used if set to the database # create table t1 (f1 integer); create trigger tr1 after insert on t1 for each row set @test_var=42; @@ -678,8 +689,8 @@ show columns from t1; drop table t1; # -# Bug #12636: SHOW TABLE STATUS with where condition containing a subquery -# over information schema +# Bug#12636 SHOW TABLE STATUS with where condition containing a subquery +# over information schema # CREATE TABLE t1 (a int); @@ -693,7 +704,7 @@ SHOW TABLE STATUS FROM test DROP TABLE t1,t2; # -# Bug #12905 show fields from view behaving erratically with current database +# Bug#12905 show fields from view behaving erratically with current database # create table t1(f1 int); create view v1 (c) as select f1 from t1; @@ -701,28 +712,29 @@ connect (con5,localhost,root,,*NO-ONE*); select database(); show fields from test.v1; connection default; +disconnect con5; drop view v1; drop table t1; # -# Bug #9846 Inappropriate error displayed while dropping table from 'INFORMATION_SCHEMA' +# Bug#9846 Inappropriate error displayed while dropping table from 'INFORMATION_SCHEMA' # --error ER_PARSE_ERROR alter database information_schema; ---error 1044 +--error ER_DBACCESS_DENIED_ERROR drop database information_schema; ---error 1044 +--error ER_DBACCESS_DENIED_ERROR drop table information_schema.tables; ---error 1044 +--error ER_DBACCESS_DENIED_ERROR alter table information_schema.tables; # -# Bug #9683 INFORMATION_SCH: Creation of temporary table allowed in Information_schema DB +# Bug#9683 INFORMATION_SCH: Creation of temporary table allowed in Information_schema DB # use information_schema; ---error 1044 +--error ER_DBACCESS_DENIED_ERROR create temporary table schemata(f1 char(10)); # -# Bug #10708 SP's can use INFORMATION_SCHEMA as ROUTINE_SCHEMA +# Bug#10708 SP's can use INFORMATION_SCHEMA as ROUTINE_SCHEMA # delimiter |; --error ER_BAD_DB_ERROR @@ -731,13 +743,13 @@ BEGIN SELECT 'foo' FROM DUAL; END | delimiter ;| -select ROUTINE_NAME from routines where ROUTINE_SCHEMA='information_schema'; +select ROUTINE_NAME from routines where ROUTINE_SCHEMA='information_schema'; # -# Bug #10734 Grant of privileges other than 'select' and 'create view' should fail on schema +# Bug#10734 Grant of privileges other than 'select' and 'create view' should fail on schema # ---error 1044 +--error ER_DBACCESS_DENIED_ERROR grant all on information_schema.* to 'user1'@'localhost'; ---error 1044 +--error ER_DBACCESS_DENIED_ERROR grant select on information_schema.* to 'user1'@'localhost'; # @@ -763,9 +775,9 @@ where table_name="v1"; drop view v1; # -# Bug #14387 SHOW COLUMNS doesn't work on temporary tables -# Bug #15224 SHOW INDEX from temporary table doesn't work -# Bug #12770 DESC cannot display the info. about temporary table +# Bug#14387 SHOW COLUMNS doesn't work on temporary tables +# Bug#15224 SHOW INDEX from temporary table doesn't work +# Bug#12770 DESC cannot display the info. about temporary table # create temporary table t1(f1 int, index(f1)); show columns from t1; @@ -850,6 +862,7 @@ connection con16681; select * from information_schema.views where table_name='v1' or table_name='v2'; connection default; +disconnect con16681; drop view v1, v2; drop table t1; drop user mysqltest_1@localhost; @@ -864,8 +877,9 @@ select concat(@a, table_name), @a, table_name from information_schema.tables where table_schema = 'test'; drop table t1,t2; + # -# Bug#20230: routine_definition is not null +# Bug#20230 routine_definition is not null # --disable_warnings DROP PROCEDURE IF EXISTS p1; @@ -898,7 +912,7 @@ DROP PROCEDURE p1; DROP USER mysql_bug20230@localhost; # -# Bug#18925: subqueries with MIN/MAX functions on INFORMARTION_SCHEMA +# Bug#18925 subqueries with MIN/MAX functions on INFORMARTION_SCHEMA # SELECT t.table_name, c1.column_name @@ -931,8 +945,8 @@ SELECT t.table_name, c1.column_name ); # -# Bug#21231: query with a simple non-correlated subquery over -# INFORMARTION_SCHEMA.TABLES +# Bug#2123 query with a simple non-correlated subquery over +# INFORMARTION_SCHEMA.TABLES # SELECT MAX(table_name) FROM information_schema.tables WHERE table_schema IN ('mysql', 'INFORMATION_SCHEMA', 'test'); @@ -940,7 +954,7 @@ SELECT table_name from information_schema.tables WHERE table_name=(SELECT MAX(table_name) FROM information_schema.tables WHERE table_schema IN ('mysql', 'INFORMATION_SCHEMA', 'test')); # -# Bug #23037: Bug in field "Default" of query "SHOW COLUMNS FROM table" +# Bug#23037 Bug in field "Default" of query "SHOW COLUMNS FROM table" # # Note, MyISAM/InnoDB can't take more that 65532 chars, because the row # size is limited to 65535 bytes (BLOBs not counted) @@ -981,7 +995,7 @@ DROP TABLE bug23037; DROP FUNCTION get_value; # -# Bug#22413: EXPLAIN SELECT FROM view with ORDER BY yield server crash +# Bug#22413 EXPLAIN SELECT FROM view with ORDER BY yield server crash # create view v1 as select table_schema as object_schema, @@ -1007,7 +1021,7 @@ drop table t1,t2; # -# Bug#24630 Subselect query crashes mysqld +# Bug#24630 Subselect query crashes mysqld # select 1 as f1 from information_schema.tables where "CHARACTER_SETS"= (select cast(table_name as char) from information_schema.tables @@ -1034,8 +1048,8 @@ group by t.table_name order by num1, t.table_name; # create table t1(f1 int); create view v1 as select f1+1 as a from t1; -create table t2 (f1 int, f2 int); -create view v2 as select f1+1 as a, f2 as b from t2; +create table t2 (f1 int, f2 int); +create view v2 as select f1+1 as a, f2 as b from t2; select table_name, is_updatable from information_schema.views; # # Note: we can perform 'delete' for non updatable view. @@ -1045,7 +1059,7 @@ drop view v1,v2; drop table t1,t2; # -# Bug#25859 ALTER DATABASE works w/o parameters +# Bug#25859 ALTER DATABASE works w/o parameters # --error ER_PARSE_ERROR alter database; @@ -1074,6 +1088,7 @@ show triggers; select trigger_name from information_schema.triggers where event_object_table='t1'; connection default; +disconnect con27629; drop user mysqltest_1@localhost; drop database mysqltest; @@ -1097,13 +1112,13 @@ drop table t1; # # Bug#30079 A check for "hidden" I_S tables is flawed # ---error 1109 +--error ER_UNKNOWN_TABLE show fields from information_schema.table_names; ---error 1109 +--error ER_UNKNOWN_TABLE show keys from information_schema.table_names; # -# Bug#34529: Crash on complex Falcon I_S select after ALTER .. PARTITION BY +# Bug#34529 Crash on complex Falcon I_S select after ALTER .. PARTITION BY # USE information_schema; SET max_heap_table_size = 16384; @@ -1112,9 +1127,9 @@ CREATE TABLE test.t1( a INT ); # What we need to create here is a bit of a corner case: # We need a star query with information_schema tables, where the first -# branch of the star join produces zero rows, so that reading of the +# branch of the star join produces zero rows, so that reading of the # second branch never happens. At the same time we have to make sure -# that data for at least the last table is swapped from MEMORY/HEAP to +# that data for at least the last table is swapped from MEMORY/HEAP to # MyISAM. This and only this triggers the bug. SELECT * FROM tables ta @@ -1142,10 +1157,11 @@ connect (con3148,localhost,user3148,,test); connection con3148; select user,db from information_schema.processlist; connection default; +disconnect con3148; drop user user3148@localhost; # -# Bug #26174 Server Crash: INSERT ... SELECT ... FROM I_S.GLOBAL_STATUS +# Bug#26174 Server Crash: INSERT ... SELECT ... FROM I_S.GLOBAL_STATUS # in Event (see also openssl_1.test) # --disable_warnings @@ -1183,7 +1199,7 @@ SET GLOBAL event_scheduler=0; # # WL#3732 Information schema optimization -# +# explain select table_name from information_schema.views where table_schema='test' and table_name='v1'; @@ -1231,7 +1247,7 @@ DROP VIEW v1; # SELECT SCHEMA_NAME FROM INFORMATION_SCHEMA.SCHEMATA WHERE SCHEMA_NAME ='information_schema'; - + # # Bug#31381 Error in retrieving Data from INFORMATION_SCHEMA # @@ -1277,7 +1293,7 @@ show events where Db= 'information_schema'; use test; --echo # ---echo # Bug#34166: Server crash in SHOW OPEN TABLES and prelocking +--echo # Bug#34166 Server crash in SHOW OPEN TABLES and prelocking --echo # --disable_warnings drop table if exists t1; @@ -1299,8 +1315,8 @@ drop table t1; drop function f1; # -# BUG#34656 - KILL a query = Assertion failed: m_status == DA_ERROR || -# m_status == DA_OK +# Bug#34656 KILL a query = Assertion failed: m_status == DA_ERROR || +# m_status == DA_OK # connect (conn1, localhost, root,,); connection conn1; @@ -1378,13 +1394,13 @@ select * from information_schema.global_variables where variable_name='init_conn set global init_connect=""; # -# Bug #34517 SHOW GLOBAL STATUS does not work properly in embedded server. +# Bug#34517 SHOW GLOBAL STATUS does not work properly in embedded server. # create table t0 select * from information_schema.global_status where VARIABLE_NAME='COM_SELECT'; SELECT 1; select a.VARIABLE_VALUE - b.VARIABLE_VALUE from t0 b, information_schema.global_status a - where a.VARIABLE_NAME = b.VARIABLE_NAME; + where a.VARIABLE_NAME = b.VARIABLE_NAME; drop table t0; # @@ -1395,3 +1411,7 @@ SELECT CREATE_OPTIONS FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME='t1'; DROP TABLE t1; --echo End of 5.1 tests. + +# Wait till all disconnects are completed +--source include/wait_until_count_sessions.inc + diff --git a/mysql-test/t/multi_update.test b/mysql-test/t/multi_update.test index 0793ef15faf..fc37fd6a27d 100644 --- a/mysql-test/t/multi_update.test +++ b/mysql-test/t/multi_update.test @@ -25,17 +25,17 @@ let $1 = 100; while ($1) { let $2 = 5; - eval insert into t1(t) values ('$1'); + eval insert into t1(t) values ('$1'); while ($2) { - eval insert into t2(id2,t) values ($1,'$2'); + eval insert into t2(id2,t) values ($1,'$2'); let $3 = 10; while ($3) { - eval insert into t3(id3,t) values ($1,'$2'); + eval insert into t3(id3,t) values ($1,'$2'); dec $3; } - dec $2; + dec $2; } dec $1; } @@ -80,11 +80,11 @@ let $1 = 1000; while ($1) { let $2 = 5; - eval insert into t1 values ($1,'aaaaaaaaaaaaaaaaaaaa'); + eval insert into t1 values ($1,'aaaaaaaaaaaaaaaaaaaa'); while ($2) { - eval insert into t2(id2,t) values ($1,'bbbbbbbbbbbbbbbbb'); - dec $2; + eval insert into t2(id2,t) values ($1,'bbbbbbbbbbbbbbbbb'); + dec $2; } dec $1; } @@ -318,7 +318,7 @@ update t2, t1 set t2.field=t1.field delete t1, t2 from t2 inner join t1 on t1.id1=t2.id2 where 0=1; -delete t1, t2 from t2,t1 +delete t1, t2 from t2,t1 where t1.id1=t2.id2 and 0=1; drop table t1,t2; @@ -352,7 +352,7 @@ create table `t2` (`c2_id` int(10) unsigned NULL auto_increment, `c2_p_id` int(1 insert into t1 values (0,'A01-Comp',1); insert into t1 values (0,'B01-Comp',1); insert into t2 values (0,1,'A Note',1); -update t1 left join t2 on p_id = c2_p_id set c2_note = 'asdf-1' where p_id = 2; +update t1 left join t2 on p_id = c2_p_id set c2_note = 'asdf-1' where p_id = 2; select * from t1; select * from t2; drop table t1, t2; @@ -380,6 +380,9 @@ revoke all privileges on mysqltest.t1 from mysqltest_1@localhost; revoke all privileges on mysqltest.* from mysqltest_1@localhost; delete from mysql.user where user=_binary'mysqltest_1'; drop database mysqltest; +connection default; +disconnect user1; +disconnect root; # # multi delete wrong table check @@ -394,7 +397,7 @@ drop table t1, t2, t3; # # multi* unique updating table check # -create table t1 (col1 int); +create table t1 (col1 int); create table t2 (col1 int); -- error ER_UPDATE_TABLE_USED update t1,t2 set t1.col1 = (select max(col1) from t1) where t1.col1 = t2.col1; @@ -402,16 +405,16 @@ update t1,t2 set t1.col1 = (select max(col1) from t1) where t1.col1 = t2.col1; delete t1 from t1,t2 where t1.col1 < (select max(col1) from t1) and t1.col1 = t2.col1; drop table t1,t2; -# Test for BUG#5837 - delete with outer join and const tables +# Test for Bug#5837 delete with outer join and const tables --disable_warnings create table t1 ( - aclid bigint not null primary key, - status tinyint(1) not null + aclid bigint not null primary key, + status tinyint(1) not null ) engine = innodb; create table t2 ( - refid bigint not null primary key, - aclid bigint, index idx_acl(aclid) + refid bigint not null primary key, + aclid bigint, index idx_acl(aclid) ) engine = innodb; --enable_warnings insert into t2 values(1,null); @@ -419,7 +422,7 @@ delete t2, t1 from t2 left join t1 on (t2.aclid=t1.aclid) where t2.refid='1'; drop table t1, t2; # -# Bug#19225: unchecked error leads to server crash +# Bug#19225 unchecked error leads to server crash # create table t1(a int); create table t2(a int); @@ -429,7 +432,7 @@ drop table t1, t2; # End of 4.1 tests # -# Test for bug #1980. +# Test for Bug#1980. # --disable_warnings create table t1 ( c char(8) not null ) engine=innodb; @@ -490,11 +493,22 @@ send alter table t1 add column c int default 100 after a; connect (updater,localhost,root,,test); connection updater; -sleep 2; +# Wait till "alter table t1 ..." of session changer is in work. +# = There is one session is in state "Locked". +let $wait_condition= select count(*)= 1 from information_schema.processlist + where state= 'Locked'; +--source include/wait_condition.inc send update t1, v1 set t1.b=t1.a+t1.b+v1.b where t1.a=v1.a; connection locker; -sleep 2; +# Wait till +# - "alter table t1 ..." of session changer and +# - "update t1, v1 ..." of session updater +# are in work. +# = There are two session is in state "Locked". +let $wait_condition= select count(*)= 2 from information_schema.processlist + where state= 'Locked'; +--source include/wait_condition.inc unlock tables; connection changer; @@ -507,6 +521,11 @@ select * from t2; drop view v1; drop table t1, t2; +connection default; +disconnect locker; +disconnect changer; +disconnect updater; + # # Test multi updates and deletes using primary key and without. # @@ -538,8 +557,9 @@ select * from t2 order by id; drop table t1, t2; # -# Bug#27716 multi-update did partially and has not binlogged +# Bug#27716 multi-update did partially and has not binlogged # + CREATE TABLE `t1` ( `a` int(11) NOT NULL auto_increment, `b` int(11) default NULL, @@ -561,29 +581,29 @@ set @@session.binlog_format= mixed; insert into t1 values (1,1),(2,2); insert into t2 values (1,1),(4,4); reset master; -error ER_DUP_ENTRY; +--error ER_DUP_ENTRY UPDATE t2,t1 SET t2.a=t1.a+2; # check select * from t2 /* must be (3,1), (4,4) */; show master status /* there must be the UPDATE query event */; -# B. testing multi_update::send_error() ineffective update -# (as there is a policy described at mysql_update() still go to binlog) +# B. testing multi_update::send_error() ineffective update +# (as there is a policy described at mysql_update() still go to binlog) delete from t1; delete from t2; insert into t1 values (1,2),(3,4),(4,4); insert into t2 values (1,2),(3,4),(4,4); reset master; -error ER_DUP_ENTRY; +--error ER_DUP_ENTRY UPDATE t2,t1 SET t2.a=t2.b where t2.a=t1.a; show master status /* there must be the UPDATE query event */; -# cleanup bug#27716 +# cleanup drop table t1, t2; set @@session.binlog_format= @sav_binlog_format; # -# Bug #29136 erred multi-delete on trans table does not rollback +# Bug#29136 erred multi-delete on trans table does not rollback # # prepare @@ -610,7 +630,7 @@ delete t3.* from t2,t3 where t2.a=t3.a; select count(*) from t1 /* must be 1 */; select count(*) from t3 /* must be 1 */; -# cleanup bug#29136 +# cleanup drop table t1, t2, t3; # diff --git a/mysql-test/t/overflow.test b/mysql-test/t/overflow.test index a62ef9c4cd2..774c43be658 100644 --- a/mysql-test/t/overflow.test +++ b/mysql-test/t/overflow.test @@ -1,6 +1,14 @@ +# Save the initial number of concurrent sessions +--source include/count_sessions.inc + connect (con1,localhost,root,,); connection con1; --- error 1064,1102,1280 +--error ER_PARSE_ERROR,ER_WRONG_DB_NAME,ER_WRONG_NAME_FOR_INDEX drop database AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA; +connection default; +disconnect con1; # End of 4.1 tests + +# Wait till we reached the initial number of concurrent sessions +--source include/wait_until_count_sessions.inc diff --git a/mysql-test/t/packet.test b/mysql-test/t/packet.test index 93b46766d99..7e08a0f6c3f 100644 --- a/mysql-test/t/packet.test +++ b/mysql-test/t/packet.test @@ -4,10 +4,18 @@ # swallowing them and returning an error --source include/not_windows.inc +# Save the initial number of concurrent sessions +--source include/count_sessions.inc + + # # Check protocol handling # +set @max_allowed_packet=@@global.max_allowed_packet; +set @net_buffer_length=@@global.net_buffer_length; + + # setting values below minimum threshold of 1024 will cause truncating set global max_allowed_packet=100; set global net_buffer_length=100; @@ -22,8 +30,10 @@ select repeat('a',2000); connect (con1,localhost,root,,); connection con1; select @@net_buffer_length, @@max_allowed_packet; ---error 1153 +--error ER_NET_PACKET_TOO_LARGE SELECT length("aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa") as len; +connection default; +disconnect con1; # # Reset to default values and reconnect @@ -34,5 +44,13 @@ connect (con2,localhost,root,,); connection con2; SELECT length("aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa") as len; select length(repeat('a',2000)); +connection default; +disconnect con2; + +set global max_allowed_packet=@max_allowed_packet; +set global net_buffer_length=@net_buffer_length; # End of 4.1 tests + +# Wait till we reached the initial number of concurrent sessions +--source include/wait_until_count_sessions.inc diff --git a/mysql-test/t/query_cache_notembedded.test b/mysql-test/t/query_cache_notembedded.test index c3889ef0267..095d47f5bdf 100644 --- a/mysql-test/t/query_cache_notembedded.test +++ b/mysql-test/t/query_cache_notembedded.test @@ -1,6 +1,10 @@ -- source include/have_query_cache.inc -- source include/not_embedded.inc +# Save the initial number of concurrent sessions +--source include/count_sessions.inc + + # # Tests with query cache # @@ -79,7 +83,7 @@ show status like "Qcache_free_blocks"; drop table t1, t2, t3, t11, t21; # -# do not use QC if tables locked (BUG#12385) +# do not use QC if tables locked (Bug#12385) # connect (root,localhost,root,,test,$MASTER_MYPORT,$MASTER_MYSOCK); connection root; @@ -96,10 +100,13 @@ SELECT * FROM t1; connection root; SELECT * FROM t1; drop table t1; +connection default; +disconnect root; +disconnect root2; # -# query in QC from normal execution and SP (BUG#6897) -# improved to also test BUG#3583 and BUG#12990 +# query in QC from normal execution and SP (Bug#6897) +# improved to also test Bug#3583 and Bug#12990 # flush query cache; reset query cache; @@ -181,7 +188,7 @@ drop procedure f4; drop table t1; # -# bug#14767: INSERT in SF + concurrent SELECT with query cache +# Bug#14767 INSERT in SF + concurrent SELECT with query cache # connection default; SET @old_log_bin_trust_function_creators= @@global.log_bin_trust_function_creators; @@ -257,6 +264,7 @@ SELECT id FROM view1 WHERE id>2; show status like 'Qcache_queries_in_cache'; connection default; +USE test; DROP DATABASE bug30269; disconnect bug30269; DROP USER 'bug30269'@'localhost'; @@ -266,3 +274,5 @@ set GLOBAL query_cache_limit=default; set GLOBAL query_cache_min_res_unit=default; set GLOBAL query_cache_size=default; +# Wait till we reached the initial number of concurrent sessions +--source include/wait_until_count_sessions.inc diff --git a/mysql-test/t/sp-threads.test b/mysql-test/t/sp-threads.test index d8a8ce5dae7..e1012e2b72d 100644 --- a/mysql-test/t/sp-threads.test +++ b/mysql-test/t/sp-threads.test @@ -5,6 +5,9 @@ # except security/privilege tests, they go to sp-security.test # +# Save the initial number of concurrent sessions +--source include/count_sessions.inc + connect (con1root,localhost,root,,); connect (con2root,localhost,root,,); connect (con3root,localhost,root,,); @@ -17,7 +20,7 @@ drop table if exists t1; --enable_warnings create table t1 (s1 int, s2 int, s3 int); -delimiter //; +delimiter //; create procedure bug4934() begin insert into t1 values (1,0,1); @@ -38,7 +41,7 @@ drop table t1; create table t1 (s1 int, s2 int, s3 int); drop procedure bug4934; -delimiter //; +delimiter //; create procedure bug4934() begin end// @@ -58,7 +61,7 @@ drop procedure bug4934; # -# BUG #9486 "Can't perform multi-update in stored procedure" +# Bug#9486 Can't perform multi-update in stored procedure # --disable_warnings drop procedure if exists bug9486; @@ -87,8 +90,9 @@ reap; drop procedure bug9486; drop table t1, t2; + # -# BUG#11158: Can't perform multi-delete in stored procedure +# Bug#11158 Can't perform multi-delete in stored procedure # --disable_warnings drop procedure if exists bug11158; @@ -114,8 +118,9 @@ connection con1root; drop procedure bug11158; drop table t1, t2; + # -# BUG#11554: Server crashes on statement indirectly using non-cached function +# Bug#11554 Server crashes on statement indirectly using non-cached function # --disable_warnings drop function if exists bug11554; @@ -134,7 +139,7 @@ drop table t1; drop view v1; -# BUG#12228 +# Bug#12228 Crash happens during calling specific SP in multithread environment --disable_warnings drop procedure if exists p1; drop procedure if exists p2; @@ -168,18 +173,26 @@ connection con2root; unlock tables; connection con1root; -# Crash will be here if we hit BUG#12228 +# Crash will be here if we hit Bug#12228 reap; drop procedure p1; drop procedure p2; drop table t1; + # -# BUG#NNNN: New bug synopsis +# Bug#NNNN New bug synopsis # #--disable_warnings #drop procedure if exists bugNNNN; #--enable_warnings #create procedure bugNNNN... +connection default; +disconnect con1root; +disconnect con2root; +disconnect con3root; + +# Wait till we reached the initial number of concurrent sessions +--source include/wait_until_count_sessions.inc diff --git a/mysql-test/t/subselect.test b/mysql-test/t/subselect.test index 9dcb645b28d..e82764d6ac4 100644 --- a/mysql-test/t/subselect.test +++ b/mysql-test/t/subselect.test @@ -78,9 +78,9 @@ select * from t2 where t2.b=(select a from t3 order by 1 desc limit 1); (select * from t2 where t2.b=(select a from t3 order by 1 desc limit 1)) union (select * from t4 where t4.b=(select max(t2.a)*4 from t2) order by a); explain extended (select * from t2 where t2.b=(select a from t3 order by 1 desc limit 1)) union (select * from t4 where t4.b=(select max(t2.a)*4 from t2) order by a); select (select a from t3 where a<t2.a*4 order by 1 desc limit 1), a from t2; -select (select t3.a from t3 where a<8 order by 1 desc limit 1), a from +select (select t3.a from t3 where a<8 order by 1 desc limit 1), a from (select * from t2 where a>1) as tt; -explain extended select (select t3.a from t3 where a<8 order by 1 desc limit 1), a from +explain extended select (select t3.a from t3 where a<8 order by 1 desc limit 1), a from (select * from t2 where a>1) as tt; select * from t1 where t1.a=(select t2.a from t2 where t2.b=(select max(a) from t3) order by 1 desc limit 1); select * from t1 where t1.a=(select t2.a from t2 where t2.b=(select max(a) from t3 where t3.a > t1.a) order by 1 desc limit 1); @@ -367,7 +367,7 @@ let $wait_condition= SELECT COUNT(*) <> $row_count_before FROM t1; --source include/wait_condition.inc select * from t1; # -#TODO: should be uncommented after bug 380 fix pushed +#TODO: should be uncommented after Bug#380 fix pushed #INSERT INTO t1 (x) SELECT (SELECT SUM(a)+b FROM t2) from t3; #select * from t1; drop table t1, t2, t3; @@ -536,7 +536,7 @@ do (SELECT a from t1); -- error ER_NO_SUCH_TABLE set @a:=(SELECT a from t1); -CREATE TABLE t1 (a int, KEY(a)); +CREATE TABLE t1 (a int, KEY(a)); HANDLER t1 OPEN; -- error ER_PARSE_ERROR HANDLER t1 READ a=((SELECT 1)); @@ -678,7 +678,7 @@ CREATE TABLE t2 ( INSERT INTO t2 VALUES ('AUS','Australia','Oceania','Australia and New Zealand',7741220.00,1901,18886000,79.8,351182.00,392911.00,'Australia','Constitutional Monarchy, Federation','Elisabeth II',135,'AU'); INSERT INTO t2 VALUES ('AZE','Azerbaijan','Asia','Middle East',86600.00,1991,7734000,62.9,4127.00,4100.00,'Azärbaycan','Federal Republic','Heydär Äliyev',144,'AZ'); -select t2.Continent, t1.Name, t1.Population from t2 LEFT JOIN t1 ON t2.Code = t1.t2 where t1.Population IN (select max(t1.Population) AS Population from t1, t2 where t1.t2 = t2.Code group by Continent); +select t2.Continent, t1.Name, t1.Population from t2 LEFT JOIN t1 ON t2.Code = t1.t2 where t1.Population IN (select max(t1.Population) AS Population from t1, t2 where t1.t2 = t2.Code group by Continent); drop table t1, t2; @@ -732,9 +732,9 @@ drop table t1,t2; # # correct NULL in <CONSTANT> IN (SELECT ...) # -create table t1 (a int, unique index indexa (a)); -insert into t1 values (-1), (-4), (-2), (NULL); -select -10 IN (select a from t1 FORCE INDEX (indexa)); +create table t1 (a int, unique index indexa (a)); +insert into t1 values (-1), (-4), (-2), (NULL); +select -10 IN (select a from t1 FORCE INDEX (indexa)); drop table t1; # @@ -816,7 +816,7 @@ disable_query_log; let $1 = 10000; while ($1) { - eval insert into t1 values (rand()*100000+200,rand()*100000); + eval insert into t1 values (rand()*100000+200,rand()*100000); dec $1; } enable_query_log; @@ -842,7 +842,7 @@ create table t2 (a int, b int); create table t3 (a int, b int); insert into t1 values (0,100),(1,2), (1,3), (2,2), (2,7), (2,-1), (3,10); insert into t2 values (0,0), (1,1), (2,1), (3,1), (4,1); -insert into t3 values (3,3), (2,2), (1,1); +insert into t3 values (3,3), (2,2), (1,1); select a,(select count(distinct t1.b) as sum from t1,t2 where t1.a=t2.a and t2.b > 0 and t1.a <= t3.b group by t1.a order by sum limit 1) from t3; drop table t1,t2,t3; @@ -1010,7 +1010,7 @@ drop table t1, t2; # # unresolved field error # -create table t1 (s1 int); +create table t1 (s1 int); create table t2 (s1 int); -- error ER_BAD_FIELD_ERROR select * from t1 where (select count(*) from t2 where t1.s2) = 1; @@ -1035,11 +1035,12 @@ INSERT INTO t1 VALUES (1),(1),(1),(1),(1),(2),(3),(4),(5); SELECT DISTINCT (SELECT a) FROM t1 LIMIT 100; DROP TABLE t1; + # -# Bug 2198 +# Bug#2198 SELECT INTO OUTFILE (with Sub-Select) Problem # -create table t1 (a int, b decimal(13, 3)); +create table t1 (a int, b decimal(13, 3)); insert into t1 values (1, 0.123); let $outfile_abs= $MYSQLTEST_VARDIR/tmp/subselect.out.file.1; let $outfile_rel= ../../tmp/subselect.out.file.1; @@ -1052,8 +1053,9 @@ eval load data infile "$outfile_rel" into table t1; select * from t1; drop table t1; + # -# Bug 2479 +# Bug#2479 dependant subquery with limit crash # CREATE TABLE `t1` ( @@ -1091,8 +1093,9 @@ select 2 in (select * from t1); SET SQL_SELECT_LIMIT=default; drop table t1; + # -# Bug #3118: subselect + order by +# Bug#3118 subselect + order by # CREATE TABLE t1 (a int, b int, INDEX (a)); @@ -1131,8 +1134,9 @@ insert into t1 values (1); explain select benchmark(1000, (select a from t1 where a=sha(rand()))); drop table t1; + # -# bug 3188 +# Bug#3188 Ambiguous Column in Subselect crashes server # create table t1(id int); create table t2(id int); @@ -1141,8 +1145,9 @@ create table t3(flag int); select (select * from t3 where id not null) from t1, t2; drop table t1,t2,t3; + # -# aggregate functions (Bug #3505) +# aggregate functions (Bug#3505 Wrong results on use of ORDER BY with subqueries) # CREATE TABLE t1 (id INT); CREATE TABLE t2 (id INT); @@ -1333,8 +1338,9 @@ select * from t1 up where exists (select * from t1 where t1.a=up.a); explain extended select * from t1 up where exists (select * from t1 where t1.a=up.a); drop table t1; + # -# Bug #4102: subselect in HAVING +# Bug#4102 subselect in HAVING # CREATE TABLE t1 (t1_a int); @@ -1345,8 +1351,10 @@ SELECT * FROM t1, t2 table2 WHERE t1_a = 1 AND table2.t2_a = 1 HAVING table2.t2_b = (SELECT MAX(t2_b) FROM t2 WHERE t2_a = table2.t2_a); DROP TABLE t1, t2; + # -# Test problem with NULL and derived tables (Bug #4097) +# Test problem with NULL and derived tables +# (Bug#4097 JOIN with subquery causes entire column to report NULL) # CREATE TABLE t1 (id int(11) default NULL,name varchar(10) default NULL); @@ -1356,18 +1364,19 @@ INSERT INTO t2 VALUES (1,'Fido'),(2,'Spot'),(3,'Felix'); SELECT a.*, b.* FROM (SELECT * FROM t1) AS a JOIN t2 as b on a.id=b.id; drop table t1,t2; + # # outer fields resolving in INSERT/REPLACE and CRETE with SELECT # CREATE TABLE t1 ( a int, b int ); CREATE TABLE t2 ( c int, d int ); INSERT INTO t1 VALUES (1,2), (2,3), (3,4); -SELECT a AS abc, b FROM t1 outr WHERE b = +SELECT a AS abc, b FROM t1 outr WHERE b = (SELECT MIN(b) FROM t1 WHERE a=outr.a); -INSERT INTO t2 SELECT a AS abc, b FROM t1 outr WHERE b = +INSERT INTO t2 SELECT a AS abc, b FROM t1 outr WHERE b = (SELECT MIN(b) FROM t1 WHERE a=outr.a); select * from t2; -CREATE TABLE t3 SELECT a AS abc, b FROM t1 outr WHERE b = +CREATE TABLE t3 SELECT a AS abc, b FROM t1 outr WHERE b = (SELECT MIN(b) FROM t1 WHERE a=outr.a); select * from t3; prepare stmt1 from "INSERT INTO t2 SELECT a AS abc, b FROM t1 outr WHERE b = (SELECT MIN(b) FROM t1 WHERE a=outr.a);"; @@ -1391,19 +1400,21 @@ insert into t2 values (1,2); select t000.a, count(*) `C` FROM t1 t000 GROUP BY t000.a HAVING count(*) > ALL (SELECT count(*) FROM t2 t001 WHERE t001.a=1); drop table t1,t2; + # -# BUG#4769 - fulltext in subselect +# Bug#4769 - fulltext in subselect # -create table t1 (a int not null auto_increment primary key, b varchar(40), fulltext(b)); -insert into t1 (b) values ('ball'),('ball games'), ('games'), ('foo'), ('foobar'), ('Serg'), ('Sergei'),('Georg'), ('Patrik'),('Hakan'); -create table t2 (a int); -insert into t2 values (1),(3),(2),(7); -select a,b from t1 where match(b) against ('Ball') > 0; -select a from t2 where a in (select a from t1 where match(b) against ('Ball') > 0); +create table t1 (a int not null auto_increment primary key, b varchar(40), fulltext(b)); +insert into t1 (b) values ('ball'),('ball games'), ('games'), ('foo'), ('foobar'), ('Serg'), ('Sergei'),('Georg'), ('Patrik'),('Hakan'); +create table t2 (a int); +insert into t2 values (1),(3),(2),(7); +select a,b from t1 where match(b) against ('Ball') > 0; +select a from t2 where a in (select a from t1 where match(b) against ('Ball') > 0); drop table t1,t2; + # -# BUG#5003 - like in subselect +# Bug#5003 - like in subselect # CREATE TABLE t1(`IZAVORGANG_ID` VARCHAR(11) CHARACTER SET latin1 COLLATE latin1_bin,`KUERZEL` VARCHAR(10) CHARACTER SET latin1 COLLATE latin1_bin,`IZAANALYSEART_ID` VARCHAR(11) CHARACTER SET latin1 COLLATE latin1_bin,`IZAPMKZ_ID` VARCHAR(11) CHARACTER SET latin1 COLLATE latin1_bin); CREATE INDEX AK01IZAVORGANG ON t1(izaAnalyseart_id,Kuerzel); @@ -1460,10 +1471,10 @@ SELECT b.sc FROM (SELECT (SELECT a.access FROM t1 a WHERE a.map = op.map AND a.s SELECT b.ac FROM (SELECT (SELECT a.access FROM t1 a WHERE a.map = op.map AND a.slave = op.pid AND a.master = 1) ac FROM t2 op WHERE op.id = 12 AND op.map = 0) b; drop tables t1,t2; + # -# Test for bug #6462. "Same request on same data returns different -# results." a.k.a. "Proper cleanup of subqueries is missing for -# SET and DO statements". +# Test for Bug#6462 Same request on same data returns different results +# a.k.a. "Proper cleanup of subqueries is missing for SET and DO statements". # create table t1 (a int not null, b int not null, c int, primary key (a,b)); insert into t1 values (1,1,1), (2,2,2), (3,3,3); @@ -1485,9 +1496,11 @@ drop table t1; connect (root,localhost,root,,test,$MASTER_MYPORT,$MASTER_MYSOCK); connection root; set @got_val= (SELECT 1 FROM (SELECT 'A' as my_col) as T1 ) ; +connection default; +disconnect root; # -# primary query with temporary table and subquery with groupping +# primary query with temporary table and subquery with grouping # create table t1 (a int, b int); create table t2 (a int, b int); @@ -1548,14 +1561,15 @@ INSERT INTO t1 VALUES ('ASM','American Samoa','Oceania','Polynesia',199.00,0,680 INSERT INTO t1 VALUES ('ATF','French Southern territories','Antarctica','Antarctica',7780.00,0,0,NULL,0.00,NULL,'Terres australes françaises','Nonmetropolitan Territory of France','Jacques Chirac',NULL,'TF'); INSERT INTO t1 VALUES ('UMI','United States Minor Outlying Islands','Oceania','Micronesia/Caribbean',16.00,0,0,NULL,0.00,NULL,'United States Minor Outlying Islands','Dependent Territory of the US','George W. Bush',NULL,'UM'); /*!40000 ALTER TABLE t1 ENABLE KEYS */; -SELECT DISTINCT Continent AS c FROM t1 outr WHERE - Code <> SOME ( SELECT Code FROM t1 WHERE Continent = outr.Continent AND +SELECT DISTINCT Continent AS c FROM t1 outr WHERE + Code <> SOME ( SELECT Code FROM t1 WHERE Continent = outr.Continent AND Population < 200); drop table t1; + # -# Test for BUG#7885: Server crash when 'any' subselect compared to -# non-existant field. +# Test for Bug#7885 Server crash when 'any' subselect compared to +# non-existant field. # create table t1 (a1 int); create table t2 (b1 int); @@ -1604,8 +1618,9 @@ select 1 = ALL (select 1 from t1 where 1 = xx ), 1 as xx; select 1 = ALL (select 1 from t1 where 1 = xx ), 1 as xx from DUAL; drop table t1; + # -# Test for BUG#8218 +# Test for Bug#8218 Join does not pass string from right table # CREATE TABLE t1 ( categoryId int(11) NOT NULL, @@ -1675,38 +1690,39 @@ select count(distinct t2.userid) pass, groupstuff.*, count(t2.courseid) crse, - t1.categoryid, + t1.categoryid, t2.courseid, date_format(date, '%b%y') as colhead -from t2 -join t1 on t2.courseid=t1.courseid +from t2 +join t1 on t2.courseid=t1.courseid join ( - select - t5.userid, - parentid, - parentgroup, - childid, - groupname, - grouptypeid - from t5 - join + select + t5.userid, + parentid, + parentgroup, + childid, + groupname, + grouptypeid + from t5 + join ( - select t4.id as parentid, - t4.name as parentgroup, - t4.id as childid, - t4.name as groupname, - t4.grouptypeid - from t4 - ) as gin on t5.groupid=gin.childid -) as groupstuff on t2.userid = groupstuff.userid -group by + select t4.id as parentid, + t4.name as parentgroup, + t4.id as childid, + t4.name as groupname, + t4.grouptypeid + from t4 + ) as gin on t5.groupid=gin.childid +) as groupstuff on t2.userid = groupstuff.userid +group by groupstuff.groupname, colhead , t2.courseid; drop table t1, t2, t3, t4, t5; + # -# Transformation in left expression of subquery (BUG#8888) +# Transformation in left expression of subquery (Bug#8888) # create table t1 (a int); insert into t1 values (1), (2), (3); @@ -1751,8 +1767,9 @@ select (1,2,3) = (select * from t1); select (select * from t1) = (1,2,3); drop table t1; + # -# Item_int_with_ref check (BUG#10020) +# Item_int_with_ref check (Bug#10020) # CREATE TABLE `t1` ( `itemid` bigint(20) unsigned NOT NULL auto_increment, @@ -1775,15 +1792,16 @@ INSERT INTO `t2` VALUES (1, 1, 1, '10.10.10.1'); SELECT s.ip, count( e.itemid ) FROM `t1` e JOIN t2 s ON s.sessionid = e.sessionid WHERE e.sessionid = ( SELECT sessionid FROM t2 ORDER BY sessionid DESC LIMIT 1 ) GROUP BY s.ip HAVING count( e.itemid ) >0 LIMIT 0 , 30; drop tables t1,t2; -# BUG#11821 : Select from subselect using aggregate function on an enum -# segfaults: + +# Bug#11821 Select from subselect using aggregate function on an enum segfaults create table t1 (fld enum('0','1')); insert into t1 values ('1'); select * from (select max(fld) from t1) as foo; drop table t1; + # -# Bug #11867: queries with ROW(,elems>) IN (SELECT DISTINCT <cols> FROM ...) +# Bug#11867 queries with ROW(,elems>) IN (SELECT DISTINCT <cols> FROM ...) # CREATE TABLE t1 (one int, two int, flag char(1)); @@ -1813,8 +1831,9 @@ explain extended SELECT one,two from t1 where ROW(one,two) IN (SELECT one,two FR explain extended SELECT one,two,ROW(one,two) IN (SELECT one,two FROM t2 WHERE flag = '0' group by one,two) as 'test' from t1; DROP TABLE t1,t2; + # -# Bug #12392: where cond with IN predicate for rows and NULL values in table +# Bug#12392 where cond with IN predicate for rows and NULL values in table # CREATE TABLE t1 (a char(5), b char(5)); @@ -1824,8 +1843,9 @@ SELECT * FROM t1 WHERE (a,b) IN (('aaa','aaa'), ('aaa','bbb')); DROP TABLE t1; + # -# Bug #11479: subquery over left join with an empty inner table +# Bug#11479 subquery over left join with an empty inner table # CREATE TABLE t1 (a int); @@ -1842,9 +1862,10 @@ SELECT * FROM t1 DROP TABLE t1,t2,t3; + # -# Bug#18503: Queries with a quantified subquery returning empty set may -# return a wrong result. +# Bug#18503 Queries with a quantified subquery returning empty set may +# return a wrong result. # CREATE TABLE t1 (f1 INT); CREATE TABLE t2 (f2 INT); @@ -1856,8 +1877,9 @@ INSERT INTO t2 VALUES (2); SELECT * FROM t1 WHERE f1 > ALL (SELECT f2 FROM t2 WHERE f2=0); DROP TABLE t1, t2; + # -# Bug#16302: Quantified subquery without any tables gives wrong results +# Bug#16302 Quantified subquery without any tables gives wrong results # select 1 from dual where 1 < any (select 2); select 1 from dual where 1 < all (select 2); @@ -1866,7 +1888,8 @@ select 1 from dual where 2 > all (select 1); select 1 from dual where 1 < any (select 2 from dual); select 1 from dual where 1 < all (select 2 from dual where 1!=1); -# BUG#20975 Wrong query results for subqueries within NOT + +# Bug#20975 Wrong query results for subqueries within NOT create table t1 (s1 char); insert into t1 values (1),(2); @@ -1883,8 +1906,9 @@ select * from t1 where (s1 = ALL (select s1/s1 from t1)); select * from t1 where NOT(s1 = ALL (select s1/s1 from t1)); drop table t1; + # -# Bug #16255: Subquery in where +# Bug#16255 Subquery in where # create table t1 ( retailerID varchar(8) NOT NULL, @@ -1900,15 +1924,16 @@ INSERT INTO t1 VALUES("0037", "2", "2006-01-06 12:25:53"); INSERT INTO t1 VALUES("0048", "1", "2006-01-06 12:37:50"); INSERT INTO t1 VALUES("0059", "1", "2006-01-06 12:37:50"); -select * from t1 r1 - where (r1.retailerID,(r1.changed)) in - (SELECT r2.retailerId,(max(changed)) from t1 r2 +select * from t1 r1 + where (r1.retailerID,(r1.changed)) in + (SELECT r2.retailerId,(max(changed)) from t1 r2 group by r2.retailerId); drop table t1; + # -# Bug #21180: Subselect with index for both WHERE and ORDER BY -# produces empty result +# Bug#21180 Subselect with index for both WHERE and ORDER BY +# produces empty result # create table t1(a int, primary key (a)); insert into t1 values (10); @@ -1916,38 +1941,39 @@ insert into t1 values (10); create table t2 (a int primary key, b varchar(32), c int, unique key b(c, b)); insert into t2(a, c, b) values (1,10,'359'), (2,10,'35988'), (3,10,'35989'); -explain SELECT sql_no_cache t1.a, r.a, r.b FROM t1 LEFT JOIN t2 r - ON r.a = (SELECT t2.a FROM t2 WHERE t2.c = t1.a AND t2.b <= '359899' +explain SELECT sql_no_cache t1.a, r.a, r.b FROM t1 LEFT JOIN t2 r + ON r.a = (SELECT t2.a FROM t2 WHERE t2.c = t1.a AND t2.b <= '359899' ORDER BY t2.c DESC, t2.b DESC LIMIT 1) WHERE t1.a = 10; -SELECT sql_no_cache t1.a, r.a, r.b FROM t1 LEFT JOIN t2 r - ON r.a = (SELECT t2.a FROM t2 WHERE t2.c = t1.a AND t2.b <= '359899' +SELECT sql_no_cache t1.a, r.a, r.b FROM t1 LEFT JOIN t2 r + ON r.a = (SELECT t2.a FROM t2 WHERE t2.c = t1.a AND t2.b <= '359899' ORDER BY t2.c DESC, t2.b DESC LIMIT 1) WHERE t1.a = 10; -explain SELECT sql_no_cache t1.a, r.a, r.b FROM t1 LEFT JOIN t2 r - ON r.a = (SELECT t2.a FROM t2 WHERE t2.c = t1.a AND t2.b <= '359899' +explain SELECT sql_no_cache t1.a, r.a, r.b FROM t1 LEFT JOIN t2 r + ON r.a = (SELECT t2.a FROM t2 WHERE t2.c = t1.a AND t2.b <= '359899' ORDER BY t2.c, t2.b LIMIT 1) WHERE t1.a = 10; -SELECT sql_no_cache t1.a, r.a, r.b FROM t1 LEFT JOIN t2 r - ON r.a = (SELECT t2.a FROM t2 WHERE t2.c = t1.a AND t2.b <= '359899' +SELECT sql_no_cache t1.a, r.a, r.b FROM t1 LEFT JOIN t2 r + ON r.a = (SELECT t2.a FROM t2 WHERE t2.c = t1.a AND t2.b <= '359899' ORDER BY t2.c, t2.b LIMIT 1) WHERE t1.a = 10; drop table t1,t2; + # -# Bug #21853: assert failure for a grouping query with -# an ALL/ANY quantified subquery in HAVING +# Bug#21853 assert failure for a grouping query with +# an ALL/ANY quantified subquery in HAVING # -CREATE TABLE t1 ( - field1 int NOT NULL, - field2 int NOT NULL, - field3 int NOT NULL, - PRIMARY KEY (field1,field2,field3) +CREATE TABLE t1 ( + field1 int NOT NULL, + field2 int NOT NULL, + field3 int NOT NULL, + PRIMARY KEY (field1,field2,field3) +); +CREATE TABLE t2 ( + fieldA int NOT NULL, + fieldB int NOT NULL, + PRIMARY KEY (fieldA,fieldB) ); -CREATE TABLE t2 ( - fieldA int NOT NULL, - fieldB int NOT NULL, - PRIMARY KEY (fieldA,fieldB) -); INSERT INTO t1 VALUES (1,1,1), (1,1,2), (1,2,1), (1,2,2), (1,2,3), (1,3,1); @@ -1959,19 +1985,20 @@ SELECT field1, field2, COUNT(*) SELECT field1, field2 FROM t1 GROUP BY field1, field2 - HAVING COUNT(*) >= ALL (SELECT fieldB + HAVING COUNT(*) >= ALL (SELECT fieldB FROM t2 WHERE fieldA = field1); SELECT field1, field2 FROM t1 GROUP BY field1, field2 - HAVING COUNT(*) < ANY (SELECT fieldB + HAVING COUNT(*) < ANY (SELECT fieldB FROM t2 WHERE fieldA = field1); DROP TABLE t1, t2; + # -# Bug #23478: not top-level IN subquery returning a non-empty result set -# with possible NULL values by index access from the outer query +# Bug#23478 not top-level IN subquery returning a non-empty result set +# with possible NULL values by index access from the outer query # CREATE TABLE t1(a int, INDEX (a)); @@ -1986,24 +2013,26 @@ SELECT a, a IN (SELECT a FROM t1) FROM t2; DROP TABLE t1,t2; + # -# Bug #11302: getObject() returns a String for a sub-query of type datetime +# Bug#11302 getObject() returns a String for a sub-query of type datetime # CREATE TABLE t1 (a DATETIME); INSERT INTO t1 VALUES ('1998-09-23'), ('2003-03-25'); -CREATE TABLE t2 AS SELECT - (SELECT a FROM t1 WHERE a < '2000-01-01') AS sub_a +CREATE TABLE t2 AS SELECT + (SELECT a FROM t1 WHERE a < '2000-01-01') AS sub_a FROM t1 WHERE a > '2000-01-01'; SHOW CREATE TABLE t2; -CREATE TABLE t3 AS (SELECT a FROM t1 WHERE a < '2000-01-01') UNION (SELECT a FROM t1 WHERE a > '2000-01-01'); +CREATE TABLE t3 AS (SELECT a FROM t1 WHERE a < '2000-01-01') UNION (SELECT a FROM t1 WHERE a > '2000-01-01'); SHOW CREATE TABLE t3; DROP TABLE t1,t2,t3; + # -# Bug 24670: subquery witout tables but with a WHERE clause +# Bug#24670 subquery witout tables but with a WHERE clause # CREATE TABLE t1 (a int); @@ -2015,9 +2044,10 @@ EXPLAIN SELECT a FROM t1 WHERE (SELECT 1 FROM DUAL WHERE 1=0) IS NULL; DROP TABLE t1; + # -# Bug 24653: sorting by expressions containing subselects -# that return more than one row +# Bug#24653 sorting by expressions containing subselects +# that return more than one row # CREATE TABLE t1 (a int); @@ -2100,8 +2130,9 @@ select * from t1; select min(a) from t1 group by grp; drop table t1; + # -# Test for bug #9338: lame substitution of c1 instead of c2 +# Test for Bug#9338 lame substitution of c1 instead of c2 # CREATE table t1 ( c1 integer ); @@ -2121,15 +2152,16 @@ SELECT * FROM t1 LEFT JOIN t2 ON c1 = c2 DROP TABLE t1,t2; + # -# Test for bug #9516: wrong evaluation of not_null_tables attribute in SQ +# Test for Bug#9516 wrong evaluation of not_null_tables attribute in SQ # CREATE TABLE t1 ( c1 integer ); INSERT INTO t1 VALUES ( 1 ); INSERT INTO t1 VALUES ( 2 ); INSERT INTO t1 VALUES ( 3 ); -INSERT INTO t1 VALUES ( 6 ); - +INSERT INTO t1 VALUES ( 6 ); + CREATE TABLE t2 ( c2 integer ); INSERT INTO t2 VALUES ( 1 ); INSERT INTO t2 VALUES ( 4 ); @@ -2140,13 +2172,14 @@ CREATE TABLE t3 ( c3 integer ); INSERT INTO t3 VALUES ( 7 ); INSERT INTO t3 VALUES ( 8 ); -SELECT c1,c2 FROM t1 LEFT JOIN t2 ON c1 = c2 +SELECT c1,c2 FROM t1 LEFT JOIN t2 ON c1 = c2 WHERE EXISTS (SELECT c3 FROM t3 WHERE c2 IS NULL ); DROP TABLE t1,t2,t3; + # -# Item_int_with_ref check (BUG#10020) +# Item_int_with_ref check (Bug#10020) # CREATE TABLE `t1` ( `itemid` bigint(20) unsigned NOT NULL auto_increment, @@ -2169,9 +2202,10 @@ INSERT INTO `t2` VALUES (1, 1, 1, '10.10.10.1'); SELECT s.ip, count( e.itemid ) FROM `t1` e JOIN t2 s ON s.sessionid = e.sessionid WHERE e.sessionid = ( SELECT sessionid FROM t2 ORDER BY sessionid DESC LIMIT 1 ) GROUP BY s.ip HAVING count( e.itemid ) >0 LIMIT 0 , 30; drop tables t1,t2; + # # Correct building of equal fields list (do not include outer -# fields) (BUG#6384) +# fields) (Bug#6384) # CREATE TABLE t1 (EMPNUM CHAR(3)); CREATE TABLE t2 (EMPNUM CHAR(3) ); @@ -2185,44 +2219,46 @@ WHERE t1.EMPNUM NOT IN select * from t1; DROP TABLE t1,t2; + # -# Test for bug #11487: range access in a subquery +# Test for Bug#11487 range access in a subquery # CREATE TABLE t1(select_id BIGINT, values_id BIGINT); INSERT INTO t1 VALUES (1, 1); -CREATE TABLE t2 (select_id BIGINT, values_id BIGINT, +CREATE TABLE t2 (select_id BIGINT, values_id BIGINT, PRIMARY KEY(select_id,values_id)); INSERT INTO t2 VALUES (0, 1), (0, 2), (0, 3), (1, 5); -SELECT values_id FROM t1 +SELECT values_id FROM t1 WHERE values_id IN (SELECT values_id FROM t2 WHERE select_id IN (1, 0)); -SELECT values_id FROM t1 +SELECT values_id FROM t1 WHERE values_id IN (SELECT values_id FROM t2 WHERE select_id BETWEEN 0 AND 1); -SELECT values_id FROM t1 +SELECT values_id FROM t1 WHERE values_id IN (SELECT values_id FROM t2 WHERE select_id = 0 OR select_id = 1); DROP TABLE t1, t2; -# BUG#11821 : Select from subselect using aggregate function on an enum -# segfaults: + +# Bug#11821 Select from subselect using aggregate function on an enum segfaults create table t1 (fld enum('0','1')); insert into t1 values ('1'); select * from (select max(fld) from t1) as foo; drop table t1; + # -# Test for bug #11762: subquery with an aggregate function in HAVING +# Test for Bug#11762 subquery with an aggregate function in HAVING # CREATE TABLE t1 (a int, b int); CREATE TABLE t2 (c int, d int); CREATE TABLE t3 (e int); -INSERT INTO t1 VALUES +INSERT INTO t1 VALUES (1,10), (2,10), (1,20), (2,20), (3,20), (2,30), (4,40); INSERT INTO t2 VALUES (2,10), (2,20), (4,10), (5,10), (3,20), (2,40); @@ -2252,7 +2288,7 @@ SELECT a FROM t1 GROUP BY a WHERE EXISTS(SELECT e FROM t3 WHERE MAX(b)=e AND e < d)); SELECT a FROM t1 GROUP BY a HAVING a IN (SELECT c FROM t2 - WHERE MIN(b) < d AND + WHERE MIN(b) < d AND EXISTS(SELECT e FROM t3 WHERE MAX(b)=e AND e <= d)); SELECT a, SUM(a) FROM t1 GROUP BY a; @@ -2280,9 +2316,9 @@ SELECT t1.a FROM t1 GROUP BY t1.a -- error ER_INVALID_GROUP_FUNC_USE SELECT t1.a FROM t1 GROUP BY t1.a HAVING t1.a > ALL(SELECT t2.c FROM t2 - WHERE EXISTS(SELECT t3.e FROM t3 + WHERE EXISTS(SELECT t3.e FROM t3 WHERE SUM(t1.a+t2.c) < t3.e/4)); --- error ER_INVALID_GROUP_FUNC_USE +-- error ER_INVALID_GROUP_FUNC_USE SELECT t1.a from t1 GROUP BY t1.a HAVING AVG(SUM(t1.b)) > 20; SELECT t1.a FROM t1 GROUP BY t1.a @@ -2298,9 +2334,10 @@ SELECT t1.a, SUM(b) AS sum FROM t1 GROUP BY t1.a DROP TABLE t1,t2,t3; + # -# Test for bug #16603: GROUP BY in a row subquery with a quantifier -# when an index is defined on the grouping field +# Test for Bug#16603 GROUP BY in a row subquery with a quantifier +# when an index is defined on the grouping field CREATE TABLE t1 (a varchar(5), b varchar(10)); INSERT INTO t1 VALUES @@ -2319,27 +2356,30 @@ SELECT * FROM t1 WHERE (a,b) = ANY (SELECT a, max(b) FROM t1 GROUP BY a); DROP TABLE t1; + # -# Bug#17366: Unchecked Item_int results in server crash +# Bug#17366 Unchecked Item_int results in server crash # create table t1( f1 int,f2 int); insert into t1 values (1,1),(2,2); select tt.t from (select 'crash1' as t, f2 from t1) as tt left join t1 on tt.t = 'crash2' and tt.f2 = t1.f2 where tt.t = 'crash1'; drop table t1; + # -# Bug #18306: server crash on delete using subquery. +# Bug#18306 server crash on delete using subquery. # -create table t1 (c int, key(c)); +create table t1 (c int, key(c)); insert into t1 values (1142477582), (1142455969); create table t2 (a int, b int); insert into t2 values (2, 1), (1, 0); delete from t1 where c <= 1140006215 and (select b from t2 where a = 2) = 1; drop table t1, t2; + # -# Bug #7549: Missing error message for invalid view selection with subquery +# Bug#7549 Missing error message for invalid view selection with subquery # CREATE TABLE t1 (a INT); @@ -2353,16 +2393,18 @@ SELECT * FROM t1 WHERE no_such_column = ANY (SELECT 1); DROP TABLE t1; + # -# Bug#19077: A nested materialized derived table is used before being populated. +# Bug#19077 A nested materialized derived table is used before being populated. # create table t1 (i int, j bigint); insert into t1 values (1, 2), (2, 2), (3, 2); select * from (select min(i) from t1 where j=(select * from (select min(j) from t1) t2)) t3; drop table t1; -# -# Bug#19700: subselect returning BIGINT always returned it as SIGNED + +# +# Bug#19700 subselect returning BIGINT always returned it as SIGNED # CREATE TABLE t1 (i BIGINT UNSIGNED); INSERT INTO t1 VALUES (10000000000000000000); # > MAX SIGNED BIGINT 9323372036854775807 @@ -2384,8 +2426,9 @@ SELECT t1.i FROM t1 WHERE t1.i = CAST((SELECT MAX(i) FROM t2) AS UNSIGNED); DROP TABLE t1; DROP TABLE t2; -# -# Bug#20519: subselect with LIMIT M, N + +# +# Bug#20519 subselect with LIMIT M, N # CREATE TABLE t1 ( @@ -2402,7 +2445,7 @@ CREATE TABLE t2 ( date date NOT NULL, PRIMARY KEY (id) ); -INSERT INTO t2 VALUES +INSERT INTO t2 VALUES (1, 1, '2006-03-30'), (2, 2, '2006-04-06'), (3, 3, '2006-04-13'), (4, 2, '2006-04-20'), (5, 1, '2006-05-01'); @@ -2424,8 +2467,9 @@ SELECT *, FROM t1; DROP TABLE t1,t2; + # -# Bug#20869: subselect with range access by DESC +# Bug#20869 subselect with range access by DESC # CREATE TABLE t1 ( @@ -2434,7 +2478,7 @@ CREATE TABLE t1 ( t datetime NOT NULL default '0000-00-00 00:00:00', PRIMARY KEY (i1,i2,t) ); -INSERT INTO t1 VALUES +INSERT INTO t1 VALUES (24,1,'2005-03-03 16:31:31'),(24,1,'2005-05-27 12:40:07'), (24,1,'2005-05-27 12:40:08'),(24,1,'2005-05-27 12:40:10'), (24,1,'2005-05-27 12:40:25'),(24,1,'2005-05-27 12:40:30'), @@ -2452,34 +2496,34 @@ INSERT INTO t2 VALUES (24,1,'2006-06-20 12:29:40'); EXPLAIN SELECT * FROM t1,t2 - WHERE t1.t = (SELECT t1.t FROM t1 + WHERE t1.t = (SELECT t1.t FROM t1 WHERE t1.t < t2.t AND t1.i2=1 AND t2.i1=t1.i1 ORDER BY t1.t DESC LIMIT 1); SELECT * FROM t1,t2 - WHERE t1.t = (SELECT t1.t FROM t1 + WHERE t1.t = (SELECT t1.t FROM t1 WHERE t1.t < t2.t AND t1.i2=1 AND t2.i1=t1.i1 ORDER BY t1.t DESC LIMIT 1); DROP TABLE t1, t2; + # -# Bug#14654 : Cannot select from the same table twice within a UNION -# statement +# Bug#14654 Cannot select from the same table twice within a UNION statement # CREATE TABLE t1 (i INT); (SELECT i FROM t1) UNION (SELECT i FROM t1); #TODO:not supported --error ER_PARSE_ERROR -SELECT sql_no_cache * FROM t1 WHERE NOT EXISTS +SELECT sql_no_cache * FROM t1 WHERE NOT EXISTS ( - (SELECT i FROM t1) UNION + (SELECT i FROM t1) UNION (SELECT i FROM t1) ); #TODO:not supported --error ER_PARSE_ERROR -SELECT * FROM t1 +SELECT * FROM t1 WHERE NOT EXISTS (((SELECT i FROM t1) UNION (SELECT i FROM t1))); #TODO:not supported @@ -2489,14 +2533,15 @@ explain select ((select t11.i from t1 t11) union (select t12.i from t1 t12)) #TODO:not supported --error ER_PARSE_ERROR -explain select * from t1 where not exists +explain select * from t1 where not exists ((select t11.i from t1 t11) union (select t12.i from t1 t12)); DROP TABLE t1; + # -# Bug#21798: memory leak during query execution with subquery in column -# list using a function +# Bug#21798 memory leak during query execution with subquery in column +# list using a function # CREATE TABLE t1 (a VARCHAR(250), b INT auto_increment, PRIMARY KEY (b)); insert into t1 (a) values (FLOOR(rand() * 100)); @@ -2514,15 +2559,16 @@ insert into t1 (a) select FLOOR(rand() * 100) from t1; insert into t1 (a) select FLOOR(rand() * 100) from t1; insert into t1 (a) select FLOOR(rand() * 100) from t1; -SELECT a, - (SELECT REPEAT(' ',250) FROM t1 i1 - WHERE i1.b=t1.a ORDER BY RAND() LIMIT 1) AS a +SELECT a, + (SELECT REPEAT(' ',250) FROM t1 i1 + WHERE i1.b=t1.a ORDER BY RAND() LIMIT 1) AS a FROM t1 ORDER BY a LIMIT 5; DROP TABLE t1; + # -# Bug #21540: Subqueries with no from and aggregate functions return -# wrong results +# Bug#21540 Subqueries with no from and aggregate functions return +# wrong results CREATE TABLE t1 (a INT, b INT); CREATE TABLE t2 (a INT); INSERT INTO t2 values (1); @@ -2531,29 +2577,30 @@ SELECT (SELECT COUNT(DISTINCT t1.b) from t2) FROM t1 GROUP BY t1.a; SELECT (SELECT COUNT(DISTINCT t1.b) from t2 union select 1 from t2 where 12 < 3) FROM t1 GROUP BY t1.a; SELECT COUNT(DISTINCT t1.b), (SELECT COUNT(DISTINCT t1.b)) FROM t1 GROUP BY t1.a; -SELECT COUNT(DISTINCT t1.b), +SELECT COUNT(DISTINCT t1.b), (SELECT COUNT(DISTINCT t1.b) union select 1 from DUAL where 12 < 3) FROM t1 GROUP BY t1.a; SELECT ( SELECT ( SELECT COUNT(DISTINCT t1.b) ) -) +) FROM t1 GROUP BY t1.a; SELECT ( SELECT ( SELECT ( SELECT COUNT(DISTINCT t1.b) ) - ) - FROM t1 GROUP BY t1.a LIMIT 1) + ) + FROM t1 GROUP BY t1.a LIMIT 1) FROM t1 t2 GROUP BY t2.a; -DROP TABLE t1,t2; +DROP TABLE t1,t2; + # -# Bug #21727: Correlated subquery that requires filesort: -# slow with big sort_buffer_size +# Bug#21727 Correlated subquery that requires filesort: +# slow with big sort_buffer_size # CREATE TABLE t1 (a int, b int auto_increment, PRIMARY KEY (b)); @@ -2571,26 +2618,27 @@ while ($1) { eval INSERT INTO t2(y,z) VALUES(@id,RAND()*1000); dec $2; - } + } dec $1; } enable_query_log; SET SESSION sort_buffer_size = 32 * 1024; -SELECT SQL_NO_CACHE COUNT(*) +SELECT SQL_NO_CACHE COUNT(*) FROM (SELECT a, b, (SELECT x FROM t2 WHERE y=b ORDER BY z DESC LIMIT 1) c FROM t1) t; SET SESSION sort_buffer_size = 8 * 1024 * 1024; -SELECT SQL_NO_CACHE COUNT(*) +SELECT SQL_NO_CACHE COUNT(*) FROM (SELECT a, b, (SELECT x FROM t2 WHERE y=b ORDER BY z DESC LIMIT 1) c FROM t1) t; DROP TABLE t1,t2; + # -# Bug #25219: EXIST subquery with UNION over a mix of -# correlated and uncorrelated selects +# Bug#25219 EXIST subquery with UNION over a mix of +# correlated and uncorrelated selects # CREATE TABLE t1 (id char(4) PRIMARY KEY, c int); @@ -2622,10 +2670,11 @@ SELECT * FROM t1 DROP TABLE t1,t2,t3; -# -# Bug#23800: Outer fields in correlated subqueries is used in a temporary -# table created for sorting. -# + +# +# Bug#23800 Outer fields in correlated subqueries is used in a temporary +# table created for sorting. +# CREATE TABLE t1(f1 int); CREATE TABLE t2(f2 int, f21 int, f3 timestamp); INSERT INTO t1 VALUES (1),(1),(2),(2); @@ -2636,19 +2685,20 @@ PREPARE stmt1 FROM 'SELECT ((SELECT f2 FROM t2 WHERE f21=f1 LIMIT 1) * COUNT(f1) EXECUTE stmt1; EXECUTE stmt1; DEALLOCATE PREPARE stmt1; -SELECT f2, AVG(f21), +SELECT f2, AVG(f21), (SELECT t.f3 FROM t2 AS t WHERE t2.f2=t.f2 AND t.f3=MAX(t2.f3)) AS test FROM t2 GROUP BY f2; -DROP TABLE t1,t2; -CREATE TABLE t1 (a int, b INT, c CHAR(10) NOT NULL); -INSERT INTO t1 VALUES - (1,1,'a'), (1,2,'b'), (1,3,'c'), (1,4,'d'), (1,5,'e'), - (2,1,'f'), (2,2,'g'), (2,3,'h'), (3,4,'i'), (3,3,'j'), - (3,2,'k'), (3,1,'l'), (1,9,'m'); -SELECT a, MAX(b), - (SELECT t.c FROM t1 AS t WHERE t1.a=t.a AND t.b=MAX(t1.b)) AS test - FROM t1 GROUP BY a; -DROP TABLE t1; +DROP TABLE t1,t2; +CREATE TABLE t1 (a int, b INT, c CHAR(10) NOT NULL); +INSERT INTO t1 VALUES + (1,1,'a'), (1,2,'b'), (1,3,'c'), (1,4,'d'), (1,5,'e'), + (2,1,'f'), (2,2,'g'), (2,3,'h'), (3,4,'i'), (3,3,'j'), + (3,2,'k'), (3,1,'l'), (1,9,'m'); +SELECT a, MAX(b), + (SELECT t.c FROM t1 AS t WHERE t1.a=t.a AND t.b=MAX(t1.b)) AS test + FROM t1 GROUP BY a; +DROP TABLE t1; + # # Bug#21904 (parser problem when using IN with a double "(())") @@ -2749,21 +2799,23 @@ DROP TABLE t1; DROP TABLE t2; DROP TABLE t1xt2; + +# +# Bug#26728 derived table with concatanation of literals in select list # -# Bug #26728: derived table with concatanation of literals in select list -# CREATE TABLE t1 (a int); -INSERT INTO t1 VALUES (3), (1), (2); +INSERT INTO t1 VALUES (3), (1), (2); SELECT 'this is ' 'a test.' AS col1, a AS col2 FROM t1; SELECT * FROM (SELECT 'this is ' 'a test.' AS col1, a AS t2 FROM t1) t; DROP table t1; + +# +# Bug#27257 COUNT(*) aggregated in outer query # -# Bug #27257: COUNT(*) aggregated in outer query -# CREATE TABLE t1 (a int, b int); CREATE TABLE t2 (m int, n int); @@ -2778,15 +2830,16 @@ SELECT COUNT(*), a, (SELECT MIN(m) FROM t2 WHERE m = count(*)) FROM t1 GROUP BY a; -SELECT COUNT(*), a +SELECT COUNT(*), a FROM t1 GROUP BY a HAVING (SELECT MIN(m) FROM t2 WHERE m = count(*)) > 1; DROP TABLE t1,t2; + +# +# Bug#27229 GROUP_CONCAT in subselect with COUNT() as an argument # -# Bug #27229: GROUP_CONCAT in subselect with COUNT() as an argument -# CREATE TABLE t1 (a int, b int); CREATE TABLE t2 (m int, n int); @@ -2803,8 +2856,9 @@ SELECT COUNT(*) c, a, DROP table t1,t2; + # -# Bug#27321: Wrong subquery result in a grouping select +# Bug#27321 Wrong subquery result in a grouping select # CREATE TABLE t1 (a int, b INT, d INT, c CHAR(10) NOT NULL, PRIMARY KEY (a, b)); INSERT INTO t1 VALUES (1,1,0,'a'), (1,2,0,'b'), (1,3,0,'c'), (1,4,0,'d'), @@ -2812,7 +2866,7 @@ INSERT INTO t1 VALUES (1,1,0,'a'), (1,2,0,'b'), (1,3,0,'c'), (1,4,0,'d'), (3,2,0,'k'), (3,1,0,'l'), (1,9,0,'m'), (1,0,10,'n'), (2,0,5,'o'), (3,0,7,'p'); SELECT a, MAX(b), - (SELECT t.c FROM t1 AS t WHERE t1.a=t.a AND t.b=MAX(t1.b + 0)) as test + (SELECT t.c FROM t1 AS t WHERE t1.a=t.a AND t.b=MAX(t1.b + 0)) as test FROM t1 GROUP BY a; SELECT a x, MAX(b), (SELECT t.c FROM t1 AS t WHERE x=t.a AND t.b=MAX(t1.b + 0)) as test @@ -2823,25 +2877,27 @@ SELECT a, AVG(b), SELECT tt.a, (SELECT (SELECT c FROM t1 as t WHERE t1.a=t.a AND t.d=MAX(t1.b + tt.a) - LIMIT 1) FROM t1 WHERE t1.a=tt.a GROUP BY a LIMIT 1) as test + LIMIT 1) FROM t1 WHERE t1.a=tt.a GROUP BY a LIMIT 1) as test FROM t1 as tt; SELECT tt.a, (SELECT (SELECT t.c FROM t1 AS t WHERE t1.a=t.a AND t.d=MAX(t1.b + tt.a) LIMIT 1) - FROM t1 WHERE t1.a=tt.a GROUP BY a LIMIT 1) as test + FROM t1 WHERE t1.a=tt.a GROUP BY a LIMIT 1) as test FROM t1 as tt GROUP BY tt.a; SELECT tt.a, MAX( (SELECT (SELECT t.c FROM t1 AS t WHERE t1.a=t.a AND t.d=MAX(t1.b + tt.a) LIMIT 1) - FROM t1 WHERE t1.a=tt.a GROUP BY a LIMIT 1)) as test + FROM t1 WHERE t1.a=tt.a GROUP BY a LIMIT 1)) as test FROM t1 as tt GROUP BY tt.a; DROP TABLE t1; + + +# +# Bug#27348 SET FUNCTION used in a subquery from WHERE condition # -# Bug #27348: SET FUNCTION used in a subquery from WHERE condition -# CREATE TABLE t1 (a int, b int); INSERT INTO t1 VALUES (2,22),(1,11),(2,22); @@ -2866,9 +2922,9 @@ SET @@sql_mode=default; DROP TABLE t1; + # -# Bug #27363: nested aggregates in outer, subquery / sum(select -# count(outer)) +# Bug#27363 nested aggregates in outer, subquery / sum(select count(outer)) # CREATE TABLE t1 (a INT); INSERT INTO t1 values (1),(1),(1),(1); CREATE TABLE t2 (x INT); INSERT INTO t1 values (1000),(1001),(1002); @@ -2883,28 +2939,30 @@ SELECT COUNT(1) FROM DUAL; SELECT SUM( (SELECT AVG( (SELECT t1.a FROM t2) ) FROM DUAL) ) FROM t1; --error ER_INVALID_GROUP_FUNC_USE -SELECT +SELECT SUM( (SELECT AVG( (SELECT COUNT(*) FROM t1 t HAVING t1.a < 12) ) FROM t2) ) FROM t1; --error ER_INVALID_GROUP_FUNC_USE -SELECT t1.a as XXA, +SELECT t1.a as XXA, SUM( (SELECT AVG( (SELECT COUNT(*) FROM t1 t HAVING XXA < 12) ) FROM t2) ) FROM t1; DROP TABLE t1,t2; + # -# Bug #27807: Server crash when executing subquery with EXPLAIN -# -CREATE TABLE t1 (a int, b int, KEY (a)); +# Bug#27807 Server crash when executing subquery with EXPLAIN +# +CREATE TABLE t1 (a int, b int, KEY (a)); INSERT INTO t1 VALUES (1,1),(2,1); EXPLAIN SELECT 1 FROM t1 WHERE a = (SELECT COUNT(*) FROM t1 GROUP BY b); DROP TABLE t1; + +# +# Bug#28377 grouping query with a correlated subquery in WHERE condition # -# Bug #28377: grouping query with a correlated subquery in WHERE condition -# CREATE TABLE t1 (id int NOT NULL, st CHAR(2), INDEX idx(id)); INSERT INTO t1 VALUES @@ -2912,24 +2970,25 @@ INSERT INTO t1 VALUES CREATE TABLE t2 (id int NOT NULL, INDEX idx(id)); INSERT INTO t2 VALUES (7), (5), (1), (3); -SELECT id, st FROM t1 +SELECT id, st FROM t1 WHERE st IN ('GA','FL') AND EXISTS(SELECT 1 FROM t2 WHERE t2.id=t1.id); -SELECT id, st FROM t1 +SELECT id, st FROM t1 WHERE st IN ('GA','FL') AND EXISTS(SELECT 1 FROM t2 WHERE t2.id=t1.id) GROUP BY id; -SELECT id, st FROM t1 +SELECT id, st FROM t1 WHERE st IN ('GA','FL') AND NOT EXISTS(SELECT 1 FROM t2 WHERE t2.id=t1.id); -SELECT id, st FROM t1 +SELECT id, st FROM t1 WHERE st IN ('GA','FL') AND NOT EXISTS(SELECT 1 FROM t2 WHERE t2.id=t1.id) GROUP BY id; DROP TABLE t1,t2; + +# +# Bug#28728 crash with EXPLAIN EXTENDED for a query with a derived table +# over a grouping subselect # -# Bug #28728: crash with EXPLAIN EXTENDED for a query with a derived table -# over a grouping subselect -# CREATE TABLE t1 (a int); @@ -2940,10 +2999,11 @@ SELECT * FROM (SELECT count(*) FROM t1 GROUP BY a) as res; DROP TABLE t1; + # -# Bug #28811: crash for query containing subquery with ORDER BY and LIMIT 1 +# Bug#28811 crash for query containing subquery with ORDER BY and LIMIT 1 # - + CREATE TABLE t1 ( a varchar(255) default NULL, b timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP, @@ -2974,8 +3034,8 @@ DROP TABLE t1,t2; # -# Bug #27333: subquery grouped for aggregate of outer query / no aggregate -# of subquery +# Bug#27333 subquery grouped for aggregate of outer query / no aggregate +# of subquery # CREATE TABLE t1 (a INTEGER, b INTEGER); CREATE TABLE t2 (x INTEGER); @@ -3015,7 +3075,7 @@ SELECT (SELECT SUM(t1.a) FROM t2 WHERE a=1) FROM t1; DROP TABLE t1,t2; # -# Bug #31884: Assertion + crash in subquery in the SELECT clause. +# Bug#31884 Assertion + crash in subquery in the SELECT clause. # CREATE TABLE t1 (a1 INT, a2 INT); @@ -3030,7 +3090,7 @@ SELECT ((a1,a2) IN (SELECT * FROM t2 WHERE b2 > 0)) IS NULL FROM t1; DROP TABLE t1, t2; # -# Bug #28076: inconsistent binary/varbinary comparison +# Bug#28076 inconsistent binary/varbinary comparison # CREATE TABLE t1 (s1 BINARY(5), s2 VARBINARY(5)); @@ -3071,8 +3131,9 @@ SELECT LEFT(t1.a1,1) FROM t1,t3 WHERE t1.b1=t3.a3; SELECT a2 FROM t2 WHERE t2.a2 IN (SELECT t1.a1 FROM t1,t3 WHERE t1.b1=t3.a3); DROP TABLE t1, t2, t3; + # -# Bug #30788: Inconsistent retrieval of char/varchar +# Bug#30788 Inconsistent retrieval of char/varchar # CREATE TABLE t1 (a CHAR(1), b VARCHAR(10)); @@ -3096,16 +3157,16 @@ SELECT a,b FROM t1 WHERE b IN (SELECT a FROM t1 WHERE LENGTH(a)<500); DROP TABLE t1,t2; + # -# Bug #32400: Complex SELECT query returns correct result only on some -# occasions +# Bug#32400 Complex SELECT query returns correct result only on some occasions # CREATE TABLE t1(a INT, b INT); INSERT INTO t1 VALUES (1,1), (1,2), (2,3), (2,4); --error ER_BAD_FIELD_ERROR -EXPLAIN +EXPLAIN SELECT a AS out_a, MIN(b) FROM t1 WHERE b > (SELECT MIN(b) FROM t1 WHERE a = out_a) GROUP BY a; @@ -3115,7 +3176,7 @@ SELECT a AS out_a, MIN(b) FROM t1 WHERE b > (SELECT MIN(b) FROM t1 WHERE a = out_a) GROUP BY a; -EXPLAIN +EXPLAIN SELECT a AS out_a, MIN(b) FROM t1 t1_outer WHERE b > (SELECT MIN(b) FROM t1 WHERE a = t1_outer.a) GROUP BY a; @@ -3128,7 +3189,7 @@ DROP TABLE t1; # -# Bug #32036: EXISTS within a WHERE clause with a UNION crashes MySQL 5.122 +# Bug#32036 EXISTS within a WHERE clause with a UNION crashes MySQL 5.122 # CREATE TABLE t1 (a INT); @@ -3145,14 +3206,15 @@ SELECT 2 FROM t1 WHERE EXISTS ((SELECT 1 FROM t2 WHERE t1.a=t2.a)); #TODO:not supported --error ER_PARSE_ERROR EXPLAIN EXTENDED -SELECT 2 FROM t1 WHERE EXISTS ((SELECT 1 FROM t2 WHERE t1.a=t2.a) UNION +SELECT 2 FROM t1 WHERE EXISTS ((SELECT 1 FROM t2 WHERE t1.a=t2.a) UNION (SELECT 1 FROM t2 WHERE t1.a = t2.a)); DROP TABLE t1,t2; + # -# Bug#33675: Usage of an uninitialized memory by filesort in a subquery -# caused server crash. +# Bug#33675 Usage of an uninitialized memory by filesort in a subquery +# caused server crash. # create table t1(f11 int, f12 int); create table t2(f21 int unsigned not null, f22 int, f23 varchar(10)); @@ -3168,13 +3230,14 @@ while ($i) --enable_warnings --enable_query_log set session sort_buffer_size= 33*1024; -select count(*) from t1 where f12 = +select count(*) from t1 where f12 = (select f22 from t2 where f22 = f12 order by f21 desc, f22, f23 limit 1); drop table t1,t2; + # -# BUG#33794 "MySQL crashes executing specific query on specific dump" +# Bug#33794 "MySQL crashes executing specific query on specific dump" # CREATE TABLE t4 ( f7 varchar(32) collate utf8_bin NOT NULL default '', @@ -3216,24 +3279,25 @@ SELECT FROM t2 VPC, t4 a2, t2 a3 WHERE VPC.f4 = a2.f10 AND a3.f2 = a4 - LIMIT 1) IS NULL, - 0, + LIMIT 1) IS NULL, + 0, t3.f5 ) ) AS a6 -FROM +FROM t2, t3, t1 JOIN t2 a1 ON t1.f9 = a1.f4 GROUP BY a4; DROP TABLE t1, t2, t3, t4; + # -# BUG#36139 "float, zerofill, crash with subquery" +# Bug#36139 "float, zerofill, crash with subquery" # create table t1 (a float(5,4) zerofill); create table t2 (a float(5,4),b float(2,0)); -select t1.a from t1 where +select t1.a from t1 where t1.a= (select b from t2 limit 1) and not t1.a= (select a from t2 limit 1) ; @@ -3241,8 +3305,8 @@ drop table t1, t2; # -# Bug #36011: Server crash with explain extended on query with dependent -# subqueries +# Bug#36011 Server crash with explain extended on query with dependent +# subqueries # CREATE TABLE t1 (a INT); @@ -3254,10 +3318,10 @@ DROP TABLE t1; --echo End of 5.0 tests. # -# Bug #27348: SET FUNCTION used in a subquery from WHERE condition -# +# Bug#27348 SET FUNCTION used in a subquery from WHERE condition +# -CREATE TABLE t1 (a int, b int); +CREATE TABLE t1 (a INT, b INT); INSERT INTO t1 VALUES (2,22),(1,11),(2,22); SELECT a FROM t1 WHERE (SELECT COUNT(b) FROM DUAL) > 0 GROUP BY a; @@ -3282,17 +3346,17 @@ DROP TABLE t1; # # Bug#20835 (literal string with =any values) # -CREATE TABLE t1 (s1 char(1)); +CREATE TABLE t1 (s1 CHAR(1)); INSERT INTO t1 VALUES ('a'); SELECT * FROM t1 WHERE _utf8'a' = ANY (SELECT s1 FROM t1); DROP TABLE t1; # -# Bug #37004: NOT IN subquery with MAX over an empty set +# Bug#37004 NOT IN subquery with MAX over an empty set # -CREATE TABLE t1(c int, KEY(c)); -CREATE TABLE t2(a int, b int); +CREATE TABLE t1(c INT, KEY(c)); +CREATE TABLE t2(a INT, b INT); INSERT INTO t2 VALUES (1, 10), (2, NULL); INSERT INTO t1 VALUES (1), (3); @@ -3301,30 +3365,30 @@ SELECT * FROM t2 WHERE b NOT IN (SELECT max(t.c) FROM t1, t1 t WHERE t.c>10); DROP TABLE t1,t2; # -# Bug #38191: Server crash with subquery containing DISTINCT and ORDER BY +# Bug#38191 Server crash with subquery containing DISTINCT and ORDER BY # -CREATE TABLE t1(pk int PRIMARY KEY, a int, INDEX idx(a)); +CREATE TABLE t1(pk INT PRIMARY KEY, a INT, INDEX idx(a)); INSERT INTO t1 VALUES (1, 10), (3, 30), (2, 20); -CREATE TABLE t2(pk int PRIMARY KEY, a int, b int, INDEX idxa(a)); +CREATE TABLE t2(pk INT PRIMARY KEY, a INT, b INT, INDEX idxa(a)); INSERT INTO t2 VALUES (2, 20, 700), (1, 10, 200), (4, 10, 100); SELECT * FROM t1 WHERE EXISTS (SELECT DISTINCT a FROM t2 WHERE t1.a < t2.a ORDER BY b); DROP TABLE t1,t2; # -# Bug #37548: result value erronously reported being NULL in certain subqueries +# Bug#37548 result value erronously reported being NULL in certain subqueries # -CREATE TABLE t1 (a int, b int, PRIMARY KEY (a), KEY b (b)); +CREATE TABLE t1 (a INT, b INT, PRIMARY KEY (a), KEY b (b)); INSERT INTO t1 VALUES (1,NULL), (9,NULL); CREATE TABLE t2 ( - a int, - b int, - c int, - d int, + a INT, + b INT, + c INT, + d INT, PRIMARY KEY (a), UNIQUE KEY b (b,c,d), KEY b_2 (b), @@ -3332,7 +3396,7 @@ CREATE TABLE t2 ( KEY d (d) ); -INSERT INTO t2 VALUES +INSERT INTO t2 VALUES (43, 2, 11 ,30), (44, 2, 12 ,30), (45, 1, 1 ,10000), @@ -3340,9 +3404,9 @@ INSERT INTO t2 VALUES (556,1, 32 ,10000); CREATE TABLE t3 ( - a int, - b int, - c int, + a INT, + b INT, + c INT, PRIMARY KEY (a), UNIQUE KEY b (b,c), KEY c (c), @@ -3351,7 +3415,7 @@ CREATE TABLE t3 ( INSERT INTO t3 VALUES (1,1,1), (2,32,1); -explain +explain SELECT t1.a, (SELECT 1 FROM t2 WHERE t2.b=t3.c AND t2.c=t1.a ORDER BY t2.d LIMIT 1) AS incorrect FROM t1, t3 WHERE t3.b=t1.a; SELECT t1.a, (SELECT 1 FROM t2 WHERE t2.b=t3.c AND t2.c=t1.a ORDER BY t2.d LIMIT 1) AS incorrect FROM t1, t3 WHERE t3.b=t1.a; @@ -3370,22 +3434,22 @@ INSERT INTO t1 (id) VALUES (1); INSERT INTO t2 (id) VALUES (1); CREATE VIEW v1 AS - SELECT t2.c AS c FROM t1, t2 - WHERE t1.id=t2.id AND 1 IN (SELECT id FROM t1) WITH CHECK OPTION; +SELECT t2.c AS c FROM t1, t2 +WHERE t1.id=t2.id AND 1 IN (SELECT id FROM t1) WITH CHECK OPTION; UPDATE v1 SET c=1; CREATE VIEW v2 (a,b) AS - SELECT t2.id, t2.c AS c FROM t1, t2 - WHERE t1.id=t2.id AND 1 IN (SELECT id FROM t1) WITH CHECK OPTION; +SELECT t2.id, t2.c AS c FROM t1, t2 +WHERE t1.id=t2.id AND 1 IN (SELECT id FROM t1) WITH CHECK OPTION; ---error 1369 +--error ER_VIEW_CHECK_FAILED INSERT INTO v2(a,b) VALUES (2,2); INSERT INTO v2(a,b) VALUES (1,2); SELECT * FROM v1; CREATE VIEW v3 AS - SELECT t2.c AS c FROM t2 - WHERE 1 IN (SELECT id FROM t1) WITH CHECK OPTION; +SELECT t2.c AS c FROM t2 +WHERE 1 IN (SELECT id FROM t1) WITH CHECK OPTION; DELETE FROM v3; @@ -3393,7 +3457,7 @@ DROP VIEW v1,v2,v3; DROP TABLE t1,t2; --echo # ---echo # BUG#37822: Correlated subquery with IN and IS UNKNOWN provides wrong result +--echo # Bug#37822 Correlated subquery with IN and IS UNKNOWN provides wrong result --echo # create table t1(id integer primary key, g integer, v integer, s char(1)); create table t2(id integer primary key, g integer, v integer, s char(1)); diff --git a/mysql-test/t/synchronization.test b/mysql-test/t/synchronization.test index 71e13a65ec3..aef06245717 100644 --- a/mysql-test/t/synchronization.test +++ b/mysql-test/t/synchronization.test @@ -1,10 +1,13 @@ +# Save the initial number of concurrent sessions +--source include/count_sessions.inc + # -# Test for Bug #2385 CREATE TABLE LIKE lacks locking on source and destination -# table +# Test for Bug#2385 CREATE TABLE LIKE lacks locking on source and destination +# table # --disable_warnings -drop table if exists t1,t2; +DROP TABLE IF EXISTS t1,t2; --enable_warnings connect (con1,localhost,root,,); @@ -12,12 +15,12 @@ connect (con2,localhost,root,,); # locking of source: -CREATE TABLE t1 (x1 int); +CREATE TABLE t1 (x1 INT); let $1= 10; while ($1) { connection con1; - send ALTER TABLE t1 CHANGE x1 x2 int; + send ALTER TABLE t1 CHANGE x1 x2 INT; connection con2; CREATE TABLE t2 LIKE t1; replace_result x1 xx x2 xx; @@ -25,7 +28,7 @@ while ($1) DROP TABLE t2; connection con1; reap; - send ALTER TABLE t1 CHANGE x2 x1 int; + send ALTER TABLE t1 CHANGE x2 x1 INT; connection con2; CREATE TABLE t2 LIKE t1; replace_result x1 xx x2 xx; @@ -37,4 +40,11 @@ while ($1) } DROP TABLE t1; +connection default; +disconnect con1; +disconnect con2; + # End of 4.1 tests + +# Wait till we reached the initial number of concurrent sessions +--source include/wait_until_count_sessions.inc diff --git a/mysql-test/t/timezone_grant.test b/mysql-test/t/timezone_grant.test index 450c1edc47e..8013f2b04ce 100644 --- a/mysql-test/t/timezone_grant.test +++ b/mysql-test/t/timezone_grant.test @@ -1,15 +1,18 @@ # Embedded server testing does not support grants -- source include/not_embedded.inc +# Save the initial number of concurrent sessions +--source include/count_sessions.inc + --disable_warnings drop tables if exists t1, t2; drop view if exists v1; --enable_warnings # -# Test for bug #6116 "SET time_zone := ... requires access to mysql.time_zone -# tables". We should allow implicit access to time zone description tables -# even for unprivileged users. +# Test for Bug#6116 SET time_zone := ... requires access to mysql.time_zone tables +# We should allow implicit access to time zone description tables even for +# unprivileged users. # # Let us prepare playground @@ -33,18 +36,20 @@ select convert_tz(b, 'Europe/Moscow', 'UTC') from t1; update t1, t2 set t1.b = convert_tz('2004-10-21 19:00:00', 'Europe/Moscow', 'UTC') where t1.a = t2.c and t2.d = (select max(d) from t2); # But still these two statements should not work: ---error 1142 +--error ER_TABLEACCESS_DENIED_ERROR select * from mysql.time_zone_name; ---error 1142 +--error ER_TABLEACCESS_DENIED_ERROR select Name, convert_tz('2004-10-21 19:00:00', Name, 'UTC') from mysql.time_zone_name; +connection default; +disconnect tzuser; + # -# Test for bug #6765 "Implicit access to time zone description tables -# requires privileges for them if some table or column level grants -# present" +# Bug#6765 Implicit access to time zone description tables requires privileges +# for them if some table or column level grants present # connection default; -# Let use some table-level grants instead of db-level +# Let use some table-level grants instead of db-level # to make life more interesting delete from mysql.db where user like 'mysqltest\_%'; flush privileges; @@ -61,14 +66,14 @@ select convert_tz(b, 'Europe/Moscow', 'UTC') from t1; update t1, t2 set t1.b = convert_tz('2004-11-30 12:00:00', 'Europe/Moscow', 'UTC') where t1.a = t2.c and t2.d = (select max(d) from t2); # Again these two statements should not work (but with different errors): ---error 1142 +--error ER_TABLEACCESS_DENIED_ERROR select * from mysql.time_zone_name; ---error 1142 +--error ER_TABLEACCESS_DENIED_ERROR select Name, convert_tz('2004-11-30 12:00:00', Name, 'UTC') from mysql.time_zone_name; # -# Bug #9979: Use of CONVERT_TZ in multiple-table UPDATE causes bogus -# privilege error +# Bug#9979 Use of CONVERT_TZ in multiple-table UPDATE causes bogus +# privilege error # drop table t1, t2; create table t1 (a int, b datetime); @@ -80,6 +85,7 @@ update t1 join t2 on (t1.a = t2.a) set t1.b = convert_tz('2005-01-01 10:00','UTC # Clean-up connection default; +disconnect tzuser2; delete from mysql.user where user like 'mysqltest\_%'; delete from mysql.db where user like 'mysqltest\_%'; delete from mysql.tables_priv where user like 'mysqltest\_%'; @@ -89,10 +95,9 @@ drop table t1, t2; # End of 4.1 tests # -# Additional test for bug #15153: CONVERT_TZ() is not allowed in all -# places in views. +# Additional test for Bug#15153 CONVERT_TZ() is not allowed in all places in views. # -# Let us check that usage of CONVERT_TZ() function in view does not +# Let us check that usage of CONVERT_TZ() function in view does not # require additional privileges. # Let us rely on that previous tests done proper cleanups @@ -109,7 +114,11 @@ drop view v1; --error ER_TABLEACCESS_DENIED_ERROR create view v1 as select a, convert_tz(b, 'UTC', 'Europe/Moscow') as lb from t1, mysql.time_zone; connection default; +disconnect tzuser3; drop table t1; drop user mysqltest_1@localhost; # End of 5.0 tests + +# Wait till we reached the initial number of concurrent sessions +--source include/wait_until_count_sessions.inc |