[MDEV-11868] min ( distinct ) over ( ) returns wrong value - Jira

Details

Type: Bug
Status: Closed (View Workflow)
Priority: Major
Resolution: Fixed
Affects Version/s: 10.2.3
Fix Version/s: 10.2.4
Component/s: Optimizer - Window functions
Labels:
- 10.2-rc

Description

10.2.3 beta

The following query works

select TDEC.CDEC, min( distinct TDEC.CDEC ) over () from TDEC

CDEC min( distinct TDEC.CDEC ) over ()
<null> -1
-1 -1
0 -1
1 -1
0.1 -1
10 -1

The same query applied to a simple view that projects the table does not

select `tdec`.`RNUM` AS `RNUM`,`tdec`.`CDEC` AS `CDEC` from `tdec`


select VDEC.CDEC, min( distinct VDEC.CDEC ) over () from VDEC

CDEC min( distinct VDEC.CDEC ) over ()
<null> 10
-1 10
0 10
1 10
0.1 10
10 10

Attachments

Activity

Ascending order - Click to sort in descending order

the6campbells created issue - 2017-01-21 23:57

Elena Stepanova made changes - 2017-01-23 15:39

Field	Original Value	New Value
Description	10.2.3 beta The following query works select TDEC.CDEC, min( distinct TDEC.CDEC ) over () from TDEC CDEC min( distinct TDEC.CDEC ) over () <null> -1 -1 -1 0 -1 1 -1 0.1 -1 10 -1 The same query applied to a simple view that projects the table does not select `tdec`.`RNUM` AS `RNUM`,`tdec`.`CDEC` AS `CDEC` from `tdec` select VDEC.CDEC, min( distinct VDEC.CDEC ) over () from VDEC CDEC min( distinct VDEC.CDEC ) over () <null> 10 -1 10 0 10 1 10 0.1 10 10 10	10.2.3 beta The following query works {code:sql} select TDEC.CDEC, min( distinct TDEC.CDEC ) over () from TDEC CDEC min( distinct TDEC.CDEC ) over () <null> -1 -1 -1 0 -1 1 -1 0.1 -1 10 -1 {code} The same query applied to a simple view that projects the table does not {code:sql} select `tdec`.`RNUM` AS `RNUM`,`tdec`.`CDEC` AS `CDEC` from `tdec` select VDEC.CDEC, min( distinct VDEC.CDEC ) over () from VDEC CDEC min( distinct VDEC.CDEC ) over () <null> 10 -1 10 0 10 1 10 0.1 10 10 10 {code}

Elena Stepanova made changes - 2017-01-23 15:43

Status

Open [ 1 ]

Confirmed [ 10101 ]

Elena Stepanova added a comment - 2017-01-23 15:45

Thanks for the report.

Test case
create table TDEC (CDEC int, RNUM int);
create view VDEC as select * from TDEC;
insert into TDEC (CDEC) values (null),(-1),(0),(1),(0),(10);
select TDEC.CDEC, min( distinct TDEC.CDEC ) over () from TDEC ;
select VDEC.CDEC, min( distinct VDEC.CDEC ) over () from VDEC ;

MariaDB [test]> select TDEC.CDEC, min( distinct TDEC.CDEC ) over () from TDEC ;

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

| CDEC | min( distinct TDEC.CDEC ) over () |

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

| NULL |                                -1 |

|   -1 |                                -1 |

|    0 |                                -1 |

|    1 |                                -1 |

|    0 |                                -1 |

|   10 |                                -1 |

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

6 rows in set (0.00 sec)

MariaDB [test]> select VDEC.CDEC, min( distinct VDEC.CDEC ) over () from VDEC ;

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

| CDEC | min( distinct VDEC.CDEC ) over () |

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

| NULL |                                10 |

|   -1 |                                10 |

|    0 |                                10 |

|    1 |                                10 |

|    0 |                                10 |

|   10 |                                10 |

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

6 rows in set (0.00 sec)

Elena Stepanova added a comment - 2017-01-23 15:45 Thanks for the report. Test case create table TDEC (CDEC int , RNUM int ); create view VDEC as select * from TDEC; insert into TDEC (CDEC) values ( null ),(-1),(0),(1),(0),(10); select TDEC.CDEC, min ( distinct TDEC.CDEC ) over () from TDEC ; select VDEC.CDEC, min ( distinct VDEC.CDEC ) over () from VDEC ; MariaDB [test]> select TDEC.CDEC, min ( distinct TDEC.CDEC ) over () from TDEC ; + ------+-----------------------------------+ | CDEC | min ( distinct TDEC.CDEC ) over () | + ------+-----------------------------------+ | NULL | -1 | | -1 | -1 | | 0 | -1 | | 1 | -1 | | 0 | -1 | | 10 | -1 | + ------+-----------------------------------+ 6 rows in set (0.00 sec) MariaDB [test]> select VDEC.CDEC, min ( distinct VDEC.CDEC ) over () from VDEC ; + ------+-----------------------------------+ | CDEC | min ( distinct VDEC.CDEC ) over () | + ------+-----------------------------------+ | NULL | 10 | | -1 | 10 | | 0 | 10 | | 1 | 10 | | 0 | 10 | | 10 | 10 | + ------+-----------------------------------+ 6 rows in set (0.00 sec)

Elena Stepanova made changes - 2017-01-23 15:46

Fix Version/s		10.2 [ 14601 ]
Assignee		Vicentiu Ciorbaru [ cvicentiu ]

Elena Stepanova made changes - 2017-01-24 16:57

Labels

10.2-rc

Vicențiu Ciorbaru made changes - 2017-02-14 21:03

Status

Confirmed [ 10101 ]

In Progress [ 3 ]

Vicențiu Ciorbaru added a comment - 2017-02-14 21:04

No longer reproducible. Probably fixed by one of Igor's patches. Adding testcase and closing.

Vicențiu Ciorbaru added a comment - 2017-02-14 21:04 No longer reproducible. Probably fixed by one of Igor's patches. Adding testcase and closing.

Vicențiu Ciorbaru added a comment - 2017-02-15 12:45

Current HEAD doesn't feature this problem. Added test case just in case.

Vicențiu Ciorbaru added a comment - 2017-02-15 12:45 Current HEAD doesn't feature this problem. Added test case just in case.

Vicențiu Ciorbaru made changes - 2017-02-15 12:45

Fix Version/s		10.2.4 [ 22116 ]
Fix Version/s	10.2 [ 14601 ]
Resolution		Fixed [ 1 ]
Status	In Progress [ 3 ]	Closed [ 6 ]

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

Workflow

MariaDB v3 [ 79313 ]

MariaDB v4 [ 151578 ]

People

Assignee:: Vicențiu Ciorbaru

Reporter:: the6campbells

Votes:: 0 Vote for this issue

Watchers:: 3 Start watching this issue

Dates

Created:: 2017-01-21 23:57

Updated:: 2017-02-15 12:45

Resolved:: 2017-02-15 12:45

Git Integration

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

MariaDB Server