`.schema table_name` return unexcepted result?

[Cayincc]

What happens?

Corresponding columns disappear:

duckdb:

D .schema tokyo_medals
CREATE INDEX ix_tokyo_medals_country_code ON tokyo_medals USING  ();
CREATE TABLE tokyo_medals(country_code VARCHAR, gold_medal BIGINT, silver_medal BIGINT, bronze_medal BIGINT);
D 

sqlite:

sqlite> .schema tokyo_medals
CREATE TABLE tokyo_medals(country_code VARCHAR, gold_medal BIGINT, silver_medal BIGINT, bronze_medal BIGINT);
CREATE INDEX ix_tokyo_medals_country_code ON tokyo_medals(country_code);
sqlite>

To Reproduce

• open sqlite3 db: duckdb xxx.db
• run .schema table_name

OS:

x86_64

DuckDB Version:

v1.1.2 f680b7d08f

DuckDB Client:

Command line

Hardware:

No response

Full Name:

Cayin Wan

Affiliation:

None

What is the latest build you tested with? If possible, we recommend testing with the latest nightly build.

I have tested with a stable release

Did you include all relevant data sets for reproducing the issue?

No - Other reason (please specify in the issue body)

Did you include all code required to reproduce the issue?

• [X] Yes, I have

Did you include all relevant configuration (e.g., CPU architecture, Python version, Linux distribution) to reproduce the issue?

• [X] Yes, I have

[szarnyasg]

Hi @Cayincc, thanks for opening this issue. What's the original schema definition?

The following seems to work fine for me:

D CREATE TABLE tokyo_medals(country_code VARCHAR, gold_medal BIGINT, silver_medal BIGINT, bronze_medal BIGINT);
D CREATE INDEX ix_tokyo_medals_country_code ON tokyo_medals(country_code);
D .schema
CREATE INDEX ix_tokyo_medals_country_code ON tokyo_medals(country_code);
CREATE TABLE tokyo_medals(country_code VARCHAR, gold_medal BIGINT, silver_medal BIGINT, bronze_medal BIGINT);

[Cayincc]

Dateset from cmu 15 445 2024fall - homework#1:

wget https://15445.courses.cs.cmu.edu/fall2024/files/olympics-cmudb2024.db.gz

[szarnyasg]

Thanks!

[szarnyasg]

The output of .schema is consistent with duckdb_indexes, so the information is lost somewhere else. This is likely a SQLite connector issue, so I'm moving it to that repository.

$ duckdb olympics-cmudb2024.db

v1.1.2 f680b7d08f
Enter ".help" for usage hints.
D select sql from duckdb_indexes();
┌──────────────────────────────────────────────────────────────────────┐
│                                 sql                                  │
│                               varchar                                │
├──────────────────────────────────────────────────────────────────────┤
│ CREATE INDEX sqlite_autoindex_gender_1 ON gender USING  ();          │
│ CREATE INDEX sqlite_autoindex_medal_info_1 ON medal_info USING  ();  │
│ CREATE INDEX ix_athletes_code ON athletes USING  ();                 │
│ CREATE INDEX ix_coaches_code ON coaches USING  ();                   │
│ CREATE INDEX ix_countries_code ON countries USING  ();               │
│ CREATE INDEX ix_medals_code ON medals USING  ();                     │
│ CREATE INDEX ix_medals_winner_code ON medals USING  ();              │
│ CREATE INDEX ix_teams_athletes_code ON teams USING  ();              │
│ CREATE INDEX ix_teams_code ON teams USING  ();                       │
│ CREATE INDEX ix_tokyo_medals_country_code ON tokyo_medals USING  (); │
│ CREATE INDEX ix_venues_code ON venues USING  ();                     │
├──────────────────────────────────────────────────────────────────────┤
│                               11 rows                                │
└──────────────────────────────────────────────────────────────────────┘