WebSVN – wasCSharpSQLite – Blame – Rev 1

1

office

1

# 2001 September 15

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

# of this file is testing the interaction of manifest types, type affinity

13

# and comparison expressions.

14

#

15

# $Id: types2.test,v 1.7 2007/02/23 03:00:45 drh Exp $

16

17

set testdir [file dirname $argv0]

18

source $testdir/tester.tcl

19

20

# Tests in this file are organized roughly as follows:

21

#

22

# types2-1.*: The '=' operator in the absence of an index.

23

# types2-2.*: The '=' operator implemented using an index.

24

# types2-3.*: The '<' operator implemented using an index.

25

# types2-4.*: The '>' operator in the absence of an index.

26

# types2-5.*: The 'IN(x, y...)' operator in the absence of an index.

27

# types2-6.*: The 'IN(x, y...)' operator with an index.

28

# types2-7.*: The 'IN(SELECT...)' operator in the absence of an index.

29

# types2-8.*: The 'IN(SELECT...)' operator with an index.

30

#

31

# All tests test the operators using literals and columns, but no

32

# other types of expressions. All expressions except columns are

33

# handled similarly in the implementation.

execsql {

CREATE TABLE t1(

i1 INTEGER,

i2 INTEGER,

n1 NUMERIC,

n2 NUMERIC,

t1 TEXT,

t2 TEXT,

o1 BLOB,

o2 BLOB

);

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

47

}

48

49

proc test_bool {testname vars expr res} {

50

if { $vars != "" } {

51

execsql "UPDATE t1 SET $vars"

52

}

53

54

foreach {t e r} [list $testname $expr $res] {}

55

56

do_test $t.1 "execsql {SELECT $e FROM t1}" $r

57

do_test $t.2 "execsql {SELECT 1 FROM t1 WHERE $expr}" [expr $r?"1":""]

58

do_test $t.3 "execsql {SELECT 1 FROM t1 WHERE NOT ($e)}" [expr $r?"":"1"]

59

}

60

61

# Compare literals against literals. This should always use a numeric

62

# comparison.

63

#

64

# Changed by ticket #805: Use no affinity for literal comparisons.

65

#

66

test_bool types2-1.1 "" {500 = 500.0} 1

67

test_bool types2-1.2 "" {'500' = 500.0} 0

68

test_bool types2-1.3 "" {500 = '500.0'} 0

69

test_bool types2-1.4 "" {'500' = '500.0'} 0

70

71

# Compare literals against a column with TEXT affinity

72

test_bool types2-1.5 {t1=500} {500 = t1} 1

73

test_bool types2-1.6 {t1=500} {'500' = t1} 1

74

test_bool types2-1.7 {t1=500} {500.0 = t1} 0

75

test_bool types2-1.8 {t1=500} {'500.0' = t1} 0

76

test_bool types2-1.9 {t1='500'} {500 = t1} 1

77

test_bool types2-1.10 {t1='500'} {'500' = t1} 1

78

test_bool types2-1.11 {t1='500'} {500.0 = t1} 0

79

test_bool types2-1.12 {t1='500'} {'500.0' = t1} 0

80

81

# Compare literals against a column with NUMERIC affinity

82

test_bool types2-1.13 {n1=500} {500 = n1} 1

83

test_bool types2-1.14 {n1=500} {'500' = n1} 1

84

test_bool types2-1.15 {n1=500} {500.0 = n1} 1

85

test_bool types2-1.16 {n1=500} {'500.0' = n1} 1

86

test_bool types2-1.17 {n1='500'} {500 = n1} 1

87

test_bool types2-1.18 {n1='500'} {'500' = n1} 1

88

test_bool types2-1.19 {n1='500'} {500.0 = n1} 1

89

test_bool types2-1.20 {n1='500'} {'500.0' = n1} 1

90

91

# Compare literals against a column with affinity NONE

92

test_bool types2-1.21 {o1=500} {500 = o1} 1

93

test_bool types2-1.22 {o1=500} {'500' = o1} 0

94

test_bool types2-1.23 {o1=500} {500.0 = o1} 1

95

test_bool types2-1.24 {o1=500} {'500.0' = o1} 0

96

test_bool types2-1.25 {o1='500'} {500 = o1} 0

97

test_bool types2-1.26 {o1='500'} {'500' = o1} 1

98

test_bool types2-1.27 {o1='500'} {500.0 = o1} 0

99

test_bool types2-1.28 {o1='500'} {'500.0' = o1} 0

100

101

set vals [list 10 10.0 '10' '10.0' 20 20.0 '20' '20.0' 30 30.0 '30' '30.0']

102

# 1 2 3 4 5 6 7 8 9 10 11 12

103

104

execsql {

105

CREATE TABLE t2(i INTEGER, n NUMERIC, t TEXT, o XBLOBY);

106

CREATE INDEX t2i1 ON t2(i);

107

CREATE INDEX t2i2 ON t2(n);

108

CREATE INDEX t2i3 ON t2(t);

109

CREATE INDEX t2i4 ON t2(o);

110

}

111

foreach v $vals {

112

execsql "INSERT INTO t2 VALUES($v, $v, $v, $v);"

113

}

114

115

proc test_boolset {testname where set} {

116

set ::tb_sql "SELECT rowid FROM t2 WHERE $where"

117

do_test $testname {

118

lsort -integer [execsql $::tb_sql]

} $set

}

test_boolset types2-2.1 {i = 10} {1 2 3 4}

123

test_boolset types2-2.2 {i = 10.0} {1 2 3 4}

124

test_boolset types2-2.3 {i = '10'} {1 2 3 4}

125

test_boolset types2-2.4 {i = '10.0'} {1 2 3 4}

126

127

test_boolset types2-2.5 {n = 20} {5 6 7 8}

128

test_boolset types2-2.6 {n = 20.0} {5 6 7 8}

129

test_boolset types2-2.7 {n = '20'} {5 6 7 8}

130

test_boolset types2-2.8 {n = '20.0'} {5 6 7 8}

131

132

test_boolset types2-2.9 {t = 20} {5 7}

133

test_boolset types2-2.10 {t = 20.0} {6 8}

134

test_boolset types2-2.11 {t = '20'} {5 7}

135

test_boolset types2-2.12 {t = '20.0'} {6 8}

136

137

test_boolset types2-2.10 {o = 30} {9 10}

138

test_boolset types2-2.11 {o = 30.0} {9 10}

139

test_boolset types2-2.12 {o = '30'} 11

140

test_boolset types2-2.13 {o = '30.0'} 12

141

142

test_boolset types2-3.1 {i < 20} {1 2 3 4}

143

test_boolset types2-3.2 {i < 20.0} {1 2 3 4}

144

test_boolset types2-3.3 {i < '20'} {1 2 3 4}

145

test_boolset types2-3.4 {i < '20.0'} {1 2 3 4}

146

147

test_boolset types2-3.1 {n < 20} {1 2 3 4}

148

test_boolset types2-3.2 {n < 20.0} {1 2 3 4}

149

test_boolset types2-3.3 {n < '20'} {1 2 3 4}

150

test_boolset types2-3.4 {n < '20.0'} {1 2 3 4}

151

152

test_boolset types2-3.1 {t < 20} {1 2 3 4}

153

test_boolset types2-3.2 {t < 20.0} {1 2 3 4 5 7}

154

test_boolset types2-3.3 {t < '20'} {1 2 3 4}

155

test_boolset types2-3.4 {t < '20.0'} {1 2 3 4 5 7}

156

157

test_boolset types2-3.1 {o < 20} {1 2}

158

test_boolset types2-3.2 {o < 20.0} {1 2}

159

test_boolset types2-3.3 {o < '20'} {1 2 3 4 5 6 9 10}

160

test_boolset types2-3.3 {o < '20.0'} {1 2 3 4 5 6 7 9 10}

161

162

# Compare literals against literals (always a numeric comparison).

163

# Change (by ticket #805): No affinity in comparisons

164

test_bool types2-4.1 "" {500 > 60.0} 1

165

test_bool types2-4.2 "" {'500' > 60.0} 1

166

test_bool types2-4.3 "" {500 > '60.0'} 0

167

test_bool types2-4.4 "" {'500' > '60.0'} 0

168

169

# Compare literals against a column with TEXT affinity

170

test_bool types2-4.5 {t1=500.0} {t1 > 500} 1

171

test_bool types2-4.6 {t1=500.0} {t1 > '500' } 1

172

test_bool types2-4.7 {t1=500.0} {t1 > 500.0 } 0

173

test_bool types2-4.8 {t1=500.0} {t1 > '500.0' } 0

174

test_bool types2-4.9 {t1='500.0'} {t1 > 500 } 1

175

test_bool types2-4.10 {t1='500.0'} {t1 > '500' } 1

176

test_bool types2-4.11 {t1='500.0'} {t1 > 500.0 } 0

177

test_bool types2-4.12 {t1='500.0'} {t1 > '500.0' } 0

178

179

# Compare literals against a column with NUMERIC affinity

180

test_bool types2-4.13 {n1=400} {500 > n1} 1

181

test_bool types2-4.14 {n1=400} {'500' > n1} 1

182

test_bool types2-4.15 {n1=400} {500.0 > n1} 1

183

test_bool types2-4.16 {n1=400} {'500.0' > n1} 1

184

test_bool types2-4.17 {n1='400'} {500 > n1} 1

185

test_bool types2-4.18 {n1='400'} {'500' > n1} 1

186

test_bool types2-4.19 {n1='400'} {500.0 > n1} 1

187

test_bool types2-4.20 {n1='400'} {'500.0' > n1} 1

188

189

# Compare literals against a column with affinity NONE

190

test_bool types2-4.21 {o1=500} {500 > o1} 0

191

test_bool types2-4.22 {o1=500} {'500' > o1} 1

192

test_bool types2-4.23 {o1=500} {500.0 > o1} 0

193

test_bool types2-4.24 {o1=500} {'500.0' > o1} 1

194

test_bool types2-4.25 {o1='500'} {500 > o1} 0

195

test_bool types2-4.26 {o1='500'} {'500' > o1} 0

196

test_bool types2-4.27 {o1='500'} {500.0 > o1} 0

197

test_bool types2-4.28 {o1='500'} {'500.0' > o1} 1

198

199

ifcapable subquery {

200

# types2-5.* - The 'IN (x, y....)' operator with no index.

201

#

202

# Compare literals against literals (no affinity applied)

203

test_bool types2-5.1 {} {(NULL IN ('10.0', 20)) ISNULL} 1

204

test_bool types2-5.2 {} {10 IN ('10.0', 20)} 0

205

test_bool types2-5.3 {} {'10' IN ('10.0', 20)} 0

206

test_bool types2-5.4 {} {10 IN (10.0, 20)} 1

207

test_bool types2-5.5 {} {'10.0' IN (10, 20)} 0

208

209

# Compare literals against a column with TEXT affinity

210

test_bool types2-5.6 {t1='10.0'} {t1 IN (10.0, 20)} 1

211

test_bool types2-5.7 {t1='10.0'} {t1 IN (10, 20)} 0

212

test_bool types2-5.8 {t1='10'} {t1 IN (10.0, 20)} 0

213

test_bool types2-5.9 {t1='10'} {t1 IN (20, '10.0')} 0

214

test_bool types2-5.10 {t1=10} {t1 IN (20, '10')} 1

215

216

# Compare literals against a column with NUMERIC affinity

217

test_bool types2-5.11 {n1='10.0'} {n1 IN (10.0, 20)} 1

218

test_bool types2-5.12 {n1='10.0'} {n1 IN (10, 20)} 1

219

test_bool types2-5.13 {n1='10'} {n1 IN (10.0, 20)} 1

220

test_bool types2-5.14 {n1='10'} {n1 IN (20, '10.0')} 1

221

test_bool types2-5.15 {n1=10} {n1 IN (20, '10')} 1

222

223

# Compare literals against a column with affinity NONE

224

test_bool types2-5.16 {o1='10.0'} {o1 IN (10.0, 20)} 0

225

test_bool types2-5.17 {o1='10.0'} {o1 IN (10, 20)} 0

226

test_bool types2-5.18 {o1='10'} {o1 IN (10.0, 20)} 0

227

test_bool types2-5.19 {o1='10'} {o1 IN (20, '10.0')} 0

228

test_bool types2-5.20 {o1=10} {o1 IN (20, '10')} 0

229

test_bool types2-5.21 {o1='10.0'} {o1 IN (10, 20, '10.0')} 1

230

test_bool types2-5.22 {o1='10'} {o1 IN (10.0, 20, '10')} 1

231

test_bool types2-5.23 {o1=10} {n1 IN (20, '10', 10)} 1

232

233

# Ticket #2248: Comparisons of strings literals that look like

234

# numbers.

235

test_bool types2-5.24 {} {'1' IN ('1')} 1

236

test_bool types2-5.25 {} {'2' IN (2)} 0

237

test_bool types2-5.26 {} {3 IN ('3')} 0

238

test_bool types2-5.27 {} {4 IN (4)} 1

239

240

# The affinity of columns on the right side of IN(...) is ignored.

241

# All values in the expression list are treated as ordinary expressions,

242

# even if they are columns with affinity.

243

test_bool types2-5.30 {t1='10'} {10 IN (5,t1,'abc')} 0

244

test_bool types2-5.31 {t1='10'} {10 IN ('abc',t1,5)} 0

245

test_bool types2-5.32 {t1='010'} {10 IN (5,t1,'abc')} 0

246

test_bool types2-5.33 {t1='010'} {10 IN ('abc',t1,5)} 0

247

test_bool types2-5.34 {t1='10'} {'10' IN (5,t1,'abc')} 1

248

test_bool types2-5.35 {t1='10'} {'10' IN ('abc',t1,5)} 1

249

test_bool types2-5.36 {t1='010'} {'10' IN (5,t1,'abc')} 0

250

test_bool types2-5.37 {t1='010'} {'10' IN ('abc',t1,5)} 0

251

252

# Columns on both the left and right of IN(...). Only the column

253

# on the left matters. The all values on the right are treated like

254

# expressions.

255

test_bool types2-5.40 {t1='10',n1=10} {t1 IN (5,n1,11)} 1

256

test_bool types2-5.41 {t1='010',n1=10} {t1 IN (5,n1,11)} 0

257

test_bool types2-5.42 {t1='10',n1=10} {n1 IN (5,t1,11)} 1

258

test_bool types2-5.43 {t1='010',n1=10} {n1 IN (5,t1,11)} 1

259

}

260

261

# Tests named types2-6.* use the same infrastructure as the types2-2.*

262

# tests. The contents of the vals array is repeated here for easy

263

# reference.

264

#

265

# set vals [list 10 10.0 '10' '10.0' 20 20.0 '20' '20.0' 30 30.0 '30' '30.0']

266

# 1 2 3 4 5 6 7 8 9 10 11 12

267

268

ifcapable subquery {

269

test_boolset types2-6.1 {o IN ('10', 30)} {3 9 10}

270

test_boolset types2-6.2 {o IN (20.0, 30.0)} {5 6 9 10}

271

test_boolset types2-6.3 {t IN ('10', 30)} {1 3 9 11}

272

test_boolset types2-6.4 {t IN (20.0, 30.0)} {6 8 10 12}

273

test_boolset types2-6.5 {n IN ('10', 30)} {1 2 3 4 9 10 11 12}

274

test_boolset types2-6.6 {n IN (20.0, 30.0)} {5 6 7 8 9 10 11 12}

275

test_boolset types2-6.7 {i IN ('10', 30)} {1 2 3 4 9 10 11 12}

276

test_boolset types2-6.8 {i IN (20.0, 30.0)} {5 6 7 8 9 10 11 12}

277

278

# Also test than IN(x, y, z) works on a rowid:

279

test_boolset types2-6.9 {rowid IN (1, 6, 10)} {1 6 10}

280

}

281

282

# Tests types2-7.* concentrate on expressions of the form

283

# "x IN (SELECT...)" with no index.

284

execsql {

285

CREATE TABLE t3(i INTEGER, n NUMERIC, t TEXT, o BLOB);

286

INSERT INTO t3 VALUES(1, 1, 1, 1);

287

INSERT INTO t3 VALUES(2, 2, 2, 2);

288

INSERT INTO t3 VALUES(3, 3, 3, 3);

289

INSERT INTO t3 VALUES('1', '1', '1', '1');

290

INSERT INTO t3 VALUES('1.0', '1.0', '1.0', '1.0');

291

}

292

293

ifcapable subquery {

294

test_bool types2-7.1 {i1=1} {i1 IN (SELECT i FROM t3)} 1

295

test_bool types2-7.2 {i1='2.0'} {i1 IN (SELECT i FROM t3)} 1

296

test_bool types2-7.3 {i1='2.0'} {i1 IN (SELECT n FROM t3)} 1

297

test_bool types2-7.4 {i1='2.0'} {i1 IN (SELECT t FROM t3)} 1

298

test_bool types2-7.5 {i1='2.0'} {i1 IN (SELECT o FROM t3)} 1

299

300

test_bool types2-7.6 {n1=1} {n1 IN (SELECT n FROM t3)} 1

301

test_bool types2-7.7 {n1='2.0'} {n1 IN (SELECT i FROM t3)} 1

302

test_bool types2-7.8 {n1='2.0'} {n1 IN (SELECT n FROM t3)} 1

303

test_bool types2-7.9 {n1='2.0'} {n1 IN (SELECT t FROM t3)} 1

304

test_bool types2-7.10 {n1='2.0'} {n1 IN (SELECT o FROM t3)} 1

305

306

test_bool types2-7.6 {t1=1} {t1 IN (SELECT t FROM t3)} 1

307

test_bool types2-7.7 {t1='2.0'} {t1 IN (SELECT t FROM t3)} 0

308

test_bool types2-7.8 {t1='2.0'} {t1 IN (SELECT n FROM t3)} 1

309

test_bool types2-7.9 {t1='2.0'} {t1 IN (SELECT i FROM t3)} 1

310

test_bool types2-7.10 {t1='2.0'} {t1 IN (SELECT o FROM t3)} 0

311

test_bool types2-7.11 {t1='1.0'} {t1 IN (SELECT t FROM t3)} 1

312

test_bool types2-7.12 {t1='1.0'} {t1 IN (SELECT o FROM t3)} 1

313

314

test_bool types2-7.13 {o1=2} {o1 IN (SELECT o FROM t3)} 1

315

test_bool types2-7.14 {o1='2'} {o1 IN (SELECT o FROM t3)} 0

316

test_bool types2-7.15 {o1='2'} {o1 IN (SELECT o||'' FROM t3)} 1

317

}

318

319

# set vals [list 10 10.0 '10' '10.0' 20 20.0 '20' '20.0' 30 30.0 '30' '30.0']

320

# 1 2 3 4 5 6 7 8 9 10 11 12

321

execsql {

322

CREATE TABLE t4(i INTEGER, n NUMERIC, t VARCHAR(20), o LARGE BLOB);

323

INSERT INTO t4 VALUES(10, 20, 20, 30);

324

}

325

ifcapable subquery {

326

test_boolset types2-8.1 {i IN (SELECT i FROM t4)} {1 2 3 4}

327

test_boolset types2-8.2 {n IN (SELECT i FROM t4)} {1 2 3 4}

328

test_boolset types2-8.3 {t IN (SELECT i FROM t4)} {1 2 3 4}

329

test_boolset types2-8.4 {o IN (SELECT i FROM t4)} {1 2 3 4}

330

test_boolset types2-8.5 {i IN (SELECT t FROM t4)} {5 6 7 8}

331

test_boolset types2-8.6 {n IN (SELECT t FROM t4)} {5 6 7 8}

332

test_boolset types2-8.7 {t IN (SELECT t FROM t4)} {5 7}

333

test_boolset types2-8.8 {o IN (SELECT t FROM t4)} {7}

334

test_boolset types2-8.9 {i IN (SELECT o FROM t4)} {9 10 11 12}

335

test_boolset types2-8.6 {n IN (SELECT o FROM t4)} {9 10 11 12}

336

test_boolset types2-8.7 {t IN (SELECT o FROM t4)} {9 11}

337

test_boolset types2-8.8 {o IN (SELECT o FROM t4)} {9 10}

338

}

339

340

finish_test

wasCSharpSQLite – Blame information for rev 1