SHOW CREATE for materialized views returns incorrect DDL

[TheodoorP]

Describe the problem
When trying to use SHOW CREATE on a materialized view, the generated DDL is no longer a materialized view but a normal view, and the internal rowid column is part of the column definition.

To Reproduce

1. Create a materialized view:

CREATE MATERIALIZED VIEW test AS (SELECT 1 AS foo);

2. Use SHOW CREATE:

SHOW CREATE test

3. See that the DDL is no longer a materialized view and that the rowid column is now included.

Expected behavior
I would expect the DDL to create a materialized view without a rowid.

Additional data / screenshots

Environment:

• CockroachDB version: 21.2.8
• Server OS: any
• Client app: any

Jira issue: CRDB-15494

[blathers-crl]

Hello, I am Blathers. I am here to help you get the issue triaged.

Hoot - a bug! Though bugs are the bane of my existence, rest assured the wretched thing will get the best of care here.

I was unable to automatically find someone to ping.

If we have not gotten back to your issue within a few business days, you can try the following:

• Join our community slack channel and ask on #cockroachdb.
• Try find someone from here if you know they worked closely on the area and CC them.

_{🦉 Hoot! I am a Blathers, a bot for CockroachDB. My owner is otan.}

[msbutler]

@TheodoorP thanks for filing! This is probably a bug (or a docs issue), also apparent on master.

Per CRDB docs:

In CockroachDB, every table requires a primary key. If one is not explicitly defined, a column called rowid of the type INT is added automatically as the primary key, with the unique_rowid() function used to ensure that new rows always default to unique rowid values.

SHOW CREATE on a regular table never shows the rowId so I don't think SHOW CREATE should return rowId for materialized views either.

[TheodoorP]

@msbutler based on your reply I would assume that the DDL using CREATE VIEW instead of CREATE MATERIALIZED VIEW is a separate bug? If so I'll open a separate issue for that.

[msbutler]

@TheodoorP ah, that was a typo. This is only apparent for materialized views.

[mehrankamal]

Hi @rafiss , I would like to take up this issue. Can you please give me some pointers to begin with. Cuz I'm kinda new to opensource and CockroachDB apparently.