[MDEV-30255] 0 changed to 0.0 caused by DISTINCT and UNION ALL [10.1.37, 10.11.1] - Jira

Details

Type: Bug
Status: Confirmed (View Workflow)
Priority: Minor
Resolution: Unresolved
Affects Version/s: 10.1.37, 10.11.1, 10.3(EOL), 10.4(EOL), 10.5, 10.6, 10.7(EOL), 10.8(EOL), 10.9(EOL), 10.10(EOL), 10.11
Fix Version/s: 10.5, 10.6
Component/s: Optimizer
Labels:
Environment:
ubuntu 18.04

Description

Description:
In theory, the result of sql2(DISTINCT) ⊆ the result of sql1:

(SELECT (c1 DIV 1)*0.1 FROM t) UNION ALL (SELECT '1'); -- sql1

(SELECT DISTINCT (c1 DIV 1)*0.1 FROM t) UNION ALL (SELECT '1'); -- sql2

However, the value 0 changed to 0.0 after adding DISTINCT, seems like a logical bug:

mysql> select version();

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

| version()                               |

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

| 10.11.1-MariaDB-1:10.11.1+maria~ubu2204 |

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

1 row in set (0.00 sec)

mysql> (SELECT (c1 DIV 1)*0.1 FROM t) UNION ALL (SELECT '1');

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

| (c1 DIV 1)*0.1 |

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

| 0              |

| 1              |

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

2 rows in set (0.00 sec)

mysql> (SELECT DISTINCT (c1 DIV 1)*0.1 FROM t) UNION ALL (SELECT '1');

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

| (c1 DIV 1)*0.1 |

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

| 0.0            |

| 1              |

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

2 rows in set (0.00 sec)

How to repeat:

drop table if exists t;

create table t (c1 double);

insert into t values (0.1);

(SELECT (c1 DIV 1)*0.1 FROM t) UNION ALL (SELECT '1');

(SELECT DISTINCT (c1 DIV 1)*0.1 FROM t) UNION ALL (SELECT '1');

Hope these can be helpful for your debugging:
We look for the first version of the bug in all docker images (with format x.x.x) of https://hub.docker.com/_/mariadb/tags
We found that the bug first occurred in mariadb:10.1.37, it cannot be reproduced in mariadb:10.1.36:

MariaDB [TEST]> select version();

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

| version()                |

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

| 10.1.37-MariaDB-1~bionic |

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

1 row in set (0.00 sec)

MariaDB [TEST]> (SELECT (c1 DIV 1)*0.1 FROM t) UNION ALL (SELECT '1');

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

| (c1 DIV 1)*0.1 |

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

| 0              |

| 1              |

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

2 rows in set (0.00 sec)

MariaDB [TEST]> (SELECT DISTINCT (c1 DIV 1)*0.1 FROM t) UNION ALL (SELECT '1');

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

| (c1 DIV 1)*0.1 |

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

| 0.0            |

| 1              |

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

2 rows in set (0.00 sec)

MariaDB [TEST]> select version();

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

| version()                |

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

| 10.1.36-MariaDB-1~bionic |

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

1 row in set (0.00 sec)

MariaDB [TEST]> (SELECT (c1 DIV 1)*0.1 FROM t) UNION ALL (SELECT '1');

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

| (c1 DIV 1)*0.1 |

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

| 0.0            |

| 1              |

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

2 rows in set (0.00 sec)

MariaDB [TEST]> (SELECT DISTINCT (c1 DIV 1)*0.1 FROM t) UNION ALL (SELECT '1');

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

| (c1 DIV 1)*0.1 |

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

| 0.0            |

| 1              |

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

2 rows in set (0.00 sec)

Attachments

Activity

Ascending order - Click to sort in descending order

qaqcatz created issue - 2022-12-17 08:01

qaqcatz made changes - 2022-12-18 14:37

Field	Original Value	New Value
Summary	0 changed to 0.0 caused by DISTINCT and UNION ALL	0 changed to 0.0 caused by DISTINCT and UNION ALL [10.1.37, 10.11.1]

Alice Sherepa made changes - 2022-12-19 09:14

Labels

distinct-optimization union

distinct-optimization innodb union

Alice Sherepa made changes - 2022-12-19 09:16

Affects Version/s		10.3 [ 22126 ]
Affects Version/s		10.4 [ 22408 ]
Affects Version/s		10.5 [ 23123 ]
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 ]
Affects Version/s		10.11 [ 27614 ]

Alice Sherepa made changes - 2022-12-19 09:16

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 ]

Alice Sherepa made changes - 2022-12-19 09:18

Assignee

Oleksandr Byelkin [ sanja ]

Alice Sherepa made changes - 2022-12-19 09:19

Labels

distinct-optimization innodb union

distinct-optimization innodb union upstream

Sergei Golubchik made changes - 2023-01-09 18:57

Status

Open [ 1 ]

Confirmed [ 10101 ]

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

Fix Version/s

10.7 [ 24805 ]

Julien Fritsch made changes - 2023-04-27 13:53

Fix Version/s

10.3 [ 22126 ]

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

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 14:56

Fix Version/s

10.4 [ 22408 ]

People

Assignee:: Oleksandr Byelkin

Reporter:: qaqcatz

Votes:: 0 Vote for this issue

Watchers:: 4 Start watching this issue

Dates

Created:: 2022-12-17 08:01

Updated:: 2024-09-10 14:56

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