~drizzle-trunk/drizzle/development

1 by brian
clean slate
1
# include/rowid_order.inc
2
#
3
# Test for rowid ordering (and comparison) functions.
4
# do index_merge select for tables with PK of various types.
5
#
6
# The variable
7
#     $engine_type       -- storage engine to be tested
8
# has to be set before sourcing this script.
9
#
10
# Note: The comments/expections refer to InnoDB.
11
#       They might be not valid for other storage engines.
12
#
13
# Last update:
14
# 2006-08-02 ML test refactored
15
#               old name was t/rowid_order.test
16
#               main code went into include/rowid_order.inc
17
#
18
19
eval SET SESSION STORAGE_ENGINE = $engine_type;
20
21
--disable_warnings
22
drop table if exists t1, t2, t3,t4;
23
--enable_warnings
24
25
# Signed number as rowid
26
create table t1 (
27
  pk1 int not NULL,
520.1.10 by Brian Aker
Adding back more tests.
28
  key1 int,
29
  key2 int,
1 by brian
clean slate
30
  PRIMARY KEY  (pk1),
31
  KEY key1 (key1),
32
  KEY key2 (key2)
33
);
34
insert into t1 values (-5, 1, 1),
35
  (-100, 1, 1),
36
  (3, 1, 1),
37
  (0, 1, 1),
38
  (10, 1, 1);
39
explain select * from t1 force index(key1, key2) where key1 < 3 or key2 < 3;
40
select * from t1 force index(key1, key2) where key1 < 3 or key2 < 3;
41
drop table t1;
42
43
# Unsigned numbers as rowids
44
create table t1 (
413.2.2 by Brian Aker
Removed UNSIGNED from parser.
45
  pk1 int not NULL,
520.1.10 by Brian Aker
Adding back more tests.
46
  key1 int,
47
  key2 int,
1 by brian
clean slate
48
  PRIMARY KEY  (pk1),
49
  KEY key1 (key1),
50
  KEY key2 (key2)
51
);
52
insert into t1 values (0, 1, 1),
520.1.10 by Brian Aker
Adding back more tests.
53
  (0x00FFFFFF, 1, 1),
54
  (0x00FFFFFE, 1, 1),
1 by brian
clean slate
55
  (1, 1, 1),
56
  (2, 1, 1);
57
select * from t1 force index(key1, key2) where key1 < 3 or key2 < 3;
58
drop table t1;
59
60
# Case-insensitive char(N)
61
create table t1 (
62
  pk1 char(4) not NULL,
520.1.10 by Brian Aker
Adding back more tests.
63
  key1 int,
64
  key2 int,
1 by brian
clean slate
65
  PRIMARY KEY  (pk1),
66
  KEY key1 (key1),
67
  KEY key2 (key2)
1245.3.4 by Stewart Smith
make the equals of KEY=VALUE required for CREATE TABLE options
68
) collate=utf8_general_ci;
1 by brian
clean slate
69
insert into t1 values ('a1', 1, 1),
70
  ('b2', 1, 1),
71
  ('A3', 1, 1),
72
  ('B4', 1, 1);
73
select * from t1 force index(key1, key2) where key1 < 3 or key2 < 3;
74
drop table t1;
75
76
# Multi-part PK
77
create table t1 (
78
  pk1 int not NULL,
520.1.10 by Brian Aker
Adding back more tests.
79
  pk2 char(4) not NULL,
80
  pk3 char(4) not NULL collate utf8_bin,
81
  key1 int,
82
  key2 int,
1 by brian
clean slate
83
  PRIMARY KEY  (pk1,pk2,pk3),
84
  KEY key1 (key1),
85
  KEY key2 (key2)
86
);
87
insert into t1 values
88
  (1, 'u', 'u',        1, 1),
520.1.10 by Brian Aker
Adding back more tests.
89
  (1, 'u', 'b', 1, 1),
1 by brian
clean slate
90
  (1, 'u', 'x',        1, 1);
91
insert ignore into t1 select pk1, char(0xEC), pk3, key1, key2  from t1;
92
insert ignore into t1 select pk1, 'x', pk3, key1, key2  from t1 where pk2='u';
93
insert ignore into t1 select 2, pk2, pk3, key1, key2  from t1;
94
select * from t1;
95
select * from t1 force index(key1, key2) where key1 < 3 or key2 < 3;
96
97
# Hidden PK
98
alter table t1 drop primary key;
99
select * from t1;
100
select * from t1 force index(key1, key2) where key1 < 3 or key2 < 3;
101
drop table t1;
102
103
# Variable-length PK
104
# this is also test for Bug#2688
105
create table t1  (
106
  pk1 varchar(8) NOT NULL default '',
107
  pk2 varchar(4) NOT NULL default '',
520.1.10 by Brian Aker
Adding back more tests.
108
  key1 int,
109
  key2 int,
1 by brian
clean slate
110
  primary key(pk1, pk2),
111
  KEY key1 (key1),
112
  KEY key2 (key2)
113
);
114
insert into t1 values ('','empt',2,2),
115
  ('a','a--a',2,2),
116
  ('bb','b--b',2,2),
117
  ('ccc','c--c',2,2),
118
  ('dddd','d--d',2,2);
119
select * from t1 force index(key1, key2) where key1 < 3 or key2 < 3;
120
121
drop table t1;