DESCRIBE TABLE and PRAGMA table_info() do not work correctly with SQLite extension

[frosshirt]

What happens?

When using the sqlite_scanner extension in DuckDB, the DESCRIBE TABLE and PRAGMA table_info() commands return incorrect or incomplete results for tables. This issue occurs even when the table is successfully created with proper constraints.

Retrieving the constraints from duckdb_constraints() is working.

Expected Behavior:

PRAGMA table_info(t) should correctly list all columns, their types, and constraints, including the primary key.
DESCRIBE TABLE t should indicate that columns a and b are part of the primary key and display accurate information for all columns.

Actual Behavior:

PRAGMA table_info(t) returns no rows.
DESCRIBE TABLE t shows incorrect data, marking all columns as nullable and omitting the primary key information.

To Reproduce

1. Install and load the sqlite_scanner extension.
2. Attach an SQLite database and create a table with a composite primary key.
3. Attempt to retrieve the table schema using PRAGMA table_info() and DESCRIBE TABLE.

Below is the reproducible code:

-- Install and load sqlite extension and attach db
install sqlite_scanner;
load sqlite_scanner;
attach 'C:\temp\test.db' as sdb (TYPE SQLITE);
use sdb;

-- Create a table with a composite primary key
create or replace table t (a varchar(32) not null, b varchar(32) not null, c varchar(32), primary key (a, b));

-- Insert data into the table
insert into t (a, b) values ('abc', 'd'), ('abc', 'd');
-- Output: UNIQUE constraint failed: t.a, t.b

-- Retrieve schema using PRAGMA table_info()
pragma table_info(t);
-- Output:
-- ┌───────┬─────────┬─────────┬─────────┬────────────┬─────────┐
-- │  cid  │  name   │  type   │ notnull │ dflt_value │   pk    │
-- │ int32 │ varchar │ varchar │ boolean │  varchar   │ boolean │
-- ├───────┴─────────┴─────────┴─────────┴────────────┴─────────┤
-- │                           0 rows                           │
-- └────────────────────────────────────────────────────────────┘

-- Retrieve schema using DESCRIBE TABLE
describe table t;
-- Output:
-- ┌─────────────┬─────────────┬─────────┬─────────┬─────────┬─────────┐
-- │ column_name │ column_type │  null   │   key   │ default │  extra  │
-- │   varchar   │   varchar   │ varchar │ varchar │ varchar │ varchar │
-- ├─────────────┼─────────────┼─────────┼─────────┼─────────┼─────────┤
-- │ a           │ VARCHAR     │ YES     │         │         │         │
-- │ b           │ VARCHAR     │ YES     │         │         │         │
-- │ c           │ VARCHAR     │ YES     │         │         │         │
-- └─────────────┴─────────────┴─────────┴─────────┴─────────┴─────────┘

-- Showing constraints using duckdb_constraints() is working
select constraint_column_names from duckdb_constraints() where table_name = 't' and constraint_type = 'PRIMARY KEY' and database_name = (select current_catalog());
-- Output:
-- ┌─────────────────────────┐
-- │ constraint_column_names │
-- │        varchar[]        │
-- ├─────────────────────────┤
-- │ [a, b]                  │
-- └─────────────────────────┘

OS:

Windows

SQLite Extension Version:

d5d6265

DuckDB Version:

1.1.3

DuckDB Client:

CLI

Full Name:

Fabian Roßhirt

Affiliation:

IBIS Prof. Thome AG

Have you tried this on the latest main branch?

• I agree

Have you tried the steps to reproduce? Do they include all relevant data and configuration? Does the issue you report still appear there?

• I agree