/ Check-in [721b73fa]
Login
SQLite training in Houston TX on 2019-11-05 (details)
Part of the 2019 Tcl Conference

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 Unified Diffs Ignore Whitespace Patch

Changes to test/e_select.test.

1075
1076
1077
1078
1079
1080
1081
1082



















































1083
1084
  # 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









>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>


1075
1076
1077
1078
1079
1080
1081
1082
1083
1084
1085
1086
1087
1088
1089
1090
1091
1092
1093
1094
1095
1096
1097
1098
1099
1100
1101
1102
1103
1104
1105
1106
1107
1108
1109
1110
1111
1112
1113
1114
1115
1116
1117
1118
1119
1120
1121
1122
1123
1124
1125
1126
1127
1128
1129
1130
1131
1132
1133
1134
1135
  # 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
}

#-------------------------------------------------------------------------
# The next block of tests - e_select-3.* - concentrate on verifying 
# statements made regarding WHERE clause processing.
#
drop_all_tables
do_execsql_test e_select-3.0 {
  CREATE TABLE x1(k, x, y, z);
  INSERT INTO x1 VALUES(1, 'relinquished', 'aphasia', 78.43);
  INSERT INTO x1 VALUES(2, X'A8E8D66F',    X'07CF',   -81);
  INSERT INTO x1 VALUES(3, -22,            -27.57,    NULL);
  INSERT INTO x1 VALUES(4, NULL,           'bygone',  'picky');
  INSERT INTO x1 VALUES(5, NULL,           96.28,     NULL);
  INSERT INTO x1 VALUES(6, 0,              1,         2);

  CREATE TABLE x2(k, x, y2);
  INSERT INTO x2 VALUES(1, 50, X'B82838');
  INSERT INTO x2 VALUES(5, 84.79, 65.88);
  INSERT INTO x2 VALUES(3, -22, X'0E1BE452A393');
  INSERT INTO x2 VALUES(7, 'mistrusted', 'standardized');
} {}

# EVIDENCE-OF: R-22873-49686 If a WHERE clause is specified, the WHERE
# expression is evaluated for each row in the input data and the result
# cast to a numeric value. All rows for which the WHERE clause
# expression evaluates to a NULL value or to zero (integer value 0 or
# real value 0.0) are excluded from the dataset before continuing.
#
do_execsql_test e_select-3.1.1 { SELECT k FROM x1 WHERE x }         {3}
do_execsql_test e_select-3.1.2 { SELECT k FROM x1 WHERE y }         {3 5 6}
do_execsql_test e_select-3.1.3 { SELECT k FROM x1 WHERE z }         {1 2 6}
do_execsql_test e_select-3.1.4 { SELECT k FROM x1 WHERE '1'||z    } {1 2 4 6}
do_execsql_test e_select-3.1.5 { SELECT k FROM x1 WHERE x IS NULL } {4 5}
do_execsql_test e_select-3.1.6 { SELECT k FROM x1 WHERE z - 78.43 } {2 4 6}

do_execsql_test e_select-3.2.1a {
  SELECT k FROM x1 LEFT JOIN x2 USING(k)
} {1 2 3 4 5 6}
do_execsql_test e_select-3.2.1b {
  SELECT k FROM x1 LEFT JOIN x2 USING(k) WHERE x2.k
} {1 3 5}
do_execsql_test e_select-3.2.2 {
  SELECT k FROM x1 LEFT JOIN x2 USING(k) WHERE x2.k IS NULL
} {2 4 6}

do_execsql_test e_select-3.2.3 {
  SELECT k FROM x1 NATURAL JOIN x2 WHERE x2.k
} {3}
do_execsql_test e_select-3.2.4 {
  SELECT k FROM x1 NATURAL JOIN x2 WHERE x2.k-3
} {}

finish_test