Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Overview
Comment: | Add tests for sub-select statements in the FROM clause of a SELECT to e_select.test. |
---|---|
Downloads: | Tarball | ZIP archive |
Timelines: | family | ancestors | descendants | both | trunk |
Files: | files | file ages | folders |
SHA1: |
2c6b1ca952be9979b8079431c2abad28 |
User & Date: | dan 2010-09-09 10:00:44.000 |
Context
2010-09-09
| ||
11:33 | Add WHERE clause tests to e_select.test. (check-in: 721b73fa5c user: dan tags: trunk) | |
10:00 | Add tests for sub-select statements in the FROM clause of a SELECT to e_select.test. (check-in: 2c6b1ca952 user: dan tags: trunk) | |
2010-09-08
| ||
19:16 | Fix problem with test names in e_select.test. (check-in: 8fe34faf6b user: dan tags: trunk) | |
Changes
Changes to test/e_select.test.
︙ | ︙ | |||
605 606 607 608 609 610 611 | # by reading the contents of a named table (te_read_tbl), or by executing # a SELECT statement (t3_read_sql). # # See the comment above, describing "te_* commands", for details of the # return values. # proc te_read_tbl {db tbl} { | | | 605 606 607 608 609 610 611 612 613 614 615 616 617 618 619 | # by reading the contents of a named table (te_read_tbl), or by executing # a SELECT statement (t3_read_sql). # # See the comment above, describing "te_* commands", for details of the # return values. # proc te_read_tbl {db tbl} { te_read_sql $db "SELECT * FROM '$tbl'" } proc te_read_sql {db sql} { set S [sqlite3_prepare_v2 $db $sql -1 DUMMY] set cols [list] for {set i 0} {$i < [sqlite3_column_count $S]} {incr i} { lappend cols [sqlite3_column_name $S $i] |
︙ | ︙ | |||
699 700 701 702 703 704 705 706 707 708 709 710 711 712 713 714 715 716 | list $cret $rret } proc te_tbljoin {db t1 t2 args} { te_join [te_read_tbl $db $t1] [te_read_tbl $db $t2] {*}$args } #---------- # te_equals ?SWITCHES? c1 c2 cols1 row1 cols2 row2 # proc te_equals {args} { if {[llength $args]<6} {error "invalid arguments to te_equals"} foreach {c1 c2 cols1 row1 cols2 row2} [lrange $args end-5 end] break set nocase 0 | > > > > > > > > > > > > > > > > > > > > > > > > | > > > > > > > > | > | 699 700 701 702 703 704 705 706 707 708 709 710 711 712 713 714 715 716 717 718 719 720 721 722 723 724 725 726 727 728 729 730 731 732 733 734 735 736 737 738 739 740 741 742 743 744 745 746 747 748 749 750 751 752 753 754 755 756 757 758 759 760 761 762 763 764 765 766 767 768 769 770 771 772 773 774 775 776 777 778 779 | list $cret $rret } proc te_tbljoin {db t1 t2 args} { te_join [te_read_tbl $db $t1] [te_read_tbl $db $t2] {*}$args } proc te_apply_affinity {affinity typevar valvar} { upvar $typevar type upvar $valvar val switch -- $affinity { integer { if {[string is double $val]} { set type REAL } if {[string is wideinteger $val]} { set type INTEGER } if {$type == "REAL" && int($val)==$val} { set type INTEGER set val [expr {int($val)}] } } text { set type TEXT } none { } default { error "invalid affinity: $affinity" } } } #---------- # te_equals ?SWITCHES? c1 c2 cols1 row1 cols2 row2 # proc te_equals {args} { if {[llength $args]<6} {error "invalid arguments to te_equals"} foreach {c1 c2 cols1 row1 cols2 row2} [lrange $args end-5 end] break set nocase 0 set affinity none for {set i 0} {$i < ([llength $args]-6)} {incr i} { set a [lindex $args $i] switch -- $a { -nocase { set nocase 1 } -affinity { set affinity [string tolower [lindex $args [incr i]]] } default { error "invalid arguments to te_equals" } } } set idx2 [if {[string is integer $c2]} { set c2 } else { lsearch $cols2 $c2 }] set idx1 [if {[string is integer $c1]} { set c1 } else { lsearch $cols1 $c1 }] set t1 [lindex $row1 $idx1 0] set t2 [lindex $row2 $idx2 0] set v1 [lindex $row1 $idx1 1] set v2 [lindex $row2 $idx2 1] te_apply_affinity $affinity t1 v1 te_apply_affinity $affinity t2 v2 if {$t1 == "NULL" || $t2 == "NULL"} { return 0 } if {$nocase && $t1 == "TEXT"} { set v1 [string tolower $v1] } if {$nocase && $t2 == "TEXT"} { set v2 [string tolower $v2] } set res [expr {$t1 == $t2 && [string equal $v1 $v2]}] return $res } proc te_false {args} { return 0 } proc te_true {args} { return 1 } proc te_and {args} { foreach a [lrange $args 0 end-4] { |
︙ | ︙ | |||
798 799 800 801 802 803 804 | INSERT INTO t3 VALUES('a'); INSERT INTO t3 VALUES('c'); INSERT INTO t3 VALUES('b'); } {} foreach {tn indexes} { | | | | | | 831 832 833 834 835 836 837 838 839 840 841 842 843 844 845 846 847 848 | INSERT INTO t3 VALUES('a'); INSERT INTO t3 VALUES('c'); INSERT INTO t3 VALUES('b'); } {} foreach {tn indexes} { e_select-2.1.1 { } e_select-2.1.2 { CREATE INDEX i1 ON t1(a) } e_select-2.1.3 { CREATE INDEX i1 ON t2(a) } e_select-2.1.4 { CREATE INDEX i1 ON t3(b) } } { catchsql { DROP INDEX i1 } catchsql { DROP INDEX i2 } catchsql { DROP INDEX i3 } execsql $indexes |
︙ | ︙ | |||
961 962 963 964 965 966 967 | te_dataset_eq_unordered $tn.28d [ te_read_sql db "SELECT * FROM t3 NATURAL LEFT JOIN (t2 NATURAL JOIN t1)" ] [te_join $t3 $t2_natural_join_t1 \ -left -using {b} -on {te_equals -nocase b b} \ ] } | > > > > > > > > > | > > > > | | > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > | | | > > | > > > | 994 995 996 997 998 999 1000 1001 1002 1003 1004 1005 1006 1007 1008 1009 1010 1011 1012 1013 1014 1015 1016 1017 1018 1019 1020 1021 1022 1023 1024 1025 1026 1027 1028 1029 1030 1031 1032 1033 1034 1035 1036 1037 1038 1039 1040 1041 1042 1043 1044 1045 1046 1047 1048 1049 1050 1051 1052 1053 1054 1055 1056 1057 1058 1059 1060 1061 1062 1063 1064 1065 1066 1067 1068 1069 1070 1071 1072 1073 1074 1075 1076 1077 1078 1079 1080 1081 1082 1083 1084 | te_dataset_eq_unordered $tn.28d [ te_read_sql db "SELECT * FROM t3 NATURAL LEFT JOIN (t2 NATURAL JOIN t1)" ] [te_join $t3 $t2_natural_join_t1 \ -left -using {b} -on {te_equals -nocase b b} \ ] } do_execsql_test e_select-2.2.0 { CREATE TABLE t4(x TEXT COLLATE nocase); 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}} 3 "SELECT * FROM t2" "SELECT * FROM %ss% AS x JOIN t1 ON (t1.a=x.a)" {%ss% t1 -on {te_equals 0 0}} 4 "SELECT * FROM t1, t2" "SELECT * FROM %ss% AS x JOIN t3" {%ss% t3} 5 "SELECT * FROM t1, t2" "SELECT * FROM %ss% NATURAL JOIN t3" {%ss% t3 -using b -on {te_equals 1 0}} 6 "SELECT * FROM t1, t2" "SELECT * FROM t3 NATURAL JOIN %ss%" {t3 %ss% -using b -on {te_equals -nocase 0 1}} 7 "SELECT * FROM t1, t2" "SELECT * FROM t3 NATURAL LEFT JOIN %ss%" {t3 %ss% -left -using b -on {te_equals -nocase 0 1}} 8 "SELECT count(*) AS y FROM t4" "SELECT * FROM t5, %ss% USING (y)" {t5 %ss% -using y -on {te_equals -affinity text 0 0}} 9 "SELECT count(*) AS y FROM t4" "SELECT * FROM %ss%, t5 USING (y)" {%ss% t5 -using y -on {te_equals -affinity text 0 0}} 10 "SELECT x AS y FROM t4" "SELECT * FROM %ss% JOIN t5 USING (y)" {%ss% t5 -using y -on {te_equals -nocase -affinity integer 0 0}} 11 "SELECT x AS y FROM t4" "SELECT * FROM t5 JOIN %ss% USING (y)" {t5 %ss% -using y -on {te_equals -nocase -affinity integer 0 0}} 12 "SELECT y AS x FROM t5" "SELECT * FROM %ss% JOIN t4 USING (x)" {%ss% t4 -using x -on {te_equals -nocase -affinity integer 0 0}} 13 "SELECT y AS x FROM t5" "SELECT * FROM t4 JOIN %ss% USING (x)" {t4 %ss% -using x -on {te_equals -nocase -affinity integer 0 0}} 14 "SELECT +y AS x FROM t5" "SELECT * FROM %ss% JOIN t4 USING (x)" {%ss% t4 -using x -on {te_equals -nocase -affinity text 0 0}} 15 "SELECT +y AS x FROM t5" "SELECT * FROM t4 JOIN %ss% USING (x)" {t4 %ss% -using x -on {te_equals -nocase -affinity text 0 0}} } { # Create a temporary table named %ss% containing the data returned by # the sub-select. Then have the [te_tbljoin] proc use this table to # compute the expected results of the $select query. Drop the temporary # table before continuing. # execsql "CREATE TEMP TABLE '%ss%' AS $subselect" set te [eval te_tbljoin db $spec] execsql "DROP TABLE '%ss%'" # Check that the actual data returned by the $select query is the same # as the expected data calculated using [te_tbljoin] above. # te_dataset_eq_unordered e_select-2.2.1.$tn [ te_read_sql db [string map [list %ss% "($subselect)"] $select] ] $te } finish_test |