diff options
Diffstat (limited to 'mysql-test')
63 files changed, 1070 insertions, 479 deletions
diff --git a/mysql-test/r/auto_increment.result b/mysql-test/r/auto_increment.result index 05317c0f845..1390141df51 100644 --- a/mysql-test/r/auto_increment.result +++ b/mysql-test/r/auto_increment.result @@ -244,3 +244,29 @@ 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 6021f02f2a6..dd5633af90d 100644 --- a/mysql-test/r/bdb.result +++ b/mysql-test/r/bdb.result @@ -1145,3 +1145,14 @@ x 7 6 drop table t1; +create table t1 ( c char(8) not null ) type=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; +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/binary.result b/mysql-test/r/binary.result index 5da627a30b9..d4576791aed 100644 --- a/mysql-test/r/binary.result +++ b/mysql-test/r/binary.result @@ -66,6 +66,7 @@ concat("-",a,"-",b,"-") alter table t1 modify b tinytext not null, drop key b, add key (b(100)); select concat("-",a,"-",b,"-") from t1 where b="hello "; concat("-",a,"-",b,"-") +-hello-hello- select concat("-",a,"-",b,"-") from t1 ignore index (b) where b="hello "; concat("-",a,"-",b,"-") -hello-hello- diff --git a/mysql-test/r/func_group.result b/mysql-test/r/func_group.result index bdac40d3378..ec00c850197 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 0 NULL NULL +NULL NULL 0 NULL NULL NULL 0 1844674407379551615 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 @@ -632,3 +632,15 @@ select a from t1 having a=1; a 1 drop table t1; +create table t1 (col int); +insert into t1 values (-1), (-2), (-3); +select bit_and(col), bit_or(col) from t1; +bit_and(col) bit_or(col) +18446744073709551612 18446744073709551615 +select SQL_BIG_RESULT bit_and(col), bit_or(col) from t1 group by col; +bit_and(col) bit_or(col) +18446744073709551613 18446744073709551613 +18446744073709551614 18446744073709551614 +18446744073709551615 18446744073709551615 +drop table t1; + diff --git a/mysql-test/r/func_op.result b/mysql-test/r/func_op.result index 25a7ac20b66..fb1e715ac3b 100644 --- a/mysql-test/r/func_op.result +++ b/mysql-test/r/func_op.result @@ -17,3 +17,21 @@ Note 1003 select high_priority (1 | (1 + 1)) AS `1 | (1+1)`,(5 & 3) AS `5 & 3`,b select 1 << 32,1 << 63, 1 << 64, 4 >> 2, 4 >> 63, 1<< 63 >> 60; 1 << 32 1 << 63 1 << 64 4 >> 2 4 >> 63 1<< 63 >> 60 4294967296 9223372036854775808 0 1 0 8 +select -1 | 0, -1 ^ 0, -1 & 0; +-1 | 0 -1 ^ 0 -1 & 0 +18446744073709551615 18446744073709551615 0 +select -1 | 1, -1 ^ 1, -1 & 1; +-1 | 1 -1 ^ 1 -1 & 1 +18446744073709551615 18446744073709551614 1 +select 1 | -1, 1 ^ -1, 1 & -1; +1 | -1 1 ^ -1 1 & -1 +18446744073709551615 18446744073709551614 1 +select 0 | -1, 0 ^ -1, 0 & -1; +0 | -1 0 ^ -1 0 & -1 +18446744073709551615 18446744073709551615 0 +select -1 >> 0, -1 << 0; +-1 >> 0 -1 << 0 +18446744073709551615 18446744073709551615 +select -1 >> 1, -1 << 1; +-1 >> 1 -1 << 1 +9223372036854775807 18446744073709551614 diff --git a/mysql-test/r/func_str.result b/mysql-test/r/func_str.result index ac075e129ee..aaebd33d010 100644 --- a/mysql-test/r/func_str.result +++ b/mysql-test/r/func_str.result @@ -144,12 +144,12 @@ montymontymontymontymonty * * select reverse('abc'),reverse('abcd'); reverse('abc') reverse('abcd') cba dcba -select rpad('a',4,'1'),rpad('a',4,'12'),rpad('abcd',3,'12'); -rpad('a',4,'1') rpad('a',4,'12') rpad('abcd',3,'12') -a111 a121 abc -select lpad('a',4,'1'),lpad('a',4,'12'),lpad('abcd',3,'12'); -lpad('a',4,'1') lpad('a',4,'12') lpad('abcd',3,'12') -111a 121a abc +select rpad('a',4,'1'),rpad('a',4,'12'),rpad('abcd',3,'12'), rpad(11, 10 , 22), rpad("ab", 10, 22); +rpad('a',4,'1') rpad('a',4,'12') rpad('abcd',3,'12') rpad(11, 10 , 22) rpad("ab", 10, 22) +a111 a121 abc 1122222222 ab22222222 +select lpad('a',4,'1'),lpad('a',4,'12'),lpad('abcd',3,'12'), lpad(11, 10 , 22); +lpad('a',4,'1') lpad('a',4,'12') lpad('abcd',3,'12') lpad(11, 10 , 22) +111a 121a abc 2222222211 select rpad(741653838,17,'0'),lpad(741653838,17,'0'); rpad(741653838,17,'0') lpad(741653838,17,'0') 74165383800000000 00000000741653838 @@ -159,6 +159,12 @@ abcdaba abaabcd select rpad('abcd',1,'ab'),lpad('abcd',1,'ab'); rpad('abcd',1,'ab') lpad('abcd',1,'ab') a a +select rpad('STRING', 20, CONCAT('p','a','d') ); +rpad('STRING', 20, CONCAT('p','a','d') ) +STRINGpadpadpadpadpa +select lpad('STRING', 20, CONCAT('p','a','d') ); +lpad('STRING', 20, CONCAT('p','a','d') ) +padpadpadpadpaSTRING select LEAST(NULL,'HARRY','HARRIOT',NULL,'HAROLD'),GREATEST(NULL,'HARRY','HARRIOT',NULL,'HAROLD'); LEAST(NULL,'HARRY','HARRIOT',NULL,'HAROLD') GREATEST(NULL,'HARRY','HARRIOT',NULL,'HAROLD') HAROLD HARRY @@ -183,6 +189,9 @@ NULL '\0\Z' select length(quote(concat(char(0),"test"))); length(quote(concat(char(0),"test"))) 8 +select hex(quote(concat(char(224),char(227),char(230),char(231),char(232),char(234),char(235)))); +hex(quote(concat(char(224),char(227),char(230),char(231),char(232),char(234),char(235)))) +27E0E3E6E7E8EAEB27 select reverse(""); reverse("") @@ -259,340 +268,3 @@ 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 b3c3d8dff5c..c3f4b9475f9 100644 --- a/mysql-test/r/func_time.result +++ b/mysql-test/r/func_time.result @@ -95,11 +95,34 @@ week(19981231,2) week(19981231,3) week(20000101,2) week(20000101,3) 52 53 52 52 select week(20001231,2),week(20001231,3); week(20001231,2) week(20001231,3) -1 52 +53 52 +select week(19981231,0) as '0', week(19981231,1) as '1', week(19981231,2) as '2', week(19981231,3) as '3', week(19981231,4) as '4', week(19981231,5) as '5', week(19981231,6) as '6', week(19981231,7) as '7'; +0 1 2 3 4 5 6 7 +52 53 52 53 52 52 52 52 +select week(20000101,0) as '0', week(20000101,1) as '1', week(20000101,2) as '2', week(20000101,3) as '3', week(20000101,4) as '4', week(20000101,5) as '5', week(20000101,6) as '6', week(20000101,7) as '7'; +0 1 2 3 4 5 6 7 +0 0 52 52 0 0 52 52 +select week(20000106,0) as '0', week(20000106,1) as '1', week(20000106,2) as '2', week(20000106,3) as '3', week(20000106,4) as '4', week(20000106,5) as '5', week(20000106,6) as '6', week(20000106,7) as '7'; +0 1 2 3 4 5 6 7 +1 1 1 1 1 1 1 1 +select week(20001231,0) as '0', week(20001231,1) as '1', week(20001231,2) as '2', week(20001231,3) as '3', week(20001231,4) as '4', week(20001231,5) as '5', week(20001231,6) as '6', week(20001231,7) as '7'; +0 1 2 3 4 5 6 7 +53 52 53 52 53 52 1 52 +select week(20010101,0) as '0', week(20010101,1) as '1', week(20010101,2) as '2', week(20010101,3) as '3', week(20010101,4) as '4', week(20010101,5) as '5', week(20010101,6) as '6', week(20010101,7) as '7'; +0 1 2 3 4 5 6 7 +0 1 53 1 1 1 1 1 +select yearweek(20001231,0), yearweek(20001231,1), yearweek(20001231,2), yearweek(20001231,3), yearweek(20001231,4), yearweek(20001231,5), yearweek(20001231,6), yearweek(20001231,7); +yearweek(20001231,0) yearweek(20001231,1) yearweek(20001231,2) yearweek(20001231,3) yearweek(20001231,4) yearweek(20001231,5) yearweek(20001231,6) yearweek(20001231,7) +200053 200052 200053 200052 200101 200052 200101 200052 +set default_week_format = 6; +select week(20001231), week(20001231,6); +week(20001231) week(20001231,6) +1 1 +set default_week_format = 0; set default_week_format = 2; select week(20001231),week(20001231,2),week(20001231,0); week(20001231) week(20001231,2) week(20001231,0) -1 1 53 +53 53 53 set default_week_format = 0; select date_format('1998-12-31','%x-%v'),date_format('1999-01-01','%x-%v'); date_format('1998-12-31','%x-%v') date_format('1999-01-01','%x-%v') @@ -408,103 +431,3 @@ 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; -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 853260a0164..98a4f9a390d 100644 --- a/mysql-test/r/group_by.result +++ b/mysql-test/r/group_by.result @@ -284,7 +284,6 @@ 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: @@ -600,3 +599,32 @@ count(*) category 1 3 1 4 drop table t1; +CREATE TABLE t1 ( +userid int(10) unsigned, +score smallint(5) unsigned, +key (score) +); +INSERT INTO t1 VALUES (1,1),(2,2),(1,1),(3,3),(3,3),(3,3),(3,3),(3,3); +SELECT userid,count(*) FROM t1 GROUP BY userid DESC; +userid count(*) +3 5 +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 +DROP TABLE t1; +CREATE TABLE t1 ( +i int(11) default NULL, +j int(11) default NULL +); +INSERT INTO t1 VALUES (1,2),(2,3),(4,5),(3,5),(1,5),(23,5); +SELECT i, COUNT(DISTINCT(i)) FROM t1 GROUP BY j ORDER BY NULL; +i COUNT(DISTINCT(i)) +1 1 +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 +DROP TABLE t1; diff --git a/mysql-test/r/have_mest_timezone.require b/mysql-test/r/have_mest_timezone.require deleted file mode 100644 index 2a219f39b7e..00000000000 --- a/mysql-test/r/have_mest_timezone.require +++ /dev/null @@ -1,2 +0,0 @@ -Variable_name Value -timezone MEST diff --git a/mysql-test/r/have_met_timezone.require b/mysql-test/r/have_met_timezone.require new file mode 100644 index 00000000000..b3fde075ebd --- /dev/null +++ b/mysql-test/r/have_met_timezone.require @@ -0,0 +1,2 @@ +FROM_UNIXTIME(24*3600) +1970-01-02 01:00:00 diff --git a/mysql-test/r/innodb.result b/mysql-test/r/innodb.result index 58c0042e122..d7bcea22dbf 100644 --- a/mysql-test/r/innodb.result +++ b/mysql-test/r/innodb.result @@ -1352,6 +1352,7 @@ 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; @@ -1390,3 +1391,24 @@ 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 23eef0b3c59..5af46e7db04 100644 --- a/mysql-test/r/insert.result +++ b/mysql-test/r/insert.result @@ -61,6 +61,7 @@ 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 @@ -73,7 +74,7 @@ drop table t1; create table t1 (email varchar(50)); insert into t1 values ('sasha@mysql.com'),('monty@mysql.com'),('foo@hotmail.com'),('foo@aol.com'),('bar@aol.com'); create table t2(id int not null auto_increment primary key, t2 varchar(50), unique(t2)); -insert into t2 (t2) select distinct substring(email, locate('@', email)+1) from t1; +insert delayed into t2 (t2) select distinct substring(email, locate('@', email)+1) from t1; select * from t2; id t2 1 mysql.com diff --git a/mysql-test/r/join_outer.result b/mysql-test/r/join_outer.result index 76c1b06b638..19368ace977 100644 --- a/mysql-test/r/join_outer.result +++ b/mysql-test/r/join_outer.result @@ -4,6 +4,7 @@ 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); @@ -665,3 +666,20 @@ left outer join t2 using (f2) left outer join t3 using (f3); ERROR 42S22: Unknown column 'test.t2.f3' in 'on clause' drop table t1,t2,t3; +create table t1 (a1 int, a2 int); +create table t2 (b1 int not null, b2 int); +create table t3 (c1 int, c2 int); +insert into t1 values (1,2), (2,2), (3,2); +insert into t2 values (1,3), (2,3); +insert into t3 values (2,4), (3,4); +select * from t1 left join t2 on b1 = a1 left join t3 on c1 = a1 and b1 is null; +a1 a2 b1 b2 c1 c2 +1 2 1 3 NULL NULL +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 +drop table t1, t2, t3; diff --git a/mysql-test/r/limit.result b/mysql-test/r/limit.result index 5a8edc99c12..c82105e6a49 100644 --- a/mysql-test/r/limit.result +++ b/mysql-test/r/limit.result @@ -36,9 +36,7 @@ a b 3 4 drop table t1; create table t1 (i int); -insert into t1 (i) values(1); -insert into t1 (i) values(1); -insert into t1 (i) values(1); +insert into t1 (i) values(1),(1),(1); delete from t1 limit 1; update t1 set i=2 limit 1; delete from t1 limit 0; @@ -50,3 +48,22 @@ i drop table t1; select 0 limit 0; 0 +CREATE TABLE t1(id int auto_increment primary key, id2 int, index(id2)); +INSERT INTO t1 (id2) values (0),(0),(0); +DELETE FROM t1 WHERE id=1; +INSERT INTO t1 SET id2=0; +SELECT * FROM t1; +id id2 +4 0 +2 0 +3 0 +DELETE FROM t1 WHERE id2 = 0 ORDER BY id LIMIT 1; +SELECT * FROM t1; +id id2 +4 0 +3 0 +DELETE FROM t1 WHERE id2 = 0 ORDER BY id desc LIMIT 1; +SELECT * FROM t1; +id id2 +3 0 +DROP TABLE t1; diff --git a/mysql-test/r/loaddata.result b/mysql-test/r/loaddata.result index d738431e016..d90ebb4cd79 100644 --- a/mysql-test/r/loaddata.result +++ b/mysql-test/r/loaddata.result @@ -40,3 +40,21 @@ Field 3,'Field 4| |Field 5' ,'Field 6| NULL |Field 6| | 'Field 7'| 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; +select * from t1; +a b +1 row 1 +2 row 2 +0 1234567890 +3 row 3 +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; +select * from t1; +a b +1 row 1 +2 row 2 +3 row 3 +0 +drop table t1; diff --git a/mysql-test/r/lowercase_table.result b/mysql-test/r/lowercase_table.result index 9c6b212a4b6..0ba4a4be945 100644 --- a/mysql-test/r/lowercase_table.result +++ b/mysql-test/r/lowercase_table.result @@ -1,6 +1,8 @@ -drop table if exists t1,t2,t3; +drop table if exists t1,t2,t3,t4,T1; create table T1 (id int primary key, Word varchar(40) not null, Index(Word)); +create table t4 (id int primary key, Word varchar(40) not null); INSERT INTO T1 VALUES (1, 'a'), (2, 'b'), (3, 'c'); +INSERT INTO T4 VALUES(1,'match'); SELECT * FROM t1; id Word 1 a @@ -12,6 +14,9 @@ id SELECT T2.id from t1 as T2 LIMIT 1; id 1 +SELECT * from t1 left join t4 on (test.t1.id= TEST.t4.id) where TEST.t1.id >= test.t4.id; +id Word id Word +1 a 1 match SELECT T2.id from t1 as t2 LIMIT 1; id 1 @@ -21,7 +26,8 @@ ALTER TABLE T2 RENAME T3; show tables like 't_'; Tables_in_test (t_) t3 -drop table t3; +t4 +drop table t3,t4; create table t1 (a int); select count(*) from T1; count(*) diff --git a/mysql-test/r/multi_update.result b/mysql-test/r/multi_update.result index 38d60cd21d5..2e561452b8b 100644 --- a/mysql-test/r/multi_update.result +++ b/mysql-test/r/multi_update.result @@ -341,6 +341,39 @@ select t1.a, t1.b,t2.a, t2.b from t1 left join t2 on t1.a=t2.a where t1.b=1 and a b a b 2 2 NULL NULL drop table t1,t2; +create table t1 (a int not null auto_increment primary key, b int not null); +insert into t1 (b) values (1),(2),(3),(4); +update t1, t1 as t2 set t1.b=t2.b+1 where t1.a=t2.a; +select * from t1; +a b +1 2 +2 3 +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'); +insert into t2 values (1, 'b'), (2, 'bb'); +select * from t1; +id1 field +1 a +2 aa +select * from t2; +id2 field +1 b +2 bb +update t2 inner join t1 on t1.id1=t2.id2 +set t2.field=t1.field +where 0=1; +update t2, t1 set t2.field=t1.field +where t1.id1=t2.id2 and 0=1; +delete t1, t2 from t2 inner join t1 on t1.id1=t2.id2 +where 0=1; +delete t1, t2 from t2,t1 +where t1.id1=t2.id2 and 0=1; +drop table t1,t2; create table t1 ( a int not null, b int not null) ; alter table t1 add index i1(a); delete from t1 where a > 2000000; diff --git a/mysql-test/r/mysqldump.result b/mysql-test/r/mysqldump.result index b49f8012776..706a4d11241 100644 --- a/mysql-test/r/mysqldump.result +++ b/mysql-test/r/mysqldump.result @@ -18,6 +18,26 @@ INSERT INTO t1 VALUES (1), (2); </database> </mysqldump> DROP TABLE t1; +CREATE TABLE t1 (a decimal(240, 20)); +INSERT INTO t1 VALUES ("1234567890123456789012345678901234567890"), +("0987654321098765432109876543210987654321"); +CREATE TABLE t1 ( + a decimal(240,20) default NULL +) TYPE=MyISAM; + +INSERT INTO t1 VALUES ("1234567890123456789012345678901234567890.00000000000000000000"); +INSERT INTO t1 VALUES ("0987654321098765432109876543210987654321.00000000000000000000"); + +DROP TABLE t1; +CREATE TABLE t1 (a double); +INSERT INTO t1 VALUES (-9e999999); +CREATE TABLE t1 ( + a double default NULL +) TYPE=MyISAM; + +INSERT INTO t1 VALUES (RES); + +DROP TABLE t1; CREATE TABLE t1(a int, b text, c varchar(3)); INSERT INTO t1 VALUES (1, "test", "tes"), (2, "TEST", "TES"); <?xml version="1.0"?> diff --git a/mysql-test/r/query_cache.result b/mysql-test/r/query_cache.result index bf658bae89e..fe2ded691d4 100644 --- a/mysql-test/r/query_cache.result +++ b/mysql-test/r/query_cache.result @@ -370,16 +370,23 @@ insert into mysqltest.t1 (a) values (1); select * from mysqltest.t1 where i is null; i a 1 1 +create table t1(a int); +select * from t1; +a +show status like "Qcache_queries_in_cache"; +Variable_name Value +Qcache_queries_in_cache 1 select * from mysqltest.t1; i a 1 1 show status like "Qcache_queries_in_cache"; Variable_name Value -Qcache_queries_in_cache 1 +Qcache_queries_in_cache 2 drop database mysqltest; show status like "Qcache_queries_in_cache"; Variable_name Value -Qcache_queries_in_cache 0 +Qcache_queries_in_cache 1 +drop table t1; create table t1 (a char(1) not null collate koi8r_general_ci); insert into t1 values(_koi8r"á"); set CHARACTER SET koi8r; diff --git a/mysql-test/r/rpl_EE_error.result b/mysql-test/r/rpl_EE_error.result new file mode 100644 index 00000000000..a5043250df6 --- /dev/null +++ b/mysql-test/r/rpl_EE_error.result @@ -0,0 +1,16 @@ +slave stop; +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; +flush tables; +drop table t1; +create table t1 (a int, unique(a)) type=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 +drop table t1; diff --git a/mysql-test/r/rpl_multi_update.result b/mysql-test/r/rpl_multi_update.result new file mode 100644 index 00000000000..1fa1dd104d2 --- /dev/null +++ b/mysql-test/r/rpl_multi_update.result @@ -0,0 +1,27 @@ +slave stop; +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; +CREATE TABLE t1 ( +a int unsigned not null auto_increment primary key, +b int unsigned, +) TYPE=MyISAM; +CREATE TABLE t2 ( +a int unsigned not null auto_increment primary key, +b int unsigned +) TYPE=MyISAM; +INSERT INTO t1 VALUES (NULL, 0); +INSERT INTO t1 SELECT NULL, 0 FROM t1; +INSERT INTO t2 VALUES (NULL, 0), (NULL,1); +SELECT * FROM t1 ORDER BY a; +a b +1 0 +2 0 +SELECT * FROM t2 ORDER BY a; +a b +1 0 +2 1 +UPDATE t1, t2 SET t1.b = t2.b WHERE t1.a = t2.a; diff --git a/mysql-test/r/rpl_rotate_logs.result b/mysql-test/r/rpl_rotate_logs.result index 87ebf870f38..2264272051a 100644 --- a/mysql-test/r/rpl_rotate_logs.result +++ b/mysql-test/r/rpl_rotate_logs.result @@ -68,6 +68,9 @@ m create temporary table temp_table (a char(80) not null); insert into temp_table values ("testing temporary tables part 2"); create table t3 (n int); +select count(*) from t3 where n >= 4; +count(*) +100 create table t4 select * from temp_table; show binary logs; Log_name diff --git a/mysql-test/r/select_found.result b/mysql-test/r/select_found.result index 42f7257dbba..e302a0cefeb 100644 --- a/mysql-test/r/select_found.result +++ b/mysql-test/r/select_found.result @@ -189,3 +189,22 @@ SELECT FOUND_ROWS(); FOUND_ROWS() 3 drop table t1; +create table t1 (id int, primary key (id)); +insert into t1 values (1), (2), (3), (4), (5); +select SQL_CALC_FOUND_ROWS * from t1 where id > 3 limit 0, 1; +id +4 +select FOUND_ROWS(); +FOUND_ROWS() +2 +select SQL_CALC_FOUND_ROWS * from t1 where id > 3 AND 1=2 limit 0, 1; +id +select FOUND_ROWS(); +FOUND_ROWS() +0 +select SQL_CALC_FOUND_ROWS * from t1 where id > 6 limit 0, 1; +id +select FOUND_ROWS(); +FOUND_ROWS() +0 +drop table t1; diff --git a/mysql-test/r/symlink.result b/mysql-test/r/symlink.result index 48a44fc2b44..5f726476d96 100644 --- a/mysql-test/r/symlink.result +++ b/mysql-test/r/symlink.result @@ -64,3 +64,23 @@ t9 CREATE TABLE `t9` ( PRIMARY KEY (`a`) ) 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; +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` int(11) NOT NULL default '0' +) TYPE=MyISAM +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 +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 +drop table t1; diff --git a/mysql-test/r/timezone.result b/mysql-test/r/timezone.result index b82b39da262..20706408075 100644 --- a/mysql-test/r/timezone.result +++ b/mysql-test/r/timezone.result @@ -1,4 +1,13 @@ DROP TABLE IF EXISTS t1; +show variables like "timezone"; +Variable_name Value +timezone MET +select @a:=FROM_UNIXTIME(1); +@a:=FROM_UNIXTIME(1) +1970-01-01 01:00:01 +select unix_timestamp(@a); +unix_timestamp(@a) +1 CREATE TABLE t1 (ts int); INSERT INTO t1 (ts) VALUES (Unix_timestamp('2002-10-27 01:00')); INSERT INTO t1 (ts) VALUES (Unix_timestamp('2002-10-27 02:00')); diff --git a/mysql-test/r/truncate.result b/mysql-test/r/truncate.result index 0e256c1bf8e..d777bd184b2 100644 --- a/mysql-test/r/truncate.result +++ b/mysql-test/r/truncate.result @@ -23,3 +23,12 @@ n drop table t1; truncate non_existing_table; ERROR 42S02: Table 'test.non_existing_table' doesn't exist +create table t1 (a integer auto_increment primary key); +insert into t1 (a) values (NULL),(NULL); +truncate table t1; +insert into t1 (a) values (NULL),(NULL); +SELECT * from t1; +a +1 +2 +drop table t1; diff --git a/mysql-test/r/type_blob.result b/mysql-test/r/type_blob.result index a69e3308f0a..f069114f752 100644 --- a/mysql-test/r/type_blob.result +++ b/mysql-test/r/type_blob.result @@ -510,3 +510,149 @@ show full fields from t1; Field Type Collation Null Key Default Extra Privileges Comment load_file('../../std_data/words.dat') longblob NULL YES NULL select,insert,update,references drop table t1; +create table t1 (id integer primary key auto_increment, txt text not null, unique index txt_index (txt (20))); +insert into t1 (txt) values ('Chevy'), ('Chevy '); +select * from t1 where txt='Chevy'; +id txt +1 Chevy +2 Chevy +select * from t1 where txt='Chevy '; +id txt +1 Chevy +2 Chevy +select * from t1 where txt='Chevy ' or txt='Chevy'; +id txt +1 Chevy +2 Chevy +select * from t1 where txt='Chevy' or txt='Chevy '; +id txt +1 Chevy +2 Chevy +select * from t1 where id='1' or id='2'; +id txt +1 Chevy +2 Chevy +insert into t1 (txt) values('Ford'); +select * from t1 where txt='Chevy' or txt='Chevy ' or txt='Ford'; +id txt +1 Chevy +2 Chevy +3 Ford +select * from t1 where txt='Chevy' or txt='Chevy '; +id txt +1 Chevy +2 Chevy +select * from t1 where txt='Chevy' or txt='Chevy ' or txt=' Chevy'; +id txt +1 Chevy +2 Chevy +select * from t1 where txt in ('Chevy ','Chevy'); +id txt +1 Chevy +2 Chevy +select * from t1 where txt in ('Chevy'); +id txt +1 Chevy +2 Chevy +select * from t1 where txt between 'Chevy' and 'Chevy'; +id txt +1 Chevy +2 Chevy +select * from t1 where txt between 'Chevy' and 'Chevy' or txt='Chevy '; +id txt +1 Chevy +2 Chevy +select * from t1 where txt between 'Chevy' and 'Chevy '; +id txt +1 Chevy +2 Chevy +select * from t1 where txt < 'Chevy '; +id txt +select * from t1 where txt <= 'Chevy'; +id txt +1 Chevy +2 Chevy +select * from t1 where txt > 'Chevy'; +id txt +3 Ford +select * from t1 where txt >= 'Chevy'; +id txt +1 Chevy +2 Chevy +3 Ford +drop table t1; +create table t1 (id integer primary key auto_increment, txt text, unique index txt_index (txt (20))); +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 +select * from t1 where txt='Chevy '; +id txt +1 Chevy +2 Chevy +select * from t1 where txt='Chevy ' or txt='Chevy'; +id txt +1 Chevy +2 Chevy +select * from t1 where txt='Chevy' or txt='Chevy '; +id txt +1 Chevy +2 Chevy +select * from t1 where id='1' or id='2'; +id txt +1 Chevy +2 Chevy +insert into t1 (txt) values('Ford'); +select * from t1 where txt='Chevy' or txt='Chevy ' or txt='Ford'; +id txt +1 Chevy +2 Chevy +4 Ford +select * from t1 where txt='Chevy' or txt='Chevy '; +id txt +1 Chevy +2 Chevy +select * from t1 where txt='Chevy' or txt='Chevy ' or txt=' Chevy'; +id txt +1 Chevy +2 Chevy +select * from t1 where txt in ('Chevy ','Chevy'); +id txt +1 Chevy +2 Chevy +select * from t1 where txt in ('Chevy'); +id txt +1 Chevy +2 Chevy +select * from t1 where txt between 'Chevy' and 'Chevy'; +id txt +1 Chevy +2 Chevy +select * from t1 where txt between 'Chevy' and 'Chevy' or txt='Chevy '; +id txt +1 Chevy +2 Chevy +select * from t1 where txt between 'Chevy' and 'Chevy '; +id txt +1 Chevy +2 Chevy +select * from t1 where txt < 'Chevy '; +id txt +select * from t1 where txt < 'Chevy ' or txt is NULL; +id txt +3 NULL +select * from t1 where txt <= 'Chevy'; +id txt +1 Chevy +2 Chevy +select * from t1 where txt > 'Chevy'; +id txt +4 Ford +select * from t1 where txt >= 'Chevy'; +id txt +1 Chevy +2 Chevy +4 Ford +drop table t1; diff --git a/mysql-test/r/type_datetime.result b/mysql-test/r/type_datetime.result index 204266d6ec2..a32c97f1d49 100644 --- a/mysql-test/r/type_datetime.result +++ b/mysql-test/r/type_datetime.result @@ -1,6 +1,6 @@ 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); +insert into t1 values(101),(691231),(700101),(991231),(10000101),(99991231),(101000000),(691231000000),(700101000000),(991231235959),(10000101000000),(99991231235959),(20030102030460),(20030102036301),(20030102240401),(20030132030401),(20031302030460); select * from t1; t 2000-01-01 00:00:00 @@ -15,6 +15,11 @@ t 1999-12-31 23:59:59 1000-01-01 00:00:00 9999-12-31 23:59:59 +0000-00-00 00:00:00 +0000-00-00 00:00:00 +0000-00-00 00:00:00 +0000-00-00 00:00:00 +0000-00-00 00:00:00 delete from t1 where t > 0; optimize table t1; Table Op Msg_type Msg_text @@ -22,7 +27,8 @@ test.t1 optimize status OK check table t1; Table Op Msg_type Msg_text test.t1 check status OK -insert into t1 values("000101"),("691231"),("700101"),("991231"),("00000101"),("00010101"),("99991231"),("00101000000"),("691231000000"),("700101000000"),("991231235959"),("10000101000000"),("99991231235959"); +delete from t1; +insert into t1 values("000101"),("691231"),("700101"),("991231"),("00000101"),("00010101"),("99991231"),("00101000000"),("691231000000"),("700101000000"),("991231235959"),("10000101000000"),("99991231235959"),("20030102030460"),("20030102036301"),("20030102240401"),("20030132030401"),("20031302030460"); select * from t1; t 2000-01-01 00:00:00 @@ -38,6 +44,11 @@ t 1999-12-31 23:59:59 1000-01-01 00:00:00 9999-12-31 23:59:59 +0000-00-00 00:00:00 +0000-00-00 00:00:00 +0000-00-00 00:00:00 +0000-00-00 00:00:00 +0000-00-00 00:00:00 drop table t1; CREATE TABLE t1 (a timestamp, b date, c time, d datetime); insert into t1 (b,c,d) values(now(),curtime(),now()); diff --git a/mysql-test/r/type_decimal.result b/mysql-test/r/type_decimal.result index da25394eb08..ee7d6583d64 100644 --- a/mysql-test/r/type_decimal.result +++ b/mysql-test/r/type_decimal.result @@ -444,3 +444,9 @@ CREATE TABLE t1 (a_dec DECIMAL(-2,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 '-2,1))' at line 1 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"); +select * from t1; +a +9999999.9999 +drop table t1; diff --git a/mysql-test/r/type_enum.result b/mysql-test/r/type_enum.result index 1903037480a..e13a93a4156 100644 --- a/mysql-test/r/type_enum.result +++ b/mysql-test/r/type_enum.result @@ -1636,6 +1636,16 @@ t1 CREATE TABLE `t1` ( `a` enum('','a','b') NOT NULL default 'b' ) ENGINE=MyISAM DEFAULT CHARSET=latin1 drop table t1; +create table t1 (a enum ('0','1')); +insert into t1 set a='foobar'; +select * from t1; +a + +update t1 set a = replace(a,'x','y'); +select * from t1; +a + +drop table t1; create table t1 (a enum(0xE4, '1', '2') not null default 0xE4); show columns from t1; Field Type Null Key Default Extra diff --git a/mysql-test/r/type_timestamp.result b/mysql-test/r/type_timestamp.result index 0c75155146d..a5d3abcdefc 100644 --- a/mysql-test/r/type_timestamp.result +++ b/mysql-test/r/type_timestamp.result @@ -24,6 +24,15 @@ 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)); @@ -43,7 +52,7 @@ date_format(a,"%Y %y") year(a) year(now()) 1970 70 1970 1970 drop table t1; create table t1 (ix timestamp); -insert into t1 values (19991101000000),(19990102030405),(19990630232922),(19990601000000),(19990930232922),(19990531232922),(19990501000000),(19991101000000),(19990501000000); +insert into t1 values (19991101000000),(19990102030405),(19990630232922),(19990601000000),(19990930232922),(19990531232922),(19990501000000),(19991101000000),(19990501000000),(20030101010160),(20030101016001),(20030101240101),(20030132010101),(20031301010101); select ix+0 from t1; ix+0 19991101000000 @@ -55,6 +64,24 @@ ix+0 19990501000000 19991101000000 19990501000000 +00000000000000 +00000000000000 +00000000000000 +00000000000000 +00000000000000 +delete from t1; +insert into t1 values ("19991101000000"),("19990102030405"),("19990630232922"),("19990601000000"),("20030101010160"),("20030101016001"),("20030101240101"),("20030132010101"),("20031301010101"); +select * from t1; +ix +19991101000000 +19990102030405 +19990630232922 +19990601000000 +00000000000000 +00000000000000 +00000000000000 +00000000000000 +00000000000000 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); @@ -104,3 +131,5 @@ t2 t4 t6 t8 t10 t12 t14 0000-00-00 00:00:00 0000-00-00 00:00:00 0000-00-00 00:00:00 0000-00-00 00:00:00 0000-00-00 00:00:00 0000-00-00 00:00:00 0000-00-00 00:00:00 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' diff --git a/mysql-test/r/union.result b/mysql-test/r/union.result index b3bf95bdf3d..1cfc6eedbaf 100644 --- a/mysql-test/r/union.result +++ b/mysql-test/r/union.result @@ -3,7 +3,7 @@ CREATE TABLE t1 (a int not null, b char (10) not null); insert into t1 values(1,'a'),(2,'b'),(3,'c'),(3,'c'); CREATE TABLE t2 (a int not null, b char (10) not null); insert into t2 values (3,'c'),(4,'d'),(5,'f'),(6,'e'); -select a,b from t1 union select a,b from t2; +select a,b from t1 union distinct select a,b from t2; a b 1 a 2 b @@ -434,7 +434,14 @@ a 3 (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; +create table t1 select a from t1 union select a from t2; +INSERT TABLE 't1' isn't allowed in FROM table list drop table t1,t2; +select length(version()) > 1 as `*` UNION select 2; +* +1 +2 CREATE TABLE t1 ( id int(3) unsigned default '0') ENGINE=MyISAM; INSERT INTO t1 (id) VALUES("1"); CREATE TABLE t2 ( id int(3) unsigned default '0', id_master int(5) default '0', text1 varchar(5) default NULL, text2 varchar(5) default NULL) ENGINE=MyISAM; diff --git a/mysql-test/std_data/loaddata3.dat b/mysql-test/std_data/loaddata3.dat new file mode 100644 index 00000000000..4c82f1396c5 --- /dev/null +++ b/mysql-test/std_data/loaddata3.dat @@ -0,0 +1,6 @@ +number row data +1 row 1 +2 row 2 +error 12345678901234567890123456789012345678901234567890 +3 row 3 +wrong end 12345678901234567890123456789012345678901234567890 diff --git a/mysql-test/std_data/loaddata4.dat b/mysql-test/std_data/loaddata4.dat new file mode 100644 index 00000000000..9c0d744438c --- /dev/null +++ b/mysql-test/std_data/loaddata4.dat @@ -0,0 +1 @@ +test row data 1 row 1 2 row 2 3 row 3 diff --git a/mysql-test/t/auto_increment.test b/mysql-test/t/auto_increment.test index 6cf286f86c0..f2a769d9f06 100644 --- a/mysql-test/t/auto_increment.test +++ b/mysql-test/t/auto_increment.test @@ -74,6 +74,25 @@ check table t1; drop table t1; # +# Test negative values (Bug #1366) +# + +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(); +insert into t1 values (NULL); +select * from t1; +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(); +insert into t1 values (NULL); +select * from t1; +drop table t1; +# # last_insert_id() madness # create table t1 (i tinyint unsigned not null auto_increment primary key); diff --git a/mysql-test/t/bdb.test b/mysql-test/t/bdb.test index 44bb753d6a1..835253e3134 100644 --- a/mysql-test/t/bdb.test +++ b/mysql-test/t/bdb.test @@ -789,3 +789,22 @@ select * from t1 where x <= 10 and x >= 7 order by x desc; select * from t1 where x <= 8 and x >= 5 order by x desc; select * from t1 where x < 8 and x > 5 order by x desc; drop table t1; + +# +# Test of multi-table-updates (bug #1980). +# + +create table t1 ( c char(8) not null ) type=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; +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/t/func_group.test b/mysql-test/t/func_group.test index 17ed4c03b5a..ce89cbe1b22 100644 --- a/mysql-test/t/func_group.test +++ b/mysql-test/t/func_group.test @@ -370,3 +370,11 @@ insert into t1 values (1); select max(a) as b from t1 having b=1; select a from t1 having a=1; drop table t1; +# +# Bug #1972: test for bit_and(), bit_or() and negative values +# +create table t1 (col int); +insert into t1 values (-1), (-2), (-3); +select bit_and(col), bit_or(col) from t1; +select SQL_BIG_RESULT bit_and(col), bit_or(col) from t1 group by col; +drop table t1; diff --git a/mysql-test/t/func_op.test b/mysql-test/t/func_op.test index cb6ca58f193..33a2884b424 100644 --- a/mysql-test/t/func_op.test +++ b/mysql-test/t/func_op.test @@ -7,3 +7,12 @@ explain extended select 1+1,1-1,1+1*2,8/5,8%5,mod(8,5),mod(8,5)|0,-(1+1)*-2; select 1 | (1+1),5 & 3,bit_count(7) ; explain extended select 1 | (1+1),5 & 3,bit_count(7) ; select 1 << 32,1 << 63, 1 << 64, 4 >> 2, 4 >> 63, 1<< 63 >> 60; +# +# bug #1993: bit functions must be unsigned +# +select -1 | 0, -1 ^ 0, -1 & 0; +select -1 | 1, -1 ^ 1, -1 & 1; +select 1 | -1, 1 ^ -1, 1 & -1; +select 0 | -1, 0 ^ -1, 0 & -1; +select -1 >> 0, -1 << 0; +select -1 >> 1, -1 << 1; diff --git a/mysql-test/t/func_str.test b/mysql-test/t/func_str.test index 0003f14c9ca..ad7b9b21b51 100644 --- a/mysql-test/t/func_str.test +++ b/mysql-test/t/func_str.test @@ -61,11 +61,13 @@ select aes_decrypt("a","a"); select aes_decrypt(aes_encrypt("","a"),"a"); select repeat('monty',5),concat('*',space(5),'*'); select reverse('abc'),reverse('abcd'); -select rpad('a',4,'1'),rpad('a',4,'12'),rpad('abcd',3,'12'); -select lpad('a',4,'1'),lpad('a',4,'12'),lpad('abcd',3,'12'); +select rpad('a',4,'1'),rpad('a',4,'12'),rpad('abcd',3,'12'), rpad(11, 10 , 22), rpad("ab", 10, 22); +select lpad('a',4,'1'),lpad('a',4,'12'),lpad('abcd',3,'12'), lpad(11, 10 , 22); select rpad(741653838,17,'0'),lpad(741653838,17,'0'); select rpad('abcd',7,'ab'),lpad('abcd',7,'ab'); select rpad('abcd',1,'ab'),lpad('abcd',1,'ab'); +select rpad('STRING', 20, CONCAT('p','a','d') ); +select lpad('STRING', 20, CONCAT('p','a','d') ); select LEAST(NULL,'HARRY','HARRIOT',NULL,'HAROLD'),GREATEST(NULL,'HARRY','HARRIOT',NULL,'HAROLD'); select least(1,2,3) | greatest(16,32,8), least(5,4)*1,greatest(-1.0,1.0)*1,least(3,2,1)*1.0,greatest(1,1.1,1.0),least("10",9),greatest("A","B","0"); @@ -77,6 +79,7 @@ select quote('\'\"\\test'); select quote(concat('abc\'', '\\cba')); select quote(1/0), quote('\0\Z'); select length(quote(concat(char(0),"test"))); +select hex(quote(concat(char(224),char(227),char(230),char(231),char(232),char(234),char(235)))); # # Wrong usage of functions diff --git a/mysql-test/t/func_time.test b/mysql-test/t/func_time.test index 6f114fe18ae..06d0ff3fa1d 100644 --- a/mysql-test/t/func_time.test +++ b/mysql-test/t/func_time.test @@ -42,6 +42,18 @@ select yearweek("2000-01-06",1) as '2000', yearweek("2001-01-06",1) as '2001', y select week(19981231,2), week(19981231,3), week(20000101,2), week(20000101,3); select week(20001231,2),week(20001231,3); +select week(19981231,0) as '0', week(19981231,1) as '1', week(19981231,2) as '2', week(19981231,3) as '3', week(19981231,4) as '4', week(19981231,5) as '5', week(19981231,6) as '6', week(19981231,7) as '7'; +select week(20000101,0) as '0', week(20000101,1) as '1', week(20000101,2) as '2', week(20000101,3) as '3', week(20000101,4) as '4', week(20000101,5) as '5', week(20000101,6) as '6', week(20000101,7) as '7'; +select week(20000106,0) as '0', week(20000106,1) as '1', week(20000106,2) as '2', week(20000106,3) as '3', week(20000106,4) as '4', week(20000106,5) as '5', week(20000106,6) as '6', week(20000106,7) as '7'; +select week(20001231,0) as '0', week(20001231,1) as '1', week(20001231,2) as '2', week(20001231,3) as '3', week(20001231,4) as '4', week(20001231,5) as '5', week(20001231,6) as '6', week(20001231,7) as '7'; +select week(20010101,0) as '0', week(20010101,1) as '1', week(20010101,2) as '2', week(20010101,3) as '3', week(20010101,4) as '4', week(20010101,5) as '5', week(20010101,6) as '6', week(20010101,7) as '7'; + +select yearweek(20001231,0), yearweek(20001231,1), yearweek(20001231,2), yearweek(20001231,3), yearweek(20001231,4), yearweek(20001231,5), yearweek(20001231,6), yearweek(20001231,7); + +set default_week_format = 6; +select week(20001231), week(20001231,6); +set default_week_format = 0; + set default_week_format = 2; select week(20001231),week(20001231,2),week(20001231,0); set default_week_format = 0; @@ -194,6 +206,13 @@ select * from t1, t3 where t1.start between t3.ctime1 and t3.ctime2; drop table t1,t2,t3; # +# Test unix timestamp +# +select @a:=FROM_UNIXTIME(1); +select unix_timestamp(@a); +select unix_timestamp('1969-12-01 19:00:01'); + +# # Test types from + INTERVAL # diff --git a/mysql-test/t/group_by.test b/mysql-test/t/group_by.test index 1e65bef19b7..d6d1922c10f 100644 --- a/mysql-test/t/group_by.test +++ b/mysql-test/t/group_by.test @@ -432,3 +432,27 @@ select id, sum(qty) as sqty, count(qty) as cqty from t1 group by id having sum(q select count(*), case interval(qty,2,3,4,5,6,7,8) when -1 then NULL when 0 then "zero" when 1 then "one" when 2 then "two" end as category from t1 group by category; select count(*), interval(qty,2,3,4,5,6,7,8) as category from t1 group by category; drop table t1; +# +# Tests for bug #1355: 'Using filesort' is missing in EXPLAIN when ORDER BY +# NULL is used. +# +CREATE TABLE t1 ( + userid int(10) unsigned, + score smallint(5) unsigned, + key (score) +); +INSERT INTO t1 VALUES (1,1),(2,2),(1,1),(3,3),(3,3),(3,3),(3,3),(3,3); +# Here we select unordered GROUP BY into a temporary talbe, +# and then sort it with filesort (GROUP BY in MySQL +# implies sorted order of results) +SELECT userid,count(*) FROM t1 GROUP BY userid DESC; +EXPLAIN SELECT userid,count(*) FROM t1 GROUP BY userid DESC; +DROP TABLE t1; +CREATE TABLE t1 ( + i int(11) default NULL, + j int(11) default NULL +); +INSERT INTO t1 VALUES (1,2),(2,3),(4,5),(3,5),(1,5),(23,5); +SELECT i, COUNT(DISTINCT(i)) FROM t1 GROUP BY j ORDER BY NULL; +explain SELECT i, COUNT(DISTINCT(i)) FROM t1 GROUP BY j ORDER BY NULL; +DROP TABLE t1; diff --git a/mysql-test/t/innodb.test b/mysql-test/t/innodb.test index 6f7a0e100c3..5add6247eaa 100644 --- a/mysql-test/t/innodb.test +++ b/mysql-test/t/innodb.test @@ -902,6 +902,37 @@ update t1 set a=a+10+b where a=1 order by b; select * from t1 order by a,b; drop table t1; +# +# Test of multi-table-updates (bug #1980). +# + +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; + +# +# test autoincrement with TRUNCATE +# + +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; +drop table t1; + CREATE TABLE t1 (col1 int(1))ENGINE=InnoDB; CREATE TABLE t2 (col1 int(1),stamp TIMESTAMP,INDEX stamp_idx diff --git a/mysql-test/t/insert.test b/mysql-test/t/insert.test index 62d277bfad5..73b5f453bb4 100644 --- a/mysql-test/t/insert.test +++ b/mysql-test/t/insert.test @@ -71,7 +71,7 @@ drop table t1; create table t1 (email varchar(50)); insert into t1 values ('sasha@mysql.com'),('monty@mysql.com'),('foo@hotmail.com'),('foo@aol.com'),('bar@aol.com'); create table t2(id int not null auto_increment primary key, t2 varchar(50), unique(t2)); -insert into t2 (t2) select distinct substring(email, locate('@', email)+1) from t1; +insert delayed into t2 (t2) select distinct substring(email, locate('@', email)+1) from t1; select * from t2; drop table t1,t2; diff --git a/mysql-test/t/join_outer.test b/mysql-test/t/join_outer.test index d048208a8ff..4ffe1c075b6 100644 --- a/mysql-test/t/join_outer.test +++ b/mysql-test/t/join_outer.test @@ -438,3 +438,16 @@ select * from t1 left outer join t2 using (f2) left outer join t3 using (f3); drop table t1,t2,t3; + +create table t1 (a1 int, a2 int); +create table t2 (b1 int not null, b2 int); +create table t3 (c1 int, c2 int); + +insert into t1 values (1,2), (2,2), (3,2); +insert into t2 values (1,3), (2,3); +insert into t3 values (2,4), (3,4); + +select * from t1 left join t2 on b1 = a1 left join t3 on c1 = a1 and b1 is null; +explain select * from t1 left join t2 on b1 = a1 left join t3 on c1 = a1 and b1 is null; + +drop table t1, t2, t3; diff --git a/mysql-test/t/limit.test b/mysql-test/t/limit.test index 88a9831aa68..61c57c9b468 100644 --- a/mysql-test/t/limit.test +++ b/mysql-test/t/limit.test @@ -22,9 +22,7 @@ select * from t1; drop table t1; create table t1 (i int); -insert into t1 (i) values(1); -insert into t1 (i) values(1); -insert into t1 (i) values(1); +insert into t1 (i) values(1),(1),(1); delete from t1 limit 1; update t1 set i=2 limit 1; delete from t1 limit 0; @@ -32,4 +30,22 @@ update t1 set i=3 limit 0; select * from t1; drop table t1; +# LIMIT 0 + select 0 limit 0; + +# +# Test with DELETE, ORDER BY and limit (bug #1024) +# + +CREATE TABLE t1(id int auto_increment primary key, id2 int, index(id2)); +INSERT INTO t1 (id2) values (0),(0),(0); +DELETE FROM t1 WHERE id=1; +INSERT INTO t1 SET id2=0; +SELECT * FROM t1; +DELETE FROM t1 WHERE id2 = 0 ORDER BY id LIMIT 1; +# should have deleted WHERE id=2 +SELECT * FROM t1; +DELETE FROM t1 WHERE id2 = 0 ORDER BY id desc LIMIT 1; +SELECT * FROM t1; +DROP TABLE t1; diff --git a/mysql-test/t/loaddata.test b/mysql-test/t/loaddata.test index 1720ae69bbb..aa0ea0a2f55 100644 --- a/mysql-test/t/loaddata.test +++ b/mysql-test/t/loaddata.test @@ -20,3 +20,14 @@ create table t1 (a text, b text); load data infile '../../std_data/loaddata2.dat' into table t1 fields terminated by ',' enclosed by ''''; select concat('|',a,'|'), concat('|',b,'|') from t1; 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; +select * from t1; +truncate table t1; +load data infile '../../std_data/loaddata4.dat' into table t1 fields terminated by '' enclosed by '' lines terminated by '' ignore 1 lines; + +# The empty line last comes from the end line field in the file +select * from t1; +drop table t1; + diff --git a/mysql-test/t/lowercase_table.test b/mysql-test/t/lowercase_table.test index 28e45bde5c5..601f1734ac6 100644 --- a/mysql-test/t/lowercase_table.test +++ b/mysql-test/t/lowercase_table.test @@ -3,21 +3,24 @@ # --disable_warnings -drop table if exists t1,t2,t3; +drop table if exists t1,t2,t3,t4,T1; --enable_warnings create table T1 (id int primary key, Word varchar(40) not null, Index(Word)); +create table t4 (id int primary key, Word varchar(40) not null); INSERT INTO T1 VALUES (1, 'a'), (2, 'b'), (3, 'c'); +INSERT INTO T4 VALUES(1,'match'); SELECT * FROM t1; SELECT T1.id from T1 LIMIT 1; SELECT T2.id from t1 as T2 LIMIT 1; +SELECT * from t1 left join t4 on (test.t1.id= TEST.t4.id) where TEST.t1.id >= test.t4.id; # This gave an error in 4.0, but it's fixed in 4.1 SELECT T2.id from t1 as t2 LIMIT 1; RENAME TABLE T1 TO T2; ALTER TABLE T2 ADD new_col int not null; ALTER TABLE T2 RENAME T3; show tables like 't_'; -drop table t3; +drop table t3,t4; # # Test alias # diff --git a/mysql-test/t/multi_update.test b/mysql-test/t/multi_update.test index 115007d9e63..4b69b002342 100644 --- a/mysql-test/t/multi_update.test +++ b/mysql-test/t/multi_update.test @@ -277,6 +277,42 @@ select t1.a, t1.b,t2.a, t2.b from t1 left join t2 on t1.a=t2.a where t1.b=1 and drop table t1,t2; # +# Test reuse of same table +# + +create table t1 (a int not null auto_increment primary key, b int not null); +insert into t1 (b) values (1),(2),(3),(4); +update t1, t1 as t2 set t1.b=t2.b+1 where t1.a=t2.a; +select * from t1; +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)); + +insert into t1 values (1, 'a'), (2, 'aa'); +insert into t2 values (1, 'b'), (2, 'bb'); + +select * from t1; +select * from t2; + +update t2 inner join t1 on t1.id1=t2.id2 + set t2.field=t1.field + where 0=1; +update t2, t1 set t2.field=t1.field + where t1.id1=t2.id2 and 0=1; + +delete t1, t2 from t2 inner join t1 on t1.id1=t2.id2 + where 0=1; +delete t1, t2 from t2,t1 + where t1.id1=t2.id2 and 0=1; + +drop table t1,t2; + + +# # Test for bug #1820. # @@ -309,4 +345,4 @@ delete t1,t2 from t1,t2 where t1.b=t2.a and t1.a < 100*1000; select 't2 rows after big delete', count(*) from t2; select 't1 rows after big delete', count(*) from t1; -#drop table t1,t2; +drop table t1,t2; diff --git a/mysql-test/t/mysqldump.test b/mysql-test/t/mysqldump.test index 582fae0da36..c926c8e0b0d 100644 --- a/mysql-test/t/mysqldump.test +++ b/mysql-test/t/mysqldump.test @@ -6,7 +6,29 @@ DROP TABLE IF EXISTS t1, `"t"1`; CREATE TABLE t1(a int); INSERT INTO t1 VALUES (1), (2); ---exec $MYSQL_DUMP --skip-all -X test t1 +--exec $MYSQL_DUMP --skip-comments -X test t1 +DROP TABLE t1; + +# +# Bug #2005 +# + +CREATE TABLE t1 (a decimal(240, 20)); +INSERT INTO t1 VALUES ("1234567890123456789012345678901234567890"), +("0987654321098765432109876543210987654321"); +--exec $MYSQL_DUMP --skip-comments test t1 +DROP TABLE t1; + +# +# Bug #2055 +# + +CREATE TABLE t1 (a double); +INSERT INTO t1 VALUES (-9e999999); +# The following replaces is here because some systems replaces the above +# double with '-inf' and others with MAX_DOUBLE +--replace_result (-1.79769313486232e+308) (RES) (NULL) (RES) +--exec $MYSQL_DUMP --skip-comments test t1 DROP TABLE t1; CREATE TABLE t1(a int, b text, c varchar(3)); diff --git a/mysql-test/t/query_cache.test b/mysql-test/t/query_cache.test index c93d616b379..d7681e9c2ec 100644 --- a/mysql-test/t/query_cache.test +++ b/mysql-test/t/query_cache.test @@ -254,10 +254,14 @@ select * from mysqltest.t1 where i is null; # # drop db # +create table t1(a int); +select * from t1; +show status like "Qcache_queries_in_cache"; select * from mysqltest.t1; show status like "Qcache_queries_in_cache"; drop database mysqltest; show status like "Qcache_queries_in_cache"; +drop table t1; # # Charset convertion (cp1251_koi8 always present) diff --git a/mysql-test/t/rpl_EE_error.test b/mysql-test/t/rpl_EE_error.test new file mode 100644 index 00000000000..de69c4897c3 --- /dev/null +++ b/mysql-test/t/rpl_EE_error.test @@ -0,0 +1,30 @@ +# See if an EE_ error in one event of the master's binlog stops replication +# (it should not: in this configuration the EE_ error is probably not +# critical). Example: you do a DROP TABLE on a table which has no MYI file +# check if START SLAVE, RESET SLAVE, CHANGE MASTER reset Last_slave_error and +# Last_slave_errno in SHOW SLAVE STATUS (1st and 3rd commands did not: bug 986). + +source include/master-slave.inc; + +create table t1 (a int) type=myisam; +flush tables; +system rm ./var/master-data/test/t1.MYI ; +drop table t1; +save_master_pos; +connection slave; +sync_with_master; + +# Now a real error. + +connection master; +create table t1 (a int, unique(a)) type=myisam; +set sql_log_bin=0; +insert into t1 values(2); +set sql_log_bin=1; +save_master_pos; +--error 1062; +insert into t1 values(1),(2); +drop table t1; +save_master_pos; +connection slave; +wait_for_slave_to_stop; diff --git a/mysql-test/t/rpl_multi_update.test b/mysql-test/t/rpl_multi_update.test new file mode 100644 index 00000000000..c44239594dd --- /dev/null +++ b/mysql-test/t/rpl_multi_update.test @@ -0,0 +1,25 @@ +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; + +CREATE TABLE t2 ( + a int unsigned not null auto_increment primary key, + b int unsigned +) TYPE=MyISAM; + +INSERT INTO t1 VALUES (NULL, 0); +INSERT INTO t1 SELECT NULL, 0 FROM t1; + +INSERT INTO t2 VALUES (NULL, 0), (NULL,1); + +SELECT * FROM t1 ORDER BY a; +SELECT * FROM t2 ORDER BY a; + +UPDATE t1, t2 SET t1.b = t2.b WHERE t1.a = t2.a; +save_master_pos; +connection slave; +sync_with_master; diff --git a/mysql-test/t/rpl_rotate_logs.test b/mysql-test/t/rpl_rotate_logs.test index 7560d56af1c..0d447b8d1a3 100644 --- a/mysql-test/t/rpl_rotate_logs.test +++ b/mysql-test/t/rpl_rotate_logs.test @@ -130,6 +130,7 @@ while ($1) dec $1; } enable_query_log; +select count(*) from t3 where n >= 4; create table t4 select * from temp_table; show binary logs; show master status; diff --git a/mysql-test/t/select_found.test b/mysql-test/t/select_found.test index f787cec6533..3cf736cafb0 100644 --- a/mysql-test/t/select_found.test +++ b/mysql-test/t/select_found.test @@ -95,3 +95,17 @@ SELECT FOUND_ROWS(); SELECT SQL_CALC_FOUND_ROWS * FROM t1 ORDER BY numeropost LIMIT 0; SELECT FOUND_ROWS(); drop table t1; + +# +# Test problem with impossible WHERE (Bug #1468) +# + +create table t1 (id int, primary key (id)); +insert into t1 values (1), (2), (3), (4), (5); +select SQL_CALC_FOUND_ROWS * from t1 where id > 3 limit 0, 1; +select FOUND_ROWS(); +select SQL_CALC_FOUND_ROWS * from t1 where id > 3 AND 1=2 limit 0, 1; +select FOUND_ROWS(); +select SQL_CALC_FOUND_ROWS * from t1 where id > 6 limit 0, 1; +select FOUND_ROWS(); +drop table t1; diff --git a/mysql-test/t/symlink.test b/mysql-test/t/symlink.test index 716eed7abc0..c0a9ce1bbcf 100644 --- a/mysql-test/t/symlink.test +++ b/mysql-test/t/symlink.test @@ -93,3 +93,25 @@ select count(*) from mysqltest.t9; --replace_result $MYSQL_TEST_DIR TEST_DIR show create table mysqltest.t9; drop database mysqltest; + +# +# Test changing data dir (Bug #1662) +# + +create table t1 (a int not null) type=myisam; +disable_query_log; +eval alter table t1 data directory="$MYSQL_TEST_DIR/var/tmp"; +enable_query_log; +--replace_result $MYSQL_TEST_DIR TEST_DIR +show create table t1; +alter table t1 add b int; +disable_query_log; +eval alter table t1 data directory="$MYSQL_TEST_DIR/var/log"; +enable_query_log; +--replace_result $MYSQL_TEST_DIR TEST_DIR +show create table t1; +disable_query_log; +eval alter table t1 index directory="$MYSQL_TEST_DIR/var/log"; +enable_query_log; +show create table t1; +drop table t1; diff --git a/mysql-test/t/timezone.test b/mysql-test/t/timezone.test index 14facc0374a..ab732c11a34 100644 --- a/mysql-test/t/timezone.test +++ b/mysql-test/t/timezone.test @@ -1,9 +1,9 @@ # -# Test of timezone handling. This script must be run with TZ=MEST +# Test of timezone handling. This script must be run with TZ=MET --- require r/have_mest_timezone.require +-- require r/have_met_timezone.require disable_query_log; -show variables like "timezone"; +select FROM_UNIXTIME(24*3600); enable_query_log; # Initialization @@ -11,6 +11,17 @@ enable_query_log; DROP TABLE IF EXISTS t1; --enable_warnings +show variables like "timezone"; + +# +# Test unix timestamp +# +select @a:=FROM_UNIXTIME(1); +select unix_timestamp(@a); + +# +# Test of some values, including some with daylight saving time +# CREATE TABLE t1 (ts int); INSERT INTO t1 (ts) VALUES (Unix_timestamp('2002-10-27 01:00')); @@ -26,3 +37,4 @@ INSERT INTO t1 (ts) VALUES (Unix_timestamp('2003-03-30 04:00:01')); SELECT ts,from_unixtime(ts) FROM t1; DROP TABLE t1; + diff --git a/mysql-test/t/truncate.test b/mysql-test/t/truncate.test index dd80baba269..434a1907e42 100644 --- a/mysql-test/t/truncate.test +++ b/mysql-test/t/truncate.test @@ -24,3 +24,15 @@ select * from t1; drop table t1; --error 1146 truncate non_existing_table; + +# +# test autoincrement with TRUNCATE +# + +create table t1 (a integer auto_increment primary key); +insert into t1 (a) values (NULL),(NULL); +truncate table t1; +insert into t1 (a) values (NULL),(NULL); +SELECT * from t1; +drop table t1; + diff --git a/mysql-test/t/type_blob.test b/mysql-test/t/type_blob.test index c9c3284fe0d..70fa2f9b172 100644 --- a/mysql-test/t/type_blob.test +++ b/mysql-test/t/type_blob.test @@ -308,3 +308,52 @@ drop table t1; create table t1 select load_file('../../std_data/words.dat'); show full fields from t1; drop table t1; + +# +# Test blob's with end space (Bug #1651) +# + +create table t1 (id integer primary key auto_increment, txt text not null, unique index txt_index (txt (20))); +insert into t1 (txt) values ('Chevy'), ('Chevy '); +select * from t1 where txt='Chevy'; +select * from t1 where txt='Chevy '; +select * from t1 where txt='Chevy ' or txt='Chevy'; +select * from t1 where txt='Chevy' or txt='Chevy '; +select * from t1 where id='1' or id='2'; +insert into t1 (txt) values('Ford'); +select * from t1 where txt='Chevy' or txt='Chevy ' or txt='Ford'; +select * from t1 where txt='Chevy' or txt='Chevy '; +select * from t1 where txt='Chevy' or txt='Chevy ' or txt=' Chevy'; +select * from t1 where txt in ('Chevy ','Chevy'); +select * from t1 where txt in ('Chevy'); +select * from t1 where txt between 'Chevy' and 'Chevy'; +select * from t1 where txt between 'Chevy' and 'Chevy' or txt='Chevy '; +select * from t1 where txt between 'Chevy' and 'Chevy '; +select * from t1 where txt < 'Chevy '; +select * from t1 where txt <= 'Chevy'; +select * from t1 where txt > 'Chevy'; +select * from t1 where txt >= 'Chevy'; +drop table t1; + +create table t1 (id integer primary key auto_increment, txt text, unique index txt_index (txt (20))); +insert into t1 (txt) values ('Chevy'), ('Chevy '), (NULL); +select * from t1 where txt='Chevy' or txt is NULL; +select * from t1 where txt='Chevy '; +select * from t1 where txt='Chevy ' or txt='Chevy'; +select * from t1 where txt='Chevy' or txt='Chevy '; +select * from t1 where id='1' or id='2'; +insert into t1 (txt) values('Ford'); +select * from t1 where txt='Chevy' or txt='Chevy ' or txt='Ford'; +select * from t1 where txt='Chevy' or txt='Chevy '; +select * from t1 where txt='Chevy' or txt='Chevy ' or txt=' Chevy'; +select * from t1 where txt in ('Chevy ','Chevy'); +select * from t1 where txt in ('Chevy'); +select * from t1 where txt between 'Chevy' and 'Chevy'; +select * from t1 where txt between 'Chevy' and 'Chevy' or txt='Chevy '; +select * from t1 where txt between 'Chevy' and 'Chevy '; +select * from t1 where txt < 'Chevy '; +select * from t1 where txt < 'Chevy ' or txt is NULL; +select * from t1 where txt <= 'Chevy'; +select * from t1 where txt > 'Chevy'; +select * from t1 where txt >= 'Chevy'; +drop table t1; diff --git a/mysql-test/t/type_datetime.test b/mysql-test/t/type_datetime.test index 7cedbc087e4..cec2aa3582b 100644 --- a/mysql-test/t/type_datetime.test +++ b/mysql-test/t/type_datetime.test @@ -7,12 +7,13 @@ drop table if exists t1; --enable_warnings create table t1 (t datetime); -insert into t1 values(101),(691231),(700101),(991231),(10000101),(99991231),(101000000),(691231000000),(700101000000),(991231235959),(10000101000000),(99991231235959); +insert into t1 values(101),(691231),(700101),(991231),(10000101),(99991231),(101000000),(691231000000),(700101000000),(991231235959),(10000101000000),(99991231235959),(20030102030460),(20030102036301),(20030102240401),(20030132030401),(20031302030460); select * from t1; delete from t1 where t > 0; optimize table t1; check table t1; -insert into t1 values("000101"),("691231"),("700101"),("991231"),("00000101"),("00010101"),("99991231"),("00101000000"),("691231000000"),("700101000000"),("991231235959"),("10000101000000"),("99991231235959"); +delete from t1; +insert into t1 values("000101"),("691231"),("700101"),("991231"),("00000101"),("00010101"),("99991231"),("00101000000"),("691231000000"),("700101000000"),("991231235959"),("10000101000000"),("99991231235959"),("20030102030460"),("20030102036301"),("20030102240401"),("20030132030401"),("20031302030460"); select * from t1; drop table t1; diff --git a/mysql-test/t/type_decimal.test b/mysql-test/t/type_decimal.test index 36bd8eb48e4..8250f40fc99 100644 --- a/mysql-test/t/type_decimal.test +++ b/mysql-test/t/type_decimal.test @@ -244,3 +244,9 @@ CREATE TABLE t1 (a_dec DECIMAL(-1,0)); CREATE TABLE t1 (a_dec DECIMAL(-2,1)); --error 1064 CREATE TABLE t1 (a_dec DECIMAL(-1,1)); + +# Zero prepend overflow bug +create table t1(a decimal(10,4)); +insert into t1 values ("+0000100000000"); +select * from t1; +drop table t1; diff --git a/mysql-test/t/type_enum.test b/mysql-test/t/type_enum.test index bcebb1a5b27..f9e5e6470c6 100644 --- a/mysql-test/t/type_enum.test +++ b/mysql-test/t/type_enum.test @@ -26,6 +26,17 @@ show create table t1; drop table t1; # +# Tests of wrong enum values (bug #2023) +# + +create table t1 (a enum ('0','1')); +insert into t1 set a='foobar'; +select * from t1; +update t1 set a = replace(a,'x','y'); +select * from t1; +drop table t1; + +# # Bug #2077 # diff --git a/mysql-test/t/type_timestamp.test b/mysql-test/t/type_timestamp.test index cd76dbe6ab0..fc7cd019171 100644 --- a/mysql-test/t/type_timestamp.test +++ b/mysql-test/t/type_timestamp.test @@ -37,8 +37,11 @@ select date_format(a,"%Y %y"),year(a),year(now()) from t1; drop table t1; create table t1 (ix timestamp); -insert into t1 values (19991101000000),(19990102030405),(19990630232922),(19990601000000),(19990930232922),(19990531232922),(19990501000000),(19991101000000),(19990501000000); -select ix+0 from t1; +insert into t1 values (19991101000000),(19990102030405),(19990630232922),(19990601000000),(19990930232922),(19990531232922),(19990501000000),(19991101000000),(19990501000000),(20030101010160),(20030101016001),(20030101240101),(20030132010101),(20031301010101); +select ix+0 from t1; +delete from t1; +insert into t1 values ("19991101000000"),("19990102030405"),("19990630232922"),("19990601000000"),("20030101010160"),("20030101016001"),("20030101240101"),("20030132010101"),("20031301010101"); +select ix+0 from t1; drop table t1; CREATE TABLE t1 (date date, date_time datetime, time_stamp timestamp); @@ -72,3 +75,9 @@ set new=1; select * from t1; drop table t1; +# +# Bug #1885 +# + +--error 1067 +create table t1 (a timestamp default 1); diff --git a/mysql-test/t/union.test b/mysql-test/t/union.test index 6138bba5a31..fc986b05fa4 100644 --- a/mysql-test/t/union.test +++ b/mysql-test/t/union.test @@ -11,7 +11,7 @@ insert into t1 values(1,'a'),(2,'b'),(3,'c'),(3,'c'); CREATE TABLE t2 (a int not null, b char (10) not null); insert into t2 values (3,'c'),(4,'d'),(5,'f'),(6,'e'); -select a,b from t1 union select a,b from t2; +select a,b from t1 union distinct select a,b from t2; select a,b from t1 union all select a,b from t2; select a,b from t1 union all select a,b from t2 order by b; select a,b from t1 union all select a,b from t2 union select 7,'g'; @@ -247,9 +247,19 @@ SELECT * FROM t1 UNION SELECT * FROM t2 ORDER BY a desc LIMIT 1; --error 1234 (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; +--error 1093 +create table t1 select a from t1 union select a from t2; drop table t1,t2; # +# Problem with alias '*' (BUG #1249) +# + +select length(version()) > 1 as `*` UNION select 2; + + +# # Test for another bug with UNION and LEFT JOIN # CREATE TABLE t1 ( id int(3) unsigned default '0') ENGINE=MyISAM; |