Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Overview
Comment: | Fix the min/max optimizer so that it works when the FROM clause is a subquery. Ticket #658. (CVS 1293) |
---|---|
Downloads: | Tarball | ZIP archive |
Timelines: | family | ancestors | descendants | both | trunk |
Files: | files | file ages | folders |
SHA1: |
31c94acc72d318b5dec0fef148562194 |
User & Date: | drh 2004-03-13 14:00:36.000 |
Context
2004-03-14
| ||
11:57 | Updates to the architecture document. (CVS 1294) (check-in: c661cc81b6 user: drh tags: trunk) | |
2004-03-13
| ||
14:00 | Fix the min/max optimizer so that it works when the FROM clause is a subquery. Ticket #658. (CVS 1293) (check-in: 31c94acc72 user: drh tags: trunk) | |
2004-03-10
| ||
18:57 | Typecast to work around a bug in {quote: CodeWarrior} Studio v9.1. Ticket #654. (CVS 1292) (check-in: 5864fc6937 user: drh tags: trunk) | |
Changes
Changes to src/os.c.
︙ | ︙ | |||
1777 1778 1779 1780 1781 1782 1783 | } } return zFull; #endif } /* | | | 1777 1778 1779 1780 1781 1782 1783 1784 1785 1786 1787 1788 1789 1790 1791 | } } return zFull; #endif } /* ** The following variable, if set to a non-zero value, becomes the result ** returned from sqliteOsCurrentTime(). This is used for testing. */ #ifdef SQLITE_TEST int sqlite_current_time = 0; #endif /* |
︙ | ︙ |
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.161 2004/03/13 14:00:36 drh Exp $ */ #include "sqliteInt.h" /* ** Allocate a new Select structure and return a pointer to that ** structure. |
︙ | ︙ | |||
1832 1833 1834 1835 1836 1837 1838 | int iCol; Table *pTab; Index *pIdx; int base; Vdbe *v; int seekOp; int cont; | | > > > | > | | > | | | | 1832 1833 1834 1835 1836 1837 1838 1839 1840 1841 1842 1843 1844 1845 1846 1847 1848 1849 1850 1851 1852 1853 1854 1855 1856 1857 1858 1859 1860 1861 1862 1863 1864 1865 1866 1867 1868 1869 1870 1871 1872 1873 1874 | int iCol; Table *pTab; Index *pIdx; int base; Vdbe *v; int seekOp; int cont; ExprList *pEList, *pList, eList; struct ExprList_item eListItem; SrcList *pSrc; /* Check to see if this query is a simple min() or max() query. Return ** zero if it is not. */ if( p->pGroupBy || p->pHaving || p->pWhere ) return 0; pSrc = p->pSrc; if( pSrc->nSrc!=1 ) return 0; pEList = p->pEList; if( pEList->nExpr!=1 ) return 0; pExpr = pEList->a[0].pExpr; if( pExpr->op!=TK_AGG_FUNCTION ) return 0; pList = pExpr->pList; if( pList==0 || pList->nExpr!=1 ) return 0; if( pExpr->token.n!=3 ) return 0; if( sqliteStrNICmp(pExpr->token.z,"min",3)==0 ){ seekOp = OP_Rewind; }else if( sqliteStrNICmp(pExpr->token.z,"max",3)==0 ){ seekOp = OP_Last; }else{ return 0; } pExpr = pList->a[0].pExpr; if( pExpr->op!=TK_COLUMN ) return 0; iCol = pExpr->iColumn; pTab = pSrc->a[0].pTab; /* If we get to here, it means the query is of the correct form. ** Check to make sure we have an index and make pIdx point to the ** appropriate index. If the min() or max() is on an INTEGER PRIMARY ** key column, no index is necessary so set pIdx to NULL. If no ** usable index is found, return 0. */ |
︙ | ︙ | |||
1895 1896 1897 1898 1899 1900 1901 | /* 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. */ sqliteCodeVerifySchema(pParse, pTab->iDb); | | > | | > > | 1900 1901 1902 1903 1904 1905 1906 1907 1908 1909 1910 1911 1912 1913 1914 1915 1916 1917 1918 1919 1920 1921 1922 1923 1924 1925 1926 1927 1928 1929 1930 1931 1932 1933 1934 1935 1936 1937 1938 | /* 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. */ sqliteCodeVerifySchema(pParse, pTab->iDb); base = pSrc->a[0].iCursor; computeLimitRegisters(pParse, p); if( pSrc->a[0].pSelect==0 ){ sqliteVdbeAddOp(v, OP_Integer, pTab->iDb, 0); sqliteVdbeOp3(v, OP_OpenRead, base, pTab->tnum, pTab->zName, 0); } cont = sqliteVdbeMakeLabel(v); if( pIdx==0 ){ sqliteVdbeAddOp(v, seekOp, base, 0); }else{ sqliteVdbeAddOp(v, OP_Integer, pIdx->iDb, 0); sqliteVdbeOp3(v, OP_OpenRead, base+1, pIdx->tnum, pIdx->zName, P3_STATIC); sqliteVdbeAddOp(v, seekOp, base+1, 0); sqliteVdbeAddOp(v, OP_IdxRecno, base+1, 0); sqliteVdbeAddOp(v, OP_Close, base+1, 0); sqliteVdbeAddOp(v, OP_MoveTo, base, 0); } eList.nExpr = 1; memset(&eListItem, 0, sizeof(eListItem)); eList.a = &eListItem; eList.a[0].pExpr = pExpr; selectInnerLoop(pParse, p, &eList, 0, 0, 0, -1, eDest, iParm, cont, cont); sqliteVdbeResolveLabel(v, cont); sqliteVdbeAddOp(v, OP_Close, base, 0); return 1; } /* ** Generate code for the given SELECT statement. ** ** The results are distributed in various ways depending on the |
︙ | ︙ | |||
2157 2158 2159 2160 2161 2162 2163 | /* 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. */ if( eDest==SRT_Callback ){ generateColumnNames(pParse, pTabList, pEList); } | < < < < < < < < | 2165 2166 2167 2168 2169 2170 2171 2172 2173 2174 2175 2176 2177 2178 | /* 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. */ if( eDest==SRT_Callback ){ generateColumnNames(pParse, pTabList, pEList); } /* Generate code for all sub-queries in the FROM clause */ for(i=0; i<pTabList->nSrc; i++){ const char *zSavedAuthContext; int needRestoreContext; if( pTabList->a[i].pSelect==0 ) continue; |
︙ | ︙ | |||
2193 2194 2195 2196 2197 2198 2199 2200 2201 2202 2203 2204 2205 2206 | if( eDest!=SRT_Union && eDest!=SRT_Except && eDest!=SRT_Discard ){ pOrderBy = p->pOrderBy; } pGroupBy = p->pGroupBy; pHaving = p->pHaving; isDistinct = p->isDistinct; } /* Check to see if this is a subquery that can be "flattened" into its parent. ** If flattening is a possiblity, do so and return immediately. */ if( pParent && pParentAgg && flattenSubquery(pParse, pParent, parentTab, *pParentAgg, isAgg) ){ if( isAgg ) *pParentAgg = 1; | > > > > > > > > | 2193 2194 2195 2196 2197 2198 2199 2200 2201 2202 2203 2204 2205 2206 2207 2208 2209 2210 2211 2212 2213 2214 | if( eDest!=SRT_Union && eDest!=SRT_Except && eDest!=SRT_Discard ){ pOrderBy = p->pOrderBy; } pGroupBy = p->pGroupBy; pHaving = p->pHaving; isDistinct = p->isDistinct; } /* 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; } /* Check to see if this is a subquery that can be "flattened" into its parent. ** If flattening is a possiblity, do so and return immediately. */ if( pParent && pParentAgg && flattenSubquery(pParse, pParent, parentTab, *pParentAgg, isAgg) ){ if( isAgg ) *pParentAgg = 1; |
︙ | ︙ |
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.9 2004/03/13 14:00:37 drh Exp $ set testdir [file dirname $argv0] source $testdir/tester.tcl do_test minmax-1.0 { execsql { BEGIN; |
︙ | ︙ | |||
254 255 256 257 258 259 260 | INSERT INTO t5 VALUES('1234'); INSERT INTO t5 VALUES('234'); INSERT INTO t5 VALUES('34'); SELECT min(a), max(a) FROM t5; } } {34 1234} | > > | > > > > > > > > > > > > > > > | 254 255 256 257 258 259 260 261 262 263 264 265 266 267 268 269 270 271 272 273 274 275 276 277 278 279 | INSERT INTO t5 VALUES('1234'); INSERT INTO t5 VALUES('234'); INSERT INTO t5 VALUES('34'); SELECT min(a), max(a) FROM t5; } } {34 1234} # Ticket #658: Test the min()/max() optimization when the FROM clause # is a subquery. # do_test minmax-9.1 { execsql { SELECT max(rowid) FROM ( SELECT max(rowid) FROM t4 UNION SELECT max(rowid) FROM t5 ) } } {1} do_test minmax-9.2 { execsql { SELECT max(rowid) FROM ( SELECT max(rowid) FROM t4 EXCEPT SELECT max(rowid) FROM t5 ) } } {{}} finish_test |