Re: [sqlite] Specific sqlite_autoindex_* missing in recent SQLite versions
2019-11-14 17:56, David Raymond wrote: Apparently it got smarter about "primary key unique" in 3.20.0 and stopped making the extra index when it's a without rowid table. Don't see anything about it in the release notes though. Even on the current release "primary key unique" will still make an extra index for the unique if it's a rowid table. So.. that's kinda regression, a bug? ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Specific sqlite_autoindex_* missing in recent SQLite versions
Apparently it got smarter about "primary key unique" in 3.20.0 and stopped making the extra index when it's a without rowid table. Don't see anything about it in the release notes though. Even on the current release "primary key unique" will still make an extra index for the unique if it's a rowid table. -Original Message- From: sqlite-users On Behalf Of Vincas Dargis Sent: Thursday, November 14, 2019 4:28 AM To: sqlite-users@mailinglists.sqlite.org Subject: [sqlite] Specific sqlite_autoindex_* missing in recent SQLite versions Hi list, Accidentally, when performing VACUUM using rather old SQLite 3.16.2 (from Debian 8 stretch) binary on some database file created with more recent SQLite (like 3.29.0 available in Qt 5.13.2 or a bit older), I've discovered that database now has a few more `sqlite_autodinex_*` entries in `sqlite_master` table (checked by test suite). I was puzzled at the beginning, but after some fiddling I see that creating this table: ``` CREATE TABLE "equipment_type"( id INTEGER NOT NULL PRIMARY KEY UNIQUE, name TEXT NOT NULL UNIQUE ) WITHOUT ROWID; ``` with recent SQLite, we get this set (only one) of internal indexes: ``` SELECT * from sqlite_master WHERE name LIKE 'sqlite_autoindex_eq%' index sqlite_autoindex_equipment_type_2 equipment_type 3 ``` Meanwhile, if I create same table using older 3.16.2 (on Debian 9 stretch), I get indexes for two fields: ``` index|sqlite_autoindex_equipment_type_1|equipment_type|3| index|sqlite_autoindex_equipment_type_2|equipment_type|4| ``` It feels strange if UNIQUE and PRIMARY KEY did not provoke creating `sqlite_autoindex`, but maybe we are missing something? Is this behavior change expected? Thanks! ___ 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
[sqlite] Specific sqlite_autoindex_* missing in recent SQLite versions
Hi list, Accidentally, when performing VACUUM using rather old SQLite 3.16.2 (from Debian 8 stretch) binary on some database file created with more recent SQLite (like 3.29.0 available in Qt 5.13.2 or a bit older), I've discovered that database now has a few more `sqlite_autodinex_*` entries in `sqlite_master` table (checked by test suite). I was puzzled at the beginning, but after some fiddling I see that creating this table: ``` CREATE TABLE "equipment_type"( id INTEGER NOT NULL PRIMARY KEY UNIQUE, name TEXT NOT NULL UNIQUE ) WITHOUT ROWID; ``` with recent SQLite, we get this set (only one) of internal indexes: ``` SELECT * from sqlite_master WHERE name LIKE 'sqlite_autoindex_eq%' index sqlite_autoindex_equipment_type_2 equipment_type 3 ``` Meanwhile, if I create same table using older 3.16.2 (on Debian 9 stretch), I get indexes for two fields: ``` index|sqlite_autoindex_equipment_type_1|equipment_type|3| index|sqlite_autoindex_equipment_type_2|equipment_type|4| ``` It feels strange if UNIQUE and PRIMARY KEY did not provoke creating `sqlite_autoindex`, but maybe we are missing something? Is this behavior change expected? Thanks! ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
