[#MDEV-14211] Wrong result set from a query with DISTINCT and HAVING over a SF call

[MDEV-14211] Wrong result set from a query with DISTINCT and HAVING over a SF call Created: 2017-10-30 Updated: 2023-11-28
Status:	Confirmed
Project:	MariaDB Server
Component/s:	Optimizer
Affects Version/s:	5.5, 10.0, 10.1, 10.2, 10.3, 10.4, 10.6.11, 10.6, 10.7, 10.8, 10.9, 10.10
Fix Version/s:	10.4, 10.5, 10.6

Type:

Bug

Priority:

Major

Reporter:

Igor Babaev

Assignee:

Michael Widenius

Resolution:

Unresolved

Votes:

Labels:

11.0-sel

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 |

+------+------+

Comments

Comment by Igor Babaev [ 2017-10-30 ]

rmysql-5.6, mysql-5.7 also return wrong result sets.
mysql-8.0 returns the right result set.

Comment by Alice Sherepa [ 2022-12-01 ]

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

Generated at Thu Feb 08 08:11:48 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.