diff options
Diffstat (limited to 'mysql-test')
29 files changed, 621 insertions, 153 deletions
diff --git a/mysql-test/r/auto_increment.result b/mysql-test/r/auto_increment.result index 1390141df51..89d07af6cf3 100644 --- a/mysql-test/r/auto_increment.result +++ b/mysql-test/r/auto_increment.result @@ -106,6 +106,32 @@ Table Op Msg_type Msg_text test.t1 check warning Found row where the auto_increment column has the value 0 test.t1 check status OK drop table t1; +create table t1 (a int not null auto_increment primary key); +insert into t1 values (NULL); +insert into t1 values (-1); +select last_insert_id(); +last_insert_id() +1 +insert into t1 values (NULL); +select * from t1; +a +-1 +1 +2 +drop table t1; +create table t1 (a int not null auto_increment primary key) /*!41002 type=heap */; +insert into t1 values (NULL); +insert into t1 values (-1); +select last_insert_id(); +last_insert_id() +1 +insert into t1 values (NULL); +select * from t1; +a +-1 +1 +2 +drop table t1; create table t1 (i tinyint unsigned not null auto_increment primary key); insert into t1 set i = 254; insert into t1 set i = null; @@ -244,29 +270,3 @@ a b 0 13 500 14 drop table t1; -create table t1 (a int not null auto_increment primary key); -insert into t1 values (NULL); -insert into t1 values (-1); -select last_insert_id(); -last_insert_id() -1 -insert into t1 values (NULL); -select * from t1; -a --1 -1 -2 -drop table t1; -create table t1 (a int not null auto_increment primary key) /*!41002 type=heap */; -insert into t1 values (NULL); -insert into t1 values (-1); -select last_insert_id(); -last_insert_id() -1 -insert into t1 values (NULL); -select * from t1; -a --1 -1 -2 -drop table t1; diff --git a/mysql-test/r/bdb.result b/mysql-test/r/bdb.result index dd5633af90d..b02574cf8a3 100644 --- a/mysql-test/r/bdb.result +++ b/mysql-test/r/bdb.result @@ -1145,14 +1145,14 @@ x 7 6 drop table t1; -create table t1 ( c char(8) not null ) type=bdb; +create table t1 ( c char(8) not null ) engine=bdb; insert into t1 values ('0'),('1'),('2'),('3'),('4'),('5'),('6'),('7'),('8'),('9'); insert into t1 values ('A'),('B'),('C'),('D'),('E'),('F'); alter table t1 add b char(8) not null; alter table t1 add a char(8) not null; alter table t1 add primary key (a,b,c); update t1 set a=c, b=c; -create table t2 (c char(8) not null, b char(8) not null, a char(8) not null, primary key(a,b,c)) type=bdb; +create table t2 (c char(8) not null, b char(8) not null, a char(8) not null, primary key(a,b,c)) engine=bdb; insert into t2 select * from t1; delete t1,t2 from t2,t1 where t1.a<'B' and t2.b=t1.b; drop table t1,t2; diff --git a/mysql-test/r/func_group.result b/mysql-test/r/func_group.result index ec00c850197..6a704f2847d 100644 --- a/mysql-test/r/func_group.result +++ b/mysql-test/r/func_group.result @@ -47,7 +47,7 @@ sum(all a) count(all a) avg(all a) std(all a) variance(all a) bit_or(all a) bit_ 21 6 3.5000 1.7078 2.9167 7 0 1 6 E select grp, sum(a),count(a),avg(a),std(a),variance(a),bit_or(a),bit_and(a),min(a),max(a),min(c),max(c) from t1 group by grp; grp sum(a) count(a) avg(a) std(a) variance(a) bit_or(a) bit_and(a) min(a) max(a) min(c) max(c) -NULL NULL 0 NULL NULL NULL 0 1844674407379551615 NULL NULL +NULL NULL 0 NULL NULL NULL 0 18446744073709551615 NULL NULL 1 1 1 1.0000 0.0000 0.0000 1 1 1 1 a a 2 5 2 2.5000 0.5000 0.2500 3 2 2 3 b c 3 15 3 5.0000 0.8165 0.6667 7 4 4 6 C E @@ -576,8 +576,8 @@ insert into t1 values (1,null); insert into t1 values (2,null); select a,count(b), sum(b), avg(b), std(b), min(b), max(b), bit_and(b), bit_or(b) from t1 group by a; a count(b) sum(b) avg(b) std(b) min(b) max(b) bit_and(b) bit_or(b) -1 0 NULL NULL NULL NULL NULL 0 0 -2 0 NULL NULL NULL NULL NULL 0 0 +1 0 NULL NULL NULL NULL NULL 18446744073709551615 0 +2 0 NULL NULL NULL NULL NULL 18446744073709551615 0 select SQL_BIG_RESULT a,count(b), sum(b), avg(b), std(b), min(b), max(b), bit_and(b), bit_or(b) from t1 group by a; a count(b) sum(b) avg(b) std(b) min(b) max(b) bit_and(b) bit_or(b) 1 0 NULL NULL NULL NULL NULL 18446744073709551615 0 @@ -585,8 +585,8 @@ a count(b) sum(b) avg(b) std(b) min(b) max(b) bit_and(b) bit_or(b) insert into t1 values (2,1); select a,count(b), sum(b), avg(b), std(b), min(b), max(b), bit_and(b), bit_or(b) from t1 group by a; a count(b) sum(b) avg(b) std(b) min(b) max(b) bit_and(b) bit_or(b) -1 0 NULL NULL NULL NULL NULL 0 0 -2 1 1 1.0000 0.0000 1 1 0 1 +1 0 NULL NULL NULL NULL NULL 18446744073709551615 0 +2 1 1 1.0000 0.0000 1 1 1 1 select SQL_BIG_RESULT a,count(b), sum(b), avg(b), std(b), min(b), max(b), bit_and(b), bit_or(b) from t1 group by a; a count(b) sum(b) avg(b) std(b) min(b) max(b) bit_and(b) bit_or(b) 1 0 NULL NULL NULL NULL NULL 18446744073709551615 0 @@ -594,8 +594,8 @@ a count(b) sum(b) avg(b) std(b) min(b) max(b) bit_and(b) bit_or(b) insert into t1 values (3,1); select a,count(b), sum(b), avg(b), std(b), min(b), max(b), bit_and(b), bit_or(b) from t1 group by a; a count(b) sum(b) avg(b) std(b) min(b) max(b) bit_and(b) bit_or(b) -1 0 NULL NULL NULL NULL NULL 0 0 -2 1 1 1.0000 0.0000 1 1 0 1 +1 0 NULL NULL NULL NULL NULL 18446744073709551615 0 +2 1 1 1.0000 0.0000 1 1 1 1 3 1 1 1.0000 0.0000 1 1 1 1 select SQL_BIG_RESULT a,count(b), sum(b), avg(b), std(b), min(b), max(b), bit_and(b), bit_or(b), bit_xor(b) from t1 group by a; a count(b) sum(b) avg(b) std(b) min(b) max(b) bit_and(b) bit_or(b) bit_xor(b) @@ -643,4 +643,3 @@ bit_and(col) bit_or(col) 18446744073709551614 18446744073709551614 18446744073709551615 18446744073709551615 drop table t1; - diff --git a/mysql-test/r/func_str.result b/mysql-test/r/func_str.result index aaebd33d010..c74feccfb7f 100644 --- a/mysql-test/r/func_str.result +++ b/mysql-test/r/func_str.result @@ -268,3 +268,340 @@ INSERT INTO t1 VALUES (1, 'a545f661efdd1fb66fdee3aab79945bf'); SELECT 1 FROM t1 WHERE tmp=AES_DECRYPT(tmp,"password"); 1 DROP TABLE t1; +select 1=_latin1'1'; +1=_latin1'1' +1 +select _latin1'1'=1; +_latin1'1'=1 +1 +select _latin2'1'=1; +_latin2'1'=1 +1 +select 1=_latin2'1'; +1=_latin2'1' +1 +select _latin1'1'=_latin2'1'; +ERROR HY000: Illegal mix of collations (latin1_swedish_ci,COERCIBLE) and (latin2_general_ci,COERCIBLE) for operation '=' +select row('a','b','c') = row('a','b','c'); +row('a','b','c') = row('a','b','c') +1 +select row('A','b','c') = row('a','b','c'); +row('A','b','c') = row('a','b','c') +1 +select row('A' COLLATE latin1_bin,'b','c') = row('a','b','c'); +row('A' COLLATE latin1_bin,'b','c') = row('a','b','c') +0 +select row('A','b','c') = row('a' COLLATE latin1_bin,'b','c'); +row('A','b','c') = row('a' COLLATE latin1_bin,'b','c') +0 +select row('A' COLLATE latin1_general_ci,'b','c') = row('a' COLLATE latin1_bin,'b','c'); +ERROR HY000: Illegal mix of collations (latin1_general_ci,EXPLICIT) and (latin1_bin,EXPLICIT) for operation '=' +select concat(_latin1'a',_latin2'a'); +ERROR HY000: Illegal mix of collations (latin1_swedish_ci,COERCIBLE) and (latin2_general_ci,COERCIBLE) for operation 'concat' +select concat(_latin1'a',_latin2'a',_latin5'a'); +ERROR HY000: Illegal mix of collations (latin1_swedish_ci,COERCIBLE), (latin2_general_ci,COERCIBLE), (latin5_turkish_ci,COERCIBLE) for operation 'concat' +select concat(_latin1'a',_latin2'a',_latin5'a',_latin7'a'); +ERROR HY000: Illegal mix of collations for operation 'concat' +select FIELD('b','A','B'); +FIELD('b','A','B') +2 +select FIELD('B','A','B'); +FIELD('B','A','B') +2 +select FIELD('b' COLLATE latin1_bin,'A','B'); +FIELD('b' COLLATE latin1_bin,'A','B') +0 +select FIELD('b','A' COLLATE latin1_bin,'B'); +FIELD('b','A' COLLATE latin1_bin,'B') +0 +select FIELD(_latin2'b','A','B'); +ERROR HY000: Illegal mix of collations (latin2_general_ci,COERCIBLE), (latin1_swedish_ci,COERCIBLE), (latin1_swedish_ci,COERCIBLE) for operation 'field' +select FIELD('b',_latin2'A','B'); +ERROR HY000: Illegal mix of collations (latin1_swedish_ci,COERCIBLE), (latin2_general_ci,COERCIBLE), (latin1_swedish_ci,COERCIBLE) for operation 'field' +select FIELD('b',_latin2'A','B',1); +FIELD('b',_latin2'A','B',1) +1 +select POSITION(_latin1'B' IN _latin1'abcd'); +POSITION(_latin1'B' IN _latin1'abcd') +2 +select POSITION(_latin1'B' IN _latin1'abcd' COLLATE latin1_bin); +POSITION(_latin1'B' IN _latin1'abcd' COLLATE latin1_bin) +0 +select POSITION(_latin1'B' COLLATE latin1_bin IN _latin1'abcd'); +POSITION(_latin1'B' COLLATE latin1_bin IN _latin1'abcd') +0 +select POSITION(_latin1'B' COLLATE latin1_general_ci IN _latin1'abcd' COLLATE latin1_bin); +ERROR HY000: Illegal mix of collations (latin1_bin,EXPLICIT) and (latin1_general_ci,EXPLICIT) for operation 'locate' +select POSITION(_latin1'B' IN _latin2'abcd'); +ERROR HY000: Illegal mix of collations (latin2_general_ci,COERCIBLE) and (latin1_swedish_ci,COERCIBLE) for operation 'locate' +select FIND_IN_SET(_latin1'B',_latin1'a,b,c,d'); +FIND_IN_SET(_latin1'B',_latin1'a,b,c,d') +2 +select FIND_IN_SET(_latin1'B' COLLATE latin1_general_ci,_latin1'a,b,c,d' COLLATE latin1_bin); +ERROR HY000: Illegal mix of collations (latin1_general_ci,EXPLICIT) and (latin1_bin,EXPLICIT) for operation 'find_in_set' +select FIND_IN_SET(_latin1'B',_latin2'a,b,c,d'); +ERROR HY000: Illegal mix of collations (latin1_swedish_ci,COERCIBLE) and (latin2_general_ci,COERCIBLE) for operation 'find_in_set' +select SUBSTRING_INDEX(_latin1'abcdabcdabcd',_latin1'd',2); +SUBSTRING_INDEX(_latin1'abcdabcdabcd',_latin1'd',2) +abcdabc +select SUBSTRING_INDEX(_latin1'abcdabcdabcd',_latin2'd',2); +ERROR HY000: Illegal mix of collations (latin1_swedish_ci,COERCIBLE) and (latin2_general_ci,COERCIBLE) for operation 'substr_index' +select SUBSTRING_INDEX(_latin1'abcdabcdabcd' COLLATE latin1_general_ci,_latin1'd' COLLATE latin1_bin,2); +ERROR HY000: Illegal mix of collations (latin1_general_ci,EXPLICIT) and (latin1_bin,EXPLICIT) for operation 'substr_index' +select _latin1'B' between _latin1'a' and _latin1'c'; +_latin1'B' between _latin1'a' and _latin1'c' +1 +select _latin1'B' collate latin1_bin between _latin1'a' and _latin1'c'; +_latin1'B' collate latin1_bin between _latin1'a' and _latin1'c' +0 +select _latin1'B' between _latin1'a' collate latin1_bin and _latin1'c'; +_latin1'B' between _latin1'a' collate latin1_bin and _latin1'c' +0 +select _latin1'B' between _latin1'a' and _latin1'c' collate latin1_bin; +_latin1'B' between _latin1'a' and _latin1'c' collate latin1_bin +0 +select _latin2'B' between _latin1'a' and _latin1'b'; +ERROR HY000: Illegal mix of collations (latin2_general_ci,COERCIBLE), (latin1_swedish_ci,COERCIBLE), (latin1_swedish_ci,COERCIBLE) for operation 'between' +select _latin1'B' between _latin2'a' and _latin1'b'; +ERROR HY000: Illegal mix of collations (latin1_swedish_ci,COERCIBLE), (latin2_general_ci,COERCIBLE), (latin1_swedish_ci,COERCIBLE) for operation 'between' +select _latin1'B' between _latin1'a' and _latin2'b'; +ERROR HY000: Illegal mix of collations (latin1_swedish_ci,COERCIBLE), (latin1_swedish_ci,COERCIBLE), (latin2_general_ci,COERCIBLE) for operation 'between' +select _latin1'B' collate latin1_general_ci between _latin1'a' collate latin1_bin and _latin1'b'; +ERROR HY000: Illegal mix of collations (latin1_general_ci,EXPLICIT), (latin1_bin,EXPLICIT), (latin1_swedish_ci,COERCIBLE) for operation 'between' +select _latin1'B' in (_latin1'a',_latin1'b'); +_latin1'B' in (_latin1'a',_latin1'b') +1 +select _latin1'B' collate latin1_bin in (_latin1'a',_latin1'b'); +_latin1'B' collate latin1_bin in (_latin1'a',_latin1'b') +0 +select _latin1'B' in (_latin1'a' collate latin1_bin,_latin1'b'); +_latin1'B' in (_latin1'a' collate latin1_bin,_latin1'b') +0 +select _latin1'B' in (_latin1'a',_latin1'b' collate latin1_bin); +_latin1'B' in (_latin1'a',_latin1'b' collate latin1_bin) +0 +select _latin2'B' in (_latin1'a',_latin1'b'); +ERROR HY000: Illegal mix of collations (latin2_general_ci,COERCIBLE), (latin1_swedish_ci,COERCIBLE), (latin1_swedish_ci,COERCIBLE) for operation ' IN ' +select _latin1'B' in (_latin2'a',_latin1'b'); +ERROR HY000: Illegal mix of collations (latin1_swedish_ci,COERCIBLE), (latin2_general_ci,COERCIBLE), (latin1_swedish_ci,COERCIBLE) for operation ' IN ' +select _latin1'B' in (_latin1'a',_latin2'b'); +ERROR HY000: Illegal mix of collations (latin1_swedish_ci,COERCIBLE), (latin1_swedish_ci,COERCIBLE), (latin2_general_ci,COERCIBLE) for operation ' IN ' +select _latin1'B' COLLATE latin1_general_ci in (_latin1'a' COLLATE latin1_bin,_latin1'b'); +ERROR HY000: Illegal mix of collations (latin1_general_ci,EXPLICIT), (latin1_bin,EXPLICIT), (latin1_swedish_ci,COERCIBLE) for operation ' IN ' +select _latin1'B' COLLATE latin1_general_ci in (_latin1'a',_latin1'b' COLLATE latin1_bin); +ERROR HY000: Illegal mix of collations (latin1_general_ci,EXPLICIT), (latin1_swedish_ci,COERCIBLE), (latin1_bin,EXPLICIT) for operation ' IN ' +select collation(bin(130)), coercibility(bin(130)); +collation(bin(130)) coercibility(bin(130)) +latin1_swedish_ci 3 +select collation(oct(130)), coercibility(oct(130)); +collation(oct(130)) coercibility(oct(130)) +latin1_swedish_ci 3 +select collation(conv(130,16,10)), coercibility(conv(130,16,10)); +collation(conv(130,16,10)) coercibility(conv(130,16,10)) +latin1_swedish_ci 3 +select collation(hex(130)), coercibility(hex(130)); +collation(hex(130)) coercibility(hex(130)) +latin1_swedish_ci 3 +select collation(char(130)), coercibility(hex(130)); +collation(char(130)) coercibility(hex(130)) +binary 3 +select collation(format(130,10)), coercibility(format(130,10)); +collation(format(130,10)) coercibility(format(130,10)) +latin1_swedish_ci 3 +select collation(lcase(_latin2'a')), coercibility(lcase(_latin2'a')); +collation(lcase(_latin2'a')) coercibility(lcase(_latin2'a')) +latin2_general_ci 3 +select collation(ucase(_latin2'a')), coercibility(ucase(_latin2'a')); +collation(ucase(_latin2'a')) coercibility(ucase(_latin2'a')) +latin2_general_ci 3 +select collation(left(_latin2'a',1)), coercibility(left(_latin2'a',1)); +collation(left(_latin2'a',1)) coercibility(left(_latin2'a',1)) +latin2_general_ci 3 +select collation(right(_latin2'a',1)), coercibility(right(_latin2'a',1)); +collation(right(_latin2'a',1)) coercibility(right(_latin2'a',1)) +latin2_general_ci 3 +select collation(substring(_latin2'a',1,1)), coercibility(substring(_latin2'a',1,1)); +collation(substring(_latin2'a',1,1)) coercibility(substring(_latin2'a',1,1)) +latin2_general_ci 3 +select collation(concat(_latin2'a',_latin2'b')), coercibility(concat(_latin2'a',_latin2'b')); +collation(concat(_latin2'a',_latin2'b')) coercibility(concat(_latin2'a',_latin2'b')) +latin2_general_ci 3 +select collation(lpad(_latin2'a',4,_latin2'b')), coercibility(lpad(_latin2'a',4,_latin2'b')); +collation(lpad(_latin2'a',4,_latin2'b')) coercibility(lpad(_latin2'a',4,_latin2'b')) +latin2_general_ci 3 +select collation(rpad(_latin2'a',4,_latin2'b')), coercibility(rpad(_latin2'a',4,_latin2'b')); +collation(rpad(_latin2'a',4,_latin2'b')) coercibility(rpad(_latin2'a',4,_latin2'b')) +latin2_general_ci 3 +select collation(concat_ws(_latin2'a',_latin2'b')), coercibility(concat_ws(_latin2'a',_latin2'b')); +collation(concat_ws(_latin2'a',_latin2'b')) coercibility(concat_ws(_latin2'a',_latin2'b')) +latin2_general_ci 3 +select collation(make_set(255,_latin2'a',_latin2'b',_latin2'c')), coercibility(make_set(255,_latin2'a',_latin2'b',_latin2'c')); +collation(make_set(255,_latin2'a',_latin2'b',_latin2'c')) coercibility(make_set(255,_latin2'a',_latin2'b',_latin2'c')) +latin2_general_ci 3 +select collation(export_set(255,_latin2'y',_latin2'n',_latin2' ')), coercibility(export_set(255,_latin2'y',_latin2'n',_latin2' ')); +collation(export_set(255,_latin2'y',_latin2'n',_latin2' ')) coercibility(export_set(255,_latin2'y',_latin2'n',_latin2' ')) +binary 3 +select collation(trim(_latin2' a ')), coercibility(trim(_latin2' a ')); +collation(trim(_latin2' a ')) coercibility(trim(_latin2' a ')) +latin2_general_ci 3 +select collation(ltrim(_latin2' a ')), coercibility(ltrim(_latin2' a ')); +collation(ltrim(_latin2' a ')) coercibility(ltrim(_latin2' a ')) +latin2_general_ci 3 +select collation(rtrim(_latin2' a ')), coercibility(rtrim(_latin2' a ')); +collation(rtrim(_latin2' a ')) coercibility(rtrim(_latin2' a ')) +latin2_general_ci 3 +select collation(trim(LEADING _latin2' ' FROM _latin2'a')), coercibility(trim(LEADING _latin2'a' FROM _latin2'a')); +collation(trim(LEADING _latin2' ' FROM _latin2'a')) coercibility(trim(LEADING _latin2'a' FROM _latin2'a')) +latin2_general_ci 3 +select collation(trim(TRAILING _latin2' ' FROM _latin2'a')), coercibility(trim(TRAILING _latin2'a' FROM _latin2'a')); +collation(trim(TRAILING _latin2' ' FROM _latin2'a')) coercibility(trim(TRAILING _latin2'a' FROM _latin2'a')) +latin2_general_ci 3 +select collation(trim(BOTH _latin2' ' FROM _latin2'a')), coercibility(trim(BOTH _latin2'a' FROM _latin2'a')); +collation(trim(BOTH _latin2' ' FROM _latin2'a')) coercibility(trim(BOTH _latin2'a' FROM _latin2'a')) +latin2_general_ci 3 +select collation(repeat(_latin2'a',10)), coercibility(repeat(_latin2'a',10)); +collation(repeat(_latin2'a',10)) coercibility(repeat(_latin2'a',10)) +latin2_general_ci 3 +select collation(reverse(_latin2'ab')), coercibility(reverse(_latin2'ab')); +collation(reverse(_latin2'ab')) coercibility(reverse(_latin2'ab')) +latin2_general_ci 3 +select collation(quote(_latin2'ab')), coercibility(quote(_latin2'ab')); +collation(quote(_latin2'ab')) coercibility(quote(_latin2'ab')) +latin2_general_ci 3 +select collation(soundex(_latin2'ab')), coercibility(soundex(_latin2'ab')); +collation(soundex(_latin2'ab')) coercibility(soundex(_latin2'ab')) +latin2_general_ci 3 +select collation(substring(_latin2'ab',1)), coercibility(substring(_latin2'ab',1)); +collation(substring(_latin2'ab',1)) coercibility(substring(_latin2'ab',1)) +latin2_general_ci 3 +select collation(insert(_latin2'abcd',2,3,_latin2'ef')), coercibility(insert(_latin2'abcd',2,3,_latin2'ef')); +collation(insert(_latin2'abcd',2,3,_latin2'ef')) coercibility(insert(_latin2'abcd',2,3,_latin2'ef')) +latin2_general_ci 3 +select collation(replace(_latin2'abcd',_latin2'b',_latin2'B')), coercibility(replace(_latin2'abcd',_latin2'b',_latin2'B')); +collation(replace(_latin2'abcd',_latin2'b',_latin2'B')) coercibility(replace(_latin2'abcd',_latin2'b',_latin2'B')) +latin2_general_ci 3 +create table t1 +select +bin(130), +oct(130), +conv(130,16,10), +hex(130), +char(130), +format(130,10), +left(_latin2'a',1), +right(_latin2'a',1), +lcase(_latin2'a'), +ucase(_latin2'a'), +substring(_latin2'a',1,1), +concat(_latin2'a',_latin2'b'), +lpad(_latin2'a',4,_latin2'b'), +rpad(_latin2'a',4,_latin2'b'), +concat_ws(_latin2'a',_latin2'b'), +make_set(255,_latin2'a',_latin2'b',_latin2'c'), +export_set(255,_latin2'y',_latin2'n',_latin2' '), +trim(_latin2' a '), +ltrim(_latin2' a '), +rtrim(_latin2' a '), +trim(LEADING _latin2' ' FROM _latin2' a '), +trim(TRAILING _latin2' ' FROM _latin2' a '), +trim(BOTH _latin2' ' FROM _latin2' a '), +repeat(_latin2'a',10), +reverse(_latin2'ab'), +quote(_latin2'ab'), +soundex(_latin2'ab'), +substring(_latin2'ab',1), +insert(_latin2'abcd',2,3,_latin2'ef'), +replace(_latin2'abcd',_latin2'b',_latin2'B') +; +Warnings: +Warning 1264 Data truncated for column 'format(130,10)' at row 1 +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `bin(130)` char(64) NOT NULL default '', + `oct(130)` char(64) NOT NULL default '', + `conv(130,16,10)` char(64) NOT NULL default '', + `hex(130)` char(6) NOT NULL default '', + `char(130)` char(1) NOT NULL default '', + `format(130,10)` char(4) NOT NULL default '', + `left(_latin2'a',1)` char(1) character set latin2 NOT NULL default '', + `right(_latin2'a',1)` char(1) character set latin2 NOT NULL default '', + `lcase(_latin2'a')` char(1) character set latin2 NOT NULL default '', + `ucase(_latin2'a')` char(1) character set latin2 NOT NULL default '', + `substring(_latin2'a',1,1)` char(1) character set latin2 NOT NULL default '', + `concat(_latin2'a',_latin2'b')` char(2) character set latin2 NOT NULL default '', + `lpad(_latin2'a',4,_latin2'b')` char(4) character set latin2 NOT NULL default '', + `rpad(_latin2'a',4,_latin2'b')` char(4) character set latin2 NOT NULL default '', + `concat_ws(_latin2'a',_latin2'b')` char(1) character set latin2 NOT NULL default '', + `make_set(255,_latin2'a',_latin2'b',_latin2'c')` char(5) character set latin2 NOT NULL default '', + `export_set(255,_latin2'y',_latin2'n',_latin2' ')` char(127) character set latin2 NOT NULL default '', + `trim(_latin2' a ')` char(3) character set latin2 NOT NULL default '', + `ltrim(_latin2' a ')` char(3) character set latin2 NOT NULL default '', + `rtrim(_latin2' a ')` char(3) character set latin2 NOT NULL default '', + `trim(LEADING _latin2' ' FROM _latin2' a ')` char(3) character set latin2 NOT NULL default '', + `trim(TRAILING _latin2' ' FROM _latin2' a ')` char(3) character set latin2 NOT NULL default '', + `trim(BOTH _latin2' ' FROM _latin2' a ')` char(3) character set latin2 NOT NULL default '', + `repeat(_latin2'a',10)` char(10) character set latin2 NOT NULL default '', + `reverse(_latin2'ab')` char(2) character set latin2 NOT NULL default '', + `quote(_latin2'ab')` char(6) character set latin2 NOT NULL default '', + `soundex(_latin2'ab')` char(4) character set latin2 NOT NULL default '', + `substring(_latin2'ab',1)` char(2) character set latin2 NOT NULL default '', + `insert(_latin2'abcd',2,3,_latin2'ef')` char(6) character set latin2 NOT NULL default '', + `replace(_latin2'abcd',_latin2'b',_latin2'B')` char(4) character set latin2 NOT NULL default '' +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +drop table t1; +select SUBSTR('abcdefg',3,2); +SUBSTR('abcdefg',3,2) +cd +select SUBSTRING('abcdefg',3,2); +SUBSTRING('abcdefg',3,2) +cd +select SUBSTR('abcdefg',-3,2) FROM DUAL; +SUBSTR('abcdefg',-3,2) +ef +select SUBSTR('abcdefg',-1,5) FROM DUAL; +SUBSTR('abcdefg',-1,5) +g +select SUBSTR('abcdefg',0,0) FROM DUAL; +SUBSTR('abcdefg',0,0) + +select SUBSTR('abcdefg',-1,-1) FROM DUAL; +SUBSTR('abcdefg',-1,-1) + +select SUBSTR('abcdefg',1,-1) FROM DUAL; +SUBSTR('abcdefg',1,-1) + +create table t7 (s1 char); +select * from t7 +where concat(s1 collate latin1_general_ci,s1 collate latin1_swedish_ci) = 'AA'; +ERROR HY000: Illegal mix of collations (latin1_general_ci,EXPLICIT) and (latin1_swedish_ci,EXPLICIT) for operation 'concat' +drop table t7; +CREATE TABLE t1 ( +wid int(10) unsigned NOT NULL auto_increment, +data_podp date default NULL, +status_wnio enum('nowy','podp','real','arch') NOT NULL default 'nowy', +PRIMARY KEY(wid), +); +INSERT INTO t1 VALUES (8,NULL,'real'); +INSERT INTO t1 VALUES (9,NULL,'nowy'); +SELECT elt(status_wnio,data_podp) FROM t1 GROUP BY wid; +elt(status_wnio,data_podp) +NULL +NULL +DROP TABLE t1; +CREATE TABLE t1 (title text) ENGINE=MyISAM; +INSERT INTO t1 VALUES ('Congress reconvenes in September to debate welfare and adult education'); +INSERT INTO t1 VALUES ('House passes the CAREERS bill'); +SELECT CONCAT("</a>",RPAD("",(55 - LENGTH(title)),".")) from t1; +CONCAT("</a>",RPAD("",(55 - LENGTH(title)),".")) +NULL +</a>.......................... +DROP TABLE t1; +select substring_index("1abcd;2abcd;3abcd;4abcd", ';', 2),substring_index("1abcd;2abcd;3abcd;4abcd", ';', -2); +substring_index("1abcd;2abcd;3abcd;4abcd", ';', 2) substring_index("1abcd;2abcd;3abcd;4abcd", ';', -2) +1abcd;2abcd 3abcd;4abcd +explain extended select md5('hello'), sha('abc'), sha1('abc'), soundex(''), 'mood' sounds like 'mud', aes_decrypt(aes_encrypt('abc','1'),'1'),concat('*',space(5),'*'), reverse('abc'), rpad('a',4,'1'), lpad('a',4,'1'), concat_ws(',','',NULL,'a'),make_set(255,_latin2'a',_latin2'b',_latin2'c'),elt(2,1),locate("a","b",2),format(130,10),char(0),conv(130,16,10),hex(130),binary 'HE', export_set(255,_latin2'y',_latin2'n',_latin2' '),FIELD('b' COLLATE latin1_bin,'A','B'),FIND_IN_SET(_latin1'B',_latin1'a,b,c,d'),collation(conv(130,16,10)), coercibility(conv(130,16,10)),length('\n\t\r\b\0\_\%\\'),bit_length('\n\t\r\b\0\_\%\\'),bit_length('\n\t\r\b\0\_\%\\'),concat('monty',' was here ','again'),length('hello'),char(ascii('h')),ord('h'),quote(1/0),crc32("123"),replace('aaaa','a','b'),insert('txs',2,1,'hi'),left(_latin2'a',1),right(_latin2'a',1),lcase(_latin2'a'),ucase(_latin2'a'),SUBSTR('abcdefg',3,2),substring_index("1abcd;2abcd;3abcd;4abcd", ';', 2),trim(_latin2' a '),ltrim(_latin2' a '),rtrim(_latin2' a '), decode(encode(repeat("a",100000),"monty"),"monty"); +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE NULL NULL NULL NULL NULL NULL NULL No tables used +Warnings: +Note 1003 select high_priority md5(_latin1'hello') AS `md5('hello')`,sha(_latin1'abc') AS `sha('abc')`,sha(_latin1'abc') AS `sha1('abc')`,soundex(_latin1'') AS `soundex('')`,(soundex(_latin1'mood') = soundex(_latin1'mud')) AS `'mood' sounds like 'mud'`,aes_decrypt(aes_encrypt(_latin1'abc',_latin1'1'),_latin1'1') AS `aes_decrypt(aes_encrypt('abc','1'),'1')`,concat(_latin1'*',repeat(_latin1' ',5),_latin1'*') AS `concat('*',space(5),'*')`,reverse(_latin1'abc') AS `reverse('abc')`,rpad(_latin1'a',4,_latin1'1') AS `rpad('a',4,'1')`,lpad(_latin1'a',4,_latin1'1') AS `lpad('a',4,'1')`,concat_ws(_latin1',',_latin1'',NULL,_latin1'a') AS `concat_ws(',','',NULL,'a')`,make_set(255,_latin2'a',_latin2'b',_latin2'c') AS `make_set(255,_latin2'a',_latin2'b',_latin2'c')`,elt(2,1) AS `elt(2,1)`,locate(_latin1'a',_latin1'b',2) AS `locate("a","b",2)`,format(130,10) AS `format(130,10)`,char(0) AS `char(0)`,conv(130,16,10) AS `conv(130,16,10)`,hex(130) AS `hex(130)`,(_latin1'HE' collate _latin1'BINARY') AS `binary 'HE'`,export_set(255,_latin2'y',_latin2'n',_latin2' ') AS `export_set(255,_latin2'y',_latin2'n',_latin2' ')`,field((_latin1'b' collate _latin1'latin1_bin'),_latin1'A',_latin1'B') AS `FIELD('b' COLLATE latin1_bin,'A','B')`,find_in_set(_latin1'B',_latin1'a,b,c,d') AS `FIND_IN_SET(_latin1'B',_latin1'a,b,c,d')`,collation(conv(130,16,10)) AS `collation(conv(130,16,10))`,coercibility(conv(130,16,10)) AS `coercibility(conv(130,16,10))`,length(_latin1'\n \r\0\\_\\%\\') AS `length('\n\t\r\b\0\_\%\\')`,bit_length(_latin1'\n \r\0\\_\\%\\') AS `bit_length('\n\t\r\b\0\_\%\\')`,bit_length(_latin1'\n \r\0\\_\\%\\') AS `bit_length('\n\t\r\b\0\_\%\\')`,concat(_latin1'monty',_latin1' was here ',_latin1'again') AS `concat('monty',' was here ','again')`,length(_latin1'hello') AS `length('hello')`,char(ascii(_latin1'h')) AS `char(ascii('h'))`,ord(_latin1'h') AS `ord('h')`,quote((1 / 0)) AS `quote(1/0)`,crc32(_latin1'123') AS `crc32("123")`,replace(_latin1'aaaa',_latin1'a',_latin1'b') AS `replace('aaaa','a','b')`,insert(_latin1'txs',2,1,_latin1'hi') AS `insert('txs',2,1,'hi')`,left(_latin2'a',1) AS `left(_latin2'a',1)`,right(_latin2'a',1) AS `right(_latin2'a',1)`,lcase(_latin2'a') AS `lcase(_latin2'a')`,ucase(_latin2'a') AS `ucase(_latin2'a')`,substr(_latin1'abcdefg',3,2) AS `SUBSTR('abcdefg',3,2)`,substr_index(_latin1'1abcd;2abcd;3abcd;4abcd',_latin1';',2) AS `substring_index("1abcd;2abcd;3abcd;4abcd", ';', 2)`,trim(_latin2' a ') AS `trim(_latin2' a ')`,ltrim(_latin2' a ') AS `ltrim(_latin2' a ')`,rtrim(_latin2' a ') AS `rtrim(_latin2' a ')`,decode(encode(repeat(_latin1'a',100000))) AS `decode(encode(repeat("a",100000),"monty"),"monty")` diff --git a/mysql-test/r/func_time.result b/mysql-test/r/func_time.result index c3f4b9475f9..0998f7b8bcf 100644 --- a/mysql-test/r/func_time.result +++ b/mysql-test/r/func_time.result @@ -431,3 +431,112 @@ select * from t1, t3 where t1.start between t3.ctime1 and t3.ctime2; start ctime1 ctime2 2002-11-04 00:00:00 2002-10-29 16:51:06 2002-11-05 16:47:31 drop table t1,t2,t3; +select @a:=FROM_UNIXTIME(1); +@a:=FROM_UNIXTIME(1) +1970-01-01 03:00:01 +select unix_timestamp(@a); +unix_timestamp(@a) +1 +select unix_timestamp('1969-12-01 19:00:01'); +unix_timestamp('1969-12-01 19:00:01') +0 +CREATE TABLE t1 (datetime datetime, timestamp timestamp, date date, time time); +INSERT INTO t1 values ("2001-01-02 03:04:05", "2002-01-02 03:04:05", "2003-01-02", "06:07:08"); +SELECT * from t1; +datetime timestamp date time +2001-01-02 03:04:05 2002-01-02 03:04:05 2003-01-02 06:07:08 +select date_add("1997-12-31",INTERVAL 1 SECOND); +date_add("1997-12-31",INTERVAL 1 SECOND) +1997-12-31 00:00:01 +select date_add("1997-12-31",INTERVAL "1 1" YEAR_MONTH); +date_add("1997-12-31",INTERVAL "1 1" YEAR_MONTH) +1999-01-31 +select date_add(datetime, INTERVAL 1 SECOND) from t1; +date_add(datetime, INTERVAL 1 SECOND) +2001-01-02 03:04:06 +select date_add(datetime, INTERVAL 1 YEAR) from t1; +date_add(datetime, INTERVAL 1 YEAR) +2002-01-02 03:04:05 +select date_add(date,INTERVAL 1 SECOND) from t1; +date_add(date,INTERVAL 1 SECOND) +2003-01-02 00:00:01 +select date_add(date,INTERVAL 1 MINUTE) from t1; +date_add(date,INTERVAL 1 MINUTE) +2003-01-02 00:01:00 +select date_add(date,INTERVAL 1 HOUR) from t1; +date_add(date,INTERVAL 1 HOUR) +2003-01-02 01:00:00 +select date_add(date,INTERVAL 1 DAY) from t1; +date_add(date,INTERVAL 1 DAY) +2003-01-03 +select date_add(date,INTERVAL 1 MONTH) from t1; +date_add(date,INTERVAL 1 MONTH) +2003-02-02 +select date_add(date,INTERVAL 1 YEAR) from t1; +date_add(date,INTERVAL 1 YEAR) +2004-01-02 +select date_add(date,INTERVAL "1:1" MINUTE_SECOND) from t1; +date_add(date,INTERVAL "1:1" MINUTE_SECOND) +2003-01-02 00:01:01 +select date_add(date,INTERVAL "1:1" HOUR_MINUTE) from t1; +date_add(date,INTERVAL "1:1" HOUR_MINUTE) +2003-01-02 01:01:00 +select date_add(date,INTERVAL "1:1" DAY_HOUR) from t1; +date_add(date,INTERVAL "1:1" DAY_HOUR) +2003-01-03 01:00:00 +select date_add(date,INTERVAL "1 1" YEAR_MONTH) from t1; +date_add(date,INTERVAL "1 1" YEAR_MONTH) +2004-02-02 +select date_add(date,INTERVAL "1:1:1" HOUR_SECOND) from t1; +date_add(date,INTERVAL "1:1:1" HOUR_SECOND) +2003-01-02 01:01:01 +select date_add(date,INTERVAL "1 1:1" DAY_MINUTE) from t1; +date_add(date,INTERVAL "1 1:1" DAY_MINUTE) +2003-01-03 01:01:00 +select date_add(date,INTERVAL "1 1:1:1" DAY_SECOND) from t1; +date_add(date,INTERVAL "1 1:1:1" DAY_SECOND) +2003-01-03 01:01:01 +select date_add(time,INTERVAL 1 SECOND) from t1; +date_add(time,INTERVAL 1 SECOND) +2006-07-08 00:00:01 +drop table t1; +select last_day('2000-02-05') as f1, last_day('2002-12-31') as f2, +last_day('2003-03-32') as f3, last_day('2003-04-01') as f4, +last_day('2001-01-01 01:01:01') as f5, last_day(NULL), +last_day('2001-02-12'); +f1 f2 f3 f4 f5 last_day(NULL) last_day('2001-02-12') +2000-02-29 2002-12-31 NULL 2003-04-30 2001-01-31 NULL 2001-02-28 +create table t1 select last_day('2000-02-05') as a; +describe t1; +Field Type Null Key Default Extra +a date 0000-00-00 +select * from t1; +a +2000-02-29 +drop table t1; +select last_day('2000-02-05'); +last_day('2000-02-05') +2000-02-29 +select strcmp(date_sub(localtimestamp(), interval 3 hour), utc_timestamp())=0; +strcmp(date_sub(localtimestamp(), interval 3 hour), utc_timestamp())=0 +1 +select strcmp(date_format(date_sub(localtimestamp(), interval 3 hour),"%T"), utc_time())=0; +strcmp(date_format(date_sub(localtimestamp(), interval 3 hour),"%T"), utc_time())=0 +1 +select strcmp(date_format(date_sub(localtimestamp(), interval 3 hour),"%Y-%m-%d"), utc_date())=0; +strcmp(date_format(date_sub(localtimestamp(), interval 3 hour),"%Y-%m-%d"), utc_date())=0 +1 +select strcmp(date_format(utc_timestamp(),"%T"), utc_time())=0; +strcmp(date_format(utc_timestamp(),"%T"), utc_time())=0 +1 +select strcmp(date_format(utc_timestamp(),"%Y-%m-%d"), utc_date())=0; +strcmp(date_format(utc_timestamp(),"%Y-%m-%d"), utc_date())=0 +1 +select strcmp(concat(utc_date(),' ',utc_time()),utc_timestamp())=0; +strcmp(concat(utc_date(),' ',utc_time()),utc_timestamp())=0 +1 +explain extended select period_add("9602",-12),period_diff(199505,"9404"),from_days(to_days("960101")),dayofmonth("1997-01-02"), month("1997-01-02"), monthname("1972-03-04"),dayofyear("0000-00-00"),HOUR("1997-03-03 23:03:22"),MINUTE("23:03:22"),SECOND(230322),QUARTER(980303),WEEK("1998-03-03"),yearweek("2000-01-01",1),week(19950101,1),year("98-02-03"),weekday(curdate())-weekday(now()),dayname("1962-03-03"),unix_timestamp(),sec_to_time(time_to_sec("0:30:47")/6.21),curtime(),utc_time(),curdate(),utc_date(),utc_timestamp(),date_format("1997-01-02 03:04:05", "%M %W %D %Y %y %m %d %h %i %s %w"),from_unixtime(unix_timestamp("1994-03-02 10:11:12")),"1997-12-31 23:59:59" + INTERVAL 1 SECOND,"1998-01-01 00:00:00" - INTERVAL 1 SECOND,INTERVAL 1 DAY + "1997-12-31", extract(YEAR FROM "1999-01-02 10:11:12"),date_add("1997-12-31 23:59:59",INTERVAL 1 SECOND); +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE NULL NULL NULL NULL NULL NULL NULL No tables used +Warnings: +Note 1003 select high_priority no_cache period_add(_latin1'9602',-(12)) AS `period_add("9602",-12)`,period_diff(199505,_latin1'9404') AS `period_diff(199505,"9404")`,from_days(to_days(_latin1'960101')) AS `from_days(to_days("960101"))`,dayofmonth(_latin1'1997-01-02') AS `dayofmonth("1997-01-02")`,month(_latin1'1997-01-02') AS `month("1997-01-02")`,monthname(_latin1'1972-03-04') AS `monthname("1972-03-04")`,dayofyear(_latin1'0000-00-00') AS `dayofyear("0000-00-00")`,hour(_latin1'1997-03-03 23:03:22') AS `HOUR("1997-03-03 23:03:22")`,minute(_latin1'23:03:22') AS `MINUTE("23:03:22")`,second(230322) AS `SECOND(230322)`,quarter(980303) AS `QUARTER(980303)`,week(_latin1'1998-03-03',0) AS `WEEK("1998-03-03")`,yearweek(_latin1'2000-01-01',1) AS `yearweek("2000-01-01",1)`,week(19950101,1) AS `week(19950101,1)`,year(_latin1'98-02-03') AS `year("98-02-03")`,(weekday(to_days(curdate())) - weekday(to_days(now()))) AS `weekday(curdate())-weekday(now())`,dayname(to_days(_latin1'1962-03-03')) AS `dayname("1962-03-03")`,unix_timestamp() AS `unix_timestamp()`,sec_to_time((time_to_sec(_latin1'0:30:47') / 6.21)) AS `sec_to_time(time_to_sec("0:30:47")/6.21)`,curtime() AS `curtime()`,utc_time() AS `utc_time()`,curdate() AS `curdate()`,utc_date() AS `utc_date()`,utc_timestamp() AS `utc_timestamp()`,date_format(_latin1'1997-01-02 03:04:05',_latin1'%M %W %D %Y %y %m %d %h %i %s %w') AS `date_format("1997-01-02 03:04:05", "%M %W %D %Y %y %m %d %h %i %s %w")`,from_unixtime(unix_timestamp(_latin1'1994-03-02 10:11:12')) AS `from_unixtime(unix_timestamp("1994-03-02 10:11:12"))`,(_latin1'1997-12-31 23:59:59' + interval 1 second) AS `"1997-12-31 23:59:59" + INTERVAL 1 SECOND`,(_latin1'1998-01-01 00:00:00' - interval 1 second) AS `"1998-01-01 00:00:00" - INTERVAL 1 SECOND`,(_latin1'1997-12-31' + interval 1 day) AS `INTERVAL 1 DAY + "1997-12-31"`,extract(year from _latin1'1999-01-02 10:11:12') AS `extract(YEAR FROM "1999-01-02 10:11:12")`,(_latin1'1997-12-31 23:59:59' + interval 1 second) AS `date_add("1997-12-31 23:59:59",INTERVAL 1 SECOND)` diff --git a/mysql-test/r/group_by.result b/mysql-test/r/group_by.result index 98a4f9a390d..bbe3326fd34 100644 --- a/mysql-test/r/group_by.result +++ b/mysql-test/r/group_by.result @@ -284,13 +284,14 @@ select spid,count(*) from t1 where spid between 1 and 2 group by spid desc; spid count(*) 2 2 1 1 +explain extended select sql_big_result spid,sum(userid) from t1 group by spid desc; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ALL NULL NULL NULL NULL 8 Using filesort Warnings: Note 1003 select high_priority big_result test.t1.spID AS `spid`,sum(test.t1.userID) AS `sum(userid)` from test.t1 group by test.t1.spID desc explain select sql_big_result spid,sum(userid) from t1 group by spid desc order by null; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ALL NULL NULL NULL NULL 8 +1 SIMPLE t1 ALL NULL NULL NULL NULL 8 Using filesort select sql_big_result spid,sum(userid) from t1 group by spid desc; spid sum(userid) 7 3 @@ -611,8 +612,8 @@ userid count(*) 2 1 1 2 EXPLAIN SELECT userid,count(*) FROM t1 GROUP BY userid DESC; -table type possible_keys key key_len ref rows Extra -t1 ALL NULL NULL NULL NULL 8 Using temporary; Using filesort +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 8 Using temporary; Using filesort DROP TABLE t1; CREATE TABLE t1 ( i int(11) default NULL, @@ -625,6 +626,6 @@ i COUNT(DISTINCT(i)) 2 1 4 4 explain SELECT i, COUNT(DISTINCT(i)) FROM t1 GROUP BY j ORDER BY NULL; -table type possible_keys key key_len ref rows Extra -t1 ALL NULL NULL NULL NULL 6 Using filesort +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 6 Using filesort DROP TABLE t1; diff --git a/mysql-test/r/innodb.result b/mysql-test/r/innodb.result index d7bcea22dbf..eb0b9af8e38 100644 --- a/mysql-test/r/innodb.result +++ b/mysql-test/r/innodb.result @@ -1312,17 +1312,41 @@ a b 111 100 111 100 drop table t1; +create table t1 ( c char(8) not null ) engine=innodb; +insert into t1 values ('0'),('1'),('2'),('3'),('4'),('5'),('6'),('7'),('8'),('9'); +insert into t1 values ('A'),('B'),('C'),('D'),('E'),('F'); +alter table t1 add b char(8) not null; +alter table t1 add a char(8) not null; +alter table t1 add primary key (a,b,c); +update t1 set a=c, b=c; +create table t2 (c char(8) not null, b char(8) not null, a char(8) not null, primary key(a,b,c)) engine=innodb; +insert into t2 select * from t1; +delete t1,t2 from t2,t1 where t1.a<'B' and t2.b=t1.b; +drop table t1,t2; +SET AUTOCOMMIT=1; +create table t1 (a integer auto_increment primary key) engine=innodb; +insert into t1 (a) values (NULL),(NULL); +truncate table t1; +insert into t1 (a) values (NULL),(NULL); +SELECT * from t1; +a +3 +4 +drop table t1; CREATE TABLE t1 (col1 int(1))ENGINE=InnoDB; CREATE TABLE t2 (col1 int(1),stamp TIMESTAMP,INDEX stamp_idx (stamp))ENGINE=InnoDB; insert into t1 values (1),(2),(3); -insert into t2 values (1, 20020204130000),(2, 20020204130000),(4,20020204310000 ); +insert into t2 values (1, 20020204130000),(2, 20020204130000),(4,20020204310000 ),(5,20020204230000); +Warnings: +Warning 1264 Data truncated for column 'stamp' at row 3 SELECT col1 FROM t1 UNION SELECT col1 FROM t2 WHERE stamp < '20020204120000' GROUP BY col1; col1 1 2 3 +4 drop table t1,t2; CREATE TABLE t1 ( `id` int(10) unsigned NOT NULL auto_increment, @@ -1352,7 +1376,6 @@ id label 1822 Test 3 3524 Societe Test 3525 Fournisseur Test -drop table t1; drop table t1,t2; create table t1 (c1 char(5) unique not null, c2 int, stamp timestamp) engine=innodb; select * from t1; @@ -1391,24 +1414,3 @@ test.t2 968604391 test.t3 968604391 test.t4 NULL drop table t1,t2,t3; -create table t1 ( c char(8) not null ) type=innodb; -insert into t1 values ('0'),('1'),('2'),('3'),('4'),('5'),('6'),('7'),('8'),('9'); -insert into t1 values ('A'),('B'),('C'),('D'),('E'),('F'); -alter table t1 add b char(8) not null; -alter table t1 add a char(8) not null; -alter table t1 add primary key (a,b,c); -update t1 set a=c, b=c; -create table t2 (c char(8) not null, b char(8) not null, a char(8) not null, primary key(a,b,c)) type=innodb; -insert into t2 select * from t1; -delete t1,t2 from t2,t1 where t1.a<'B' and t2.b=t1.b; -drop table t1,t2; -SET AUTOCOMMIT=1; -create table t1 (a integer auto_increment primary key) type=innodb; -insert into t1 (a) values (NULL),(NULL); -truncate table t1; -insert into t1 (a) values (NULL),(NULL); -SELECT * from t1; -a -3 -4 -drop table t1; diff --git a/mysql-test/r/insert.result b/mysql-test/r/insert.result index 5af46e7db04..ff5111c552e 100644 --- a/mysql-test/r/insert.result +++ b/mysql-test/r/insert.result @@ -61,7 +61,6 @@ drop table t1; create table t1 (id int NOT NULL DEFAULT 8); insert into t1 values(NULL); ERROR 23000: Column 'id' cannot be null -ERROR 23000: Column 'id' cannot be null insert into t1 values (1), (NULL), (2); Warnings: Warning 1262 Data truncated, NULL supplied to NOT NULL column 'id' at row 2 diff --git a/mysql-test/r/join_outer.result b/mysql-test/r/join_outer.result index 19368ace977..c40f86827f8 100644 --- a/mysql-test/r/join_outer.result +++ b/mysql-test/r/join_outer.result @@ -4,7 +4,6 @@ grp int(11) default NULL, a bigint(20) unsigned default NULL, c char(10) NOT NULL default '' ) ENGINE=MyISAM; -) ENGINE=MyISAM; INSERT INTO t1 VALUES (1,1,'a'),(2,2,'b'),(2,3,'c'),(3,4,'E'),(3,5,'C'),(3,6,'D'),(NULL,NULL,''); create table t2 (id int, a bigint unsigned not null, c char(10), d int, primary key (a)); insert into t2 values (1,1,"a",1),(3,4,"A",4),(3,5,"B",5),(3,6,"C",6),(4,7,"D",7); @@ -678,8 +677,8 @@ a1 a2 b1 b2 c1 c2 2 2 2 3 NULL NULL 3 2 NULL NULL 3 4 explain select * from t1 left join t2 on b1 = a1 left join t3 on c1 = a1 and b1 is null; -table type possible_keys key key_len ref rows Extra -t1 ALL NULL NULL NULL NULL 3 -t2 ALL NULL NULL NULL NULL 2 -t3 ALL NULL NULL NULL NULL 2 +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 3 +1 SIMPLE t2 ALL NULL NULL NULL NULL 2 +1 SIMPLE t3 ALL NULL NULL NULL NULL 2 drop table t1, t2, t3; diff --git a/mysql-test/r/loaddata.result b/mysql-test/r/loaddata.result index d90ebb4cd79..0547596dfbd 100644 --- a/mysql-test/r/loaddata.result +++ b/mysql-test/r/loaddata.result @@ -31,7 +31,7 @@ drop table t1; create table t1 (a text, b text); load data infile '../../std_data/loaddata2.dat' into table t1 fields terminated by ',' enclosed by ''''; Warnings: -Warning 1260 Record count is fewer than the column count at row 3 +Warning 1260 Row 3 doesn't contain data for all columns select concat('|',a,'|'), concat('|',b,'|') from t1; concat('|',a,'|') concat('|',b,'|') |Field A| |Field B| @@ -42,6 +42,11 @@ Field 3,'Field 4| drop table t1; create table t1 (a int, b char(10)); load data infile '../../std_data/loaddata3.dat' into table t1 fields terminated by '' enclosed by '' ignore 1 lines; +Warnings: +Warning 1264 Data truncated for column 'a' at row 3 +Warning 1261 Row 3 was truncated; It contained more data than there where input columns +Warning 1264 Data truncated for column 'a' at row 5 +Warning 1261 Row 5 was truncated; It contained more data than there where input columns select * from t1; a b 1 row 1 @@ -51,6 +56,9 @@ a b 0 1234567890 truncate table t1; load data infile '../../std_data/loaddata4.dat' into table t1 fields terminated by '' enclosed by '' lines terminated by '' ignore 1 lines; +Warnings: +Warning 1264 Data truncated for column 'a' at row 4 +Warning 1260 Row 4 doesn't contain data for all columns select * from t1; a b 1 row 1 diff --git a/mysql-test/r/multi_update.result b/mysql-test/r/multi_update.result index 2e561452b8b..d335c9c1d10 100644 --- a/mysql-test/r/multi_update.result +++ b/mysql-test/r/multi_update.result @@ -351,7 +351,6 @@ a b 3 4 4 5 drop table t1; -drop table if exists t1, t2; create table t1(id1 smallint(5), field char(5)); create table t2(id2 smallint(5), field char(5)); insert into t1 values (1, 'a'), (2, 'aa'); @@ -396,3 +395,4 @@ t2 rows after big delete 1900001 select 't1 rows after big delete', count(*) from t1; t1 rows after big delete count(*) t1 rows after big delete 1900001 +drop table t1,t2; diff --git a/mysql-test/r/mysqldump.result b/mysql-test/r/mysqldump.result index 706a4d11241..04effdfef7c 100644 --- a/mysql-test/r/mysqldump.result +++ b/mysql-test/r/mysqldump.result @@ -21,21 +21,42 @@ DROP TABLE t1; CREATE TABLE t1 (a decimal(240, 20)); INSERT INTO t1 VALUES ("1234567890123456789012345678901234567890"), ("0987654321098765432109876543210987654321"); +DROP TABLE IF EXISTS t1; CREATE TABLE t1 ( a decimal(240,20) default NULL -) TYPE=MyISAM; +) ENGINE=MyISAM DEFAULT CHARSET=latin1; -INSERT INTO t1 VALUES ("1234567890123456789012345678901234567890.00000000000000000000"); -INSERT INTO t1 VALUES ("0987654321098765432109876543210987654321.00000000000000000000"); + +/*!40000 ALTER TABLE t1 DISABLE KEYS */; +LOCK TABLES t1 WRITE; +INSERT INTO t1 VALUES ("1234567890123456789012345678901234567890.00000000000000000000"),("0987654321098765432109876543210987654321.00000000000000000000"); +UNLOCK TABLES; +/*!40000 ALTER TABLE t1 ENABLE KEYS */; + +/*!40101 SET SQL_MODE=@OLD_SQL_MODE */; +/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */; +/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */; +/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */; DROP TABLE t1; CREATE TABLE t1 (a double); INSERT INTO t1 VALUES (-9e999999); +DROP TABLE IF EXISTS t1; CREATE TABLE t1 ( a double default NULL -) TYPE=MyISAM; +) ENGINE=MyISAM DEFAULT CHARSET=latin1; + +/*!40000 ALTER TABLE t1 DISABLE KEYS */; +LOCK TABLES t1 WRITE; INSERT INTO t1 VALUES (RES); +UNLOCK TABLES; +/*!40000 ALTER TABLE t1 ENABLE KEYS */; + +/*!40101 SET SQL_MODE=@OLD_SQL_MODE */; +/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */; +/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */; +/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */; DROP TABLE t1; CREATE TABLE t1(a int, b text, c varchar(3)); @@ -84,30 +105,11 @@ INSERT INTO t1 VALUES ("1\""), ("\"2"); DROP TABLE t1; CREATE TABLE t1 (a VARCHAR(255)) DEFAULT CHARSET koi8r; INSERT INTO t1 VALUES (_koi8r x'C1C2C3C4C5'); --- MySQL dump 10.3 --- --- Host: localhost Database: test --- ------------------------------------------------------ --- Server version 4.1.2-alpha-debug-log - -/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT, CHARACTER_SET_CLIENT=utf8 */; -/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */; -/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */; -/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE=NO_AUTO_VALUE_ON_ZERO */; - --- --- Table structure for table `t1` --- - DROP TABLE IF EXISTS t1; CREATE TABLE t1 ( a varchar(255) default NULL ) ENGINE=MyISAM DEFAULT CHARSET=koi8r; --- --- Dumping data for table `t1` --- - /*!40000 ALTER TABLE t1 DISABLE KEYS */; LOCK TABLES t1 WRITE; diff --git a/mysql-test/r/rpl_EE_error.result b/mysql-test/r/rpl_EE_error.result index a5043250df6..49ad4832c81 100644 --- a/mysql-test/r/rpl_EE_error.result +++ b/mysql-test/r/rpl_EE_error.result @@ -1,16 +1,16 @@ -slave stop; +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; -slave start; -create table t1 (a int) type=myisam; +start slave; +create table t1 (a int) engine=myisam; flush tables; drop table t1; -create table t1 (a int, unique(a)) type=myisam; +create table t1 (a int, unique(a)) engine=myisam; set sql_log_bin=0; insert into t1 values(2); set sql_log_bin=1; insert into t1 values(1),(2); -Duplicate entry '2' for key 1 +ERROR 23000: Duplicate entry '2' for key 1 drop table t1; diff --git a/mysql-test/r/rpl_multi_update.result b/mysql-test/r/rpl_multi_update.result index 1fa1dd104d2..65587d25f63 100644 --- a/mysql-test/r/rpl_multi_update.result +++ b/mysql-test/r/rpl_multi_update.result @@ -1,18 +1,17 @@ -slave stop; +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; -slave start; -drop table if exists t1,t2; +start slave; CREATE TABLE t1 ( a int unsigned not null auto_increment primary key, b int unsigned, -) TYPE=MyISAM; +) ENGINE=MyISAM; CREATE TABLE t2 ( a int unsigned not null auto_increment primary key, b int unsigned -) TYPE=MyISAM; +) ENGINE=MyISAM; INSERT INTO t1 VALUES (NULL, 0); INSERT INTO t1 SELECT NULL, 0 FROM t1; INSERT INTO t2 VALUES (NULL, 0), (NULL,1); diff --git a/mysql-test/r/symlink.result b/mysql-test/r/symlink.result index 5f726476d96..4ba0ca0eac4 100644 --- a/mysql-test/r/symlink.result +++ b/mysql-test/r/symlink.result @@ -65,22 +65,24 @@ t9 CREATE TABLE `t9` ( ) ENGINE=MyISAM DEFAULT CHARSET=latin1 DATA DIRECTORY='TEST_DIR/var/tmp/' INDEX DIRECTORY='TEST_DIR/var/run/' drop database mysqltest; create table t1 (a int not null) type=myisam; +Warnings: +Warning 1286 'TYPE=database_engine' is deprecated. Use 'ENGINE=database_engine' instead. show create table t1; Table Create Table t1 CREATE TABLE `t1` ( `a` int(11) NOT NULL default '0' -) TYPE=MyISAM +) ENGINE=MyISAM DEFAULT CHARSET=latin1 alter table t1 add b int; show create table t1; Table Create Table t1 CREATE TABLE `t1` ( `a` int(11) NOT NULL default '0', `b` int(11) default NULL -) TYPE=MyISAM +) ENGINE=MyISAM DEFAULT CHARSET=latin1 show create table t1; Table Create Table t1 CREATE TABLE `t1` ( `a` int(11) NOT NULL default '0', `b` int(11) default NULL -) TYPE=MyISAM +) ENGINE=MyISAM DEFAULT CHARSET=latin1 drop table t1; diff --git a/mysql-test/r/type_blob.result b/mysql-test/r/type_blob.result index f069114f752..255b7f0d942 100644 --- a/mysql-test/r/type_blob.result +++ b/mysql-test/r/type_blob.result @@ -585,9 +585,9 @@ create table t1 (id integer primary key auto_increment, txt text, unique index t insert into t1 (txt) values ('Chevy'), ('Chevy '), (NULL); select * from t1 where txt='Chevy' or txt is NULL; id txt -3 NULL 1 Chevy 2 Chevy +3 NULL select * from t1 where txt='Chevy '; id txt 1 Chevy diff --git a/mysql-test/r/type_datetime.result b/mysql-test/r/type_datetime.result index a32c97f1d49..c1ee621697d 100644 --- a/mysql-test/r/type_datetime.result +++ b/mysql-test/r/type_datetime.result @@ -1,6 +1,12 @@ drop table if exists t1; create table t1 (t datetime); insert into t1 values(101),(691231),(700101),(991231),(10000101),(99991231),(101000000),(691231000000),(700101000000),(991231235959),(10000101000000),(99991231235959),(20030102030460),(20030102036301),(20030102240401),(20030132030401),(20031302030460); +Warnings: +Warning 1264 Data truncated for column 't' at row 13 +Warning 1264 Data truncated for column 't' at row 14 +Warning 1264 Data truncated for column 't' at row 15 +Warning 1264 Data truncated for column 't' at row 16 +Warning 1264 Data truncated for column 't' at row 17 select * from t1; t 2000-01-01 00:00:00 diff --git a/mysql-test/r/type_decimal.result b/mysql-test/r/type_decimal.result index ee7d6583d64..3d3e5082b8a 100644 --- a/mysql-test/r/type_decimal.result +++ b/mysql-test/r/type_decimal.result @@ -446,6 +446,8 @@ CREATE TABLE t1 (a_dec DECIMAL(-1,1)); 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 '-1,1))' at line 1 create table t1(a decimal(10,4)); insert into t1 values ("+0000100000000"); +Warnings: +Warning 1263 Data truncated, out of range for column 'a' at row 1 select * from t1; a 9999999.9999 diff --git a/mysql-test/r/type_enum.result b/mysql-test/r/type_enum.result index e13a93a4156..8a7c15f7da9 100644 --- a/mysql-test/r/type_enum.result +++ b/mysql-test/r/type_enum.result @@ -1638,10 +1638,14 @@ t1 CREATE TABLE `t1` ( drop table t1; create table t1 (a enum ('0','1')); insert into t1 set a='foobar'; +Warnings: +Warning 1264 Data truncated for column 'a' at row 1 select * from t1; a update t1 set a = replace(a,'x','y'); +Warnings: +Warning 1264 Data truncated for column 'a' at row 1 select * from t1; a diff --git a/mysql-test/r/type_timestamp.result b/mysql-test/r/type_timestamp.result index a5d3abcdefc..172ccc440f4 100644 --- a/mysql-test/r/type_timestamp.result +++ b/mysql-test/r/type_timestamp.result @@ -24,15 +24,6 @@ a t 7 2002-03-05 00:00:00 8 0000-00-00 00:00:00 9 1970-01-01 03:20:38 -1 1970-01-01 03:20:34 -2 2002-03-03 00:00:00 -3 1970-01-01 03:20:35 -4 1970-01-01 03:20:36 -5 2002-03-04 00:00:00 -6 1970-01-01 03:20:37 -7 2002-03-05 00:00:00 -8 0000-00-00 00:00:00 -9 1970-01-01 03:20:38 drop table t1,t2; SET TIMESTAMP=1234; CREATE TABLE t1 (value TEXT NOT NULL, id VARCHAR(32) NOT NULL, stamp timestamp, PRIMARY KEY (id)); @@ -53,6 +44,12 @@ date_format(a,"%Y %y") year(a) year(now()) drop table t1; create table t1 (ix timestamp); insert into t1 values (19991101000000),(19990102030405),(19990630232922),(19990601000000),(19990930232922),(19990531232922),(19990501000000),(19991101000000),(19990501000000),(20030101010160),(20030101016001),(20030101240101),(20030132010101),(20031301010101); +Warnings: +Warning 1264 Data truncated for column 'ix' at row 10 +Warning 1264 Data truncated for column 'ix' at row 11 +Warning 1264 Data truncated for column 'ix' at row 12 +Warning 1264 Data truncated for column 'ix' at row 13 +Warning 1264 Data truncated for column 'ix' at row 14 select ix+0 from t1; ix+0 19991101000000 @@ -64,24 +61,24 @@ ix+0 19990501000000 19991101000000 19990501000000 -00000000000000 -00000000000000 -00000000000000 -00000000000000 -00000000000000 +0 +0 +0 +0 +0 delete from t1; insert into t1 values ("19991101000000"),("19990102030405"),("19990630232922"),("19990601000000"),("20030101010160"),("20030101016001"),("20030101240101"),("20030132010101"),("20031301010101"); -select * from t1; -ix +select ix+0 from t1; +ix+0 19991101000000 19990102030405 19990630232922 19990601000000 -00000000000000 -00000000000000 -00000000000000 -00000000000000 -00000000000000 +0 +0 +0 +0 +0 drop table t1; CREATE TABLE t1 (date date, date_time datetime, time_stamp timestamp); INSERT INTO t1 VALUES ("1998-12-31","1998-12-31 23:59:59",19981231235959); @@ -132,4 +129,4 @@ t2 t4 t6 t8 t10 t12 t14 1997-12-31 23:47:59 1997-12-31 23:47:59 1997-12-31 23:47:59 1997-12-31 23:47:59 1997-12-31 23:47:59 1997-12-31 23:47:59 1997-12-31 23:47:59 drop table t1; create table t1 (a timestamp default 1); -Invalid default value for 'a' +ERROR 42000: Invalid default value for 'a' diff --git a/mysql-test/r/union.result b/mysql-test/r/union.result index 1cfc6eedbaf..4a514e3dd18 100644 --- a/mysql-test/r/union.result +++ b/mysql-test/r/union.result @@ -435,8 +435,9 @@ a (SELECT * FROM t1) UNION all (SELECT SQL_CALC_FOUND_ROWS * FROM t2) LIMIT 1; ERROR 42000: Wrong usage/placement of 'SQL_CALC_FOUND_ROWS' create temporary table t1 select a from t1 union select a from t2; +drop temporary table t1; create table t1 select a from t1 union select a from t2; -INSERT TABLE 't1' isn't allowed in FROM table list +ERROR HY000: You can't specify target table 't1' for update in FROM clause drop table t1,t2; select length(version()) > 1 as `*` UNION select 2; * diff --git a/mysql-test/r/warnings.result b/mysql-test/r/warnings.result index 2769e284a95..fce2ddfd57a 100644 --- a/mysql-test/r/warnings.result +++ b/mysql-test/r/warnings.result @@ -49,9 +49,9 @@ Warnings: Warning 1262 Data truncated, NULL supplied to NOT NULL column 'b' at row 2 Warning 1264 Data truncated for column 'd' at row 3 Warning 1264 Data truncated for column 'c' at row 4 -Warning 1260 Record count is fewer than the column count at row 5 +Warning 1260 Row 5 doesn't contain data for all columns Warning 1264 Data truncated for column 'b' at row 6 -Warning 1261 Record count is more than the column count at row 7 +Warning 1261 Row 7 was truncated; It contained more data than there where input columns Warning 1263 Data truncated, out of range for column 'a' at row 8 select @@warning_count; @@warning_count diff --git a/mysql-test/t/bdb.test b/mysql-test/t/bdb.test index 835253e3134..12b81f27147 100644 --- a/mysql-test/t/bdb.test +++ b/mysql-test/t/bdb.test @@ -794,7 +794,7 @@ drop table t1; # Test of multi-table-updates (bug #1980). # -create table t1 ( c char(8) not null ) type=bdb; +create table t1 ( c char(8) not null ) engine=bdb; insert into t1 values ('0'),('1'),('2'),('3'),('4'),('5'),('6'),('7'),('8'),('9'); insert into t1 values ('A'),('B'),('C'),('D'),('E'),('F'); @@ -803,7 +803,7 @@ alter table t1 add a char(8) not null; alter table t1 add primary key (a,b,c); update t1 set a=c, b=c; -create table t2 (c char(8) not null, b char(8) not null, a char(8) not null, primary key(a,b,c)) type=bdb; +create table t2 (c char(8) not null, b char(8) not null, a char(8) not null, primary key(a,b,c)) engine=bdb; insert into t2 select * from t1; delete t1,t2 from t2,t1 where t1.a<'B' and t2.b=t1.b; diff --git a/mysql-test/t/innodb.test b/mysql-test/t/innodb.test index 5add6247eaa..b227614e7ae 100644 --- a/mysql-test/t/innodb.test +++ b/mysql-test/t/innodb.test @@ -906,7 +906,7 @@ drop table t1; # Test of multi-table-updates (bug #1980). # -create table t1 ( c char(8) not null ) type=innodb; +create table t1 ( c char(8) not null ) engine=innodb; insert into t1 values ('0'),('1'),('2'),('3'),('4'),('5'),('6'),('7'),('8'),('9'); insert into t1 values ('A'),('B'),('C'),('D'),('E'),('F'); @@ -915,7 +915,7 @@ alter table t1 add a char(8) not null; alter table t1 add primary key (a,b,c); update t1 set a=c, b=c; -create table t2 (c char(8) not null, b char(8) not null, a char(8) not null, primary key(a,b,c)) type=innodb; +create table t2 (c char(8) not null, b char(8) not null, a char(8) not null, primary key(a,b,c)) engine=innodb; insert into t2 select * from t1; delete t1,t2 from t2,t1 where t1.a<'B' and t2.b=t1.b; @@ -926,19 +926,23 @@ drop table t1,t2; # SET AUTOCOMMIT=1; -create table t1 (a integer auto_increment primary key) type=innodb; +create table t1 (a integer auto_increment primary key) engine=innodb; insert into t1 (a) values (NULL),(NULL); truncate table t1; insert into t1 (a) values (NULL),(NULL); SELECT * from t1; drop table t1; +# +# Test timestamps +# CREATE TABLE t1 (col1 int(1))ENGINE=InnoDB; CREATE TABLE t2 (col1 int(1),stamp TIMESTAMP,INDEX stamp_idx (stamp))ENGINE=InnoDB; insert into t1 values (1),(2),(3); -insert into t2 values (1, 20020204130000),(2, 20020204130000),(4,20020204310000 ); +# Note that timestamp 3 is wrong +insert into t2 values (1, 20020204130000),(2, 20020204130000),(4,20020204310000 ),(5,20020204230000); SELECT col1 FROM t1 UNION SELECT col1 FROM t2 WHERE stamp < '20020204120000' GROUP BY col1; drop table t1,t2; diff --git a/mysql-test/t/multi_update.test b/mysql-test/t/multi_update.test index 4b69b002342..6f012801972 100644 --- a/mysql-test/t/multi_update.test +++ b/mysql-test/t/multi_update.test @@ -60,8 +60,8 @@ delete from t1, t2, t3 using t1,t2,t3 where t1.id1 = t2.id2 and t2.id2 = t3.id3 select count(*) from t1 where id1; select count(*) from t2 where id2; select count(*) from t3 where id3; - drop table t1,t2,t3; + create table t1(id1 int not null primary key, t varchar(100)) pack_keys = 1; create table t2(id2 int not null, t varchar(100), index(id2)) pack_keys = 1; disable_query_log; @@ -288,7 +288,6 @@ drop table t1; # Test multi-update and multi-delete with impossible where -drop table if exists t1, t2; create table t1(id1 smallint(5), field char(5)); create table t2(id2 smallint(5), field char(5)); diff --git a/mysql-test/t/mysqldump.test b/mysql-test/t/mysqldump.test index c926c8e0b0d..1299d73eb99 100644 --- a/mysql-test/t/mysqldump.test +++ b/mysql-test/t/mysqldump.test @@ -6,7 +6,7 @@ DROP TABLE IF EXISTS t1, `"t"1`; CREATE TABLE t1(a int); INSERT INTO t1 VALUES (1), (2); ---exec $MYSQL_DUMP --skip-comments -X test t1 +--exec $MYSQL_DUMP --skip-all --skip-comments -X test t1 DROP TABLE t1; # @@ -33,7 +33,7 @@ DROP TABLE t1; CREATE TABLE t1(a int, b text, c varchar(3)); INSERT INTO t1 VALUES (1, "test", "tes"), (2, "TEST", "TES"); ---exec $MYSQL_DUMP --skip-all -X test t1 +--exec $MYSQL_DUMP --skip-all --skip-comments -X test t1 DROP TABLE t1; # @@ -42,7 +42,7 @@ DROP TABLE t1; CREATE TABLE t1 (`a"b"` char(2)); INSERT INTO t1 VALUES ("1\""), ("\"2"); ---exec $MYSQL_DUMP --skip-all -X test t1 +--exec $MYSQL_DUMP --skip-all --skip-comments -X test t1 DROP TABLE t1; # @@ -51,5 +51,5 @@ DROP TABLE t1; CREATE TABLE t1 (a VARCHAR(255)) DEFAULT CHARSET koi8r; INSERT INTO t1 VALUES (_koi8r x'C1C2C3C4C5'); ---exec $MYSQL_DUMP test t1 +--exec $MYSQL_DUMP --skip-comments test t1 DROP TABLE t1; diff --git a/mysql-test/t/rpl_EE_error.test b/mysql-test/t/rpl_EE_error.test index de69c4897c3..1a1572b48b0 100644 --- a/mysql-test/t/rpl_EE_error.test +++ b/mysql-test/t/rpl_EE_error.test @@ -6,7 +6,7 @@ source include/master-slave.inc; -create table t1 (a int) type=myisam; +create table t1 (a int) engine=myisam; flush tables; system rm ./var/master-data/test/t1.MYI ; drop table t1; @@ -17,7 +17,7 @@ sync_with_master; # Now a real error. connection master; -create table t1 (a int, unique(a)) type=myisam; +create table t1 (a int, unique(a)) engine=myisam; set sql_log_bin=0; insert into t1 values(2); set sql_log_bin=1; diff --git a/mysql-test/t/rpl_multi_update.test b/mysql-test/t/rpl_multi_update.test index c44239594dd..98a199ae412 100644 --- a/mysql-test/t/rpl_multi_update.test +++ b/mysql-test/t/rpl_multi_update.test @@ -1,15 +1,14 @@ source include/master-slave.inc; -drop table if exists t1,t2; CREATE TABLE t1 ( a int unsigned not null auto_increment primary key, b int unsigned, -) TYPE=MyISAM; +) ENGINE=MyISAM; CREATE TABLE t2 ( a int unsigned not null auto_increment primary key, b int unsigned -) TYPE=MyISAM; +) ENGINE=MyISAM; INSERT INTO t1 VALUES (NULL, 0); INSERT INTO t1 SELECT NULL, 0 FROM t1; diff --git a/mysql-test/t/union.test b/mysql-test/t/union.test index fc986b05fa4..8ce4aa997fc 100644 --- a/mysql-test/t/union.test +++ b/mysql-test/t/union.test @@ -248,6 +248,7 @@ SELECT * FROM t1 UNION SELECT * FROM t2 ORDER BY a desc LIMIT 1; (SELECT * FROM t1) UNION all (SELECT SQL_CALC_FOUND_ROWS * FROM t2) LIMIT 1; create temporary table t1 select a from t1 union select a from t2; +drop temporary table t1; --error 1093 create table t1 select a from t1 union select a from t2; drop table t1,t2; @@ -258,7 +259,6 @@ drop table t1,t2; select length(version()) > 1 as `*` UNION select 2; - # # Test for another bug with UNION and LEFT JOIN # @@ -465,4 +465,3 @@ show status like 'Slow_queries'; select count(*) from t1 where a=7 union select count(*) from t1 where b=13; show status like 'Slow_queries'; drop table t1; - |