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

Window function multiplication on empty table on default value returns wrong result

    XMLWordPrintable

Details

    Description

      The following code returns deterministically the wrong result:

      CREATE SCHEMA A;
       
      CREATE TABLE A.A (B INTEGER DEFAULT 1);
       
      SELECT MAX(2) OVER () * COUNT(AJ.B) AS JA FROM A.A AS AJ WHERE FALSE;
      

      MariaDB return '2', Postgres and MySQL 8.0.16 return '0', which is correct (COUNT should return 0). Therefore, I expect that MariaDB also returns 0.

      This bug required DEFAULT 1 and WHERE FALSE. If one removes one of both, the bug disappears.
      Additionally, if COUNT is replaced with a constant, all databases return the same result. Therefore, I assume that COUNT forces an evaluation on the empty table, but returns the wrong result (1 instead of 0)

      I tested versions 10.4.5 and 10.4.6, both versions are affected. In MySQL 8.0.16, the correct value is returned.

      Attachments

        Issue Links

          Activity

            People

              varun Varun Gupta (Inactive)
              Alicen -
              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.