diff options
author | unknown <monty@mishka.local> | 2004-04-26 15:53:31 +0300 |
---|---|---|
committer | unknown <monty@mishka.local> | 2004-04-26 15:53:31 +0300 |
commit | 1065f2bbd66ac4b1161f5c188171a54cbad5b422 (patch) | |
tree | 25e3315af05fa92d20d2ad1d812882957c400337 /mysql-test/t/func_group.test | |
parent | 0ba6cb48d84f1ff951d09871a96be6cdef3f2c3c (diff) | |
parent | 6366a9090c7fc24f0e13b5b9d73d6777dcda9d9e (diff) | |
download | mariadb-git-1065f2bbd66ac4b1161f5c188171a54cbad5b422.tar.gz |
Merge with 4.0
innobase/dict/dict0boot.c:
Auto merged
innobase/dict/dict0load.c:
Auto merged
innobase/dict/dict0mem.c:
Auto merged
innobase/fut/fut0lst.c:
Auto merged
innobase/include/buf0lru.h:
Auto merged
innobase/include/dict0mem.h:
Auto merged
innobase/include/fsp0fsp.h:
Auto merged
innobase/include/ha0ha.h:
Auto merged
innobase/include/ibuf0ibuf.h:
Auto merged
innobase/include/lock0lock.h:
Auto merged
innobase/include/log0log.h:
Auto merged
innobase/include/mem0pool.h:
Auto merged
innobase/include/mtr0mtr.h:
Auto merged
innobase/include/os0file.h:
Auto merged
innobase/include/rem0rec.h:
Auto merged
innobase/include/rem0rec.ic:
Auto merged
innobase/include/srv0srv.h:
Auto merged
innobase/include/sync0sync.h:
Auto merged
innobase/include/trx0sys.h:
Auto merged
innobase/include/ut0byte.h:
Auto merged
innobase/include/ut0ut.h:
Auto merged
innobase/mem/mem0pool.c:
Auto merged
innobase/mtr/mtr0mtr.c:
Auto merged
innobase/os/os0proc.c:
Auto merged
innobase/pars/lexyy.c:
Auto merged
innobase/pars/pars0opt.c:
Auto merged
innobase/row/row0ins.c:
Auto merged
innobase/row/row0purge.c:
Auto merged
innobase/row/row0uins.c:
Auto merged
innobase/row/row0umod.c:
Auto merged
innobase/row/row0undo.c:
Auto merged
innobase/row/row0upd.c:
Auto merged
innobase/trx/trx0purge.c:
Auto merged
innobase/trx/trx0roll.c:
Auto merged
innobase/trx/trx0sys.c:
Auto merged
innobase/trx/trx0undo.c:
Auto merged
innobase/ut/ut0byte.c:
Auto merged
pstack/bucomm.h:
Auto merged
pstack/budbg.h:
Auto merged
sql/item_sum.h:
Auto merged
sql/slave.cc:
Auto merged
sql/sql_db.cc:
Auto merged
support-files/mysql.spec.sh:
Auto merged
tests/insert_test.c:
Auto merged
mysql-test/t/func_group.test:
Merge with 4.0
Put 4.1 tests lasts
sql/ha_innodb.cc:
Merge with 4.0
Added checking of results from my_malloc()
BitKeeper/etc/logging_ok:
Logging to logging@openlogging.org accepted
Diffstat (limited to 'mysql-test/t/func_group.test')
-rw-r--r-- | mysql-test/t/func_group.test | 70 |
1 files changed, 39 insertions, 31 deletions
diff --git a/mysql-test/t/func_group.test b/mysql-test/t/func_group.test index 2bd4838f934..76d572f5de3 100644 --- a/mysql-test/t/func_group.test +++ b/mysql-test/t/func_group.test @@ -135,6 +135,45 @@ select max(t1.a2),max(t2.a1) from t1 left outer join t2 on t1.a1=10; drop table t1,t2; # +# Test of group function and NULL values +# + +CREATE TABLE t1 (a int, b int); +select count(b), sum(b), avg(b), std(b), min(b), max(b), bit_and(b), bit_or(b) from t1; +select a,count(b), sum(b), avg(b), std(b), min(b), max(b), bit_and(b), bit_or(b) from t1 group by a; +insert into t1 values (1,null); +select a,count(b), sum(b), avg(b), std(b), min(b), max(b), bit_and(b), bit_or(b) from t1 group by a; +insert into t1 values (1,null); +insert into t1 values (2,null); +select a,count(b), sum(b), avg(b), std(b), min(b), max(b), bit_and(b), bit_or(b) from t1 group by a; +select SQL_BIG_RESULT a,count(b), sum(b), avg(b), std(b), min(b), max(b), bit_and(b), bit_or(b) from t1 group by a; +insert into t1 values (2,1); +select a,count(b), sum(b), avg(b), std(b), min(b), max(b), bit_and(b), bit_or(b) from t1 group by a; +select SQL_BIG_RESULT a,count(b), sum(b), avg(b), std(b), min(b), max(b), bit_and(b), bit_or(b) from t1 group by a; +insert into t1 values (3,1); +select a,count(b), sum(b), avg(b), std(b), min(b), max(b), bit_and(b), bit_or(b) from t1 group by a; +select SQL_BIG_RESULT a,count(b), sum(b), avg(b), std(b), min(b), max(b), bit_and(b), bit_or(b), bit_xor(b) from t1 group by a; +explain extended select SQL_BIG_RESULT a,count(b), sum(b), avg(b), std(b), min(b), max(b), bit_and(b), bit_or(b), bit_xor(b) from t1 group by a; +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; + +# +# Bug #3376: avg() and an empty table +# + +create table t1 (a int); +select avg(2) from t1; +drop table t1; + +# # Tests to check MIN/MAX query optimization # @@ -327,30 +366,7 @@ explain select concat(min(t1.a1),min(t2.a4)) from t1, t2 where t2.a4 <> 'AME'; drop table t1, t2; -# -# Test of group function and NULL values -# - -CREATE TABLE t1 (a int, b int); -select count(b), sum(b), avg(b), std(b), min(b), max(b), bit_and(b), bit_or(b) from t1; -select a,count(b), sum(b), avg(b), std(b), min(b), max(b), bit_and(b), bit_or(b) from t1 group by a; -insert into t1 values (1,null); -select a,count(b), sum(b), avg(b), std(b), min(b), max(b), bit_and(b), bit_or(b) from t1 group by a; -insert into t1 values (1,null); -insert into t1 values (2,null); -select a,count(b), sum(b), avg(b), std(b), min(b), max(b), bit_and(b), bit_or(b) from t1 group by a; -select SQL_BIG_RESULT a,count(b), sum(b), avg(b), std(b), min(b), max(b), bit_and(b), bit_or(b) from t1 group by a; -insert into t1 values (2,1); -select a,count(b), sum(b), avg(b), std(b), min(b), max(b), bit_and(b), bit_or(b) from t1 group by a; -select SQL_BIG_RESULT a,count(b), sum(b), avg(b), std(b), min(b), max(b), bit_and(b), bit_or(b) from t1 group by a; -insert into t1 values (3,1); -select a,count(b), sum(b), avg(b), std(b), min(b), max(b), bit_and(b), bit_or(b) from t1 group by a; -select SQL_BIG_RESULT a,count(b), sum(b), avg(b), std(b), min(b), max(b), bit_and(b), bit_or(b), bit_xor(b) from t1 group by a; -explain extended select SQL_BIG_RESULT a,count(b), sum(b), avg(b), std(b), min(b), max(b), bit_and(b), bit_or(b), bit_xor(b) from t1 group by a; -drop table t1; ---disable_warnings create table t1 (USR_ID integer not null, MAX_REQ integer not null, constraint PK_SEA_USER primary key (USR_ID)) engine=InnoDB; ---enable_warnings insert into t1 values (1, 3); select count(*) + MAX_REQ - MAX_REQ + MAX_REQ - MAX_REQ + MAX_REQ - MAX_REQ + MAX_REQ - MAX_REQ + MAX_REQ - MAX_REQ from t1 group by MAX_REQ; select Case When Count(*) < MAX_REQ Then 1 Else 0 End from t1 where t1.USR_ID = 1 group by MAX_REQ; @@ -369,11 +385,3 @@ 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; |