sqlite3 ".expert" command returns "no such function: REGEXP" for each sql command containing 'REGEXP'

sqlite> .expert
sqlite> select * from sqlite_master where sql REGEXP '.*table.*';
Error: no such function: REGEXP

sqlite> select sqlite_version();
3.43.1

The "expert" extension needs a little more work to support SQL with user-defined functions. (aka "UDFs") Currently, it creates a temporary DB (with a private connection to use it) and replicates certain aspects of the user's DB to that temporary DB so that index suggestions can be formulated. However, the extension does not register the set of UDFs with the new connection that are registered with the user's DB connection. Hence, those UDFs are unknown as the expert extension does its compilation. This same issue arises with a slew of other UDFs that the shell always registers for user connections.

I'm not sure when (or if) this will be fixed. The feature is marked "EXPERIMENTAL", and has always had this limitation. I will have more to say on this after consultation with the development team.

Nevertheless, thanks for the report.

Would it make sense to have .expert pretend to have any undefined functions it comes across? Or depending on how it works, have it register some dummy function when it comes across an undefined function error, then retry?

Would it make sense to have .expert pretend to have any undefined functions it comes across? Or depending on how it works, have it register some dummy function when it comes across an undefined function error, then retry?

The attributes of a function, e.g. whether or not it's deterministic, can be used by the query planner and having .expert mode stub those would cause it, at times, to give different results than without .expert, which would cause more confusion than it solved.

I would argue that the work needed is not little, I genuinely tried making it work before and gave up after a while

This reported difficulty with .expert, along with a similar one, has been fixed on the repo trunk.

With the changes, all UDFs registered with a connection given to the expert extension are also registered, by name and type (but not implementation¹), with the temporary DBs that expert uses for its machinations. Further, provided that user-provided SQL compiles against the user connection, any custom collation sequence used in that SQL is stubbed out.²

There was a theoretic difficulty with "replicating" UDFs. A UDF can be either of types "scalar", "aggregate" or "window", or some combinations thereof. The replication, relying as it does on pragma_function_list() results, can only create the first three of those possible types as stand-ins. This might, conceivably, affect a query plan to a degree that would affect index recommendations. This was adjudged unlikely to happen, and if it does it is likely a minor effect. So, since expert's effort was always a "best effort", this theoretic shortcoming has been deemed acceptable. The alternative was to mess with SQLite library interfaces to support digging out complete UDF characteristics. That was deemed a step too far for today.

1. ^{^} There is no need for any UDF implementation; the compiled SQL is never run.
2. ^{^} Because the compiled SQL is not run, the stub is never called, so it do-nothing nature does not matter.

Went to try it out and gcc is throwing "parameter name omitted" errors in the new code, for a few new functions.

shell.c: In function 'dummyCompare':
shell.c:12850:18: error: parameter name omitted
 int dummyCompare(void*, int, const void*, int, const void*){
                  ^~~~~
shell.c:12850:25: error: parameter name omitted
 int dummyCompare(void*, int, const void*, int, const void*){
                         ^~~
shell.c:12850:30: error: parameter name omitted
 int dummyCompare(void*, int, const void*, int, const void*){
                              ^~~~~~~~~~~
shell.c:12850:43: error: parameter name omitted
 int dummyCompare(void*, int, const void*, int, const void*){
                                           ^~~
shell.c:12850:48: error: parameter name omitted
 int dummyCompare(void*, int, const void*, int, const void*){
                                                ^~~~~~~~~~~
shell.c: In function 'useDummyCS':
shell.c:12854:17: error: parameter name omitted
 void useDummyCS(void *, sqlite3 *db, int etr, const char *zName){
                 ^~~~~~
shell.c: In function 'dummyUDF':
shell.c:12863:15: error: parameter name omitted
 void dummyUDF(sqlite3_context*,int,sqlite3_value**){
               ^~~~~~~~~~~~~~~~
shell.c:12863:32: error: parameter name omitted
 void dummyUDF(sqlite3_context*,int,sqlite3_value**){
                                ^~~
shell.c:12863:36: error: parameter name omitted
 void dummyUDF(sqlite3_context*,int,sqlite3_value**){
                                    ^~~~~~~~~~~~~~~
shell.c: In function 'dummyUDFvalue':
shell.c:12866:20: error: parameter name omitted
 void dummyUDFvalue(sqlite3_context*){
                    ^~~~~~~~~~~~~~~~

Thanks for that report. Please try next check-in, reflecting my reversion to the C89 mindset.

Yep, it compiled just fine this time around.
I still have a "no such function" issue though, presumably because I have virtual columns with UDFs. When I use .expert, it immediately gives sqlite3_expert_new: no such function: IP_TO_INT. Not sure if this is beyond the scope of what you wanted to do though

compiled just fine

Thanks.

I still have a "no such function" issue though, presumably because I have virtual columns with UDFs. ...

I do not see why this should matter. Can you please provide a repro sequence in the CLI, using one of its UDFs (such as result from "select name from pragma_function_list where builtin=0;")?

Not sure if this is beyond the scope of what you wanted to do though

The intent is to make expert work with UDFs as well as with BIFs.

Thanks for the repro. This defect is fixed now.

I did not realize that UDF lookups could occur during the schema copy that happens when .expert is executed. So I learned something today.

Nice, that fixed that issue. Unfortunately, I did accidentally find one more. Virtual tables cause .expert to fail immediately as well. sqlite3_expert_new: no such module: csv

Easily solved for me by just removing the virtual table I forgot about, but could be annoying for others.