/ Check-in [b0450120]
Login

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

Overview
Comment:Fix long-standing bugs with the handling of LIMIT clausing in compound SELECT statements with FROM clause subqueries. Ticket [38cb5df375078d3f9].
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: b0450120eabfe5455cea1ed04cec587a5540b6b7
User & Date: drh 2010-10-06 18:55:37
Context
2010-10-06
20:25
Update the configure script and Makefile so that they work with 3.7.3. check-in: 97b63a11 user: drh tags: trunk
18:55
Fix long-standing bugs with the handling of LIMIT clausing in compound SELECT statements with FROM clause subqueries. Ticket [38cb5df375078d3f9]. check-in: b0450120 user: drh tags: trunk
16:42
Fix minor test suite problems causing errors on OSX. check-in: 759c954f user: dan tags: trunk
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to src/select.c.

   438    438       addr1 = sqlite3VdbeAddOp1(v, OP_IfZero, iLimit);
   439    439       sqlite3VdbeAddOp2(v, OP_AddImm, iLimit, -1);
   440    440       addr2 = sqlite3VdbeAddOp0(v, OP_Goto);
   441    441       sqlite3VdbeJumpHere(v, addr1);
   442    442       sqlite3VdbeAddOp1(v, OP_Last, pOrderBy->iECursor);
   443    443       sqlite3VdbeAddOp1(v, OP_Delete, pOrderBy->iECursor);
   444    444       sqlite3VdbeJumpHere(v, addr2);
   445         -    pSelect->iLimit = 0;
   446    445     }
   447    446   }
   448    447   
   449    448   /*
   450    449   ** Add code to implement the OFFSET
   451    450   */
   452    451   static void codeOffset(
................................................................................
   717    716       default: {
   718    717         assert( eDest==SRT_Discard );
   719    718         break;
   720    719       }
   721    720   #endif
   722    721     }
   723    722   
   724         -  /* Jump to the end of the loop if the LIMIT is reached.
          723  +  /* Jump to the end of the loop if the LIMIT is reached.  Except, if
          724  +  ** there is a sorter, in which case the sorter has already limited
          725  +  ** the output for us.
   725    726     */
   726         -  if( p->iLimit ){
   727         -    assert( pOrderBy==0 );  /* If there is an ORDER BY, the call to
   728         -                            ** pushOntoSorter() would have cleared p->iLimit */
          727  +  if( pOrderBy==0 && p->iLimit ){
   729    728       sqlite3VdbeAddOp3(v, OP_IfZero, p->iLimit, iBreak, -1);
   730    729     }
   731    730   }
   732    731   
   733    732   /*
   734    733   ** Given an expression list, generate a KeyInfo structure that records
   735    734   ** the collating sequence for each expression in that expression list.
................................................................................
   856    855         }
   857    856         break;
   858    857       }
   859    858     }
   860    859     sqlite3ReleaseTempReg(pParse, regRow);
   861    860     sqlite3ReleaseTempReg(pParse, regRowid);
   862    861   
   863         -  /* LIMIT has been implemented by the pushOntoSorter() routine.
   864         -  */
   865         -  assert( p->iLimit==0 );
   866         -
   867    862     /* The bottom of the loop
   868    863     */
   869    864     sqlite3VdbeResolveLabel(v, addrContinue);
   870    865     sqlite3VdbeAddOp2(v, OP_Next, iTab, addr);
   871    866     sqlite3VdbeResolveLabel(v, addrBreak);
   872    867     if( eDest==SRT_Output || eDest==SRT_Coroutine ){
   873    868       sqlite3VdbeAddOp2(v, OP_Close, pseudoTab, 0);
................................................................................
  2177   2172         }
  2178   2173       }
  2179   2174     }
  2180   2175    
  2181   2176     /* Separate the left and the right query from one another
  2182   2177     */
  2183   2178     p->pPrior = 0;
  2184         -  pPrior->pRightmost = 0;
  2185   2179     sqlite3ResolveOrderGroupBy(pParse, p, p->pOrderBy, "ORDER");
  2186   2180     if( pPrior->pPrior==0 ){
  2187   2181       sqlite3ResolveOrderGroupBy(pParse, pPrior, pPrior->pOrderBy, "ORDER");
  2188   2182     }
  2189   2183   
  2190   2184     /* Compute the limit registers */
  2191   2185     computeLimitRegisters(pParse, p, labelEnd);

Added test/tkt-38cb5df375.test.

            1  +# 2010 October 6
            2  +#
            3  +# The author disclaims copyright to this source code.  In place of
            4  +# a legal notice, here is a blessing:
            5  +#
            6  +#    May you do good and not evil.
            7  +#    May you find forgiveness for yourself and forgive others.
            8  +#    May you share freely, never taking more than you give.
            9  +#
           10  +#***********************************************************************
           11  +# This file implements regression tests for SQLite library. Specifically,
           12  +# it tests that ticket [38cb5df375078d3f9711482d2a1615d09f6b3f33] has
           13  +# been resolved.
           14  +#
           15  +
           16  +set testdir [file dirname $argv0]
           17  +source $testdir/tester.tcl
           18  +
           19  +do_test tkt-38cb5df375.0 {
           20  +  execsql {
           21  +    CREATE TABLE t1(a);
           22  +    INSERT INTO t1 VALUES(1);
           23  +    INSERT INTO t1 VALUES(2);
           24  +    INSERT INTO t1 SELECT a+2 FROM t1;
           25  +    INSERT INTO t1 SELECT a+4 FROM t1;
           26  +  }
           27  +} {}
           28  +
           29  +foreach ii {1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16} {
           30  +  do_test tkt-38cb5df375.1.$ii {
           31  +    execsql {
           32  +      SELECT * FROM (SELECT * FROM t1 ORDER BY a)
           33  +      UNION ALL SELECT 9 FROM (SELECT a FROM t1)
           34  +      LIMIT $::ii;
           35  +    }
           36  +  } [lrange {1 2 3 4 5 6 7 8 9 9 9 9 9 9 9 9} 0 [expr {$ii-1}]]
           37  +}
           38  +foreach ii {1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16} {
           39  +  do_test tkt-38cb5df375.2.$ii {
           40  +    execsql {
           41  +      SELECT 9 FROM (SELECT * FROM t1)
           42  +      UNION ALL SELECT a FROM (SELECT a FROM t1 ORDER BY a)
           43  +      LIMIT $::ii;
           44  +    }
           45  +  } [lrange {9 9 9 9 9 9 9 9 1 2 3 4 5 6 7 8} 0 [expr {$ii-1}]]
           46  +}
           47  +foreach ii {1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16} {
           48  +  do_test tkt-38cb5df375.3.$ii {
           49  +    execsql {
           50  +      SELECT a FROM (SELECT * FROM t1 ORDER BY a)
           51  +      UNION ALL SELECT a FROM (SELECT a FROM t1 ORDER BY a)
           52  +      LIMIT $::ii;
           53  +    }
           54  +  } [lrange {1 2 3 4 5 6 7 8 1 2 3 4 5 6 7 8} 0 [expr {$ii-1}]]
           55  +}
           56  +foreach ii {1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16} {
           57  +  do_test tkt-38cb5df375.4.$ii {
           58  +    execsql {
           59  +      SELECT 0 FROM (SELECT * FROM t1)
           60  +      UNION ALL SELECT 9 FROM (SELECT a FROM t1)
           61  +      LIMIT $::ii;
           62  +    }
           63  +  } [lrange {0 0 0 0 0 0 0 0 9 9 9 9 9 9 9 9} 0 [expr {$ii-1}]]
           64  +}
           65  +foreach ii {1 2 3 4} {
           66  +  do_test tkt-38cb5df375.5.$ii {
           67  +    execsql {
           68  +      SELECT 0 FROM (SELECT * FROM t1)
           69  +      UNION SELECT 9 FROM (SELECT a FROM t1)
           70  +      LIMIT $::ii;
           71  +    }
           72  +  } [lrange {0 9} 0 [expr {$ii-1}]]
           73  +}
           74  +
           75  +foreach ii {1 2 3 4 5 6 7 8 9 10 11} {
           76  +  do_test tkt-38cb5df375.11.$ii {
           77  +    execsql {
           78  +      SELECT * FROM (SELECT * FROM t1 ORDER BY a LIMIT 3)
           79  +      UNION ALL SELECT 9 FROM (SELECT a FROM t1)
           80  +      LIMIT $::ii;
           81  +    }
           82  +  } [lrange {1 2 3 9 9 9 9 9 9 9 9} 0 [expr {$ii-1}]]
           83  +}
           84  +foreach ii {1 2 3 4 5 6 7 8 9 10 11} {
           85  +  do_test tkt-38cb5df375.12.$ii {
           86  +    execsql {
           87  +      SELECT 9 FROM (SELECT * FROM t1)
           88  +      UNION ALL SELECT a FROM (SELECT a FROM t1 ORDER BY a LIMIT 3)
           89  +      LIMIT $::ii;
           90  +    }
           91  +  } [lrange {9 9 9 9 9 9 9 9 1 2 3} 0 [expr {$ii-1}]]
           92  +}
           93  +foreach ii {1 2 3 4 5 6} {
           94  +  do_test tkt-38cb5df375.13.$ii {
           95  +    execsql {
           96  +      SELECT a FROM (SELECT * FROM t1 ORDER BY a LIMIT 3)
           97  +      UNION ALL SELECT a FROM (SELECT a FROM t1 ORDER BY a LIMIT 3)
           98  +      LIMIT $::ii;
           99  +    }
          100  +  } [lrange {1 2 3 1 2 3} 0 [expr {$ii-1}]]
          101  +}
          102  +foreach ii {1 2 3 4 5 6} {
          103  +  do_test tkt-38cb5df375.14.$ii {
          104  +    execsql {
          105  +      SELECT 0 FROM (SELECT * FROM t1 LIMIT 3)
          106  +      UNION ALL SELECT 9 FROM (SELECT a FROM t1 LIMIT 3)
          107  +      LIMIT $::ii;
          108  +    }
          109  +  } [lrange {0 0 0 9 9 9} 0 [expr {$ii-1}]]
          110  +}
          111  +
          112  +foreach ii {1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16} {
          113  +  do_test tkt-38cb5df375.21.$ii {
          114  +    execsql {
          115  +      SELECT * FROM (SELECT * FROM t1 ORDER BY a)
          116  +      UNION ALL SELECT 9 FROM (SELECT a FROM t1)
          117  +      ORDER BY 1
          118  +      LIMIT $::ii;
          119  +    }
          120  +  } [lrange {1 2 3 4 5 6 7 8 9 9 9 9 9 9 9 9} 0 [expr {$ii-1}]]
          121  +}
          122  +foreach ii {1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16} {
          123  +  do_test tkt-38cb5df375.22.$ii {
          124  +    execsql {
          125  +      SELECT 9 FROM (SELECT * FROM t1)
          126  +      UNION ALL SELECT a FROM (SELECT a FROM t1 ORDER BY a)
          127  +      ORDER BY 1
          128  +      LIMIT $::ii;
          129  +    }
          130  +  } [lrange {1 2 3 4 5 6 7 8 9 9 9 9 9 9 9 9} 0 [expr {$ii-1}]]
          131  +}
          132  +foreach ii {1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16} {
          133  +  do_test tkt-38cb5df375.23.$ii {
          134  +    execsql {
          135  +      SELECT a FROM (SELECT * FROM t1 ORDER BY a)
          136  +      UNION ALL SELECT a FROM (SELECT a FROM t1 ORDER BY a)
          137  +      ORDER BY 1 DESC
          138  +      LIMIT $::ii;
          139  +    }
          140  +  } [lrange {8 8 7 7 6 6 5 5 4 4 3 3 2 2 1 1} 0 [expr {$ii-1}]]
          141  +}
          142  +foreach ii {1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16} {
          143  +  do_test tkt-38cb5df375.24.$ii {
          144  +    execsql {
          145  +      SELECT 0 FROM (SELECT * FROM t1)
          146  +      UNION ALL SELECT 9 FROM (SELECT a FROM t1)
          147  +      ORDER BY 1
          148  +      LIMIT $::ii;
          149  +    }
          150  +  } [lrange {0 0 0 0 0 0 0 0 9 9 9 9 9 9 9 9} 0 [expr {$ii-1}]]
          151  +}
          152  +
          153  +foreach ii {1 2 3 4 5 6 7 8 9 10 11} {
          154  +  do_test tkt-38cb5df375.31.$ii {
          155  +    execsql {
          156  +      SELECT * FROM (SELECT * FROM t1 ORDER BY a LIMIT 3)
          157  +      UNION ALL SELECT 9 FROM (SELECT a FROM t1)
          158  +      ORDER BY 1
          159  +      LIMIT $::ii;
          160  +    }
          161  +  } [lrange {1 2 3 9 9 9 9 9 9 9 9} 0 [expr {$ii-1}]]
          162  +}
          163  +foreach ii {1 2 3 4 5 6 7 8 9 10 11} {
          164  +  do_test tkt-38cb5df375.32.$ii {
          165  +    execsql {
          166  +      SELECT 9 FROM (SELECT * FROM t1)
          167  +      UNION ALL SELECT a FROM (SELECT a FROM t1 ORDER BY a LIMIT 3)
          168  +      ORDER BY 1
          169  +      LIMIT $::ii;
          170  +    }
          171  +  } [lrange {1 2 3 9 9 9 9 9 9 9 9} 0 [expr {$ii-1}]]
          172  +}
          173  +foreach ii {1 2 3 4 5 6 7} {
          174  +  do_test tkt-38cb5df375.33.$ii {
          175  +    execsql {
          176  +      SELECT a FROM (SELECT * FROM t1 ORDER BY a LIMIT 4)
          177  +      UNION ALL SELECT 90+a FROM (SELECT a FROM t1 ORDER BY a LIMIT 3)
          178  +      ORDER BY 1
          179  +      LIMIT $::ii;
          180  +    }
          181  +  } [lrange {1 2 3 4 91 92 93} 0 [expr {$ii-1}]]
          182  +}
          183  +foreach ii {1 2 3 4 5 6 7} {
          184  +  do_test tkt-38cb5df375.34.$ii {
          185  +    execsql {
          186  +      SELECT a FROM (SELECT * FROM t1 ORDER BY a LIMIT 2)
          187  +      UNION ALL SELECT a FROM (SELECT a FROM t1 ORDER BY a LIMIT 5)
          188  +      ORDER BY 1
          189  +      LIMIT $::ii;
          190  +    }
          191  +  } [lrange {1 1 2 2 3 4 5} 0 [expr {$ii-1}]]
          192  +}
          193  +foreach ii {1 2 3 4 5 6 7} {
          194  +  do_test tkt-38cb5df375.35.$ii {
          195  +    execsql {
          196  +      SELECT a FROM (SELECT * FROM t1 ORDER BY a LIMIT 5)
          197  +      UNION ALL SELECT a FROM (SELECT a FROM t1 ORDER BY a LIMIT 2)
          198  +      ORDER BY 1
          199  +      LIMIT $::ii;
          200  +    }
          201  +  } [lrange {1 1 2 2 3 4 5} 0 [expr {$ii-1}]]
          202  +}
          203  +foreach ii {1 2 3 4 5 6 7} {
          204  +  do_test tkt-38cb5df375.35b.$ii {
          205  +    execsql {
          206  +      SELECT a FROM (SELECT * FROM t1 ORDER BY a LIMIT 5)
          207  +      UNION ALL SELECT a+10 FROM (SELECT a FROM t1 ORDER BY a LIMIT 2)
          208  +      ORDER BY 1
          209  +      LIMIT $::ii;
          210  +    }
          211  +  } [lrange {1 2 3 4 5 11 12} 0 [expr {$ii-1}]]
          212  +}
          213  +foreach ii {1 2 3 4 5 6 7} {
          214  +  do_test tkt-38cb5df375.35c.$ii {
          215  +    execsql {
          216  +      SELECT a FROM (SELECT * FROM t1 ORDER BY a LIMIT 5)
          217  +      UNION SELECT a+10 FROM (SELECT a FROM t1 ORDER BY a LIMIT 2)
          218  +      ORDER BY 1
          219  +      LIMIT $::ii;
          220  +    }
          221  +  } [lrange {1 2 3 4 5 11 12} 0 [expr {$ii-1}]]
          222  +}
          223  +foreach ii {1 2 3 4 5 6 7} {
          224  +  do_test tkt-38cb5df375.35d.$ii {
          225  +    execsql {
          226  +      SELECT a FROM (SELECT * FROM t1 ORDER BY a LIMIT 5)
          227  +      INTERSECT SELECT a FROM (SELECT a FROM t1 ORDER BY a LIMIT 2)
          228  +      ORDER BY 1
          229  +      LIMIT $::ii;
          230  +    }
          231  +  } [lrange {1 2} 0 [expr {$ii-1}]]
          232  +}
          233  +foreach ii {1 2 3 4 5 6 7} {
          234  +  do_test tkt-38cb5df375.35e.$ii {
          235  +    execsql {
          236  +      SELECT a FROM (SELECT * FROM t1 ORDER BY a LIMIT 5)
          237  +      EXCEPT SELECT a FROM (SELECT a FROM t1 ORDER BY a LIMIT 2)
          238  +      ORDER BY 1
          239  +      LIMIT $::ii;
          240  +    }
          241  +  } [lrange {3 4 5} 0 [expr {$ii-1}]]
          242  +}
          243  +foreach ii {1 2 3 4 5 6 7} {
          244  +  do_test tkt-38cb5df375.36.$ii {
          245  +    execsql {
          246  +      SELECT 0 FROM (SELECT * FROM t1 LIMIT 3)
          247  +      UNION ALL SELECT 9 FROM (SELECT a FROM t1 LIMIT 4)
          248  +      ORDER BY 1
          249  +      LIMIT $::ii;
          250  +    }
          251  +  } [lrange {0 0 0 9 9 9 9} 0 [expr {$ii-1}]]
          252  +}
          253  +foreach ii {1 2 3 4 5 6 7} {
          254  +  do_test tkt-38cb5df375.37.$ii {
          255  +    execsql {
          256  +      SELECT 0 FROM (SELECT * FROM t1 LIMIT 3)
          257  +      UNION SELECT 9 FROM (SELECT a FROM t1 LIMIT 4)
          258  +      ORDER BY 1
          259  +      LIMIT $::ii;
          260  +    }
          261  +  } [lrange {0 9} 0 [expr {$ii-1}]]
          262  +}
          263  +foreach ii {1 2 3 4 5 6 7} {
          264  +  do_test tkt-38cb5df375.38.$ii {
          265  +    execsql {
          266  +      SELECT 0 FROM (SELECT * FROM t1 LIMIT 3)
          267  +      EXCEPT SELECT 9 FROM (SELECT a FROM t1 LIMIT 4)
          268  +      ORDER BY 1
          269  +      LIMIT $::ii;
          270  +    }
          271  +  } [lrange {0} 0 [expr {$ii-1}]]
          272  +}
          273  +
          274  +foreach ii {1 2 3 4 5 6 7 8 9} {
          275  +  do_test tkt-38cb5df375.41.$ii {
          276  +    execsql {
          277  +      SELECT 0 FROM (SELECT * FROM t1 LIMIT 3)
          278  +      UNION ALL SELECT 9 FROM (SELECT a FROM t1 LIMIT 4)
          279  +      UNION ALL SELECT 88 FROM (SELECT a FROM t1 LIMIT 2)
          280  +      ORDER BY 1
          281  +      LIMIT $::ii;
          282  +    }
          283  +  } [lrange {0 0 0 9 9 9 9 88 88} 0 [expr {$ii-1}]]
          284  +}
          285  +foreach ii {1 2 3 4 5 6 7 8 9} {
          286  +  do_test tkt-38cb5df375.42.$ii {
          287  +    execsql {
          288  +      SELECT a FROM (SELECT * FROM t1 ORDER BY a LIMIT 3)
          289  +      UNION ALL SELECT a+10 FROM (SELECT a FROM t1 ORDER BY a LIMIT 4)
          290  +      UNION ALL SELECT a+20 FROM (SELECT a FROM t1 ORDER BY a LIMIT 2)
          291  +      ORDER BY 1
          292  +      LIMIT $::ii;
          293  +    }
          294  +  } [lrange {1 2 3 11 12 13 14 21 22} 0 [expr {$ii-1}]]
          295  +}
          296  +foreach ii {1 2 3 4 5 6 7 8 9} {
          297  +  do_test tkt-38cb5df375.43.$ii {
          298  +    execsql {
          299  +      SELECT a FROM (SELECT * FROM t1 ORDER BY a LIMIT 3)
          300  +      UNION SELECT a+10 FROM (SELECT a FROM t1 ORDER BY a LIMIT 4)
          301  +      UNION SELECT a+20 FROM (SELECT a FROM t1 ORDER BY a LIMIT 2)
          302  +      ORDER BY 1
          303  +      LIMIT $::ii;
          304  +    }
          305  +  } [lrange {1 2 3 11 12 13 14 21 22} 0 [expr {$ii-1}]]
          306  +}
          307  +
          308  +foreach ii {1 2 3 4 5 6 7} {
          309  +  set jj [expr {7-$ii}]
          310  +  do_test tkt-38cb5df375.51.$ii {
          311  +    execsql {
          312  +      SELECT a FROM (SELECT * FROM t1 ORDER BY a)
          313  +      EXCEPT SELECT a FROM (SELECT a FROM t1 ORDER BY a LIMIT $::ii)
          314  +      ORDER BY a DESC
          315  +      LIMIT $::jj;
          316  +    }
          317  +  } [lrange {8 7 6 5 4 3 2 1} 0 [expr {$jj-1}]]
          318  +}
          319  +
          320  +
          321  +finish_test