/ Check-in [497ee36c]
Login

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

Overview
Comment:Fix LIMIT and OFFSET so that they work and do not leak memory even on complex queries involving deeply nested views of UNION ALL compounds. Ticket [db4d96798da8]. Secondary to ticket [d58ccbb3f1b7].
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: 497ee36cb8d07c936e6896135163c5cd7e94bdde
User & Date: drh 2013-01-29 23:55:50
Context
2013-01-30
01:46
Remove some debugging logic accidently left in the limit.test module. check-in: 7d346250 user: drh tags: trunk
2013-01-29
23:55
Fix LIMIT and OFFSET so that they work and do not leak memory even on complex queries involving deeply nested views of UNION ALL compounds. Ticket [db4d96798da8]. Secondary to ticket [d58ccbb3f1b7]. check-in: 497ee36c user: drh tags: trunk
19:14
New debugging pragmas: PRAGMA vdbe_debug=ON is short-hand for the sql_trace, vdbe_listing, and vdbe_trace pragmas. PRAGMA vdbe_debug enables tracing of sqlite3VdbeAddOp() calls. None of this is active unless compiled with SQLITE_DEBUG. check-in: ae565ff3 user: drh tags: trunk
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to src/select.c.

  1703   1703     /* Generate code for the left and right SELECT statements.
  1704   1704     */
  1705   1705     switch( p->op ){
  1706   1706       case TK_ALL: {
  1707   1707         int addr = 0;
  1708   1708         int nLimit;
  1709   1709         assert( !pPrior->pLimit );
         1710  +      pPrior->iLimit = p->iLimit;
         1711  +      pPrior->iOffset = p->iOffset;
  1710   1712         pPrior->pLimit = p->pLimit;
  1711   1713         pPrior->pOffset = p->pOffset;
  1712   1714         explainSetInteger(iSub1, pParse->iNextSelectId);
  1713   1715         rc = sqlite3Select(pParse, pPrior, &dest);
  1714   1716         p->pLimit = 0;
  1715   1717         p->pOffset = 0;
  1716   1718         if( rc ){
................................................................................
  2959   2961     **
  2960   2962     ** We call this the "compound-subquery flattening".
  2961   2963     */
  2962   2964     for(pSub=pSub->pPrior; pSub; pSub=pSub->pPrior){
  2963   2965       Select *pNew;
  2964   2966       ExprList *pOrderBy = p->pOrderBy;
  2965   2967       Expr *pLimit = p->pLimit;
         2968  +    Expr *pOffset = p->pOffset;
  2966   2969       Select *pPrior = p->pPrior;
  2967   2970       p->pOrderBy = 0;
  2968   2971       p->pSrc = 0;
  2969   2972       p->pPrior = 0;
  2970   2973       p->pLimit = 0;
         2974  +    p->pOffset = 0;
  2971   2975       pNew = sqlite3SelectDup(db, p, 0);
         2976  +    p->pOffset = pOffset;
  2972   2977       p->pLimit = pLimit;
  2973   2978       p->pOrderBy = pOrderBy;
  2974   2979       p->pSrc = pSrc;
  2975   2980       p->op = TK_ALL;
  2976   2981       p->pRightmost = 0;
  2977   2982       if( pNew==0 ){
  2978   2983         pNew = pPrior;

Changes to test/limit.test.

   464    464   } {1 {no such column: x}}
   465    465   do_test limit-12.4 {
   466    466     catchsql {
   467    467        SELECT * FROM t1 LIMIT 1 OFFSET x
   468    468     }
   469    469   } {1 {no such column: x}}
   470    470   
          471  +db close
          472  +sqlite3_shutdown
          473  +sqlite3_config_lookaside 0 0
          474  +sqlite3_initialize
          475  +sqlite3 db :memory:
          476  +# Ticket [db4d96798da8b]
          477  +# LIMIT does not work with nested views containing UNION ALL 
          478  +#
          479  +do_test limit-13.1 {
          480  +  db eval {
          481  +    CREATE TABLE t13(x);
          482  +    INSERT INTO t13 VALUES(1),(2);
          483  +    CREATE VIEW v13a AS SELECT x AS y FROM t13;
          484  +    CREATE VIEW v13b AS SELECT y AS z FROM v13a UNION ALL SELECT y+10 FROM v13a;
          485  +    CREATE VIEW v13c AS SELECT z FROM v13b UNION ALL SELECT z+20 FROM v13b;
          486  +  }
          487  +} {}
          488  +do_test limit-13.2 {
          489  +  db eval {SELECT z FROM v13c LIMIT 1}
          490  +} {1}
          491  +do_test limit-13.3 {
          492  +  db eval {SELECT z FROM v13c LIMIT 2}
          493  +} {1 2}
          494  +do_test limit-13.4 {
          495  +  db eval {SELECT z FROM v13c LIMIT 3}
          496  +} {1 2 11}
          497  +do_test limit-13.5 {
          498  +  db eval {SELECT z FROM v13c LIMIT 4}
          499  +} {1 2 11 12}
          500  +do_test limit-13.6 {
          501  +  db eval {SELECT z FROM v13c LIMIT 5}
          502  +} {1 2 11 12 21}
          503  +do_test limit-13.7 {
          504  +  db eval {SELECT z FROM v13c LIMIT 6}
          505  +} {1 2 11 12 21 22}
          506  +do_test limit-13.8 {
          507  +  db eval {SELECT z FROM v13c LIMIT 7}
          508  +} {1 2 11 12 21 22 31}
          509  +do_test limit-13.9 {
          510  +  db eval {SELECT z FROM v13c LIMIT 8}
          511  +} {1 2 11 12 21 22 31 32}
          512  +do_test limit-13.10 {
          513  +  db eval {SELECT z FROM v13c LIMIT 9}
          514  +} {1 2 11 12 21 22 31 32}
          515  +do_test limit-13.11 {
          516  +  db eval {SELECT z FROM v13c LIMIT 1 OFFSET 1}
          517  +} {2}
          518  +do_test limit-13.12 {
          519  +  db eval {SELECT z FROM v13c LIMIT 2 OFFSET 1}
          520  +} {2 11}
          521  +do_test limit-13.13 {
          522  +  db eval {SELECT z FROM v13c LIMIT 3 OFFSET 1}
          523  +} {2 11 12}
          524  +do_test limit-13.14 {
          525  +  db eval {SELECT z FROM v13c LIMIT 4 OFFSET 1}
          526  +} {2 11 12 21}
          527  +do_test limit-13.15 {
          528  +  db eval {SELECT z FROM v13c LIMIT 5 OFFSET 1}
          529  +} {2 11 12 21 22}
          530  +do_test limit-13.16 {
          531  +  db eval {SELECT z FROM v13c LIMIT 6 OFFSET 1}
          532  +} {2 11 12 21 22 31}
          533  +do_test limit-13.17 {
          534  +  db eval {SELECT z FROM v13c LIMIT 7 OFFSET 1}
          535  +} {2 11 12 21 22 31 32}
          536  +do_test limit-13.18 {
          537  +  db eval {SELECT z FROM v13c LIMIT 8 OFFSET 1}
          538  +} {2 11 12 21 22 31 32}
          539  +do_test limit-13.21 {
          540  +  db eval {SELECT z FROM v13c LIMIT 1 OFFSET 2}
          541  +} {11}
          542  +do_test limit-13.22 {
          543  +  db eval {SELECT z FROM v13c LIMIT 2 OFFSET 2}
          544  +} {11 12}
          545  +do_test limit-13.23 {
          546  +  db eval {SELECT z FROM v13c LIMIT 3 OFFSET 2}
          547  +} {11 12 21}
          548  +do_test limit-13.24 {
          549  +  db eval {SELECT z FROM v13c LIMIT 4 OFFSET 2}
          550  +} {11 12 21 22}
          551  +do_test limit-13.25 {
          552  +  db eval {SELECT z FROM v13c LIMIT 5 OFFSET 2}
          553  +} {11 12 21 22 31}
          554  +do_test limit-13.26 {
          555  +  db eval {SELECT z FROM v13c LIMIT 6 OFFSET 2}
          556  +} {11 12 21 22 31 32}
          557  +do_test limit-13.27 {
          558  +  db eval {SELECT z FROM v13c LIMIT 7 OFFSET 2}
          559  +} {11 12 21 22 31 32}
          560  +do_test limit-13.31 {
          561  +  db eval {SELECT z FROM v13c LIMIT 1 OFFSET 3}
          562  +} {12}
          563  +do_test limit-13.32 {
          564  +  db eval {SELECT z FROM v13c LIMIT 2 OFFSET 3}
          565  +} {12 21}
          566  +do_test limit-13.33 {
          567  +  db eval {SELECT z FROM v13c LIMIT 3 OFFSET 3}
          568  +} {12 21 22}
          569  +do_test limit-13.34 {
          570  +  db eval {SELECT z FROM v13c LIMIT 4 OFFSET 3}
          571  +} {12 21 22 31}
          572  +do_test limit-13.35 {
          573  +  db eval {SELECT z FROM v13c LIMIT 5 OFFSET 3}
          574  +} {12 21 22 31 32}
          575  +do_test limit-13.36 {
          576  +  db eval {SELECT z FROM v13c LIMIT 6 OFFSET 3}
          577  +} {12 21 22 31 32}
          578  +do_test limit-13.41 {
          579  +  db eval {SELECT z FROM v13c LIMIT 1 OFFSET 4}
          580  +} {21}
          581  +do_test limit-13.42 {
          582  +  db eval {SELECT z FROM v13c LIMIT 2 OFFSET 4}
          583  +} {21 22}
          584  +do_test limit-13.43 {
          585  +  db eval {SELECT z FROM v13c LIMIT 3 OFFSET 4}
          586  +} {21 22 31}
          587  +do_test limit-13.44 {
          588  +  db eval {SELECT z FROM v13c LIMIT 4 OFFSET 4}
          589  +} {21 22 31 32}
          590  +do_test limit-13.45 {
          591  +  db eval {SELECT z FROM v13c LIMIT 5 OFFSET 4}
          592  +} {21 22 31 32}
          593  +do_test limit-13.51 {
          594  +  db eval {SELECT z FROM v13c LIMIT 1 OFFSET 5}
          595  +} {22}
          596  +do_test limit-13.52 {
          597  +  db eval {SELECT z FROM v13c LIMIT 2 OFFSET 5}
          598  +} {22 31}
          599  +do_test limit-13.53 {
          600  +  db eval {SELECT z FROM v13c LIMIT 3 OFFSET 5}
          601  +} {22 31 32}
          602  +do_test limit-13.54 {
          603  +  db eval {SELECT z FROM v13c LIMIT 4 OFFSET 5}
          604  +} {22 31 32}
          605  +do_test limit-13.61 {
          606  +  db eval {SELECT z FROM v13c LIMIT 1 OFFSET 6}
          607  +} {31}
          608  +do_test limit-13.62 {
          609  +  db eval {SELECT z FROM v13c LIMIT 2 OFFSET 6}
          610  +} {31 32}
          611  +do_test limit-13.63 {
          612  +  db eval {SELECT z FROM v13c LIMIT 3 OFFSET 6}
          613  +} {31 32}
          614  +do_test limit-13.71 {
          615  +  db eval {SELECT z FROM v13c LIMIT 1 OFFSET 7}
          616  +} {32}
          617  +do_test limit-13.72 {
          618  +  db eval {SELECT z FROM v13c LIMIT 2 OFFSET 7}
          619  +} {32}
          620  +do_test limit-13.81 {
          621  +  db eval {SELECT z FROM v13c LIMIT 1 OFFSET 8}
          622  +} {}
   471    623   
   472    624   finish_test