Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Overview
| Comment: | Modify testable statement ids in a few test files to account for recent docsrc changes. |
|---|---|
| Downloads: | Tarball | ZIP archive | SQL archive |
| Timelines: | family | ancestors | descendants | both | trunk |
| Files: | files | file ages | folders |
| SHA1: |
7893e525953da6c97eaea23fe94d26e1 |
| User & Date: | dan 2010-09-24 08:00:11 |
Context
|
2010-09-24
| ||
| 18:08 | Merge experimental branch back into trunk. (check-in: 83ecec5d user: dan tags: trunk) | |
| 09:32 | Add experimental branch disallowing VACUUM when there are one or more active SQL statements. (check-in: c1ebcacd user: dan tags: experimental) | |
| 08:00 | Modify testable statement ids in a few test files to account for recent docsrc changes. (check-in: 7893e525 user: dan tags: trunk) | |
|
2010-09-23
| ||
| 18:47 | Add tests to e_delete.test. (check-in: fab3b383 user: dan tags: trunk) | |
Changes
Changes to test/e_delete.test.
62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 |
}
} {}
do_delete_tests e_delete-1.1 {
1 "DELETE FROM t1 ; SELECT * FROM t1" {}
2 "DELETE FROM main.t2 ; SELECT * FROM t2" {}
}
# EVIDENCE-OF: R-25092-63878 If a WHERE clause is supplied, then only
# those rows for which evaluating the WHERE clause and casting the
# result to a NUMERIC value produces a result other than NULL or zero
# (integer value 0 or real value 0.0).
#
do_delete_tests e_delete-1.2 {
1 "DELETE FROM t3 WHERE 1 ; SELECT x FROM t3" {}
2 "DELETE FROM main.t4 WHERE 0 ; SELECT x FROM t4" {1 2 3 4 5}
3 "DELETE FROM t4 WHERE 0.0 ; SELECT x FROM t4" {1 2 3 4 5}
4 "DELETE FROM t4 WHERE NULL ; SELECT x FROM t4" {1 2 3 4 5}
5 "DELETE FROM t4 WHERE y!='two'; SELECT x FROM t4" {2}
|
| | | < |
62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 |
}
} {}
do_delete_tests e_delete-1.1 {
1 "DELETE FROM t1 ; SELECT * FROM t1" {}
2 "DELETE FROM main.t2 ; SELECT * FROM t2" {}
}
# EVIDENCE-OF: R-30203-16177 If a WHERE clause is supplied, then only
# those rows for which the result of evaluating the WHERE clause as a
# boolean expression is true are deleted.
#
do_delete_tests e_delete-1.2 {
1 "DELETE FROM t3 WHERE 1 ; SELECT x FROM t3" {}
2 "DELETE FROM main.t4 WHERE 0 ; SELECT x FROM t4" {1 2 3 4 5}
3 "DELETE FROM t4 WHERE 0.0 ; SELECT x FROM t4" {1 2 3 4 5}
4 "DELETE FROM t4 WHERE NULL ; SELECT x FROM t4" {1 2 3 4 5}
5 "DELETE FROM t4 WHERE y!='two'; SELECT x FROM t4" {2}
|
Changes to test/e_select.test.
503 504 505 506 507 508 509 510 511 512 513 514 515 516 517 518 519 520 521 ... 542 543 544 545 546 547 548 549 550 551 552 553 554 555 556 557 558 559 560 .... 1056 1057 1058 1059 1060 1061 1062 1063 1064 1065 1066 1067 1068 1069 1070 1071 1072 1073 1074 .... 1287 1288 1289 1290 1291 1292 1293 1294 1295 1296 1297 1298 1299 1300 1301 1302 1303 1304 1305 .... 1623 1624 1625 1626 1627 1628 1629 1630 1631 1632 1633 1634 1635 1636 1637 1638 1639 1640 |
1 { SELECT * FROM t1 CROSS JOIN t2 } $t1_cross_t2 \
2 { SELECT * FROM t1 AS y CROSS JOIN t1 AS x } $t1_cross_t1 \
3 { SELECT * FROM t1 INNER JOIN t2 } $t1_cross_t2 \
4 { SELECT * FROM t1 AS y INNER JOIN t1 AS x } $t1_cross_t1 \
]
# EVIDENCE-OF: R-45641-53865 If there is an ON clause specified, then
# the ON expression is evaluated for each row of the cartesian product
# and the result cast to a numeric value as if by a CAST expression. All
# rows for which the expression evaluates to NULL or zero (integer value
# 0 or real value 0.0) are excluded from the dataset.
#
foreach {tn select res} [list \
1 { SELECT * FROM t1 %JOIN% t2 ON (1) } $t1_cross_t2 \
2 { SELECT * FROM t1 %JOIN% t2 ON (0) } [list] \
3 { SELECT * FROM t1 %JOIN% t2 ON (NULL) } [list] \
4 { SELECT * FROM t1 %JOIN% t2 ON ('abc') } [list] \
5 { SELECT * FROM t1 %JOIN% t2 ON ('1ab') } $t1_cross_t2 \
................................................................................
cannot join using column %s - column not present in both tables
} {
1 { SELECT * FROM t1, t3 USING (b) } "b"
2 { SELECT * FROM t3, t1 USING (c) } "c"
3 { SELECT * FROM t3, (SELECT a AS b, b AS c FROM t1) USING (a) } "a"
}
# EVIDENCE-OF: R-42568-37000 For each pair of namesake columns, the
# expression "lhs.X = rhs.X" is evaluated for each row of the cartesian
# product and the result cast to a numeric value. All rows for which one
# or more of the expressions evaluates to NULL or zero are excluded from
# the result set.
#
do_select_tests e_select-1.5 {
1 { SELECT * FROM t1, t3 USING (a) } {a one 1 b two 2}
2 { SELECT * FROM t3, t4 USING (a,c) } {b 2}
}
# EVIDENCE-OF: R-54046-48600 When comparing values as a result of a
................................................................................
test_join $tn.1.7 "t2 CROSS JOIN t3" {t2 t3}
test_join $tn.1.8 "t2 JOIN t3" {t2 t3}
test_join $tn.1.9 "t2, t2 AS x" {t2 t2}
test_join $tn.1.10 "t2 INNER JOIN t2 AS x" {t2 t2}
test_join $tn.1.11 "t2 CROSS JOIN t2 AS x" {t2 t2}
test_join $tn.1.12 "t2 JOIN t2 AS x" {t2 t2}
# EVIDENCE-OF: R-45641-53865 If there is an ON clause specified, then
# the ON expression is evaluated for each row of the cartesian product
# and the result cast to a numeric value as if by a CAST expression. All
# rows for which the expression evaluates to NULL or zero (integer value
# 0 or real value 0.0) are excluded from the dataset.
#
test_join $tn.2.1 "t1, t2 ON (t1.a=t2.a)" {t1 t2 -on {te_equals a a}}
test_join $tn.2.2 "t2, t1 ON (t1.a=t2.a)" {t2 t1 -on {te_equals a a}}
test_join $tn.2.3 "t2, t1 ON (1)" {t2 t1 -on te_true}
test_join $tn.2.4 "t2, t1 ON (NULL)" {t2 t1 -on te_false}
test_join $tn.2.5 "t2, t1 ON (1.1-1.1)" {t2 t1 -on te_false}
test_join $tn.2.6 "t1, t2 ON (1.1-1.0)" {t1 t2 -on te_true}
................................................................................
CREATE TABLE x2(k, x, y2);
INSERT INTO x2 VALUES(1, 50, X'B82838');
INSERT INTO x2 VALUES(5, 84.79, 65.88);
INSERT INTO x2 VALUES(3, -22, X'0E1BE452A393');
INSERT INTO x2 VALUES(7, 'mistrusted', 'standardized');
} {}
# EVIDENCE-OF: R-22873-49686 If a WHERE clause is specified, the WHERE
# expression is evaluated for each row in the input data and the result
# cast to a numeric value. All rows for which the WHERE clause
# expression evaluates to a NULL value or to zero (integer value 0 or
# real value 0.0) are excluded from the dataset before continuing.
#
do_execsql_test e_select-3.1.1 { SELECT k FROM x1 WHERE x } {3}
do_execsql_test e_select-3.1.2 { SELECT k FROM x1 WHERE y } {3 5 6}
do_execsql_test e_select-3.1.3 { SELECT k FROM x1 WHERE z } {1 2 6}
do_execsql_test e_select-3.1.4 { SELECT k FROM x1 WHERE '1'||z } {1 2 4 6}
do_execsql_test e_select-3.1.5 { SELECT k FROM x1 WHERE x IS NULL } {4 5}
do_execsql_test e_select-3.1.6 { SELECT k FROM x1 WHERE z - 78.43 } {2 4 6}
................................................................................
12.2 "SELECT max(a) FROM b3 GROUP BY max(b)"
12.3 "SELECT group_concat(a) FROM b3 GROUP BY a, max(b)"
} {
set res {1 {aggregate functions are not allowed in the GROUP BY clause}}
do_catchsql_test e_select-4.$tn $select $res
}
# EVIDENCE-OF: R-40359-04817 If a HAVING clause is specified, it is
# evaluated once for each group of rows and cast to an integer value. If
# the result of evaluating the HAVING clause is NULL or zero (integer
# value 0), the group is discarded.
#
# This requirement is tested by all e_select-4.13.* tests.
#
# EVIDENCE-OF: R-04132-09474 If the HAVING clause is an aggregate
# expression, it is evaluated across all rows in the group.
#
# Tested by e_select-4.13.1.*
|
| < | | | | | < | < | | | | | < | | | | | |
503 504 505 506 507 508 509 510 511 512 513 514 515 516 517 518 519 520 ... 541 542 543 544 545 546 547 548 549 550 551 552 553 554 555 556 557 558 .... 1054 1055 1056 1057 1058 1059 1060 1061 1062 1063 1064 1065 1066 1067 1068 1069 1070 1071 .... 1284 1285 1286 1287 1288 1289 1290 1291 1292 1293 1294 1295 1296 1297 1298 1299 1300 1301 .... 1619 1620 1621 1622 1623 1624 1625 1626 1627 1628 1629 1630 1631 1632 1633 1634 1635 1636 |
1 { SELECT * FROM t1 CROSS JOIN t2 } $t1_cross_t2 \
2 { SELECT * FROM t1 AS y CROSS JOIN t1 AS x } $t1_cross_t1 \
3 { SELECT * FROM t1 INNER JOIN t2 } $t1_cross_t2 \
4 { SELECT * FROM t1 AS y INNER JOIN t1 AS x } $t1_cross_t1 \
]
# EVIDENCE-OF: R-22775-56496 If there is an ON clause specified, then
# the ON expression is evaluated for each row of the cartesian product
# as a boolean expression. All rows for which the expression evaluates
# to false are excluded from the dataset.
#
foreach {tn select res} [list \
1 { SELECT * FROM t1 %JOIN% t2 ON (1) } $t1_cross_t2 \
2 { SELECT * FROM t1 %JOIN% t2 ON (0) } [list] \
3 { SELECT * FROM t1 %JOIN% t2 ON (NULL) } [list] \
4 { SELECT * FROM t1 %JOIN% t2 ON ('abc') } [list] \
5 { SELECT * FROM t1 %JOIN% t2 ON ('1ab') } $t1_cross_t2 \
................................................................................
cannot join using column %s - column not present in both tables
} {
1 { SELECT * FROM t1, t3 USING (b) } "b"
2 { SELECT * FROM t3, t1 USING (c) } "c"
3 { SELECT * FROM t3, (SELECT a AS b, b AS c FROM t1) USING (a) } "a"
}
# EVIDENCE-OF: R-55987-04584 For each pair of namesake columns, the
# expression "lhs.X = rhs.X" is evaluated for each row of the cartesian
# product as a boolean expression. All rows for which one or more of the
# expressions evaluates to false are excluded from the result set.
#
do_select_tests e_select-1.5 {
1 { SELECT * FROM t1, t3 USING (a) } {a one 1 b two 2}
2 { SELECT * FROM t3, t4 USING (a,c) } {b 2}
}
# EVIDENCE-OF: R-54046-48600 When comparing values as a result of a
................................................................................
test_join $tn.1.7 "t2 CROSS JOIN t3" {t2 t3}
test_join $tn.1.8 "t2 JOIN t3" {t2 t3}
test_join $tn.1.9 "t2, t2 AS x" {t2 t2}
test_join $tn.1.10 "t2 INNER JOIN t2 AS x" {t2 t2}
test_join $tn.1.11 "t2 CROSS JOIN t2 AS x" {t2 t2}
test_join $tn.1.12 "t2 JOIN t2 AS x" {t2 t2}
# EVIDENCE-OF: R-22775-56496 If there is an ON clause specified, then
# the ON expression is evaluated for each row of the cartesian product
# as a boolean expression. All rows for which the expression evaluates
# to false are excluded from the dataset.
#
test_join $tn.2.1 "t1, t2 ON (t1.a=t2.a)" {t1 t2 -on {te_equals a a}}
test_join $tn.2.2 "t2, t1 ON (t1.a=t2.a)" {t2 t1 -on {te_equals a a}}
test_join $tn.2.3 "t2, t1 ON (1)" {t2 t1 -on te_true}
test_join $tn.2.4 "t2, t1 ON (NULL)" {t2 t1 -on te_false}
test_join $tn.2.5 "t2, t1 ON (1.1-1.1)" {t2 t1 -on te_false}
test_join $tn.2.6 "t1, t2 ON (1.1-1.0)" {t1 t2 -on te_true}
................................................................................
CREATE TABLE x2(k, x, y2);
INSERT INTO x2 VALUES(1, 50, X'B82838');
INSERT INTO x2 VALUES(5, 84.79, 65.88);
INSERT INTO x2 VALUES(3, -22, X'0E1BE452A393');
INSERT INTO x2 VALUES(7, 'mistrusted', 'standardized');
} {}
# EVIDENCE-OF: R-06999-14330 If a WHERE clause is specified, the WHERE
# expression is evaluated for each row in the input data as a boolean
# expression. All rows for which the WHERE clause expression evaluates
# to false are excluded from the dataset before continuing.
#
do_execsql_test e_select-3.1.1 { SELECT k FROM x1 WHERE x } {3}
do_execsql_test e_select-3.1.2 { SELECT k FROM x1 WHERE y } {3 5 6}
do_execsql_test e_select-3.1.3 { SELECT k FROM x1 WHERE z } {1 2 6}
do_execsql_test e_select-3.1.4 { SELECT k FROM x1 WHERE '1'||z } {1 2 4 6}
do_execsql_test e_select-3.1.5 { SELECT k FROM x1 WHERE x IS NULL } {4 5}
do_execsql_test e_select-3.1.6 { SELECT k FROM x1 WHERE z - 78.43 } {2 4 6}
................................................................................
12.2 "SELECT max(a) FROM b3 GROUP BY max(b)"
12.3 "SELECT group_concat(a) FROM b3 GROUP BY a, max(b)"
} {
set res {1 {aggregate functions are not allowed in the GROUP BY clause}}
do_catchsql_test e_select-4.$tn $select $res
}
# EVIDENCE-OF: R-31537-00101 If a HAVING clause is specified, it is
# evaluated once for each group of rows as a boolean expression. If the
# result of evaluating the HAVING clause is false, the group is
# discarded.
#
# This requirement is tested by all e_select-4.13.* tests.
#
# EVIDENCE-OF: R-04132-09474 If the HAVING clause is an aggregate
# expression, it is evaluated across all rows in the group.
#
# Tested by e_select-4.13.1.*
|
Changes to test/e_update.test.
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
...
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
|
1 "UPDATE t1 SET b = 'roman' ; SELECT * FROM t1"
{1 roman 2 roman 3 roman}
2 "UPDATE t1 SET a = 'greek' ; SELECT * FROM t1"
{greek roman greek roman greek roman}
}
# EVIDENCE-OF: R-41754-00978 Otherwise, the UPDATE affects only those
# rows for which evaluating the WHERE clause expression and casting the
# result to a NUMERIC value produces a value other than NULL or zero
# (integer value 0 or real value 0.0).
#
do_execsql_test e_update-1.3.0 {
DELETE FROM main.t1;
INSERT INTO main.t1 VALUES(NULL, '');
INSERT INTO main.t1 VALUES(1, 'i');
INSERT INTO main.t1 VALUES(2, 'ii');
INSERT INTO main.t1 VALUES(3, 'iii');
................................................................................
3 "UPDATE t1 SET b = 'macedonian' WHERE a; SELECT * FROM t1"
{{} {} 1 macedonian 2 macedonian 3 macedonian}
4 "UPDATE t1 SET b = 'lithuanian' WHERE a IS NULL; SELECT * FROM t1"
{{} lithuanian 1 macedonian 2 macedonian 3 macedonian}
}
# EVIDENCE-OF: R-61178-36001 It is not an error if the WHERE clause does
# not evaluate to a non-NULL, non-zero value for any row in the table -
# this just means that the UPDATE statement affects zero rows.
#
do_execsql_test e_update-1.4.0 {
DELETE FROM main.t1;
INSERT INTO main.t1 VALUES(NULL, '');
INSERT INTO main.t1 VALUES(1, 'i');
INSERT INTO main.t1 VALUES(2, 'ii');
INSERT INTO main.t1 VALUES(3, 'iii');
|
|
|
|
<
|
|
|
|
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
...
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
|
1 "UPDATE t1 SET b = 'roman' ; SELECT * FROM t1"
{1 roman 2 roman 3 roman}
2 "UPDATE t1 SET a = 'greek' ; SELECT * FROM t1"
{greek roman greek roman greek roman}
}
# EVIDENCE-OF: R-42117-40023 Otherwise, the UPDATE affects only those
# rows for which the result of evaluating the WHERE clause expression as
# a boolean expression is true.
#
do_execsql_test e_update-1.3.0 {
DELETE FROM main.t1;
INSERT INTO main.t1 VALUES(NULL, '');
INSERT INTO main.t1 VALUES(1, 'i');
INSERT INTO main.t1 VALUES(2, 'ii');
INSERT INTO main.t1 VALUES(3, 'iii');
................................................................................
3 "UPDATE t1 SET b = 'macedonian' WHERE a; SELECT * FROM t1"
{{} {} 1 macedonian 2 macedonian 3 macedonian}
4 "UPDATE t1 SET b = 'lithuanian' WHERE a IS NULL; SELECT * FROM t1"
{{} lithuanian 1 macedonian 2 macedonian 3 macedonian}
}
# EVIDENCE-OF: R-58129-20729 It is not an error if the WHERE clause does
# not evaluate to true for any row in the table - this just means that
# the UPDATE statement affects zero rows.
#
do_execsql_test e_update-1.4.0 {
DELETE FROM main.t1;
INSERT INTO main.t1 VALUES(NULL, '');
INSERT INTO main.t1 VALUES(1, 'i');
INSERT INTO main.t1 VALUES(2, 'ii');
INSERT INTO main.t1 VALUES(3, 'iii');
|