~drizzle-trunk/drizzle/development

CREATE TABLE t1 ( USID INTEGER, ServerID int, State ENUM ('unknown', 'Access-Granted', 'Session-Active', 'Session-Closed' ) NOT NULL DEFAULT 'unknown', SessionID CHAR(32), User CHAR(32) NOT NULL DEFAULT '<UNKNOWN>', NASAddr INTEGER, NASPort INTEGER, NASPortType INTEGER, ConnectSpeed INTEGER, CarrierType CHAR(32), CallingStationID CHAR(32), CalledStationID CHAR(32), AssignedAddr INTEGER, SessionTime INTEGER, PacketsIn INTEGER, OctetsIn INTEGER, PacketsOut INTEGER, OctetsOut INTEGER, TerminateCause INTEGER, UnauthTime int, AccessRequestTime DATETIME, AcctStartTime DATETIME, AcctLastTime DATETIME, LastModification TIMESTAMP NOT NULL);

117

CREATE TABLE t2 ( USID INTEGER AUTO_INCREMENT, ServerID int, State ENUM ('unknown', 'Access-Granted', 'Session-Active', 'Session-Closed' ) NOT NULL DEFAULT 'unknown', SessionID CHAR(32), User TEXT NOT NULL, NASAddr INTEGER, NASPort INTEGER, NASPortType INTEGER, ConnectSpeed INTEGER, CarrierType CHAR(32), CallingStationID CHAR(32), CalledStationID CHAR(32), AssignedAddr INTEGER, SessionTime INTEGER, PacketsIn INTEGER, OctetsIn INTEGER, PacketsOut INTEGER, OctetsOut INTEGER, TerminateCause INTEGER, UnauthTime int, AccessRequestTime DATETIME, AcctStartTime DATETIME, AcctLastTime DATETIME, LastModification TIMESTAMP NOT NULL, INDEX(USID,ServerID,NASAddr,SessionID), INDEX(AssignedAddr));

118

INSERT INTO t1 VALUES (39,42,'Access-Granted','46','491721000045',2130706433,17690,NULL,NULL,'Localnet','491721000045','49172200000',754974766,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'2003-07-18 00:11:21',NULL,NULL,20030718001121);

119

INSERT INTO t2 SELECT USID, ServerID, State, SessionID, User, NASAddr, NASPort, NASPortType, ConnectSpeed, CarrierType, CallingStationID, CalledStationID, AssignedAddr, SessionTime, PacketsIn, OctetsIn, PacketsOut, OctetsOut, TerminateCause, UnauthTime, AccessRequestTime, AcctStartTime, AcctLastTime, LastModification from t1 LIMIT 1;

120

drop table t1,t2;

121

122

123

# Another problem from Bug #2012

124

125

126

CREATE TABLE t1(

127

Month date NOT NULL,

128

Type int NOT NULL auto_increment,

129

Field int NOT NULL,

130

Count int NOT NULL,

131

PRIMARY KEY (Type),

132

UNIQUE KEY Month (Month,Type,Field)

133

);

134

135

insert into t1 Values (20030901, 1, 1, 100);

136

insert into t1 Values (20030901, 2, 2, 100);

137

insert into t1 Values (20030901, 3, 3, 100);

138

insert into t1 Values (20030901, 4, 4, 100);

139

insert into t1 Values (20030901, 5, 5, 100);

140

141

select * from t1;

142

143

Select null, Field, Count From t1 Where Month=20030901 and Type=2;

144

145

create table t2(No int not null, Field int not null, Count int not null);

146

147

insert into t2 Select 0, Field, Count From t1 Where Month=20030901 and Type=2;

148

149

select * from t2;

150

151

drop table t1, t2;

152

153

154

# BUG#6034 - Error code 124: Wrong medium type

155

156

CREATE TABLE t1 (

157

ID int NOT NULL auto_increment,

158

NO int NOT NULL default '0',

159

SEQ int NOT NULL default '0',

160

PRIMARY KEY (ID),

161

KEY t1$NO (SEQ,NO)

162

);

163

INSERT INTO t1 (SEQ, NO) SELECT "1" AS SEQ, IF(MAX(NO) IS NULL, 0, MAX(NO)) + 1 AS NO FROM t1 WHERE (SEQ = 1);

164

select SQL_BUFFER_RESULT * from t1 WHERE (SEQ = 1);

165

drop table t1;

166

167

168

# Bug#10886 - Have to restore default values after update ON DUPLICATE KEY

169

170

create table t1 (f1 int);

171

create table t2 (ff1 int unique, ff2 int default 1);

172

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

173

insert into t2(ff1) select f1 from t1 on duplicate key update ff2=ff2+1;

174

select * from t2;

175

drop table t1, t2;

176

177

# BUGS #9728 - 'Decreased functionality in "on duplicate key update"'

178

# #8147 - 'a column proclaimed ambigous in INSERT ... SELECT .. ON

179

# DUPLICATE'

180

181

create table t1 (a int unique);

182

create table t2 (a int, b int);

183

create table t3 (c int, d int);

184

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

185

insert into t2 values (1,2);

186

insert into t3 values (1,6),(3,7);

187

select * from t1;

188

insert into t1 select a from t2 on duplicate key update a= t1.a + t2.b;

189

select * from t1;

190

insert into t1 select a+1 from t2 on duplicate key update t1.a= t1.a + t2.b+1;

191

select * from t1;

192

insert into t1 select t3.c from t3 on duplicate key update a= a + t3.d;

193

select * from t1;

194

insert into t1 select t2.a from t2 group by t2.a on duplicate key update a= a + 10;

195

196

#Some error cases

197

--error ER_NON_UNIQ_ERROR

198

insert into t1 select t2.a from t2 on duplicate key update a= a + t2.b;

199

--error ER_BAD_FIELD_ERROR

200

insert into t1 select t2.a from t2 on duplicate key update t2.a= a + t2.b;

201

--error ER_BAD_FIELD_ERROR

202

insert into t1 select t2.a from t2 group by t2.a on duplicate key update a= t1.a + t2.b;

203

drop table t1,t2,t3;

204

205

206

# Bug #12695 Item_func_isnull::update_used_tables() did not update

207

# const_item_cache

208

create table t1(f1 varchar(5) key);

209

insert into t1(f1) select if(max(f1) is null, '2000',max(f1)+1) from t1;

210

insert into t1(f1) select if(max(f1) is null, '2000',max(f1)+1) from t1;

211

insert into t1(f1) select if(max(f1) is null, '2000',max(f1)+1) from t1;

212

select * from t1;

213

drop table t1;

214

215

216

# Bug #13392 values() fails with 'ambiguous' or returns NULL

217

# with ON DUPLICATE and SELECT

218

create table t1(x int, y int);

219

create table t2(x int, z int);

220

insert into t1(x,y) select x,z from t2 on duplicate key update x=values(x);

221

--error ER_BAD_FIELD_ERROR

222

insert into t1(x,y) select x,z from t2 on duplicate key update x=values(z);

223

--error ER_BAD_FIELD_ERROR

224

insert into t1(x,y) select x,z from t2 on duplicate key update x=values(t2.x);

225

drop table t1,t2;

226

227

228

# Bug #9676: INSERT INTO x SELECT .. FROM x LIMIT 1; slows down with big

229

# tables

230

231

232

#Note: not an exsaustive test : just a check of the code path.

233

CREATE TABLE t1 (a int PRIMARY KEY);

234

INSERT INTO t1 values (1), (2);

235

236

flush status;

237

INSERT INTO t1 SELECT a + 2 FROM t1 LIMIT 1;

238

--replace_column 2 #

239

show status like 'Handler_read%';

240

241

DROP TABLE t1;

242

243

244

# Bug #29717 INSERT INTO SELECT inserts values even if SELECT statement itself returns empty

245

246

247

CREATE TABLE t1 (

248

f1 int NOT NULL auto_increment PRIMARY KEY,

249

f2 varchar(100) NOT NULL default ''

250

);

251

CREATE TABLE t2 (

252

f1 varchar(10) NOT NULL default '',

253

f2 char(3) NOT NULL default '',

254

PRIMARY KEY (`f1`),

255

KEY `k1` (`f2`, `f1`)

256

);

257

258

INSERT INTO t1 values(NULL, '');

259

INSERT INTO `t2` VALUES ('486878','WDT'),('486910','WDT');

260

SELECT COUNT(*) FROM t1;

261

262

SELECT min(t2.f1) FROM t1, t2 where t2.f2 = 'SIR' GROUP BY t1.f1;

263

264

INSERT INTO t1 (f2)

265

SELECT min(t2.f1) FROM t1, t2 where t2.f2 = 'SIR' GROUP BY t1.f1;

266

267

SELECT COUNT(*) FROM t1;

268

SELECT * FROM t1;

269

DROP TABLE t1, t2;

270

271

# End of 4.1 tests

272

273

274

# Bug #18080: INSERT ... SELECT ... JOIN results in ambiguous field list error

275

276

CREATE TABLE t1 (x int, y int);

277

CREATE TABLE t2 (z int, y int);

278

CREATE TABLE t3 (a int, b int);

279

INSERT INTO t3 (SELECT x, y FROM t1 JOIN t2 USING (y) WHERE z = 1);

280

DROP TABLE IF EXISTS t1,t2,t3;

281

282

283

# Bug #21774: Column count doesn't match value count at row x

284

285

CREATE DATABASE bug21774_1;

286

CREATE DATABASE bug21774_2;

287

288

CREATE TABLE bug21774_1.t1(id VARCHAR(10) NOT NULL,label VARCHAR(255));

289

CREATE TABLE bug21774_2.t1(id VARCHAR(10) NOT NULL,label VARCHAR(255));

290

CREATE TABLE bug21774_1.t2(id VARCHAR(10) NOT NULL,label VARCHAR(255));

291

292

INSERT INTO bug21774_2.t1 SELECT t1.* FROM bug21774_1.t1;

293

294

use bug21774_1;

295

INSERT INTO bug21774_2.t1 SELECT t1.* FROM t1;

296

297

DROP DATABASE bug21774_1;

298

DROP DATABASE bug21774_2;

299

USE test;

300

301

302

# Bug#19978: INSERT .. ON DUPLICATE erroneously reports some records were

303

# updated.

304

305

create table t1(f1 int primary key, f2 int);

306

--enable_info

307

insert into t1 values (1,1);

308

insert into t1 values (1,1) on duplicate key update f2=1;

309

insert into t1 values (1,1) on duplicate key update f2=2;

310

--disable_info

311

select * from t1;

312

drop table t1;

313

314

315

# Bug#16630: wrong result, when INSERT t1 SELECT ... FROM t1 ON DUPLICATE

316

317

CREATE TABLE t1 (f1 INT, f2 INT );

318

CREATE TABLE t2 (f1 INT PRIMARY KEY, f2 INT);

319

INSERT INTO t1 VALUES (1,1),(2,2),(10,10);

320

INSERT INTO t2 (f1, f2) SELECT f1, f2 FROM t1;

321

INSERT INTO t2 (f1, f2)

322

SELECT f1, f1 FROM t2 src WHERE f1 < 2

323

ON DUPLICATE KEY UPDATE f1 = 100 + src.f1;

324

SELECT * FROM t2;

325

DROP TABLE t1, t2;

326

327

328

# Bug #29095: incorrect pushing of LIMIT into the temporary

329

# table ignoring ORDER BY clause

330

331

332

CREATE TABLE t1 (

333

id INT AUTO_INCREMENT PRIMARY KEY,

334

prev_id INT,

335

join_id INT DEFAULT 0);

336

337

INSERT INTO t1 (prev_id) VALUES (NULL), (1), (2);

338

SELECT * FROM t1;

339

340

CREATE TABLE t2 (join_id INT);

341

INSERT INTO t2 (join_id) VALUES (0);

342

343

INSERT INTO t1 (prev_id) SELECT id

344

FROM t2 LEFT JOIN t1 ON t1.join_id = t2.join_id

345

ORDER BY id DESC LIMIT 1;

346

SELECT * FROM t1;

347

348

DROP TABLE t1,t2;

349

350

--echo #

351

--echo # Bug#30384: Having SQL_BUFFER_RESULT option in the

352

--echo # CREATE .. KEY(..) .. SELECT led to creating corrupted index.

353

--echo #

354

create table t1(f1 int);

355

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

356

create temporary table t2 (key(f1)) engine=myisam select sql_buffer_result f1 from t1;

357

check table t2;

358

drop table t1,t2;

359

--echo ##################################################################

360

Older »