~linuxjedi/drizzle/trunk-bug-667053

insert into t1 values (0,-4294967295,-4294967295,-4294967295,-4294967295,-4294967295,-4294967295,-4294967295,-4294967295,-4294967295,-4294967295,-4294967295,-4294967295,-4294967295,NULL,0,0,0,-4294967295,-4294967295,-4294967295,'-4294967295',0,"one,two,tree");

105

Warnings:

106

Warning 1265 Data truncated for column 'string' at row 1

107

Warning 1264 Out of range value for column 'tiny' at row 1

108

Warning 1264 Out of range value for column 'short' at row 1

109

Warning 1264 Out of range value for column 'medium' at row 1

110

Warning 1264 Out of range value for column 'long_int' at row 1

111

Warning 1264 Out of range value for column 'utiny' at row 1

112

Warning 1264 Out of range value for column 'ushort' at row 1

113

Warning 1264 Out of range value for column 'umedium' at row 1

114

Warning 1264 Out of range value for column 'ulong' at row 1

115

Warning 1264 Out of range value for column 'ulonglong' at row 1

116

Warning 1265 Data truncated for column 'options' at row 1

117

insert into t1 values (0,4294967295,4294967295,4294967295,4294967295,4294967295,4294967295,4294967295,4294967295,4294967295,4294967295,4294967295,4294967295,4294967295,NULL,0,0,0,4294967295,4294967295,4294967295,'4294967295',0,0);

118

Warnings:

119

Warning 1264 Out of range value for column 'tiny' at row 1

120

Warning 1264 Out of range value for column 'short' at row 1

121

Warning 1264 Out of range value for column 'medium' at row 1

122

Warning 1264 Out of range value for column 'long_int' at row 1

123

Warning 1264 Out of range value for column 'utiny' at row 1

124

Warning 1264 Out of range value for column 'ushort' at row 1

125

Warning 1264 Out of range value for column 'umedium' at row 1

126

Warning 1265 Data truncated for column 'options' at row 1

127

insert into t1 (tiny) values (1);

128

select auto,string,tiny,short,medium,long_int,longlong,real_float,real_double,utiny,ushort,umedium,ulong,ulonglong,mod(floor(time_stamp/1000000),1000000)-mod(curdate(),1000000),date_field,time_field,date_time,blob_col,tinyblob_col,mediumblob_col,longblob_col from t1;

129

auto string tiny short medium long_int longlong real_float real_double utiny ushort umedium ulong ulonglong mod(floor(time_stamp/1000000),1000000)-mod(curdate(),1000000) date_field time_field date_time blob_col tinyblob_col mediumblob_col longblob_col

130

10 1 1 1 1 1 1 1.0 1.0000 1 00001 1 1 1 0 0000-00-00 00:00:00 0000-00-00 00:00:00 1 1 1 1

131

11 2 2 2 2 2 2 2.0 2.0000 2 00002 2 2 2 0 NULL NULL NULL NULL NULL 2 2

132

12 0.33333333 3 3 3 3 3 3.0 3.0000 3 00003 3 3 3 0 1997-03-03 10:10:10 1997-03-03 10:10:10 3

133

13 -1 -1 -1 -1 -1 -1 -1.0 -1.0000 0 00000 0 0 0 0 1997-08-07 08:07:06 1997-04-03 09:08:07 -1 -1 -1 -1

134

14 -429496729 -128 -32768 -8388608 -2147483648 -4294967295 -4294967296.0 -4294967295.0000 0 00000 0 0 0 0 0000-00-00 00:00:00 0000-00-00 00:00:00 -4294967295 -4294967295 -4294967295 -4294967295

135

15 4294967295 127 32767 8388607 2147483647 4294967295 4294967296.0 4294967295.0000 255 65535 16777215 4294967295 4294967295 0 0000-00-00 00:00:00 0000-00-00 00:00:00 4294967295 4294967295 4294967295 4294967295

136

16 hello 1 1 0 0 0 0.0 NULL 0 00000 0 0 0 0 NULL NULL NULL NULL NULL

137

ALTER TABLE t1

138

add new_field char(10) default "new" not null,

139

change blob_col new_blob_col varchar(20),

140

change date_field date_field char(10),

141

alter column string set default "newdefault",

142

alter short drop default,

143

DROP INDEX utiny,

144

DROP INDEX ushort,

145

DROP PRIMARY KEY,

146

DROP FOREIGN KEY any_name,

147

ADD INDEX (auto);

148

LOCK TABLES t1 WRITE;

149

ALTER TABLE t1

150

RENAME as t2,

151

DROP longblob_col;

152

UNLOCK TABLES;

153

ALTER TABLE t2 rename as t3;

154

LOCK TABLES t3 WRITE ;

155

ALTER TABLE t3 rename as t1;

156

UNLOCK TABLES;

157

select auto,new_field,new_blob_col,date_field from t1 ;

158

auto new_field new_blob_col date_field

159

10 new 1 0000-00-00

160

11 new NULL NULL

161

12 new 1997-03-03

162

13 new -1 1997-08-07

163

14 new -4294967295 0000-00-00

164

15 new 4294967295 0000-00-00

165

16 new NULL NULL

166

CREATE TABLE t2 (

167

auto int(5) unsigned NOT NULL auto_increment,

168

string char(20),

169

mediumblob_col mediumblob not null,

170

new_field char(2),

171

PRIMARY KEY (auto)

172

);

173

INSERT INTO t2 (string,mediumblob_col,new_field) SELECT string,mediumblob_col,new_field from t1 where auto > 10;

174

Warnings:

175

Warning 1265 Data truncated for column 'new_field' at row 2

176

Warning 1265 Data truncated for column 'new_field' at row 3

177

Warning 1265 Data truncated for column 'new_field' at row 4

178

Warning 1265 Data truncated for column 'new_field' at row 5

179

Warning 1265 Data truncated for column 'new_field' at row 6

180

Warning 1265 Data truncated for column 'new_field' at row 7

181

select * from t2;

182

auto string mediumblob_col new_field

183

1 2 2 ne

184

2 0.33333333 ne

185

3 -1 -1 ne

186

4 -429496729 -4294967295 ne

187

5 4294967295 4294967295 ne

188

6 hello ne

189

select distinct flags from t1;

190

flags

191

192

one,two,tree

193

one

194

one,two

195

select flags from t1 where find_in_set("two",flags)>0;

196

flags

197

one,two,tree

198

one,two,tree

199

one,two

200

one,two

201

select flags from t1 where find_in_set("unknown",flags)>0;

202

flags

203

select options,flags from t1 where options="ONE" and flags="ONE";

204

options flags

205

one one

206

select options,flags from t1 where options="one" and flags="one";

207

options flags

208

one one

209

drop table t2;

210

create table t2 select * from t1;

211

update t2 set string="changed" where auto=16;

212

show full columns from t1;

213

Field Type Collation Null Key Default Extra Privileges Comment

214

auto int(5) unsigned NULL NO MUL NULL auto_increment #

215

string char(10) latin1_swedish_ci YES newdefault #

216

tiny tinyint(4) NULL NO MUL 0 #

217

short smallint(6) NULL NO MUL NULL #

218

medium mediumint(8) NULL NO MUL 0 #

219

long_int int(11) NULL NO 0 #

220

longlong bigint(13) NULL NO MUL 0 #

221

real_float float(13,1) NULL NO MUL 0.0 #

222

real_double double(16,4) NULL YES NULL #

223

utiny tinyint(3) unsigned NULL NO 0 #

224

ushort smallint(5) unsigned zerofill NULL NO 00000 #

225

umedium mediumint(8) unsigned NULL NO MUL 0 #

226

ulong int(11) unsigned NULL NO MUL 0 #

227

ulonglong bigint(13) unsigned NULL NO MUL 0 #

228

time_stamp timestamp NULL NO CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP #

229

date_field char(10) latin1_swedish_ci YES NULL #

230

time_field time NULL YES NULL #

231

date_time datetime NULL YES NULL #

232

new_blob_col varchar(20) latin1_swedish_ci YES NULL #

233

tinyblob_col tinyblob NULL YES NULL #

234

mediumblob_col mediumblob NULL NO NULL #

235

options enum('one','two','tree') latin1_swedish_ci NO MUL NULL #

236

flags set('one','two','tree') latin1_swedish_ci NO #

237

new_field char(10) latin1_swedish_ci NO new #

238

show full columns from t2;

239

Field Type Collation Null Key Default Extra Privileges Comment

240

auto int(5) unsigned NULL NO 0 #

241

string char(10) latin1_swedish_ci YES newdefault #

242

tiny tinyint(4) NULL NO 0 #

243

short smallint(6) NULL NO NULL #

244

medium mediumint(8) NULL NO 0 #

245

long_int int(11) NULL NO 0 #

246

longlong bigint(13) NULL NO 0 #

247

real_float float(13,1) NULL NO 0.0 #

248

real_double double(16,4) NULL YES NULL #

249

utiny tinyint(3) unsigned NULL NO 0 #

250

ushort smallint(5) unsigned zerofill NULL NO 00000 #

251

umedium mediumint(8) unsigned NULL NO 0 #

252

ulong int(11) unsigned NULL NO 0 #

253

ulonglong bigint(13) unsigned NULL NO 0 #

254

time_stamp timestamp NULL NO 0000-00-00 00:00:00 #

255

date_field char(10) latin1_swedish_ci YES NULL #

256

time_field time NULL YES NULL #

257

date_time datetime NULL YES NULL #

258

new_blob_col varchar(20) latin1_swedish_ci YES NULL #

259

tinyblob_col tinyblob NULL YES NULL #

260

mediumblob_col mediumblob NULL NO NULL #

261

options enum('one','two','tree') latin1_swedish_ci NO NULL #

262

flags set('one','two','tree') latin1_swedish_ci NO #

263

new_field char(10) latin1_swedish_ci NO new #

264

select t1.auto,t2.auto from t1,t2 where t1.auto=t2.auto and ((t1.string<>t2.string and (t1.string is not null or t2.string is not null)) or (t1.tiny<>t2.tiny and (t1.tiny is not null or t2.tiny is not null)) or (t1.short<>t2.short and (t1.short is not null or t2.short is not null)) or (t1.medium<>t2.medium and (t1.medium is not null or t2.medium is not null)) or (t1.long_int<>t2.long_int and (t1.long_int is not null or t2.long_int is not null)) or (t1.longlong<>t2.longlong and (t1.longlong is not null or t2.longlong is not null)) or (t1.real_float<>t2.real_float and (t1.real_float is not null or t2.real_float is not null)) or (t1.real_double<>t2.real_double and (t1.real_double is not null or t2.real_double is not null)) or (t1.utiny<>t2.utiny and (t1.utiny is not null or t2.utiny is not null)) or (t1.ushort<>t2.ushort and (t1.ushort is not null or t2.ushort is not null)) or (t1.umedium<>t2.umedium and (t1.umedium is not null or t2.umedium is not null)) or (t1.ulong<>t2.ulong and (t1.ulong is not null or t2.ulong is not null)) or (t1.ulonglong<>t2.ulonglong and (t1.ulonglong is not null or t2.ulonglong is not null)) or (t1.time_stamp<>t2.time_stamp and (t1.time_stamp is not null or t2.time_stamp is not null)) or (t1.date_field<>t2.date_field and (t1.date_field is not null or t2.date_field is not null)) or (t1.time_field<>t2.time_field and (t1.time_field is not null or t2.time_field is not null)) or (t1.date_time<>t2.date_time and (t1.date_time is not null or t2.date_time is not null)) or (t1.new_blob_col<>t2.new_blob_col and (t1.new_blob_col is not null or t2.new_blob_col is not null)) or (t1.tinyblob_col<>t2.tinyblob_col and (t1.tinyblob_col is not null or t2.tinyblob_col is not null)) or (t1.mediumblob_col<>t2.mediumblob_col and (t1.mediumblob_col is not null or t2.mediumblob_col is not null)) or (t1.options<>t2.options and (t1.options is not null or t2.options is not null)) or (t1.flags<>t2.flags and (t1.flags is not null or t2.flags is not null)) or (t1.new_field<>t2.new_field and (t1.new_field is not null or t2.new_field is not null)));

265

auto auto

266

16 16

267

select t1.auto,t2.auto from t1,t2 where t1.auto=t2.auto and not (t1.string<=>t2.string and t1.tiny<=>t2.tiny and t1.short<=>t2.short and t1.medium<=>t2.medium and t1.long_int<=>t2.long_int and t1.longlong<=>t2.longlong and t1.real_float<=>t2.real_float and t1.real_double<=>t2.real_double and t1.utiny<=>t2.utiny and t1.ushort<=>t2.ushort and t1.umedium<=>t2.umedium and t1.ulong<=>t2.ulong and t1.ulonglong<=>t2.ulonglong and t1.time_stamp<=>t2.time_stamp and t1.date_field<=>t2.date_field and t1.time_field<=>t2.time_field and t1.date_time<=>t2.date_time and t1.new_blob_col<=>t2.new_blob_col and t1.tinyblob_col<=>t2.tinyblob_col and t1.mediumblob_col<=>t2.mediumblob_col and t1.options<=>t2.options and t1.flags<=>t2.flags and t1.new_field<=>t2.new_field);

268

auto auto

269

16 16

270

drop table t2;

271

create table t2 (primary key (auto)) select auto+1 as auto,1 as t1, 'a' as t2, repeat('a',256) as t3, binary repeat('b',256) as t4, repeat('a',4096) as t5, binary repeat('b',4096) as t6, '' as t7, binary '' as t8 from t1;

272

show full columns from t2;

273

Field Type Collation Null Key Default Extra Privileges Comment

274

auto bigint(12) unsigned NULL NO PRI 0 #

275

t1 int(1) NULL NO 0 #

276

t2 varchar(1) latin1_swedish_ci NO #

277

t3 varchar(256) latin1_swedish_ci NO #

278

t4 varbinary(256) NULL NO #

279

t5 longtext latin1_swedish_ci NO NULL #

280

t6 longblob NULL NO NULL #

281

t7 char(0) latin1_swedish_ci NO #

282

t8 binary(0) NULL NO #

283

select t1,t2,length(t3),length(t4),length(t5),length(t6),t7,t8 from t2;

284

t1 t2 length(t3) length(t4) length(t5) length(t6) t7 t8

285

1 a 256 256 4096 4096

286

1 a 256 256 4096 4096

287

1 a 256 256 4096 4096

288

1 a 256 256 4096 4096

289

1 a 256 256 4096 4096

290

1 a 256 256 4096 4096

291

1 a 256 256 4096 4096

292

drop table t1,t2;

293

create table t1 (c int);

294

insert into t1 values(1),(2);

295

create table t2 select * from t1;

296

create table t3 select * from t1, t2;

297

ERROR 42S21: Duplicate column name 'c'

298

create table t3 select t1.c AS c1, t2.c AS c2,1 as "const" from t1, t2;

299

show full columns from t3;

300

Field Type Collation Null Key Default Extra Privileges Comment

301

c1 int(11) NULL YES NULL #

302

c2 int(11) NULL YES NULL #

303

const int(1) NULL NO 0 #

304

drop table t1,t2,t3;

305

create table t1 ( myfield INT NOT NULL, UNIQUE INDEX (myfield), unique (myfield), index(myfield));

306

drop table t1;

307

create table t1 ( id integer unsigned not null primary key );

308

create table t2 ( id integer unsigned not null primary key );

309

insert into t1 values (1), (2);

310

insert into t2 values (1);

311

select t1.id as id_A, t2.id as id_B from t1 left join t2 using ( id );

312

id_A id_B

313

1 1

314

2 NULL

315

select t1.id as id_A, t2.id as id_B from t1 left join t2 on (t1.id = t2.id);

316

id_A id_B

317

1 1

318

2 NULL

319

create table t3 (id_A integer unsigned not null, id_B integer unsigned null );

320

insert into t3 select t1.id as id_A, t2.id as id_B from t1 left join t2 using ( id );

321

select * from t3;

322

id_A id_B

323

1 1

324

2 NULL

325

truncate table t3;

326

insert into t3 select t1.id as id_A, t2.id as id_B from t1 left join t2 on (t1.id = t2.id);

327

select * from t3;

328

id_A id_B

329

1 1

330

2 NULL

331

drop table t3;

332

create table t3 select t1.id as id_A, t2.id as id_B from t1 left join t2 using ( id );

333

select * from t3;

334

id_A id_B

335

1 1

336

2 NULL

337

drop table t3;

338

create table t3 select t1.id as id_A, t2.id as id_B from t1 left join t2 on (t1.id = t2.id);

339

select * from t3;

340

id_A id_B

341

1 1

342

2 NULL

343

drop table t1,t2,t3;

Older »