cockroachdb / cockroachdb/cockroach

`SQRDIFF` has different results when the order of arguments changed

Open
#101,588 6 comments 0 reactions 0 assignees View on GitHub
C-bug O-community T-sql-queries X-blathers-triaged
Dominant language
Go
Stars
32.5k
Forks
4.1k
PR merge metrics
PR metrics pending

Description

**Describe the problem**

Please describe the issue you observed, and any steps we can take to reproduce it:

Consider the following program
```
CREATE DATABASE database0;
USE database0;
SET CLUSTER SETTING diagnostics.reporting.enabled = false;
SET CLUSTER SETTING diagnostics.reporting.send_crash_reports = false;
SET CLUSTER SETTING sql.metrics.statement_details.plan_collection.enabled = 'off';
SET CLUSTER SETTING sql.stats.automatic_collection.enabled = 'off';
SET CLUSTER SETTING timeseries.storage.enabled = 'off';
set experimental_enable_hash_sharded_indexes='on';

CREATE TABLE t1 (c0 STRING);
CREATE TABLE t2 (c0 INT);

INSERT INTO t1 (c0) VALUES('');
INSERT INTO t2 (c0) VALUES(1), (2);
CREATE STATISTICS s0 FROM t2;
UPSERT INTO t1 (c0) VALUES('w');

SELECT SQRDIFF(t2.rowid) FROM t2 CROSS HASH JOIN t1;
SELECT t2.rowid FROM t2 CROSS HASH JOIN t1;

SELECT SQRDIFF(t2.rowid) FROM t2 CROSS JOIN t1;
SELECT t2.rowid FROM t2 CROSS JOIN t1;
```
The use of `STATISTICS` and two different `CROSS JOIN` changes the order of `t2.rowid` in `SQRDIFF`, then it produces different results.

This is one of results I got:
```
> SELECT SQRDIFF(t2.rowid) FROM t2 CROSS HASH JOIN t1;
sqrdiff
----------------------
1073741823.9983616
(1 row)

Time: 1ms

> SELECT t2.rowid FROM t2 CROSS HASH JOIN t1;
rowid
----------------------
856809699799498753
856809699799498753
856809699799531521
856809699799531521
(4 rows)

Time: 1ms

> SELECT SQRDIFF(t2.rowid) FROM t2 CROSS JOIN t1;
sqrdiff
--------------
1073741824
(1 row)

Time: 1ms

> SELECT t2.rowid FROM t2 CROSS JOIN t1;
rowid
----------------------
856809699799498753
856809699799531521
856809699799498753
856809699799531521
(4 rows)

Time: 1ms
```

When I reduced it, I also got integer results that were all different. It shouldn't be just a difference in precision.
**To Reproduce**

What did you do? Describe in your own words.

I build CockroachDB from the latest commit version https://github.com/cockroachdb/cockroach/commit/51fcf796c5595549e90fca3cc1fd8165ee14fd6c, and run it with `./cockroach start-single-node --insecure`

**Expected behavior**
These two queries have the same results.

**Environment:**
- CockroachDB version https://github.com/cockroachdb/cockroach/commit/51fcf796c5595549e90fca3cc1fd8165ee14fd6c
- Server OS: [Ubuntu 22.04]
- Client app [CLI]

Jira issue: CRDB-27041

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.