Details
-
Bug
-
Status: Confirmed (View Workflow)
-
Major
-
Resolution: Unresolved
-
10.6.11, 5.5(EOL), 10.0(EOL), 10.1(EOL), 10.2(EOL), 10.3(EOL), 10.4(EOL), 10.6, 10.7(EOL), 10.8(EOL), 10.9(EOL), 10.10(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 DISTINCT t1.t, next_seq_value() r FROM t1, t1 P |
WHERE P.t = t1.t HAVING r = 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 DISTINCT t1.t, next_seq_value() r FROM t1, t1 P
|
-> WHERE P.t = t1.t HAVING r = 1 ORDER BY t1.u;
|
+------+------+
|
| t | r |
|
+------+------+
|
| 10 | 0 |
|
| 11 | 0 |
|
| 12 | 0 |
|
| 14 | 0 |
|
| 15 | 0 |
|
| 16 | 0 |
|
| 17 | 0 |
|
+------+------+
|
Attachments
Activity
Please check all variations of the query = with/without ORDER BY, etc
currently 10.3 4e9206736c403206915c09-10.11:
MariaDB [test]> SELECT distinct t1.t, next_seq_value() r FROM t1, t1 P WHERE P.t = t1.t HAVING r = 1 ORDER BY t1.u;
|
Empty set (0,017 sec)
|
without DISTINCT- 10.3-10.5
SELECT t1.t, next_seq_value() r FROM t1, t1 P WHERE P.t = t1.t HAVING r = 1 ORDER BY t1.u;
|
t r
|
10 0
|
11 0
|
12 0
|
12 0
|
12 0
|
12 0
|
14 0
|
15 0
|
16 0
|
17 0
|
10.6+:
SELECT t1.t, next_seq_value() r FROM t1, t1 P WHERE P.t = t1.t HAVING r = 1 ORDER BY t1.u;
|
t r
|
10 1
|
12 1
|
12 1
|
14 1
|
16 1
|
without ORDER BY: 10.3-10.11:
SELECT t1.t, next_seq_value() r FROM t1, t1 P WHERE P.t = t1.t HAVING r = 1;
|
t r
|
10 0
|
11 0
|
12 0
|
12 0
|
12 0
|
12 0
|
14 0
|
15 0
|
16 0
|
17 0
|
|
|
SELECT t1.t, next_seq_value() r FROM t1, t1 P WHERE P.t = t1.t;
|
t r
|
10 1
|
11 0
|
12 1
|
12 0
|
12 1
|
12 0
|
14 1
|
15 0
|
16 1
|
17 0
|
|
rmysql-5.6, mysql-5.7 also return wrong result sets.
mysql-8.0 returns the right result set.