Only your first example fits the conditions for this optimization.

Isn't that missing the OP's point though?

I.e. that those extra brackets are an artifact of the GLOB's syntax escaping rules,
but the optimizer currently does not know that, and thus there is a missed opportunity on that optimization.

In other words, the queries with brackets logically should be able to take advantage of the like optimization,
but they fail to do so with the current implementation. And the OP is hoping that DRH can remedy that.
(probably with like 2-10 lines of code, as he often amazingly can do!)

There could be such an expression for any of the millions of directories I have backed up, sadly, so I cannot make an index for each. The use case is https://github.com/leijurv/gb/commit/e2b543ce380e865d900072cb0d6932be28040ef9

I'd love such a check :-) or an alternative (supported in existing sqlite)?

Yes thank you. A patch to allow a single character in square brackets to be index accelerated in a GLOB just as well as that character alone would be appreciated.

In short, I'd love WHERE path GLOB '[a]*' to work just like WHERE path GLOB 'a*', even though it doesn't today, so as to allow convenient searching for special characters like asterisk and square brackets.

Alternatively, after reading the above link, I suppose I could do a lexicographic select manually then filter it. For example WHERE path GLOB 'b*foo' becomes WHERE path > 'a' AND path < 'c' AND path GLOB 'b*foo' perhaps? But that's worse and less readable and such.

I see the confusion, but no. I would want path > 'b*foo' || NULL AND path < 'b*foo' || 0xff (or something vaguely like that, where NULL means a 0 character and 0xff means the highest possible character, I think).

The whole point is that I am able to query the entire path using the index and NOT stopping at the asterisk. The current GLOB approach only uses the index "up to" the asterisk, I want to fix that.

Perhaps I'm missing something, but why would the GLOB be necessary?

You are manually doing the like-optimization for GLOB basically. So the GLOB is consumed and no longer necessary IMHO.

And the * is for GLOB, so shouldn't be in the RHS of >.
And 0 is the next char after / then, not +.

The OP's hope is for that optimization to be EQP-driven, not manual.

WHERE path > '/some/path/folderwith[brackets]/more/folders/'
  AND path < '/some/path/folderwith[brackets]/more/folders0'

Why not just:

where path between '/some/pathfolderwith[' and '/some/pathfolderwith[' || char(1e7)

This will select all paths that startwith the sequence '/some/pathfolderwith['. char(1e7) returns the largest possible unicode codepoint as utf-8. If you really want to ensure that there is a ']/' after that then you can cull the index located candidates using a regex or glob if you like (whichever floats your boat and is available to you) since the path between will be used to constrain the candidates via an index.

where path between '/some/pathfolderwith[' and '/some/pathfolderwith[' || char(1e7)
  and path regex '.+/\[[0-9A-Za-z]*\]/.+'

char(1e7) returns the largest possible unicode codepoint as utf-8.

Nope. The char function returns U+FFFD REPLACEMENT CHARACTER for out-of-range inputs. char(0x10FFFF) would work.

You are correct. However, I suspect that you only need to append x'ff'. That works with all the collations I have tried, including unicode (icu) collations (at least on Windows using the built-in Windows ICU).

Why would I have to stop at the first square bracket? Perhaps couldn't I solve the problem with nothing more than WHERE path BETWEEN '/some/path/folderwith[brackets]/more/folders.' AND '/some/path/folderwith[brackets]/more/folders0'? I think this would work because . is the ASCII character right below / and 0 is the ASCII character right above it? Or am I missing something?

Actually, as per https://sqlite.org/forum/forumpost/2dd98dab4bfb52d1 I have just realized that BETWEEN is inclusive not exclusive. Therefore I just need something like: WHERE path BETWEEN '/some/path/folderwith[brackets]/more/folders/' AND '/some/path/folderwith[brackets]/more/folders/' || char(0x10FFFF). It seems like this works correctly:

sqlite> create table files(path string);
sqlite> create index files_by_path on files(path);
sqlite> explain query plan SELECT * FROM files WHERE path BETWEEN 'abc/' and 'abc/' || char(0x10FFFF);
QUERY PLAN
`--SEARCH files USING COVERING INDEX files_by_path (path>? AND path<?)
sqlite> explain query plan SELECT * FROM files WHERE path BETWEEN 'abc/with[brackets]/' and 'abc/with[brackets]/' || char(0x10FFFF);
QUERY PLAN
`--SEARCH files USING COVERING INDEX files_by_path (path>? AND path<?)
sqlite> SELECT 'abc/with[brackets]/foo' BETWEEN 'abc/with[brackets]/' and 'abc/with[brackets]/' || char(0x10FFFF);
1
sqlite> 

Thank you all, I think this solves it!

Edit: committed

Thank you all, I think this solves it!

WHERE path
  BETWEEN '/some/path/folderwith[brackets]/more/folders/'
      AND '/some/path/folderwith[brackets]/more/folders/ || char(0x10FFFF)'

I don't see how that's better than https://sqlite.org/forum/forumpost/773c6df11c, to be honest.

Strictly less than the next char that replaces the tail char (i.e. exclusive), versus Equal-or-less than the highest-possible char appended to the tail (i.e. inclusive), are functionally equivalent. (you just have to check one more char!)

It's also more verbose, and assume prior knowledge of inclusivity of between and of unicode limits, while op> and op< are mostly obvious to all.

Also the SQL is closer to the plan, with the ordering operators. But to each his/her own I guess :)

PS: On 2nd thought, the one advantage of the above between where-clause is that you can bind the same value, using between ?1 and ?1 || char(0x10FFFF).

As an interesting note, you should probably append x'ff' (or x'ffff' or even x'f48fbfbf') rather than char(0x10ffff).

Although char(0x10ffff) should be constant (the function declaration declares it as constant) the construct:

where x between 'something' and 'something' || char(0x10ffff)

currently executes the 'something' || char(0x10ffff) for each row (though it would only do the calculation once if it were being used to constrain an index).

where x between 'something' and 'something' || x'ff'

computes 'something' || x'ff' as a constant in the statement prolog.

Correction -- it does put a ONCE construct around the computation so ignore this

My thought is that to do it with op< and op> I would need to do something like: WHERE path > ?1 AND path < ?2, and pass in two parameters, one being the path and the other being the path with the last slash replaced with a period. That feels awkward to me, as I'd have to write extra code in my Go program to handle this in every case where I want to query the contents of a directory. WHERE path BETWEEN ?1 AND ?1 || CHAR(0x10FFFF) allows me to pass in the directory unmodified.

Yes, that's my PS edit was about (which you may have missed).

But if your path always ends with a slash, you can also trim it in code and do:

WHERE path BETWEEN ?1 || '/' AND ?1 || '0'

PS: Not a period, but a 0. The next char after / is 0, not ,.