cockroachdb / cockroachdb/cockroach
`SQRDIFF` has different results when the order of arguments changed
- 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
Assessment
This issue has not been assessed yet.