Simple SQLite Question - maybe for Tom
Quote from thejerrybryan on 2019-04-09, 4:37 pmThe addition of COLLATE NOCASE to the query solves the problem. But I repeat that it's difficult to see what characteristic of RMNOCASE there could be that would produce my particular symptoms.
Obviously, this is a really trivial issue - solved either by COLLATE NOCASE or by sorting on Abbrev instead of Name. It's just a very puzzling mystery that I'm very curious about.
It's an unsettling issue that makes the fake RMNOCASE collation suspect or the underlying B-Tree sort suspect. It's the sort of subtle thing that could manifest itself in other databases under the right conditions. I'm sure the developers would be interested in a replicatible test database. I tried to emulate the situation with the following code but the results are fine. Could you make a copy of your database and DROP all the tables but those of interest to share it with me or directly with Dr. Hipp?
CREATE TABLE Names (NameID INTEGER Primary Key, Name TEXT Collation RMNOCASE)
;
CREATE INDEX NameIdx ON Names (Name COLLATE RMNOCASE)
;
INSERT INTO Names (Name)
SELECT 'MARRIAGE '|| CHAR((ABS(RANDOM())%91)%26+65)
; --repeated ~500 times to get "MARRIAGE " || 1 of {A,B,C,...,Z}
SELECT DISTINCT name FROM Names
; --returns 26
CREATE TABLE Event (EventID INTEGER PRIMARY KEY, NameID INTEGER)
;
INSERT INTO Event (NameID) SELECT (ABS(RANDOM()))%500+1
; -- repeat many times
SELECT Name, EventID, Event.NameID AS NameID
FROM Names
JOIN
Event
ON (Names.NameID = Event.NameID)
ORDER BY Name, EventID -- or BY Name, NameID
; -- inspect for sorting anomaly
My first attempt to upload the files failed - it spun forever without completing and without producing an error message. I suspect the problem was the rmgc file. It's not one of file types that are listed as supported. And DROPing the unnecessary tables as requested did not reduce its size as reported by Windows. So let's try again.
There are two files attached: 1) rmnocase_error.sql which may be executed to produce the report with the erroneous collation, and 2) rmnocase_error.jpg which contains a screen shot of running the report with the erroneous collation.
For the database itself, I have created a Dropbox link. It may be accessed at https://www.dropbox.com/s/gukz1i1a2wl0xz2/copy_for_tom.rmgc?dl=0
Jerry
Uploaded files:
Jerry, I cannot replicate your results using SQLiteSpy 1.9.13 with the extension RMNOCASE_fake-SQLiteSpy.dll. Everything sorts normally. This suggests that the issue is in your system. Reinstall SQLiteSpy? Try a different SQLite manager? Test your RAM?
There should be no difference in SQLiteSpy between using the explicit NOCASE collation and the fake RMNOCASE extension . The latter is just an alias for NOCASE so it should be referencing the exact same collation sequence. Where unifuzz.dll is used as RMNOCASE, it is truly a different collation than NOCASE.
BTW, to return no longer needed disk space from the database file to Windows, run the SQLite VACUUM command (I think I mistakenly said Compact which is RM parlance). That reduces the file from ~290MB to ~23MB in a few seconds. I tested both before and after VACUUM. I will add .rmgc and .rmgb to the allowed uploads.
The fact that you can't replicate my results is distressing but not surprising. I'm on SQLiteSpy 1.9.11. I'll upgrade to 1.9.13 and see if that solves the problem. If that doesn't solve the problem, then I'll try to find a newer rmnocase_fake-sqlitespy.dll. If that doesn't solve the problem, then I just don't really know.
Jerry
I was distracted by other projects, but I was able to get back to this one tonight. I downloaded SQLiteSpy 1.9.13 (made sure to get the 64 bit version) and also downloaded the fake RMNOCASE again, using the URL https://sqlitetoolsforrootsmagic.com/wp-content/uploads/2017/12/RMNOCASE_fake-SQLiteSpy.dll.bak Having done so, all is well. I'm not sure if it was the newer SQLiteSpy or the newer RMNOCASE that fixed the problem, but it's fixed.