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

Wrong result of query using DISTINCT COUNT(*) OVER (*)

    Details

      Description

      CREATE TABLE tt (i int) ;
      INSERT INTO `tt` VALUES (1),(0),(1),(2),(0),(NULL),(1),(2),(NULL),(1),(2);
       
      SELECT  DISTINCT COUNT(*) OVER () FROM tt GROUP BY i ;
      SELECT  DISTINCT COUNT(*) OVER (), MOD(MIN(i),2) FROM tt GROUP BY i ;
      

      MariaDB [test]> SELECT  DISTINCT COUNT(*) OVER () FROM tt GROUP BY i ;
      +------------------+
      | COUNT(*) OVER () |
      +------------------+
      |                4 |
      +------------------+
      1 row in set (0.001 sec)
       
      MariaDB [test]> SELECT  DISTINCT COUNT(*) OVER (), MOD(MIN(i),2) FROM tt GROUP BY i ;
      +------------------+---------------+
      | COUNT(*) OVER () | MOD(MIN(i),2) |
      +------------------+---------------+
      |                0 |          NULL |
      |                0 |             0 |
      |                0 |             1 |
      +------------------+---------------+
      3 rows in set (0.001 sec)
      

      in Mysql 8.0.11

      mysql> SELECT  DISTINCT COUNT(*) OVER (), MOD(MIN(i),2) FROM tt GROUP BY i ;
      +------------------+---------------+
      | COUNT(*) OVER () | MOD(MIN(i),2) |
      +------------------+---------------+
      |                4 |             1 |
      |                4 |             0 |
      |                4 |          NULL |
      +------------------+---------------+
      3 rows in set (0.00 sec)
      

        Attachments

          Activity

            People

            • Assignee:
              varun Varun Gupta
              Reporter:
              alice Alice Sherepa
            • Votes:
              0 Vote for this issue
              Watchers:
              1 Start watching this issue

              Dates

              • Created:
                Updated: