From 4af3c668c9ffe6ef2e04833ff49414038886ec24 Mon Sep 17 00:00:00 2001 From: unknown Date: Mon, 29 Oct 2001 18:21:33 -0700 Subject: fixed bug with temporary tables replication if one does FLUSH LOGS mysql-test/t/rpl000012.test: test for replication of temp tables with FLUSH LOGS --- mysql-test/t/rpl000012.test | 1 + 1 file changed, 1 insertion(+) (limited to 'mysql-test') diff --git a/mysql-test/t/rpl000012.test b/mysql-test/t/rpl000012.test index 01ff9ec8a37..9f8ba9a4f91 100644 --- a/mysql-test/t/rpl000012.test +++ b/mysql-test/t/rpl000012.test @@ -9,6 +9,7 @@ insert into t2 select * from t1; drop table if exists test.t3; create temporary table test.t3 (n int not null); alter table test.t3 add primary key(n); +flush logs; insert into t3 values (100); insert into t2 select * from t3; drop table if exists test.t3; -- cgit v1.2.1 From 03f6fdd575f71aacd4b6e782c9df4b929f8222b1 Mon Sep 17 00:00:00 2001 From: unknown Date: Wed, 31 Oct 2001 18:27:49 +0200 Subject: Portability fixes Fix for consistent 0000-00-00 date handling Close + open binary logs on flush tables Fix for AVG() in HAVING. BUILD/SETUP.sh: Compile MySQL with -O0 to make debugging easier. Docs/manual.texi: Changelog configure.in: Version change innobase/buf/buf0lru.c: Fix for windows mysql-test/r/func_time.result: Updated tests mysql-test/r/having.result: Updated tests mysql-test/t/func_time.test: Updated tests mysql-test/t/having.test: Updated tests mysys/mf_casecnv.c: Portability fix mysys/mf_qsort.c: Portability fix mysys/my_compress.c: Portability fix mysys/my_tempnam.c: More comments sql/field.cc: Fix for consistent 0000-00-00 date handling sql/item.h: Fix for AVG() in HAVING sql/log.cc: Close + open update logs on flush tables sql/sql_select.cc: More debugging info sql/time.cc: Fix for consistent 0000-00-00 date handling strings/ctype.c: Portability fix strings/strto.c: Portability fix --- mysql-test/r/func_time.result | 18 ++++++++++++++++++ mysql-test/r/having.result | 2 ++ mysql-test/t/func_time.test | 18 ++++++++++++++++++ mysql-test/t/having.test | 42 +++++++++++++++++++++++++++++++++++++++++- 4 files changed, 79 insertions(+), 1 deletion(-) (limited to 'mysql-test') diff --git a/mysql-test/r/func_time.result b/mysql-test/r/func_time.result index 790cce9f1c1..e2e86723e31 100644 --- a/mysql-test/r/func_time.result +++ b/mysql-test/r/func_time.result @@ -200,3 +200,21 @@ month(updated) NULL year(updated) NULL +dayofyear("0000-00-00") dayofyear(d) dayofyear(dt) dayofyear(t) dayofyear(c) +NULL NULL NULL NULL NULL +dayofmonth("0000-00-00") dayofmonth(d) dayofmonth(dt) dayofmonth(t) dayofmonth(c) +0 0 0 0 0 +month("0000-00-00") month(d) month(dt) month(t) month(c) +0 0 0 0 0 +quarter("0000-00-00") quarter(d) quarter(dt) quarter(t) quarter(c) +0 0 0 0 0 +week("0000-00-00") week(d) week(dt) week(t) week(c) +NULL NULL NULL NULL NULL +year("0000-00-00") year(d) year(dt) year(t) year(c) +0 0 0 0 0 +yearweek("0000-00-00") yearweek(d) yearweek(dt) yearweek(t) yearweek(c) +NULL NULL NULL NULL NULL +to_days("0000-00-00") to_days(d) to_days(dt) to_days(t) to_days(c) +NULL NULL NULL NULL NULL +extract(MONTH FROM "0000-00-00") extract(MONTH FROM d) extract(MONTH FROM dt) extract(MONTH FROM t) extract(MONTH FROM c) +0 0 0 0 0 diff --git a/mysql-test/r/having.result b/mysql-test/r/having.result index cbd3fbfff4e..69943c505f0 100644 --- a/mysql-test/r/having.result +++ b/mysql-test/r/having.result @@ -2,3 +2,5 @@ b b b 0 +id start end chr_strand +133197 813898 813898 -1.0000 diff --git a/mysql-test/t/func_time.test b/mysql-test/t/func_time.test index cb021d25ae3..7901387eb24 100644 --- a/mysql-test/t/func_time.test +++ b/mysql-test/t/func_time.test @@ -131,3 +131,21 @@ INSERT INTO t1 VALUES (''); SELECT month(updated) from t1; SELECT year(updated) from t1; drop table t1; + +# +# Check that functions work identically on 0000-00-00 as a constant and on a +# column +# + +create table t1 (d date, dt datetime, t timestamp, c char(10)); +insert into t1 values ("0000-00-00", "0000-00-00", "0000-00-00", "0000-00-00"); +select dayofyear("0000-00-00"),dayofyear(d),dayofyear(dt),dayofyear(t),dayofyear(c) from t1; +select dayofmonth("0000-00-00"),dayofmonth(d),dayofmonth(dt),dayofmonth(t),dayofmonth(c) from t1; +select month("0000-00-00"),month(d),month(dt),month(t),month(c) from t1; +select quarter("0000-00-00"),quarter(d),quarter(dt),quarter(t),quarter(c) from t1; +select week("0000-00-00"),week(d),week(dt),week(t),week(c) from t1; +select year("0000-00-00"),year(d),year(dt),year(t),year(c) from t1; +select yearweek("0000-00-00"),yearweek(d),yearweek(dt),yearweek(t),yearweek(c) from t1; +select to_days("0000-00-00"),to_days(d),to_days(dt),to_days(t),to_days(c) from t1; +select extract(MONTH FROM "0000-00-00"),extract(MONTH FROM d),extract(MONTH FROM dt),extract(MONTH FROM t),extract(MONTH FROM c) from t1; +drop table t1; diff --git a/mysql-test/t/having.test b/mysql-test/t/having.test index a952f5e8d5c..fff5415976c 100644 --- a/mysql-test/t/having.test +++ b/mysql-test/t/having.test @@ -1,10 +1,50 @@ # test of problems with having (Reported by Mark Rogers) # -drop table if exists t1; +drop table if exists t1,t2; create table t1 (a int); select count(a) as b from t1 where a=0 having b > 0; insert into t1 values (null); select count(a) as b from t1 where a=0 having b > 0; select count(a) as b from t1 where a=0 having b >=0; drop table t1; + +# +# Test of problem with HAVING and AVG() +# + +CREATE TABLE t1 ( + raw_id int(10) NOT NULL default '0', + chr_start int(10) NOT NULL default '0', + chr_end int(10) NOT NULL default '0', + raw_start int(10) NOT NULL default '0', + raw_end int(10) NOT NULL default '0', + raw_ori int(2) NOT NULL default '0' +); + +INSERT INTO t1 VALUES (469713,1,164123,1,164123,1),(317330,164124,317193,101,153170,1),(469434,317194,375620,101,58527,1),(591816,375621,484273,1,108653,1),(591807,484274,534671,91,50488,1),(318885,534672,649362,101,114791,1),(318728,649363,775520,102,126259,1),(336829,775521,813997,101,38577,1),(317740,813998,953227,101,139330,1),(1,813998,953227,101,139330,1); + +CREATE TABLE t2 ( + id int(10) unsigned NOT NULL default '0', + contig_id int(10) unsigned NOT NULL default '0', + seq_start int(10) NOT NULL default '0', + seq_end int(10) NOT NULL default '0', + strand tinyint(2) NOT NULL default '0', + KEY id (id) +); +INSERT INTO t2 VALUES (133195,469713,61327,61384,1),(133196,469713,64113,64387,1),(133197,1,1,1,0),(133197,1,1,1,-2); +SELECT e.id, + MIN( IF(sgp.raw_ori=1, + (e.seq_start+sgp.chr_start-sgp.raw_start), + (sgp.chr_start+sgp.raw_end-e.seq_end))) as start, + MAX( IF(sgp.raw_ori=1, + (e.seq_end+sgp.chr_start-sgp.raw_start), + (sgp.chr_start+sgp.raw_end-e.seq_start))) as end, + AVG(IF (sgp.raw_ori=1,e.strand,(-e.strand))) as chr_strand +FROM t1 sgp, + t2 e +WHERE sgp.raw_id=e.contig_id +GROUP BY e.id +HAVING chr_strand= -1 and end >= 0 + AND start <= 999660; +drop table t1,t2; -- cgit v1.2.1 From b2cec26dfb0fa6e2e7240d5dd2044e1192596ef8 Mon Sep 17 00:00:00 2001 From: unknown Date: Sat, 3 Nov 2001 15:18:09 +0200 Subject: Fix that mysqladmin shutdown can be interrupted with ^C Fixed bug with BLOB keys in BDB tables Fixed problem with MERGE tables on OS with 32 bit files Fixed that TIME_TO_SEC() works with negative times Docs/manual.texi: Changelog client/mysqladmin.c: Fix that mysqladmin shutdown can be interrupted with ^C include/mysqld_error.h: Fixed typo mysql-test/r/func_time.result: Added test case for negative times mysql-test/t/func_time.test: Added test case for negative times mysql-test/t/rpl000015-slave.sh: Removed warnings mysql-test/t/rpl000016-slave.sh: Removed warnings sql/field.cc: Fixed bug with BLOB keys in BDB tables sql/field.h: Fixed bug with BLOB keys in BDB tables sql/ha_berkeley.cc: Fixed bug with BLOB keys in BDB tables sql/ha_myisammrg.cc: Fixed problem with MERGE tables on OS with 32 bit files sql/item_timefunc.cc: Fixed that TIME_TO_SEC() works with negative times. sql/share/swedish/errmsg.txt: Merge with 4.0 sql/sql_acl.cc: Fixed typo --- mysql-test/r/func_time.result | 2 ++ mysql-test/t/func_time.test | 1 + mysql-test/t/rpl000015-slave.sh | 2 +- mysql-test/t/rpl000016-slave.sh | 2 +- 4 files changed, 5 insertions(+), 2 deletions(-) (limited to 'mysql-test') diff --git a/mysql-test/r/func_time.result b/mysql-test/r/func_time.result index e2e86723e31..71fc7e4b90b 100644 --- a/mysql-test/r/func_time.result +++ b/mysql-test/r/func_time.result @@ -8,6 +8,8 @@ from_unixtime(unix_timestamp("1994-03-02 10:11:12")) from_unixtime(unix_timestam 1994-03-02 10:11:12 1994-03-02 10:11:12 19940302101112 sec_to_time(9001) sec_to_time(9001)+0 time_to_sec("15:12:22") sec_to_time(time_to_sec("0:30:47")/6.21) 02:30:01 23001 54742 00:04:57 +sec_to_time(time_to_sec('-838:59:59')) +-838:59:59 now()-curdate()*1000000-curtime() 0 strcmp(current_timestamp(),concat(current_date()," ",current_time())) diff --git a/mysql-test/t/func_time.test b/mysql-test/t/func_time.test index 7901387eb24..ffb0f8bbf1e 100644 --- a/mysql-test/t/func_time.test +++ b/mysql-test/t/func_time.test @@ -9,6 +9,7 @@ select now()-now(),weekday(curdate())-weekday(now()),unix_timestamp()-unix_times select from_unixtime(unix_timestamp("1994-03-02 10:11:12")),from_unixtime(unix_timestamp("1994-03-02 10:11:12"),"%Y-%m-%d %h:%i:%s"),from_unixtime(unix_timestamp("1994-03-02 10:11:12"))+0; select sec_to_time(9001),sec_to_time(9001)+0,time_to_sec("15:12:22"), sec_to_time(time_to_sec("0:30:47")/6.21); +select sec_to_time(time_to_sec('-838:59:59')); select now()-curdate()*1000000-curtime(); select strcmp(current_timestamp(),concat(current_date()," ",current_time())); select date_format("1997-01-02 03:04:05", "%M %W %D %Y %y %m %d %h %i %s %w"); diff --git a/mysql-test/t/rpl000015-slave.sh b/mysql-test/t/rpl000015-slave.sh index a421a6f287b..62748605af1 100755 --- a/mysql-test/t/rpl000015-slave.sh +++ b/mysql-test/t/rpl000015-slave.sh @@ -1 +1 @@ -rm $MYSQL_TEST_DIR/var/slave-data/master.info +rm -f $MYSQL_TEST_DIR/var/slave-data/master.info diff --git a/mysql-test/t/rpl000016-slave.sh b/mysql-test/t/rpl000016-slave.sh index a421a6f287b..62748605af1 100755 --- a/mysql-test/t/rpl000016-slave.sh +++ b/mysql-test/t/rpl000016-slave.sh @@ -1 +1 @@ -rm $MYSQL_TEST_DIR/var/slave-data/master.info +rm -f $MYSQL_TEST_DIR/var/slave-data/master.info -- cgit v1.2.1 From 0176dacd54b7eb62ebc4a81909b189517aee2cb4 Mon Sep 17 00:00:00 2001 From: unknown Date: Sat, 3 Nov 2001 21:22:06 +0200 Subject: Fixed problem with t1 LEFT_JOIN t2 ... WHERE t2.date_column IS NULL when date_column is declared as NOT NULL. BUILD/SETUP.sh: Use -O1 to avoid problem with INLINE functions Docs/manual.texi: Changelog mysql-test/r/join.result: Test for bugfix mysql-test/t/join.test: Test for bugfix sql/sql_select.cc: Fixed problem with t1 LEFT_JOIN t2 ... WHERE t2.date_column IS NULL when date_column was declared as NOT NULL. BitKeeper/etc/ignore: Added bdb/include/db_ext.h bdb/include/mutex_ext.h to the ignore list --- mysql-test/r/join.result | 5 +++++ mysql-test/t/join.test | 14 +++++++++++--- 2 files changed, 16 insertions(+), 3 deletions(-) (limited to 'mysql-test') diff --git a/mysql-test/r/join.result b/mysql-test/r/join.result index 7db456a1af5..e48c209fed6 100644 --- a/mysql-test/r/join.result +++ b/mysql-test/r/join.result @@ -22,3 +22,8 @@ a 2 a a b 2 2 3 +d d +2001-08-01 NULL +0000-00-00 NULL +d +0000-00-00 diff --git a/mysql-test/t/join.test b/mysql-test/t/join.test index 224db1dd8f0..530dcd5fca7 100644 --- a/mysql-test/t/join.test +++ b/mysql-test/t/join.test @@ -106,9 +106,17 @@ INSERT INTO t1 VALUES (1,1),(1,2),(1,3),(1,4),(1,5),(1,6),(1,7),(2,3); CREATE TABLE t2 ( a int(11) default NULL ) TYPE=MyISAM; - INSERT INTO t2 VALUES (2),(3); - SELECT t1.a,t2.a,b FROM t1,t2 WHERE t1.a=t2.a AND (t1.a=1 OR t1.a=2) AND b>=1 AND b<=3; - DROP TABLE t1, t2; + +# +# TEST LEFT JOIN with DATE columns +# + +CREATE TABLE t1 (d DATE NOT NULL); +CREATE TABLE t2 (d DATE NOT NULL); +INSERT INTO t1 (d) VALUES ('2001-08-01'),('0000-00-00'); +SELECT * FROM t1 LEFT JOIN t2 USING (d) WHERE t2.d IS NULL; +SELECT * from t1 WHERE t1.d IS NULL; +DROP TABLE t1,t2; -- cgit v1.2.1