minor correction, char(255) should be x'ff' ... char(255) does not return the
byte 0xff, but x'ff' is the byte 0xff ...
where (name between :prefix and (:prefix || x'ff') and substr(name, 1,
length(:prefix)) collate nocase == :prefix)
--
The fact that there's a Highway to Hell but only a Stairway to Heaven says a
lot about anticipated traffic volume.
>-----Original Message-----
>From: sqlite-users <sqlite-users-boun...@mailinglists.sqlite.org> On
>Behalf Of Keith Medcalf
>Sent: Wednesday, 9 October, 2019 13:04
>To: SQLite mailing list <sqlite-users@mailinglists.sqlite.org>
>Subject: Re: [sqlite] SELECT uses index with SUBSTR but UPDATE doesn't
>
>
>On Wednesday, 9 October, 2019 12:01, Jens Alfke <j...@mooseyard.com>
>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
>sqlite-users@mailinglists.sqlite.org
>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
