Bug Report
It appears that the query produces different results depending on the column constraints, specifically when ZEROFILL, NOT NULL, and UNIQUE are applied. The result of the ABS and MAX aggregation functions differs when a column has the ZEROFILL and NOT NULL constraints, even though the query itself remains the same. This unexpected behavior could be due to how TiDB handles columns with these specific constraints in combination with aggregation functions.
1. Minimal reproduce step (Required)
DROP DATABASE IF EXISTS test;
CREATE DATABASE test;
USE test;
-- Create table with ZEROFILL, NOT NULL, and UNIQUE constraints
CREATE TABLE t0(c0 NUMERIC ZEROFILL NOT NULL UNIQUE);
INSERT IGNORE INTO t0 VALUES (1);
-- Query 1:
SELECT ABS(MAX(b'101010')) AS c3
FROM t0 AS tom8
GROUP BY tom8.c0;
-- Drop table and recreate without ZEROFILL, NOT NULL, and UNIQUE constraints
DROP TABLE t0;
CREATE TABLE t0(c0 NUMERIC);
-- Insert data into t0
INSERT IGNORE INTO t0 VALUES (1);
-- Query 2:
SELECT ABS(MAX(b'101010')) AS c3
FROM t0 AS tom8
GROUP BY tom8.c0;
2. What did you expect to see? (Required)
I expected both queries to return the same result because the logic of the aggregation (MAX and ABS) should not be affected by the presence of column constraints like ZEROFILL, NOT NULL, or UNIQUE.
3. What did you see instead (Required)
Output for Query 1:
+------+
| c3 |
+------+
| 42 |
+------+
1 row in set (0.00 sec)
Output for Query 2:
+------+
| c3 |
+------+
| 0 |
+------+
1 row in set, 1 warning (0.00 sec)
4. What is your TiDB version? (Required)
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| tidb_version() |
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Release Version: v8.5.5
Edition: Community
Git Commit Hash: 1fa258b833ff113883beeba40bc130be7ce66610
Git Branch: HEAD
UTC Build Time: 2026-03-12 08:34:59
GoVersion: go1.25.5
Race Enabled: false
Check Table Before Drop: false
Store: unistore |
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
Bug Report
It appears that the query produces different results depending on the column constraints, specifically when ZEROFILL, NOT NULL, and UNIQUE are applied. The result of the ABS and MAX aggregation functions differs when a column has the ZEROFILL and NOT NULL constraints, even though the query itself remains the same. This unexpected behavior could be due to how TiDB handles columns with these specific constraints in combination with aggregation functions.
1. Minimal reproduce step (Required)
2. What did you expect to see? (Required)
I expected both queries to return the same result because the logic of the aggregation (MAX and ABS) should not be affected by the presence of column constraints like ZEROFILL, NOT NULL, or UNIQUE.
3. What did you see instead (Required)
4. What is your TiDB version? (Required)