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 |
Timelines: | family | ancestors | descendants | both | trunk |
Files: | files | file ages | folders |
SHA1: |
be1acb610f7e594b417dd8409b7a7aa8 |
User & Date: | drh 2014-01-27 15:02:07.424 |
Context
2014-01-27
| ||
16:35 | Fix a couple of problems with new test scripts causing the permutations test to fail. (check-in: 48d736c0ee user: dan tags: trunk) | |
15:02 | Update some requirements marks to conform with improvements in the documentation. (check-in: be1acb610f user: drh tags: trunk) | |
14:19 | Fix a problem in pagerfault.test causing an assert() to fail. (check-in: ba8d2d507f user: dan tags: trunk) | |
Changes
Changes to test/e_select.test.
︙ | ︙ | |||
329 330 331 332 333 334 335 | 2 "SELECT 'abc' WHERE NULL" {} 3 "SELECT NULL" {{}} 4 "SELECT count(*)" {1} 5 "SELECT count(*) WHERE 0" {0} 6 "SELECT count(*) WHERE 1" {1} } | | | | | 329 330 331 332 333 334 335 336 337 338 339 340 341 342 343 344 345 | 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'); |
︙ | ︙ | |||
353 354 355 356 357 358 359 | -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} } | | | | | | 353 354 355 356 357 358 359 360 361 362 363 364 365 366 367 368 369 370 | -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 { |
︙ | ︙ | |||
379 380 381 382 383 384 385 | } # # 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: # | | | | | | 379 380 381 382 383 384 385 386 387 388 389 390 391 392 393 394 395 396 | } # # 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. |
︙ | ︙ | |||
509 510 511 512 513 514 515 | 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 \ ] | < | | | | | 509 510 511 512 513 514 515 516 517 518 519 520 521 522 523 524 525 526 | 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 \ |
︙ | ︙ | |||
536 537 538 539 540 541 542 | 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 } | | | | | | | | | | 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 | 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'); |
︙ | ︙ | |||
618 619 620 621 622 623 624 | {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 } | | < | | | 617 618 619 620 621 622 623 624 625 626 627 628 629 630 631 632 633 | {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); |
︙ | ︙ | |||
656 657 658 659 660 661 662 | 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 {} {}} } | | | | 654 655 656 657 658 659 660 661 662 663 664 665 666 667 668 669 | 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 | } { catchsql { DROP INDEX i1 } catchsql { DROP INDEX i2 } catchsql { DROP INDEX i3 } execsql $indexes | | | | | | | | | | 340 341 342 343 344 345 346 347 348 349 350 351 352 353 354 355 356 357 358 359 360 361 362 363 364 365 366 367 368 369 370 371 372 373 374 375 376 377 378 379 380 381 | } { 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.1 "t1, t2" {t1 t2} test_join $tn.1.2 "t1 INNER JOIN t2" {t1 t2} test_join $tn.1.3 "t1 CROSS JOIN t2" {t1 t2} test_join $tn.1.4 "t1 JOIN t2" {t1 t2} test_join $tn.1.5 "t2, t3" {t2 t3} test_join $tn.1.6 "t2 INNER JOIN t3" {t2 t3} 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} |
︙ | ︙ | |||
500 501 502 503 504 505 506 | 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'); } {} | | | | | | | | | 500 501 502 503 504 505 506 507 508 509 510 511 512 513 514 515 516 517 518 519 520 521 | 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 | 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} | | | 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} |
︙ | ︙ |