summaryrefslogtreecommitdiff
path: root/mysql-test
diff options
context:
space:
mode:
Diffstat (limited to 'mysql-test')
-rw-r--r--mysql-test/r/func_group.result23
-rw-r--r--mysql-test/r/group_by.result31
-rw-r--r--mysql-test/r/query_cache.result11
-rw-r--r--mysql-test/r/type_timestamp.result2
-rw-r--r--mysql-test/t/func_group.test8
-rw-r--r--mysql-test/t/group_by.test24
-rw-r--r--mysql-test/t/query_cache.test4
-rw-r--r--mysql-test/t/type_timestamp.test6
8 files changed, 100 insertions, 9 deletions
diff --git a/mysql-test/r/func_group.result b/mysql-test/r/func_group.result
index f6a30813bb0..99c42bb83cf 100644
--- a/mysql-test/r/func_group.result
+++ b/mysql-test/r/func_group.result
@@ -207,13 +207,13 @@ drop table t1,t2;
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;
count(b) sum(b) avg(b) std(b) min(b) max(b) bit_and(b) bit_or(b)
-0 NULL NULL NULL NULL NULL -1 0
+0 NULL NULL NULL NULL NULL 18446744073709551615 0
select a,count(b), sum(b), avg(b), std(b), min(b), max(b), bit_and(b), bit_or(b) from t1 group by a;
a count(b) sum(b) avg(b) std(b) min(b) max(b) bit_and(b) bit_or(b)
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;
a count(b) sum(b) avg(b) std(b) min(b) max(b) bit_and(b) bit_or(b)
-1 0 NULL NULL NULL NULL NULL -1 0
+1 0 NULL NULL NULL NULL NULL 18446744073709551615 0
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;
@@ -222,8 +222,8 @@ a count(b) sum(b) avg(b) std(b) min(b) max(b) bit_and(b) bit_or(b)
2 0 NULL NULL NULL NULL NULL 18446744073709551615 0
select SQL_BIG_RESULT a,count(b), sum(b), avg(b), std(b), min(b), max(b), bit_and(b), bit_or(b) from t1 group by a;
a count(b) sum(b) avg(b) std(b) min(b) max(b) bit_and(b) bit_or(b)
-1 0 NULL NULL NULL NULL NULL -1 0
-2 0 NULL NULL NULL NULL NULL -1 0
+1 0 NULL NULL NULL NULL NULL 18446744073709551615 0
+2 0 NULL NULL NULL NULL NULL 18446744073709551615 0
insert into t1 values (2,1);
select a,count(b), sum(b), avg(b), std(b), min(b), max(b), bit_and(b), bit_or(b) from t1 group by a;
a count(b) sum(b) avg(b) std(b) min(b) max(b) bit_and(b) bit_or(b)
@@ -231,7 +231,7 @@ a count(b) sum(b) avg(b) std(b) min(b) max(b) bit_and(b) bit_or(b)
2 1 1 1.0000 0.0000 1 1 1 1
select SQL_BIG_RESULT a,count(b), sum(b), avg(b), std(b), min(b), max(b), bit_and(b), bit_or(b) from t1 group by a;
a count(b) sum(b) avg(b) std(b) min(b) max(b) bit_and(b) bit_or(b)
-1 0 NULL NULL NULL NULL NULL -1 0
+1 0 NULL NULL NULL NULL NULL 18446744073709551615 0
2 1 1 1.0000 0.0000 1 1 1 1
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;
@@ -241,7 +241,18 @@ a count(b) sum(b) avg(b) std(b) min(b) max(b) bit_and(b) bit_or(b)
3 1 1 1.0000 0.0000 1 1 1 1
select SQL_BIG_RESULT a,count(b), sum(b), avg(b), std(b), min(b), max(b), bit_and(b), bit_or(b) from t1 group by a;
a count(b) sum(b) avg(b) std(b) min(b) max(b) bit_and(b) bit_or(b)
-1 0 NULL NULL NULL NULL NULL -1 0
+1 0 NULL NULL NULL NULL NULL 18446744073709551615 0
2 1 1 1.0000 0.0000 1 1 1 1
3 1 1 1.0000 0.0000 1 1 1 1
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/group_by.result b/mysql-test/r/group_by.result
index f89cb8a6792..dba95614052 100644
--- a/mysql-test/r/group_by.result
+++ b/mysql-test/r/group_by.result
@@ -287,7 +287,7 @@ table type possible_keys key key_len ref rows Extra
t1 ALL NULL NULL NULL NULL 8 Using filesort
explain select sql_big_result spid,sum(userid) from t1 group by spid desc order by null;
table type possible_keys key key_len ref rows Extra
-t1 ALL NULL NULL NULL NULL 8
+t1 ALL NULL NULL NULL NULL 8 Using filesort
select sql_big_result spid,sum(userid) from t1 group by spid desc;
spid sum(userid)
7 3
@@ -597,3 +597,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/query_cache.result b/mysql-test/r/query_cache.result
index 83d2e439fbc..c7554211a1a 100644
--- a/mysql-test/r/query_cache.result
+++ b/mysql-test/r/query_cache.result
@@ -365,16 +365,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);
insert into t1 values("á");
select * from t1;
diff --git a/mysql-test/r/type_timestamp.result b/mysql-test/r/type_timestamp.result
index 9403b73d459..b513f958787 100644
--- a/mysql-test/r/type_timestamp.result
+++ b/mysql-test/r/type_timestamp.result
@@ -122,3 +122,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/t/func_group.test b/mysql-test/t/func_group.test
index 40d829d3e70..d03e4b9b629 100644
--- a/mysql-test/t/func_group.test
+++ b/mysql-test/t/func_group.test
@@ -147,3 +147,11 @@ 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) 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;
diff --git a/mysql-test/t/group_by.test b/mysql-test/t/group_by.test
index df99bc2a9dc..58bb4b3e268 100644
--- a/mysql-test/t/group_by.test
+++ b/mysql-test/t/group_by.test
@@ -423,3 +423,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/query_cache.test b/mysql-test/t/query_cache.test
index 2ab5504f84b..ad0dc80e2f7 100644
--- a/mysql-test/t/query_cache.test
+++ b/mysql-test/t/query_cache.test
@@ -251,10 +251,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/type_timestamp.test b/mysql-test/t/type_timestamp.test
index 0c88f1b8025..17f7b7c487f 100644
--- a/mysql-test/t/type_timestamp.test
+++ b/mysql-test/t/type_timestamp.test
@@ -72,3 +72,9 @@ set new=1;
select * from t1;
drop table t1;
+#
+# Bug #1885
+#
+
+--error 1067
+create table t1 (a timestamp default 1);
View Lorry Controller Status