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

Details

Type: Bug
Status: Confirmed (View Workflow)
Priority: Major
Resolution: Unresolved
Affects Version/s: 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)
Fix Version/s: 10.5, 10.6
Component/s: Optimizer
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 |

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

Attachments

Activity

Ascending order - Click to sort in descending order

Igor Babaev created issue - 2017-10-30 04:49

Igor Babaev made changes - 2017-10-30 04:53

Field	Original Value	New Value
Summary	Wrong result set from a query with DISTINCT and SF call in select list	Wrong result set from a query with DISTINCT and HAVING over a SF call

Igor Babaev made changes - 2017-10-30 04:57

Description

The query with HAVING from the following test case
{noformat}
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;
{noformat}
returns a wrong result set:
{noformat}
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 |
+------+------+
{noformat}

Igor Babaev made changes - 2017-10-30 04:59

Affects Version/s		5.5 [ 15800 ]
Affects Version/s		10.0 [ 16000 ]
Affects Version/s		10.1 [ 16100 ]
Affects Version/s		10.2 [ 14601 ]

Igor Babaev made changes - 2017-10-30 05:00

Component/s

Optimizer [ 10200 ]

Igor Babaev added a comment - 2017-10-30 05:02

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

Igor Babaev added a comment - 2017-10-30 05:02 rmysql-5.6, mysql-5.7 also return wrong result sets. mysql-8.0 returns the right result set.

Elena Stepanova made changes - 2017-11-07 17:27

Fix Version/s		10.2 [ 14601 ]
Assignee		Igor Babaev [ igor ]

Sergei Golubchik made changes - 2021-12-06 21:33

Workflow

MariaDB v3 [ 83341 ]

MariaDB v4 [ 140484 ]

Sergei Golubchik made changes - 2022-12-01 09:10

Description

The query with HAVING from the following test case
{code:sql}
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;
{code}
returns a wrong result set:
{noformat}
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 |
+------+------+
{noformat}

Alice Sherepa made changes - 2022-12-01 09:38

Affects Version/s		10.6.11 [ 28441 ]
Affects Version/s		10.3 [ 22126 ]
Affects Version/s		10.4 [ 22408 ]
Affects Version/s		10.6 [ 24028 ]
Affects Version/s		10.7 [ 24805 ]
Affects Version/s		10.8 [ 26121 ]
Affects Version/s		10.9 [ 26905 ]
Affects Version/s		10.10 [ 27530 ]

Alice Sherepa made changes - 2022-12-01 09:38

Fix Version/s		10.3 [ 22126 ]
Fix Version/s		10.4 [ 22408 ]
Fix Version/s		10.5 [ 23123 ]
Fix Version/s		10.6 [ 24028 ]
Fix Version/s		10.7 [ 24805 ]
Fix Version/s		10.8 [ 26121 ]
Fix Version/s		10.9 [ 26905 ]
Fix Version/s	10.2 [ 14601 ]

Alice Sherepa added a comment - 2022-12-01 09:53

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

Alice Sherepa added a comment - 2022-12-01 09:53 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

Alice Sherepa made changes - 2022-12-01 09:53

Status

Open [ 1 ]

Confirmed [ 10101 ]

Alice Sherepa made changes - 2022-12-01 09:55

Labels

11.0-sel

Michael Widenius made changes - 2023-01-27 14:11

Assignee

Igor Babaev [ igor ]

Michael Widenius [ monty ]

Julien Fritsch made changes - 2023-03-03 17:31

Fix Version/s

10.7 [ 24805 ]

Julien Fritsch made changes - 2023-04-27 14:19

Fix Version/s

10.3 [ 22126 ]

Julien Fritsch made changes - 2023-04-27 14:49

Fix Version/s

10.8 [ 26121 ]

Julien Fritsch made changes - 2023-11-28 10:39

Fix Version/s

10.9 [ 26905 ]

Julien Fritsch made changes - 2024-09-10 15:07

Fix Version/s

10.4 [ 22408 ]

People

Assignee:: Michael Widenius

Reporter:: Igor Babaev

Votes:: 0 Vote for this issue

Watchers:: 5 Start watching this issue

Dates

Created:: 2017-10-30 04:49

Updated:: 2024-09-10 15:07

Git Integration

Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.

MariaDB Server

Details

Description

Attachments

Activity

People

Dates

Git Integration