roachtest.unoptimized-query-oracle/disable-rules=half failed due to extra precision decimals

[rytaft]

roachtest.unoptimized-query-oracle/disable-rules=half failed with artifacts on master @ 80c274877a917580af62be6eb0cd48c8c7ae9c08:

test artifacts and logs in: /artifacts/unoptimized-query-oracle/disable-rules=half/run_1
	query_comparison_util.go:237,query_comparison_util.go:69,unoptimized_query_oracle.go:51,test_runner.go:896: . 17242 statements run: expected unoptimized and optimized results to be equal
		(1) attached stack trace
		  -- stack trace:
		  | github.com/cockroachdb/cockroach/pkg/cmd/roachtest/tests.(*queryComparisonHelper).makeError
		  | 	github.com/cockroachdb/cockroach/pkg/cmd/roachtest/tests/query_comparison_util.go:345
		  | [...repeated from below...]
		Wraps: (2) . 17242 statements run
		Wraps: (3) attached stack trace
		  -- stack trace:
		  | github.com/cockroachdb/cockroach/pkg/cmd/roachtest/tests.runUnoptimizedQueryOracleImpl
		  | 	github.com/cockroachdb/cockroach/pkg/cmd/roachtest/tests/unoptimized_query_oracle.go:160
		  | github.com/cockroachdb/cockroach/pkg/cmd/roachtest/tests.registerUnoptimizedQueryOracle.func1.1
		  | 	github.com/cockroachdb/cockroach/pkg/cmd/roachtest/tests/unoptimized_query_oracle.go:54
		  | github.com/cockroachdb/cockroach/pkg/cmd/roachtest/tests.runOneRoundQueryComparison
		  | 	github.com/cockroachdb/cockroach/pkg/cmd/roachtest/tests/query_comparison_util.go:236
		  | github.com/cockroachdb/cockroach/pkg/cmd/roachtest/tests.runQueryComparison
		  | 	github.com/cockroachdb/cockroach/pkg/cmd/roachtest/tests/query_comparison_util.go:69
		  | github.com/cockroachdb/cockroach/pkg/cmd/roachtest/tests.registerUnoptimizedQueryOracle.func1
		  | 	github.com/cockroachdb/cockroach/pkg/cmd/roachtest/tests/unoptimized_query_oracle.go:51
		  | main.(*testRunner).runTest.func2
		  | 	main/pkg/cmd/roachtest/test_runner.go:896
		  | runtime.goexit
		  | 	GOROOT/src/runtime/asm_amd64.s:1571
		Wraps: (4) expected unoptimized and optimized results to be equal
		  |   []string{
		  |   	strings.Join({
		  |   		"-9.8,0,0,104,0,1661263847326383409.0",
		  | + 		"000000000",
		  |   	}, ""),
		  |   	strings.Join({
		  |   		"-9.8,104,0,104,0,1661263847077927364.0",
		  | + 		"000000000",
		  |   	}, ""),
		  |   }
		  | sql: SELECT
		  | 	(-9.8):::DECIMAL AS col_37722,
		  | 	tab_21236.col1_0 AS col_37723,
		  | 	0:::OID AS col_37724,
		  | 	tab_21236.tableoid AS col_37725,
		  | 	0:::OID AS col_37726,
		  | 	(tab_21236.crdb_internal_mvcc_timestamp::DECIMAL / 1:::DECIMAL::DECIMAL)::DECIMAL AS col_37727
		  | FROM
		  | 	defaultdb.public.table1@[0] AS tab_21236
		  | 	JOIN defaultdb.public.table1@[0] AS tab_21237 ON
		  | 			(tab_21236.crdb_internal_mvcc_timestamp) = (tab_21237.crdb_internal_mvcc_timestamp)
		  | ORDER BY
		  | 	tab_21236.tableoid ASC
		Error types: (1) *withstack.withStack (2) *errutil.withPrefix (3) *withstack.withStack (4) *errutil.leafError

Parameters: ROACHTEST_cloud=gce , ROACHTEST_cpu=4 , ROACHTEST_ssd=0

Help

See: roachtest README

See: How To Investigate (internal)

This test on roachdash | Improve this report!

Originally posted by @cockroach-teamcity in #86308 (comment)

Jira issue: CRDB-18936

[blathers-crl]

Hi @rytaft, please add a C-ategory label to your issue. Check out the label system docs.

While you're here, please consider adding an A- label to help keep our repository tidy.

_{🦉 Hoot! I am a Blathers, a bot for CockroachDB. My owner is otan.}

[rytaft]

This failure looks like the same issue: #86308 (comment)

[rytaft]

Hey @cucaroach can you please look into this failure? Seems to me like we have one version returning a number with much higher precision than the other. Doesn't look like a correctness issue, but we should figure out what's going on and fix it to prevent the test from continuing to fail for this reason.

[DrewKimball]

#85318 also looks the same as this one

[DrewKimball]

I found a reproduction here

[rytaft]

Great, closing this as a duplicate.

[DrewKimball]

Reopening this one to specifically track the decimal precision issue.

[cucaroach]

Interestingly it repros with vectorize = off too.

demo@127.0.0.1:26257/defaultdb> CREATE TABLE t (x INT8 NOT NULL);
CREATE TABLE


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

demo@127.0.0.1:26257/defaultdb> INSERT INTO t VALUES (-4429772553778622992);
INSERT 0 1


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

demo@127.0.0.1:26257/defaultdb> EXPLAIN  SELECT (x / 1)::DECIMAL FROM t;
           info
--------------------------
  distribution: local
  vectorized: true

  • render
  │
  └── • scan
        missing stats
        table: t@t_pkey
        spans: FULL SCAN
(9 rows)


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

demo@127.0.0.1:26257/defaultdb> EXPLAIN (VERBOSE) SELECT (x / 1)::DECIMAL FROM t;
                        info
----------------------------------------------------
  distribution: local
  vectorized: true

  • render
  │ columns: ("numeric")
  │ render numeric: x::DECIMAL
  │
  └── • scan
        columns: (x)
        estimated row count: 1,000 (missing stats)
        table: t@t_pkey
        spans: FULL SCAN
(12 rows)


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

demo@127.0.0.1:26257/defaultdb> SELECT (x / 1)::DECIMAL FROM t;
        numeric
------------------------
  -4429772553778622992
(1 row)


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

demo@127.0.0.1:26257/defaultdb> set vectorize = off;
SET


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

demo@127.0.0.1:26257/defaultdb> SELECT (x / 1)::DECIMAL FROM t;
        numeric
------------------------
  -4429772553778622992
(1 row)


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

demo@127.0.0.1:26257/defaultdb> SET testing_optimizer_disable_rule_probability = 1;
SET


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

warning: error retrieving the database name: ERROR: use of crdb_internal_vtable_pk column not allowed. (SQLSTATE XXUUU)
demo@127.0.0.1:26257/?> SELECT (x / 1)::DECIMAL FROM t;
         numeric
--------------------------
  -4429772553778622992.0
(1 row)


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

warning: error retrieving the database name: ERROR: use of crdb_internal_vtable_pk column not allowed. (SQLSTATE XXUUU)
demo@127.0.0.1:26257/?> EXPLAIN (VERBOSE) SELECT (x / 1)::DECIMAL FROM t;
                                        info
------------------------------------------------------------------------------------
  distribution: local
  vectorized: false

  • render
  │ columns: ("numeric")
  │ render numeric: x / 1
  │
  └── • scan
        columns: (x, rowid, crdb_internal_mvcc_timestamp, tableoid)
        estimated row count: 1 (100% of the table; stats collected 17 seconds ago)
        table: t@t_pkey
        spans: FULL SCAN
(12 rows)


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

warning: error retrieving the database name: ERROR: use of crdb_internal_vtable_pk column not allowed. (SQLSTATE XXUUU)
demo@127.0.0.1:26257/?> 

[cucaroach]

I think this issue is super fundamental:

❯ cr demo --empty -e "SELECT 1 / 1"
        ?column?
-------------------------
  1.0000000000000000000
(1 row)


Time: 1ms

vs.

❯ sudo -u postgres psql -c "SELECT 1/1"
 ?column? 
----------
        1
(1 row)

[cucaroach]

Related: #41448

[cucaroach]

So my current take is we have two bugs:

1. sql: make / operator perform integer division #41448 which is out of scope for 22.2
2. the optimizer doesn't follow the same rules as the eval engine

I think we could consider #2 for 23.1 (ie if we're gonna constant fold integer division the result should be a decimal so we're internally consistent but that would come at the cost of more visible disagreement with PG and potentially turn the heat up on #41448). Probably we should let sleeping dogs lie at wait for #41448.

Anyways all this is to say that I'm only gonna look for an expedient roachtest specific fix for this and ignore the larger issues.

[DrewKimball]

Here's a similar problem from #87495 that happens when using // on decimals instead of / on ints:

statement ok
CREATE TABLE t (x DECIMAL NOT NULL);
INSERT INTO t VALUES (1.0);

query T
EXPLAIN (VEC) SELECT (x::DECIMAL // 1::DECIMAL)::DECIMAL FROM t;
----
│
└ Node 1
  └ *colfetcher.ColBatchScan

query R
SELECT (x::DECIMAL // 1::DECIMAL)::DECIMAL FROM t;
----
1.0

statement ok
SET testing_optimizer_disable_rule_probability = 1;

query T
EXPLAIN (VEC) SELECT (x::DECIMAL // 1::DECIMAL)::DECIMAL FROM t;
----
│
└ Node 1
  └ *colexecprojconst.projFloorDivDecimalDecimalConstOp
    └ *colfetcher.ColBatchScan

query R
SELECT (x::DECIMAL // 1::DECIMAL)::DECIMAL FROM t;
----
1

[mgartner]

Another reproduction here: #87605 (comment)