Hello, I found a bug in sqlite when using table-values pragma functions with attached databases when using the sqlite3.exe for windows. In my tests I found out, that it is not possible to use table-valued pragma functions on attached databases e.g. to read user_version or schema_version.
So the Query: SELECT a.user_version as 'main_version', b.user_version as 'attached_version' FROM main.pragma_user_version() as 'a', test_db.pragma_user_version() as 'b'; Should display the user_version of main-database and attached database as wel, but both values display always the version of the main database. I tested this with 2 szenarios, first with attaching a in-memory database and second with attaching a physical database. Results are identical for both. Infos about test envoirement OS: Windows XP Professional Service Pack 3 (32-Bit) Sqlite-Version: 3.27.1 2019-02-08 13:17:39 0eca3dd3d38b31c92b49ca2d311128b74584714d9e7de895b1a6286ef959a1dd Test-Command: sqlite3 -echo bug_test.db < pragma_bug-testcase.sql To make it easy to reconstruct this Bug I added example outputs and my inputs-commands (as sql file) for sqlite3.exe. Simply run "sqlite3 -echo bug_test.db < pragma_bug-testcase.sql" on any test-databse and compare results to my "test-ouputs" for szenario 1 and szenario 2.
-- switch display mode .mode column .header on -- show my sqlite-version SELECT sqlite_version(); sqlite_version() ---------------- 3.27.1 -- TEST SZENARIO 1: attach an in-memory test-database ATTACH DATABASE ':memory:' AS test_db; -- TEST SZENARIO 2: attach another psysical database -- lets check if attached database is here SELECT * FROM pragma_database_list(); seq name file ---------- ---------- ------------------------------------------------------------ 0 main J:\bug_test.db 2 test_db SELECT a.user_version as 'main_version', b.user_version as 'attached_version' FROM main.pragma_user_version() as 'a', test_db.pragma_user_version() as 'b'; main_version attached_version ------------ ---------------- 0 0 -- Change user Version of main-db and attached db PRAGMA main.user_version=123; PRAGMA test_db.user_version=456; -- WRONG-BEHAVOIR: both cols show the values for main-db -- Expected Behavoir: Col 'test_db.pragma_user_version()' should show values from attached database 'test_db' like 'PRAGMA test_db.user_version;' SELECT a.user_version as 'main_version', b.user_version as 'attached_version' FROM main.pragma_user_version() as 'a', test_db.pragma_user_version() as 'b'; main_version attached_version ------------ ---------------- 123 123 -- This shows correct values PRAGMA main.user_version; user_version ------------ 123 PRAGMA test_db.user_version; user_version ------------ 456 -- Show that database-name prefix is completly ignored: -- Expected behavoir: 'Error: unknown database not_existing_database' - because no database name 'not_existing_database' exists -- Actual behavoir: no error, it shows the value for pragma user_version from main-db SELECT * FROM not_existing_database.pragma_user_version(); user_version ------------ 123 .quit
-- switch display mode .mode column .header on -- show my sqlite-version SELECT sqlite_version(); sqlite_version() ---------------- 3.27.1 -- TEST SZENARIO 1: attach an in-memory test-database -- ATTACH DATABASE ':memory:' AS test_db; -- TEST SZENARIO 2: attach another psysical database ATTACH DATABASE './physical.db' AS test_db; -- lets check if attached database is here SELECT * FROM pragma_database_list(); seq name file ---------- ---------- ------------------------------------------------------------ 0 main J:\bug_test.db 2 test_db J:\physical.db SELECT a.user_version as 'main_version', b.user_version as 'attached_version' FROM main.pragma_user_version() as 'a', test_db.pragma_user_version() as 'b'; main_version attached_version ------------ ---------------- 0 0 -- Change user Version of main-db and attached db PRAGMA main.user_version=123; PRAGMA test_db.user_version=456; -- WRONG-BEHAVOIR: both cols show the values for main-db -- Expected Behavoir: Col 'test_db.pragma_user_version()' should show values from attached database 'test_db' like 'PRAGMA test_db.user_version;' SELECT a.user_version as 'main_version', b.user_version as 'attached_version' FROM main.pragma_user_version() as 'a', test_db.pragma_user_version() as 'b'; main_version attached_version ------------ ---------------- 123 123 -- This shows correct values PRAGMA main.user_version; user_version ------------ 123 PRAGMA test_db.user_version; user_version ------------ 456 -- Show that database-name prefix is completly ignored: -- Expected behavoir: 'Error: unknown database not_existing_database' - because no database name 'not_existing_database' exists -- Actual behavoir: no error, it shows the value for pragma user_version from main-db SELECT * FROM not_existing_database.pragma_user_version(); user_version ------------ 123 .quit
_______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users