/ Check-in [e4ab6cdb]
Login
SQLite training in Houston TX on 2019-11-05 (details)
Part of the 2019 Tcl Conference

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

Overview
Comment:Changes to fts3auto.test to test OR, AND and NOT operations.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | fts3-prefix-search
Files: files | file ages | folders
SHA1: e4ab6cdb101bbeb804820425cf569ee7dc2397fb
User & Date: dan 2011-06-13 13:48:36
Context
2011-06-13
17:00
Add tests for deferred tokens to fts3auto.test. Fix a problem with OR queries and deferred tokens. check-in: b9fb69e5 user: dan tags: fts3-prefix-search
13:48
Changes to fts3auto.test to test OR, AND and NOT operations. check-in: e4ab6cdb user: dan tags: fts3-prefix-search
09:11
Fix a bug exposed by combining matchinfo(), NEAR and "ORDER BY rowid DESC". check-in: 5f6b87f4 user: dan tags: fts3-prefix-search
Changes
Hide Diffs Unified Diffs Ignore Whitespace Patch

Changes to test/fts3auto.test.

12
13
14
15
16
17
18
19
20

21
22
23
24


25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
..
58
59
60
61
62
63
64
65



66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
...
139
140
141
142
143
144
145
146














































































































147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177



































178









179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195










196
197
198
199


200
201

# If this build does not include FTS3, skip the tests in this file.
#
ifcapable !fts3 { finish_test ; return }
source $testdir/fts3_common.tcl
source $testdir/malloc_common.tcl

set testprefix fts3rnd2


proc test_fts3_near_match {tn doc expr res} {
  fts3_near_match $doc $expr -phrasecountvar p
  uplevel do_test [list $tn] [list [list set {} $p]] [list $res]
}



# Simple test cases for C routine [fts3_near_match].
#
test_fts3_near_match 1.1.1 {a b c a b} a                   {2}
test_fts3_near_match 1.1.2 {a b c a b} {a 5 b 6 c}         {2 2 1}
test_fts3_near_match 1.1.3 {a b c a b} {"a b"}             {2}
test_fts3_near_match 1.1.4 {a b c a b} {"b c"}             {1}
test_fts3_near_match 1.1.5 {a b c a b} {"c c"}             {0}

test_fts3_near_match 1.2.1 "a b c d e f g" {b 2 f}         {0 0}
test_fts3_near_match 1.2.2 "a b c d e f g" {b 3 f}         {1 1}
test_fts3_near_match 1.2.3 "a b c d e f g" {f 2 b}         {0 0}
test_fts3_near_match 1.2.4 "a b c d e f g" {f 3 b}         {1 1}
test_fts3_near_match 1.2.5 "a b c d e f g" {"a b" 2 "f g"} {0 0}
test_fts3_near_match 1.2.6 "a b c d e f g" {"a b" 3 "f g"} {1 1}

set A "a b c d e f g h i j k l m n o p q r s t u v w x y z"
test_fts3_near_match 1.3.1 $A {"c d" 5 "i j" 1 "e f"}      {0 0 0}
test_fts3_near_match 1.3.2 $A {"c d" 5 "i j" 2 "e f"}      {1 1 1}

proc mit {blob} {
  set scan(littleEndian) i*
  set scan(bigEndian) I*
  binary scan $blob $scan($::tcl_platform(byteOrder)) r
  return $r
}
db func mit mit
................................................................................
    if {[string match -nocase near/* $a]} { set a [string range $a 5 end] }
    lappend out $a
    lappend out $b
  }
  return $out
}

proc do_near_test {tn tbl expr} {




  set expr [fix_near_expr $expr]

  # Create the MATCH expression from $expr
  #
  set match [lindex $expr 0]
  if {[llength $match]>1} {
    set match "\"$match\""
  } 
  foreach {nNear phrase} [lrange $expr 1 end] {
    if {[llength $phrase]>1} {
      append match " NEAR/$nNear \"$phrase\""
    } else {
      append match " NEAR/$nNear $phrase"
    }
  }

  # Calculate the expected results using [fts3_near_match]. The following
  # loop populates the "hits" and "counts" arrays as follows:
  # 
  #   1. For each document in the table that matches the NEAR expression,
  #      hits($docid) is set to 1. The set of docids that match the expression
  #      can therefore be found using [array names hits].
  #
................................................................................
        lappend mi [lindex $counts($docid,$iCol) $iPhrase]
        lappend mi $nDoc($iPhrase,$iCol)
        lappend mi $nHit($iPhrase,$iCol)
      }
    }
    set aMatchinfo($docid) $mi
  }















































































































  set matchinfo_asc [list]
  foreach docid [lsort -integer -incr [array names aMatchinfo]] {
    lappend matchinfo_asc $docid $aMatchinfo($docid)
  }
  set matchinfo_desc [list]
  foreach docid [lsort -integer -decr [array names aMatchinfo]] {
    lappend matchinfo_desc $docid $aMatchinfo($docid)
  }

  set title "(\"$match\" -> [llength [array names hits]] rows)"

  do_execsql_test $tn$title.1 "
    SELECT docid FROM $tbl WHERE $tbl MATCH '$match' ORDER BY docid ASC
  " [lsort -integer -incr [array names hits]] 

  do_execsql_test $tn$title.2 "
    SELECT docid FROM $tbl WHERE $tbl MATCH '$match' ORDER BY docid DESC
  " [lsort -integer -decr [array names hits]] 

  do_execsql_test $tn$title.3 "
    SELECT docid, mit(matchinfo($tbl, 'x')) FROM $tbl 
    WHERE $tbl MATCH '$match' ORDER BY docid DESC
  " $matchinfo_desc

  do_execsql_test $tn$title.4 "
    SELECT docid, mit(matchinfo($tbl, 'x')) FROM $tbl 
    WHERE $tbl MATCH '$match' ORDER BY docid ASC
  " $matchinfo_asc
} 

do_test 2.1 {



































  execsql { CREATE VIRTUAL TABLE t1 USING fts3(a, b) }









  for {set i 0} {$i<32} {incr i} {
    set doc [list]
    if {$i&0x01} {lappend doc one}
    if {$i&0x02} {lappend doc two}
    if {$i&0x04} {lappend doc three}
    if {$i&0x08} {lappend doc four}
    if {$i&0x10} {lappend doc five}
    execsql { INSERT INTO t1 VALUES($doc, null) }
  }
} {}
foreach {tn expr} {
  1     {one}
  2     {one NEAR/1 five}
  3     {t*}
  4     {t* NEAR/0 five}
  5     {o* NEAR/1 f*}
  6     {one NEAR five NEAR two NEAR four NEAR three}










} {
  do_near_test 2.2.$tn t1 $expr
}



finish_test








|
<
>
|
<
<
|
>
>
|
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<







 







|
>
>
>



<
<
<
<
<
<
<
<
<
<
<
<
<
<







 








>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>









|



|



|












<
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
|
>
>
>
>
>
>
>
>
>
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
>
>
>
>
>
>
>
>
>
>
|
|
|
|
>
>


12
13
14
15
16
17
18
19

20
21


22
23
24
25



















26
27
28
29
30
31
32
..
39
40
41
42
43
44
45
46
47
48
49
50
51
52














53
54
55
56
57
58
59
...
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
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
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336

# If this build does not include FTS3, skip the tests in this file.
#
ifcapable !fts3 { finish_test ; return }
source $testdir/fts3_common.tcl
source $testdir/malloc_common.tcl

set testprefix fts3auto

set sfep $sqlite_fts3_enable_parentheses
set sqlite_fts3_enable_parentheses 1



#--------------------------------------------------------------------------
# Start of Tcl procs used by tests.
#



















proc mit {blob} {
  set scan(littleEndian) i*
  set scan(bigEndian) I*
  binary scan $blob $scan($::tcl_platform(byteOrder)) r
  return $r
}
db func mit mit
................................................................................
    if {[string match -nocase near/* $a]} { set a [string range $a 5 end] }
    lappend out $a
    lappend out $b
  }
  return $out
}

proc get_single_near_results {tbl expr arrayvar nullvar} {
  upvar $arrayvar aMatchinfo
  upvar $nullvar nullentry
  catch {array unset aMatchinfo}

  set expr [fix_near_expr $expr]















  # Calculate the expected results using [fts3_near_match]. The following
  # loop populates the "hits" and "counts" arrays as follows:
  # 
  #   1. For each document in the table that matches the NEAR expression,
  #      hits($docid) is set to 1. The set of docids that match the expression
  #      can therefore be found using [array names hits].
  #
................................................................................
        lappend mi [lindex $counts($docid,$iCol) $iPhrase]
        lappend mi $nDoc($iPhrase,$iCol)
        lappend mi $nHit($iPhrase,$iCol)
      }
    }
    set aMatchinfo($docid) $mi
  }

  # Set up the nullentry output.
  #
  set nullentry [list]
  for {set iPhrase 0} {$iPhrase<$nPhrase} {incr iPhrase} {
    for {set iCol 0} {$iCol<$nCol} {incr iCol} {
      lappend nullentry 0 $nDoc($iPhrase,$iCol) $nHit($iPhrase,$iCol)
    }
  }
}


proc matching_brackets {expr} {
  if {[string range $expr 0 0]!="(" || [string range $expr end end] !=")"} { 
    return 0 
  }

  set iBracket 1
  set nExpr [string length $expr]
  for {set i 1} {$iBracket && $i < $nExpr} {incr i} {
    set c [string range $expr $i $i]
    if {$c == "("} {incr iBracket}
    if {$c == ")"} {incr iBracket -1}
  }

  return [expr ($iBracket==0 && $i==$nExpr)]
}

proc get_near_results {tbl expr arrayvar {nullvar ""}} {
  upvar $arrayvar aMatchinfo
  if {$nullvar != ""} { upvar $nullvar nullentry }

  set expr [string trim $expr]
  while { [matching_brackets $expr] } {
    set expr [string trim [string range $expr 1 end-1]]
  }

  set prec(NOT) 1
  set prec(AND) 2
  set prec(OR)  3

  set currentprec 0
  set iBracket 0
  set expr_length [llength $expr]
  for {set i 0} {$i < $expr_length} {incr i} {
    set op [lindex $expr $i]
    if {$iBracket==0 && [info exists prec($op)] && $prec($op)>=$currentprec } {
      set opidx $i
      set currentprec $prec($op)
    } else {
      for {set j 0} {$j < [string length $op]} {incr j} {
        set c [string range $op $j $j]
        if {$c == "("} { incr iBracket +1 }
        if {$c == ")"} { incr iBracket -1 }
      }
    }
  }
  if {$iBracket!=0} { error "mismatched brackets in: $expr" }

  if {[info exists opidx]==0} {
    get_single_near_results $tbl $expr aMatchinfo nullentry
  } else {
    set eLeft  [lrange $expr 0 [expr $opidx-1]]
    set eRight [lrange $expr [expr $opidx+1] end]

    get_near_results $tbl $eLeft  aLeft  nullleft
    get_near_results $tbl $eRight aRight nullright

    switch -- [lindex $expr $opidx] {
      "NOT" {
        foreach hit [array names aLeft] {
          if {0==[info exists aRight($hit)]} {
            set aMatchinfo($hit) $aLeft($hit)
          }
        }
        set nullentry $nullleft
      }

      "AND" {
        foreach hit [array names aLeft] {
          if {[info exists aRight($hit)]} {
            set aMatchinfo($hit) [concat $aLeft($hit) $aRight($hit)]
          }
        }
        set nullentry [concat $nullleft $nullright]
      }

      "OR" {
        foreach hit [array names aLeft] {
          if {[info exists aRight($hit)]} {
            set aMatchinfo($hit) [concat $aLeft($hit) $aRight($hit)]
            unset aRight($hit)
          } else {
            set aMatchinfo($hit) [concat $aLeft($hit) $nullright]
          }
        }
        foreach hit [array names aRight] {
          set aMatchinfo($hit) [concat $nullleft $aRight($hit)]
        }

        set nullentry [concat $nullleft $nullright]
      }
    }
  }
}

proc do_near_test {tn tbl expr} {

  get_near_results $tbl $expr aMatchinfo
  set match $expr

  set matchinfo_asc [list]
  foreach docid [lsort -integer -incr [array names aMatchinfo]] {
    lappend matchinfo_asc $docid $aMatchinfo($docid)
  }
  set matchinfo_desc [list]
  foreach docid [lsort -integer -decr [array names aMatchinfo]] {
    lappend matchinfo_desc $docid $aMatchinfo($docid)
  }

  set title "(\"$match\" -> [llength [array names aMatchinfo]] rows)"

  do_execsql_test $tn$title.1 "
    SELECT docid FROM $tbl WHERE $tbl MATCH '$match' ORDER BY docid ASC
  " [lsort -integer -incr [array names aMatchinfo]] 

  do_execsql_test $tn$title.2 "
    SELECT docid FROM $tbl WHERE $tbl MATCH '$match' ORDER BY docid DESC
  " [lsort -integer -decr [array names aMatchinfo]] 

  do_execsql_test $tn$title.3 "
    SELECT docid, mit(matchinfo($tbl, 'x')) FROM $tbl 
    WHERE $tbl MATCH '$match' ORDER BY docid DESC
  " $matchinfo_desc

  do_execsql_test $tn$title.4 "
    SELECT docid, mit(matchinfo($tbl, 'x')) FROM $tbl 
    WHERE $tbl MATCH '$match' ORDER BY docid ASC
  " $matchinfo_asc
} 



# End of test procs. Actual tests are below this line.
#--------------------------------------------------------------------------

#--------------------------------------------------------------------------
# The following test cases - fts3auto-1.* - focus on testing the Tcl 
# command [fts3_near_match], which is used by other tests in this file.
#
proc test_fts3_near_match {tn doc expr res} {
  fts3_near_match $doc $expr -phrasecountvar p
  uplevel do_test [list $tn] [list [list set {} $p]] [list $res]
}

test_fts3_near_match 1.1.1 {a b c a b} a                   {2}
test_fts3_near_match 1.1.2 {a b c a b} {a 5 b 6 c}         {2 2 1}
test_fts3_near_match 1.1.3 {a b c a b} {"a b"}             {2}
test_fts3_near_match 1.1.4 {a b c a b} {"b c"}             {1}
test_fts3_near_match 1.1.5 {a b c a b} {"c c"}             {0}

test_fts3_near_match 1.2.1 "a b c d e f g" {b 2 f}         {0 0}
test_fts3_near_match 1.2.2 "a b c d e f g" {b 3 f}         {1 1}
test_fts3_near_match 1.2.3 "a b c d e f g" {f 2 b}         {0 0}
test_fts3_near_match 1.2.4 "a b c d e f g" {f 3 b}         {1 1}
test_fts3_near_match 1.2.5 "a b c d e f g" {"a b" 2 "f g"} {0 0}
test_fts3_near_match 1.2.6 "a b c d e f g" {"a b" 3 "f g"} {1 1}

set A "a b c d e f g h i j k l m n o p q r s t u v w x y z"
test_fts3_near_match 1.3.1 $A {"c d" 5 "i j" 1 "e f"}      {0 0 0}
test_fts3_near_match 1.3.2 $A {"c d" 5 "i j" 2 "e f"}      {1 1 1}

#--------------------------------------------------------------------------
# Test cases fts3auto-2.* run some simple tests using the 
# [do_near_test] proc.
#
foreach {tn create} {
  1    "CREATE VIRTUAL TABLE t1 USING fts4(a, b)"
  2    "CREATE VIRTUAL TABLE t1 USING fts4(a, b, order=DESC)"
  3    "CREATE VIRTUAL TABLE t1 USING fts4(a, b, order=ASC)"
  4    "CREATE VIRTUAL TABLE t1 USING fts4(a, b, prefix=1)"
  5    "CREATE VIRTUAL TABLE t1 USING fts4(a, b, order=DESC, prefix=1)"
  6    "CREATE VIRTUAL TABLE t1 USING fts4(a, b, order=ASC, prefix=1)"
} {
  do_test 2.$tn.1 {
    catchsql { DROP TABLE t1 }
    execsql  $create
    for {set i 0} {$i<32} {incr i} {
      set doc [list]
      if {$i&0x01} {lappend doc one}
      if {$i&0x02} {lappend doc two}
      if {$i&0x04} {lappend doc three}
      if {$i&0x08} {lappend doc four}
      if {$i&0x10} {lappend doc five}
      execsql { INSERT INTO t1 VALUES($doc, null) }
    }
  } {}
  foreach {tn2 expr} {
    1     {one}
    2     {one NEAR/1 five}
    3     {t*}
    4     {t* NEAR/0 five}
    5     {o* NEAR/1 f*}
    6     {one NEAR five NEAR two NEAR four NEAR three}
    7     {one NEAR xyz}
    8     {one OR two}
    9     {one AND two}
    10    {one NOT two}
    11    {one AND two OR three}
    12    {three OR one AND two}
    13    {(three OR one) AND two}
    14    {(three OR one) AND two NOT (five NOT four)}
    15    {"one two"}
    16    {"one two" NOT "three four"}
  } {
    do_near_test 2.$tn.2.$tn2 t1 $expr
  }
}

set sqlite_fts3_enable_parentheses $sfep
finish_test