/ Check-in [8fe34faf]

 ```847 848 849 850 851 852 853 854 855 856 857 858 859 860 861 862 863 864 865 866 867 868 869 870 871 872 873 874 875 876 877 878 879 880 881 882 883 884 885 886 887 888 889 890 891 892 893 894 895 896 897 898 899 900 901 902 903 904 905 906 907 908 909 910 911 912 913 914 915 916 917 918 919 920 921 922 923 924 925 926 927 928 929 ... 966 967 968 969 970 971 972 973 974 975 976 977 978 979 980 981 982 983 984 985 ``` ``` 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} test_join 3 "t1 LEFT JOIN t2 ON (t1.a=t2.a)" {t1 t2 -left -on {te_equals a a}} test_join 4 "t1 LEFT JOIN t2 USING (a)" { t1 t2 -left -using a -on {te_equals a a} } test_join 5 "t1 CROSS JOIN t2 USING(b, a)" { t1 t2 -using {a b} -on {te_and {te_equals a a} {te_equals b b}} } test_join 6 "t1 NATURAL JOIN t2" { t1 t2 -using {a b} -on {te_and {te_equals a a} {te_equals b b}} } test_join 7 "t1 NATURAL INNER JOIN t2" { t1 t2 -using {a b} -on {te_and {te_equals a a} {te_equals b b}} } test_join 8 "t1 NATURAL CROSS JOIN t2" { t1 t2 -using {a b} -on {te_and {te_equals a a} {te_equals b b}} } test_join 9 "t1 NATURAL INNER JOIN t2" { t1 t2 -using {a b} -on {te_and {te_equals a a} {te_equals b b}} } test_join 10 "t1 NATURAL LEFT JOIN t2" { t1 t2 -left -using {a b} -on {te_and {te_equals a a} {te_equals b b}} } test_join 11 "t1 NATURAL LEFT OUTER JOIN t2" { t1 t2 -left -using {a b} -on {te_and {te_equals a a} {te_equals b b}} } test_join 12 "t2 NATURAL JOIN t1" { t2 t1 -using {a b} -on {te_and {te_equals a a} {te_equals b b}} } test_join 13 "t2 NATURAL INNER JOIN t1" { t2 t1 -using {a b} -on {te_and {te_equals a a} {te_equals b b}} } test_join 14 "t2 NATURAL CROSS JOIN t1" { t2 t1 -using {a b} -on {te_and {te_equals a a} {te_equals b b}} } test_join 15 "t2 NATURAL INNER JOIN t1" { t2 t1 -using {a b} -on {te_and {te_equals a a} {te_equals b b}} } test_join 16 "t2 NATURAL LEFT JOIN t1" { t2 t1 -left -using {a b} -on {te_and {te_equals a a} {te_equals b b}} } test_join 17 "t2 NATURAL LEFT OUTER JOIN t1" { t2 t1 -left -using {a b} -on {te_and {te_equals a a} {te_equals b b}} } test_join 18 "t1 LEFT JOIN t2 USING (b)" { t1 t2 -left -using b -on {te_equals b b} } test_join 19 "t1 JOIN t3 USING(b)" {t1 t3 -using b -on {te_equals b b}} test_join 20 "t3 JOIN t1 USING(b)" { t3 t1 -using b -on {te_equals -nocase b b} } test_join 21 "t1 NATURAL JOIN t3" { t1 t3 -using b -on {te_equals b b} } test_join 22 "t3 NATURAL JOIN t1" { t3 t1 -using b -on {te_equals -nocase b b} } test_join 23 "t1 NATURAL LEFT JOIN t3" { t1 t3 -left -using b -on {te_equals b b} } test_join 24 "t3 NATURAL LEFT JOIN t1" { t3 t1 -left -using b -on {te_equals -nocase b b} } test_join 25 "t1 LEFT JOIN t3 ON (t3.b=t1.b)" { t1 t3 -left -on {te_equals -nocase b b} } test_join 26 "t1 LEFT JOIN t3 ON (t1.b=t3.b)" { t1 t3 -left -on {te_equals b b} } test_join 27 "t1 JOIN t3 ON (t1.b=t3.b)" { t1 t3 -on {te_equals b b} } # EVIDENCE-OF: R-28760-53843 When more than two tables are joined # together as part of a FROM clause, the join operations are processed # in order from left to right. In other words, the FROM clause (A # join-op-1 B join-op-2 C) is computed as ((A join-op-1 B) join-op-2 C). # # Tests 28a and 28b show that the statement above is true for this case. ................................................................................ } # XXXEVIDENCE-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. # proc test_subselect_join {tn subselect select script} { 1 "SELECT * FROM t2" "SELECT * FROM t1 JOIN (%ss%)" {t1 %ss%} } { execsql "CREATE TEMP TABLE sstemp AS \$subselect" set ssdata [te_read_tbl db sstemp] execsql "DROP TABLE sstemp" } finish_test ``` ``` | | | | | | | | | | | | | | | | | | | | | | | | | | ``` ```847 848 849 850 851 852 853 854 855 856 857 858 859 860 861 862 863 864 865 866 867 868 869 870 871 872 873 874 875 876 877 878 879 880 881 882 883 884 885 886 887 888 889 890 891 892 893 894 895 896 897 898 899 900 901 902 903 904 905 906 907 908 909 910 911 912 913 914 915 916 917 918 919 920 921 922 923 924 925 926 927 928 929 ... 966 967 968 969 970 971 972 973 974 975 976 977 978 979 980 981 982 983 984 985 ``` ``` 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} test_join \$tn.3 "t1 LEFT JOIN t2 ON (t1.a=t2.a)" {t1 t2 -left -on {te_equals a a}} test_join \$tn.4 "t1 LEFT JOIN t2 USING (a)" { t1 t2 -left -using a -on {te_equals a a} } test_join \$tn.5 "t1 CROSS JOIN t2 USING(b, a)" { t1 t2 -using {a b} -on {te_and {te_equals a a} {te_equals b b}} } test_join \$tn.6 "t1 NATURAL JOIN t2" { t1 t2 -using {a b} -on {te_and {te_equals a a} {te_equals b b}} } test_join \$tn.7 "t1 NATURAL INNER JOIN t2" { t1 t2 -using {a b} -on {te_and {te_equals a a} {te_equals b b}} } test_join \$tn.8 "t1 NATURAL CROSS JOIN t2" { t1 t2 -using {a b} -on {te_and {te_equals a a} {te_equals b b}} } test_join \$tn.9 "t1 NATURAL INNER JOIN t2" { t1 t2 -using {a b} -on {te_and {te_equals a a} {te_equals b b}} } test_join \$tn.10 "t1 NATURAL LEFT JOIN t2" { t1 t2 -left -using {a b} -on {te_and {te_equals a a} {te_equals b b}} } test_join \$tn.11 "t1 NATURAL LEFT OUTER JOIN t2" { t1 t2 -left -using {a b} -on {te_and {te_equals a a} {te_equals b b}} } test_join \$tn.12 "t2 NATURAL JOIN t1" { t2 t1 -using {a b} -on {te_and {te_equals a a} {te_equals b b}} } test_join \$tn.13 "t2 NATURAL INNER JOIN t1" { t2 t1 -using {a b} -on {te_and {te_equals a a} {te_equals b b}} } test_join \$tn.14 "t2 NATURAL CROSS JOIN t1" { t2 t1 -using {a b} -on {te_and {te_equals a a} {te_equals b b}} } test_join \$tn.15 "t2 NATURAL INNER JOIN t1" { t2 t1 -using {a b} -on {te_and {te_equals a a} {te_equals b b}} } test_join \$tn.16 "t2 NATURAL LEFT JOIN t1" { t2 t1 -left -using {a b} -on {te_and {te_equals a a} {te_equals b b}} } test_join \$tn.17 "t2 NATURAL LEFT OUTER JOIN t1" { t2 t1 -left -using {a b} -on {te_and {te_equals a a} {te_equals b b}} } test_join \$tn.18 "t1 LEFT JOIN t2 USING (b)" { t1 t2 -left -using b -on {te_equals b b} } test_join \$tn.19 "t1 JOIN t3 USING(b)" {t1 t3 -using b -on {te_equals b b}} test_join \$tn.20 "t3 JOIN t1 USING(b)" { t3 t1 -using b -on {te_equals -nocase b b} } test_join \$tn.21 "t1 NATURAL JOIN t3" { t1 t3 -using b -on {te_equals b b} } test_join \$tn.22 "t3 NATURAL JOIN t1" { t3 t1 -using b -on {te_equals -nocase b b} } test_join \$tn.23 "t1 NATURAL LEFT JOIN t3" { t1 t3 -left -using b -on {te_equals b b} } test_join \$tn.24 "t3 NATURAL LEFT JOIN t1" { t3 t1 -left -using b -on {te_equals -nocase b b} } test_join \$tn.25 "t1 LEFT JOIN t3 ON (t3.b=t1.b)" { t1 t3 -left -on {te_equals -nocase b b} } test_join \$tn.26 "t1 LEFT JOIN t3 ON (t1.b=t3.b)" { t1 t3 -left -on {te_equals b b} } test_join \$tn.27 "t1 JOIN t3 ON (t1.b=t3.b)" { t1 t3 -on {te_equals b b} } # EVIDENCE-OF: R-28760-53843 When more than two tables are joined # together as part of a FROM clause, the join operations are processed # in order from left to right. In other words, the FROM clause (A # join-op-1 B join-op-2 C) is computed as ((A join-op-1 B) join-op-2 C). # # Tests 28a and 28b show that the statement above is true for this case. ................................................................................ } # XXXEVIDENCE-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. # foreach {tn subselect select script} { 1 "SELECT * FROM t2" "SELECT * FROM t1 JOIN (%ss%)" {t1 %ss%} } { execsql "CREATE TEMP TABLE sstemp AS \$subselect" set ssdata [te_read_tbl db sstemp] execsql "DROP TABLE sstemp" } finish_test ```