/ Check-in [6cbbae84]
Login

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

Overview
Comment:Fix handling of "WHERE col MATCH ? AND docid = ?" clauses in fts3.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1:6cbbae849990d99b7ffe252b642d6be49d0c7235
User & Date: dan 2009-12-10 18:29:05
Context
2009-12-10
18:38
Remove some unused macros and types from fts3.c. check-in: 83a80efe user: dan tags: trunk
18:29
Fix handling of "WHERE col MATCH ? AND docid = ?" clauses in fts3. check-in: 6cbbae84 user: dan tags: trunk
18:20
Fix an OOM related problem in the snippet() and offsets() functions of fts3. check-in: 61efff41 user: dan tags: trunk
Changes
Hide Diffs Unified Diffs Ignore Whitespace Patch

Changes to ext/fts3/fts3.c.

799
800
801
802
803
804
805
806
807
808
809
810
811
812
813
814
815
816
817
818
819
820
821
822



823
824
825
826
827
828
829
830

831
832
833
834
835
836
837
  */
  pInfo->idxNum = FTS3_FULLSCAN_SEARCH;
  pInfo->estimatedCost = 500000;
  for(i=0; i<pInfo->nConstraint; i++){
    struct sqlite3_index_constraint *pCons = &pInfo->aConstraint[i];
    if( pCons->usable==0 ) continue;

    /* A direct lookup on the rowid or docid column. This is the best
    ** strategy in all cases. Assign a cost of 1.0 and return early.
    */
    if( pCons->op==SQLITE_INDEX_CONSTRAINT_EQ 
     && (pCons->iColumn<0 || pCons->iColumn==p->nColumn+1 )
    ){
      pInfo->idxNum = FTS3_DOCID_SEARCH;
      pInfo->estimatedCost = 1.0;
      iCons = i;
      break;
    }

    /* A MATCH constraint. Use a full-text search.
    **
    ** If there is more than one MATCH constraint available, use the first
    ** one encountered. If there is both a MATCH constraint and a direct
    ** rowid/docid lookup, prefer the rowid/docid strategy.



    */
    if( iCons<0 
     && pCons->op==SQLITE_INDEX_CONSTRAINT_MATCH 
     && pCons->iColumn>=0 && pCons->iColumn<=p->nColumn
    ){
      pInfo->idxNum = FTS3_FULLTEXT_SEARCH + pCons->iColumn;
      pInfo->estimatedCost = 2.0;
      iCons = i;

    }
  }

  if( iCons>=0 ){
    pInfo->aConstraintUsage[iCons].argvIndex = 1;
    pInfo->aConstraintUsage[iCons].omit = 1;
  } 







|
<
<






<






|
>
>
>

<
|





>







799
800
801
802
803
804
805
806


807
808
809
810
811
812

813
814
815
816
817
818
819
820
821
822
823

824
825
826
827
828
829
830
831
832
833
834
835
836
837
  */
  pInfo->idxNum = FTS3_FULLSCAN_SEARCH;
  pInfo->estimatedCost = 500000;
  for(i=0; i<pInfo->nConstraint; i++){
    struct sqlite3_index_constraint *pCons = &pInfo->aConstraint[i];
    if( pCons->usable==0 ) continue;

    /* A direct lookup on the rowid or docid column. Assign a cost of 1.0. */


    if( pCons->op==SQLITE_INDEX_CONSTRAINT_EQ 
     && (pCons->iColumn<0 || pCons->iColumn==p->nColumn+1 )
    ){
      pInfo->idxNum = FTS3_DOCID_SEARCH;
      pInfo->estimatedCost = 1.0;
      iCons = i;

    }

    /* A MATCH constraint. Use a full-text search.
    **
    ** If there is more than one MATCH constraint available, use the first
    ** one encountered. If there is both a MATCH constraint and a direct
    ** rowid/docid lookup, prefer the MATCH strategy. This is done even 
    ** though the rowid/docid lookup is faster than a MATCH query, selecting
    ** it would lead to an "unable to use function MATCH in the requested 
    ** context" error.
    */

    if( pCons->op==SQLITE_INDEX_CONSTRAINT_MATCH 
     && pCons->iColumn>=0 && pCons->iColumn<=p->nColumn
    ){
      pInfo->idxNum = FTS3_FULLTEXT_SEARCH + pCons->iColumn;
      pInfo->estimatedCost = 2.0;
      iCons = i;
      break;
    }
  }

  if( iCons>=0 ){
    pInfo->aConstraintUsage[iCons].argvIndex = 1;
    pInfo->aConstraintUsage[iCons].omit = 1;
  } 

Changes to test/e_fts3.test.

34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
...
543
544
545
546
547
548
549
550
551
552
553
554
555
556
557
558
559
560
561
562

563
564
565
566
567
568
569
...
585
586
587
588
589
590
591
592
593

594
595
596
597
598
599
600
  uplevel [list do_select_test e_fts3-$tn $sql $result]
}
proc error_test {tn sql result} {
  uplevel [list do_error_test e_fts3-$tn $sql $result]
}


if 1 {

#-------------------------------------------------------------------------
# The body of the following [foreach] block contains test cases to verify
# that the example code in fts3.html works as expected. The tests run three
# times, with different values for DO_MALLOC_TEST.
# 
#   DO_MALLOC_TEST=0: Run tests with no OOM errors.
#   DO_MALLOC_TEST=1: Run tests with transient OOM errors.
................................................................................
write_test 6.4 t5_content { INSERT INTO t6 VALUES('c'); }
read_test  6.5 { SELECT * FROM t6 WHERE t6 MATCH '' } {}
read_test  6.6 { SELECT * FROM t6 WHERE x MATCH '' } {}
read_test  6.7 { SELECT * FROM t6 WHERE t6 MATCH NULL } {}
read_test  6.8 { SELECT * FROM t6 WHERE x MATCH NULL } {}
#-------------------------------------------------------------------------


#-------------------------------------------------------------------------
# Test a few facets of the FTS3 xFilter() callback implementation:
#
#   1. That the sqlite3_index_constraint.usable flag is respected.
#
#   2. That it is an error to use the "docid" or "rowid" column of
#      an FTS3 table as the LHS of a MATCH operator.
#
#   3. That it is an error to AND together two MATCH expressions in 
#      that refer to a single FTS3 table in a WHERE clause.
#
#

ddl_test   7.1.1 { CREATE VIRTUAL TABLE t7 USING fts3(a) }
ddl_test   7.1.2 { CREATE VIRTUAL TABLE t8 USING fts3(b) }
write_test 7.1.3 t7_content { INSERT INTO t7(docid, a) VALUES(4,'number four') }
write_test 7.1.4 t7_content { INSERT INTO t7(docid, a) VALUES(5,'number five') }
write_test 7.1.5 t8_content { INSERT INTO t8(docid, b) VALUES(4,'letter D') }
write_test 7.1.6 t8_content { INSERT INTO t8(docid, b) VALUES(5,'letter E') }
read_test  7.1.7 {
................................................................................
} {unable to use function MATCH in the requested context}
error_test 7.3.3 {
  SELECT * FROM t7, t8 WHERE b MATCH 'letter' AND b MATCH 'd'
} {unable to use function MATCH in the requested context}
read_test 7.3.4 {
  SELECT * FROM t7, t8 WHERE a MATCH 'number' AND b MATCH 'letter'
} {{number four} {letter D} {number four} {letter E} {number five} {letter D} {number five} {letter E}}

}


#-------------------------------------------------------------------------
# Test the quoting of FTS3 table column names. Names may be quoted using
# any of "", '', ``` or [].
#
set DO_MALLOC_TEST 0
ddl_test  8.1.1 { CREATE VIRTUAL TABLE t9a USING fts3("c1", [c2]) }







<
<







 







<












>







 







|
|
>







34
35
36
37
38
39
40


41
42
43
44
45
46
47
...
541
542
543
544
545
546
547

548
549
550
551
552
553
554
555
556
557
558
559
560
561
562
563
564
565
566
567
...
583
584
585
586
587
588
589
590
591
592
593
594
595
596
597
598
599
  uplevel [list do_select_test e_fts3-$tn $sql $result]
}
proc error_test {tn sql result} {
  uplevel [list do_error_test e_fts3-$tn $sql $result]
}




#-------------------------------------------------------------------------
# The body of the following [foreach] block contains test cases to verify
# that the example code in fts3.html works as expected. The tests run three
# times, with different values for DO_MALLOC_TEST.
# 
#   DO_MALLOC_TEST=0: Run tests with no OOM errors.
#   DO_MALLOC_TEST=1: Run tests with transient OOM errors.
................................................................................
write_test 6.4 t5_content { INSERT INTO t6 VALUES('c'); }
read_test  6.5 { SELECT * FROM t6 WHERE t6 MATCH '' } {}
read_test  6.6 { SELECT * FROM t6 WHERE x MATCH '' } {}
read_test  6.7 { SELECT * FROM t6 WHERE t6 MATCH NULL } {}
read_test  6.8 { SELECT * FROM t6 WHERE x MATCH NULL } {}
#-------------------------------------------------------------------------


#-------------------------------------------------------------------------
# Test a few facets of the FTS3 xFilter() callback implementation:
#
#   1. That the sqlite3_index_constraint.usable flag is respected.
#
#   2. That it is an error to use the "docid" or "rowid" column of
#      an FTS3 table as the LHS of a MATCH operator.
#
#   3. That it is an error to AND together two MATCH expressions in 
#      that refer to a single FTS3 table in a WHERE clause.
#
#
set DO_MALLOC_TEST 0
ddl_test   7.1.1 { CREATE VIRTUAL TABLE t7 USING fts3(a) }
ddl_test   7.1.2 { CREATE VIRTUAL TABLE t8 USING fts3(b) }
write_test 7.1.3 t7_content { INSERT INTO t7(docid, a) VALUES(4,'number four') }
write_test 7.1.4 t7_content { INSERT INTO t7(docid, a) VALUES(5,'number five') }
write_test 7.1.5 t8_content { INSERT INTO t8(docid, b) VALUES(4,'letter D') }
write_test 7.1.6 t8_content { INSERT INTO t8(docid, b) VALUES(5,'letter E') }
read_test  7.1.7 {
................................................................................
} {unable to use function MATCH in the requested context}
error_test 7.3.3 {
  SELECT * FROM t7, t8 WHERE b MATCH 'letter' AND b MATCH 'd'
} {unable to use function MATCH in the requested context}
read_test 7.3.4 {
  SELECT * FROM t7, t8 WHERE a MATCH 'number' AND b MATCH 'letter'
} {{number four} {letter D} {number four} {letter E} {number five} {letter D} {number five} {letter E}}
read_test 7.3.5 {
  SELECT * FROM t7 WHERE a MATCH 'number' AND docid = 4
} {{number four}}

#-------------------------------------------------------------------------
# Test the quoting of FTS3 table column names. Names may be quoted using
# any of "", '', ``` or [].
#
set DO_MALLOC_TEST 0
ddl_test  8.1.1 { CREATE VIRTUAL TABLE t9a USING fts3("c1", [c2]) }