summaryrefslogtreecommitdiff
path: root/mysql-test
diff options
context:
space:
mode:
Diffstat (limited to 'mysql-test')
-rw-r--r--mysql-test/r/create.result71
-rw-r--r--mysql-test/r/derived.result6
-rw-r--r--mysql-test/r/func_crypt.result11
-rw-r--r--mysql-test/r/func_str.result3
-rw-r--r--mysql-test/r/multi_update.result15
-rw-r--r--mysql-test/r/query_cache.result1
-rw-r--r--mysql-test/r/rpl_temporary.result74
-rw-r--r--mysql-test/r/subselect.result150
-rw-r--r--mysql-test/r/update.result5
-rw-r--r--mysql-test/t/create.test41
-rw-r--r--mysql-test/t/derived.test6
-rw-r--r--mysql-test/t/func_crypt.test7
-rw-r--r--mysql-test/t/func_str.test2
-rw-r--r--mysql-test/t/multi_update.test6
-rw-r--r--mysql-test/t/outfile.test5
-rw-r--r--mysql-test/t/query_cache.test1
-rw-r--r--mysql-test/t/rpl_temporary.test77
-rw-r--r--mysql-test/t/subselect.test64
-rw-r--r--mysql-test/t/update.test5
19 files changed, 535 insertions, 15 deletions
diff --git a/mysql-test/r/create.result b/mysql-test/r/create.result
index 0be0d624fca..6bfda427f78 100644
--- a/mysql-test/r/create.result
+++ b/mysql-test/r/create.result
@@ -161,3 +161,74 @@ drop table if exists t1;
create table t1 (a int, key(a));
create table t2 (b int, foreign key(b) references t1(a), key(b));
drop table if exists t1,t2;
+drop table if exists t1, t2, t3;
+create table t1(id int not null, name char(20));
+insert into t1 values(10,'mysql'),(20,'monty- the creator');
+create table t2(id int not null);
+insert into t2 values(10),(20);
+create table t3 like t1;
+show create table t3;
+Table Create Table
+t3 CREATE TABLE `t3` (
+ `id` int(11) NOT NULL default '0',
+ `name` char(20) character set latin1 default NULL
+) TYPE=MyISAM CHARSET=latin1
+select * from t3;
+id name
+create table if not exists t3 like t1;
+Warnings:
+Warning 1050 Table 't3' already exists
+select @@warning_count;
+@@warning_count
+1
+create temporary table t3 like t2;
+show create table t3;
+Table Create Table
+t3 CREATE TEMPORARY TABLE `t3` (
+ `id` int(11) NOT NULL default '0'
+) TYPE=MyISAM CHARSET=latin1
+select * from t3;
+id
+drop table t3;
+show create table t3;
+Table Create Table
+t3 CREATE TABLE `t3` (
+ `id` int(11) NOT NULL default '0',
+ `name` char(20) character set latin1 default NULL
+) TYPE=MyISAM CHARSET=latin1
+select * from t3;
+id name
+drop table t2, t3;
+drop database if exists test_$1;
+create database test_$1;
+create table test_$1.t3 like t1;
+create temporary table t3 like test_$1.t3;
+show create table t3;
+Table Create Table
+t3 CREATE TEMPORARY TABLE `t3` (
+ `id` int(11) NOT NULL default '0',
+ `name` char(20) character set latin1 default NULL
+) TYPE=MyISAM CHARSET=latin1
+create table t2 like t3;
+show create table t2;
+Table Create Table
+t2 CREATE TABLE `t2` (
+ `id` int(11) NOT NULL default '0',
+ `name` char(20) character set latin1 default NULL
+) TYPE=MyISAM CHARSET=latin1
+select * from t2;
+id name
+create table t3 like t1;
+create table t3 like test_$1.t3;
+Table 't3' already exists
+create table non_existing_database.t1 like t1;
+Got one of the listed errors
+create table t3 like non_existing_table;
+Unknown table 'non_existing_table'
+create temporary table t3 like t1;
+Table 't3' already exists
+create table t3 like `a/a`;
+Incorrect table name 'a/a'
+drop table t1, t2, t3;
+drop table t3;
+drop database test_$1;
diff --git a/mysql-test/r/derived.result b/mysql-test/r/derived.result
index 6a3aa328175..e2d2b1cb652 100644
--- a/mysql-test/r/derived.result
+++ b/mysql-test/r/derived.result
@@ -130,3 +130,9 @@ SELECT * FROM (SELECT (SELECT * FROM (SELECT 1 as a) as a )) as b;
select * from (select 1 as a) b left join (select 2 as a) c using(a);
a a
1 NULL
+SELECT * FROM (SELECT 1 UNION SELECT a) b;
+Unknown column 'a' in 'field list'
+SELECT 1 as a FROM (SELECT a UNION SELECT 1) b;
+Unknown column 'a' in 'field list'
+SELECT 1 as a FROM (SELECT 1 UNION SELECT a) b;
+Unknown column 'a' in 'field list'
diff --git a/mysql-test/r/func_crypt.result b/mysql-test/r/func_crypt.result
index ad3a64ccd1d..461ae1e7e09 100644
--- a/mysql-test/r/func_crypt.result
+++ b/mysql-test/r/func_crypt.result
@@ -1,6 +1,15 @@
select length(encrypt('foo', 'ff')) <> 0;
length(encrypt('foo', 'ff')) <> 0
1
-select old_password('test'),length(password("1")),length(encrypt('test')),encrypt('test','aa');
+select password("a",""), password("a",NULL), password("","a"), password(NULL,"a");
+password("a","") password("a",NULL) password("","a") password(NULL,"a")
+*2517f7235d68d4ba2e5019c93420523101157a792c01 NULL NULL
+select password("aaaaaaaaaaaaaaaa","a"), password("a","aaaaaaaaaaaaaaaa");
+password("aaaaaaaaaaaaaaaa","a") password("a","aaaaaaaaaaaaaaaa")
+*2cd3b9a44e9a9994789a30f935c92f45a96c5472f381 *37c7c5c794ff144819f2531bf03c57772cd84e40db09
+select old_password('test'), length(password("1")), length(encrypt('test')), encrypt('test','aa');
old_password('test') length(password("1")) length(encrypt('test')) encrypt('test','aa')
378b243e220ca493 45 13 aaqPiZY5xR5l.
+select old_password(""), old_password(NULL), password(""), password(NULL);
+old_password("") old_password(NULL) password("") password(NULL)
+ NULL NULL
diff --git a/mysql-test/r/func_str.result b/mysql-test/r/func_str.result
index d0358aad6ba..429574575f1 100644
--- a/mysql-test/r/func_str.result
+++ b/mysql-test/r/func_str.result
@@ -170,6 +170,9 @@ quote(concat('abc\'', '\\cba'))
select quote(1/0), quote('\0\Z');
quote(1/0) quote('\0\Z')
NULL '\0\Z'
+select length(quote(concat(char(0), "test")));
+length(quote(concat(char(0), "test")))
+8
select reverse("");
reverse("")
diff --git a/mysql-test/r/multi_update.result b/mysql-test/r/multi_update.result
index fe028a4cb95..b037fc87996 100644
--- a/mysql-test/r/multi_update.result
+++ b/mysql-test/r/multi_update.result
@@ -235,6 +235,21 @@ select * from t2;
n d
1 30
1 30
+UPDATE t1 a ,t2 b SET a.d=b.d,b.d=30 WHERE a.n=b.n;
+select * from t1;
+n d
+1 30
+3 2
+select * from t2;
+n d
+1 30
+1 30
+DELETE t1, t2 FROM t1 a,t2 b where a.n=b.n;
+select * from t1;
+n d
+3 2
+select * from t2;
+n d
drop table t1,t2;
drop table if exists t1,t2,t3;
CREATE TABLE t1 ( broj int(4) unsigned NOT NULL default '0', naziv char(25) NOT NULL default 'NEPOZNAT', PRIMARY KEY (broj)) TYPE=MyISAM;
diff --git a/mysql-test/r/query_cache.result b/mysql-test/r/query_cache.result
index a37313a150a..ad9294e6d3a 100644
--- a/mysql-test/r/query_cache.result
+++ b/mysql-test/r/query_cache.result
@@ -531,6 +531,7 @@ i
show status like "Qcache_queries_in_cache";
Variable_name Value
Qcache_queries_in_cache 2
+update t1 set i=(select distinct 1 from (select * from t2) a);
drop table t1, t2, t3;
use mysql;
select * from db;
diff --git a/mysql-test/r/rpl_temporary.result b/mysql-test/r/rpl_temporary.result
new file mode 100644
index 00000000000..a628936b600
--- /dev/null
+++ b/mysql-test/r/rpl_temporary.result
@@ -0,0 +1,74 @@
+stop slave;
+drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t9;
+reset master;
+reset slave;
+drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t9;
+start slave;
+drop table if exists t1;
+create table t1(f int);
+drop table if exists t2;
+create table t2(f int);
+insert into t1 values (1),(2),(3),(4),(5),(6),(7),(8),(9),(10);
+create temporary table t3(f int);
+insert into t3 select * from t1 where f<6;
+create temporary table t3(f int);
+insert into t2 select count(*) from t3;
+insert into t3 select * from t1 where f>=4;
+drop temporary table t3;
+insert into t2 select count(*) from t3;
+drop temporary table t3;
+select * from t2;
+f
+5
+7
+show binlog events;
+Log_name Pos Event_type Server_id Orig_log_pos Info
+master-bin.000001 4 Start 1 4 Server ver: 4.1.0-alpha-debug-log, Binlog ver: 3
+master-bin.000001 79 Query 1 79 use `test`; create table t1(f int)
+master-bin.000001 136 Query 1 136 use `test`; create table t2(f int)
+master-bin.000001 193 Query 1 193 use `test`; insert into t1 values (1),(2),(3),(4),(5),(6),(7),(8),(9),(10)
+master-bin.000001 290 Query 1 290 use `test`; create temporary table t3(f int)
+master-bin.000001 357 Query 1 357 use `test`; insert into t3 select * from t1 where f<6
+master-bin.000001 433 Query 1 433 use `test`; create temporary table t3(f int)
+master-bin.000001 500 Query 1 500 use `test`; insert into t2 select count(*) from t3
+master-bin.000001 573 Query 1 573 use `test`; insert into t3 select * from t1 where f>=4
+master-bin.000001 650 Query 1 650 use `test`; drop temporary table t3
+master-bin.000001 708 Query 1 708 use `test`; insert into t2 select count(*) from t3
+master-bin.000001 781 Query 1 781 use `test`; drop temporary table t3
+drop table if exists t1;
+drop table if exists t2;
+use test;
+SET TIMESTAMP=1040323920;
+create table t1(f int);
+SET TIMESTAMP=1040323931;
+create table t2(f int);
+SET TIMESTAMP=1040323938;
+insert into t1 values (1),(2),(3),(4),(5),(6),(7),(8),(9),(10);
+SET TIMESTAMP=1040323945;
+SET @@session.pseudo_thread_id=1;
+create temporary table t3(f int);
+SET TIMESTAMP=1040323952;
+SET @@session.pseudo_thread_id=1;
+insert into t3 select * from t1 where f<6;
+SET TIMESTAMP=1040324145;
+SET @@session.pseudo_thread_id=2;
+create temporary table t3(f int);
+SET TIMESTAMP=1040324186;
+SET @@session.pseudo_thread_id=1;
+insert into t2 select count(*) from t3;
+SET TIMESTAMP=1040324200;
+SET @@session.pseudo_thread_id=2;
+insert into t3 select * from t1 where f>=4;
+SET TIMESTAMP=1040324211;
+SET @@session.pseudo_thread_id=1;
+drop temporary table t3;
+SET TIMESTAMP=1040324219;
+SET @@session.pseudo_thread_id=2;
+insert into t2 select count(*) from t3;
+SET TIMESTAMP=1040324224;
+SET @@session.pseudo_thread_id=2;
+drop temporary table t3;
+select * from t2;
+f
+5
+7
diff --git a/mysql-test/r/subselect.result b/mysql-test/r/subselect.result
index f12e0396694..38a8e0368c6 100644
--- a/mysql-test/r/subselect.result
+++ b/mysql-test/r/subselect.result
@@ -1,13 +1,32 @@
select (select 2);
(select 2)
2
+explain select (select 2);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY NULL NULL NULL NULL NULL NULL NULL No tables used
+Warnings:
+Note 1247 Select 2 was reduced during optimisation
SELECT (SELECT 1) UNION SELECT (SELECT 2);
(SELECT 1)
1
2
+explain SELECT (SELECT 1) UNION SELECT (SELECT 2);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY NULL NULL NULL NULL NULL NULL NULL No tables used
+3 UNION NULL NULL NULL NULL NULL NULL NULL No tables used
+Warnings:
+Note 1247 Select 2 was reduced during optimisation
+Note 1247 Select 4 was reduced during optimisation
SELECT (SELECT (SELECT 0 UNION SELECT 0));
(SELECT (SELECT 0 UNION SELECT 0))
0
+explain SELECT (SELECT (SELECT 0 UNION SELECT 0));
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY NULL NULL NULL NULL NULL NULL NULL No tables used
+3 SUBSELECT NULL NULL NULL NULL NULL NULL NULL No tables used
+4 UNION NULL NULL NULL NULL NULL NULL NULL No tables used
+Warnings:
+Note 1247 Select 2 was reduced during optimisation
SELECT (SELECT 1 FROM (SELECT 1) as b HAVING a=1) as a;
Reference 'a' not supported (forward reference in item list)
SELECT (SELECT 1 FROM (SELECT 1) as b HAVING b=1) as a,(SELECT 1 FROM (SELECT 1) as c HAVING a=1) as b;
@@ -52,6 +71,54 @@ a
SELECT (SELECT 1) as a FROM (SELECT 1) b WHERE (SELECT a) IS NOT NULL;
a
1
+SELECT (SELECT 1,2,3) = ROW(1,2,3);
+(SELECT 1,2,3) = ROW(1,2,3)
+1
+SELECT (SELECT 1,2,3) = ROW(1,2,1);
+(SELECT 1,2,3) = ROW(1,2,1)
+0
+SELECT (SELECT 1,2,3) < ROW(1,2,1);
+(SELECT 1,2,3) < ROW(1,2,1)
+0
+SELECT (SELECT 1,2,3) > ROW(1,2,1);
+(SELECT 1,2,3) > ROW(1,2,1)
+1
+SELECT (SELECT 1,2,3) = ROW(1,2,NULL);
+(SELECT 1,2,3) = ROW(1,2,NULL)
+NULL
+SELECT ROW(1,2,3) = (SELECT 1,2,3);
+ROW(1,2,3) = (SELECT 1,2,3)
+1
+SELECT ROW(1,2,3) = (SELECT 1,2,1);
+ROW(1,2,3) = (SELECT 1,2,1)
+0
+SELECT ROW(1,2,3) < (SELECT 1,2,1);
+ROW(1,2,3) < (SELECT 1,2,1)
+0
+SELECT ROW(1,2,3) > (SELECT 1,2,1);
+ROW(1,2,3) > (SELECT 1,2,1)
+1
+SELECT ROW(1,2,3) = (SELECT 1,2,NULL);
+ROW(1,2,3) = (SELECT 1,2,NULL)
+NULL
+SELECT (SELECT 1.5,2,'a') = ROW(1.5,2,'a');
+(SELECT 1.5,2,'a') = ROW(1.5,2,'a')
+1
+SELECT (SELECT 1.5,2,'a') = ROW(1.5,2,'b');
+(SELECT 1.5,2,'a') = ROW(1.5,2,'b')
+0
+SELECT (SELECT 1.5,2,'a') = ROW('b',2,'b');
+(SELECT 1.5,2,'a') = ROW('b',2,'b')
+0
+SELECT (SELECT 'b',2,'a') = ROW(1.5,2,'a');
+(SELECT 'b',2,'a') = ROW(1.5,2,'a')
+0
+SELECT (SELECT 1.5,2,'a') = ROW(1.5,'c','a');
+(SELECT 1.5,2,'a') = ROW(1.5,'c','a')
+0
+SELECT (SELECT 1.5,'c','a') = ROW(1.5,2,'a');
+(SELECT 1.5,'c','a') = ROW(1.5,2,'a')
+0
drop table if exists t1,t2,t3,t4,t5,t6,t7,t8;
create table t1 (a int);
create table t2 (a int, b int);
@@ -86,20 +153,20 @@ select * from t2 where t2.b=(select a from t3 order by 1 desc limit 1);
a b
1 7
2 7
-select * from t2 where t2.b=(select a from t3 order by 1 desc limit 1)
+(select * from t2 where t2.b=(select a from t3 order by 1 desc limit 1))
union (select * from t4 order by a limit 2) limit 3;
a b
1 7
2 7
3 8
-select * from t2 where t2.b=(select a from t3 order by 1 desc limit 1)
+(select * from t2 where t2.b=(select a from t3 order by 1 desc limit 1))
union (select * from t4 where t4.b=(select max(t2.a)*4 from t2) order by a);
a b
1 7
2 7
3 8
4 8
-explain select * from t2 where t2.b=(select a from t3 order by 1 desc limit 1)
+explain (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);
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t2 ALL NULL NULL NULL NULL 2 Using where
@@ -581,12 +648,13 @@ id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t ref id id 5 const 1 Using where; Using index
3 SUBSELECT NULL NULL NULL NULL NULL NULL NULL No tables used
Warnings:
+Note 1247 Select 3 was reduced during optimisation
Note 1247 Select 2 was reduced during optimisation
EXPLAIN SELECT * FROM t WHERE id IN (SELECT 1 UNION SELECT 3);
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t index NULL id 5 NULL 2 Using where; Using index
2 DEPENDENT SUBSELECT NULL NULL NULL NULL NULL NULL NULL No tables used
-3 UNION NULL NULL NULL NULL NULL NULL NULL No tables used
+3 DEPENDENT UNION NULL NULL NULL NULL NULL NULL NULL No tables used
SELECT * FROM t WHERE id IN (SELECT 5 UNION SELECT 3);
id
SELECT * FROM t WHERE id IN (SELECT 5 UNION SELECT 2);
@@ -602,7 +670,7 @@ CREATE TABLE t1 (id int(11) default NULL, KEY id (id)) TYPE=MyISAM CHARSET=latin
INSERT INTO t1 values (1),(1);
UPDATE t SET id=(SELECT * FROM t1);
Subselect returns more than 1 record
-drop table t;
+drop table t, t1;
create table t (a int);
insert into t values (1),(2),(3);
select 1 IN (SELECT * from t);
@@ -704,6 +772,16 @@ NULL
select 10.5 > ANY (SELECT * from t);
10.5 > ANY (SELECT * from t)
1
+explain select (select a+1) from t;
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY t ALL NULL NULL NULL NULL 3
+Warnings:
+Note 1247 Select 2 was reduced during optimisation
+select (select a+1) from t;
+(select a+1)
+2.5
+NULL
+4.5
drop table t;
create table t (a float);
select 10.5 IN (SELECT * from t LIMIT 1);
@@ -711,3 +789,65 @@ This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery'
select 10.5 IN (SELECT * from t LIMIT 1 UNION SELECT 1.5);
This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery'
drop table t;
+create table t1 (a int, b int, c varchar(10));
+create table t2 (a int);
+insert into t1 values (1,2,'a'),(2,3,'b'),(3,4,'c');
+insert into t2 values (1),(2),(NULL);
+select a, (select a,b,c from t1 where t1.a=t2.a) = ROW(a,2,'a'),(select c from t1 where a=t2.a) from t2;
+a (select a,b,c from t1 where t1.a=t2.a) = ROW(a,2,'a') (select c from t1 where a=t2.a)
+1 1 a
+2 0 b
+NULL NULL NULL
+select a, (select a,b,c from t1 where t1.a=t2.a) = ROW(a,3,'b'),(select c from t1 where a=t2.a) from t2;
+a (select a,b,c from t1 where t1.a=t2.a) = ROW(a,3,'b') (select c from t1 where a=t2.a)
+1 0 a
+2 1 b
+NULL NULL NULL
+select a, (select a,b,c from t1 where t1.a=t2.a) = ROW(a,4,'c'),(select c from t1 where a=t2.a) from t2;
+a (select a,b,c from t1 where t1.a=t2.a) = ROW(a,4,'c') (select c from t1 where a=t2.a)
+1 0 a
+2 0 b
+NULL NULL NULL
+drop table t1,t2;
+drop table if exists t;
+create table t (a int, b real, c varchar(10));
+insert into t values (1, 1, 'a'), (2,2,'b'), (NULL, 2, 'b');
+select ROW(1, 1, 'a') IN (select a,b,c from t);
+ROW(1, 1, 'a') IN (select a,b,c from t)
+1
+select ROW(1, 2, 'a') IN (select a,b,c from t);
+ROW(1, 2, 'a') IN (select a,b,c from t)
+NULL
+select ROW(1, 1, 'a') IN (select b,a,c from t);
+ROW(1, 1, 'a') IN (select b,a,c from t)
+1
+select ROW(1, 1, 'a') IN (select a,b,c from t where a is not null);
+ROW(1, 1, 'a') IN (select a,b,c from t where a is not null)
+1
+select ROW(1, 2, 'a') IN (select a,b,c from t where a is not null);
+ROW(1, 2, 'a') IN (select a,b,c from t where a is not null)
+0
+select ROW(1, 1, 'a') IN (select b,a,c from t where a is not null);
+ROW(1, 1, 'a') IN (select b,a,c from t where a is not null)
+1
+select ROW(1, 1, 'a') IN (select a,b,c from t where c='b' or c='a');
+ROW(1, 1, 'a') IN (select a,b,c from t where c='b' or c='a')
+1
+select ROW(1, 2, 'a') IN (select a,b,c from t where c='b' or c='a');
+ROW(1, 2, 'a') IN (select a,b,c from t where c='b' or c='a')
+NULL
+select ROW(1, 1, 'a') IN (select b,a,c from t where c='b' or c='a');
+ROW(1, 1, 'a') IN (select b,a,c from t where c='b' or c='a')
+1
+select ROW(1, 1, 'a') IN (select b,a,c from t limit 2);
+This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery'
+drop table t;
+create table t (a int);
+insert into t values (1);
+do @a:=(SELECT a from t);
+select @a;
+@a
+1
+drop table t;
+do (SELECT a from t);
+Table 'test.t' doesn't exist
diff --git a/mysql-test/r/update.result b/mysql-test/r/update.result
index ba5c1c6e28f..60c975b540e 100644
--- a/mysql-test/r/update.result
+++ b/mysql-test/r/update.result
@@ -106,9 +106,12 @@ create table t1 (a int not null, b int not null);
insert into t1 values (1,1),(1,2),(1,3);
update t1 set b=4 where a=1 order by b asc limit 1;
update t1 set b=4 where a=1 order by b desc limit 1;
+create table t2 (a int not null, b int not null);
+insert into t2 values (1,1),(1,2),(1,3);
select * from t1;
a b
1 4
1 2
1 4
-drop table t1;
+update t1 set b=(select distinct 1 from (select * from t2) a);
+drop table t1,t2;
diff --git a/mysql-test/t/create.test b/mysql-test/t/create.test
index 3bad053875c..e057ffaebb0 100644
--- a/mysql-test/t/create.test
+++ b/mysql-test/t/create.test
@@ -113,3 +113,44 @@ drop table if exists t1;
create table t1 (a int, key(a));
create table t2 (b int, foreign key(b) references t1(a), key(b));
drop table if exists t1,t2;
+
+#
+# Test for CREATE TABLE .. LIKE ..
+#
+
+drop table if exists t1, t2, t3;
+create table t1(id int not null, name char(20));
+insert into t1 values(10,'mysql'),(20,'monty- the creator');
+create table t2(id int not null);
+insert into t2 values(10),(20);
+create table t3 like t1;
+show create table t3;
+select * from t3;
+create table if not exists t3 like t1;
+select @@warning_count;
+create temporary table t3 like t2;
+show create table t3;
+select * from t3;
+drop table t3;
+show create table t3;
+select * from t3;
+drop table t2, t3;
+drop database if exists test_$1;
+create database test_$1;
+create table test_$1.t3 like t1;
+create temporary table t3 like test_$1.t3;
+show create table t3;
+create table t2 like t3;
+show create table t2;
+select * from t2;
+create table t3 like t1;
+!$1050 create table t3 like test_$1.t3;
+--error 1044,1
+create table non_existing_database.t1 like t1;
+!$1051 create table t3 like non_existing_table;
+!$1050 create temporary table t3 like t1;
+!$1103 create table t3 like `a/a`;
+drop table t1, t2, t3;
+drop table t3;
+drop database test_$1;
+
diff --git a/mysql-test/t/derived.test b/mysql-test/t/derived.test
index 0c366b65797..8767f9209b3 100644
--- a/mysql-test/t/derived.test
+++ b/mysql-test/t/derived.test
@@ -48,3 +48,9 @@ explain select count(*) from t1 as tt1, (select * from t1) as tt2;
drop table if exists t1;
SELECT * FROM (SELECT (SELECT * FROM (SELECT 1 as a) as a )) as b;
select * from (select 1 as a) b left join (select 2 as a) c using(a);
+--error 1054
+SELECT * FROM (SELECT 1 UNION SELECT a) b;
+--error 1054
+SELECT 1 as a FROM (SELECT a UNION SELECT 1) b;
+--error 1054
+SELECT 1 as a FROM (SELECT 1 UNION SELECT a) b;
diff --git a/mysql-test/t/func_crypt.test b/mysql-test/t/func_crypt.test
index 812bdade39f..af0ef661d06 100644
--- a/mysql-test/t/func_crypt.test
+++ b/mysql-test/t/func_crypt.test
@@ -1,3 +1,8 @@
select length(encrypt('foo', 'ff')) <> 0;
--replace_result $1$aa$4OSUA5cjdx0RUQ08opV27/ aaqPiZY5xR5l.
-select old_password('test'),length(password("1")),length(encrypt('test')),encrypt('test','aa');
+
+# Test new and old password handling functions
+select password("a",""), password("a",NULL), password("","a"), password(NULL,"a");
+select password("aaaaaaaaaaaaaaaa","a"), password("a","aaaaaaaaaaaaaaaa");
+select old_password('test'), length(password("1")), length(encrypt('test')), encrypt('test','aa');
+select old_password(""), old_password(NULL), password(""), password(NULL);
diff --git a/mysql-test/t/func_str.test b/mysql-test/t/func_str.test
index d355cc95317..97b771e7363 100644
--- a/mysql-test/t/func_str.test
+++ b/mysql-test/t/func_str.test
@@ -69,7 +69,7 @@ select decode(encode("abcdef","monty"),"monty")="abcdef";
select quote('\'\"\\test');
select quote(concat('abc\'', '\\cba'));
select quote(1/0), quote('\0\Z');
-
+select length(quote(concat(char(0), "test")));
#
# Wrong usage of functions
#
diff --git a/mysql-test/t/multi_update.test b/mysql-test/t/multi_update.test
index 088b355a17c..29239a022ec 100644
--- a/mysql-test/t/multi_update.test
+++ b/mysql-test/t/multi_update.test
@@ -213,6 +213,12 @@ insert into t2 values(1,10),(1,20);
UPDATE t1,t2 SET t1.d=t2.d,t2.d=30 WHERE t1.n=t2.n;
select * from t1;
select * from t2;
+UPDATE t1 a ,t2 b SET a.d=b.d,b.d=30 WHERE a.n=b.n;
+select * from t1;
+select * from t2;
+DELETE t1, t2 FROM t1 a,t2 b where a.n=b.n;
+select * from t1;
+select * from t2;
drop table t1,t2;
drop table if exists t1,t2,t3;
CREATE TABLE t1 ( broj int(4) unsigned NOT NULL default '0', naziv char(25) NOT NULL default 'NEPOZNAT', PRIMARY KEY (broj)) TYPE=MyISAM;
diff --git a/mysql-test/t/outfile.test b/mysql-test/t/outfile.test
index c126d221bd2..a944df01051 100644
--- a/mysql-test/t/outfile.test
+++ b/mysql-test/t/outfile.test
@@ -21,3 +21,8 @@
#select * into dumpfile "/tmp/select-test.99" from t1;
#select load_file("/tmp/select-test.not-exist");
#drop table t1;
+#drop table if exists t;
+#CREATE TABLE t ( t timestamp NOT NULL, c char(200) character set latin1 NOT NULL default '', i int(11), v varchar(200), b blob, KEY t (t)) TYPE=MyISAM;
+#INSERT INTO t VALUES ('2002-12-20 12:01:20','',1,"aaa","bbb");
+#select * from t into outfile "check";
+#drop table if exists t;
diff --git a/mysql-test/t/query_cache.test b/mysql-test/t/query_cache.test
index 6c3f3d6ac52..eeaf1a83d1a 100644
--- a/mysql-test/t/query_cache.test
+++ b/mysql-test/t/query_cache.test
@@ -368,6 +368,7 @@ select * from t2;
show status like "Qcache_queries_in_cache";
select * from t3;
show status like "Qcache_queries_in_cache";
+update t1 set i=(select distinct 1 from (select * from t2) a);
drop table t1, t2, t3;
#
diff --git a/mysql-test/t/rpl_temporary.test b/mysql-test/t/rpl_temporary.test
new file mode 100644
index 00000000000..1cb53c77f1f
--- /dev/null
+++ b/mysql-test/t/rpl_temporary.test
@@ -0,0 +1,77 @@
+source include/master-slave.inc;
+
+connect (con1,localhost,root,,);
+connect (con2,localhost,root,,);
+
+drop table if exists t1;
+create table t1(f int);
+drop table if exists t2;
+create table t2(f int);
+insert into t1 values (1),(2),(3),(4),(5),(6),(7),(8),(9),(10);
+
+connection con1;
+create temporary table t3(f int);
+insert into t3 select * from t1 where f<6;
+sleep 1;
+
+connection con2;
+create temporary table t3(f int);
+sleep 1;
+
+connection con1;
+insert into t2 select count(*) from t3;
+sleep 1;
+
+connection con2;
+insert into t3 select * from t1 where f>=4;
+sleep 1;
+
+connection con1;
+drop temporary table t3;
+sleep 1;
+
+connection con2;
+insert into t2 select count(*) from t3;
+drop temporary table t3;
+
+select * from t2;
+
+show binlog events;
+
+drop table if exists t1;
+drop table if exists t2;
+
+use test;
+SET TIMESTAMP=1040323920;
+create table t1(f int);
+SET TIMESTAMP=1040323931;
+create table t2(f int);
+SET TIMESTAMP=1040323938;
+insert into t1 values (1),(2),(3),(4),(5),(6),(7),(8),(9),(10);
+
+SET TIMESTAMP=1040323945;
+SET @@session.pseudo_thread_id=1;
+create temporary table t3(f int);
+SET TIMESTAMP=1040323952;
+SET @@session.pseudo_thread_id=1;
+insert into t3 select * from t1 where f<6;
+SET TIMESTAMP=1040324145;
+SET @@session.pseudo_thread_id=2;
+create temporary table t3(f int);
+SET TIMESTAMP=1040324186;
+SET @@session.pseudo_thread_id=1;
+insert into t2 select count(*) from t3;
+SET TIMESTAMP=1040324200;
+SET @@session.pseudo_thread_id=2;
+insert into t3 select * from t1 where f>=4;
+SET TIMESTAMP=1040324211;
+SET @@session.pseudo_thread_id=1;
+drop temporary table t3;
+SET TIMESTAMP=1040324219;
+SET @@session.pseudo_thread_id=2;
+insert into t2 select count(*) from t3;
+SET TIMESTAMP=1040324224;
+SET @@session.pseudo_thread_id=2;
+drop temporary table t3;
+
+select * from t2; \ No newline at end of file
diff --git a/mysql-test/t/subselect.test b/mysql-test/t/subselect.test
index 347d6276280..de07df1905b 100644
--- a/mysql-test/t/subselect.test
+++ b/mysql-test/t/subselect.test
@@ -1,6 +1,9 @@
select (select 2);
+explain select (select 2);
SELECT (SELECT 1) UNION SELECT (SELECT 2);
+explain SELECT (SELECT 1) UNION SELECT (SELECT 2);
SELECT (SELECT (SELECT 0 UNION SELECT 0));
+explain SELECT (SELECT (SELECT 0 UNION SELECT 0));
-- error 1245
SELECT (SELECT 1 FROM (SELECT 1) as b HAVING a=1) as a;
-- error 1245
@@ -26,6 +29,22 @@ select (SELECT 1 FROM (SELECT 1) a PROCEDURE ANALYSE(1));
SELECT 1 FROM (SELECT 1) a PROCEDURE ANALYSE((SELECT 1));
SELECT (SELECT 1) as a FROM (SELECT 1) b WHERE (SELECT a) IS NULL;
SELECT (SELECT 1) as a FROM (SELECT 1) b WHERE (SELECT a) IS NOT NULL;
+SELECT (SELECT 1,2,3) = ROW(1,2,3);
+SELECT (SELECT 1,2,3) = ROW(1,2,1);
+SELECT (SELECT 1,2,3) < ROW(1,2,1);
+SELECT (SELECT 1,2,3) > ROW(1,2,1);
+SELECT (SELECT 1,2,3) = ROW(1,2,NULL);
+SELECT ROW(1,2,3) = (SELECT 1,2,3);
+SELECT ROW(1,2,3) = (SELECT 1,2,1);
+SELECT ROW(1,2,3) < (SELECT 1,2,1);
+SELECT ROW(1,2,3) > (SELECT 1,2,1);
+SELECT ROW(1,2,3) = (SELECT 1,2,NULL);
+SELECT (SELECT 1.5,2,'a') = ROW(1.5,2,'a');
+SELECT (SELECT 1.5,2,'a') = ROW(1.5,2,'b');
+SELECT (SELECT 1.5,2,'a') = ROW('b',2,'b');
+SELECT (SELECT 'b',2,'a') = ROW(1.5,2,'a');
+SELECT (SELECT 1.5,2,'a') = ROW(1.5,'c','a');
+SELECT (SELECT 1.5,'c','a') = ROW(1.5,2,'a');
drop table if exists t1,t2,t3,t4,t5,t6,t7,t8;
create table t1 (a int);
@@ -44,11 +63,11 @@ select (select a from t3), a from t2;
select * from t2 where t2.a=(select a from t1);
insert into t3 values (6),(7),(3);
select * from t2 where t2.b=(select a from t3 order by 1 desc limit 1);
-select * from t2 where t2.b=(select a from t3 order by 1 desc limit 1)
+(select * from t2 where t2.b=(select a from t3 order by 1 desc limit 1))
union (select * from t4 order by a limit 2) limit 3;
-select * from t2 where t2.b=(select a from t3 order by 1 desc limit 1)
+(select * from t2 where t2.b=(select a from t3 order by 1 desc limit 1))
union (select * from t4 where t4.b=(select max(t2.a)*4 from t2) order by a);
-explain select * from t2 where t2.b=(select a from t3 order by 1 desc limit 1)
+explain (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
@@ -363,7 +382,7 @@ CREATE TABLE t1 (id int(11) default NULL, KEY id (id)) TYPE=MyISAM CHARSET=latin
INSERT INTO t1 values (1),(1);
-- error 1240
UPDATE t SET id=(SELECT * FROM t1);
-drop table t;
+drop table t, t1;
#NULL test
@@ -408,6 +427,8 @@ select 1.5 > ALL (SELECT * from t);
select 10.5 > ALL (SELECT * from t);
select 1.5 > ANY (SELECT * from t);
select 10.5 > ANY (SELECT * from t);
+explain select (select a+1) from t;
+select (select a+1) from t;
drop table t;
#LIMIT is not supported now
@@ -416,4 +437,37 @@ create table t (a float);
select 10.5 IN (SELECT * from t LIMIT 1);
-- error 1235
select 10.5 IN (SELECT * from t LIMIT 1 UNION SELECT 1.5);
-drop table t; \ No newline at end of file
+drop table t;
+
+create table t1 (a int, b int, c varchar(10));
+create table t2 (a int);
+insert into t1 values (1,2,'a'),(2,3,'b'),(3,4,'c');
+insert into t2 values (1),(2),(NULL);
+select a, (select a,b,c from t1 where t1.a=t2.a) = ROW(a,2,'a'),(select c from t1 where a=t2.a) from t2;
+select a, (select a,b,c from t1 where t1.a=t2.a) = ROW(a,3,'b'),(select c from t1 where a=t2.a) from t2;
+select a, (select a,b,c from t1 where t1.a=t2.a) = ROW(a,4,'c'),(select c from t1 where a=t2.a) from t2;
+drop table t1,t2;
+
+drop table if exists t;
+create table t (a int, b real, c varchar(10));
+insert into t values (1, 1, 'a'), (2,2,'b'), (NULL, 2, 'b');
+select ROW(1, 1, 'a') IN (select a,b,c from t);
+select ROW(1, 2, 'a') IN (select a,b,c from t);
+select ROW(1, 1, 'a') IN (select b,a,c from t);
+select ROW(1, 1, 'a') IN (select a,b,c from t where a is not null);
+select ROW(1, 2, 'a') IN (select a,b,c from t where a is not null);
+select ROW(1, 1, 'a') IN (select b,a,c from t where a is not null);
+select ROW(1, 1, 'a') IN (select a,b,c from t where c='b' or c='a');
+select ROW(1, 2, 'a') IN (select a,b,c from t where c='b' or c='a');
+select ROW(1, 1, 'a') IN (select b,a,c from t where c='b' or c='a');
+-- error 1235
+select ROW(1, 1, 'a') IN (select b,a,c from t limit 2);
+drop table t;
+
+create table t (a int);
+insert into t values (1);
+do @a:=(SELECT a from t);
+select @a;
+drop table t;
+-- error 1146
+do (SELECT a from t);
diff --git a/mysql-test/t/update.test b/mysql-test/t/update.test
index 5cbbd2a350e..24620982cda 100644
--- a/mysql-test/t/update.test
+++ b/mysql-test/t/update.test
@@ -85,5 +85,8 @@ create table t1 (a int not null, b int not null);
insert into t1 values (1,1),(1,2),(1,3);
update t1 set b=4 where a=1 order by b asc limit 1;
update t1 set b=4 where a=1 order by b desc limit 1;
+create table t2 (a int not null, b int not null);
+insert into t2 values (1,1),(1,2),(1,3);
select * from t1;
-drop table t1;
+update t1 set b=(select distinct 1 from (select * from t2) a);
+drop table t1,t2;
View Lorry Controller Status