From 93626f486965463bffc8a542e456529bcf9b4abd Mon Sep 17 00:00:00 2001 From: danielk1977 Date: Tue, 20 Jun 2006 13:07:27 +0000 Subject: [PATCH] Fix some problems with virtual tables and joins in where.c. (CVS 3277) FossilOrigin-Name: 3e19a7d8eabcd2fa71ced3f76c5f9bc1f3900b81 --- manifest | 16 ++++----- manifest.uuid | 2 +- src/select.c | 6 ++-- src/where.c | 35 ++++++++++++------ test/vtab1.test | 96 +++++++++++++++++++++++++++++++++++++++++++++++-- 5 files changed, 130 insertions(+), 25 deletions(-) diff --git a/manifest b/manifest index 0701bec74c..f402497176 100644 --- a/manifest +++ b/manifest @@ -1,5 +1,5 @@ -C Modifications\sso\sthat\scompilation\sand\stesting\swith\sthe\svarious\sOMIT\smacros\sdefined\sworks.\s(CVS\s3276) -D 2006-06-20T11:01:07 +C Fix\ssome\sproblems\swith\svirtual\stables\sand\sjoins\sin\swhere.c.\s(CVS\s3277) +D 2006-06-20T13:07:27 F Makefile.in f839b470345d3cb4b0644068474623fe2464b5d3 F Makefile.linux-gcc 2d8574d1ba75f129aba2019f0b959db380a90935 F README 9c4e2d6706bdcc3efdd773ce752a8cdab4f90028 @@ -69,7 +69,7 @@ F src/pragma.c 27d5e395c5d950931c7ac4fe610e7c2993e2fa55 F src/prepare.c 6dc945dab34cf97364c661d2b7a12be65d338267 F src/printf.c 7029e5f7344a478394a02c52837ff296ee1ab240 F src/random.c d40f8d356cecbd351ccfab6eaedd7ec1b54f5261 -F src/select.c 668ab7cf8b5f7bba4c9d5e81cca29f73055fd8b0 +F src/select.c 6dd3fdfeaebe2565944241e7e7a0d3e1d8469c12 F src/server.c 087b92a39d883e3fa113cae259d64e4c7438bc96 F src/shell.c ad73192b30a338a58fe81183d4a5d5a1d4e51d36 F src/sqlite.h.in 7855b46387f3f6ac1925301f450df9cbd7a1269b @@ -105,7 +105,7 @@ F src/vdbeaux.c 2e4cb97e6d1612c0e108d68e038a7cd612bceb72 F src/vdbefifo.c 9efb94c8c3f4c979ebd0028219483f88e57584f5 F src/vdbemem.c 5f0afe3b92bb2c037f8d5d697f7c151fa50783a3 F src/vtab.c 8fbf4a8f718229d2158826ed6e440f2d32a07c80 -F src/where.c 485d368d1f6c71713d6190cd730f9efde1385e6e +F src/where.c 0a13357175678d51a218326bca389f20bb41e38b F tclinstaller.tcl 046e3624671962dc50f0481d7c25b38ef803eb42 F test/aggerror.test a867e273ef9e3d7919f03ef4f0e8c0d2767944f2 F test/all.test 5df90d015ca63fcef2a4b62c24f7316b66c4bfd4 @@ -291,7 +291,7 @@ F test/vacuum.test 37f998b841cb335397c26d9bbc3457182af2565f F test/vacuum2.test 5aea8c88a65cb29f7d175296e7c819c6158d838c F test/varint.test ab7b110089a08b9926ed7390e7e97bdefeb74102 F test/view.test 16e2774fe35e47a07ac4471b7f0bcc948b1aa6d5 -F test/vtab1.test e5e668092bcd4a6792dc93c6e23cca4212fdb748 +F test/vtab1.test 1a5233154cbdf3e5324feba9c74dda0eee0ac135 F test/vtab2.test e57f9865368df26ef5eb8bc630962d82086f174b F test/vtab3.test f38d6d7d19f08bffdadce4d5b8cba078f8118587 F test/vtab4.test 4b4293341443839ef6dc02f8d9e614702a6c67ff @@ -371,7 +371,7 @@ F www/tclsqlite.tcl bb0d1357328a42b1993d78573e587c6dcbc964b9 F www/vdbe.tcl 87a31ace769f20d3627a64fa1fade7fed47b90d0 F www/version3.tcl 890248cf7b70e60c383b0e84d77d5132b3ead42b F www/whentouse.tcl 97e2b5cd296f7d8057e11f44427dea8a4c2db513 -P 24a94bb07589c04305eceec1dc9a1a9a2c39e2c5 -R 0e9d4aa43a759d30bd6e09e13e78f464 +P c6ea353bd9f5fe6c9cc3349d2034ada283f9c80e +R 29f7d43d1ba21e43482cd4da4cb99600 U danielk1977 -Z 9457f3939e98083f3d4fddae18968ed4 +Z 1d9af2117d83aafa007dd01f8ea28b29 diff --git a/manifest.uuid b/manifest.uuid index 84eb280a5a..fd2061af5e 100644 --- a/manifest.uuid +++ b/manifest.uuid @@ -1 +1 @@ -c6ea353bd9f5fe6c9cc3349d2034ada283f9c80e \ No newline at end of file +3e19a7d8eabcd2fa71ced3f76c5f9bc1f3900b81 \ No newline at end of file diff --git a/src/select.c b/src/select.c index e5a4260e8d..be9c6c23c7 100644 --- a/src/select.c +++ b/src/select.c @@ -12,7 +12,7 @@ ** This file contains C code routines that are called by the parser ** to handle SELECT statements in SQLite. ** -** $Id: select.c,v 1.316 2006/06/20 11:01:08 danielk1977 Exp $ +** $Id: select.c,v 1.317 2006/06/20 13:07:27 danielk1977 Exp $ */ #include "sqliteInt.h" @@ -1213,8 +1213,8 @@ static int prepSelectStmt(Parse *pParse, Select *p){ return 1; } pTab->nRef++; -#ifndef SQLITE_OMIT_VIEW - if( pTab->pSelect ){ +#if !defined(SQLITE_OMIT_VIEW) || !defined (SQLITE_OMIT_VIRTUALTABLE) + if( pTab->pSelect || IsVirtual(pTab) ){ /* We reach here if the named table is a really a view */ if( sqlite3ViewGetColumnNames(pParse, pTab) ){ return 1; diff --git a/src/where.c b/src/where.c index 040598e067..f11ee759ca 100644 --- a/src/where.c +++ b/src/where.c @@ -16,7 +16,7 @@ ** so is applicable. Because this module is responsible for selecting ** indices, you might also think of this module as the "query optimizer". ** -** $Id: where.c,v 1.220 2006/06/19 12:02:59 danielk1977 Exp $ +** $Id: where.c,v 1.221 2006/06/20 13:07:28 danielk1977 Exp $ */ #include "sqliteInt.h" @@ -1118,7 +1118,7 @@ static double bestVirtualIndex( pIdxInfo->idxNum = 0; pIdxInfo->needToFreeIdxStr = 0; pIdxInfo->orderByConsumed = 0; - pIdxInfo->estimatedCost = SQLITE_BIG_DBL; + pIdxInfo->estimatedCost = SQLITE_BIG_DBL / 2.0; nOrderBy = pIdxInfo->nOrderBy; if( pIdxInfo->nOrderBy && !orderByUsable ){ *(int*)&pIdxInfo->nOrderBy = 0; @@ -1786,6 +1786,8 @@ WhereInfo *sqlite3WhereBegin( int bestJ = 0; /* The value of j */ Bitmask m; /* Bitmask value for j or bestJ */ int once = 0; /* True when first table is seen */ + sqlite3_index_info *pBestIndex = 0; + sqlite3_index_info *pIndex = 0; lowestCost = SQLITE_BIG_DBL; for(j=iFrom, pTabItem=&pTabList->a[j]; jnSrc; j++, pTabItem++){ @@ -1804,9 +1806,9 @@ WhereInfo *sqlite3WhereBegin( if( IsVirtual(pTabItem->pTab) ){ cost = bestVirtualIndex(pParse, &wc, pTabItem, notReady, ppOrderBy ? *ppOrderBy : 0, i==0, - &pLevel->pIdxInfo); + &pIndex); flags = WHERE_VIRTUALTABLE; - if( pLevel->pIdxInfo && pLevel->pIdxInfo->orderByConsumed ){ + if( pIndex && pIndex->orderByConsumed ){ flags = WHERE_VIRTUALTABLE | WHERE_ORDERBY; } pIdx = 0; @@ -1825,6 +1827,14 @@ WhereInfo *sqlite3WhereBegin( bestFlags = flags; bestNEq = nEq; bestJ = j; +#ifndef SQLITE_OMIT_VIRTUALTABLE + sqliteFree(pBestIndex); + pBestIndex = pIndex; + pIndex = 0; + }else{ + sqliteFree(pIndex); + pIndex = 0; +#endif } if( doNotReorder ) break; } @@ -1839,6 +1849,7 @@ WhereInfo *sqlite3WhereBegin( pLevel->nEq = bestNEq; pLevel->aInLoop = 0; pLevel->nIn = 0; + pLevel->pIdxInfo = pBestIndex; if( pBest ){ pLevel->iIdxCur = pParse->nTab++; }else{ @@ -1899,7 +1910,8 @@ WhereInfo *sqlite3WhereBegin( if( pTab->isEphem || pTab->pSelect ) continue; #ifndef SQLITE_OMIT_VIRTUALTABLE if( pLevel->pIdxInfo ){ - sqlite3VdbeOp3(v, OP_VOpen, 0, 0, (const char*)pTab->pVtab, P3_VTAB); + int iCur = pTabItem->iCursor; + sqlite3VdbeOp3(v, OP_VOpen, iCur, 0, (const char*)pTab->pVtab, P3_VTAB); }else #endif if( (pLevel->flags & WHERE_IDX_ONLY)==0 ){ @@ -1974,6 +1986,7 @@ WhereInfo *sqlite3WhereBegin( /* Case 0: The table is a virtual-table. Use the VFilter and VNext ** to access the data. */ + int ii; sqlite3_index_info *pIdxInfo = pLevel->pIdxInfo; int nConstraint = pIdxInfo->nConstraint; struct sqlite3_index_constraint_usage *aUsage = @@ -1981,10 +1994,10 @@ WhereInfo *sqlite3WhereBegin( const struct sqlite3_index_constraint *aConstraint = pIdxInfo->aConstraint; - for(i=1; i<=nConstraint; i++){ + for(ii=1; ii<=nConstraint; ii++){ int j; for(j=0; jpRight); break; @@ -1992,14 +2005,14 @@ WhereInfo *sqlite3WhereBegin( } if( j==nConstraint ) break; } - sqlite3VdbeAddOp(v, OP_Integer, i-1, 0); + sqlite3VdbeAddOp(v, OP_Integer, ii-1, 0); sqlite3VdbeAddOp(v, OP_Integer, pIdxInfo->idxNum, 0); sqlite3VdbeOp3(v, OP_VFilter, iCur, brk, pIdxInfo->idxStr, pIdxInfo->needToFreeIdxStr ? P3_MPRINTF : P3_STATIC); pIdxInfo->needToFreeIdxStr = 0; - for(i=0; inConstraint; i++){ - if( pIdxInfo->aConstraintUsage[i].omit ){ - disableTerm(pLevel, &wc.a[i]); + for(ii=0; iinConstraint; ii++){ + if( pIdxInfo->aConstraintUsage[ii].omit ){ + disableTerm(pLevel, &wc.a[ii]); } } pLevel->op = OP_VNext; diff --git a/test/vtab1.test b/test/vtab1.test index 5d3f813b90..c659526409 100644 --- a/test/vtab1.test +++ b/test/vtab1.test @@ -11,7 +11,7 @@ # This file implements regression tests for SQLite library. The # focus of this file is creating and dropping virtual tables. # -# $Id: vtab1.test,v 1.23 2006/06/20 11:01:09 danielk1977 Exp $ +# $Id: vtab1.test,v 1.24 2006/06/20 13:07:28 danielk1977 Exp $ set testdir [file dirname $argv0] source $testdir/tester.tcl @@ -204,7 +204,6 @@ do_test vtab1-2.5 { } set echo_module } {xDestroy} -finish_test do_test vtab1-2.6 { execsql { @@ -414,6 +413,99 @@ execsql { DROP TABLE treal; } +#---------------------------------------------------------------------- +# Test cases vtab1-5 test SELECT queries that include joins on virtual +# tables. + +proc filter {log} { + set out [list] + for {set ii 0} {$ii < [llength $log]} {incr ii} { + if {[lindex $log $ii] eq "xFilter"} { + lappend out xFilter + lappend out [lindex $log [expr $ii+1]] + } + } + return $out +} + +do_test vtab1-5-1 { + execsql { + CREATE TABLE t1(a, b, c); + CREATE TABLE t2(d, e, f); + INSERT INTO t1 VALUES(1, 'red', 'green'); + INSERT INTO t1 VALUES(2, 'blue', 'black'); + INSERT INTO t2 VALUES(1, 'spades', 'clubs'); + INSERT INTO t2 VALUES(2, 'hearts', 'diamonds'); + CREATE VIRTUAL TABLE et1 USING echo(t1); + CREATE VIRTUAL TABLE et2 USING echo(t2); + } +} {} + +do_test vtab1-5-2 { + set echo_module "" + execsql { + SELECT * FROM et1, et2; + } +} [list \ + 1 red green 1 spades clubs \ + 1 red green 2 hearts diamonds \ + 2 blue black 1 spades clubs \ + 2 blue black 2 hearts diamonds \ +] +do_test vtab1-5-3 { + filter $echo_module +} [list \ + xFilter {SELECT rowid, * FROM 't1'} \ + xFilter {SELECT rowid, * FROM 't2'} \ + xFilter {SELECT rowid, * FROM 't2'} \ +] +do_test vtab1-5-4 { + set echo_module "" + execsql { + SELECT * FROM et1, et2 WHERE et2.d = 2; + } +} [list \ + 1 red green 2 hearts diamonds \ + 2 blue black 2 hearts diamonds \ +] +do_test vtab1-5-5 { + filter $echo_module +} [list \ + xFilter {SELECT rowid, * FROM 't1'} \ + xFilter {SELECT rowid, * FROM 't2'} \ + xFilter {SELECT rowid, * FROM 't2'} \ +] +do_test vtab1-5-6 { + execsql { + CREATE INDEX i1 ON t2(d); + } + + db close + sqlite3 db test.db + register_echo_module [sqlite3_connection_pointer db] + + set echo_module "" + execsql { + SELECT * FROM et1, et2 WHERE et2.d = 2; + } +} [list \ + 1 red green 2 hearts diamonds \ + 2 blue black 2 hearts diamonds \ +] +do_test vtab1-5-7 { + filter $echo_module +} [list \ + xFilter {SELECT rowid, * FROM 't2' WHERE d = ?} \ + xFilter {SELECT rowid, * FROM 't1'} \ +] + +execsql { + DROP TABLE t1; + DROP TABLE t2; + DROP TABLE et1; + DROP TABLE et2; +} + #---------------------------------------------------------------------- # Test cases vtab1-6 test INSERT, UPDATE and DELETE operations # on virtual tables. -- 2.47.2