Wrong result: pragma_page_count('two') and pragma_journal_mode('two') report NULL in their schema column
(1) By Marius (mfleisher) on 2026-11-04 20:26:34 [source]
For the pragma table-valued functions pragma_page_count and pragma_journal_mode, whose only argument is the optional schema name, the hidden schema column reads back as NULL even when a schema was given, so a query that tests that column again loses the row: pragma_page_count('two') reports the page count of the attached database two but schema NULL, and adding WHERE schema IS NOT NULL returns nothing. pragma_table_info('t1','two'), which also takes a regular argument, reports its schema column as 'two'.
Tested on SQLite 3.54.1 and on trunk as of 2026-11-04 (check-in 3cbb575c6eae7c03), both compiled with clang 14.0.0; the output below is the same on both.
Steps:
./configure && make sqlite3
./sqlite3 < script.sql
script.sql:
.mode quote
ATTACH ':memory:' AS two;
CREATE TABLE two.t1(x);
CREATE TABLE two.t2(x);
.print "-- (1) PRAGMA two.page_count:"
PRAGMA two.page_count;
.print "-- (2) schema, page_count FROM pragma_page_count('two'):"
SELECT schema, page_count FROM pragma_page_count('two');
.print "-- (3) the same with WHERE schema IS NOT NULL added:"
SELECT schema, page_count FROM pragma_page_count('two') WHERE schema IS NOT NULL;
.print "-- (4) schema, journal_mode FROM pragma_journal_mode('two'):"
SELECT schema, journal_mode FROM pragma_journal_mode('two');
.print "-- (5) schema, name FROM pragma_table_info('t1','two'), a pragma function that also takes an argument:"
SELECT schema, name FROM pragma_table_info('t1','two');
Output:
-- (1) PRAGMA two.page_count:
3
-- (2) schema, page_count FROM pragma_page_count('two'):
NULL,3
-- (3) the same with WHERE schema IS NOT NULL added:
-- (4) schema, journal_mode FROM pragma_journal_mode('two'):
NULL,'memory'
-- (5) schema, name FROM pragma_table_info('t1','two'), a pragma function that also takes an argument:
'two','x'
Expected: (2) and (4) should show 'two' in the schema column, as (5) does, and (3) should return the same row as (2).
Possible cause (Claude's reading of the source, not verified): for a pragma without a regular argument, pragmaVtabFilter() in src/pragma.c stores the schema value in the second argument slot (the slot used as the schema prefix of the PRAGMA statement it builds, so the right database is examined), while pragmaVtabColumn() returns hidden-column values by position starting at the first slot, which is empty.
Found with Claude at Anthropic; the output shown was produced by running the script above on both versions.