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

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

    XMLWordPrintable

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

            varun Varun Gupta (Inactive)
            alice Alice Sherepa
            Votes:
            0 Vote for this issue
            Watchers:
            4 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.