matrixorigin / matrixorigin/matrixone

[Compatibility]: temporal VAR_POP differs from MySQL at DECIMAL(24,6) precision

Open
#28,852 0 comments 0 reactions 1 assignee Claimed by @XuPeng-SH View on GitHub
kind/bug needs-triage
Dominant language
Go
Stars
1.9k
Forks
311
Avg merge
1d 3h
Merged PRs (30d)
768

Description

## Problem

After the type-binding regression in #28229 was fixed, MatrixOne accepts temporal operands for \`VAR_POP\`, \`VAR_SAMP\`, \`STDDEV_POP\`, and \`STDDEV_SAMP\`. However, for some large \`DATETIME\` / \`TIMESTAMP\` numeric values, the aggregate result differs from MySQL at user-observable \`DECIMAL(24,6)\` precision.

This is a follow-up compatibility issue, not a reopening of #28229: the prior binding-time rejection is fixed.

## Environment

- MatrixOne: \`main\`
- MatrixOne commit: \`be30a70fe50eac4e8c78e5117fdd69179ba853e0\`
- MatrixOne title: \`fix(agg): support decimal256 variance aggregates (#28841)\`
- Related fix: #28509, merge commit \`fffcacc813c9e3b30afb64f18e96e7b21e7ca71e\`
- Comparison: MySQL \`8.3.0\`

## Reproduction

\`\`\`sql
CREATE DATABASE temporal_variance_precision;
USE temporal_variance_precision;

CREATE TABLE t (
id INT PRIMARY KEY,
dt DATETIME,
ts TIMESTAMP
);

INSERT INTO t VALUES
(1, '2024-01-01 01:02:03', '2024-01-01 01:02:03'),
(2, '2024-01-02 02:03:04', '2024-01-02 02:03:04'),
(3, '2024-01-03 03:04:05', '2024-01-03 03:04:05');

SELECT
CAST(VAR_POP(dt) AS DECIMAL(24,6)) AS datetime_var_pop,
CAST(VAR_POP(ts) AS DECIMAL(24,6)) AS timestamp_var_pop
FROM t;
\`\`\`

## Actual Result

MatrixOne:

\`\`\`text
datetime_var_pop = 680202686800.666624
timestamp_var_pop = 680202686800.666624
\`\`\`

MySQL \`8.3.0\`:

\`\`\`text
datetime_var_pop = 680202686800.666600
timestamp_var_pop = 680202686800.666600
\`\`\`

## Expected Result

For temporal operands subject to MySQL-compatible numeric coercion, MatrixOne should produce a result compatible with MySQL at the requested \`DECIMAL(24,6)\` precision.

## Notes

- The absolute difference is small, but it is observable after \`CAST(... AS DECIMAL(24,6))\` and can affect comparisons, persisted computed values, and downstream calculations.
- String, date, window, prepared-statement, and binding behavior from #28229 were otherwise verified as fixed.
- The current MOTR regression for #28229 stabilizes this approximate temporal \`VAR_POP\` assertion at \`DECIMAL(24,3)\`; this issue tracks the higher-precision compatibility gap.

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.