Describe the problem
We have observed in our database, that after updating a row, queries leveraging a partial, unique index work as if row wasn't updated. This leads to wrong query results when the index is used. The problem seems to occur only if more than one index is defined on the table.
To reproduce
The problem is easily reproducible.
First we will start a CockroachDB node (single-node, but the behavior was originally observed in Cockroach Cloud cluster) and connect SQL client to it:
# Start CockroachDB
docker run -d --rm --name roach0 -p 26257:26257 cockroachdb/cockroach:v21.2.3 start-single-node --insecure
# Open SQL shell
docker exec -it roach0 ./cockroach sql --insecure
Then run the following SQL:
-- Create database with table and indexes
create database test;
use test;
create table assets (
id uuid not null default gen_random_uuid(),
tenant_id uuid not null,
external_id text,
legacy_id text not null,
delete_time timestamptz default null,
constraint asset_ck_id unique (id)
);
create unique index asset_unique_external_id_per_tenant
on assets(external_id, tenant_id)
where delete_time is null
;
create unique index asset_unique_legacy_id_per_tenant
on assets (legacy_id, tenant_id)
where delete_time is null
;
-- Insert sample data
insert into assets (id,tenant_id,external_id,legacy_id,delete_time) VALUES
('d07dfac2-1384-4a3e-9bd6-5b7e63d5bcbf'::uuid,'f862c2b1-f949-4117-8fc8-dbdd7d5d352a'::uuid,'2-3231925','Qo4E89',NULL),
('3121b4e5-d1be-4d8c-9008-227fcd1d52b8'::uuid,'f862c2b1-f949-4117-8fc8-dbdd7d5d352a'::uuid,NULL,'5cOAr6','2021-11-26 18:13:17.314984+01')
;
-- Find asset by external ID
select * from assets
where external_id ='2-3231925'
and tenant_id='f862c2b1-f949-4117-8fc8-dbdd7d5d352a'
and delete_time is null
;
-- id | tenant_id | external_id | legacy_id | delete_time
-- ---------------------------------------+--------------------------------------+-------------+-----------+--------------
-- d07dfac2-1384-4a3e-9bd6-5b7e63d5bcbf | f862c2b1-f949-4117-8fc8-dbdd7d5d352a | 2-3231925 | Qo4E89 | NULL
-- (1 row)
-- Set asset's external ID to null
update assets set external_id = null
where id='d07dfac2-1384-4a3e-9bd6-5b7e63d5bcbf'
;
-- Again, find asset by external ID. This still returns the asset, even though it no longer has the external ID.
select * from assets
where external_id ='2-3231925'
and tenant_id='f862c2b1-f949-4117-8fc8-dbdd7d5d352a'
and delete_time is null
;
-- id | tenant_id | external_id | legacy_id | delete_time
-- ---------------------------------------+--------------------------------------+-------------+-----------+--------------
-- d07dfac2-1384-4a3e-9bd6-5b7e63d5bcbf | f862c2b1-f949-4117-8fc8-dbdd7d5d352a | NULL | Qo4E89 | NULL
-- (1 row)
-- Again, find asset by external ID using primary index (index selection). This correctly doesn't find any asset with the ID.
select * from assets@primary
where external_id ='2-3231925'
and tenant_id='f862c2b1-f949-4117-8fc8-dbdd7d5d352a'
and delete_time is null
;
-- id | tenant_id | external_id | legacy_id | delete_time
-- ----+-----------+-------------+-----------+--------------
-- (0 rows)Expected behavior
Query should return correct results, regardless which index is used.
Environment:
- CockroachDB version 21.2.3
- Client app:
cockroach sql and JDBC
gz#10904
Describe the problem
We have observed in our database, that after updating a row, queries leveraging a partial, unique index work as if row wasn't updated. This leads to wrong query results when the index is used. The problem seems to occur only if more than one index is defined on the table.
To reproduce
The problem is easily reproducible.
First we will start a CockroachDB node (single-node, but the behavior was originally observed in Cockroach Cloud cluster) and connect SQL client to it:
Then run the following SQL:
Expected behavior
Query should return correct results, regardless which index is used.
Environment:
cockroach sqland JDBCgz#10904