sqlite3 CLI .expert mode returns “Error: not an error”, if suggested index name is created, but does not match suggestion
(1.1) By Thomas Hess (luziferius) on 2021-09-21 13:59:17 edited from 1.0 [source]
Version found in: 3.34.1 (As shipped by Ubuntu 21.04) and the latest commit to trunk, which currently is [af5dcc9c2a3a45d1].
Minimal reproduction:
> CREATE TABLE t (a INTEGER PRIMARY KEY, b TEXT NOT NULL, c TEXT NOT NULL);
> .expert
> SELECT * from t AS t1 JOIN t AS t2 USING (b, c);
CREATE INDEX t_idx_00012959 ON t(c, b);
SCAN t1
SEARCH t2 USING COVERING INDEX t_idx_00012959 (c=? AND b=?)
> CREATE INDEX t_idx_00012959 ON t(c); -- Note that this is different from the suggested index.
> .expert
> SELECT * from t AS t1 JOIN t AS t2 USING (b, c);
Error: not an error
I found this, because I wanted to optimize an analysis query on a database. I reused the previously suggested index name, because using the history is faster than typing out the CREATE INDEX statement and coming up with a new name ;) .
My educated guess: This is caused by a name collision. Expert mode tries to suggest an index, but the name is unexpectedly already taken, because it assumes that the hash suffix is sufficient to prevent a collision.
Even if you don’t want to support this edge case, at least the error message should be improved :)
(2) By Larry Brasfield (larrybr) on 2021-09-21 13:06:23 in reply to 1.0 [link] [source]
Yes, that error message is anti-tautological.
The output should be "(no new indexes)" instead of that cryptic error, right? In other words, no new index should be proposed if I understand your scenario.
(3) By Thomas Hess (luziferius) on 2021-09-21 13:22:18 in reply to 2 [link] [source]
The output should be "(no new indexes)" instead of that cryptic error, right?
Not necessarily. Only if the modified index actually results in a presumably optimal plan. If the modified CREATE INDEX statement does not give the prospect of reasonable speed-up, it should still suggest the original index, but preferably with a non-colliding name.
E.g. if the index in the above example were created as
CREATE INDEX t_idx_00012959 ON t(a); (i.e. create a useless index on the INTEGER PRIMARY KEY column)., it should still suggest the index creation ON t(b, c)
(4.1) By Larry Brasfield (larrybr) on 2021-09-21 13:44:36 edited from 4.0 in reply to 3 [link] [source]
The output should be "(no new indexes)" instead of that cryptic error, right?
Not necessarily. Only if the modified index actually results in a presumably optimal plan.
Now I feel confused. I thought that was precisely the case with your usage where you got a strange rather than useful or expected response.
At this example session, when the index exists that .expert mode would suggest were it missing, it is supposed to say "(no new indexes)". And if I understand your repro steps, it produces that strange-but-humorous error instead.
If you disagree, please show the sequence of SQLite shell commands that induces the error. (I thought you already had. I'm not trying to be difficult here; maybe it's time for more coffee.)
(Append via edit to resolve confusion:)
Ok, now I see that the index you added is subtly different from the one proposed. I guess I need to read more carefully. Now I agree that this is a bug, not cured by simply improving an error message.
(5) By Thomas Hess (luziferius) on 2021-09-21 14:03:25 in reply to 4.1 [link] [source]
Ok, now I see that the index you added is subtly different from the one proposed.
I’ve edited the original script and added a comment that points this out clearly. That’s my fault, I should have done that in the first place.
not cured by simply improving an error message.
An error message like “Name collision in suggested index. Index with name t_idx_00012959 already exists.” should be sufficient, if taking existing indices into account for the name generation proves to be overly complicated.
(6) By Larry Brasfield (larrybr) on 2021-09-21 20:16:41 in reply to 1.1 [link] [source]
Thomas,
Among the other noise, there should have been thanks for reporting the problem. Please accept my belated: Thanks.
The problem is fixed on trunk, as of this checkin. Please let us know of any more strange corners of the .expert input space that you find infested.
(7) By Thomas Hess (luziferius) on 2021-09-22 11:44:48 in reply to 6 [link] [source]
Please accept my belated: Thanks.
Accepted.
Please also accept my thanks for fixing this issue in a timely manner. :-)
The problem is fixed on trunk
Tried it on both my reported,reduced case and the original query it came up with, and it indeed works as expected now.
The new error message “Cannot find a unique index name to propose.” that is output in case no free name can be found is also really clear.
Please let us know of any more strange corners of the .expert input space that you find infested.
Will do, if something comes up.
The expert mode already was of great help: I would never have thought of creating a COLLATE NOCASE index for LIKE matches, which sped up a frequently occurring query by factor ~1000.
(8) By Scott Robison (casaderobison) on 2021-09-22 15:53:05 in reply to 7 [link] [source]
I didn't think to try expert mode, but recently had to troubleshoot a query issue at work where they wanted a query to run every 1/10 of a second on a largeish dataset. Unfortunately, the query was taking about 1.1s to run, so just a little outside the 0.1s desired window. I was able to use explain query plan to figure out to add an index to each of two tables that got the query from 1.1s to 0.0001s. I should go back now and see what expert mode suggested.