#At file:///work2/6.0/mysql-6.0-bugteam-42003-1/
3035 Matthias Leich 2009-02-11 [merge]
Merge 5.1 -> 6.0
modified:
mysql-test/include/wait_until_count_sessions.inc
mysql-test/r/grant.result
mysql-test/r/information_schema.result
mysql-test/r/multi_update.result
mysql-test/r/packet.result
mysql-test/r/query_cache_notembedded.result
mysql-test/r/subselect.result
mysql-test/r/subselect_no_mat.result
mysql-test/r/subselect_no_opts.result
mysql-test/r/subselect_no_semijoin.result
mysql-test/r/synchronization.result
mysql-test/t/check.test
mysql-test/t/compress.test
mysql-test/t/grant.test
mysql-test/t/information_schema.test
mysql-test/t/multi_update.test
mysql-test/t/overflow.test
mysql-test/t/packet.test
mysql-test/t/query_cache_notembedded.test
mysql-test/t/sp-threads.test
mysql-test/t/subselect.test
mysql-test/t/synchronization.test
mysql-test/t/timezone_grant.test
=== modified file 'mysql-test/include/wait_until_count_sessions.inc'
--- a/mysql-test/include/wait_until_count_sessions.inc 2009-02-02 21:20:25 +0000
+++ b/mysql-test/include/wait_until_count_sessions.inc 2009-02-09 20:52:40 +0000
@@ -82,7 +82,7 @@
# Created: 2009-01-14 mleich
#
-let $wait_counter= 50;
+let $wait_counter= 100;
if ($wait_timeout)
{
let $wait_counter= `SELECT $wait_timeout * 10`;
@@ -108,5 +108,6 @@ if (!$success)
{
--echo # Timeout in wait_until_count_sessions.inc
--echo # Number of sessions expected: $count_sessions found: $current_sessions
+ SHOW PROCESSLIST;
}
=== modified file 'mysql-test/r/grant.result'
--- a/mysql-test/r/grant.result 2009-01-26 16:32:29 +0000
+++ b/mysql-test/r/grant.result 2009-02-11 16:48:50 +0000
@@ -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 ��(HOW GRANTS FOR ��@localhost;
Grants for ��@localhost
@@ -383,21 +383,21 @@ grant update (a) on mysqltest_1.t1 to my
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'''
@@ -885,11 +885,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;
@@ -959,7 +959,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;
@@ -1197,16 +1197,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;
@@ -1218,7 +1218,7 @@ REVOKE SELECT ON `test`.* FROM 'mysqltes
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
@@ -1356,6 +1356,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;
=== modified file 'mysql-test/r/information_schema.result'
--- a/mysql-test/r/information_schema.result 2009-01-26 16:32:29 +0000
+++ b/mysql-test/r/information_schema.result 2009-02-11 16:48:50 +0000
@@ -103,7 +103,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
@@ -123,7 +123,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
@@ -204,7 +204,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
@@ -295,7 +295,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
@@ -580,7 +580,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
@@ -641,7 +641,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
@@ -749,7 +749,7 @@ where table_schema="information_schema"
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
@@ -783,7 +783,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
@@ -834,7 +834,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);
@@ -863,7 +863,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' AND TABLE_NAME NOT LIKE 'FALCON%';
TABLE_NAME COLUMN_NAME PRIVILEGES
COLUMNS TABLE_NAME select
@@ -1112,7 +1112,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'
@@ -1583,7 +1583,7 @@ show events where Db= 'information_schem
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;
=== modified file 'mysql-test/r/multi_update.result'
--- a/mysql-test/r/multi_update.result 2008-12-15 12:41:31 +0000
+++ b/mysql-test/r/multi_update.result 2009-02-11 16:48:50 +0000
@@ -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 < (se
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';
=== modified file 'mysql-test/r/packet.result'
--- a/mysql-test/r/packet.result 2008-11-20 12:55:24 +0000
+++ b/mysql-test/r/packet.result 2009-02-11 16:48:50 +0000
@@ -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;
=== modified file 'mysql-test/r/query_cache_notembedded.result'
--- a/mysql-test/r/query_cache_notembedded.result 2009-01-31 15:53:35 +0000
+++ b/mysql-test/r/query_cache_notembedded.result 2009-02-11 16:48:50 +0000
@@ -376,6 +376,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;
=== modified file 'mysql-test/r/subselect.result'
--- a/mysql-test/r/subselect.result 2009-02-03 09:16:53 +0000
+++ b/mysql-test/r/subselect.result 2009-02-11 16:48:50 +0000
@@ -192,11 +192,11 @@ select (select a from t3 where a<t2.a*4
(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
@@ -2307,20 +2307,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
@@ -2521,8 +2521,8 @@ INSERT INTO t1 VALUES ('ASM','American S
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
@@ -2632,32 +2632,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
@@ -2933,9 +2933,9 @@ INSERT INTO t1 VALUES("0037", "1", "2005
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
@@ -2947,41 +2947,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 index condition
-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 index condition
-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);
@@ -2995,14 +2995,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
@@ -3025,8 +3025,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
@@ -3192,7 +3192,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
@@ -3235,20 +3235,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
@@ -3263,7 +3263,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);
@@ -3326,7 +3326,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
@@ -3377,7 +3377,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;
@@ -3490,7 +3490,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 *,
@@ -3528,7 +3528,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'),
@@ -3544,7 +3544,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
@@ -3552,7 +3552,7 @@ id select_type table type possible_keys
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
@@ -3561,20 +3561,20 @@ 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)
);
i
-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));
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t1 system NULL NULL NULL NULL 0 const row not found
@@ -3597,9 +3597,9 @@ insert into t1 (a) select FLOOR(rand() *
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
@@ -3628,7 +3628,7 @@ COUNT(DISTINCT t1.b) (SELECT COUNT(DISTI
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)
@@ -3639,7 +3639,7 @@ SELECT (
SELECT (
SELECT COUNT(DISTINCT t1.b)
)
-)
+)
FROM t1 GROUP BY t1.a;
(
SELECT (
@@ -3654,8 +3654,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;
(
@@ -3663,7 +3663,7 @@ SELECT (
SELECT (
SELECT COUNT(DISTINCT t1.b)
)
-)
+)
FROM t1 GROUP BY t1.a LIMIT 1)
2
2
@@ -3680,13 +3680,13 @@ insert into t2(y,z) select t1.b, RAND()*
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(*)
@@ -3747,7 +3747,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
@@ -3755,12 +3755,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
@@ -3911,7 +3911,7 @@ COUNT(*) a (SELECT MIN(m) FROM t2 WHERE
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
@@ -3942,7 +3942,7 @@ INSERT INTO t1 VALUES (1,1,0,'a'), (1,2,
(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
@@ -3964,7 +3964,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
@@ -3986,7 +3986,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
@@ -3995,7 +3995,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
@@ -4038,11 +4038,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
@@ -4059,25 +4059,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
@@ -4248,7 +4248,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;
@@ -4257,7 +4257,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;
@@ -4288,7 +4288,7 @@ 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));
id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY t1 ALL NULL NULL NULL NULL 2 100.00 Using where
@@ -4305,11 +4305,11 @@ insert into t0 values (0),(1),(2),(3),(4
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);
-insert into t2
-select -1 , (@a:=(A.a + 10 * (B.a + 10 * (C.a+10*D.a))))/5000 + 1, @a
+insert into t2
+select -1 , (@a:=(A.a + 10 * (B.a + 10 * (C.a+10*D.a))))/5000 + 1, @a
from t0 A, t0 B, t0 C, t0 D;
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
@@ -4350,12 +4350,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
@@ -4364,7 +4364,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
@@ -4582,7 +4582,7 @@ not_pred_in
T
drop table t_out;
drop table t_in;
-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
@@ -4605,23 +4605,23 @@ WHERE (SELECT COUNT(t0.b) FROM t1 t WHER
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;
End of 5.0 tests.
-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);
@@ -4630,36 +4630,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
@@ -4672,52 +4672,52 @@ DROP TABLE t1,t2,t3;
CREATE TABLE t1( a INT );
INSERT INTO t1 VALUES (1),(2);
CREATE TABLE t2( a INT, b INT );
-SELECT *
+SELECT *
FROM (SELECT a INTO @var FROM t1 WHERE a = 2) t1a;
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 'INTO @var FROM t1 WHERE a = 2) t1a' at line 2
-SELECT *
+SELECT *
FROM (SELECT a INTO OUTFILE 'file' FROM t1 WHERE a = 2) t1a;
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 'INTO OUTFILE 'file' FROM t1 WHERE a = 2) t1a' at line 2
-SELECT *
+SELECT *
FROM (SELECT a INTO DUMPFILE 'file' FROM t1 WHERE a = 2) t1a;
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 'INTO DUMPFILE 'file' FROM t1 WHERE a = 2) t1a' at line 2
-SELECT * FROM (
-SELECT 1 a
-UNION
-SELECT a INTO @var FROM t1 WHERE a = 2
+SELECT * FROM (
+SELECT 1 a
+UNION
+SELECT a INTO @var FROM t1 WHERE a = 2
) t1a;
-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 'INTO @var FROM t1 WHERE a = 2
+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 'INTO @var FROM t1 WHERE a = 2
) t1a' at line 4
-SELECT * FROM (
-SELECT 1 a
-UNION
-SELECT a INTO OUTFILE 'file' FROM t1 WHERE a = 2
+SELECT * FROM (
+SELECT 1 a
+UNION
+SELECT a INTO OUTFILE 'file' FROM t1 WHERE a = 2
) t1a;
-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 'INTO OUTFILE 'file' FROM t1 WHERE a = 2
+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 'INTO OUTFILE 'file' FROM t1 WHERE a = 2
) t1a' at line 4
-SELECT * FROM (
-SELECT 1 a
-UNION
-SELECT a INTO DUMPFILE 'file' FROM t1 WHERE a = 2
+SELECT * FROM (
+SELECT 1 a
+UNION
+SELECT a INTO DUMPFILE 'file' FROM t1 WHERE a = 2
) t1a;
-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 'INTO DUMPFILE 'file' FROM t1 WHERE a = 2
+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 'INTO DUMPFILE 'file' FROM t1 WHERE a = 2
) t1a' at line 4
SELECT * FROM (SELECT a FROM t1 WHERE a = 2) t1a;
a
2
-SELECT * FROM (
-SELECT a FROM t1 WHERE a = 2
-UNION
-SELECT a FROM t1 WHERE a = 2
+SELECT * FROM (
+SELECT a FROM t1 WHERE a = 2
+UNION
+SELECT a FROM t1 WHERE a = 2
) t1a;
a
2
-SELECT * FROM (
-SELECT 1 a
-UNION
-SELECT a FROM t1 WHERE a = 2
-UNION
-SELECT a FROM t1 WHERE a = 2
+SELECT * FROM (
+SELECT 1 a
+UNION
+SELECT a FROM t1 WHERE a = 2
+UNION
+SELECT a FROM t1 WHERE a = 2
) t1a;
a
1
@@ -4931,7 +4931,7 @@ SELECT * FROM t1 WHERE a IN ( SELECT 1 I
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 'INTO @v )' at line 1
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));
@@ -4967,7 +4967,7 @@ id g v s
61 60 NULL l
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));
=== modified file 'mysql-test/r/subselect_no_mat.result'
--- a/mysql-test/r/subselect_no_mat.result 2009-02-03 09:16:53 +0000
+++ b/mysql-test/r/subselect_no_mat.result 2009-02-11 16:48:50 +0000
@@ -196,11 +196,11 @@ select (select a from t3 where a<t2.a*4
(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
@@ -2311,20 +2311,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
@@ -2525,8 +2525,8 @@ INSERT INTO t1 VALUES ('ASM','American S
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
@@ -2636,32 +2636,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
@@ -2937,9 +2937,9 @@ INSERT INTO t1 VALUES("0037", "1", "2005
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
@@ -2951,41 +2951,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 index condition
-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 index condition
-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);
@@ -2999,14 +2999,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
@@ -3029,8 +3029,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
@@ -3196,7 +3196,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
@@ -3239,20 +3239,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
@@ -3267,7 +3267,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);
@@ -3330,7 +3330,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
@@ -3381,7 +3381,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;
@@ -3494,7 +3494,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 *,
@@ -3532,7 +3532,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'),
@@ -3548,7 +3548,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
@@ -3556,7 +3556,7 @@ id select_type table type possible_keys
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
@@ -3565,20 +3565,20 @@ 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)
);
i
-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));
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t1 system NULL NULL NULL NULL 0 const row not found
@@ -3601,9 +3601,9 @@ insert into t1 (a) select FLOOR(rand() *
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
@@ -3632,7 +3632,7 @@ COUNT(DISTINCT t1.b) (SELECT COUNT(DISTI
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)
@@ -3643,7 +3643,7 @@ SELECT (
SELECT (
SELECT COUNT(DISTINCT t1.b)
)
-)
+)
FROM t1 GROUP BY t1.a;
(
SELECT (
@@ -3658,8 +3658,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;
(
@@ -3667,7 +3667,7 @@ SELECT (
SELECT (
SELECT COUNT(DISTINCT t1.b)
)
-)
+)
FROM t1 GROUP BY t1.a LIMIT 1)
2
2
@@ -3684,13 +3684,13 @@ insert into t2(y,z) select t1.b, RAND()*
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(*)
@@ -3751,7 +3751,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
@@ -3759,12 +3759,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
@@ -3915,7 +3915,7 @@ COUNT(*) a (SELECT MIN(m) FROM t2 WHERE
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
@@ -3946,7 +3946,7 @@ INSERT INTO t1 VALUES (1,1,0,'a'), (1,2,
(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
@@ -3968,7 +3968,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
@@ -3990,7 +3990,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
@@ -3999,7 +3999,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
@@ -4042,11 +4042,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
@@ -4063,25 +4063,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
@@ -4252,7 +4252,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;
@@ -4261,7 +4261,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;
@@ -4292,7 +4292,7 @@ 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));
id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY t1 ALL NULL NULL NULL NULL 2 100.00 Using where
@@ -4309,11 +4309,11 @@ insert into t0 values (0),(1),(2),(3),(4
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);
-insert into t2
-select -1 , (@a:=(A.a + 10 * (B.a + 10 * (C.a+10*D.a))))/5000 + 1, @a
+insert into t2
+select -1 , (@a:=(A.a + 10 * (B.a + 10 * (C.a+10*D.a))))/5000 + 1, @a
from t0 A, t0 B, t0 C, t0 D;
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
@@ -4354,12 +4354,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
@@ -4368,7 +4368,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
@@ -4586,7 +4586,7 @@ not_pred_in
T
drop table t_out;
drop table t_in;
-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
@@ -4609,23 +4609,23 @@ WHERE (SELECT COUNT(t0.b) FROM t1 t WHER
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;
End of 5.0 tests.
-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);
@@ -4634,36 +4634,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
@@ -4676,52 +4676,52 @@ DROP TABLE t1,t2,t3;
CREATE TABLE t1( a INT );
INSERT INTO t1 VALUES (1),(2);
CREATE TABLE t2( a INT, b INT );
-SELECT *
+SELECT *
FROM (SELECT a INTO @var FROM t1 WHERE a = 2) t1a;
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 'INTO @var FROM t1 WHERE a = 2) t1a' at line 2
-SELECT *
+SELECT *
FROM (SELECT a INTO OUTFILE 'file' FROM t1 WHERE a = 2) t1a;
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 'INTO OUTFILE 'file' FROM t1 WHERE a = 2) t1a' at line 2
-SELECT *
+SELECT *
FROM (SELECT a INTO DUMPFILE 'file' FROM t1 WHERE a = 2) t1a;
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 'INTO DUMPFILE 'file' FROM t1 WHERE a = 2) t1a' at line 2
-SELECT * FROM (
-SELECT 1 a
-UNION
-SELECT a INTO @var FROM t1 WHERE a = 2
+SELECT * FROM (
+SELECT 1 a
+UNION
+SELECT a INTO @var FROM t1 WHERE a = 2
) t1a;
-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 'INTO @var FROM t1 WHERE a = 2
+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 'INTO @var FROM t1 WHERE a = 2
) t1a' at line 4
-SELECT * FROM (
-SELECT 1 a
-UNION
-SELECT a INTO OUTFILE 'file' FROM t1 WHERE a = 2
+SELECT * FROM (
+SELECT 1 a
+UNION
+SELECT a INTO OUTFILE 'file' FROM t1 WHERE a = 2
) t1a;
-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 'INTO OUTFILE 'file' FROM t1 WHERE a = 2
+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 'INTO OUTFILE 'file' FROM t1 WHERE a = 2
) t1a' at line 4
-SELECT * FROM (
-SELECT 1 a
-UNION
-SELECT a INTO DUMPFILE 'file' FROM t1 WHERE a = 2
+SELECT * FROM (
+SELECT 1 a
+UNION
+SELECT a INTO DUMPFILE 'file' FROM t1 WHERE a = 2
) t1a;
-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 'INTO DUMPFILE 'file' FROM t1 WHERE a = 2
+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 'INTO DUMPFILE 'file' FROM t1 WHERE a = 2
) t1a' at line 4
SELECT * FROM (SELECT a FROM t1 WHERE a = 2) t1a;
a
2
-SELECT * FROM (
-SELECT a FROM t1 WHERE a = 2
-UNION
-SELECT a FROM t1 WHERE a = 2
+SELECT * FROM (
+SELECT a FROM t1 WHERE a = 2
+UNION
+SELECT a FROM t1 WHERE a = 2
) t1a;
a
2
-SELECT * FROM (
-SELECT 1 a
-UNION
-SELECT a FROM t1 WHERE a = 2
-UNION
-SELECT a FROM t1 WHERE a = 2
+SELECT * FROM (
+SELECT 1 a
+UNION
+SELECT a FROM t1 WHERE a = 2
+UNION
+SELECT a FROM t1 WHERE a = 2
) t1a;
a
1
@@ -4935,7 +4935,7 @@ SELECT * FROM t1 WHERE a IN ( SELECT 1 I
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 'INTO @v )' at line 1
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));
@@ -4971,7 +4971,7 @@ id g v s
61 60 NULL l
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));
=== modified file 'mysql-test/r/subselect_no_opts.result'
--- a/mysql-test/r/subselect_no_opts.result 2009-02-03 09:16:53 +0000
+++ b/mysql-test/r/subselect_no_opts.result 2009-02-11 16:48:50 +0000
@@ -196,11 +196,11 @@ select (select a from t3 where a<t2.a*4
(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
@@ -2311,20 +2311,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
@@ -2525,8 +2525,8 @@ INSERT INTO t1 VALUES ('ASM','American S
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
@@ -2636,32 +2636,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
@@ -2937,9 +2937,9 @@ INSERT INTO t1 VALUES("0037", "1", "2005
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
@@ -2951,41 +2951,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 index condition
-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 index condition
-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);
@@ -2999,14 +2999,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
@@ -3029,8 +3029,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
@@ -3196,7 +3196,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
@@ -3239,20 +3239,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
@@ -3267,7 +3267,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);
@@ -3330,7 +3330,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
@@ -3381,7 +3381,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;
@@ -3494,7 +3494,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 *,
@@ -3532,7 +3532,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'),
@@ -3548,7 +3548,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
@@ -3556,7 +3556,7 @@ id select_type table type possible_keys
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
@@ -3565,20 +3565,20 @@ 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)
);
i
-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));
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t1 system NULL NULL NULL NULL 0 const row not found
@@ -3601,9 +3601,9 @@ insert into t1 (a) select FLOOR(rand() *
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
@@ -3632,7 +3632,7 @@ COUNT(DISTINCT t1.b) (SELECT COUNT(DISTI
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)
@@ -3643,7 +3643,7 @@ SELECT (
SELECT (
SELECT COUNT(DISTINCT t1.b)
)
-)
+)
FROM t1 GROUP BY t1.a;
(
SELECT (
@@ -3658,8 +3658,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;
(
@@ -3667,7 +3667,7 @@ SELECT (
SELECT (
SELECT COUNT(DISTINCT t1.b)
)
-)
+)
FROM t1 GROUP BY t1.a LIMIT 1)
2
2
@@ -3684,13 +3684,13 @@ insert into t2(y,z) select t1.b, RAND()*
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(*)
@@ -3751,7 +3751,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
@@ -3759,12 +3759,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
@@ -3915,7 +3915,7 @@ COUNT(*) a (SELECT MIN(m) FROM t2 WHERE
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
@@ -3946,7 +3946,7 @@ INSERT INTO t1 VALUES (1,1,0,'a'), (1,2,
(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
@@ -3968,7 +3968,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
@@ -3990,7 +3990,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
@@ -3999,7 +3999,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
@@ -4042,11 +4042,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
@@ -4063,25 +4063,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
@@ -4252,7 +4252,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;
@@ -4261,7 +4261,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;
@@ -4292,7 +4292,7 @@ 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));
id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY t1 ALL NULL NULL NULL NULL 2 100.00 Using where
@@ -4309,11 +4309,11 @@ insert into t0 values (0),(1),(2),(3),(4
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);
-insert into t2
-select -1 , (@a:=(A.a + 10 * (B.a + 10 * (C.a+10*D.a))))/5000 + 1, @a
+insert into t2
+select -1 , (@a:=(A.a + 10 * (B.a + 10 * (C.a+10*D.a))))/5000 + 1, @a
from t0 A, t0 B, t0 C, t0 D;
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
@@ -4354,12 +4354,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
@@ -4368,7 +4368,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
@@ -4586,7 +4586,7 @@ not_pred_in
T
drop table t_out;
drop table t_in;
-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
@@ -4609,23 +4609,23 @@ WHERE (SELECT COUNT(t0.b) FROM t1 t WHER
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;
End of 5.0 tests.
-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);
@@ -4634,36 +4634,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
@@ -4676,52 +4676,52 @@ DROP TABLE t1,t2,t3;
CREATE TABLE t1( a INT );
INSERT INTO t1 VALUES (1),(2);
CREATE TABLE t2( a INT, b INT );
-SELECT *
+SELECT *
FROM (SELECT a INTO @var FROM t1 WHERE a = 2) t1a;
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 'INTO @var FROM t1 WHERE a = 2) t1a' at line 2
-SELECT *
+SELECT *
FROM (SELECT a INTO OUTFILE 'file' FROM t1 WHERE a = 2) t1a;
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 'INTO OUTFILE 'file' FROM t1 WHERE a = 2) t1a' at line 2
-SELECT *
+SELECT *
FROM (SELECT a INTO DUMPFILE 'file' FROM t1 WHERE a = 2) t1a;
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 'INTO DUMPFILE 'file' FROM t1 WHERE a = 2) t1a' at line 2
-SELECT * FROM (
-SELECT 1 a
-UNION
-SELECT a INTO @var FROM t1 WHERE a = 2
+SELECT * FROM (
+SELECT 1 a
+UNION
+SELECT a INTO @var FROM t1 WHERE a = 2
) t1a;
-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 'INTO @var FROM t1 WHERE a = 2
+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 'INTO @var FROM t1 WHERE a = 2
) t1a' at line 4
-SELECT * FROM (
-SELECT 1 a
-UNION
-SELECT a INTO OUTFILE 'file' FROM t1 WHERE a = 2
+SELECT * FROM (
+SELECT 1 a
+UNION
+SELECT a INTO OUTFILE 'file' FROM t1 WHERE a = 2
) t1a;
-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 'INTO OUTFILE 'file' FROM t1 WHERE a = 2
+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 'INTO OUTFILE 'file' FROM t1 WHERE a = 2
) t1a' at line 4
-SELECT * FROM (
-SELECT 1 a
-UNION
-SELECT a INTO DUMPFILE 'file' FROM t1 WHERE a = 2
+SELECT * FROM (
+SELECT 1 a
+UNION
+SELECT a INTO DUMPFILE 'file' FROM t1 WHERE a = 2
) t1a;
-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 'INTO DUMPFILE 'file' FROM t1 WHERE a = 2
+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 'INTO DUMPFILE 'file' FROM t1 WHERE a = 2
) t1a' at line 4
SELECT * FROM (SELECT a FROM t1 WHERE a = 2) t1a;
a
2
-SELECT * FROM (
-SELECT a FROM t1 WHERE a = 2
-UNION
-SELECT a FROM t1 WHERE a = 2
+SELECT * FROM (
+SELECT a FROM t1 WHERE a = 2
+UNION
+SELECT a FROM t1 WHERE a = 2
) t1a;
a
2
-SELECT * FROM (
-SELECT 1 a
-UNION
-SELECT a FROM t1 WHERE a = 2
-UNION
-SELECT a FROM t1 WHERE a = 2
+SELECT * FROM (
+SELECT 1 a
+UNION
+SELECT a FROM t1 WHERE a = 2
+UNION
+SELECT a FROM t1 WHERE a = 2
) t1a;
a
1
@@ -4935,7 +4935,7 @@ SELECT * FROM t1 WHERE a IN ( SELECT 1 I
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 'INTO @v )' at line 1
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));
@@ -4971,7 +4971,7 @@ id g v s
61 60 NULL l
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));
=== modified file 'mysql-test/r/subselect_no_semijoin.result'
--- a/mysql-test/r/subselect_no_semijoin.result 2009-02-03 09:16:53 +0000
+++ b/mysql-test/r/subselect_no_semijoin.result 2009-02-11 16:48:50 +0000
@@ -196,11 +196,11 @@ select (select a from t3 where a<t2.a*4
(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
@@ -2311,20 +2311,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
@@ -2525,8 +2525,8 @@ INSERT INTO t1 VALUES ('ASM','American S
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
@@ -2636,32 +2636,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
@@ -2937,9 +2937,9 @@ INSERT INTO t1 VALUES("0037", "1", "2005
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
@@ -2951,41 +2951,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 index condition
-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 index condition
-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);
@@ -2999,14 +2999,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
@@ -3029,8 +3029,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
@@ -3196,7 +3196,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
@@ -3239,20 +3239,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
@@ -3267,7 +3267,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);
@@ -3330,7 +3330,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
@@ -3381,7 +3381,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;
@@ -3494,7 +3494,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 *,
@@ -3532,7 +3532,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'),
@@ -3548,7 +3548,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
@@ -3556,7 +3556,7 @@ id select_type table type possible_keys
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
@@ -3565,20 +3565,20 @@ 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)
);
i
-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));
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t1 system NULL NULL NULL NULL 0 const row not found
@@ -3601,9 +3601,9 @@ insert into t1 (a) select FLOOR(rand() *
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
@@ -3632,7 +3632,7 @@ COUNT(DISTINCT t1.b) (SELECT COUNT(DISTI
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)
@@ -3643,7 +3643,7 @@ SELECT (
SELECT (
SELECT COUNT(DISTINCT t1.b)
)
-)
+)
FROM t1 GROUP BY t1.a;
(
SELECT (
@@ -3658,8 +3658,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;
(
@@ -3667,7 +3667,7 @@ SELECT (
SELECT (
SELECT COUNT(DISTINCT t1.b)
)
-)
+)
FROM t1 GROUP BY t1.a LIMIT 1)
2
2
@@ -3684,13 +3684,13 @@ insert into t2(y,z) select t1.b, RAND()*
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(*)
@@ -3751,7 +3751,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
@@ -3759,12 +3759,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
@@ -3915,7 +3915,7 @@ COUNT(*) a (SELECT MIN(m) FROM t2 WHERE
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
@@ -3946,7 +3946,7 @@ INSERT INTO t1 VALUES (1,1,0,'a'), (1,2,
(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
@@ -3968,7 +3968,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
@@ -3990,7 +3990,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
@@ -3999,7 +3999,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
@@ -4042,11 +4042,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
@@ -4063,25 +4063,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
@@ -4252,7 +4252,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;
@@ -4261,7 +4261,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;
@@ -4292,7 +4292,7 @@ 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));
id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY t1 ALL NULL NULL NULL NULL 2 100.00 Using where
@@ -4309,11 +4309,11 @@ insert into t0 values (0),(1),(2),(3),(4
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);
-insert into t2
-select -1 , (@a:=(A.a + 10 * (B.a + 10 * (C.a+10*D.a))))/5000 + 1, @a
+insert into t2
+select -1 , (@a:=(A.a + 10 * (B.a + 10 * (C.a+10*D.a))))/5000 + 1, @a
from t0 A, t0 B, t0 C, t0 D;
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
@@ -4354,12 +4354,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
@@ -4368,7 +4368,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
@@ -4586,7 +4586,7 @@ not_pred_in
T
drop table t_out;
drop table t_in;
-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
@@ -4609,23 +4609,23 @@ WHERE (SELECT COUNT(t0.b) FROM t1 t WHER
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;
End of 5.0 tests.
-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);
@@ -4634,36 +4634,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
@@ -4676,52 +4676,52 @@ DROP TABLE t1,t2,t3;
CREATE TABLE t1( a INT );
INSERT INTO t1 VALUES (1),(2);
CREATE TABLE t2( a INT, b INT );
-SELECT *
+SELECT *
FROM (SELECT a INTO @var FROM t1 WHERE a = 2) t1a;
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 'INTO @var FROM t1 WHERE a = 2) t1a' at line 2
-SELECT *
+SELECT *
FROM (SELECT a INTO OUTFILE 'file' FROM t1 WHERE a = 2) t1a;
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 'INTO OUTFILE 'file' FROM t1 WHERE a = 2) t1a' at line 2
-SELECT *
+SELECT *
FROM (SELECT a INTO DUMPFILE 'file' FROM t1 WHERE a = 2) t1a;
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 'INTO DUMPFILE 'file' FROM t1 WHERE a = 2) t1a' at line 2
-SELECT * FROM (
-SELECT 1 a
-UNION
-SELECT a INTO @var FROM t1 WHERE a = 2
+SELECT * FROM (
+SELECT 1 a
+UNION
+SELECT a INTO @var FROM t1 WHERE a = 2
) t1a;
-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 'INTO @var FROM t1 WHERE a = 2
+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 'INTO @var FROM t1 WHERE a = 2
) t1a' at line 4
-SELECT * FROM (
-SELECT 1 a
-UNION
-SELECT a INTO OUTFILE 'file' FROM t1 WHERE a = 2
+SELECT * FROM (
+SELECT 1 a
+UNION
+SELECT a INTO OUTFILE 'file' FROM t1 WHERE a = 2
) t1a;
-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 'INTO OUTFILE 'file' FROM t1 WHERE a = 2
+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 'INTO OUTFILE 'file' FROM t1 WHERE a = 2
) t1a' at line 4
-SELECT * FROM (
-SELECT 1 a
-UNION
-SELECT a INTO DUMPFILE 'file' FROM t1 WHERE a = 2
+SELECT * FROM (
+SELECT 1 a
+UNION
+SELECT a INTO DUMPFILE 'file' FROM t1 WHERE a = 2
) t1a;
-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 'INTO DUMPFILE 'file' FROM t1 WHERE a = 2
+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 'INTO DUMPFILE 'file' FROM t1 WHERE a = 2
) t1a' at line 4
SELECT * FROM (SELECT a FROM t1 WHERE a = 2) t1a;
a
2
-SELECT * FROM (
-SELECT a FROM t1 WHERE a = 2
-UNION
-SELECT a FROM t1 WHERE a = 2
+SELECT * FROM (
+SELECT a FROM t1 WHERE a = 2
+UNION
+SELECT a FROM t1 WHERE a = 2
) t1a;
a
2
-SELECT * FROM (
-SELECT 1 a
-UNION
-SELECT a FROM t1 WHERE a = 2
-UNION
-SELECT a FROM t1 WHERE a = 2
+SELECT * FROM (
+SELECT 1 a
+UNION
+SELECT a FROM t1 WHERE a = 2
+UNION
+SELECT a FROM t1 WHERE a = 2
) t1a;
a
1
@@ -4935,7 +4935,7 @@ SELECT * FROM t1 WHERE a IN ( SELECT 1 I
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 'INTO @v )' at line 1
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));
@@ -4971,7 +4971,7 @@ id g v s
61 60 NULL l
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));
=== modified file 'mysql-test/r/synchronization.result'
--- a/mysql-test/r/synchronization.result 2006-11-27 21:19:53 +0000
+++ b/mysql-test/r/synchronization.result 2009-02-09 21:00:15 +0000
@@ -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
=== modified file 'mysql-test/t/check.test'
--- a/mysql-test/t/check.test 2007-07-19 10:54:31 +0000
+++ b/mysql-test/t/check.test 2009-02-09 21:00:15 +0000
@@ -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
=== modified file 'mysql-test/t/compress.test'
--- a/mysql-test/t/compress.test 2007-04-03 09:24:35 +0000
+++ b/mysql-test/t/compress.test 2009-02-09 21:00:15 +0000
@@ -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
# 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
+
=== modified file 'mysql-test/t/grant.test'
--- a/mysql-test/t/grant.test 2009-01-26 16:32:29 +0000
+++ b/mysql-test/t/grant.test 2009-02-11 16:48:50 +0000
@@ -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='mysqlte
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='test1
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='test6
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@
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@localh
# 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 ��(CT 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,
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 mysqlt
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 use
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
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
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 us
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_thisisreallytoolo
#
#
-# 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
#
-# 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
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 'mysqltes
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() = PASSWO
--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 юзер_юз.* to очень_д� 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 * f
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
=== modified file 'mysql-test/t/information_schema.test'
--- a/mysql-test/t/information_schema.test 2009-01-26 16:32:29 +0000
+++ b/mysql-test/t/information_schema.test 2009-02-11 16:48:50 +0000
@@ -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 @@ table_name NOT LIKE 'ndb_%' AND
table_name NOT LIKE 'falcon%';
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 mysq
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;
@@ -150,7 +155,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 #
@@ -177,7 +182,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;
@@ -196,6 +201,7 @@ show create function sub2;
show function status like "sub2";
connection default;
disconnect user1;
+disconnect user3;
drop function sub2;
show create procedure sel2;
@@ -335,7 +341,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;
@@ -348,7 +354,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;
@@ -358,16 +364,16 @@ 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%";
@@ -375,7 +381,7 @@ show tables from information_schema like
create database information_schema;
use information_schema;
show full tables like "T%";
---error 1044
+--error ER_DBACCESS_DENIED_ERROR
create table t1(a int);
use test;
show tables;
@@ -383,15 +389,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
@@ -418,9 +424,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;
#
@@ -433,7 +439,7 @@ where table_schema="information_schema"
#
# 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";
@@ -446,14 +452,14 @@ show status where variable_name like "%d
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);
@@ -464,7 +470,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);
@@ -492,26 +498,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
@@ -529,21 +535,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' AND TABLE_NAME NOT LIKE 'FALCON%';
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
@@ -588,7 +595,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;
@@ -632,12 +639,16 @@ select * from information_schema.user_pr
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;
@@ -652,13 +663,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,
@@ -674,7 +685,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;
@@ -696,8 +707,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);
@@ -711,7 +722,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;
@@ -719,28 +730,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 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
@@ -749,13 +761,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';
#
@@ -781,9 +793,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;
@@ -868,6 +880,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;
@@ -882,8 +895,9 @@ select concat(@a, table_name), @a, table
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;
@@ -916,8 +930,8 @@ DROP PROCEDURE p1;
DROP USER mysql_bug20230@localhost;
#
-# Bug#21231: query with a simple non-correlated subquery over
-# INFORMARTION_SCHEMA.TABLES
+# Bug#21231 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');
@@ -925,7 +939,7 @@ SELECT table_name from information_schem
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)
@@ -966,7 +980,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,
@@ -992,7 +1006,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
@@ -1020,8 +1034,8 @@ group by t.table_name order by num1, t.t
#
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.
@@ -1031,7 +1045,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;
@@ -1060,6 +1074,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;
@@ -1083,13 +1098,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;
@@ -1098,9 +1113,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
@@ -1128,10 +1143,11 @@ connect (con3148,localhost,user3148,,tes
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
@@ -1169,7 +1185,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';
@@ -1217,7 +1233,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
#
@@ -1263,7 +1279,7 @@ show events where Db= 'information_schem
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;
@@ -1285,8 +1301,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;
@@ -1364,13 +1380,13 @@ select * from information_schema.global_
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;
#
@@ -1384,7 +1400,7 @@ DROP TABLE t1;
#
# WL#2003 INFORMATION_SCHEMA: PARAMETERS view
-# WL#2822 INFORMATION_SCHEMA.ROUTINES: Add missing columns
+# WL#2822 INFORMATION_SCHEMA.ROUTINES: Add missing columns
#
create function f1 (p1 int, p2 datetime, p3 decimal(10,2))
returns char(10) return null;
@@ -1414,13 +1430,14 @@ GRANT SELECT ON testdb1.t1 TO mysqluser_
--connect (con_35955, localhost, mysqluser_35955, , testdb1)
SELECT * FROM INFORMATION_SCHEMA.tables, t1;
---error 1142
+--error ER_TABLEACCESS_DENIED_ERROR
SELECT * FROM t2;
---error 1142
+--error ER_TABLEACCESS_DENIED_ERROR
SELECT * FROM t2, INFORMATION_SCHEMA.tables;
---error 1142
+--error ER_TABLEACCESS_DENIED_ERROR
SELECT * FROM INFORMATION_SCHEMA.tables, t2;
--connection default
+--disconnect con_35955
DROP TABLE t1,t2;
DROP DATABASE testdb1;
DROP USER mysqluser_35955@localhost;
@@ -1467,6 +1484,8 @@ connection con3726_2;
--reap
--echo # Switching to connection 'default'
connection default;
+disconnect con3726_1;
+disconnect con3726_2;
drop tables t1, t3;
#
@@ -1483,7 +1502,7 @@ EXPLAIN SELECT * FROM INFORMATION_SCHEMA
#
# Bug#24062 Incorrect error msg after execute DROP TABLE IF EXISTS on information_schema
-#
+#
--error ER_DBACCESS_DENIED_ERROR
create table information_schema.t1 (f1 INT);
--error ER_DBACCESS_DENIED_ERROR
@@ -1498,7 +1517,7 @@ drop view information_schema.v1;
create view information_schema.v1;
--error ER_DBACCESS_DENIED_ERROR
create trigger mysql.trg1 after insert on information_schema.t1 for each row set @a=1;
---error 1109
+--error ER_UNKNOWN_TABLE
create table t1 select * from information_schema.t1;
CREATE TABLE t1(f1 char(100));
@@ -1529,3 +1548,8 @@ where routine_schema = 'test';
connection default;
drop function f1;
disconnect con7;
+
+
+# Wait till all disconnects are completed
+--source include/wait_until_count_sessions.inc
+
=== modified file 'mysql-test/t/multi_update.test'
--- a/mysql-test/t/multi_update.test 2008-11-25 12:17:50 +0000
+++ b/mysql-test/t/multi_update.test 2009-02-11 16:48:50 +0000
@@ -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) unsig
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 fr
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(c
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 (t
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 def
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= 'Table lock';
+--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= 'Table lock';
+--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;
#
=== modified file 'mysql-test/t/overflow.test'
--- a/mysql-test/t/overflow.test 2007-02-26 10:49:24 +0000
+++ b/mysql-test/t/overflow.test 2009-02-05 20:47:23 +0000
@@ -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
=== modified file 'mysql-test/t/packet.test'
--- a/mysql-test/t/packet.test 2008-11-20 12:55:24 +0000
+++ b/mysql-test/t/packet.test 2009-02-11 16:48:50 +0000
@@ -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
=== modified file 'mysql-test/t/query_cache_notembedded.test'
--- a/mysql-test/t/query_cache_notembedded.test 2009-01-31 15:53:35 +0000
+++ b/mysql-test/t/query_cache_notembedded.test 2009-02-11 16:48:50 +0000
@@ -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;
@@ -97,10 +101,13 @@ connection root;
SELECT * FROM t1;
UNLOCK TABLES;
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;
@@ -182,7 +189,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;
@@ -258,6 +265,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';
@@ -267,3 +275,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
=== modified file 'mysql-test/t/sp-threads.test'
--- a/mysql-test/t/sp-threads.test 2006-02-25 19:54:34 +0000
+++ b/mysql-test/t/sp-threads.test 2009-02-09 21:00:15 +0000
@@ -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
=== modified file 'mysql-test/t/subselect.test'
--- a/mysql-test/t/subselect.test 2009-01-31 15:53:35 +0000
+++ b/mysql-test/t/subselect.test 2009-02-11 16:48:50 +0000
@@ -10,6 +10,7 @@
--disable_warnings
drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t11,t12;
--enable_warnings
+
select (select 2);
explain extended select (select 2);
SELECT (SELECT 1) UNION SELECT (SELECT 2);
@@ -85,9 +86,9 @@ select * from t2 where t2.b=(select a fr
(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);
@@ -374,7 +375,7 @@ let $wait_condition= SELECT COUNT(*) <>
--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;
@@ -543,7 +544,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));
@@ -685,7 +686,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;
@@ -739,9 +740,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;
#
@@ -846,7 +847,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;
@@ -1014,7 +1015,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;
@@ -1039,11 +1040,12 @@ INSERT INTO t1 VALUES (1),(1),(1),(1),(1
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;
@@ -1056,8 +1058,9 @@ eval load data infile "$outfile_rel" int
select * from t1;
drop table t1;
+
#
-# Bug 2479
+# Bug#2479 dependant subquery with limit crash
#
CREATE TABLE `t1` (
@@ -1095,8 +1098,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));
@@ -1135,8 +1139,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);
@@ -1145,8 +1150,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);
@@ -1337,8 +1343,9 @@ select * from t1 up where exists (select
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);
@@ -1349,8 +1356,10 @@ SELECT * FROM t1, t2 table2 WHERE t1_a =
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);
@@ -1360,18 +1369,19 @@ INSERT INTO t2 VALUES (1,'Fido'),(2,'Spo
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);";
@@ -1395,19 +1405,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);
@@ -1464,10 +1476,10 @@ SELECT b.sc FROM (SELECT (SELECT a.acces
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);
@@ -1494,7 +1506,7 @@ disconnect root;
connection default;
#
-# 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);
@@ -1555,14 +1567,15 @@ INSERT INTO t1 VALUES ('ASM','American S
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);
@@ -1611,8 +1624,9 @@ select 1 = ALL (select 1 from t1 where 1
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,
@@ -1682,43 +1696,45 @@ 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);
SELECT 1 FROM t1 WHERE (SELECT 1) in (SELECT 1);
drop table t1;
+
#
# subselect into HAVING clause (code covarage improvement)
#
@@ -1757,8 +1773,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,
@@ -1781,15 +1798,16 @@ INSERT INTO `t2` VALUES (1, 1, 1, '10.10
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));
@@ -1819,8 +1837,9 @@ explain extended SELECT one,two from t1
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));
@@ -1830,8 +1849,9 @@ SELECT * FROM t1 WHERE (a,b) IN (('aaa',
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);
@@ -1848,9 +1868,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);
@@ -1862,8 +1883,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);
@@ -1872,7 +1894,8 @@ select 1 from dual where 2 > all (select
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);
@@ -1889,8 +1912,9 @@ select * from t1 where (s1 = ALL (select
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,
@@ -1906,15 +1930,16 @@ INSERT INTO t1 VALUES("0037", "2", "2006
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);
@@ -1922,38 +1947,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 t2 (
- fieldA int NOT NULL,
- fieldB int NOT NULL,
- PRIMARY KEY (fieldA,fieldB)
-);
+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)
+);
INSERT INTO t1 VALUES
(1,1,1), (1,1,2), (1,2,1), (1,2,2), (1,2,3), (1,3,1);
@@ -1965,19 +1991,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));
@@ -1992,24 +2019,26 @@ SELECT a, a IN (SELECT a FROM t1) FROM t
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);
@@ -2021,9 +2050,10 @@ EXPLAIN SELECT a FROM t1 WHERE (SELECT 1
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);
@@ -2106,8 +2136,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 );
@@ -2127,15 +2158,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 );
@@ -2146,13 +2178,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,
@@ -2175,9 +2208,10 @@ INSERT INTO `t2` VALUES (1, 1, 1, '10.10
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) );
@@ -2191,44 +2225,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);
@@ -2258,7 +2294,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;
@@ -2286,9 +2322,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
@@ -2304,9 +2340,10 @@ SELECT t1.a, SUM(b) AS sum FROM t1 GROU
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
@@ -2325,27 +2362,30 @@ SELECT * FROM t1 WHERE (a,b) = ANY (SELE
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);
@@ -2359,16 +2399,18 @@ SELECT * FROM t1 WHERE no_such_column =
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
@@ -2390,8 +2432,9 @@ SELECT t1.i FROM t1 WHERE t1.i = CAST((S
DROP TABLE t1;
DROP TABLE t2;
-#
-# Bug#20519: subselect with LIMIT M, N
+
+#
+# Bug#20519 subselect with LIMIT M, N
#
CREATE TABLE t1 (
@@ -2408,7 +2451,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');
@@ -2430,8 +2473,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 (
@@ -2440,7 +2484,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'),
@@ -2458,32 +2502,32 @@ INSERT INTO t2 VALUES (24,1,'2006-06-20
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);
-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
@@ -2491,14 +2535,15 @@ WHERE NOT EXISTS (((SELECT i FROM t1) UN
explain select ((select t11.i from t1 t11) union (select t12.i from t1 t12))
from t1;
-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));
@@ -2516,15 +2561,16 @@ insert into t1 (a) select FLOOR(rand() *
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);
@@ -2533,29 +2579,30 @@ SELECT (SELECT COUNT(DISTINCT t1.b) from
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, PRIMARY KEY (b));
@@ -2570,20 +2617,20 @@ insert into t2(y,z) select t1.b, RAND()*
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,t3;
#
-# 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);
@@ -2615,10 +2662,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);
@@ -2629,19 +2677,20 @@ PREPARE stmt1 FROM 'SELECT ((SELECT f2 F
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 "(())")
@@ -2742,21 +2791,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);
@@ -2771,15 +2822,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);
@@ -2796,8 +2848,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'),
@@ -2805,7 +2858,7 @@ INSERT INTO t1 VALUES (1,1,0,'a'), (1,2,
(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
@@ -2816,25 +2869,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);
@@ -2859,9 +2914,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);
@@ -2876,28 +2931,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
@@ -2905,24 +2962,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);
@@ -2933,10 +2991,11 @@ SELECT * FROM (SELECT count(*) FROM t1 G
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,
@@ -2967,8 +3026,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);
@@ -3008,7 +3067,7 @@ SELECT (SELECT SUM(t1.a) FROM t2 WHERE a
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);
@@ -3023,7 +3082,7 @@ SELECT ((a1,a2) IN (SELECT * FROM t2 WHE
DROP TABLE t1, t2;
#
-# Bug31048: Many nested subqueries may cause server crash.
+# Bug31048 Many nested subqueries may cause server crash.
#
#create table t1(a int,b int,key(a),key(b));
#insert into t1(a,b) values (1,2),(2,1),(2,3),(3,4),(5,4),(5,5),
@@ -3070,7 +3129,7 @@ DROP TABLE t1, t2;
#drop table t1;
#
-# Bug #28076: inconsistent binary/varbinary comparison
+# Bug#28076 inconsistent binary/varbinary comparison
#
CREATE TABLE t1 (s1 BINARY(5), s2 VARBINARY(5));
@@ -3111,8 +3170,9 @@ SELECT LEFT(t1.a1,1) FROM t1,t3 WHERE t1
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));
@@ -3136,16 +3196,16 @@ SELECT a,b FROM t1 WHERE b IN (SELECT a
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;
@@ -3155,7 +3215,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;
@@ -3168,7 +3228,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);
@@ -3183,14 +3243,15 @@ SELECT 2 FROM t1 WHERE EXISTS ((SELECT 1
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 t0(a int);
insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
@@ -3198,18 +3259,18 @@ 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);
--disable_warnings
-insert into t2
-select -1 , (@a:=(A.a + 10 * (B.a + 10 * (C.a+10*D.a))))/5000 + 1, @a
+insert into t2
+select -1 , (@a:=(A.a + 10 * (B.a + 10 * (C.a+10*D.a))))/5000 + 1, @a
from t0 A, t0 B, t0 C, t0 D;
--enable_warnings
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 t0,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 '',
@@ -3251,24 +3312,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) ;
@@ -3276,8 +3338,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);
@@ -3289,7 +3351,7 @@ DROP TABLE t1;
--echo End of 5.0 tests.
#
-# Test [NOT] IN truth table (both as top-level and general predicate).
+# Test [NOT] IN truth table (both as top-level and general predicate).
#
create table t_out (subcase char(3),
@@ -3309,9 +3371,9 @@ insert into t_in values ('1a', '1a', '1a
insert into t_in values ('2a', '2a', '2a');
insert into t_in values (NULL, '2a', '2a');
insert into t_in values ('3a', NULL, '3a');
--- echo
+-- echo
-- echo Test general IN semantics (not top-level)
--- echo
+-- echo
-- echo case A.1
select subcase,
(a1, b1, c1) IN (select * from t_in where a2 = 'no_match') pred_in,
@@ -3356,10 +3418,10 @@ select subcase,
(a1, b1, c1) NOT IN (select * from t_in) pred_not_in
from t_out where subcase = 'B.4';
--- echo
+-- echo
-- echo Test IN as top-level predicate, and
-- echo as non-top level for cases A.3, B.3 (the only cases with NULL result).
--- echo
+-- echo
-- echo case A.1
select case when count(*) > 0 then 'T' else 'F' end as pred_in from t_out
where subcase = 'A.1' and
@@ -3466,10 +3528,10 @@ drop table t_in;
#
-# 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;
@@ -3494,17 +3556,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);
@@ -3515,30 +3577,30 @@ DROP TABLE t1,t2;
--echo End of 5.0 tests.
#
-# 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),
@@ -3546,7 +3608,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),
@@ -3554,9 +3616,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),
@@ -3565,13 +3627,14 @@ 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;
+
DROP TABLE t1,t2,t3;
#
-# Bug#33204: INTO is allowed in subselect, causing inconsistent results
+# Bug#33204 INTO is allowed in subselect, causing inconsistent results
#
CREATE TABLE t1( a INT );
INSERT INTO t1 VALUES (1),(2);
@@ -3579,50 +3642,50 @@ INSERT INTO t1 VALUES (1),(2);
CREATE TABLE t2( a INT, b INT );
--error ER_PARSE_ERROR
-SELECT *
+SELECT *
FROM (SELECT a INTO @var FROM t1 WHERE a = 2) t1a;
--error ER_PARSE_ERROR
-SELECT *
+SELECT *
FROM (SELECT a INTO OUTFILE 'file' FROM t1 WHERE a = 2) t1a;
--error ER_PARSE_ERROR
-SELECT *
+SELECT *
FROM (SELECT a INTO DUMPFILE 'file' FROM t1 WHERE a = 2) t1a;
--error ER_PARSE_ERROR
-SELECT * FROM (
- SELECT 1 a
- UNION
- SELECT a INTO @var FROM t1 WHERE a = 2
+SELECT * FROM (
+ SELECT 1 a
+ UNION
+ SELECT a INTO @var FROM t1 WHERE a = 2
) t1a;
--error ER_PARSE_ERROR
-SELECT * FROM (
- SELECT 1 a
- UNION
- SELECT a INTO OUTFILE 'file' FROM t1 WHERE a = 2
+SELECT * FROM (
+ SELECT 1 a
+ UNION
+ SELECT a INTO OUTFILE 'file' FROM t1 WHERE a = 2
) t1a;
--error ER_PARSE_ERROR
-SELECT * FROM (
- SELECT 1 a
- UNION
- SELECT a INTO DUMPFILE 'file' FROM t1 WHERE a = 2
+SELECT * FROM (
+ SELECT 1 a
+ UNION
+ SELECT a INTO DUMPFILE 'file' FROM t1 WHERE a = 2
) t1a;
SELECT * FROM (SELECT a FROM t1 WHERE a = 2) t1a;
-SELECT * FROM (
- SELECT a FROM t1 WHERE a = 2
- UNION
- SELECT a FROM t1 WHERE a = 2
+SELECT * FROM (
+ SELECT a FROM t1 WHERE a = 2
+ UNION
+ SELECT a FROM t1 WHERE a = 2
) t1a;
-SELECT * FROM (
- SELECT 1 a
- UNION
- SELECT a FROM t1 WHERE a = 2
- UNION
- SELECT a FROM t1 WHERE a = 2
+SELECT * FROM (
+ SELECT 1 a
+ UNION
+ SELECT a FROM t1 WHERE a = 2
+ UNION
+ SELECT a FROM t1 WHERE a = 2
) t1a;
# This was not allowed previously. Possibly, it should be allowed on the future.
@@ -3661,7 +3724,7 @@ SELECT * FROM (SELECT 1 a UNION SELECT 1
# table_factor: '(' get_select_lex query_expression_body ')' opt_table_alias
# UNION should not be allowed inside the parentheses, nor should
# aliases after.
-#
+#
SELECT * FROM t1 JOIN (SELECT 1 UNION SELECT 1) alias ON 1;
--error ER_PARSE_ERROR
SELECT * FROM t1 JOIN ((SELECT 1 UNION SELECT 1)) ON 1;
@@ -3797,7 +3860,7 @@ SELECT * FROM t1 WHERE a IN ( SELECT 1 I
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));
@@ -3832,7 +3895,7 @@ where v in(select v
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));
=== modified file 'mysql-test/t/synchronization.test'
--- a/mysql-test/t/synchronization.test 2006-11-27 21:19:53 +0000
+++ b/mysql-test/t/synchronization.test 2009-02-09 21:00:15 +0000
@@ -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
=== modified file 'mysql-test/t/timezone_grant.test'
--- a/mysql-test/t/timezone_grant.test 2006-04-22 07:54:25 +0000
+++ b/mysql-test/t/timezone_grant.test 2009-02-05 20:47:23 +0000
@@ -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', 'U
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', 'U
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 t
# 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
| Thread |
|---|
| • bzr commit into mysql-6.0-bugteam branch (Matthias.Leich:3035) | Matthias Leich | 11 Feb |
