matrixorigin / matrixorigin/matrixone
[Compatibility]: temporal VAR_POP differs from MySQL at DECIMAL(24,6) precision
- 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
Assessment
This issue has not been assessed yet.