diff options
Diffstat (limited to 'mysql-test/t')
| -rw-r--r-- | mysql-test/t/func_time.test | 18 | ||||
| -rw-r--r-- | mysql-test/t/having.test | 42 |
2 files changed, 59 insertions, 1 deletions
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; |