~ken-vandine/+junk/mysql-3.23.58

SELECT * FROM t1 WHERE area='Vancouver' and transityes='y' and schoolsyes='y' and ( ((type='1 Bedroom' or type='Studio/Bach') and (price<=500)) or ((type='2 Bedroom') and (price<=550)) or ((type='Shared/Roomate') and (price<=300)) or ((type='Room and Board') and (price<=500)) ) and price <= 400;

drop table t1;

# problem med primary key

CREATE TABLE t1 (program enum('signup','unique','sliding') not null, type enum('basic','sliding','signup'), sites set('mt'), PRIMARY KEY (program));

# The following should give an error for wrong primary key

!$1171 ALTER TABLE t1 modify program enum('signup','unique','sliding');

drop table t1;

# Test of compressed decimal index.

CREATE TABLE t1 (

name varchar(50) DEFAULT '' NOT NULL,

author varchar(50) DEFAULT '' NOT NULL,

category decimal(10,0) DEFAULT '0' NOT NULL,

email varchar(50),

password varchar(50),

proxy varchar(50),

bitmap varchar(20),

msg varchar(255),

urlscol varchar(127),

urlhttp varchar(127),

timeout decimal(10,0),

nbcnx decimal(10,0),

creation decimal(10,0),

livinguntil decimal(10,0),

lang decimal(10,0),

type decimal(10,0),

subcat decimal(10,0),

subtype decimal(10,0),

reg char(1),

scs varchar(255),

capacity decimal(10,0),

userISP varchar(50),

CCident varchar(50) DEFAULT '' NOT NULL,

PRIMARY KEY (name,author,category)

);

INSERT INTO t1 VALUES

('patnom','patauteur',0,'p.favre@cryo-networks.fr',NULL,NULL,'#p2sndnq6ae5g1u6t','essai\nsalut','scol://195.242.78.119:patauteur.patnom',NULL,NULL,NULL,950036174,-882087474,NULL,3,0,3,'1','Pub/patnom/futur_divers.scs',NULL,'pat','CC1');

INSERT INTO t1 VALUES

('LeNomDeMonSite','Marc',0,'m.barilley@cryo-networks.fr',NULL,NULL,NULL,NULL,'scol://195.242.78.119:Marc.LeNomDeMonSite',NULL,NULL,NULL,950560434,-881563214,NULL,3,0,3,'1','Pub/LeNomDeMonSite/domus_hibere.scs',NULL,'Marq','CC1');

select * from t1 where name='patnom' and author='patauteur' and category=0;

100

drop table t1;

101

102

103

# Problem with search on partial index

104

105

106

create table t1

107

(

108

name_id int not null auto_increment,

109

name blob,

110

INDEX name_idx (name(5)),

111

primary key (name_id)

112

);

113

114

INSERT t1 VALUES(NULL,'/');

115

INSERT t1 VALUES(NULL,'[T,U]_axpby');

116

SELECT * FROM t1 WHERE name='[T,U]_axpy';

117

SELECT * FROM t1 WHERE name='[T,U]_axpby';

118

create table t2

119

(

120

name_id int not null auto_increment,

121

name char(255) binary,

122

INDEX name_idx (name(5)),

123

primary key (name_id)

124

);

125

INSERT t2 select * from t1;

126

SELECT * FROM t2 WHERE name='[T,U]_axpy';

127

SELECT * FROM t2 WHERE name='[T,U]_axpby';

128

drop table t1,t2;

129

130

131

# Test bug with long primary key

132

133

134

create table t1

135

(

136

SEQNO numeric(12 ) not null,

137

MOTYPEID numeric(12 ) not null,

138

MOINSTANCEID numeric(12 ) not null,

139

ATTRID numeric(12 ) not null,

140

VALUE varchar(120) not null,

141

primary key (SEQNO, MOTYPEID, MOINSTANCEID, ATTRID, VALUE )

142

);

143

INSERT INTO t1 VALUES (1, 1, 1, 1, 'a');

144

INSERT INTO t1 VALUES (1, 1, 1, 1, 'b');

145

!$1062 INSERT INTO t1 VALUES (1, 1, 1, 1, 'a');

146

drop table t1;

147

148

149

# Test with blob + tinyint key

150

# (Failed for Greg Valure)

151

152

153

CREATE TABLE t1 (

154

a tinytext NOT NULL,

155

b tinyint(3) unsigned NOT NULL default '0',

156

PRIMARY KEY (a(32),b)

157

) TYPE=MyISAM;

158

INSERT INTO t1 VALUES ('a',1),('a',2);

159

SELECT * FROM t1 WHERE a='a' AND b=2;

160

SELECT * FROM t1 WHERE a='a' AND b in (2);

161

SELECT * FROM t1 WHERE a='a' AND b in (1,2);

162

drop table t1;

Older »