Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Overview
Comment: | Make sure the min() and max() optimizer works correctly when there is a LIMIT clause. Ticket #396. (CVS 1057) |
---|---|
Downloads: | Tarball | ZIP archive |
Timelines: | family | ancestors | descendants | both | trunk |
Files: | files | file ages | folders |
SHA1: |
c35e50717678703763c696e3e9b265ad |
User & Date: | drh 2003-07-19 00:44:14.000 |
Context
2003-07-20
| ||
01:16 | Fix the LIMIT clause so that it applies to the entire query in a compound query. Prior to this change LIMITs on compound queries did not work at all. Ticket #393. (CVS 1058) (check-in: 543479e3ae user: drh tags: trunk) | |
2003-07-19
| ||
00:44 | Make sure the min() and max() optimizer works correctly when there is a LIMIT clause. Ticket #396. (CVS 1057) (check-in: c35e507176 user: drh tags: trunk) | |
2003-07-18
| ||
01:30 | In the sqlite shell, change the name of function getline() to local_getline() to avoid a clash with a library function. Ticket #400. (CVS 1056) (check-in: 558969ee86 user: drh tags: trunk) | |
Changes
Changes to src/select.c.
︙ | ︙ | |||
8 9 10 11 12 13 14 | ** May you find forgiveness for yourself and forgive others. ** May you share freely, never taking more than you give. ** ************************************************************************* ** This file contains C code routines that are called by the parser ** to handle SELECT statements in SQLite. ** | | | 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 | ** May you find forgiveness for yourself and forgive others. ** May you share freely, never taking more than you give. ** ************************************************************************* ** This file contains C code routines that are called by the parser ** to handle SELECT statements in SQLite. ** ** $Id: select.c,v 1.144 2003/07/19 00:44:14 drh Exp $ */ #include "sqliteInt.h" /* ** Allocate a new Select structure and return a pointer to that ** structure. |
︙ | ︙ | |||
1848 1849 1850 1851 1852 1853 1854 | for(pIdx=pTab->pIndex; pIdx; pIdx=pIdx->pNext){ assert( pIdx->nColumn>=1 ); if( pIdx->aiColumn[0]==iCol ) break; } if( pIdx==0 ) return 0; } | | > < | 1848 1849 1850 1851 1852 1853 1854 1855 1856 1857 1858 1859 1860 1861 1862 1863 1864 1865 1866 1867 1868 | for(pIdx=pTab->pIndex; pIdx; pIdx=pIdx->pNext){ assert( pIdx->nColumn>=1 ); if( pIdx->aiColumn[0]==iCol ) break; } if( pIdx==0 ) return 0; } /* Identify column types if we will be using the callback. This ** step is skipped if the output is going to a table or a memory cell. ** The column names have already been generated in the calling function. */ v = sqliteGetVdbe(pParse); if( v==0 ) return 0; if( eDest==SRT_Callback ){ generateColumnTypes(pParse, p->pSrc, p->pEList); } /* Generating code to find the min or the max. Basically all we have ** to do is find the first or the last entry in the chosen index. If ** the min() or max() is on the INTEGER PRIMARY KEY, then find the first ** or last entry in the main table. |
︙ | ︙ | |||
2116 2117 2118 2119 2120 2121 2122 | "between 1 and %d", iCol, pEList->nExpr); goto select_end; } } } } | < < < < < < < < | 2116 2117 2118 2119 2120 2121 2122 2123 2124 2125 2126 2127 2128 2129 | "between 1 and %d", iCol, pEList->nExpr); goto select_end; } } } } /* Begin generating code. */ v = sqliteGetVdbe(pParse); if( v==0 ) goto select_end; /* Identify column names if we will be using them in a callback. This ** step is skipped if the output is going to some other destination. |
︙ | ︙ | |||
2168 2169 2170 2171 2172 2173 2174 2175 2176 2177 2178 2179 2180 2181 | }else{ int iMem = pParse->nMem++; if( iMem==0 ) iMem = pParse->nMem++; sqliteVdbeAddOp(v, OP_Integer, -p->nOffset, 0); sqliteVdbeAddOp(v, OP_MemStore, iMem, 1); p->nOffset = iMem; } /* Generate code for all sub-queries in the FROM clause */ for(i=0; i<pTabList->nSrc; i++){ const char *zSavedAuthContext; int needRestoreContext; | > > > > > > > > | 2160 2161 2162 2163 2164 2165 2166 2167 2168 2169 2170 2171 2172 2173 2174 2175 2176 2177 2178 2179 2180 2181 | }else{ int iMem = pParse->nMem++; if( iMem==0 ) iMem = pParse->nMem++; sqliteVdbeAddOp(v, OP_Integer, -p->nOffset, 0); sqliteVdbeAddOp(v, OP_MemStore, iMem, 1); p->nOffset = iMem; } /* Check for the special case of a min() or max() function by itself ** in the result set. */ if( simpleMinMaxQuery(pParse, p, eDest, iParm) ){ rc = 0; goto select_end; } /* Generate code for all sub-queries in the FROM clause */ for(i=0; i<pTabList->nSrc; i++){ const char *zSavedAuthContext; int needRestoreContext; |
︙ | ︙ | |||
2210 2211 2212 2213 2214 2215 2216 2217 2218 2219 2220 2221 2222 2223 | if( isAgg ) *pParentAgg = 1; return rc; } /* Identify column types if we will be using a callback. This ** step is skipped if the output is going to a destination other ** than a callback. */ if( eDest==SRT_Callback ){ generateColumnTypes(pParse, pTabList, pEList); } /* If the output is destined for a temporary table, open that table. */ | > > > > > | 2210 2211 2212 2213 2214 2215 2216 2217 2218 2219 2220 2221 2222 2223 2224 2225 2226 2227 2228 | if( isAgg ) *pParentAgg = 1; return rc; } /* Identify column types if we will be using a callback. This ** step is skipped if the output is going to a destination other ** than a callback. ** ** We have to do this separately from the creation of column names ** above because if the pTabList contains views then they will not ** have been resolved and we will not know the column types until ** now. */ if( eDest==SRT_Callback ){ generateColumnTypes(pParse, pTabList, pEList); } /* If the output is destined for a temporary table, open that table. */ |
︙ | ︙ |
Changes to test/minmax.test.
︙ | ︙ | |||
9 10 11 12 13 14 15 | # #*********************************************************************** # This file implements regression tests for SQLite library. The # focus of this file is testing SELECT statements that contain # aggregate min() and max() functions and which are handled as # as a special case. # | | | 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 | # #*********************************************************************** # This file implements regression tests for SQLite library. The # focus of this file is testing SELECT statements that contain # aggregate min() and max() functions and which are handled as # as a special case. # # $Id: minmax.test,v 1.6 2003/07/19 00:44:15 drh Exp $ set testdir [file dirname $argv0] source $testdir/tester.tcl do_test minmax-1.0 { execsql { BEGIN; |
︙ | ︙ | |||
168 169 170 171 172 173 174 175 | } } {999} do_test minmax-5.5 { execsql { SELECT coalesce(max(rowid),999) FROM t3 WHERE rowid<25; } } {999} | > > | > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > | 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 216 | } } {999} do_test minmax-5.5 { execsql { SELECT coalesce(max(rowid),999) FROM t3 WHERE rowid<25; } } {999} # Make sure the min(x) and max(x) optimizations work when there # is a LIMIT clause. Ticket #396. # do_test minmax-6.1 { execsql { SELECT min(a) FROM t2 LIMIT 1 } } {1} do_test minmax-6.2 { execsql { SELECT max(a) FROM t2 LIMIT 3 } } {22} do_test minmax-6.3 { execsql { SELECT min(a) FROM t2 LIMIT 0,100 } } {1} do_test minmax-6.4 { execsql { SELECT max(a) FROM t2 LIMIT 1,100 } } {} do_test minmax-6.5 { execsql { SELECT min(x) FROM t3 LIMIT 1 } } {{}} do_test minmax-6.6 { execsql { SELECT max(x) FROM t3 LIMIT 0 } } {} do_test minmax-6.7 { execsql { SELECT max(a) FROM t2 LIMIT 0 } } {} finish_test |