~linuxjedi/drizzle/trunk-bug-667053

insert into t1 values (101),(691231),(700101),(991231),(10000101),(99991231),(101000000),(691231000000),(700101000000),(991231235959),(10000101000000),(99991231235959),(20030100000000),(20030000000000);

select * from t1;

delete from t1 where t > 0;

optimize table t1;

check table t1;

delete from t1;

insert into t1 values("000101"),("691231"),("700101"),("991231"),("00000101"),("00010101"),("99991231"),("00101000000"),("691231000000"),("700101000000"),("991231235959"),("10000101000000"),("99991231235959"),("20030100000000"),("20030000000000");

# Strange dates

insert into t1 values ("2003-003-03");

# Bug #7308: ISO-8601 date format not handled correctly

insert into t1 values ("20030102T131415"),("2001-01-01T01:01:01"), ("2001-1-1T1:01:01");

select * from t1;

# Test some wrong dates

truncate table t1;

insert into t1 values("2003-0303 12:13:14");

select * from t1;

drop table t1;

# Test insert of now() and curtime()

CREATE TABLE t1 (a timestamp, b date, c time, d datetime);

insert into t1 (b,c,d) values(now(),curtime(),now());

select date_format(a,"%Y-%m-%d")=b,right(a+0,6)=c+0,a=d+0 from t1;

drop table t1;

# Test of datetime and not null

CREATE TABLE t1 (a datetime not null);

insert into t1 values (0);

select * from t1 where a is null;

drop table t1;

# Test with bug when propagating DATETIME to integer and WHERE optimization

create table t1 (id int, dt datetime);

insert into t1 values (1,"2001-08-14 00:00:00"),(2,"2001-08-15 00:00:00"),(3,"2001-08-16 00:00:00"),(4,"2003-09-15 01:20:30");

select * from t1 where dt='2001-08-14 00:00:00' and dt = if(id=1,'2001-08-14 00:00:00','1999-08-15');

create index dt on t1 (dt);

select * from t1 where dt > 20021020;

select * from t1 ignore index (dt) where dt > 20021020;

drop table t1;

# Test of datetime optimization

CREATE TABLE `t1` (

`date` datetime NOT NULL default '0000-00-00 00:00:00',

`numfacture` int(6) unsigned NOT NULL default '0',

`expedition` datetime NOT NULL default '0000-00-00 00:00:00',

PRIMARY KEY (`numfacture`),

KEY `date` (`date`),

KEY `expedition` (`expedition`)

) ENGINE=MyISAM;

INSERT INTO t1 (expedition) VALUES ('0001-00-00 00:00:00');

SELECT * FROM t1 WHERE expedition='0001-00-00 00:00:00';

INSERT INTO t1 (numfacture,expedition) VALUES ('1212','0001-00-00 00:00:00');

SELECT * FROM t1 WHERE expedition='0001-00-00 00:00:00';

EXPLAIN SELECT * FROM t1 WHERE expedition='0001-00-00 00:00:00';

drop table t1;

create table t1 (a datetime not null, b datetime not null);

insert into t1 values (now(), now());

select * from t1 where a is null or b is null;

drop table t1;

# Let us check if we properly treat wrong datetimes and produce proper

# warnings (for both strings and numbers)

create table t1 (t datetime);

insert into t1 values (20030102030460),(20030102036301),(20030102240401),

(20030132030401),(20031302030401),(100001202030401);

select * from t1;

delete from t1;

insert into t1 values

("2003-01-02 03:04:60"),("2003-01-02 03:63:01"),("2003-01-02 24:04:01"),

("2003-01-32 03:04:01"),("2003-13-02 03:04:01"), ("10000-12-02 03:04:00");

select * from t1;

delete from t1;

100

insert into t1 values ("0000-00-00 00:00:00 some trailer"),("2003-01-01 00:00:00 some trailer");

101

select * from t1 order by t;

102

drop table t1;

103

104

105

# Test for bug #7297 "Two digit year should be interpreted correctly even

106

# with zero month and day"

107

108

create table t1 (dt datetime);

109

# These dates should be treated as dates in 21st century

110

insert into t1 values ("12-00-00"), ("00-00-00 01:00:00");

111

# Zero dates are still special :/

112

insert into t1 values ("00-00-00"), ("00-00-00 00:00:00");

113

select * from t1;

114

drop table t1;

115

116

117

# Bug #16546 DATETIME+0 not always coerced the same way

118

119

select cast('2006-12-05 22:10:10' as datetime) + 0;

120

121

122

# End of 4.1 tests

123

124

125

# Bug#21475: Wrongly applied constant propagation leads to a false comparison.

126

127

CREATE TABLE t1(a DATETIME NOT NULL);

128

INSERT INTO t1 VALUES ('20060606155555');

129

SELECT a FROM t1 WHERE a=(SELECT MAX(a) FROM t1) AND (a="20060606155555");

130

PREPARE s FROM 'SELECT a FROM t1 WHERE a=(SELECT MAX(a) FROM t1) AND (a="20060606155555")';

131

EXECUTE s;

132

DROP PREPARE s;

133

DROP TABLE t1;

134

135

136

137

# Bug 19491 (CAST DATE AS DECIMAL returns incorrect result

138

139

SELECT CAST(CAST('2006-08-10' AS DATE) AS DECIMAL(20,6));

140

SELECT CAST(CAST('2006-08-10 10:11:12' AS DATETIME) AS DECIMAL(20,6));

141

SELECT CAST(CAST('2006-08-10 10:11:12' AS DATETIME) + INTERVAL 14 MICROSECOND AS DECIMAL(20,6));

142

SELECT CAST(CAST('10:11:12.098700' AS TIME) AS DECIMAL(20,6));

143

144

145

146

# Test of storing datetime into date fields

147

148

149

set @org_mode=@@sql_mode;

150

create table t1 (da date default '1962-03-03 23:33:34', dt datetime default '1962-03-03');

151

show create table t1;

152

insert into t1 values ();

153

insert into t1 values ('2007-03-23 13:49:38','2007-03-23 13:49:38');

154

set @@sql_mode='ansi,traditional';

155

insert into t1 values ('2007-03-23 13:49:38','2007-03-23 13:49:38');

156

insert into t1 set dt='2007-03-23 13:49:38',da=dt;

157

# Test error handling

158

--error ER_TRUNCATED_WRONG_VALUE

159

insert into t1 values ('2007-03-32','2007-03-23 13:49:38');

160

select * from t1;

161

drop table t1;

162

--error ER_INVALID_DEFAULT

163

create table t1 (da date default '1962-03-32 23:33:34', dt datetime default '1962-03-03');

164

--error ER_INVALID_DEFAULT

165

create table t1 (t time default '916:00:00 a');

166

set @@sql_mode= @org_mode;

167

168

169

170

# Bug#27590: Wrong DATE/DATETIME comparison.

171

172

## The following sub test will fail (difference to expected result) if the

173

## select curdate() < now(), f1 < now(), cast(f1 as date) < now() from t1;

174

## runs exact at midnight ('00:00:00').

175

## ( Bug#29290 type_datetime.test failure in 5.1 )

176

## Therefore we sleep a bit if we are too close to midnight.

177

## The complete test itself needs around 1 second.

178

## Therefore a time_distance to midnight of 5 seconds should be sufficient.

179

if (`SELECT CURTIME() > SEC_TO_TIME(24 * 3600 - 5)`)

180

{

181

# We are here when CURTIME() is between '23:59:56' and '23:59:59'.

182

# So a sleep time of 5 seconds brings us between '00:00:01' and '00:00:04'.

183

--real_sleep 5

184

}

185

create table t1 (f1 date, f2 datetime, f3 timestamp);

186

insert into t1(f1) values(curdate());

187

select curdate() < now(), f1 < now(), cast(f1 as date) < now() from t1;

188

delete from t1;

189

insert into t1 values('2001-01-01','2001-01-01 01:01:01','2001-01-01 01:01:01');

190

insert into t1 values('2001-02-05','2001-02-05 00:00:00','2001-02-05 01:01:01');

191

insert into t1 values('2001-03-10','2001-03-09 01:01:01','2001-03-10 01:01:01');

192

insert into t1 values('2001-04-15','2001-04-15 00:00:00','2001-04-15 00:00:00');

193

insert into t1 values('2001-05-20','2001-05-20 01:01:01','2001-05-20 01:01:01');

194

select f1, f3 from t1 where f1 >= '2001-02-05 00:00:00' and f3 <= '2001-04-15';

195

select f1, f3 from t1 where f1 >= '2001-2-5 0:0:0' and f2 <= '2001-4-15';

196

select f1, f2 from t1 where if(1, f1, 0) >= f2;

197

select 1 from dual where cast('2001-1-1 2:3:4' as date) = cast('2001-01-01' as datetime);

198

select f1, f2, f1 > f2, f1 = f2, f1 < f2 from t1;

199

drop table t1;

200

201

202

# Bug#16377: Wrong DATE/DATETIME comparison in BETWEEN function.

203

204

create table t1 (f1 date, f2 datetime, f3 timestamp);

205

insert into t1 values('2001-01-01','2001-01-01 01:01:01','2001-01-01 01:01:01');

206

insert into t1 values('2001-02-05','2001-02-05 00:00:00','2001-02-05 01:01:01');

207

insert into t1 values('2001-03-10','2001-03-09 01:01:01','2001-03-10 01:01:01');

208

insert into t1 values('2001-04-15','2001-04-15 00:00:00','2001-04-15 00:00:00');

209

insert into t1 values('2001-05-20','2001-05-20 01:01:01','2001-05-20 01:01:01');

210

select f2 from t1 where f2 between '2001-2-5' and '01-04-14';

211

select f1, f2, f3 from t1 where f1 between f2 and f3;

212

select f1, f2, f3 from t1 where cast(f1 as datetime) between f2 and

213

cast(f3 as date);

214

select f2 from t1 where '2001-04-10 12:34:56' between f2 and '01-05-01';

215

select f2, f3 from t1 where '01-03-10' between f2 and f3;

216

select f2 from t1 where DATE(f2) between "2001-4-15" AND "01-4-15";

217

SELECT 1 from dual where NOW() BETWEEN CURRENT_DATE() - INTERVAL 1 DAY AND CURRENT_DATE();

218

drop table t1;

219

220

221

# Bug#28133: Wrong DATE/DATETIME comparison in IN() function.

222

223

create table t1 (f1 date);

224

insert into t1 values('01-01-01'),('01-01-02'),('01-01-03');

225

select * from t1 where f1 in ('01-01-01','2001-01-02','2001-01-03 00:00:00');

226

create table t2(f2 datetime);

227

insert into t2 values('01-01-01 00:00:00'),('01-02-03 12:34:56'),('02-04-06 11:22:33');

228

select * from t2 where f2 in ('01-01-01','01-02-03 12:34:56','01-02-03');

229

select * from t1,t2 where '01-01-02' in (f1, cast(f2 as date));

230

select * from t1,t2 where '01-01-01' in (f1, '01-02-03');

231

select * from t1,t2 where if(1,'01-02-03 12:34:56','') in (f1, f2);

232

create table t3(f3 varchar(20));

233

insert into t3 select * from t2;

234

select * from t2,t3 where f2 in (f3,'03-04-05');

235

select f1,f2,f3 from t1,t2,t3 where (f1,'1') in ((f2,'1'),(f3,'1'));

236

select f1 from t1 where ('1',f1) in (('1','01-01-01'),('1','2001-1-1 0:0:0'),('1','02-02-02'));

237

drop table t1,t2,t3;

238

239

240

# Bug#27759: Wrong DATE/DATETIME comparison in LEAST()/GREATEST() functions.

241

242

select least(cast('01-01-01' as date), '01-01-02');

243

select greatest(cast('01-01-01' as date), '01-01-02');

244

select least(cast('01-01-01' as date), '01-01-02') + 0;

245

select greatest(cast('01-01-01' as date), '01-01-02') + 0;

246

select least(cast('01-01-01' as datetime), '01-01-02') + 0;

247

select cast(least(cast('01-01-01' as datetime), '01-01-02') as signed);

248

select cast(least(cast('01-01-01' as datetime), '01-01-02') as decimal(16,2));

249

--disable_warnings

250

DROP PROCEDURE IF EXISTS test27759 ;

251

--enable_warnings

252

DELIMITER |;

253

CREATE PROCEDURE test27759()

254

BEGIN

255

declare v_a date default '2007-4-10';

256

declare v_b date default '2007-4-11';

257

declare v_c datetime default '2004-4-9 0:0:0';

258

select v_a as a,v_b as b,

259

least( v_a, v_b ) as a_then_b,

260

least( v_b, v_a ) as b_then_a,

261

least( v_c, v_a ) as c_then_a;

262

END;|

263

DELIMITER ;|

264

call test27759();

265

drop procedure test27759;

266

267

268

# Bug#28208: Wrong result of a non-const STRING function with a const

269

# DATETIME function.

270

271

create table t1 (f1 date);

272

insert into t1 values (curdate());

273

select left(f1,10) = curdate() from t1;

274

drop table t1;

275

276

277

# Bug#28261: Wrong DATETIME comparison result when the GET_USER_VAR function

278

# is involved.

279

280

create table t1(f1 date);

281

insert into t1 values('01-01-01'),('02-02-02'),('01-01-01'),('02-02-02');

282

set @bug28261='';

283

select if(@bug28261 = f1, '', @bug28261:= f1) from t1;

284

select if(@bug28261 = f1, '', @bug28261:= f1) from t1;

285

select if(@bug28261 = f1, '', @bug28261:= f1) from t1;

286

drop table t1;

287

288

289

# Bug#28778: Wrong result of BETWEEN when comparing a DATETIME field with an

290

# integer constants.

291

292

create table t1(f1 datetime);

293

insert into t1 values('2001-01-01'),('2002-02-02');

294

select * from t1 where f1 between 20020101 and 20070101000000;

295

select * from t1 where f1 between 2002010 and 20070101000000;

296

select * from t1 where f1 between 20020101 and 2007010100000;

297

drop table t1;

298

299

--echo #

300

--echo # Bug#27216: functions with parameters of different date types may

301

--echo # return wrong type of the result.

302

--echo #

303

create table t1 (f1 date, f2 datetime, f3 varchar(20));

304

create table t2 as select coalesce(f1,f1) as f4 from t1;

305

desc t2;

306

create table t3 as select coalesce(f1,f2) as f4 from t1;

307

desc t3;

308

create table t4 as select coalesce(f2,f2) as f4 from t1;

309

desc t4;

310

create table t5 as select coalesce(f1,f3) as f4 from t1;

311

desc t5;

312

create table t6 as select coalesce(f2,f3) as f4 from t1;

313

desc t6;

314

create table t7 as select coalesce(makedate(1997,1),f2) as f4 from t1;

315

desc t7;

316

create table t8 as select coalesce(cast('01-01-01' as datetime),f2) as f4

317

from t1;

318

desc t8;

319

create table t9 as select case when 1 then cast('01-01-01' as date)

320

when 0 then cast('01-01-01' as date) end as f4 from t1;

321

desc t9;

322

create table t10 as select case when 1 then cast('01-01-01' as datetime)

323

when 0 then cast('01-01-01' as datetime) end as f4 from t1;

324

desc t10;

325

create table t11 as select if(1, cast('01-01-01' as datetime),

326

cast('01-01-01' as date)) as f4 from t1;

327

desc t11;

328

create table t12 as select least(cast('01-01-01' as datetime),

329

cast('01-01-01' as date)) as f4 from t1;

330

desc t12;

331

create table t13 as select ifnull(cast('01-01-01' as datetime),

332

cast('01-01-01' as date)) as f4 from t1;

333

desc t13;

334

drop tables t1,t2,t3,t4,t5,t6,t7,t8,t9,t10,t11,t12,t13;

335

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

336

337

# Bug #31253: crash comparing datetime to double

338

# Should return 1st row only. Crashes if NULL propagation fails.

339

340

create table t1 (f1 time);

341

insert into t1 set f1 = '45:44:44';

342

insert into t1 set f1 = '15:44:44';

343

select * from t1 where (convert(f1,datetime)) != 1;

344

drop table t1;

345

346

347

# Bug #31249: problem with convert(..., datetime)

348

349

create table t1 (a tinyint);

350

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

351

select sum(a) from t1 group by convert(a, datetime);

352

drop table t1;

353

354

355

# Bug #32694: NOT NULL table field in a subquery produces invalid results

356

357

create table t1 (id int(10) not null, cur_date datetime not null);

358

create table t2 (id int(10) not null, cur_date date not null);

359

insert into t1 (id, cur_date) values (1, '2007-04-25 18:30:22');

360

insert into t2 (id, cur_date) values (1, '2007-04-25');

361

362

set @@optimizer_switch=no_semijoin;

363

explain extended

364

select * from t1

365

where id in (select id from t1 as x1 where (t1.cur_date is null));

366

select * from t1

367

where id in (select id from t1 as x1 where (t1.cur_date is null));

368

369

explain extended

370

select * from t2

371

where id in (select id from t2 as x1 where (t2.cur_date is null));

372

select * from t2

373

where id in (select id from t2 as x1 where (t2.cur_date is null));

374

375

insert into t1 (id, cur_date) values (2, '2007-04-26 18:30:22');

376

insert into t2 (id, cur_date) values (2, '2007-04-26');

377

378

explain extended

379

select * from t1

380

where id in (select id from t1 as x1 where (t1.cur_date is null));

381

select * from t1

382

where id in (select id from t1 as x1 where (t1.cur_date is null));

383

384

explain extended

385

select * from t2

386

where id in (select id from t2 as x1 where (t2.cur_date is null));

387

select * from t2

388

where id in (select id from t2 as x1 where (t2.cur_date is null));

389

set @@optimizer_switch='';

390

drop table t1,t2;

391

392

--echo End of 5.0 tests

393

394

# Test of storing datetime into date fields

395

396

397

set @org_mode=@@sql_mode;

398

create table t1 (da date default '1962-03-03 23:33:34', dt datetime default '1962-03-03');

399

show create table t1;

400

insert into t1 values ();

401

insert into t1 values ('2007-03-23 13:49:38','2007-03-23 13:49:38');

402

set @@sql_mode='ansi,traditional';

403

insert into t1 values ('2007-03-23 13:49:38','2007-03-23 13:49:38');

404

insert into t1 set dt='2007-03-23 13:49:38',da=dt;

405

# Test error handling

406

--error 1292

407

insert into t1 values ('2007-03-32','2007-03-23 13:49:38');

408

select * from t1;

409

drop table t1;

410

--error 1067

411

create table t1 (da date default '1962-03-32 23:33:34', dt datetime default '1962-03-03');

412

--error 1067

413

create table t1 (t time default '916:00:00 a');

414

set @@sql_mode= @org_mode;

Older »