WebSVN – wasCSharpSQLite – Blame – Rev 1

1

office

1

# 2008 December 23

2

#

3

# The author disclaims copyright to this source code. In place of

4

# a legal notice, here is a blessing:

5

#

6

# May you do good and not evil.

7

# May you find forgiveness for yourself and forgive others.

8

# May you share freely, never taking more than you give.

9

#

10

#***********************************************************************

11

# This file implements regression tests for SQLite library. The focus

12

# is testing of where.c. More specifically, the focus is the optimization

13

# of WHERE clauses that feature the OR operator.

14

#

15

# $Id: where8.test,v 1.9 2009/07/31 06:14:52 danielk1977 Exp $

16

17

set testdir [file dirname $argv0]

18

source $testdir/tester.tcl

19

20

# Test organization:

21

#

22

# where8-1.*: Tests to demonstrate simple cases work with a single table

23

# in the FROM clause.

24

#

25

# where8-2.*: Tests surrounding virtual tables and the OR optimization.

26

#

27

# where8-3.*: Tests with more than one table in the FROM clause.

28

#

29

30

proc execsql_status {sql {db db}} {

31

set result [uplevel $db eval [list $sql]]

32

concat $result [db status step] [db status sort]

33

}

34

35

proc execsql_status2 {sql {db db}} {

36

set ::sqlite_search_count 0

37

set result [uplevel [list execsql_status $sql $db]]

38

concat $result $::sqlite_search_count

39

}

40

41

do_test where8-1.1 {

42

execsql {

43

CREATE TABLE t1(a, b TEXT, c);

44

CREATE INDEX i1 ON t1(a);

45

CREATE INDEX i2 ON t1(b);

46

47

INSERT INTO t1 VALUES(1, 'one', 'I');

48

INSERT INTO t1 VALUES(2, 'two', 'II');

49

INSERT INTO t1 VALUES(3, 'three', 'III');

50

INSERT INTO t1 VALUES(4, 'four', 'IV');

51

INSERT INTO t1 VALUES(5, 'five', 'V');

52

INSERT INTO t1 VALUES(6, 'six', 'VI');

53

INSERT INTO t1 VALUES(7, 'seven', 'VII');

54

INSERT INTO t1 VALUES(8, 'eight', 'VIII');

55

INSERT INTO t1 VALUES(9, 'nine', 'IX');

56

INSERT INTO t1 VALUES(10, 'ten', 'X');

}

} {}

do_test where8-1.2 {

61

execsql_status2 { SELECT c FROM t1 WHERE a = 1 OR b = 'nine' }

62

} {I IX 0 0 6}

63

64

do_test where8-1.3 {

65

execsql_status2 { SELECT c FROM t1 WHERE a > 8 OR b = 'two' }

66

} {IX X II 0 0 6}

67

68

do_test where8-1.4 {

69

execsql_status2 { SELECT c FROM t1 WHERE a > 8 OR b GLOB 't*' }

70

} {IX X III II 0 0 9}

71

72

do_test where8-1.5 {

73

execsql_status2 { SELECT c FROM t1 WHERE a > 8 OR b GLOB 'f*' }

74

} {IX X V IV 0 0 9}

75

76

do_test where8-1.6 {

77

execsql_status { SELECT c FROM t1 WHERE a = 1 OR b = 'three' ORDER BY rowid }

78

} {I III 0 1}

79

80

do_test where8-1.7 {

81

execsql_status { SELECT c FROM t1 WHERE a = 1 OR b = 'three' ORDER BY a }

82

} {I III 0 1}

83

84

do_test where8-1.8 {

85

# 18 searches. 9 on the index cursor and 9 on the table cursor.

86

execsql_status2 { SELECT c FROM t1 WHERE a > 1 AND c LIKE 'I%' }

87

} {II III IV IX 0 0 18}

88

89

do_test where8-1.9 {

90

execsql_status2 { SELECT c FROM t1 WHERE a >= 9 OR b <= 'eight' }

91

} {IX X VIII 0 0 6}

92

93

do_test where8-1.10 {

94

execsql_status2 {

95

SELECT c FROM t1 WHERE (a >= 9 AND c != 'X') OR b <= 'eight'

}

} {IX VIII 0 0 6}

do_test where8-1.11 {

100

execsql_status2 {

101

SELECT c FROM t1 WHERE (a >= 4 AND a <= 6) OR b = 'nine'

102

}

103

} {IV V VI IX 0 0 10}

104

105

do_test where8-1.12.1 {

106

execsql_status2 {

107

SELECT c FROM t1 WHERE a IN(1, 2, 3) OR a = 5

108

}

109

} {I II III V 0 0 14}

110

111

do_test where8-1.12.2 {

112

execsql_status2 {

113

SELECT c FROM t1 WHERE +a IN(1, 2, 3) OR +a = 5

114

}

115

} {I II III V 9 0 9}

116

117

do_test where8-1.13 {

118

execsql_status2 {

119

SELECT c FROM t1

120

WHERE a = 2 OR b = 'three' OR a = 4 OR b = 'five' OR a = 6

121

ORDER BY rowid

122

}

123

} {II III IV V VI 0 1 18}

124

do_test where8-1.14 {

execsql_status2 {

SELECT c FROM t1

WHERE

a = 2 OR b = 'three' OR a = 4 OR b = 'five' OR a = 6 OR

129

b = 'seven' OR a = 8 OR b = 'nine' OR a = 10

130

ORDER BY rowid

131

}

132

} {II III IV V VI VII VIII IX X 0 1 33}

133

134

do_test where8-1.15 {

135

execsql_status2 {

136

SELECT c FROM t1 WHERE

137

a BETWEEN 2 AND 4 OR b = 'nine'

138

ORDER BY rowid

139

}

140

} {II III IV IX 0 1 12}

141

142

143

#--------------------------------------------------------------------------

144

# Tests where8-2.*: Virtual tables

#

if 0 {

ifcapable vtab {

# Register the 'echo' module used for testing virtual tables.

150

#

151

register_echo_module [sqlite3_connection_pointer db]

152

153

do_test where8-2.1 {

154

execsql {

155

CREATE VIRTUAL TABLE e1 USING echo(t1);

156

SELECT b FROM e1;

157

}

158

} {one two three four five six seven eight nine ten}

159

160

do_test where8-2.2.1 {

161

set echo_module ""

162

execsql {

163

SELECT c FROM e1 WHERE a=1 OR b='three';

164

}

165

} {I III}

166

do_test where8-2.2.2 {

167

set echo_module

168

} {TODO: What should this be?}

}

}

#--------------------------------------------------------------------------

173

# Tests where8-3.*: Cases with multiple tables in the FROM clause.

174

#

175

do_test where8-3.1 {

176

execsql {

177

CREATE TABLE t2(d, e, f);

178

CREATE INDEX i3 ON t2(d);

179

CREATE INDEX i4 ON t2(e);

180

181

INSERT INTO t2 VALUES(1, NULL, 'I');

182

INSERT INTO t2 VALUES(2, 'four', 'IV');

183

INSERT INTO t2 VALUES(3, NULL, 'IX');

184

INSERT INTO t2 VALUES(4, 'sixteen', 'XVI');

185

INSERT INTO t2 VALUES(5, NULL, 'XXV');

186

INSERT INTO t2 VALUES(6, 'thirtysix', 'XXXVI');

187

INSERT INTO t2 VALUES(7, 'fortynine', 'XLIX');

188

INSERT INTO t2 VALUES(8, 'sixtyeight', 'LXIV');

189

INSERT INTO t2 VALUES(9, 'eightyone', 'LXXXIX');

190

INSERT INTO t2 VALUES(10, NULL, 'C');

}

} {}

do_test where8-3.2 {

195

execsql_status {

196

SELECT a, d FROM t1, t2 WHERE b=e

}

} {4 2 9 0}

do_test where8-3.3 {

201

execsql_status {

202

SELECT a, d FROM t1, t2 WHERE (a = 2 OR a = 3) AND d = 6

}

} {2 6 3 6 0 0}

do_test where8-3.4 {

207

execsql_status {

208

SELECT a, d FROM t1, t2 WHERE (a = 2 OR a = 3) AND d = a

}

} {2 2 3 3 0 0}

do_test where8-3.5 {

213

execsql_status {

214

SELECT a, d FROM t1, t2 WHERE (a = 2 OR a = 3) AND (d = a OR e = 'sixteen')

215

}

216

} {2 2 2 4 3 3 3 4 0 0}

217

218

do_test where8-3.6 {

219

# The first part of the WHERE clause in this query, (a=2 OR a=3) is

220

# transformed into "a IN (2, 3)". This is why the sort is required.

#

execsql_status {

SELECT a, d

FROM t1, t2

WHERE (a = 2 OR a = 3) AND (d = a OR e = 'sixteen')

226

ORDER BY t1.rowid

227

}

228

} {2 2 2 4 3 3 3 4 0 1}

229

do_test where8-3.7 {

execsql_status {

SELECT a, d

FROM t1, t2

WHERE a = 2 AND (d = a OR e = 'sixteen')

ORDER BY t1.rowid

}

} {2 2 2 4 0 0}

do_test where8-3.8 {

execsql_status {

SELECT a, d

FROM t1, t2

WHERE (a = 2 OR b = 'three') AND (d = a OR e = 'sixteen')

242

ORDER BY t1.rowid

243

}

244

} {2 2 2 4 3 3 3 4 0 1}

245

246

do_test where8-3.9 {

247

# The "OR c = 'IX'" term forces a linear scan.

execsql_status {

SELECT a, d

FROM t1, t2

WHERE (a = 2 OR b = 'three' OR c = 'IX') AND (d = a OR e = 'sixteen')

252

ORDER BY t1.rowid

253

}

254

} {2 2 2 4 3 3 3 4 9 9 9 4 9 0}

255

256

do_test where8-3.10 {

257

execsql_status {

258

SELECT d FROM t2 WHERE e IS NULL OR e = 'four'

}

} {1 3 5 10 2 0 0}

do_test where8-3.11 {

263

execsql_status {

264

SELECT a, d FROM t1, t2 WHERE (a=d OR b=e) AND a<5 ORDER BY a

265

}

266

} {1 1 2 2 3 3 4 2 4 4 0 0}

267

do_test where8-3.12 {

268

execsql_status {

269

SELECT a, d FROM t1, t2 WHERE (a=d OR b=e) AND +a<5 ORDER BY a

270

}

271

} {1 1 2 2 3 3 4 2 4 4 0 0}

272

do_test where8-3.13 {

273

execsql_status {

274

SELECT a, d FROM t1, t2 WHERE (a=d OR b=e) AND +a<5

275

}

276

} {1 1 2 2 3 3 4 2 4 4 9 0}

277

278

do_test where8-3.14 {

279

execsql_status {

280

SELECT c FROM t1 WHERE a > (SELECT d FROM t2 WHERE e = b) OR a = 5

}

} {IV V 9 0}

do_test where8-3.15 {

285

execsql_status {

286

SELECT c FROM t1, t2 WHERE a BETWEEN 1 AND 2 OR a = (

287

SELECT sum(e IS NULL) FROM t2 AS inner WHERE t2.d>inner.d

)

ORDER BY c

}

} {I I I I I I I I I I II II II II II II II II II II III III III III III 9 1}

292

293

#-----------------------------------------------------------------------

294

# The following tests - where8-4.* - verify that adding or removing

295

# indexes does not change the results returned by various queries.

296

#

297

do_test where8-4.1 {

298

execsql {

299

BEGIN;

300

CREATE TABLE t3(a INTEGER, b REAL, c TEXT);

301

CREATE TABLE t4(f INTEGER, g REAL, h TEXT);

302

INSERT INTO t3 VALUES('hills', NULL, 1415926535);

303

INSERT INTO t3 VALUES('and', 'of', NULL);

304

INSERT INTO t3 VALUES('have', 'towering', 53594.08128);

305

INSERT INTO t3 VALUES(NULL, 45.64856692, 'Not');

306

INSERT INTO t3 VALUES('same', 5028841971, NULL);

307

INSERT INTO t3 VALUES('onlookers', 'in', 8214808651);

308

INSERT INTO t3 VALUES(346.0348610, 2643383279, NULL);

309

INSERT INTO t3 VALUES(1415926535, 'of', 'are');

310

INSERT INTO t3 VALUES(NULL, 0.4811174502, 'snapshots');

311

INSERT INTO t3 VALUES('over', 'the', 8628034825);

312

INSERT INTO t3 VALUES(8628034825, 66.59334461, 2847564.823);

313

INSERT INTO t3 VALUES('onlookers', 'same', 'and');

314

INSERT INTO t3 VALUES(NULL, 'light', 6939937510);

315

INSERT INTO t3 VALUES('from', 'their', 'viewed');

316

INSERT INTO t3 VALUES('from', 'Alpine', 'snapshots');

317

INSERT INTO t3 VALUES('from', 'sometimes', 'unalike');

318

INSERT INTO t3 VALUES(1339.360726, 'light', 'have');

319

INSERT INTO t3 VALUES(6939937510, 3282306647, 'other');

320

INSERT INTO t3 VALUES('paintings', 8628034825, 'all');

321

INSERT INTO t3 VALUES('paintings', NULL, 'same');

322

INSERT INTO t3 VALUES('Alpine', 378678316.5, 'unalike');

323

INSERT INTO t3 VALUES('Alpine', NULL, 'same');

324

INSERT INTO t3 VALUES(1339.360726, 2847564.823, 'over');

325

INSERT INTO t3 VALUES('villages', 'their', 'have');

326

INSERT INTO t3 VALUES('unalike', 'remarkably', 'in');

327

INSERT INTO t3 VALUES('and', 8979323846, 'and');

328

INSERT INTO t3 VALUES(NULL, 1415926535, 'an');

329

INSERT INTO t3 VALUES(271.2019091, 8628034825, 0.4811174502);

330

INSERT INTO t3 VALUES('all', 3421170679, 'the');

331

INSERT INTO t3 VALUES('Not', 'and', 1415926535);

332

INSERT INTO t3 VALUES('of', 'other', 'light');

333

INSERT INTO t3 VALUES(NULL, 'towering', 'Not');

334

INSERT INTO t3 VALUES(346.0348610, NULL, 'other');

335

INSERT INTO t3 VALUES('Not', 378678316.5, NULL);

336

INSERT INTO t3 VALUES('snapshots', 8628034825, 'of');

337

INSERT INTO t3 VALUES(3282306647, 271.2019091, 'and');

338

INSERT INTO t3 VALUES(50.58223172, 378678316.5, 5028841971);

339

INSERT INTO t3 VALUES(50.58223172, 2643383279, 'snapshots');

340

INSERT INTO t3 VALUES('writings', 8979323846, 8979323846);

341

INSERT INTO t3 VALUES('onlookers', 'his', 'in');

342

INSERT INTO t3 VALUES('unalike', 8628034825, 1339.360726);

343

INSERT INTO t3 VALUES('of', 'Alpine', 'and');

344

INSERT INTO t3 VALUES('onlookers', NULL, 'from');

345

INSERT INTO t3 VALUES('writings', 'it', 1339.360726);

346

INSERT INTO t3 VALUES('it', 'and', 'villages');

347

INSERT INTO t3 VALUES('an', 'the', 'villages');

348

INSERT INTO t3 VALUES(8214808651, 8214808651, 'same');

349

INSERT INTO t3 VALUES(346.0348610, 'light', 1415926535);

350

INSERT INTO t3 VALUES(NULL, 8979323846, 'and');

351

INSERT INTO t3 VALUES(NULL, 'same', 1339.360726);

352

INSERT INTO t4 VALUES('his', 'from', 'an');

353

INSERT INTO t4 VALUES('snapshots', 'or', NULL);

354

INSERT INTO t4 VALUES('Alpine', 'have', 'it');

355

INSERT INTO t4 VALUES('have', 'peak', 'remarkably');

356

INSERT INTO t4 VALUES('hills', NULL, 'Not');

357

INSERT INTO t4 VALUES('same', 'from', 2643383279);

358

INSERT INTO t4 VALUES('have', 'angle', 8628034825);

359

INSERT INTO t4 VALUES('sometimes', 'it', 2847564.823);

360

INSERT INTO t4 VALUES(0938446095, 'peak', 'of');

361

INSERT INTO t4 VALUES(8628034825, 'and', 'same');

362

INSERT INTO t4 VALUES('and', 271.2019091, 'their');

363

INSERT INTO t4 VALUES('the', 'of', 'remarkably');

364

INSERT INTO t4 VALUES('and', 3421170679, 1415926535);

365

INSERT INTO t4 VALUES('and', 'in', 'all');

366

INSERT INTO t4 VALUES(378678316.5, 0.4811174502, 'snapshots');

367

INSERT INTO t4 VALUES('it', 'are', 'have');

368

INSERT INTO t4 VALUES('angle', 'snapshots', 378678316.5);

369

INSERT INTO t4 VALUES('from', 1415926535, 8628034825);

370

INSERT INTO t4 VALUES('snapshots', 'angle', 'have');

371

INSERT INTO t4 VALUES(3421170679, 0938446095, 'Not');

372

INSERT INTO t4 VALUES('peak', NULL, 0.4811174502);

373

INSERT INTO t4 VALUES('same', 'have', 'Alpine');

374

INSERT INTO t4 VALUES(271.2019091, 66.59334461, 0938446095);

375

INSERT INTO t4 VALUES(8979323846, 'his', 'an');

376

INSERT INTO t4 VALUES(NULL, 'and', 3282306647);

377

INSERT INTO t4 VALUES('remarkably', NULL, 'Not');

378

INSERT INTO t4 VALUES('villages', 4543.266482, 'his');

379

INSERT INTO t4 VALUES(2643383279, 'paintings', 'onlookers');

380

INSERT INTO t4 VALUES(1339.360726, 'of', 'the');

381

INSERT INTO t4 VALUES('peak', 'other', 'peak');

382

INSERT INTO t4 VALUES('it', 'or', 8979323846);

383

INSERT INTO t4 VALUES('onlookers', 'Not', 'towering');

384

INSERT INTO t4 VALUES(NULL, 'peak', 'Not');

385

INSERT INTO t4 VALUES('of', 'have', 6939937510);

386

INSERT INTO t4 VALUES('light', 'hills', 0.4811174502);

387

INSERT INTO t4 VALUES(5028841971, 'Not', 'it');

388

INSERT INTO t4 VALUES('and', 'Not', NULL);

389

INSERT INTO t4 VALUES(346.0348610, 'villages', NULL);

390

INSERT INTO t4 VALUES(8979323846, NULL, 6939937510);

391

INSERT INTO t4 VALUES('an', 'light', 'peak');

392

INSERT INTO t4 VALUES(5028841971, 6939937510, 'light');

393

INSERT INTO t4 VALUES('sometimes', 'peak', 'peak');

394

INSERT INTO t4 VALUES(378678316.5, 5028841971, 'an');

395

INSERT INTO t4 VALUES(378678316.5, 'his', 'Alpine');

396

INSERT INTO t4 VALUES('from', 'of', 'all');

397

INSERT INTO t4 VALUES(0938446095, 'same', NULL);

398

INSERT INTO t4 VALUES(0938446095, 'Alpine', NULL);

399

INSERT INTO t4 VALUES('his', 'of', 378678316.5);

400

INSERT INTO t4 VALUES(271.2019091, 'viewed', 3282306647);

401

INSERT INTO t4 VALUES('hills', 'all', 'peak');

402

CREATE TABLE t5(s);

403

INSERT INTO t5 VALUES('tab-t5');

404

CREATE TABLE t6(t);

405

INSERT INTO t6 VALUES(123456);

COMMIT;

}

} {}

catch {unset results}

catch {unset A}

catch {unset B}

set A 2

foreach idxsql {

{

/* No indexes */

} {

CREATE INDEX i5 ON t3(a);

420

} {

421

CREATE INDEX i5 ON t3(a, b);

422

CREATE INDEX i6 ON t4(f);

423

} {

424

CREATE UNIQUE INDEX i5 ON t3(a, b);

425

CREATE INDEX i7 ON t3(c);

426

CREATE INDEX i6 ON t4(f);

427

CREATE INDEX i8 ON t4(h);

428

} {

429

CREATE INDEX i5 ON t3(a, b, c);

430

CREATE INDEX i6 ON t4(f, g, h);

431

CREATE INDEX i7 ON t3(c, b, a);

432

CREATE INDEX i8 ON t4(h, g, f);

}

} {

execsql {

DROP INDEX IF EXISTS i5;

438

DROP INDEX IF EXISTS i6;

439

DROP INDEX IF EXISTS i7;

440

DROP INDEX IF EXISTS i8;

}

execsql $idxsql

foreach {B sql} {

1 { SELECT * FROM t3 WHERE c LIKE b }

446

2 { SELECT * FROM t3 WHERE c||'' LIKE 'the%' }

447

3 { SELECT * FROM t3 WHERE rowid LIKE '12%' }

448

4 { SELECT * FROM t3 WHERE +c LIKE 'the%' }

449

5 { SELECT * FROM t3 WHERE c LIKE 'the%' }

450

6 { SELECT * FROM t3 WHERE c GLOB '*llo' }

451

452

7 { SELECT * FROM t3 WHERE a = 'angle' }

453

8 { SELECT * FROM t3 WHERE a = 'it' OR b = 6939937510 }

454

9 { SELECT * FROM t3, t4 WHERE a = 'painting' OR a = 'are' OR a = f }

455

10 { SELECT * FROM t3, t4 WHERE a = 'all' OR a = 'and' OR a = h }

456

11 { SELECT * FROM t3, t4 WHERE a < 'of' OR b > 346 AND c IS NULL }

457

12 { SELECT * FROM t3, t4 WHERE 'the' > a OR b > 'have' AND c = 1415926535 }

458

459

13 { SELECT * FROM t3 WHERE a BETWEEN 'one' AND 'two' OR a = 3421170679 }

460

14 { SELECT * FROM t3 WHERE a BETWEEN 'one' AND 'two' OR a IS NULL }

461

15 { SELECT * FROM t3 WHERE c > 'one' OR c >= 'one' OR c LIKE 'one%' }

462

16 { SELECT * FROM t3 WHERE c > 'one' OR c = c OR c = a }

463

17 { SELECT * FROM t3 WHERE c IS NULL OR a >= 'peak' }

464

18 { SELECT * FROM t3 WHERE c IN ('other', 'all', 'snapshots') OR a>1 }

465

19 { SELECT * FROM t3 WHERE c IN ('other', 'all', 'snapshots') AND a>1 }

466

20 { SELECT * FROM t3 WHERE c IS NULL AND a>'one' }

467

21 { SELECT * FROM t3 WHERE c IS NULL OR a>'one' }

468

22 { SELECT * FROM t3 WHERE b = b AND a > 'are' }

469

23 { SELECT * FROM t3 WHERE c <= b OR b < 'snapshots' }

470

24 { SELECT * FROM t3 WHERE 'onlookers' >= c AND a <= b OR b = 'angle' }

471

25 { SELECT * FROM t3 WHERE b = 'from' }

472

26 { SELECT * FROM t3 WHERE b = 4543.266482 }

473

27 { SELECT * FROM t3 WHERE c < 3282306647 }

474

28 { SELECT * FROM t3 WHERE c IS NULL AND b >= c }

475

29 { SELECT * FROM t3 WHERE b > 0.4811174502 AND c = 'other' AND 'viewed' > a }

476

30 { SELECT * FROM t3 WHERE c = 'peak' }

477

31 { SELECT * FROM t3 WHERE c < 53594.08128 OR c <= b }

478

32 { SELECT * FROM t3 WHERE 'writings' <= b }

479

33 { SELECT * FROM t3 WHERE 2643383279 = b OR c < b AND b <= 3282306647 }

480

34 { SELECT * FROM t3 WHERE a IS NULL }

481

35 { SELECT * FROM t3 WHERE 'writings' = a OR b = 378678316.5 }

482

36 { SELECT * FROM t3 WHERE 'and' >= c }

483

37 { SELECT * FROM t3 WHERE c < 'from' }

484

38 { SELECT * FROM t3 WHERE 'his' < c OR b < b }

485

39 { SELECT * FROM t3 WHERE 53594.08128 = b AND c >= b }

486

40 { SELECT * FROM t3 WHERE 'unalike' < c AND 'are' >= c AND a <= b }

487

41 { SELECT * FROM t3 WHERE b >= 4543.266482 OR 'Alpine' > a OR 271.2019091 <= a }

488

42 { SELECT * FROM t3 WHERE b = c }

489

43 { SELECT * FROM t3 WHERE c > a AND b < 'all' }

490

44 { SELECT * FROM t3 WHERE c BETWEEN 'hills' AND 'snapshots' AND c <= 'the' OR c = a }

491

45 { SELECT * FROM t3 WHERE b > c AND c >= 'hills' }

492

46 { SELECT * FROM t3 WHERE b > 'or' OR a <= 'hills' OR c IS NULL }

493

47 { SELECT * FROM t3 WHERE c > b OR b BETWEEN 1339.360726 AND 'onlookers' OR 1415926535 >= b }

494

48 { SELECT * FROM t3 WHERE a IS NULL }

495

49 { SELECT * FROM t3 WHERE a > 'other' }

496

50 { SELECT * FROM t3 WHERE 'the' <= c AND a <= c }

497

51 { SELECT * FROM t3 WHERE 346.0348610 = a AND c = b }

498

52 { SELECT * FROM t3 WHERE c BETWEEN 50.58223172 AND 'same' AND a < b }

499

53 { SELECT * FROM t3 WHERE 'Alpine' <= b AND c >= 'angle' OR b <= 271.2019091 }

500

54 { SELECT * FROM t3 WHERE a < a AND 1415926535 > b }

501

55 { SELECT * FROM t3 WHERE c > a AND 'have' >= c }

502

56 { SELECT * FROM t3 WHERE b <= b AND c > b }

503

57 { SELECT * FROM t3 WHERE a IS NULL AND c <= c }

504

58 { SELECT * FROM t3 WHERE b < c OR b = c }

505

59 { SELECT * FROM t3 WHERE c = 'it' }

506

60 { SELECT * FROM t3 WHERE a = b AND a <= b OR b >= a }

507

61 { SELECT * FROM t3 WHERE b = c }

508

62 { SELECT * FROM t3 WHERE c BETWEEN 'the' AND 271.2019091 OR c <= 3282306647 AND c >= b }

509

63 { SELECT * FROM t3 WHERE c >= c AND c < 'writings' }

510

64 { SELECT * FROM t3 WHERE c <= 3282306647 AND b > a OR 'unalike' <= a }

511

65 { SELECT * FROM t3 WHERE a > c }

512

66 { SELECT * FROM t3 WHERE c = 'it' OR b >= b }

513

67 { SELECT * FROM t3 WHERE c = a OR b < c }

514

68 { SELECT * FROM t3 WHERE b > a }

515

69 { SELECT * FROM t3 WHERE a 4543.266482 OR 'same' = b }

516

70 { SELECT * FROM t3 WHERE c < c OR b <= c OR a <= b }

517

71 { SELECT * FROM t3 WHERE c > a }

518

72 { SELECT * FROM t3 WHERE c > b }

519

73 { SELECT * FROM t3 WHERE b <= a }

520

74 { SELECT * FROM t3 WHERE 3282306647 = 'or' OR a >= 378678316.5 }

521

75 { SELECT * FROM t3 WHERE 50.58223172 <= c OR c = c AND b < b }

522

76 { SELECT * FROM t3 WHERE 'and' 1339.360726 }

523

77 { SELECT * FROM t3 WHERE b <= c }

524

78 { SELECT * FROM t3 WHERE 'in' <= c }

525

79 { SELECT * FROM t3 WHERE c <= b AND a > a AND c < b }

526

80 { SELECT * FROM t3 WHERE 'over' < b }

527

81 { SELECT * FROM t3 WHERE b >= b OR b < c OR a < b }

528

82 { SELECT * FROM t3 WHERE 'towering' <= b OR 'towering' = a AND c > b }

529

83 { SELECT * FROM t3 WHERE 'peak' = a OR b BETWEEN 2643383279 AND 'the' }

530

84 { SELECT * FROM t3 WHERE 'an' < c AND c > 'the' AND c IS NULL }

531

85 { SELECT * FROM t3 WHERE a <= 'sometimes' AND a BETWEEN 'unalike' AND 1339.360726 }

532

86 { SELECT * FROM t3 WHERE 1339.360726 < c AND c IS NULL }

533

87 { SELECT * FROM t3 WHERE b > 'the' }

534

88 { SELECT * FROM t3 WHERE 'and' = a }

535

89 { SELECT * FROM t3 WHERE b >= b }

536

90 { SELECT * FROM t3 WHERE b >= 8979323846 }

537

91 { SELECT * FROM t3 WHERE c <= a }

538

92 { SELECT * FROM t3 WHERE a BETWEEN 'have' AND 'light' OR a > b OR a >= 378678316.5 }

539

93 { SELECT * FROM t3 WHERE c > 3282306647 }

540

94 { SELECT * FROM t3 WHERE b > c }

541

95 { SELECT * FROM t3 WHERE b >= a AND 'villages' > a AND b >= c }

542

96 { SELECT * FROM t3 WHERE 'angle' > a }

543

97 { SELECT * FROM t3 WHERE 'paintings' >= a }

544

98 { SELECT * FROM t3 WHERE 'or' >= c }

545

99 { SELECT * FROM t3 WHERE c < b }

546

547

548

101 { SELECT * FROM t3, t4 WHERE f < 'sometimes' OR 'over' <= g AND h < 1415926535 }

549

102 { SELECT * FROM t3, t4 WHERE h >= 'from' AND h < 6939937510 OR g > h }

550

103 { SELECT * FROM t3, t4 WHERE c <= h AND g = h AND c >= 'all' }

551

104 { SELECT * FROM t3, t4 WHERE c = a }

552

105 { SELECT * FROM t3, t4 WHERE 'of' >= h }

553

106 { SELECT * FROM t3, t4 WHERE f >= b AND a < g AND h < 'and' }

554

107 { SELECT * FROM t3, t4 WHERE f <= 8628034825 AND 0938446095 >= b }

555

108 { SELECT * FROM t3, t4 WHERE a < 'the' }

556

109 { SELECT * FROM t3, t4 WHERE f = 'sometimes' OR b < 'of' }

557

110 { SELECT * FROM t3, t4 WHERE c IS NULL }

558

111 { SELECT * FROM t3, t4 WHERE 'have' = b OR g <= 346.0348610 }

559

112 { SELECT * FROM t3, t4 WHERE f > b AND b <= h }

560

113 { SELECT * FROM t3, t4 WHERE f > c OR 'the' = a OR 50.58223172 = a }

561

114 { SELECT * FROM t3, t4 WHERE 2643383279 <= a AND c = a }

562

115 { SELECT * FROM t3, t4 WHERE h >= b AND 'it' <= b }

563

116 { SELECT * FROM t3, t4 WHERE g BETWEEN 'from' AND 'peak' }

564

117 { SELECT * FROM t3, t4 WHERE 'their' > a AND g > b AND f <= c }

565

118 { SELECT * FROM t3, t4 WHERE h = 5028841971 AND 'unalike' <= f }

566

119 { SELECT * FROM t3, t4 WHERE c IS NULL AND a = 3282306647 OR a <= 'Alpine' }

567

120 { SELECT * FROM t3, t4 WHERE 'sometimes' <= f OR 8214808651 >= a AND b <= 53594.08128 }

568

121 { SELECT * FROM t3, t4 WHERE 6939937510 <= f OR c < f OR 'sometimes' = c }

569

122 { SELECT * FROM t3, t4 WHERE b < 'onlookers' AND 'paintings' = g AND c <= h }

570

123 { SELECT * FROM t3, t4 WHERE a BETWEEN 'all' AND 'from' OR c > 346.0348610 }

571

124 { SELECT * FROM t3, t4 WHERE 'from' <= b OR a BETWEEN 53594.08128 AND 'their' AND c > a }

572

125 { SELECT * FROM t3, t4 WHERE h = 2643383279 }

573

126 { SELECT * FROM t3, t4 WHERE a <= 'the' }

574

127 { SELECT * FROM t3, t4 WHERE h <= c }

575

128 { SELECT * FROM t3, t4 WHERE g <= 346.0348610 AND 66.59334461 >= f AND f <= f }

576

129 { SELECT * FROM t3, t4 WHERE g >= c OR 'in' g }

577

130 { SELECT * FROM t3, t4 WHERE 'over' > g AND b BETWEEN 'unalike' AND 'remarkably' }

578

131 { SELECT * FROM t3, t4 WHERE h <= 2847564.823 }

579

132 { SELECT * FROM t3, t4 WHERE h <= 'remarkably' AND 4543.266482 > h }

580

133 { SELECT * FROM t3, t4 WHERE a >= c AND 'it' > g AND c < c }

581

134 { SELECT * FROM t3, t4 WHERE h <= 66.59334461 AND b > 3421170679 }

582

135 { SELECT * FROM t3, t4 WHERE h < 'are' OR f BETWEEN 0938446095 AND 'are' OR b = b }

583

136 { SELECT * FROM t3, t4 WHERE h = a OR 66.59334461 <= f }

584

137 { SELECT * FROM t3, t4 WHERE f > 'of' OR h <= h OR a = f }

585

138 { SELECT * FROM t3, t4 WHERE 'other' >= g }

586

139 { SELECT * FROM t3, t4 WHERE b <= 3421170679 }

587

140 { SELECT * FROM t3, t4 WHERE 'all' = f AND 4543.266482 = b OR f BETWEEN 'and' AND 'angle' }

588

141 { SELECT * FROM t3, t4 WHERE 'light' = f OR h BETWEEN 'remarkably' AND 1415926535 }

589

142 { SELECT * FROM t3, t4 WHERE 'hills' = f OR 'the' >= f }

590

143 { SELECT * FROM t3, t4 WHERE a > 346.0348610 }

591

144 { SELECT * FROM t3, t4 WHERE 5028841971 = h }

592

145 { SELECT * FROM t3, t4 WHERE b >= c AND 'the' >= g OR 45.64856692 <= g }

593

146 { SELECT * FROM t3, t4 WHERE c < 5028841971 }

594

147 { SELECT * FROM t3, t4 WHERE a > a }

595

148 { SELECT * FROM t3, t4 WHERE c = 'snapshots' }

596

149 { SELECT * FROM t3, t4 WHERE h > 1339.360726 AND 'and' > c }

597

150 { SELECT * FROM t3, t4 WHERE 'and' > g OR 'sometimes' = c }

598

151 { SELECT * FROM t3, t4 WHERE g >= 'the' AND b >= 'onlookers' }

599

152 { SELECT * FROM t3, t4 WHERE h BETWEEN 'other' AND 2643383279 }

600

153 { SELECT * FROM t3, t4 WHERE 'it' = b }

601

154 { SELECT * FROM t3, t4 WHERE f = c OR c BETWEEN 'and' AND 0.4811174502 }

602

155 { SELECT * FROM t3, t4 WHERE b <= 'sometimes' OR c <= 0938446095 }

603

156 { SELECT * FROM t3, t4 WHERE 'and' <= b }

604

157 { SELECT * FROM t3, t4 WHERE g > a AND f = 'the' AND b < a }

605

158 { SELECT * FROM t3, t4 WHERE a < 'an' }

606

159 { SELECT * FROM t3, t4 WHERE a BETWEEN 'his' AND 'same' OR 8628034825 > f }

607

160 { SELECT * FROM t3, t4 WHERE b = 'peak' }

608

161 { SELECT * FROM t3, t4 WHERE f IS NULL AND a >= h }

609

162 { SELECT * FROM t3, t4 WHERE a IS NULL OR 2643383279 = c }

610

163 { SELECT * FROM t3, t4 WHERE b >= 5028841971 AND f < c AND a IS NULL }

611

164 { SELECT * FROM t3, t4 WHERE a >= g }

612

165 { SELECT * FROM t3, t4 WHERE c IS NULL }

613

166 { SELECT * FROM t3, t4 WHERE h >= h }

614

167 { SELECT * FROM t3, t4 WHERE 'over' <= h }

615

168 { SELECT * FROM t3, t4 WHERE b < 4543.266482 OR b = 2643383279 OR 8628034825 < b }

616

169 { SELECT * FROM t3, t4 WHERE g >= 6939937510 }

617

170 { SELECT * FROM t3, t4 WHERE 'or' < a OR b < g }

618

171 { SELECT * FROM t3, t4 WHERE h < 'hills' OR 'and' > g }

619

172 { SELECT * FROM t3, t4 WHERE 'from' > f OR f <= f }

620

173 { SELECT * FROM t3, t4 WHERE 'viewed' > b AND f < c }

621

174 { SELECT * FROM t3, t4 WHERE 'of' <= a }

622

175 { SELECT * FROM t3, t4 WHERE f > 0938446095 }

623

176 { SELECT * FROM t3, t4 WHERE a = g }

624

177 { SELECT * FROM t3, t4 WHERE g >= b AND f BETWEEN 'peak' AND 'and' }

625

178 { SELECT * FROM t3, t4 WHERE g = a AND 'it' > f }

626

179 { SELECT * FROM t3, t4 WHERE a <= b OR 'from' > f }

627

180 { SELECT * FROM t3, t4 WHERE f < 'and' }

628

181 { SELECT * FROM t3, t4 WHERE 6939937510 < b OR 'sometimes' < h }

629

182 { SELECT * FROM t3, t4 WHERE f > g AND f < 'peak' }

630

183 { SELECT * FROM t3, t4 WHERE a <= 53594.08128 AND c <= f AND f >= c }

631

184 { SELECT * FROM t3, t4 WHERE f = c OR 'it' > b OR g BETWEEN 'the' AND 'all' }

632

185 { SELECT * FROM t3, t4 WHERE c <= g OR a = h }

633

186 { SELECT * FROM t3, t4 WHERE 'same' = b OR c >= 2643383279 }

634

187 { SELECT * FROM t3, t4 WHERE h <= g OR c > 66.59334461 OR a <= f }

635

188 { SELECT * FROM t3, t4 WHERE b < c AND f = 'writings' }

636

189 { SELECT * FROM t3, t4 WHERE b < a }

637

190 { SELECT * FROM t3, t4 WHERE c >= f OR c = 'and' }

638

191 { SELECT * FROM t3, t4 WHERE f >= 'peak' AND g > f AND h > g }

639

192 { SELECT * FROM t3, t4 WHERE a >= 8979323846 AND 'same' > b OR c = 'and' }

640

193 { SELECT * FROM t3, t4 WHERE c >= g OR 'writings' >= c AND b = 'all' }

641

194 { SELECT * FROM t3, t4 WHERE 'remarkably' < g }

642

195 { SELECT * FROM t3, t4 WHERE a BETWEEN 'or' AND 'paintings' AND g <= f }

643

196 { SELECT * FROM t3, t4 WHERE 0938446095 > b OR g <= a OR h > b }

644

197 { SELECT * FROM t3, t4 WHERE g = 2643383279 AND f = g }

645

198 { SELECT * FROM t3, t4 WHERE g < 8979323846 }

646

199 { SELECT * FROM t3, t4 WHERE 'are' <= b }

647

200 { SELECT * FROM t3, t4 WHERE (a=1415926535 AND f=8628034825)

648

OR (a=6939937510 AND f=2643383279) }

649

201 { SELECT * FROM t3, t4, t5, t6

650

WHERE (a=1415926535 AND f=8628034825 AND s!='hello' AND t!=5)

651

OR (a=6939937510 AND f=2643383279 AND s='tab-t5' AND t=123456) }

652

202 { SELECT * FROM t3, t4, t5, t6

653

WHERE (a=1415926535 AND f=8628034825 AND s!='hello' AND t==5)

654

OR (a=6939937510 AND f=2643383279 AND s='tab-t5' AND t!=123456) }

655

656

} {

657

do_test where8-4.$A.$B.1 {

658

unset -nocomplain R

659

set R [execsql $sql]

660

if {![info exists results($B)]} {

set results($B) $R

}

list

} {}

do_test where8-4.$A.$B.2 { lsort $R } [lsort $results($B)]

}

incr A

}

catch {unset results}

catch {unset A}

catch {unset B}

# At one point the following tests provoked an invalid write error (writing

675

# to memory that had already been freed). It was not possible to demonstrate

676

# that this bug could cause a query to return bad data.

677

#

678

do_test where8-5.1 {

679

db close

680

sqlite3 db test.db

681

sqlite3_db_config_lookaside db 0 0 0

682

execsql {

683

CREATE TABLE tA(

684

a, b, c, d, e, f, g, h,

685

i, j, k, l, m, n, o, p

);

}

execsql {

SELECT * FROM tA WHERE

690

a=1 AND b=2 AND c=3 AND d=4 AND e=5 AND f=6 AND g=7 AND h=8 AND

691

i=1 AND j=2 AND k=3 AND l=4 AND m=5 AND n=6 AND o=7 AND

692

(p = 1 OR p = 2 OR p = 3)

693

}

694

} {}

695

do_test where8-5.2 {

696

execsql {

697

SELECT * FROM tA WHERE

698

a=1 AND b=2 AND c=3 AND d=4 AND e=5 AND f=6 AND g=7 AND h=8 AND

699

i=1 AND j=2 AND k=3 AND l=4 AND m=5 AND

700

(p = 1 OR p = 2 OR p = 3) AND n=6 AND o=7

701

}

702

} {}

703

do_test where8-5.3 {

704

execsql {

705

INSERT INTO tA VALUES(1, 2, 3, 4, 5, 6, 7, 8, 1, 2, 3, 4, 5, 6, 7, 8);

706

CREATE UNIQUE INDEX tAI ON tA(p);

707

CREATE TABLE tB(x);

708

INSERT INTO tB VALUES('x');

709

}

710

execsql {

711

SELECT a, x FROM tA LEFT JOIN tB ON (

712

a=1 AND b=2 AND c=3 AND d=4 AND e=5 AND f=6 AND g=7 AND h=8 AND

713

i=1 AND j=2 AND k=3 AND l=4 AND m=5 AND n=6 AND o=7 AND

714

(p = 1 OR p = 2 OR p = 3)

)

}

} {1 {}}

finish_test

wasCSharpSQLite – Blame information for rev 1