/ Check-in [5e73f7b2]
Login

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

Overview
Comment:Add test cases to e_select.test.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: 5e73f7b2b77ba5e0670c512d9ef9eeb9bb654c27
User & Date: dan 2010-09-09 19:02:56
Context
2010-09-09
23:42
Make sure MEMSTATUS is enabled at the beginning of the memsubsys2.test script. check-in: c23b9721 user: drh tags: trunk
19:02
Add test cases to e_select.test. check-in: 5e73f7b2 user: dan tags: trunk
18:25
Further updates to the sqlite3_pcache_methods documentation, plus the addition of a few evidence marks related to pcache. check-in: 34edb54b user: drh tags: trunk
Changes
Hide Diffs Unified Diffs Ignore Whitespace Patch

Changes to test/e_select.test.

1126
1127
1128
1129
1130
1131
1132
1133












































































































































1134
1135

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









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


1126
1127
1128
1129
1130
1131
1132
1133
1134
1135
1136
1137
1138
1139
1140
1141
1142
1143
1144
1145
1146
1147
1148
1149
1150
1151
1152
1153
1154
1155
1156
1157
1158
1159
1160
1161
1162
1163
1164
1165
1166
1167
1168
1169
1170
1171
1172
1173
1174
1175
1176
1177
1178
1179
1180
1181
1182
1183
1184
1185
1186
1187
1188
1189
1190
1191
1192
1193
1194
1195
1196
1197
1198
1199
1200
1201
1202
1203
1204
1205
1206
1207
1208
1209
1210
1211
1212
1213
1214
1215
1216
1217
1218
1219
1220
1221
1222
1223
1224
1225
1226
1227
1228
1229
1230
1231
1232
1233
1234
1235
1236
1237
1238
1239
1240
1241
1242
1243
1244
1245
1246
1247
1248
1249
1250
1251
1252
1253
1254
1255
1256
1257
1258
1259
1260
1261
1262
1263
1264
1265
1266
1267
1268
1269
1270
1271
1272
1273
1274
1275

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
} {}

#-------------------------------------------------------------------------
# Tests below this point are focused on verifying the testable statements
# related to caculating the result rows of a simple SELECT statement.
#

drop_all_tables
do_execsql_test e_select-4.0 {
  CREATE TABLE z1(a, b, c);
  CREATE TABLE z2(d, e);
  CREATE TABLE z3(a, b);

  INSERT INTO z1 VALUES(51.65, -59.58, 'belfries');
  INSERT INTO z1 VALUES(-5, NULL, 75);
  INSERT INTO z1 VALUES(-2.2, -23.18, 'suiters');
  INSERT INTO z1 VALUES(NULL, 67, 'quartets');
  INSERT INTO z1 VALUES(-1.04, -32.3, 'aspen');
  INSERT INTO z1 VALUES(63, 'born', -26);

  INSERT INTO z2 VALUES(NULL, 21);
  INSERT INTO z2 VALUES(36, 6);

  INSERT INTO z3 VALUES('subsistence', 'gauze');
  INSERT INTO z3 VALUES(49.17, -67);
} {}

# EVIDENCE-OF: R-36327-17224 If a result expression is the special
# expression "*" then all columns in the input data are substituted for
# that one expression.
#
# EVIDENCE-OF: R-43693-30522 If the expression is the alias of a table
# or subquery in the FROM clause followed by ".*" then all columns from
# the named table or subquery are substituted for the single expression.
#
foreach {tn select res} {
  1  "SELECT * FROM z1 LIMIT 1"             {51.65 -59.58 belfries}
  2  "SELECT * FROM z1,z2 LIMIT 1"          {51.65 -59.58 belfries {} 21}
  3  "SELECT z1.* FROM z1,z2 LIMIT 1"       {51.65 -59.58 belfries}
  4  "SELECT z2.* FROM z1,z2 LIMIT 1"       {{} 21}
  5  "SELECT z2.*, z1.* FROM z1,z2 LIMIT 1" {{} 21 51.65 -59.58 belfries}

  6  "SELECT count(*), * FROM z1"           {6 63 born -26}
  7  "SELECT max(a), * FROM z1"             {63 63 born -26}
  8  "SELECT *, min(a) FROM z1"             {63 born -26 -5}

  9  "SELECT *,* FROM z1,z2 LIMIT 1" {        
     51.65 -59.58 belfries {} 21 51.65 -59.58 belfries {} 21
  }
  10 "SELECT z1.*,z1.* FROM z2,z1 LIMIT 1" {        
     51.65 -59.58 belfries 51.65 -59.58 belfries
  }
} {
  do_execsql_test e_select-4.1.$tn $select [list {*}$res]
}

# EVIDENCE-OF: R-61869-22578 It is an error to use a "*" or "alias.*"
# expression in any context other than than a result expression list.
#
# EVIDENCE-OF: R-44324-41166 It is also an error to use a "*" or
# "alias.*" expression in a simple SELECT query that does not have a
# FROM clause.
#
foreach {tn select err} {
  1.1  "SELECT a, b, c FROM z1 WHERE *"    {near "*": syntax error}
  1.2  "SELECT a, b, c FROM z1 GROUP BY *" {near "*": syntax error}
  1.3  "SELECT 1 + * FROM z1"              {near "*": syntax error}
  1.4  "SELECT * + 1 FROM z1"              {near "+": syntax error}

  2.1 "SELECT *" {no tables specified}
  2.2 "SELECT * WHERE 1" {no tables specified}
  2.3 "SELECT * WHERE 0" {no tables specified}
  2.4 "SELECT count(*), *" {no tables specified}
} {
  do_catchsql_test e_select-4.2.$tn $select [list 1 $err]
}

# EVIDENCE-OF: R-08669-22397 The number of columns in the rows returned
# by a simple SELECT statement is equal to the number of expressions in
# the result expression list after substitution of * and alias.*
# expressions.
#
foreach {tn select nCol} {
  1   "SELECT * FROM z1"   3
  2   "SELECT * FROM z1 NATURAL JOIN z3"            3
  3   "SELECT z1.* FROM z1 NATURAL JOIN z3"         3
  4   "SELECT z3.* FROM z1 NATURAL JOIN z3"         2
  5   "SELECT z1.*, z3.* FROM z1 NATURAL JOIN z3"   5
  6   "SELECT 1, 2, z1.* FROM z1"                   5
  7   "SELECT a, *, b, c FROM z1"                   6
} {
  set ::stmt [sqlite3_prepare_v2 db $select -1 DUMMY]
  do_test e_select-4.3.$tn { sqlite3_column_count $::stmt } $nCol
  sqlite3_finalize $::stmt
}

# EVIDENCE-OF: R-44050-47362 If the SELECT statement is a non-aggregate
# query, then each expression in the result expression list is evaluated
# for each row in the dataset filtered by the WHERE clause.
#
#   By other definitions in lang_select.html, a non-aggregate query is
#   any simple SELECT that has no GROUP BY clause and no aggregate expressions
#   in the result expression list.
#
do_execsql_test e_select-4.4.1 {
  SELECT a, b FROM z1
} {51.65 -59.58 -5 {} -2.2 -23.18 {} 67 -1.04 -32.3 63 born}

do_execsql_test e_select-4.4.2 {
  SELECT a IS NULL, b+1, * FROM z1
} [list {*}{
        0 -58.58   51.65 -59.58 belfries
        0 {}       -5 {} 75            
        0 -22.18   -2.2 -23.18 suiters
        1 68       {} 67 quartets    
        0 -31.3    -1.04 -32.3 aspen
        0 1        63 born -26
}]

do_execsql_test e_select-4.4.3 {
  SELECT 32*32, d||e FROM z2
} {1024 {} 1024 366}

# EVIDENCE-OF: R-57629-25253 If the SELECT statement is an aggregate
# query without a GROUP BY clause, then each aggregate expression in the
# result-set is evaluated once across the entire dataset.
#
foreach {tn select res} {
  5.1 "SELECT count(a), max(a), count(b), max(b) FROM z1" {5 63 5 born}
  5.2 "SELECT count(*), max(1)"                           {1 1}

  5.3 "SELECT sum(b+1) FROM z1 NATURAL LEFT JOIN z3"           {-43.06}
  5.4 "SELECT sum(b+2) FROM z1 NATURAL LEFT JOIN z3"           {-38.06}
  5.5 "SELECT sum(b IS NOT NULL) FROM z1 NATURAL LEFT JOIN z3" {5}
} {
  do_execsql_test e_select-4.$tn $select [list {*}$res]
}





finish_test