Skip to content

sql: interval type not using same comparison as sql equality for aggregation or insertion #79549

Open
Open
sql: interval type not using same comparison as sql equality for aggregation or insertion#79549
Labels
A-sql-pgcompatSemantic compatibility with PostgreSQLA-sql-typingSQLtype inference, typing rules, type compatibility.C-bugCode not up to spec/doc, specs & docs deemed correct. Solution expected to change code/behavior.S-2Medium-high impact: many users impacted, risks of availability and difficult-to-fix data errorsT-sql-foundationsSQL Foundations Team (formerly SQL Schema + SQL Sessions)
@michae2

Description

@michae2
michae2
opened on Apr 6, 2022

It appears that the INTERVAL type is not using the same comparison for SQL equality as for aggregation. These queries illustrate the problem:

SELECT INTERVAL '1 day';
SELECT INTERVAL '24 hours';
SELECT INTERVAL '1 day' = INTERVAL '24 hours';
SELECT DISTINCT i FROM (VALUES (INTERVAL '1 day'), (INTERVAL '24 hours')) v(i);
SELECT i, COUNT(*) FROM (VALUES (INTERVAL '1 day'), (INTERVAL '24 hours')) v(i) GROUP BY i;
CREATE TABLE t (i INTERVAL PRIMARY KEY);
INSERT INTO t VALUES (INTERVAL '1 day');
INSERT INTO t VALUES (INTERVAL '24 hours');

In PostgreSQL, the two interval values are equal, group together in the same bucket, and count as the same key during insertion. PostgreSQL 14.2:

michae2=# SELECT INTERVAL '1 day';
 interval
----------
 1 day
(1 row)

michae2=# SELECT INTERVAL '24 hours';
 interval
----------
 24:00:00
(1 row)

michae2=# SELECT INTERVAL '1 day' = INTERVAL '24 hours';
 ?column?
----------
 t
(1 row)

michae2=# SELECT DISTINCT i FROM (VALUES (INTERVAL '1 day'), (INTERVAL '24 hours')) v(i);
   i
-------
 1 day
(1 row)

michae2=# SELECT i, COUNT(*) FROM (VALUES (INTERVAL '1 day'), (INTERVAL '24 hours')) v(i) GROUP BY i;
   i   | count
-------+-------
 1 day |     2
(1 row)

michae2=# CREATE TABLE t (i INTERVAL PRIMARY KEY);
CREATE TABLE

michae2=# INSERT INTO t VALUES (INTERVAL '1 day');
INSERT 0 1

michae2=# INSERT INTO t VALUES (INTERVAL '24 hours');
2022-04-06 15:07:42.453 PDT [27581] ERROR:  duplicate key value violates unique constraint "t_pkey"
2022-04-06 15:07:42.453 PDT [27581] DETAIL:  Key (i)=(24:00:00) already exists.
2022-04-06 15:07:42.453 PDT [27581] STATEMENT:  INSERT INTO t VALUES (INTERVAL '24 hours');
ERROR:  duplicate key value violates unique constraint "t_pkey"
DETAIL:  Key (i)=(24:00:00) already exists.

But in CockroachDB, the two interval values are equal, but do not group together in the same bucket or count as the same key during insertion. CockroachDB v22.1.0-beta.1, same for both vectorized engine and row-based engine:

demo@127.0.0.1:26257/defaultdb> SELECT INTERVAL '1 day';
SELECT INTERVAL '24 hours';
SELECT INTERVAL '1 day' = INTERVAL '24 hours';
SELECT DISTINCT i FROM (VALUES (INTERVAL '1 day'), (INTERVAL '24 hours')) v(i);
SELECT i, COUNT(*) FROM (VALUES (INTERVAL '1 day'), (INTERVAL '24 hours')) v(i) GROUP BY i;
  interval
------------
  1 day
(1 row)


Time: 0ms total (execution 0ms / network 0ms)

  interval
------------
  24:00:00
(1 row)


Time: 0ms total (execution 0ms / network 0ms)

  ?column?
------------
    true
(1 row)


Time: 0ms total (execution 0ms / network 0ms)

     i
------------
  1 day
  24:00:00
(2 rows)


Time: 0ms total (execution 0ms / network 0ms)

     i     | count
-----------+--------
  1 day    |     1
  24:00:00 |     1
(2 rows)


Time: 1ms total (execution 1ms / network 0ms)

demo@127.0.0.1:26257/defaultdb> CREATE TABLE t (i INTERVAL PRIMARY KEY);
CREATE TABLE


Time: 5ms total (execution 5ms / network 0ms)

demo@127.0.0.1:26257/defaultdb> INSERT INTO t VALUES (INTERVAL '1 day');
INSERT 1


Time: 7ms total (execution 7ms / network 0ms)

demo@127.0.0.1:26257/defaultdb> INSERT INTO t VALUES (INTERVAL '24 hours');
INSERT 1


Time: 1ms total (execution 1ms / network 0ms)

Jira issue: CRDB-14903

Epic: CRDB-20062

Metadata

Metadata

Assignees

No one assigned

    Labels

    A-sql-pgcompatSemantic compatibility with PostgreSQLA-sql-typingSQLtype inference, typing rules, type compatibility.C-bugCode not up to spec/doc, specs & docs deemed correct. Solution expected to change code/behavior.S-2Medium-high impact: many users impacted, risks of availability and difficult-to-fix data errorsT-sql-foundationsSQL Foundations Team (formerly SQL Schema + SQL Sessions)

    Type

    No type

    Projects

    SQL Queries

    Status

    Bugs to Fix

    Milestone

    No milestone

    Relationships

    None yet

    Development

    No branches or pull requests

    Issue actions