/ Check-in [8560091e]
Login

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

Overview
Comment:Update a requirement mark and add some additional test cases to cover the requirement associated with ORDER BY and LIMIT on compound SELECT statements.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: 8560091e85f2f99a24810648868bf2e081c32698
User & Date: drh 2014-01-30 14:10:00
Context
2014-01-30
15:03
Fix requirements marks only. No changes to code or tests. check-in: 4a34378b user: drh tags: trunk
14:10
Update a requirement mark and add some additional test cases to cover the requirement associated with ORDER BY and LIMIT on compound SELECT statements. check-in: 8560091e user: drh tags: trunk
12:45
Enhance the MSVC makefile for better debugging symbol support. check-in: c9bef0f2 user: mistachkin tags: trunk
Changes
Hide Diffs Side-by-Side Diffs Show Whitespace Changes Patch

Changes to test/e_select.test.

  1367   1367     24  "SELECT a FROM j1 LIMIT (SELECT e FROM j2) INTERSECT SELECT g FROM j2,j3" 
  1368   1368         LIMIT {INTERSECT}
  1369   1369   } {
  1370   1370     set err "$op1 clause should come after $op2 not before"
  1371   1371     do_catchsql_test e_select-7.2.$tn $select [list 1 $err]
  1372   1372   }
  1373   1373   
  1374         -# EVIDENCE-OF: R-22874-32655 ORDER BY and LIMIT clauses may only occur
  1375         -# at the end of the entire compound SELECT.
         1374  +# EVIDENCE-OF: R-45440-25633 ORDER BY and LIMIT clauses may only occur
         1375  +# at the end of the entire compound SELECT, and then only if the final
         1376  +# element of the compound is not a VALUES clause.
  1376   1377   #
  1377   1378   foreach {tn select} {
  1378   1379     1   "SELECT * FROM j1 UNION ALL SELECT * FROM j2,j3 ORDER BY a"
  1379   1380     2   "SELECT count(*) FROM j1 UNION ALL SELECT max(e) FROM j2 ORDER BY 1"
  1380   1381     3   "SELECT count(*), * FROM j1 UNION ALL SELECT *,* FROM j2 ORDER BY 1,2,3"
  1381   1382     4   "SELECT * FROM j1 UNION ALL SELECT * FROM j2,j3 LIMIT 10" 
  1382   1383     5   "SELECT * FROM j1 UNION ALL SELECT * FROM j2,j3 LIMIT 10 OFFSET 5" 
  1383   1384     6   "SELECT a FROM j1 UNION ALL SELECT g FROM j2,j3 LIMIT (SELECT 10)" 
  1384   1385   
  1385   1386     7   "SELECT * FROM j1 UNION SELECT * FROM j2,j3 ORDER BY a"
  1386   1387     8   "SELECT count(*) FROM j1 UNION SELECT max(e) FROM j2 ORDER BY 1"
         1388  +  8b  "VALUES('8b') UNION SELECT max(e) FROM j2 ORDER BY 1"
  1387   1389     9   "SELECT count(*), * FROM j1 UNION SELECT *,* FROM j2 ORDER BY 1,2,3"
  1388   1390     10  "SELECT * FROM j1 UNION SELECT * FROM j2,j3 LIMIT 10" 
  1389   1391     11  "SELECT * FROM j1 UNION SELECT * FROM j2,j3 LIMIT 10 OFFSET 5" 
  1390   1392     12  "SELECT a FROM j1 UNION SELECT g FROM j2,j3 LIMIT (SELECT 10)" 
  1391   1393   
  1392   1394     13  "SELECT * FROM j1 EXCEPT SELECT * FROM j2,j3 ORDER BY a"
  1393   1395     14  "SELECT count(*) FROM j1 EXCEPT SELECT max(e) FROM j2 ORDER BY 1"
................................................................................
  1401   1403     21  "SELECT count(*), * FROM j1 INTERSECT SELECT *,* FROM j2 ORDER BY 1,2,3"
  1402   1404     22  "SELECT * FROM j1 INTERSECT SELECT * FROM j2,j3 LIMIT 10" 
  1403   1405     23  "SELECT * FROM j1 INTERSECT SELECT * FROM j2,j3 LIMIT 10 OFFSET 5" 
  1404   1406     24  "SELECT a FROM j1 INTERSECT SELECT g FROM j2,j3 LIMIT (SELECT 10)" 
  1405   1407   } {
  1406   1408     do_test e_select-7.3.$tn { catch {execsql $select} msg } 0
  1407   1409   }
         1410  +foreach {tn select} {
         1411  +  50   "SELECT * FROM j1 ORDER BY 1 UNION ALL SELECT * FROM j2,j3"
         1412  +  51   "SELECT * FROM j1 LIMIT 1 UNION ALL SELECT * FROM j2,j3"
         1413  +  52   "SELECT count(*) FROM j1 UNION ALL VALUES(11) ORDER BY 1"
         1414  +  53   "SELECT count(*) FROM j1 UNION ALL VALUES(11) LIMIT 1"
         1415  +} {
         1416  +  do_test e_select-7.3.$tn { catch {execsql $select} msg } 1
         1417  +}
  1408   1418   
  1409   1419   # EVIDENCE-OF: R-08531-36543 A compound SELECT created using UNION ALL
  1410   1420   # operator returns all the rows from the SELECT to the left of the UNION
  1411   1421   # ALL operator, and all the rows from the SELECT to the right of it.
  1412   1422   #
  1413   1423   drop_all_tables
  1414   1424   do_execsql_test e_select-7.4.0 {