/ Check-in [c996185a]
Login
Home Timeline Branches Tags Tickets Wiki More...

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

Overview
Comment:Bug fixes in the MATCH and ORDER BY processing of virtual tables. (CVS 3249)
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: c996185a9e0671e46bed06e5803b1ca3b42d61e2
User & Date: drh 2006-06-14 22:07:11
Context
2006-06-14
23:43
Changes to test8.c to support UPDATE operations on xUpdate. (CVS 3250) check-in: 9e96511f user: drh tags: trunk
22:07
Bug fixes in the MATCH and ORDER BY processing of virtual tables. (CVS 3249) check-in: c996185a user: drh tags: trunk
19:00
Added code to INSERT, DELETE and UPDATE virtual tables. The new code is mostly untested. (CVS 3248) check-in: 32c97b88 user: drh tags: trunk
Changes
Hide Diffs Unified Diffs Ignore Whitespace Patch

Changes to src/test8.c. 

9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
...
436
437
438
439
440
441
442
443
444




445

446
447
448
449
450
451
452

**    May you share freely, never taking more than you give.
**
*************************************************************************
** Code for testing the virtual table interfaces.  This code
** is not included in the SQLite library.  It is used for automated
** testing of the SQLite library.
**
** $Id: test8.c,v 1.17 2006/06/14 15:16:36 danielk1977 Exp $
*/
#include "sqliteInt.h"
#include "tcl.h"
#include "os.h"
#include <stdlib.h>
#include <string.h>

................................................................................
        case SQLITE_INDEX_CONSTRAINT_GT:
          zOp = ">"; break;
        case SQLITE_INDEX_CONSTRAINT_LE:
          zOp = "<="; break;
        case SQLITE_INDEX_CONSTRAINT_GE:
          zOp = ">="; break;
        case SQLITE_INDEX_CONSTRAINT_MATCH:
          zOp = "MATCH"; break;
      }




      zNew = sqlite3_mprintf("%s %s %s %s ?", zQuery, zSep, zCol, zOp);

      sqlite3_free(zQuery);
      zQuery = zNew;
      zSep = "AND";
      pUsage->argvIndex = ++nArg;
      pUsage->omit = 1;
    }
  }








|







 







|

>
>
>
>
|
>








9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
...
436
437
438
439
440
441
442
443
444
445
446
447
448
449
450
451
452
453
454
455
456
457

**    May you share freely, never taking more than you give.
**
*************************************************************************
** Code for testing the virtual table interfaces.  This code
** is not included in the SQLite library.  It is used for automated
** testing of the SQLite library.
**
** $Id: test8.c,v 1.18 2006/06/14 22:07:11 drh Exp $
*/
#include "sqliteInt.h"
#include "tcl.h"
#include "os.h"
#include <stdlib.h>
#include <string.h>

................................................................................
        case SQLITE_INDEX_CONSTRAINT_GT:
          zOp = ">"; break;
        case SQLITE_INDEX_CONSTRAINT_LE:
          zOp = "<="; break;
        case SQLITE_INDEX_CONSTRAINT_GE:
          zOp = ">="; break;
        case SQLITE_INDEX_CONSTRAINT_MATCH:
          zOp = "LIKE"; break;
      }
      if( zOp[0]=='L' ){
        zNew = sqlite3_mprintf("%s %s %s LIKE (SELECT '%%'||?||'%%')", 
                               zQuery, zSep, zCol);
      } else {
        zNew = sqlite3_mprintf("%s %s %s %s ?", zQuery, zSep, zCol, zOp);
      }
      sqlite3_free(zQuery);
      zQuery = zNew;
      zSep = "AND";
      pUsage->argvIndex = ++nArg;
      pUsage->omit = 1;
    }
  }

Changes to src/where.c. 

12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
...
791
792
793
794
795
796
797


798
799
800
801
802
803
804
805
....
1791
1792
1793
1794
1795
1796
1797



1798
1799
1800
1801
1802
1803
1804

** This module contains C code that generates VDBE code used to process
** the WHERE clause of SQL statements.  This module is reponsible for
** generating the code that loops through a table looking for applicable
** rows.  Indices are selected and used to speed the search when doing
** 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.217 2006/06/14 19:00:22 drh Exp $
*/
#include "sqliteInt.h"

/*
** The number of bits in a Bitmask.  "BMS" means "BitMask Size".
*/
#define BMS  (sizeof(Bitmask)*8)
................................................................................
    Bitmask prereqColumn, prereqExpr;

    pRight = pExpr->pList->a[0].pExpr;
    pLeft = pExpr->pList->a[1].pExpr;
    prereqExpr = exprTableUsage(pMaskSet, pRight);
    prereqColumn = exprTableUsage(pMaskSet, pLeft);
    if( (prereqExpr & prereqColumn)==0 ){


      idxNew = whereClauseInsert(pWC, pExpr, TERM_VIRTUAL);
      pNewTerm = &pWC->a[idxNew];
      pNewTerm->prereqRight = prereqExpr;
      pNewTerm->leftCursor = pLeft->iTable;
      pNewTerm->leftColumn = pLeft->iColumn;
      pNewTerm->eOperator = WO_MATCH;
      pNewTerm->iParent = idxTerm;
      pTerm = &pWC->a[idxNew];
................................................................................
      assert( pTabItem->pTab );
#ifndef SQLITE_OMIT_VIRTUALTABLE
      if( IsVirtual(pTabItem->pTab) ){
        cost = bestVirtualIndex(pParse, &wc, pTabItem, notReady,
                                ppOrderBy ? *ppOrderBy : 0, i==0,
                                &pLevel->pIdxInfo);
        flags = WHERE_VIRTUALTABLE;



        pIdx = 0;
        nEq = 0;
      }else 
#endif
      {
        cost = bestIndex(pParse, &wc, pTabItem, notReady,
                         (i==0 && ppOrderBy) ? *ppOrderBy : 0,








|







 







>
>
|







 







>
>
>








12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
...
791
792
793
794
795
796
797
798
799
800
801
802
803
804
805
806
807
....
1793
1794
1795
1796
1797
1798
1799
1800
1801
1802
1803
1804
1805
1806
1807
1808
1809

** This module contains C code that generates VDBE code used to process
** the WHERE clause of SQL statements.  This module is reponsible for
** generating the code that loops through a table looking for applicable
** rows.  Indices are selected and used to speed the search when doing
** 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.218 2006/06/14 22:07:11 drh Exp $
*/
#include "sqliteInt.h"

/*
** The number of bits in a Bitmask.  "BMS" means "BitMask Size".
*/
#define BMS  (sizeof(Bitmask)*8)
................................................................................
    Bitmask prereqColumn, prereqExpr;

    pRight = pExpr->pList->a[0].pExpr;
    pLeft = pExpr->pList->a[1].pExpr;
    prereqExpr = exprTableUsage(pMaskSet, pRight);
    prereqColumn = exprTableUsage(pMaskSet, pLeft);
    if( (prereqExpr & prereqColumn)==0 ){
      Expr *pNewExpr;
      pNewExpr = sqlite3Expr(TK_MATCH, 0, sqlite3ExprDup(pRight), 0);
      idxNew = whereClauseInsert(pWC, pNewExpr, TERM_VIRTUAL|TERM_DYNAMIC);
      pNewTerm = &pWC->a[idxNew];
      pNewTerm->prereqRight = prereqExpr;
      pNewTerm->leftCursor = pLeft->iTable;
      pNewTerm->leftColumn = pLeft->iColumn;
      pNewTerm->eOperator = WO_MATCH;
      pNewTerm->iParent = idxTerm;
      pTerm = &pWC->a[idxNew];
................................................................................
      assert( pTabItem->pTab );
#ifndef SQLITE_OMIT_VIRTUALTABLE
      if( IsVirtual(pTabItem->pTab) ){
        cost = bestVirtualIndex(pParse, &wc, pTabItem, notReady,
                                ppOrderBy ? *ppOrderBy : 0, i==0,
                                &pLevel->pIdxInfo);
        flags = WHERE_VIRTUALTABLE;
        if( pLevel->pIdxInfo && pLevel->pIdxInfo->orderByConsumed ){
          flags = WHERE_VIRTUALTABLE | WHERE_ORDERBY;
        }
        pIdx = 0;
        nEq = 0;
      }else 
#endif
      {
        cost = bestIndex(pParse, &wc, pTabItem, notReady,
                         (i==0 && ppOrderBy) ? *ppOrderBy : 0,

Changes to test/vtab1.test. 

7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
...
318
319
320
321
322
323
324
325
326
327
328

329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346

347
348
349
350
351
352

353

354

355
356
357
358
359
360
361
...
400
401
402
403
404
405
406
407

#    May you find forgiveness for yourself and forgive others.
#    May you share freely, never taking more than you give.
#
#***********************************************************************
# This file implements regression tests for SQLite library.  The
# focus of this file is creating and dropping virtual tables.
#
# $Id: vtab1.test,v 1.14 2006/06/14 08:48:26 danielk1977 Exp $

set testdir [file dirname $argv0]
source $testdir/tester.tcl

ifcapable !vtab {
  finish_test
  return
................................................................................
} {1 2 3 4 5 6}
do_test vtab1-3.13 {
  set echo_module
} [list xBestIndex {SELECT rowid, * FROM 'treal' WHERE b >= ? AND b <= ?}      \
        xFilter    {SELECT rowid, * FROM 'treal' WHERE b >= ? AND b <= ?} 2 10 ]

# Add a function for the MATCH operator. Everything always matches!
proc test_match {lhs rhs} {
  lappend ::echo_module MATCH $lhs $rhs
  return 1
}

db function match test_match

set echo_module ""
do_test vtab1-3.12 {
  set echo_module ""
  execsql {
    SELECT * FROM t1 WHERE a MATCH 'string';
  }
} {1 2 3 4 5 6}
do_test vtab1-3.13 {
  set echo_module
} [list xBestIndex {SELECT rowid, * FROM 'treal'} \
        xFilter    {SELECT rowid, * FROM 'treal'} \
        MATCH string 1                            \
        MATCH string 4                            \
]
do_test vtab1-3.14 {
  set echo_module ""

  execsql {
    SELECT * FROM t1 WHERE b MATCH 'string';
  }
} {1 2 3 4 5 6}
do_test vtab1-3.15 {
  set echo_module

} [list xBestIndex {SELECT rowid, * FROM 'treal' WHERE b MATCH ?}        \

        xFilter    {SELECT rowid, * FROM 'treal' WHERE b MATCH ?} string ]


#----------------------------------------------------------------------
# Test case vtab1-3 test table scans and the echo module's 
# xBestIndex/xFilter handling of ORDER BY clauses.

# This procedure executes the SQL.  Then it checks to see if the OP_Sort
# opcode was executed.  If an OP_Sort did occur, then "sort" is appended
................................................................................
} {2 5 sort}
do_test vtab1-4.4 {
  set echo_module
} [list xBestIndex {SELECT rowid, * FROM 'treal'} \
        xFilter    {SELECT rowid, * FROM 'treal'} ]

finish_test









|







 







|
|
|
<
>
|




|


|



|
<
<
<


>



|


>
|
>
|
>







 







<

7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
...
318
319
320
321
322
323
324
325
326
327

328
329
330
331
332
333
334
335
336
337
338
339
340
341



342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
...
401
402
403
404
405
406
407


#    May you find forgiveness for yourself and forgive others.
#    May you share freely, never taking more than you give.
#
#***********************************************************************
# This file implements regression tests for SQLite library.  The
# focus of this file is creating and dropping virtual tables.
#
# $Id: vtab1.test,v 1.15 2006/06/14 22:07:11 drh Exp $

set testdir [file dirname $argv0]
source $testdir/tester.tcl

ifcapable !vtab {
  finish_test
  return
................................................................................
} {1 2 3 4 5 6}
do_test vtab1-3.13 {
  set echo_module
} [list xBestIndex {SELECT rowid, * FROM 'treal' WHERE b >= ? AND b <= ?}      \
        xFilter    {SELECT rowid, * FROM 'treal' WHERE b >= ? AND b <= ?} 2 10 ]

# Add a function for the MATCH operator. Everything always matches!
#proc test_match {lhs rhs} {
#  lappend ::echo_module MATCH $lhs $rhs
#  return 1

#}
#db function match test_match

set echo_module ""
do_test vtab1-3.12 {
  set echo_module ""
  catchsql {
    SELECT * FROM t1 WHERE a MATCH 'string';
  }
} {1 {MATCH is not implemented}}
do_test vtab1-3.13 {
  set echo_module
} [list xBestIndex {SELECT rowid, * FROM 'treal'} \
        xFilter    {SELECT rowid, * FROM 'treal'}]



do_test vtab1-3.14 {
  set echo_module ""
btree_breakpoint
  execsql {
    SELECT * FROM t1 WHERE b MATCH 'string';
  }
} {}
do_test vtab1-3.15 {
  set echo_module
} [list xBestIndex \
        {SELECT rowid, * FROM 'treal' WHERE b LIKE (SELECT '%'||?||'%')}  \
        xFilter \
        {SELECT rowid, * FROM 'treal' WHERE b LIKE (SELECT '%'||?||'%')} \
        string ]

#----------------------------------------------------------------------
# Test case vtab1-3 test table scans and the echo module's 
# xBestIndex/xFilter handling of ORDER BY clauses.

# This procedure executes the SQL.  Then it checks to see if the OP_Sort
# opcode was executed.  If an OP_Sort did occur, then "sort" is appended
................................................................................
} {2 5 sort}
do_test vtab1-4.4 {
  set echo_module
} [list xBestIndex {SELECT rowid, * FROM 'treal'} \
        xFilter    {SELECT rowid, * FROM 'treal'} ]

finish_test

This page was generated in about 0.041s by Fossil 2.11 [a92d5a514d] 2019-10-23 00:46:53