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

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

    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

          Activity

            People

            • Assignee:
              varun Varun Gupta
              Reporter:
              Alicen -
            • Votes:
              0 Vote for this issue
              Watchers:
              3 Start watching this issue

              Dates

              • Created:
                Updated: