On Wednesday, 9 October, 2019 12:01, Jens Alfke <[email protected]> said:
>BETWEEN doesn't work well because it's inclusive, i.e. `BETWEEN 'foo' and
>'fop'` doesn't work because it matches 'fop'. Coming up with the upper
>end of a string prefix match is super annoying — `BETWEEN 'foo' and
>'foo\xff' only works until some wise guy adds the key `foo\xff` to the
>table, and is invalid UTF-8 anyway.
I don't think that the UTF-8 point is meaningful. However, If *I* were in need
of doing this I would use a construct that looks like this:
where (name between :prefix and (:prefix || char(255)) and substr(name, 1,
length(:prefix)) collate nocase == :prefix)
where the "name between :prefix and (:prefix || char(255))" constrains the
index search and "substr(name, 1, length(:prefix)) collate nocase == :prefix"
constrains the resulting candidates.
(Replace all instances of "collate nocase" with the collation you want to use,
omit entirely to use the default BINARY collation).
>sqlite
SQLite version 3.30.0 2019-10-09 16:25:22
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> create table nodes(name text not null collate nocase unique);
sqlite> insert into nodes values ('dangtalk');
sqlite> insert into nodes values ('dingdong');
sqlite> insert into nodes values ('dingwhit');
sqlite> insert into nodes values ('dongdong');
sqlite> .param init
sqlite> .param set :prefix 'DING'
sqlite> .eqp full
sqlite> select * from nodes where name between :prefix and (:prefix ||
char(255)) and substr(name,1,length(:prefix)) collate nocase == :prefix;
QUERY PLAN
`--SEARCH TABLE nodes USING COVERING INDEX sqlite_autoindex_nodes_1 (name>? AND
name<?) (~15360 rows)
addr opcode p1 p2 p3 p4 p5 comment
---- ------------- ---- ---- ---- ------------- -- -------------
0 Init 0 24 0 00 Start at 24
1 OpenRead 1 3 0 k(1,NOCASE) 00 root=3 iDb=0;
sqlite_autoindex_nodes_1
2 ColumnsUsed 1 0 0 1 00
3 Explain 3 0 0 SEARCH TABLE nodes USING COVERING INDEX
sqlite_autoindex_nodes_1 (name>? AND name<?) (~15360 rows) 00
4 Noop 0 0 0 00 Begin WHERE-loop0:
nodes
5 CursorHint 1 0 0 AND(expr,EQ(expr,expr)) 00
6 Variable 1 1 0 :prefix 00
r[1]=parameter(1,:prefix)
7 IsNull 1 22 0 00 if r[1]==NULL goto 22
8 Affinity 1 1 0 B 00 affinity(r[1])
9 SeekGE 1 22 1 1 00 key=r[1]
10 Concat 3 2 1 00 r[1]=r[2]+r[3]
11 IsNull 1 22 0 00 if r[1]==NULL goto 22
12 Affinity 1 1 0 B 00 affinity(r[1])
13 IdxGT 1 22 1 1 00 key=r[1]
14 Column 1 0 5 00 r[5]=nodes.name
15 Function0 6 5 4 substr(3) 03 r[4]=func(r[5..7])
16 Ne 2 21 4 (NOCASE) 50 if r[4]!=r[2] goto
21
17 Noop 0 0 0 00 Begin WHERE-core
18 Column 1 0 8 00 r[8]=nodes.name
19 ResultRow 8 1 0 00 output=r[8]
20 Noop 0 0 0 00 End WHERE-core
21 Next 1 13 0 00
22 Noop 0 0 0 00 End WHERE-loop0: nodes
23 Halt 0 0 0 00
24 Transaction 0 0 1 0 01 usesStmtJournal=0
25 Variable 1 2 0 :prefix 00
r[2]=parameter(1,:prefix)
26 Integer 255 9 0 00 r[9]=255
27 Function0 1 9 3 char(-1) 01 r[3]=func(r[9])
28 Integer 1 6 0 00 r[6]=1
29 Variable 1 10 0 :prefix 00
r[10]=parameter(1,:prefix)
30 Function0 1 10 7 length(1) 01 r[7]=func(r[10])
31 Goto 0 1 0 00
dingdong
dingwhit
--
The fact that there's a Highway to Hell but only a Stairway to Heaven says a
lot about anticipated traffic volume.
_______________________________________________
sqlite-users mailing list
[email protected]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
