WebSVN – wasCSharpSQLite – Blame – Rev 1

1

office

1

# 2008 June 24

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.

12

#

13

# $Id: select9.test,v 1.4 2008/07/01 14:39:35 danielk1977 Exp $

14

15

# The tests in this file are focused on test compound SELECT statements

16

# that have any or all of an ORDER BY, LIMIT or OFFSET clauses. As of

17

# version 3.6.0, SQLite contains code to use SQL indexes where possible

18

# to optimize such statements.

#

# TODO Points:

#

# * Are there any "column affinity" issues to consider?

24

25

set testdir [file dirname $argv0]

26

source $testdir/tester.tcl

27

28

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

29

# test_compound_select TESTNAME SELECT RESULT

30

#

31

# This command is used to run multiple LIMIT/OFFSET test cases based on

32

# the single SELECT statement passed as the second argument. The SELECT

33

# statement may not contain a LIMIT or OFFSET clause. This proc tests

34

# many statements of the form:

35

#

36

# "$SELECT limit $X offset $Y"

37

#

38

# for various values of $X and $Y.

39

#

40

# The third argument, $RESULT, should contain the expected result of

41

# the command [execsql $SELECT].

42

#

43

# The first argument, $TESTNAME, is used as the base test case name to

44

# pass to [do_test] for each individual LIMIT OFFSET test case.

45

#

46

proc test_compound_select {testname sql result} {

set nCol 1

db eval $sql A {

set nCol [llength $A(*)]

51

break

52

}

53

set nRow [expr {[llength $result] / $nCol}]

54

55

set ::compound_sql $sql

56

do_test $testname {

57

execsql $::compound_sql

} $result

#return

set iLimitIncr 1

set iOffsetIncr 1

if {[info exists ::G(isquick)] && $::G(isquick) && $nRow>=5} {

64

set iOffsetIncr [expr $nRow / 5]

65

set iLimitIncr [expr $nRow / 5]

66

}

67

68

set iLimitEnd [expr $nRow+$iLimitIncr]

69

set iOffsetEnd [expr $nRow+$iOffsetIncr]

70

71

for {set iOffset 0} {$iOffset < $iOffsetEnd} {incr iOffset $iOffsetIncr} {

72

for {set iLimit 0} {$iLimit < $iLimitEnd} {incr iLimit} {

73

74

set ::compound_sql "$sql LIMIT $iLimit"

75

if {$iOffset != 0} {

76

append ::compound_sql " OFFSET $iOffset"

77

}

78

79

set iStart [expr {$iOffset*$nCol}]

80

set iEnd [expr {($iOffset*$nCol) + ($iLimit*$nCol) -1}]

81

82

do_test $testname.limit=$iLimit.offset=$iOffset {

83

execsql $::compound_sql

84

} [lrange $result $iStart $iEnd]

}

}

}

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

90

# test_compound_select_flippable TESTNAME SELECT RESULT

91

#

92

# This command is for testing statements of the form:

93

#

94

# <simple select 1> <compound op> <simple select 2> ORDER BY <order by>

95

#

96

# where each <simple select> is a simple (non-compound) select statement

97

# and <compound op> is one of "INTERSECT", "UNION ALL" or "UNION".

98

#

99

# This proc calls [test_compound_select] twice, once with the select

100

# statement as it is passed to this command, and once with the positions

101

# of <select statement 1> and <select statement 2> exchanged.

102

#

103

proc test_compound_select_flippable {testname sql result} {

104

test_compound_select $testname $sql $result

105

106

set select [string trim $sql]

107

set RE {(.*)(UNION ALL|INTERSECT|UNION)(.*)(ORDER BY.*)}

108

set rc [regexp $RE $select -> s1 op s2 order_by]

109

if {!$rc} {error "Statement is unflippable: $select"}

110

111

set flipsql "$s2 $op $s1 $order_by"

112

test_compound_select $testname.flipped $flipsql $result

113

}

114

115

#############################################################################

# Begin tests.

#

# Create and populate a sample database.

120

#

121

do_test select9-1.0 {

122

execsql {

123

CREATE TABLE t1(a, b, c);

124

CREATE TABLE t2(d, e, f);

125

BEGIN;

126

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

127

INSERT INTO t1 VALUES(3, NULL, NULL);

128

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

129

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

130

INSERT INTO t1 VALUES(9, NULL, NULL);

131

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

132

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

133

INSERT INTO t1 VALUES(6, NULL, NULL);

134

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

135

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

136

137

INSERT INTO t2 VALUES(1, 'two', 'IV');

138

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

139

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

140

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

141

INSERT INTO t2 VALUES(5, 'ten', 'XX');

142

INSERT INTO t2 VALUES(6, NULL, NULL);

143

INSERT INTO t2 VALUES(7, 'fourteen', 'XXVIII');

144

INSERT INTO t2 VALUES(8, 'sixteen', 'XXXII');

145

INSERT INTO t2 VALUES(9, NULL, NULL);

146

INSERT INTO t2 VALUES(10, 'twenty', 'XL');

COMMIT;

}

} {}

# Each iteration of this loop runs the same tests with a different set

153

# of indexes present within the database schema. The data returned by

154

# the compound SELECT statements in the test cases should be the same

# in each case.

#

set iOuterLoop 1

foreach indexes [list {

159

/* Do not create any indexes. */

160

} {

161

CREATE INDEX i1 ON t1(a)

162

} {

163

CREATE INDEX i2 ON t1(b)

164

} {

165

CREATE INDEX i3 ON t2(d)

166

} {

167

CREATE INDEX i4 ON t2(e)

168

}] {

169

170

do_test select9-1.$iOuterLoop.1 {

execsql $indexes

} {}

# Test some 2-way UNION ALL queries. No WHERE clauses.

175

#

176

test_compound_select select9-1.$iOuterLoop.2 {

177

SELECT a, b FROM t1 UNION ALL SELECT d, e FROM t2

178

  } {1 one 3 {} 5 five 7 seven 9 {} 2 two 4 four 6 {} 8 eight 10 ten 1 two 2 four 3 {} 4 eight 5 ten 6 {} 7 fourteen 8 sixteen 9 {} 10 twenty}

179

test_compound_select select9-1.$iOuterLoop.3 {

180

SELECT a, b FROM t1 UNION ALL SELECT d, e FROM t2 ORDER BY 1

181

  } {1 one 1 two 2 two 2 four 3 {} 3 {} 4 four 4 eight 5 five 5 ten 6 {} 6 {} 7 seven 7 fourteen 8 eight 8 sixteen 9 {} 9 {} 10 ten 10 twenty}

182

test_compound_select select9-1.$iOuterLoop.4 {

183

SELECT a, b FROM t1 UNION ALL SELECT d, e FROM t2 ORDER BY 2

184

  } {3 {} 9 {} 6 {} 3 {} 6 {} 9 {} 8 eight 4 eight 5 five 4 four 2 four 7 fourteen 1 one 7 seven 8 sixteen 10 ten 5 ten 10 twenty 2 two 1 two}

185

test_compound_select_flippable select9-1.$iOuterLoop.5 {

186

SELECT a, b FROM t1 UNION ALL SELECT d, e FROM t2 ORDER BY 1, 2

187

  } {1 one 1 two 2 four 2 two 3 {} 3 {} 4 eight 4 four 5 five 5 ten 6 {} 6 {} 7 fourteen 7 seven 8 eight 8 sixteen 9 {} 9 {} 10 ten 10 twenty}

188

test_compound_select_flippable select9-1.$iOuterLoop.6 {

189

SELECT a, b FROM t1 UNION ALL SELECT d, e FROM t2 ORDER BY 2, 1

190

  } {3 {} 3 {} 6 {} 6 {} 9 {} 9 {} 4 eight 8 eight 5 five 2 four 4 four 7 fourteen 1 one 7 seven 8 sixteen 5 ten 10 ten 10 twenty 1 two 2 two}

191

192

# Test some 2-way UNION queries.

193

#

194

test_compound_select select9-1.$iOuterLoop.7 {

195

SELECT a, b FROM t1 UNION SELECT d, e FROM t2

196

  } {1 one 1 two 2 four 2 two 3 {} 4 eight 4 four 5 five 5 ten 6 {} 7 fourteen 7 seven 8 eight 8 sixteen 9 {} 10 ten 10 twenty}

197

198

test_compound_select select9-1.$iOuterLoop.8 {

199

SELECT a, b FROM t1 UNION SELECT d, e FROM t2 ORDER BY 1

200

  } {1 one 1 two 2 four 2 two 3 {} 4 eight 4 four 5 five 5 ten 6 {} 7 fourteen 7 seven 8 eight 8 sixteen 9 {} 10 ten 10 twenty}

201

202

test_compound_select select9-1.$iOuterLoop.9 {

203

SELECT a, b FROM t1 UNION SELECT d, e FROM t2 ORDER BY 2

204

  } {3 {} 6 {} 9 {} 4 eight 8 eight 5 five 2 four 4 four 7 fourteen 1 one 7 seven 8 sixteen 5 ten 10 ten 10 twenty 1 two 2 two}

205

206

test_compound_select_flippable select9-1.$iOuterLoop.10 {

207

SELECT a, b FROM t1 UNION SELECT d, e FROM t2 ORDER BY 1, 2

208

  } {1 one 1 two 2 four 2 two 3 {} 4 eight 4 four 5 five 5 ten 6 {} 7 fourteen 7 seven 8 eight 8 sixteen 9 {} 10 ten 10 twenty}

209

210

test_compound_select_flippable select9-1.$iOuterLoop.11 {

211

SELECT a, b FROM t1 UNION SELECT d, e FROM t2 ORDER BY 2, 1

212

  } {3 {} 6 {} 9 {} 4 eight 8 eight 5 five 2 four 4 four 7 fourteen 1 one 7 seven 8 sixteen 5 ten 10 ten 10 twenty 1 two 2 two}

213

214

# Test some 2-way INTERSECT queries.

215

#

216

test_compound_select select9-1.$iOuterLoop.11 {

217

SELECT a, b FROM t1 INTERSECT SELECT d, e FROM t2

218

} {3 {} 6 {} 9 {}}

219

test_compound_select_flippable select9-1.$iOuterLoop.12 {

220

SELECT a, b FROM t1 INTERSECT SELECT d, e FROM t2 ORDER BY 1

221

} {3 {} 6 {} 9 {}}

222

test_compound_select select9-1.$iOuterLoop.13 {

223

SELECT a, b FROM t1 INTERSECT SELECT d, e FROM t2 ORDER BY 2

224

} {3 {} 6 {} 9 {}}

225

test_compound_select_flippable select9-1.$iOuterLoop.14 {

226

SELECT a, b FROM t1 INTERSECT SELECT d, e FROM t2 ORDER BY 2, 1

227

} {3 {} 6 {} 9 {}}

228

test_compound_select_flippable select9-1.$iOuterLoop.15 {

229

SELECT a, b FROM t1 INTERSECT SELECT d, e FROM t2 ORDER BY 1, 2

230

} {3 {} 6 {} 9 {}}

231

232

# Test some 2-way EXCEPT queries.

233

#

234

test_compound_select select9-1.$iOuterLoop.16 {

235

SELECT a, b FROM t1 EXCEPT SELECT d, e FROM t2

236

} {1 one 2 two 4 four 5 five 7 seven 8 eight 10 ten}

237

238

test_compound_select select9-1.$iOuterLoop.17 {

239

SELECT a, b FROM t1 EXCEPT SELECT d, e FROM t2 ORDER BY 1

240

} {1 one 2 two 4 four 5 five 7 seven 8 eight 10 ten}

241

242

test_compound_select select9-1.$iOuterLoop.18 {

243

SELECT a, b FROM t1 EXCEPT SELECT d, e FROM t2 ORDER BY 2

244

} {8 eight 5 five 4 four 1 one 7 seven 10 ten 2 two}

245

246

test_compound_select select9-1.$iOuterLoop.19 {

247

SELECT a, b FROM t1 EXCEPT SELECT d, e FROM t2 ORDER BY 1, 2

248

} {1 one 2 two 4 four 5 five 7 seven 8 eight 10 ten}

249

250

test_compound_select select9-1.$iOuterLoop.20 {

251

SELECT a, b FROM t1 EXCEPT SELECT d, e FROM t2 ORDER BY 2, 1

252

} {8 eight 5 five 4 four 1 one 7 seven 10 ten 2 two}

incr iOuterLoop

}

do_test select9-2.0 {

execsql {

DROP INDEX i1;

DROP INDEX i2;

DROP INDEX i3;

DROP INDEX i4;

}

} {}

proc reverse {lhs rhs} {

267

return [string compare $rhs $lhs]

268

}

269

db collate reverse reverse

270

271

# This loop is similar to the previous one (test cases select9-1.*)

272

# except that the simple select statements have WHERE clauses attached

273

# to them. Sometimes the WHERE clause may be satisfied using the same

274

# index used for ORDER BY, sometimes not.

275

#

276

set iOuterLoop 1

277

foreach indexes [list {

278

/* Do not create any indexes. */

279

} {

280

CREATE INDEX i1 ON t1(a)

281

} {

282

DROP INDEX i1;

283

CREATE INDEX i1 ON t1(b, a)

284

} {

285

CREATE INDEX i2 ON t2(d DESC, e COLLATE REVERSE ASC);

286

} {

287

CREATE INDEX i3 ON t1(a DESC);

288

}] {

289

do_test select9-2.$iOuterLoop.1 {

execsql $indexes

} {}

test_compound_select_flippable select9-2.$iOuterLoop.2 {

294

SELECT * FROM t1 WHERE a<5 UNION SELECT * FROM t2 WHERE d>=5 ORDER BY 1

295

} {1 one I 2 two II 3 {} {} 4 four IV 5 ten XX 6 {} {} 7 fourteen XXVIII 8 sixteen XXXII 9 {} {} 10 twenty XL}

296

297

test_compound_select_flippable select9-2.$iOuterLoop.2 {

298

SELECT * FROM t1 WHERE a<5 UNION SELECT * FROM t2 WHERE d>=5 ORDER BY 2, 1

299

} {3 {} {} 6 {} {} 9 {} {} 4 four IV 7 fourteen XXVIII 1 one I 8 sixteen XXXII 5 ten XX 10 twenty XL 2 two II}

300

301

test_compound_select_flippable select9-2.$iOuterLoop.3 {

302

SELECT * FROM t1 WHERE a<5 UNION SELECT * FROM t2 WHERE d>=5

303

ORDER BY 2 COLLATE reverse, 1

304

} {3 {} {} 6 {} {} 9 {} {} 2 two II 10 twenty XL 5 ten XX 8 sixteen XXXII 1 one I 7 fourteen XXVIII 4 four IV}

305

306

test_compound_select_flippable select9-2.$iOuterLoop.4 {

307

SELECT * FROM t1 WHERE a<5 UNION ALL SELECT * FROM t2 WHERE d>=5 ORDER BY 1

308

} {1 one I 2 two II 3 {} {} 4 four IV 5 ten XX 6 {} {} 7 fourteen XXVIII 8 sixteen XXXII 9 {} {} 10 twenty XL}

309

310

test_compound_select_flippable select9-2.$iOuterLoop.5 {

311

SELECT * FROM t1 WHERE a<5 UNION ALL SELECT * FROM t2 WHERE d>=5 ORDER BY 2, 1

312

} {3 {} {} 6 {} {} 9 {} {} 4 four IV 7 fourteen XXVIII 1 one I 8 sixteen XXXII 5 ten XX 10 twenty XL 2 two II}

313

314

test_compound_select_flippable select9-2.$iOuterLoop.6 {

315

SELECT * FROM t1 WHERE a<5 UNION ALL SELECT * FROM t2 WHERE d>=5

316

ORDER BY 2 COLLATE reverse, 1

317

} {3 {} {} 6 {} {} 9 {} {} 2 two II 10 twenty XL 5 ten XX 8 sixteen XXXII 1 one I 7 fourteen XXVIII 4 four IV}

318

319

test_compound_select select9-2.$iOuterLoop.4 {

320

SELECT a FROM t1 WHERE a<8 EXCEPT SELECT d FROM t2 WHERE d<=3 ORDER BY 1

321

} {4 5 6 7}

322

323

test_compound_select select9-2.$iOuterLoop.4 {

324

SELECT a FROM t1 WHERE a<8 INTERSECT SELECT d FROM t2 WHERE d<=3 ORDER BY 1

} {1 2 3}

}

do_test select9-2.X {

execsql {

DROP INDEX i1;

DROP INDEX i2;

DROP INDEX i3;

}

} {}

# This procedure executes the SQL. Then it checks the generated program

338

# for the SQL and appends a "nosort" to the result if the program contains the

339

# SortCallback opcode. If the program does not contain the SortCallback

340

# opcode it appends "sort"

341

#

342

proc cksort {sql} {

343

set ::sqlite_sort_count 0

344

set data [execsql $sql]

345

if {$::sqlite_sort_count} {set x sort} {set x nosort}

lappend data $x

return $data

}

# If the right indexes exist, the following query:

351

#

352

# SELECT t1.a FROM t1 UNION ALL SELECT t2.d FROM t2 ORDER BY 1

353

#

354

# can use indexes to run without doing a in-memory sort operation.

355

# This block of tests (select9-3.*) is used to check if the same

356

# is possible with:

357

#

358

# CREATE VIEW v1 AS SELECT a FROM t1 UNION ALL SELECT d FROM t2

359

# SELECT a FROM v1 ORDER BY 1

360

#

361

# It turns out that it is.

362

#

363

do_test select9-3.1 {

364

cksort { SELECT a FROM t1 ORDER BY 1 }

365

} {1 2 3 4 5 6 7 8 9 10 sort}

366

do_test select9-3.2 {

367

execsql { CREATE INDEX i1 ON t1(a) }

368

cksort { SELECT a FROM t1 ORDER BY 1 }

369

} {1 2 3 4 5 6 7 8 9 10 nosort}

370

do_test select9-3.3 {

371

cksort { SELECT a FROM t1 UNION ALL SELECT d FROM t2 ORDER BY 1 LIMIT 5 }

372

} {1 1 2 2 3 sort}

373

do_test select9-3.4 {

374

execsql { CREATE INDEX i2 ON t2(d) }

375

cksort { SELECT a FROM t1 UNION ALL SELECT d FROM t2 ORDER BY 1 LIMIT 5 }

376

} {1 1 2 2 3 nosort}

377

do_test select9-3.5 {

378

execsql { CREATE VIEW v1 AS SELECT a FROM t1 UNION ALL SELECT d FROM t2 }

379

cksort { SELECT a FROM v1 ORDER BY 1 LIMIT 5 }

380

} {1 1 2 2 3 nosort}

381

do_test select9-3.X {

execsql {

DROP INDEX i1;

DROP INDEX i2;

DROP VIEW v1;

}

} {}

# This block of tests is the same as the preceding one, except that

390

# "UNION" is tested instead of "UNION ALL".

391

#

392

do_test select9-4.1 {

393

cksort { SELECT a FROM t1 ORDER BY 1 }

394

} {1 2 3 4 5 6 7 8 9 10 sort}

395

do_test select9-4.2 {

396

execsql { CREATE INDEX i1 ON t1(a) }

397

cksort { SELECT a FROM t1 ORDER BY 1 }

398

} {1 2 3 4 5 6 7 8 9 10 nosort}

399

do_test select9-4.3 {

400

cksort { SELECT a FROM t1 UNION SELECT d FROM t2 ORDER BY 1 LIMIT 5 }

401

} {1 2 3 4 5 sort}

402

do_test select9-4.4 {

403

execsql { CREATE INDEX i2 ON t2(d) }

404

cksort { SELECT a FROM t1 UNION SELECT d FROM t2 ORDER BY 1 LIMIT 5 }

405

} {1 2 3 4 5 nosort}

406

do_test select9-4.5 {

407

execsql { CREATE VIEW v1 AS SELECT a FROM t1 UNION SELECT d FROM t2 }

408

cksort { SELECT a FROM v1 ORDER BY 1 LIMIT 5 }

409

} {1 2 3 4 5 sort}

410

do_test select9-4.X {

execsql {

DROP INDEX i1;

DROP INDEX i2;

DROP VIEW v1;

}

} {}

finish_test

wasCSharpSQLite – Blame information for rev 1