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
