~drizzle-trunk/drizzle/development

1 by brian
clean slate
1
--disable_warnings
2
drop table if exists t1;
3
--enable_warnings
4
5
create table t1(n1 int, n2 int, s char(20), vs varchar(20), t text);
6
insert into t1 values (1,11, 'one','eleven', 'eleven'),
7
 (1,11, 'one','eleven', 'eleven'),
8
 (2,11, 'two','eleven', 'eleven'),
9
 (2,12, 'two','twevle', 'twelve'),
10
 (2,13, 'two','thirteen', 'foo'),
11
 (2,13, 'two','thirteen', 'foo'),
12
 (2,13, 'two','thirteen', 'bar'),
13
 (NULL,13, 'two','thirteen', 'bar'),
14
 (2,NULL, 'two','thirteen', 'bar'),
15
 (2,13, NULL,'thirteen', 'bar'),
16
 (2,13, 'two',NULL, 'bar'),
17
 (2,13, 'two','thirteen', NULL);
18
19
select distinct n1 from t1;
20
select count(distinct n1) from t1;
21
22
select distinct n2 from t1;
23
select count(distinct n2) from t1;
24
25
select distinct s from t1;
26
select count(distinct s) from t1;
27
28
select distinct vs from t1;
29
select count(distinct vs) from t1;
30
31
select distinct t from t1;
32
select count(distinct t) from t1;
33
34
select distinct n1,n2 from t1;
35
select count(distinct n1,n2) from t1;
36
37
select distinct n1,s from t1;
38
select count(distinct n1,s) from t1;
39
40
select distinct s,n1,vs from t1;
41
select count(distinct s,n1,vs) from t1;
42
43
select distinct s,t from t1;
44
select count(distinct s,t) from t1;
45
46
select count(distinct n1), count(distinct n2) from t1;
47
48
select count(distinct n2), n1 from t1 group by n1;
49
drop table t1;
50
51
# test the conversion from tree to MyISAM
52
create table t1 (n int default NULL);
53
let $1=5000;
54
disable_query_log;
55
while ($1)
56
{
57
 eval insert into t1 values($1);
58
 dec $1;
59
}
60
enable_query_log;
61
62
flush status;
63
select count(distinct n) from t1;
64
show status like 'Created_tmp_disk_tables';
65
drop table t1;
66
67
# Test use of MyISAM tmp tables
68
create table t1 (s text);
69
let $1=5000;
70
disable_query_log;
71
while ($1)
72
{
73
 eval insert into t1 values('$1');
74
 dec $1;
75
}
76
enable_query_log;
77
flush status;
78
select count(distinct s) from t1;
79
show status like 'Created_tmp_disk_tables';
80
drop table t1;
81
82
# End of 4.1 tests