cockroachdb / cockroachdb/cockroach

sql/builtins: `format_type` does not match Postgres behavior for INTERVAL

Open
#113,514 1 comment 0 reactions 0 assignees View on GitHub
A-sql-builtins A-sql-pgcompat C-bug db-cy-23 T-sql-foundations
Dominant language
Go
Stars
32.5k
Forks
4.1k
PR merge metrics
PR metrics pending

Description

**Describe the problem**

Postgres shows different information than CRDB with format_type for INTERVALs.

**To Reproduce**

This is the postgres behavior.

```
postgres=# CREATE TYPE utry AS (ufoo interval hour, ubar interval hour to minute);

postgres=# SELECT a.attname,
t.typname,
format_type(a.atttypid, a.atttypmod),
a.atttypid,
a.atttypmod
FROM pg_attribute a
JOIN pg_type t
ON a.atttypid = t.oid
WHERE a.attname IN ('ufoo', 'ubar')
ORDER BY a.attname;
```

**Expected behavior**

The above query should return
```
attname | typname | format_type | atttypid | atttypmod
---------+----------+-------------------------+----------+-----------
ubar | interval | interval hour to minute | 1186 | 201392127
ufoo | interval | interval hour | 1186 | 67174399
(2 rows)
```

But in CockroachDB it returns
```
attname | typname | format_type | atttypid | atttypmod
----------+----------+-------------+----------+------------
ubar | interval | interval | 1186 | -1
ufoo | interval | interval | 1186 | -1
(2 rows)
```

**Additional context**
This was discovered while implementing `_pg_interval_type` in #111401. This bug prevents that function from working correctly.

Jira issue: CRDB-33034

Epic CRDB-60811

Contributor guide

Open the contributing guide

Assessment

This issue has not been assessed yet.

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.