[MDEV-20012] Window function multiplication on empty table on default value returns wrong result Created: 2019-07-10  Updated: 2020-06-19  Resolved: 2020-06-19

Status: Closed
Project: MariaDB Server
Component/s: Optimizer, Optimizer - Window functions
Affects Version/s: 10.4.5, 10.4.6, 10.2, 10.3, 10.4
Fix Version/s: N/A

Type: Bug Priority: Major
Reporter: - Assignee: Varun Gupta (Inactive)
Resolution: Fixed Votes: 0
Labels: bug, wrong_result
Environment:

Ubuntu 18.04 running MariaDB in Docker

Issue Links:
Duplicate
is duplicated by MDEV-21318 Wrong results with window functions a... Closed
Relates
relates to MDEV-21318 Wrong results with window functions a... Closed

 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.

 Comments   
Comment by Alice Sherepa [ 2019-07-10 ]

Thank you for the report!
Reproduced on 10.2-10.4, wrong results when the column has default value different from NULL:

MariaDB [test]> create table t1 (b int not null);
 
Query OK, 0 rows affected (0.046 sec)
 
 
 
MariaDB [test]> insert into t1 values (1),(2),(3);
 
Query OK, 3 rows affected (0.009 sec)
 
Records: 3  Duplicates: 0  Warnings: 0
 
 
 
MariaDB [test]> select count(b) from t1 where false;
 
+----------+
 
| count(b) |
 
+----------+
 
|        0 |
 
+----------+
 
1 row in set (0.001 sec)
 
 
 
MariaDB [test]> select count(b), max(2) over ()  from t1 where false;
 
+----------+----------------+
 
| count(b) | max(2) over () |
 
+----------+----------------+
 
|        1 |              2 |
 
+----------+----------------+
 
1 row in set (0.001 sec)
 
 
 
MariaDB [test]> select count(b), avg(b)  over ()  from t1 where false;
 
+----------+-----------------+
 
| count(b) | avg(b)  over () |
 
+----------+-----------------+
 
|        1 |          0.0000 |
 
+----------+-----------------+
 
1 row in set (0.001 sec)

Comment by Varun Gupta (Inactive) [ 2020-06-19 ]

Fixed by MDEV-21318

Comment by Varun Gupta (Inactive) [ 2020-06-19 ]

I see these results now

MariaDB [test]> select version();
 
+---------------------------+
 
| version()                 |
 
+---------------------------+
 
| 10.2.33-MariaDB-debug-log |
 
+---------------------------+
 
1 row in set (0.00 sec)
 
 
 
MariaDB [test]> select count(b) from t1 where false;
 
+----------+
 
| count(b) |
 
+----------+
 
|        0 |
 
+----------+
 
1 row in set (0.00 sec)
 
 
 
MariaDB [test]> select count(b), max(2) over ()  from t1 where false;
 
+----------+----------------+
 
| count(b) | max(2) over () |
 
+----------+----------------+
 
|        0 |              2 |
 
+----------+----------------+
 
1 row in set (0.01 sec)
 
 
 
MariaDB [test]> select count(b), avg(b)  over ()  from t1 where false;
 
+----------+-----------------+
 
| count(b) | avg(b)  over () |
 
+----------+-----------------+
 
|        0 |          0.0000 |
 
+----------+-----------------+
 
1 row in set (0.00 sec)

Generated at Thu Feb 08 08:56:04 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.