/ Check-in [3a051a76]
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 tests to e_select.test.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: 3a051a76f7f116f9ba25fe4e41418b9f0c1401be
User & Date: dan 2010-09-10 19:18:00
Context
2010-09-10
20:23
A further correction to the sqlite3_create_function() interface documentation. check-in: c65583db user: drh tags: trunk
19:18
Add tests to e_select.test. check-in: 3a051a76 user: dan tags: trunk
16:38
Updates to the documentation on the sqlite3_create_function() family of interfaces. check-in: 9d277e0b user: drh tags: trunk
Changes
Hide Diffs Unified Diffs Ignore Whitespace Patch

Changes to test/e_select.test.

1253
1254
1255
1256
1257
1258
1259
1260
1261
1262
1263
1264
1265
1266
1267
1268
1269



1270





































































































































































































1271
1272
1273
1274
1275

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









|
|








>
>
>
|
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>





>
1253
1254
1255
1256
1257
1258
1259
1260
1261
1262
1263
1264
1265
1266
1267
1268
1269
1270
1271
1272
1273
1274
1275
1276
1277
1278
1279
1280
1281
1282
1283
1284
1285
1286
1287
1288
1289
1290
1291
1292
1293
1294
1295
1296
1297
1298
1299
1300
1301
1302
1303
1304
1305
1306
1307
1308
1309
1310
1311
1312
1313
1314
1315
1316
1317
1318
1319
1320
1321
1322
1323
1324
1325
1326
1327
1328
1329
1330
1331
1332
1333
1334
1335
1336
1337
1338
1339
1340
1341
1342
1343
1344
1345
1346
1347
1348
1349
1350
1351
1352
1353
1354
1355
1356
1357
1358
1359
1360
1361
1362
1363
1364
1365
1366
1367
1368
1369
1370
1371
1372
1373
1374
1375
1376
1377
1378
1379
1380
1381
1382
1383
1384
1385
1386
1387
1388
1389
1390
1391
1392
1393
1394
1395
1396
1397
1398
1399
1400
1401
1402
1403
1404
1405
1406
1407
1408
1409
1410
1411
1412
1413
1414
1415
1416
1417
1418
1419
1420
1421
1422
1423
1424
1425
1426
1427
1428
1429
1430
1431
1432
1433
1434
1435
1436
1437
1438
1439
1440
1441
1442
1443
1444
1445
1446
1447
1448
1449
1450
1451
1452
1453
1454
1455
1456
1457
1458
1459
1460
1461
1462
1463
1464
1465
1466
1467
1468
1469
1470
1471
1472
1473
1474
1475
1476
} {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]
}

# EVIDENCE-OF: R-26684-40576 Each non-aggregate expression in the
# result-set is evaluated once for an arbitrarily selected row of the
# dataset.
#
# EVIDENCE-OF: R-27994-60376 The same arbitrarily selected row is used
# for each non-aggregate expression.
#
#   Note: The results of many of the queries in this block of tests are
#   technically undefined, as the documentation does not specify which row
#   SQLite will arbitrarily select to use for the evaluation of the
#   non-aggregate expressions.
#
drop_all_tables
do_execsql_test e_select-4.6.0 {
  CREATE TABLE a1(one PRIMARY KEY, two);
  INSERT INTO a1 VALUES(1, 1);
  INSERT INTO a1 VALUES(2, 3);
  INSERT INTO a1 VALUES(3, 6);
  INSERT INTO a1 VALUES(4, 10);

  CREATE TABLE a2(one PRIMARY KEY, three);
  INSERT INTO a2 VALUES(1, 1);
  INSERT INTO a2 VALUES(3, 2);
  INSERT INTO a2 VALUES(6, 3);
  INSERT INTO a2 VALUES(10, 4);
} {}
foreach {tn select res} {
  6.1 "SELECT one, two, count(*) FROM a1"                        {4 10 4} 
  6.2 "SELECT one, two, count(*) FROM a1 WHERE one<3"            {2 3 2} 
  6.3 "SELECT one, two, count(*) FROM a1 WHERE one>3"            {4 10 1} 
  6.4 "SELECT *, count(*) FROM a1 JOIN a2"                       {4 10 10 4 16} 
  6.5 "SELECT *, sum(three) FROM a1 NATURAL JOIN a2"             {3 6 2 3}
  6.6 "SELECT *, sum(three) FROM a1 NATURAL JOIN a2"             {3 6 2 3}
  6.7 "SELECT group_concat(three, ''), a1.* FROM a1 NATURAL JOIN a2" {12 3 6}
} {
  do_execsql_test e_select-4.$tn $select [list {*}$res]
}

# EVIDENCE-OF: R-04486-07266 Or, if the dataset contains zero rows, then
# each non-aggregate expression is evaluated against a row consisting
# entirely of NULL values.
#
foreach {tn select res} {
  7.1  "SELECT one, two, count(*) FROM a1 WHERE 0"           {{} {} 0}
  7.2  "SELECT sum(two), * FROM a1, a2 WHERE three>5"        {{} {} {} {} {}}
  7.3  "SELECT max(one) IS NULL, one IS NULL, two IS NULL FROM a1 WHERE two=7" {
    1 1 1
  }
} {
  do_execsql_test e_select-4.$tn $select [list {*}$res]
}  

# EVIDENCE-OF: R-64138-28774 An aggregate query without a GROUP BY
# clause always returns exactly one row of data, even if there are zero
# rows of input data.
#
foreach {tn select} {
  8.1  "SELECT count(*) FROM a1"
  8.2  "SELECT count(*) FROM a1 WHERE 0"
  8.3  "SELECT count(*) FROM a1 WHERE 1"
  8.4  "SELECT max(a1.one)+min(two), a1.one, two, * FROM a1, a2 WHERE 1"
  8.5  "SELECT max(a1.one)+min(two), a1.one, two, * FROM a1, a2 WHERE 0"
} {
  # Set $nRow to the number of rows returned by $select:
  set ::stmt [sqlite3_prepare_v2 db $select -1 DUMMY]
  set nRow 0
  while {"SQLITE_ROW" == [sqlite3_step $::stmt]} { incr nRow }
  set rc [sqlite3_finalize $::stmt]

  # Test that $nRow==1 and that statement execution was successful 
  # (rc==SQLITE_OK).
  do_test e_select-4.$tn [list list $rc $nRow] {SQLITE_OK 1}
}

drop_all_tables
do_execsql_test e_select-4.9.0 {
  CREATE TABLE b1(one PRIMARY KEY, two);
  INSERT INTO b1 VALUES(1, 'o');
  INSERT INTO b1 VALUES(4, 'f');
  INSERT INTO b1 VALUES(3, 't');
  INSERT INTO b1 VALUES(2, 't');
  INSERT INTO b1 VALUES(5, 'f');
  INSERT INTO b1 VALUES(7, 's');
  INSERT INTO b1 VALUES(6, 's');

  CREATE TABLE b2(x, y);
  INSERT INTO b2 VALUES(NULL, 0);
  INSERT INTO b2 VALUES(NULL, 1);
  INSERT INTO b2 VALUES('xyz', 2);
  INSERT INTO b2 VALUES('abc', 3);
  INSERT INTO b2 VALUES('xyz', 4);

  CREATE TABLE b3(a COLLATE nocase, b COLLATE binary);
  INSERT INTO b3 VALUES('abc', 'abc');
  INSERT INTO b3 VALUES('aBC', 'aBC');
  INSERT INTO b3 VALUES('Def', 'Def');
  INSERT INTO b3 VALUES('dEF', 'dEF');
} {}

# EVIDENCE-OF: R-57754-57109 If the SELECT statement is an aggregate
# query with a GROUP BY clause, then each of the expressions specified
# as part of the GROUP BY clause is evaluated for each row of the
# dataset. Each row is then assigned to a "group" based on the results;
# rows for which the results of evaluating the GROUP BY expressions are
# the same are assigned to the same group.
#
foreach {tn select res} {
  9.1  "SELECT group_concat(one), two FROM b1 GROUP BY two" {
    4,5 f   1 o   7,6   s 3,2 t
  }
  9.2  "SELECT group_concat(one), sum(one) FROM b1 GROUP BY (one>4)" {
    1,4,3,2 10    5,7,6 18
  }
  9.3  "SELECT group_concat(one) FROM b1 GROUP BY (two>'o'), one%2" {
    4  1,5    2,6   3,7
  }
  9.4  "SELECT group_concat(one) FROM b1 GROUP BY (one==2 OR two=='o')" {
    4,3,5,7,6    1,2
  }
} {
  do_execsql_test e_select-4.$tn $select [list {*}$res]
}  

# EVIDENCE-OF: R-14926-50129 For the purposes of grouping rows, NULL
# values are considered equal.
#
foreach {tn select res} {
  10.1  "SELECT group_concat(y) FROM b2 GROUP BY x" {0,1   3   2,4}
  10.2  "SELECT count(*) FROM b2 GROUP BY CASE WHEN y<4 THEN NULL ELSE 0 END" {
    4 1
  }
} {
  do_execsql_test e_select-4.$tn $select [list {*}$res]
}  

# EVIDENCE-OF: R-10470-30318 The usual rules for selecting a collation
# sequence with which to compare text values apply when evaluating
# expressions in a GROUP BY clause.
#
foreach {tn select res} {
  11.1  "SELECT count(*) FROM b3 GROUP BY b"      {1 1 1 1}
  11.2  "SELECT count(*) FROM b3 GROUP BY a"      {2 2}
  11.3  "SELECT count(*) FROM b3 GROUP BY +b"     {1 1 1 1}
  11.4  "SELECT count(*) FROM b3 GROUP BY +a"     {2 2}
  11.5  "SELECT count(*) FROM b3 GROUP BY b||''"  {1 1 1 1}
  11.6  "SELECT count(*) FROM b3 GROUP BY a||''"  {1 1 1 1}
} {
  do_execsql_test e_select-4.$tn $select [list {*}$res]
}

# EVIDENCE-OF: R-63573-50730 The expressions in a GROUP BY clause may
# not be aggregate expressions.
#
foreach {tn select} {
  12.1  "SELECT * FROM b3 GROUP BY count(*)"
  12.2  "SELECT max(a) FROM b3 GROUP BY max(b)"
  12.3  "SELECT group_concat(a) FROM b3 GROUP BY a, max(b)"
} {
  set res {1 {aggregate functions are not allowed in the GROUP BY clause}}
  do_catchsql_test e_select-4.$tn $select $res
}

# EVIDENCE-OF: R-40359-04817 If a HAVING clause is specified, it is
# evaluated once for each group of rows and cast to an integer value. If
# the result of evaluating the HAVING clause is NULL or zero (integer
# value 0), the group is discarded.
#
#   This requirement is tested by all e_select-4.13.* tests.
#
# EVIDENCE-OF: R-04132-09474 If the HAVING clause is an aggregate
# expression, it is evaluated across all rows in the group.
#
#   Tested by e_select-4.13.1.*
#
# EVIDENCE-OF: R-28262-47447 If a HAVING clause is a non-aggregate
# expression, it is evaluated with respect to an arbitrarily selected
# row from the group.
#
#   Tested by e_select-4.13.2.*
#
do_execsql_test e_select-4.13.0 {
  CREATE TABLE c1(up, down);
  INSERT INTO c1 VALUES('x', 1);
  INSERT INTO c1 VALUES('x', 2);
  INSERT INTO c1 VALUES('x', 4);
  INSERT INTO c1 VALUES('x', 8);
  INSERT INTO c1 VALUES('y', 16);
  INSERT INTO c1 VALUES('y', 32);
} {}

foreach {tn select res} {
  13.1.1  "SELECT up FROM c1 GROUP BY up HAVING count(*)>3" {x}
  13.1.2  "SELECT up FROM c1 GROUP BY up HAVING sum(down)>16" {y}
  13.1.3  "SELECT up FROM c1 GROUP BY up HAVING sum(down)<16" {x}
  13.1.3  "SELECT up||down FROM c1 GROUP BY (down<5) HAVING max(down)<10" {x4}

  13.2.1  "SELECT up FROM c1 GROUP BY up HAVING down>10" {y}
  13.2.2  "SELECT up FROM c1 GROUP BY up HAVING up='y'"  {y}
} {
  do_execsql_test e_select-4.$tn $select [list {*}$res]
}



finish_test