| Bug #115909 | Inconsistent behavior when using MAKEDATE function with SMALLINT | ||
|---|---|---|---|
| Submitted: | 23 Aug 2024 6:18 | Modified: | 23 Aug 2024 9:22 |
| Reporter: | Wenqian Deng | Email Updates: | |
| Status: | Not a Bug | Impact on me: | |
| Category: | MySQL Server | Severity: | S3 (Non-critical) |
| Version: | 9 | OS: | Any |
| Assigned to: | CPU Architecture: | Any | |
[23 Aug 2024 9:22]
MySQL Verification Team
Hi Mr. Deng,
Thank you for your bug report.
However, this is not a bug.
Our Reference Manual clearly states that function AVG() can be used only in aggregated queries, which are those that use GROUP BY.
If you run all the parts of your test case properly, it is clear to see from the output, including the warning and the result from the AVG, why this is not a bug:
mysql> DROP TABLE IF EXISTS t0, t1;
Query OK, 0 rows affected, 2 warnings (0.01 sec)
mysql>
mysql> CREATE TABLE t0 (c0 SMALLINT, c1 SMALLINT);
Query OK, 0 rows affected (0.01 sec)
mysql>
mysql> INSERT INTO t0 VALUES (3880, 262);
Query OK, 1 row affected (0.00 sec)
mysql>
mysql> SELECT AVG(c0) FROM t0;
+-----------+
| AVG(c0) |
+-----------+
| 3880.0000 |
+-----------+
1 row in set (0.00 sec)
mysql>
mysql> SELECT MAKEDATE(c0, c1) FROM t0;
+------------------+
| MAKEDATE(c0, c1) |
+------------------+
| 3880-09-18 |
+------------------+
1 row in set (0.00 sec)
mysql>
mysql> SELECT (MAKEDATE(t0.c0, t0.c1)) FROM t0 WHERE ((MAKEDATE(t0.c0, t0.c1)) BETWEEN (SELECT AVG((MAKEDATE(t0.c0, t0.c1))) FROM t0) AND ('2026-10-19'));
Empty set, 1 warning (0.00 sec)
mysql>
mysql> SHOW WARNINGS;
+---------+------+------------------------------------------------+
| Level | Code | Message |
+---------+------+------------------------------------------------+
| Warning | 1292 | Truncated incorrect DOUBLE value: '2026-10-19' |
+---------+------+------------------------------------------------+
1 row in set (0.00 sec)
mysql>
mysql> CREATE TABLE t1 AS (SELECT (MAKEDATE(t0.c0, t0.c1)) AS c0 FROM t0);
Query OK, 1 row affected (0.01 sec)
Records: 1 Duplicates: 0 Warnings: 0
mysql>
mysql> SELECT * FROM t1;
+------------+
| c0 |
+------------+
| 3880-09-18 |
+------------+
1 row in set (0.00 sec)
mysql>
mysql> SELECT AVG(c0) FROM t1;
+---------------+
| AVG(c0) |
+---------------+
| 38800918.0000 |
+---------------+
1 row in set (0.00 sec)
mysql>
mysql> SELECT t1.c0 FROM t1 WHERE (t1.c0 BETWEEN (SELECT AVG(t1.c0) FROM t1) AND ('2026-10-19'));
+------------+
| c0 |
+------------+
| 3880-09-18 |
+------------+
1 row in set (0.00 sec)
mysql>
mysql> DROP TABLE IF EXISTS t0, t1;
Query OK, 0 rows affected (0.00 sec)
Not a bug.
Description: The issue arises when the result of the MAKEDATE function is used in a BETWEEN clause to compare dates. How to repeat: 1. set up table t0 as follows: CREATE TABLE t0 (c0 SMALLINT, c1 SMALLINT); INSERT INTO t0 VALUES (3880, 262); 2. execute a SELECT on t0: SELECT (MAKEDATE(t0.c0, t0.c1)) FROM t0 WHERE ((MAKEDATE(t0.c0, t0.c1)) BETWEEN (SELECT AVG((MAKEDATE(t0.c0, t0.c1))) FROM t0) AND ('2026-10-19')); Result: 0 rows in set 3. Next, I create another table t1 to store the result of the MAKEDATE(t0.c0,t0.c1) as follows: CREATE TABLE t1 AS (SELECT (MAKEDATE(t0.c0, t0.c1)) AS c0 FROM t0); 4. performing a similar SELECT by using the value of t1.c0 instead of the origin MAKEDATE(t0.c0, t0.c1): SELECT t1.c0 FROM t1 WHERE (t1.c0 BETWEEN (SELECT AVG(t1.c0) FROM t1) AND ('2026-10-19')); Result: 3880-09-18 The expected behavior is that the two SELECTs return the same result because the data stored in t1.c0 equals the result of MAKEDATE(t0.c0, t0.c1).