Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-11868

min ( distinct ) over ( ) returns wrong value

Details

    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 the6campbells created issue -
          elenst Elena Stepanova made changes -
          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}
          elenst Elena Stepanova made changes -
          Status Open [ 1 ] Confirmed [ 10101 ]
          elenst Elena Stepanova added a comment -

          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)

          elenst Elena Stepanova added a comment - 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)
          elenst Elena Stepanova made changes -
          Fix Version/s 10.2 [ 14601 ]
          Assignee Vicentiu Ciorbaru [ cvicentiu ]
          elenst Elena Stepanova made changes -
          Labels 10.2-rc
          cvicentiu Vicențiu Ciorbaru made changes -
          Status Confirmed [ 10101 ] In Progress [ 3 ]
          cvicentiu Vicențiu Ciorbaru added a comment -

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

          cvicentiu Vicențiu Ciorbaru added a comment - No longer reproducible. Probably fixed by one of Igor's patches. Adding testcase and closing.
          cvicentiu Vicențiu Ciorbaru added a comment -

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

          cvicentiu Vicențiu Ciorbaru added a comment - Current HEAD doesn't feature this problem. Added test case just in case.
          cvicentiu Vicențiu Ciorbaru made changes -
          Fix Version/s 10.2.4 [ 22116 ]
          Fix Version/s 10.2 [ 14601 ]
          Resolution Fixed [ 1 ]
          Status In Progress [ 3 ] Closed [ 6 ]
          serg Sergei Golubchik made changes -
          Workflow MariaDB v3 [ 79313 ] MariaDB v4 [ 151578 ]

          People

            cvicentiu Vicențiu Ciorbaru
            the6campbells the6campbells
            Votes:
            0 Vote for this issue
            Watchers:
            3 Start watching this issue

            Dates

              Created:
              Updated:
              Resolved:

              Git Integration

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