summaryrefslogtreecommitdiff
path: root/mysql-test/r
diff options
context:
space:
mode:
Diffstat (limited to 'mysql-test/r')
-rw-r--r--mysql-test/r/bigint.result30
-rw-r--r--mysql-test/r/join.result14
-rw-r--r--mysql-test/r/merge.result248
-rw-r--r--mysql-test/r/null_key.result2
-rw-r--r--mysql-test/r/rpl000002.result2
-rw-r--r--mysql-test/r/rpl000015.result2
-rw-r--r--mysql-test/r/rpl000016.result4
-rw-r--r--mysql-test/r/rpl_log.result16
-rw-r--r--mysql-test/r/show_check.result25
9 files changed, 329 insertions, 14 deletions
diff --git a/mysql-test/r/bigint.result b/mysql-test/r/bigint.result
index 1a1748155ce..1c4a94ef1ee 100644
--- a/mysql-test/r/bigint.result
+++ b/mysql-test/r/bigint.result
@@ -25,3 +25,33 @@ select * from t1;
a
18446744073709551614
drop table t1;
+create table t1 ( a int not null default 1, big bigint );
+insert into t1 (big) values (-1),(12345678901234567),(9223372036854775807),(18446744073709551615);
+select min(big),max(big),max(big)-1 from t1;
+min(big) max(big) max(big)-1
+-1 9223372036854775807 9223372036854775806
+select min(big),max(big),max(big)-1 from t1 group by a;
+min(big) max(big) max(big)-1
+-1 9223372036854775807 9223372036854775806
+alter table t1 modify big bigint unsigned not null;
+select min(big),max(big),max(big)-1 from t1;
+min(big) max(big) max(big)-1
+12345678901234567 18446744073709551615 18446744073709551614
+select min(big),max(big),max(big)-1 from t1 group by a;
+min(big) max(big) max(big)-1
+12345678901234567 18446744073709551615 18446744073709551614
+alter table t1 add key (big);
+select min(big),max(big),max(big)-1 from t1;
+min(big) max(big) max(big)-1
+12345678901234567 18446744073709551615 18446744073709551614
+select min(big),max(big),max(big)-1 from t1 group by a;
+min(big) max(big) max(big)-1
+12345678901234567 18446744073709551615 18446744073709551614
+alter table t1 modify big bigint not null;
+select min(big),max(big),max(big)-1 from t1;
+min(big) max(big) max(big)-1
+-1 9223372036854775807 9223372036854775806
+select min(big),max(big),max(big)-1 from t1 group by a;
+min(big) max(big) max(big)-1
+-1 9223372036854775807 9223372036854775806
+drop table t1;
diff --git a/mysql-test/r/join.result b/mysql-test/r/join.result
index 6ffdf252375..d738ff6be73 100644
--- a/mysql-test/r/join.result
+++ b/mysql-test/r/join.result
@@ -91,3 +91,17 @@ a
select t1.a from t1 as t1 left join t1 as t2 using (a) left join t1 as t3 using (a) left join t1 as t4 using (a) left join t1 as t5 using (a) left join t1 as t6 using (a) left join t1 as t7 using (a) left join t1 as t8 using (a) left join t1 as t9 using (a) left join t1 as t10 using (a) left join t1 as t11 using (a) left join t1 as t12 using (a) left join t1 as t13 using (a) left join t1 as t14 using (a) left join t1 as t15 using (a) left join t1 as t16 using (a) left join t1 as t17 using (a) left join t1 as t18 using (a) left join t1 as t19 using (a) left join t1 as t20 using (a) left join t1 as t21 using (a) left join t1 as t22 using (a) left join t1 as t23 using (a) left join t1 as t24 using (a) left join t1 as t25 using (a) left join t1 as t26 using (a) left join t1 as t27 using (a) left join t1 as t28 using (a) left join t1 as t29 using (a) left join t1 as t30 using (a) left join t1 as t31 using (a) left join t1 as t32 using (a) left join t1 as t33 using (a) left join t1 as t34 using (a) left join t1 as t35 using (a) left join t1 as t36 using (a) left join t1 as t37 using (a) left join t1 as t38 using (a) left join t1 as t39 using (a) left join t1 as t40 using (a) left join t1 as t41 using (a) left join t1 as t42 using (a) left join t1 as t43 using (a) left join t1 as t44 using (a) left join t1 as t45 using (a) left join t1 as t46 using (a) left join t1 as t47 using (a) left join t1 as t48 using (a) left join t1 as t49 using (a) left join t1 as t50 using (a) left join t1 as t51 using (a) left join t1 as t52 using (a) left join t1 as t53 using (a) left join t1 as t54 using (a) left join t1 as t55 using (a) left join t1 as t56 using (a) left join t1 as t57 using (a) left join t1 as t58 using (a) left join t1 as t59 using (a) left join t1 as t60 using (a) left join t1 as t61 using (a) left join t1 as t62 using (a) left join t1 as t63 using (a) left join t1 as t64 using (a) left join t1 as t65 using (a);
Too many tables. MySQL can only use 31 tables in a join
drop table t1;
+CREATE TABLE t1 (
+a int(11) NOT NULL,
+b int(11) NOT NULL,
+PRIMARY KEY (a,b)
+) TYPE=MyISAM;
+INSERT INTO t1 VALUES (1,1),(1,2),(1,3),(1,4),(1,5),(1,6),(1,7),(2,3);
+CREATE TABLE t2 (
+a int(11) default NULL
+) TYPE=MyISAM;
+INSERT INTO t2 VALUES (2),(3);
+SELECT t1.a,t2.a,b FROM t1,t2 WHERE t1.a=t2.a AND (t1.a=1 OR t1.a=2) AND b>=1 AND b<=3;
+a a b
+2 2 3
+DROP TABLE t1, t2;
diff --git a/mysql-test/r/merge.result b/mysql-test/r/merge.result
index 5c15b37995b..9b72ff26350 100644
--- a/mysql-test/r/merge.result
+++ b/mysql-test/r/merge.result
@@ -1,4 +1,4 @@
-drop table if exists t1,t2,t3;
+drop table if exists t1,t2,t3,t4,t5,t6;
create table t1 (a int not null primary key auto_increment, message char(20));
create table t2 (a int not null primary key auto_increment, message char(20));
INSERT INTO t1 (message) VALUES ("Testing"),("table"),("t1");
@@ -110,6 +110,64 @@ a
412
412
411
+delete from t3 where a=3;
+select * from t3 where a < 10;
+a b
+1 Testing
+1 Testing
+2 table
+2 table
+4 Testing
+4 Testing
+5 table
+5 table
+6 t2
+6 t1
+7 Testing
+7 Testing
+8 table
+8 table
+9 t2
+9 t2
+delete from t3 where a >= 6 and a <= 8;
+select * from t3 where a < 10;
+a b
+1 Testing
+1 Testing
+2 table
+2 table
+4 Testing
+4 Testing
+5 table
+5 table
+9 t2
+9 t2
+update t3 set a=3 where a=9;
+select * from t3 where a < 10;
+a b
+1 Testing
+1 Testing
+2 table
+2 table
+3 t2
+3 t2
+4 Testing
+4 Testing
+5 table
+5 table
+update t3 set a=6 where a=7;
+select * from t3 where a < 10;
+a b
+1 Testing
+1 Testing
+2 table
+2 table
+3 t2
+3 t2
+4 Testing
+4 Testing
+5 table
+5 table
show create table t3;
Table Create Table
t3 CREATE TABLE `t3` (
@@ -216,3 +274,191 @@ a b
1 1
1 2
drop table t3,t1,t2;
+drop table if exists t6, t5, t4, t3, t2, t1;
+create table t1 (a int not null, b int not null, key(a,b));
+create table t2 (a int not null, b int not null, key(a,b));
+create table t3 (a int not null, b int not null, key(a,b)) UNION=(t1,t2) INSERT_METHOD=NO;
+create table t4 (a int not null, b int not null, key(a,b)) TYPE=MERGE UNION=(t1,t2) INSERT_METHOD=NO;
+create table t5 (a int not null, b int not null, key(a,b)) TYPE=MERGE UNION=(t1,t2) INSERT_METHOD=FIRST;
+create table t6 (a int not null, b int not null, key(a,b)) TYPE=MERGE UNION=(t1,t2) INSERT_METHOD=LAST;
+show create table t3;
+Table Create Table
+t3 CREATE TABLE `t3` (
+ `a` int(11) NOT NULL default '0',
+ `b` int(11) NOT NULL default '0',
+ KEY `a` (`a`,`b`)
+) TYPE=MyISAM
+show create table t4;
+Table Create Table
+t4 CREATE TABLE `t4` (
+ `a` int(11) NOT NULL default '0',
+ `b` int(11) NOT NULL default '0',
+ KEY `a` (`a`,`b`)
+) TYPE=MRG_MyISAM UNION=(t1,t2)
+show create table t5;
+Table Create Table
+t5 CREATE TABLE `t5` (
+ `a` int(11) NOT NULL default '0',
+ `b` int(11) NOT NULL default '0',
+ KEY `a` (`a`,`b`)
+) TYPE=MRG_MyISAM INSERT_METHOD=FIRST UNION=(t1,t2)
+show create table t6;
+Table Create Table
+t6 CREATE TABLE `t6` (
+ `a` int(11) NOT NULL default '0',
+ `b` int(11) NOT NULL default '0',
+ KEY `a` (`a`,`b`)
+) TYPE=MRG_MyISAM INSERT_METHOD=LAST UNION=(t1,t2)
+insert into t1 values (1,1),(1,2),(1,3),(1,4);
+insert into t2 values (2,1),(2,2),(2,3),(2,4);
+select * from t3 order by b,a limit 3;
+a b
+select * from t4 order by b,a limit 3;
+a b
+1 1
+2 1
+1 2
+select * from t5 order by b,a limit 3,3;
+a b
+2 2
+1 3
+2 3
+select * from t6 order by b,a limit 6,3;
+a b
+1 4
+2 4
+insert into t5 values (5,1),(5,2);
+insert into t6 values (6,1),(6,2);
+select * from t1 order by a,b;
+a b
+1 1
+1 2
+1 3
+1 4
+5 1
+5 2
+select * from t2 order by a,b;
+a b
+2 1
+2 2
+2 3
+2 4
+6 1
+6 2
+select * from t4 order by a,b;
+a b
+1 1
+1 2
+1 3
+1 4
+2 1
+2 2
+2 3
+2 4
+5 1
+5 2
+6 1
+6 2
+insert into t3 values (3,1),(3,2),(3,3),(3,4);
+select * from t3 order by a,b;
+a b
+3 1
+3 2
+3 3
+3 4
+alter table t4 UNION=(t1,t2,t3);
+show create table t4;
+Table Create Table
+t4 CREATE TABLE `t4` (
+ `a` int(11) NOT NULL default '0',
+ `b` int(11) NOT NULL default '0',
+ KEY `a` (`a`,`b`)
+) TYPE=MRG_MyISAM UNION=(t1,t2,t3)
+select * from t4 order by a,b;
+a b
+1 1
+1 2
+1 3
+1 4
+2 1
+2 2
+2 3
+2 4
+3 1
+3 2
+3 3
+3 4
+5 1
+5 2
+6 1
+6 2
+alter table t4 INSERT_METHOD=FIRST;
+show create table t4;
+Table Create Table
+t4 CREATE TABLE `t4` (
+ `a` int(11) NOT NULL default '0',
+ `b` int(11) NOT NULL default '0',
+ KEY `a` (`a`,`b`)
+) TYPE=MRG_MyISAM INSERT_METHOD=FIRST UNION=(t1,t2,t3)
+insert into t4 values (4,1),(4,2);
+select * from t1 order by a,b;
+a b
+1 1
+1 2
+1 3
+1 4
+4 1
+4 2
+5 1
+5 2
+select * from t2 order by a,b;
+a b
+2 1
+2 2
+2 3
+2 4
+6 1
+6 2
+select * from t3 order by a,b;
+a b
+3 1
+3 2
+3 3
+3 4
+select * from t4 order by a,b;
+a b
+1 1
+1 2
+1 3
+1 4
+2 1
+2 2
+2 3
+2 4
+3 1
+3 2
+3 3
+3 4
+4 1
+4 2
+5 1
+5 2
+6 1
+6 2
+select * from t5 order by a,b;
+a b
+1 1
+1 2
+1 3
+1 4
+2 1
+2 2
+2 3
+2 4
+4 1
+4 2
+5 1
+5 2
+6 1
+6 2
+drop table if exists t6, t5, t4, t3, t2, t1;
diff --git a/mysql-test/r/null_key.result b/mysql-test/r/null_key.result
index a28830a696d..9906c7c5b2c 100644
--- a/mysql-test/r/null_key.result
+++ b/mysql-test/r/null_key.result
@@ -21,7 +21,7 @@ table type possible_keys key key_len ref rows Extra
t1 range a,b a 9 NULL 3 where used; Using index
explain select * from t1 where (a is null or a = 7) and b=7;
table type possible_keys key key_len ref rows Extra
-t1 ref a,b b 4 const 2 where used
+t1 range a,b a 9 NULL 2 where used; Using index
explain select * from t1 where (a is null and b>a) or a is null and b=7 limit 2;
table type possible_keys key key_len ref rows Extra
t1 ref a,b a 5 const 3 where used; Using index
diff --git a/mysql-test/r/rpl000002.result b/mysql-test/r/rpl000002.result
index d557c3f8171..27b61a0af1b 100644
--- a/mysql-test/r/rpl000002.result
+++ b/mysql-test/r/rpl000002.result
@@ -16,7 +16,7 @@ n
2002
show slave hosts;
Server_id Host Port
-2 127.0.0.1 9307
+2 127.0.0.1 $SLAVE_MYPORT
drop table t1;
slave stop;
drop table if exists t2;
diff --git a/mysql-test/r/rpl000015.result b/mysql-test/r/rpl000015.result
index 777d855f2c9..d34c8d21327 100644
--- a/mysql-test/r/rpl000015.result
+++ b/mysql-test/r/rpl000015.result
@@ -11,7 +11,7 @@ show slave status;
Master_Host Master_User Master_Port Connect_retry Log_File Pos Slave_Running Replicate_do_db Replicate_ignore_db Last_errno Last_error Skip_counter Last_log_seq
127.0.0.1 test 9998 60 4 No 0 0 0
change master to master_host='127.0.0.1',master_user='root',
-master_password='',master_port=9306;
+master_password='',master_port=$MASTER_MYPORT;
show slave status;
Master_Host Master_User Master_Port Connect_retry Log_File Pos Slave_Running Replicate_do_db Replicate_ignore_db Last_errno Last_error Skip_counter Last_log_seq
127.0.0.1 root 9999 60 4 No 0 0 0
diff --git a/mysql-test/r/rpl000016.result b/mysql-test/r/rpl000016.result
index 96e22d99aac..899ca3b46b8 100644
--- a/mysql-test/r/rpl000016.result
+++ b/mysql-test/r/rpl000016.result
@@ -1,9 +1,9 @@
slave start;
slave start;
- change master to master_host='127.0.0.1',master_port=9306,
+ change master to master_host='127.0.0.1',master_port=$MASTER_MYPORT,
master_user='root';
reset slave;
- change master to master_host='127.0.0.1',master_port=9306,
+ change master to master_host='127.0.0.1',master_port=$MASTER_MYPORT,
master_user='root';
reset master;
slave start;
diff --git a/mysql-test/r/rpl_log.result b/mysql-test/r/rpl_log.result
index 3cee343d989..37b2474f9e6 100644
--- a/mysql-test/r/rpl_log.result
+++ b/mysql-test/r/rpl_log.result
@@ -16,7 +16,7 @@ load data infile '../../std_data/words.dat' into table t1;
drop table t1;
show binlog events;
Log_name Pos Event_type Server_id Log_seq Info
-master-bin.001 4 Start 1 1 Server ver: 4.0.0-alpha-debug-log, Binlog ver: 2
+master-bin.001 4 Start 1 1 Server ver: $VERSION, Binlog ver: 2
master-bin.001 79 Query 1 2 use test; create table t1(n int not null auto_increment primary key)
master-bin.001 172 Intvar 1 3 INSERT_ID=1
master-bin.001 200 Query 1 4 use test; insert into t1 values (NULL)
@@ -41,7 +41,7 @@ insert into t1 values (1);
drop table t1;
show binlog events;
Log_name Pos Event_type Server_id Log_seq Info
-master-bin.001 4 Start 1 1 Server ver: 4.0.0-alpha-debug-log, Binlog ver: 2
+master-bin.001 4 Start 1 1 Server ver: $VERSION, Binlog ver: 2
master-bin.001 79 Query 1 2 use test; create table t1(n int not null auto_increment primary key)
master-bin.001 172 Intvar 1 3 INSERT_ID=1
master-bin.001 200 Query 1 4 use test; insert into t1 values (NULL)
@@ -54,7 +54,7 @@ master-bin.001 627 Rotate 1 10 master-bin.002;pos=4
master-bin.001 668 Stop 1 11
show binlog events in 'master-bin.002';
Log_name Pos Event_type Server_id Log_seq Info
-master-bin.002 4 Start 1 1 Server ver: 4.0.0-alpha-debug-log, Binlog ver: 2
+master-bin.002 4 Start 1 1 Server ver: $VERSION, Binlog ver: 2
master-bin.002 79 Query 1 2 use test; create table t1 (n int)
master-bin.002 137 Query 1 3 use test; insert into t1 values (1)
master-bin.002 197 Query 1 4 use test; drop table t1
@@ -69,8 +69,8 @@ slave-bin.001
slave-bin.002
show binlog events in 'slave-bin.001' from 4;
Log_name Pos Event_type Server_id Log_seq Info
-slave-bin.001 4 Start 2 1 Server ver: 4.0.0-alpha-debug-log, Binlog ver: 2
-slave-bin.001 79 Slave 2 3 host=127.0.0.1,port=9306,log=master-bin.001,pos=4
+slave-bin.001 4 Start 2 1 Server ver: $VERSION, Binlog ver: 2
+slave-bin.001 79 Slave 2 3 host=127.0.0.1,port=$MASTER_MYPORT,log=master-bin.001,pos=4
slave-bin.001 132 Query 1 2 use test; create table t1(n int not null auto_increment primary key)
slave-bin.001 225 Intvar 1 3 INSERT_ID=1
slave-bin.001 253 Query 1 4 use test; insert into t1 values (NULL)
@@ -83,14 +83,14 @@ slave-bin.001 689 Rotate 1 4 slave-bin.002;pos=4; forced by master
slave-bin.001 729 Stop 2 5
show binlog events in 'slave-bin.002' from 4;
Log_name Pos Event_type Server_id Log_seq Info
-slave-bin.002 4 Start 2 1 Server ver: 4.0.0-alpha-debug-log, Binlog ver: 2
-slave-bin.002 79 Slave 2 10 host=127.0.0.1,port=9306,log=master-bin.002,pos=4
+slave-bin.002 4 Start 2 1 Server ver: $VERSION, Binlog ver: 2
+slave-bin.002 79 Slave 2 10 host=127.0.0.1,port=$MASTER_MYPORT,log=master-bin.002,pos=4
slave-bin.002 132 Query 1 2 use test; create table t1 (n int)
slave-bin.002 190 Query 1 3 use test; insert into t1 values (1)
slave-bin.002 250 Query 1 4 use test; drop table t1
show slave status;
Master_Host Master_User Master_Port Connect_retry Log_File Pos Slave_Running Replicate_do_db Replicate_ignore_db Last_errno Last_error Skip_counter Last_log_seq
-127.0.0.1 root 9306 1 master-bin.002 245 Yes 0 0 4
+127.0.0.1 root $MASTER_MYPORT 1 master-bin.002 245 Yes 0 0 4
show new master for slave with master_log_file='master-bin.001' and
master_log_pos=4 and master_log_seq=1 and master_server_id=1;
Log_name Log_pos
diff --git a/mysql-test/r/show_check.result b/mysql-test/r/show_check.result
index 77f558f84b8..126fd72e9cd 100644
--- a/mysql-test/r/show_check.result
+++ b/mysql-test/r/show_check.result
@@ -157,3 +157,28 @@ show open tables;
Database Table In_use Name_locked
test t1 0 0
drop table t1;
+create table t1 (a int not null, b VARCHAR(10), INDEX (b) ) AVG_ROW_LENGTH=10 CHECKSUM=1 COMMENT="test" TYPE=MYISAM MIN_ROWS=10 MAX_ROWS=100 PACK_KEYS=1 DELAY_KEY_WRITE=1 ROW_FORMAT=fixed;
+show create table t1;
+Table Create Table
+t1 CREATE TABLE `t1` (
+ `a` int(11) NOT NULL default '0',
+ `b` char(10) default NULL,
+ KEY `b` (`b`)
+) TYPE=MyISAM MIN_ROWS=10 MAX_ROWS=100 AVG_ROW_LENGTH=10 PACK_KEYS=1 CHECKSUM=1 DELAY_KEY_WRITE=1 ROW_FORMAT=FIXED COMMENT='test'
+alter table t1 MAX_ROWS=200 ROW_FORMAT=dynamic PACK_KEYS=0;
+show create table t1;
+Table Create Table
+t1 CREATE TABLE `t1` (
+ `a` int(11) NOT NULL default '0',
+ `b` varchar(10) default NULL,
+ KEY `b` (`b`)
+) TYPE=MyISAM MIN_ROWS=10 MAX_ROWS=200 AVG_ROW_LENGTH=10 PACK_KEYS=0 CHECKSUM=1 DELAY_KEY_WRITE=1 ROW_FORMAT=DYNAMIC COMMENT='test'
+ALTER TABLE t1 AVG_ROW_LENGTH=0 CHECKSUM=0 COMMENT="" MIN_ROWS=0 MAX_ROWS=0 PACK_KEYS=DEFAULT DELAY_KEY_WRITE=0 ROW_FORMAT=default;
+show create table t1;
+Table Create Table
+t1 CREATE TABLE `t1` (
+ `a` int(11) NOT NULL default '0',
+ `b` varchar(10) default NULL,
+ KEY `b` (`b`)
+) TYPE=MyISAM
+drop table t1;
View Lorry Controller Status