Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Overview
| Comment: | Update some requirements marks to conform with improvements in the documentation. |
|---|---|
| Downloads: | Tarball | ZIP archive | SQL archive |
| Timelines: | family | ancestors | descendants | both | trunk |
| Files: | files | file ages | folders |
| SHA1: |
be1acb610f7e594b417dd8409b7a7aa8 |
| User & Date: | drh 2014-01-27 15:02:07 |
Context
|
2014-01-27
| ||
| 16:35 | Fix a couple of problems with new test scripts causing the permutations test to fail. (check-in: 48d736c0 user: dan tags: trunk) | |
| 15:02 | Update some requirements marks to conform with improvements in the documentation. (check-in: be1acb61 user: drh tags: trunk) | |
| 14:19 | Fix a problem in pagerfault.test causing an assert() to fail. (check-in: ba8d2d50 user: dan tags: trunk) | |
Changes
Changes to test/e_select.test.
329 330 331 332 333 334 335 336 337 338 339 340 341 342 343 344 345 ... 353 354 355 356 357 358 359 360 361 362 363 364 365 366 367 368 369 370 ... 379 380 381 382 383 384 385 386 387 388 389 390 391 392 393 394 395 396 ... 509 510 511 512 513 514 515 516 517 518 519 520 521 522 523 524 525 526 527 ... 536 537 538 539 540 541 542 543 544 545 546 547 548 549 550 551 552 553 554 555 556 557 558 559 560 561 562 563 564 565 566 567 568 569 570 571 572 573 574 575 576 577 ... 618 619 620 621 622 623 624 625 626 627 628 629 630 631 632 633 634 635 ... 656 657 658 659 660 661 662 663 664 665 666 667 668 669 670 671 |
2 "SELECT 'abc' WHERE NULL" {}
3 "SELECT NULL" {{}}
4 "SELECT count(*)" {1}
5 "SELECT count(*) WHERE 0" {0}
6 "SELECT count(*) WHERE 1" {1}
}
# EVIDENCE-OF: R-48114-33255 If there is only a single table in the
# join-source following the FROM clause, then the input data used by the
# SELECT statement is the contents of the named table.
#
# The results of the SELECT queries suggest that they are operating on the
# contents of the table 'xx'.
#
do_execsql_test e_select-1.2.0 {
CREATE TABLE xx(x, y);
INSERT INTO xx VALUES('IiJlsIPepMuAhU', X'10B00B897A15BAA02E3F98DCE8F2');
................................................................................
-17.89 'linguistically'
}
2 "SELECT count(*), count(x), count(y) FROM xx" {3 2 3}
3 "SELECT sum(x), sum(y) FROM xx" {-17.89 -16.87}
}
# EVIDENCE-OF: R-23593-12456 If there is more than one table specified
# as part of the join-source following the FROM keyword, then the
# contents of each named table are joined into a single dataset for the
# simple SELECT statement to operate on.
#
# There are more detailed tests for subsequent requirements that add
# more detail to this idea. We just add a single test that shows that
# data is coming from each of the three tables following the FROM clause
# here to show that the statement, vague as it is, is not incorrect.
#
do_select_tests e_select-1.3 {
................................................................................
}
#
# The following block of tests - e_select-1.4.* - test that the description
# of cartesian joins in the SELECT documentation is consistent with SQLite.
# In doing so, we test the following three requirements as a side-effect:
#
# EVIDENCE-OF: R-46122-14930 If the join-op is "CROSS JOIN", "INNER
# JOIN", "JOIN" or a comma (",") and there is no ON or USING clause,
# then the result of the join is simply the cartesian product of the
# left and right-hand datasets.
#
# The tests are built on this assertion. Really, they test that the output
# of a CROSS JOIN, JOIN, INNER JOIN or "," join matches the expected result
# of calculating the cartesian product of the left and right-hand datasets.
#
# EVIDENCE-OF: R-46256-57243 There is no difference between the "INNER
# JOIN", "JOIN" and "," join operators.
................................................................................
do_select_tests e_select-1.4.5 [list \
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 \
................................................................................
11 { SELECT t1.b, t2.b
FROM t1 %JOIN% t2 ON (CASE WHEN t1.a = 'a' THEN NULL ELSE 1 END) } \
{two I two II two III three I three II three III} \
] {
do_join_test e_select-1.3.$tn $select $res
}
# EVIDENCE-OF: R-63358-54862 If there is a USING clause specified as
# part of the join-constraint, then each of the column names specified
# must exist in the datasets to both the left and right of the join-op.
#
do_select_tests e_select-1.4 -error {
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
# USING clause, the normal rules for handling affinities, collation
# sequences and NULL values in comparisons apply.
#
# EVIDENCE-OF: R-35466-18578 The column from the dataset on the
# left-hand side of the join operator is considered to be on the
# left-hand side of the comparison operator (=) for the purposes of
# collation sequence and affinity precedence.
#
do_execsql_test e_select-1.6.0 {
CREATE TABLE t5(a COLLATE nocase, b COLLATE binary);
INSERT INTO t5 VALUES('AA', 'cc');
INSERT INTO t5 VALUES('BB', 'dd');
................................................................................
{aa cc cc bb DD dd}
4b { SELECT * FROM (SELECT a COLLATE nocase, b FROM t6) AS x
%JOIN% t5 ON (x.a=t5.a) }
{aa cc AA cc bb DD BB dd}
} {
do_join_test e_select-1.7.$tn $select $res
}
# EVIDENCE-OF: R-41434-12448 If the join-op is a "LEFT JOIN" or "LEFT
# OUTER JOIN", then after the ON or USING filtering clauses have been
# applied, an extra row is added to the output for each row in the
# original left-hand input dataset that corresponds to no rows at all in
# the composite dataset (if any).
#
do_execsql_test e_select-1.8.0 {
CREATE TABLE t7(a, b, c);
CREATE TABLE t8(a, d, e);
................................................................................
1a "SELECT * FROM t7 JOIN t8 ON (t7.a=t8.a)" {x ex 24 x abc 24}
1b "SELECT * FROM t7 LEFT JOIN t8 ON (t7.a=t8.a)"
{x ex 24 x abc 24 y why 25 {} {} {}}
2a "SELECT * FROM t7 JOIN t8 USING (a)" {x ex 24 abc 24}
2b "SELECT * FROM t7 LEFT JOIN t8 USING (a)" {x ex 24 abc 24 y why 25 {} {}}
}
# EVIDENCE-OF: R-01809-52134 If the NATURAL keyword is added to any of
# the join-ops, then an implicit USING clause is added to the
# join-constraints. The implicit USING clause contains each of the
# column names that appear in both the left and right-hand input
# datasets.
#
do_select_tests e_select-1-10 {
1a "SELECT * FROM t7 JOIN t8 USING (a)" {x ex 24 abc 24}
1b "SELECT * FROM t7 NATURAL JOIN t8" {x ex 24 abc 24}
|
| | | | | | | | | | | < < > | | | | | | | | | | | < < > | | | | |
329 330 331 332 333 334 335 336 337 338 339 340 341 342 343 344 345 ... 353 354 355 356 357 358 359 360 361 362 363 364 365 366 367 368 369 370 ... 379 380 381 382 383 384 385 386 387 388 389 390 391 392 393 394 395 396 ... 509 510 511 512 513 514 515 516 517 518 519 520 521 522 523 524 525 526 ... 535 536 537 538 539 540 541 542 543 544 545 546 547 548 549 550 551 552 553 554 555 556 557 558 559 560 561 562 563 564 565 566 567 568 569 570 571 572 573 574 575 576 ... 617 618 619 620 621 622 623 624 625 626 627 628 629 630 631 632 633 ... 654 655 656 657 658 659 660 661 662 663 664 665 666 667 668 669 |
2 "SELECT 'abc' WHERE NULL" {}
3 "SELECT NULL" {{}}
4 "SELECT count(*)" {1}
5 "SELECT count(*) WHERE 0" {0}
6 "SELECT count(*) WHERE 1" {1}
}
# EVIDENCE-OF: R-45424-07352 If there is only a single table or subquery
# in the FROM clause, then the input data used by the SELECT statement
# is the contents of the named table.
#
# The results of the SELECT queries suggest that they are operating on the
# contents of the table 'xx'.
#
do_execsql_test e_select-1.2.0 {
CREATE TABLE xx(x, y);
INSERT INTO xx VALUES('IiJlsIPepMuAhU', X'10B00B897A15BAA02E3F98DCE8F2');
................................................................................
-17.89 'linguistically'
}
2 "SELECT count(*), count(x), count(y) FROM xx" {3 2 3}
3 "SELECT sum(x), sum(y) FROM xx" {-17.89 -16.87}
}
# EVIDENCE-OF: R-28355-09804 If there is more than one table or subquery
# in FROM clause then the contents of all tables and/or subqueries are
# joined into a single dataset for the simple SELECT statement to
# operate on.
#
# There are more detailed tests for subsequent requirements that add
# more detail to this idea. We just add a single test that shows that
# data is coming from each of the three tables following the FROM clause
# here to show that the statement, vague as it is, is not incorrect.
#
do_select_tests e_select-1.3 {
................................................................................
}
#
# The following block of tests - e_select-1.4.* - test that the description
# of cartesian joins in the SELECT documentation is consistent with SQLite.
# In doing so, we test the following three requirements as a side-effect:
#
# EVIDENCE-OF: R-49872-03192 If the join-operator is "CROSS JOIN",
# "INNER JOIN", "JOIN" or a comma (",") and there is no ON or USING
# clause, then the result of the join is simply the cartesian product of
# the left and right-hand datasets.
#
# The tests are built on this assertion. Really, they test that the output
# of a CROSS JOIN, JOIN, INNER JOIN or "," join matches the expected result
# of calculating the cartesian product of the left and right-hand datasets.
#
# EVIDENCE-OF: R-46256-57243 There is no difference between the "INNER
# JOIN", "JOIN" and "," join operators.
................................................................................
do_select_tests e_select-1.4.5 [list \
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-38465-03616 If there is an ON clause then the ON
# expression is evaluated for each row of the cartesian product as a
# boolean expression. Only rows for which the expression evaluates to
# true are included 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 \
................................................................................
11 { SELECT t1.b, t2.b
FROM t1 %JOIN% t2 ON (CASE WHEN t1.a = 'a' THEN NULL ELSE 1 END) } \
{two I two II two III three I three II three III} \
] {
do_join_test e_select-1.3.$tn $select $res
}
# EVIDENCE-OF: R-49933-05137 If there is a USING clause then each of the
# column names specified must exist in the datasets to both the left and
# right of the join-operator.
#
do_select_tests e_select-1.4 -error {
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-22776-52830 For each pair of named columns, the
# expression "lhs.X = rhs.X" is evaluated for each row of the cartesian
# product as a boolean expression. Only rows for which all such
# expressions evaluates to true are included 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
# USING clause, the normal rules for handling affinities, collation
# sequences and NULL values in comparisons apply.
#
# EVIDENCE-OF: R-38422-04402 The column from the dataset on the
# left-hand side of the join-operator is considered to be on the
# left-hand side of the comparison operator (=) for the purposes of
# collation sequence and affinity precedence.
#
do_execsql_test e_select-1.6.0 {
CREATE TABLE t5(a COLLATE nocase, b COLLATE binary);
INSERT INTO t5 VALUES('AA', 'cc');
INSERT INTO t5 VALUES('BB', 'dd');
................................................................................
{aa cc cc bb DD dd}
4b { SELECT * FROM (SELECT a COLLATE nocase, b FROM t6) AS x
%JOIN% t5 ON (x.a=t5.a) }
{aa cc AA cc bb DD BB dd}
} {
do_join_test e_select-1.7.$tn $select $res
}
# EVIDENCE-OF: R-42531-52874 If the join-operator is a "LEFT JOIN" or
# "LEFT OUTER JOIN", then after the ON or USING filtering clauses have
# been applied, an extra row is added to the output for each row in the
# original left-hand input dataset that corresponds to no rows at all in
# the composite dataset (if any).
#
do_execsql_test e_select-1.8.0 {
CREATE TABLE t7(a, b, c);
CREATE TABLE t8(a, d, e);
................................................................................
1a "SELECT * FROM t7 JOIN t8 ON (t7.a=t8.a)" {x ex 24 x abc 24}
1b "SELECT * FROM t7 LEFT JOIN t8 ON (t7.a=t8.a)"
{x ex 24 x abc 24 y why 25 {} {} {}}
2a "SELECT * FROM t7 JOIN t8 USING (a)" {x ex 24 abc 24}
2b "SELECT * FROM t7 LEFT JOIN t8 USING (a)" {x ex 24 abc 24 y why 25 {} {}}
}
# EVIDENCE-OF: R-04932-55942 If the NATURAL keyword is in the
# join-operator then an implicit USING clause is added to the
# join-constraints. The implicit USING clause contains each of the
# column names that appear in both the left and right-hand input
# datasets.
#
do_select_tests e_select-1-10 {
1a "SELECT * FROM t7 JOIN t8 USING (a)" {x ex 24 abc 24}
1b "SELECT * FROM t7 NATURAL JOIN t8" {x ex 24 abc 24}
|
Changes to test/e_select2.test.
340 341 342 343 344 345 346 347 348 349 350 351 352 353 354 355 356 357 ... 364 365 366 367 368 369 370 371 372 373 374 375 376 377 378 379 380 381 ... 500 501 502 503 504 505 506 507 508 509 510 511 512 513 514 515 516 517 518 519 520 521 |
} {
catchsql { DROP INDEX i1 }
catchsql { DROP INDEX i2 }
catchsql { DROP INDEX i3 }
execsql $indexes
# EVIDENCE-OF: R-46122-14930 If the join-op is "CROSS JOIN", "INNER
# JOIN", "JOIN" or a comma (",") and there is no ON or USING clause,
# then the result of the join is simply the cartesian product of the
# left and right-hand datasets.
#
# EVIDENCE-OF: R-46256-57243 There is no difference between the "INNER
# JOIN", "JOIN" and "," join operators.
#
# EVIDENCE-OF: R-25071-21202 The "CROSS JOIN" join operator produces the
# same result as the "INNER JOIN", "JOIN" and "," operators
#
................................................................................
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 t5(y INTEGER, z TEXT COLLATE binary);
INSERT INTO t4 VALUES('2.0');
INSERT INTO t4 VALUES('TWO');
INSERT INTO t5 VALUES(2, 'two');
} {}
# EVIDENCE-OF: R-55824-40976 A sub-select specified in the join-source
# following the FROM clause in a simple SELECT statement is handled as
# if it was a table containing the data returned by executing the
# sub-select statement.
#
# EVIDENCE-OF: R-42612-06757 Each column of the sub-select dataset
# inherits the collation sequence and affinity of the corresponding
# expression in the sub-select statement.
#
foreach {tn subselect select spec} {
1 "SELECT * FROM t2" "SELECT * FROM t1 JOIN %ss%"
{t1 %ss%}
2 "SELECT * FROM t2" "SELECT * FROM t1 JOIN %ss% AS x ON (t1.a=x.a)"
{t1 %ss% -on {te_equals 0 0}}
|
| | | | | | | | | | | | | | | |
340 341 342 343 344 345 346 347 348 349 350 351 352 353 354 355 356 357 ... 364 365 366 367 368 369 370 371 372 373 374 375 376 377 378 379 380 381 ... 500 501 502 503 504 505 506 507 508 509 510 511 512 513 514 515 516 517 518 519 520 521 |
} {
catchsql { DROP INDEX i1 }
catchsql { DROP INDEX i2 }
catchsql { DROP INDEX i3 }
execsql $indexes
# EVIDENCE-OF: R-49872-03192 If the join-operator is "CROSS JOIN",
# "INNER JOIN", "JOIN" or a comma (",") and there is no ON or USING
# clause, then the result of the join is simply the cartesian product of
# the left and right-hand datasets.
#
# EVIDENCE-OF: R-46256-57243 There is no difference between the "INNER
# JOIN", "JOIN" and "," join operators.
#
# EVIDENCE-OF: R-25071-21202 The "CROSS JOIN" join operator produces the
# same result as the "INNER JOIN", "JOIN" and "," operators
#
................................................................................
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-38465-03616 If there is an ON clause then the ON
# expression is evaluated for each row of the cartesian product as a
# boolean expression. Only rows for which the expression evaluates to
# true are included 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 t5(y INTEGER, z TEXT COLLATE binary);
INSERT INTO t4 VALUES('2.0');
INSERT INTO t4 VALUES('TWO');
INSERT INTO t5 VALUES(2, 'two');
} {}
# EVIDENCE-OF: R-59237-46742 A subquery specified in the
# table-or-subquery following the FROM clause in a simple SELECT
# statement is handled as if it was a table containing the data returned
# by executing the subquery statement.
#
# EVIDENCE-OF: R-27438-53558 Each column of the subquery has the
# collation sequence and affinity of the corresponding expression in the
# subquery statement.
#
foreach {tn subselect select spec} {
1 "SELECT * FROM t2" "SELECT * FROM t1 JOIN %ss%"
{t1 %ss%}
2 "SELECT * FROM t2" "SELECT * FROM t1 JOIN %ss% AS x ON (t1.a=x.a)"
{t1 %ss% -on {te_equals 0 0}}
|
Changes to test/printf2.test.
55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 |
do_execsql_test printf2-1.10 {
SELECT printf('%lld',314159.2653);
} {314159}
do_execsql_test printf2-1.11 {
SELECT printf('%lld%n',314159.2653,'hi');
} {314159}
# EVIDENCE-OF: R-20555-31089 The %z format is interchangable with %s.
#
do_execsql_test printf2-1.12 {
SELECT printf('%.*z',5,'abcdefghijklmnop');
} {abcde}
do_execsql_test printf2-1.13 {
SELECT printf('%c','abcdefghijklmnop');
} {a}
|
| |
55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 |
do_execsql_test printf2-1.10 {
SELECT printf('%lld',314159.2653);
} {314159}
do_execsql_test printf2-1.11 {
SELECT printf('%lld%n',314159.2653,'hi');
} {314159}
# EVIDENCE-OF: R-17002-27534 The %z format is interchangeable with %s.
#
do_execsql_test printf2-1.12 {
SELECT printf('%.*z',5,'abcdefghijklmnop');
} {abcde}
do_execsql_test printf2-1.13 {
SELECT printf('%c','abcdefghijklmnop');
} {a}
|