DESCRIBE TABLE and PRAGMA table_info() do not work correctly with SQLite extension #125
Description
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
- Install and load the sqlite_scanner extension.
- Attach an SQLite database and create a table with a composite primary key.
- 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:
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