Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Overview
Comment: | Add tests to e_expr.test. |
---|---|
Downloads: | Tarball | ZIP archive |
Timelines: | family | ancestors | descendants | both | trunk |
Files: | files | file ages | folders |
SHA1: |
5c1c694ee1b3b71e20089412f6cba184 |
User & Date: | dan 2010-08-24 13:11:53.000 |
Context
2010-08-24
| ||
16:59 | Fixes to allow fts3 tables to be renamed mid-transaction. (check-in: d1c875320a user: dan tags: trunk) | |
13:11 | Add tests to e_expr.test. (check-in: 5c1c694ee1 user: dan tags: trunk) | |
01:49 | The R-tree module should not assume that its shadow tables are consistent. If a problem is found in a shadow table, return SQLITE_CORRUPT. (check-in: 7f2f71cc9e user: drh tags: trunk) | |
Changes
Changes to test/e_expr.test.
︙ | ︙ | |||
1098 1099 1100 1101 1102 1103 1104 1105 1106 | do_execsql_test e_expr-19.2.1 { SELECT 'abc' MATCH 'def' } 1 do_test e_expr-19.2.2 { set matchargs } {def abc} set ::matchargs [list] do_execsql_test e_expr-19.2.3 { SELECT 'X' NOT MATCH 'Y' } 0 do_test e_expr-19.2.4 { set matchargs } {Y X} sqlite3 db test.db finish_test | > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > | 1098 1099 1100 1101 1102 1103 1104 1105 1106 1107 1108 1109 1110 1111 1112 1113 1114 1115 1116 1117 1118 1119 1120 1121 1122 1123 1124 1125 1126 1127 1128 1129 1130 1131 1132 1133 1134 1135 1136 1137 1138 1139 1140 1141 1142 1143 1144 1145 1146 1147 1148 1149 1150 1151 1152 1153 1154 1155 1156 1157 1158 1159 1160 1161 1162 1163 1164 1165 1166 1167 1168 1169 1170 1171 1172 1173 1174 1175 1176 1177 1178 1179 1180 1181 1182 1183 1184 1185 1186 1187 1188 1189 1190 1191 1192 1193 1194 1195 1196 1197 1198 1199 1200 1201 1202 1203 1204 1205 1206 1207 1208 1209 1210 1211 1212 1213 1214 1215 1216 1217 1218 1219 1220 1221 1222 1223 1224 1225 1226 1227 1228 1229 1230 1231 1232 1233 1234 1235 1236 1237 1238 1239 1240 1241 1242 1243 1244 1245 1246 1247 1248 1249 1250 1251 1252 1253 1254 1255 1256 1257 1258 1259 1260 1261 1262 1263 1264 1265 1266 1267 1268 1269 1270 1271 1272 1273 1274 1275 1276 1277 1278 1279 1280 1281 1282 1283 1284 1285 1286 1287 1288 1289 1290 1291 1292 1293 1294 1295 1296 1297 1298 1299 1300 1301 1302 1303 1304 1305 1306 1307 1308 1309 1310 1311 1312 1313 1314 1315 1316 1317 1318 1319 1320 1321 1322 1323 1324 1325 1326 1327 1328 1329 1330 1331 1332 1333 1334 1335 1336 1337 1338 1339 1340 1341 1342 1343 1344 1345 1346 1347 1348 1349 1350 1351 1352 1353 1354 1355 1356 1357 1358 1359 1360 1361 1362 1363 1364 1365 1366 1367 1368 1369 1370 1371 1372 1373 1374 1375 1376 1377 1378 1379 1380 1381 | do_execsql_test e_expr-19.2.1 { SELECT 'abc' MATCH 'def' } 1 do_test e_expr-19.2.2 { set matchargs } {def abc} set ::matchargs [list] do_execsql_test e_expr-19.2.3 { SELECT 'X' NOT MATCH 'Y' } 0 do_test e_expr-19.2.4 { set matchargs } {Y X} sqlite3 db test.db #------------------------------------------------------------------------- # Test cases for the testable statements related to the CASE expression. # # EVIDENCE-OF: R-15199-61389 There are two basic forms of the CASE # expression: those with a base expression and those without. # do_execsql_test e_expr-20.1 { SELECT CASE WHEN 1 THEN 'true' WHEN 0 THEN 'false' ELSE 'else' END; } {true} do_execsql_test e_expr-20.2 { SELECT CASE 0 WHEN 1 THEN 'true' WHEN 0 THEN 'false' ELSE 'else' END; } {false} proc var {nm} { lappend ::varlist $nm return [set "::$nm"] } db func var var # EVIDENCE-OF: R-30638-59954 In a CASE without a base expression, each # WHEN expression is evaluated and the result treated as a boolean, # starting with the leftmost and continuing to the right. # foreach {a b c} {0 0 0} break set varlist [list] do_execsql_test e_expr-21.1.1 { SELECT CASE WHEN var('a') THEN 'A' WHEN var('b') THEN 'B' WHEN var('c') THEN 'C' END } {{}} do_test e_expr-21.1.2 { set varlist } {a b c} set varlist [list] do_execsql_test e_expr-21.1.3 { SELECT CASE WHEN var('c') THEN 'C' WHEN var('b') THEN 'B' WHEN var('a') THEN 'A' ELSE 'no result' END } {{no result}} do_test e_expr-21.1.4 { set varlist } {c b a} # EVIDENCE-OF: R-39009-25596 The result of the CASE expression is the # evaluation of the THEN expression that corresponds to the first WHEN # expression that evaluates to true. # foreach {a b c} {0 1 0} break do_execsql_test e_expr-21.2.1 { SELECT CASE WHEN var('a') THEN 'A' WHEN var('b') THEN 'B' WHEN var('c') THEN 'C' ELSE 'no result' END } {B} foreach {a b c} {0 1 1} break do_execsql_test e_expr-21.2.2 { SELECT CASE WHEN var('a') THEN 'A' WHEN var('b') THEN 'B' WHEN var('c') THEN 'C' ELSE 'no result' END } {B} foreach {a b c} {0 0 1} break do_execsql_test e_expr-21.2.3 { SELECT CASE WHEN var('a') THEN 'A' WHEN var('b') THEN 'B' WHEN var('c') THEN 'C' ELSE 'no result' END } {C} # EVIDENCE-OF: R-24227-04807 Or, if none of the WHEN expressions # evaluate to true, the result of evaluating the ELSE expression, if # any. # foreach {a b c} {0 0 0} break do_execsql_test e_expr-21.3.1 { SELECT CASE WHEN var('a') THEN 'A' WHEN var('b') THEN 'B' WHEN var('c') THEN 'C' ELSE 'no result' END } {{no result}} # EVIDENCE-OF: R-14168-07579 If there is no ELSE expression and none of # the WHEN expressions are true, then the overall result is NULL. # db nullvalue null do_execsql_test e_expr-21.3.2 { SELECT CASE WHEN var('a') THEN 'A' WHEN var('b') THEN 'B' WHEN var('c') THEN 'C' END } {null} db nullvalue {} # EVIDENCE-OF: R-13943-13592 A NULL result is considered untrue when # evaluating WHEN terms. # do_execsql_test e_expr-21.4.1 { SELECT CASE WHEN NULL THEN 'A' WHEN 1 THEN 'B' END } {B} do_execsql_test e_expr-21.4.2 { SELECT CASE WHEN 0 THEN 'A' WHEN NULL THEN 'B' ELSE 'C' END } {C} # EVIDENCE-OF: R-38620-19499 In a CASE with a base expression, the base # expression is evaluated just once and the result is compared against # the evaluation of each WHEN expression from left to right. # # Note: This test case tests the "evaluated just once" part of the above # statement. Tests associated with the next two statements test that the # comparisons take place. # foreach {a b c} [list [expr 3] [expr 4] [expr 5]] break set ::varlist [list] do_execsql_test e_expr-22.1.1 { SELECT CASE var('a') WHEN 1 THEN 'A' WHEN 2 THEN 'B' WHEN 3 THEN 'C' END } {C} do_test e_expr-22.1.2 { set ::varlist } {a} # EVIDENCE-OF: R-07667-49537 The result of the CASE expression is the # evaluation of the THEN expression that corresponds to the first WHEN # expression for which the comparison is true. # do_execsql_test e_expr-22.2.1 { SELECT CASE 23 WHEN 1 THEN 'A' WHEN 23 THEN 'B' WHEN 23 THEN 'C' END } {B} do_execsql_test e_expr-22.2.2 { SELECT CASE 1 WHEN 1 THEN 'A' WHEN 23 THEN 'B' WHEN 23 THEN 'C' END } {A} # EVIDENCE-OF: R-47543-32145 Or, if none of the WHEN expressions # evaluate to a value equal to the base expression, the result of # evaluating the ELSE expression, if any. # do_execsql_test e_expr-22.3.1 { SELECT CASE 24 WHEN 1 THEN 'A' WHEN 23 THEN 'B' WHEN 23 THEN 'C' ELSE 'D' END } {D} # EVIDENCE-OF: R-54721-48557 If there is no ELSE expression and none of # the WHEN expressions produce a result equal to the base expression, # the overall result is NULL. # do_execsql_test e_expr-22.4.1 { SELECT CASE 24 WHEN 1 THEN 'A' WHEN 23 THEN 'B' WHEN 23 THEN 'C' END } {{}} db nullvalue null do_execsql_test e_expr-22.4.2 { SELECT CASE 24 WHEN 1 THEN 'A' WHEN 23 THEN 'B' WHEN 23 THEN 'C' END } {null} db nullvalue {} # EVIDENCE-OF: R-11479-62774 When comparing a base expression against a # WHEN expression, the same collating sequence, affinity, and # NULL-handling rules apply as if the base expression and WHEN # expression are respectively the left- and right-hand operands of an = # operator. # proc rev {str} { set ret "" set chars [split $str] for {set i [expr [llength $chars]-1]} {$i>=0} {incr i -1} { append ret [lindex $chars $i] } set ret } proc reverse {lhs rhs} { string compare [rev $lhs] [ref $rhs] } db collate reverse reverse do_execsql_test e_expr-23.1.1 { CREATE TABLE t1( a TEXT COLLATE NOCASE, b COLLATE REVERSE, c INTEGER, d BLOB ); INSERT INTO t1 VALUES('abc', 'cba', 55, 34.5); } {} do_execsql_test e_expr-23.1.2 { SELECT CASE a WHEN 'xyz' THEN 'A' WHEN 'AbC' THEN 'B' END FROM t1 } {B} do_execsql_test e_expr-23.1.3 { SELECT CASE 'AbC' WHEN 'abc' THEN 'A' WHEN a THEN 'B' END FROM t1 } {B} do_execsql_test e_expr-23.1.4 { SELECT CASE a WHEN b THEN 'A' ELSE 'B' END FROM t1 } {B} do_execsql_test e_expr-23.1.5 { SELECT CASE b WHEN a THEN 'A' ELSE 'B' END FROM t1 } {A} do_execsql_test e_expr-23.1.6 { SELECT CASE 55 WHEN '55' THEN 'A' ELSE 'B' END } {B} do_execsql_test e_expr-23.1.7 { SELECT CASE c WHEN '55' THEN 'A' ELSE 'B' END FROM t1 } {A} do_execsql_test e_expr-23.1.8 { SELECT CASE '34.5' WHEN d THEN 'A' ELSE 'B' END FROM t1 } {B} do_execsql_test e_expr-23.1.9 { SELECT CASE NULL WHEN NULL THEN 'A' ELSE 'B' END } {B} # EVIDENCE-OF: R-37304-39405 If the base expression is NULL then the # result of the CASE is always the result of evaluating the ELSE # expression if it exists, or NULL if it does not. # do_execsql_test e_expr-24.1.1 { SELECT CASE NULL WHEN 'abc' THEN 'A' WHEN 'def' THEN 'B' END; } {{}} do_execsql_test e_expr-24.1.2 { SELECT CASE NULL WHEN 'abc' THEN 'A' WHEN 'def' THEN 'B' ELSE 'C' END; } {C} # EVIDENCE-OF: R-56280-17369 Both forms of the CASE expression use lazy, # or short-circuit, evaluation. # set varlist [list] foreach {a b c} {0 1 0} break do_execsql_test e_expr-25.1.1 { SELECT CASE WHEN var('a') THEN 'A' WHEN var('b') THEN 'B' WHEN var('c') THEN 'C' END } {B} do_test e_expr-25.1.2 { set ::varlist } {a b} set varlist [list] do_execsql_test e_expr-25.1.3 { SELECT CASE '0' WHEN var('a') THEN 'A' WHEN var('b') THEN 'B' WHEN var('c') THEN 'C' END } {A} do_test e_expr-25.1.4 { set ::varlist } {a} # EVIDENCE-OF: R-34773-62253 The only difference between the following # two CASE expressions is that the x expression is evaluated exactly # once in the first example but might be evaluated multiple times in the # second: CASE x WHEN w1 THEN r1 WHEN w2 THEN r2 ELSE r3 END CASE WHEN # x=w1 THEN r1 WHEN x=w2 THEN r2 ELSE r3 END # proc ceval {x} { incr ::evalcount return $x } db func ceval ceval set ::evalcount 0 do_execsql_test e_expr-26.1.1 { CREATE TABLE t2(x, w1, r1, w2, r2, r3); INSERT INTO t2 VALUES(1, 1, 'R1', 2, 'R2', 'R3'); INSERT INTO t2 VALUES(2, 1, 'R1', 2, 'R2', 'R3'); INSERT INTO t2 VALUES(3, 1, 'R1', 2, 'R2', 'R3'); } {} do_execsql_test e_expr-26.1.2 { SELECT CASE x WHEN w1 THEN r1 WHEN w2 THEN r2 ELSE r3 END FROM t2 } {R1 R2 R3} do_execsql_test e_expr-26.1.3 { SELECT CASE WHEN x=w1 THEN r1 WHEN x=w2 THEN r2 ELSE r3 END FROM t2 } {R1 R2 R3} do_execsql_test e_expr-26.1.4 { SELECT CASE ceval(x) WHEN w1 THEN r1 WHEN w2 THEN r2 ELSE r3 END FROM t2 } {R1 R2 R3} do_test e_expr-26.1.5 { set ::evalcount } {3} set ::evalcount 0 do_execsql_test e_expr-26.1.6 { SELECT CASE WHEN ceval(x)=w1 THEN r1 WHEN ceval(x)=w2 THEN r2 ELSE r3 END FROM t2 } {R1 R2 R3} do_test e_expr-26.1.6 { set ::evalcount } {5} finish_test |