SQLite

Check-in [c996185a9e]
Login

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
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: c996185a9e0671e46bed06e5803b1ca3b42d61e2
User & Date: drh 2006-06-14 22:07:11.000
Context
2006-06-14
23:43
Changes to test8.c to support UPDATE operations on xUpdate. (CVS 3250) (check-in: 9e96511f83 user: drh tags: trunk)
22:07
Bug fixes in the MATCH and ORDER BY processing of virtual tables. (CVS 3249) (check-in: c996185a9e 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: 32c97b884b user: drh tags: trunk)
Changes
Unified Diff Ignore Whitespace Patch
Changes to src/test8.c.
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
**    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>








|







9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
**    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>

436
437
438
439
440
441
442
443
444




445

446
447
448
449
450
451
452
        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;
    }
  }







|

>
>
>
>
|
>







436
437
438
439
440
441
442
443
444
445
446
447
448
449
450
451
452
453
454
455
456
457
        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
** 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)







|







12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
** 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)
791
792
793
794
795
796
797


798
799
800
801
802
803
804
805
    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];







>
>
|







791
792
793
794
795
796
797
798
799
800
801
802
803
804
805
806
807
    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];
1791
1792
1793
1794
1795
1796
1797



1798
1799
1800
1801
1802
1803
1804
      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,







>
>
>







1793
1794
1795
1796
1797
1798
1799
1800
1801
1802
1803
1804
1805
1806
1807
1808
1809
      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.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
# 2006 June 10
#
# The author disclaims copyright to this source code.  In place of
# a legal notice, here is a blessing:
#
#    May you do good and not evil.
#    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
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
# 2006 June 10
#
# The author disclaims copyright to this source code.  In place of
# a legal notice, here is a blessing:
#
#    May you do good and not evil.
#    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
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
} {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







|
|
|
<
>
|




|


|



|
<
<
<


>



|


|
|
>
>
>







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
} {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
400
401
402
403
404
405
406
407
} {2 5 sort}
do_test vtab1-4.4 {
  set echo_module
} [list xBestIndex {SELECT rowid, * FROM 'treal'} \
        xFilter    {SELECT rowid, * FROM 'treal'} ]

finish_test








<
401
402
403
404
405
406
407

} {2 5 sort}
do_test vtab1-4.4 {
  set echo_module
} [list xBestIndex {SELECT rowid, * FROM 'treal'} \
        xFilter    {SELECT rowid, * FROM 'treal'} ]

finish_test