/ Check-in [721b73fa]
Login

Many hyperlinks are disabled.
Use anonymous login to enable hyperlinks.

Overview
Comment:Add WHERE clause tests to e_select.test.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: 721b73fa5c5898f6c6d5946e1c70ccd2d0b0dccc
User & Date: dan 2010-09-09 11:33:09
Context
2010-09-09
15:48
Updates to the sqlite3_pcache_methods documentation. check-in: b21425c4 user: drh tags: trunk
11:33
Add WHERE clause tests to e_select.test. check-in: 721b73fa 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: 2c6b1ca9 user: dan tags: trunk
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to test/e_select.test.

  1075   1075     # Check that the actual data returned by the $select query is the same
  1076   1076     # as the expected data calculated using [te_tbljoin] above.
  1077   1077     #
  1078   1078     te_dataset_eq_unordered e_select-2.2.1.$tn [
  1079   1079       te_read_sql db [string map [list %ss% "($subselect)"] $select]
  1080   1080     ] $te
  1081   1081   }
         1082  +
         1083  +#-------------------------------------------------------------------------
         1084  +# The next block of tests - e_select-3.* - concentrate on verifying 
         1085  +# statements made regarding WHERE clause processing.
         1086  +#
         1087  +drop_all_tables
         1088  +do_execsql_test e_select-3.0 {
         1089  +  CREATE TABLE x1(k, x, y, z);
         1090  +  INSERT INTO x1 VALUES(1, 'relinquished', 'aphasia', 78.43);
         1091  +  INSERT INTO x1 VALUES(2, X'A8E8D66F',    X'07CF',   -81);
         1092  +  INSERT INTO x1 VALUES(3, -22,            -27.57,    NULL);
         1093  +  INSERT INTO x1 VALUES(4, NULL,           'bygone',  'picky');
         1094  +  INSERT INTO x1 VALUES(5, NULL,           96.28,     NULL);
         1095  +  INSERT INTO x1 VALUES(6, 0,              1,         2);
         1096  +
         1097  +  CREATE TABLE x2(k, x, y2);
         1098  +  INSERT INTO x2 VALUES(1, 50, X'B82838');
         1099  +  INSERT INTO x2 VALUES(5, 84.79, 65.88);
         1100  +  INSERT INTO x2 VALUES(3, -22, X'0E1BE452A393');
         1101  +  INSERT INTO x2 VALUES(7, 'mistrusted', 'standardized');
         1102  +} {}
         1103  +
         1104  +# EVIDENCE-OF: R-22873-49686 If a WHERE clause is specified, the WHERE
         1105  +# expression is evaluated for each row in the input data and the result
         1106  +# cast to a numeric value. All rows for which the WHERE clause
         1107  +# expression evaluates to a NULL value or to zero (integer value 0 or
         1108  +# real value 0.0) are excluded from the dataset before continuing.
         1109  +#
         1110  +do_execsql_test e_select-3.1.1 { SELECT k FROM x1 WHERE x }         {3}
         1111  +do_execsql_test e_select-3.1.2 { SELECT k FROM x1 WHERE y }         {3 5 6}
         1112  +do_execsql_test e_select-3.1.3 { SELECT k FROM x1 WHERE z }         {1 2 6}
         1113  +do_execsql_test e_select-3.1.4 { SELECT k FROM x1 WHERE '1'||z    } {1 2 4 6}
         1114  +do_execsql_test e_select-3.1.5 { SELECT k FROM x1 WHERE x IS NULL } {4 5}
         1115  +do_execsql_test e_select-3.1.6 { SELECT k FROM x1 WHERE z - 78.43 } {2 4 6}
         1116  +
         1117  +do_execsql_test e_select-3.2.1a {
         1118  +  SELECT k FROM x1 LEFT JOIN x2 USING(k)
         1119  +} {1 2 3 4 5 6}
         1120  +do_execsql_test e_select-3.2.1b {
         1121  +  SELECT k FROM x1 LEFT JOIN x2 USING(k) WHERE x2.k
         1122  +} {1 3 5}
         1123  +do_execsql_test e_select-3.2.2 {
         1124  +  SELECT k FROM x1 LEFT JOIN x2 USING(k) WHERE x2.k IS NULL
         1125  +} {2 4 6}
         1126  +
         1127  +do_execsql_test e_select-3.2.3 {
         1128  +  SELECT k FROM x1 NATURAL JOIN x2 WHERE x2.k
         1129  +} {3}
         1130  +do_execsql_test e_select-3.2.4 {
         1131  +  SELECT k FROM x1 NATURAL JOIN x2 WHERE x2.k-3
         1132  +} {}
  1082   1133   
  1083   1134   finish_test
  1084   1135