Details
-
Bug
-
Status: Stalled (View Workflow)
-
Major
-
Resolution: Unresolved
-
10.2(EOL), 10.3(EOL), 10.4(EOL), 10.5, 10.6, 10.7(EOL), 10.8(EOL)
Description
The query with HAVING from the following test case
SET @@sql_mode = '';
|
|
|
CREATE TABLE series (
|
val INT(10) UNSIGNED NOT NULL
|
);
|
INSERT INTO series VALUES(1);
|
CREATE TABLE seq_calls (
|
c INT
|
);
|
INSERT INTO seq_calls VALUES(0);
|
|
|
DELIMITER |;
|
CREATE FUNCTION next_seq_value() RETURNS INT
|
BEGIN
|
DECLARE next_val INT;
|
SELECT val INTO next_val FROM series;
|
UPDATE series SET val=mod(val + 1, 2);
|
UPDATE seq_calls SET c=c+1;
|
RETURN next_val;
|
END;
|
|
|
DELIMITER ;|
|
|
|
CREATE TABLE t1 (t INT, u INT, KEY(t));
|
INSERT INTO t1 VALUES(10, 10), (11, 11), (12, 12), (12, 13),(14, 15), (15, 16),
|
(16, 17), (17, 18);
|
ANALYZE TABLE t1;
|
|
|
SELECT * FROM series, seq_calls;
|
SELECT t, next_seq_value() r FROM t1 FORCE INDEX(t)
|
GROUP BY t HAVING (SELECT 1 FROM t1 WHERE r = 1 LIMIT 1)
|
ORDER BY t1.u;
|
|
|
DROP TABLE t1;
|
DROP FUNCTION next_seq_value;
|
DROP TABLE series, seq_calls;
|
returns a wrong result set:
MariaDB [test]> SELECT t, next_seq_value() r FROM t1 FORCE INDEX(t)
|
-> GROUP BY t HAVING (SELECT 1 FROM t1 WHERE r = 1 LIMIT 1)
|
-> ORDER BY t1.u;
|
+------+------+
|
| t | r |
|
+------+------+
|
| 10 | 1 |
|
| 11 | 1 |
|
| 12 | 1 |
|
| 14 | 1 |
|
| 15 | 1 |
|
| 16 | 1 |
|
| 17 | 1 |
|
+------+------+
|
The expected result set is:
MariaDB [test]> SELECT t, next_seq_value() r FROM t1 FORCE INDEX(t)
|
-> GROUP BY t HAVING (SELECT 1 FROM t1 WHERE r = 1 LIMIT 1)
|
-> ORDER BY t1.u;
|
+------+------+
|
| t | r |
|
+------+------+
|
| 10 | 1 |
|
| 12 | 1 |
|
| 15 | 1 |
|
| 17 | 1 |
|
+------+------+
|
This bug is a regression as 5.5,10.0,10.1 return the right result set