Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Fixed
-
10.4.5, 10.4.6, 10.2(EOL), 10.3(EOL), 10.4(EOL)
-
Ubuntu 18.04 running MariaDB in Docker
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
- is duplicated by
-
-
- Closed
-
- relates to
-
-
- Closed
-
Activity
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)
|
|
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: 0MariaDB [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)